'use strict';

const ExcelJS = require('exceljs');
const JSZip = require('jszip');

const TABLE_KEYS = {
  grupoformasdepago: ['idgrupoformadepago'],
  formasdepago: ['idformadepago'],
  tipoproveedores: ['idtipoproveedor'],
  proveedores: ['idproveedor'],
  almacen: ['idalmacen'],
  grupos: ['idgrupo'],
  subgrupos: ['idsubgrupo'],
  grupossubgrupos: ['idgrupo', 'idsubgrupo'],
  subgruposproductos: ['idsubgrupo', 'idproducto'],
  gruposi: ['idgruposi'],
  insumos: ['idinsumo'],
  insumosdetalle: ['idinsumo', 'idempresa'],
  insumospresentaciones: ['idinsumospresentaciones'],
  insumospresentacionesdetalle: ['idinsumospresentaciones', 'idempresa'],
  insumoscostoproveedor: ['idproveedor', 'idpresentacion', 'idempresa'],
  stockinsumos: ['idinsumo', 'idinsumospresentaciones', 'idalmacen', 'idempresa'],
  productos: ['idproducto'],
  productosdetalle: ['idproducto', 'idempresa'],
  productosdelivery: ['idproducto', 'idapp'],
  productosmonitores: ['idproducto', 'idmonitor'],
  costos: ['idproducto', 'idinsumo', 'idempresa'],
  recetasalmacenes: ['idproducto', 'idarearestaurant', 'idalmacen', 'idempresa', 'idinsumo'],
  gruposmodificadores: ['idgruposmodificadores'],
  gruposmodificadoresproductos: ['id'],
  modificadores: ['idproducto', 'idmodificador', 'idgruposmodificadores', 'idempresa'],
};

const TABLE_ORDER = [
  'grupoformasdepago', 'formasdepago',
  'tipoproveedores', 'proveedores', 'almacen',
  'grupos', 'subgrupos', 'grupossubgrupos', 'subgruposproductos',
  'gruposi', 'insumos', 'insumospresentaciones',
  'insumosdetalle', 'insumospresentacionesdetalle', 'insumoscostoproveedor', 'stockinsumos',
  'productos', 'productosdetalle', 'productosdelivery', 'productosmonitores',
  'costos', 'recetasalmacenes',
  'gruposmodificadores', 'gruposmodificadoresproductos', 'modificadores',
];

const SENSITIVE_TABLES = new Set([
  'clientes',
  'direccionesdomicilio',
  'huellaclientes',
  'huellameseros',
]);

const LARGE_COLUMNS = new Set([
  'imagen',
  'imagen_menu',
  'imagenmenuelectronico',
  'imagenbackoffice',
  'fotografia',
  'huella',
  'huella2',
  'pic',
]);

