namespace MMDAL.Query.Register
{
    public static class RegisterQB
    {
        #region Document Traceability (bidirectional recursive CTE)
        // TALLOCATION only has ALLOTEDALLOCATIONID as its self-reference (confirmed live on
        // GB5DEMO 2026-08-16 — no PreAllotedAllocationId/ItemId/OuId/etc. exist on TALLOCATION
        // itself, unlike TPENDINGALLOCATION which already carries all of them). Both directions
        // walk the SAME single column:
        //   Forward  (what this allocation was later allotted TO):   next.ALLOTEDALLOCATIONID = current.ALLOCATIONID
        //   Backward (what this allocation was originally allotted FROM): current.ALLOTEDALLOCATIONID -> that row's ALLOCATIONID
        // VisitedPath cycle-guard is NEW relative to legacy (which had none) — corrupt/legacy chains
        // could otherwise loop; a raw MAXRECURSION overflow is poor UX for a traceability screen.
        // Enrichment columns (DocumentNumber/Date/ReferenceNumber/Date/PartyReferenceNumber/Date/
        // ItemId/SkuId/PartyBranchId/WorkCenterId/OuId) are NULL on historical rows pre-dating the
        // TALLOCATION migration — COALESCE falls back onto TMMHEAD via OBJECTHEADERID=DOCUMENTID,
        // matching the existing AllocationQB.cs fallback-join convention.
        public const string GET_DOCUMENT_TRACEABILITY = @"
    ;WITH PostCTE AS (
        SELECT a.ALLOCATIONID, a.ALLOTEDALLOCATIONID, a.OBJECTHEADERTYPEID, a.OBJECTHEADERID,
               a.OBJECTTYPEID, a.OBJECTID, a.BIZTRANSACTIONTYPEID,
               a.QUANTITY, a.REJECTEDQUANTITY, a.REWORKQUANTITY, a.OTHERQUANTITY,
               a.DOCUMENTNUMBER, a.DOCUMENTDATE, a.REFERENCENUMBER, a.REFERENCEDATE,
               a.ITEMID, a.SKUID, a.PARTYBRANCHID, a.WORKCENTERID, a.OUID,
               CAST(0 AS INT) AS DocumentLevel,
               CAST('|' + CAST(a.ALLOCATIONID AS VARCHAR(10)) + '|' AS VARCHAR(4000)) AS VisitedPath
        FROM   TALLOCATION a
        WHERE  a.ALLOCATIONID = @PivotAllocationId

        UNION ALL

        SELECT nxt.ALLOCATIONID, nxt.ALLOTEDALLOCATIONID, nxt.OBJECTHEADERTYPEID, nxt.OBJECTHEADERID,
               nxt.OBJECTTYPEID, nxt.OBJECTID, nxt.BIZTRANSACTIONTYPEID,
               nxt.QUANTITY, nxt.REJECTEDQUANTITY, nxt.REWORKQUANTITY, nxt.OTHERQUANTITY,
               nxt.DOCUMENTNUMBER, nxt.DOCUMENTDATE, nxt.REFERENCENUMBER, nxt.REFERENCEDATE,
               nxt.ITEMID, nxt.SKUID, nxt.PARTYBRANCHID, nxt.WORKCENTERID, nxt.OUID,
               p.DocumentLevel + 1,
               p.VisitedPath + CAST(nxt.ALLOCATIONID AS VARCHAR(10)) + '|'
        FROM   PostCTE p
        JOIN   TALLOCATION nxt ON nxt.ALLOTEDALLOCATIONID = p.ALLOCATIONID
        WHERE  p.DocumentLevel < @MaxRecursion
          AND  p.VisitedPath NOT LIKE '%|' + CAST(nxt.ALLOCATIONID AS VARCHAR(10)) + '|%'
    ),
    PreCTE AS (
        SELECT a.ALLOCATIONID, a.ALLOTEDALLOCATIONID, a.OBJECTHEADERTYPEID, a.OBJECTHEADERID,
               a.OBJECTTYPEID, a.OBJECTID, a.BIZTRANSACTIONTYPEID,
               a.QUANTITY, a.REJECTEDQUANTITY, a.REWORKQUANTITY, a.OTHERQUANTITY,
               a.DOCUMENTNUMBER, a.DOCUMENTDATE, a.REFERENCENUMBER, a.REFERENCEDATE,
               a.ITEMID, a.SKUID, a.PARTYBRANCHID, a.WORKCENTERID, a.OUID,
               CAST(0 AS INT) AS DocumentLevel,
               CAST('|' + CAST(a.ALLOCATIONID AS VARCHAR(10)) + '|' AS VARCHAR(4000)) AS VisitedPath
        FROM   TALLOCATION a
        WHERE  a.ALLOCATIONID = @PivotAllocationId

        UNION ALL

        SELECT prv.ALLOCATIONID, prv.ALLOTEDALLOCATIONID, prv.OBJECTHEADERTYPEID, prv.OBJECTHEADERID,
               prv.OBJECTTYPEID, prv.OBJECTID, prv.BIZTRANSACTIONTYPEID,
               prv.QUANTITY, prv.REJECTEDQUANTITY, prv.REWORKQUANTITY, prv.OTHERQUANTITY,
               prv.DOCUMENTNUMBER, prv.DOCUMENTDATE, prv.REFERENCENUMBER, prv.REFERENCEDATE,
               prv.ITEMID, prv.SKUID, prv.PARTYBRANCHID, prv.WORKCENTERID, prv.OUID,
               p.DocumentLevel - 1,
               p.VisitedPath + CAST(prv.ALLOCATIONID AS VARCHAR(10)) + '|'
        FROM   PreCTE p
        JOIN   TALLOCATION prv ON prv.ALLOCATIONID = p.ALLOTEDALLOCATIONID
        WHERE  p.DocumentLevel > -@MaxRecursion
          AND  p.ALLOTEDALLOCATIONID <> -1
          AND  p.VisitedPath NOT LIKE '%|' + CAST(prv.ALLOCATIONID AS VARCHAR(10)) + '|%'
    ),
    Combined AS (
        SELECT * FROM PostCTE
        UNION
        SELECT * FROM PreCTE
    )
    SELECT
        c.DocumentLevel                                                     AS DocumentLevel,
        c.ALLOTEDALLOCATIONID                                               AS ParentAllocationid,
        c.ALLOCATIONID                                                      AS AllocationId,
        ISNULL(c.OUID, mh.OUID)                                             AS OUId,
        ISNULL(c.PARTYBRANCHID, mh.PARTYBRANCHID)                           AS PartyBranchId,
        ISNULL(c.WORKCENTERID, 0)                                           AS WorkCenterId,
        c.BIZTRANSACTIONTYPEID                                              AS BizTransactionTypeId,
        bt.BIZTRANSACTIONTYPENAME                                           AS BizTransactionTypeName,
        c.OBJECTHEADERTYPEID                                                AS ObjectHeaderTypeId,
        c.OBJECTHEADERID                                                    AS ObjectHeaderId,
        c.OBJECTTYPEID                                                      AS ObjectTypeId,
        c.OBJECTID                                                          AS ObjectId,
        ISNULL(c.DOCUMENTNUMBER, mh.DOCUMENTNUMBER)                         AS DocumentNumber,
        ISNULL(c.DOCUMENTDATE, mh.DOCUMENTDATE)                             AS DocumentDate,
        ISNULL(c.REFERENCENUMBER, mh.REFERENCENUMBER)                       AS ReferenceNumber,
        ISNULL(c.REFERENCEDATE, mh.REFERENCEDATE)                           AS ReferenceDate,
        ISNULL(c.ITEMID, md.ITEMID)                                         AS ItemId,
        ISNULL(c.SKUID, md.SKUID)                                           AS SKUId,
        c.ALLOTEDALLOCATIONID                                               AS AllotedAllocationId,
        c.QUANTITY                                                          AS Quantity,
        c.REJECTEDQUANTITY                                                  AS RejectedQuantity,
        c.REWORKQUANTITY                                                    AS ReworkQuantity,
        c.OTHERQUANTITY                                                     AS OtherQuantity,
        ISNULL(i.ITEMCODE,'')                                               AS ItemCode,
        ISNULL(i.ITEMNAME,'')                                               AS ItemName,
        ISNULL(s.SKUCODE,'')                                                AS SKUCode,
        ISNULL(s.SKUNAME,'')                                                AS SKUName
    FROM   Combined c
    LEFT JOIN TMMHEAD   mh ON mh.DOCUMENTID = c.OBJECTHEADERID
    LEFT JOIN TMMDETAIL md ON md.DOCUMENTDETAILID = c.OBJECTID
    LEFT JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = c.BIZTRANSACTIONTYPEID
    LEFT JOIN MITEM i ON i.ITEMID = ISNULL(c.ITEMID, md.ITEMID) AND i.TENANTID = @TenantId
    LEFT JOIN MSKU  s ON s.SKUID  = ISNULL(c.SKUID, md.SKUID)   AND s.TENANTID = @TenantId
    ORDER BY c.DocumentLevel
    OPTION (MAXRECURSION 100)";
        #endregion

        #region Prescription Location Picklist
        // GB4 parity: mms/Register.svc/PrescriptionLocationPicklist —
        // RegisterBLL.GetPrescriptionLocationPicklist -> RegisterDAL.GetPrescriptionLocationPicklist
        // -> RegisterQueryBuilder.GET_PRESCRIPTION_LOCATION_PICKLIST.
        // Domain: optical/eyewear prescription register (NOT a warehouse/inventory location
        // master) — distinct location codes recorded against glass-prescription entries.
        // GB4's version appended the Location filter via raw string concatenation (SQL-injection
        // risk); this parameterizes it instead.
        //
        // CAUTION: `erpglassprescription` has no other DAL/QB usage anywhere in GB5 today (only a
        // DTO stub exists) and could not be verified against a live schema in this session —
        // gb5-schema MCP was unavailable. Confirm the table/column still exist under this name
        // before relying on this query in production.
        public const string GET_PRESCRIPTION_LOCATION_PICKLIST = @"
            SELECT DISTINCT a.RVD_LOCATION_CD AS Location
            FROM erpglassprescription a
            WHERE 1 = 1
            AND (@location IS NULL OR a.RVD_LOCATION_CD LIKE '%' + @location + '%');";
        #endregion
    }
}