from django.contrib.auth.decorators import login_required
from django.shortcuts import render
from django.views.decorators.csrf import csrf_exempt
from django.http import JsonResponse, HttpResponse
from django.db import transaction
from django.utils.timezone import now
from datetime import datetime
from ..services.foxReportService import report_exporta_data
from ..services.connexionsService import getConnexion
import pyodbc
import pandas as pd
import csv
from app.Models.fox_products_models import (queryfox_hist)


def getCampaignNumbers(): 
    query = f"""
            SELECT id,Number FROM Campaign ORDER BY id DESC
            """
        
    connection = getConnexion()
    try:         
        with connection.cursor() as cursor:
            print("conectando..") 
            cursor.execute(query)
            
            # Obtener nombres de columnas
            columns = [column[0] for column in cursor.description]

            # Obtener resultados
            results = [dict(zip(columns, row)) for row in cursor.fetchall()]

            return results
    
    except Exception as e:
        return []
    


def get_the_number_for_campaignid(campaign_id: int):
    """
    Dado un campaign_id, obtiene el campo Number (ej: '14/2025').
    """
    
    connection = getConnexion()
    try:
        with connection.cursor() as cursor:
            sql = "SELECT Number FROM Campaign WHERE Id = ?"
            # 👇 Aseguramos tupla de 1 elemento
            cursor.execute(sql, (campaign_id,))
            row = cursor.fetchone()
            if row:
                return row[0]
            else:
                return None
    except Exception as e:
        print("Error obteniendo campaign_number:", e)
        return None
    finally:
        if connection:
            connection.close()


def get_n_and_n1_campaign_numbers(campaign_id: int):
    """
    Retorna un diccionario con:
      - campaign_number (N)
      - previous_campaign_id (N-1)
      - previous_campaign_number (N-1)
    """

    sql = """
        DECLARE @CCID INT = ?;

        SELECT 
            C.Number AS CampaignNumber,
            Prev.Id AS PreviousCampaignId,
            Prev.Number AS PreviousCampaignNumber
        FROM Campaign C
        LEFT JOIN Campaign Prev
            ON Prev.Sequence = C.Sequence - 1
        WHERE C.Id = @CCID;
    """

    connection = getConnexion()
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, (campaign_id,))
            row = cursor.fetchone()

        if not row:
            return {
                "campaign_number": None,
                "previous_campaign_id": None,
                "previous_campaign_number": None
            }

        cols = [col[0] for col in cursor.description]
        return dict(zip(cols, row))

    finally:
        connection.close()



