/**
 * CNAS — Casa Națională de Asigurări de Sănătate.
 * Lista furnizorilor de servicii medicale aflați în relație contractuală
 * cu CAS-urile județene.
 *
 * v1 strategy (this script):
 *   1. Enumerate cnas.ro/wp-json/wp/v2/media (4.18K total → ~70-90 furnizori PDFs)
 *   2. Filter to furnizor-related files, classify tip_serviciu + județ from filename
 *   3. Download each PDF, compute sha1, store metadata in cnas.documents
 *   4. Run pdftotext -layout, parse by tip_serviciu-specific regex, insert
 *      cnas.furnizori rows. Schemas vary across PDFs — we do best-effort and
 *      mark documents with parse_status='ok' / 'no_table' / 'failed'.
 *   5. Skip CUI matching — runs separately via match-cui-cnas.ts (future).
 *
 * Idempotent: cnas.documents UNIQUE(source_url) + cnas.furnizori
 * UNIQUE(document_id, nr_crt, name). Re-runs UPDATE fetched_at + replace rows
 * for documents whose sha1 changed.
 *
 * Run:
 *   npx tsx src/scrape-cnas.ts                 # full
 *   npx tsx src/scrape-cnas.ts --limit=5       # smoke test (first 5 docs)
 *   npx tsx src/scrape-cnas.ts --metadata-only # list+register, no parse
 *   npx tsx src/scrape-cnas.ts --parse-only    # parse already-registered docs
 *
 * Requires: poppler-utils (pdftotext) — auto-installed in scrape-cnas.sh.
 */
import { Pool } from 'pg';
import { createHash } from 'crypto';
import { execFile } from 'child_process';
import { promisify } from 'util';
import { writeFile, mkdir, readFile, unlink, stat } from 'fs/promises';
import { tmpdir } from 'os';
import { join } from 'path';
import {
  parseProviderTextJudetGrouped,
  parseProviderTextRadio,
  parseProviderTextSingleCAS,
  parseProviderTextNumberedDot,
  type ProviderRow as ProviderRowB,
} from './cnas-layout-b.js';

const execFileAsync = promisify(execFile);

const WP_API = 'https://cnas.ro/wp-json/wp/v2/media';
const USER_AGENT = 'gov-agreg/1.0 vreaudigital.ro (contact: m.tarau@beletage.ro)';

// ── CLI args ────────────────────────────────────────────────────────────────
const args = process.argv.slice(2);
const argLimit = parseInt(args.find((a) => a.startsWith('--limit='))?.split('=')[1] || '0');
const argMetadataOnly = args.includes('--metadata-only');
const argParseOnly = args.includes('--parse-only');

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

// ── Helpers ─────────────────────────────────────────────────────────────────
function sha1Hex(buf: Buffer | string): string {
  return createHash('sha1').update(buf).digest('hex');
}

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

