import { mkdirSync, writeFileSync } from 'node:fs'
import { dirname, join } from 'node:path'
import { findSmartStackStructure } from '../../../lib/detector.js'
import { resolveConnectionString, parseConnectionString } from '../../../lib/appsettings.js'
import { FK_AUDIT_ALLOWLIST } from '../../../lib/fk-allowlist.js'
import { fetchSchema } from './sql.js'
import { classifyColumns, sortFindings } from './classify.js'
import type { EntityAuditSpec, EntityAuditReport, ColumnFinding } from './types.js'

export interface ExecuteResult {
  report: EntityAuditReport
  errors: string[]
  warnings: string[]
  nextSteps: string[]
  reportPath: string | null
}

export async function execute(spec: EntityAuditSpec): Promise<ExecuteResult> {
  const warnings: string[] = []

  // 1. Resolve the connection string (explicit override → appsettings*.json).
  let connStr = spec.connectionString ?? null
  let source = 'spec.connectionString'
  if (!connStr) {
    const structure = await findSmartStackStructure(spec.projectPath)
    if (!structure.api) {
      throw new Error(`Could not locate the API project (*.Api) under "${spec.projectPath}". Pass connectionString explicitly.`)
    }
    const resolved = resolveConnectionString(structure.api)
    if (!resolved.value) {
      throw new Error(`No ConnectionStrings:DefaultConnection found in appsettings*.json under "${structure.api}".`)
    }
    connStr = resolved.value
    source = resolved.source ?? 'appsettings'
  }
  const conn = parseConnectionString(connStr)

  // 2. Allowlist = built-in identity/audit columns + per-run extras.
  const allowlist = [...FK_AUDIT_ALLOWLIST, ...spec.allowlist.map((s) => new RegExp(s, 'i'))]

  // 3. Introspect the LIVE database, then classify.
  const { columns, fks, tables } = fetchSchema(conn, spec.schemas, spec.sqlcmdPath)
  const all = classifyColumns(columns, fks, tables, allowlist)
  const findings = sortFindings(all.filter((f) => f.classification !== 'ok'))
  const count = (c: ColumnFinding['classification']) => all.filter((f) => f.classification === c).length

  const report: EntityAuditReport = {
    database: conn.database,
    server: conn.server,
    schemasAudited: spec.schemas,
    totalIdColumns: all.length,
    criticalCount: count('critical'),
    reviewCount: count('review'),
    exemptCount: count('exempt'),
    okCount: count('ok'),
    findings,
  }

  const nextSteps: string[] = []
  if (report.criticalCount > 0) {
    nextSteps.push(
      'Add the missing FK constraints — every CRITICAL column references a real table with no foreign key. Re-run /ba-develop Phase 2 (scaffold-entity now emits Tenant/cross-module/core FKs) or add HasOne<…>().HasForeignKey(…) + regenerate the migration.',
    )
  }
  if (report.reviewCount > 0) {
    nextSteps.push('Review each REVIEW column: confirm it is a genuinely external/opaque id (no principal table). Otherwise it needs a FK too.')
  }
  if (report.criticalCount === 0 && report.reviewCount === 0) {
    nextSteps.push('No missing foreign keys. Referential integrity is intact for the audited schemas.')
  }

  let reportPath: string | null = null
  if (spec.writeReport) {
    reportPath = join(spec.projectPath, '.smartstack', '_audit', 'entity-fk-audit.md')
    mkdirSync(dirname(reportPath), { recursive: true })
    writeFileSync(reportPath, renderReport(report, source), 'utf-8')
  }

  return { report, errors: [], warnings, nextSteps, reportPath }
}

function renderReport(report: EntityAuditReport, source: string): string {
  const verdict = report.criticalCount > 0 ? '❌ FAIL' : report.reviewCount > 0 ? '⚠️ REVIEW' : '✅ PASS'
  const lines: string[] = []
  lines.push('# Entity FK Audit — referential integrity verdict', '')
  lines.push(`**Verdict:** ${verdict}`, '')
  lines.push(`- Server / database: \`${report.server}\` / \`${report.database}\` (connection from ${source})`)
  lines.push(`- Schemas audited: ${report.schemasAudited.map((s) => `\`${s}\``).join(', ')}`)
  lines.push(
    `- \`*Id\` columns: ${report.totalIdColumns} — ` +
      `**${report.criticalCount} critical**, ${report.reviewCount} review, ${report.exemptCount} exempt, ${report.okCount} ok`,
    '',
  )
  const section = (title: string, cls: ColumnFinding['classification']) => {
    const rows = report.findings.filter((f) => f.classification === cls)
    if (!rows.length) return
    lines.push(`## ${title} (${rows.length})`, '')
    lines.push('| Column | Candidate principal | Reason |', '|---|---|---|')
    for (const f of rows) {
      lines.push(`| \`${f.schema}.${f.table}.${f.column}\` | ${f.candidatePrincipal ? `\`${f.candidatePrincipal}\`` : '—'} | ${f.reason} |`)
    }
    lines.push('')
  }
  section('CRITICAL — missing FK to an existing table', 'critical')
  section('REVIEW — *Id with no resolvable principal', 'review')
  section('EXEMPT — identity/audit columns (intentionally not a FK)', 'exempt')
  lines.push('---', '', '_A cross-table reference without a FK violates referential integrity. The only non-FK `*Id` columns are the documented identity/audit allowlist (`lib/fk-allowlist.ts`)._')
  return lines.join('\n') + '\n'
}