'use strict';

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

let metadataPromise;

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

function flag(metadata, tableName, alias, columnName, fallback) {
  return hasColumn(metadata, tableName, columnName) ? `${alias}.${columnName}` : fallback;
}

async function metadata(pool) {
  metadataPromise ||= inspectTables(pool, {
    cheques: ['folio', 'fecha', 'total', 'pagado', 'cancelado'],
    tempcheques: ['folio', 'fecha', 'total', 'pagado', 'cancelado'],
  });
  return metadataPromise;
}

function paidSelect(meta, tableName, alias) {
  const paid = flag(meta, tableName, alias, 'pagado', '1');
  const canceled = flag(meta, tableName, alias, 'cancelado', '0');
  return `SELECT ${alias}.folio, ${alias}.total
            FROM ${db()}${tableName} ${alias}
           WHERE CAST(${alias}.fecha AS date) = CAST(GETDATE() AS date)
             AND ISNULL(${paid}, 1) = 1
             AND ISNULL(${canceled}, 0) = 0
             AND ISNULL(${alias}.total, 0) > 0`;
}

async function extract(pool) {
  const meta = await metadata(pool);
  const errors = requireColumns(meta, 'cheques', ['folio', 'fecha', 'total']);
  if (errors.length) throw new Error(`SoftRestaurant live sales schema incompleto: ${errors.join('; ')}`);

  const consolidated = paidSelect(meta, 'cheques', 'ch');
  const hasTemporary = meta.tables?.tempcheques?.exists
    && !requireColumns(meta, 'tempcheques', ['folio', 'fecha', 'total']).length;
  const temporary = hasTemporary ? paidSelect(meta, 'tempcheques', 'tc') : null;
  const source = temporary
    ? `${consolidated}
       UNION
       ${temporary}
         AND NOT EXISTS (
           SELECT 1 FROM ${db()}cheques ch2
            WHERE ch2.folio = tc.folio
              AND CAST(ch2.fecha AS date) = CAST(GETDATE() AS date)
              AND ISNULL(${flag(meta, 'cheques', 'ch2', 'pagado', '1')}, 1) = 1
              AND ISNULL(${flag(meta, 'cheques', 'ch2', 'cancelado', '0')}, 0) = 0
         )`
    : consolidated;

  const result = await pool.request().query(`
    SELECT COUNT(*) AS cuentas_pagadas,
           ROUND(ISNULL(SUM(p.total), 0), 2) AS venta_pagada
      FROM (${source}) p
  `);
  const row = result.recordset?.[0] || {};
  const tickets = Number(row.cuentas_pagadas || 0);
  const total = Number(row.venta_pagada || 0);
  return {
    cuentas_pagadas: tickets,
    venta_pagada: total,
    venta_total: total,
    ticket_promedio: tickets ? Math.round((total / tickets) * 100) / 100 : 0,
  };
}

module.exports = { extract };
