-- +goose Up
-- GATE A REGRESSION FIX. Seven integration tests failed and four of them traced to one systemic cause that
-- had never been named: THE STAGING DATABASE'S APP ROLE DOES NOT RECEIVE WHAT THE MIGRATIONS GRANT.
--
-- 51 of the 84 migrations name missionmatch_app or missionmatch_migrator literally in GRANT statements and in
-- RLS policy role lists. ZERO of them mention missionmatch_staging_app or missionmatch_staging_migrator, which
-- are the roles the staging environment actually connects as, established deliberately for containment. So
-- every grant and every role scoped policy a migration adds lands on the production named role and the staging
-- app role silently misses it.
--
-- MEASURED, rather than assumed, before writing a line of this:
--   table level grants     missionmatch_app 195, missionmatch_staging_app 195. Already mirrored.
--   column level grants    missionmatch_app 2055, missionmatch_staging_app 2048. SEVEN MISSING, and all seven
--                          are the organizations UPDATE columns that migration 0057 granted for the time zone
--                          feature. Someone mirrored the grants by hand once and 0057 landed afterwards.
--   RLS policies           57 total, 6 name missionmatch_app, 0 name missionmatch_staging_app.
--
-- WHY THE POLICY HALF IS WORSE THAN THE GRANT HALF. An RLS policy with a TO clause applies only to the named
-- roles and their members. With no applicable policy, RLS DENIES EVERYTHING, so the staging app role was
-- refused on outcome_events even though it holds INSERT and SELECT. The error reads "new row violates row
-- level security policy", which sounds like a policy bug and is actually a policy that was never addressed to
-- the caller. That is what broke TestBackfillConvertsSlateDecisionsAndDedups.
--
-- WHY NOT ROLE MEMBERSHIP, WHICH WOULD BE ONE LINE. GRANT missionmatch_app TO missionmatch_staging_app would
-- fix both halves at once, and the PostgreSQL documentation confirms normal role membership and inheritance
-- rules apply to a policy's TO clause. IT IS REFUSED HERE BECAUSE IT WOULD BREAK CONTAINMENT. Membership
-- conveys ALL privileges of the granted role, INCLUDING database level CONNECT, and missionmatch_app has
-- CONNECT on the production database while the staging roles deliberately have none. Measured:
--   missionmatch              app CONNECT t, staging_app CONNECT f
--   missionmatch_staging      app CONNECT t, staging_app CONNECT t
-- That f is the containment boundary the D6 work established, and it is enforced at connection time, which is
-- the strongest place to enforce it. Trading it for a shorter migration would be a bad trade.
--
-- SO THIS MIRRORS PRIVILEGES WITHOUT MIRRORING IDENTITY. It copies table privileges, column privileges, and
-- policy applicability, and it never touches a database level privilege, so CONNECT stays exactly as it is.
--
-- IT IS WRITTEN GENERICALLY RATHER THAN AS SEVEN GRANTS AND SIX ALTERS, because a hand written patch fixes
-- today and drifts again on the next migration that adds a grant. Re running this block after any future
-- migration re establishes parity.

-- +goose StatementBegin
DO $$
DECLARE
  staging_app CONSTANT text := 'missionmatch_staging_app';
  prod_app    CONSTANT text := 'missionmatch_app';
  g           record;
  pol         record;
  n_tab       int := 0;
  n_col       int := 0;
  n_pol       int := 0;
