-- Cross-tenant reference guard (defence in depth on top of RLS).
--
-- RLS stops a tenant from reading or writing rows of another tenant, but a
-- plain foreign key is checked by PostgreSQL without RLS, so a row in tenant B
-- could reference (for example) a storer or SKU that belongs to tenant A.
-- This trigger rejects any INSERT/UPDATE where a foreign key column points at
-- a row whose tenant_id differs from the new row's tenant_id.
--
-- The check is generic: it reads the table's foreign keys from the catalogue,
-- so new tables and new FK columns are covered automatically after
-- SELECT app_apply_tenant_ref_guard();

CREATE OR REPLACE FUNCTION app_check_tenant_refs() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  fk record;
  new_tenant uuid;
  ref_value uuid;
  ref_tenant uuid;
BEGIN
  new_tenant := (to_jsonb(NEW) ->> 'tenant_id')::uuid;
  IF new_tenant IS NULL THEN
    RETURN NEW; -- platform-level rows (e.g. audit entries without a tenant)
  END IF;

  FOR fk IN
    SELECT a.attname AS col, cl.relname AS ref_table
    FROM pg_constraint c
    JOIN pg_class cl ON cl.oid = c.confrelid
    JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
    WHERE c.contype = 'f'
      AND c.conrelid = TG_RELID
      AND array_length(c.conkey, 1) = 1
      AND cl.relname <> 'tenants'
      AND EXISTS (SELECT 1 FROM pg_attribute ra WHERE ra.attrelid = c.confrelid AND ra.attname = 'tenant_id' AND NOT ra.attisdropped)
  LOOP
    ref_value := (to_jsonb(NEW) ->> fk.col)::uuid;
    IF ref_value IS NOT NULL THEN
      -- The caller's tenant row is changing a reference: only check columns that are set/changed.
      IF TG_OP = 'UPDATE' AND (to_jsonb(OLD) ->> fk.col) IS NOT DISTINCT FROM (to_jsonb(NEW) ->> fk.col) THEN
        CONTINUE;
      END IF;
      EXECUTE format('SELECT tenant_id FROM public.%I WHERE id = $1', fk.ref_table) INTO ref_tenant USING ref_value;
      IF ref_tenant IS NULL OR ref_tenant <> new_tenant THEN
        RAISE EXCEPTION 'Cross-tenant reference rejected: %.% -> %(%) belongs to another tenant or does not exist',
          TG_TABLE_NAME, fk.col, fk.ref_table, ref_value
          USING ERRCODE = 'foreign_key_violation';
      END IF;
    END IF;
  END LOOP;
  RETURN NEW;
END
$$;

CREATE OR REPLACE FUNCTION app_apply_tenant_ref_guard() RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
  r record;
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
    EXECUTE format('DROP TRIGGER IF EXISTS tenant_ref_guard ON public.%I', r.table_name);
    EXECUTE format('CREATE TRIGGER tenant_ref_guard BEFORE INSERT OR UPDATE ON public.%I FOR EACH ROW EXECUTE FUNCTION app_check_tenant_refs()', r.table_name);
  END LOOP;
END
$$;

SELECT app_apply_tenant_ref_guard();