-- ============================================================================
-- CLIENT MIGRATION TEMPLATE: Add organization_id to Application Schema Tables
-- ============================================================================
--
-- Maintainer note: this is the single source file (docs/hugo/static/downloads/).
-- Hugo copies it verbatim into the built public/downloads/ output; there is no
-- second source copy to keep in sync.
--
-- PURPOSE:
--   Migrate existing application-schema tables to multi-tenancy by adding
--   organization_id, RLS (ENABLE + FORCE), a baseline policy, and an index.
--
-- IMPORTANT NOTES:
--   1. CLIENT RESPONSIBILITY — CYPEX core does not run this automatically
--   2. Customize schema/table filters before running
--   3. Test in development/staging first; take a backup before production
--   4. Decide the default-organization assignment strategy before running
--   5. Large tables: prefer Option 2 with batched backfill + CREATE INDEX
--      CONCURRENTLY (outside a transaction block)
--
-- PREREQUISITES:
--   - The v2.0.0 multi-tenancy migration is applied: cypex.t_organization
--     exists, core cypex/cypex_log tables carry organization_id, and RLS is
--     enabled on them.
--   - Helpers exist: cypex.current_organization_id(), cypex.is_admin(),
--     cypex.is_organization_admin(), cypex.current_user_organization_ids()
--   - At least one organization exists (default organization recommended)
--
-- USAGE:
--   1. Copy this file into your migration directory
--   2. Customize schema/table filters and continue_on_error
--   3. Review default organization assignment
--   4. Rehearse on a staging replica
--   5. Apply to production after verification
--
-- ============================================================================

-- ============================================================================
-- OPTION 1: Automated Migration (All Application Schemas)
-- ============================================================================
-- WARNING: Review the schema filters carefully before running.

DO $$
DECLARE
  schema_rec RECORD;
  table_rec RECORD;
  default_org_id BIGINT;
  policy_name TEXT;
  index_name TEXT;
  fk_name TEXT;
  col_exists BOOLEAN;
  fk_exists BOOLEAN;
  null_count BIGINT;
  tables_processed INTEGER := 0;
  tables_failed INTEGER := 0;
  -- Set true only when you want a best-effort inventory run that logs
  -- failures and continues. Production must leave this false (fail loud).
  continue_on_error BOOLEAN := false;
