// Build miami_schools from the FLDOE Master School ID file (the universe:
// every district-13 number, active + closed + future), enriched with the NCES
// CCD directory (federal id, level, charter cross-check), the county GIS
// Public School layer (region, permanent FISH capacity, lat/lng fallback) and
// the latest School Grades file (Title I, current grade, charter cross-check).
//
// Decisions M.2 / M.3 in docs/cities/miami/00_overview.md; code tables from the
// MSID Application Guidelines (Appendix B), findings.md F.12.
//
// Run:  npx tsx scripts/loaders/miami/directory.ts
// Idempotent: upserts on school_number.

import fs from "node:fs";
import { parse } from "csv-parse/sync";
import * as XLSX from "xlsx";
import {
  requireSource, startDataLoad, finishDataLoad, stageLoadOperation, batched, padSchoolNumber, runMain,
} from "./_lib";
import { streamZipCsv } from "./_zip";
import type { MiamiSchoolType, MiamiAdmissionCategory } from "@prisma/client";

const SCRIPT_PATH = "scripts/loaders/miami/directory.ts";
const MSID_ID = "fldoe_msid_all_schools";
const CCD_ID = "nces_ccd_directory_2023-24";
const GIS_ID = "mdc_gis_schoolsite";
const GRADES_ID = "fldoe_school_grades_2024-25";

// MSID Appendix B: grade code → grade combination. (Codes present in Dade plus
// the rest of the table so other districts would work too.)
const GRADE_CODES: Record<string, string> = {
  "1": "PK", "2": "KG", "3": "KG-1", "4": "PK-2", "5": "KG-2", "6": "KG-3", "7": "PK-3", "8": "PK-5", "9": "KG,3-5", "10": "KG,3-6",
  "11": "KG-4", "12": "PK-KG", "13": "KG,4-6", "14": "PK-12", "15": "KG-5", "16": "PK-4", "17": "KG,5-6", "18": "KG-6", "19": "KG,6",
  "20": "KG,1,6", "21": "KG,6-8", "22": "KG-7", "23": "KG,7", "24": "KG,7-12", "25": "KG-8", "26": "KG-9", "27": "PK-8", "28": "KG-12",
  "29": "PK-9", "30": "2", "31": "1-2", "32": "1-3", "33": "1-4", "34": "1-5", "35": "1-6", "36": "PK,3-5", "37": "PK,9-12", "38": "PK-KG,3-5",
  "39": "PK-KG,5-6", "40": "1-7", "41": "2-3", "42": "2-4", "43": "3-5", "44": "3-6", "45": "3-7", "46": "3-4", "47": "KG-11", "48": "PK-KG,4-6",
  "49": "PK,4-5", "50": "4", "51": "4-5", "52": "4-6", "53": "4-8", "54": "6", "55": "PK,6-12", "56": "PK-6", "57": "3-8", "58": "5", "59": "5-6",
  "60": "5-7", "61": "5-8", "62": "7-11", "63": "5-12", "64": "7-9,12", "65": "6,9-12", "66": "6-7", "67": "6-8", "68": "6-9", "69": "6-12",
  "70": "8-10", "71": "2-6", "72": "7", "73": "7-8", "74": "7-9", "75": "7-12", "76": "6-10", "77": "4-12", "78": "7-10", "79": "8-9", "80": "8-12",
  "81": "7-Adult", "82": "9", "83": "9-10", "84": "9-11", "85": "9-12", "86": "10-12", "87": "11-12", "88": "9-Adult", "90": "Adult", "99": "Unassigned",
  "100": "KG-10", "101": "1-8", "102": "2-8", "103": "6-11", "104": "PK-1", "105": "1-12", "106": "2-7", "107": "4-9", "108": "2-12", "109": "PK-7",
  "110": "6-Adult", "111": "12", "113": "3-10", "114": "3-12", "115": "11-Adult", "116": "5-11", "117": "12-Adult", "118": "3-Adult", "119": "PK-2,9-12",
  "120": "PK,6-8", "121": "2-5", "122": "PK,9-Adult",
};

