// Builds the tables for the FactHub check of the Washington Policy Center claim that
// Washington had the fastest 2014-2024 growth in per-capita drug overdose deaths.
// Inputs (all in raw/, fetched 2026-10-18 to 2026-11-03):
//   nchs_dp_2014.json          CDC NCHS "Drug Poisoning Mortality by State" (data.cdc.gov xbxb-epbu), 2014, both sexes, all ages
//   sos_dfe.json               CDC NCHS "Drug Overdose Mortality by State" map data (final, by residence)
//   nst2024.csv                Census Bureau Vintage 2024 state population estimates (POPESTIMATE2024)
//   vsrr_dec_all.json          CDC VSRR provisional 12-month-ending counts (xkb8-kh2a), December, all states
//   vsrr_wa_us_or_2019on.json  CDC VSRR provisional counts by drug, WA/US/OR, 2019 on
// The map file's 2014 rows for Arizona and Arkansas are swapped (AZ shows 356 deaths, AR 1,211).
// 2014 values here come from xbxb-epbu, which has AZ 1,211 and AR 356.
const fs = require('fs');
const path = require('path');
const raw = f => path.join(__dirname, 'raw', f);
const readJson = f => JSON.parse(fs.readFileSync(raw(f), 'utf8'));
const abbr = {Alabama:'AL',Alaska:'AK',Arizona:'AZ',Arkansas:'AR',California:'CA',Colorado:'CO',Connecticut:'CT',Delaware:'DE',Florida:'FL',Georgia:'GA',Hawaii:'HI',Idaho:'ID',Illinois:'IL',Indiana:'IN',Iowa:'IA',Kansas:'KS',Kentucky:'KY',Louisiana:'LA',Maine:'ME',Maryland:'MD',Massachusetts:'MA',Michigan:'MI',Minnesota:'MN',Mississippi:'MS',Missouri:'MO',Montana:'MT',Nebraska:'NE',Nevada:'NV','New Hampshire':'NH','New Jersey':'NJ','New Mexico':'NM','New York':'NY','North Carolina':'NC','North Dakota':'ND',Ohio:'OH',Oklahoma:'OK',Oregon:'OR',Pennsylvania:'PA','Rhode Island':'RI','South Carolina':'SC','South Dakota':'SD',Tennessee:'TN',Texas:'TX',Utah:'UT',Vermont:'VT',Virginia:'VA',Washington:'WA','West Virginia':'WV',Wisconsin:'WI',Wyoming:'WY'};
const name = Object.fromEntries(Object.entries(abbr).map(([n, a]) => [a, n]));
const west = new Set(['AK','AZ','CA','CO','HI','ID','MT','NV','NM','OR','UT','WA','WY']); // Census West region
const r1 = x => Math.round(x * 10) / 10;
const pct = (a, b) => r1((b / a - 1) * 100);

const dp14 = {};
for (const r of readJson('nchs_dp_2014.json')) {
  const a = r.state === 'United States' ? 'US' : abbr[r.state];
  if (a) dp14[a] = { deaths: +r.deaths, pop: +r.population, crude: +r.crude_death_rate, aa: +r.age_adjusted_rate };
}
const sos = {};
for (const r of readJson('sos_dfe.json').data)
  if (name[r.STATE]) (sos[r.STATE] = sos[r.STATE] || {})[+r.YEAR] = { rate: +r.RATE, deaths: +String(r.DEATHS).replace(/,/g, '') };
const [hdr, ...lines] = fs.readFileSync(raw('nst2024.csv'), 'utf8').trim().split(/\r?\n/).map(l => l.split(','));
const pop24 = {};
for (const l of lines) {
  const o = Object.fromEntries(hdr.map((k, i) => [k, l[i]]));
  if (o.SUMLEV === '040' && abbr[o.NAME]) pop24[abbr[o.NAME]] = +o.POPESTIMATE2024;
  if (o.SUMLEV === '010') pop24.US = +o.POPESTIMATE2024;
}
const vsrrDec = readJson('vsrr_dec_all.json');
const prov = {};
for (const r of vsrrDec) if (r.year === '2024') prov[r.state] = +r.predicted_value;
// U.S. final 2024 from NCHS Data Brief 549.
const usFinal24 = { deaths: 79384, aa: 23.1 };

