// Load the residual-z engine's per-cell output
// (data/cities/miami/reference/residual_z.csv, written by
// python -m pipeline_miami.analysis.residual_z) into the additive columns on
// miami_school_year_metrics: fitted, residual, residual_se, residual_z,
// outlier_flag, persistent_dir, persistent_n, residual_n. Cells not in the
// CSV (not fitted, ineligible) are reset to null so a re-run never leaves
// stale values. Never touches the percentile columns.
// Run:  npx tsx scripts/loaders/miami/residual_z.ts

import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { startDataLoad, finishDataLoad, stageLoadOperation, runMain } from "./_lib";

const SCRIPT_PATH = "scripts/loaders/miami/residual_z.ts";
const CSV = path.join(process.cwd(), "data/cities/miami/reference/residual_z.csv");
const PACKET = path.join(process.cwd(), "data/analysis/miami-residual-z.json");

type Row = Record<string, string>;
const num = (v: string) => (v === "" || v === undefined ? null : Number(v));

async function main() {
  const rows = parse(fs.readFileSync(CSV, "utf-8"), { columns: true, skip_empty_lines: true }) as Row[];
  const packet = JSON.parse(fs.readFileSync(PACKET, "utf-8")) as { meta: { generated_at: string; config_sha256_12: string; n_cells: number } };
  const metricKeys = [...new Set(rows.map((r) => r.metric_key))];
  console.log(`[residual_z] ${rows.length} cells across ${metricKeys.length} outcomes (packet ${packet.meta.generated_at})`);
  const load = await startDataLoad({
    sourceId: "derived_residual_z", sourceUrl: "data/cities/miami/reference/residual_z.csv", sha256: packet.meta.config_sha256_12, bytes: fs.statSync(CSV).size,
    yearCovered: "multi", scriptPath: SCRIPT_PATH, notes: `residual-z engine output ${packet.meta.generated_at}; config ${packet.meta.config_sha256_12}`,
  });
  let written = 0, check = 0;
  stageLoadOperation(load.id, async (tx) => {
    // Reset and refill together: a bad row leaves the previous complete fit intact.
    await tx.miamiSchoolYearMetric.updateMany({
      where: { OR: [{ metricKey: { in: metricKeys } }, { residualZ: { not: null } }, { outlierFlag: { not: null } }, { fitted: { not: null } }] },
      data: { fitted: null, residual: null, residualSe: null, residualZ: null, outlierFlag: null, persistentDir: null, persistentN: null, residualN: null },
    });
    for (const r of rows) {
      const result = await tx.miamiSchoolYearMetric.updateMany({
        where: { schoolNumber: r.school_number, year: r.year, metricKey: r.metric_key, subgroup: "ALL" },
        data: {
          fitted: num(r.fitted), residual: num(r.resid), residualSe: num(r.se_i), residualZ: num(r.z), residualN: num(r.n),
          outlierFlag: r.eligible === "True" ? r.flag : null, persistentDir: r.eligible === "True" ? r.persistent_dir : null,
          persistentN: r.eligible === "True" ? Number(r.persistent_n) : null,
        },
      });
      if (result.count !== 1) throw new Error(`[residual_z] ${r.school_number} ${r.year} ${r.metric_key}: expected exactly one source cell, got ${result.count}`);
      written += result.count;
    }
    check = await tx.miamiSchoolYearMetric.count({ where: { residualZ: { not: null }, metricKey: { in: metricKeys }, subgroup: "ALL" } });
  });
  await finishDataLoad(load.id, { inserted: 0, updated: rows.length, suppressed: 0 }, `candidate residual rows: ${rows.length}`);
  console.log(`[residual_z] wrote ${written} rows; ${check} cells carry z (${rows.length - check} rows without z)`);
}

runMain(main);