import "server-only";
import { CURRICULUM_COLUMNS } from "@/lib/imports/curriculum-import";
import { STUDENT_COLUMNS } from "@/lib/imports/student-import";
import { ASSESSMENT_COLUMNS } from "@/lib/imports/assessment-import";

/**
 * XLSX / CSV generation for reports and import templates (SPEC 23, 34).
 *
 * Every exported value comes from the same server-side computation used by the
 * screens, so an export can never disagree with what a user just saw.
 */

export interface SheetSpec {
  name: string;
  columns: { header: string; width?: number }[];
  rows: (string | number | null)[][];
  /** Optional note rendered above the header row. */
  note?: string;
}

function sanitizeCell(value: string | number | null): string | number | null {
  if (typeof value !== "string") return value;
  // Neutralise spreadsheet formula injection in exported text.
  if (/^[=+\-@\t\r]/.test(value)) return `'${value}`;
  return value;
}

export async function buildXlsx(sheets: SheetSpec[]): Promise<Buffer> {
  const ExcelJS = (await import("exceljs")).default;
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "OCCP System";
  workbook.created = new Date();

  for (const spec of sheets) {
    // Excel sheet names cannot exceed 31 chars or contain : \ / ? * [ ]
    const safeName = spec.name.replace(/[:\\/?*[\]]/g, "-").slice(0, 31);
    const sheet = workbook.addWorksheet(safeName);

    let headerRowIndex = 1;
    if (spec.note) {
      // Deliberately NOT merged: a spreadsheet reader reports a merged cell's
      // value in every column of the range, which makes a banner row look like
      // a header row on re-import.
      sheet.getCell("A1").value = spec.note;
      sheet.getCell("A1").font = { italic: true, size: 10, color: { argb: "FF6B7280" } };
      sheet.addRow([]);
      headerRowIndex = 3;
    }

    const headerRow = sheet.getRow(headerRowIndex);
    headerRow.values = spec.columns.map((c) => c.header);
    headerRow.font = { bold: true, color: { argb: "FFFFFFFF" } };
    headerRow.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF0F4C81" } };
    headerRow.alignment = { vertical: "middle", wrapText: true };
    headerRow.height = 24;
    headerRow.commit();

    spec.columns.forEach((column, index) => {
      sheet.getColumn(index + 1).width = column.width ?? Math.min(40, Math.max(12, column.header.length + 4));
    });

    for (const row of spec.rows) {
      sheet.addRow(row.map(sanitizeCell));
    }

    sheet.views = [{ state: "frozen", ySplit: headerRowIndex }];
    if (spec.rows.length > 0) {
      sheet.autoFilter = {
        from: { row: headerRowIndex, column: 1 },
        to: { row: headerRowIndex + spec.rows.length, column: spec.columns.length },
      };
    }
  }

  const buffer = await workbook.xlsx.writeBuffer();
  return Buffer.from(buffer);
}

export function buildCsv(spec: SheetSpec): string {
  const escape = (value: string | number | null): string => {
    const sanitized = sanitizeCell(value);
    if (sanitized === null || sanitized === undefined) return "";
    const text = String(sanitized);
    return /[",\n\r]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
  };
  const lines = [spec.columns.map((c) => escape(c.header)).join(",")];
  for (const row of spec.rows) lines.push(row.map(escape).join(","));
  return `${lines.join("\r\n")}\r\n`;
}

// -----------------------------------------------------------------------------
// Import templates (SPEC 34)
// -----------------------------------------------------------------------------

export const TEMPLATE_NAMES = {
  STUDENT: "OCCP_Student_Import_Template.xlsx",
  CURRICULUM: "OCCP_Curriculum_Import_Template.xlsx",
  ASSESSMENT: "OCCP_Assessment_Import_Template.xlsx",
} as const;

export type TemplateKind = keyof typeof TEMPLATE_NAMES;

const TEMPLATE_EXAMPLES: Record<TemplateKind, Record<string, string>> = {
  STUDENT: {
    student_number: "2099-0001",
    last_name: "Example",
    first_name: "Fictional",
    middle_name: "Sample",
    suffix: "",
    premed_course: "BS Sample Science",
    premed_school: "Sample University",
    graduation_year: "2024",
    gwa: "1.75",
    nmat_score: "88",
    nmat_year: "2024",
    curriculum_code: "",
    notes: "FICTIONAL EXAMPLE ROW — delete before importing real data.",
  },
  CURRICULUM: {
    course_code: "SAMPLE 1-A",
    course_title: "Example Subject Description",
    units: "6",
    intended_year: "1st Year",
    term: "1st Semester",
    lecture_units: "3",
    laboratory_units: "3",
    hours_per_week: "6",
    prerequisite_text: "Any B.S. Course",
    required: "Yes",
    counts_toward_program_units: "Yes",
    category: "",
    notes: "FICTIONAL EXAMPLE ROW — delete before importing real data.",
  },
  ASSESSMENT: {
    student_number: "2099-0001",
    course_code: "SAMPLE 1-A",
    grade: "85",
    status: "PASSED",
    credited_units: "",
    equivalent_course: "",
    equivalent_school: "",
    remarks: "FICTIONAL EXAMPLE ROW — delete before importing real data.",
  },
};

export function templateSheetSpec(kind: TemplateKind): SheetSpec {
  const definitions =
    kind === "STUDENT" ? STUDENT_COLUMNS : kind === "CURRICULUM" ? CURRICULUM_COLUMNS : ASSESSMENT_COLUMNS;
  const example = TEMPLATE_EXAMPLES[kind];

  return {
    name: kind === "STUDENT" ? "Students" : kind === "CURRICULUM" ? "Curriculum" : "Assessments",
    note:
      `OCCP System import template — ${kind.toLowerCase()}. Required columns: ` +
      `${definitions.filter((d) => d.required).map((d) => d.label).join(", ")}. ` +
      "Column order does not matter; you will map columns during import. The example row below is fictional — delete it.",
    columns: definitions.map((definition) => ({
      header: definition.required ? `${definition.label} *` : definition.label,
      width: Math.min(34, Math.max(14, definition.label.length + 6)),
    })),
    rows: [definitions.map((definition) => example[definition.key] ?? "")],
  };
}

export async function buildTemplate(kind: TemplateKind): Promise<Buffer> {
  return buildXlsx([templateSheetSpec(kind)]);
}