'use strict';

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

async function countRows(pool, tableName) {
  return pool.request()
    .query(`SELECT COUNT(*) AS count FROM ${qname(tableName)}`)
    .then((result) => Number(result.recordset[0]?.count || 0))
    .catch(() => 0);
}

function mapRows(rows, sourceName) {
  return rows.map((row) => {
    const payload = { ...row, source_table: sourceName };
    return {
      ...row,
      payload,
    };
  });
}

async function extractFromMovsInv(pool, limit) {
  const result = await pool.request()
    .input('limit', sql.Int, limit)
    .query(`
      SELECT TOP (@limit)
        CAST(CONCAT('movsinv-stock-', LTRIM(RTRIM(m.idalmacen)), '-', LTRIM(RTRIM(m.idinsumo))) AS varchar(220)) AS external_id,
        MIN(m.fecha) AS source_created_at,
        GETDATE() AS source_updated_at,
        CAST(GETDATE() AS date) AS business_date,
        LTRIM(RTRIM(m.idinsumo)) AS idinsumo,
        i.descripcion AS insumo,
        COALESCE(p.idinsumospresentaciones, LTRIM(RTRIM(m.idinsumo))) AS idinsumospresentaciones,
        COALESCE(p.descripcion, i.descripcion) AS presentacion,
        COALESCE(p.unidad, i.unidad) AS unidad,
        LTRIM(RTRIM(m.idalmacen)) AS idalmacen,
        a.nombre AS almacen,
        ROUND(ISNULL(SUM(m.cantidad), 0), 4) AS existencia,
        ROUND(ISNULL(SUM(m.cantidad), 0), 4) AS stock,
        ROUND(ISNULL(AVG(m.costo), 0), 2) AS costo_promedio,
        ROUND(ISNULL(SUM(m.cantidad * m.costo), 0), 2) AS valor_total,
        ISNULL(pd.stockminimogeneral, 0) AS stock_minimo,
        CAST(0 AS numeric(18,4)) AS stock_ideal,
        ISNULL(pd.stockmaximogeneral, 0) AS stock_maximo,
        CASE
          WHEN ISNULL(pd.stockminimogeneral,0) > 0 AND ISNULL(SUM(m.cantidad),0) <= pd.stockminimogeneral THEN 'critico'
          ELSE 'ok'
        END AS estado,
        MAX(m.fecha) AS last_movement_at,
        COUNT(*) AS movimientos
      FROM ${qname('movsinv')} m
      INNER JOIN ${qname('insumos')} i ON LTRIM(RTRIM(i.idinsumo)) = LTRIM(RTRIM(m.idinsumo))
      INNER JOIN ${qname('almacen')} a ON LTRIM(RTRIM(a.idalmacen)) = LTRIM(RTRIM(m.idalmacen))
      OUTER APPLY (
        SELECT TOP 1 ip.idinsumospresentaciones, ip.descripcion, ip.unidad
        FROM ${qname('insumospresentaciones')} ip
        WHERE LTRIM(RTRIM(ip.idinsumo)) = LTRIM(RTRIM(m.idinsumo))
        ORDER BY CASE WHEN LTRIM(RTRIM(ip.idinsumospresentaciones)) = LTRIM(RTRIM(m.idinsumo)) THEN 0 ELSE 1 END,
                 ip.idinsumospresentaciones
      ) p
      LEFT JOIN ${qname('insumospresentacionesdetalle')} pd
        ON LTRIM(RTRIM(pd.idinsumospresentaciones)) = LTRIM(RTRIM(p.idinsumospresentaciones))
      GROUP BY
        LTRIM(RTRIM(m.idalmacen)), LTRIM(RTRIM(m.idinsumo)),
        i.descripcion, COALESCE(p.idinsumospresentaciones, LTRIM(RTRIM(m.idinsumo))),
        COALESCE(p.descripcion, i.descripcion), COALESCE(p.unidad, i.unidad),
        a.nombre, pd.stockminimogeneral, pd.stockmaximogeneral
      ORDER BY i.descripcion, a.nombre
    `);
  return mapRows(result.recordset, 'movsinv');
}

