import "server-only";
import { getDb, newId, nowIso, type DbClient } from "@/lib/db";
import { normalizeStudentNumber, resolveCurriculumForStudentNumber } from "@/lib/curriculum-resolver";
import { getSettings } from "@/lib/settings";
import { programFilter, type AccessScope } from "@/lib/auth/scope";
import type { CurriculumAssignmentRule, Student } from "@/types";

/** Student data access + curriculum resolution glue (SPEC 9, 12, 22). */

export interface StudentListFilters {
  search?: string;
  curriculumId?: string;
  yearLevel?: string;
  assignment?: "ALL" | "UNRESOLVED" | "AUTO" | "MANUAL";
  validation?: "ALL" | "NEEDS_VALIDATION" | "NOT_ASSESSED";
  activeOnly?: boolean;
  page?: number;
  pageSize?: number;
  sort?: "name" | "student_number" | "completion" | "updated";
  direction?: "asc" | "desc";
}

export interface StudentListRow extends Student {
  curriculum_code: string | null;
  curriculum_name: string | null;
  program_code: string | null;
}

/**
 * Active assignment rules visible to the scope.
 *
 * A rule assigns a curriculum, so a scoped user only ever sees — and only ever
 * resolves against — rules pointing at their own program's curricula.
 */
export async function listActiveRules(
  scope: AccessScope,
  client?: DbClient,
): Promise<CurriculumAssignmentRule[]> {
  const db = client ?? (await getDb());
  const program = programFilter(scope, "c.program_id");
  return db.all<CurriculumAssignmentRule>(
    `SELECT r.* FROM curriculum_assignment_rules r
       JOIN curricula c ON c.id = r.curriculum_id
      WHERE r.active = 1 AND ${program.sql}
      ORDER BY r.priority DESC, r.name ASC`,
    program.params,
  );
}

export async function listAllRules(
  scope: AccessScope,
  client?: DbClient,
): Promise<CurriculumAssignmentRule[]> {
  const db = client ?? (await getDb());
  const program = programFilter(scope, "c.program_id");
  return db.all<CurriculumAssignmentRule>(
    `SELECT r.* FROM curriculum_assignment_rules r
       JOIN curricula c ON c.id = r.curriculum_id
      WHERE ${program.sql}
      ORDER BY r.priority DESC, r.name ASC`,
    program.params,
  );
}

export async function getStudent(id: string, client?: DbClient): Promise<Student | undefined> {
  const db = client ?? (await getDb());
  return db.get<Student>("SELECT * FROM students WHERE id = ?", [id]);
}

export async function getStudentByNumber(
  studentNumberKey: string,
  client?: DbClient,
): Promise<Student | undefined> {
  const db = client ?? (await getDb());
  return db.get<Student>("SELECT * FROM students WHERE student_number_key = ?", [studentNumberKey]);
}

/**
 * Paginated student list. Filtering that depends on the computed OCCP result
 * (year level) is applied by the caller after `computeSummaries`, because the
 * percentage is never stored as a source of truth.
 */
export async function listStudents(
  filters: StudentListFilters,
  scope: AccessScope,
): Promise<{ rows: StudentListRow[]; total: number }> {
  const db = await getDb();
  const where: string[] = [];
  const params: unknown[] = [];

  // Program scoping first: a scoped user must never see another program's rows.
  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.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 ? OR LOWER(s.last_name || ', ' || s.first_name) LIKE ? OR LOWER(s.first_name || ' ' || s.last_name) LIKE ?)",
    );
    params.push(term, term, term, term, term);
  }
  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.assignment && filters.assignment !== "ALL") {
    if (filters.assignment === "UNRESOLVED") {
      where.push("(s.curriculum_id IS NULL OR s.curriculum_assignment_method = 'UNRESOLVED')");
    } else {
      where.push("s.curriculum_assignment_method = ?");
      params.push(filters.assignment);
    }
  }
  if (filters.validation === "NEEDS_VALIDATION") {
    where.push(
      "EXISTS (SELECT 1 FROM student_course_assessments a WHERE a.student_id = s.id AND a.status = 'FOR_VALIDATION')",
    );
  } else if (filters.validation === "NOT_ASSESSED") {
    where.push(
      "NOT EXISTS (SELECT 1 FROM student_course_assessments a WHERE a.student_id = s.id AND a.status <> 'NOT_TAKEN')",
    );
  }

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const countRow = await db.get<{ n: number }>(`SELECT COUNT(*) AS n FROM students s ${whereSql}`, params);
  const total = Number(countRow?.n ?? 0);

  const direction = filters.direction === "desc" ? "DESC" : "ASC";
  let orderBy: string;
  switch (filters.sort) {
    case "student_number":
      orderBy = `s.student_number_key ${direction}`;
      break;
    case "updated":
      orderBy = `s.updated_at ${direction}`;
      break;
    // "completion" is sorted in memory by the caller, since it is derived.
    case "completion":
      orderBy = `s.last_name ASC, s.first_name ASC`;
      break;
    default:
      orderBy = `s.last_name ${direction}, s.first_name ${direction}`;
  }

  const pageSize = Math.min(200, Math.max(10, filters.pageSize ?? 25));
  const page = Math.max(1, filters.page ?? 1);
  const offset = (page - 1) * pageSize;

  // Completion sorting needs the full filtered set; other sorts paginate in SQL.
  const usingDerivedSort = filters.sort === "completion" || !!filters.yearLevel;
  const limitSql = usingDerivedSort ? "" : `LIMIT ${pageSize} OFFSET ${offset}`;

  const rows = await db.all<StudentListRow>(
    `SELECT s.*, c.code AS curriculum_code, c.name AS curriculum_name, pr.code AS program_code
       FROM students s
       LEFT JOIN curricula c ON c.id = s.curriculum_id
       LEFT JOIN programs pr ON pr.id = s.program_id
       ${whereSql}
      ORDER BY ${orderBy}
      ${limitSql}`,
    params,
  );

  return { rows, total };
}

