/**
 * Scraper pentru donații electorale & finanțare partide (AEP via Expert Forum).
 *
 * Sursa primară (mandatată prin Legea 334/2006) e AEP — dar AEP publică în
 * PDF/Excel + reCAPTCHA, deci ingest-ul direct e dureros. Expert Forum
 * (banipartide.ro) deja a structurat datele într-un SQLite expus via
 * un endpoint base64-SQL:
 *
 *   GET https://www.banipartide.ro/app/json.php?mode=dt&ssid=<base64(SQL)>
 *   → { "data": [[col1, col2, ...], ...], "distinctData": {} }
 *
 * Trei tabele țintă pe care le ingestăm separat:
 *   - "Donatori persoane juridice"    → aep.donatii_pj    (3,612 rows)
 *   - "Donatori persoane fizice"      → aep.donatii_pf    (30,792 rows, CNP-uri se hash-uiesc)
 *   - "Donatori rapoarte de venituri și cheltuieli" → aep.donatii_rvc (353,473 rows)
 *
 * Idempotent: source_hash UNIQUE → ON CONFLICT DO UPDATE.
 *
 * Run:
 *   npx tsx src/scrape-aep-donatii.ts --table=pj
 *   npx tsx src/scrape-aep-donatii.ts --table=pf
 *   npx tsx src/scrape-aep-donatii.ts --table=rvc
 *   npx tsx src/scrape-aep-donatii.ts --table=all
 *   npx tsx src/scrape-aep-donatii.ts --table=pj --limit=100   # smoke test
 */
import { Pool } from 'pg';
import { createHash } from 'crypto';

const SOURCE_BASE = 'https://www.banipartide.ro';
const ENDPOINT = `${SOURCE_BASE}/app/json.php?mode=dt&ssid=`;
const USER_AGENT = 'gov-agreg/1.0 vreaudigital.ro (contact: m.tarau@beletage.ro)';

const args = process.argv.slice(2);
const argTable = args.find((a) => a.startsWith('--table='))?.split('=')[1] || 'pj';
const argLimit = parseInt(args.find((a) => a.startsWith('--limit='))?.split('=')[1] || '0');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 4,
});

// ── Source SQL queries (must match banipartide.ro schema exactly) ───────────
// Column order matters — we read positionally.
const SQL_PJ =
  'SELECT Nume AS nume, Partid, Suma, An, CUI, "Reprezentant societate", Sediu, "Naționalitatea donatorului", "Data donației", "Felul donației", "Filiala partidului", "Tip donație" FROM "Donatori persoane juridice"';

const SQL_PF =
  'SELECT "Nume donator", Partid, Suma, An, CNP, "Tip donație", "Ce s-a donat", "Data donației", Organizația FROM "Donatori persoane fizice"';

const SQL_RVC =
  'SELECT "Nume donator", Partid, Suma, An, "Tip venit", "Mod încasare", "Data donației", Judet, "Cod județ" FROM "Donatori rapoarte de venituri și cheltuieli"';

// ── Helpers ─────────────────────────────────────────────────────────────────
function b64utf8(s: string): string {
  return Buffer.from(s, 'utf-8').toString('base64');
}

function normCui(c: any): { norm: string | null; raw: string | null } {
  if (c == null) return { norm: null, raw: null };
  const raw = String(c).trim();
  if (raw === '') return { norm: null, raw: null };
  const cleaned = raw.replace(/^RO/i, '').replace(/\s+/g, '');
  if (!/^\d+$/.test(cleaned)) return { norm: null, raw };
  return { norm: cleaned, raw };
}

function normPartid(p: any): string | null {
  if (p == null) return null;
  const s = String(p).trim().toUpperCase();
  return s === '' ? null : s;
}

function parseSuma(s: any): number | null {
  if (s == null) return null;
  if (typeof s === 'number') return Number.isFinite(s) ? Math.round(s * 100) / 100 : null;
  const cleaned = String(s).trim().replace(/\./g, '').replace(',', '.').replace(/[^\d.\-]/g, '');
  if (cleaned === '') return null;
  const n = Number(cleaned);
  return Number.isFinite(n) ? Math.round(n * 100) / 100 : null;
}

function parseAn(a: any): number | null {
  if (a == null) return null;
  const n = parseInt(String(a).trim(), 10);
  return Number.isFinite(n) && n >= 1990 && n <= 2099 ? n : null;
}

