scripts/analysis/export-dataset.ts

// Export a reproducible analysis dataset for the clustering/decile bake-off.
// Read-only against the warehouse. Writes CSVs to scripts/analysis/data/.
//
//   school_year_panel.csv  — one row per (dbn, year): demographic feature vector
//                            + same-year outcomes (ELA/Math proficiency, grad
//                            rate, chronic absenteeism, college readiness).
//   school_current.csv     — current proclivity decile/score + comparison-group
//                            id per school (the production methods, for head-to-head).
//   comparison_groups.csv  — current DERIVED/NYCENET group id -> peer dbns.
//
// Long-format metrics are pivoted to wide here so the Python modeling is simple.
import { prisma } from "../loaders/_lib";
import fs from "node:fs";
import path from "node:path";

const OUT_DIR = path.join("scripts", "analysis", "data");

const OUTCOME_KEYS = [
  "ela_all_proficiency",
  "math_all_proficiency",
  "graduation_rate_4yr",
  "chronic_absenteeism_rate",
  "college_career_readiness",
] as const;

const DEMOG_COLS = [
  "totalEnrollment",
  "pctBlack",
  "pctHispanic",
  "pctWhite",
  "pctAsian",
  "pctMultiRacial",
  "pctOther",
  "pctEll",
  "pctSwd",
  "pctEconDis",
  "pctTempHousing",
  "pctFemale",
] as const;

function csvEscape(v: unknown): string {
  if (v === null || v === undefined) return "";
  const s = String(v);
  return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

function writeCsv(file: string, header: string[], rows: unknown[][]) {
  const lines = [header.join(",")];
  for (const r of rows) lines.push(r.map(csvEscape).join(","));
  fs.writeFileSync(file, lines.join("\n"));
  console.log(`  wrote ${file}  (${rows.length} rows)`);
}

async function main() {
  fs.mkdirSync(OUT_DIR, { recursive: true });

  // ---- Schools (static attributes) ----
  const schools = await prisma.school.findMany({
    select: {
      dbn: true,
      name: true,
      borough: true,
      district: true,
      schoolType: true,
      gradeBand: true,
      admissionCategory: true,
      includeInDefaultComparisons: true,
      proclivityDecile: true,
      proclivityScore: true,
      comparisonGroupId: true,
    },
  });
  const schoolMeta = new Map(schools.map((s) => [s.dbn, s]));
  console.log(`Schools: ${schools.length}`);

  // ---- Demographics (all years) ----
  const dem = await prisma.schoolYearDemographics.findMany({
    select: {
      schoolDbn: true,
      year: true,
      totalEnrollment: true,
      pctBlack: true,
      pctHispanic: true,
      pctWhite: true,
      pctAsian: true,
      pctMultiRacial: true,
      pctOther: true,
      pctEll: true,
      pctSwd: true,
      pctEconDis: true,
      pctTempHousing: true,
      pctFemale: true,
    },
  });
  console.log(`Demographics rows: ${dem.length}`);

  // ---- Outcomes (ALL subgroup, all years), pivoted ----
  const metricRows = await prisma.schoolYearMetric.findMany({
    where: { metricKey: { in: OUTCOME_KEYS as unknown as string[] }, subgroup: "ALL" },
    select: { schoolDbn: true, year: true, metricKey: true, value: true, suppressed: true },
  });
  console.log(`Outcome metric rows: ${metricRows.length}`);
  // (dbn|year) -> { metricKey: value }
  const outBySY = new Map<string, Record<string, number | null>>();
  for (const m of metricRows) {
    const k = `${m.schoolDbn}|${m.year}`;
    const o = outBySY.get(k) ?? {};
    o[m.metricKey] = m.suppressed ? null : m.value;
    outBySY.set(k, o);
  }

  // ---- Panel ----
  const panelHeader = [
    "dbn",
    "year",
    "name",
    "borough",
    "district",
    "school_type",
    "grade_band",
    "admission_category",
    "include_in_default_comparisons",
    ...DEMOG_COLS.map((c) => c.replace(/[A-Z]/g, (m) => "_" + m.toLowerCase())),
    ...OUTCOME_KEYS,
  ];
  const panelRows: unknown[][] = [];
  for (const d of dem) {
    const meta = schoolMeta.get(d.schoolDbn);
    if (!meta) continue;
    const out = outBySY.get(`${d.schoolDbn}|${d.year}`) ?? {};
    panelRows.push([
      d.schoolDbn,
      d.year,
      meta.name,
      meta.borough,
      meta.district,
      meta.schoolType,
      meta.gradeBand,
      meta.admissionCategory,
      meta.includeInDefaultComparisons,
      ...DEMOG_COLS.map((c) => (d as Record<string, unknown>)[c]),
      ...OUTCOME_KEYS.map((k) => out[k] ?? ""),
    ]);
  }
  writeCsv(path.join(OUT_DIR, "school_year_panel.csv"), panelHeader, panelRows);

  // ---- Current production snapshot ----
  writeCsv(
    path.join(OUT_DIR, "school_current.csv"),
    ["dbn", "grade_band", "admission_category", "include_in_default_comparisons", "proclivity_decile", "proclivity_score", "comparison_group_id"],
    schools.map((s) => [
      s.dbn,
      s.gradeBand,
      s.admissionCategory,
      s.includeInDefaultComparisons,
      s.proclivityDecile ?? "",
      s.proclivityScore ?? "",
      s.comparisonGroupId ?? "",
    ])
  );

  // ---- Current comparison groups (peer lists from methodologyNotes) ----
  const groups = await prisma.comparisonGroup.findMany({
    select: { id: true, year: true, source: true, methodologyNotes: true },
  });
  const groupRows: unknown[][] = [];
  for (const g of groups) {
    let peers: string[] = [];
    try {
      const parsed = g.methodologyNotes ? JSON.parse(g.methodologyNotes) : null;
      if (parsed && Array.isArray(parsed.peers)) peers = parsed.peers;
    } catch {
      /* ignore */
    }
    groupRows.push([g.id, g.year, g.source, peers.join("|")]);
  }
  writeCsv(path.join(OUT_DIR, "comparison_groups.csv"), ["id", "year", "source", "peer_dbns"], groupRows);

  console.log("Export complete.");
}

main()
  .catch((e) => {
    console.error("EXPORT ERROR:", e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());