import "server-only";
import { newId, nowIso, type DbClient } from "@/lib/db";
import { normalizeStudentNumber, resolveCurriculumForStudentNumber } from "@/lib/curriculum-resolver";
import type { StudentNumberNormalization } from "@/lib/settings/constants";
import { parseNumberCell, readMapped, type ColumnDefinition, type ParsedSheet } from "@/lib/imports/workbook";
import type { CurriculumAssignmentRule, ImportMode } from "@/types";

/** Student bulk import (SPEC 14). */

export const STUDENT_COLUMNS: ColumnDefinition[] = [
  {
    key: "student_number",
    label: "Student Number",
    required: true,
    aliases: ["student no", "studentno", "student id", "id number", "idno", "sn"],
  },
  { key: "last_name", label: "Last Name", required: true, aliases: ["surname", "family name", "lastname"] },
  { key: "first_name", label: "First Name", required: true, aliases: ["given name", "firstname"] },
  { key: "middle_name", label: "Middle Name", required: false, aliases: ["middlename", "middle initial", "mi"] },
  { key: "suffix", label: "Suffix", required: false, aliases: ["name suffix", "jr sr"] },
  {
    key: "premed_course",
    label: "Pre-Med Course",
    required: false,
    aliases: ["premed", "pre med course", "undergraduate degree", "undergrad course", "bachelor degree", "course"],
  },
  {
    key: "premed_school",
    label: "Pre-Med School",
    required: false,
    aliases: ["premed school", "school", "undergraduate school", "university", "college"],
  },
  {
    key: "graduation_year",
    label: "Year Graduated",
    required: false,
    aliases: ["year graduated", "grad year", "yeargraduated"],
  },
  { key: "gwa", label: "GWA", required: false, aliases: ["general weighted average", "gpa", "average"] },
  { key: "nmat_score", label: "NMAT Score", required: false, aliases: ["nmat", "nmat percentile", "nmat rating"] },
  { key: "nmat_year", label: "NMAT Year", required: false, aliases: ["year nmat taken", "nmat date", "nmat taken"] },
  {
    key: "curriculum_code",
    label: "Curriculum (optional override)",
    required: false,
    aliases: ["curriculum", "curriculum code", "assigned curriculum", "curr"],
    hint: "When present, this value overrides the automatic student-number resolution.",
  },
  { key: "notes", label: "Notes", required: false, aliases: ["remarks", "comment", "comments"] },
];

export type StudentRowAction = "CREATE" | "UPDATE" | "SKIP" | "REJECT";

export interface StudentPreviewRow {
  rowNumber: number;
  student_number: string;
  student_number_key: string;
  last_name: string;
  first_name: string;
  middle_name: string | null;
  suffix: string | null;
  premed_course: string | null;
  premed_school: string | null;
  graduation_year: number | null;
  gwa: string | null;
  nmat_score: number | null;
  nmat_year: number | null;
  notes: string | null;
  curriculumCode: string | null;
  resolvedCurriculumId: string | null;
  resolvedCurriculumCode: string | null;
  assignmentMethod: "AUTO" | "MANUAL" | "UNRESOLVED";
  assignmentRuleId: string | null;
  resolutionOutcome: "MATCHED" | "UNRESOLVED" | "CONFLICT" | "EXPLICIT";
  resolutionMessage: string;
  action: StudentRowAction;
  errors: string[];
  warnings: string[];
  /** Fields that differ from the existing record, for UPDATE previews. */
  changedFields: string[];
  existingStudentId: string | null;
}

export interface StudentPreview {
  sheetName: string;
  mode: ImportMode;
  detectedRowCount: number;
  createCount: number;
  updateCount: number;
  skipCount: number;
  rejectCount: number;
  unresolvedCount: number;
  conflictCount: number;
  duplicateInFileCount: number;
  rows: StudentPreviewRow[];
  canCommit: boolean;
}

const CURRENT_YEAR = new Date().getFullYear();

