import "server-only";
import { type DbClient } from "@/lib/db";
import { normalizeCourseCode, normalizeStudentNumber } from "@/lib/curriculum-resolver";
import type { StudentNumberNormalization } from "@/lib/settings/constants";
import { upsertAssessment } from "@/lib/services/assessment";
import { parseNumberCell, readMapped, type ColumnDefinition, type ParsedSheet } from "@/lib/imports/workbook";
import { ASSESSMENT_STATUSES, type AssessmentStatus } from "@/types";

/** Grade / credit migration import (SPEC 15). */

export const ASSESSMENT_COLUMNS: ColumnDefinition[] = [
  {
    key: "student_number",
    label: "Student Number",
    required: true,
    aliases: ["student no", "studentno", "student id", "id number"],
  },
  {
    key: "course_code",
    label: "Curriculum Course Code",
    required: true,
    aliases: ["course code", "subject code", "code", "subject"],
  },
  { key: "grade", label: "Grade", required: false, aliases: ["final grade", "rating", "mark"] },
  {
    key: "status",
    label: "Status",
    required: false,
    aliases: ["course status", "credit status", "remark status"],
    hint: "NOT_TAKEN, IN_PROGRESS, PASSED, CREDITED, FAILED or FOR_VALIDATION.",
  },
  {
    key: "credited_units",
    label: "Credited Units",
    required: false,
    aliases: ["approved credited units", "units credited", "credit units"],
    hint: "Only for special equivalency cases; capped at the curriculum requirement's own units.",
  },
  {
    key: "equivalent_course",
    label: "Equivalent / Source Course",
    required: false,
    aliases: ["source course", "equivalent course code", "transferred course", "equivalency"],
  },
  {
    key: "equivalent_school",
    label: "Source School",
    required: false,
    aliases: ["source school", "previous school", "from school"],
  },
  { key: "remarks", label: "Remarks", required: false, aliases: ["notes", "comment", "comments"] },
];

export type AssessmentRowAction = "APPLY" | "FOR_VALIDATION" | "REJECT";

export interface AssessmentPreviewRow {
  rowNumber: number;
  studentNumber: string;
  studentId: string | null;
  studentName: string | null;
  courseCode: string;
  curriculumCourseId: string | null;
  curriculumCourseTitle: string | null;
  curriculumCourseUnits: number | null;
  grade: string | null;
  status: AssessmentStatus;
  creditedUnitsOverride: number | null;
  equivalentCourse: string | null;
  equivalentSchool: string | null;
  remarks: string | null;
  action: AssessmentRowAction;
  errors: string[];
  warnings: string[];
}

export interface AssessmentPreview {
  sheetName: string;
  detectedRowCount: number;
  applyCount: number;
  validationCount: number;
  rejectCount: number;
  rows: AssessmentPreviewRow[];
  canCommit: boolean;
}

/**
 * Maps a free-text status cell onto a controlled status. Grades never silently
 * determine credit (SPEC 5 #8, 17) — an unrecognised value routes the row to
 * FOR_VALIDATION rather than guessing PASSED.
 */
export function parseStatusCell(raw: string): { status: AssessmentStatus; recognised: boolean } {
  const value = raw.trim().toUpperCase().replace(/[\s-]+/g, "_");
  if (!value) return { status: "FOR_VALIDATION", recognised: false };
  if ((ASSESSMENT_STATUSES as readonly string[]).includes(value)) {
    return { status: value as AssessmentStatus, recognised: true };
  }
  const synonyms: Record<string, AssessmentStatus> = {
    PASS: "PASSED",
    PASSED: "PASSED",
    P: "PASSED",
    COMPLETE: "PASSED",
    COMPLETED: "PASSED",
    CREDIT: "CREDITED",
    CREDITED: "CREDITED",
    TRANSFERRED: "CREDITED",
    ACCREDITED: "CREDITED",
    FAIL: "FAILED",
    FAILED: "FAILED",
    F: "FAILED",
    ONGOING: "IN_PROGRESS",
    IN_PROGRESS: "IN_PROGRESS",
    ENROLLED: "IN_PROGRESS",
    CURRENT: "IN_PROGRESS",
    NOT_TAKEN: "NOT_TAKEN",
    NONE: "NOT_TAKEN",
    NA: "NOT_TAKEN",
    N_A: "NOT_TAKEN",
  };
  const mapped = synonyms[value];
  if (mapped) return { status: mapped, recognised: true };
  return { status: "FOR_VALIDATION", recognised: false };
}

export interface BuildAssessmentPreviewContext {
  sheet: ParsedSheet;
  mapping: Record<string, number | null>;
  normalization: StudentNumberNormalization;
  /** normalised student number -> student */
  students: Map<string, { id: string; name: string; curriculum_id: string | null }>;
  /** `${curriculumId}::${normalisedCourseCode}` -> course */
  courses: Map<string, { id: string; title: string; units: number }>;
}

