// Builds the wa-i645-campaign-finance dataset tables from PDC open data (data.wa.gov) and leg.wa.gov roll calls.
// Usage: node i645_campaign_finance.js <outDir>
// Network: data.wa.gov (Socrata) and wslwebservices.leg.wa.gov. User-Agent identifies FactHub only.
const { execFileSync } = require('child_process');
const fs = require('fs');
const path = require('path');
const OUT = process.argv[2] || '.';
const UA = 'FactHubResearch/1.0 (https://facthub.org)';

function soql(dataset, params) {
  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 j = JSON.parse(execFileSync('curl', args, { maxBuffer: 1 << 28 }).toString());
  if (!Array.isArray(j)) throw new Error(JSON.stringify(j));
  return j;
}
function get(url) { return execFileSync('curl', ['-s', '-A', UA, url], { maxBuffer: 1 << 28 }).toString(); }
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;

// ---------- 1. Committee contributions by donor ----------
const COMMITTEES = [
  { id: '645-N--960', name: 'No on 645', side: 'No' },
  { id: 'TAX-V--889', name: 'Vote Yes Repeal the Income Tax', side: 'Yes' },
  { id: 'HEYWL--535', name: "Let's Go Washington (Sponsored by Brian Heywood)", side: 'Yes (3 measures)' },
];
// Donor-type classification for labor organizations (by exact PDC contributor name). Everything else is classified
// from PDC's contributor_category (Individual / Organization) plus the business/trade-group lists below.
const LABOR = {
  'Washington Education Association': 'WA public-sector union',
  'Washington Federation of State Employees': 'WA public-sector union',
  'SEIU 775 Ballot Fund': 'WA public-sector union',
  'SEIU 775': 'WA public-sector union',
  'Service Employees International Union Local 925': 'WA public-sector union',
  'Professional & Technical Employees Local 17 PAC': 'WA public-sector union',
  'UAW Local 4121': 'WA public-sector union',
  'National Education Association': 'National public-employee union',
  'American Federation of State, County, Municipal Employees': 'National public-employee union',
  'American Federation of Teachers': 'National public-employee union',
  'SEIU Initiative Fund': 'National union, mixed public and private',
  'SEIU Healthcare 1199NW': 'Health-care union, mostly private employers',
  'Washington State Nurses Association': 'Health-care union, mostly private employers',
  'UFCW Local 3000': 'Private-sector union',
  'North Coast States Regional Council of Carpenters': 'Private-sector union',
  'Teamsters Joint Council 28': 'Private-sector union',
  'Firelands Workers United': 'Private-sector union',
  'NW Regional Council of Smart SSF': 'Private-sector union',
  'PPP Committee': 'Labor federation, mixed public and private',
  'MLK Jr. County Labor Council': 'Labor federation, mixed public and private',
};
const PUBLIC_TYPES = ['WA public-sector union', 'National public-employee union'];
const NAME_MIN = 25000; // donors giving at least this much are named; smaller ones are aggregated by type

const donorRows = [];
for (const c of COMMITTEES) {
  const rows = soql('kv7h-kjye', {
    select: 'contributor_name,contributor_category,cash_or_in_kind,sum(amount) as total,count(*) as n,max(receipt_date) as last',
    where: `filer_id='${c.id}' and election_year='2026'`,
    group: 'contributor_name,contributor_category,cash_or_in_kind', limit: '50000',
  });
  // merge cash + in-kind per donor
  const by = {};
  for (const r of rows) {
    const k = r.contributor_name;
    by[k] = by[k] || { name: k, category: r.contributor_category, cash: 0, inkind: 0, n: 0 };
    if (r.cash_or_in_kind === 'In-kind') by[k].inkind += +r.total; else by[k].cash += +r.total;
    by[k].n += +r.n;
  }
  const agg = {};
  for (const d of Object.values(by)) {
    const total = d.cash + d.inkind;
    let type = LABOR[d.name];
    if (!type) {
      if (d.name === 'SMALL CONTRIBUTIONS' || d.name === 'MISCELLANEOUS RECEIPTS') type = 'Small or unitemized contributions';
      else if (d.category === 'Individual') type = 'Individual';
      else type = 'Business, trade group or other organization';
    }
    const named = total >= NAME_MIN && type !== 'Small or unitemized contributions';
    if (named) donorRows.push({ committee_id: c.id, committee: c.name, side: c.side, donor: d.name, donor_type: type, cash: r2(d.cash), in_kind: r2(d.inkind), total: r2(total), receipts: d.n });
    else {
      const k = type;
      agg[k] = agg[k] || { committee_id: c.id, committee: c.name, side: c.side, donor: `Other donors under $${NAME_MIN.toLocaleString('en-US')} (${type.toLowerCase()})`, donor_type: type, cash: 0, in_kind: 0, total: 0, receipts: 0 };
      agg[k].cash += d.cash; agg[k].in_kind += d.inkind; agg[k].total += total; 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) });
}
donorRows.sort((a, b) => a.committee_id.localeCompare(b.committee_id) || b.total - a.total);
writeCsv('i645_donors.csv', ['committee_id', 'committee', 'side', 'donor', 'donor_type', 'cash', 'in_kind', 'total', 'receipts'], donorRows);

