'use strict';

const { sql } = require('../../db');
const { db } = require('../../utils/dbName');
const { inspectTables, requireColumns } = require('../softrestaurantIntrospection');
const { AREA_CATALOG_SPECS, SOURCE_AREA_COLUMNS, areaCatalogSql, detectSourceAreaColumn, normalizeServiceArea } = require('../serviceAreaNormalizer');

function hasTable(metadata, tableName) {
  return Boolean(metadata.tables?.[tableName]?.exists);
}

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

function column(metadata, tableName, alias, columnName, fallback = 'NULL') {
  return hasColumn(metadata, tableName, columnName) ? `${alias}.${columnName}` : fallback;
}

function canUseTemp(metadata) {
  if (!hasTable(metadata, 'tempcheques')) return false;
  return !requireColumns(metadata, 'tempcheques', ['folio', 'fecha']).length;
}

async function inspect(pool) {
  return inspectTables(pool, {
    cheques: ['folio', 'fecha', 'fechacancelado', 'mesa', 'idmesero', 'idturno', ...SOURCE_AREA_COLUMNS, 'total', 'subtotal', 'totaldescuentos', 'totalcortesias', 'totalalimentos', 'totalbebidas', 'totalotros', 'nopersonas', 'impreso', 'cancelado', 'pagado'],
    tempcheques: ['folio', 'fecha', 'mesa', 'idmesero', 'idturno', ...SOURCE_AREA_COLUMNS, 'total', 'subtotal', 'totaldescuentos', 'totalcortesias', 'totalalimentos', 'totalbebidas', 'totalotros', 'nopersonas', 'impreso', 'cancelado', 'pagado'],
    ...AREA_CATALOG_SPECS,
  });
}