function gradeSpan(code: string | null): { low: string | null; high: string | null; combo: string | null } {
  if (!code) return { low: null, high: null, combo: null };
  const combo = GRADE_CODES[String(Number(code))];
  if (!combo || combo === "Unassigned") return { low: null, high: null, combo: combo ?? null };
  const toks = combo.split(/[,\-]/).map((t) => t.trim()).filter(Boolean);
  return { low: toks[0] ?? null, high: toks[toks.length - 1] ?? null, combo };
}

// MSID TYPE (01 elementary / 02 middle / 03 senior high / 04 combination / 05 adult / 07 other)
// + grade combination → the level label used for peer-group hard filters.
function schoolLevel(type: string | null, combo: string | null): string | null {
  const t = type ? String(Number(type)) : null;
  if (t === "1") return "Elementary";
  if (t === "2") return "Middle";
  if (t === "3") return combo && /^(5|6|7)-12$/.test(combo) ? "6-12" : "High";
  if (t === "5") return "Adult";
  if (t === "7") return "Other";
  if (t === "4") {
    if (!combo) return "Combination";
    if (/^(PK|KG|1|2|3|4)(-|,).*8$/.test(combo) || combo === "KG,6-8" || combo === "PK,6-8") return "K-8";
    if (/12$/.test(combo)) return "K-12";
    return "Combination";
  }
  return null;
}

// PRIMARY_SERV_TYPE (R general / S special ed / B alternative / V CTE / A adult / O other)
// and SCHL_FUNC_SETTING (Z none / B adult gen ed / D DJJ / J jail / T CTE center /
// V virtual / L hospital-homebound / N migrant / H home ed / P FES program).
function schoolType(serv: string | null, func: string | null, type: string | null): MiamiSchoolType {
  const f = (func ?? "Z").trim();
  const s = (serv ?? "R").trim();
  const t = type ? String(Number(type)) : null;
  if (f === "V") return "VIRTUAL";
  if (f === "T") return "TECHNICAL_COLLEGE";
  if (f === "D" || f === "J") return "DJJ";
  if (f === "B" || t === "5" || s === "A") return "ADULT";
  if (f === "L" || f === "N" || f === "H" || f === "P") return "OTHER";
  if (t === "7") return "OTHER";
  if (s === "R") return "REGULAR";
  if (s === "S") return "ESE_CENTER";
  if (s === "B") return "ALTERNATIVE";
  if (s === "V") return "TECHNICAL_COLLEGE";
  if (s === "O") return "OTHER";
  return "OTHER";
}

function admission(isCharter: boolean, magnet: string | null, st: MiamiSchoolType): MiamiAdmissionCategory {
  if (isCharter) return "CHARTER_LOTTERY";
  if (magnet === "S") return "MAGNET_SCHOOL";
  if (magnet === "P") return "MAGNET_PROGRAM_HOST";
  if (st === "ALTERNATIVE") return "ALTERNATIVE";
  if (st === "REGULAR" || st === "ESE_CENTER") return "NEIGHBORHOOD";
  return "OTHER";
}

function parseDate(v: string | null): Date | null {
  if (!v) return null;
  const m = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/.exec(v.trim());
  if (!m) return null;
  return new Date(Date.UTC(Number(m[3]), Number(m[1]) - 1, Number(m[2])));
}

const nz = (v: unknown): string | null => {
  if (v === null || v === undefined) return null;
  const s = String(v).trim();
  return s === "" || s.toLowerCase() === "nan" ? null : s;
};

