import "server-only";
import { newId, nowIso, type DbClient } from "@/lib/db";
import { normalizeCourseCode } from "@/lib/curriculum-resolver";
import { addValidationIssue, createCurriculumRecord, insertCurriculumCourse } from "@/lib/services/curricula";
import {
  parseBooleanCell,
  parseNumberCell,
  readMapped,
  type ColumnDefinition,
  type ParsedSheet,
} from "@/lib/imports/workbook";

/** Curriculum Excel import (SPEC 13). */

export const CURRICULUM_COLUMNS: ColumnDefinition[] = [
  {
    key: "course_code",
    label: "Course Code",
    required: true,
    aliases: ["code", "subject code", "subj code", "course no", "course number", "catalog code"],
  },
  {
    key: "course_title",
    label: "Course Title",
    required: true,
    aliases: ["title", "subject", "subject description", "description", "course description", "course name"],
  },
  {
    key: "units",
    label: "Units",
    required: true,
    aliases: ["total units", "unit", "credit units", "credits", "no of units"],
  },
  {
    key: "intended_year",
    label: "Academic Year",
    required: true,
    aliases: ["year", "year level", "intended year", "yr", "academic level"],
    hint: 'Accepts "1", "1st Year", "FIRST YEAR" and similar.',
  },
  {
    key: "term",
    label: "Semester / Term",
    required: true,
    aliases: ["semester", "sem", "term", "period", "academic period"],
  },
  {
    key: "lecture_units",
    label: "Lecture Units",
    required: false,
    aliases: ["lec", "lec units", "lecture"],
  },
  {
    key: "laboratory_units",
    label: "Laboratory Units",
    required: false,
    aliases: ["lab", "lab units", "laboratory"],
  },
  {
    key: "hours_per_week",
    label: "Hours per Week",
    required: false,
    aliases: ["hours", "hrs", "hours/week", "hrs per week", "contact hours"],
  },
  {
    key: "prerequisite_text",
    label: "Prerequisite(s)",
    required: false,
    aliases: ["prerequisite", "prereq", "pre requisite", "prerequisites"],
  },
  {
    key: "required",
    label: "Required",
    required: false,
    aliases: ["is required", "mandatory"],
    hint: "Defaults to Yes when the column is absent.",
  },
  {
    key: "counts_toward_program_units",
    label: "Counts Toward Program Units",
    required: false,
    aliases: ["counts toward units", "counts", "include in total", "counted"],
    hint: "Defaults to the Required value when the column is absent.",
  },
  { key: "category", label: "Category", required: false, aliases: ["course category", "type", "group"] },
  { key: "notes", label: "Notes", required: false, aliases: ["remarks", "comment", "comments"] },
];

export interface CurriculumRowIssue {
  rowNumber: number;
  severity: "ERROR" | "WARNING";
  code: string;
  message: string;
}

export interface CurriculumPreviewRow {
  rowNumber: number;
  course_code: string;
  course_title: string;
  units: number | null;
  lecture_units: number | null;
  laboratory_units: number | null;
  hours_per_week: number | null;
  intended_year: number | null;
  term: string;
  category: string | null;
  prerequisite_text: string | null;
  required: boolean;
  counts_toward_program_units: boolean;
  notes: string | null;
  valid: boolean;
}

export interface CurriculumPreview {
  sheetName: string;
  detectedRowCount: number;
  validRowCount: number;
  courseCount: number;
  totalUnits: number;
  unitsByYearTerm: { intended_year: number; term: string; count: number; units: number }[];
  rows: CurriculumPreviewRow[];
  issues: CurriculumRowIssue[];
  errorCount: number;
  warningCount: number;
  /** True when the import may proceed (no ERROR-level issues). */
  canCommit: boolean;
}

const YEAR_WORDS: Record<string, number> = {
  first: 1,
  second: 2,
  third: 3,
  fourth: 4,
  fifth: 5,
  one: 1,
  two: 2,
  three: 3,
  four: 4,
  five: 5,
  i: 1,
  ii: 2,
  iii: 3,
  iv: 4,
  v: 5,
};

/** Splits a label into words and numbers, keeping ordinals like "1st" whole. */
function tokenizeLabel(value: string): string[] {
  return value.toLowerCase().match(/\d+(?:st|nd|rd|th)?|[a-z]+/g) ?? [];
}