const states = Object.keys(name).sort((a, b) => name[a].localeCompare(name[b]));
const rows = states.map(s => {
  const a = dp14[s], b = sos[s][2024];
  const crude24 = b.deaths / pop24[s] * 1e5, prov24 = prov[s] / pop24[s] * 1e5;
  return { state: name[s], stateCode: s, censusRegionWest: west.has(s),
    deaths2014: a.deaths, population2014: a.pop, crudeRate2014: r1(a.crude), ageAdjRate2014: r1(a.aa),
    deaths2024: b.deaths, population2024: pop24[s], crudeRate2024: r1(crude24), ageAdjRate2024: b.rate,
    provisionalDeaths2024: prov[s], provisionalCrudeRate2024: r1(prov24),
    ageAdjChangePct: pct(r1(a.aa), b.rate), crudeChangePct: pct(a.crude, crude24), provisionalCrudeChangePct: pct(a.crude, prov24) };
});
// Ranks: 1 = highest; ranks use unrounded values except the age-adjusted 2024 rate, which CDC publishes to one decimal.
const exact = r => ({ crudeRate2014: dp14[r.stateCode].crude, ageAdjRate2014: r.ageAdjRate2014, ageAdjRate2024: r.ageAdjRate2024,
  crudeRate2024: r.deaths2024 / r.population2024, provisionalCrudeRate2024: r.provisionalDeaths2024 / r.population2024,
  ageAdjChangePct: r.ageAdjRate2024 / r.ageAdjRate2014, crudeChangePct: (r.deaths2024 / r.population2024) / dp14[r.stateCode].crude,
  provisionalCrudeChangePct: (r.provisionalDeaths2024 / r.population2024) / dp14[r.stateCode].crude });
const rankFields = { ageAdjRate2014: 'ageAdjRank2014', ageAdjRate2024: 'ageAdjRank2024', crudeRate2014: 'crudeRank2014', crudeRate2024: 'crudeRank2024',
  provisionalCrudeRate2024: 'provisionalCrudeRank2024', ageAdjChangePct: 'ageAdjChangeRank', crudeChangePct: 'crudeChangeRank', provisionalCrudeChangePct: 'provisionalCrudeChangeRank' };
const ex = Object.fromEntries(rows.map(r => [r.stateCode, exact(r)]));
for (const [f, rf] of Object.entries(rankFields)) for (const r of rows) r[rf] = 1 + rows.filter(o => ex[o.stateCode][f] > ex[r.stateCode][f]).length;

const us = { state: 'United States', stateCode: 'US', deaths2014: dp14.US.deaths, population2014: dp14.US.pop, crudeRate2014: r1(dp14.US.crude), ageAdjRate2014: r1(dp14.US.aa),
  deaths2024: usFinal24.deaths, population2024: pop24.US, crudeRate2024: r1(usFinal24.deaths / pop24.US * 1e5), ageAdjRate2024: usFinal24.aa,
  provisionalDeaths2024: prov.US, provisionalCrudeRate2024: r1(prov.US / pop24.US * 1e5) };
us.ageAdjChangePct = pct(r1(dp14.US.aa), us.ageAdjRate2024);
us.crudeChangePct = pct(dp14.US.crude, usFinal24.deaths / pop24.US * 1e5);
us.provisionalCrudeChangePct = pct(dp14.US.crude, prov.US / pop24.US * 1e5);