const TEMPLATE_COLUMNS = {
  grupos: ['idgrupo', 'descripcion', 'clasificacion', 'prioridad', 'color', 'colorletra', 'prioridadimpresion', 'cambiacolorcuenta', 'colorcuenta', 'colorletracuenta', 'solicitaautorizacion', 'extmenu', 'porcentajepropina'],
  subgrupos: ['idsubgrupo', 'descripcion', 'id_etiqueta', 'workspaceid', 'servicecode'],
  grupossubgrupos: ['idgrupo', 'idsubgrupo'],
  subgruposproductos: ['idsubgrupo', 'idproducto'],
  gruposi: ['idgruposi', 'descripcion', 'idgruposiclasificacion', 'idcuentacontable', 'idtipopedido', 'prioridad', 'workspaceid'],
  proveedores: ['idproveedor', 'nombre', 'razonsocial', 'direccion', 'codigopostal', 'telefono', 'fax', 'email', 'rfc', 'credito', 'usarcostosasignados', 'usarenpoliza', 'idtipoproveedor', 'idcuentacontable', 'nombrebanco', 'nocuenta', 'cuentaclave', 'estatus'],
  tipoproveedores: ['idtipoproveedor', 'descripcion'],
  insumos: ['idinsumo', 'descripcion', 'idgruposi', 'unidad', 'elaborado', 'rendimientoelaborado', 'minutosalerta', 'minutospreparacion', 'workspaceid'],
  insumospresentaciones: ['idinsumospresentaciones', 'descripcion', 'idinsumo', 'idgruposi', 'rendimiento', 'unidad', 'workspaceid'],
  insumosdetalle: ['idinsumo', 'idempresa', 'inventariable', 'costo', 'costopromedio', 'impuesto1', 'impuesto2', 'impuesto3', 'costoconimpuestos', 'merma', 'descargar', 'usarbasculainv', 'alertaexistencias', 'estatus', 'costoestandar', 'idarea'],
  insumospresentacionesdetalle: ['idinsumospresentaciones', 'idempresa', 'costo', 'costopromedio', 'idproveedor', 'impuesto1', 'impuesto2', 'impuesto3', 'indicadorimpuesto', 'stockminimogeneral', 'stockmaximogeneral', 'estatus', 'descargar', 'ubicacion', 'usarbasculainv', 'costoestandar'],
  insumoscostoproveedor: ['idproveedor', 'idpresentacion', 'costo', 'idempresa'],
  stockinsumos: ['idinsumo', 'idinsumospresentaciones', 'idalmacen', 'stockminimo', 'stockideal', 'stockmaximo', 'idempresa'],
  productos: ['idproducto', 'descripcion', 'idgrupo', 'nombrecorto', 'plu', 'nofacturable', 'comentario', 'usarcomedor', 'usardomicilio', 'usarrapido', 'usarcedis', 'idinsumospresentaciones', 'descripcionmenuelectronico', 'usarmenuelectronico', 'extmenu', 'descripcion_detalle', 'calorias', 'visible_menu', 'capturar_pendientes', 'id_etiqueta', 'id_etiqueta_descripcion', 'idprodserv_sat', 'usarvectorplus', 'workspaceid', 'imagenme_modified', 'monitoreo', 'prioridadimpresion'],
  productosdetalle: ['idproducto', 'idempresa', 'precio', 'impuesto1', 'impuesto2', 'impuesto3', 'preciosinimpuestos', 'bloqueado', 'precioabierto', 'canjeablepuntos', 'preciopuntos', 'puntoscanje', 'puntosextras', 'lunesinicio', 'lunesfin', 'preciolunes', 'martesinicio', 'martesfin', 'preciomartes', 'miercolesinicio', 'miercolesfin', 'preciomiercoles', 'juevesinicio', 'juevesfin', 'preciojueves', 'viernesinicio', 'viernesfin', 'precioviernes', 'sabadoinicio', 'sabadofin', 'preciosabado', 'domingoinicio', 'domingofin', 'preciodomingo', 'excentoimpuestos', 'descargar', 'idarea', 'idunidad', 'usa_bascula'],
  productosdelivery: ['idproducto', 'precio', 'descripcion', 'nombre', 'idapp', 'visiblemenu'],
  productosmonitores: ['idproducto', 'idmonitor'],
  costos: ['idproducto', 'idinsumo', 'cantidad', 'idempresa', 'workspaceid'],
  recetasalmacenes: ['idproducto', 'idarearestaurant', 'idalmacen', 'idempresa', 'idinsumo'],
  gruposmodificadores: ['idgruposmodificadores', 'descripcion', 'id_etiqueta', 'workspaceid'],
  gruposmodificadoresproductos: ['idproducto', 'idgruposmodificadores', 'incluidos', 'prioridad', 'forzarcaptura', 'maximomodificadores', 'idempresa', 'captura_incluido', 'id', 'workspaceid'],
  modificadores: ['idproducto', 'idmodificador', 'precio', 'idgruposmodificadores', 'idempresa', 'workspaceid'],
  formasdepago: ['idformadepago', 'descripcion', 'tipo', 'tipodecambio', 'solicitareferencia', 'prioridadboton', 'comision', 'visible', 'aceptapropina', 'subtipo', 'generapuntos', 'idfpagofiscal', 'pagoenlinea', 'tipotarjeta', 'nofacturable', 'idformapago_sat', 'workspaceid'],
};

