const { fetchInvimaDataset, INVIMA_DATASETS } = require('../APIinvima');
const { invimaAdapters, adapterKeys, cleanText } = require('./adapters');

const clampInt = (value, fallback, min, max) => {
  const parsed = Number.parseInt(String(value), 10);
  if (Number.isNaN(parsed)) return fallback;
  return Math.max(min, Math.min(max, parsed));
};

const parseListParam = (value) => {
  if (Array.isArray(value)) {
    return value.map((item) => String(item).trim()).filter(Boolean);
  }
  if (typeof value !== 'string') return [];
  return value
    .split(',')
    .map((item) => item.trim())
    .filter(Boolean);
};

const removeAccents = (value) =>
  String(value || '')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '');

const slugify = (value) => {
  const normalized = removeAccents(String(value || '').toLowerCase())
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '');
  return normalized || 'sin-categoria';
};

const toTitleCase = (value) =>
  String(value || '')
    .toLowerCase()
    .replace(/\b\w/g, (char) => char.toUpperCase())
    .trim();

const normalizeCategory = (value, fallback = 'Sin categoria') => {
  const base = cleanText(value) || fallback;
  const normalized = removeAccents(base).toUpperCase().replace(/\s+/g, ' ').trim();

  if (normalized.includes('DISPOSIT') || normalized.includes('TECNOLOG')) {
    return 'Dispositivos y otras tecnologias';
  }
  if (normalized.includes('MEDICO') && normalized.includes('QUIR')) return 'Medico quirurgicos';
  if (normalized.includes('MED') && normalized.includes('OFIC')) return 'Medicamentos oficinales';
  if (normalized.includes('MEDIC')) return 'Medicamentos';
  if (normalized.includes('RS') && normalized.includes('NSO')) return 'RS y NSO';
  if (normalized.includes('CUM')) return 'CUM vigentes';
  if (normalized.includes('HOMEOP')) return 'Medicamentos homeopaticos';
  if (normalized.includes('FITOTER')) return 'Productos fitoterapeuticos';
  if (normalized.includes('SUPLEMENT')) return 'Suplementos dietarios';
  if (normalized.includes('VACUN')) return 'Vacunas';
  if (normalized.includes('COSMET')) return 'Cosmeticos';
  if (normalized.includes('REACTIV') && normalized.includes('DIAG')) return 'Reactivos diagnostico';
  if (normalized.includes('BIOLOGIC')) return 'Biologicos';
  if (normalized.includes('ALIMENTO')) return 'Alimentos';
  if (normalized.includes('ASEO') || normalized.includes('LIMPIEZA')) return 'Aseo y limpieza';
  if (normalized.includes('ODONTO')) return 'Odontologicos';
  if (normalized.includes('BEBIDA') && normalized.includes('ALCOH')) return 'Bebidas alcoholicas';
  if (normalized.includes('PLAGUIC')) return 'Plaguicidas';

  return toTitleCase(base);
};

const buildSearchTokens = (value) =>
  removeAccents(String(value || '').toLowerCase())
    .split(/\s+/)
    .map((token) => token.replace(/[^a-z0-9]/g, ''))
    .filter((token) => token.length >= 3)
    .slice(0, 8);

const buildSearchText = (item) =>
  [
    item.source_dataset_name,
    item.categoria_linea,
    item.categoria_canonica,
    item.expediente,
    item.registro_sanitario,
    item.producto,
    item.titular,
    item.estado_registro,
    item.modalidad,
    item.grupo,
    item.marca,
    item.principio_activo,
    item.forma_farmaceutica,
    item.presentacion_comercial,
    item.atc,
    item.rol_nombre,
    item.rol_tipo,
    item.ciudad_titular,
    item.pais_titular,
    item.fabricante,
    item.importador,
    item.vigencia
  ]
    .map((value) => cleanText(value))
    .filter(Boolean)
    .join(' ');

const normalizeCatalogRecord = (item) => {
  const categoriaCanonica = normalizeCategory(
    item.categoria_canonica || item.categoria_linea || item.source_dataset_name || item.source_dataset_key
  );
  return {
    ...item,
    categoria_canonica: categoriaCanonica,
    categoria_slug: slugify(categoriaCanonica),
    search_text: buildSearchText({
      ...item,
      categoria_canonica: categoriaCanonica
    })
  };
};

