scripts/analysis/_probe-data.ts

// Connectivity + data-availability probe for the clustering/decile bake-off.
// Read-only. Prints what's actually in the warehouse so the analysis can be scoped.
import { prisma } from "../loaders/_lib";

async function main() {
  const schools = await prisma.school.count();
  const inclSchools = await prisma.school.count({
    where: { includeInDefaultComparisons: true },
  });
  console.log(`Schools: ${schools} (include_in_default_comparisons=true: ${inclSchools})`);

  // Grade-band breakdown
  const bands = await prisma.school.groupBy({
    by: ["gradeBand"],
    _count: { _all: true },
  });
  console.log("\nGrade bands:");
  for (const b of bands.sort((a, z) => z._count._all - a._count._all))
    console.log(`  ${String(b.gradeBand).padEnd(10)} ${b._count._all}`);

  // Proclivity coverage
  const withDecile = await prisma.school.count({ where: { proclivityDecile: { not: null } } });
  const withCG = await prisma.school.count({ where: { comparisonGroupId: { not: null } } });
  console.log(`\nproclivityDecile populated: ${withDecile}`);
  console.log(`comparisonGroupId populated: ${withCG}`);

  // Demographics coverage by year
  const demYears = await prisma.schoolYearDemographics.groupBy({
    by: ["year"],
    _count: { _all: true },
  });
  console.log("\nDemographics rows by year:");
  for (const y of demYears.sort((a, z) => (a.year < z.year ? -1 : 1)))
    console.log(`  ${y.year}  ${y._count._all}`);

  // Metric definitions catalog
  const metrics = await prisma.metricDefinition.findMany({
    select: { key: true, domain: true, displayName: true, direction: true },
    orderBy: [{ domain: "asc" }, { key: "asc" }],
  });
  console.log(`\nMetricDefinitions: ${metrics.length}`);
  for (const m of metrics)
    console.log(`  [${m.domain}] ${m.key.padEnd(42)} ${m.direction}  ${m.displayName}`);

  // For the candidate outcome metrics, show year coverage + row counts (ALL subgroup)
  const outcomeKeys = metrics
    .map((m) => m.key)
    .filter((k) =>
      /grad|profic|ela|math|absen|chronic|regents|college|readiness/i.test(k)
    );
  console.log(`\nCandidate outcome metric_keys (${outcomeKeys.length}):`);
  for (const k of outcomeKeys) {
    const rows = await prisma.schoolYearMetric.groupBy({
      by: ["year"],
      where: { metricKey: k, subgroup: "ALL" },
      _count: { _all: true },
    });
    const yrs = rows
      .sort((a, z) => (a.year < z.year ? -1 : 1))
      .map((r) => `${r.year}:${r._count._all}`)
      .join("  ");
    console.log(`  ${k.padEnd(42)} ${yrs}`);
  }
}

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