/**
 * Best-effort date parser. Sursa are formate haotice:
 *   "11.10.2019; 13.11.2019"   → null (multiple, nu se poate alege)
 *   "10042010"                 → 2010-04-10 (DDMMYYYY compact)
 *   4102019                    → 2019-10-04 (number version of compact)
 *   "9/7/20"                   → 2020-09-07 (US-ish M/D/YY → ambigu, presupunem D/M/YY)
 *   "30.04;07.01;25.09"        → null
 *   "20.01;09.02;30.03.2010"   → null
 */
function parseDataDonatie(s: any): string | null {
  if (s == null) return null;
  let str = String(s).trim();
  if (str === '' || str === '0') return null;
  if (str.includes(';')) return null; // multiple dates → can't pick one
  // Compact 7-8 digit (could be DDMMYYYY or DMMYYYY) — validate, swap if needed
  if (/^\d+$/.test(str)) {
    if (str.length === 8) {
      const dd = str.slice(0, 2);
      const mm = str.slice(2, 4);
      const yyyy = str.slice(4);
      return validatedYMD(yyyy, mm, dd);
    }
    if (str.length === 7) {
      // D-MM-YYYY
      const dd = '0' + str.slice(0, 1);
      const mm = str.slice(1, 3);
      const yyyy = str.slice(3);
      return validatedYMD(yyyy, mm, dd);
    }
    return null;
  }
  // DD.MM.YYYY or DD/MM/YYYY — validate month/day, swap if needed
  let m = str.match(/^(\d{1,2})[./-](\d{1,2})[./-](\d{4})$/);
  if (m) {
    return validatedYMD(m[3], m[2], m[1]);
  }
  // M/D/YY or D/M/YY (ambiguous; assume D/M/YY since data is RO)
  m = str.match(/^(\d{1,2})\/(\d{1,2})\/(\d{2})$/);
  if (m) {
    const yy = parseInt(m[3], 10);
    const yyyy = String(yy < 50 ? 2000 + yy : 1900 + yy);
    return validatedYMD(yyyy, m[2], m[1]);
  }
  return null;
}

// Returns YYYY-MM-DD if (year, month, day) form a valid date.
// If month > 12 but day <= 12 (i.e. positions swapped), tries the swap.
// Otherwise returns null — caller should skip this row.
function validatedYMD(yyyy: string, mm: string, dd: string): string | null {
  let mNum = parseInt(mm, 10);
  let dNum = parseInt(dd, 10);
  const yNum = parseInt(yyyy, 10);
  if (!Number.isFinite(yNum) || yNum < 1990 || yNum > 2099) return null;
  // Swap if month is out of range and day looks like a valid month
  if ((mNum < 1 || mNum > 12) && dNum >= 1 && dNum <= 12) {
    [mNum, dNum] = [dNum, mNum];
  }
  if (mNum < 1 || mNum > 12) return null;
  if (dNum < 1 || dNum > 31) return null;
  // Validate via Date constructor (catches Feb 30, etc.)
  const d = new Date(Date.UTC(yNum, mNum - 1, dNum));
  if (d.getUTCFullYear() !== yNum || d.getUTCMonth() !== mNum - 1 || d.getUTCDate() !== dNum) return null;
  return `${yyyy}-${String(mNum).padStart(2, '0')}-${String(dNum).padStart(2, '0')}`;
}

function sha256Hex(s: string): string {
  return createHash('sha256').update(s).digest('hex');
}

function sha1Hex(s: string): string {
  return createHash('sha1').update(s).digest('hex');
}

function hashCnp(cnp: any): string | null {
  if (cnp == null) return null;
  const raw = String(cnp).trim().replace(/\D/g, '');
  if (raw === '' || raw.length < 7) return null; // too short to be a CNP
  return sha256Hex(raw);
}

// ── HTTP fetch ─────────────────────────────────────────────────────────────
async function fetchSql(sql: string): Promise<{ data: any[][] }> {
  const url = ENDPOINT + b64utf8(sql);
  const res = await fetch(url, {
    headers: {
      'User-Agent': USER_AGENT,
      Accept: 'application/json,text/plain,*/*',
      Referer: SOURCE_BASE + '/donatori-persoane-juridice.html',
    },
    signal: AbortSignal.timeout(120000),
  });
  if (!res.ok) {
    const txt = await res.text().catch(() => '');
    throw new Error(`fetch ${res.status}: ${txt.slice(0, 200)}`);
  }
  const json = (await res.json()) as { data: any[][] };
  if (!Array.isArray(json.data)) throw new Error(`bad payload: ${JSON.stringify(json).slice(0, 200)}`);
  return json;
}