BEGIN
  -- Step 1: Resolve default organization for backfill
  -- CUSTOMIZE: Change this logic if existing rows should land elsewhere
  SELECT id INTO default_org_id
    FROM cypex.t_organization
   WHERE organization_domain = 'default'
     AND is_active = true
   ORDER BY id ASC
   LIMIT 1;

  IF default_org_id IS NULL THEN
    SELECT id INTO default_org_id
      FROM cypex.t_organization
     WHERE is_active = true
     ORDER BY id ASC
     LIMIT 1;
  END IF;

  IF default_org_id IS NULL THEN
    RAISE EXCEPTION 'No organizations found. Create at least one organization first.';
  END IF;

  RAISE NOTICE 'Starting application schema migration (default_org_id: %, continue_on_error: %)...',
    default_org_id, continue_on_error;

  -- Step 2: Process each application schema
  -- CUSTOMIZE: Adjust schema filters to match your application schemas
  FOR schema_rec IN
    SELECT nspname
      FROM pg_namespace
     WHERE nspname NOT LIKE 'pg_%'
       AND nspname NOT LIKE 'cypex%'
       AND nspname NOT LIKE 'sso_gateway%'
       AND nspname <> 'information_schema'
       AND nspname <> 'public'
       -- ADD YOUR SCHEMA FILTERS HERE:
       -- AND nspname IN ('your_schema_1', 'your_schema_2')
       -- AND nspname LIKE 'app_%'
     ORDER BY nspname
  LOOP
    RAISE NOTICE 'Processing schema: %', schema_rec.nspname;

    -- Step 3: Ordinary / partitioned-parent tables only (skip partition children)
    FOR table_rec IN
      SELECT c.relname AS tablename
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
       WHERE n.nspname = schema_rec.nspname
         AND c.relkind IN ('r', 'p')   -- ordinary table or partitioned parent
         AND NOT c.relispartition     -- skip partition children
         AND c.relname NOT LIKE '\_%' ESCAPE '\'
         AND c.relname NOT LIKE 'pg\_%' ESCAPE '\'
         -- ADD YOUR TABLE FILTERS HERE:
         -- AND c.relname NOT IN ('excluded_table_1', 'excluded_table_2')
       ORDER BY c.relname
    LOOP
      BEGIN
        policy_name := schema_rec.nspname || '_' || table_rec.tablename || '_user_access';
        index_name := 'idx_' || schema_rec.nspname || '_' || table_rec.tablename || '_organization_id';
        fk_name := table_rec.tablename || '_organization_id_fkey';

        SELECT EXISTS (
          SELECT 1
            FROM information_schema.columns
           WHERE table_schema = schema_rec.nspname
             AND table_name = table_rec.tablename
             AND column_name = 'organization_id'
        ) INTO col_exists;

        -- Step 3.1: Add column, or finish a partial prior run
        IF NOT col_exists THEN
          EXECUTE format(
            'ALTER TABLE %I.%I
               ADD COLUMN organization_id BIGINT
               NOT NULL DEFAULT %L
               REFERENCES cypex.t_organization(id) ON DELETE RESTRICT',
            schema_rec.nspname, table_rec.tablename, default_org_id
          );
        ELSE
          EXECUTE format(
            'SELECT count(*) FROM %I.%I WHERE organization_id IS NULL',
            schema_rec.nspname, table_rec.tablename
          ) INTO null_count;

          IF null_count > 0 THEN
            EXECUTE format(
              'UPDATE %I.%I SET organization_id = %L WHERE organization_id IS NULL',
              schema_rec.nspname, table_rec.tablename, default_org_id
            );
            RAISE NOTICE '  Backfilled % NULL organization_id row(s) on %.%',
              null_count, schema_rec.nspname, table_rec.tablename;
          END IF;

          EXECUTE format(
            'ALTER TABLE %I.%I ALTER COLUMN organization_id SET NOT NULL',
            schema_rec.nspname, table_rec.tablename
          );

          SELECT EXISTS (
            SELECT 1
              FROM pg_constraint con
              JOIN pg_class rel ON rel.oid = con.conrelid
              JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
             WHERE nsp.nspname = schema_rec.nspname
               AND rel.relname = table_rec.tablename
               AND con.contype = 'f'
               AND pg_get_constraintdef(con.oid) LIKE '%organization_id%cypex.t_organization%'
          ) INTO fk_exists;

          IF NOT fk_exists THEN
            EXECUTE format(
              'ALTER TABLE %I.%I
                 ADD CONSTRAINT %I
                 FOREIGN KEY (organization_id)
                 REFERENCES cypex.t_organization(id) ON DELETE RESTRICT',
              schema_rec.nspname, table_rec.tablename, fk_name
            );
          END IF;
        END IF;

        -- Step 3.2: Replace migration backfill DEFAULT with session-aware DEFAULT
        -- Literal DEFAULT left in place would stamp every omitted INSERT into
        -- the migration default org — not the caller's current organization.
        EXECUTE format(
          'ALTER TABLE %I.%I
             ALTER COLUMN organization_id SET DEFAULT cypex.current_organization_id()',
          schema_rec.nspname, table_rec.tablename
        );

        -- OPTIONAL: stamp/reject via trigger (function ships with the v2.0.0
        -- multi-tenancy migration; not attached to any table by core).
        -- Uncomment if the application does not always supply organization_id:
        -- EXECUTE format(
        --   'DROP TRIGGER IF EXISTS trg_validate_organization ON %I.%I',
        --   schema_rec.nspname, table_rec.tablename);
        -- EXECUTE format(
        --   'CREATE TRIGGER trg_validate_organization
        --      BEFORE INSERT OR UPDATE OF organization_id ON %I.%I
        --      FOR EACH ROW EXECUTE FUNCTION cypex.validate_root_table_organization()',
        --   schema_rec.nspname, table_rec.tablename);

        -- Step 3.3: Enable RLS (owners included via FORCE)
        EXECUTE format(
          'ALTER TABLE %I.%I ENABLE ROW LEVEL SECURITY',
          schema_rec.nspname, table_rec.tablename
        );
        EXECUTE format(
          'ALTER TABLE %I.%I FORCE ROW LEVEL SECURITY',
          schema_rec.nspname, table_rec.tablename
        );

        -- Step 3.4: Baseline policy (USING + WITH CHECK)
        -- Active-org isolation for members; system-admin bypass via cypex.is_admin().
        -- CUSTOMIZE: to also admit org-admins across their mapped orgs, extend both
        -- USING and WITH CHECK with:
        --   OR (cypex.is_organization_admin()
        --       AND organization_id = ANY (cypex.current_user_organization_ids()))
        EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
          policy_name, schema_rec.nspname, table_rec.tablename);

        EXECUTE format(
          'CREATE POLICY %I ON %I.%I
             FOR ALL
             USING (
               organization_id = cypex.current_organization_id()
               OR cypex.is_admin()
             )
             WITH CHECK (
               organization_id = cypex.current_organization_id()
               OR cypex.is_admin()
             )',
          policy_name, schema_rec.nspname, table_rec.tablename
        );

        -- Step 3.5: Index for RLS / tenant filters
        -- Prefer adding (organization_id, <natural_key>) composites for hot queries.
        EXECUTE format(
          'CREATE INDEX IF NOT EXISTS %I ON %I.%I (organization_id)',
          index_name, schema_rec.nspname, table_rec.tablename
        );

        EXECUTE format('ANALYZE %I.%I', schema_rec.nspname, table_rec.tablename);

        tables_processed := tables_processed + 1;
        RAISE NOTICE '  ✓ Migrated %.%', schema_rec.nspname, table_rec.tablename;
      EXCEPTION WHEN OTHERS THEN
        tables_failed := tables_failed + 1;
        RAISE WARNING '  ✗ Failed %.%: %',
          schema_rec.nspname, table_rec.tablename, SQLERRM;
        IF NOT continue_on_error THEN
          RAISE EXCEPTION
            'Migration aborted on %.% (set continue_on_error := true only for inventory runs): %',
            schema_rec.nspname, table_rec.tablename, SQLERRM;
        END IF;
      END;
    END LOOP;
  END LOOP;

  RAISE NOTICE 'Application schema migration completed: % tables processed, % failed',
    tables_processed, tables_failed;

  -- With continue_on_error := false we already re-raised on the first failure.
  -- With continue_on_error := true, warn and allow the DO block to commit the
  -- tables that succeeded so a re-run can finish the rest.
  IF tables_failed > 0 THEN
    RAISE WARNING
      'Migration finished with % failed table(s). Fix errors and re-run (idempotent for completed tables).',
      tables_failed;
  END IF;
