import { createHash } from 'node:crypto'
import { cp, mkdtemp, mkdir, readFile, rm, writeFile } from 'node:fs/promises'
import { tmpdir } from 'node:os'
import { join, resolve } from 'node:path'
import postgres, { type Sql } from 'postgres'
import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import { canonicalDefinitionMatches, classifyCanonicalStatement, reconcileLedger, splitCanonicalStatements, transformCanonicalStatementForExecution, type CanonicalMigration, type ReconciliationCheck, type ReconciliationDb } from './reconcile-production-migrations-core'

const ROOT = resolve(import.meta.dirname, '..')
const MIGRATIONS = join(ROOT, 'migrations')
const EXPECTED: readonly CanonicalMigration[] = [
  ['0047_onboarding_step',1781340000000,'cf321b41789cfdd8791f681f40bb5b55cd5e8344de02682d387d6488f306d406'],
  ['0048_contractor_portal_sessions',1781420000000,'8f9c98be388f223f8c7e9b6c8f28dc2cb3f8a335c2dd5ea1b92f4a0247bebd97'],
  ['0049_contractor_approval_default_true',1781500000000,'fddcd74f43967970c4b1a658436c52f8acc27b4a439564662a3524d103bd7151'],
  ['0050_tenant_portals',1781141257394,'096b180560fc77d105f11e358c37787f4fcada8785baf0ba985334bab904daea'],
  ['0051_crm_support_center_auto_close',1781580000000,'629ce7ab4c206b934f9729ec97e255748b1cfab577a813cd2d7f8b76bd473894'],
  ['0052_reports_analytics_dashboards',1781660000000,'34757e82c968d039b9ec541ba0fb49d6a6f94a066fd442e374c52dea1474e9cc'],
  ['0056_advance_payments_ytd',1781740000000,'f506c583c8a68c6fc078816908f3bad80ff417d9f6eafcf79c0f16f1d7ec7db1'],
  ['0057_invoices_dedup_key',1781800000000,'5f12e756ee31d06b8d9203f111bfaec666f71be348ce50278f37c90e0ea951f2'],
  ['0058_profile_phone_timezone',1781247815892,'7916a359ac282fecac9e65a8a2222acf8b665d8e40b6f93edf31a53a1e17d566'],
  ['0060_kb_publish_permission',1782000000000,'425bc4ac9a6de086b12733cc78857d747130c4ea103fb0512ae0f97b3829e7fe'],
  ['0061_recurring_invoice_constraints',1782086400000,'2fad721dc7cbb873afff2def59f635f8ef5e6c1a28bb647104b813a95322e835'],
  ['0062_invoices_recurring_period_start',1782172800000,'1f6c4b168c241695ede3009d4e8b77debccf1bd4bc0d5fff4a4a19499b927fd6'],
].map(([tag, createdAt, hash]) => ({ tag: String(tag), createdAt: Number(createdAt), hash: String(hash) }))

