import { PgBoss } from 'pg-boss'
import postgres from 'postgres'
import { afterAll, beforeAll, describe, expect, it } from 'vitest'
import { createDb, DB_ROLES, runMigrations, saveOAuthClient, searchProducts, resolveStore, writeAudit, type DbHandle } from '@znet/catalog-db'
import { createLogger } from '@znet/shared'
import { MagentoApi, MagentoClient } from '@znet/magento'
import { syncInventory, syncProductsFull, syncReference } from '../apps/sync/src/jobs.js'
import { fixtures, startMockMagento } from './mock-magento.js'

/**
 * Least-privilege roles: each service's role can do its job and nothing else.
 * Creates cluster-wide roles mcp_sync / mcp_api / mcp_server on the local Postgres if missing.
 */
const OWNER_URL = process.env.TEST_ROLES_DATABASE_URL ?? 'postgres://localhost/mcp_znet_roles_test'
const asRole = (role: string) => {
  const u = new URL(OWNER_URL)
  u.username = role
  u.password = 'test-only-password'
  return u.toString()
}

let admin: postgres.Sql
let magento: Awaited<ReturnType<typeof startMockMagento>>
const handles: DbHandle[] = []
const connect = (role: string) => {
  const h = createDb(asRole(role), { max: 2 })
  handles.push(h)
  return h.db
}
/** Drizzle wraps driver errors as "Failed query: …"; the Postgres reason is on `cause`. */
const denied = async (p: Promise<unknown>) => {
  const err = await p.then(() => null, (e: Error & { cause?: Error }) => e)
  expect(err, 'expected the query to be refused').not.toBeNull()
  expect(String(err!.cause?.message ?? err!.message)).toMatch(/permission denied/)
}

beforeAll(async () => {
  const u = new URL(OWNER_URL)
  const name = u.pathname.slice(1)
  u.pathname = '/postgres'
  admin = postgres(u.toString(), { max: 1, onnotice: () => {} })
  for (const role of Object.values(DB_ROLES)) {
    await admin.unsafe(`DO $$ BEGIN CREATE ROLE ${role} LOGIN PASSWORD 'test-only-password'; EXCEPTION WHEN duplicate_object THEN NULL; END $$`)
  }
  await admin.unsafe(`DROP DATABASE IF EXISTS "${name}" WITH (FORCE)`)
  await admin.unsafe(`CREATE DATABASE "${name}"`)
  magento = await startMockMagento(fixtures())
})

afterAll(async () => {
  await Promise.all(handles.map(h => h.close()))
  await magento?.close()
  await admin?.end()
})

describe('database roles', () => {
  it('migrations grant every service role', async () => {
    expect((await runMigrations(OWNER_URL)).sort()).toEqual(['mcp_api', 'mcp_server', 'mcp_sync'])
  })

  it('mcp_sync can run every sync job and start pg-boss, but cannot read the audit log or sessions', async () => {
    const db = connect(DB_ROLES.sync)
    const ctx = { db, magento: new MagentoApi(new MagentoClient({ baseUrl: magento.url, accessToken: magento.token })), log: createLogger('t', 'error'), storeCodes: [], stockId: 1 }
    await syncReference(ctx)
    await expect(syncProductsFull(ctx)).resolves.toMatchObject({ 'products:all': 5 })
    await expect(syncInventory(ctx)).resolves.toMatchObject({ sourceItems: 5 })

    const boss = new PgBoss({ connectionString: asRole(DB_ROLES.sync), schema: 'pgboss', createSchema: false })
    await boss.start()
    await boss.createQueue('roles-test')
    await boss.stop({ graceful: false })

    await denied(db.execute('select * from ops.audit_log' as never))
    await denied(db.execute('select * from ops.oauth_tokens' as never))
  })

  it('mcp_api can read the catalog but not write it, nor read the audit log or sessions', async () => {
    const db = connect(DB_ROLES.api)
    const res = await searchProducts(db, await resolveStore(db, 'default'), { q: 'mug', limit: 5, offset: 0 })
    expect(res.items[0]?.sku).toBe('MUG')

    await denied(db.execute(`update catalog.products set price = 0` as never))
    await denied(db.execute(`insert into ops.sync_state (stream) values ('x')` as never))
    await denied(db.execute('select * from ops.audit_log' as never))
    await denied(db.execute('select * from ops.oauth_tokens' as never))
  })

  it('mcp_server can write the audit log and sessions, but cannot read the catalog or the audit log back', async () => {
    const db = connect(DB_ROLES.server)
    await writeAudit(db, { subject: 'alice', tool: 'get_product', args: {}, outcome: 'ok', durationMs: 1 })
    await saveOAuthClient(db, 'client-1', { client_id: 'client-1', redirect_uris: ['https://chatgpt.com/cb'] })

    await denied(db.execute('select * from catalog.products' as never))
    await denied(db.execute('select * from ops.audit_log' as never))
    await denied(db.execute('delete from ops.audit_log' as never))
  })
})