export function buildAssessmentPreview(context: BuildAssessmentPreviewContext): AssessmentPreview {
  const { sheet, mapping, normalization, students, courses } = context;
  const rows: AssessmentPreviewRow[] = [];
  const seen = new Map<string, number>();

  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 courseCode = readMapped(raw, mapping, "course_code");
    const grade = readMapped(raw, mapping, "grade") || null;
    const statusCell = readMapped(raw, mapping, "status");
    const parsedStatus = parseStatusCell(statusCell);

    if (!studentNumber) errors.push("Student number is required.");
    if (!courseCode) errors.push("Curriculum course code is required.");

    const key = normalizeStudentNumber(studentNumber, normalization);
    const student = key ? students.get(key) : undefined;
    if (studentNumber && !student) errors.push(`No student exists with student number "${studentNumber}".`);
    if (student && !student.curriculum_id) {
      errors.push("This student has no assigned curriculum. Assign a curriculum before importing assessments.");
    }

    let course: { id: string; title: string; units: number } | undefined;
    if (student?.curriculum_id && courseCode) {
      course = courses.get(`${student.curriculum_id}::${normalizeCourseCode(courseCode)}`);
      if (!course) {
        // Without a matching curriculum requirement there is nothing to credit,
        // so the row cannot be stored at all (SPEC 5 #5, 27).
        errors.push(
          `Course "${courseCode}" does not exist in the student's assigned curriculum. Map it to a curriculum requirement, or add the course to the curriculum first.`,
        );
      }
    }

    if (!parsedStatus.recognised && statusCell.trim()) {
      warnings.push(`Status "${statusCell}" was not recognised — routed to FOR_VALIDATION.`);
    } else if (!statusCell.trim()) {
      warnings.push("No status supplied — routed to FOR_VALIDATION rather than inferring credit from the grade.");
    }

    let creditedOverride: number | null = null;
    const creditedRaw = readMapped(raw, mapping, "credited_units");
    if (creditedRaw.trim()) {
      const parsed = parseNumberCell(creditedRaw);
      if (parsed === null || parsed < 0) {
        errors.push(`Credited units "${creditedRaw}" is not a valid number.`);
      } else if (course && parsed > course.units) {
        warnings.push(
          `Credited units ${parsed} exceeds the requirement's ${course.units} units and will be capped at ${course.units}.`,
        );
        creditedOverride = course.units;
      } else {
        creditedOverride = parsed;
      }
    }

    if (student && course) {
      const dedupeKey = `${student.id}::${course.id}`;
      const previous = seen.get(dedupeKey);
      if (previous !== undefined) {
        errors.push(`Duplicate row — this student/course pair already appears on row ${previous}.`);
      } else {
        seen.set(dedupeKey, rowNumber);
      }
    }

    const action: AssessmentRowAction =
      errors.length > 0 ? "REJECT" : parsedStatus.status === "FOR_VALIDATION" ? "FOR_VALIDATION" : "APPLY";

    rows.push({
      rowNumber,
      studentNumber,
      studentId: student?.id ?? null,
      studentName: student?.name ?? null,
      courseCode,
      curriculumCourseId: course?.id ?? null,
      curriculumCourseTitle: course?.title ?? null,
      curriculumCourseUnits: course?.units ?? null,
      grade,
      status: parsedStatus.status,
      creditedUnitsOverride: creditedOverride,
      equivalentCourse: readMapped(raw, mapping, "equivalent_course") || null,
      equivalentSchool: readMapped(raw, mapping, "equivalent_school") || null,
      remarks: readMapped(raw, mapping, "remarks") || null,
      action,
      errors,
      warnings,
    });
  });

  const applyCount = rows.filter((r) => r.action === "APPLY").length;
  const validationCount = rows.filter((r) => r.action === "FOR_VALIDATION").length;
  const rejectCount = rows.filter((r) => r.action === "REJECT").length;

  return {
    sheetName: sheet.name,
    detectedRowCount: sheet.rows.length,
    applyCount,
    validationCount,
    rejectCount,
    rows,
    canCommit: applyCount + validationCount > 0,
  };
}

export async function commitAssessmentImport(
  client: DbClient,
  preview: AssessmentPreview,
  actorId: string | null,
): Promise<{ applied: number; forValidation: number; rejected: number }> {
  let applied = 0;
  let forValidation = 0;

  for (const row of preview.rows) {
    if (row.action === "REJECT" || !row.studentId || !row.curriculumCourseId) continue;
    await upsertAssessment(
      client,
      row.studentId,
      row.curriculumCourseId,
      {
        status: row.status,
        grade_raw: row.grade,
        remarks: row.remarks,
        equivalent_source_school: row.equivalentSchool,
        equivalent_course_code: row.equivalentCourse,
        equivalent_course_title: null,
        equivalent_source_units: null,
        approved_credited_units_override: row.creditedUnitsOverride,
      },
      actorId,
    );
    if (row.action === "APPLY") applied += 1;
    else forValidation += 1;
  }

  return { applied, forValidation, rejected: preview.rejectCount };
}