'use strict';

const { db } = require('../utils/dbName');

function qname(tableName) {
  return `${db()}${tableName}`;
}

function tableObjectIdExpr(tableName) {
  return `OBJECT_ID('${qname(tableName)}', 'U')`;
}

function columnLengthExpr(tableName, columnName) {
  return `COL_LENGTH('${qname(tableName)}', '${columnName}')`;
}

async function inspectTables(pool, tableSpecs = {}) {
  const selects = [];
  const map = [];
  for (const [tableName, columns] of Object.entries(tableSpecs)) {
    selects.push(`${tableObjectIdExpr(tableName)} AS [table_${tableName}]`);
    map.push({ kind: 'table', tableName, alias: `table_${tableName}` });
    for (const columnName of columns || []) {
      const alias = `col_${tableName}_${columnName}`.replace(/[^A-Za-z0-9_]/g, '_');
      selects.push(`${columnLengthExpr(tableName, columnName)} AS [${alias}]`);
      map.push({ kind: 'column', tableName, columnName, alias });
    }
  }
  if (!selects.length) return { tables: {}, missing: [] };
  const result = await pool.request().query(`SELECT ${selects.join(',\n')}`);
  const row = result.recordset[0] || {};
  const tables = {};
  const missing = [];
  for (const item of map) {
    if (item.kind === 'table') {
      tables[item.tableName] = tables[item.tableName] || { exists: false, columns: {}, missingColumns: [] };
      tables[item.tableName].exists = Boolean(row[item.alias]);
      if (!tables[item.tableName].exists) missing.push({ table: item.tableName, type: 'table' });
    } else {
      tables[item.tableName] = tables[item.tableName] || { exists: false, columns: {}, missingColumns: [] };
      const exists = Boolean(row[item.alias]);
      tables[item.tableName].columns[item.columnName] = exists;
      if (!exists) {
        tables[item.tableName].missingColumns.push(item.columnName);
        missing.push({ table: item.tableName, column: item.columnName, type: 'column' });
      }
    }
  }
  return { tables, missing };
}

function requireColumns(introspection, tableName, columns = []) {
  const table = introspection.tables?.[tableName];
  if (!table?.exists) return [`${tableName} no existe`];
  return columns
    .filter((columnName) => !table.columns?.[columnName])
    .map((columnName) => `${tableName}.${columnName} no existe`);
}

async function detectSoftRestaurantSchema(pool) {
  const specs = {
    empresas: ['idempresa', 'nombre', 'WorkspaceId'],
    productos: ['idproducto', 'descripcion', 'idgrupo', 'WorkspaceId'],
    productosdetalle: ['idproducto', 'idempresa', 'precio', 'bloqueado'],
    cheques: ['folio', 'fecha', 'idempresa', 'idturno', 'WorkspaceId'],
    cheqdet: ['foliodet', 'idproducto', 'idproductocompuesto', 'modificador', 'productocompuestoprincipal'],
    chequespagos: ['folio', 'idformadepago', 'importe', 'WorkspaceId'],
    tempcheques: ['folio', 'fecha', 'idempresa', 'idturno', 'WorkspaceId'],
    tempcheqdet: ['foliodet', 'idproducto', 'idproductocompuesto', 'modificador', 'productocompuestoprincipal'],
    tempchequespagos: ['folio', 'idformadepago', 'importe', 'WorkspaceId'],
    almacen: ['idalmacen', 'nombre', 'idempresa'],
    movtosalmacen: ['fecha', 'movto', 'idinsumospresentaciones', 'cantidad', 'idalmacen'],
    movsinv: ['fecha', 'movto', 'idinsumo', 'cantidad', 'idalmacen'],
    desperdicios: ['cantidad', 'idproducto', 'idinsumo', 'fecha'],
    paquetes: ['idproducto', 'idproductocompuesto', 'idproductopadre', 'idproductohijo', 'cantidad'],
    gruposmodificadores: ['idgruposmodificadores', 'descripcion', 'WorkspaceId'],
    gruposmodificadoresproductos: ['idproducto', 'idgruposmodificadores', 'incluidos', 'prioridad', 'forzarcaptura', 'maximomodificadores', 'idempresa', 'captura_incluido', 'WorkspaceId'],
    modificadores: ['idproducto', 'idmodificador', 'precio', 'idgruposmodificadores', 'idempresa', 'WorkspaceId'],
  };
  const result = await inspectTables(pool, specs);
  const compoundSupport = await detectCompoundSupport(pool, result);
  return {
    engine: 'sqlserver',
    source_system: 'softrestaurant',
    schema_version: 'softrestaurant11-detected',
    compound_support: compoundSupport,
    ...result,
  };
}