function decodeXml(value) {
  return String(value || '')
    .replace(/<!\[CDATA\[([\s\S]*?)\]\]>/g, '$1')
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&amp;/g, '&')
    .replace(/&quot;/g, '"')
    .replace(/&apos;/g, "'")
    .trim();
}

function normalizeTableName(name) {
  return String(name || '')
    .toLowerCase()
    .replace(/\\/g, '/')
    .split('/')
    .pop()
    .replace(/\.xml$/i, '')
    .replace(/[^a-z0-9_]/g, '');
}

function parseNativeCatalogXml(xml, options = {}) {
  const rows = [];
  const omittedColumns = new Set();
  const blockRe = /<curtemp>([\s\S]*?)<\/curtemp>/gi;
  let block;
  while ((block = blockRe.exec(String(xml || '')))) {
    const row = {};
    const fieldRe = /<([A-Za-z0-9_]+)(?:\s[^>]*)?>([\s\S]*?)<\/\1>|<([A-Za-z0-9_]+)\s*\/\s*>/g;
    let field;
    while ((field = fieldRe.exec(block[1]))) {
      const column = String(field[1] || field[3]).toLowerCase();
      if (!column) continue;
      if (LARGE_COLUMNS.has(column)) {
        omittedColumns.add(column);
        continue;
      }
      row[column] = decodeXml(field[2] || '');
    }
    if (Object.keys(row).length) rows.push(row);
    if (options.limit && rows.length >= options.limit) break;
  }
  return { rows, omittedColumns: [...omittedColumns] };
}

function normalizeCellValue(value) {
  if (value === undefined || value === null) return null;
  if (typeof value === 'object' && value.text) value = value.text;
  const text = String(value).trim();
  return text === '' ? null : text;
}

function compactRows(rows, limit = 3) {
  return rows.slice(0, limit).map(row => {
    const sample = {};
    Object.entries(row).slice(0, 10).forEach(([key, value]) => {
      const text = value === null || value === undefined ? '' : String(value);
      sample[key] = text.length > 80 ? `[${text.length} chars]` : text;
    });
    return sample;
  });
}

function buildTablePayload(table, rows, source, omittedColumns = []) {
  const keys = TABLE_KEYS[table] || [];
  const missingKeys = keys.filter(key => !rows.some(row => normalizeCellValue(row[key]) !== null));
  const columns = [...new Set(rows.flatMap(row => Object.keys(row)))];
  const supported = Boolean(TABLE_KEYS[table]) && !SENSITIVE_TABLES.has(table);
  return {
    tabla: table,
    archivo: source,
    filas: supported && !missingKeys.length ? rows : [],
    total: rows.length,
    columnas: columns,
    llaves: keys,
    muestra: compactRows(rows),
    columnas_omitidas: omittedColumns,
    estado: supported && !missingKeys.length ? 'lista' : 'omitida',
    motivo: supported ? (missingKeys.length ? `Faltan llaves: ${missingKeys.join(', ')}` : '') : 'Tabla no habilitada para importacion automatica',
  };
}

async function previewNativeCatalogZip(buffer) {
  const zip = await JSZip.loadAsync(buffer);
  const tables = [];
  for (const entry of Object.values(zip.files)) {
    if (entry.dir || !/\.xml$/i.test(entry.name)) continue;
    const table = normalizeTableName(entry.name);
    const xml = await entry.async('string');
    const parsed = parseNativeCatalogXml(xml);
    if (SENSITIVE_TABLES.has(table)) {
      tables.push({
        tabla: table,
        archivo: entry.name,
        filas: [],
        total: parsed.rows.length,
        columnas: [...new Set(parsed.rows.flatMap(row => Object.keys(row)))],
        llaves: TABLE_KEYS[table] || [],
        muestra: compactRows(parsed.rows),
        columnas_omitidas: parsed.omittedColumns,
        estado: 'omitida',
        motivo: 'Tabla omitida por contener datos personales o biometria',
      });
      continue;
    }
    if (!TABLE_KEYS[table]) {
      if (parsed.rows.length) {
        tables.push(buildTablePayload(table, parsed.rows, entry.name, parsed.omittedColumns));
      }
      continue;
    }
    tables.push(buildTablePayload(table, parsed.rows, entry.name, parsed.omittedColumns));
  }
  return normalizePreviewTables(tables, 'softrestaurant-zip');
}

