scripts/loaders/miami/climate_survey.ts

// Load the M-DCPS School Climate Survey (students, parents, staff; 2018-19 →
// 2024-25) → miami_school_year_metrics. Reads the per-item tables and the
// item crosswalk written by python -m pipeline_miami.parse.climate_survey
// (data/cities/miami/reference/parsed/survey/*.csv, survey_items.csv):
//   survey_{r}_{safety|leadership|teaching|resources}: mean favorable share
//     over the domain's CORE items (asked in every year for that respondent
//     group; domain and reverse-coding are properties of the canonical item,
//     so the item set is the same in every year), negatively worded items
//     scored by disagreement (positive items by agreement; unknown is never
//     favorable); requires at least half of the domain's
//     core items unsuppressed; denominator = the school's respondent count.
//   survey_{r}_recommend, survey_{r}_grade_ab from the school-level extras
//     where the workbook carries them (withheld below 10 respondents).
//   survey_{r}_response_rate: parents and staff against the workbook's
//     eligible count; students against the same year's October membership in
//     grades 4–12 (the workbooks' own eligible column alternates between
//     all-grade FTE and grades 4+ across years, review 2026-09-16 M6).
// Values outside [0, 100] are invalid and skipped (never clamped); the load
// note counts them.
// Run:  npx tsx scripts/loaders/miami/climate_survey.ts

import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { favorableSurveyShare } from "./_survey-score";
import { prisma, requireSource, startDataLoad, finishDataLoad, schoolUniverse, upsertFacts, runMain, type Fact } from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/climate_survey.ts";
const PARSED = path.join(process.cwd(), "data/cities/miami/reference/parsed/survey");
const ITEMS = path.join(process.cwd(), "data/cities/miami/reference/survey_items.csv");
const DOMAINS = ["safety", "leadership", "teaching", "resources"] as const;
const STUDENT_GRADES = ["4", "5", "6", "7", "8", "9", "10", "11", "12"];

type Row = Record<string, string>;
const num = (v: string | undefined) => (v === undefined || v === "" ? null : Number(v));
const valid = (v: number | null): v is number => v !== null && Number.isFinite(v) && v >= 0 && v <= 100;
const readCsv = (p: string) => parse(fs.readFileSync(p, "utf-8"), { columns: true, skip_empty_lines: true }) as Row[];

// Fetched workbooks that are not served (reasons in pipeline_miami/parse/climate_survey.py RETIRED_SPECS). Their
// cells are deleted here because nothing else prunes a source that no longer loads; the load history stays.
const RETIRED_SOURCES: Record<string, string> = {
  "mdcps_climate_survey_staff_2018-19": "no don't-know column; its disagreement column is not the later files' measure (retired 2026-09-16)",
};

