import { sql } from 'kysely'
import { nanoid } from 'nanoid'

import * as Runtime from '../../test/runtime.js'
import * as Db from './Db.js'
import * as Webhooks from '../internal/Webhooks.js'
import * as Invitations from './tables/invitations.js'
import * as Organizations from './tables/organizations.js'
import * as WebhookSubscriptions from './tables/webhookSubscriptions.js'

const create = () => Db.postgres({ connectionString: Runtime.postgresUrl, schema: `t_${nanoid()}` })

describe('postgres', () => {
  test('behavior: migrate is idempotent', async () => {
    const db = create()
    await db.migrate()
    await db.migrate()
    expect(await Organizations.get(db, 'missing')).toBeUndefined()
    await db.close()
  })

  test('behavior: legacy invitation inserts do not grant early access', async () => {
    const db = create()
    await db.migrate()
    await Organizations.create(db, { id: 'org_legacy', name: 'Legacy' })
    await sql`
      INSERT INTO invitations (
        id, org_id, email, role, invited_by, expires_at, accepted_at, revoked_at, created_at
      ) VALUES (
        'inv_legacy', 'org_legacy', 'legacy@example.org', 'member', 'usr_legacy',
        '2027-01-01T00:00:00.000Z', NULL, NULL, '2026-07-20T00:00:00.000Z'
      )
    `.execute(db.kysely)

    expect((await Invitations.get(db, 'inv_legacy'))?.grantsEarlyAccess).toBe(false)
    await db.close()
  })

  test('behavior: raw webhook staging respects schema scoping', async () => {
    // Exercises the one raw statement against a schema-scoped db whose only
    // qualification is WithSchemaPlugin, matching preview Workers: migrate on
    // one pool (whose session gains a search_path), then query from a fresh
    // pool that never ran it.
    const schema = `t_${nanoid()}`
    const migrator = Db.postgres({ connectionString: Runtime.postgresUrl, schema })
    await migrator.migrate()
    await migrator.close()
    const db = Db.postgres({ connectionString: Runtime.postgresUrl, schema })
    const subscription = await Webhooks.createSubscription(db, {
      chainId: 4217,
      destination: { type: 'url', url: 'https://hooks.example.com/endpoint' },
      eventType: 'token:transfer',
      owner: { orgId: 'org_1', type: 'api_key' },
    })
    const envelope = Webhooks.buildEnvelope({
      blockNumber: 123,
      createdAt: new Date(),
      data: { amount: '1' },
      logIndex: 4,
      subscription,
    })

    const staged = await Webhooks.ensureQueueEvents(db, [{ envelope, subscription }])
    expect(staged.created).toBe(1)
    const claim = await Webhooks.claimQueueEvent(db, staged.references[0]!)
    if (claim.type !== 'claimed') throw new Error(`Unexpected claim: ${claim.type}`)
    await Webhooks.completeQueueEvent(db, staged.references[0]!, {
      claimedAt: claim.record.attemptingAt!,
      status: 'succeeded',
    })

    // The completion read inside the same raw statement must also resolve
    // inside the schema for the replay to be suppressed.
    const replay = await Webhooks.ensureQueueEvents(db, [{ envelope, subscription }])
    expect(replay.created).toBe(0)
    await db.close()
  })

  test('behavior: creates a Postgres db without schema scoping', async () => {
    const db = Db.postgres({ connectionString: Runtime.postgresUrl })
    await db.close()
  })

  test('behavior: defers schema creation until migration', async () => {
    const schema = `t_${nanoid()}`
    const db = Db.postgres({ connectionString: Runtime.postgresUrl, schema })
    const exists = async () =>
      (
        await sql<{
          exists: boolean
        }>`SELECT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = ${schema}) AS exists`.execute(
          db.kysely,
        )
      ).rows[0]!.exists

    await sql`SELECT 1`.execute(db.kysely)
    expect(await exists()).toBe(false)
    await db.migrate()
    expect(await exists()).toBe(true)
    await db.close()
  })

  test('behavior: qualifies repository queries with the configured schema', async () => {
    const db = Db.postgres({
      connectionString: Runtime.postgresUrl,
      schema: `t_MixedCase_${nanoid()}`,
    })
    await sql`SET search_path TO public`.execute(db.kysely)
    await db.migrate()
    await sql`SET search_path TO public`.execute(db.kysely)
    await db.transaction(async (tx) => {
      await Organizations.create(tx, { id: 'org_qualified', name: 'Qualified' })
    })
    expect((await Organizations.get(db, 'org_qualified'))?.name).toBe('Qualified')
    expect(await WebhookSubscriptions.getCursor(db, 'wh_missing')).toBeNull()
    await db.close()
  })

  test('behavior: concurrent migrate tolerates the ledger race', async () => {
    const schema = `t_${nanoid()}`
    // Several runners migrate the same fresh schema at once (mirrors parallel
    // deploys). The session advisory lock serializes them; none throw.
    const dbs = Array.from({ length: 5 }, () =>
      Db.postgres({ connectionString: Runtime.postgresUrl, schema }),
    )
    await Promise.all(dbs.map((db) => db.migrate()))
    const created = await Organizations.create(dbs[0]!, { id: 'org_race', name: 'Race' })
    expect(created.id).toBe('org_race')
    await Promise.all(dbs.map((db) => db.close()))
  })

  test('error: rejects a modified applied migration', async () => {
    const db = create()
    await db.migrate()
    await db.kysely
      .updateTable('migrations')
      .set({ checksum: '00000000' })
      .where('name', '=', Db.migrations[0]!.name)
      .execute()
    await expect(db.migrate()).rejects.toThrow(/checksum mismatch/)
    await db.close()
  })

  test('behavior: transaction rolls back on throw', async () => {
    const db = create()
    await db.migrate()
    await expect(
      db.transaction(async (tx) => {
        await Organizations.create(tx, { id: 'org_2', name: 'Roll' })
        throw new Error('boom')
      }),
    ).rejects.toThrow('boom')
    expect(await Organizations.get(db, 'org_2')).toBeUndefined()
    await db.close()
  })

  test('error: transaction rejects nesting', async () => {
    const db = create()
    await db.migrate()
    await expect(db.transaction(() => db.transaction(async () => 1))).rejects.toThrow(/nesting/)
    await db.close()
  })

  test('behavior: transaction commits and returns the value', async () => {
    const db = create()
    await db.migrate()
    const id = await db.transaction(
      async (tx) => (await Organizations.create(tx, { id: 'org_3', name: 'Commit' })).id,
    )
    expect(id).toBe('org_3')
    expect((await Organizations.get(db, 'org_3'))?.name).toBe('Commit')
    await db.close()
  })
})