async function main() {
  const msid = requireSource(MSID_ID);
  const ccd = requireSource(CCD_ID);
  const gis = requireSource(GIS_ID);
  const grades = requireSource(GRADES_ID);

  // ── MSID: the universe ──
  const msidText = fs.readFileSync(msid.filePath, "latin1");
  const msidRows = parse(msidText, { columns: true, delimiter: "\t", quote: null, relax_column_count: true, skip_empty_lines: true, trim: true, bom: true }) as Record<string, string>[];
  const dade = msidRows.filter((r) => String(r.DISTRICT).trim() === "13");
  console.log(`[directory] MSID rows: ${msidRows.length}; district 13: ${dade.length}`);

  // ── CCD 2023-24: federal ids, level, charter flag ──
  const ccdRows = await streamZipCsv(ccd.filePath, /ccd_sch_029_.*\.csv$/i, (r) => r.ST_LEAID === "FL-13");
  const ccdByNum = new Map<string, Record<string, string>>();
  for (const r of ccdRows) {
    const num = padSchoolNumber(String(r.ST_SCHID).replace("FL-13-", ""));
    if (num) ccdByNum.set(num, r);
  }
  console.log(`[directory] CCD FL-13 rows: ${ccdRows.length}`);

  // ── County GIS: region, permanent capacity, coordinates ──
  const gisJson = JSON.parse(fs.readFileSync(gis.filePath, "utf-8")) as { features: { attributes: Record<string, unknown> }[] };
  const gisByNum = new Map<string, Record<string, unknown>>();
  for (const f of gisJson.features) {
    const num = padSchoolNumber(f.attributes.ID);
    if (num) gisByNum.set(num, f.attributes);
  }
  console.log(`[directory] GIS points: ${gisJson.features.length}`);

  // ── School Grades 2025: Title I, current grade, charter flag ──
  const wb = XLSX.readFile(grades.filePath);
  const sheet = wb.Sheets[wb.SheetNames[0]];
  const gradeRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, { range: 3, defval: null });
  const gradesByNum = new Map<string, Record<string, unknown>>();
  for (const r of gradeRows) {
    if (String(r["District Number"]).trim() !== "13") continue;
    const num = padSchoolNumber(r["School Number"]);
    if (num) gradesByNum.set(num, r);
  }
  console.log(`[directory] School Grades 2025 Dade rows: ${gradesByNum.size}`);

  const dataLoad = await startDataLoad({
    sourceId: MSID_ID, sourceUrl: msid.prov.url, sha256: msid.prov.sha256, bytes: msid.prov.bytes,
    yearCovered: "2025-26", scriptPath: SCRIPT_PATH,
    notes: `Enriched with ${CCD_ID} (${ccd.prov.sha256.slice(0, 12)}), ${GIS_ID} (${gis.prov.sha256.slice(0, 12)}), ${GRADES_ID} (${grades.prov.sha256.slice(0, 12)})`,
  });

  let upserted = 0;
  const charterDisagreements: string[] = [];
  const levelCounts = new Map<string, number>();
  await batched(dade, 25, async (batch) => {
    for (const r of batch) {
      const num = padSchoolNumber(r.SCHOOL);
      if (!num) continue;
      const name = nz(r.SCHOOL_NAME_LONG) ?? nz(r.SCHOOL_NAME_SHORT);
      if (!name) continue;
      const charterCode = nz(r.CHARTER_SCHL_STAT);
      const isCharter = !!charterCode && ["R", "C", "T", "B", "H"].includes(charterCode);
      const gs = gradeSpan(nz(r.GRADE_CODE));
      const type = schoolType(nz(r.PRIMARY_SERV_TYPE), nz(r.SCHL_FUNC_SETTING), nz(r.TYPE));
      const level = schoolLevel(nz(r.TYPE), gs.combo);
      const magnet = nz(r.MAGNET_STATUS);
      const c = ccdByNum.get(num);
      const g = gisByNum.get(num);
      const sg = gradesByNum.get(num);
      // Three-way charter check (MSID is canonical; disagreements logged).
      const ccdCharter = c ? c.CHARTER_TEXT === "Yes" : null;
      const sgCharter = sg ? String(sg["Charter School"] ?? "").toUpperCase() === "YES" : null;
      if ((ccdCharter !== null && ccdCharter !== isCharter) || (sgCharter !== null && sgCharter !== isCharter)) {
        charterDisagreements.push(`${num} ${name}: msid=${isCharter} ccd=${ccdCharter} grades=${sgCharter}`);
      }
      const activity = nz(r.ACTIVITY_CODE);
      const dateClosed = parseDate(nz(r.DATE_CLOSED));
      const lat = Number.parseFloat(nz(r.LATITUDE) ?? "") ;
      const lng = Number.parseFloat(nz(r.LONGITUDE) ?? "");
      const gisLat = g ? Number(g.LAT) : NaN;
      const gisLng = g ? Number(g.LON) : NaN;
      const openYear = nz(r.CHARTER_SCHL_OPEN) ? Number.parseInt(String(r.CHARTER_SCHL_OPEN).slice(0, 4), 10) : null;
      const fedNo = nz(r.FEDERAL_SCHL_NO);
      const ncesId = c ? String(c.NCESSCH) : fedNo && nz(r.FEDERAL_DIST_NO) === "1200390" ? `1200390${fedNo.padStart(5, "0")}` : null;
      const letter = sg ? nz(sg["Grade 2025"]) : null;
      levelCounts.set(`${type}|${level}`, (levelCounts.get(`${type}|${level}`) ?? 0) + 1);

      const fields = {
        ncesId,
        name,
        nameShort: nz(r.SCHOOL_NAME_SHORT),
        schoolType: type,
        isCharter,
        charterStatusCode: charterCode,
        charterOpenYear: openYear && Number.isFinite(openYear) ? openYear : null,
        admissionType: admission(isCharter, magnet, type),
        magnetStatus: magnet,
        schoolChoice: nz(r.CS_SCHOOL_CHOICE) === null ? null : r.CS_SCHOOL_CHOICE.trim() === "Y",
        titleI: sg ? String(sg["Title I"] ?? "").toUpperCase() === "YES" : false,
        schoolLevel: level,
        gradeLow: gs.low,
        gradeHigh: gs.high,
        region: g ? nz(g.REGION) : null,
        municipality: nz(r.PHYSICAL_CITY),
        streetAddress: nz(r.PHYSICAL_ADDRESS),
        zipCode: nz(r.PHYSICAL_ZIP)?.slice(0, 5) ?? null,
        latitude: Number.isFinite(lat) ? lat : Number.isFinite(gisLat) ? gisLat : null,
        longitude: Number.isFinite(lng) ? lng : Number.isFinite(gisLng) ? gisLng : null,
        activityCode: activity,
        dateOpened: parseDate(nz(r.DATE_OPENED)),
        dateClosed,
        currentSchoolGrade: letter,
        fishCapacityPermanent: g && Number(g.CAPACITY) > 0 ? Number(g.CAPACITY) : null,
        includeInDefaultComparisons: activity === "A" && type === "REGULAR",
        closedAt: activity === "C" ? dateClosed : null,
      };
      // fish_capacity_permanent is owned by fish_los.ts (state FISH report); the GIS CAPACITY attribute
      // only seeds a brand-new record so a directory re-run never reverts FISH values (review 2026-09-16, C11).
      const { fishCapacityPermanent: _seedCapacity, ...updateFields } = fields;
      void _seedCapacity;
      stageLoadOperation(dataLoad.id, (tx) => tx.miamiSchool.upsert({ where: { schoolNumber: num }, update: updateFields, create: { schoolNumber: num, ...fields } }));
      upserted++;
    }
  });

  await finishDataLoad(dataLoad.id, { inserted: upserted, updated: 0, suppressed: 0 },
    `${upserted} district-13 schools upserted; charter disagreements: ${charterDisagreements.length}`);
  console.log(`[directory] upserted ${upserted} schools`);
  console.log(`[directory] charter flag disagreements (MSID canonical): ${charterDisagreements.length}`);
  for (const d of charterDisagreements.slice(0, 20)) console.log("   ", d);
  console.log(`[directory] type|level counts:`);
  for (const [k, v] of [...levelCounts.entries()].sort()) console.log(`   ${k.padEnd(32)} ${v}`);
}

runMain(main);