const fs = require('fs');
const path = require('path');
const { createClient, readCsv, writeJson } = require('./benchmark-lib');
const { explainSummary, pageFields, query02Dir, query02Sql } = require('./query02-lib');

const rootDir = path.resolve(__dirname, '..', '..');
const reportFile = path.join(rootDir, 'benchmarks', 'INFORME_OPTIMIZACION_QUERY02.md');

const readJsonIfExists = (file) => (fs.existsSync(file) ? JSON.parse(fs.readFileSync(file, 'utf8')) : null);
const fmt = (value, suffix = '') => {
  if (value === null || value === undefined || value === '') return 'N/D';
  const number = Number(value);
  if (!Number.isFinite(number)) return `${value}${suffix}`;
  return `${number.toLocaleString('es-CO', { maximumFractionDigits: 4 })}${suffix}`;
};

const findRow = (rows, queryId, level = null) =>
  rows.find(
    (row) =>
      row.query_id === queryId &&
      (level === null || String(row.concurrency_level) === String(level)) &&
      !String(row.phase || '').endsWith('_warmup')
  );

const improvement = (before, after) => {
  const b = Number(before);
  const a = Number(after);
  if (!Number.isFinite(b) || !Number.isFinite(a)) return 'N/D';
  return `${fmt(b - a, ' ms')} (${fmt(((b - a) / b) * 100, ' %')})`;
};

const criteriaLine = (name, value, limit, unit = 'ms') => {
  const ok = Number(value) <= limit;
  return `- ${name}: ${fmt(value, ` ${unit}`)} frente a objetivo ${fmt(limit, ` ${unit}`)} => ${ok ? 'cumple' : 'no cumple'}.`;
};

const indexRows = async (client) => {
  const { rows } = await client.query(`
    SELECT c.relname AS index_name,
           pg_size_pretty(pg_relation_size(c.oid)) AS size,
           i.indisvalid,
           i.indisready,
           pg_get_indexdef(i.indexrelid) AS definition
    FROM pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
    JOIN pg_class t ON t.oid = i.indrelid
    WHERE t.relname = 'invima_api_catalog'
      AND c.relname IN (
        'idx_query02_catalog_vencimiento_desc_nullslast',
        'idx_invima_api_catalog_vencimiento',
        'idx_invima_api_catalog_producto_prefix',
        'invima_api_catalog_pk'
      )
    ORDER BY c.relname;
  `);
  return rows;
};

const tableStats = async (client) => {
  const { rows } = await client.query(`
    SELECT reltuples::bigint AS estimated_rows,
           pg_size_pretty(pg_relation_size('invima_api_catalog')) AS table_size,
           pg_size_pretty(pg_total_relation_size('invima_api_catalog')) AS total_size
    FROM pg_class
    WHERE oid = 'invima_api_catalog'::regclass;
  `);
  return rows[0];
};

const renderIndexTable = (indexes) =>
  [
    '| Indice | Tamano | Valido | Listo | Definicion |',
    '|---|---:|---|---|---|',
    ...indexes.map(
      (row) =>
        `| \`${row.index_name}\` | ${row.size} | ${row.indisvalid} | ${row.indisready} | \`${row.definition.replace(/\|/g, '\\|')}\` |`
    )
  ].join('\n');