// summary by committee and donor type
const sum = {};
for (const r of donorRows) {
  const k = r.committee_id + '|' + r.donor_type;
  sum[k] = sum[k] || { committee_id: r.committee_id, committee: r.committee, side: r.side, donor_type: r.donor_type, total: 0 };
  sum[k].total += r.total;
}
const commTotals = {};
for (const s of Object.values(sum)) commTotals[s.committee_id] = (commTotals[s.committee_id] || 0) + s.total;
const typeRows = Object.values(sum).map(s => ({ ...s, total: r2(s.total), share_of_committee: Math.round(10000 * s.total / commTotals[s.committee_id]) / 10000 }))
  .sort((a, b) => a.committee_id.localeCompare(b.committee_id) || b.total - a.total);
writeCsv('i645_raised_by_donor_type.csv', ['committee_id', 'committee', 'side', 'donor_type', 'total', 'share_of_committee'], typeRows);

// ---------- 2. LGW 2026 spending allocated to I-645 ----------
const exp = soql('tijg-9zyp', { select: 'amount,description,recipient_name,expenditure_date', where: "filer_id='HEYWL--535' and election_year='2026'", limit: '50000' });
const alloc = { 'Described as supporting IL26-645 only': 0, 'IL26-645 share of shared items': 0, 'Other measures only (IL26-001, IL26-638)': 0, 'Not allocated to a measure': 0 };
for (const x of exp) {
  const a = +x.amount, s = x.description || '';
  const has645 = /645/.test(s), others = /(001|638)/.test(s);
  if (!has645) { alloc[others ? 'Other measures only (IL26-001, IL26-638)' : 'Not allocated to a measure'] += a; continue; }
  if (!others) { alloc['Described as supporting IL26-645 only'] += a; continue; }
  const m = s.match(/645\s*\(\s*(?:est\.\s*)?\$?\s*([\d,]+(?:\.\d+)?)\s*\)/i);
  if (m) alloc['IL26-645 share of shared items'] += +m[1].replace(/,/g, '');
  else { const n = 1 + (/001/.test(s) ? 1 : 0) + (/638/.test(s) ? 1 : 0); alloc['IL26-645 share of shared items'] += a / n; }
}
const expTotal = exp.reduce((t, x) => t + +x.amount, 0);
const heyRows = soql('kv7h-kjye', { select: 'filer_id,sum(amount) as t', where: "upper(contributor_name) like '%HEYWOOD%' and filer_id in('HEYWL--535','TAX-V--889') and election_year='2026'", group: 'filer_id' });
const heyLGW = +(heyRows.find(r => r.filer_id === 'HEYWL--535') || { t: 0 }).t;
const heyYes = +(heyRows.find(r => r.filer_id === 'TAX-V--889') || { t: 0 }).t;
const heywood = [
  ...Object.entries(alloc).map(([k, v]) => ({ measure: 'LGW 2026 spending', item: k, amount: r2(v) })),
  { measure: 'LGW 2026 spending', item: 'Total itemized 2026 expenditures', amount: r2(expTotal) },
  { measure: 'Heywood 2026 contributions', item: "To Let's Go Washington", amount: r2(heyLGW) },
  { measure: 'Heywood 2026 contributions', item: 'To Vote Yes Repeal the Income Tax', amount: r2(heyYes) },
];
writeCsv('i645_heywood_lgw.csv', ['measure', 'item', 'amount'], heywood);

