namespace MMDAL.Query.SalesDB
{
    public static class SalesDBQB
    {
        // Legacy GB4 sourced this from a pre-aggregated `fsale` fact table with no ETL pipeline in
        // this codebase (confirmed absent) — computed live here from TMMHEAD/TMMDETAIL instead, same
        // decision as the OU-Wise GST Dashboard. Sales-vs-Sales-Return resolved via
        // MBIZTRANSACTIONCLASS.BIZTRANSACTIONCLASSNAME (not fsale's pre-split columns, which don't
        // exist here). Quantity column is TRANSACTIONQUANTITY (TMMDETAIL has no bare QUANTITY column
        // on this schema — confirmed live; matches the convention already used in MMReportsQB).
        // NumberConversion scaling (0=Lakh,1=Million,2=Crore) matches the PayRoll HRMS precedent
        // (EmployeeQB.GET_HRMS_PAY_COST_BY_MONTH).
        #region Period-bucketed (calendar month) sales detail, with a Total row (DetailType=9)
        public const string GET_SALES_DETAIL = @"
;WITH BillTotals AS (
    SELECT
        h.DOCUMENTID, h.PARTYID,
        DATEFROMPARTS(YEAR(h.DOCUMENTDATE), MONTH(h.DOCUMENTDATE), 1)  AS PeriodFrom,
        EOMONTH(h.DOCUMENTDATE)                                        AS PeriodTo,
        CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 1 ELSE 0 END AS IsReturn,
        SUM(d.ITEMNETVALUE)         AS BillValue,
        SUM(d.TRANSACTIONQUANTITY)  AS BillQuantity,
        SUM(d.ITEMCOSTVALUE)        AS BillCogs,
        COUNT(d.DOCUMENTDETAILID)   AS LineItemCount
    FROM TMMHEAD h
    JOIN TMMDETAIL d              ON d.DOCUMENTID = h.DOCUMENTID
    JOIN MBIZTRANSACTIONTYPE bt   ON bt.BIZTRANSACTIONTYPEID = h.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS bc  ON bc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID
    WHERE bc.BIZTRANSACTIONCLASSNAME LIKE 'Sales%'
      AND (@OuId IS NULL OR h.OUID = @OuId)
      AND h.DOCUMENTDATE >= @PeriodFromDate
      AND h.DOCUMENTDATE <= @PeriodToDate
    GROUP BY h.DOCUMENTID, h.PARTYID, h.DOCUMENTDATE,
             CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 1 ELSE 0 END
),
RawBucketed AS (
    SELECT
        1 AS DetailType,
        FORMAT(PeriodFrom, 'MMM yyyy')                                     AS Particulars,
        PeriodFrom AS FromDate, PeriodTo AS ToDate,
        SUM(CASE WHEN IsReturn = 0 THEN BillValue ELSE 0 END)              AS SalesValue,
        SUM(CASE WHEN IsReturn = 0 THEN BillQuantity ELSE 0 END)           AS SalesQuantity,
        SUM(CASE WHEN IsReturn = 0 THEN BillCogs ELSE 0 END)               AS Cogs,
        COUNT(CASE WHEN IsReturn = 0 THEN DOCUMENTID END)                  AS NumberOfBills,
        COUNT(DISTINCT CASE WHEN IsReturn = 0 THEN PARTYID END)            AS NumberOfCustomers,
        SUM(CASE WHEN IsReturn = 0 THEN LineItemCount ELSE 0 END)          AS TotalLineItems,
        MIN(CASE WHEN IsReturn = 0 THEN BillValue END)                    AS MinBillValue,
        MAX(CASE WHEN IsReturn = 0 THEN BillValue END)                    AS MaxBillValue,
        SUM(CASE WHEN IsReturn = 1 THEN BillValue ELSE 0 END)              AS SalesReturnValue,
        SUM(CASE WHEN IsReturn = 1 THEN BillQuantity ELSE 0 END)           AS SalesReturnQuantity
    FROM BillTotals
    GROUP BY PeriodFrom, PeriodTo
),
UnionRaw AS (
    SELECT * FROM RawBucketed
    UNION ALL
    SELECT
        9, 'Total', MIN(FromDate), MAX(ToDate),
        SUM(SalesValue), SUM(SalesQuantity), SUM(Cogs), SUM(NumberOfBills), SUM(NumberOfCustomers),
        SUM(TotalLineItems), MIN(MinBillValue), MAX(MaxBillValue), SUM(SalesReturnValue), SUM(SalesReturnQuantity)
    FROM RawBucketed
)
SELECT
    DetailType, Particulars, FromDate, ToDate,
    SalesValue, SalesValue AS SalesRealisation, SalesQuantity, Cogs,
    (SalesValue - Cogs) AS GrossMarginValue,
    CAST(ROUND((SalesValue - Cogs) / NULLIF(SalesValue,0) * 100.0, 2) AS DECIMAL(8,2)) AS GmPercentage,
    NumberOfBills AS NumberoOfBills, NumberOfCustomers,
    ISNULL(SalesValue / NULLIF(NumberOfBills,0), 0)                                    AS AverageBillValue,
    0                                                                                   AS NumberOfCancelBills,
    ISNULL(CAST(TotalLineItems AS DECIMAL(18,2)) / NULLIF(NumberOfBills,0), 0)          AS AverageNoOfLineItems,
    ISNULL(MinBillValue,0) AS MinBillValue, ISNULL(MaxBillValue,0) AS MaxBillValue,
    SalesReturnValue, SalesReturnQuantity,
    (SalesValue - SalesReturnValue)                                                    AS NetSalesValue,
    (SalesQuantity - SalesReturnQuantity)                                              AS NetSalesQuantity,
    Cogs                                                                               AS NetCogs,
    CAST(ROUND(SalesValue         / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesValueShort,
    CAST(ROUND(SalesValue         / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesRealisationShort,
    CAST(ROUND(Cogs               / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS CogsShort,
    CAST(ROUND((SalesValue-Cogs)  / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS GrossMarginValueShort,
    CAST(ROUND(SalesReturnValue   / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesReturnValueShort,
    CAST(ROUND((SalesValue-SalesReturnValue) / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS NetSalesValueShort
FROM UnionRaw
ORDER BY DetailType, FromDate";
        #endregion

        #region Flat OU-level sales summary (no period bucketing, matches legacy's flat grouping)
        public const string GET_OU_LEVEL = @"
;WITH BillTotals AS (
    SELECT
        h.DOCUMENTID, h.PARTYID, h.OUID,
        CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 1 ELSE 0 END AS IsReturn,
        SUM(d.ITEMNETVALUE)         AS BillValue,
        SUM(d.TRANSACTIONQUANTITY)  AS BillQuantity,
        SUM(d.ITEMCOSTVALUE)        AS BillCogs,
        COUNT(d.DOCUMENTDETAILID)   AS LineItemCount
    FROM TMMHEAD h
    JOIN TMMDETAIL d              ON d.DOCUMENTID = h.DOCUMENTID
    JOIN MBIZTRANSACTIONTYPE bt   ON bt.BIZTRANSACTIONTYPEID = h.BIZTRANSACTIONTYPEID
    JOIN MBIZTRANSACTIONCLASS bc  ON bc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID
    WHERE bc.BIZTRANSACTIONCLASSNAME LIKE 'Sales%'
      AND (@OuId IS NULL OR h.OUID = @OuId)
      AND h.DOCUMENTDATE >= @PeriodFromDate
      AND h.DOCUMENTDATE <= @PeriodToDate
    GROUP BY h.DOCUMENTID, h.PARTYID, h.OUID,
             CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 1 ELSE 0 END
),
RawOU AS (
    SELECT
        ou.ORGANIZATIONUNITCODE                                            AS OuCode,
        SUM(CASE WHEN b.IsReturn = 0 THEN b.BillValue ELSE 0 END)          AS SalesValue,
        SUM(CASE WHEN b.IsReturn = 0 THEN b.BillQuantity ELSE 0 END)       AS SalesQuantity,
        SUM(CASE WHEN b.IsReturn = 0 THEN b.BillCogs ELSE 0 END)           AS Cogs,
        COUNT(CASE WHEN b.IsReturn = 0 THEN b.DOCUMENTID END)              AS NumberOfBills,
        COUNT(DISTINCT CASE WHEN b.IsReturn = 0 THEN b.PARTYID END)        AS NumberOfCustomers,
        SUM(CASE WHEN b.IsReturn = 0 THEN b.LineItemCount ELSE 0 END)      AS TotalLineItems,
        MIN(CASE WHEN b.IsReturn = 0 THEN b.BillValue END)                 AS MinBillValue,
        MAX(CASE WHEN b.IsReturn = 0 THEN b.BillValue END)                 AS MaxBillValue,
        SUM(CASE WHEN b.IsReturn = 1 THEN b.BillValue ELSE 0 END)          AS SalesReturnValue,
        SUM(CASE WHEN b.IsReturn = 1 THEN b.BillQuantity ELSE 0 END)       AS SalesReturnQuantity
    FROM BillTotals b
    JOIN MORGANIZATIONUNIT ou ON ou.OUID = b.OUID
    GROUP BY ou.ORGANIZATIONUNITCODE
)
SELECT
    OuCode,
    SalesValue, SalesValue AS SalesRealisation, SalesQuantity, Cogs,
    (SalesValue - Cogs) AS GrossMarginValue,
    CAST(ROUND((SalesValue - Cogs) / NULLIF(SalesValue,0) * 100.0, 2) AS DECIMAL(8,2)) AS GmPercentage,
    NumberOfBills, NumberOfCustomers,
    ISNULL(SalesValue / NULLIF(NumberOfBills,0), 0)                                    AS AverageBillValue,
    0                                                                                   AS NumberOfCancelBills,
    ISNULL(CAST(TotalLineItems AS DECIMAL(18,2)) / NULLIF(NumberOfBills,0), 0)          AS AverageNoOfLineItems,
    ISNULL(MinBillValue,0) AS MinBillValue, ISNULL(MaxBillValue,0) AS MaxBillValue,
    SalesReturnValue, SalesReturnQuantity,
    (SalesValue - SalesReturnValue)                                                    AS NetSaleValue,
    (SalesQuantity - SalesReturnQuantity)                                              AS NetSalesQuantity,
    Cogs                                                                               AS NetCogs,
    CAST(ROUND(SalesValue         / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesValueShort,
    CAST(ROUND(SalesValue         / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesRealisationShort,
    CAST(ROUND(Cogs               / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS CogsShort,
    CAST(ROUND((SalesValue-Cogs)  / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS GrossMarginValueShort,
    CAST(ROUND(SalesReturnValue   / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS SalesReturnValueShort,
    CAST(ROUND((SalesValue-SalesReturnValue) / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END), 2) AS VARCHAR(50)) AS NetSalesValueShort
FROM RawOU
ORDER BY OuCode";
        #endregion
    }
}