// Yearly age-adjusted rates (final): Washington from the NCHS map data; U.S. from NCHS Data Briefs 522 and 549.
const usAA = {2014:14.7,2015:16.3,2016:19.8,2017:21.7,2018:20.7,2019:21.6,2020:28.3,2021:32.4,2022:32.6,2023:31.3,2024:23.1};
const yearly = [];
for (let y = 2014; y <= 2024; y++) {
  yearly.push({ year: y, series: 'Washington', ageAdjRate: sos.WA[y].rate });
  yearly.push({ year: y, series: 'United States', ageAdjRate: usAA[y] });
}
// Washington provisional December 12-month counts: all overdose deaths and those involving synthetic opioids (mainly fentanyl).
const vsrrWa = readJson('vsrr_wa_us_or_2019on.json').filter(r => r.state === 'WA' && r.month === 'December' && +r.year <= 2024);
const fent = [2019, 2020, 2021, 2022, 2023, 2024].map(y => ({
  year: y,
  allOverdoseDeaths: +vsrrWa.find(r => +r.year === y && r.indicator === 'Number of Drug Overdose Deaths').predicted_value,
  syntheticOpioidDeaths: +vsrrWa.find(r => +r.year === y && r.indicator === 'Synthetic opioids, excl. methadone (T40.4)').predicted_value,
}));

// Yearly age-adjusted rates for all 50 states, 2014-2024; 2014 from xbxb-epbu (fixes the AZ/AR swap).
const stateYearly = [];
for (const s of states) for (let y = 2014; y <= 2024; y++)
  stateYearly.push({ state: name[s], stateCode: s, year: y, ageAdjRate: y === 2014 ? r1(dp14[s].aa) : sos[s][y].rate, deaths: y === 2014 ? dp14[s].deaths : sos[s][y].deaths });
const rateOf = (s, y) => stateYearly.find(r => r.stateCode === s && r.year === y).ageAdjRate;

// Saved-analysis results.
const wa = rows.find(r => r.stateCode === 'WA');
const byChange = [...rows].sort((a, b) => a.ageAdjChangeRank - b.ageAdjChangeRank);
const analyses = {
  growthByState: byChange.map(r => ({ state: r.state, region: r.censusRegionWest ? 'West' : 'Rest of U.S.', ageAdjRate2014: r.ageAdjRate2014, ageAdjRate2024: r.ageAdjRate2024,
    ageAdjChangePct: r.ageAdjChangePct, ageAdjChangeRank: r.ageAdjChangeRank, crudeChangePct: r.crudeChangePct, crudeChangeRank: r.crudeChangeRank,
    provisionalCrudeChangePct: r.provisionalCrudeChangePct, provisionalCrudeChangeRank: r.provisionalCrudeChangeRank })),
  waByBasis: [
    { basis: 'Age-adjusted rate, final data (CDC standard)', rate2014: wa.ageAdjRate2014, rank2014: wa.ageAdjRank2014, rate2024: wa.ageAdjRate2024, rank2024: wa.ageAdjRank2024, changePct: wa.ageAdjChangePct, changeRank: wa.ageAdjChangeRank },
    { basis: 'Crude rate, final data', rate2014: wa.crudeRate2014, rank2014: wa.crudeRank2014, rate2024: wa.crudeRate2024, rank2024: wa.crudeRank2024, changePct: wa.crudeChangePct, changeRank: wa.crudeChangeRank },
    { basis: 'Crude rate, 2024 provisional counts by place of death (WPC basis)', rate2014: wa.crudeRate2014, rank2014: wa.crudeRank2014, rate2024: wa.provisionalCrudeRate2024, rank2024: wa.provisionalCrudeRank2024, changePct: wa.provisionalCrudeChangePct, changeRank: wa.provisionalCrudeChangeRank },
  ],
  top2024: ['ageAdjRate2024', 'crudeRate2024', 'provisionalCrudeRate2024'].map(f => ({ measure: f,
    top5: [...rows].sort((a, b) => b[f] - a[f]).slice(0, 5).map(r => ({ state: r.state, rate: r[f] })) })),
  windows: [[2014, 2024], [2014, 2023], [2015, 2024], [2019, 2024], [2020, 2024], [2014, 2019]].map(([a, b]) => {
    const g = states.map(s => ({ s, pct: r1((rateOf(s, b) / rateOf(s, a) - 1) * 100) })).sort((x, y) => y.pct - x.pct);
    const i = g.findIndex(x => x.s === 'WA');
    return { window: a + '-' + b, waChangePct: g[i].pct, waRank: 1 + g.filter(x => x.pct > g[i].pct).length,
      top3: g.slice(0, 3).map(x => name[x.s] + ' ' + x.pct + '%').join('; ') };
  }),
  regions: (() => {
    const med = a => { const s = [...a].sort((x, y) => x - y); return r1((s[(s.length - 1) >> 1] + s[s.length >> 1]) / 2); };
    const w = rows.filter(r => r.censusRegionWest), o = rows.filter(r => !r.censusRegionWest);
    return [{ group: 'West (13 states)', states: w.length, medianAgeAdjChangePct: med(w.map(r => r.ageAdjChangePct)), inTop5: byChange.slice(0, 5).filter(r => r.censusRegionWest).length },
            { group: 'Rest of U.S. (37 states)', states: o.length, medianAgeAdjChangePct: med(o.map(r => r.ageAdjChangePct)), inTop5: byChange.slice(0, 5).filter(r => !r.censusRegionWest).length }];
  })(),
  absoluteChange: [['ageAdjRate2014', 'ageAdjRate2024', 'Age-adjusted rate, final data'], ['crudeRate2014', 'crudeRate2024', 'Crude rate, final data'], ['crudeRate2014', 'provisionalCrudeRate2024', 'Crude rate, 2024 provisional counts']].map(([a, b, basis]) => {
    const g = rows.map(r => ({ state: r.state, increase: r1(r[b] - r[a]) })).sort((x, y) => y.increase - x.increase);
    const w = g.find(x => x.state === 'Washington');
    return { basis, waIncreasePer100k: w.increase, waRank: 1 + g.filter(x => x.increase > w.increase).length,
      top3: g.slice(0, 3).map(x => x.state + ' +' + x.increase).join('; ') };
  }),
  waYearly: yearly.map(r => ({ year: String(r.year), series: r.series, rate: r.ageAdjRate })),
  fentanyl: fent.map(r => ({ year: String(r.year), allOverdoseDeaths: r.allOverdoseDeaths, syntheticOpioidDeaths: r.syntheticOpioidDeaths, syntheticSharePct: r1(r.syntheticOpioidDeaths / r.allOverdoseDeaths * 100) })),
  us,
};

