-- Outbox: lease timestamp so rows stuck in PROCESSING after a crash are re-claimed.
ALTER TABLE outbox_events ADD COLUMN IF NOT EXISTS claimed_at timestamptz;
CREATE INDEX IF NOT EXISTS outbox_events_claim_idx ON outbox_events (status, claimed_at);

-- Tenant reference guard: fire on INSERT and only on UPDATE OF the foreign-key columns,
-- so hot-path updates (balance quantities, task status) do not pay for the check.
CREATE OR REPLACE FUNCTION app_apply_tenant_ref_guard() RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
  r record;
  cols text;
BEGIN
  FOR r IN
    SELECT c.table_name
    FROM information_schema.columns c
    JOIN information_schema.tables t ON t.table_name = c.table_name AND t.table_schema = c.table_schema
    WHERE c.table_schema = 'public' AND c.column_name = 'tenant_id' AND t.table_type = 'BASE TABLE' AND c.table_name <> 'tenants'
  LOOP
    SELECT string_agg(quote_ident(a.attname), ', ') INTO cols
    FROM pg_constraint k
    JOIN pg_class cl ON cl.oid = k.confrelid
    JOIN pg_attribute a ON a.attrelid = k.conrelid AND a.attnum = k.conkey[1]
    WHERE k.contype = 'f' AND k.conrelid = ('public.' || quote_ident(r.table_name))::regclass
      AND array_length(k.conkey, 1) = 1 AND cl.relname <> 'tenants'
      AND EXISTS (SELECT 1 FROM pg_attribute ra WHERE ra.attrelid = k.confrelid AND ra.attname = 'tenant_id' AND NOT ra.attisdropped);
    EXECUTE format('DROP TRIGGER IF EXISTS tenant_ref_guard ON public.%I', r.table_name);
    IF cols IS NULL THEN
      CONTINUE; -- no guarded foreign keys on this table
    END IF;
    EXECUTE format('CREATE TRIGGER tenant_ref_guard BEFORE INSERT OR UPDATE OF %s ON public.%I FOR EACH ROW EXECUTE FUNCTION app_check_tenant_refs()', cols, r.table_name);
  END LOOP;
END
$$;

SELECT app_apply_tenant_ref_guard();