import "server-only";
import { getDb } from "@/lib/db";
import { computeSummaries, type StudentCompletionSummary } from "@/lib/services/completion";
import { getSettings } from "@/lib/settings";
import { programFilter, type AccessScope } from "@/lib/auth/scope";
import type { SheetSpec } from "@/lib/exports/workbook";
import type { OccpYearLevel, Student } from "@/types";

/** OCCP Master List report (SPEC 23). */

export interface ReportFilters {
  curriculumId?: string;
  yearLevel?: string;
  completionMin?: number;
  completionMax?: number;
  activeOnly?: boolean;
  search?: string;
}

export interface MasterListRow {
  student: Student & { curriculum_code: string | null; curriculum_name: string | null };
  summary: StudentCompletionSummary;
}

export async function buildMasterList(
  filters: ReportFilters,
  scope: AccessScope,
): Promise<MasterListRow[]> {
  const db = await getDb();
  const settings = await getSettings();

  const where: string[] = [];
  const params: unknown[] = [];

  const program = programFilter(scope, "s.program_id");
  where.push(program.sql);
  params.push(...program.params);

  if (filters.activeOnly !== false) where.push("s.active = 1");
  if (filters.curriculumId && filters.curriculumId !== "ALL") {
    if (filters.curriculumId === "NONE") where.push("s.curriculum_id IS NULL");
    else {
      where.push("s.curriculum_id = ?");
      params.push(filters.curriculumId);
    }
  }
  if (filters.search?.trim()) {
    const term = `%${filters.search.trim().toLowerCase()}%`;
    where.push("(LOWER(s.student_number) LIKE ? OR LOWER(s.last_name) LIKE ? OR LOWER(s.first_name) LIKE ?)");
    params.push(term, term, term);
  }

  const rows = await db.all<Student & { curriculum_code: string | null; curriculum_name: string | null }>(
    `SELECT s.*, c.code AS curriculum_code, c.name AS curriculum_name
       FROM students s
       LEFT JOIN curricula c ON c.id = s.curriculum_id
       ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
      ORDER BY s.last_name ASC, s.first_name ASC`,
    params,
  );

  const summaries = await computeSummaries(
    rows.map((r) => r.id),
    settings.display_precision,
  );

  let result: MasterListRow[] = rows.map((student) => ({
    student,
    summary: summaries.get(student.id)!,
  }));

  // Derived filters are applied after computation, because completion is never
  // stored as a source of truth.
  if (filters.yearLevel && filters.yearLevel !== "ALL") {
    if (filters.yearLevel === "UNRESOLVED") {
      result = result.filter((r) => r.summary.unresolved);
    } else {
      result = result.filter((r) => r.summary.occp_year_level === (filters.yearLevel as OccpYearLevel));
    }
  }
  if (filters.completionMin !== undefined) {
    result = result.filter((r) => r.summary.raw_completion_percentage >= filters.completionMin!);
  }
  if (filters.completionMax !== undefined) {
    result = result.filter((r) => r.summary.raw_completion_percentage <= filters.completionMax!);
  }

  return result;
}

function formatDate(iso: string | null): string {
  if (!iso) return "";
  const date = new Date(iso);
  if (Number.isNaN(date.getTime())) return "";
  return date.toISOString().slice(0, 10);
}

export function masterListSheetSpec(rows: MasterListRow[], generatedFor: string): SheetSpec {
  return {
    name: "OCCP Master List",
    note:
      `OCCP Master List — generated ${new Date().toISOString().slice(0, 19).replace("T", " ")} UTC by ${generatedFor}. ` +
      "Completion percentage and OCCP year level are computed by the server from the assigned curriculum and " +
      "recorded course credits. Confidential academic records — handle accordingly.",
    columns: [
      { header: "Student Number", width: 16 },
      { header: "Last Name", width: 18 },
      { header: "First Name", width: 18 },
      { header: "Middle Name", width: 16 },
      { header: "Suffix", width: 9 },
      { header: "Pre-Med Course", width: 24 },
      { header: "Pre-Med School", width: 26 },
      { header: "Year Graduated", width: 15 },
      { header: "GWA", width: 9 },
      { header: "NMAT Score", width: 12 },
      { header: "NMAT Year", width: 11 },
      { header: "Assigned Curriculum", width: 34 },
      { header: "Assignment Method", width: 18 },
      { header: "Total Program Units", width: 18 },
      { header: "Credited Units", width: 14 },
      { header: "Remaining Units", width: 15 },
      { header: "Completion %", width: 13 },
      { header: "OCCP Year Level", width: 16 },
      { header: "Courses Credited", width: 16 },
      { header: "Courses Remaining", width: 17 },
      { header: "For Validation", width: 14 },
      { header: "Active", width: 8 },
      { header: "Last Assessed", width: 14 },
    ],
    rows: rows.map(({ student, summary }) => [
      student.student_number,
      student.last_name,
      student.first_name,
      student.middle_name ?? "",
      student.suffix ?? "",
      student.premed_course ?? "",
      student.premed_school ?? "",
      student.graduation_year ?? "",
      student.gwa ?? "",
      student.nmat_score ?? "",
      student.nmat_year ?? "",
      student.curriculum_code ? `${student.curriculum_code} — ${student.curriculum_name ?? ""}`.trim() : "UNRESOLVED",
      student.curriculum_assignment_method,
      summary.unresolved ? "" : summary.total_program_units,
      summary.unresolved ? "" : summary.credited_units,
      summary.unresolved ? "" : summary.remaining_units,
      summary.unresolved || summary.error ? "" : summary.display_completion_percentage,
      summary.error ? `ERROR: ${summary.error.code}` : (summary.occp_year_level ?? "UNRESOLVED"),
      summary.unresolved ? "" : summary.credited_course_count,
      summary.unresolved ? "" : summary.remaining_course_count,
      summary.validation_count,
      student.active ? "Yes" : "No",
      formatDate(student.last_assessed_at),
    ]),
  };
}