// ---------- 3. Union giving to legislators, by vote on ESSB 6346 ----------
const rcXml = get('https://wslwebservices.leg.wa.gov/LegislationService.asmx/GetRollCalls?biennium=2025-26&billNumber=6346');
const spXml = get('https://wslwebservices.leg.wa.gov/SponsorService.asmx/GetSponsors?biennium=2025-26');
const tag = (s, t) => (s.match(new RegExp('<' + t + '>([^<]*)</' + t + '>')) || [])[1];
const members = {};
for (const m of spXml.matchAll(/<Member>([\s\S]*?)<\/Member>/g)) members[tag(m[1], 'Id')] = { first: tag(m[1], 'FirstName'), last: tag(m[1], 'LastName'), party: tag(m[1], 'Party'), district: tag(m[1], 'District') };
const finals = [];
for (const r of rcXml.split('<RollCall>').slice(1)) {
  const agency = tag(r, 'Agency'), date = tag(r, 'VoteDate'), motion = tag(r, 'Motion');
  const isFinal = (agency === 'House' && date.startsWith('2026-03-09') && /Final Passage/i.test(motion)) || (agency === 'Senate' && date.startsWith('2026-03-11') && /Final Passage/i.test(motion));
  if (!isFinal) continue;
  for (const v of r.matchAll(/<Vote>\s*<MemberId>(\d+)<\/MemberId>\s*<Name>([^<]*)<\/Name>\s*<VOte>([^<]*)<\/VOte>/g)) finals.push({ chamber: agency, id: v[1], vote: v[3], ...members[v[1]] });
}
const leg = soql('3h9x-7bvm', { select: 'filer_id,filer_name,election_year,office,jurisdiction', where: "election_year in ('2020','2022','2024','2026') and office in ('STATE REPRESENTATIVE','STATE SENATOR')", limit: '50000' });
const norm = s => (s || '').toUpperCase().normalize('NFD').replace(/[^A-Z ]/g, '');
for (const f of finals) {
  const d = String(+f.district).padStart(2, '0');
  f.ids = [...new Set(leg.filter(l => norm(l.filer_name).includes(norm(f.last)) && (l.jurisdiction || '').includes(d)).map(l => l.filer_id))];
}
const UNION_PAT = "(upper(contributor_name) like '%EDUCATION ASS%' or upper(contributor_name) like 'WEA%' or upper(contributor_name) like '%STATE EMPLOYEES%' or upper(contributor_name) like '%WFSE%' or upper(contributor_name) like '%SEIU%' or upper(contributor_name) like '%SERVICE EMPLOYEES%' or upper(contributor_name) like '%1199%' or upper(contributor_name) like '%AFSCME%' or upper(contributor_name) like '%UFCW%' or upper(contributor_name) like '%FOOD & COMMERCIAL%' or upper(contributor_name) like '%FEDERATION OF TEACHERS%' or upper(contributor_name) like 'AFT %' or upper(contributor_name) like '%QUALITY CARE COMMITTEE%')";
const allIds = [...new Set(finals.flatMap(f => f.ids))];
const byFiler = {};
for (let i = 0; i < allIds.length; i += 30) {
  const chunk = allIds.slice(i, i + 30).map(s => "'" + s + "'").join(',');
  for (const r of soql('kv7h-kjye', { select: 'filer_id,sum(amount) as t', where: `filer_id in(${chunk}) and election_year in ('2020','2022','2024','2026') and ${UNION_PAT}`, group: 'filer_id', limit: '50000' })) byFiler[r.filer_id] = +r.t;
}
const legRows = finals.map(f => ({ chamber: f.chamber, legislator: `${f.first} ${f.last}`, district: f.district, party: f.party, vote: f.vote, pdc_filer_ids: f.ids.join(' '), donor_union_contributions_2020_2026: r2(f.ids.reduce((a, i) => a + (byFiler[i] || 0), 0)) }))
  .sort((a, b) => a.chamber.localeCompare(b.chamber) || a.legislator.localeCompare(b.legislator));
writeCsv('i645_legislator_union_giving.csv', ['chamber', 'legislator', 'district', 'party', 'vote', 'pdc_filer_ids', 'donor_union_contributions_2020_2026'], legRows);
const median = a => { const s = [...a].sort((x, y) => x - y); const n = s.length; return n % 2 ? s[(n - 1) / 2] : (s[n / 2 - 1] + s[n / 2]) / 2; };
const groups = [['D', 'Yea'], ['D', 'Nay'], ['R', 'Nay'], ['R', 'Yea']];
const legSummary = groups.map(([p, v]) => { const xs = legRows.filter(r => r.party === p && r.vote === v).map(r => r.donor_union_contributions_2020_2026); return xs.length ? { group: `${p === 'D' ? 'Democrats' : 'Republicans'} voting ${v === 'Yea' ? 'yes' : 'no'}`, legislators: xs.length, median: r2(median(xs)), total: r2(xs.reduce((a, b) => a + b, 0)), with_any: xs.filter(x => x > 0).length } : null; }).filter(Boolean);

fs.writeFileSync(path.join(OUT, 'i645_campaign_finance_results.json'), JSON.stringify({ builtAt: new Date().toISOString(), typeRows, commTotals, heywood, legSummary, publicShare: Object.fromEntries(Object.keys(commTotals).map(k => [k, r2(typeRows.filter(t => t.committee_id === k && PUBLIC_TYPES.includes(t.donor_type)).reduce((a, t) => a + t.total, 0))])) }, null, 1));
console.log(JSON.stringify({ commTotals, legSummary, heywood }, null, 1));