import "server-only";
import { getDb } from "@/lib/db";
import { computeSummaries, type StudentCompletionSummary } from "@/lib/services/completion";
import { listActiveRules } from "@/lib/services/students";
import { resolveCurriculumForStudentNumber } from "@/lib/curriculum-resolver";
import { getSettings } from "@/lib/settings";
import { programFilter, type AccessScope } from "@/lib/auth/scope";
import { OCCP_YEAR_LEVELS, type OccpYearLevel, type Student } from "@/types";

/** Dashboard aggregates (SPEC 21). All values are computed, never stored. */

export interface DashboardStudent {
  id: string;
  student_number: string;
  name: string;
  curriculum_code: string | null;
  summary: StudentCompletionSummary;
  last_assessed_at: string | null;
}

export interface CurriculumBucket {
  curriculum_id: string | null;
  code: string;
  name: string;
  status: string | null;
  count: number;
}

export interface DashboardData {
  totalStudents: number;
  inactiveStudents: number;
  byCurriculum: CurriculumBucket[];
  byYearLevel: Record<OccpYearLevel, number>;
  unresolvedCount: number;
  conflictCount: number;
  validationCount: number;
  notAssessedCount: number;
  calculationErrorCount: number;
  completionDistribution: { label: string; count: number }[];
  recentlyAssessed: DashboardStudent[];
  conflicts: { student: DashboardStudent; message: string }[];
  notAssessed: DashboardStudent[];
  needsValidation: DashboardStudent[];
  hasCurricula: boolean;
  hasPublishedCurricula: boolean;
  displayPrecision: number;
}

const BUCKETS = [
  { label: "0–24%", min: 0, max: 25 },
  { label: "25–49%", min: 25, max: 50 },
  { label: "50–74%", min: 50, max: 75 },
  { label: "75–100%", min: 75, max: 100.0001 },
];

export async function getDashboardData(scope: AccessScope): Promise<DashboardData> {
  const db = await getDb();
  const settings = await getSettings();

  const studentProgram = programFilter(scope, "s.program_id");
  const students = await db.all<Student & { curriculum_code: string | null }>(
    `SELECT s.*, c.code AS curriculum_code
       FROM students s
       LEFT JOIN curricula c ON c.id = s.curriculum_id
      WHERE s.active = 1 AND ${studentProgram.sql}`,
    studentProgram.params,
  );
  const inactiveProgram = programFilter(scope, "program_id");
  const inactiveRow = await db.get<{ n: number }>(
    `SELECT COUNT(*) AS n FROM students WHERE active = 0 AND ${inactiveProgram.sql}`,
    inactiveProgram.params,
  );

  const curriculaProgram = programFilter(scope, "program_id");
  const curricula = await db.all<{ id: string; code: string; name: string; status: string }>(
    `SELECT id, code, name, status FROM curricula
      WHERE status <> 'ARCHIVED' AND ${curriculaProgram.sql}
      ORDER BY effective_year DESC, code ASC`,
    curriculaProgram.params,
  );

  const summaries = await computeSummaries(
    students.map((s) => s.id),
    settings.display_precision,
  );

  const toDashboardStudent = (student: Student & { curriculum_code: string | null }): DashboardStudent => ({
    id: student.id,
    student_number: student.student_number,
    name: `${student.last_name}, ${student.first_name}`,
    curriculum_code: student.curriculum_code,
    summary: summaries.get(student.id)!,
    last_assessed_at: student.last_assessed_at,
  });

  const byYearLevel: Record<OccpYearLevel, number> = {
    "1st Year": 0,
    "2nd Year": 0,
    "3rd Year": 0,
    "4th Year": 0,
  };
  const distribution = BUCKETS.map((bucket) => ({ label: bucket.label, count: 0 }));
  const curriculumCounts = new Map<string, number>();

  let unresolvedCount = 0;
  let validationCount = 0;
  let notAssessedCount = 0;
  let calculationErrorCount = 0;

  for (const student of students) {
    const summary = summaries.get(student.id)!;
    if (summary.unresolved) unresolvedCount += 1;
    if (summary.error) calculationErrorCount += 1;
    if (summary.validation_count > 0) validationCount += 1;
    if (!summary.unresolved && summary.credited_course_count === 0 && !student.last_assessed_at) {
      notAssessedCount += 1;
    }
    if (student.curriculum_id) {
      curriculumCounts.set(student.curriculum_id, (curriculumCounts.get(student.curriculum_id) ?? 0) + 1);
    }
    if (!summary.unresolved && !summary.error && summary.occp_year_level) {
      byYearLevel[summary.occp_year_level] += 1;
      const index = BUCKETS.findIndex(
        (b) => summary.raw_completion_percentage >= b.min && summary.raw_completion_percentage < b.max,
      );
      if (index !== -1) distribution[index].count += 1;
    }
  }

  const byCurriculum: CurriculumBucket[] = curricula.map((c) => ({
    curriculum_id: c.id,
    code: c.code,
    name: c.name,
    status: c.status,
    count: curriculumCounts.get(c.id) ?? 0,
  }));
  if (unresolvedCount > 0) {
    byCurriculum.push({
      curriculum_id: null,
      code: "UNRESOLVED",
      name: "No curriculum assigned",
      status: null,
      count: unresolvedCount,
    });
  }

  // Conflicts are recomputed for unresolved students only — a conflict is a
  // live property of the current rule set, not a stored flag.
  const rules = await listActiveRules(scope);
  const conflicts: { student: DashboardStudent; message: string }[] = [];
  for (const student of students) {
    if (student.curriculum_id) continue;
    const resolution = resolveCurriculumForStudentNumber(student.student_number, rules, {
      normalization: settings.student_number_normalization,
    });
    if (resolution.outcome === "CONFLICT") {
      conflicts.push({ student: toDashboardStudent(student), message: resolution.message });
    }
  }

  const recentlyAssessed = students
    .filter((s) => s.last_assessed_at)
    .sort((a, b) => String(b.last_assessed_at).localeCompare(String(a.last_assessed_at)))
    .slice(0, 8)
    .map(toDashboardStudent);

  const notAssessed = students
    .filter((s) => !s.last_assessed_at && s.curriculum_id)
    .slice(0, 8)
    .map(toDashboardStudent);

  const needsValidation = students
    .filter((s) => (summaries.get(s.id)?.validation_count ?? 0) > 0)
    .slice(0, 8)
    .map(toDashboardStudent);

  return {
    totalStudents: students.length,
    inactiveStudents: Number(inactiveRow?.n ?? 0),
    byCurriculum,
    byYearLevel,
    unresolvedCount,
    conflictCount: conflicts.length,
    validationCount,
    notAssessedCount,
    calculationErrorCount,
    completionDistribution: distribution,
    recentlyAssessed,
    conflicts,
    notAssessed,
    needsValidation,
    hasCurricula: curricula.length > 0,
    hasPublishedCurricula: curricula.some((c) => c.status === "PUBLISHED"),
    displayPrecision: settings.display_precision,
  };
}

export const YEAR_LEVELS = OCCP_YEAR_LEVELS;