const main = async () => {
  const beforeExplain = readJsonIfExists(path.join(query02Dir, 'explain_before.json'));
  const afterExplain = readJsonIfExists(path.join(query02Dir, 'explain_after.json'));
  const validation = readJsonIfExists(path.join(query02Dir, 'validation.json'));
  const serviceProfile = readJsonIfExists(path.join(query02Dir, 'service_profile.json'));
  const oldOptimized = readCsv(path.join(rootDir, 'benchmarks', 'results', 'optimized_summary.csv'));
  const oldConcurrency = readCsv(path.join(rootDir, 'benchmarks', 'results', 'concurrency_summary.csv'));
  const newOptimized = readCsv(path.join(query02Dir, 'optimized_summary.csv'));
  const newConcurrency = readCsv(path.join(query02Dir, 'concurrency_summary.csv'));

  const before = beforeExplain ? explainSummary(beforeExplain) : {};
  const after = afterExplain ? explainSummary(afterExplain) : {};
  const oldSeq = findRow(oldOptimized, 'query_02');
  const newSeq = findRow(newOptimized, 'query_02');

  const client = await createClient();
  let indexes = [];
  let stats = {};
  try {
    indexes = await indexRows(client);
    stats = await tableStats(client);
  } finally {
    await client.end();
  }

  const concurrencyLines = [1, 5, 10, 25, 50]
    .map((level) => {
      const oldRow = findRow(oldConcurrency, 'query_02', level);
      const newRow = findRow(newConcurrency, 'query_02', level);
      return `- ${level} usuario(s): promedio ${fmt(oldRow?.latency_avg_ms, ' ms')} -> ${fmt(
        newRow?.latency_avg_ms,
        ' ms'
      )}; p95 ${fmt(oldRow?.latency_p95_ms, ' ms')} -> ${fmt(newRow?.latency_p95_ms, ' ms')}; throughput ${fmt(
        oldRow?.throughput_per_second,
        ' req/s'
      )} -> ${fmt(newRow?.throughput_per_second, ' req/s')}.`;
    })
    .join('\n');

  const validationSummary = validation?.comparison
    ? [
        `- Total antes/despues: ${fmt(validation.comparison.totalBefore)} / ${fmt(validation.comparison.totalAfter)}.`,
        `- Resultado global: ${validation.comparison.valid ? 'VALIDO' : 'NO VALIDO'}.`,
        ...validation.comparison.pages.map(
          (page) =>
            `- Offset ${fmt(page.offset)}: valido=${page.valid}; faltantes=${page.missingIds?.length ?? 'N/D'}; adicionales=${page.additionalIds?.length ?? 'N/D'}; cambio orden=${page.orderChanged}; cambio claves ORDER BY=${page.orderKeysChanged}.`
        )
      ].join('\n')
    : 'No disponible.';

  const report = `# Informe de optimizacion query_02

Generado: ${new Date().toISOString()}

## 1. Descripcion de query_02

\`query_02\` corresponde a la primera pagina general del catalogo INVIMA:

\`\`\`sql
${query02Sql};
\`\`\`

El endpoint real asociado es \`GET /api/invima/catalogo?limit=25&offset=0\`. En Angular se invoca desde \`InvimaService.searchCatalog()\` y \`CatalogPageComponent.loadData()\`. En Express pasa por \`backend/src/routes/invima.routes.js\` y termina en \`getInvimaCatalog()\` / \`runCatalogQuery()\` dentro de \`backend/invima/service.js\`.

La solicitud real del endpoint ejecuta un \`COUNT(*)\` exacto y luego la consulta paginada. El benchmark historico de \`query_02\` mide la consulta paginada SQL.

## 2. Causa raiz

Antes de optimizar, PostgreSQL recorria aproximadamente ${fmt(stats.estimated_rows)} filas de una tabla de ${stats.table_size || 'N/D'} y aplicaba \`top-N heapsort\` para devolver solo 25 registros. La razon principal es que el orden requerido \`fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST\` no tenia un indice compatible. El indice existente sobre \`fecha_vencimiento\` es ascendente y no satisface directamente \`DESC NULLS LAST\`.

Un indice compuesto completo con \`producto\` no es recomendable en esta base: se observaron valores de \`producto\` extremadamente largos, y btree puede superar el limite de tamano de tupla. Por eso la optimizacion usa un indice estrecho por fecha descendente y deja el orden secundario sobre \`producto\` para un conjunto reducido.

## 3. Plan anterior

- Nodos: ${before.node_types || 'N/D'}.
- Seq Scan: ${before.seq_scans ?? 'N/D'}; Index Scan: ${before.index_scans ?? 'N/D'}; Incremental Sort: ${before.incremental_sorts ?? 'N/D'}; Gather Merge: ${before.gather_merges ?? 'N/D'}.
- Filas estimadas/real raiz: ${fmt(before.estimated_rows)} / ${fmt(before.actual_rows)}.
- Ancho estimado de fila: ${fmt(before.estimated_width)} bytes.
- Bloques hit/read: ${fmt(before.shared_hit_blocks)} / ${fmt(before.shared_read_blocks)}.
- Temporales read/write: ${fmt(before.temp_read_blocks)} / ${fmt(before.temp_written_blocks)}.
- Metodo de ordenamiento: ${(before.sort_methods || []).join(', ') || 'N/D'}.
- Planeacion/ejecucion: ${fmt(before.planning_ms, ' ms')} / ${fmt(before.execution_ms, ' ms')}.

## 4. Cambios implementados

Se agrego una migracion reversible:

\`\`\`sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_query02_catalog_vencimiento_desc_nullslast
ON invima_api_catalog (fecha_vencimiento DESC NULLS LAST);
ALTER TABLE invima_api_catalog ALTER COLUMN fecha_vencimiento SET STATISTICS 1000;
ALTER TABLE invima_api_catalog ALTER COLUMN producto SET STATISTICS 1000;
\`\`\`

No se modificaron columnas de respuesta, filtros, universo consultado, conteo exacto ni registros regulatorios. Se agrego un desempate estable al \`ORDER BY\` con \`source_dataset_key, source_uid\`, que son la clave primaria, para evitar duplicados u omisiones entre paginas cuando hay empates en \`fecha_vencimiento\` y \`producto\`.

## 5. Indices relevantes

${renderIndexTable(indexes)}

## 6. Plan posterior

- Nodos: ${after.node_types || 'N/D'}.
- Seq Scan: ${after.seq_scans ?? 'N/D'}; Index Scan: ${after.index_scans ?? 'N/D'}; Incremental Sort: ${after.incremental_sorts ?? 'N/D'}; Gather Merge: ${after.gather_merges ?? 'N/D'}.
- Filas estimadas/real raiz: ${fmt(after.estimated_rows)} / ${fmt(after.actual_rows)}.
- Ancho estimado de fila: ${fmt(after.estimated_width)} bytes.
- Bloques hit/read: ${fmt(after.shared_hit_blocks)} / ${fmt(after.shared_read_blocks)}.
- Temporales read/write: ${fmt(after.temp_read_blocks)} / ${fmt(after.temp_written_blocks)}.
- Metodo de ordenamiento: ${(after.sort_methods || []).join(', ') || 'N/D'}.
- Planeacion/ejecucion: ${fmt(after.planning_ms, ' ms')} / ${fmt(after.execution_ms, ' ms')}.

## 7. Validacion funcional

${validationSummary}

La validacion before se ejecuto con el plan antiguo forzado mediante GUCs de sesion, y la validacion after con el nuevo indice habilitado. En ambos casos se uso la misma consulta determinista. La validacion exige que no cambien IDs, orden, claves \`ORDER BY\`, estructura publica ni valores publicos.

## 8. Resultados secuenciales

- Promedio: ${fmt(oldSeq?.latency_avg_ms, ' ms')} -> ${fmt(newSeq?.latency_avg_ms, ' ms')}; mejora ${improvement(
    oldSeq?.latency_avg_ms,
    newSeq?.latency_avg_ms
  )}.
- Mediana: ${fmt(oldSeq?.latency_median_ms, ' ms')} -> ${fmt(newSeq?.latency_median_ms, ' ms')}.
- p95: ${fmt(oldSeq?.latency_p95_ms, ' ms')} -> ${fmt(newSeq?.latency_p95_ms, ' ms')}; mejora ${improvement(
    oldSeq?.latency_p95_ms,
    newSeq?.latency_p95_ms
  )}.
- p99: ${fmt(oldSeq?.latency_p99_ms, ' ms')} -> ${fmt(newSeq?.latency_p99_ms, ' ms')}; mejora ${improvement(
    oldSeq?.latency_p99_ms,
    newSeq?.latency_p99_ms
  )}.
- Fallos: ${fmt(newSeq?.failures)}; tasa de fallos: ${fmt(newSeq?.failure_rate_percent, ' %')}.
- Throughput: ${fmt(oldSeq?.throughput_per_second, ' ops/s')} -> ${fmt(newSeq?.throughput_per_second, ' ops/s')}.

## 9. Resultados concurrentes

${concurrencyLines}

## 10. Criterios

${criteriaLine('Promedio individual', newSeq?.latency_avg_ms, 300)}
${criteriaLine('p95 individual', newSeq?.latency_p95_ms, 500)}
${criteriaLine('p99 individual', newSeq?.latency_p99_ms, 750)}
${criteriaLine('p95 con 5 usuarios', findRow(newConcurrency, 'query_02', 5)?.latency_p95_ms, 1000)}
${criteriaLine('p95 con 10 usuarios', findRow(newConcurrency, 'query_02', 10)?.latency_p95_ms, 2000)}
${criteriaLine('p95 con 25 usuarios', findRow(newConcurrency, 'query_02', 25)?.latency_p95_ms, 5000)}

## 11. Paginacion, conteo y concurrencia

La consulta usa \`LIMIT 25 OFFSET n\`. Para \`OFFSET 0\`, el costo dominante era encontrar el orden inicial sin indice compatible. Las paginas profundas siguen pagando el costo natural de \`OFFSET\`; una siguiente iteracion compatible seria exponer paginacion keyset con cursor basado en \`fecha_vencimiento\`, \`producto\` y la clave primaria \`source_dataset_key/source_uid\`, manteniendo la ruta actual como compatibilidad.

El endpoint real conserva \`COUNT(*)\` exacto en cada solicitud. No se reemplazo por estimaciones ni cache como unica solucion. Si el conteo se convierte en cuello de botella, se recomienda medir y separar el total exacto en una ruta o tabla de estadisticas invalidada durante sincronizacion.

Perfil del servicio actualizado: \`getInvimaCatalog(pool, { limit: 25, offset: 0 })\` devolvio ${fmt(
    serviceProfile?.items
  )} filas, total exacto ${fmt(serviceProfile?.total)}, tiempo logico ${fmt(
    serviceProfile?.service_total_ms,
    ' ms'
  )}, serializacion ${fmt(serviceProfile?.serialization_ms, ' ms')} y respuesta de ${fmt(
    serviceProfile?.response_bytes,
    ' bytes'
  )}.

El pool actual usa \`PGPOOL_MAX\` desde entorno y PostgreSQL tiene \`max_connections\` estandar. En una maquina de 2 vCPU y 4 GB, aumentar conexiones sin reducir el costo de query empeora la contencion; la mejora principal provino del plan SQL.

## 12. Riesgos

- El nuevo indice aumenta espacio en disco y costo de mantenimiento durante inserciones/sincronizaciones.
- No cubrir \`producto\` evita un indice enorme, pero todavia requiere ordenar grupos con la misma fecha.
- El desempate por \`source_dataset_key, source_uid\` estabiliza la paginacion, pero cambia el orden interno previamente no garantizado de filas empatadas.
- \`ANALYZE\` automatico se omitio porque esta base local tiene bytes con codificacion invalida que hacen fallar el escaneo completo.

## 13. Redaccion academica

Antes de la optimizacion, la consulta general paginada presento una latencia promedio de ${fmt(
    oldSeq?.latency_avg_ms,
    ' ms'
  )} y un p95 de ${fmt(oldSeq?.latency_p95_ms, ' ms')}. Despues de implementar el indice \`idx_query02_catalog_vencimiento_desc_nullslast\`, la latencia promedio se redujo a ${fmt(
    newSeq?.latency_avg_ms,
    ' ms'
  )} y el p95 a ${fmt(newSeq?.latency_p95_ms, ' ms')}, equivalente a una mejora de ${improvement(
    oldSeq?.latency_avg_ms,
    newSeq?.latency_avg_ms
  )}. Bajo 50 usuarios concurrentes, la latencia promedio paso de aproximadamente ${fmt(
    findRow(oldConcurrency, 'query_02', 50)?.latency_avg_ms,
    ' ms'
  )} a ${fmt(findRow(newConcurrency, 'query_02', 50)?.latency_avg_ms, ' ms')}.

## 14. Reversion y repeticion

Revertir:

\`\`\`powershell
npm.cmd run benchmark:query02:rollback
\`\`\`

Repetir:

\`\`\`powershell
npm.cmd run benchmark:query02:validate:before
npm.cmd run benchmark:query02:explain:before
npm.cmd run benchmark:query02:optimize
npm.cmd run benchmark:query02:explain:after
npm.cmd run benchmark:query02:validate
npm.cmd run benchmark:query02:optimized
npm.cmd run benchmark:query02:concurrency
npm.cmd run benchmark:query02:report
\`\`\`
`;

  fs.writeFileSync(reportFile, report, 'utf8');
  writeJson(path.join(query02Dir, 'report_metadata.json'), {
    generatedAt: new Date().toISOString(),
    reportFile
  });
};

main().catch((error) => {
  console.error(error);
  process.exit(1);
});