function parseYearCell(value: string): { year: number | null; error: string | null } {
  if (!value.trim()) return { year: null, error: null };
  const parsed = parseNumberCell(value);
  if (parsed === null || !Number.isInteger(parsed)) {
    return { year: null, error: `"${value}" is not a valid 4-digit year.` };
  }
  if (parsed < 1900 || parsed > CURRENT_YEAR + 10) {
    return { year: null, error: `Year ${parsed} is outside the accepted range 1900–${CURRENT_YEAR + 10}.` };
  }
  return { year: parsed, error: null };
}

export interface BuildStudentPreviewContext {
  sheet: ParsedSheet;
  mapping: Record<string, number | null>;
  mode: ImportMode;
  rules: CurriculumAssignmentRule[];
  normalization: StudentNumberNormalization;
  /** Existing students, keyed by normalised student number. */
  existing: Map<string, Record<string, unknown> & { id: string }>;
  /** Curriculum code -> id, for the optional explicit curriculum column. */
  curriculaByCode: Map<string, { id: string; code: string }>;
  curriculaById: Map<string, { id: string; code: string }>;
}

export function buildStudentPreview(context: BuildStudentPreviewContext): StudentPreview {
  const { sheet, mapping, mode, rules, normalization, existing, curriculaByCode, curriculaById } = context;
  const rows: StudentPreviewRow[] = [];
  const seenInFile = new Map<string, number>();

  for (const definition of STUDENT_COLUMNS) {
    if (definition.required && (mapping[definition.key] === null || mapping[definition.key] === undefined)) {
      // Surfaced as a row-0 rejection so the wizard can block the commit.
      rows.push({
        rowNumber: 0,
        student_number: "",
        student_number_key: "",
        last_name: "",
        first_name: "",
        middle_name: null,
        suffix: null,
        premed_course: null,
        premed_school: null,
        graduation_year: null,
        gwa: null,
        nmat_score: null,
        nmat_year: null,
        notes: null,
        curriculumCode: null,
        resolvedCurriculumId: null,
        resolvedCurriculumCode: null,
        assignmentMethod: "UNRESOLVED",
        assignmentRuleId: null,
        resolutionOutcome: "UNRESOLVED",
        resolutionMessage: "",
        action: "REJECT",
        errors: [`The required column "${definition.label}" has not been mapped.`],
        warnings: [],
        changedFields: [],
        existingStudentId: null,
      });
    }
  }

  sheet.rows.forEach((raw, index) => {
    const rowNumber = sheet.headerRowNumber + index + 1;
    const errors: string[] = [];
    const warnings: string[] = [];

    const studentNumber = readMapped(raw, mapping, "student_number");
    const key = normalizeStudentNumber(studentNumber, normalization);
    const lastName = readMapped(raw, mapping, "last_name");
    const firstName = readMapped(raw, mapping, "first_name");

    if (!studentNumber) errors.push("Student number is required.");
    if (!lastName) errors.push("Last name is required.");
    if (!firstName) errors.push("First name is required.");

    const gradYear = parseYearCell(readMapped(raw, mapping, "graduation_year"));
    if (gradYear.error) errors.push(`Year graduated: ${gradYear.error}`);
    const nmatYear = parseYearCell(readMapped(raw, mapping, "nmat_year"));
    if (nmatYear.error) errors.push(`NMAT year: ${nmatYear.error}`);

    const nmatRaw = readMapped(raw, mapping, "nmat_score");
    let nmatScore: number | null = null;
    if (nmatRaw.trim()) {
      const parsed = parseNumberCell(nmatRaw);
      if (parsed === null || parsed < 0 || parsed > 100) {
        errors.push(`NMAT score "${nmatRaw}" must be a number between 0 and 100.`);
      } else {
        nmatScore = parsed;
      }
    }

    let duplicateInFile = false;
    if (key) {
      const previous = seenInFile.get(key);
      if (previous !== undefined) {
        duplicateInFile = true;
        errors.push(`Duplicate student number — already appears on row ${previous} of this file.`);
      } else {
        seenInFile.set(key, rowNumber);
      }
    }

    // --------------------------------------------------- curriculum resolution
    const explicitCode = readMapped(raw, mapping, "curriculum_code");
    let resolvedCurriculumId: string | null = null;
    let assignmentMethod: "AUTO" | "MANUAL" | "UNRESOLVED" = "UNRESOLVED";
    let assignmentRuleId: string | null = null;
    let resolutionOutcome: StudentPreviewRow["resolutionOutcome"] = "UNRESOLVED";
    let resolutionMessage = "";

    if (explicitCode) {
      const match = curriculaByCode.get(explicitCode.trim().toUpperCase());
      if (match) {
        resolvedCurriculumId = match.id;
        assignmentMethod = "MANUAL";
        resolutionOutcome = "EXPLICIT";
        resolutionMessage = `Curriculum specified in the file: ${match.code}`;
      } else {
        errors.push(`Curriculum "${explicitCode}" does not exist. Create it before importing, or clear the cell.`);
        resolutionMessage = `Unknown curriculum code "${explicitCode}".`;
      }
    } else if (key) {
      const resolution = resolveCurriculumForStudentNumber(key, rules, {
        normalization,
        normalizeInput: false,
      });
      resolutionOutcome = resolution.outcome;
      resolutionMessage = resolution.message;
      if (resolution.outcome === "MATCHED") {
        resolvedCurriculumId = resolution.curriculumId;
        assignmentMethod = "AUTO";
        assignmentRuleId = resolution.matchedRule?.id ?? null;
      } else {
        warnings.push(
          resolution.outcome === "CONFLICT"
            ? "Conflicting assignment rules — curriculum must be assigned manually after import."
            : "No assignment rule matches — curriculum must be assigned manually after import.",
        );
      }
    }

    // ------------------------------------------------------- create vs update
    const existingRow = key ? existing.get(key) : undefined;
    let action: StudentRowAction = "CREATE";
    const changedFields: string[] = [];

    if (errors.length > 0) {
      action = "REJECT";
    } else if (existingRow) {
      if (mode === "CREATE_ONLY") {
        action = "SKIP";
        warnings.push("A student with this student number already exists. Skipped in CREATE_ONLY mode.");
      } else {
        action = "UPDATE";
        const candidate: Record<string, unknown> = {
          last_name: lastName,
          first_name: firstName,
          middle_name: readMapped(raw, mapping, "middle_name") || null,
          suffix: readMapped(raw, mapping, "suffix") || null,
          premed_course: readMapped(raw, mapping, "premed_course") || null,
          premed_school: readMapped(raw, mapping, "premed_school") || null,
          graduation_year: gradYear.year,
          gwa: readMapped(raw, mapping, "gwa") || null,
          nmat_score: nmatScore,
          nmat_year: nmatYear.year,
          notes: readMapped(raw, mapping, "notes") || null,
        };
        for (const [field, value] of Object.entries(candidate)) {
          const before = existingRow[field] ?? null;
          if (String(before ?? "") !== String(value ?? "")) changedFields.push(field);
        }
        if (changedFields.length === 0) warnings.push("No field changes detected for this existing student.");
      }
    }

    rows.push({
      rowNumber,
      student_number: studentNumber,
      student_number_key: key,
      last_name: lastName,
      first_name: firstName,
      middle_name: readMapped(raw, mapping, "middle_name") || null,
      suffix: readMapped(raw, mapping, "suffix") || null,
      premed_course: readMapped(raw, mapping, "premed_course") || null,
      premed_school: readMapped(raw, mapping, "premed_school") || null,
      graduation_year: gradYear.year,
      gwa: readMapped(raw, mapping, "gwa") || null,
      nmat_score: nmatScore,
      nmat_year: nmatYear.year,
      notes: readMapped(raw, mapping, "notes") || null,
      curriculumCode: explicitCode || null,
      resolvedCurriculumId,
      resolvedCurriculumCode: resolvedCurriculumId
        ? (curriculaById.get(resolvedCurriculumId)?.code ?? null)
        : null,
      assignmentMethod,
      assignmentRuleId,
      resolutionOutcome,
      resolutionMessage,
      action,
      errors,
      warnings,
      changedFields,
      existingStudentId: existingRow?.id ?? null,
    });

    if (duplicateInFile) {
      // Keep the counter honest even though the row is already rejected.
    }
  });

  const createCount = rows.filter((r) => r.action === "CREATE").length;
  const updateCount = rows.filter((r) => r.action === "UPDATE").length;
  const skipCount = rows.filter((r) => r.action === "SKIP").length;
  const rejectCount = rows.filter((r) => r.action === "REJECT").length;

  return {
    sheetName: sheet.name,
    mode,
    detectedRowCount: sheet.rows.length,
    createCount,
    updateCount,
    skipCount,
    rejectCount,
    unresolvedCount: rows.filter((r) => r.action !== "REJECT" && r.resolutionOutcome === "UNRESOLVED").length,
    conflictCount: rows.filter((r) => r.resolutionOutcome === "CONFLICT").length,
    duplicateInFileCount: rows.filter((r) =>
      r.errors.some((e) => e.startsWith("Duplicate student number")),
    ).length,
    rows,
    canCommit: createCount + updateCount > 0,
  };
}