async function main() {
  const universe = await schoolUniverse();
  const items = readCsv(ITEMS);
  const files = fs.readdirSync(PARSED).filter((f) => /^mdcps_climate_survey_\w+_\d{4}-\d{2}\.csv$/.test(f)).sort();
  for (const [sourceId, why] of Object.entries(RETIRED_SOURCES)) {
    if (files.includes(`${sourceId}.csv`)) throw new Error(`${sourceId} is retired (${why}) but a parsed table exists; re-run the parser`);
    const removed = await prisma.$executeRaw`DELETE FROM miami_school_year_metrics WHERE source_load_id IN (SELECT id FROM miami_data_loads WHERE source_id = ${sourceId})`;
    console.log(`[survey] ${sourceId}: retired (${why}); removed ${removed} served cells`);
  }
  const staffYears = new Set(files.flatMap((f) => /^mdcps_climate_survey_staff_(\d{4}-\d{2})\.csv$/.exec(f)?.slice(1) ?? []));
  const workload = items.filter((r) => r.respondent === "staff" && r.alias_group === "staff_overload");
  if (staffYears.size === 0 || workload.length !== staffYears.size || new Set(workload.map((r) => r.year)).size !== staffYears.size || workload.some((r) => !staffYears.has(r.year) || r.core !== "True" || r.domain !== "teaching")) {
    throw new Error("survey crosswalk is stale: regenerate all survey files with the reviewed staff workload alias before loading favorable-response-v2");
  }
  // (respondent, year, item_no) → { canonical, domain, reverse, core }
  const xw = new Map(items.map((r) => [`${r.respondent}|${r.year}|${r.item_no}`, { canonical: r.canonical, domain: r.domain, reverse: r.reverse === "True", core: r.core === "True" }]));
  // core canonicals per (respondent, domain): the composite's item set, identical in every year by construction
  const coreByDomain = new Map<string, Set<string>>();
  for (const r of items) if (r.core === "True") { const k = `${r.respondent}|${r.domain}`; (coreByDomain.get(k) ?? coreByDomain.set(k, new Set()).get(k)!).add(r.canonical); }
  // Consistency guard: every core canonical must appear in every year of its respondent group.
  const yearsByResp = new Map<string, Set<string>>();
  for (const r of items) (yearsByResp.get(r.respondent) ?? yearsByResp.set(r.respondent, new Set()).get(r.respondent)!).add(r.year);
  for (const [k, canon] of coreByDomain) {
    const resp = k.split("|")[0];
    for (const c of canon) for (const y of yearsByResp.get(resp)!) {
      if (!items.some((r) => r.respondent === resp && r.year === y && r.canonical === c)) throw new Error(`crosswalk: core item ${c} missing in ${y}`);
    }
  }

  for (const f of files) {
    const sourceId = f.replace(/\.csv$/, "");
    const m = /^mdcps_climate_survey_(\w+?)_(\d{4}-\d{2})$/.exec(sourceId)!;
    const resp = m[1], year = m[2];
    let src;
    try { src = requireSource(sourceId); } catch { console.warn(`[survey] ${sourceId}: workbook not fetched; skipped`); continue; }
    const rows = readCsv(path.join(PARSED, f));
    const extras = readCsv(path.join(PARSED, `${sourceId}.school.csv`));
    const nBySchool = new Map(extras.map((e) => [e.school_number.padStart(4, "0"), num(e.n_responses)]));
    // Students' response-rate denominator: same-year October membership in grades 4–12.
    const g412 = new Map<string, number>();
    if (resp === "students") {
      const cells = await prisma.miamiSchoolYearMetric.findMany({ where: { year, subgroup: "ALL", suppressed: false, metricKey: { in: STUDENT_GRADES.map((g) => `enrollment_oct_g${g}`) } }, select: { schoolNumber: true, value: true } });
      for (const c of cells) g412.set(c.schoolNumber, (g412.get(c.schoolNumber) ?? 0) + (c.value ?? 0));
    }
    // per school: domain → [scores over core items]
    const bySchool = new Map<string, { dom: Record<string, number[]>; suppressedItems: number }>();
    let unknownItems = 0, invalidItems = 0, invalidExtras = 0;
    for (const r of rows) {
      const num4 = r.school_number.padStart(4, "0");
      if (!universe.has(num4)) continue;
      const x = xw.get(`${resp}|${year}|${r.item_no}`);
      if (!x) { unknownItems++; continue; }
      const s = bySchool.get(num4) ?? { dom: {}, suppressedItems: 0 };
      bySchool.set(num4, s);
      if (r.suppressed === "True") { s.suppressedItems++; continue; }
      if (!x.core || !(DOMAINS as readonly string[]).includes(x.domain)) continue;
      const favorable = favorableSurveyShare(r, x.reverse);
      if (favorable === null) { invalidItems++; continue; }
      (s.dom[x.domain] ??= []).push(favorable);
    }
    const facts: Fact[] = [];
    for (const [num4, s] of bySchool) {
      const n = nBySchool.get(num4) ?? null;
      for (const d of DOMAINS) {
        const nCore = coreByDomain.get(`${resp}|${d}`)?.size ?? 0;
        const vals = s.dom[d] ?? [];
        if (nCore === 0) continue;
        if (vals.length * 2 < nCore) { facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_${d}`, value: null, suppressed: true, denominator: n }); continue; }
        facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_${d}`, value: vals.reduce((a, b) => a + b, 0) / vals.length, denominator: n });
      }
    }
    for (const e of extras) {
      const num4 = e.school_number.padStart(4, "0");
      if (!universe.has(num4)) continue;
      const n = num(e.n_responses);
      const sup = n !== null && n < 10;
      for (const [col, key] of [["recommend_yes_pct", "recommend"], ["grade_ab_pct", "grade_ab"]] as const) {
        const v = num(e[col]);
        if (v === null) continue;
        if (sup) { facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_${key}`, value: null, suppressed: true, denominator: n }); continue; }
        if (!valid(v)) { invalidExtras++; continue; }
        facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_${key}`, value: v, denominator: n });
      }
      // Response rate: students against October membership grades 4–12; parents and staff against the workbook's eligible count.
      let rate: number | null = null, denom: number | null = null;
      if (resp === "students") { denom = g412.get(num4) ?? null; rate = n !== null && denom && denom > 0 ? (n / denom) * 100 : null; }
      else { denom = num(e.n_eligible); rate = num(e.response_rate); }
      if (rate === null) continue;
      if (sup) { facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_response_rate`, value: null, suppressed: true, denominator: denom }); continue; }
      if (!valid(rate)) { invalidExtras++; continue; }
      facts.push({ schoolNumber: num4, year, metricKey: `survey_${resp}_response_rate`, value: rate, denominator: denom });
    }
    const load = await startDataLoad({ sourceId, sourceUrl: src.prov.url, sha256: src.prov.sha256, bytes: src.prov.bytes, yearCovered: year, scriptPath: SCRIPT_PATH,
      notes: `scoring favorable-response-v2 (agreement for positive items, disagreement for negative; unknown not favorable); parsed table ${f}; schools ${bySchool.size}; items not in crosswalk ${unknownItems}; missing or invalid favorable shares skipped ${invalidItems}; extras outside [0,100] skipped ${invalidExtras}; response-rate denominator: ${resp === "students" ? "October membership grades 4-12" : "workbook eligible count"}` });
    const res = await upsertFacts(facts, load.id);
    await finishDataLoad(load.id, { inserted: res.written, updated: 0, suppressed: res.suppressed });
    console.log(`[survey] ${sourceId.padEnd(38)} ${res.written} facts (${res.suppressed} suppressed); schools ${bySchool.size}; invalid items ${invalidItems}, extras ${invalidExtras}`);
  }
}

runMain(main);