namespace MMDAL.Query.VendorComparison
{
    // Ported from GB4 MMHeadQueryBuilder (d:\GB4 Service\DAL\MMDAL\Query\MM\MMHeadQueryBuilder.cs,
    // GET_VENDOR_COMPARISON_DATA / _TEMP / _CHARGES, UPDATE_VENDOR_DATA — lines 13989-14316).
    // Same join/filter semantics; GB4's raw string ".Replace(\":x\", value)" parameter substitution
    // (a SQL-injection-shaped pattern) is replaced with real Dapper @parameters throughout.
    //
    // @OUId was declared and set in GB4's version but never actually referenced anywhere in its
    // WHERE clause — confirmed by reading the full original SQL — so it's omitted here entirely
    // rather than carried forward as dead plumbing.
    public static class VendorComparisonQB
    {
        // Core comparison rows — one per {Item, Sku, Party}. Used directly for Type=1 (Detail);
        // Type=0 (Summary) reduces these same rows in-memory in VendorComparisonBLL, mirroring
        // GB4's own in-memory grouping rather than a second SQL query.
        //
        // V1/V2/V3/V4/BestFit/Status(grid)/% grid columns are intentionally not selected — see
        // VendorComparisonDetailDTO.cs comment; they were never implemented in GB4.
        public const string GET_VENDOR_COMPARISON_DETAIL = @"
DECLARE @ASON DATE = CAST(GETDATE() AS DATE);

SELECT
    CASE WHEN @DocumentId = -1 THEN RANK() OVER (ORDER BY A.itemcode, B.skucode)
         ELSE ID.slno END                                                    AS ItemSlNo,
    A.itemid                                                                 AS ItemId,
    A.itemcode                                                               AS ItemCode,
    A.itemname                                                               AS ItemName,
    B.skuid                                                                  AS SkuId,
    B.skucode                                                                AS SkuCode,
    B.skuname                                                                AS SkuName,
    ID.transactionquantity                                                   AS Quantity,
    ID.Remarks                                                               AS Remarks,
    ROW_NUMBER() OVER (PARTITION BY A.itemid, B.skuid ORDER BY D.partyid)    AS SlNo,
    D.partyid                                                                AS PartyId,
    D.partycode                                                              AS PartyCode,
    D.partyname                                                              AS PartyName,
    C.itempartyid                                                            AS ItemPartyId,
    C.itempartycode                                                          AS ItemPartyCode,
    C.itempartyname                                                         AS ItemPartyName,
    C.leaddays                                                               AS LeadDays,
    C.lotsize                                                                AS LotSize,
    C.deliveryrank                                                           AS DeliveryRank,
    C.moq                                                                    AS Moq,
    C.overallrank                                                            AS OverallRank,
    C.partystatus                                                            AS PartyStatus,
    CASE C.partystatus
        WHEN 0 THEN 'PENDING' WHEN 1 THEN 'TRIAL'
        WHEN 2 THEN 'APPROVED' WHEN 3 THEN 'REJECTED' END                    AS TxtPartyStatus,
    C.qualityrank                                                            AS QualityRank,
    C.raterank                                                               AS RateRank,
    C.sharepercentage                                                        AS SharePercentage,
    CASE C.modeofdespatch
        WHEN 0 THEN 'AIR' WHEN 1 THEN 'SEA' WHEN 2 THEN 'ROAD'
        WHEN 3 THEN 'TRAIN' WHEN 4 THEN 'PERSON' END                         AS TxtModeOfDispatch,
    C.transitdays                                                            AS TransitDays,
    C.status                                                                 AS Status,
    CP.effectivefrom                                                        AS EffectiveFrom,
    CP.effectiveto                                                           AS EffectiveTo,
    CP.rate                                                                  AS Rate,
    CP.netcost                                                               AS NetCost,
    CP.mrprate                                                               AS MrpRate,
    CP.currencyid                                                            AS CurrencyId,
    CR.currencycode                                                          AS CurrencyCode,
    CR.currencyname                                                         AS CurrencyName,
    CP.documentdetailid                                                      AS DocumentDetailId,
    uom.uomid                                                                AS UomId,
    uom.uomcode                                                              AS UomCode,
    uom.uomname                                                              AS UomName,
    PARTYDTLMMDTL.Remarks                                                    AS PartyDetailRemarks,
    mr.REASONNAME                                                            AS ReasonName,
    ID.DocumentNumber                                                        AS DocumentNumber,
    ID.REMARKS                                                               AS HeadRemarks,
    ID.DELIVERYINSTRUCTIONS                                                  AS DeliveryInstructions,
    p.PAYMENTTERMNAME                                                        AS PaymentTerms,
    pp.PENDINGQUANTITY                                                       AS PendingQuantity,
    pp.DOCUMENTNUMBER                                                        AS QuotationDocumentNumber,
    pp.OBJECTHEADERID                                                        AS QuotationDocumentId,
    PARTYDTLMMDTL.RATE                                                       AS NegatioblePrice,
    oldqto.DOCUMENTDATE                                                      AS QuotationDocumentDate,
    oldqto.RATE                                                              AS OldRate,
    PARTYDTLMMDTL.BILLVALUEDETAIL                                            AS QuotationValue,
    oldqto.PartyCode                                                         AS OldPartyCode,
    oldqto.PartyName                                                         AS OldPartyName,
    pod.DocumentDate                                                         AS PODate,
    pod.PORate                                                                AS PORate,
    pod.PARTYNAME                                                             AS POPartyName,
    pod.PARTYCODE                                                             AS POPartyCode,
    s.GRADE                                                                   AS SKUGrade,
    ID.TRANSACTIONQUANTITY * ISNULL(A.CONVERSIONFACTOR, 1)                    AS ConversionQuantity
FROM mitem A
OUTER APPLY (
    SELECT TOP (1) detail.RATE AS PORate, p2.PARTYCODE, p2.PARTYNAME, head.DOCUMENTDATE AS DocumentDate
    FROM TMMHEAD head
    JOIN TMMDETAIL detail ON head.DOCUMENTID = detail.DOCUMENTID
    JOIN MBIZTRANSACTIONTYPE c ON head.BIZTRANSACTIONTYPEID = c.BIZTRANSACTIONTYPEID
    JOIN MPARTY p2 ON head.PARTYID = p2.PARTYID
    WHERE c.BIZTRANSACTIONCLASSID = -1499999975
      AND A.ITEMID = detail.ITEMID
    ORDER BY head.CREATEDON DESC
) pod
JOIN vsku B ON A.itemid = B.itemid
LEFT OUTER JOIN mitemparty C
    ON B.itemid = C.itemid AND B.skuid = C.skuid AND C.itempartytype = 0
LEFT OUTER JOIN mitempartydetail CP
    ON C.itempartyid = CP.itempartyid
   AND CP.effectiveto >= @ASON AND CP.effectivefrom <= @ASON
LEFT OUTER JOIN TMMDETAIL PARTYDTLMMDTL ON CP.DOCUMENTDETAILID = PARTYDTLMMDTL.DOCUMENTDETAILID
LEFT OUTER JOIN MREASON mr ON mr.REASONID = PARTYDTLMMDTL.REASONID
LEFT OUTER JOIN TPENDINGALLOCATION pp
    ON pp.OBJECTHEADERID = PARTYDTLMMDTL.DOCUMENTID AND pp.OBJECTID = PARTYDTLMMDTL.DOCUMENTDETAILID
OUTER APPLY (
    SELECT TOP (1) oldqto2.DOCUMENTID, oldqto2.DOCUMENTNUMBER, oldqto2.DOCUMENTDATE, oldqtodtl.RATE,
           p3.PARTYCODE AS PartyCode, p3.PARTYNAME AS PartyName
    FROM TMMHEAD oldqto2
    JOIN TMMDETAIL oldqtodtl ON oldqto2.DOCUMENTID = oldqtodtl.DOCUMENTID
    JOIN MPARTY p3 ON oldqto2.PARTYID = p3.PARTYID
    WHERE oldqto2.BIZTRANSACTIONTYPEID = pp.BIZTRANSACTIONTYPEID
      AND oldqto2.DOCUMENTNUMBER = pp.DOCUMENTNUMBER
      AND oldqto2.STATUS = 3
      AND oldqtodtl.ITEMID = pp.ITEMID
      AND oldqtodtl.SKUID = pp.SKUID
    ORDER BY oldqto2.DOCUMENTID DESC
) oldqto
LEFT OUTER JOIN mcurrency CR ON CP.currencyid = CR.currencyid
LEFT OUTER JOIN mparty D ON C.partyid = D.partyid
CROSS JOIN (
    SELECT DISTINCT d.slno, d.itemid, d.skuid, d.transactionquantity, d.REMARKS,
           a2.DOCUMENTNUMBER, a2.PAYMENTTERMSID, d.ALLOCATIONID, a2.DELIVERYINSTRUCTIONS
    FROM tmmdetail d
    JOIN TMMHEAD a2 ON d.DOCUMENTID = a2.DOCUMENTID
    WHERE d.documentid = @DocumentId
) ID
-- LEFT (not INNER, unlike GB4's original comma-joins) — these four are purely descriptive
-- lookups (UOM name/code, SKU grade, payment term name; MALLOCATION's own columns aren't even
-- selected, it was a pure existence-check join). An unmatched lookup must not erase the whole
-- comparison row, which is exactly what happened testing against DocumentId 2750: an INNER JOIN
-- here silently zeroed out the entire result set for that document.
LEFT OUTER JOIN muom uom ON A.purchaseuomid = uom.uomid
LEFT OUTER JOIN msku s ON B.skuid = s.SKUID
LEFT OUTER JOIN MPAYMENTTERM p ON p.PAYMENTTERMID = ID.PAYMENTTERMSID
LEFT OUTER JOIN MALLOCATION ma ON ma.ALLOCATIONID = ID.ALLOCATIONID
WHERE (A.itemid = ID.itemid OR @DocumentId = -1)
  AND (B.skuid = ID.skuid OR @DocumentId = -1)
  AND (A.itemid = @ItemId OR @ItemId = -1)
  AND (B.skuid = @SkuId OR @SkuId = -1)
  AND (A.categoryid = @ItemCategoryId OR @ItemCategoryId = -1)
  AND (A.subcategoryid = @ItemSubCategoryId OR @ItemSubCategoryId = -1)
  AND (A.itemgroupid = @ItemGroupId OR @ItemGroupId = -1)
  AND (A.procurementinchargeid = @BuyerId OR @BuyerId = -1)
ORDER BY CASE WHEN @DocumentId = -1 THEN RANK() OVER (ORDER BY A.itemcode, B.skucode) ELSE ID.slno END
OFFSET (CASE WHEN @FirstNumber = -1 THEN 0 ELSE @FirstNumber END) ROWS
FETCH NEXT (CASE WHEN @MaxResult = -1 THEN 2147483647 ELSE @MaxResult END) ROWS ONLY;";

        // Two-statement batch: seed #tempvendor (same Item/Sku/Party join, no result set), then
        // read the aggregated per-formula charges against it — GB4's Genric.ExceuteQuery +
        // ExceuteSelectQuerySameSession pattern, replicated here as one QueryMultipleAsync call so
        // both statements share the same connection (temp tables are connection-scoped).
        // Type=1 (Detail) only.
        public const string GET_VENDOR_COMPARISON_CHARGES = @"
DECLARE @ASON DATE = CAST(GETDATE() AS DATE);

SELECT
    CASE WHEN @DocumentId = -1 THEN RANK() OVER (ORDER BY A.itemcode, B.skucode) ELSE ID.slno END AS ItemSlNo,
    A.itemid                        AS ItemId,
    A.itemcode                      AS ItemCode,
    A.itemname                      AS ItemName,
    B.skuid                         AS SkuId,
    B.skucode                       AS SkuCode,
    B.skuname                       AS SkuName,
    ROW_NUMBER() OVER (PARTITION BY A.itemid, B.skuid ORDER BY D.partyid) AS SlNo,
    D.partyid                       AS PartyId,
    D.partycode                     AS PartyCode,
    D.partyname                     AS PartyName,
    CP.documentdetailid             AS DocumentDetailId,
    ID.DocumentNumber               AS DocumentNumber,
    ID.DOCUMENTID                   AS DocumentId
INTO #tempvendor
FROM mitem A
JOIN vsku B ON A.itemid = B.itemid
LEFT OUTER JOIN mitemparty C
    ON B.itemid = C.itemid AND B.skuid = C.skuid AND C.itempartytype = 0
LEFT OUTER JOIN mitempartydetail CP
    ON C.itempartyid = CP.itempartyid
   AND CP.effectiveto >= @ASON AND CP.effectivefrom <= @ASON
LEFT OUTER JOIN TMMDETAIL PARTYDTLMMDTL ON CP.DOCUMENTDETAILID = PARTYDTLMMDTL.DOCUMENTDETAILID
LEFT OUTER JOIN MREASON mr ON mr.REASONID = PARTYDTLMMDTL.REASONID
LEFT OUTER JOIN TPENDINGALLOCATION pp
    ON pp.OBJECTHEADERID = PARTYDTLMMDTL.DOCUMENTID AND pp.OBJECTID = PARTYDTLMMDTL.DOCUMENTDETAILID
LEFT OUTER JOIN mparty D ON C.partyid = D.partyid
CROSS JOIN (
    SELECT DISTINCT d.slno, d.itemid, d.skuid, d.transactionquantity, d.REMARKS,
           a2.DOCUMENTNUMBER, a2.PAYMENTTERMSID, d.ALLOCATIONID, a2.DELIVERYINSTRUCTIONS, d.DOCUMENTID
    FROM tmmdetail d
    JOIN TMMHEAD a2 ON d.DOCUMENTID = a2.DOCUMENTID
    WHERE d.documentid = @DocumentId
) ID
WHERE (A.itemid = ID.itemid OR @DocumentId = -1)
  AND (B.skuid = ID.skuid OR @DocumentId = -1)
  AND (A.itemid = @ItemId OR @ItemId = -1)
  AND (B.skuid = @SkuId OR @SkuId = -1)
  AND (A.categoryid = @ItemCategoryId OR @ItemCategoryId = -1)
  AND (A.subcategoryid = @ItemSubCategoryId OR @ItemSubCategoryId = -1)
  AND (A.itemgroupid = @ItemGroupId OR @ItemGroupId = -1)
  AND (A.procurementinchargeid = @BuyerId OR @BuyerId = -1);

SELECT
    a.DOCUMENTDETAILID AS DocumentDetailId,
    a.documentid        AS DocumentId,
    a.FORMULAID          AS FormulaId,
    a.CHARGEDETAILID     AS ChargeDetailId,
    b.FORMULACODE        AS FormulaCode,
    b.PRINTINGNAME       AS FormulaPrintingName,
    c.CHARGENATURE       AS ChargeNature,
    SUM(a.calculatedvalue) AS CalculatedValue,
    MAX(a.inputvalue)      AS InputValue,
    p.partyid   AS PartyId,
    p.partycode AS PartyCode,
    p.partyname AS PartyName,
    p.SlNo
FROM TMMCHARGES a
JOIN MFORMULA b ON a.FORMULAID = b.formulaid
JOIN MCHARGEDETAIL c ON a.chargedetailid = c.chargedetailid
JOIN TMMHEAD d ON a.DOCUMENTID = d.DOCUMENTID
JOIN MBIZTRANSACTIONTYPE e ON d.BIZTRANSACTIONTYPEID = e.BIZTRANSACTIONTYPEID
JOIN #tempvendor p ON d.partyid = p.partyid
WHERE e.BIZTRANSACTIONCLASSID = -1799999925
  AND c.printrequired = 0
  AND p.DocumentId = @DocumentId
GROUP BY a.DOCUMENTDETAILID, a.FORMULAID, a.CHARGEDETAILID, b.FORMULACODE, b.PRINTINGNAME,
         c.CHARGENATURE, a.documentid, p.partyid, p.partycode, p.partyname, p.SlNo
HAVING SUM(a.calculatedvalue) <> 0;

DROP TABLE #tempvendor;";

        public const string UPDATE_VENDOR_DETAIL = @"
UPDATE MITEMPARTY
SET    PARTYSTATUS = @PartyStatus,
       SHAREPERCENTAGE = @SharePercentage
WHERE  ITEMPARTYID = @ItemPartyId;";
    }
}