def get_queryfoxservice(campaign_id,is_save=False):
    """
        Ejecuta la query Fox principal. 
        Si is_save=True → guarda los resultados en queryfox_hist.
    """
   
    campaign_number = get_the_number_for_campaignid(campaign_id)
    if not campaign_number:
        print(f"No se encontró Number para campaign_id={campaign_id}")
        return []

    query = """
        ------------------------------------------------------------
        -- PARAMS
        ------------------------------------------------------------
        DECLARE @CCID INT = ?;
        DECLARE @campana VARCHAR(10) = ?;

         -- Campaña anterior por Sequence
        DECLARE @previousCampaignId INT = (
            SELECT TOP 1 Id
            FROM Campaign
            WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID)
        );

		SELECT DISTINCT CPL.LevelId
        INTO #TotalesZonasN 
        FROM CampaignLevel CPL
        INNER JOIN Level LV ON LV.id = CPL.LevelId
        INNER JOIN CampaignZona CZ on cz.CampaignLevelId = CPL.Id
        WHERE CPL.CampaignId = @CCID and LV.LevelTypeId = 6 --tipo zona 
        AND CZ.DateCC != '0001-01-01 00:00:00.0000000'
        
        -- Total facturado en campaña N-1 (solo computables, en UNIDADES)
        DECLARE @TotalNM1 INT = (
            SELECT SUM(OI.Quantity)
            FROM Orders O
            INNER JOIN OrderItem OI ON OI.OrderId = O.Id
            INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId
            INNER JOIN ProductType PT ON PT.Id = CP.ProductTypeId
            INNER JOIN #TotalesZonasN TZN ON TZN.LevelId = o.Zone
            WHERE O.CampaignId = @previousCampaignId
            AND O.OrderStatus IN (5,6,7,8,10)
            AND OI.PaymentCycle = 1
            AND OI.IsReturn = 0
            AND OI.IsHidden = 0
            AND O.Zone NOT IN (999,998,997)
            AND OI.IsPreviousNoStock = 0
            AND PT.Id != 13
        );

        ------------------------------------------------------------
        -- ItemsNormalizados (3 bloques)
        ------------------------------------------------------------
        SELECT 
            O.Id AS OrderId,
            CAST(O.InvoicingDate AS DATE) AS Fecha,
            P.Code,
            P.Name,
            OI.PromotionPrice AS PrecioVenta,
            OI.PromotionPriceQuantity AS Cantidad,
            CASE WHEN OI.OrderItemStatus = 3 THEN OI.PromotionPriceQuantity ELSE 0 END AS NoStockCantidad,
            C.CommercialOrders,
            CP.EstimatedSales,
            PT.Id AS ProductTypeId,
            PT.Description AS ProductType,
            PC.Name       AS Division
        INTO #ItemsNormalizados
        FROM OrderItem OI
        INNER JOIN Orders O           ON O.Id = OI.OrderId
        INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId
        INNER JOIN Product P          ON P.Id = CP.ProductId
        INNER JOIN ProductType PT     ON CP.ProductTypeId = PT.Id
        INNER JOIN ProductCategory PC ON P.ProductCategoryId = PC.Id
        INNER JOIN Campaign C         ON C.Id = @CCID
        WHERE O.CampaignId = @CCID
        AND O.OrderStatus IN (5,6,7,8,10)
        AND OI.PromotionPriceQuantity > 0
        AND OI.PaymentCycle = 1
        AND OI.IsReturn    = 0
        AND OI.IsHidden    = 0
        AND O.Zone NOT IN (999,998,997)
        AND OI.IsPreviousNoStock = 0
        UNION ALL
        -- Diferencia sin promo
        SELECT 
            O.Id AS OrderId,
            CAST(O.InvoicingDate AS DATE) AS Fecha,
            P.Code,
            P.Name,
            OI.Price AS PrecioVenta,
            (OI.Quantity - OI.PromotionPriceQuantity) AS Cantidad,
            CASE WHEN OI.OrderItemStatus = 3 THEN (OI.Quantity - OI.PromotionPriceQuantity) ELSE 0 END AS NoStockCantidad,
            C.CommercialOrders,
            CP.EstimatedSales,
            PT.Id AS ProductTypeId,
            PT.Description AS ProductType,
            PC.Name       AS Division
        FROM OrderItem OI
        INNER JOIN Orders O           ON O.Id = OI.OrderId
        INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId
        INNER JOIN Product P          ON P.Id = CP.ProductId
        INNER JOIN ProductType PT     ON CP.ProductTypeId = PT.Id
        INNER JOIN ProductCategory PC ON P.ProductCategoryId = PC.Id
        INNER JOIN Campaign C         ON C.Id = @CCID
        WHERE O.CampaignId = @CCID
        AND O.OrderStatus IN (5,6,7,8,10)
        AND OI.PromotionPriceQuantity > 0
        AND OI.Quantity > OI.PromotionPriceQuantity
        AND OI.PaymentCycle = 1
        AND OI.IsReturn    = 0
        AND OI.IsHidden    = 0
        AND O.Zone NOT IN (999,998,997)
        AND OI.IsPreviousNoStock = 0
        UNION ALL
        -- Sin promo
        SELECT 
            O.Id AS OrderId,
            CAST(O.InvoicingDate AS DATE) AS Fecha,
            P.Code,
            P.Name,
            OI.Price AS PrecioVenta,
            OI.Quantity AS Cantidad,
            CASE WHEN OI.OrderItemStatus = 3 THEN OI.Quantity ELSE 0 END AS NoStockCantidad,
            C.CommercialOrders,
            CP.EstimatedSales,
            PT.Id AS ProductTypeId,
            PT.Description AS ProductType,
            PC.Name       AS Division
        FROM OrderItem OI
        INNER JOIN Orders O           ON O.Id = OI.OrderId
        INNER JOIN CampaignProduct CP ON CP.Id = OI.CampaignProductId
        INNER JOIN Product P          ON P.Id = CP.ProductId
        INNER JOIN ProductType PT     ON CP.ProductTypeId = PT.Id
        INNER JOIN ProductCategory PC ON P.ProductCategoryId = PC.Id
        INNER JOIN Campaign C         ON C.Id = @CCID
        WHERE O.CampaignId = @CCID
        AND O.OrderStatus IN (5,6,7,8,10)
        AND ISNULL(OI.PromotionPriceQuantity,0) = 0
        AND OI.PaymentCycle = 1
        AND OI.IsReturn    = 0
        AND OI.IsHidden    = 0
        AND O.Zone NOT IN (999,998,997)
        AND OI.IsPreviousNoStock = 0;

        ------------------------------------------------------------
        -- Agregado por producto/día (N)
        ------------------------------------------------------------
        SELECT
            F.Fecha,
            F.Code,
            F.Name,
            F.PrecioVenta,
            F.ProductType,
            F.Division,
            SUM(F.Cantidad) - SUM(F.NoStockCantidad) AS [UNI FACT X DIA_PRECIO],
            SUM(F.NoStockCantidad)                    AS [UNI CORTADAS X DIA_PRECIO],
            MAX(F.CommercialOrders)                   AS CommercialOrders,
            MAX(F.EstimatedSales)                     AS EstimatedSales
        INTO #Agregado
        FROM #ItemsNormalizados F
        GROUP BY F.Fecha, F.Code, F.Name, F.PrecioVenta, F.ProductType, F.Division;

        ------------------------------------------------------------
        -- CampaignDaily / CampaignCum / PriceCum / Proy
        ------------------------------------------------------------
        SELECT Fecha, SUM([UNI FACT X DIA_PRECIO]) AS UnitsByDay
        INTO #CampaignDaily
        FROM #Agregado
        GROUP BY Fecha;

        SELECT Fecha, SUM(UnitsByDay) OVER (ORDER BY Fecha ROWS UNBOUNDED PRECEDING) AS TotalNXDay
        INTO #CampaignCum
        FROM #CampaignDaily;

        SELECT Code, PrecioVenta, Fecha,
            SUM([UNI FACT X DIA_PRECIO]) OVER (
                PARTITION BY Code, PrecioVenta ORDER BY Fecha ROWS UNBOUNDED PRECEDING
            ) AS TotalPrice
        INTO #PriceCum
        FROM #Agregado;

        SELECT PC.Code, PC.PrecioVenta, PC.Fecha,
            CC.TotalNXDay, @TotalNM1 AS Porcentaje, PC.TotalPrice AS InvoicingUnitsProjected
        INTO #Proy
        FROM #PriceCum PC
        INNER JOIN #CampaignCum CC ON CC.Fecha = PC.Fecha;
        

        ------------------------------------------------------------
		-- CALENDARIO REAL DE FACTURACIÓN (solo días con ventas)
		------------------------------------------------------------

		SELECT
		  Fecha,
		  ROW_NUMBER() OVER (ORDER BY Fecha) AS DIA_CALENDARIO_REAL
		INTO #CalendarioDias
		FROM (
		  -- Podés basarte en #Agregado (por producto/día/precio) o en #CampaignDaily (por día)
		  SELECT DISTINCT Fecha FROM #CampaignDaily
		) D
		ORDER BY Fecha;

        ------------------------------------------------------------
        -- Facturación acumulada y NoStock (en UNIDADES)
        ------------------------------------------------------------
        SELECT Code, PrecioVenta, Fecha,
            SUM([UNI FACT X DIA_PRECIO]) OVER (
                PARTITION BY Code, PrecioVenta ORDER BY Fecha ROWS UNBOUNDED PRECEDING
            ) AS UNI_ACU_FACT_HASTA_DIA
        INTO #FactAcum
        FROM #Agregado;

        SELECT Fecha, Code, SUM([UNI CORTADAS X DIA_PRECIO]) AS UNI_CORTADAS_X_DIA
        INTO #NoStockPorDia
        FROM #Agregado
        GROUP BY Fecha, Code;

        SELECT Code, Fecha,
            SUM(UNI_CORTADAS_X_DIA) OVER (
                PARTITION BY Code ORDER BY Fecha ROWS UNBOUNDED PRECEDING
            ) AS UNI_ACU_CORTADAS_HASTA_DIA
        INTO #NoStockAcum
        FROM #NoStockPorDia;

        ------------------------------------------------------------
        -- N-1: Totales por zona (constantes)
        ------------------------------------------------------------
        SELECT 
            o.Zone AS ZonaPedido,
            SUM(oi.Quantity) AS UnidsNM1_Total
        INTO #NM1Totales
        FROM Orders o
        JOIN OrderItem oi ON oi.OrderId = o.Id
        JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
        JOIN ProductType pt ON pt.Id = cp.ProductTypeId
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND oi.PaymentCycle = 1
        AND oi.IsReturn = 0
        AND oi.IsHidden = 0
        AND o.Zone NOT IN (999,998,997)
        AND oi.IsPreviousNoStock = 0
        AND pt.Id != 13
        GROUP BY o.Zone;

        ------------------------------------------------------------
        -- Fechas de corte N y zonas de N
        ------------------------------------------------------------
        SELECT DISTINCT Fecha INTO #Cortes FROM #Agregado;

        SELECT c.Fecha, o.Zone AS ZonaPedido
        INTO #ZonasN_porDia
        FROM #Cortes c
        JOIN Orders o ON o.CampaignId = @CCID
        JOIN OrderItem oi ON oi.OrderId = o.Id
        JOIN CampaignProduct CP ON CP.Id = oi.CampaignProductId
        JOIN ProductType PT ON CP.ProductTypeId = PT.Id
        WHERE o.OrderStatus IN (5,6,7,8,10)
        AND oi.PaymentCycle = 1
        AND oi.IsReturn = 0
        AND oi.IsHidden = 0
        AND o.Zone NOT IN (999,998,997)
        AND oi.IsPreviousNoStock = 0
        AND CAST(o.InvoicingDate AS date) <= c.Fecha
        AND PT.Id != 13
        GROUP BY c.Fecha, o.Zone;

        ------------------------------------------------------------
        -- Denominador N-1: suma de totales N-1 de las zonas que ya aparecieron en N
        ------------------------------------------------------------
        SELECT zn.Fecha, SUM(t.UnidsNM1_Total) AS TotalNM1_Hoy
        INTO #TotalNM1_HoyPorDia
        FROM #ZonasN_porDia zn
        JOIN #NM1Totales t ON t.ZonaPedido = zn.ZonaPedido
        GROUP BY zn.Fecha;

        ------------------------------------------------------------
        -- Facturación N por día y acumulada
        ------------------------------------------------------------
        DECLARE @TotalFactNM1 DECIMAL(18,2) = (
            SELECT SUM(
                ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
                + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * oi.Price
            )
            FROM Orders o
            JOIN OrderItem oi ON oi.OrderId = o.Id
            JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
            JOIN ProductType pt ON pt.Id = cp.ProductTypeId
            INNER JOIN #TotalesZonasN TZN ON TZN.LevelId = o.Zone
            WHERE o.CampaignId = @previousCampaignId
            AND o.OrderStatus IN (5,6,7,8,10)
            AND oi.PaymentCycle = 1
            AND oi.IsReturn = 0
            AND oi.IsHidden = 0
            AND o.Zone NOT IN (997,998,999)
            AND oi.IsPreviousNoStock = 0
            AND pt.Id != 13
        );

        SELECT Fecha,
            SUM(PrecioVenta * [UNI FACT X DIA_PRECIO]) AS FacturacionDia
        INTO #FactN_Dia
        FROM #Agregado
        GROUP BY Fecha;

        SELECT Fecha,
            FacturacionDia,
            SUM(FacturacionDia) OVER (ORDER BY Fecha ROWS UNBOUNDED PRECEDING) AS FacturacionAcumuladaN
        INTO #FactN
        FROM #FactN_Dia;

        ------------------------------------------------------------
        -- SELECT FINAL
        ------------------------------------------------------------
        SELECT 
            RIGHT(SUBSTRING(@campana, CHARINDEX('/', @campana) + 1, 4), 2) AS AA,
            CAST(LEFT(@campana, CHARINDEX('/', @campana) - 1) AS INT) AS CC,
            DAY(A.Fecha)  AS DIA,
            MONTH(A.Fecha) AS MES,
            YEAR(A.Fecha) AS ANO,
            A.Code AS COD_PROD,
            A.Name AS DESC_PROD,
            CAST(CASE WHEN A.CommercialOrders = 0 OR A.EstimatedSales = 0 THEN 0
                    ELSE ROUND(A.EstimatedSales * 1.0 / A.CommercialOrders, 4) END AS DECIMAL(10,4)) AS ESTI_PED,
            CAST(CASE WHEN A.CommercialOrders = 0 THEN 0
                    ELSE ROUND(P.InvoicingUnitsProjected * 1.0 / A.CommercialOrders, 4) END AS DECIMAL(10,4)) AS ENTREG_PED,
            A.EstimatedSales AS UNI_EST,
            CAST((1.0 * @TotalNM1 * FA.UNI_ACU_FACT_HASTA_DIA) / NULLIF(tnm1d.TotalNM1_Hoy,0) AS INT) AS UNI_PROY_INVOICING,
            A.[UNI FACT X DIA_PRECIO] AS [UNI FACT X DIA],
            FA.UNI_ACU_FACT_HASTA_DIA,
            NSD.UNI_CORTADAS_X_DIA,
            NSA.UNI_ACU_CORTADAS_HASTA_DIA,
            CD.DIA_CALENDARIO_REAL        AS  DIA_CALENDARIO_FACT,
            A.ProductType,
            A.Division,
            A.PrecioVenta
        FROM #Agregado A
        INNER JOIN #Proy P ON P.Code = A.Code AND P.PrecioVenta = A.PrecioVenta AND P.Fecha = A.Fecha
        INNER JOIN #FactAcum FA ON FA.Code = A.Code AND FA.PrecioVenta = A.PrecioVenta AND FA.Fecha = A.Fecha
        INNER JOIN #NoStockPorDia NSD ON NSD.Code = A.Code AND NSD.Fecha = A.Fecha 
        INNER JOIN #NoStockAcum NSA ON NSA.Code = A.Code AND NSA.Fecha = A.Fecha
        INNER JOIN #TotalNM1_HoyPorDia tnm1d ON tnm1d.Fecha = A.Fecha
        INNER JOIN #CalendarioDias CD ON CD.Fecha = A.Fecha

        ORDER BY A.Fecha, DIA_CALENDARIO_FACT, A.PrecioVenta;

        ------------------------------------------------------------
        -- Limpieza de tablas temporales
        ------------------------------------------------------------
        DROP TABLE #ItemsNormalizados;
        DROP TABLE #Agregado;
        DROP TABLE #CampaignDaily;
        DROP TABLE #CampaignCum;
        DROP TABLE #PriceCum;
        DROP TABLE #Proy;
        DROP TABLE #CalendarioDias;
        DROP TABLE #FactAcum;
        DROP TABLE #NoStockPorDia;
        DROP TABLE #NoStockAcum;
        DROP TABLE #Cortes;
        DROP TABLE #ZonasN_porDia;
        DROP TABLE #TotalNM1_HoyPorDia;
        DROP TABLE #FactN_Dia;
        DROP TABLE #FactN;
        DROP TABLE #NM1Totales;
        DROP TABLE #TotalesZonasN;



        """

    connection = getConnexion()
    try:
        with connection.cursor() as cursor:
            cursor.execute(query, (campaign_id, campaign_number))

            # Avanzar hasta el último SELECT con columnas
            while cursor.description is None and cursor.nextset():
                pass

            if cursor.description is None:
                print("No se encontró ningún SELECT final")
                return []

            columns = [col[0] for col in cursor.description]
            results = [dict(zip(columns, row)) for row in cursor.fetchall()]

             # 3 Si corresponde, guardar en queryfox_hist
            if is_save and results:
                print(" Guardando resultados en queryfox_hist...")
                insert_queryfox_hist(results, campaign_id, campaign_number)
                print(" Datos guardados en queryfox_hist correctamente.")

            return results
    except Exception as e:
        print("Error:", e)
        return []
    finally:
        connection.close()




