export interface CanonicalMigration {
  tag: string
  createdAt: number
  hash: string
}

export interface ReconciliationCheck {
  id: string
  migration: string
  sql: string
}

export interface ReconciliationDb {
  check(sql: string): Promise<boolean>
  ledgerHas(hash: string, createdAt: number): Promise<boolean>
  insertLedger(hash: string, createdAt: number): Promise<void>
}

export interface LedgerPlanItem extends CanonicalMigration {
  action: 'insert' | 'skip'
}

export async function reconcileLedger(
  db: ReconciliationDb,
  migrations: readonly CanonicalMigration[],
  checks: readonly ReconciliationCheck[],
  apply: boolean,
): Promise<LedgerPlanItem[]> {
  for (const check of checks) {
    if (!await db.check(check.sql)) throw new Error(`Postcondition failed closed: ${check.migration}/${check.id}`)
  }

  const plan: LedgerPlanItem[] = []
  for (const migration of migrations) {
    const exists = await db.ledgerHas(migration.hash, migration.createdAt)
    plan.push({ ...migration, action: exists ? 'skip' : 'insert' })
  }
  if (!apply) return plan

  for (const item of plan) {
    if (item.action === 'insert') await db.insertLedger(item.hash, item.createdAt)
  }
  return plan
}

export function splitCanonicalStatements(sql: string): string[] {
  return sql.split('--> statement-breakpoint').map((statement) => statement.trim()).filter(Boolean)
}

export type CanonicalObject = {
  kind: 'table' | 'column' | 'constraint' | 'constraint-drop' | 'column-drop' | 'not-null' | 'index' | 'seed'
  table?: string
  name: string
}

export function classifyCanonicalStatement(statement: string): CanonicalObject {
  const normalized = statement.replace(/^\s*(?:--[^\n]*\n\s*)+/, '').trim()
  const table = normalized.match(/^CREATE TABLE\s+"?([\w]+)"?/i)
  if (table) return { kind: 'table', name: table[1]! }
  const column = normalized.match(/^ALTER TABLE\s+"?([\w]+)"?\s+ADD COLUMN(?: IF NOT EXISTS)?\s+"?([\w]+)"?/i)
  if (column) return { kind: 'column', table: column[1]!, name: column[2]! }
  const constraintDrop = normalized.match(/^ALTER TABLE\s+"?([\w]+)"?\s+DROP CONSTRAINT\s+"?([\w]+)"?/i)
  if (constraintDrop) return { kind: 'constraint-drop', table: constraintDrop[1]!, name: constraintDrop[2]! }
  const columnDrop = normalized.match(/^ALTER TABLE\s+"?([\w]+)"?\s+DROP COLUMN\s+"?([\w]+)"?/i)
  if (columnDrop) return { kind: 'column-drop', table: columnDrop[1]!, name: columnDrop[2]! }
  const notNull = normalized.match(/^ALTER TABLE\s+"?([\w]+)"?\s+ALTER COLUMN\s+"?([\w]+)"?\s+SET NOT NULL/i)
  if (notNull) return { kind: 'not-null', table: notNull[1]!, name: notNull[2]! }
  const constraint = normalized.match(/^ALTER TABLE\s+"?([\w]+)"?\s+ADD CONSTRAINT\s+"?([\w]+)"?/i)
  if (constraint) return { kind: 'constraint', table: constraint[1]!, name: constraint[2]! }
  const index = normalized.match(/^CREATE(?: UNIQUE)? INDEX(?: IF NOT EXISTS)?\s+"?([\w]+)"?\s+ON\s+"?([\w]+)"?/i)
  if (index) return { kind: 'index', table: index[2]!, name: index[1]! }
  const seed = normalized.match(/^INSERT INTO\s+"?([\w]+)"?/i)
  if (seed) return { kind: 'seed', table: seed[1]!, name: `${seed[1]}-seed` }
  throw new Error(`Unsupported canonical statement; refusing to execute: ${normalized.slice(0, 100)}`)
}