async function previewCatalogExcel(buffer) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(buffer);
  const tables = [];
  workbook.eachSheet(sheet => {
    const table = normalizeTableName(sheet.name);
    if (!TABLE_KEYS[table] || SENSITIVE_TABLES.has(table)) return;
    let headers = null;
    const rows = [];
    sheet.eachRow((row, rowNumber) => {
      const values = [];
      row.eachCell({ includeEmpty: true }, cell => values.push(normalizeCellValue(cell.value)));
      if (!headers) {
        headers = values.map(value => String(value || '').trim().toLowerCase()).filter(Boolean);
        return;
      }
      const item = {};
      headers.forEach((header, index) => {
        if (!header || LARGE_COLUMNS.has(header)) return;
        const value = normalizeCellValue(values[index]);
        if (value !== null) item[header] = value;
      });
      if (Object.keys(item).length) rows.push(item);
      if (rowNumber > 5000) throw new Error(`La hoja ${sheet.name} excede el limite de 5000 filas`);
    });
    tables.push(buildTablePayload(table, rows, sheet.name));
  });
  return normalizePreviewTables(tables, 'softrestaurant-excel');
}

function normalizePreviewTables(tables, source) {
  const ordered = [...tables].sort((a, b) => {
    const ai = TABLE_ORDER.indexOf(a.tabla);
    const bi = TABLE_ORDER.indexOf(b.tabla);
    return (ai === -1 ? 999 : ai) - (bi === -1 ? 999 : bi) || a.tabla.localeCompare(b.tabla);
  });
  const importables = ordered.filter(table => table.estado === 'lista' && table.total > 0);
  return {
    tipo: 'softrestaurant',
    fuente: source,
    tablas: ordered,
    total: importables.reduce((sum, table) => sum + table.total, 0),
    tablas_importables: importables.length,
    tablas_omitidas: ordered.filter(table => table.estado !== 'lista').length,
    requieren_revision: ordered.filter(table => table.estado !== 'lista').length,
  };
}

function quoteIdentifier(name) {
  return `[${String(name).replace(/]/g, ']]')}]`;
}

async function getTableColumns(pool, table) {
  const { sql } = require('../db');
  const result = await pool.request()
    .input('table', sql.NVarChar, table)
    .query(`
      SELECT LOWER(COLUMN_NAME) AS name
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = @table
    `);
  return new Set(result.recordset.map(row => row.name));
}

async function upsertRow(pool, table, keys, columns, row) {
  const data = {};
  for (const column of columns) {
    if (!Object.prototype.hasOwnProperty.call(row, column)) continue;
    data[column] = normalizeCellValue(row[column]);
  }
  if (!keys.every(key => normalizeCellValue(data[key]) !== null)) {
    return { ok: false, error: `Llave incompleta en ${table}` };
  }
  const writable = Object.keys(data);
  if (!writable.length) return { ok: false, error: `Fila vacia en ${table}` };

  const request = pool.request();
  writable.forEach((column, index) => request.input(`p${index}`, data[column]));
  const paramFor = column => `@p${writable.indexOf(column)}`;
  const where = keys.map(key => `${quoteIdentifier(key)} = ${paramFor(key)}`).join(' AND ');
  const updateColumns = writable.filter(column => !keys.includes(column));
  const insertColumns = writable.map(quoteIdentifier).join(', ');
  const insertValues = writable.map(paramFor).join(', ');

  let query = `IF EXISTS (SELECT 1 FROM ${quoteIdentifier(table)} WHERE ${where})\n`;
  if (updateColumns.length) {
    query += `UPDATE ${quoteIdentifier(table)} SET ${updateColumns.map(column => `${quoteIdentifier(column)} = ${paramFor(column)}`).join(', ')} WHERE ${where};\n`;
  } else {
    query += `SELECT 1;\n`;
  }
  query += `ELSE INSERT INTO ${quoteIdentifier(table)} (${insertColumns}) VALUES (${insertValues});`;
  await request.query(query);
  return { ok: true };
}