def get_projected_orders(campaign_id: int):
    """
    Calcula los pedidos proyectados de una campaña.
    Retorna solo el último registro (día más reciente).
    """

    campaign_number = get_the_number_for_campaignid(campaign_id)

    if not campaign_number:
        print(f"No se encontró Number para campaign_id={campaign_id}")
        return None

    sql = """
        DECLARE @CCID INT = ?;
        DECLARE @campana VARCHAR(10) = ?;
        DECLARE @previousCampaignId INT = (
            SELECT TOP 1 Id
            FROM Campaign
            WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID)
        );

        ------------------------------------------------------------
        -- 1 Zonas que existen en N
        ------------------------------------------------------------
        SELECT DISTINCT o.Zone
        INTO #ZonasN
        FROM Orders o
        WHERE o.CampaignId = @CCID
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999);

        ------------------------------------------------------------
        -- 2 TotalesZonasN
        ------------------------------------------------------------
        SELECT DISTINCT CPL.LevelId
        INTO #TotalesZonasN
        FROM CampaignLevel CPL
        INNER JOIN Level LV ON LV.Id = CPL.LevelId
        INNER JOIN CampaignZona CZ ON CZ.CampaignLevelId = CPL.Id
        WHERE CPL.CampaignId = @CCID 
        AND LV.LevelTypeId = 6
        AND CZ.DateCC != '0001-01-01 00:00:00.0000000';

        ------------------------------------------------------------
        -- 3 Pedidos N
        ------------------------------------------------------------
        SELECT 
            CAST(o.InvoicingDate AS DATE) AS InvoicingDate,
            o.Zone,
            CAST(COUNT(o.id) AS BIGINT) AS Cantidad,
            SUM(CAST(COUNT(o.id) AS BIGINT)) OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS Acumulado
        INTO #N
        FROM Orders o
        WHERE o.CampaignId = @CCID
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999)
        GROUP BY CAST(o.InvoicingDate AS DATE), o.Zone;

        ------------------------------------------------------------
        -- 4 Pedidos N-1 (solo zonas de N)
        ------------------------------------------------------------
        SELECT
            CAST(o.InvoicingDate AS DATE) AS Fecha,
            o.Zone,
            CAST(COUNT(o.Id) AS BIGINT) AS Cantidad,
            SUM(CAST(COUNT(o.Id) AS BIGINT)) OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS Acumulado
        INTO #N1
        FROM Orders o
        INNER JOIN #ZonasN zn ON zn.Zone = o.Zone
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999)
        GROUP BY CAST(o.InvoicingDate AS DATE), o.Zone;

        ------------------------------------------------------------
        -- 5 TotalesN1
        ------------------------------------------------------------
        SELECT COUNT(o.id) AS TotalPedidos
        INTO #TotalesN1
        FROM Orders o
        INNER JOIN #ZonasN zn ON zn.Zone = o.Zone
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999);

        ------------------------------------------------------------
        -- 6 TotalesCampanaN1
        ------------------------------------------------------------
        SELECT COUNT(o.id) AS TotalPedidosNM1
        INTO #TotalesCampanaN1
        FROM Orders o
        INNER JOIN #TotalesZonasN TZN ON TZN.LevelId = o.Zone
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999);

        ------------------------------------------------------------
        -- 7 Total diario (todas las zonas) en N
        ------------------------------------------------------------
        SELECT 
            CAST(o.InvoicingDate AS DATE) AS Fecha,
            COUNT(o.Id) AS AcumuladoDia
        INTO #TotalesDia
        FROM Orders o
        WHERE o.CampaignId = @CCID
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997, 998, 999)
        GROUP BY CAST(o.InvoicingDate AS DATE);

        ------------------------------------------------------------
        -- 8 Crear índices de día para mapear campañas
        ------------------------------------------------------------
        SELECT DISTINCT
            CAST(o.InvoicingDate AS DATE) AS Fecha,
            DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
        INTO #DiasN
        FROM Orders o
        WHERE o.CampaignId = @CCID
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997,998,999);

        SELECT DISTINCT
            CAST(o.InvoicingDate AS DATE) AS Fecha,
            DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
        INTO #DiasN1
        FROM Orders o
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997,998,999);

        ------------------------------------------------------------
        -- 9 AcumuladoN1 (progresivo) mapeado por índice de día - versión segura
        ------------------------------------------------------------
        -- Paso 1: calcular cantidad diaria por índice de día en BIGINT
        SELECT 
            d1.DiaIdx,
            CAST(COUNT(o.Id) AS BIGINT) AS CantidadDia
        INTO #BaseN1PorDia
        FROM Orders o
        JOIN #DiasN1 d1 ON d1.Fecha = CAST(o.InvoicingDate AS DATE)
        INNER JOIN #ZonasN zn ON zn.Zone = o.Zone
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997,998,999)
        GROUP BY d1.DiaIdx;

        -- Paso 2: calcular acumulado progresivo
        SELECT 
            DiaIdx,
            SUM(CantidadDia) OVER (ORDER BY DiaIdx ROWS UNBOUNDED PRECEDING) AS AcumuladoActualNM1
        INTO #AcumuladoN1Idx
        FROM #BaseN1PorDia;


        ------------------------------------------------------------
        -- 10 AcumuladoNM1 (solo el total del día equivalente)
        ------------------------------------------------------------
        SELECT 
            d1.DiaIdx,
            COUNT(o.Id) AS AcumuladoDiaNM1
        INTO #TotalesDiaNM1
        FROM Orders o
        JOIN #DiasN1 d1 ON d1.Fecha = CAST(o.InvoicingDate AS DATE)
        INNER JOIN #ZonasN zn ON zn.Zone = o.Zone
        WHERE o.CampaignId = @previousCampaignId
        AND o.OrderStatus IN (5,6,7,8,10)
        AND o.Zone NOT IN (997,998,999)
        GROUP BY d1.DiaIdx;

        ------------------------------------------------------------
        -- 11 Resultado final (proyección corregida)
        ------------------------------------------------------------
        SELECT 
            @campana AS Campaña,
            a.InvoicingDate,
            a.Cantidad AS CantidadActual,
            a.Acumulado AS AcumuladoActual,
            td.AcumuladoDia,
            accn1.AcumuladoActualNM1,                  --  acumulado progresivo N-1 alineado
            tdn1.AcumuladoDiaNM1,                  --  total del día N-1
            t.TotalPedidos AS TotalPedidosN,
            t2.TotalPedidosNM1 AS TotalPedidosNM1,
            CAST(
            (CAST(t2.TotalPedidosNM1 AS BIGINT) * CAST(a.Acumulado AS BIGINT))
            / NULLIF(CAST(accn1.AcumuladoActualNM1 AS BIGINT), 0)
        AS INT) AS Proyeccion
        FROM #N a
        LEFT JOIN #TotalesDia td ON td.Fecha = a.InvoicingDate
        LEFT JOIN #DiasN dmap ON dmap.Fecha = a.InvoicingDate
        LEFT JOIN #AcumuladoN1Idx accn1 ON accn1.DiaIdx = dmap.DiaIdx
        LEFT JOIN #TotalesDiaNM1 tdn1 ON tdn1.DiaIdx = dmap.DiaIdx
        CROSS JOIN #TotalesN1 t
        CROSS JOIN #TotalesCampanaN1 t2 
        GROUP BY a.InvoicingDate, a.Cantidad, a.Acumulado, 
                td.AcumuladoDia, tdn1.AcumuladoDiaNM1,
                t.TotalPedidos, accn1.AcumuladoActualNM1, t2.TotalPedidosNM1
        ORDER BY a.InvoicingDate DESC
        OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

        ------------------------------------------------------------
        --  Limpieza de tablas temporales
        ------------------------------------------------------------
        DROP TABLE #ZonasN;
        DROP TABLE #TotalesZonasN;
        DROP TABLE #N;
        DROP TABLE #N1;
        DROP TABLE #TotalesN1;
        DROP TABLE #TotalesCampanaN1;
        DROP TABLE #TotalesDia;
        DROP TABLE #DiasN;
        DROP TABLE #DiasN1;
        DROP TABLE #AcumuladoN1Idx;
        DROP TABLE #TotalesDiaNM1;
        DROP TABLE #BaseN1PorDia;

        """

    connection = getConnexion()
    with connection.cursor() as cursor:
        cursor.execute(sql, (campaign_id, campaign_number))

        # Avanzar hasta el último conjunto de resultados (el SELECT final)
        while True:
            if cursor.description is not None:
                break
            if not cursor.nextset():
                break

        if cursor.description is None:
            print("⚠️ La consulta no devolvió resultados.")
            return None

        cols = [col[0] for col in cursor.description]
        row = cursor.fetchone()

    if not row:
        return None

    result = dict(zip(cols, row))

    # asegurar que venga como int en Python
    if result.get("Proyeccion") is not None:
        result["Proyeccion"] = int(result["Proyeccion"])

    # asegurar que Totales y Acumulados vengan como int
    if result.get("TotalCampañaN1") is not None:
        result["TotalCampañaN1"] = int(result["TotalCampañaN1"])
    if result.get("AcumuladoActual") is not None:
        result["AcumuladoActual"] = int(result["AcumuladoActual"])

    return result