export function transformCanonicalStatementForExecution(statement: string): string {
  const object = classifyCanonicalStatement(statement)
  if (object.kind !== 'seed' || object.table !== 'stock_locations') return statement

  const trimmed = statement.trim()
  if (/\bWHERE\s+NOT\s+EXISTS\s*\(/i.test(trimmed)) return statement
  const withoutSemicolon = trimmed.replace(/;\s*$/, '')
  if (!/\bFROM\s+"?tenants"?\s*$/i.test(withoutSemicolon)) {
    throw new Error('Unsupported stock_locations seed shape; refusing to transform')
  }

  return `${withoutSemicolon}
WHERE NOT EXISTS (
  SELECT 1 FROM "stock_locations" existing
  WHERE existing."tenant_id" = "tenants"."id" AND existing."code" = 'MAIN'
);`
}

function normalizeSql(value: string): string {
  return value
    .replace(/\bpublic\./gi, '')
    .replace(/[";]/g, '')
    .replace(/\s+/g, ' ')
    .replace(/\s*,\s*/g, ',')
    .replace(/\(\s+/g, '(')
    .replace(/\s+\)/g, ')')
    .replace(/^check \(\((.*)\)\)$/i, 'check ($1)')
    .trim()
    .toLowerCase()
}

function unwrapParentheses(value: string): string {
  let result = value.trim()
  while (result.startsWith('(') && result.endsWith(')')) {
    let depth = 0
    let enclosesAll = true
    for (let index = 0; index < result.length; index += 1) {
      if (result[index] === '(') depth += 1
      if (result[index] === ')') depth -= 1
      if (depth === 0 && index < result.length - 1) {
        enclosesAll = false
        break
      }
    }
    if (!enclosesAll) break
    result = result.slice(1, -1).trim()
  }
  return result
}

function splitTopLevel(value: string): string[] {
  const parts: string[] = []
  let start = 0
  let depth = 0
  for (let index = 0; index < value.length; index += 1) {
    if (value[index] === '(') depth += 1
    if (value[index] === ')') depth -= 1
    if (value[index] === ',' && depth === 0) {
      parts.push(value.slice(start, index))
      start = index + 1
    }
  }
  parts.push(value.slice(start))
  return parts
}

function findClosingParenthesis(value: string, opening: number): number {
  let depth = 0
  for (let index = opening; index < value.length; index += 1) {
    if (value[index] === '(') depth += 1
    if (value[index] === ')') {
      depth -= 1
      if (depth === 0) return index
    }
  }
  return -1
}

function parseIndexDefinition(value: string): { unique: boolean; table: string; method: string; columns: string[]; predicate: string | null } | null {
  const normalized = normalizeSql(value)
  const match = normalized.match(/^create\s+(unique\s+)?index(?:\s+if\s+not\s+exists)?\s+\S+\s+on\s+([\w.]+)(?:\s+using\s+(\w+))?\s*/i)
  if (!match) return null
  const opening = normalized.indexOf('(', match[0].length)
  const closing = opening >= 0 ? findClosingParenthesis(normalized, opening) : -1
  if (opening < 0 || closing < 0) return null
  const suffix = normalized.slice(closing + 1).trim()
  if (suffix && !suffix.startsWith('where ')) return null
  return {
    unique: Boolean(match[1]),
    table: match[2]!.replace(/^public\./i, ''),
    method: (match[3] ?? 'btree').toLowerCase(),
    columns: splitTopLevel(normalized.slice(opening + 1, closing)).map((column) => normalizeSql(column)),
    predicate: suffix ? unwrapParentheses(suffix.slice('where '.length).trim()) : null,
  }
}

export function canonicalDefinitionMatches(statement: string, actualDefinition: string): boolean {
  const object = classifyCanonicalStatement(statement)
  if (object.kind === 'index') {
    const expected = parseIndexDefinition(statement)
    const actual = parseIndexDefinition(actualDefinition)
    return Boolean(expected && actual
      && expected.unique === actual.unique
      && expected.table === actual.table
      && expected.method === actual.method
      && expected.columns.length === actual.columns.length
      && expected.columns.every((column, index) => column === actual.columns[index])
      && expected.predicate === actual.predicate)
  }
  if (object.kind === 'constraint') {
    const expected = statement.match(/ADD CONSTRAINT\s+"?[\w]+"?\s+(.+)$/i)?.[1]
    const tablePrefix = new RegExp(`${object.table}\\.`, 'g')
    const normalizeConstraint = (value: string) => normalizeSql(value)
      .replace(/\s+\(/g, '(')
      .replace(tablePrefix, '')
      .replace(/\s+match\s+simple\b|\s+on\s+update\s+no\s+action\b|\s+on\s+delete\s+no\s+action\b|\s+not\s+deferrable\b|\s+initially\s+immediate\b/gi, '')
      .replace(/^check\(\((.*)\)\)$/i, 'check($1)')
    return expected
      ? normalizeConstraint(expected) === normalizeConstraint(actualDefinition)
      : false
  }
  return true
}