-- Validacion rapida de costo promedio (normales y formulados)
-- Uso sugerido:
--   SET @p = '2026-05';
--   SOURCE scripts/validar_promedios_rapido.sql;
--
-- Si @p no viene seteado, toma el mes anterior.

SET @p = IFNULL(@p, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m'));
SET @tol = 0.0001;
SET @tol_formula = 0.01;

SELECT 'periodo_validado' AS regla, @p AS detalle, 'INFO' AS estado;

-- 1) Cobertura: todo plu con movimiento del periodo debe existir en promedios
SELECT
    'movimientos_sin_promedio' AS regla,
    COUNT(*) AS casos,
    IF(COUNT(*) = 0, 'OK', 'ERROR') AS estado
FROM (
    SELECT DISTINCT TRIM(d.plu) AS plu
    FROM detmov d
    WHERE d.fecha BETWEEN CONCAT(@p, '-01') AND LAST_DAY(CONCAT(@p, '-01'))
) m
LEFT JOIN promedios p
    ON p.periodo = @p
   AND TRIM(p.plu) = m.plu
WHERE p.plu IS NULL;

-- 2) Consistencia: promedios vs promedios_calculado
SELECT
    'promedio_vs_calculado_diferente' AS regla,
    COUNT(*) AS casos,
    IF(COUNT(*) = 0, 'OK', 'ERROR') AS estado
FROM promedios p
JOIN promedios_calculado pc
    ON pc.periodo = p.periodo
   AND TRIM(pc.plu) = TRIM(p.plu)
WHERE p.periodo = @p
  AND (
      ABS(COALESCE(p.promedio_final, 0) - COALESCE(pc.promedio_final, 0)) > @tol
      OR ABS(COALESCE(p.ultimo_final, 0) - COALESCE(pc.ultimo_final, 0)) > @tol
  );

-- 3) Integridad inventario_final: final = inicial + entradas - salidas
SELECT
    'inventario_final_inconsistente' AS regla,
    COUNT(*) AS casos,
    IF(COUNT(*) = 0, 'OK', 'ERROR') AS estado
FROM inventario_final i
WHERE i.periodo = @p
  AND ABS(
      COALESCE(i.final_cantidad, 0)
      - (
          COALESCE(i.inicial_cantidad, 0)
          + COALESCE(i.entradas_cantidad, 0)
          - COALESCE(i.salidas_cantidad, 0)
      )
  ) > @tol;

-- 4) Formulados: promedio del padre vs costo esperado por receta
WITH formulados_mov AS (
    SELECT DISTINCT TRIM(d.plu) AS plu
    FROM detmov d
    WHERE d.fecha BETWEEN CONCAT(@p, '-01') AND LAST_DAY(CONCAT(@p, '-01'))
      AND d.movimiento = 'E'
      AND EXISTS (
          SELECT 1
          FROM maetdc tdc
          JOIN maemov mov ON mov.movimiento = tdc.movimiento
          WHERE tdc.tipodoc = d.tipodoc
            AND (mov.promedio = 'S' OR mov.escompra = 'S' OR tdc.es_produccion = 'C')
      )
      AND NOT EXISTS (
          SELECT 1
          FROM maetdc tdc
          JOIN maemov mov ON mov.movimiento = tdc.movimiento
          WHERE tdc.tipodoc = d.tipodoc
            AND mov.escompra = 'S'
            AND mov.promedio = 'S'
      )
),
receta AS (
    SELECT TRIM(plu) AS plu, TRIM(envplu) AS envplu, cantidad FROM detfor
    UNION ALL
    SELECT TRIM(plu) AS plu, TRIM(envplu) AS envplu, cantidad FROM detfor_produccion
),
esperado AS (
    SELECT fm.plu,
           SUM(r.cantidad * COALESCE(ph.promedio_final, 0)) AS costo_esperado
    FROM formulados_mov fm
    JOIN receta r ON r.plu = fm.plu
    LEFT JOIN promedios ph
      ON ph.periodo = @p
     AND TRIM(ph.plu) = r.envplu
    GROUP BY fm.plu
)
SELECT
    'formulados_costo_fuera_tolerancia' AS regla,
    COUNT(*) AS casos,
    IF(COUNT(*) = 0, 'OK', 'ERROR') AS estado
FROM promedios p
JOIN esperado e ON TRIM(e.plu) = TRIM(p.plu)
WHERE p.periodo = @p
  AND ABS(COALESCE(p.promedio_final, 0) - COALESCE(e.costo_esperado, 0)) > @tol_formula;

-- 5) Ultimo costo: debe empatar con ultima compra de entrada del periodo
SELECT
    'ultimo_final_no_empata_ultima_compra' AS regla,
    COUNT(*) AS casos,
    IF(COUNT(*) = 0, 'OK', 'ERROR') AS estado
FROM promedios_calculado pc
JOIN (
    SELECT d1.plu, d1.sicosto
    FROM detmov d1
    JOIN (
        SELECT d.plu, MAX(CONCAT(d.fecha, ' ', LPAD(d.id, 12, '0'))) AS clave_ult
        FROM detmov d
        WHERE d.fecha BETWEEN CONCAT(@p, '-01') AND LAST_DAY(CONCAT(@p, '-01'))
          AND d.movimiento = 'E'
          AND d.cantidad > 0
          AND EXISTS (
              SELECT 1
              FROM maetdc tdc
              JOIN maemov mov ON mov.movimiento = tdc.movimiento
              WHERE tdc.tipodoc = d.tipodoc
                AND mov.escompra = 'S'
                AND mov.promedio = 'S'
          )
        GROUP BY d.plu
    ) x ON x.plu = d1.plu
       AND CONCAT(d1.fecha, ' ', LPAD(d1.id, 12, '0')) = x.clave_ult
) lc ON TRIM(lc.plu) = TRIM(pc.plu)
WHERE pc.periodo = @p
  AND ABS(COALESCE(pc.ultimo_final, 0) - COALESCE(lc.sicosto, 0)) > @tol;