export interface StudentCommitResult {
  created: number;
  updated: number;
  skipped: number;
  rejected: number;
}

/**
 * Applies a validated preview inside one transaction, so an invalid file can
 * never partially corrupt existing records (SPEC 27).
 */
export async function commitStudentImport(
  client: DbClient,
  preview: StudentPreview,
  actorId: string | null,
  programId: string,
): Promise<StudentCommitResult> {
  let created = 0;
  let updated = 0;

  for (const row of preview.rows) {
    const ts = nowIso();
    if (row.action === "CREATE") {
      await client.run(
        `INSERT INTO students (
           id, student_number, student_number_key, last_name, first_name, middle_name, suffix,
           premed_course, premed_school, graduation_year, gwa, nmat_score, nmat_year,
           curriculum_id, curriculum_assignment_method, curriculum_assignment_rule_id,
           curriculum_override_reason, active, notes, last_assessed_at, updated_by, program_id,
           created_at, updated_at
         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, NULL, ?, ?, ?, ?)`,
        [
          newId(),
          row.student_number,
          row.student_number_key,
          row.last_name,
          row.first_name,
          row.middle_name,
          row.suffix,
          row.premed_course,
          row.premed_school,
          row.graduation_year,
          row.gwa,
          row.nmat_score,
          row.nmat_year,
          row.resolvedCurriculumId,
          row.assignmentMethod,
          row.assignmentRuleId,
          row.assignmentMethod === "MANUAL" ? "Curriculum specified in the imported file." : null,
          row.notes,
          actorId,
          programId,
          ts,
          ts,
        ],
      );
      created += 1;
    } else if (row.action === "UPDATE" && row.existingStudentId) {
      await client.run(
        `UPDATE students
            SET last_name = ?, first_name = ?, middle_name = ?, suffix = ?,
                premed_course = ?, premed_school = ?, graduation_year = ?, gwa = ?,
                nmat_score = ?, nmat_year = ?, notes = ?, updated_by = ?, updated_at = ?
          WHERE id = ?`,
        [
          row.last_name,
          row.first_name,
          row.middle_name,
          row.suffix,
          row.premed_course,
          row.premed_school,
          row.graduation_year,
          row.gwa,
          row.nmat_score,
          row.nmat_year,
          row.notes,
          actorId,
          ts,
          row.existingStudentId,
        ],
      );
      updated += 1;
    }
  }

  return {
    created,
    updated,
    skipped: preview.skipCount,
    rejected: preview.rejectCount,
  };
}