import { describe, expect, it } from "vitest"; import { buildTemplate, buildXlsx, buildCsv, templateSheetSpec } from "@/lib/exports/workbook"; import { parseWorkbook, suggestMapping } from "@/lib/imports/workbook"; import { buildCurriculumPreview, CURRICULUM_COLUMNS } from "@/lib/imports/curriculum-import"; import { buildStudentPreview, STUDENT_COLUMNS } from "@/lib/imports/student-import"; import { ASSESSMENT_COLUMNS, buildAssessmentPreview } from "@/lib/imports/assessment-import"; import { normalizeCourseCode } from "@/lib/curriculum-resolver"; import type { CurriculumAssignmentRule } from "@/types"; /** * Round-trip tests: a template this system generates must be importable by this * system. This closes the loop between the export and import halves — a broken * header, a renamed column or a mangled example row would fail here. */ const rule: CurriculumAssignmentRule = { id: "r1", name: "2099 prefix", curriculum_id: "curr-x", rule_type: "PREFIX", prefix: "2099", regex_pattern: null, exact_student_number: null, numeric_start: null, numeric_end: null, priority: 100, active: 1, description: null, created_at: "2026-01-01T00:00:00.000Z", updated_at: "2026-01-01T00:00:00.000Z", }; describe("student import template round trip", () => { it("re-imports its own template and understands the example row", async () => { const buffer = await buildTemplate("STUDENT"); const workbook = await parseWorkbook(buffer, "OCCP_Student_Import_Template.xlsx"); expect(workbook.sheets).toHaveLength(1); const sheet = workbook.sheets[0]; const mapping = suggestMapping(sheet.headers, [...STUDENT_COLUMNS]); // Every required column must be auto-detected from our own headers. for (const column of STUDENT_COLUMNS.filter((c) => c.required)) { expect(mapping[column.key], `required column ${column.key} was not auto-mapped`).not.toBeNull(); } const preview = buildStudentPreview({ sheet, mapping, mode: "CREATE_ONLY", rules: [rule], normalization: "UPPERCASE_STRIP_SPACES_HYPHENS", existing: new Map(), curriculaByCode: new Map(), curriculaById: new Map([["curr-x", { id: "curr-x", code: "CURR-X" }]]), }); expect(preview.detectedRowCount).toBe(1); expect(preview.rejectCount).toBe(0); expect(preview.createCount).toBe(1); const row = preview.rows[0]; expect(row.student_number).toBe("2099-0001"); expect(row.last_name).toBe("Example"); expect(row.first_name).toBe("Fictional"); expect(row.graduation_year).toBe(2024); expect(row.nmat_score).toBe(88); expect(row.resolvedCurriculumCode).toBe("CURR-X"); }); }); describe("curriculum import template round trip", () => { it("re-imports its own template with no blocking errors", async () => { const buffer = await buildTemplate("CURRICULUM"); const workbook = await parseWorkbook(buffer, "OCCP_Curriculum_Import_Template.xlsx"); const sheet = workbook.sheets[0]; const mapping = suggestMapping(sheet.headers, [...CURRICULUM_COLUMNS]); for (const column of CURRICULUM_COLUMNS.filter((c) => c.required)) { expect(mapping[column.key], `required column ${column.key} was not auto-mapped`).not.toBeNull(); } const preview = buildCurriculumPreview(sheet, mapping); expect(preview.errorCount).toBe(0); expect(preview.canCommit).toBe(true); expect(preview.courseCount).toBe(1); expect(preview.totalUnits).toBe(6); const row = preview.rows[0]; expect(row.course_code).toBe("SAMPLE 1-A"); expect(row.intended_year).toBe(1); expect(row.term).toBe("1ST SEMESTER"); expect(row.lecture_units).toBe(3); expect(row.laboratory_units).toBe(3); expect(row.units).toBe(6); }); }); describe("assessment import template round trip", () => { it("re-imports its own template and applies the example row", async () => { const buffer = await buildTemplate("ASSESSMENT"); const workbook = await parseWorkbook(buffer, "OCCP_Assessment_Import_Template.xlsx"); const sheet = workbook.sheets[0]; const mapping = suggestMapping(sheet.headers, [...ASSESSMENT_COLUMNS]); for (const column of ASSESSMENT_COLUMNS.filter((c) => c.required)) { expect(mapping[column.key], `required column ${column.key} was not auto-mapped`).not.toBeNull(); } const preview = buildAssessmentPreview({ sheet, mapping, normalization: "UPPERCASE_STRIP_SPACES_HYPHENS", students: new Map([["20990001", { id: "s1", name: "Example, Fictional", curriculum_id: "curr-x" }]]), courses: new Map([ [`curr-x::${normalizeCourseCode("SAMPLE 1-A")}`, { id: "c1", title: "Example Subject", units: 6 }], ]), }); expect(preview.rejectCount).toBe(0); expect(preview.applyCount).toBe(1); expect(preview.rows[0].status).toBe("PASSED"); expect(preview.rows[0].grade).toBe("85"); expect(preview.rows[0].curriculumCourseId).toBe("c1"); }); }); describe("header row detection", () => { /** * Regression: institutional workbooks commonly carry a MERGED title banner * above the real header. A spreadsheet reader reports a merged cell's value in * every column of the range, so a naive "row with the most filled cells" * heuristic mistakes the banner for the header. */ it("skips a merged title banner and finds the real header row", async () => { const ExcelJS = (await import("exceljs")).default; const workbook = new ExcelJS.Workbook(); const sheet = workbook.addWorksheet("CURRICULUM"); sheet.getCell("A1").value = "MMC CAST COLLEGE OF MEDICINE — PROPOSED CURRICULUM"; sheet.mergeCells(1, 1, 1, 5); sheet.getCell("A2").value = "Effective Academic Year 2026-2027"; sheet.mergeCells(2, 1, 2, 5); sheet.addRow([]); sheet.addRow(["Course Code", "Subject Description", "Total Units", "Year", "Semester"]); sheet.addRow(["HSB 1-AG", "Normal Organ Systems (Structure-Gross)", 8, "FIRST YEAR", "1ST SEMESTER"]); sheet.addRow(["PH1", "Public Health I", 2, "FIRST YEAR", "1ST SEMESTER"]); const buffer = Buffer.from(await workbook.xlsx.writeBuffer()); const parsed = await parseWorkbook(buffer, "curriculum.xlsx"); const parsedSheet = parsed.sheets[0]; expect(parsedSheet.headers).toEqual([ "Course Code", "Subject Description", "Total Units", "Year", "Semester", ]); expect(parsedSheet.rows).toHaveLength(2); const preview = buildCurriculumPreview( parsedSheet, suggestMapping(parsedSheet.headers, [...CURRICULUM_COLUMNS]), ); expect(preview.errorCount).toBe(0); expect(preview.totalUnits).toBe(10); expect(preview.rows[0].intended_year).toBe(1); expect(preview.rows[0].term).toBe("1ST SEMESTER"); }); it("still reads a plain sheet whose header is the very first row", async () => { const buffer = await buildXlsx([ { name: "S", columns: [{ header: "Course Code" }, { header: "Units" }, { header: "Year" }], rows: [["A 1", 3, 1]] }, ]); const parsed = await parseWorkbook(buffer, "plain.xlsx"); expect(parsed.sheets[0].headers).toEqual(["Course Code", "Units", "Year"]); expect(parsed.sheets[0].headerRowNumber).toBe(1); expect(parsed.sheets[0].rows).toEqual([["A 1", "3", "1"]]); }); }); describe("export helpers", () => { it("round-trips an arbitrary sheet through XLSX and back", async () => { const buffer = await buildXlsx([ { name: "Data", columns: [{ header: "Code" }, { header: "Units" }], rows: [ ["ABC 1", 6], ["DEF 2", 4.5], ], }, ]); const workbook = await parseWorkbook(buffer, "data.xlsx"); expect(workbook.sheets[0].headers).toEqual(["Code", "Units"]); expect(workbook.sheets[0].rows).toEqual([ ["ABC 1", "6"], ["DEF 2", "4.5"], ]); }); it("neutralises spreadsheet formula injection in exported text", () => { const csv = buildCsv({ name: "x", columns: [{ header: "Notes" }], rows: [["=SUM(A1:A9)"], ["+1234"], ["safe text"]], }); expect(csv).toContain("'=SUM(A1:A9)"); expect(csv).toContain("'+1234"); expect(csv).toContain("safe text"); // The raw formula must never appear unquoted at the start of a field. expect(csv).not.toMatch(/(^|\r\n)=SUM/); }); it("marks required columns and flags the example row as fictional", () => { const spec = templateSheetSpec("STUDENT"); expect(spec.columns.some((c) => c.header.endsWith(" *"))).toBe(true); expect(spec.note).toMatch(/fictional/i); expect(spec.rows[0].join(" ")).toMatch(/FICTIONAL EXAMPLE ROW/); }); });