`scripts/ingest/meic-pymes.js`

153 lines
'use strict';

// MEIC PYMEs Activas — bulk national list of registered SMEs.
// Source: datos.go.cr CKAN — package "Lista de Pymes activas - Empresas Activas".

const { fetchJson, fetchBuffer, slug, regionMap, ensureRegion, upsertPlace, startRun, finishRun, pool } = require('./_lib');
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');

const CKAN_HOSTS = (process.env.CKAN_BASE
  ? [process.env.CKAN_BASE]
  : [
      'https://www.datosabiertos.go.cr/api/3/action',
      'https://datos.go.cr/api/3/action',
      'https://datosabiertos.presidencia.go.cr/api/3/action',
    ]);
const SEARCH_QS = 'q=pymes+activas+OR+empresas+activas&rows=10';
const CACHE_DIR = path.join(__dirname, '..', '..', 'data', 'cache');
const ROW_CAP = parseInt(process.env.MEIC_ROW_CAP || '15000', 10);

function findResource(pkg) {
  const resources = pkg.result?.resources || [];
  return resources.find(r => /xlsx?$/i.test(r.format||r.url||''))
      || resources.find(r => /csv$/i.test(r.format||r.url||''))
      || resources[0];
}

function findCol(headers, ...needles) {
  for (let i = 0; i < headers.length; i++) {
    const h = String(headers[i] || '').toLowerCase().trim();
    if (needles.some(n => h.includes(n))) return i;
  }
  return -1;
}

