import { readFileSync } from 'node:fs'
import { resolve } from 'node:path'
import { describe, expect, it, vi } from 'vitest'
import { classifyCanonicalStatement, canonicalDefinitionMatches, reconcileLedger, splitCanonicalStatements, transformCanonicalStatementForExecution } from '../scripts/reconcile-production-migrations-core'

const migration = { tag: '0047_x', createdAt: 1, hash: 'abc' }

describe('production migration reconciliation', () => {
  it('aborts on mismatched preconditions before any ledger write', async () => {
    const insertLedger = vi.fn()
    const db = { check: vi.fn().mockResolvedValue(false), ledgerHas: vi.fn(), insertLedger }
    await expect(reconcileLedger(db, [migration], [{ id: 'shape', migration: migration.tag, sql: 'select false' }], true)).rejects.toThrow('failed closed')
    expect(db.ledgerHas).not.toHaveBeenCalled()
    expect(insertLedger).not.toHaveBeenCalled()
  })

  it('dry-run returns the guarded plan and performs no writes', async () => {
    const insertLedger = vi.fn()
    const db = { check: vi.fn().mockResolvedValue(true), ledgerHas: vi.fn().mockResolvedValue(false), insertLedger }
    await expect(reconcileLedger(db, [migration], [{ id: 'shape', migration: migration.tag, sql: 'select true' }], false)).resolves.toEqual([{ ...migration, action: 'insert' }])
    expect(insertLedger).not.toHaveBeenCalled()
  })

  it('parses only supported canonical breakpoint statements', () => {
    const statements = splitCanonicalStatements('CREATE TABLE "x" ("id" uuid);--> statement-breakpoint\nALTER TABLE "x" ADD COLUMN "name" text;')
    expect(statements).toHaveLength(2)
    expect(classifyCanonicalStatement(statements[0]!)).toEqual({ kind: 'table', name: 'x' })
    expect(classifyCanonicalStatement(statements[1]!)).toEqual({ kind: 'column', table: 'x', name: 'name' })
    expect(classifyCanonicalStatement('ALTER TABLE "x" DROP CONSTRAINT "old_check"')).toEqual({ kind: 'constraint-drop', table: 'x', name: 'old_check' })
    expect(classifyCanonicalStatement('ALTER TABLE "x" ALTER COLUMN "amount" SET NOT NULL')).toEqual({ kind: 'not-null', table: 'x', name: 'amount' })
    expect(classifyCanonicalStatement('ALTER TABLE "x" DROP COLUMN "legacy"')).toEqual({ kind: 'column-drop', table: 'x', name: 'legacy' })
    expect(classifyCanonicalStatement('INSERT INTO "x" ("id") VALUES (1)')).toEqual({ kind: 'seed', table: 'x', name: 'x-seed' })
    expect(() => classifyCanonicalStatement('DROP TABLE x')).toThrow('Unsupported canonical statement')
  })

  it('transforms the stock location seed to insert only tenants without MAIN', () => {
    const canonical = `INSERT INTO "stock_locations" ("tenant_id", "name", "code", "is_default", "is_active")
SELECT "id", 'Main', 'MAIN', true, true
FROM "tenants";`

    expect(transformCanonicalStatementForExecution(canonical)).toBe(`INSERT INTO "stock_locations" ("tenant_id", "name", "code", "is_default", "is_active")
SELECT "id", 'Main', 'MAIN', true, true
FROM "tenants"
WHERE NOT EXISTS (
  SELECT 1 FROM "stock_locations" existing
  WHERE existing."tenant_id" = "tenants"."id" AND existing."code" = 'MAIN'
);`)
    const transformed = transformCanonicalStatementForExecution(canonical)
    expect(transformed).not.toBe(canonical)
    expect(transformCanonicalStatementForExecution(transformed)).toBe(transformed)
  })

  it('does not transform non-stock seed statements', () => {
    const canonical = 'INSERT INTO "oauth_clients" ("id") VALUES (gen_random_uuid());'
    expect(transformCanonicalStatementForExecution(canonical)).toBe(canonical)
  })

  it('rejects a same-named index with a different definition', () => {
    expect(canonicalDefinitionMatches(
      'CREATE UNIQUE INDEX "idx_products_name_tenant" ON "products" USING btree ("tenant_id","name")',
      'CREATE UNIQUE INDEX idx_products_name_tenant ON public.products USING btree (tenant_id, code)',
    )).toBe(false)
  })

  it('rejects a same-named constraint with a different definition', () => {
    expect(canonicalDefinitionMatches(
      'ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_status_check" CHECK ("calendar_connections"."status" IN (\'active\',\'error\',\'disconnected\'))',
      'CHECK ((status IN (\'active\', \'error\')))',
    )).toBe(false)
  })

  it('accepts PostgreSQL-rendered definitions for canonical objects', () => {
    expect(canonicalDefinitionMatches(
      'CREATE UNIQUE INDEX "idx_products_name_tenant" ON "products" USING btree ("tenant_id","name")',
      'CREATE UNIQUE INDEX idx_products_name_tenant ON public.products USING btree (tenant_id, name)',
    )).toBe(true)
    expect(canonicalDefinitionMatches(
      'ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_status_check" CHECK ("calendar_connections"."status" IN (\'active\',\'error\',\'disconnected\'))',
      'CHECK ((status IN (\'active\', \'error\', \'disconnected\')))',
    )).toBe(true)
    expect(canonicalDefinitionMatches(
      'ALTER TABLE "orders" ADD CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers" ("id")',
      'FOREIGN KEY (customer_id) REFERENCES public.customers(id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION',
    )).toBe(true)
  })

  it('matches partial indexes when PostgreSQL omits the default btree method', () => {
    expect(canonicalDefinitionMatches(
      'CREATE INDEX "idx_orders_open" ON "orders" USING btree ("tenant_id") WHERE ("status" = \'open\')',
      'CREATE INDEX idx_orders_open ON public.orders (tenant_id) WHERE ((status = \'open\'))',
    )).toBe(true)
  })

  it('rejects semantically different partial indexes', () => {
    expect(canonicalDefinitionMatches(
      'CREATE INDEX "idx_orders_open" ON "orders" USING btree ("tenant_id") WHERE ("status" = \'open\')',
      'CREATE INDEX idx_orders_open ON public.orders USING hash (tenant_id) WHERE (status = \'open\')',
    )).toBe(false)
    expect(canonicalDefinitionMatches(
      'CREATE INDEX "idx_orders_open" ON "orders" USING btree ("tenant_id") WHERE ("status" = \'open\')',
      'CREATE INDEX idx_orders_open ON public.orders (tenant_id) WHERE (status = \'closed\')',
    )).toBe(false)
  })

  it('keeps the platform audit migration valid for PostgreSQL', () => {
    const sql = readFileSync(resolve(import.meta.dirname, '../migrations/0065_platform_audit.sql'), 'utf8')
    expect(sql).toContain('ALTER COLUMN "actor_type" DROP DEFAULT')
    expect(sql).not.toContain('DROP DEFAULT FOR')
    expect(sql).toContain('ADD COLUMN IF NOT EXISTS "actor_label"')
    expect(sql).toContain('CREATE INDEX IF NOT EXISTS "idx_audit_log_tenant_created_idx"')
    expect(sql).toContain('DISABLE TRIGGER "audit_log_no_update"')
    expect(sql).toContain('ENABLE TRIGGER "audit_log_no_update"')
  })
})