def get_estimated_orders(campaign_id: int):
    """
    Trae EstimatedOrders de la campaña actual (N)
    y EstimatedOrders de la campaña anterior (N-1).
    """

    sql = """
        DECLARE @CCID INT = ?;

        DECLARE @previousCampaignId INT = (
            SELECT TOP 1 Id
            FROM Campaign
            WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID)
        );

        SELECT 
            C.EstimatedOrders,
            Cprev.EstimatedOrders AS EstimatedOrdersN1
        FROM Campaign C
        LEFT JOIN Campaign Cprev ON Cprev.Id = @previousCampaignId
        WHERE C.Id = @CCID;
    """

    connection = getConnexion()
    with connection.cursor() as cursor:
        cursor.execute(sql, (campaign_id,))
        row = cursor.fetchone()

    if not row:
        return {"EstimatedOrders": None, "EstimatedOrdersN1": None}

    cols = [col[0] for col in cursor.description]
    return dict(zip(cols, row))



def get_accumulated_units(campaign_id: int):
    """
    Devuelve las unidades del día, acumuladas y proyectadas para la campaña actual (N) y la anterior (N-1).
    Usa tablas temporales y es compatible con ejecución en conjunto con otras queries tipo 'query fox'.
    """

    # Obtener el Number de la campaña
    #print("🟢 campaign_id:", campaign_id)
    campaign_number = get_the_number_for_campaignid(campaign_id)
    #print("🔹 campaign_number:", campaign_number)
    if not campaign_number:
        print(f"⚠️ No se encontró Number para campaign_id={campaign_id}")
        return []

    query = """
    ------------------------------------------------------------
    -- Variables base
    ------------------------------------------------------------
    DECLARE @CCID INT = ?;
    DECLARE @campana VARCHAR(10) = ?;

    DECLARE @previousCampaignId INT = (
        SELECT TOP 1 Id
        FROM Campaign
        WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID)
    );


    ------------------------------------------------------------
    -- 1) Zonas activas en campaña N (para filtrar todo)
    ------------------------------------------------------------
    SELECT DISTINCT o.Zone
    INTO #ZonasN
    FROM Orders o
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    ------------------------------------------------------------
    -- 2) Índice de días de N y N-1 (para alinear día N con día N-1)
    ------------------------------------------------------------
    SELECT DISTINCT
        CAST(o.InvoicingDate AS DATE) AS Fecha,
        DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
    INTO #DiasN
    FROM Orders o
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    SELECT DISTINCT
        CAST(o.InvoicingDate AS DATE) AS Fecha,
        DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
    INTO #DiasN1
    FROM Orders o
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    ------------------------------------------------------------
    -- 3) Último día (Fecha y DiaIdx) de N y el día equivalente en N-1
    ------------------------------------------------------------
    SELECT MAX(DiaIdx) AS UltimoDiaIdxN
    INTO #UltimoDiaIdxN
    FROM #DiasN;

    SELECT d.Fecha
    INTO #UltimaFechaN
    FROM #DiasN d
    JOIN #UltimoDiaIdxN u ON u.UltimoDiaIdxN = d.DiaIdx;

    -- Fecha equivalente en N-1 (mismo índice de día)
    SELECT d1.Fecha
    INTO #UltimaFechaN1
    FROM #DiasN1 d1
    JOIN #UltimoDiaIdxN u ON u.UltimoDiaIdxN = d1.DiaIdx;

    ------------------------------------------------------------
    -- 4) UnitsOfDay (N) = Unidades del último día de N
    ------------------------------------------------------------
    SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsOfDay
    INTO #UnitsDiaN
    FROM Orders o
    JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 5) UnitsOfDayNM1 (N-1) = Unidades del día equivalente,
    --    solo en zonas que existen en N
    ------------------------------------------------------------
    SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsOfDayNM1
    INTO #UnitsDiaN1
    FROM Orders o
    JOIN #UltimaFechaN1 uf1 ON CAST(o.InvoicingDate AS DATE) = uf1.Fecha
    JOIN #ZonasN zn ON zn.Zone = o.Zone
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 6) UnitsAccumulatedN = Unidades acumuladas de N (todas las fechas)
    ------------------------------------------------------------
    SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsAccumulatedN
    INTO #UnitsN
    FROM Orders o
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 7) UnitsAccumulatedN1 = Unidades acumuladas de N-1,
    --    solo para zonas que existen en N
    ------------------------------------------------------------
    SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS UnitsAccumulatedN1
    INTO #UnitsN1
    FROM Orders o
    JOIN #ZonasN zn ON zn.Zone = o.Zone
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 8) TotalUnidadesNM1 = Total de la campaña N-1 (todas las zonas válidas)
    --    (igual que "TotalPedidosNM1" en pedidos)
    ------------------------------------------------------------
    SELECT CAST(SUM(oi.Quantity) AS BIGINT) AS TotalUnidadesNM1
    INTO #TotalesCampanaUnidadesN1
    FROM Orders o
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 9) Resultado + Proyección de unidades
    --    Proyección = TotalUnidadesNM1 * UnitsAccumulatedN / UnitsAccumulatedN1
    ------------------------------------------------------------
    SELECT 
        @campana AS Campaña,
        (SELECT Fecha FROM #UltimaFechaN)  AS UltimaFechaN,
        (SELECT UnitsOfDay FROM #UnitsDiaN)    AS UnitsOfDay,
        (SELECT Fecha FROM #UltimaFechaN1) AS UltimaFechaN1,
        (SELECT UnitsOfDayNM1 FROM #UnitsDiaN1) AS UnitsOfDayNM1,
        (SELECT UnitsAccumulatedN  FROM #UnitsN)  AS UnitsAccumulatedN,
        (SELECT UnitsAccumulatedN1 FROM #UnitsN1) AS UnitsAccumulatedN1,
        (SELECT TotalUnidadesNM1   FROM #TotalesCampanaUnidadesN1) AS TotalUnidadesNM1,
        CAST(
            (CAST((SELECT TotalUnidadesNM1 FROM #TotalesCampanaUnidadesN1) AS BIGINT) 
            * CAST((SELECT UnitsAccumulatedN FROM #UnitsN) AS BIGINT))
            / NULLIF(CAST((SELECT UnitsAccumulatedN1 FROM #UnitsN1) AS BIGINT), 0)
        AS INT) AS ProjectedUnits;

    ------------------------------------------------------------
    -- 10) Limpieza
    ------------------------------------------------------------
    DROP TABLE #ZonasN;
    DROP TABLE #DiasN;
    DROP TABLE #DiasN1;
    DROP TABLE #UltimoDiaIdxN;
    DROP TABLE #UltimaFechaN;
    DROP TABLE #UltimaFechaN1;
    DROP TABLE #UnitsDiaN;
    DROP TABLE #UnitsDiaN1;
    DROP TABLE #UnitsN;
    DROP TABLE #UnitsN1;
    DROP TABLE #TotalesCampanaUnidadesN1;

    """

    connection = getConnexion()
    try:
        with connection.cursor() as cursor:
            cursor.execute(query, (campaign_id, campaign_number))

            # Avanzar hasta el SELECT final
            while cursor.description is None and cursor.nextset():
                pass

            if cursor.description is None:
                print(" No se encontró ningún SELECT final con datos.")
                return []

            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
            results = [dict(zip(columns, row)) for row in rows]
            print(" Resultados obtenidos correctamente.")
            return results

    except Exception as e:
        print(" Error en get_accumulated_units:", e)
        return []
    finally:
        connection.close()