// ── Party registry — derived from observed values + manual map ──────────────
const PARTY_NAMES: Record<string, string> = {
  PSD: 'Partidul Social Democrat',
  PNL: 'Partidul Național Liberal',
  USR: 'Uniunea Salvați România',
  AUR: 'Alianța pentru Unirea Românilor',
  UDMR: 'Uniunea Democrată a Maghiarilor din România',
  PMP: 'Partidul Mișcarea Populară',
  ALDE: 'Alianța Liberalilor și Democraților',
  PD: 'Partidul Democrat',
  PDL: 'Partidul Democrat-Liberal',
  PRM: 'Partidul România Mare',
  PNTCD: 'Partidul Național Țărănesc Creștin Democrat',
  PC: 'Partidul Conservator',
  PIN: 'Partidul Inițiativa Națională',
  PLD: 'Partidul Liberal Democrat',
  UNPR: 'Uniunea Națională pentru Progresul României',
  PPDD: 'Partidul Poporului Dan Diaconescu',
  PPRO: 'PRO România',
  PLUS: 'Partidul Libertății, Unității și Solidarității',
  USL: 'Uniunea Social Liberală',
  USAR: 'Uniunea Salvați Bucureștiul / USR (vechi)',
  UCR: 'Uniunea Civică Republicană',
  PNGCD: 'Partidul Noua Generație - Creștin Democrat',
  PCM: 'Partidul Conservator Moldovenesc',
  PCC: 'Partidul Conservator Creștin',
  PUSL: 'Partidul Umanist Social Liberal',
  PSDU: 'Partidul Social Democrat Unit',
  ADA: 'Alianța Dreapta Aliantă',
  FC: 'Forța Civică',
  FDGR: 'Forumul Democrat al Germanilor din România',
  PPMT: 'Partidul Popular Maghiar din Transilvania',
  PROEUROPA: 'PRO Europa',
  PPUSL: 'Partidul Puterii Umaniste',
};

async function ensurePartid(client: any, partidId: string | null): Promise<void> {
  if (!partidId) return;
  await client.query(
    `INSERT INTO aep.partide (id, nume_oficial)
     VALUES ($1, $2)
     ON CONFLICT (id) DO NOTHING`,
    [partidId, PARTY_NAMES[partidId] || null]
  );
}

// ── Per-table ingest functions ──────────────────────────────────────────────

interface IngestStats { seen: number; inserted: number; updated: number; skipped: number; }

async function ingestPj(rows: any[][]): Promise<IngestStats> {
  const stats: IngestStats = { seen: rows.length, inserted: 0, updated: 0, skipped: 0 };
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (const r of rows) {
      // SELECT Nume, Partid, Suma, An, CUI, "Reprezentant societate", Sediu,
      //        "Naționalitatea donatorului", "Data donației", "Felul donației",
      //        "Filiala partidului", "Tip donație"
      const [nume, partid, suma, an, cui, reprezentant, sediu, nationalitate, dataDon, felul, filiala, tip] = r;
      const cuiInfo = normCui(cui);
      const partidId = normPartid(partid);
      const sumaParsed = parseSuma(suma);
      const anParsed = parseAn(an);
      if (sumaParsed == null || anParsed == null || !nume) {
        stats.skipped++;
        continue;
      }
      await ensurePartid(client, partidId);

      const dataParsed = parseDataDonatie(dataDon);
      const dataText = dataDon == null ? null : String(dataDon);
      const sourceHash = sha1Hex(
        [String(nume).toLowerCase().trim(), cuiInfo.norm || '', partidId || '', anParsed, sumaParsed, dataText || ''].join('|')
      );

      const result = await client.query(
        `INSERT INTO aep.donatii_pj (
          source_hash, donator_nume, donator_cui, donator_cui_raw, reprezentant, sediu,
          nationalitate, partid_id, filiala_partid, suma_lei, an,
          data_donatie_text, data_donatie, tip_donatie, felul_donatie
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
        ON CONFLICT (source_hash) DO UPDATE SET
          donator_nume = EXCLUDED.donator_nume,
          donator_cui = EXCLUDED.donator_cui,
          donator_cui_raw = EXCLUDED.donator_cui_raw,
          reprezentant = EXCLUDED.reprezentant,
          sediu = EXCLUDED.sediu,
          nationalitate = EXCLUDED.nationalitate,
          partid_id = EXCLUDED.partid_id,
          filiala_partid = EXCLUDED.filiala_partid,
          suma_lei = EXCLUDED.suma_lei,
          an = EXCLUDED.an,
          data_donatie_text = EXCLUDED.data_donatie_text,
          data_donatie = EXCLUDED.data_donatie,
          tip_donatie = EXCLUDED.tip_donatie,
          felul_donatie = EXCLUDED.felul_donatie,
          fetched_at = now()
        RETURNING (xmax = 0) AS inserted`,
        [
          sourceHash,
          String(nume).trim(),
          cuiInfo.norm,
          cuiInfo.raw,
          reprezentant ? String(reprezentant).trim() || null : null,
          sediu ? String(sediu).trim() || null : null,
          nationalitate ? String(nationalitate).trim() || null : null,
          partidId,
          filiala ? String(filiala).trim() || null : null,
          sumaParsed,
          anParsed,
          dataText,
          dataParsed,
          tip ? String(tip).trim() || null : null,
          felul ? String(felul).trim() || null : null,
        ]
      );
      if (result.rows[0]?.inserted) stats.inserted++; else stats.updated++;
    }
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
  return stats;
}