async function extractFromAcumulado(pool, limit) {
  const result = await pool.request()
    .input('limit', sql.Int, limit)
    .query(`
      SELECT TOP (@limit)
        CAST(CONCAT('acumulado-stock-', LTRIM(RTRIM(ai.idalmacen)), '-', LTRIM(RTRIM(ai.idinsumo))) AS varchar(220)) AS external_id,
        NULL AS source_created_at,
        GETDATE() AS source_updated_at,
        CAST(GETDATE() AS date) AS business_date,
        LTRIM(RTRIM(ai.idinsumo)) AS idinsumo,
        i.descripcion AS insumo,
        COALESCE(p.idinsumospresentaciones, LTRIM(RTRIM(ai.idinsumo))) AS idinsumospresentaciones,
        COALESCE(p.descripcion, i.descripcion) AS presentacion,
        COALESCE(p.unidad, i.unidad) AS unidad,
        LTRIM(RTRIM(ai.idalmacen)) AS idalmacen,
        a.nombre AS almacen,
        ROUND(ISNULL(ai.existencia, 0), 4) AS existencia,
        ROUND(ISNULL(ai.existencia, 0), 4) AS stock,
        ROUND(ISNULL(id.costopromedio, id.costo), 2) AS costo_promedio,
        ROUND(ISNULL(ai.existencia, 0) * ISNULL(id.costopromedio, id.costo), 2) AS valor_total,
        ISNULL(pd.stockminimogeneral, 0) AS stock_minimo,
        CAST(0 AS numeric(18,4)) AS stock_ideal,
        ISNULL(pd.stockmaximogeneral, 0) AS stock_maximo,
        CASE
          WHEN ISNULL(pd.stockminimogeneral,0) > 0 AND ISNULL(ai.existencia,0) <= pd.stockminimogeneral THEN 'critico'
          ELSE 'ok'
        END AS estado,
        NULL AS last_movement_at,
        CAST(0 AS int) AS movimientos
      FROM ${qname('acumuladoinsumos')} ai
      INNER JOIN ${qname('insumos')} i ON LTRIM(RTRIM(i.idinsumo)) = LTRIM(RTRIM(ai.idinsumo))
      INNER JOIN ${qname('almacen')} a ON LTRIM(RTRIM(a.idalmacen)) = LTRIM(RTRIM(ai.idalmacen))
      LEFT JOIN ${qname('insumosdetalle')} id ON LTRIM(RTRIM(id.idinsumo)) = LTRIM(RTRIM(ai.idinsumo))
      OUTER APPLY (
        SELECT TOP 1 ip.idinsumospresentaciones, ip.descripcion, ip.unidad
        FROM ${qname('insumospresentaciones')} ip
        WHERE LTRIM(RTRIM(ip.idinsumo)) = LTRIM(RTRIM(ai.idinsumo))
        ORDER BY CASE WHEN LTRIM(RTRIM(ip.idinsumospresentaciones)) = LTRIM(RTRIM(ai.idinsumo)) THEN 0 ELSE 1 END,
                 ip.idinsumospresentaciones
      ) p
      LEFT JOIN ${qname('insumospresentacionesdetalle')} pd
        ON LTRIM(RTRIM(pd.idinsumospresentaciones)) = LTRIM(RTRIM(p.idinsumospresentaciones))
      ORDER BY i.descripcion, a.nombre
    `);
  return mapRows(result.recordset, 'acumuladoinsumos');
}