(async () => {
  const runId = await startRun('meic-pymes', 'Fetch MEIC PYMEs Activas via CKAN + parse XLSX');
  let added = 0, updated = 0, total = 0;
  try {
    fs.mkdirSync(CACHE_DIR, { recursive: true });

    let CKAN_BASE = null, search = null;
    for (const host of CKAN_HOSTS) {
      try { search = await fetchJson(`${host}/package_search?${SEARCH_QS}`); CKAN_BASE = host; break; }
      catch (e) { console.warn(`[meic-pymes] ${host} → ${e.message}`); }
    }
    if (!search) throw new Error('all CKAN hosts unreachable');

    const pkg = (search.result?.results || [])[0];
    if (!pkg) throw new Error('no PYMEs package in CKAN');
    const pkgFull = await fetchJson(`${CKAN_BASE}/package_show?id=${encodeURIComponent(pkg.id)}`);
    const res = findResource(pkgFull);
    if (!res?.url) throw new Error('no resource on package');

    const ext = (path.extname(res.url) || '.xlsx').toLowerCase();
    const cachePath = path.join(CACHE_DIR, `meic-pymes${ext}`);
    let buf;
    if (fs.existsSync(cachePath) && Buffer.byteLength(fs.readFileSync(cachePath)) > 100000) {
      buf = fs.readFileSync(cachePath);
      console.log(`[meic-pymes] using cache (${(buf.length/1024).toFixed(1)} KB)`);
    } else {
      buf = await fetchBuffer(res.url);
      fs.writeFileSync(cachePath, buf);
      console.log(`[meic-pymes] downloaded ${(buf.length/1024).toFixed(1)} KB → ${cachePath}`);
    }

    const wb = XLSX.read(buf, { type: 'buffer' });
    const sheetName = wb.SheetNames[0];
    const ws = wb.Sheets[sheetName];
    const rows = XLSX.utils.sheet_to_json(ws, { header: 1, defval: null });
    if (!rows.length) throw new Error('XLSX has no rows');

    // Header is usually row 0 or 1 (sometimes a title banner is at row 0).
    let headerIdx = 0;
    for (let i = 0; i < Math.min(5, rows.length); i++) {
      const r = rows[i] || [];
      const lower = r.map(c => String(c||'').toLowerCase());
      if (lower.some(c => /(nombre|raz[oó]n)/i.test(c)) && lower.some(c => /(canton|cantón|provincia|distrito)/i.test(c))) {
        headerIdx = i; break;
      }
    }
    const headers = rows[headerIdx] || [];
    console.log(`[meic-pymes] sheet=${sheetName} headerIdx=${headerIdx} cols=${headers.length}`);
    console.log(`[meic-pymes] headers: ${headers.map(h => String(h||'').trim()).join(' | ')}`);

    const iName    = findCol(headers, 'nombre','razon','razón','denominación','empresa');
    const iAct     = findCol(headers, 'actividad','rubro','sector','codiiso','ciiu');
    const iCedula  = findCol(headers, 'cedula','cédula','identif','id_juri');
    const iProv    = findCol(headers, 'provincia');
    const iCanton  = findCol(headers, 'canton','cantón');
    const iDist    = findCol(headers, 'distrito');
    const iEmail   = findCol(headers, 'email','correo');
    const iPhone   = findCol(headers, 'telefono','teléfono');
    const iSize    = findCol(headers, 'tamaño','tamano','tamaño_empresa','classification');

    if (iName < 0) throw new Error(`no name column found; headers=${headers.slice(0,12)}`);
    console.log(`[meic-pymes] mapping: name=${iName} cedula=${iCedula} canton=${iCanton} prov=${iProv} act=${iAct}`);

    const rmap = await regionMap();
    const dataRows = rows.slice(headerIdx + 1).filter(r => Array.isArray(r) && r[iName]);

    for (const r of dataRows.slice(0, ROW_CAP)) {
      const name = String(r[iName] || '').trim(); if (!name) continue;
      const canton   = String(r[iCanton] || '').trim();
      const province = String(r[iProv] || '').trim();
      const region = canton ? await ensureRegion(rmap, canton, province) : rmap.fallback;
      const act = String(r[iAct] || '').toLowerCase();
      const vertical =
        /restaurante|gastronom|comida|cafeter|bar|panade|repostería|alimentos/.test(act) ? 'service_food' :
        /hotel|hospeda|cabin|alojami|villa|posada|lodge/.test(act) ? 'tourism_hotel' :
        /tour|turismo|aventura|operador|recreac/.test(act) ? 'tourism_tour' :
        /belleza|spa|peluquer|barber|estética|estetica/.test(act) ? 'service_beauty' :
        /tienda|comerc|venta|retail|supermerc|abarrot|ferreter/.test(act) ? 'service_retail' :
        /gimna|fitness|crossfit|yoga|deporte/.test(act) ? 'service_fitness' :
        /mascot|veterin|pet|animal/.test(act) ? 'service_pet' :
        /taller|mecan|automotr|car wash|llanter|repuesto/.test(act) ? 'service_auto' :
        /limpie|aseo|janitor|lavander/.test(act) ? 'service_cleaning' :
        /diseñ|publicid|fotograf|estudio|art|creativ|imprent/.test(act) ? 'service_creative' :
        /alquiler|rental|arrendami|inmobil/.test(act) ? 'rentals_realestate' :
        'service_retail';
      const category = vertical.startsWith('tourism_') ? 'tourism' : (vertical.startsWith('rentals_') ? 'rentals' : 'service');
      const cedula = iCedula >= 0 ? String(r[iCedula] || '').trim() : '';
      const placeSlug = cedula ? `meic-${cedula}` : slug(`meic-${name}-${region.slug}`);
      const upsertResult = await upsertPlace({
        slug: placeSlug,
        name,
        category, vertical,
        region_id: region.id,
        description: act ? `MEIC-registered: ${act}` : 'MEIC-registered PYME.',
        address: [iDist >= 0 ? r[iDist] : null, canton].filter(Boolean).join(', '),
        phone: iPhone >= 0 ? (String(r[iPhone] || '').trim() || null) : null,
        email: iEmail >= 0 ? (String(r[iEmail] || '').trim() || null) : null,
        cedula_juridica: cedula || null,
        source: 'meic-pymes',
        source_url: res.url,
      });
      if (upsertResult.inserted) added++; else updated++;
      total++;
      if (total % 1000 === 0) console.log(`[meic-pymes] progress: ${total}`);
    }

    await finishRun(runId, { rows_in: total, rows_added: added, rows_updated: updated, status: 'ok' });
    console.log(`[meic-pymes] DONE in=${total} added=${added} updated=${updated}`);
  } catch (e) {
    await finishRun(runId, { status: 'error', notes: e.message });
    console.error('[meic-pymes] FAIL', e.message);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
})();