function toYearNumber(token: string): number | null {
  const word = YEAR_WORDS[token];
  if (word !== undefined) return word;
  const digits = token.match(/^(\d+)/);
  if (!digits) return null;
  const parsed = Number(digits[1]);
  return Number.isFinite(parsed) && parsed >= 1 && parsed <= 10 ? parsed : null;
}

/**
 * Parses an academic-year label into a year number.
 *
 * Accepts "1", "1st Year", "FIRST YEAR", "Year 2", "II" — and, importantly, the
 * combined academic-period labels the MMC CAST workbook uses, such as
 * "SECOND YEAR - 1ST SEMESTER". A token attached to the word "year" always wins
 * over a bare number elsewhere in the label, so a semester ordinal is never
 * mistaken for the academic year.
 */
export function parseAcademicYear(raw: string): number | null {
  const tokens = tokenizeLabel(String(raw ?? "").trim());
  if (tokens.length === 0) return null;

  const yearIndex = tokens.indexOf("year");
  if (yearIndex !== -1) {
    // Look immediately before "year" first ("second year", "1st year").
    for (let i = yearIndex - 1; i >= 0 && i >= yearIndex - 2; i -= 1) {
      const year = toYearNumber(tokens[i]);
      if (year !== null) return year;
    }
    // Then after it ("year 2", "year level 2").
    for (let i = yearIndex + 1; i < tokens.length && i <= yearIndex + 2; i += 1) {
      const year = toYearNumber(tokens[i]);
      if (year !== null) return year;
    }
  }

  // No usable "year" keyword — fall back to the first number in the label…
  for (const token of tokens) {
    if (/^\d/.test(token)) {
      const year = toYearNumber(token);
      if (year !== null) return year;
    }
  }
  // …and finally to a number word or roman numeral.
  for (const token of tokens) {
    const year = YEAR_WORDS[token];
    if (year !== undefined) return year;
  }
  return null;
}

/** Normalises "1st sem", "SEMESTER 2", "Summer" into a canonical term label. */
export function parseTerm(raw: string): string {
  const value = raw.trim();
  if (!value) return "";
  const lower = value.toLowerCase();
  if (/summer|midyear|mid-year/.test(lower)) return "SUMMER";
  if (/(^|\D)1(st)?(\D|$)|first/.test(lower)) return "1ST SEMESTER";
  if (/(^|\D)2(nd)?(\D|$)|second/.test(lower)) return "2ND SEMESTER";
  if (/(^|\D)3(rd)?(\D|$)|third/.test(lower)) return "3RD TERM";
  return value.toUpperCase();
}

