'use strict';

const { sql } = require('../../db');
const { db } = require('../../utils/dbName');
const { inspectTables, requireColumns } = require('../softrestaurantIntrospection');

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 sourceUpdatedAt(metadata, tableName, alias) {
  return hasColumn(metadata, tableName, 'fechacancelado')
    ? `COALESCE(${alias}.fechacancelado, ${alias}.fecha)`
    : `${alias}.fecha`;
}

function formaPagoSelect(metadata, alias) {
  if (!hasTable(metadata, 'formasdepago') || !hasColumn(metadata, 'formasdepago', 'descripcion')) return `CAST(${alias}.idformadepago AS nvarchar(80))`;
  return `LTRIM(RTRIM(ISNULL(fp.descripcion, CAST(${alias}.idformadepago AS nvarchar(80)))))`;
}

function formaPagoJoin(metadata, alias) {
  if (!hasTable(metadata, 'formasdepago') || !hasColumn(metadata, 'formasdepago', 'idformadepago')) return '';
  return `LEFT JOIN ${db()}formasdepago fp ON fp.idformadepago = ${alias}.idformadepago`;
}

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

async function inspect(pool) {
  return inspectTables(pool, {
    cheques: ['folio', 'fecha', 'fechacancelado', 'idturno', 'idmesero', 'cancelado', 'pagado'],
    chequespagos: ['folio', 'idformadepago', 'importe'],
    tempcheques: ['folio', 'fecha', 'idturno', 'idmesero', 'cancelado', 'pagado'],
    tempchequespagos: ['folio', 'idformadepago', 'importe'],
    formasdepago: ['idformadepago', 'descripcion'],
  });
}

async function extract(pool, watermark, limit) {
  const metadata = await inspect(pool);
  const errors = [
    ...requireColumns(metadata, 'cheques', ['folio', 'fecha', 'cancelado', 'pagado']),
    ...requireColumns(metadata, 'chequespagos', ['folio', 'idformadepago', 'importe']),
  ];
  if (errors.length) throw new Error(`SoftRestaurant sale_payments schema incompleto: ${errors.join('; ')}`);
  const unions = [`
        SELECT
          'C' AS origen,
          c.folio,
          c.fecha AS source_created_at,
          ${sourceUpdatedAt(metadata, 'cheques', 'c')} AS source_updated_at,
          CAST(c.fecha AS date) AS business_date,
          ${column(metadata, 'cheques', 'c', 'idturno')} AS idturno,
          ${column(metadata, 'cheques', 'c', 'idmesero')} AS idmesero,
          cp.idformadepago,
          ${formaPagoSelect(metadata, 'cp')} AS forma_pago,
          ISNULL(cp.importe, 0) AS importe
        FROM ${db()}cheques c
        INNER JOIN ${db()}chequespagos cp ON cp.folio = c.folio
        ${formaPagoJoin(metadata, 'cp')}
        WHERE c.cancelado = 0 AND c.pagado = 1 AND ${sourceUpdatedAt(metadata, 'cheques', 'c')} > @last AND ISNULL(cp.importe, 0) <> 0`];
  if (canUseTemp(metadata)) {
    unions.push(`
        SELECT
          'T' AS origen,
          tc.folio,
          tc.fecha AS source_created_at,
          tc.fecha AS source_updated_at,
          CAST(tc.fecha AS date) AS business_date,
          ${column(metadata, 'tempcheques', 'tc', 'idturno')} AS idturno,
          ${column(metadata, 'tempcheques', 'tc', 'idmesero')} AS idmesero,
          tp.idformadepago,
          ${formaPagoSelect(metadata, 'tp')} AS forma_pago,
          ISNULL(tp.importe, 0) AS importe
        FROM ${db()}tempcheques tc
        INNER JOIN ${db()}tempchequespagos tp ON tp.folio = tc.folio
        ${formaPagoJoin(metadata, 'tp')}
        WHERE ISNULL(${column(metadata, 'tempcheques', 'tc', 'cancelado', '0')}, 0) = 0
          AND ISNULL(${column(metadata, 'tempcheques', 'tc', 'pagado', '1')}, 0) = 1
          AND tc.fecha > @last
          AND ISNULL(tp.importe, 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 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(p.origen, '-', CONVERT(varchar(10), p.business_date, 120), '-', p.folio, '-', p.idformadepago, '-', ROW_NUMBER() OVER (PARTITION BY p.origen, p.business_date, p.folio ORDER BY p.idformadepago, p.importe)) AS varchar(180)) AS external_id,
        p.source_created_at,
        p.source_updated_at,
        p.business_date,
        p.origen,
        p.folio,
        p.idturno,
        p.idmesero,
        p.idformadepago,
        p.forma_pago,
        p.importe
      FROM (
${unions.join('\n        UNION ALL\n')}
      ) p
      WHERE p.source_updated_at > @last
      ORDER BY p.source_updated_at, p.folio, p.idformadepago
    `);
  return result.recordset.map((row) => ({ ...row, payload: { ...row, raw_payload: row } }));
}

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