def get_accumulated_invoicing(campaign_id: int):
    """
    Calcula la facturación del día, acumulada y proyectada
    para la campaña actual (N) y la anterior (N-1),
    excluyendo productos NO COMPUTABLES (pt.Id != 13).
    """

    campaign_number = get_the_number_for_campaignid(campaign_id)
    if not campaign_number:
        print(f"⚠️ No se encontró Number para campaign_id={campaign_id}")
        return []

    query = """
    ------------------------------------------------------------
    -- Variables base
    ------------------------------------------------------------
    DECLARE @CCID INT = ?;
    DECLARE @campana VARCHAR(10) = ?;

    DECLARE @previousCampaignId INT = (
        SELECT TOP 1 Id
        FROM Campaign
        WHERE Sequence = (SELECT Sequence - 1 FROM Campaign WHERE Id = @CCID)
    );

     ------------------------------------------------------------
    -- 1) Zonas activas en campaña N (para filtrar)
    ------------------------------------------------------------
    SELECT DISTINCT o.Zone
    INTO #ZonasN
    FROM Orders o
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    ------------------------------------------------------------
    -- 2) Índices de días (N y N-1)
    ------------------------------------------------------------
    SELECT DISTINCT
        CAST(o.InvoicingDate AS DATE) AS Fecha,
        DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
    INTO #DiasN
    FROM Orders o
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    SELECT DISTINCT
        CAST(o.InvoicingDate AS DATE) AS Fecha,
        DENSE_RANK() OVER (ORDER BY CAST(o.InvoicingDate AS DATE)) AS DiaIdx
    INTO #DiasN1
    FROM Orders o
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999);

    ------------------------------------------------------------
    -- 3) Último día de N y fecha equivalente de N-1
    ------------------------------------------------------------
    SELECT MAX(DiaIdx) AS UltimoDiaIdxN INTO #UltimoDiaIdxN FROM #DiasN;

    SELECT d.Fecha INTO #UltimaFechaN
    FROM #DiasN d JOIN #UltimoDiaIdxN u ON u.UltimoDiaIdxN = d.DiaIdx;

    SELECT d1.Fecha INTO #UltimaFechaN1
    FROM #DiasN1 d1 JOIN #UltimoDiaIdxN u ON u.UltimoDiaIdxN = d1.DiaIdx;

    ------------------------------------------------------------
    -- 4) Facturación del día actual (N)
    ------------------------------------------------------------
    SELECT CAST(SUM(
        ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
        + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0)
    ) AS DECIMAL(18,2)) AS InvoicingOfDay
    INTO #FactDiaN
    FROM Orders o
    JOIN #UltimaFechaN uf ON CAST(o.InvoicingDate AS DATE) = uf.Fecha
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 5) Facturación del día equivalente (N-1)
    ------------------------------------------------------------
    SELECT CAST(SUM(
        ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
        + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0)
    ) AS DECIMAL(18,2)) AS InvoicingOfDayN1
    INTO #FactDiaN1
    FROM Orders o
    JOIN #UltimaFechaN1 uf1 ON CAST(o.InvoicingDate AS DATE) = uf1.Fecha
    JOIN #ZonasN zn ON zn.Zone = o.Zone
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 6) Facturación acumulada N
    ------------------------------------------------------------
    SELECT CAST(SUM(
        ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
        + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0)
    ) AS DECIMAL(18,2)) AS InvoicingAccumulatedN
    INTO #FactN
    FROM Orders o
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @CCID
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 7) Facturación acumulada N-1 (solo zonas válidas de N)
    ------------------------------------------------------------
    SELECT CAST(SUM(
        ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
        + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0)
    ) AS DECIMAL(18,2)) AS InvoicingAccumulatedN1
    INTO #FactN1
    FROM Orders o
    JOIN #ZonasN zn ON zn.Zone = o.Zone
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 8) Total Facturación N-1 (todas las zonas válidas)
    ------------------------------------------------------------
    SELECT CAST(SUM(
        ISNULL(oi.PromotionPriceQuantity,0) * ISNULL(oi.PromotionPrice,0)
        + (oi.Quantity - ISNULL(oi.PromotionPriceQuantity,0)) * ISNULL(oi.Price,0)
    ) AS DECIMAL(18,2)) AS TotalFacturacionN1
    INTO #TotalesFacturacionN1
    FROM Orders o
    JOIN OrderItem oi ON oi.OrderId = o.Id
    JOIN CampaignProduct cp ON cp.Id = oi.CampaignProductId
    JOIN ProductType pt ON pt.Id = cp.ProductTypeId
    WHERE o.CampaignId = @previousCampaignId
    AND o.OrderStatus IN (5,6,7,8,10)
    AND o.Zone NOT IN (997,998,999)
    AND oi.PaymentCycle = 1
    AND oi.IsReturn = 0
    AND oi.IsHidden = 0
    AND oi.IsPreviousNoStock = 0
    AND pt.Id != 13;

    ------------------------------------------------------------
    -- 9) Resultado y proyección
    ------------------------------------------------------------
    SELECT 
       @campana AS Campaña,
    (SELECT Fecha FROM #UltimaFechaN)  AS UltimaFechaN,
    (SELECT InvoicingOfDay FROM #FactDiaN) AS FacturacionDia,
    (SELECT InvoicingOfDayN1 FROM #FactDiaN1) AS FacturacionDiaN1,
    (SELECT InvoicingAccumulatedN FROM #FactN) AS FacturacionAcumuladaN,
    (SELECT InvoicingAccumulatedN1 FROM #FactN1) AS FacturacionAcumuladaN1_Comparable,
    (SELECT TotalFacturacionN1 FROM #TotalesFacturacionN1) AS TotalFacturacionN1,
    CAST(
        (CAST((SELECT TotalFacturacionN1 FROM #TotalesFacturacionN1) AS DECIMAL(18,2)) 
        * CAST((SELECT InvoicingAccumulatedN FROM #FactN) AS DECIMAL(18,2)))
        / NULLIF(CAST((SELECT InvoicingAccumulatedN1 FROM #FactN1) AS DECIMAL(18,2)), 0)
    AS DECIMAL(18,2)) AS FacturacionProyectada;

    ------------------------------------------------------------
    -- Limpieza
    ------------------------------------------------------------
    DROP TABLE #ZonasN;
    DROP TABLE #DiasN;
    DROP TABLE #DiasN1;
    DROP TABLE #UltimoDiaIdxN;
    DROP TABLE #UltimaFechaN;
    DROP TABLE #UltimaFechaN1;
    DROP TABLE #FactDiaN;
    DROP TABLE #FactDiaN1;
    DROP TABLE #FactN;
    DROP TABLE #FactN1;
    DROP TABLE #TotalesFacturacionN1;
    """

    connection = getConnexion()
    with connection.cursor() as cursor:
        cursor.execute(query, (campaign_id, campaign_number))
        while cursor.description is None and cursor.nextset():
            pass
        if cursor.description is None:
            print("⚠️ Sin resultados de facturación.")
            return []
        columns = [col[0] for col in cursor.description]
        rows = cursor.fetchall()
        return [dict(zip(columns, row)) for row in rows]