export function buildCurriculumPreview(
  sheet: ParsedSheet,
  mapping: Record<string, number | null>,
): CurriculumPreview {
  const issues: CurriculumRowIssue[] = [];
  const rows: CurriculumPreviewRow[] = [];
  const seenScopes = new Map<string, number>();

  for (const definition of CURRICULUM_COLUMNS) {
    if (definition.required && (mapping[definition.key] === null || mapping[definition.key] === undefined)) {
      issues.push({
        rowNumber: 0,
        severity: "ERROR",
        code: "MISSING_COLUMN_MAPPING",
        message: `The required column "${definition.label}" has not been mapped.`,
      });
    }
  }

  sheet.rows.forEach((raw, index) => {
    // +1 for the header row, +1 to make it a 1-based worksheet row number.
    const rowNumber = sheet.headerRowNumber + index + 1;
    const rowIssues: CurriculumRowIssue[] = [];

    const code = readMapped(raw, mapping, "course_code");
    const title = readMapped(raw, mapping, "course_title");
    const unitsRaw = readMapped(raw, mapping, "units");
    const units = parseNumberCell(unitsRaw);
    const lecture = parseNumberCell(readMapped(raw, mapping, "lecture_units"));
    const laboratory = parseNumberCell(readMapped(raw, mapping, "laboratory_units"));
    const hours = parseNumberCell(readMapped(raw, mapping, "hours_per_week"));
    const year = parseAcademicYear(readMapped(raw, mapping, "intended_year"));
    const term = parseTerm(readMapped(raw, mapping, "term"));
    const required = parseBooleanCell(readMapped(raw, mapping, "required"), true);
    const counts = parseBooleanCell(readMapped(raw, mapping, "counts_toward_program_units"), required);

    if (!code) {
      rowIssues.push({ rowNumber, severity: "ERROR", code: "BLANK_COURSE_CODE", message: "Course code is blank." });
    }
    if (!title) {
      rowIssues.push({ rowNumber, severity: "ERROR", code: "BLANK_COURSE_TITLE", message: "Course title is blank." });
    }
    if (unitsRaw.trim() === "") {
      rowIssues.push({ rowNumber, severity: "ERROR", code: "BLANK_UNITS", message: "Units value is blank." });
    } else if (units === null) {
      rowIssues.push({
        rowNumber,
        severity: "ERROR",
        code: "INVALID_UNITS",
        message: `"${unitsRaw}" is not a valid unit value.`,
      });
    } else if (units < 0) {
      rowIssues.push({ rowNumber, severity: "ERROR", code: "NEGATIVE_UNITS", message: "Units cannot be negative." });
    }
    if (year === null) {
      rowIssues.push({
        rowNumber,
        severity: "ERROR",
        code: "INVALID_ACADEMIC_YEAR",
        message: `Could not read an academic year from "${readMapped(raw, mapping, "intended_year")}".`,
      });
    }
    if (!term) {
      rowIssues.push({ rowNumber, severity: "ERROR", code: "BLANK_TERM", message: "Semester / term is blank." });
    }
    if (lecture !== null && laboratory !== null && units !== null) {
      const sum = Math.round((lecture + laboratory + Number.EPSILON) * 1e6) / 1e6;
      const total = Math.round((units + Number.EPSILON) * 1e6) / 1e6;
      if (sum !== total) {
        rowIssues.push({
          rowNumber,
          severity: "ERROR",
          code: "LEC_LAB_UNIT_MISMATCH",
          message: `Lecture (${lecture}) + laboratory (${laboratory}) = ${sum}, which does not equal total units (${total}).`,
        });
      }
    }

    // Uniqueness is scoped to (course code, year, term) — never global.
    if (code && year !== null && term) {
      const scopeKey = `${normalizeCourseCode(code)}::${year}::${term}`;
      const previous = seenScopes.get(scopeKey);
      if (previous !== undefined) {
        rowIssues.push({
          rowNumber,
          severity: "ERROR",
          code: "DUPLICATE_COURSE_CODE",
          message: `Course code "${code}" already appears for year ${year} / ${term} on row ${previous}.`,
        });
      } else {
        seenScopes.set(scopeKey, rowNumber);
      }
    }

    issues.push(...rowIssues);
    rows.push({
      rowNumber,
      course_code: code,
      course_title: title,
      units,
      lecture_units: lecture,
      laboratory_units: laboratory,
      hours_per_week: hours,
      intended_year: year,
      term,
      category: readMapped(raw, mapping, "category") || null,
      prerequisite_text: readMapped(raw, mapping, "prerequisite_text") || null,
      required,
      counts_toward_program_units: counts,
      notes: readMapped(raw, mapping, "notes") || null,
      valid: rowIssues.every((i) => i.severity !== "ERROR"),
    });
  });

  const validRows = rows.filter((r) => r.valid);
  const totalUnits = validRows
    .filter((r) => r.counts_toward_program_units)
    .reduce((sum, r) => sum + (r.units ?? 0), 0);

  const grouped = new Map<string, { intended_year: number; term: string; count: number; units: number }>();
  for (const row of validRows) {
    if (row.intended_year === null) continue;
    const key = `${row.intended_year}::${row.term}`;
    const bucket = grouped.get(key) ?? { intended_year: row.intended_year, term: row.term, count: 0, units: 0 };
    bucket.count += 1;
    if (row.counts_toward_program_units) bucket.units += row.units ?? 0;
    grouped.set(key, bucket);
  }

  const errorCount = issues.filter((i) => i.severity === "ERROR").length;
  const warningCount = issues.filter((i) => i.severity === "WARNING").length;

  return {
    sheetName: sheet.name,
    detectedRowCount: sheet.rows.length,
    validRowCount: validRows.length,
    courseCount: validRows.length,
    totalUnits: Math.round((totalUnits + Number.EPSILON) * 1e6) / 1e6,
    unitsByYearTerm: [...grouped.values()].sort(
      (a, b) => a.intended_year - b.intended_year || a.term.localeCompare(b.term),
    ),
    rows,
    issues,
    errorCount,
    warningCount,
    canCommit: errorCount === 0 && validRows.length > 0,
  };
}

