import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import postgres from 'postgres';

const TEST_URL = process.env.TEST_DATABASE_URL;
const skipReason = TEST_URL
  ? null
  : 'Set TEST_DATABASE_URL to a Postgres URL to run RLS tests.';

(skipReason ? describe.skip : describe)('RLS isolation', () => {
  if (skipReason) it.skip(skipReason, () => {});

  let client: postgres.Sql;
  let orgA: string;
  let orgB: string;

  beforeAll(async () => {
    // Assumes migrations have already been run (locally: `pnpm db:migrate`;
    // in CI: the workflow's migrate step). The munin_app role must exist.
    // Connect as the non-superuser app role so RLS policies actually apply —
    // Postgres superusers always bypass RLS regardless of FORCE.
    const appUrl = TEST_URL!.replace(/(postgres(?:ql)?:\/\/)[^:@]+:[^@]+@/, '$1munin_app:munin_app@');
    client = postgres(appUrl, { max: 2 });

    // Create two orgs in service-role mode (bypass via session-local GUC).
    await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'on', true)`;
      const ts = Date.now();
      const a = await sql`
        INSERT INTO orgs (id, name)
        VALUES ('org_test_a' || ${ts}, 'Org A')
        RETURNING id`;
      const b = await sql`
        INSERT INTO orgs (id, name)
        VALUES ('org_test_b' || ${ts}, 'Org B')
        RETURNING id`;
      orgA = a[0]!.id as string;
      orgB = b[0]!.id as string;

      await sql`
        INSERT INTO end_users (id, org_id, external_id, name, metadata)
        VALUES ('eu_a_' || ${ts}, ${orgA}, 'a-eu-1', 'A user', '{}'),
               ('eu_b_' || ${ts}, ${orgB}, 'b-eu-1', 'B user', '{}')`;
    });
  });

  afterAll(async () => {
    if (!client) return;
    // Clean up
    await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'on', true)`;
      await sql`DELETE FROM end_users WHERE org_id IN (${orgA}, ${orgB})`;
      await sql`DELETE FROM orgs WHERE id IN (${orgA}, ${orgB})`;
    });
    await client.end();
  });

  it('Org A scope sees only its own row', async () => {
    const rows = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'off', true)`;
      await sql`SELECT set_config('app.org_id', ${orgA}, true)`;
      return sql<{ name: string }[]>`SELECT name FROM end_users WHERE name IN ('A user', 'B user')`;
    });
    expect(rows.map((r) => r.name).sort()).toEqual(['A user']);
  });

  it('Org B scope sees only its own row', async () => {
    const rows = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'off', true)`;
      await sql`SELECT set_config('app.org_id', ${orgB}, true)`;
      return sql<{ name: string }[]>`SELECT name FROM end_users WHERE name IN ('A user', 'B user')`;
    });
    expect(rows.map((r) => r.name).sort()).toEqual(['B user']);
  });

  it('Org A cannot insert a row stamped for Org B', async () => {
    let threw = false;
    try {
      await client.begin(async (sql) => {
        await sql`SELECT set_config('app.bypass_rls', 'off', true)`;
        await sql`SELECT set_config('app.org_id', ${orgA}, true)`;
        await sql`
          INSERT INTO end_users (id, org_id, external_id, metadata)
          VALUES ('eu_spoof_' || ${Date.now()}::text, ${orgB}, 'spoof', '{}')`;
      });
    } catch (err) {
      threw = true;
      expect(String(err)).toMatch(/row.level security|violates row-level security/i);
    }
    expect(threw).toBe(true);
  });

  it('bypass mode sees rows for all orgs', async () => {
    const rows = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'on', true)`;
      return sql<{ name: string }[]>`SELECT name FROM end_users WHERE name IN ('A user', 'B user')`;
    });
    expect(rows.map((r) => r.name).sort()).toEqual(['A user', 'B user']);
  });

  it('delegated end-user scope further constrains visibility to that user', async () => {
    // Find Org A's eu id.
    const aEu = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'on', true)`;
      const rows = await sql`SELECT id FROM end_users WHERE org_id = ${orgA} LIMIT 1`;
      return rows[0]!.id as string;
    });

    const rows = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'off', true)`;
      await sql`SELECT set_config('app.org_id', ${orgA}, true)`;
      await sql`SELECT set_config('app.end_user_id', ${aEu}, true)`;
      return sql`SELECT id FROM end_users`;
    });

    expect(rows.length).toBe(1);
    expect(rows[0]!.id).toBe(aEu);
  });

  // Meta-test: enforce the "every org-scoped table has RLS" invariant.
  // If you add a new table with an `org_id` column, you must also enable
  // row-level security on it and add a tenant_isolation policy (typically
  // in packages/db/src/sql/<module>.sql). The few exempt tables are listed
  // explicitly below — usually because tenancy is delegated to a parent
  // table's policy via FK or the table is a system-level catalog.
  it('every table with an org_id column has RLS enabled', async () => {
    const exempt = new Set<string>([]);
    const rows = await client.begin(async (sql) => {
      await sql`SELECT set_config('app.bypass_rls', 'on', true)`;
      return sql<{ table_name: string; relrowsecurity: boolean; relforcerowsecurity: boolean; policy_count: number }[]>`
        SELECT
          c.relname AS table_name,
          c.relrowsecurity,
          c.relforcerowsecurity,
          (SELECT count(*) FROM pg_policies p
            WHERE p.schemaname = 'public' AND p.tablename = c.relname)::int AS policy_count
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relkind = 'r'
          AND EXISTS (
            SELECT 1 FROM information_schema.columns col
            WHERE col.table_schema = 'public'
              AND col.table_name = c.relname
              AND col.column_name = 'org_id'
          )
        ORDER BY c.relname`;
    });

    const offenders = rows
      .filter((r) => !exempt.has(r.table_name))
      .filter((r) => !r.relrowsecurity || !r.relforcerowsecurity || r.policy_count === 0);

    if (offenders.length > 0) {
      const detail = offenders
        .map(
          (r) =>
            `  - ${r.table_name}: rls=${r.relrowsecurity} force=${r.relforcerowsecurity} policies=${r.policy_count}`,
        )
        .join('\n');
      throw new Error(
        `Tables with an org_id column are missing RLS:\n${detail}\n\nAdd a tenant_isolation policy to packages/db/src/sql/<module>.sql, or add the table to the exempt set with a comment justifying why.`,
      );
    }
  });
});