async function extractFromMovtosAlmacen(pool, limit) {
  const result = await pool.request()
    .input('limit', sql.Int, limit)
    .query(`
      SELECT TOP (@limit)
        CAST(CONCAT('movtosalmacen-stock-', LTRIM(RTRIM(m.idalmacen)), '-', LTRIM(RTRIM(ip.idinsumospresentaciones))) AS varchar(220)) AS external_id,
        MIN(m.fecha) AS source_created_at,
        GETDATE() AS source_updated_at,
        CAST(GETDATE() AS date) AS business_date,
        LTRIM(RTRIM(i.idinsumo)) AS idinsumo,
        i.descripcion AS insumo,
        LTRIM(RTRIM(ip.idinsumospresentaciones)) AS idinsumospresentaciones,
        ip.descripcion AS presentacion,
        ip.unidad,
        LTRIM(RTRIM(m.idalmacen)) AS idalmacen,
        a.nombre AS almacen,
        ROUND(ISNULL(SUM(m.cantidad), 0), 4) AS existencia,
        ROUND(ISNULL(SUM(m.cantidad), 0), 4) AS stock,
        ROUND(ISNULL(AVG(m.costo), 0), 2) AS costo_promedio,
        ROUND(ISNULL(SUM(m.cantidad * m.costo), 0), 2) AS valor_total,
        ISNULL(si.stockminimo, 0) AS stock_minimo,
        ISNULL(si.stockideal, 0) AS stock_ideal,
        ISNULL(si.stockmaximo, 0) AS stock_maximo,
        CASE
          WHEN ISNULL(si.stockminimo,0) > 0 AND ISNULL(SUM(m.cantidad),0) <= si.stockminimo THEN 'critico'
          WHEN ISNULL(si.stockideal,0) > 0 AND ISNULL(SUM(m.cantidad),0) <= si.stockideal THEN 'bajo'
          ELSE 'ok'
        END AS estado,
        MAX(m.fecha) AS last_movement_at,
        COUNT(*) AS movimientos
      FROM ${qname('movtosalmacen')} m
      INNER JOIN ${qname('insumospresentaciones')} ip ON LTRIM(RTRIM(m.idinsumospresentaciones)) = LTRIM(RTRIM(ip.idinsumospresentaciones))
      INNER JOIN ${qname('insumos')} i ON LTRIM(RTRIM(ip.idinsumo)) = LTRIM(RTRIM(i.idinsumo))
      INNER JOIN ${qname('almacen')} a ON LTRIM(RTRIM(m.idalmacen)) = LTRIM(RTRIM(a.idalmacen))
      LEFT JOIN ${qname('stockinsumos')} si
        ON LTRIM(RTRIM(si.idinsumospresentaciones)) = LTRIM(RTRIM(ip.idinsumospresentaciones))
       AND LTRIM(RTRIM(si.idalmacen)) = LTRIM(RTRIM(m.idalmacen))
      GROUP BY LTRIM(RTRIM(m.idalmacen)), a.nombre, LTRIM(RTRIM(i.idinsumo)), i.descripcion,
        LTRIM(RTRIM(ip.idinsumospresentaciones)), ip.descripcion, ip.unidad,
        si.stockminimo, si.stockideal, si.stockmaximo
      ORDER BY i.descripcion, a.nombre
    `);
  return mapRows(result.recordset, 'movtosalmacen');
}

async function extract(pool, watermark, limit) {
  const metadata = await inspectTables(pool, {
    movtosalmacen: ['fecha', 'idinsumospresentaciones', 'cantidad', 'idalmacen', 'costo'],
    movsinv: ['fecha', 'idinsumo', 'cantidad', 'idalmacen', 'costo'],
    acumuladoinsumos: ['idinsumo', 'idalmacen', 'existencia'],
    insumos: ['idinsumo', 'descripcion', 'unidad'],
    insumospresentaciones: ['idinsumospresentaciones', 'idinsumo', 'descripcion', 'unidad'],
    insumospresentacionesdetalle: ['idinsumospresentaciones', 'stockminimogeneral', 'stockmaximogeneral'],
    insumosdetalle: ['idinsumo', 'costo', 'costopromedio'],
    almacen: ['idalmacen', 'nombre'],
    stockinsumos: ['idinsumospresentaciones', 'idalmacen', 'stockminimo', 'stockideal', 'stockmaximo'],
  });
  if (!requireColumns(metadata, 'almacen', ['idalmacen']).length && !requireColumns(metadata, 'insumos', ['idinsumo']).length) {
    const movtosCount = requireColumns(metadata, 'movtosalmacen', ['fecha', 'idinsumospresentaciones', 'cantidad', 'idalmacen']).length ? 0 : await countRows(pool, 'movtosalmacen');
    if (movtosCount > 0) return extractFromMovtosAlmacen(pool, limit);
    const movsInvCount = requireColumns(metadata, 'movsinv', ['fecha', 'idinsumo', 'cantidad', 'idalmacen']).length ? 0 : await countRows(pool, 'movsinv');
    if (movsInvCount > 0) return extractFromMovsInv(pool, limit);
    const acumuladoCount = requireColumns(metadata, 'acumuladoinsumos', ['idinsumo', 'idalmacen', 'existencia']).length ? 0 : await countRows(pool, 'acumuladoinsumos');
    if (acumuladoCount > 0) return extractFromAcumulado(pool, limit);
  }
  return [];
}

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