END;
$$;

-- ============================================================================
-- OPTION 2: Manual Migration (Specific Tables)
-- ============================================================================
-- Safer for large tables: nullable add → batched backfill → index
-- CONCURRENTLY → SET NOT NULL → FK → swap DEFAULT → RLS last.
-- Uncomment and customize.

-- DO $$
-- DECLARE
--   default_org_id BIGINT;
-- BEGIN
--   SELECT id INTO default_org_id
--     FROM cypex.t_organization
--    WHERE organization_domain = 'default'
--      AND is_active = true
--    ORDER BY id ASC
--    LIMIT 1;
--
--   IF default_org_id IS NULL THEN
--     RAISE EXCEPTION 'No default organization found';
--   END IF;
--
--   -- 1) Add nullable column (no rewrite of existing rows yet on modern PG)
--   ALTER TABLE your_schema.your_table
--     ADD COLUMN IF NOT EXISTS organization_id BIGINT;
--
--   -- 2) Batched backfill (repeat with key ranges as needed)
--   UPDATE your_schema.your_table
--      SET organization_id = default_org_id
--    WHERE organization_id IS NULL
--      AND id BETWEEN 1 AND 100000;
--
--   -- 3) Index (run CREATE INDEX CONCURRENTLY outside a transaction block)
--   -- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_your_schema_your_table_organization_id
--   --   ON your_schema.your_table (organization_id);
--
--   -- 4) Enforce NOT NULL + FK
--   ALTER TABLE your_schema.your_table
--     ALTER COLUMN organization_id SET NOT NULL;
--   ALTER TABLE your_schema.your_table
--     ADD CONSTRAINT your_table_organization_id_fkey
--     FOREIGN KEY (organization_id)
--     REFERENCES cypex.t_organization(id) ON DELETE RESTRICT;
--
--   -- 5) Session-aware DEFAULT for future inserts that omit the column
--   ALTER TABLE your_schema.your_table
--     ALTER COLUMN organization_id SET DEFAULT cypex.current_organization_id();
--
--   -- 6) RLS last (after backfill) so maintenance queries are not filtered early
--   ALTER TABLE your_schema.your_table ENABLE ROW LEVEL SECURITY;
--   ALTER TABLE your_schema.your_table FORCE ROW LEVEL SECURITY;
--
--   DROP POLICY IF EXISTS your_schema_your_table_user_access ON your_schema.your_table;
--   CREATE POLICY your_schema_your_table_user_access ON your_schema.your_table
--     FOR ALL
--     USING (
--       organization_id = cypex.current_organization_id()
--       OR cypex.is_admin()
--     )
--     WITH CHECK (
--       organization_id = cypex.current_organization_id()
--       OR cypex.is_admin()
--     );
--
--   ANALYZE your_schema.your_table;
-- END;
-- $$;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- 1. Application schemas and table counts:
-- SELECT
--   n.nspname AS schema_name,
--   count(c.oid) AS table_count
-- FROM pg_namespace n
-- LEFT JOIN pg_class c
--   ON c.relnamespace = n.oid
--  AND c.relkind IN ('r', 'p')
--  AND NOT c.relispartition
-- WHERE n.nspname NOT LIKE 'pg_%'
--   AND n.nspname NOT LIKE 'cypex%'
--   AND n.nspname NOT LIKE 'sso_gateway%'
--   AND n.nspname <> 'information_schema'
--   AND n.nspname <> 'public'
-- GROUP BY n.nspname
-- ORDER BY n.nspname;