export interface ResolveOutcome {
  curriculumId: string | null;
  method: "AUTO" | "UNRESOLVED";
  ruleId: string | null;
  outcome: "MATCHED" | "UNRESOLVED" | "CONFLICT";
  message: string;
  matchedRuleName: string | null;
  conflictingCurriculumIds: string[];
}

/** Runs the resolver for a student number using current settings and rules. */
export async function resolveForStudentNumber(
  studentNumber: string,
  scope: AccessScope,
  rules?: CurriculumAssignmentRule[],
): Promise<ResolveOutcome> {
  const settings = await getSettings();
  const activeRules = rules ?? (await listActiveRules(scope));
  const result = resolveCurriculumForStudentNumber(studentNumber, activeRules, {
    normalization: settings.student_number_normalization,
  });
  return {
    curriculumId: result.outcome === "MATCHED" ? result.curriculumId : null,
    method: result.outcome === "MATCHED" ? "AUTO" : "UNRESOLVED",
    ruleId: result.matchedRule?.id ?? null,
    outcome: result.outcome,
    message: result.message,
    matchedRuleName: result.matchedRule?.name ?? null,
    conflictingCurriculumIds: result.conflictingCurriculumIds,
  };
}

export async function normalizeNumber(raw: string): Promise<string> {
  const settings = await getSettings();
  return normalizeStudentNumber(raw, settings.student_number_normalization);
}

export interface CreateStudentValues {
  student_number: string;
  last_name: string;
  first_name: string;
  middle_name: string | null;
  suffix: string | null;
  premed_course: string | null;
  premed_school: string | null;
  graduation_year: number | null;
  gwa: string | null;
  nmat_score: number | null;
  nmat_year: number | null;
  notes: string | null;
  active: boolean;
}

export async function insertStudent(
  client: DbClient,
  values: CreateStudentValues,
  resolution: { curriculumId: string | null; method: string; ruleId: string | null },
  actorId: string | null,
  normalizedNumber: string,
  programId: string | null,
): Promise<string> {
  const id = newId();
  const ts = nowIso();
  await client.run(
    `INSERT INTO students (
       id, student_number, student_number_key, last_name, first_name, middle_name, suffix,
       premed_course, premed_school, graduation_year, gwa, nmat_score, nmat_year,
       curriculum_id, curriculum_assignment_method, curriculum_assignment_rule_id,
       curriculum_override_reason, active, notes, last_assessed_at, updated_by, program_id, created_at, updated_at
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, NULL, ?, ?, ?, ?)`,
    [
      id,
      values.student_number,
      normalizedNumber,
      values.last_name,
      values.first_name,
      values.middle_name,
      values.suffix,
      values.premed_course,
      values.premed_school,
      values.graduation_year,
      values.gwa,
      values.nmat_score,
      values.nmat_year,
      resolution.curriculumId,
      resolution.method,
      resolution.ruleId,
      values.active ? 1 : 0,
      values.notes,
      actorId,
      programId,
      ts,
      ts,
    ],
  );
  return id;
}

export function studentDisplayName(student: {
  last_name: string;
  first_name: string;
  middle_name?: string | null;
  suffix?: string | null;
}): string {
  const middle = student.middle_name ? ` ${student.middle_name}` : "";
  const suffix = student.suffix ? ` ${student.suffix}` : "";
  return `${student.last_name}, ${student.first_name}${middle}${suffix}`.trim();
}