BEGIN
  -- ONLY IN THE STAGING DATABASE. Roles are CLUSTER wide in PostgreSQL while privileges are per database, so
  -- without this guard the block would grant the staging role privileges on PRODUCTION tables. Those grants
  -- would be inert today because the staging roles cannot connect to the production database, and relying on
  -- one boundary when a second is free is how the first one eventually gets removed by someone in a hurry.
  IF current_database() <> 'missionmatch_staging' THEN
    RAISE NOTICE 'staging role parity: skipped, this is %, not missionmatch_staging', current_database();
    RETURN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = staging_app) THEN
    RAISE NOTICE 'staging role parity: skipped, role % does not exist', staging_app;
    RETURN;
  END IF;

  -- 1. TABLE LEVEL PRIVILEGES the production app role holds and the staging app role does not.
  FOR g IN
    SELECT a.table_name, a.privilege_type
    FROM information_schema.role_table_grants a
    WHERE a.grantee = prod_app AND a.table_schema = 'public'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.role_table_grants b
        WHERE b.grantee = staging_app AND b.table_schema = 'public'
          AND b.table_name = a.table_name AND b.privilege_type = a.privilege_type)
  LOOP
    EXECUTE format('GRANT %s ON public.%I TO %I', g.privilege_type, g.table_name, staging_app);
    n_tab := n_tab + 1;
  END LOOP;

  -- 2. COLUMN LEVEL PRIVILEGES, which is where the seven organizations UPDATE grants live. These do NOT appear
  -- in role_table_grants, which is why the first diagnosis of this failure read the app role as SELECT only
  -- and called it a grant drift. A column privilege is invisible to the table privilege view.
  FOR g IN
    SELECT a.table_name, a.column_name, a.privilege_type
    FROM information_schema.column_privileges a
    WHERE a.grantee = prod_app AND a.table_schema = 'public'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.column_privileges b
        WHERE b.grantee = staging_app AND b.table_schema = 'public'
          AND b.table_name = a.table_name AND b.column_name = a.column_name
          AND b.privilege_type = a.privilege_type)
  LOOP
    EXECUTE format('GRANT %s (%I) ON public.%I TO %I',
                   g.privilege_type, g.column_name, g.table_name, staging_app);
    n_col := n_col + 1;
  END LOOP;

  -- 3. RLS POLICY APPLICABILITY. The staging role is ADDED to the existing role list, never substituted for it,
  -- so production behaviour is untouched. polroles is read from pg_policy rather than pg_policies so the
  -- existing roles are rebuilt with quote_ident and a role name needing quoting cannot corrupt the statement.
  FOR pol IN
    SELECT p.polname, c.relname,
           (SELECT string_agg(quote_ident(r.rolname), ', ' ORDER BY r.rolname)
              FROM pg_roles r WHERE r.oid = ANY (p.polroles)) AS role_list
    FROM pg_policy p
    JOIN pg_class c ON c.oid = p.polrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = prod_app)
      AND NOT EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = staging_app)
  LOOP
    EXECUTE format('ALTER POLICY %I ON public.%I TO %s, %I',
                   pol.polname, pol.relname, pol.role_list, staging_app);
    n_pol := n_pol + 1;
  END LOOP;

  RAISE NOTICE 'staging role parity: % table grants, % column grants, % policies updated', n_tab, n_col, n_pol;

  -- THE ASSERTION, because a DO block that granted nothing and a DO block that had nothing to grant look
  -- identical in a migration log. After this runs, parity must be EXACT in both directions that matter.
  IF EXISTS (
    SELECT 1 FROM information_schema.column_privileges a
    WHERE a.grantee = prod_app AND a.table_schema = 'public'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.column_privileges b
        WHERE b.grantee = staging_app AND b.table_schema = 'public'
          AND b.table_name = a.table_name AND b.column_name = a.column_name
          AND b.privilege_type = a.privilege_type))
  THEN
    RAISE EXCEPTION 'staging role parity FAILED: column privileges still differ after mirroring';
  END IF;
  IF EXISTS (
    SELECT 1 FROM pg_policy p
    JOIN pg_class c ON c.oid = p.polrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = prod_app)
      AND NOT EXISTS (SELECT 1 FROM pg_roles r WHERE r.oid = ANY (p.polroles) AND r.rolname = staging_app))
  THEN
    RAISE EXCEPTION 'staging role parity FAILED: a policy naming % still does not name %', prod_app, staging_app;
  END IF;

  -- CONTAINMENT IS RE ASSERTED RATHER THAN TRUSTED. Nothing above touches a database privilege, and this
  -- proves it rather than claiming it. If a future edit ever reaches for role membership, this fails.
  IF has_database_privilege(staging_app, 'missionmatch', 'CONNECT') THEN
    RAISE EXCEPTION 'staging role parity FAILED: % gained CONNECT on the production database', staging_app;
  END IF;
END $$;
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
-- NOT REVERSED, DELIBERATELY, and this is a considered choice rather than laziness.
--
-- Removing the mirrored privileges would return the staging database to the state where its app role is
-- refused by RLS on four tables and cannot write the organizations columns the product's own time zone
-- endpoint updates. That is a broken environment, not a previous version of a schema. A down migration whose
-- effect is to reintroduce a Gate A failure is not a rollback, it is a regression with a version number.
--
-- Nothing here changes schema shape, so there is no structure to unwind: only privileges and policy role
-- lists were touched, both additively.
SELECT 1;
-- +goose StatementEnd