// Builds dataset wa-paid-leave-premiums-solvency: tables (CSV) + saved-analysis results (JSON).
// Every input number is typed in below with the source it comes from; nothing is fetched.
// Run: node build.js   (writes ../data/*.csv and ../data/results.json)
const fs = require('fs'), path = require('path');
const out = path.join(__dirname, '..', 'data'); fs.mkdirSync(out, { recursive: true });
const r2 = x => Math.round(x * 100) / 100, r1 = x => Math.round(x * 10) / 10;
const csv = rows => {
  const c = Object.keys(rows[0]);
  const e = v => { const s = v === null ? '' : String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
  return c.join(',') + '\n' + rows.map(r => c.map(k => e(r[k])).join(',')).join('\n') + '\n';
};

// Table 1: total premium rate (% of wages up to the Social Security wage base) and the split
// for employers with 50 or more employees. Split is given only where an ESD source states it.
const RATES = [
  [2019, 0.40, 63.333, 36.667, 'esd-2020-report'],
  [2020, 0.40, 63.333, 36.667, 'esd-2020-report'],
  [2021, 0.40, null, null, 'esd-actuarial-2025'],
  [2022, 0.60, null, null, 'esd-actuarial-2025'],
  [2023, 0.80, null, null, 'esd-actuarial-2025'],
  [2024, 0.74, null, null, 'esd-actuarial-2025'],
  [2025, 0.92, null, null, 'esd-actuarial-2025'],
  [2026, 1.13, 71.43, 28.57, 'esd-rate-2026'],
  [2027, 1.20, 70.7, 29.3, 'esd-rate-2027'],
];
const CAP = 1.20;
const rates = RATES.map(([year, rate, emp, er, src], i) => ({
  year, total_premium_rate_pct: rate, employee_share_pct: emp, employer_share_pct: er,
  multiple_of_2019_rate: r2(rate / RATES[0][1]),
  change_from_prior_year_pct: i === 0 ? null : r1((rate / RATES[i - 1][1] - 1) * 100),
  at_statutory_cap: rate >= CAP ? 'yes' : 'no', source_id: src,
}));

// Table 2: annual premium on one worker's wage. Wages: BLS OEWS May 2025, Washington, all occupations.
const WAGES = [['p10', '10th percentile', 38670], ['p25', '25th percentile', 47020], ['p50', 'Median', 62990],
  ['mean', 'Mean', 83330], ['p75', '75th percentile', 101710], ['p90', '90th percentile', 153630]];
const costRow = (year, [id, label, wage]) => {
  const r = rates.find(x => x.year === year);
  const total = wage * r.total_premium_rate_pct / 100;
  return { premium_year: year, wage_point_id: id, wage_point: label, annual_wage: wage,
    total_premium_rate_pct: r.total_premium_rate_pct, employee_share_pct: r.employee_share_pct,
    total_premium: r2(total), employee_premium: r2(total * r.employee_share_pct / 100),
    employer_premium_50_plus: r2(total * r.employer_share_pct / 100) };
};
const workerCost = [
  ...WAGES.map(w => costRow(2027, w)),
  costRow(2026, WAGES[2]),
  costRow(2019, WAGES[2]),
];

// Table 3: ESD actuary's projection by calendar (leave) year, Nov. 2025 report, Figure 17 ($ millions).
const PROJ = [
  // year, rate, assessed premium, leave expenditure, small business grants, admin, interest, pricing income (as printed), combined ratio %
  [2026, 1.13, 2827, 2603, 13, 97, 2, 115, 96],
  [2027, 1.20, 3105, 2939, 20, 92, 3, 58, 98],
  [2028, 1.20, 3237, 3352, 32, 95, 2, -240, 107],
  [2029, 1.20, 3376, 3830, 44, 97, 0, -596, 118],
];
const proj = PROJ.map(([year, rate, prem, leave, grants, admin, interest, income, cr]) => ({
  calendar_year: year, premium_rate_pct: rate, assessed_premium_m: prem, leave_benefits_m: leave,
  small_business_grants_m: grants, administrative_expenses_m: admin, interest_income_m: interest,
  total_net_costs_m: leave + grants + admin - interest, pricing_income_m: income, combined_ratio_pct: cr,
}));

// Table 4: ESD actuary's account projection by fiscal year (July-June), Nov. 2025 report, Figure 7 ($ millions).
const acct = [
  [2026, 2379, 2354, 5, 100, -26], [2027, 2898, 2710, 15, 88, 60],
  [2028, 3138, 3071, 26, 90, 12], [2029, 3274, 3506, 38, 95, -353],
].map(([fy, prem, ben, grants, admin, bal]) => ({ fiscal_year: fy, premium_collection_m: prem,
  leave_benefit_payment_m: ben, small_business_grants_m: grants, net_administrative_costs_m: admin,
  end_of_year_account_balance_m: bal }));

// Table 5: revenue vs expenses, 2020-2025 ($ millions). 2020-2023: JLARC Report 25-01, Figure 2 (actuals);
// 2024-2025: ESD actuary Nov. 2025, Figure 8 (adjusted unaudited financial statements, fiscal years).
const hist = [
  [2020, 678, 665, 'jlarc-2025'], [2021, 701, 959, 'jlarc-2025'], [2022, 1109, 1258, 'jlarc-2025'],
  [2023, 1635, 1538, 'jlarc-2025'], [2024, 1742, 1773, 'esd-actuarial-2025'], [2025, 1986, 2144, 'esd-actuarial-2025'],
].map(([year, rev, exp, src]) => ({ year, revenue_m: rev, expenses_m: exp, net_income_m: rev - exp, source_id: src }));

// Table 6: what drove costs.
const drivers = [
  ['applications', 'Benefit applications per year', '2020', 168000, '2023', 259000, 'applications', 'jlarc-2025'],
  ['max-weekly-benefit', 'Maximum weekly benefit', '2020', 1000, '2024', 1456, 'dollars per week', 'jlarc-2025'],
  ['avg-weekly-benefit', 'Average weekly benefit', '2020', 630, '2024', 878, 'dollars per week', 'jlarc-2025'],
  ['monthly-benefit-payments', 'Benefit payments in the month of January', 'January 2023', 117, 'January 2025', 171, 'millions of dollars', 'esd-actuarial-2025'],
  ['paid-weekly-claims', 'Paid weekly claims in the month of January', 'January 2023', 141000, 'January 2025', 191000, 'weekly claims', 'esd-actuarial-2025'],
  ['family-cost-per-claim', 'Benefit per Family Leave claim (approximate)', 'about 2022', 6700, '2025', 8000, 'dollars per claim', 'esd-actuarial-2025'],
  ['medical-cost-per-claim', 'Benefit per Medical Leave claim (approximate)', 'about 2022', 6100, '2025', 7100, 'dollars per claim', 'esd-actuarial-2025'],
].map(([id, metric, p0, v0, p1, v1, unit, src]) => ({ driver_id: id, metric, start_period: p0, start_value: v0,
  end_period: p1, end_value: v1, unit, percent_change: r1((v1 / v0 - 1) * 100), source_id: src }));

// Saved-analysis results.
const r27 = rates.find(r => r.year === 2027), r26 = rates.find(r => r.year === 2026), r19 = rates[0];
const med = y => workerCost.find(w => w.premium_year === y && w.wage_point_id === 'p50');
const results = {
  rateRows: rates.map(r => ({ year: r.year, rate_pct: r.total_premium_rate_pct })),
  arithmetic: [
    { measure: '2027 rate as a multiple of the 2019 rate', value: r2(r27.total_premium_rate_pct / r19.total_premium_rate_pct), unit: 'times' },
    { measure: 'Change in the rate from 2026 to 2027', value: r1((r27.total_premium_rate_pct / r26.total_premium_rate_pct - 1) * 100), unit: 'percent' },
    { measure: 'Change in the rate from 2026 to 2027', value: r2(r27.total_premium_rate_pct - r26.total_premium_rate_pct), unit: 'percentage points' },
    { measure: 'Change in the rate from 2025 to 2026', value: r1((r26.total_premium_rate_pct / 0.92 - 1) * 100), unit: 'percent' },
    { measure: 'Employee premium at the median wage, 2027 vs. 2019 rules', value: r2(med(2027).employee_premium / med(2019).employee_premium), unit: 'times' },
  ],
  workerCost,
  pricingRows: proj.flatMap(p => [
    { year: p.calendar_year, series: 'Premiums at the projected rate', amount_m: p.assessed_premium_m },
    { year: p.calendar_year, series: 'Benefits and other costs', amount_m: p.total_net_costs_m },
  ]),
  proj,
  acct,
  histRows: hist.flatMap(h => [
    { year: h.year, series: 'Revenue', amount_m: h.revenue_m },
    { year: h.year, series: 'Expenses', amount_m: h.expenses_m },
  ]),
  hist,
  drivers,
};
fs.writeFileSync(path.join(out, 'premium-rates.csv'), csv(rates));
fs.writeFileSync(path.join(out, 'worker-premium-cost.csv'), csv(workerCost));
fs.writeFileSync(path.join(out, 'actuarial-pricing-projection.csv'), csv(proj));
fs.writeFileSync(path.join(out, 'account-balance-projection.csv'), csv(acct));
fs.writeFileSync(path.join(out, 'revenue-expenses-history.csv'), csv(hist));
fs.writeFileSync(path.join(out, 'cost-drivers.csv'), csv(drivers));
fs.writeFileSync(path.join(out, 'results.json'), JSON.stringify(results, null, 1));
console.log(JSON.stringify({ arithmetic: results.arithmetic, med: [med(2019), med(2026), med(2027)], proj, drivers }, null, 1));