// Builds the FactHub dataset files for "Washington movers' income, IRS migration data".
// Usage: node fh-build-migration-dataset.js <dir with IRS SOI CSVs> <output dir>
// Inputs (downloaded from https://www.irs.gov/pub/irs-soi/): stateinflowYYYY.csv,
// stateoutflowYYYY.csv and YYYYinmigall.csv for YYYY = 1112 ... 2223.
const fs = require('fs'), path = require('path');
const [IN, OUT] = process.argv.slice(2);
const YRS = ['1112','1213','1314','1415','1516','1617','1718','1819','1920','2021','2122','2223'];
const PEERS = { WA: '53', OR: '41', CA: '06', ID: '16', TX: '48', FL: '12', AZ: '04' };
const AGES = ['under 26', '26-34', '35-44', '45-54', '55-64', '65 and over'];
const label = y => `20${y.slice(0, 2)}-${y.slice(2)}`;
const r1 = x => Math.round(x * 10) / 10;
const read = f => fs.readFileSync(path.join(IN, f), 'latin1').split(/\r?\n/).filter(Boolean)
  .slice(1).map(l => l.split(',').map(s => s.replace(/"/g, '').trim()));
// "Total Migration-US" (code 97, not "Same State", which reuses 97)
const isUS = c => c[1] === '97' && /Total Migration.?US$/i.test(c[3]);
function gross(y) {
  const L = fs.readFileSync(path.join(IN, `${y}inmigall.csv`), 'latin1').split(/\r?\n/).filter(Boolean);
  const h = L[0].toLowerCase().split(',').map(s => s.replace(/"/g, '').trim());
  return L.slice(1).map(l => { const c = l.split(',').map(s => s.replace(/"/g, '').trim());
    return { st: c[1], stub: c[3], g: n => +c[h.indexOf(n)] }; });
}
const csv = (rows) => rows.map(r => r.map(v => v === null ? '' : (typeof v === 'string' && /[,"]/.test(v) ? `"${v.replace(/"/g, '""')}"` : v)).join(',')).join('\n') + '\n';

// Table 1: state-year flows for WA and six peers
const T1 = [['state','filing_years','in_returns','in_individuals','in_agi_thousands','out_returns','out_individuals','out_agi_thousands',
  'avg_agi_in','avg_agi_out','leaver_premium_pct','agi_per_person_in','agi_per_person_out','per_person_premium_pct',
  'net_returns','net_agi_thousands','hi_out_returns','hi_in_returns','hi_net_returns','hi_matched_returns','hi_net_pct_of_matched','hi_net_agi_thousands']];
const res = { waAvg: [], peerGap: [], waHigh: [], peerHigh2223: [], waY1Y2: [], waAge: [], allStates2223: [], allStates2122: [], partners2223: [] };
const AGE = [['filing_years','age_band','out_returns','out_agi_thousands','in_returns','in_agi_thousands','avg_agi_out','avg_agi_in']];
for (const y of YRS) {
  const inf = read(`stateinflow${y}.csv`), out = read(`stateoutflow${y}.csv`), G = gross(y);
  for (const [st, f] of Object.entries(PEERS)) {
    const i = inf.find(c => c[0] === f && isUS(c)), o = out.find(c => c[0] === f && isUS(c));
    const hi = G.find(r => r.st === st && r.stub === '7').g;
    const [ir, ii, ia, or_, oi, oa] = [+i[4], +i[5], +i[6], +o[4], +o[5], +o[6]];
    const avgIn = ia * 1000 / ir, avgOut = oa * 1000 / or_, ppIn = ia * 1000 / ii, ppOut = oa * 1000 / oi;
    const hiNet = hi('inflow_n1_0') - hi('outflow_n1_0');
    T1.push([st, label(y), ir, ii, ia, or_, oi, oa, Math.round(avgIn), Math.round(avgOut), r1((avgOut / avgIn - 1) * 100),
      Math.round(ppIn), Math.round(ppOut), r1((ppOut / ppIn - 1) * 100), ir - or_, ia - oa,
      hi('outflow_n1_0'), hi('inflow_n1_0'), hiNet, hi('total_n1_0'), Math.round(hiNet / hi('total_n1_0') * 10000) / 100,
      hi('inflow_y2_agi_0') - hi('outflow_y2_agi_0')]);
    res.peerGap.push({ filing_years: label(y), state: st, leaver_premium_pct: r1((avgOut / avgIn - 1) * 100) });
    if (st === 'WA') {
      res.waAvg.push({ filing_years: label(y), group: 'Households leaving Washington', avg_agi: Math.round(avgOut) });
      res.waAvg.push({ filing_years: label(y), group: 'Households moving to Washington', avg_agi: Math.round(avgIn) });
      res.waHigh.push({ filing_years: label(y), net_returns_200k_plus: hiNet });
      const a = G.find(r => r.st === 'WA' && r.stub === '0').g;
      if (['2021', '2122', '2223'].includes(y)) res.waY1Y2.push({ filing_years: label(y),
        leavers_avg_agi_year1: Math.round(a('outflow_y1_agi_0') * 1000 / a('outflow_n1_0')),
        arrivals_avg_agi_year1: Math.round(a('inflow_y1_agi_0') * 1000 / a('inflow_n1_0')),
        leavers_avg_agi_year2: Math.round(a('outflow_y2_agi_0') * 1000 / a('outflow_n1_0')),
        arrivals_avg_agi_year2: Math.round(a('inflow_y2_agi_0') * 1000 / a('inflow_n1_0')) });
      if (['1718', '1819', '1920', '2021', '2122', '2223'].includes(y)) {
        let std = 0, n = 0, bandsHigher = 0;
        for (let k = 1; k <= 6; k++) {
          const on = a(`outflow_n1_${k}`), oag = a(`outflow_y2_agi_${k}`), inn = a(`inflow_n1_${k}`), iag = a(`inflow_y2_agi_${k}`);
          AGE.push([label(y), AGES[k - 1], on, oag, inn, iag, Math.round(oag * 1000 / on), Math.round(iag * 1000 / inn)]);
          std += on * iag * 1000 / inn; n += on; if (oag / on > iag / inn) bandsHigher++;
        }
        res.waAge.push({ filing_years: label(y), age_bands_where_leavers_earn_more: bandsHigher,
          leavers_avg_agi: Math.round(a('outflow_y2_agi_0') * 1000 / a('outflow_n1_0')),
          arrivals_avg_agi_at_leavers_age_mix: Math.round(std / n) });
      }
    }
    if (y === '2223') res.peerHigh2223.push({ state: st, net_returns_200k_plus: hiNet, pct_of_matched_200k_plus: Math.round(hiNet / hi('total_n1_0') * 10000) / 100 });
  }
  if (y === '2223') {
    const a = G.find(r => r.st === 'WA' && r.stub === '0').g;
    res.waNetShare = { net_agi_thousands: -265134, total_matched_agi_year2_thousands: a('total_y2_agi_0') };
  }
}
// Table 2: all states, leaver premium 2020-21 to 2022-23
const T2 = [['filing_years','state','rank','avg_agi_in','avg_agi_out','leaver_premium_pct','agi_per_person_in','agi_per_person_out','per_person_premium_pct','net_agi_thousands']];
for (const y of ['2021', '2122', '2223']) {
  const inf = read(`stateinflow${y}.csv`), out = read(`stateoutflow${y}.csv`);
  const arr = inf.filter(isUS).map(i => { const o = out.find(c => isUS(c) && c[0] === i[0]);
    const ai = i[6] * 1000 / i[4], ao = o[6] * 1000 / o[4], pi = i[6] * 1000 / i[5], po = o[6] * 1000 / o[5];
    return { s: i[2], ai, ao, p: (ao / ai - 1) * 100, pi, po, pp: (po / pi - 1) * 100, net: i[6] - o[6] }; })
    .sort((a, b) => b.p - a.p);
  arr.forEach((a, k) => {
    T2.push([label(y), a.s, k + 1, Math.round(a.ai), Math.round(a.ao), r1(a.p), Math.round(a.pi), Math.round(a.po), r1(a.pp), a.net]);
    if (y !== '2021') res[y === '2223' ? 'allStates2223' : 'allStates2122'].push({ rank: k + 1, state: a.s, leaver_premium_pct: r1(a.p), per_person_premium_pct: r1(a.pp) });
  });
}
// Table 3: WA partner states, 2021-22 and 2022-23
const T3 = [['filing_years','state','left_wa_returns','left_wa_agi_thousands','came_to_wa_returns','came_to_wa_agi_thousands','avg_agi_leaving','avg_agi_arriving','net_agi_to_wa_thousands']];
for (const y of ['2122', '2223']) {
  const o = read(`stateoutflow${y}.csv`).filter(c => c[0] === '53' && +c[1] < 57 && c[1] !== '53');
  const i = read(`stateinflow${y}.csv`).filter(c => c[0] === '53' && +c[1] < 57 && c[1] !== '53');
  const rows = o.map(c => { const j = i.find(x => x[1] === c[1]);
    const inN = j ? +j[4] : null, inA = j ? +j[6] : null;
    const ok = v => v !== null && v >= 0;
    return [label(y), c[2], +c[4], +c[6], inN, inA, Math.round(c[6] * 1000 / c[4]), ok(inN) && inN > 0 ? Math.round(inA * 1000 / inN) : null,
      ok(inA) && +c[6] >= 0 ? inA - +c[6] : null]; }).sort((a, b) => b[2] - a[2]);
  T3.push(...rows);
  if (y === '2223') res.partners2223 = rows.slice(0, 8).map(r => ({ state: r[1], left_wa_returns: r[2], came_to_wa_returns: r[4], avg_agi_leaving: r[6], avg_agi_arriving: r[7], net_agi_to_wa_thousands: r[8] }));
}
fs.writeFileSync(path.join(OUT, 'fh-state-year-migration.csv'), csv(T1));
fs.writeFileSync(path.join(OUT, 'fh-all-states-leaver-premium.csv'), csv(T2));
fs.writeFileSync(path.join(OUT, 'fh-wa-partner-states.csv'), csv(T3));
fs.writeFileSync(path.join(OUT, 'fh-wa-age-bands.csv'), csv(AGE));
fs.writeFileSync(path.join(OUT, 'fh-analysis-results.json'), JSON.stringify(res, null, 1) + '\n');
console.log(JSON.stringify({ waY1Y2: res.waY1Y2, waAge: res.waAge, peerHigh2223: res.peerHigh2223, waNetShare: res.waNetShare, partners: res.partners2223 }));