-- 2. Column / RLS / FORCE / policy WITH CHECK coverage:
-- SELECT
--   n.nspname AS schemaname,
--   c.relname AS tablename,
--   EXISTS (
--     SELECT 1 FROM information_schema.columns col
--      WHERE col.table_schema = n.nspname
--        AND col.table_name = c.relname
--        AND col.column_name = 'organization_id'
--   ) AS has_org_id,
--   c.relrowsecurity AS rls_enabled,
--   c.relforcerowsecurity AS rls_forced,
--   (
--     SELECT p.with_check IS NOT NULL
--       FROM pg_policies p
--      WHERE p.schemaname = n.nspname
--        AND p.tablename = c.relname
--        AND p.policyname = n.nspname || '_' || c.relname || '_user_access'
--      LIMIT 1
--   ) AS policy_has_with_check
-- FROM pg_class c
-- JOIN pg_namespace n ON n.oid = c.relnamespace
-- WHERE n.nspname NOT LIKE 'pg_%'
--   AND n.nspname NOT LIKE 'cypex%'
--   AND n.nspname NOT LIKE 'sso_gateway%'
--   AND n.nspname <> 'information_schema'
--   AND n.nspname <> 'public'
--   AND c.relkind IN ('r', 'p')
--   AND NOT c.relispartition
-- ORDER BY n.nspname, c.relname;