async function ingestPf(rows: any[][]): Promise<IngestStats> {
  const stats: IngestStats = { seen: rows.length, inserted: 0, updated: 0, skipped: 0 };
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (const r of rows) {
      // "Nume donator", Partid, Suma, An, CNP, "Tip donație", "Ce s-a donat", "Data donației", Organizația
      const [nume, partid, suma, an, cnp, tip, ceSDonat, dataDon, organizatia] = r;
      const partidId = normPartid(partid);
      const sumaParsed = parseSuma(suma);
      const anParsed = parseAn(an);
      if (sumaParsed == null || anParsed == null || !nume) {
        stats.skipped++;
        continue;
      }
      await ensurePartid(client, partidId);
      const cnpHash = hashCnp(cnp);
      const dataParsed = parseDataDonatie(dataDon);
      const dataText = dataDon == null ? null : String(dataDon);
      const sourceHash = sha1Hex(
        [String(nume).toLowerCase().trim(), cnpHash || '', partidId || '', anParsed, sumaParsed, dataText || ''].join('|')
      );

      const result = await client.query(
        `INSERT INTO aep.donatii_pf (
          source_hash, donator_nume, donator_cnp_sha256, partid_id, organizatia,
          suma_lei, an, data_donatie_text, data_donatie, tip_donatie, ce_s_a_donat
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
        ON CONFLICT (source_hash) DO UPDATE SET
          donator_nume = EXCLUDED.donator_nume,
          donator_cnp_sha256 = EXCLUDED.donator_cnp_sha256,
          partid_id = EXCLUDED.partid_id,
          organizatia = EXCLUDED.organizatia,
          suma_lei = EXCLUDED.suma_lei,
          an = EXCLUDED.an,
          data_donatie_text = EXCLUDED.data_donatie_text,
          data_donatie = EXCLUDED.data_donatie,
          tip_donatie = EXCLUDED.tip_donatie,
          ce_s_a_donat = EXCLUDED.ce_s_a_donat,
          fetched_at = now()
        RETURNING (xmax = 0) AS inserted`,
        [
          sourceHash,
          String(nume).trim(),
          cnpHash,
          partidId,
          organizatia ? String(organizatia).trim() || null : null,
          sumaParsed,
          anParsed,
          dataText,
          dataParsed,
          tip ? String(tip).trim() || null : null,
          ceSDonat ? String(ceSDonat).trim() || null : null,
        ]
      );
      if (result.rows[0]?.inserted) stats.inserted++; else stats.updated++;
    }
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
  return stats;
}

async function ingestRvc(rows: any[][]): Promise<IngestStats> {
  const stats: IngestStats = { seen: rows.length, inserted: 0, updated: 0, skipped: 0 };
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    for (const r of rows) {
      // "Nume donator", Partid, Suma, An, "Tip venit", "Mod încasare", "Data donației", Judet, "Cod județ"
      const [nume, partid, suma, an, tipVenit, modIncasare, dataDon, judet, codJudet] = r;
      const partidId = normPartid(partid);
      const sumaParsed = parseSuma(suma);
      const anParsed = parseAn(an);
      if (sumaParsed == null || anParsed == null || !nume) {
        stats.skipped++;
        continue;
      }
      await ensurePartid(client, partidId);
      const dataParsed = parseDataDonatie(dataDon);
      const dataText = dataDon == null ? null : String(dataDon);
      const sourceHash = sha1Hex(
        [
          String(nume).toLowerCase().trim(),
          partidId || '',
          anParsed,
          sumaParsed,
          dataText || '',
          codJudet || '',
          tipVenit || '',
        ].join('|')
      );

      const result = await client.query(
        `INSERT INTO aep.donatii_rvc (
          source_hash, donator_nume, judet, cod_judet, tip_venit, partid_id,
          suma_lei, mod_incasare, an, data_donatie_text, data_donatie
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
        ON CONFLICT (source_hash) DO UPDATE SET
          donator_nume = EXCLUDED.donator_nume,
          judet = EXCLUDED.judet,
          cod_judet = EXCLUDED.cod_judet,
          tip_venit = EXCLUDED.tip_venit,
          partid_id = EXCLUDED.partid_id,
          suma_lei = EXCLUDED.suma_lei,
          mod_incasare = EXCLUDED.mod_incasare,
          an = EXCLUDED.an,
          data_donatie_text = EXCLUDED.data_donatie_text,
          data_donatie = EXCLUDED.data_donatie,
          fetched_at = now()
        RETURNING (xmax = 0) AS inserted`,
        [
          sourceHash,
          String(nume).trim(),
          judet ? String(judet).trim() || null : null,
          codJudet ? String(codJudet).trim() || null : null,
          tipVenit ? String(tipVenit).trim() || null : null,
          partidId,
          sumaParsed,
          modIncasare ? String(modIncasare).trim() || null : null,
          anParsed,
          dataText,
          dataParsed,
        ]
      );
      if (result.rows[0]?.inserted) stats.inserted++; else stats.updated++;
    }
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
  return stats;
}

