import { PGlite } from '@electric-sql/pglite'
import { sql } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/pglite'
import { withTransactionIdentity, type TransactionalDatabase } from '@platform-modules/db'
import { pgTable, text, uuid } from 'drizzle-orm/pg-core'
import { describe, expect, it } from 'vitest'
import { ScopeViolationError } from './index.js'
import { runInTenant, tenantPolicyDDL } from './isolation-rls.js'

const itemsTable = pgTable('rls_items', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull(),
  val: text('val').notNull(),
})

const schema = { items: itemsTable }

const TENANT_A = '00000000-0000-4000-8000-000000000001'
const TENANT_B = '00000000-0000-4000-8000-000000000002'

async function createFixture() {
  const client = new PGlite()
  const db = withTransactionIdentity(drizzle(client, { schema })) as unknown as TransactionalDatabase<typeof schema>
  await client.exec(`
    CREATE TABLE rls_items (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      tenant_id uuid NOT NULL,
      val text NOT NULL
    );
  `)
  for (const stmt of tenantPolicyDDL('rls_items')) {
    await client.exec(stmt)
  }
  await client.exec(`
    CREATE ROLE app_user NOLOGIN;
    GRANT SELECT, INSERT, UPDATE, DELETE ON rls_items TO app_user;
  `)
  await db.insert(itemsTable).values([
    { tenantId: TENANT_A, val: 'a-1' },
    { tenantId: TENANT_B, val: 'b-1' },
  ])
  return { client, db }
}

// RLS enforces ONLY under a NON-SUPERUSER, non-BYPASSRLS role — pglite's default
// `postgres` is a superuser and bypasses RLS even with FORCE, which is why these
// tests use `SET LOCAL ROLE app_user`; production has the same hard precondition.
describe('isolation-rls', () => {
  it('unfiltered SELECT returns only the active tenant rows', async () => {
    const { db } = await createFixture()
    const rows = await runInTenant(
      db,
      TENANT_A,
      async (tx) => tx.select().from(itemsTable),
      { role: 'app_user' },
    )
    expect(rows.map((r) => r.val)).toEqual(['a-1'])
  })

  it('raw tx.execute(sql) is also tenant-filtered by RLS', async () => {
    const { db } = await createFixture()
    const result = await runInTenant(
      db,
      TENANT_A,
      async (tx) => tx.execute(sql`SELECT * FROM rls_items`),
      { role: 'app_user' },
    )
    const rows = result as unknown as Array<{ val: string }>
    expect(rows.map((r) => r.val)).toEqual(['a-1'])
  })

  it('unfiltered UPDATE touches only the active tenant rows', async () => {
    const { db } = await createFixture()
    await runInTenant(
      db,
      TENANT_A,
      async (tx) => tx.execute(sql`UPDATE rls_items SET val = 'a-updated'`),
      { role: 'app_user' },
    )
    const all = await db.select().from(itemsTable)
    expect(all.find((r) => r.tenantId === TENANT_A)?.val).toBe('a-updated')
    expect(all.find((r) => r.tenantId === TENANT_B)?.val).toBe('b-1')
  })

  it('unset tenant context yields zero rows (fail-closed)', async () => {
    const { db } = await createFixture()
    const rows = await runInTenant(
      db,
      '',
      async (tx) => tx.select().from(itemsTable),
      { role: 'app_user' },
    )
    expect(rows).toEqual([])
  })

  // Test #5 — the misdeployment keystone: called WITHOUT the non-privileged role
  // switch, i.e. as pglite's default `postgres` superuser (the realistic
  // misconfigured-connection case). RLS is silently bypassed under a privileged
  // role, so the guard must REFUSE outright (fail-loud) — proving the dangerous
  // default is rejected, not silently un-isolated. `fn` must never run.
  it('refuses a privileged (superuser/BYPASSRLS) role fail-loud before running fn', async () => {
    const { db } = await createFixture()
    let ran = false
    await expect(
      runInTenant(
        db,
        TENANT_A,
        async () => {
          ran = true
          return null
        },
        {},
      ),
    ).rejects.toThrow(ScopeViolationError)
    expect(ran).toBe(false)
  })
})

describe('tenantPolicyDDL', () => {
  it('emits enable, force, and policy statements with safe quoting', () => {
    const ddl = tenantPolicyDDL('rls_items')
    expect(ddl).toEqual([
      'ALTER TABLE "rls_items" ENABLE ROW LEVEL SECURITY',
      'ALTER TABLE "rls_items" FORCE ROW LEVEL SECURITY',
      `CREATE POLICY tenant_isolation ON "rls_items" USING ("tenant_id"::text = current_setting('app.current_tenant', true))`,
    ])
  })
})