'use strict';

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

async function extract(pool, watermark, limit) {
  const metadata = await pool.request().query(`
    SELECT
      COL_LENGTH('${db()}cancela', 'idcancelacion') AS has_idcancelacion,
      COL_LENGTH('${db()}cancela', 'foliocheque') AS has_foliocheque,
      COL_LENGTH('${db()}cancela', 'fecha') AS has_fecha
  `);
  const row = metadata.recordset[0] || {};
  const folioExpr = row.has_foliocheque ? 'ca.foliocheque' : '0';
  const fechaExpr = row.has_fecha ? 'ca.fecha' : "CAST('2000-01-01' AS datetime)";
  const idExpr = row.has_idcancelacion
    ? 'ca.idcancelacion'
    : `CONVERT(varchar(23), ${fechaExpr}, 126)`;
  const whereExpr = row.has_fecha ? `WHERE ${fechaExpr} > @last` : '';
  const orderExpr = row.has_fecha ? `${fechaExpr}, ${folioExpr}` : folioExpr;

  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)
        CONCAT(CAST(${folioExpr} AS varchar(80)), ':', CAST(${idExpr} AS varchar(80))) AS external_id,
        ${fechaExpr} AS source_created_at,
        ${fechaExpr} AS source_updated_at,
        CAST(${fechaExpr} AS date) AS business_date,
        ca.*
      FROM ${db()}cancela ca
      ${whereExpr}
      ORDER BY ${orderExpr}
    `);
  return result.recordset.map((row) => ({ ...row, payload: row }));
}

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