// ── Orchestration ───────────────────────────────────────────────────────────
async function logScrape(scraper: string, sourceUrl: string, stats: IngestStats, durationMs: number, startedAt: Date, error?: string) {
  await pool.query(
    `INSERT INTO aep.scrape_log (scraper, source_url, rows_seen, rows_inserted, rows_updated, rows_skipped, duration_ms, started_at, error)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)`,
    [scraper, sourceUrl, stats.seen, stats.inserted, stats.updated, stats.skipped, durationMs, startedAt, error || null]
  );
}

async function runOne(table: 'pj' | 'pf' | 'rvc'): Promise<IngestStats> {
  let sql: string;
  let sourceUrl: string;
  let ingest: (rows: any[][]) => Promise<IngestStats>;
  switch (table) {
    case 'pj': sql = SQL_PJ; sourceUrl = `${SOURCE_BASE}/donatori-persoane-juridice.html`; ingest = ingestPj; break;
    case 'pf': sql = SQL_PF; sourceUrl = `${SOURCE_BASE}/donatori-persoane-fizice.html`; ingest = ingestPf; break;
    case 'rvc': sql = SQL_RVC; sourceUrl = `${SOURCE_BASE}/donatori-rvc.html`; ingest = ingestRvc; break;
  }
  // Apply limit by appending LIMIT to SQL (sqlite-syntax compatible).
  if (argLimit > 0) sql = `${sql} LIMIT ${argLimit}`;

  const startedAt = new Date();
  const t0 = Date.now();
  console.log(`[aep:${table}] fetching from banipartide.ro (${argLimit > 0 ? `limit=${argLimit}` : 'full'})...`);
  let stats: IngestStats = { seen: 0, inserted: 0, updated: 0, skipped: 0 };
  let error: string | undefined;
  try {
    const payload = await fetchSql(sql);
    console.log(`[aep:${table}] fetched ${payload.data.length} rows; upserting...`);
    stats = await ingest(payload.data);
  } catch (e) {
    error = e instanceof Error ? e.message : String(e);
    console.error(`[aep:${table}] ERROR: ${error}`);
  }
  const dur = Date.now() - t0;
  await logScrape(`donatii_${table}`, sourceUrl, stats, dur, startedAt, error);
  console.log(`[aep:${table}] done in ${(dur / 1000).toFixed(1)}s seen=${stats.seen} ins=${stats.inserted} upd=${stats.updated} skip=${stats.skipped}${error ? ' ERROR' : ''}`);
  if (error) throw new Error(error);
  return stats;
}

async function main(): Promise<void> {
  console.log(`[aep] table=${argTable} limit=${argLimit || 'none'}`);
  if (argTable === 'all') {
    await runOne('pj');
    await runOne('pf');
    await runOne('rvc');
  } else if (['pj', 'pf', 'rvc'].includes(argTable)) {
    await runOne(argTable as 'pj' | 'pf' | 'rvc');
  } else {
    console.error(`Unknown --table=${argTable}. Use pj|pf|rvc|all`);
    process.exit(2);
  }
  // Refresh MVs (cheap; only PJ ones currently)
  console.log(`[aep] refreshing materialized views...`);
  await pool.query('REFRESH MATERIALIZED VIEW CONCURRENTLY aep.mv_donatii_per_cui');
  await pool.query('REFRESH MATERIALIZED VIEW aep.mv_top_donatori_partid');
  console.log(`[aep] done.`);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => pool.end());