const out = path.join(__dirname, 'out');
fs.mkdirSync(out, { recursive: true });
fs.writeFileSync(path.join(out, 'analyses.json'), JSON.stringify(analyses, null, 1));
const cell = v => v === null || v === undefined ? '' : (typeof v === 'string' && v.includes(',') ? '"' + v + '"' : v);
function csv(file, arr, cols = Object.keys(arr[0])) {
  fs.writeFileSync(path.join(out, file), [cols.join(','), ...arr.map(r => cols.map(c => cell(r[c])).join(','))].join('\n') + '\n');
}
csv('states-2014-2024.csv', rows);
csv('us-2014-2024.csv', [us]);
csv('wa-us-yearly-rates.csv', yearly);
csv('wa-synthetic-opioid-deaths.csv', fent);
csv('states-yearly-rates.csv', stateYearly);
fs.writeFileSync(path.join(out, 'built.json'), JSON.stringify({ rows, us, yearly, fent }, null, 1));

console.log('WA', wa); console.log('US', us);
const med = a => { const s = [...a].sort((x, y) => x - y); return (s[(s.length - 1) >> 1] + s[s.length >> 1]) / 2; };
console.log('median aa change West', med(rows.filter(r => r.censusRegionWest).map(r => r.ageAdjChangePct)), 'rest', med(rows.filter(r => !r.censusRegionWest).map(r => r.ageAdjChangePct)));
const top = (f, n) => [...rows].sort((a, b) => b[f] - a[f]).slice(0, n).map(r => r.stateCode + ' ' + r[f]).join(', ');
console.log('top aa change:', top('ageAdjChangePct', 12));
console.log('top crude change:', top('crudeChangePct', 5));
console.log('top prov crude change:', top('provisionalCrudeChangePct', 5));
console.log('top prov crude 2024:', top('provisionalCrudeRate2024', 6));
console.log('top crude 2024:', top('crudeRate2024', 6));
console.log('top aa 2024:', top('ageAdjRate2024', 6));
console.log('fent', fent);