## para guardado query fox


def get_current_campaign():
    """
    Devuelve la campaña activa según la fecha actual.
    Busca en la tabla Campaign usando la conexión externa.
    """
    connection = getConnexion()
    today = now().strftime("%Y-%m-%d")

    query = """
        SELECT TOP 1 Id, Number, StartDatePick, EndDateCC, EndDatePick, Sequence
        FROM Campaign
        WHERE CAST(GETDATE() AS date) BETWEEN CAST(StartDatePick AS date) AND CAST(EndDatePick AS date)
        ORDER BY Sequence DESC
    """

    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            row = cursor.fetchone()

            if not row:
                print(" No se encontró ninguna campaña activa.")
                return None

            campaign = {
                "Id": row[0],
                "Number": row[1],
                "StartDatePick": row[2],
                "EndDateCC": row[3],
                "EndDatePick": row[4],
                "Sequence": row[5],
            }
            return campaign

    except Exception as e:
        print(" Error en get_current_campaign:", e)
        return None
    finally:
        connection.close()

def is_last_day_of_campaign_id(campaign_id: int) -> bool:
    """
    Valida en DB si HOY (según hora de Argentina) es el EndDateCC
    de la campaña con Id = campaign_id.
    Usa SYSDATETIMEOFFSET() con AT TIME ZONE para evitar diferencias horarias.
    """
    # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    # FORZAR FECHA DE PRUEBA (solo para test manual)
    forced_today = "2025-10-28"   # ← la fecha EndDateCC que querés simular
    use_forced_date = False
        # ← poné False para modo normal
    # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    #  Armar el SQL según el modo
    if use_forced_date:
        sql = f"""
            SELECT CASE 
                     WHEN CAST(EndDateCC AS date) = CAST('{forced_today}' AS date)
                     THEN 1 ELSE 0 
                   END AS IsLastDay
            FROM Campaign
            WHERE Id = ?
        """
    else:
        sql = """
            SELECT CASE 
                     WHEN CAST(EndDateCC AS date) = 
                          CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AT TIME ZONE 'Argentina Standard Time' AS date)
                     THEN 1 ELSE 0 
                   END AS IsLastDay
            FROM Campaign
            WHERE Id = ?
        """

    conn = getConnexion()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, (campaign_id,))
            row = cur.fetchone()
            if not row:
                print(f"No existe Campaign.Id = {campaign_id}")
                return False

            is_last = bool(row[0])
            if is_last:
                print(f"Hoy (hora Argentina) es EndDateCC para Campaign.Id={campaign_id}.")
            else:
                print(f" Hoy (hora Argentina) NO es EndDateCC para Campaign.Id={campaign_id}.")
            return is_last

    except Exception as e:
        print(" Error en is_last_day_of_campaign_id:", e)
        return False
    finally:
        conn.close()