export interface CurriculumCommitInput {
  code: string;
  name: string;
  effectiveYear: number | null;
  sourceWorkbook: string;
  sheetName: string;
  preview: CurriculumPreview;
  /** The academic program that will own this curriculum version. */
  programId: string;
}

export interface CurriculumCommitResult {
  curriculumId: string;
  code: string;
  courseCount: number;
  totalUnits: number;
}

/**
 * Creates a DRAFT curriculum version from a validated preview.
 * Never overwrites a PUBLISHED curriculum (SPEC 13 step 7).
 */
export async function commitCurriculumImport(
  client: DbClient,
  input: CurriculumCommitInput,
): Promise<CurriculumCommitResult> {
  const existing = await client.get<{ id: string; status: string }>(
    "SELECT id, status FROM curricula WHERE code = ?",
    [input.code],
  );
  if (existing) {
    throw new Error(
      `A curriculum with the code "${input.code}" already exists (status ${existing.status}). ` +
        "Choose a new curriculum code — published curricula are never silently overwritten.",
    );
  }

  const curriculumId = await createCurriculumRecord(client, {
    code: input.code,
    name: input.name,
    effective_year: input.effectiveYear,
    version: "1",
    status: "DRAFT",
    program_id: input.programId,
    source_workbook: input.sourceWorkbook,
    source_name: input.sheetName,
    mapping_status: "REQUIRES_ADMIN_CONFIRMATION",
    notes: `Imported from "${input.sourceWorkbook}" worksheet "${input.sheetName}".`,
  });

  let order = 0;
  let totalUnits = 0;
  for (const row of input.preview.rows) {
    if (!row.valid || row.intended_year === null || row.units === null) continue;
    await insertCurriculumCourse(client, curriculumId, {
      course_code: row.course_code,
      course_title: row.course_title,
      units: row.units,
      lecture_units: row.lecture_units,
      laboratory_units: row.laboratory_units,
      hours_per_week: row.hours_per_week,
      intended_year: row.intended_year,
      term: row.term,
      category: row.category,
      prerequisite_text: row.prerequisite_text,
      required: row.required,
      counts_toward_program_units: row.counts_toward_program_units,
      sort_order: order++,
      notes: row.notes,
      source_sheet: input.sheetName,
      source_row: row.rowNumber,
    });
    if (row.counts_toward_program_units) totalUnits += row.units;
  }

  await addValidationIssue(
    client,
    curriculumId,
    "IMPORTED_FROM_WORKBOOK",
    "INFO",
    `Imported ${order} course rows totalling ${totalUnits} program units from "${input.sourceWorkbook}" / "${input.sheetName}".`,
  );
  for (const issue of input.preview.issues.filter((i) => i.severity === "WARNING")) {
    await addValidationIssue(
      client,
      curriculumId,
      issue.code,
      "WARNING",
      `Row ${issue.rowNumber}: ${issue.message}`,
    );
  }
  await addValidationIssue(
    client,
    curriculumId,
    "CURRICULUM_TYPE_MAPPING_REQUIRED",
    "WARNING",
    "This imported curriculum is not yet mapped to an institutional curriculum type (2023 / 2024 / 2025). An administrator must confirm the mapping.",
  );

  return {
    curriculumId,
    code: input.code,
    courseCount: order,
    totalUnits: Math.round((totalUnits + Number.EPSILON) * 1e6) / 1e6,
  };
}

export async function recordImportJob(
  client: DbClient,
  values: {
    importType: "CURRICULUM" | "STUDENT" | "ASSESSMENT";
    fileName: string;
    status: string;
    totalRows: number;
    acceptedRows: number;
    rejectedRows: number;
    uploadedBy: string | null;
    errorSummary?: unknown;
  },
): Promise<string> {
  const id = newId();
  await client.run(
    `INSERT INTO import_jobs (
       id, import_type, file_name, status, total_rows, accepted_rows, rejected_rows,
       uploaded_by, error_summary_json, created_at, completed_at
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id,
      values.importType,
      values.fileName,
      values.status,
      values.totalRows,
      values.acceptedRows,
      values.rejectedRows,
      values.uploadedBy,
      values.errorSummary === undefined ? null : JSON.stringify(values.errorSummary),
      nowIso(),
      nowIso(),
    ],
  );
  return id;
}