import "server-only";
import { getDb } from "@/lib/db";
import { COUNTING_COURSE_FILTER } from "@/lib/services/curricula";
import { programFilter, type AccessScope } from "@/lib/auth/scope";
import { computeOccpSafe, type AssessmentRow, type SafeOccpResult } from "@/lib/calculations/occp";
import type { AssessmentStatus, OccpYearLevel, YearLongCreditPolicy } from "@/types";

/**
 * Server-side completion summaries (SPEC 19, 26).
 *
 * The authoritative sources are always: the student's assigned curriculum, that
 * curriculum's course rows, and the student's assessment rows. Nothing derived
 * is trusted from the client, and no completion percentage is stored as a
 * source of truth — it is recomputed here on every read.
 */

export interface StudentCompletionSummary {
  student_id: string;
  curriculum_id: string | null;
  curriculum_code: string | null;
  curriculum_name: string | null;
  curriculum_status: string | null;
  total_program_units: number;
  credited_units: number;
  remaining_units: number;
  raw_completion_percentage: number;
  display_completion_percentage: number;
  occp_year_level: OccpYearLevel | null;
  credited_course_count: number;
  total_course_count: number;
  remaining_course_count: number;
  /** Courses that have no assessment row yet, or are still NOT_TAKEN. */
  not_encoded_count: number;
  validation_count: number;
  in_progress_count: number;
  failed_count: number;
  /** True when the student has no assigned curriculum at all. */
  unresolved: boolean;
  /** Set when the calculation could not be performed. */
  error: { code: string; message: string } | null;
}

interface CourseRow {
  id: string;
  curriculum_id: string;
  units: number;
  counts_toward_program_units: number;
  year_long_group_id: string | null;
  component_term: string | null;
  final_grade_term: string | null;
}

interface AssessmentDbRow {
  student_id: string;
  curriculum_course_id: string;
  status: AssessmentStatus;
  approved_credited_units_override: number | null;
}

interface StudentRow {
  id: string;
  curriculum_id: string | null;
}

function emptySummary(studentId: string): StudentCompletionSummary {
  return {
    student_id: studentId,
    curriculum_id: null,
    curriculum_code: null,
    curriculum_name: null,
    curriculum_status: null,
    total_program_units: 0,
    credited_units: 0,
    remaining_units: 0,
    raw_completion_percentage: 0,
    display_completion_percentage: 0,
    occp_year_level: null,
    credited_course_count: 0,
    total_course_count: 0,
    remaining_course_count: 0,
    not_encoded_count: 0,
    validation_count: 0,
    in_progress_count: 0,
    failed_count: 0,
    unresolved: true,
    error: null,
  };
}

function placeholders(n: number): string {
  return new Array(n).fill("?").join(", ");
}

/** Chunked IN() helper — SQLite caps bound parameters at 999 by default. */
async function fetchInChunks<T>(
  ids: string[],
  chunkSize: number,
  fn: (chunk: string[]) => Promise<T[]>,
): Promise<T[]> {
  const out: T[] = [];
  for (let i = 0; i < ids.length; i += chunkSize) {
    out.push(...(await fn(ids.slice(i, i + chunkSize))));
  }
  return out;
}

/**
 * Computes completion for many students in a fixed number of queries.
 * Used by the student list, the dashboard and the report exports so every
 * surface shows exactly the same numbers.
 */
