// Builds the curated tables and saved-analysis results for the FactHub dataset
// wa-big-employers-hq-layoffs from the inputs folder. Run: node build.js
// Inputs: inputs/sec-submissions.json (SEC EDGAR submissions API, pulled 2026-11-03),
// inputs/wa-esd-warn-notices-2018-2026.csv (ESD WARN database, scraped 2026-11-02/03),
// inputs/official-series.json (BLS QCEW, Census BFS via FRED, population, ESD workbook).
const fs = require('fs');
const path = require('path');
const dir = __dirname;
const out = path.join(dir, 'out');
fs.mkdirSync(out, { recursive: true });
const series = JSON.parse(fs.readFileSync(path.join(dir, 'inputs/official-series.json'), 'utf8'));
const sec = JSON.parse(fs.readFileSync(path.join(dir, 'inputs/sec-submissions.json'), 'utf8'));
const r1 = x => Math.round(x * 10) / 10;
const r2 = x => Math.round(x * 100) / 100;
const pct = (a, b) => r1(100 * (a / b - 1));
const csv = (file, header, rows) => {
  const esc = v => (v === null || v === undefined) ? '' : (/[",\n]/.test(String(v)) ? '"' + String(v).replace(/"/g, '""') + '"' : String(v));
  fs.writeFileSync(path.join(out, file), [header.join(','), ...rows.map(r => header.map(h => esc(r[h])).join(','))].join('\n') + '\n');
};
const analyses = {};

// 1. Headquarters of large Washington-based public companies (SEC business address)
const notes = {
  'Nordstrom': 'Taken private in May 2025; latest annual report filed March 2025.',
  'Starbucks': 'Opening a second corporate office in Nashville announced 2026; Seattle remains global headquarters.',
  'Columbia Banking System': 'Moving most executive roles to a Scottsdale, Arizona office announced October 2026; filings list Tacoma.'
};
const hq = sec.map(s => ({
  company: s.n, cik: s.cik, secName: s.name, businessCity: s.city, businessState: s.state,
  latestAnnualReport: '10-K', latestAnnualReportFiled: s.last10K, accessionNumber: s.acc,
  stillWashington: s.state === 'WA', note: notes[s.n] || ''
}));
csv('company-hq.csv', Object.keys(hq[0]), hq);
analyses['hq-status'] = { rows: hq.map(h => ({ company: h.company, businessCity: h.businessCity, businessState: h.businessState, latestAnnualReportFiled: h.latestAnnualReportFiled, note: h.note })), checked: hq.length, inWashington: hq.filter(h => h.stillWashington).length };

// 2. Private worksites with 1,000 or more workers, first quarter
const ls = series.qcewLargeWorksites;
const years = Object.keys(ls.WA);
const big = years.map(y => ({ year: +y, waSites: ls.WA[y][0], usSites: ls.US[y][0], waMarchJobs: ls.WA[y][1], usMarchJobs: ls.US[y][1] }));
csv('large-worksites.csv', Object.keys(big[0]), big);
const idx = [];
for (const b of big) {
  idx.push({ year: b.year, area: 'Washington', index: r1(100 * b.waSites / big[0].waSites), sites: b.waSites });
  idx.push({ year: b.year, area: 'United States', index: r1(100 * b.usSites / big[0].usSites), sites: b.usSites });
}
analyses['big-sites-index'] = idx;
const bl = big[big.length - 1], bp = big[big.length - 2], b23 = big.find(b => b.year === 2023), b22 = big.find(b => b.year === 2022);
analyses['big-sites-change'] = [
  { period: '2019 to 2026', area: 'Washington', from: big[0].waSites, to: bl.waSites, pctChange: pct(bl.waSites, big[0].waSites) },
  { period: '2019 to 2026', area: 'United States', from: big[0].usSites, to: bl.usSites, pctChange: pct(bl.usSites, big[0].usSites) },
  { period: '2019 to 2022', area: 'Washington', from: big[0].waSites, to: b22.waSites, pctChange: pct(b22.waSites, big[0].waSites) },
  { period: '2019 to 2022', area: 'United States', from: big[0].usSites, to: b22.usSites, pctChange: pct(b22.usSites, big[0].usSites) },
  { period: '2023 to 2026', area: 'Washington', from: b23.waSites, to: bl.waSites, pctChange: pct(bl.waSites, b23.waSites) },
  { period: '2023 to 2026', area: 'United States', from: b23.usSites, to: bl.usSites, pctChange: pct(bl.usSites, b23.usSites) },
  { period: '2025 to 2026', area: 'Washington', from: bp.waSites, to: bl.waSites, pctChange: pct(bl.waSites, bp.waSites) },
  { period: '2025 to 2026', area: 'United States', from: bp.usSites, to: bl.usSites, pctChange: pct(bl.usSites, bp.usSites) },
  { period: 'March 2025 to March 2026, jobs at these sites', area: 'Washington', from: bp.waMarchJobs, to: bl.waMarchJobs, pctChange: pct(bl.waMarchJobs, bp.waMarchJobs) },
  { period: 'March 2025 to March 2026, jobs at these sites', area: 'United States', from: bp.usMarchJobs, to: bl.usMarchJobs, pctChange: pct(bl.usMarchJobs, bp.usMarchJobs) }
];

// 3. Private establishments and private jobs (QCEW)
const ea = series.qcewEstablishmentsPrivate, ja = series.qcewPrivateJobs;
const est = Object.keys(ea.annualAverage.WA).map(y => ({ year: +y, basis: 'annual average', waEstablishments: ea.annualAverage.WA[y], usEstablishments: ea.annualAverage.US[y], waPrivateJobs: ja.annualAverage.WA[y], usPrivateJobs: ja.annualAverage.US[y] }));
for (const y of Object.keys(ea.firstQuarter.WA)) est.push({ year: +y, basis: 'first quarter (jobs: March)', waEstablishments: ea.firstQuarter.WA[y], usEstablishments: ea.firstQuarter.US[y], waPrivateJobs: ja.march.WA[y] ?? null, usPrivateJobs: ja.march.US[y] ?? null });
csv('establishments-jobs.csv', Object.keys(est[0]), est);
const A = ea.annualAverage, Q = ea.firstQuarter;
analyses['estab-change'] = [
  { period: '2023 to 2025 annual average', waPctChange: pct(A.WA['2025'], A.WA['2023']), usPctChange: pct(A.US['2025'], A.US['2023']) },
  { period: '2019 to 2025 annual average (crosses a 2022-23 break in Washington counts)', waPctChange: pct(A.WA['2025'], A.WA['2019']), usPctChange: pct(A.US['2025'], A.US['2019']) },
  { period: 'First quarter 2025 to first quarter 2026 (preliminary)', waPctChange: pct(Q.WA['2026'], Q.WA['2025']), usPctChange: pct(Q.US['2026'], Q.US['2025']) },
  { period: 'Private jobs, 2024 to 2025 annual average', waPctChange: r2(100 * (ja.annualAverage.WA['2025'] / ja.annualAverage.WA['2024'] - 1)), usPctChange: r2(100 * (ja.annualAverage.US['2025'] / ja.annualAverage.US['2024'] - 1)) },
  { period: 'Private jobs, March 2025 to March 2026 (preliminary)', waPctChange: pct(ja.march.WA['2026'], ja.march.WA['2025']), usPctChange: pct(ja.march.US['2026'], ja.march.US['2025']) }
];

// 4. WARN notices: farm and orchard employers flagged by name
const parse = l => { const o = []; let c = '', q = false; for (const ch of l) { if (ch === '"') { q = !q; continue; } if (ch === ',' && !q) { o.push(c); c = ''; continue; } c += ch; } o.push(c); return o; };
const farmRe = /orchard|\bfarms?\b|farming|farm labor|farm services|fruit|growers|ranch|vineyard|agri|\bag\b|congdon packing/i;
const notFarm = /farmers new world life|state farm/i;
const lines = fs.readFileSync(path.join(dir, 'inputs/wa-esd-warn-notices-2018-2026.csv'), 'utf8').trim().split(/\r?\n/);
const notices = lines.map(parse).map(r => {
  const [m, d, y] = r[6].split('/');
  r[0] = r[0].replace(/'/g, "'").replace(/&/g, '&'); r[1] = r[1].replace(/'/g, "'").replace(/&/g, '&');
  return { company: r[0], location: r[1], layoffStartDate: r[2], workers: +r[3], closureOrLayoff: r[4], temporaryOrPermanent: r[5], receivedDate: `${y}-${m.padStart(2, '0')}-${d.padStart(2, '0')}`, receivedYear: +y, farmOrOrchard: farmRe.test(r[0]) && !notFarm.test(r[0]) };
});
csv('warn-notices.csv', Object.keys(notices[0]), notices);
const wy = {};
for (const n of notices) {
  const w = wy[n.receivedYear] ||= { year: n.receivedYear, notices: 0, workers: 0, closureWorkers: 0, farmWorkers: 0, otherWorkers: 0, partialYear: n.receivedYear === 2018 || n.receivedYear === 2026 };
  w.notices++; w.workers += n.workers; if (n.closureOrLayoff === 'Closure') w.closureWorkers += n.workers;
  if (n.farmOrOrchard) w.farmWorkers += n.workers; else w.otherWorkers += n.workers;
}
const warnYears = Object.values(wy).sort((a, b) => a.year - b.year);
csv('warn-by-year.csv', Object.keys(warnYears[0]), warnYears);
analyses['warn-totals'] = warnYears.filter(w => w.year >= 2019).map(w => ({ year: w.year, notices: w.notices, workers: w.workers, farmWorkers: w.farmWorkers, otherWorkers: w.otherWorkers, closureWorkers: w.closureWorkers, throughNov2: w.year === 2026 }));
analyses['warn-by-group'] = warnYears.filter(w => w.year >= 2019).flatMap(w => [
  { year: String(w.year === 2026 ? '2026 (to Nov. 2)' : w.year), group: 'Farms and orchards', workers: w.farmWorkers },
  { year: String(w.year === 2026 ? '2026 (to Nov. 2)' : w.year), group: 'All other employers', workers: w.otherWorkers }
]);
const big2526 = {};
for (const n of notices.filter(n => n.receivedYear >= 2025 && !n.farmOrOrchard)) {
  const k = n.company.replace(/,? Inc\.?$/, '').replace(/ America$/, '').trim();
  big2526[k] = (big2526[k] || 0) + n.workers;
}
analyses['warn-top-2025-2026'] = Object.entries(big2526).sort((a, b) => b[1] - a[1]).slice(0, 8).map(([company, workers]) => ({ company, workers }));
// Twelve months before and after Washington's own notice law took effect (July 27, 2025)
const win = (label, a, b) => {
  const s = notices.filter(n => n.receivedDate >= a && n.receivedDate <= b), f = s.filter(n => n.farmOrOrchard);
  const w = s.reduce((x, n) => x + n.workers, 0), fw = f.reduce((x, n) => x + n.workers, 0);
  return { window: label, from: a, to: b, notices: s.length, workers: w, farmNotices: f.length, farmWorkers: fw, otherWorkers: w - fw };
};
analyses['warn-law-change'] = [
  win('Two years before the state law', '2023-07-27', '2024-07-26'),
  win('Year before the state law', '2024-07-27', '2025-07-26'),
  win('First year under the state law', '2025-07-27', '2026-07-26')
];
const farmNames = [...new Set(notices.filter(n => n.farmOrOrchard).map(n => n.company))].sort();

// 5. High-propensity business applications per 1,000 residents
const bfs = series.bfsHighPropensityMonthly, pop = series.populationThousands;
const form = [];
for (let y = 2015; y <= 2025; y++) {
  const i = (y - 2015) * 12;
  const wa = bfs.WA.slice(i, i + 12).reduce((s, v) => s + v, 0), us = bfs.US.slice(i, i + 12).reduce((s, v) => s + v, 0);
  const waRate = wa / pop.WA[y], usRate = us / pop.US[y];
  form.push({ year: y, waApplications: wa, usApplications: us, waPopulationThousands: pop.WA[y], usPopulationThousands: pop.US[y], waPer1000: r2(waRate), usPer1000: r2(usRate), waShareOfUsRate: Math.round(100 * waRate / usRate) });
}
csv('business-formation.csv', Object.keys(form[0]), form);
analyses['formation-rate'] = form.map(f => ({ year: f.year, waPer1000: f.waPer1000, usPer1000: f.usPer1000, waShareOfUsRate: f.waShareOfUsRate }));
const js = (arr, y) => arr.slice((y - 2015) * 12, (y - 2015) * 12 + 9).reduce((s, v) => s + v, 0);
analyses['formation-2026-ytd'] = [
  { area: 'Washington', janSep2025: js(bfs.WA, 2025), janSep2026: js(bfs.WA, 2026), pctChange: pct(js(bfs.WA, 2026), js(bfs.WA, 2025)) },
  { area: 'United States', janSep2025: js(bfs.US, 2025), janSep2026: js(bfs.US, 2026), pctChange: pct(js(bfs.US, 2026), js(bfs.US, 2025)) }
];

// 6. ESD September 2026 over-the-year change; private = total nonfarm minus government
const esd = series.esdSept2026OverYear.rows.map(([industry, change, share]) => ({ industry, changeSep2025toSep2026: change, pctChange: r2(100 * share) }));
csv('esd-sept-2026-over-year.csv', Object.keys(esd[0]), esd);
const tot = series.esdSept2026OverYear.rows[0], gov = series.esdSept2026OverYear.rows.find(r => r[0] === 'Government');
const privBase = tot[1] / tot[2] - gov[1] / gov[2];
analyses['esd-private-sept-2026'] = { totalNonfarmChange: tot[1], governmentChange: gov[1], privateChange: tot[1] - gov[1], privatePctChange: r2(100 * (tot[1] - gov[1]) / privBase), period: 'September 2025 to September 2026, not seasonally adjusted' };

fs.copyFileSync(path.join(dir, 'inputs/company-moves.csv'), path.join(out, 'company-moves.csv'));
fs.writeFileSync(path.join(out, 'analyses.json'), JSON.stringify(analyses, null, 1));
fs.writeFileSync(path.join(out, 'farm-employer-names.txt'), farmNames.join('\n') + '\n');
console.log(JSON.stringify(analyses, null, 1));
console.log('farm names:', farmNames.length);