-- 3. No NULL organization_id left:
-- SELECT count(*) FROM <application_schema>.<table> WHERE organization_id IS NULL;
-- -- expect 0

-- 4. Test RLS as a regular user (JWT claims — not app.current_organization):
-- SET LOCAL "request.jwt.claims" = '{
--   "org_id": "1",
--   "organization_ids": ["1"],
--   "role": "cypex_user",
--   "isSuperAdmin": false,
--   "isOrganizationAdmin": false
-- }';
-- SET ROLE cypex_user;
-- SELECT * FROM <application_schema>.<table> LIMIT 5;
-- RESET ROLE;
-- RESET "request.jwt.claims";

-- 5. Test RLS as system admin (isSuperAdmin drives cypex.is_admin()):
-- SET LOCAL "request.jwt.claims" = '{
--   "org_id": "0",
--   "organization_ids": [],
--   "role": "cypex_admin",
--   "isSuperAdmin": true,
--   "isOrganizationAdmin": false
-- }';
-- SET ROLE cypex_admin;
-- SELECT * FROM <application_schema>.<table> LIMIT 5;
-- RESET ROLE;
-- RESET "request.jwt.claims";

-- ============================================================================
-- ROLLBACK PROCEDURE
-- ============================================================================
-- WARNING: Removes organization isolation from application-schema tables.
-- Only safe with no production tenant data, or when deliberately reverting.
-- Uncomment to run.

-- DO $$
-- DECLARE
--   schema_rec RECORD;
--   table_rec RECORD;
--   policy_name TEXT;
--   index_name TEXT;
--   tables_rolled_back INTEGER := 0;
-- BEGIN
--   RAISE NOTICE 'Starting rollback of application schema migration...';
--
--   FOR schema_rec IN
--     SELECT nspname
--       FROM pg_namespace
--      WHERE nspname NOT LIKE 'pg_%'
--        AND nspname NOT LIKE 'cypex%'
--        AND nspname NOT LIKE 'sso_gateway%'
--        AND nspname <> 'information_schema'
--        AND nspname <> 'public'
--   LOOP
--     FOR table_rec IN
--       SELECT c.relname AS tablename
--         FROM pg_class c
--         JOIN pg_namespace n ON n.oid = c.relnamespace
--        WHERE n.nspname = schema_rec.nspname
--          AND c.relkind IN ('r', 'p')
--          AND NOT c.relispartition
--     LOOP
--       BEGIN
--         policy_name := schema_rec.nspname || '_' || table_rec.tablename || '_user_access';
--         index_name := 'idx_' || schema_rec.nspname || '_' || table_rec.tablename || '_organization_id';
--
--         EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
--           policy_name, schema_rec.nspname, table_rec.tablename);
--         EXECUTE format('ALTER TABLE %I.%I DISABLE ROW LEVEL SECURITY',
--           schema_rec.nspname, table_rec.tablename);
--         EXECUTE format('DROP INDEX IF EXISTS %I.%I',
--           schema_rec.nspname, index_name);
--         EXECUTE format('ALTER TABLE %I.%I DROP COLUMN IF EXISTS organization_id CASCADE',
--           schema_rec.nspname, table_rec.tablename);
--
--         tables_rolled_back := tables_rolled_back + 1;
--         RAISE NOTICE 'Rolled back %.%', schema_rec.nspname, table_rec.tablename;
--       EXCEPTION WHEN OTHERS THEN
--         RAISE WARNING 'Failed to rollback %.%: %',
--           schema_rec.nspname, table_rec.tablename, SQLERRM;
--       END;
--     END LOOP;
--   END LOOP;
--
--   RAISE NOTICE 'Rollback completed: % tables rolled back', tables_rolled_back;
-- END;
-- $$;