function detectedColumn(introspection, tableName, columnName) {
  return Boolean(introspection.tables?.[tableName]?.columns?.[columnName]);
}

function compoundColumnsFor(introspection, tableName) {
  return {
    idproductocompuesto: detectedColumn(introspection, tableName, 'idproductocompuesto'),
    modificador: detectedColumn(introspection, tableName, 'modificador'),
    productocompuestoprincipal: detectedColumn(introspection, tableName, 'productocompuestoprincipal'),
  };
}

function compoundColumnsComplete(columns) {
  return Boolean(columns.idproductocompuesto && columns.modificador && columns.productocompuestoprincipal);
}

async function scalar(pool, sqlText) {
  const result = await pool.request().query(sqlText);
  const row = result.recordset?.[0] || {};
  const key = Object.keys(row)[0];
  return Number(row[key] || 0);
}

async function detectCompoundSupport(pool, introspection) {
  const cheqdet = compoundColumnsFor(introspection, 'cheqdet');
  const tempcheqdet = compoundColumnsFor(introspection, 'tempcheqdet');
  const warnings = [];
  const counts = {
    compound_lines: 0,
    modifiers: 0,
    temp_compound_lines: 0,
    temp_modifiers: 0,
  };

  if (compoundColumnsComplete(cheqdet)) {
    counts.compound_lines = await scalar(pool, `
      SELECT COUNT(*) AS count
      FROM (
        SELECT TOP 5000 idproductocompuesto
        FROM ${qname('cheqdet')}
        ORDER BY foliodet DESC
      ) recent
      WHERE NULLIF(LTRIM(RTRIM(CAST(recent.idproductocompuesto AS nvarchar(80)))), '') IS NOT NULL
    `).catch(() => 0);
    counts.modifiers = await scalar(pool, `
      SELECT COUNT(*) AS count
      FROM (
        SELECT TOP 5000 modificador
        FROM ${qname('cheqdet')}
        ORDER BY foliodet DESC
      ) recent
      WHERE ISNULL(recent.modificador, 0) = 1
    `).catch(() => 0);
  } else {
    warnings.push('cheqdet columnas compuestas incompletas');
  }

  if (introspection.tables?.tempcheqdet?.exists) {
    if (compoundColumnsComplete(tempcheqdet)) {
      counts.temp_compound_lines = await scalar(pool, `
        SELECT COUNT(*) AS count
        FROM (
          SELECT TOP 5000 idproductocompuesto
          FROM ${qname('tempcheqdet')}
          ORDER BY foliodet DESC
        ) recent
        WHERE NULLIF(LTRIM(RTRIM(CAST(recent.idproductocompuesto AS nvarchar(80)))), '') IS NOT NULL
      `).catch(() => 0);
      counts.temp_modifiers = await scalar(pool, `
        SELECT COUNT(*) AS count
        FROM (
          SELECT TOP 5000 modificador
          FROM ${qname('tempcheqdet')}
          ORDER BY foliodet DESC
        ) recent
        WHERE ISNULL(recent.modificador, 0) = 1
      `).catch(() => 0);
    } else {
      warnings.push('tempcheqdet columnas compuestas incompletas');
    }
  }

  const paquetes = await inspectPackages(pool, introspection);
  warnings.push(...paquetes.warnings);
  const supported = compoundColumnsComplete(cheqdet) || compoundColumnsComplete(tempcheqdet);
  return {
    supported,
    status: supported ? 'soportado' : 'columnas_faltantes',
    columns: { cheqdet, tempcheqdet },
    counts,
    paquetes,
    warnings,
  };
}

async function inspectPackages(pool, introspection) {
  const exists = Boolean(introspection.tables?.paquetes?.exists);
  const warnings = [];
  if (!exists) return { exists: false, optional_reference_only: true, orphan_count: 0, warnings };
  warnings.push('paquetes detectada: se usa solo como referencia, no como fuente primaria de compuestos vendidos');
  let orphanCount = 0;
  if (detectedColumn(introspection, 'paquetes', 'idproducto') && introspection.tables?.productos?.exists) {
    orphanCount = await scalar(pool, `
      SELECT COUNT(*) AS count
      FROM ${qname('paquetes')} pa
      LEFT JOIN ${qname('productos')} p ON p.idproducto = pa.idproducto
      WHERE pa.idproducto IS NOT NULL AND p.idproducto IS NULL
    `).catch(() => 0);
    if (orphanCount > 0) warnings.push(`paquetes contiene ${orphanCount} referencias huerfanas contra productos`);
  }
  return { exists: true, optional_reference_only: true, orphan_count: orphanCount, warnings };
}

module.exports = {
  qname,
  inspectTables,
  requireColumns,
  detectSoftRestaurantSchema,
  detectCompoundSupport,
};