const CHECKS: readonly ReconciliationCheck[] = [
  ['0047_onboarding_step','column',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenants' AND column_name='onboarding_step' AND is_nullable='NO' AND column_default='0')`],
  ['0048_contractor_portal_sessions','table-index',`SELECT to_regclass('public.contractor_portal_sessions') IS NOT NULL AND to_regclass('public.idx_cps_token') IS NOT NULL`],
  ['0049_contractor_approval_default_true','default',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenant_settings' AND column_name='contractor_require_time_approval' AND column_default='true')`],
  ['0050_tenant_portals','objects',`SELECT to_regclass('public.portal_sessions') IS NOT NULL AND to_regclass('public.idx_portal_sessions_token') IS NOT NULL AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenant_settings' AND column_name='portal_max_session_hours' AND column_default='24')`],
  ['0051_crm_support_center_auto_close','column',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenant_settings' AND column_name='ticket_auto_close_days' AND column_default='7')`],
  ['0052_reports_analytics_dashboards','objects',`SELECT to_regclass('public.dashboards') IS NOT NULL AND to_regclass('public.dashboard_widgets') IS NOT NULL AND to_regclass('public.idx_dashboards_tenant_user_default_uniq') IS NOT NULL`],
  ['0056_advance_payments_ytd','column',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenant_settings' AND column_name='advance_payments_ytd_ils' AND column_default='0')`],
  ['0057_invoices_dedup_key','column-index',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='invoices' AND column_name='dedup_key') AND to_regclass('public.invoices_tenant_dedup_key_uniq') IS NOT NULL`],
  ['0058_profile_phone_timezone','columns',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='users' AND column_name='phone') AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='user_preferences' AND column_name='timezone' AND is_nullable='NO')`],
  ['0060_kb_publish_permission','grant',`SELECT EXISTS (SELECT 1 FROM permissions p JOIN role_permissions rp ON rp.permission_id=p.id JOIN roles r ON r.id=rp.role_id WHERE p.key='kb:publish' AND r.is_system_role AND r.name='OWNER') AND EXISTS (SELECT 1 FROM permissions p JOIN role_permissions rp ON rp.permission_id=p.id JOIN roles r ON r.id=rp.role_id WHERE p.key='kb:publish' AND r.is_system_role AND r.name='ADMIN')`],
  ['0061_recurring_invoice_constraints','objects',`SELECT to_regclass('public.idx_rit_tenant_status') IS NOT NULL AND EXISTS (SELECT 1 FROM pg_constraint WHERE conname='rit_currency_check' AND conrelid='public.recurring_invoice_templates'::regclass)`],
  ['0062_invoices_recurring_period_start','column-index',`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='invoices' AND column_name='recurring_period_start') AND to_regclass('public.invoices_recurring_template_period_uniq') IS NOT NULL`],
].map(([migration,id,sql]) => ({ migration, id, sql }))

function sha256(value: string | Buffer): string { return createHash('sha256').update(value).digest('hex') }
function normalizedType(value: string): string { return value.toLowerCase().replace(/\s+/g, ' ').replace(/,\s*/g, ',').trim() }
function normalizedDefault(value: unknown): string | null {
  if (value === null || value === undefined) return null
  let normalized = String(value).replace(/::[a-z_ ]+(?:\[\])?/gi, '').replace(/^\((.*)\)$/s, '$1').replace(/\s+/g, ' ').trim()
  if (normalized.startsWith("'") && normalized.endsWith("'")) normalized = normalized.slice(1, -1).replace(/''/g, "'")
  if (normalized.toUpperCase() === 'NULL') return null
  if (/^-?\d+(?:\.\d+)?$/.test(normalized)) return String(Number(normalized))
  if (normalized.startsWith('{') || normalized.startsWith('[')) {
    try {
      const sortJson = (input: unknown): unknown => Array.isArray(input)
        ? input.map(sortJson)
        : input && typeof input === 'object'
          ? Object.fromEntries(Object.entries(input).sort(([a], [b]) => a.localeCompare(b)).map(([key, item]) => [key, sortJson(item)]))
          : input
      return JSON.stringify(sortJson(JSON.parse(normalized)))
    } catch { /* retain the normalized SQL expression */ }
  }
  return normalized
}

async function validateCanonicalFiles(): Promise<void> {
  const journal = JSON.parse(await readFile(join(MIGRATIONS, 'meta/_journal.json'), 'utf8')) as { entries: Array<{ tag: string; when: number }> }
  for (const expected of EXPECTED) {
    const entry = journal.entries.find((item) => item.tag === expected.tag)
    if (!entry || entry.when !== expected.createdAt) throw new Error(`Canonical journal mismatch: ${expected.tag}`)
    if (sha256(await readFile(join(MIGRATIONS, `${expected.tag}.sql`))) !== expected.hash) throw new Error(`Canonical SQL hash mismatch: ${expected.tag}`)
  }
}

function adapter(sql: Sql): ReconciliationDb {
  return {
    async check(query) { const rows = await sql.unsafe<{ ok: boolean }[]>(`SELECT (${query.replace(/^SELECT\s+/i, '')}) AS ok`); return rows[0]?.ok === true },
    async ledgerHas(hash, createdAt) { const rows = await sql`SELECT EXISTS (SELECT 1 FROM drizzle.__drizzle_migrations WHERE hash=${hash} AND created_at=${createdAt}) AS ok`; return rows[0]?.ok === true },
    async insertLedger(hash, createdAt) { await sql`INSERT INTO drizzle.__drizzle_migrations (hash, created_at) SELECT ${hash}, ${createdAt} WHERE NOT EXISTS (SELECT 1 FROM drizzle.__drizzle_migrations WHERE hash=${hash} AND created_at=${createdAt})` },
  }
}

async function exists(sql: Sql, statement: string): Promise<boolean> {
  const object = classifyCanonicalStatement(statement)
  if (object.kind === 'table') return (await sql`SELECT to_regclass(${`public.${object.name}`}) IS NOT NULL AS ok`)[0]?.ok === true
  if (object.kind === 'index') {
    const rows = await sql`SELECT indexdef AS definition FROM pg_indexes WHERE schemaname='public' AND indexname=${object.name}`
    if (!rows[0]?.definition) return false
    if (!canonicalDefinitionMatches(statement, String(rows[0].definition))) throw new Error(`Canonical index mismatch: ${object.name}`)
    return true
  }
  if (object.kind === 'column') return (await sql`SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name=${object.table!} AND column_name=${object.name}) AS ok`)[0]?.ok === true
  if (object.kind === 'column-drop') return (await sql`SELECT NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name=${object.table!} AND column_name=${object.name}) AS ok`)[0]?.ok === true
  if (object.kind === 'not-null') return (await sql`SELECT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid=to_regclass(${`public.${object.table}`}) AND attname=${object.name} AND attnotnull AND NOT attisdropped) AS ok`)[0]?.ok === true
  if (object.kind === 'constraint-drop') return (await sql`SELECT to_regclass(${`public.${object.table}`}) IS NULL OR NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname=${object.name} AND conrelid=to_regclass(${`public.${object.table}`})) AS ok`)[0]?.ok === true
  if (object.kind === 'seed' && object.table === 'stock_locations') {
    if ((await sql`SELECT to_regclass('public.stock_locations') IS NULL AS missing`)[0]?.missing === true) return false
    return (await sql`SELECT NOT EXISTS (SELECT 1 FROM tenants t WHERE NOT EXISTS (SELECT 1 FROM stock_locations s WHERE s.tenant_id=t.id AND s.code='MAIN' AND s.is_default AND s.is_active)) AS ok`)[0]?.ok === true
  }
  if (object.kind === 'seed' && object.table === 'oauth_clients') return (await sql`SELECT (SELECT count(*) FROM oauth_clients WHERE client_id IN ('zapier_zync','make_zync')) = 2 AS ok`)[0]?.ok === true
  if (object.kind === 'constraint') {
    const rows = await sql`SELECT pg_get_constraintdef(oid, true) AS definition FROM pg_constraint WHERE conname=${object.name} AND conrelid=to_regclass(${`public.${object.table}`})`
    if (!rows[0]?.definition) return false
    if (!canonicalDefinitionMatches(statement, String(rows[0].definition))) throw new Error(`Canonical constraint mismatch: ${object.name}`)
    return true
  }
  return (await sql`SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conname=${object.name} AND conrelid=to_regclass(${`public.${object.table}`})) AS ok`)[0]?.ok === true
}

async function validate0068Snapshot(sql: Sql, statements: readonly string[]): Promise<void> {
  const snapshot = JSON.parse(await readFile(join(MIGRATIONS, 'meta/0068_snapshot.json'), 'utf8')) as {
    tables: Record<string, { columns: Record<string, { type: string; notNull: boolean; default?: unknown }>; indexes: Record<string, unknown>; foreignKeys: Record<string, unknown>; compositePrimaryKeys: Record<string, unknown>; uniqueConstraints: Record<string, unknown>; checkConstraints: Record<string, unknown> }>
  }
  const canonicalObjects = statements.map(classifyCanonicalStatement)
  const touched = new Set(canonicalObjects.map((item) => item.table ?? (item.kind === 'table' ? item.name : undefined)).filter((item): item is string => Boolean(item)))
  const newTables = new Set(canonicalObjects.filter((item) => item.kind === 'table').map((item) => item.name))
  for (const tableName of touched) {
    const expected = snapshot.tables[`public.${tableName}`]
    if (!expected) throw new Error(`0068 snapshot lacks touched table: ${tableName}`)
    const actualColumns = await sql.unsafe<Array<{ name: string; type: string; not_null: boolean; default_value: string | null }>>(`
      SELECT a.attname AS name, format_type(a.atttypid,a.atttypmod) AS type, a.attnotnull AS not_null,
             pg_get_expr(d.adbin,d.adrelid) AS default_value
      FROM pg_attribute a JOIN pg_class c ON c.oid=a.attrelid JOIN pg_namespace n ON n.oid=c.relnamespace
      LEFT JOIN pg_attrdef d ON d.adrelid=a.attrelid AND d.adnum=a.attnum
      WHERE n.nspname='public' AND c.relname=$1 AND a.attnum>0 AND NOT a.attisdropped ORDER BY a.attname
    `, [tableName])
    const canonicalColumnNames = new Set(canonicalObjects.filter((item) => item.table === tableName && (item.kind === 'column' || item.kind === 'not-null')).map((item) => item.name))
    const expectedColumns = Object.entries(expected.columns)
      .filter(([name]) => newTables.has(tableName) || canonicalColumnNames.has(name))
      .map(([name, column]) => ({ name, type: normalizedType(column.type), notNull: column.notNull, defaultValue: normalizedDefault(column.default) }))
      .sort((a,b) => a.name.localeCompare(b.name))
    const actualShape = actualColumns.map((column) => ({ name: column.name, type: normalizedType(column.type), notNull: column.not_null, defaultValue: normalizedDefault(column.default_value) }))
    const actualByName = new Map(actualShape.map((column) => [column.name, column]))
    const columnMismatches = expectedColumns.filter((expectedColumn) => JSON.stringify(actualByName.get(expectedColumn.name)) !== JSON.stringify(expectedColumn))
    if (columnMismatches.length) throw new Error(`0068 snapshot column mismatch: ${tableName}; expected=${JSON.stringify(columnMismatches)} actual=${JSON.stringify(columnMismatches.map((column) => actualByName.get(column.name) ?? null))}`)

    const actualIndexes = await sql`SELECT indexname AS name FROM pg_indexes WHERE schemaname='public' AND tablename=${tableName} ORDER BY indexname`
    const rawIndexNames = canonicalObjects.filter((item) => item.kind === 'index' && item.table === tableName).map((item) => item.name)
    const snapshotIndexNames = newTables.has(tableName) ? [...Object.keys(expected.indexes), ...Object.keys(expected.compositePrimaryKeys), ...Object.keys(expected.uniqueConstraints)] : []
    const expectedIndexes = [...new Set([...snapshotIndexNames, ...rawIndexNames])].sort()
    const actualIndexNames = actualIndexes.map((row) => String(row.name)).filter((name) => !name.endsWith('_pkey')).sort()
    const expectedIndexNames = expectedIndexes.filter((name) => !name.endsWith('_pkey'))
    const missingIndexes = expectedIndexNames.filter((name) => !actualIndexNames.includes(name))
    if (missingIndexes.length) throw new Error(`0068 snapshot indexes missing: ${tableName}; missing=${JSON.stringify(missingIndexes)} actual=${JSON.stringify(actualIndexNames)}`)

    const actualConstraints = await sql`SELECT conname AS name FROM pg_constraint WHERE conrelid=${`public.${tableName}`}::regclass AND contype IN ('f','c') ORDER BY conname`
    const canonicalConstraintNames = canonicalObjects.filter((item) => item.kind === 'constraint' && item.table === tableName).map((item) => item.name)
    const snapshotConstraintNames = newTables.has(tableName) ? [...Object.keys(expected.foreignKeys), ...Object.keys(expected.checkConstraints)] : []
    const expectedConstraints = [...new Set([...snapshotConstraintNames, ...canonicalConstraintNames])].sort()
    const actualConstraintNames = actualConstraints.map((row) => String(row.name)).sort()
    const missingConstraints = expectedConstraints.filter((name) => !actualConstraintNames.includes(name))
    if (missingConstraints.length) throw new Error(`0068 snapshot constraints missing: ${tableName}; missing=${JSON.stringify(missingConstraints)}`)
  }
}

async function makeThrough0066Folder(): Promise<string> {
  const folder = await mkdtemp(join(tmpdir(), 'zync-migrations-through-0066-'))
  await mkdir(join(folder, 'meta'))
  const journal = JSON.parse(await readFile(join(MIGRATIONS, 'meta/_journal.json'), 'utf8')) as { entries: Array<{ tag: string }> }
  const entries = journal.entries.filter((entry) => Number(entry.tag.slice(0, 4)) <= 66)
  for (const entry of entries) await cp(join(MIGRATIONS, `${entry.tag}.sql`), join(folder, `${entry.tag}.sql`))
  await writeFile(join(folder, 'meta/_journal.json'), JSON.stringify({ ...journal, entries }, null, 2))
  return folder
}

async function main(): Promise<void> {
  const apply = process.argv.includes('--apply')
  const apply0069 = process.argv.includes('--apply-0069')
  const targetArg = process.argv.find((arg) => arg.startsWith('--target='))
  const manifestArg = process.argv.find((arg) => arg.startsWith('--manifest='))
  if (!process.env.DATABASE_URL) throw new Error('DATABASE_URL is required and must identify the explicit Neon branch')
  if (!targetArg) throw new Error('--target=<project/branch label> is required')
  if (apply0069 && !apply) throw new Error('--apply-0069 requires --apply')
  await validateCanonicalFiles()
  const client = postgres(process.env.DATABASE_URL, { max: 1, prepare: false })
  const evidence: Record<string, unknown> = { version: 1, target: targetArg.slice(9), mode: apply ? 'apply' : 'dry-run', startedAt: new Date().toISOString(), ledger: [], migration0068: [] }
  let tempFolder: string | undefined
  try {
    const sql0068 = await readFile(join(MIGRATIONS, '0068_schema_drift_consolidated.sql'), 'utf8')
    const statements = splitCanonicalStatements(sql0068)
    const actions: Array<{ ordinal: number; hash: string; object: ReturnType<typeof classifyCanonicalStatement>; action: 'execute' | 'skip' }> = []
    const inspect = async (db: Sql, execute: boolean) => {
      for (const [index, statement] of statements.entries()) {
        const object = classifyCanonicalStatement(statement)
        const present = await exists(db, statement)
        actions.push({ ordinal: index + 1, hash: sha256(statement), object, action: present ? 'skip' : 'execute' })
        if (execute && !present) await db.unsafe(transformCanonicalStatementForExecution(statement))
      }
      if (execute) {
        for (const statement of statements) {
          const object = classifyCanonicalStatement(statement)
          if (object.kind !== 'constraint-drop' && !await exists(db, statement)) throw new Error(`0068 postcondition failed: ${JSON.stringify(object)}`)
        }
      }
    }
    if (apply) {
      await inspect(client, false)
      actions.length = 0
    }
    evidence.ledger = apply
      ? await client.begin((tx) => reconcileLedger(adapter(tx), EXPECTED, CHECKS, true))
      : await reconcileLedger(adapter(client), EXPECTED, CHECKS, false)

    if (apply) {
      tempFolder = await makeThrough0066Folder()
      await migrate(drizzle(client), { migrationsFolder: tempFolder })
    }
    if (apply) await client.begin(async (tx) => { await inspect(tx, true); await validate0068Snapshot(tx, statements) })
    else {
      await inspect(client, false)
      if (actions.every((item) => item.action === 'skip')) await validate0068Snapshot(client, statements)
    }
    evidence.migration0068 = actions

    if (apply) {
      const canonical0068 = await readFile(join(MIGRATIONS, '0068_schema_drift_consolidated.sql'))
      const journal = JSON.parse(await readFile(join(MIGRATIONS, 'meta/_journal.json'), 'utf8')) as { entries: Array<{ tag: string; when: number }> }
      const entry0068 = journal.entries.find((entry) => entry.tag === '0068_schema_drift_consolidated')!
      await adapter(client).insertLedger(sha256(canonical0068), entry0068.when)
      if (apply0069) {
        await migrate(drizzle(client), { migrationsFolder: MIGRATIONS })
        const verification = await client<{
          ledger_count: number
          shell_layouts: boolean
          force_shell: boolean
          ui_shell: boolean
          shell_constraint_count: number
        }[]>`
          SELECT
            (SELECT count(*)::int FROM drizzle.__drizzle_migrations) AS ledger_count,
            to_regclass('public.shell_layouts') IS NOT NULL AS shell_layouts,
            EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='tenant_settings' AND column_name='force_shell') AS force_shell,
            EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='user_preferences' AND column_name='ui_shell') AS ui_shell,
            (SELECT count(*)::int FROM pg_constraint WHERE conrelid=to_regclass('public.shell_layouts')) AS shell_constraint_count
        `
        const finalState = verification[0]!
        if (finalState.ledger_count !== 65 || !finalState.shell_layouts || !finalState.force_shell || !finalState.ui_shell || finalState.shell_constraint_count < 3) {
          throw new Error(`0069 final verification failed: ${JSON.stringify(finalState)}`)
        }
        evidence.finalVerification = finalState
      }
    }
    evidence.completedAt = new Date().toISOString()
    evidence.next = apply0069 ? 'verify canonical ledger and 0069 schema' : 'rerun with --apply-0069 after reviewing this manifest'
  } finally {
    if (tempFolder) await rm(tempFolder, { recursive: true, force: true })
    await client.end()
  }
  const manifest = manifestArg?.slice(11) ?? join(process.cwd(), `migration-reconciliation-${Date.now()}.json`)
  await writeFile(manifest, `${JSON.stringify(evidence, null, 2)}\n`, { flag: 'wx' })
  console.log(`${apply ? 'Applied' : 'Dry-run complete'}; evidence: ${manifest}`)
}

if (import.meta.url === `file://${process.argv[1]}`) main().catch((error) => { console.error(error instanceof Error ? error.message : error); process.exitCode = 1 })