// Builds the wa-i638-campaign-finance dataset tables from PDC open data on data.wa.gov (Socrata).
// Usage: node i638_campaign_finance.js <rawDir> <outDir>
// Downloads to <rawDir> (cached; set REFRESH=1 to re-download); writes CSV tables and results.json to <outDir>.
// User-Agent identifies FactHub only. Organizations giving $5,000+ are named; individuals are named only if they
// gave $25,000 or more to one committee (the I-645 dataset's threshold). No addresses are kept.
const { execFileSync } = require('child_process');
const fs = require('fs');
const path = require('path');
const RAW = process.argv[2] || 'raw', OUT = process.argv[3] || 'out';
fs.mkdirSync(RAW, { recursive: true }); fs.mkdirSync(OUT, { recursive: true });
const UA = 'FactHubResearch/1.0 (https://facthub.org)';
function soql(dataset, params, cacheName) {
  const f = path.join(RAW, cacheName + '.json');
  if (fs.existsSync(f) && !process.env.REFRESH) return JSON.parse(fs.readFileSync(f));
  const args = ['-s', '-A', UA, '-G', `https://data.wa.gov/resource/${dataset}.json`];
  for (const [k, v] of Object.entries(params)) args.push('--data-urlencode', `$${k}=${v}`);
  const txt = execFileSync('curl', args, { maxBuffer: 1 << 28 }).toString();
  const j = JSON.parse(txt); if (!Array.isArray(j)) throw new Error(txt.slice(0, 500));
  fs.writeFileSync(f, txt); return j;
}
const csvCell = v => { const s = v === null || v === undefined ? '' : String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
const writeCsv = (file, cols, rows) => fs.writeFileSync(path.join(OUT, file), [cols.join(','), ...rows.map(r => cols.map(c => csvCell(r[c])).join(','))].join('\n') + '\n');
const r2 = x => Math.round(x * 100) / 100, r4 = x => Math.round(x * 10000) / 10000;

// ---------- 0. Every committee registered for or against I-638 (2026 summary) ----------
const summ = soql('3h9x-7bvm', { where: "election_year='2026' and ballot_number like '%638%'", limit: '500' }, 'summary_2026_638');
const committeeRows = summ.map(r => ({
  filer_id: r.filer_id, committee: r.filer_name, position: r.for_or_against, measures: r.ballot_number,
  measure_count: (r.ballot_number || '').split(',').length,
  contributions_2026: r2(+(r.contributions_amount || 0)), expenditures_2026: r2(+(r.expenditures_amount || 0)),
  summary_updated: (r.updated_at || '').slice(0, 10),
})).sort((a, b) => b.contributions_2026 - a.contributions_2026);
writeCsv('i638_committees.csv', Object.keys(committeeRows[0]), committeeRows);

// ---------- 1. Contributions to the two main committees, 2025 and 2026 election years ----------
const MAIN = [
  { id: 'HEYWL--535', name: "Let's Go Washington (Sponsored by Brian Heywood)", side: 'Yes', measures: 'IL26-001, IL26-638, IP26-645' },
  { id: 'FREEW--138', name: 'No Hate in WA State', side: 'No', measures: 'IL26-001, IL26-638' },
];
const ADV = 'Civil-liberties, LGBTQ or reproductive-rights group';
const ORG = {
  'Inatai Foundation': 'Foundation or philanthropy', 'Social Justice Fund': 'Foundation or philanthropy', 'Northwest Health Foundation': 'Foundation or philanthropy',
  'The Nelson Foundation': 'Foundation or philanthropy',
  'SEIU 775 Ballot Fund': 'Labor union', 'Washington Education Association': 'Labor union', 'National Education Association': 'Labor union',
  'SEIU Initiative Fund': 'Labor union', 'UFCW Local 3000': 'Labor union', 'WFSE': 'Labor union', 'SEIU 925 Ballot Fund': 'Labor union',
  'Teamsters Local Union 117': 'Labor union', 'SEIU Washington State Council': 'Labor union', 'Spokane Regional Labor Council, AFL-CIO': 'Labor union',
  'ACLU of Washington': ADV, 'Gender Justice League': ADV, 'Human Rights Campaign': ADV, 'Lavender Rights Project': ADV,
  'Pro-Choice Washington': ADV, 'Planned Parenthood Alliance Advocates': ADV, 'Planned Parenthood Great Northwest, Hawaii, Alaska, Indiana, Kentucky': ADV,
  'Equal Rights for All Petition Committee': 'Political committee', 'Invest in Washington Now': 'Political committee', 'Build Our Movement PAC': 'Political committee',
  'Pacific County Voices Uniting': 'Political committee', 'Evergreen Leadership Fund': 'Political committee',
  'Washington Bus': 'Other nonprofit', 'Washington Bus Education Fund': 'Other nonprofit', 'The Washington Bus': 'Other nonprofit',
  'WA State Coalition Against Domestic Violence & Sexual Assault': 'Other nonprofit', 'Washington Rising Inc': 'Other nonprofit',
  'Building Industry Association of Washington': 'Business or trade group', 'BIAW Member Services Corporation': 'Business or trade group',
  'Associated Builders and Contractors Inland Pacific Chapter, Inc.': 'Business or trade group',
};
const NAME_MIN = 25000, ORG_MIN = 5000;
const contrib = {}, donorRows = [], typeAgg = {}, stateAgg = {}, yearAgg = {};
for (const c of MAIN) {
  const rows = soql('kv7h-kjye', { where: `filer_id='${c.id}' and election_year in('2025','2026')`, limit: '50000' }, 'con_' + c.id);
  contrib[c.id] = rows;
  const by = {};
  for (const r of rows) {
    const k = r.contributor_name;
    by[k] = by[k] || { name: k, category: r.contributor_category, state: r.contributor_state || '', cash: 0, inkind: 0, n: 0, y2025: 0, y2026: 0 };
    const a = +r.amount;
    if (r.cash_or_in_kind === 'In-kind') by[k].inkind += a; else by[k].cash += a;
    by[k]['y' + r.election_year] += a; by[k].n += 1;
    const yk = c.id + '|' + r.election_year; yearAgg[yk] = (yearAgg[yk] || 0) + a;
  }
  const agg = {};
  for (const d of Object.values(by)) {
    const total = d.cash + d.inkind;
    let type = ORG[d.name];
    if (!type) {
      if (/^(SMALL CONTRIBUTIONS|MISCELLANEOUS RECEIPTS)$/i.test(d.name)) type = 'Small or unitemized contributions';
      else if (d.category === 'Individual') type = /^HEYWOOD BRIAN$/i.test(d.name) ? 'Brian Heywood' : 'Individual';
      else type = 'Business or other organization';
    }
    const loc = type === 'Small or unitemized contributions' ? 'Not reported' : (d.state === 'WA' ? 'Washington' : (d.state ? 'Outside Washington' : 'Not reported'));
    const sk = c.id + '|' + loc; stateAgg[sk] = (stateAgg[sk] || 0) + total;
    const tk = c.id + '|' + type; typeAgg[tk] = (typeAgg[tk] || 0) + total;
    const named = type !== 'Small or unitemized contributions' && (d.category !== 'Individual' ? total >= ORG_MIN : total >= NAME_MIN);
    if (named) donorRows.push({ filer_id: c.id, committee: c.name, side: c.side, donor: /^HEYWOOD BRIAN$/i.test(d.name) ? 'Brian Heywood' : d.name, donor_type: type,
      donor_location: loc, cash: r2(d.cash), in_kind: r2(d.inkind), total: r2(total), election_year_2025: r2(d.y2025), election_year_2026: r2(d.y2026), receipts: d.n });
    else {
      const k = type + '|' + loc;
      agg[k] = agg[k] || { filer_id: c.id, committee: c.name, side: c.side,
        donor: type === 'Small or unitemized contributions' ? 'Small contributions (unitemized)' : `Other donors under the naming threshold (${type.toLowerCase()}, ${loc})`,
        donor_type: type, donor_location: loc, cash: 0, in_kind: 0, total: 0, election_year_2025: 0, election_year_2026: 0, receipts: 0 };
      agg[k].cash += d.cash; agg[k].in_kind += d.inkind; agg[k].total += total; agg[k].election_year_2025 += d.y2025; agg[k].election_year_2026 += d.y2026; agg[k].receipts += d.n;
    }
  }
  for (const a of Object.values(agg)) donorRows.push({ ...a, cash: r2(a.cash), in_kind: r2(a.in_kind), total: r2(a.total), election_year_2025: r2(a.election_year_2025), election_year_2026: r2(a.election_year_2026) });
}
donorRows.sort((a, b) => a.side.localeCompare(b.side) || b.total - a.total);
const commTotal = {}; for (const r of donorRows) commTotal[r.filer_id] = (commTotal[r.filer_id] || 0) + r.total;
for (const r of donorRows) r.share_of_committee = r4(r.total / commTotal[r.filer_id]);
writeCsv('i638_donors.csv', ['filer_id', 'committee', 'side', 'donor', 'donor_type', 'donor_location', 'cash', 'in_kind', 'total', 'share_of_committee', 'election_year_2025', 'election_year_2026', 'receipts'], donorRows);

const typeRows = Object.entries(typeAgg).map(([k, v]) => { const [id, t] = k.split('|'); const c = MAIN.find(m => m.id === id);
  return { filer_id: id, committee: c.name, side: c.side, donor_type: t, total: r2(v), share_of_committee: r4(v / commTotal[id]) }; })
  .sort((a, b) => a.side.localeCompare(b.side) || b.total - a.total);
writeCsv('i638_raised_by_donor_type.csv', Object.keys(typeRows[0]), typeRows);
const locRows = Object.entries(stateAgg).map(([k, v]) => { const [id, l] = k.split('|'); const c = MAIN.find(m => m.id === id);
  return { filer_id: id, committee: c.name, side: c.side, donor_location: l, total: r2(v), share_of_committee: r4(v / commTotal[id]) }; })
  .sort((a, b) => a.side.localeCompare(b.side) || b.total - a.total);
writeCsv('i638_raised_by_location.csv', Object.keys(locRows[0]), locRows);

// ---------- 2. Expenditures, allocated to I-638 from each item's own description ----------
// Rule: find the 2026 measure numbers (001, 638, 645) an item's description names. An item naming I-638 counts
// toward I-638 in equal shares across the measures it names. Items naming no measure are 'not allocated'.
// Items naming only 2023-24 measures (I-2066 to I-2124) are 'other measures'.
const allocRows = [];
for (const c of MAIN) {
  const expAll = soql('tijg-9zyp', { where: `filer_id='${c.id}' and election_year in('2025','2026')`, limit: '50000' }, 'exp_' + c.id);
  for (const period of ['2025', '2026', '2025-2026']) {
  const exp = period === '2025-2026' ? expAll : expAll.filter(x => x.election_year === period);
  const a = { described638: 0, described638Only: 0, sharedItems638: 0, otherMeasures: 0, notAllocated: 0, total: 0, n638: 0, explicitMismatch: 0 };
  for (const x of exp) {
    const amt = +x.amount, s = x.description || ''; a.total += amt;
    const ms = new Set((s.match(/(?:IL|IP|I)?\s*-?\s*26\s*-?\s*(001|638|645)\b|\b(001|638|645)\b/g) || []).map(m => m.match(/(001|638|645)/)[1]));
    if (!ms.size) { if (/\b(2066|2081|2109|2111|2113|2117|2124)\b/.test(s)) a.otherMeasures += amt; else a.notAllocated += amt; continue; }
    if (!ms.has('638')) { a.otherMeasures += amt; continue; }
    const share = amt / ms.size; a.described638 += share; a.n638++;
    if (ms.size === 1) a.described638Only += amt; else a.sharedItems638 += share;
    a.otherMeasures += amt - share;
    // check: explicit I-638 dollar estimate in the description, if any, vs the equal split
    const m = s.match(/638\s*\(\s*(?:est\.?\s*)?\$\s*([\d,]+(?:\.\d+)?)\s*(k)?/i) || s.match(/\$\s*([\d,]+(?:\.\d+)?)\s*(k)?\s*opposing\s*IL26-638/i);
    if (m && ms.size > 1) { const v = +m[1].replace(/,/g, '') * (m[2] ? 1000 : 1); if (Math.abs(v - share) > 1) a.explicitMismatch += v - share; }
  }
  const allocated = a.total - a.notAllocated;
  const prorated = a.described638 + a.notAllocated * (allocated ? a.described638 / allocated : 0);
  allocRows.push({ filer_id: c.id, committee: c.name, side: c.side, measures: c.measures, election_years: period, expenditures_total: r2(a.total),
    described_i638_only: r2(a.described638Only), i638_share_of_shared_items: r2(a.sharedItems638), described_i638_total: r2(a.described638),
    other_measures: r2(a.otherMeasures), not_allocated: r2(a.notAllocated), i638_with_prorated_overhead: r2(prorated),
    i638_items: a.n638, explicit_estimate_minus_equal_split: r2(a.explicitMismatch) });
  }
}
writeCsv('i638_spending_allocation.csv', Object.keys(allocRows[0]), allocRows);

// ---------- 3. Loans to LGW ----------
const loans = soql('d2ig-r3q4', { where: "filer_id='HEYWL--535'", limit: '1000' }, 'loans_HEYWL--535');
const lenders = {}; for (const l of loans) { const k = (l.lenders_name || '') + '|' + l.election_year + '|' + (/Carry forward/i.test(l.description || '') ? 'carry-forward' : 'new'); lenders[k] = r2((lenders[k] || 0) + +l.amount); }

const named = id => donorRows.filter(r => r.filer_id === id && !/^Other donors|^Small/.test(r.donor));
const results = {
  runDate: new Date().toISOString().slice(0, 10),
  latestReceipt: Object.fromEntries(MAIN.map(c => [c.id, contrib[c.id].map(r => (r.receipt_date || '').slice(0, 10)).filter(Boolean).sort().pop()])),
  committeeTotals: Object.fromEntries(Object.entries(commTotal).map(([k, v]) => [k, r2(v)])),
  byYear: Object.fromEntries(Object.entries(yearAgg).map(([k, v]) => [k, r2(v)])),
  topDonors: Object.fromEntries(MAIN.map(c => [c.id, named(c.id).slice(0, 12).map(r => [r.donor, r.donor_type, r.donor_location, r.total, r.share_of_committee, r.election_year_2025, r.election_year_2026])])),
  top5Share: Object.fromEntries(MAIN.map(c => [c.id, r4(named(c.id).slice(0, 5).reduce((t, r) => t + r.total, 0) / commTotal[c.id])])),
  byType: typeRows, byLocation: locRows, allocation: allocRows, lenders,
  committees: committeeRows.length,
  otherCommitteeTotals: committeeRows.filter(r => !MAIN.some(m => m.id === r.filer_id)).reduce((t, r) => { t[r.position] = r2((t[r.position] || 0) + r.contributions_2026); return t; }, {}),
};
fs.writeFileSync(path.join(OUT, 'results.json'), JSON.stringify(results, null, 1));
console.log(JSON.stringify(results, null, 1));