async function extract(pool, watermark, limit) {
  const metadata = await inspect(pool);
  const errors = requireColumns(metadata, 'cheques', ['folio', 'fecha', 'total', 'cancelado', 'pagado']);
  if (errors.length) throw new Error(`SoftRestaurant sales schema incompleto: ${errors.join('; ')}`);
  const chequeAreaColumn = detectSourceAreaColumn(metadata, 'cheques');
  const tempAreaColumn = detectSourceAreaColumn(metadata, 'tempcheques');
  const unions = [`
        SELECT
          'C' AS origen,
          c.folio,
          c.fecha,
          ${column(metadata, 'cheques', 'c', 'fechacancelado', 'c.fecha')} AS fechacancelado,
          ${column(metadata, 'cheques', 'c', 'mesa')} AS mesa,
          ${column(metadata, 'cheques', 'c', 'idmesero')} AS idmesero,
          ${column(metadata, 'cheques', 'c', 'idturno')} AS idturno,
          ${chequeAreaColumn ? `c.${chequeAreaColumn}` : 'NULL'} AS idarearestaurant,
          c.total,
          ${column(metadata, 'cheques', 'c', 'subtotal', 'c.total')} AS subtotal,
          ${column(metadata, 'cheques', 'c', 'totaldescuentos', '0')} AS totaldescuentos,
          ${column(metadata, 'cheques', 'c', 'totalcortesias', '0')} AS totalcortesias,
          ${column(metadata, 'cheques', 'c', 'totalalimentos', '0')} AS totalalimentos,
          ${column(metadata, 'cheques', 'c', 'totalbebidas', '0')} AS totalbebidas,
          ${column(metadata, 'cheques', 'c', 'totalotros', '0')} AS totalotros,
          ${column(metadata, 'cheques', 'c', 'nopersonas', '0')} AS nopersonas,
          ${column(metadata, 'cheques', 'c', 'impreso', '0')} AS impreso,
          c.cancelado,
          c.pagado
        FROM ${db()}cheques c
        WHERE c.cancelado = 0 AND c.pagado = 1 AND c.total > 0`];
  if (canUseTemp(metadata)) {
    unions.push(`
        SELECT
          'T' AS origen,
          tc.folio,
          tc.fecha,
          tc.fecha AS fechacancelado,
          ${column(metadata, 'tempcheques', 'tc', 'mesa')} AS mesa,
          ${column(metadata, 'tempcheques', 'tc', 'idmesero')} AS idmesero,
          ${column(metadata, 'tempcheques', 'tc', 'idturno')} AS idturno,
          ${tempAreaColumn ? `tc.${tempAreaColumn}` : 'NULL'} AS idarearestaurant,
          ${column(metadata, 'tempcheques', 'tc', 'total', '0')} AS total,
          ${column(metadata, 'tempcheques', 'tc', 'subtotal', column(metadata, 'tempcheques', 'tc', 'total', '0'))} AS subtotal,
          ${column(metadata, 'tempcheques', 'tc', 'totaldescuentos', '0')} AS totaldescuentos,
          ${column(metadata, 'tempcheques', 'tc', 'totalcortesias', '0')} AS totalcortesias,
          ${column(metadata, 'tempcheques', 'tc', 'totalalimentos', '0')} AS totalalimentos,
          ${column(metadata, 'tempcheques', 'tc', 'totalbebidas', '0')} AS totalbebidas,
          ${column(metadata, 'tempcheques', 'tc', 'totalotros', '0')} AS totalotros,
          ${column(metadata, 'tempcheques', 'tc', 'nopersonas', '0')} AS nopersonas,
          ${column(metadata, 'tempcheques', 'tc', 'impreso', '0')} AS impreso,
          ${column(metadata, 'tempcheques', 'tc', 'cancelado', '0')} AS cancelado,
          ${column(metadata, 'tempcheques', 'tc', 'pagado', '1')} AS pagado
        FROM ${db()}tempcheques tc
        WHERE ISNULL(${column(metadata, 'tempcheques', 'tc', 'cancelado', '0')}, 0) = 0
          AND ISNULL(${column(metadata, 'tempcheques', 'tc', 'pagado', '1')}, 0) = 1
          AND ISNULL(${column(metadata, 'tempcheques', 'tc', 'total', '1')}, 0) > 0
          AND NOT EXISTS (
            SELECT 1
            FROM ${db()}cheques ch
            WHERE ch.folio = tc.folio
              AND CAST(ch.fecha AS date) = CAST(tc.fecha AS date)
              AND ch.cancelado = 0
              AND ch.pagado = 1
          )`);
  }
  const areaCatalog = areaCatalogSql(metadata, db(), 'v.idarearestaurant');
  const result = await pool.request()
    .input('last', sql.DateTime, watermark ? new Date(watermark) : new Date('2000-01-01T00:00:00'))
    .input('limit', sql.Int, limit)
    .query(`
      SELECT TOP (@limit)
        CAST(CONCAT(v.origen, '-', CONVERT(varchar(10), CAST(v.fecha AS date), 120), '-', v.folio) AS varchar(120)) AS external_id,
        v.fecha AS source_created_at,
        COALESCE(v.fechacancelado, v.fecha) AS source_updated_at,
        CAST(v.fecha AS date) AS business_date,
        v.folio, v.fecha, v.mesa, v.idmesero, v.idturno, v.idarearestaurant,
        ${areaCatalog.nameExpression} AS area_nombre_fuente,
        v.total, v.subtotal, v.totaldescuentos, v.totalcortesias,
        v.totalalimentos, v.totalbebidas, v.totalotros, v.nopersonas, v.impreso,
        v.cancelado, v.pagado, v.origen
      FROM (
${unions.join('\n        UNION ALL\n')}
      ) v
      ${areaCatalog.join}
      WHERE COALESCE(v.fechacancelado, v.fecha) > @last
      ORDER BY COALESCE(v.fechacancelado, v.fecha), v.folio
    `);
  return result.recordset.map((row) => {
    const area = normalizeServiceArea({ code: row.idarearestaurant, name: row.area_nombre_fuente });
    const normalized = { ...row, ...area };
    return { ...normalized, payload: { ...normalized, raw_payload: row } };
  });
}

module.exports = { entityType: 'sales', extract, inspect };