def save_foxquery_service(campaign_id):
    is_save = True
    #print(f"Llegamos hasta el paso 3. campaign_id = {campaign_id}, is_save = {is_save}")
    get_queryfoxservice(campaign_id, is_save)
    return True
 
def insert_queryfox_hist(results, campaign_id, campaign_number):
    """
    Inserta los resultados de get_queryfoxservice en la tabla queryfox_hist.
    """
    try:
        # Verificar si la campaña ya existe usando la función externa
        if check_closed_campaign(campaign_id):
            #print(f"Eliminando registros previos de queryfox_hist para CampaignId={campaign_id}...")
            queryfox_hist.objects.filter(CampaignId=campaign_id).delete()

        # Si no hay resultados, evitar insertar nada
        if not results:
            #print(f"⚠️ No hay resultados para insertar en CampaignId={campaign_id}.")
            return False

        # Inserción masiva en una transacción segura
        with transaction.atomic():
            bulk_objs = [
                queryfox_hist(
                    CampaignId=campaign_id,
                    CampaignNumber=campaign_number,  # corregido (antes: CamapignNumber)
                    AA=row.get("AA"),
                    CC=row.get("CC"),
                    DIA=row.get("DIA"),
                    MES=row.get("MES"),
                    ANO=row.get("ANO"),
                    COD_PROD=row.get("COD_PROD"),
                    DESC_PROD=row.get("DESC_PROD"),
                    ESTI_PED=row.get("ESTI_PED"),
                    ENTREG_PED=row.get("ENTREG_PED"),
                    UNI_EST=row.get("UNI_EST"),
                    UNI_PROY_INVOICING=row.get("UNI_PROY_INVOICING"),
                    PORC_DESCUENTO=row.get("PORC_DESCUENTO", 0),
                    UNI_FACT_X_DIA=row.get("UNI FACT X DIA"),
                    UNI_ACU_FACT_HASTA_DIA=row.get("UNI_ACU_FACT_HASTA_DIA"),
                    UNI_CORTADAS_X_DIA=row.get("UNI_CORTADAS_X_DIA"),
                    UNI_ACU_CORTADAS_HASTA_DIA=row.get("UNI_ACU_CORTADAS_HASTA_DIA"),
                    ANT_DIA=row.get("ANT_DIA", 0),
                    ANT_ACUM=row.get("ANT_ACUM", 0),
                    ANT_NODIA=row.get("ANT_NODIA", 0),
                    ANT_NOACUM=row.get("ANT_NOACUM", 0),
                    DIA_CALENDARIO_FACT=row.get("DIA_CALENDARIO_FACT"),
                    TIPO_PROD=row.get("ProductType"),
                    DIVISION_DE_NEGOCIO=row.get("Division"),
                    PRECIO_VENTA_SIN_DESCUENTO=row.get("PrecioVenta"),
                )
                for row in results
            ]

            queryfox_hist.objects.bulk_create(bulk_objs, batch_size=500)
            #print(f"insert_queryfox_hist: {len(bulk_objs)} registros insertados para CampaignId={campaign_id}.")

        return True

    except Exception as e:
        #print(f"Error en insert_queryfox_hist para CampaignId={campaign_id}: {e}")
        return False
    


