import { newId, nowIso, type DbClient } from "@/lib/db"; import { addValidationIssue, createCurriculumRecord, insertCurriculumCourse } from "@/lib/services/curricula"; import { APPROVED_COURSES, APPROVED_CURRICULUM_CODE, APPROVED_NON_UNIT_REQUIREMENTS, APPROVED_PRIMARY_EFFECTIVE_AY, APPROVED_PRIMARY_SHEET, APPROVED_SECONDARY_EFFECTIVE_AY, APPROVED_SECONDARY_SHEET, APPROVED_SOURCE_NAME, APPROVED_SOURCE_WORKBOOK, CLERKSHIP_DURATION_MONTHS, CLERKSHIP_REFERENCE_DAYS, EXPECTED_SEMESTER_TOTALS, EXPECTED_TOTAL_PROGRAM_UNITS, EXPECTED_UNIT_BEARING_ROWS, TERM_SECOND, WORKBOOK_FINAL_TOTAL_UNITS, WORKBOOK_SUMMARY_MEDICAL_COURSE_UNITS, } from "@/lib/curriculum/approved-dataset"; /** * "Import Embedded Approved Curriculum" (SPEC 53). * * Creates the exact 57-course structure from the supplied MMC CAST workbook in * DRAFT_REVIEW status, together with every source discrepancy recorded as a * validation issue. Publication requires an administrator to acknowledge those * issues; acknowledgement never deletes the validation history. * * The curriculum is intentionally NOT mapped to Curriculum 2023/2024/2025. */ export interface ApprovedImportResult { curriculumId: string; created: boolean; courseCount: number; totalUnits: number; nonUnitRequirementCount: number; issueCount: number; } export async function importApprovedCurriculum( client: DbClient, options: { code?: string; programId?: string | null } = {}, ): Promise { const code = options.code ?? APPROVED_CURRICULUM_CODE; const existing = await client.get<{ id: string }>("SELECT id FROM curricula WHERE code = ?", [code]); if (existing) { const counts = await client.get<{ n: number; total: number | null }>( "SELECT COUNT(*) AS n, SUM(units) AS total FROM curriculum_courses WHERE curriculum_id = ?", [existing.id], ); const issues = await client.get<{ n: number }>( "SELECT COUNT(*) AS n FROM curriculum_validation_issues WHERE curriculum_id = ?", [existing.id], ); return { curriculumId: existing.id, created: false, courseCount: Number(counts?.n ?? 0), totalUnits: Number(counts?.total ?? 0), nonUnitRequirementCount: APPROVED_NON_UNIT_REQUIREMENTS.length, issueCount: Number(issues?.n ?? 0), }; } const curriculumId = await createCurriculumRecord(client, { code, name: "Approved MMC CAST MD Curriculum (source workbook)", effective_year: 2026, version: "1", status: "DRAFT_REVIEW", year_long_credit_policy: "ON_FINAL_PASS", institutional_type: null, source_name: APPROVED_SOURCE_NAME, source_workbook: APPROVED_SOURCE_WORKBOOK, primary_effective_ay: APPROVED_PRIMARY_EFFECTIVE_AY, secondary_sheet_effective_ay: APPROVED_SECONDARY_EFFECTIVE_AY, mapping_status: "REQUIRES_ADMIN_CONFIRMATION", source_total_units_declared: WORKBOOK_FINAL_TOTAL_UNITS, clerkship_reference_days: CLERKSHIP_REFERENCE_DAYS, program_id: options.programId ?? null, notes: "Imported from the official MMC CAST workbook. Not yet mapped to an institutional curriculum type — " + "the source sheets carry conflicting effective academic years.", }); // --------------------------------------------------------------- courses let sortOrder = 0; let totalUnits = 0; for (const row of APPROVED_COURSES) { const isYearLong = row.yearLongGroup !== null; await insertCurriculumCourse(client, curriculumId, { course_code: row.code, course_title: row.title, units: row.total, lecture_units: row.lecture, laboratory_units: row.laboratory, hours_per_week: row.hoursPerWeek, intended_year: row.year, term: row.term, category: null, prerequisite_text: row.prerequisite, required: true, counts_toward_program_units: true, is_year_long_component: isYearLong, year_long_group_id: row.yearLongGroup, component_term: isYearLong ? row.term : null, // A year-long subject's grade is final at the end of the 2nd semester. final_grade_term: isYearLong ? TERM_SECOND : null, sort_order: sortOrder++, notes: null, source_sheet: APPROVED_PRIMARY_SHEET, source_row: row.sourceRow, source_notes: row.yearLongMarker ? "Workbook ** marker: one-year subject." : null, }); totalUnits += row.total; } // ------------------------------------------------ non-unit requirements let nonUnitOrder = 0; for (const requirement of APPROVED_NON_UNIT_REQUIREMENTS) { const ts = nowIso(); await client.run( `INSERT INTO non_unit_requirements ( id, curriculum_id, requirement_type, code, title, academic_year, term, required_hours, required_days, required_months, elective, elective_group, minimum_selections, prerequisite_text, sort_order, active, source_sheet, source_row, notes, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, 1, ?, NULL, ?, ?, ?)`, [ newId(), curriculumId, requirement.requirementType, requirement.code, requirement.title, requirement.academicYear, requirement.term, requirement.hours, null, requirement.months, requirement.elective ? 1 : 0, requirement.electiveGroup, requirement.minimumSelections, nonUnitOrder++, APPROVED_PRIMARY_SHEET, requirement.notes, ts, ts, ], ); } // ------------------------------------------------------ validation report const issues: { code: string; severity: "ERROR" | "WARNING" | "INFO"; message: string; details?: unknown }[] = []; issues.push({ code: "DETAILED_UNIT_SUM", severity: "INFO", message: `Detailed course-unit sum reproduces ${totalUnits} units across ${APPROVED_COURSES.length} unit-bearing rows.`, details: { semesterTotals: EXPECTED_SEMESTER_TOTALS, expectedRows: EXPECTED_UNIT_BEARING_ROWS, expectedTotal: EXPECTED_TOTAL_PROGRAM_UNITS, }, }); if (totalUnits !== EXPECTED_TOTAL_PROGRAM_UNITS || APPROVED_COURSES.length !== EXPECTED_UNIT_BEARING_ROWS) { issues.push({ code: "TRANSCRIPTION_MISMATCH", severity: "ERROR", message: `Imported structure does not reproduce the expected ${EXPECTED_UNIT_BEARING_ROWS} rows / ${EXPECTED_TOTAL_PROGRAM_UNITS} units (got ${APPROVED_COURSES.length} rows / ${totalUnits} units).`, }); } issues.push({ code: "MEDICAL_COURSES_SUMMARY_DISCREPANCY", severity: "WARNING", message: `The workbook's summary row states "Medical Courses — ${WORKBOOK_SUMMARY_MEDICAL_COURSE_UNITS} Units", but the six unit-bearing semester totals sum to ${totalUnits} units and the workbook's own final total also states ${WORKBOOK_FINAL_TOTAL_UNITS} units. Detailed course units were NOT altered to force a ${WORKBOOK_SUMMARY_MEDICAL_COURSE_UNITS}-unit sum. Production default: ${WORKBOOK_FINAL_TOTAL_UNITS} units.`, details: { summaryRow: WORKBOOK_SUMMARY_MEDICAL_COURSE_UNITS, detailedSum: totalUnits, workbookFinalTotal: WORKBOOK_FINAL_TOTAL_UNITS, }, }); issues.push({ code: "EFFECTIVE_AY_MISMATCH", severity: "WARNING", message: `The ${APPROVED_PRIMARY_SHEET} sheet states Effective Academic Year ${APPROVED_PRIMARY_EFFECTIVE_AY}, while the ${APPROVED_SECONDARY_SHEET} sheet states ${APPROVED_SECONDARY_EFFECTIVE_AY}. The institutional curriculum type was not inferred from these labels.`, }); issues.push({ code: "SUMMER_TITLE_MISMATCH_SECOND_YEAR", severity: "WARNING", message: `Second Year summer requirement CI1 is titled "Summer Enhancement Program" in ${APPROVED_PRIMARY_SHEET} but "Community Immersion" in ${APPROVED_SECONDARY_SHEET}. The primary sheet value was used.`, }); issues.push({ code: "SUMMER_CODE_MISMATCH_THIRD_YEAR", severity: "WARNING", message: `Third Year summer requirement is coded CI1 in ${APPROVED_PRIMARY_SHEET} but CI2 in ${APPROVED_SECONDARY_SHEET}. The primary sheet value was used.`, }); issues.push({ code: "ORPHAN_PREREQUISITE_REFERENCE", severity: "WARNING", message: 'Ethics 2-A lists prerequisite "Ethics 1", but no Ethics 1 course exists in the unit-bearing course list. No course was invented to resolve this reference.', details: { course: "Ethics 2-A", missingPrerequisite: "Ethics 1" }, }); issues.push({ code: "SECONDARY_SHEET_MISSING_DESCRIPTION", severity: "INFO", message: `PHYSIO 1-A has a blank subject description in ${APPROVED_SECONDARY_SHEET}. The ${APPROVED_PRIMARY_SHEET} title "Normal Organ Systems (Function)" was used.`, }); issues.push({ code: "CURRICULUM_TYPE_MAPPING_REQUIRED", severity: "WARNING", message: "This dataset is not yet mapped to Curriculum 2023, 2024 or 2025. An administrator must confirm the mapping before students can be auto-assigned to it. It must not be cloned into all three curriculum types.", }); issues.push({ code: "NON_UNIT_REQUIREMENTS_EXCLUDED", severity: "INFO", message: `${APPROVED_NON_UNIT_REQUIREMENTS.length} non-unit requirements (2 summer training requirements and the ${CLERKSHIP_DURATION_MONTHS}-month clinical clerkship, reference ${CLERKSHIP_REFERENCE_DAYS} days) are tracked separately and are excluded from the ${totalUnits}-unit denominator.`, }); const lecLabMismatch = APPROVED_COURSES.filter((c) => c.lecture + c.laboratory !== c.total); if (lecLabMismatch.length > 0) { issues.push({ code: "LEC_LAB_UNIT_MISMATCH", severity: "ERROR", message: `${lecLabMismatch.length} course(s) have lecture + laboratory units that do not equal total units.`, details: lecLabMismatch.map((c) => c.code), }); } else { issues.push({ code: "LEC_LAB_RECONCILED", severity: "INFO", message: `Lecture + laboratory units reconcile to total units for all ${APPROVED_COURSES.length} unit-bearing course rows.`, }); } for (const issue of issues) { await addValidationIssue(client, curriculumId, issue.code, issue.severity, issue.message, issue.details); } return { curriculumId, created: true, courseCount: APPROVED_COURSES.length, totalUnits, nonUnitRequirementCount: APPROVED_NON_UNIT_REQUIREMENTS.length, issueCount: issues.length, }; }