async function applyCatalogImport({ tablas = [] }) {
  if (!Array.isArray(tablas) || !tablas.length) throw new Error('tablas[] requerido');
  const { getPool } = require('../db');
  const pool = await getPool();
  const resultados = [];
  const ordered = [...tablas].sort((a, b) => {
    const ai = TABLE_ORDER.indexOf(a.tabla);
    const bi = TABLE_ORDER.indexOf(b.tabla);
    return (ai === -1 ? 999 : ai) - (bi === -1 ? 999 : bi);
  });

  for (const tablePayload of ordered) {
    const table = normalizeTableName(tablePayload.tabla);
    const rows = Array.isArray(tablePayload.filas) ? tablePayload.filas : [];
    if (!TABLE_KEYS[table] || SENSITIVE_TABLES.has(table) || !rows.length) {
      resultados.push({ tabla: table, ok: true, omitida: true, total: rows.length });
      continue;
    }
    try {
      const dbColumns = await getTableColumns(pool, table);
      if (!dbColumns.size) throw new Error(`Tabla ${table} no existe en la base actual`);
      const keys = TABLE_KEYS[table];
      const columns = [...new Set(rows.flatMap(row => Object.keys(row).map(key => key.toLowerCase())))]
        .filter(column => dbColumns.has(column) && !LARGE_COLUMNS.has(column));
      const missingKeys = keys.filter(key => !columns.includes(key));
      if (missingKeys.length) throw new Error(`Tabla ${table} sin llaves requeridas: ${missingKeys.join(', ')}`);

      let guardados = 0;
      const errores = [];
      for (const row of rows) {
        try {
          const normalized = {};
          Object.entries(row).forEach(([key, value]) => { normalized[key.toLowerCase()] = value; });
          const result = await upsertRow(pool, table, keys, columns, normalized);
          if (result.ok) guardados++;
          else errores.push(result.error);
        } catch (error) {
          errores.push(error.message);
        }
      }
      resultados.push({ tabla: table, ok: errores.length === 0, total: rows.length, guardados, errores: errores.slice(0, 10) });
    } catch (error) {
      resultados.push({ tabla: table, ok: false, total: rows.length, guardados: 0, errores: [error.message] });
    }
  }

  return {
    ok: resultados.every(item => item.ok),
    total: resultados.reduce((sum, item) => sum + (item.total || 0), 0),
    guardados: resultados.reduce((sum, item) => sum + (item.guardados || 0), 0),
    errores: resultados.reduce((sum, item) => sum + (item.errores?.length || 0), 0),
    resultados,
  };
}

async function buildCatalogExcelTemplate() {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'ARKHON Monitor';
  workbook.created = new Date();

  const intro = workbook.addWorksheet('Instrucciones');
  intro.columns = [{ width: 28 }, { width: 95 }];
  intro.addRow(['Formato', 'Cada hoja representa una tabla de catalogo SoftRestaurant. Mantenga los nombres de hojas y columnas.']);
  intro.addRow(['Operacion', 'El importador hace upsert por llave natural: actualiza si existe, inserta si no existe. No elimina registros.']);
  intro.addRow(['Omitido', 'Imagenes, huellas, clientes y domicilios no se importan automaticamente por peso/sensibilidad.']);
  intro.getRow(1).font = { bold: true };

  for (const table of TABLE_ORDER) {
    const columns = TEMPLATE_COLUMNS[table];
    if (!columns) continue;
    const sheet = workbook.addWorksheet(table);
    sheet.addRow(columns);
    sheet.getRow(1).font = { bold: true };
    sheet.views = [{ state: 'frozen', ySplit: 1 }];
    sheet.columns = columns.map(column => ({ key: column, width: Math.max(14, Math.min(32, column.length + 4)) }));
  }

  return workbook.xlsx.writeBuffer();
}

module.exports = {
  TABLE_KEYS,
  TABLE_ORDER,
  parseNativeCatalogXml,
  previewNativeCatalogZip,
  previewCatalogExcel,
  applyCatalogImport,
  buildCatalogExcelTemplate,
};