function decodeHtmlEntities(s: string): string {
  return s
    .replace(/&#8211;/g, '–')
    .replace(/&#8217;/g, "'")
    .replace(/&#8220;/g, '"')
    .replace(/&#8221;/g, '"')
    .replace(/&amp;/g, '&')
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&#039;/g, "'")
    .replace(/&nbsp;/g, ' ');
}

// ── Tip serviciu classification (filename → category) ───────────────────────
interface FileClass {
  tip_serviciu: string;
  judet: string | null;
  perioada: string | null;
}

const TIP_PATTERNS: Array<[RegExp, string]> = [
  // Most specific first
  [/INGRIJIRI[-_ ]+PALIATIVE|paliative/i, 'ingrijiri_paliative'],
  [/INGRIJIRI[-_ ]+MEDICALE|ingrijir/i, 'ingrijiri_medicale'],
  [/RECUPERARE[-_ ]+MEDICALA|recuperare|RECA(\b|[-_])|MEDICINA[-_ ]+FIZICA|reabilitare/i, 'recuperare_medicala'],
  [/MEDICINA[-_ ]+DENTARA|medicin[ăa][-_ ]+dentar[ăa]|stomato/i, 'medicina_dentara'],
  [/MEDICINA[-_ ]+DE[-_ ]+FAMILIE|FURNIZORI[-_ ]MF|MF[-_ ]\d|asistenta[-_ ]+primara|asistenta[-_ ]?medical[ăa][-_ ]?primara/i, 'medicina_familie'],
  [/CENTRE[-_ ]DE[-_ ]+PERMANENTA/i, 'centre_permanenta'],
  [/AMBULATORIU[-_ ]+CLINIC|servicii[-_ ]+clinice|Lista[-_ ]furnizori[-_ ]clinic/i, 'ambulatoriu_clinic'],
  [/AMBULATORIU[-_ ]+(PENTRU[-_ ]+)?(SPECIALITATI[-_ ]+)?PARACLINIC|paraclinic/i, 'paraclinic'],
  [/SERVICII[-_ ]+CONEXE/i, 'servicii_conexe'],
  [/DISPOZITIVE[-_ ]+MEDICALE|dispozitive/i, 'dispozitive_medicale'],
  [/SERVICII[-_ ]+FARMACEUTICE|farma/i, 'farmacie'],
  [/SPITAL|spital/i, 'spital'],
  [/URGENTA[-_ ]+PRESPITALICEASCA|TRANSPORT[-_ ]+SANITAR|urgenta/i, 'urgenta_transport'],
  [/PNS|programe[-_ ]+nationale/i, 'pns'],
  [/RADIOTERAPIE/i, 'radioterapie'],
  [/TUMORI[-_ ]+SOLIDE|ONCO|oncologie|hematologie/i, 'oncologie'],
  [/TESTARE[-_ ]+GENETICA|TSA/i, 'testare_genetica'],
];

const JUDET_PATTERNS: Array<[RegExp, string]> = [
  [/CAS[-_ ]+(GORJ)\b/i, 'GORJ'],
  [/CAS[-_ ]+(ARAD)\b/i, 'ARAD'],
  [/CAS[-_ ]+(BIHOR)\b/i, 'BIHOR'],
  [/CAS[-_ ]+(BACAU)\b/i, 'BACAU'],
  [/CJAS[-_ ]+(BACAU)\b/i, 'BACAU'],
  [/CAS[-_ ]+(CLUJ)\b/i, 'CLUJ'],
  [/CAS[-_ ]+(IASI)\b/i, 'IASI'],
  [/CAS[-_ ]+(BRASOV|BV)\b/i, 'BRASOV'],
  [/CAS[-_ ]+(TIMIS|TM)\b/i, 'TIMIS'],
  [/CAS[-_ ]+(MB|BUC)\b/i, 'BUCURESTI'],
  [/CAS[-_ ]+(SIBIU|SB)\b/i, 'SIBIU'],
  [/CAS[-_ ]+(MURES|MS)\b/i, 'MURES'],
  [/CAS[-_ ]+(CONSTANTA|CT)\b/i, 'CONSTANTA'],
];

function classifyFilename(filename: string, title: string): FileClass {
  const both = `${filename} ${title}`;
  let tip_serviciu = 'other';
  for (const [re, t] of TIP_PATTERNS) {
    if (re.test(both)) { tip_serviciu = t; break; }
  }
  let judet: string | null = null;
  for (const [re, j] of JUDET_PATTERNS) {
    if (re.test(both)) { judet = j; break; }
  }
  // Perioada: extract DD.MM.YYYY or DD-MM-YYYY or "month year"
  const dmYRe = /(\d{2})[.\-_](\d{2})[.\-_](\d{4})/;
  const monthYearRe = /(ianuarie|februarie|martie|aprilie|mai|iunie|iulie|august|septembrie|octombrie|noiembrie|decembrie)[-_ ]+(\d{4})/i;
  let perioada: string | null = null;
  const m1 = both.match(dmYRe);
  if (m1) perioada = `${m1[1]}.${m1[2]}.${m1[3]}`;
  else {
    const m2 = both.match(monthYearRe);
    if (m2) perioada = `${m2[1].toLowerCase()} ${m2[2]}`;
  }
  return { tip_serviciu, judet, perioada };
}

// ── WP REST API enumeration ─────────────────────────────────────────────────
interface WPMedia {
  id: number;
  source_url: string;
  title: string;
  date: string;
  mime_type: string;
}

async function listAllWPMedia(): Promise<WPMedia[]> {
  const out: WPMedia[] = [];
  const pageSize = 100;
  // Get total first
  const head = await fetch(`${WP_API}?per_page=1&_fields=id`, {
    headers: { 'User-Agent': USER_AGENT },
    signal: AbortSignal.timeout(30000),
  });
  const total = parseInt(head.headers.get('x-wp-total') || '0', 10);
  console.log(`[cnas] WP media total: ${total}`);
  for (let offset = 0; offset < total; offset += pageSize) {
    const url = `${WP_API}?per_page=${pageSize}&offset=${offset}&_fields=id,source_url,title,date,mime_type`;
    let attempt = 0;
    while (true) {
      try {
        const res = await fetch(url, {
          headers: { 'User-Agent': USER_AGENT },
          signal: AbortSignal.timeout(60000),
        });
        if (!res.ok) throw new Error(`${res.status}`);
        const items = (await res.json()) as Array<{ id: number; source_url: string; title?: { rendered?: string }; date: string; mime_type: string }>;
        for (const it of items) {
          if (!it.source_url) continue;
          out.push({
            id: it.id,
            source_url: it.source_url,
            title: decodeHtmlEntities(it.title?.rendered || ''),
            date: it.date,
            mime_type: it.mime_type,
          });
        }
        break;
      } catch (e) {
        attempt++;
        if (attempt >= 3) throw e;
        console.warn(`[cnas] WP list offset=${offset} attempt ${attempt}: ${e instanceof Error ? e.message : e}`);
        await new Promise((r) => setTimeout(r, 2000 * attempt));
      }
    }
    if (offset % 1000 === 0) console.log(`[cnas]   listed ${out.length}/${total}`);
  }
  return out;
}

function isProviderListFile(m: WPMedia): boolean {
  const u = m.source_url.toLowerCase();
  const t = m.title.toLowerCase();
  // Must be PDF or XLSX
  if (!/\.(pdf|xlsx?)(\?|$)/i.test(u)) return false;
  // Must mention furnizor or lista (rough filter)
  const blob = `${u} ${t}`;
  if (!/furnizor|lista[-_ ]?furniz|lista_furniz|tabel[-_ ]?nominal|liste[-_ ]+cu[-_ ]+furnizor/i.test(blob)) return false;
  // Reject obvious non-list documents (declarations, templates, FAQs)
  if (/anunt|comunicat|declaratie|chestionar|grafic[-_ ]?intalniri|calendar|raporteaza|aplicati|specificat|interfat|dosar/i.test(blob)) return false;
  if (/anexa[-_ ]?(nr\.?\s?1[-_ ]?centralizator)/i.test(blob)) return false;
  // Allow "valori" only when clearly a contract/list (not generic)
  return true;
}

// ── Document download + register ───────────────────────────────────────────
async function downloadFile(url: string, destPath: string): Promise<{ size: number; sha1: string }> {
  const res = await fetch(url, {
    headers: { 'User-Agent': USER_AGENT },
    signal: AbortSignal.timeout(120000),
  });
  if (!res.ok) throw new Error(`download ${res.status}`);
  const buf = Buffer.from(await res.arrayBuffer());
  await writeFile(destPath, buf);
  return { size: buf.length, sha1: sha1Hex(buf) };
}

async function registerDocument(m: WPMedia): Promise<{ id: number; needsReparse: boolean }> {
  const filename = m.source_url.split('/').pop() || '';
  const { tip_serviciu, judet, perioada } = classifyFilename(filename, m.title);
  const r = await pool.query<{ id: number; file_sha1: string | null; parse_status: string }>(
    `INSERT INTO cnas.documents (
       source_url, source, wp_media_id, title, filename, mime_type,
       published_at, tip_serviciu, judet, perioada
     ) VALUES ($1,'wp-media',$2,$3,$4,$5,$6,$7,$8,$9)
     ON CONFLICT (source_url) DO UPDATE SET
       title = EXCLUDED.title,
       mime_type = EXCLUDED.mime_type,
       tip_serviciu = COALESCE(cnas.documents.tip_serviciu, EXCLUDED.tip_serviciu),
       judet = COALESCE(cnas.documents.judet, EXCLUDED.judet),
       perioada = COALESCE(cnas.documents.perioada, EXCLUDED.perioada),
       fetched_at = now()
     RETURNING id, file_sha1, parse_status`,
    [m.source_url, m.id, m.title, filename, m.mime_type, m.date, tip_serviciu, judet, perioada]
  );
  const row = r.rows[0];
  return { id: row.id, needsReparse: row.file_sha1 === null || row.parse_status === 'pending' || row.parse_status === 'failed' };
}

// ── PDF parsing ─────────────────────────────────────────────────────────────
async function pdftotextLayout(pdfPath: string): Promise<string> {
  const { stdout } = await execFileAsync('pdftotext', ['-layout', '-enc', 'UTF-8', pdfPath, '-'], {
    maxBuffer: 64 * 1024 * 1024,
  });
  return stdout;
}

async function pdfPageCount(pdfPath: string): Promise<number | null> {
  try {
    const { stdout } = await execFileAsync('pdfinfo', [pdfPath]);
    const m = stdout.match(/Pages:\s+(\d+)/);
    return m ? parseInt(m[1], 10) : null;
  } catch { return null; }
}

interface ProviderRow {
  nr_crt: number | null;
  cod_furnizor: string | null;
  name: string;
  reprezentant: string | null;
  sediu: string | null;
  localitate: string | null;
  telefon: string | null;
  fax: string | null;
  email: string | null;
  specialitate: string | null;
  judet?: string | null;   // per-row override (used by layout-B parsers); null=use doc-level
  raw_text: string;
}

/**
 * Best-effort generic parser. Most CNAS PDFs follow one of 3 layouts:
 *   A. SPITALE: NR | cod | FURNIZOR | Sediu | telefon | fax | email
 *   B. MF/Cabinet: Nr | Denumire | Reprezentant | Localitate | Adresa | LocPctLucru | AdrPctLucru | Telefon | Email
 *   C. FARMACIE: Nr | Ctr | Denumire | Sediu | Reprezentant | Telefon | Email
 *
 * Strategy: look for lines that START with "NR_CRT" (1-4 digit number followed
 * by 2+ spaces), gather columns by whitespace splits, heuristically pick out
 * email + phone + name. Multi-line cell values get folded into the main row by
 * looking at next lines until we see a new NR_CRT or page header.
 */
function parseProviderText(text: string, filenameHints: { tip: string }): ProviderRow[] {
  const rows: ProviderRow[] = [];
  const lines = text.split('\n');
  let current: { nr_crt: number; cod: string | null; raw: string[] } | null = null;
  const flush = () => {
    if (!current) return;
    const raw = current.raw.join('\n');
    rows.push(extractFields(current.nr_crt, current.cod, raw, filenameHints));
    current = null;
  };
  // Match: leading whitespace, integer 1-9999, 1+ spaces, then a Romanian
  // capital letter (or "SC"/"SRL" prefix). Some PDFs (e.g. INGRIJIRI-PALIATIVE)
  // use single-space separation: `1 ASOCIATIA LUMINA       Tartescu nr.5`.
  // The capital-letter guard prevents picking up dates / page numbers /
  // phones (which start with 0).
  const nrCrtRe = /^\s*(\d{1,4})\s+([A-ZĂÂÎȘȚ"][A-Za-zĂÂÎȘȚăâîșț0-9].*)$/;
  // CAS supplier code: 2 letters + digits (BH01, MB427, T08), 3 letters + digits (CASMB123)
  const codRe = /^([A-Z]{2,4}\d{1,4})\s{1,}/;
  for (const ln of lines) {
    if (!ln.trim()) {
      // Blank line — soft separator, but don't flush yet (cells can have empty lines)
      if (current) current.raw.push('');
      continue;
    }
    // Skip headers/footers
    if (/^\s*(NR\.?[\s_]+CRT|Nr\s*Crt|Pagina|Page \d|FURNIZORI|TABEL NOMINAL|CASA DE ASIGURARI|denumire furnizor|denumire cabinet|reprezentant|localitate|sediu|telefon|email|fax)/i.test(ln) && !nrCrtRe.test(ln)) {
      // header — skip
      continue;
    }
    const m = nrCrtRe.exec(ln);
    if (m) {
      flush();
      const nr = parseInt(m[1], 10);
      const rest = m[2];
      let cod: string | null = null;
      const codM = codRe.exec(rest);
      if (codM) cod = codM[1];
      current = { nr_crt: nr, cod, raw: [ln] };
    } else if (current) {
      current.raw.push(ln);
    }
  }
  flush();
  return rows;
}

const EMAIL_RE = /[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}/;
const PHONE_RE = /\b0\d{2,3}[\s.\-/]?\d{3}[\s.\-/]?\d{3,4}\b/g;
const COLUMN_SPLIT_RE = /\s{3,}/;  // 3+ spaces = column separator (pdftotext -layout)

function extractFields(nrCrt: number, cod: string | null, raw: string, hints: { tip: string }): ProviderRow {
  // Aggregate non-empty lines into one big string, then split by column gaps
  const lines = raw.split('\n').map(l => l.trimEnd());
  // First line is the "primary" — has nr_crt + cod + first column values
  // Subsequent lines often complete fields wrapped from the first
  // Strategy: for each line, split by 3+ spaces, collect columns indexed by
  // approximate x-position. Then merge adjacent column tokens vertically.
  // For v1, use simpler approach: extract email + phones globally; name = first non-empty
  // multi-line cell after cod.

  const text = lines.join(' ');
  const emails = text.match(EMAIL_RE);
  const phones = (text.match(PHONE_RE) || []).map((p) => p.replace(/\s+/g, ''));
  const email = emails ? emails[0] : null;
  const telefon = phones.length > 0 ? phones[0] : null;
  const fax = phones.length > 1 ? phones[1] : null;

  // Strip nr_crt + cod from first line, then split by 3+ spaces
  let firstLine = lines[0] || '';
  firstLine = firstLine.replace(/^\s*\d{1,4}\s+/, '');
  if (cod) firstLine = firstLine.replace(new RegExp(`^${cod}\\s+`), '');

  // Concatenate first line + continuation lines aligned to first column
  // For now, take the 1st column-token = name candidate, 2nd-3rd = sediu/localitate
  const cols0 = firstLine.split(COLUMN_SPLIT_RE).map((s) => s.trim()).filter(Boolean);
  // Multi-line: append line N's first column to cols0[0] if nrCrt-line was sparse
  for (let i = 1; i < lines.length && i < 6; i++) {
    const lc = lines[i].split(COLUMN_SPLIT_RE).map(s => s.trim()).filter(Boolean);
    // Heuristic: append continuation tokens column-wise
    for (let j = 0; j < lc.length && j < cols0.length; j++) {
      // If column j is short or looks like a continuation, append
      if (lc[j] && !/@/.test(lc[j]) && !PHONE_RE.test(lc[j])) {
        // Don't double-append the same value
        if (!cols0[j].includes(lc[j])) cols0[j] = (cols0[j] + ' ' + lc[j]).replace(/\s+/g, ' ').trim();
      }
    }
  }
  const name = (cols0[0] || '').replace(/\s+/g, ' ').trim() || `[unknown #${nrCrt}]`;
  const sediu = cols0[1] ? cols0[1].replace(/\s+/g, ' ').trim() : null;

  // Localitate: try to extract from sediu (first comma-separated token usually)
  let localitate: string | null = null;
  if (sediu) {
    const parts = sediu.split(/[,\s]+(?=str\.|nr\.|bd\.|aleea|cal\.|bld\.|sat|com\.)/i);
    if (parts[0]) localitate = parts[0].replace(/[,;]+$/, '').trim().slice(0, 100);
  }

  // Reprezentant + specialitate: hint-dependent
  let reprezentant: string | null = null;
  let specialitate: string | null = null;
  if (hints.tip === 'medicina_familie' || hints.tip === 'medicina_dentara' || hints.tip === 'ambulatoriu_clinic') {
    // col 1 = name, col 2 = reprezentant, col 3 = localitate, col 4 = adresa (sediu)
    reprezentant = cols0[1] ? cols0[1].replace(/\s+/g, ' ').trim() : null;
    if (cols0[2]) localitate = cols0[2].replace(/\s+/g, ' ').trim();
    if (cols0[3]) {
      // override sediu with col 3 (adresa) when reprezentant is in col 1
      // Actually we can't know without column detection; leave sediu as is
    }
  }

  return {
    nr_crt: nrCrt,
    cod_furnizor: cod,
    name: name.slice(0, 500),
    reprezentant: reprezentant?.slice(0, 300) || null,
    sediu: sediu?.slice(0, 500) || null,
    localitate: localitate?.slice(0, 200) || null,
    telefon,
    fax,
    email: email?.slice(0, 200) || null,
    specialitate,
    raw_text: raw.slice(0, 2000),
  };
}

// ── Layout-B dispatch ──────────────────────────────────────────────────────
// Some CNAS PDFs use judet-grouped layouts (no row numbers) that the
// row-numbered parser above fails to extract from. This helper routes to the
// correct layout-B variant based on filename + tip_serviciu hints.
function tryLayoutBParse(
  text: string,
  doc: { tip_serviciu: string | null; judet: string | null },
  filename: string
): ProviderRow[] {
  const fn = filename.toLowerCase();
  const tip = doc.tip_serviciu || 'other';
  // 1. Radioterapie: list with no email/phone, judet+name+service-type cols
  if (tip === 'radioterapie' || /radioterapie/i.test(fn)) {
    return parseProviderTextRadio(text, { tip });
  }
  // 2. Single-CAS PNS lists (CAS-GORJ-PNS, Valori-PNS): name | program | valoare
  if ((tip === 'pns' && doc.judet) || /^cas-/i.test(fn) || /valori-de-contract-furnizori-pns/i.test(fn)) {
    return parseProviderTextSingleCAS(text, { tip, judet: doc.judet || 'GORJ' });
  }
  // 3. ASISTENTA-MEDICALA-PRIMARA: numbered-dot rows
  if (/asistenta-medicala-primara/i.test(fn) || /numbered.dot/i.test(fn)) {
    // judet inferred from CAS header inside the PDF (looking for "SIBIU", "BRASOV", etc.)
    let judet = doc.judet || null;
    const m = text.match(/CASA JUDETEANA DE ASIGURARI DE SANATATE\s*\n\s*(\S+)/i);
    if (m) judet = normalizeJudetSimple(m[1].trim());
    return parseProviderTextNumberedDot(text, { tip, judet });
  }
  // 4. Default: multi-judet provider list (testare_genetica / oncologie / hematologie)
  return parseProviderTextJudetGrouped(text, { tip });
}

function normalizeJudetSimple(s: string): string {
  return s.toUpperCase().replace(/[ŞȘ]/g, 'S').replace(/[ŢȚ]/g, 'T').replace(/[ĂÂ]/g, 'A').replace(/Î/g, 'I');
}

// ── Insert provider rows for a document ────────────────────────────────────
async function insertProviders(documentId: number, tipServiciu: string | null, judet: string | null, perioada: string | null, rows: ProviderRow[]): Promise<{ inserted: number; updated: number; skipped: number }> {
  const stats = { inserted: 0, updated: 0, skipped: 0 };
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Wipe existing rows for this document (replace-all since we re-parsed)
    await client.query('DELETE FROM cnas.furnizori WHERE document_id = $1', [documentId]);
    for (const r of rows) {
      if (!r.name || r.name.length < 2 || r.name.startsWith('[unknown')) { stats.skipped++; continue; }
      // Per-row judet (from layout-B parsers) wins over document-level judet.
      const rowJudet = r.judet || judet;
      try {
        await client.query(
          `INSERT INTO cnas.furnizori (
             document_id, tip_serviciu, judet, perioada,
             nr_crt, cod_furnizor, name, reprezentant, sediu, localitate,
             telefon, fax, email, specialitate, raw_text
           ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
           ON CONFLICT (document_id, nr_crt, name) DO NOTHING`,
          [documentId, tipServiciu, rowJudet, perioada,
           r.nr_crt, r.cod_furnizor, r.name, r.reprezentant, r.sediu, r.localitate,
           r.telefon, r.fax, r.email, r.specialitate, r.raw_text]
        );
        stats.inserted++;
      } catch (e) {
        stats.skipped++;
      }
    }
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
  return stats;
}

async function updateDocStatus(id: number, fields: Record<string, any>) {
  const cols = Object.keys(fields);
  const sets = cols.map((c, i) => `${c} = $${i + 2}`).join(', ');
  await pool.query(`UPDATE cnas.documents SET ${sets} WHERE id = $1`, [id, ...cols.map((c) => fields[c])]);
}

// ── Main ────────────────────────────────────────────────────────────────────
async function logScrape(scraper: string, url: string | null, started: Date, stats: any, error?: string) {
  await pool.query(
    `INSERT INTO cnas.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, url, stats.seen || 0, stats.inserted || 0, stats.updated || 0, stats.skipped || 0,
     Date.now() - started.getTime(), started, error || null]
  );
}

async function main() {
  console.log(`[cnas] start (limit=${argLimit || 'all'} metadata-only=${argMetadataOnly} parse-only=${argParseOnly})`);
  const tmpDir = join(tmpdir(), `cnas-${Date.now()}`);
  await mkdir(tmpDir, { recursive: true });

  const started = new Date();
  let docsRegistered = 0, docsParsed = 0, totalRowsInserted = 0;

  try {
    let docs: Array<{ id: number; source_url: string; tip_serviciu: string | null; judet: string | null; perioada: string | null; mime_type: string | null }> = [];

    if (!argParseOnly) {
      // Step 1: enumerate WP media
      const allMedia = await listAllWPMedia();
      console.log(`[cnas] WP media listed: ${allMedia.length}`);
      const providerFiles = allMedia.filter(isProviderListFile);
      console.log(`[cnas] Provider files: ${providerFiles.length}`);

      // Step 2: register documents
      const limit = argLimit > 0 ? Math.min(argLimit, providerFiles.length) : providerFiles.length;
      for (let i = 0; i < limit; i++) {
        const f = providerFiles[i];
        try {
          const { id, needsReparse } = await registerDocument(f);
          docsRegistered++;
          docs.push({ id, source_url: f.source_url, tip_serviciu: null, judet: null, perioada: null, mime_type: f.mime_type });
          if (i % 10 === 0) console.log(`[cnas]   registered ${docsRegistered}/${limit}`);
        } catch (e) {
          console.warn(`[cnas] register failed ${f.source_url}: ${e instanceof Error ? e.message : e}`);
        }
      }
      await logScrape('wp-media-list', null, started, { seen: providerFiles.length, inserted: docsRegistered });
    } else {
      // Parse mode: load pending/failed/no_table docs from DB.
      // no_table is included so the Layout-B fallback (added 2026-05-12) can
      // reprocess the 9 judet-grouped PDFs the original parser couldn't handle.
      const r = await pool.query<{ id: number; source_url: string; tip_serviciu: string | null; judet: string | null; perioada: string | null; mime_type: string | null }>(
        `SELECT id, source_url, tip_serviciu, judet, perioada, mime_type FROM cnas.documents
         WHERE source = 'wp-media' AND parse_status IN ('pending', 'failed', 'no_table')
         ORDER BY published_at DESC NULLS LAST
         ${argLimit > 0 ? `LIMIT ${argLimit}` : ''}`
      );
      docs = r.rows;
      console.log(`[cnas] Parse mode: ${docs.length} pending docs`);
    }

    if (argMetadataOnly) {
      console.log(`[cnas] --metadata-only set, skipping parse`);
      return;
    }

    // Step 3: download + parse each document
    // Reload tip/judet/perioada from DB to ensure fresh
    if (docs.length > 0) {
      const ids = docs.map((d) => d.id);
      const enriched = await pool.query<{ id: number; source_url: string; tip_serviciu: string | null; judet: string | null; perioada: string | null; mime_type: string | null }>(
        `SELECT id, source_url, tip_serviciu, judet, perioada, mime_type FROM cnas.documents WHERE id = ANY($1)`,
        [ids]
      );
      docs = enriched.rows;
    }

    for (let i = 0; i < docs.length; i++) {
      const d = docs[i];
      const docStart = new Date();
      const filename = d.source_url.split('/').pop() || `doc-${d.id}.pdf`;
      const localPath = join(tmpDir, filename);
      try {
        if (!/\.pdf(\?|$)/i.test(d.source_url)) {
          await updateDocStatus(d.id, { parse_status: 'unsupported_format', parsed_at: new Date(), parse_error: `unsupported mime: ${d.mime_type}` });
          continue;
        }
        const dl = await downloadFile(d.source_url, localPath);
        const pageCount = await pdfPageCount(localPath);
        const text = await pdftotextLayout(localPath);
        let rows: ProviderRow[] = parseProviderText(text, { tip: d.tip_serviciu || 'other' });
        // Layout-B fallback: if the row-numbered parser found nothing, try the
        // judet-grouped / radio / single-CAS / numbered-dot variants based on
        // tip_serviciu + filename. The 9 PDFs that hit this path are listed
        // in HANDOFF-cnas-layout-b.md.
        if (rows.length === 0) {
          const lbRows = tryLayoutBParse(text, d, filename);
          if (lbRows.length > 0) rows = lbRows;
        }
        const stats = await insertProviders(d.id, d.tip_serviciu, d.judet, d.perioada, rows);

        await updateDocStatus(d.id, {
          file_sha1: dl.sha1,
          file_size_bytes: dl.size,
          page_count: pageCount,
          parse_status: rows.length > 0 ? 'ok' : 'no_table',
          rows_extracted: stats.inserted,
          parse_error: null,
          parsed_at: new Date(),
        });
        docsParsed++;
        totalRowsInserted += stats.inserted;
        console.log(`[cnas] ${i+1}/${docs.length} ${filename} → ${rows.length} rows (${stats.inserted} ins) [tip=${d.tip_serviciu} judet=${d.judet}]`);
        await logScrape('parse-pdf', d.source_url, docStart, { seen: rows.length, inserted: stats.inserted, skipped: stats.skipped });
      } catch (e) {
        const msg = e instanceof Error ? e.message : String(e);
        console.warn(`[cnas] parse failed ${filename}: ${msg}`);
        try { await updateDocStatus(d.id, { parse_status: 'failed', parse_error: msg.slice(0, 1000), parsed_at: new Date() }); } catch {}
        await logScrape('parse-pdf', d.source_url, docStart, {}, msg);
      } finally {
        try { await unlink(localPath); } catch {}
      }
    }

    console.log(`[cnas] done: ${docsRegistered} docs registered, ${docsParsed} parsed, ${totalRowsInserted} rows inserted`);
  } catch (e) {
    const msg = e instanceof Error ? e.message : String(e);
    await logScrape('main', null, started, {}, msg);
    throw e;
  } finally {
    await pool.end();
  }
}

main().catch((e) => {
  console.error('[cnas] FATAL:', e);
  process.exit(1);
});
