// Version 2 additions to dataset:wa-general-fund-growth.
// Reads v1 outputs (results.json, CSVs written by build.js, dataset-payload.json) and adds:
// revenue history, post-2013 tax legislation and the existing-base counterfactual,
// a government-cost deflator, base-year sensitivity, spending above the population x CPI
// benchmark by area and program group, maintenance-level vs policy steps, K-12 district
// revenue by source, a NASBO cross-state check, and two attributed statements.
// Every input figure is written below with its source. Run: node build_v2.js
// Writes the v2 CSVs, v2-content.json (arrays expanded into the dataset content) and v2-patch.json.
const fs = require('fs');
const path = require('path');
const crypto = require('crypto');
const dir = __dirname;
const V1 = JSON.parse(fs.readFileSync(path.join(dir, 'dataset-payload.json'), 'utf8'));
const R1 = JSON.parse(fs.readFileSync(path.join(dir, 'results.json'), 'utf8'));
const today = '2026-10-10';
const executedAt = '2026-10-10T06:00:00Z';
const r = (x, d = 1) => Math.round(x * 10 ** d) / 10 ** d;
const pct = (a, b) => r((b / a - 1) * 100, 1);
const readCsv = f => { const [h, ...rows] = fs.readFileSync(path.join(dir, f), 'utf8').trim().split('\n'); const cols = h.split(','); return rows.map(l => { const v = l.match(/("([^"]|"")*"|[^,]*)(,|$)/g).map(s => s.replace(/,$/, '').replace(/^"|"$/g, '').replace(/""/g, '"')); return Object.fromEntries(cols.map((c, i) => [c, v[i] === '' ? null : isNaN(+v[i]) ? v[i] : +v[i]])); }); };
const csv = (rows, cols) => [cols.join(','), ...rows.map(o => cols.map(c => o[c] === null || o[c] === undefined ? '' : /[,"]/.test(String(o[c])) ? `"${String(o[c]).replace(/"/g, '""')}"` : o[c]).join(','))].join('\n') + '\n';

const ngf = readCsv('wa-ngf-by-biennium.csv');
const bench = readCsv('wa-benchmarks-by-biennium.csv');
const areas = readCsv('wa-ngf-by-functional-area.csv');
const N = Object.fromEntries(ngf.map(x => [x.biennium, x]));

// ---- Government-cost deflator: BEA NIPA Table 3.9.4, state and local government consumption
// expenditures and gross investment, chain-type price index, 2017=100 (FRED B829RG3A086NBEA), annual.
const slPrice = { 2006: 77.406, 2008: 85.692, 2010: 87.642, 2012: 92.579, 2014: 97.804, 2016: 97.505, 2018: 104.126, 2020: 108.650, 2022: 125.720, 2024: 131.059 };
const bench2 = bench.map(b => ({ ...b, slPriceIndex: slPrice[b.midYear] }));
const B = Object.fromEntries(bench2.map(x => [x.biennium, x]));

// ---- NGF-O revenue, ERFC September 2026 forecast, Table 3.17 (PDF p. 78), cash basis, $ millions.
// Basket: GF-S (current definition) + ELTA + OPA + WEIA, restated to today's definition.
// 2005-07 and 2007-09 are sums of the table's fiscal-year rows (FY06 14,432 + FY07 15,734; FY08 15,872 + FY09 14,382).
const revenue = [
  ['2005-07', 29785, 381, null, null, 30166, 'actual'], ['2007-09', 29817, 437, null, null, 30254, 'actual'],
  ['2009-11', 28218, 269, 99, 0, 28586, 'actual'], ['2011-13', 30657, 215, 244, 0, 31116, 'actual'],
  ['2013-15', 33666, 405, 234, 0, 34305, 'actual'], ['2015-17', 38317, 467, 266, 0, 39050, 'actual'],
  ['2017-19', 44143, 1654, 284, 0, 46081, 'actual'], ['2019-21', 50803, 1639, 337, 352, 53132, 'actual'],
  ['2021-23', 61002, 2575, 378, 779, 64734, 'actual'], ['2023-25', 63392, 2428, 349, 967, 67137, 'actual'],
  ['2025-27', 69974, 4191, 338, 1756, 76258, 'forecast (Sept 2026)'],
].map(([biennium, gfs, elta, opa, weia, total, status]) => ({ biennium, gfsMillions: gfs, eltaMillions: elta, opaMillions: opa, weiaMillions: weia, ngfoRevenueMillions: total, status }));
const Rev = Object.fromEntries(revenue.map(x => [x.biennium, x]));
const revRows = revenue.filter(x => B[x.biennium]).map(x => {
  const s = N[x.biennium].finalNgf / 1000, piM = B[x.biennium].personalIncomeBiennialMillions;
  return { biennium: x.biennium, revenueMillions: x.ngfoRevenueMillions, finalNgfMillions: r(s, 0), spendingMinusRevenueMillions: r(s - x.ngfoRevenueMillions, 0), revenueShareOfIncomePct: r(x.ngfoRevenueMillions / piM * 100, 2), spendingShareOfIncomePct: r(s / piM * 100, 2) };
});

// ---- Revenue in 2023-25 from tax legislation enacted after the 2013-15 base ($ billions, estimated).
// Method: property tax = actual 2023-25 collections in the NGF basket (9,330M, 2024 LBN O-8) minus a levy
// growing at the pre-2017 pace from 3,949M in 2013-15 (2014 LBN O-18) at 2.3-3.0% a year; capital gains and
// WEIA = measured receipts; other measures = first-biennium LBN scores scaled to a full biennium.
const taxLeg = [
  ['ehb-2242-levy', 'State school property levy increase (EHB 2242, 2017)', 2017, 4.0, 4.2, 4.4, 'Actual 2023-25 NGF property tax less a counterfactual levy growing 2.3-3.0% a year from 2013-15.', 'medium', ['lbn-2017', 'lbn-2014', 'lbn-2024', 'dor-proptax-2024']],
  ['capital-gains', 'Capital gains tax (ESSB 5096, 2021), deposited to ELTA', 2021, 0.98, 0.98, 0.98, 'DOR collections: tax year 2023 $418.6M + tax year 2024 $560.6M (initial).', 'high', ['dor-capgains-2025', 'lbn-2021']],
  ['weia', 'Workforce Education Investment Account B&O surcharges (E2SHB 2158, 2019; ESSB 6492, 2020)', 2019, 0.97, 0.97, 0.97, 'ERFC Table 3.17 WEIA collections, 2023-25 (the whole account post-dates 2013).', 'high', ['erfc-sep-2026', 'lbn-2019']],
  ['graduated-reet', 'Graduated real estate excise tax (ESSB 5998, 2019)', 2019, 0.3, 0.45, 0.6, 'First score $243.5M for 18 months of 2019-21, scaled; later fiscal notes not checked.', 'low', ['lbn-2019']],
  ['remote-sellers', 'Remote-seller and marketplace collection (EHB 2163, 2017; SSB 5581, 2019)', 2017, 0.4, 0.65, 0.9, 'First scores $341.0M (2017-19) and $114.9M (2019-21), scaled. Similar laws followed the 2018 Wayfair decision in most states.', 'low', ['lbn-2017', 'lbn-2019']],
  ['bank-surcharge', 'B&O surcharge on large financial institutions (SHB 2167, 2019)', 2019, 0.2, 0.25, 0.3, 'First score $133.2M for about 1.5 years, scaled.', 'low', ['lbn-2019']],
  ['package-2015', '2015 revenue changes (ESSB 6138 and others)', 2015, 0.25, 0.3, 0.35, 'First score $185.3M net (2015-17), scaled.', 'low', ['lbn-2015']],
  ['package-2013', '2013 estate tax and telecommunications changes (EHB 2075, E2SHB 1971)', 2013, 0.25, 0.3, 0.35, 'First score $269.3M (2013-15), scaled.', 'low', ['lbn-2013']],
  ['aerospace-repeal', 'Repeal of the aerospace preferential B&O rate (ESB 6690, 2020)', 2020, 0.1, 0.2, 0.3, 'First score $134.0M for about 3 months of 2019-21; whether the rate stayed repealed through 2025 was not checked.', 'low', ['lbn-2020']],
  ['base-2019-other', 'Other 2019 base changes (ESSB 5997, ESB 6016, ESSB 6004)', 2019, 0.1, 0.15, 0.2, 'First scores $117.9M (2019-21), scaled.', 'low', ['lbn-2019']],
  ['sessions-2021-2024', 'Other 2021-2024 session changes, net', 2021, 0, 0.05, 0.1, 'Session totals: 2021 +432M excluding capital gains, 2022 -67.0M, 2023 +57.3M, 2024 -32.8M.', 'medium', ['lbn-2021', 'lbn-2022', 'lbn-2023', 'lbn-2024']],
  ['preferences', 'Tax preference extensions and expansions, 2013-2024', 2013, -0.5, -0.35, -0.2, 'Sum of negative line items in the session lists, scaled.', 'low', ['lbn-2013', 'lbn-2015', 'lbn-2017', 'lbn-2019']],
].map(([id, measure, year, low, mid, high, basis, confidence, sourceIds]) => ({ id, measure, year, est2023_25LowBillions: low, est2023_25MidBillions: mid, est2023_25HighBillions: high, basis, confidence, sourceIdList: sourceIds.join(' ') }));
const tsum = k => r(taxLeg.reduce((s, x) => s + x[k], 0), 2);
const legLow = tsum('est2023_25LowBillions'), legMid = tsum('est2023_25MidBillions'), legHigh = tsum('est2023_25HighBillions');
const rev2325 = Rev['2023-25'].ngfoRevenueMillions / 1000;
const base = { low: r(rev2325 - legHigh, 2), mid: r(rev2325 - legMid, 2), high: r(rev2325 - legLow, 2) };
const g = (a, b) => (B[b].population / B[a].population);
const popCpi = g('2013-15', '2023-25') * B['2023-25'].cpiUs / B['2013-15'].cpiUs;
const piGrowth = B['2023-25'].personalIncomeBiennialMillions / B['2013-15'].personalIncomeBiennialMillions;
const s1315 = N['2013-15'].finalNgf / 1e6, s2325 = N['2023-25'].finalNgf / 1e6;
const paths = [['Population x US CPI-U', s1315 * popCpi], ['WA personal income', s1315 * piGrowth], ['Actual final appropriations', s2325]]
  .map(([path, level]) => ({ spendingPath: path, spending2023_25Billions: r(level, 2), existingBaseRevenueMidBillions: base.mid, surplusMidBillions: r(base.mid - level, 2), surplusLowBillions: r(base.low - level, 2), surplusHighBillions: r(base.high - level, 2), covered: base.low - level >= 0 ? 'yes, across the range' : base.high - level < 0 ? 'no, across the range' : 'depends on estimate' }));

// ---- Base-year sensitivity: growth from each base biennium to 2023-25.
const L = '2023-25';
const baseYear = bench2.filter(b => b.biennium !== L).map(b => {
  const ng = N[L].finalNgf / N[b.biennium].finalNgf, pop = B[L].population / b.population;
  const pc = pop * B[L].cpiUs / b.cpiUs, ps = pop * B[L].cpiSeattle / b.cpiSeattle, pl = pop * B[L].slPriceIndex / b.slPriceIndex;
  return { baseBiennium: b.biennium, ngfGrowthPct: r((ng - 1) * 100), popCpiUsGrowthPct: r((pc - 1) * 100), realPerCapitaUsPct: r((ng / pc - 1) * 100), realPerCapitaSeattlePct: r((ng / ps - 1) * 100), realPerCapitaSlDeflatorPct: r((ng / pl - 1) * 100), personalIncomeGrowthPct: pct(b.personalIncomeBiennialMillions, B[L].personalIncomeBiennialMillions), revenueGrowthPct: pct(Rev[b.biennium].ngfoRevenueMillions, Rev[L].ngfoRevenueMillions), ngfShareOfIncomeAtBasePct: r(N[b.biennium].finalNgf / 1000 / b.personalIncomeBiennialMillions * 100, 2) };
});
const deflRows = [['2013-15', '2023-25'], ['2005-07', '2023-25']].map(([a, b]) => {
  const ng = N[b].finalNgf / N[a].finalNgf, pop = B[b].population / B[a].population;
  return { from: a, to: b, slPriceGrowthPct: pct(B[a].slPriceIndex, B[b].slPriceIndex), cpiUsGrowthPct: pct(B[a].cpiUs, B[b].cpiUs), popTimesSlPriceGrowthPct: r((pop * B[b].slPriceIndex / B[a].slPriceIndex - 1) * 100), realPerCapitaSlDeflatorPct: r((ng / (pop * B[b].slPriceIndex / B[a].slPriceIndex) - 1) * 100), realPerCapitaCpiUsPct: r((ng / (pop * B[b].cpiUs / B[a].cpiUs) - 1) * 100) };
});

// ---- Spending above the population x US CPI-U benchmark, by functional area ($ millions).
const excessArea = areas.map(a => ({ area: a.area, ngf2013_15Millions: r(a.ngf2013_15 / 1000, 0), benchmark2023_25Millions: r(a.ngf2013_15 / 1000 * popCpi, 0), ngf2023_25Millions: r(a.ngf2023_25 / 1000, 0), aboveBenchmarkMillions: r((a.ngf2023_25 - a.ngf2013_15 * popCpi) / 1000, 0) }));
const excessTotal = r(excessArea.reduce((s, x) => s + x.aboveBenchmarkMillions, 0), 0);
excessArea.forEach(x => { x.shareOfExcessPct = r(x.aboveBenchmarkMillions / excessTotal * 100, 1); });

// ---- Program groups, reorganization-adjusted ($ millions, final NGF; 2015 LBN pp. O-24 to O-32 and agency
// detail; 2025 LBN pp. O-18 to O-25 and agency detail). Groupings follow programs across the 2018 creation of
// DCYF and the move of community behavioral health from DSHS to HCA.
const groups = [
  ['Human services', 'Low-income health care (HCA medical assistance + Health Benefit Exchange)', 4306.7, 6233.0, 'HCA 2013-15; HCA Medical Assistance + HBE 2023-25.'],
  ['Human services', 'Behavioral health (community behavioral health + state hospitals)', 1089.5, 3753.4, 'DSHS Mental Health + Alcohol/Substance Abuse 2013-15; HCA Community Behavioral Health + DSHS Mental Health 2023-25.'],
  ['Human services', 'Long-term care (DSHS Aging and Long-Term Support)', 1774.9, 4665.4, 'Average clients served rose about 27% (FY2014-15 to FY2024-25).'],
  ['Human services', 'Developmental disabilities (DSHS)', 1113.3, 2667.1, ''],
  ['Human services', 'Children, early learning and economic services (incl. child-care subsidies)', 1687.2, 5731.5, 'DSHS Children & Family + Juvenile Rehab + Economic Services + Dept of Early Learning 2013-15; DCYF + DSHS Economic Services 2023-25. Child-care subsidies sat in DSHS Economic Services in 2013-15.'],
  ['Human services', 'Corrections (DOC)', 1704.2, 2514.5, ''],
  ['Human services', 'Health (DOH)', 120.3, 358.4, ''],
  ['Human services', 'All other human services', 378.7, 867.6, 'Residual: CJTC, ESD, Veterans Affairs, Special Commitment Center, Office of Independent Investigations, DSHS administration and vocational rehabilitation, others. Human services total (DSHS + Other Human Services + DEL) 12,174.8 to 26,790.9.'],
  ['Governmental operations', 'Department of Commerce', 126.6, 1137.4, '2023-25 enacted Commerce NGF included $725.3M for Community Services & Housing (2023 LBN p. O-136).'],
  ['Governmental operations', 'Department of Revenue (incl. Working Families Tax Credit)', 213.0, 781.0, 'WFTC first funded 2021-23; 2021 estimate $518M for 2023-25.'],
  ['Governmental operations', 'All other governmental operations', 124.9, 456.5, 'Residual of the area total (464.5 to 2,374.9).'],
  ['Higher education', 'Student Achievement Council (incl. Washington College Grant)', 724.9, 1188.5, ''],
].map(([area, group, a, b, note]) => ({ area, group, ngf2013_15Millions: a, ngf2023_25Millions: b, aboveBenchmarkMillions: r(b - a * popCpi, 0), pctChange: pct(a, b), note }));

// ---- Maintenance level (ML) vs policy level, each budget step 2015-2025 ($ billions; LBN overview pages).
const mlpl = [
  ['2015-17 biennial', 2015, 5.30, -0.98, 'lbn-2015', 'stated (ML $39.2B; enacted $38.22B; policy includes -2.04 I-1351 change)'],
  ['2016 supplemental', 2016, 0.203, 0.031, 'lbn-2016', 'stated'], ['2017 supplemental (2015-17)', 2017, -0.106, 0.078, 'lbn-2017', 'stated'],
  ['2017-19 biennial', 2017, 3.11, 2.11, 'lbn-2017', 'stated (ML $41.6B; enacted $43.71B)'],
  ['2018 supplemental', 2018, -0.146, 1.102, 'lbn-2018', 'stated'], ['2019 supplemental (2017-19)', 2019, 0.087, 0.102, 'lbn-2019', 'stated'],
  ['2019-21 biennial', 2019, 5.63, 2.02, 'lbn-2019', 'stated (ML $50.48B; enacted $52.50B)'],
  ['2020 supplemental', 2020, 0.122, 0.690, 'lbn-2020', 'stated (after vetoes)'], ['2021 supplemental (2019-21)', 2021, -0.539, -0.210, 'lbn-2021', 'ML is a residual; policy mainly enhanced federal Medicaid match'],
  ['2021-23 biennial', 2021, 3.44, 3.07, 'lbn-2021', 'stated (ML $56.0B; enacted $59.07B)'],
  ['2022 supplemental', 2022, -1.10, 6.20, 'lbn-2022', 'stated; policy includes one-time transfers into dedicated accounts'],
  ['2023 supplemental (2021-23)', 2023, 0.39, -1.20, 'lbn-2023', 'ML is a residual'], ['2023-25 biennial', 2023, 1.76, 4.70, 'lbn-2023', 'ML derived (enacted $69.80B less stated policy)'],
  ['2024 supplemental', 2024, 1.10, 1.00, 'lbn-2024', 'stated'], ['2025 supplemental (2023-25)', 2025, 0.855, -0.057, 'lbn-2025', 'stated'],
].map(([step, session, ml, pl, src, basis]) => ({ step, session, maintenanceLevelChangeBillions: ml, policyChangeBillions: pl, sourceId: src, basis }));
const mlSum = r(mlpl.reduce((s, x) => s + x.maintenanceLevelChangeBillions, 0), 2), plSum = r(mlpl.reduce((s, x) => s + x.policyChangeBillions, 0), 2);

// ---- K-12 district General Fund revenue by source (OSPI F-196), $ millions, annual average FTE enrollment.
// 2014-15 onward: OSPI 10-year F-196 workbook, sheets "REV by Source" and "Enrollment". 2013-14: 2015-16
// Financial Reporting Summary Table Three per-pupil values x Table Two enrollment (1,037,834.97).
const k12 = [
  ['2013-14', 2014, 1037835, 2459, 7410, 878], ['2014-15', 2015, 1051083, 2579, 7889, 899], ['2016-17', 2017, 1099228, 2772, 9376, 944],
  ['2017-18', 2018, 1112719, 2902, 10412, 936], ['2018-19', 2019, 1117395, 2409, 12858, 956], ['2019-20', 2020, 1127527, 2090, 13544, 1057],
  ['2023-24', 2024, 1085190, 2971, 15145, 1817], ['2024-25', 2025, 1088262, 3110, 16188, 1316],
].map(([schoolYear, cy, fte, loc, st, fed]) => ({ schoolYear, alignedCalendarYear: cy, annualAverageFte: fte, localMillions: loc, stateMillions: st, federalMillions: fed, totalLsfMillions: loc + st + fed, totalPerPupil: r((loc + st + fed) * 1e6 / fte, 0) }));
const K = Object.fromEntries(k12.map(x => [x.schoolYear, x]));
const cpi14 = 236.736, cpi24 = 313.689, sl14 = 97.804, sl24 = 131.059;
const k12Growth = [['Local', 'localMillions'], ['State', 'stateMillions'], ['Federal', 'federalMillions'], ['Local + state + federal', 'totalLsfMillions']].map(([source, k]) => {
  const a = K['2013-14'][k] * 1e6 / K['2013-14'].annualAverageFte, b = K['2023-24'][k] * 1e6 / K['2023-24'].annualAverageFte;
  return { source, perPupil2013_14: r(a, 0), perPupil2023_24: r(b, 0), totalNominalPct: pct(K['2013-14'][k], K['2023-24'][k]), perPupilNominalPct: pct(a, b), perPupilRealCpiPct: r((b / a / (cpi24 / cpi14) - 1) * 100), perPupilRealSlPct: r((b / a / (sl24 / sl14) - 1) * 100) };
});

// ---- NASBO Fiscal Survey of States, general fund expenditures, actual, $ millions.
const nasbo = [
  ['Washington', 16079, 22937, 35612], ['Idaho', 2781, 3653, 5392], ['Colorado', 8764, 12828, 18586], ['Florida', 26914, 33262, 51886],
  ['Tennessee', 12136, 14266, 21839], ['Utah', 5402, 7578, 12657], ['Oregon', 7693, 10172, 17026], ['California', 99838, 141861, 229231],
  ['Texas', 46764, 52281, 76193], ['50-state total', 722811, 868707, 1278624],
].map(([state, a, b, c]) => ({ state, fy2014Millions: a, fy2019Millions: b, fy2025Millions: c, growthFy2019to2025Pct: pct(b, c), growthFy2014to2025Pct: pct(a, c) }));

// ---- Write CSVs
const files = {
  'wa-benchmarks-by-biennium-v2.csv': csv(bench2, ['biennium', 'midYear', 'population', 'cpiUs', 'cpiSeattle', 'slPriceIndex', 'personalIncomeBiennialMillions']),
  'wa-ngfo-revenue-by-biennium.csv': csv(revenue, ['biennium', 'gfsMillions', 'eltaMillions', 'opaMillions', 'weiaMillions', 'ngfoRevenueMillions', 'status']),
  'wa-tax-legislation-2023-25.csv': csv(taxLeg, ['id', 'measure', 'year', 'est2023_25LowBillions', 'est2023_25MidBillions', 'est2023_25HighBillions', 'basis', 'confidence', 'sourceIdList']),
  'wa-base-year-sensitivity.csv': csv(baseYear, Object.keys(baseYear[0])),
  'wa-program-groups.csv': csv(groups, ['area', 'group', 'ngf2013_15Millions', 'ngf2023_25Millions', 'aboveBenchmarkMillions', 'pctChange', 'note']),
  'wa-ml-policy-steps.csv': csv(mlpl, ['step', 'session', 'maintenanceLevelChangeBillions', 'policyChangeBillions', 'sourceId', 'basis']),
  'wa-k12-district-revenue.csv': csv(k12, ['schoolYear', 'alignedCalendarYear', 'annualAverageFte', 'localMillions', 'stateMillions', 'federalMillions', 'totalLsfMillions', 'totalPerPupil']),
  'nasbo-general-fund-by-state.csv': csv(nasbo, ['state', 'fy2014Millions', 'fy2019Millions', 'fy2025Millions', 'growthFy2019to2025Pct', 'growthFy2014to2025Pct']),
};
for (const [f, c] of Object.entries(files)) fs.writeFileSync(path.join(dir, f), c);
const results = { revRows, taxLeg, legLow, legMid, legHigh, base, paths, baseYear, deflRows, excessArea, excessTotal, groups, mlpl, mlSum, plSum, k12Growth, nasbo, popCpi: r(popCpi, 5), piGrowth: r(piGrowth, 5) };
fs.writeFileSync(path.join(dir, 'results-v2.json'), JSON.stringify(results, null, 1));
module.exports = { V1, R1, results, files, today, executedAt, r };
if (require.main === module) {
  console.log('legislation 2023-25 $B low/mid/high', legLow, legMid, legHigh, 'existing base', base);
  console.table(paths); console.table(deflRows); console.log('excess total $M', excessTotal, 'ML', mlSum, 'PL', plSum);
  console.table(k12Growth); console.table(revRows);
}