def check_closed_campaign(campaign_id: int) -> bool:
    """
    Verifica si una campaña ya fue procesada y almacenada en queryfox_hist.
    Retorna True si existen registros, False si no.
    """
    exists = queryfox_hist.objects.filter(CampaignId=campaign_id).exists()
    if exists:
        print(f"La campaña {campaign_id} ya fue cerrada (existe en queryfox_hist).")
    else:
        print(f" La campaña {campaign_id} aún no fue cerrada.")
    return exists


def get_queryfoxservice_hist(campaign_id: int):
    """
    Devuelve los registros de queryfox_hist para una campaña, 
    adaptados con las mismas claves que espera el exportador Excel.
    """
    try:
        registros = queryfox_hist.objects.filter(CampaignId=campaign_id).values()
        if not registros:
            #print(f"⚠️ No se encontraron datos en queryfox_hist para CampaignId={campaign_id}.")
            return []

        data_excel = []
        for r in registros:
            item = {
                'AA': r['AA'],
                'CC': r['CC'],
                'DIA': r['DIA'],
                'MES': r['MES'],
                'ANO': r['ANO'],
                'COD_PROD': r['COD_PROD'],
                'DESC_PROD': r['DESC_PROD'],
                'ESTI_PED': float(r['ESTI_PED']) if r['ESTI_PED'] is not None else 0,
                'ENTREG_PED': float(r['ENTREG_PED']) if r['ENTREG_PED'] is not None else 0,
                'UNI_EST': r['UNI_EST'],
                'UNI_PROY_INVOICING': r['UNI_PROY_INVOICING'],
                'UNI FACT X DIA': r['UNI_FACT_X_DIA'],
                'UNI_ACU_FACT_HASTA_DIA': r['UNI_ACU_FACT_HASTA_DIA'],
                'UNI_CORTADAS_X_DIA': r['UNI_CORTADAS_X_DIA'],
                'UNI_ACU_CORTADAS_HASTA_DIA': r['UNI_ACU_CORTADAS_HASTA_DIA'],
                'DIA_CALENDARIO_FACT': r['DIA_CALENDARIO_FACT'],
                'ProductType': r['TIPO_PROD'],
                'Division': r['DIVISION_DE_NEGOCIO'],
                'PrecioVenta': float(r['PRECIO_VENTA_SIN_DESCUENTO']) if r['PRECIO_VENTA_SIN_DESCUENTO'] is not None else 0,
            }
            data_excel.append(item)

        #print(f"Se recuperaron {len(data_excel)} registros históricos de queryfox_hist.")
        return data_excel

    except Exception as e:
        #print("❌ Error en get_queryfoxservice_HIST:", e)
        return []