import type { Database } from "@/lib/db/driver";

/**
 * Idempotent in-place upgrades for databases created before a column existed.
 *
 * `CREATE TABLE IF NOT EXISTS` cannot add a column to an existing table, so the
 * schema files describe the current shape for FRESH installs and this module
 * brings EXISTING installs up to it. Every step checks first and is safe to
 * re-run, on both SQLite and PostgreSQL.
 */

interface ColumnSpec {
  table: string;
  column: string;
  /** Type + constraints, written in the portable subset used by both schemas. */
  definition: string;
}

async function existingColumns(db: Database, table: string): Promise<Set<string>> {
  if (db.dialect === "postgres") {
    const rows = await db.all<{ column_name: string }>(
      "SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = ?",
      [table],
    );
    return new Set(rows.map((r) => r.column_name));
  }
  // PRAGMA cannot be parameterised; the table name is from our own constant list.
  const rows = await db.all<{ name: string }>(`PRAGMA table_info(${table})`);
  return new Set(rows.map((r) => r.name));
}

async function tableExists(db: Database, table: string): Promise<boolean> {
  if (db.dialect === "postgres") {
    const row = await db.get<{ n: number }>(
      "SELECT COUNT(*) AS n FROM information_schema.tables WHERE table_schema = 'public' AND table_name = ?",
      [table],
    );
    return Number(row?.n ?? 0) > 0;
  }
  const row = await db.get<{ n: number }>(
    "SELECT COUNT(*) AS n FROM sqlite_master WHERE type = 'table' AND name = ?",
    [table],
  );
  return Number(row?.n ?? 0) > 0;
}

/**
 * Columns added after the initial release.
 *
 * A foreign key cannot be attached by ALTER TABLE in SQLite, so these are added
 * as plain nullable columns on upgraded databases. Referential integrity is
 * still declared for fresh installs (see the schema files) and enforced in the
 * application by `assertProgramScope`.
 */
const ADDED_COLUMNS: ColumnSpec[] = [
  { table: "profiles", column: "program_id", definition: "TEXT" },
  { table: "curricula", column: "program_id", definition: "TEXT" },
  { table: "students", column: "program_id", definition: "TEXT" },
  { table: "audit_logs", column: "program_id", definition: "TEXT" },
];

export interface UpgradeReport {
  addedColumns: string[];
  createdDefaultProgram: string | null;
  backfilledCurricula: number;
  backfilledStudents: number;
}

export function emptyUpgradeReport(): UpgradeReport {
  return { addedColumns: [], createdDefaultProgram: null, backfilledCurricula: 0, backfilledStudents: 0 };
}

/**
 * Phase 1 — runs BEFORE the schema file.
 *
 * The schema declares indexes over the new columns, so those columns have to
 * exist first on an already-populated database. On a fresh database every table
 * is still missing and each step simply skips.
 */
export async function applyColumnUpgrades(db: Database): Promise<string[]> {
  const added: string[] = [];
  for (const spec of ADDED_COLUMNS) {
    if (!(await tableExists(db, spec.table))) continue;
    const columns = await existingColumns(db, spec.table);
    if (columns.has(spec.column)) continue;
    await db.exec(`ALTER TABLE ${spec.table} ADD COLUMN ${spec.column} ${spec.definition}`);
    added.push(`${spec.table}.${spec.column}`);
  }
  return added;
}

/**
 * Phase 2 — runs AFTER the schema file, once `programs` exists.
 *
 * An installation that predates program scoping holds exactly one program's
 * worth of data, so everything is assigned to the default program.
 */
export async function applyDataBackfill(
  db: Database,
  options: { defaultProgramCode?: string; defaultProgramName?: string } = {},
): Promise<UpgradeReport> {
  const report = emptyUpgradeReport();

  if (!(await tableExists(db, "programs"))) return report;

  const orphanedCurricula = await db.get<{ n: number }>(
    "SELECT COUNT(*) AS n FROM curricula WHERE program_id IS NULL",
  );
  const orphanedStudents = await db.get<{ n: number }>(
    "SELECT COUNT(*) AS n FROM students WHERE program_id IS NULL",
  );
  const needsBackfill = Number(orphanedCurricula?.n ?? 0) > 0 || Number(orphanedStudents?.n ?? 0) > 0;
  if (!needsBackfill) return report;

  const code = options.defaultProgramCode ?? "MED";
  const name = options.defaultProgramName ?? "College of Medicine";

  let program = await db.get<{ id: string }>("SELECT id FROM programs WHERE code = ?", [code]);
  if (!program) {
    const existingAny = await db.get<{ id: string; code: string }>(
      "SELECT id, code FROM programs ORDER BY created_at ASC",
    );
    if (existingAny) {
      // Never invent a second program when one already exists.
      program = { id: existingAny.id };
    } else {
      const id = crypto.randomUUID();
      const now = new Date().toISOString();
      await db.run(
        "INSERT INTO programs (id, code, name, active, notes, created_at, updated_at) VALUES (?, ?, ?, 1, ?, ?, ?)",
        [id, code, name, "Created automatically when program scoping was introduced.", now, now],
      );
      program = { id };
      report.createdDefaultProgram = `${code} — ${name}`;
    }
  }

  await db.run("UPDATE curricula SET program_id = ? WHERE program_id IS NULL", [program.id]);
  await db.run("UPDATE students SET program_id = ? WHERE program_id IS NULL", [program.id]);
  report.backfilledCurricula = Number(orphanedCurricula?.n ?? 0);
  report.backfilledStudents = Number(orphanedStudents?.n ?? 0);

  return report;
}