export async function computeSummaries(
  studentIds: string[],
  displayPrecision = 2,
): Promise<Map<string, StudentCompletionSummary>> {
  const result = new Map<string, StudentCompletionSummary>();
  if (studentIds.length === 0) return result;

  const db = await getDb();

  const students = await fetchInChunks(studentIds, 400, (chunk) =>
    db.all<StudentRow>(`SELECT id, curriculum_id FROM students WHERE id IN (${placeholders(chunk.length)})`, chunk),
  );

  for (const s of students) result.set(s.id, emptySummary(s.id));

  const curriculumIds = [...new Set(students.map((s) => s.curriculum_id).filter((v): v is string => !!v))];
  if (curriculumIds.length === 0) return result;

  const curricula = await fetchInChunks(curriculumIds, 400, (chunk) =>
    db.all<{
      id: string;
      code: string;
      name: string;
      status: string;
      year_long_credit_policy: YearLongCreditPolicy;
    }>(
      `SELECT id, code, name, status, year_long_credit_policy FROM curricula WHERE id IN (${placeholders(chunk.length)})`,
      chunk,
    ),
  );
  const curriculumById = new Map(curricula.map((c) => [c.id, c]));

  const courses = await fetchInChunks(curriculumIds, 400, (chunk) =>
    db.all<CourseRow>(
      `SELECT id, curriculum_id, units, counts_toward_program_units,
              year_long_group_id, component_term, final_grade_term
         FROM curriculum_courses
        WHERE curriculum_id IN (${placeholders(chunk.length)}) AND active = 1`,
      chunk,
    ),
  );
  const coursesByCurriculum = new Map<string, CourseRow[]>();
  for (const course of courses) {
    const bucket = coursesByCurriculum.get(course.curriculum_id);
    if (bucket) bucket.push(course);
    else coursesByCurriculum.set(course.curriculum_id, [course]);
  }

  const assessments = await fetchInChunks(studentIds, 400, (chunk) =>
    db.all<AssessmentDbRow>(
      `SELECT student_id, curriculum_course_id, status, approved_credited_units_override
         FROM student_course_assessments
        WHERE student_id IN (${placeholders(chunk.length)})`,
      chunk,
    ),
  );
  const assessmentsByStudent = new Map<string, Map<string, AssessmentDbRow>>();
  for (const row of assessments) {
    let bucket = assessmentsByStudent.get(row.student_id);
    if (!bucket) {
      bucket = new Map();
      assessmentsByStudent.set(row.student_id, bucket);
    }
    bucket.set(row.curriculum_course_id, row);
  }

  for (const student of students) {
    const summary = result.get(student.id)!;
    if (!student.curriculum_id) continue;

    const curriculum = curriculumById.get(student.curriculum_id);
    const curriculumCourses = coursesByCurriculum.get(student.curriculum_id) ?? [];
    const studentAssessments = assessmentsByStudent.get(student.id) ?? new Map<string, AssessmentDbRow>();

    summary.unresolved = false;
    summary.curriculum_id = student.curriculum_id;
    summary.curriculum_code = curriculum?.code ?? null;
    summary.curriculum_name = curriculum?.name ?? null;
    summary.curriculum_status = curriculum?.status ?? null;

    const rows: AssessmentRow[] = curriculumCourses.map((course) => {
      const assessment = studentAssessments.get(course.id);
      return {
        curriculumCourseId: course.id,
        curriculumUnits: Number(course.units) || 0,
        status: assessment?.status ?? "NOT_TAKEN",
        countsTowardProgramUnits: course.counts_toward_program_units === 1,
        yearLongGroupId: course.year_long_group_id,
        isFinalComponent:
          !!course.year_long_group_id &&
          !!course.final_grade_term &&
          course.component_term === course.final_grade_term,
        approvedCreditedUnitsOverride: assessment?.approved_credited_units_override ?? null,
      };
    });

    const totalProgramUnits = rows
      .filter((r) => r.countsTowardProgramUnits)
      .reduce((sum, r) => sum + r.curriculumUnits, 0);

    for (const course of curriculumCourses) {
      const assessment = studentAssessments.get(course.id);
      const status = assessment?.status ?? "NOT_TAKEN";
      if (status === "NOT_TAKEN") summary.not_encoded_count += 1;
      else if (status === "FOR_VALIDATION") summary.validation_count += 1;
      else if (status === "IN_PROGRESS") summary.in_progress_count += 1;
      else if (status === "FAILED") summary.failed_count += 1;
    }

    const computed: SafeOccpResult = computeOccpSafe(totalProgramUnits, rows, {
      yearLongCreditPolicy: curriculum?.year_long_credit_policy ?? "ON_FINAL_PASS",
      displayPrecision,
    });

    if (!computed.ok) {
      summary.total_program_units = Math.round((totalProgramUnits + Number.EPSILON) * 1e6) / 1e6;
      summary.error = { code: computed.code, message: computed.message };
      continue;
    }

    const occp = computed.result;
    summary.total_program_units = occp.totalProgramUnits;
    summary.credited_units = occp.creditedUnits;
    summary.remaining_units = occp.remainingUnits;
    summary.raw_completion_percentage = occp.rawPercentage;
    summary.display_completion_percentage = occp.displayPercentage;
    summary.occp_year_level = occp.yearLevel;
    summary.credited_course_count = occp.countedCourseCount;
    summary.total_course_count = occp.totalCourseCount;
    summary.remaining_course_count = occp.remainingCourseIds.length;
  }

  return result;
}

export async function computeSummary(
  studentId: string,
  displayPrecision = 2,
): Promise<StudentCompletionSummary> {
  const map = await computeSummaries([studentId], displayPrecision);
  return map.get(studentId) ?? emptySummary(studentId);
}

/** Every active student id in scope, used by the dashboard and unfiltered reports. */
export async function allStudentIds(activeOnly = true, scope: AccessScope): Promise<string[]> {
  const db = await getDb();
  const program = programFilter(scope, "program_id");
  const rows = await db.all<{ id: string }>(
    `SELECT id FROM students
      WHERE ${program.sql}${activeOnly ? " AND active = 1" : ""}
      ORDER BY last_name, first_name`,
    program.params,
  );
  return rows.map((r) => r.id);
}

/**
 * Confirms that a curriculum's derived denominator matches what the calculation
 * engine will use. Exposed for the curriculum detail screen.
 */
export async function verifyDenominator(curriculumId: string): Promise<number> {
  const db = await getDb();
  const row = await db.get<{ total: number | null }>(
    `SELECT SUM(units) AS total FROM curriculum_courses
      WHERE curriculum_id = ? AND ${COUNTING_COURSE_FILTER}`,
    [curriculumId],
  );
  return Math.round(((row?.total ?? 0) + Number.EPSILON) * 1e6) / 1e6;
}