const ensureInvimaTables = async (pool) => {
  const lockKey = 93477111;
  await pool.query('SELECT pg_advisory_lock($1);', [lockKey]);

  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS invima_api_raw (
        id bigserial PRIMARY KEY,
        source_dataset_key text NOT NULL,
        source_dataset_id text NOT NULL,
        source_uid text NOT NULL,
        raw_payload jsonb NOT NULL,
        fetched_at timestamptz NOT NULL DEFAULT NOW(),
        created_at timestamptz NOT NULL DEFAULT NOW(),
        updated_at timestamptz NOT NULL DEFAULT NOW(),
        CONSTRAINT invima_api_raw_unique UNIQUE (source_dataset_key, source_uid)
      );
    `);

    await pool.query(`
      CREATE TABLE IF NOT EXISTS invima_api_catalog (
        source_dataset_key text NOT NULL,
        source_dataset_id text NOT NULL,
        source_uid text NOT NULL,
        source_dataset_name text NOT NULL,
        categoria_linea text NOT NULL,
        categoria_canonica text,
        categoria_slug text,
        expediente text,
        registro_sanitario text,
        producto text,
        titular text,
        estado_registro text,
        fecha_expedicion date,
        fecha_vencimiento date,
        modalidad text,
        grupo text,
        marca text,
        principio_activo text,
        forma_farmaceutica text,
        presentacion_comercial text,
        atc text,
        rol_nombre text,
        rol_tipo text,
        ciudad_titular text,
        pais_titular text,
        fabricante text,
        importador text,
        vigencia text,
        search_text text,
        extra jsonb NOT NULL,
        fetched_at timestamptz NOT NULL DEFAULT NOW(),
        updated_at timestamptz NOT NULL DEFAULT NOW(),
        CONSTRAINT invima_api_catalog_pk PRIMARY KEY (source_dataset_key, source_uid)
      );
    `);

    await pool.query('ALTER TABLE invima_api_catalog ADD COLUMN IF NOT EXISTS categoria_canonica text;');
    await pool.query('ALTER TABLE invima_api_catalog ADD COLUMN IF NOT EXISTS categoria_slug text;');
    await pool.query('ALTER TABLE invima_api_catalog ADD COLUMN IF NOT EXISTS search_text text;');

    let slugSourceExpression = "lower(COALESCE(NULLIF(categoria_canonica, ''), categoria_linea, 'Sin categoria'))";
    try {
      await pool.query('CREATE EXTENSION IF NOT EXISTS unaccent;');
      slugSourceExpression =
        "lower(unaccent(COALESCE(NULLIF(categoria_canonica, ''), categoria_linea, 'Sin categoria')))";
    } catch (error) {
      console.warn('No se pudo crear extension unaccent:', error.message);
    }

    await pool.query(`
      UPDATE invima_api_catalog
      SET
        categoria_canonica = COALESCE(NULLIF(categoria_canonica, ''), categoria_linea, 'Sin categoria'),
        categoria_slug = COALESCE(
          NULLIF(categoria_slug, ''),
          COALESCE(
            NULLIF(
              trim(BOTH '-' FROM regexp_replace(
                ${slugSourceExpression},
                '[^a-z0-9]+',
                '-',
                'g'
              )),
              ''
            ),
            'sin-categoria'
          )
        ),
        search_text = COALESCE(
          NULLIF(search_text, ''),
          trim(concat_ws(' ',
            source_dataset_name,
            categoria_linea,
            categoria_canonica,
            expediente,
            registro_sanitario,
            producto,
            titular,
            estado_registro,
            modalidad,
            grupo,
            marca,
            principio_activo,
            forma_farmaceutica,
            presentacion_comercial,
            atc,
            rol_nombre,
            rol_tipo,
            ciudad_titular,
            pais_titular,
            fabricante,
            importador,
            vigencia
          ))
        )
      WHERE
        categoria_canonica IS NULL OR categoria_canonica = '' OR
        categoria_slug IS NULL OR categoria_slug = '' OR
        search_text IS NULL OR search_text = '';
    `);

    await pool.query('DROP INDEX IF EXISTS idx_invima_api_catalog_producto;');
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_dataset ON invima_api_catalog (source_dataset_key);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_categoria_slug ON invima_api_catalog (categoria_slug);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_estado ON invima_api_catalog (estado_registro);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_registro ON invima_api_catalog (registro_sanitario);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_expediente ON invima_api_catalog (expediente);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_modalidad ON invima_api_catalog (modalidad);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_grupo ON invima_api_catalog (grupo);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_vencimiento ON invima_api_catalog (fecha_vencimiento);'
    );
    await pool.query(
      'CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_expedicion ON invima_api_catalog (fecha_expedicion);'
    );
    await pool.query(
      "CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_search_fts ON invima_api_catalog USING GIN (to_tsvector('spanish', COALESCE(search_text, '')));"
    );

    try {
      await pool.query('CREATE EXTENSION IF NOT EXISTS pg_trgm;');
      await pool.query(`
        CREATE INDEX IF NOT EXISTS idx_invima_api_catalog_search_trgm
        ON invima_api_catalog USING GIN (search_text gin_trgm_ops);
      `);
    } catch (error) {
      console.warn('No se pudo crear indice trigram:', error.message);
    }
  } finally {
    await pool.query('SELECT pg_advisory_unlock($1);', [lockKey]);
  }
};

const buildUpsertQuery = (table, columns, rows, conflictColumns, updateColumns) => {
  if (!rows.length) return null;

  const values = [];
  const placeholders = rows.map((row, rowIndex) => {
    const offset = rowIndex * columns.length;
    row.forEach((value) => values.push(value));
    const rowPlaceholders = columns.map((_, colIndex) => `$${offset + colIndex + 1}`);
    return `(${rowPlaceholders.join(', ')})`;
  });

  const updateSet = updateColumns.map((column) => `${column} = EXCLUDED.${column}`).join(', ');

  return {
    query: `
      INSERT INTO ${table} (${columns.join(', ')})
      VALUES ${placeholders.join(', ')}
      ON CONFLICT (${conflictColumns.join(', ')}) DO UPDATE SET ${updateSet};
    `,
    values
  };
};

const chunkArray = (items, size) => {
  const chunks = [];
  for (let i = 0; i < items.length; i += size) {
    chunks.push(items.slice(i, i + size));
  }
  return chunks;
};

const dedupeBySourceUid = (rows) => {
  const unique = new Map();
  for (const row of rows) {
    if (!row || !row.source_uid) continue;
    unique.set(row.source_uid, row);
  }
  return Array.from(unique.values());
};

const persistBatch = async (pool, datasetKey, datasetId, normalizedRows) => {
  if (!normalizedRows.length) return 0;
  const uniqueRows = dedupeBySourceUid(normalizedRows).map((row) => normalizeCatalogRecord(row));
  if (!uniqueRows.length) return 0;

  const rawColumns = [
    'source_dataset_key',
    'source_dataset_id',
    'source_uid',
    'raw_payload',
    'fetched_at',
    'updated_at'
  ];

  const catalogColumns = [
    'source_dataset_key',
    'source_dataset_id',
    'source_uid',
    'source_dataset_name',
    'categoria_linea',
    'categoria_canonica',
    'categoria_slug',
    'expediente',
    'registro_sanitario',
    'producto',
    'titular',
    'estado_registro',
    'fecha_expedicion',
    'fecha_vencimiento',
    'modalidad',
    'grupo',
    'marca',
    'principio_activo',
    'forma_farmaceutica',
    'presentacion_comercial',
    'atc',
    'rol_nombre',
    'rol_tipo',
    'ciudad_titular',
    'pais_titular',
    'fabricante',
    'importador',
    'vigencia',
    'search_text',
    'extra',
    'fetched_at',
    'updated_at'
  ];

  const now = new Date();

  const rawRows = uniqueRows.map((item) => [
    datasetKey,
    datasetId,
    item.source_uid,
    item.extra,
    now,
    now
  ]);

  const catalogRows = uniqueRows.map((item) => [
    item.source_dataset_key,
    item.source_dataset_id,
    item.source_uid,
    item.source_dataset_name,
    item.categoria_linea,
    item.categoria_canonica,
    item.categoria_slug,
    item.expediente,
    item.registro_sanitario,
    item.producto,
    item.titular,
    item.estado_registro,
    item.fecha_expedicion,
    item.fecha_vencimiento,
    item.modalidad,
    item.grupo,
    item.marca,
    item.principio_activo,
    item.forma_farmaceutica,
    item.presentacion_comercial,
    item.atc,
    item.rol_nombre,
    item.rol_tipo,
    item.ciudad_titular,
    item.pais_titular,
    item.fabricante,
    item.importador,
    item.vigencia,
    item.search_text,
    item.extra,
    now,
    now
  ]);

  const rawUpsert = buildUpsertQuery(
    'invima_api_raw',
    rawColumns,
    rawRows,
    ['source_dataset_key', 'source_uid'],
    ['raw_payload', 'fetched_at', 'updated_at']
  );

  const catalogUpsert = buildUpsertQuery(
    'invima_api_catalog',
    catalogColumns,
    catalogRows,
    ['source_dataset_key', 'source_uid'],
    [
      'source_dataset_id',
      'source_dataset_name',
      'categoria_linea',
      'categoria_canonica',
      'categoria_slug',
      'expediente',
      'registro_sanitario',
      'producto',
      'titular',
      'estado_registro',
      'fecha_expedicion',
      'fecha_vencimiento',
      'modalidad',
      'grupo',
      'marca',
      'principio_activo',
      'forma_farmaceutica',
      'presentacion_comercial',
      'atc',
      'rol_nombre',
      'rol_tipo',
      'ciudad_titular',
      'pais_titular',
      'fabricante',
      'importador',
      'vigencia',
      'search_text',
      'extra',
      'fetched_at',
      'updated_at'
    ]
  );

  await pool.query('BEGIN');
  try {
    if (rawUpsert) {
      await pool.query(rawUpsert.query, rawUpsert.values);
    }
    if (catalogUpsert) {
      await pool.query(catalogUpsert.query, catalogUpsert.values);
    }
    await pool.query('COMMIT');
  } catch (error) {
    await pool.query('ROLLBACK');
    throw error;
  }

  return uniqueRows.length;
};

const isRetryableSocrataError = (error) => {
  const text = String(error?.message || error || '').toLowerCase();
  return (
    text.includes(' 429 ') ||
    text.includes(' 500 ') ||
    text.includes(' 502 ') ||
    text.includes(' 503 ') ||
    text.includes(' 504 ') ||
    text.includes('fetch failed') ||
    text.includes('timeout') ||
    text.includes('internal error') ||
    text.includes('internal-error') ||
    text.includes('socket hang up')
  );
};

const syncOneDataset = async (pool, datasetKey, options = {}) => {
  const adapter = invimaAdapters[datasetKey];
  if (!adapter) {
    throw new Error(`Dataset adapter no soportado: ${datasetKey}`);
  }

  const pageSize = clampInt(options.pageSize, 1000, 100, 5000);
  const maxPages = options.maxPages ? clampInt(options.maxPages, 1, 1, 50000) : null;
  const where = cleanText(options.where);
  const select = cleanText(options.select);
  const requestedOrder = cleanText(options.order) || cleanText(adapter.defaultOrder);
  let currentOrder = requestedOrder;
  let currentPageSize = pageSize;

  let offset = 0;
  let page = 0;
  let totalFetched = 0;
  let totalPersisted = 0;
  const warnings = [];

  const emitProgress = (event) => {
    if (typeof options.onProgress === 'function') {
      options.onProgress({
        datasetKey,
        datasetId: adapter.id,
        ...event
      });
    }
  };

  while (true) {
    if (maxPages && page >= maxPages) break;

    const sodaParams = {
      $limit: currentPageSize,
      $offset: offset
    };

    if (select) sodaParams.$select = select;
    if (where) sodaParams.$where = where;
    if (currentOrder) sodaParams.$order = currentOrder;

    let rawRows = null;
    try {
      rawRows = await fetchInvimaDataset(datasetKey, sodaParams, options.appToken, {
        retries: clampInt(options.fetchRetries, 4, 0, 8),
        timeoutMs: clampInt(options.fetchTimeoutMs, 35000, 4000, 120000)
      });
    } catch (error) {
      const retryable = isRetryableSocrataError(error);
      const message = String(error?.message || error);

      if (retryable && currentOrder && currentOrder !== ':id') {
        currentOrder = ':id';
        warnings.push(
          `[${datasetKey}] Fallo con order "${requestedOrder || 'default'}" en offset ${offset}; reintentando con ":id".`
        );
        continue;
      }

      if (retryable && currentOrder === ':id') {
        currentOrder = '';
        warnings.push(`[${datasetKey}] Fallo con order ":id" en offset ${offset}; reintentando sin order.`);
        continue;
      }

      if (retryable && currentPageSize > 200) {
        const nextSize = Math.max(200, Math.floor(currentPageSize / 2));
        if (nextSize < currentPageSize) {
          warnings.push(
            `[${datasetKey}] Error en offset ${offset}; reduciendo pagina ${currentPageSize} -> ${nextSize}.`
          );
          currentPageSize = nextSize;
          continue;
        }
      }

      const wrapped = new Error(
        `Sincronizacion detenida en dataset ${datasetKey} (offset ${offset}, pagina ${page + 1}): ${message}`
      );
      wrapped.cause = error;
      throw wrapped;
    }

    if (!Array.isArray(rawRows) || rawRows.length === 0) break;

    const normalizedRows = rawRows
      .map((row) => adapter.normalize(row))
      .filter((row) => row && row.source_uid);

    const chunks = chunkArray(normalizedRows, 500);
    let pagePersisted = 0;
    for (const chunk of chunks) {
      const persistedCount = await persistBatch(pool, adapter.key, adapter.id, chunk);
      totalPersisted += persistedCount;
      pagePersisted += persistedCount;
    }

    totalFetched += rawRows.length;
    page += 1;
    offset += rawRows.length;
    emitProgress({
      type: 'page_persisted',
      fetched: totalFetched,
      persisted: totalPersisted,
      pages: page,
      pageFetched: rawRows.length,
      pagePersisted,
      offset,
      pageSizeUsed: currentPageSize,
      warnings: [...warnings]
    });
    if (rawRows.length < currentPageSize) break;
  }

  return {
    datasetKey,
    datasetId: adapter.id,
    fetched: totalFetched,
    persisted: totalPersisted,
    pages: page,
    pageSizeUsed: currentPageSize,
    orderUsed: currentOrder || null,
    warnings
  };
};

const syncInvimaDatasets = async (pool, options = {}) => {
  const keys = getSyncDatasetKeys(options.datasets);
  const concurrency = clampInt(options.concurrency, 3, 1, 8);
  const emitProgress = (event) => {
    if (typeof options.onProgress === 'function') {
      options.onProgress(event);
    }
  };

  if (!keys.length) {
    throw new Error('No hay datasets validos para sincronizar.');
  }

  const queue = [...keys];
  const resultMap = new Map();
  const failed = [];
  const workerCount = Math.min(concurrency, keys.length);

  emitProgress({ type: 'sync_started', keys, concurrency: workerCount });

  const worker = async () => {
    while (queue.length) {
      const key = queue.shift();
      if (!key) return;
      try {
        emitProgress({ type: 'dataset_started', datasetKey: key });
        const result = await syncOneDataset(pool, key, {
          pageSize: options.pageSize,
          maxPages: options.maxPages,
          appToken: options.appToken,
          where: options.where,
          select: options.select,
          order: options.order,
          fetchRetries: options.fetchRetries,
          fetchTimeoutMs: options.fetchTimeoutMs,
          onProgress: options.onProgress
        });
        resultMap.set(key, result);
        emitProgress({ type: 'dataset_completed', ...result });
      } catch (error) {
        const failure = {
          datasetKey: key,
          error: String(error?.message || error)
        };
        failed.push(failure);
        emitProgress({ type: 'dataset_failed', ...failure });
      }
    }
  };

  await Promise.all(Array.from({ length: workerCount }, () => worker()));
  const results = keys.map((key) => resultMap.get(key)).filter(Boolean);

  const summary = results.reduce(
    (acc, item) => {
      acc.datasets += 1;
      acc.fetched += item.fetched;
      acc.persisted += item.persisted;
      acc.pages += item.pages;
      return acc;
    },
    { datasets: 0, fetched: 0, persisted: 0, pages: 0, failed: failed.length, requested: keys.length }
  );

  return {
    summary,
    datasets: results,
    failed
  };
};

const buildCatalogWhere = (filters, params, options = {}) => {
  const where = [];
  let searchQueryParamIndex = null;
  const searchMode = options.searchMode || 'primary';

  if (filters.q) {
    if (searchMode === 'fallback_like') {
      const tokens = filters.searchTokens.length ? filters.searchTokens : [filters.q];
      const tokenClauses = tokens.slice(0, 8).map((token) => {
        params.push(`%${token}%`);
        const likeIdx = params.length;
        return `(
          COALESCE(search_text, '') ILIKE $${likeIdx}
          OR COALESCE(registro_sanitario, '') ILIKE $${likeIdx}
          OR COALESCE(expediente, '') ILIKE $${likeIdx}
          OR COALESCE(producto, '') ILIKE $${likeIdx}
          OR COALESCE(categoria_canonica, '') ILIKE $${likeIdx}
          OR COALESCE(categoria_linea, '') ILIKE $${likeIdx}
        )`;
      });
      where.push(`(${tokenClauses.join(' AND ')})`);
    } else if (filters.useFts) {
      params.push(filters.q);
      searchQueryParamIndex = params.length;
      params.push(`%${filters.q}%`);
      const likeIdx = params.length;
      where.push(
        `(
          to_tsvector('spanish', COALESCE(search_text, '')) @@ websearch_to_tsquery('spanish', $${searchQueryParamIndex})
          OR COALESCE(registro_sanitario, '') ILIKE $${likeIdx}
          OR COALESCE(expediente, '') ILIKE $${likeIdx}
          OR COALESCE(producto, '') ILIKE $${likeIdx}
          OR COALESCE(categoria_canonica, '') ILIKE $${likeIdx}
          OR COALESCE(categoria_linea, '') ILIKE $${likeIdx}
        )`
      );
    } else {
      params.push(`%${filters.q}%`);
      const likeIdx = params.length;
      where.push(`(
        COALESCE(search_text, '') ILIKE $${likeIdx}
        OR COALESCE(registro_sanitario, '') ILIKE $${likeIdx}
        OR COALESCE(expediente, '') ILIKE $${likeIdx}
      )`);
    }
  }

  if (filters.categorias.length) {
    params.push(filters.categorias);
    where.push(`categoria_slug = ANY($${params.length}::text[])`);
  }

  if (filters.datasets.length) {
    params.push(filters.datasets);
    where.push(`source_dataset_key = ANY($${params.length}::text[])`);
  }

  if (filters.estados.length) {
    params.push(filters.estados);
    where.push(`estado_registro = ANY($${params.length}::text[])`);
  }

  if (filters.modalidades.length) {
    params.push(filters.modalidades);
    where.push(`modalidad = ANY($${params.length}::text[])`);
  }

  if (filters.grupos.length) {
    params.push(filters.grupos);
    where.push(`grupo = ANY($${params.length}::text[])`);
  }

  if (filters.fechaVencDesde) {
    params.push(filters.fechaVencDesde);
    where.push(`fecha_vencimiento >= $${params.length}::date`);
  }

  if (filters.fechaVencHasta) {
    params.push(filters.fechaVencHasta);
    where.push(`fecha_vencimiento <= $${params.length}::date`);
  }

  if (filters.fechaExpDesde) {
    params.push(filters.fechaExpDesde);
    where.push(`fecha_expedicion >= $${params.length}::date`);
  }

  if (filters.fechaExpHasta) {
    params.push(filters.fechaExpHasta);
    where.push(`fecha_expedicion <= $${params.length}::date`);
  }

  if (filters.onlyVigentes) {
    where.push(`(
      (COALESCE(estado_registro, '') ILIKE '%vigente%' OR COALESCE(vigencia, '') ILIKE '%vigente%')
      AND COALESCE(estado_registro, '') NOT ILIKE '%no vigente%'
      AND COALESCE(vigencia, '') NOT ILIKE '%no vigente%'
    )`);
  }

  return {
    whereSql: where.length ? `WHERE ${where.join(' AND ')}` : '',
    searchQueryParamIndex
  };
};

const parseCatalogFilters = (query = {}) => {
  const parseDate = (value) => {
    const text = cleanText(value);
    if (!text) return null;
    return /^\d{4}-\d{2}-\d{2}$/.test(text) ? text : null;
  };

  const q = cleanText(query.q) || '';
  const searchTokens = buildSearchTokens(q);
  const useFts = q.length >= 3;
  const queryTokenCount = q ? q.split(/\s+/).filter(Boolean).length : 0;
  const useRank = useFts && queryTokenCount > 1;
  const categorias = parseListParam(query.categorias).map((item) => slugify(item)).slice(0, 30);
  const datasets = parseListParam(query.datasets).filter((key) => invimaAdapters[key]);
  const estados = parseListParam(query.estados).slice(0, 30);
  const modalidades = parseListParam(query.modalidades).slice(0, 30);
  const grupos = parseListParam(query.grupos).slice(0, 30);
  const limit = clampInt(query.limit, 25, 1, 500);
  const offset = clampInt(query.offset, 0, 0, 2_000_000);
  const onlyVigentes = String(query.onlyVigentes || '').toLowerCase() === 'true';
  const fechaVencDesde = parseDate(query.fechaVencDesde);
  const fechaVencHasta = parseDate(query.fechaVencHasta);
  const fechaExpDesde = parseDate(query.fechaExpDesde);
  const fechaExpHasta = parseDate(query.fechaExpHasta);

  return {
    q,
    searchTokens,
    useFts,
    useRank,
    categorias,
    datasets,
    estados,
    modalidades,
    grupos,
    fechaVencDesde,
    fechaVencHasta,
    fechaExpDesde,
    fechaExpHasta,
    limit,
    offset,
    onlyVigentes
  };
};

const catalogSelectSql = `
  source_dataset_key,
  source_dataset_id,
  source_dataset_name,
  categoria_linea,
  categoria_canonica,
  categoria_slug,
  expediente,
  registro_sanitario,
  producto,
  titular,
  estado_registro,
  fecha_expedicion,
  fecha_vencimiento,
  modalidad,
  grupo,
  marca,
  principio_activo,
  forma_farmaceutica,
  presentacion_comercial,
  atc,
  rol_nombre,
  rol_tipo,
  ciudad_titular,
  pais_titular,
  fabricante,
  importador,
  vigencia
`;

const runCatalogQuery = async (pool, filters, options = {}) => {
  const params = [];
  const searchMode = options.searchMode || 'primary';
  const { whereSql, searchQueryParamIndex } = buildCatalogWhere(filters, params, { searchMode });

  const countSql = `SELECT COUNT(*)::int AS total FROM invima_api_catalog ${whereSql};`;
  const countResult = await pool.query(countSql, params);
  const total = countResult.rows[0]?.total || 0;

  const dataParams = [...params, filters.limit, filters.offset];
  const limitIndex = params.length + 1;
  const offsetIndex = params.length + 2;
  const hasSearch =
    searchMode === 'primary' && Boolean(filters.q) && filters.useRank && searchQueryParamIndex !== null;

  const rankSql = hasSearch
    ? `
      ts_rank(
        to_tsvector('spanish', COALESCE(search_text, '')),
        websearch_to_tsquery('spanish', $${searchQueryParamIndex})
      ) AS score,
    `
    : '';

  const orderSql = hasSearch
    ? 'ORDER BY score DESC NULLS LAST, fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST, source_dataset_key ASC, source_uid ASC'
    : 'ORDER BY fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST, source_dataset_key ASC, source_uid ASC';

  const dataSql = filters.q
    ? `
      WITH matched AS MATERIALIZED (
        SELECT
          ${rankSql}
          ${catalogSelectSql},
          source_uid
        FROM invima_api_catalog
        ${whereSql}
      )
      SELECT
        ${hasSearch ? 'score,' : ''}
        ${catalogSelectSql}
      FROM matched
      ${orderSql}
      LIMIT $${limitIndex} OFFSET $${offsetIndex};
    `
    : `
      SELECT
        ${catalogSelectSql}
      FROM invima_api_catalog
      ${whereSql}
      ${orderSql}
      LIMIT $${limitIndex} OFFSET $${offsetIndex};
    `;

  const { rows } = await pool.query(dataSql, dataParams);

  return {
    items: rows,
    total,
    limit: filters.limit,
    offset: filters.offset
  };
};

const getInvimaCatalog = async (pool, query = {}) => {
  const filters = parseCatalogFilters(query);
  const primary = await runCatalogQuery(pool, filters, { searchMode: 'primary' });

  if (!filters.q || primary.total > 0 || filters.searchTokens.length <= 1) {
    return primary;
  }

  return runCatalogQuery(pool, filters, { searchMode: 'fallback_like' });
};

const getInvimaCatalogForExport = async (pool, query = {}) => {
  const filters = parseCatalogFilters(query);
  const exportLimit = clampInt(query.exportLimit, 50000, 1, 1_000_000);

  const params = [];
  const { whereSql } = buildCatalogWhere(filters, params);

  params.push(exportLimit);
  const limitIndex = params.length;

  const sql = `
    SELECT
      ${catalogSelectSql}
    FROM invima_api_catalog
    ${whereSql}
    ORDER BY fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST, source_dataset_key ASC, source_uid ASC
    LIMIT $${limitIndex};
  `;

  const { rows } = await pool.query(sql, params);
  return rows;
};

const getInvimaCatalogForExportChunk = async (pool, query = {}) => {
  const filters = parseCatalogFilters(query);
  const chunkLimit = clampInt(query.chunkLimit, 5000, 100, 20000);
  const chunkOffset = clampInt(query.chunkOffset, 0, 0, 10_000_000);

  const params = [];
  const { whereSql } = buildCatalogWhere(filters, params);
  params.push(chunkLimit, chunkOffset);
  const limitIndex = params.length - 1;
  const offsetIndex = params.length;

  const sql = `
    SELECT
      ${catalogSelectSql}
    FROM invima_api_catalog
    ${whereSql}
    ORDER BY fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST, source_dataset_key ASC, source_uid ASC
    LIMIT $${limitIndex} OFFSET $${offsetIndex};
  `;

  const { rows } = await pool.query(sql, params);
  return rows;
};

const listInvimaCategories = async (pool) => {
  const { rows } = await pool.query(`
    SELECT
      COALESCE(NULLIF(categoria_slug, ''), 'sin-categoria') AS slug,
      MIN(COALESCE(NULLIF(categoria_canonica, ''), 'Sin categoria')) AS categoria,
      COUNT(*)::int AS total
    FROM invima_api_catalog
    GROUP BY 1
    ORDER BY total DESC, categoria ASC;
  `);
  return rows;
};

const listInvimaFilterOptions = async (pool) => {
  const [estadosResult, modalidadesResult, gruposResult] = await Promise.all([
    pool.query(`
      SELECT estado_registro AS value, COUNT(*)::int AS total
      FROM invima_api_catalog
      WHERE estado_registro IS NOT NULL AND estado_registro <> ''
      GROUP BY estado_registro
      ORDER BY total DESC, estado_registro ASC
      LIMIT 200;
    `),
    pool.query(`
      SELECT modalidad AS value, COUNT(*)::int AS total
      FROM invima_api_catalog
      WHERE modalidad IS NOT NULL AND modalidad <> ''
      GROUP BY modalidad
      ORDER BY total DESC, modalidad ASC
      LIMIT 200;
    `),
    pool.query(`
      SELECT grupo AS value, COUNT(*)::int AS total
      FROM invima_api_catalog
      WHERE grupo IS NOT NULL AND grupo <> ''
      GROUP BY grupo
      ORDER BY total DESC, grupo ASC
      LIMIT 200;
    `)
  ]);

  return {
    estados: estadosResult.rows,
    modalidades: modalidadesResult.rows,
    grupos: gruposResult.rows
  };
};

const listInvimaDatasets = () =>
  Object.values(INVIMA_DATASETS).map((dataset) => ({
    key: dataset.key,
    id: dataset.id,
    name: dataset.name,
    defaultCategory: dataset.defaultCategory
  }));

const getSyncDatasetKeys = (datasets) => {
  const requested = parseListParam(datasets);
  return requested.length ? requested.filter((key) => invimaAdapters[key]) : adapterKeys;
};

const getInvimaSyncStats = async (pool, options = {}) => {
  const selectedKeys = parseListParam(options.datasets).filter((key) => invimaAdapters[key]);

  const aggregateSql = `
    SELECT
      COUNT(*)::bigint AS catalog_rows,
      COUNT(DISTINCT categoria_slug)::int AS categories,
      COUNT(DISTINCT source_dataset_key)::int AS datasets_with_data,
      MAX(updated_at) AS max_updated_at,
      COUNT(*) FILTER (
        WHERE
          (COALESCE(estado_registro, '') ILIKE '%vigente%' OR COALESCE(vigencia, '') ILIKE '%vigente%')
          AND COALESCE(estado_registro, '') NOT ILIKE '%no vigente%'
          AND COALESCE(vigencia, '') NOT ILIKE '%no vigente%'
      )::bigint AS vigentes,
      COUNT(*) FILTER (
        WHERE COALESCE(estado_registro, '') ILIKE '%vencido%' OR COALESCE(vigencia, '') ILIKE '%vencido%'
      )::bigint AS vencidos
    FROM invima_api_catalog;
  `;

  const rawSql = `SELECT COUNT(*)::bigint AS raw_rows FROM invima_api_raw;`;

  const byDatasetSql = selectedKeys.length
    ? `
      SELECT source_dataset_key AS key, COUNT(*)::bigint AS total
      FROM invima_api_catalog
      WHERE source_dataset_key = ANY($1::text[])
      GROUP BY 1
      ORDER BY total DESC;
    `
    : `
      SELECT source_dataset_key AS key, COUNT(*)::bigint AS total
      FROM invima_api_catalog
      GROUP BY 1
      ORDER BY total DESC;
    `;

  const byDatasetParams = selectedKeys.length ? [selectedKeys] : [];

  const [aggregateResult, rawResult, byDatasetResult] = await Promise.all([
    pool.query(aggregateSql),
    pool.query(rawSql),
    pool.query(byDatasetSql, byDatasetParams)
  ]);

  const aggregate = aggregateResult.rows[0] || {};
  const datasetMap = new Map(
    byDatasetResult.rows.map((row) => [String(row.key), Number(row.total || 0)])
  );

  const configuredDatasets = listInvimaDatasets();
  const datasetRows = configuredDatasets
    .filter((dataset) => !selectedKeys.length || selectedKeys.includes(dataset.key))
    .map((dataset) => ({
      key: dataset.key,
      id: dataset.id,
      name: dataset.name,
      localRows: datasetMap.get(dataset.key) || 0,
      selected: selectedKeys.length ? selectedKeys.includes(dataset.key) : true
    }));

  return {
    at: new Date().toISOString(),
    totals: {
      catalogRows: Number(aggregate.catalog_rows || 0),
      rawRows: Number(rawResult.rows[0]?.raw_rows || 0),
      categories: Number(aggregate.categories || 0),
      datasetsWithData: Number(aggregate.datasets_with_data || 0),
      vigentes: Number(aggregate.vigentes || 0),
      vencidos: Number(aggregate.vencidos || 0),
      maxUpdatedAt: aggregate.max_updated_at || null
    },
    datasets: datasetRows
  };
};

const toCsv = (rows) => {
  if (!rows.length) return '';

  const headers = Object.keys(rows[0]);
  const escapeCsvValue = (value) => {
    if (value === null || value === undefined) return '';
    const text = String(value);
    if (text.includes('"') || text.includes(',') || text.includes('\n')) {
      return `"${text.replace(/"/g, '""')}"`;
    }
    return text;
  };

  const lines = [headers.join(',')];
  for (const row of rows) {
    lines.push(headers.map((header) => escapeCsvValue(row[header])).join(','));
  }
  return lines.join('\n');
};

module.exports = {
  ensureInvimaTables,
  syncInvimaDatasets,
  getInvimaCatalog,
  getInvimaCatalogForExport,
  getInvimaCatalogForExportChunk,
  listInvimaCategories,
  listInvimaFilterOptions,
  listInvimaDatasets,
  getInvimaSyncStats,
  toCsv,
  parseCatalogFilters,
  parseListParam,
  getSyncDatasetKeys,
  clampInt,
  normalizeCategory,
  slugify
};