/**
 * classify.ts — PURE classification of `*Id` columns against the live FK constraints.
 * No I/O — unit-testable with plain fixtures (the SQL layer feeds it real rows).
 */
import { singularize } from '../../../lib/string-utils.js'
import { isAuditAllowlisted, FK_AUDIT_ALLOWLIST } from '../../../lib/fk-allowlist.js'
import type { ColumnFinding } from './types.js'

export interface DbColumn { schema: string; table: string; column: string; dataType: string }
export interface DbFk { schema: string; table: string; column: string }
export interface DbTable { schema: string; table: string }

/** Entity base name = the part after the first `_` (the table prefix), else the whole name. */
export function tableBaseName(table: string): string {
  const i = table.indexOf('_')
  return i >= 0 ? table.slice(i + 1) : table
}

/**
 * Index every table by its entity stem (singular + plural, lowercased) → "schema.table".
 * Used to resolve the principal a `*Id` column points at (e.g. `TenantId` → `core.tenant_Tenants`).
 */
export function buildPrincipalIndex(tables: DbTable[]): Map<string, string> {
  const idx = new Map<string, string>()
  for (const t of tables) {
    const base = tableBaseName(t.table)
    for (const k of [base.toLowerCase(), singularize(base).toLowerCase()]) {
      if (!idx.has(k)) idx.set(k, `${t.schema}.${t.table}`)
    }
  }
  return idx
}

/** Resolve the principal table a `*Id` column references, or null when none matches. */
export function resolvePrincipal(column: string, idx: Map<string, string>): string | null {
  if (!/Id$/i.test(column) || column.toLowerCase() === 'id') return null
  const stem = column.replace(/Id$/i, '')
  if (!stem) return null
  return idx.get(stem.toLowerCase()) ?? idx.get(singularize(stem).toLowerCase()) ?? null
}

/**
 * Classify each `*Id` column:
 *   - ok       → already covered by a FK constraint.
 *   - exempt   → identity/audit allowlist column (intentionally not a FK).
 *   - critical → resolves to an existing principal table but has NO FK → integrity violation.
 *   - review   → ends in Id with no resolvable principal (likely an external/opaque id).
 */
export function classifyColumns(
  columns: DbColumn[],
  fks: DbFk[],
  tables: DbTable[],
  allowlist: ReadonlyArray<RegExp | string> = FK_AUDIT_ALLOWLIST,
): ColumnFinding[] {
  const covered = new Set(fks.map((f) => `${f.schema}.${f.table}.${f.column}`.toLowerCase()))
  const idx = buildPrincipalIndex(tables)
  return columns.map((c) => {
    const hasFk = covered.has(`${c.schema}.${c.table}.${c.column}`.toLowerCase())
    const candidatePrincipal = resolvePrincipal(c.column, idx)
    let classification: ColumnFinding['classification']
    let reason: string
    if (hasFk) {
      classification = 'ok'
      reason = 'covered by a foreign-key constraint'
    } else if (isAuditAllowlisted(c.column, allowlist)) {
      classification = 'exempt'
      reason = 'identity/audit column — intentionally not a FK (allowlist)'
    } else if (candidatePrincipal) {
      classification = 'critical'
      reason = `references ${candidatePrincipal} but has NO foreign-key constraint`
    } else {
      classification = 'review'
      reason = 'ends in Id with no resolvable principal table — verify whether it is an external/opaque id'
    }
    return { schema: c.schema, table: c.table, column: c.column, classification, candidatePrincipal, hasFk, reason }
  })
}

const ORDER: Record<ColumnFinding['classification'], number> = { critical: 0, review: 1, exempt: 2, ok: 3 }

/** Sort findings critical-first, then by schema.table.column. */
export function sortFindings(findings: ColumnFinding[]): ColumnFinding[] {
  return [...findings].sort(
    (a, b) =>
      ORDER[a.classification] - ORDER[b.classification] ||
      `${a.schema}.${a.table}.${a.column}`.localeCompare(`${b.schema}.${b.table}.${b.column}`),
  )
}