using Dapper;
using GB5Shared.DTO.Framework.Login;
using MMDAL.DTO.MRPRun;
using System.Text;

namespace MMDAL.Query.MRPRun
{
    // Indexes:
    //   TMRPRUNDETAILS (MRPRUNID, PLANITEMID, TENANTID)
    //   TMRPACTION     (MRPRUNID, PLANITEMID, PRIORITY, TENANTID)
    public static class MRPReportQB
    {
        // {0} = optional dynamic WHERE extension appended at runtime via BuildXxx()
        // Never string-concatenate {0} with user input — always use DynamicParameters.

        public const string GET_SHORTAGE_REPORT = @"
            SELECT rd.PLANITEMID            AS PlanItemId,
                   i.ITEMCODE               AS ItemCode,
                   i.ITEMNAME               AS ItemName,
                   rd.PLANSKUID             AS SkuId,
                   s.SKUCODE                AS SkuCode,
                   s.SKUNAME                AS SkuName,
                   rd.REQUIREDPERIOD        AS RequirePeriod,
                   rd.TOTALGROSSREQUIREMENT AS GrossRequirement,
                   rd.SOH                   AS SOH,
                   rd.SCHEDULEDRECEIPTS     AS ScheduledReceipts,
                   rd.PROJECTEDSOH          AS ProjectedSOH,
                   rd.PLANNEDORDERRECEIPTS  AS PlanOrderRecipt,
                   rd.NETREQUIREMENT        AS NetRequirement,
                   CASE WHEN rd.NETREQUIREMENT > rd.SCHEDULEDRECEIPTS
                        THEN rd.NETREQUIREMENT - rd.SCHEDULEDRECEIPTS ELSE 0 END AS ShortageWithSchReceipts,
                   CASE WHEN rd.NETREQUIREMENT > 0 THEN rd.NETREQUIREMENT ELSE 0 END AS Shortage,
                   ISNULL(a.PRIORITY, 0)   AS Priority,
                   rd.TENANTID
            FROM   TMRPRUNDETAILS rd
            JOIN   MITEM i ON i.ITEMID = rd.PLANITEMID
            LEFT   JOIN MSKU s ON s.SKUID = rd.PLANSKUID AND s.TENANTID = rd.TENANTID
            LEFT   JOIN TMRPACTION a ON a.MRPRUNID = rd.MRPRUNID
                                    AND a.PLANITEMID = rd.PLANITEMID
                                    AND a.TENANTID   = rd.TENANTID
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            AND    rd.NETREQUIREMENT > 0
            {0}
            ORDER BY CASE WHEN ISNULL(a.PRIORITY,0) = 0 THEN 999
                          ELSE ISNULL(a.PRIORITY,0) END ASC, rd.REQUIREDPERIOD ASC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_SHORTAGE_REPORT = @"
            SELECT COUNT(1)
            FROM   TMRPRUNDETAILS rd
            LEFT   JOIN TMRPACTION a ON a.MRPRUNID  = rd.MRPRUNID
                                    AND a.PLANITEMID = rd.PLANITEMID
                                    AND a.TENANTID   = rd.TENANTID
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            AND    rd.NETREQUIREMENT > 0
            {0}";

        public const string GET_STAGEWISE_SHORTAGE_REPORT = @"
            SELECT rd.PLANITEMID            AS PlanItemId,
                   i.ITEMCODE               AS ItemCode,
                   i.ITEMNAME               AS ItemName,
                   rd.PLANSKUID             AS SkuId,
                   rd.REQUIREDPERIOD        AS RequirePeriod,
                   rd.NETREQUIREMENT        AS NetRequirement,
                   rd.SCHEDULEDRECEIPTS     AS ScheduledReceipts,
                   rd.SOH                   AS SOH,
                   ISNULL(a.PRIORITY, 0)   AS Priority,
                   rd.TENANTID
            FROM   TMRPRUNDETAILS rd
            JOIN   MITEM i ON i.ITEMID = rd.PLANITEMID
            LEFT   JOIN TMRPACTION a ON a.MRPRUNID  = rd.MRPRUNID
                                    AND a.PLANITEMID = rd.PLANITEMID
                                    AND a.TENANTID   = rd.TENANTID
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            AND    rd.NETREQUIREMENT > 0
            {0}
            ORDER BY 
                CASE WHEN ISNULL(a.PRIORITY,0) = 0 THEN 999 
                     ELSE ISNULL(a.PRIORITY,0) END,
                rd.REQUIREDPERIOD
            OFFSET @PageOffset ROWS 
            FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_STAGEWISE_SHORTAGE_REPORT = @"
            SELECT COUNT(1)
            FROM   TMRPRUNDETAILS rd
            LEFT   JOIN TMRPACTION a ON a.MRPRUNID  = rd.MRPRUNID
                                    AND a.PLANITEMID = rd.PLANITEMID
                                    AND a.TENANTID   = rd.TENANTID
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            AND    rd.NETREQUIREMENT > 0
            {0}";

        public const string GET_MRP_WORKSHEET = @"
            SELECT rd.PLANITEMID              AS ItemId,
                   i.ITEMCODE                 AS ItemCode,
                   i.ITEMNAME                 AS ItemName,
                   rd.PLANSKUID               AS SkuId,
                   rd.REQUIREDPERIOD          AS RequirePeriod,
                   rd.TOTALGROSSREQUIREMENT   AS TotalGrossRequirement,
                   rd.SOH                     AS SOH,
                   rd.SCHEDULEDRECEIPTS       AS ScheduledReceipts,
                   rd.PROJECTEDSOH            AS ProjectedSOH,
                   rd.ALLOCATION              AS Allocation,
                   rd.NETREQUIREMENT          AS NetRequirement,
                   rd.PLANNEDORDERRECEIPTS    AS PlannedOrderReceipts,
                   rd.PLANNEDORDERRELEASES    AS PlannedOrderReleases,
                   rd.WIP                     AS WIP,
                   rd.LOWESTLEVELINBOM        AS LowestLevelInBOM,
                   rd.LEADTIME                AS LeadTime,
                   rd.LOTSIZE                 AS LotSize,
                   rd.SAFETYSTOCK             AS SafetyStock,
                   rd.ISMAKEORBUY             AS IsMakeOrBuy,
                   rd.SLNO                    AS SlNo,
                   rd.TENANTID
            FROM   TMRPRUNDETAILS rd
            JOIN   MITEM i ON i.ITEMID = rd.PLANITEMID
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            {0}
            ORDER BY rd.LOWESTLEVELINBOM ASC, rd.PLANITEMID ASC, rd.REQUIREDPERIOD ASC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_MRP_WORKSHEET = @"
            SELECT COUNT(1)
            FROM   TMRPRUNDETAILS rd
            WHERE  rd.MRPRUNID = @MrpRunId
            AND    rd.TENANTID = @TenantId
            {0}";

        public const string GET_PURCHASE_REQUIREMENT = @"
            SELECT a.PLANITEMID              AS ItemId,
                   i.ITEMCODE                AS ItemCode,
                   i.ITEMNAME                AS ItemName,
                   a.PLANSKUID               AS SKUId,
                   s.SKUCODE                 AS SKUCode,
                   s.SKUNAME                 AS SKUName,
                   rd.SOH                    AS SOH,
                   rd.SCHEDULEDRECEIPTS      AS ScheduledReceipt,
                   rd.NETREQUIREMENT         AS NetRequirement,
                   a.PLANNEDQUANTITY         AS LotSize,
                   u.UOMCODE                 AS UomCode,
                   ISNULL(a.PRIORITY, 0)    AS Priority,
                   a.TENANTID
            FROM   TMRPACTION a
            JOIN   TMRPRUNDETAILS rd ON rd.MRPRUNID  = a.MRPRUNID
                                    AND rd.PLANITEMID = a.PLANITEMID
                                    AND rd.TENANTID   = a.TENANTID
            JOIN   MITEM i ON i.ITEMID = a.PLANITEMID
            LEFT   JOIN MSKU s ON s.SKUID = a.PLANSKUID AND s.TENANTID = a.TENANTID
            LEFT   JOIN MUOM u ON u.UOMID = i.PURCHASEUOMID AND u.TENANTID = a.TENANTID
            WHERE  a.MRPRUNID   = @MrpRunId
            AND    a.RELEASETYPE = 1
            AND    a.TENANTID   = @TenantId
            {0}
            ORDER BY CASE WHEN ISNULL(a.PRIORITY,0) = 0 THEN 999
                          ELSE ISNULL(a.PRIORITY,0) END ASC, a.PLANNEDSTARTDATE ASC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_PURCHASE_REQUIREMENT = @"
            SELECT COUNT(1)
            FROM   TMRPACTION a
            WHERE  a.MRPRUNID    = @MrpRunId
            AND    a.RELEASETYPE = 1
            AND    a.TENANTID    = @TenantId
            {0}";

        public const string GET_SCHEDULE_RECEIPT = @"
            SELECT sr.ITEMID               AS ItemId,
                   i.ITEMCODE              AS ItemCode,
                   i.ITEMNAME              AS ItemName,
                   sr.SKUID                AS SkuId,
                   sr.SCHEDULEDATE         AS ScheduleDate,
                   sr.PURCHASEORDERPENDING AS PurchaseOrder_SchedulePending,
                   sr.PURREQPENDING        AS PurchaseRequisition_SchedulePending,
                   sr.WORKORDERPENDING     AS WorkOrder_SchedulePending,
                   sr.TENANTID
            FROM   TMRPSCHEDULERECEIPT sr
            JOIN   MITEM i ON i.ITEMID = sr.ITEMID
            WHERE  sr.MRPRUNID = @MrpRunId
            AND    sr.TENANTID = @TenantId
            {0}
            ORDER BY sr.SCHEDULEDATE ASC, i.ITEMCODE ASC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_SCHEDULE_RECEIPT = @"
            SELECT COUNT(1)
            FROM   TMRPSCHEDULERECEIPT sr
            WHERE  sr.MRPRUNID = @MrpRunId
            AND    sr.TENANTID = @TenantId
            {0}";

        public const string GET_MRPRUN_WITH_ACTIONS = @"
SELECT rd.PLANITEMID              AS PlanItemId,
       i.ITEMCODE                 AS ItemCode,
       i.ITEMNAME                 AS ItemName,
       rd.PLANSKUID               AS PlanSKUId,
       rd.REQUIREDPERIOD          AS RequiredPeriod,
       rd.TOTALGROSSREQUIREMENT   AS TotalGrossRequirement,
       rd.SOH                     AS SOH,
       rd.SCHEDULEDRECEIPTS       AS ScheduledReceipts,
       rd.PROJECTEDSOH            AS ProjectedSOH,
       rd.ALLOCATION              AS Allocation,
       rd.NETREQUIREMENT          AS NetRequirement,
       rd.PLANNEDORDERRECEIPTS    AS PlannedOrderReceipts,
       rd.PLANNEDORDERRELEASES    AS PlannedOrderReleases,
       rd.WIP                     AS WIP,

       a.MRPACTIONID              AS MRPActionId,
       a.PLANTYPE                 AS PlanType,
       a.PLANNEDSTARTDATE         AS PlannedStartDate,
       a.PLANNEDORDERDATE         AS PlannedOrderDate,
       a.PLANNEDDUEDATE           AS PlannedDueDate,
       a.PLANNEDQUANTITY          AS PlannedQuantity,
       a.FIRMQUANTITY             AS FirmQuantity,
       a.FIRMDATE                 AS FirmDate,
       a.ISFIRMPLAN               AS IsFirmPlan,
       a.PLANSTATUS               AS PlanStatus,
       a.RELEASETYPE              AS ReleaseType,
       a.ALLOCATIONID             AS AllocationId,
       ISNULL(a.PRIORITY, 0)      AS Priority,
       a.RUNSCOPE                 AS RunScope,

       rd.SLNO                    AS SlNo

FROM TMRPRUNDETAILS rd

JOIN MITEM i 
    ON i.ITEMID = rd.PLANITEMID 
  
LEFT JOIN TMRPACTION a 
    ON a.MRPRUNID = rd.MRPRUNID
   AND a.PLANITEMID = rd.PLANITEMID
   AND (@AllocationId IS NULL OR a.ALLOCATIONID = @AllocationId)
   AND (@MaxPriority IS NULL OR ISNULL(a.PRIORITY,0) <= @MaxPriority)

WHERE rd.MRPRUNID = @MrpRunId
{0}

ORDER BY rd.SLNO ASC, rd.REQUIREDPERIOD ASC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_MRPRUN_WITH_ACTIONS = @"
SELECT COUNT(1)
FROM TMRPRUNDETAILS rd
LEFT JOIN TMRPACTION a
    ON a.MRPRUNID   = rd.MRPRUNID
   AND a.PLANITEMID = rd.PLANITEMID
   AND a.TENANTID   = rd.TENANTID
WHERE rd.MRPRUNID = @MrpRunId
{0}";

        public const string GET_MRP_WITH_DIMENSION = @"
            SELECT a.MRPACTIONID              AS MrpActionId,
       a.MRPRUNID                 AS MrpRunId,
       a.PLANITEMID               AS PlanItemId,
       i.ITEMCODE                 AS ItemCode,
       i.ITEMNAME                 AS ItemName,
       a.PLANSKUID                AS PlanSKUId,
       s.SKUCODE                  AS SKUCode,
       s.SKUNAME                  AS SKUName,

       pd.PARTDIMID               AS PartDimId,
       pd.D1                      AS Dimension1,
       pd.D2                      AS Dimension2,
       pd.D3                      AS Dimension3,
       pd.D4                      AS Dimension4,
       pd.D5                      AS Dimension5,

       a.PLANNEDSTARTDATE         AS PlannedStartDate,
       a.PLANNEDDUEDATE           AS PlannedDueDate,
       a.PLANNEDQUANTITY          AS PlannedQuantity,
       a.FIRMQUANTITY             AS FirmQuantity,
       a.IMPLEMENTEDQUANTITY      AS ImplementedQuantity,
       a.PLANSTATUS               AS PlanStatus,
       a.RELEASETYPE              AS ReleaseType,
       a.ALLOCATIONID             AS AllocationId,
       al.ALLOCATIONNAME          AS AllocationName,

       ISNULL(a.PRIORITY, 0)      AS Priority,
       a.RUNSCOPE                 AS RunScope,
       a.TENANTID

FROM   TMRPACTION a

JOIN   MITEM i 
    ON i.ITEMID = a.PLANITEMID

LEFT JOIN MSKU s  
    ON s.SKUID = a.PLANSKUID 
   AND s.TENANTID = a.TENANTID

-- ✅ FIXED JOIN (removed ENTITYTYPEID)
LEFT JOIN TPARTDETAIL ptd 
    ON ptd.ENTITYID = a.MRPACTIONID

LEFT JOIN MPARTDIMENSION pd 
    ON pd.PARTDIMID = ptd.PARTDIMID

LEFT JOIN MALLOCATION al 
    ON al.ALLOCATIONID = a.ALLOCATIONID

WHERE  a.MRPRUNID = @MrpRunId
{0}

ORDER BY 
    CASE 
        WHEN ISNULL(a.PRIORITY,0) = 0 THEN 999
        ELSE ISNULL(a.PRIORITY,0)
    END ASC,
    a.PLANNEDSTARTDATE ASC

OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY;";

        public const string COUNT_MRP_WITH_DIMENSION = @"
            SELECT COUNT(1)
            FROM   TMRPACTION a
            WHERE  a.MRPRUNID = @MrpRunId
            {0}";

        public const string GET_SCOPED_RUN_COMPARISON = @"
            SELECT a.PLANITEMID               AS PlanItemId,
                   i.ITEMCODE                 AS ItemCode,
                   i.ITEMNAME                 AS ItemName,
                   a.PLANNEDQUANTITY          AS MacroQty,
                   b.PLANNEDQUANTITY          AS ScopedQty,
                   (b.PLANNEDQUANTITY - a.PLANNEDQUANTITY) AS Delta,
                   ISNULL(a.PRIORITY, 0)     AS MacroPriority,
                   ISNULL(b.PRIORITY, 0)     AS ScopedPriority,
                   a.TENANTID
            FROM   TMRPACTION a
            JOIN   TMRPACTION b ON b.PLANITEMID = a.PLANITEMID
                                AND b.MRPRUNID   = @ScopedRunId
                                AND b.TENANTID   = a.TENANTID
            JOIN   MITEM i ON i.ITEMID = a.PLANITEMID
            WHERE  a.MRPRUNID = @MacroRunId
            AND    a.TENANTID = @TenantId
            ORDER BY ABS(b.PLANNEDQUANTITY - a.PLANNEDQUANTITY) DESC
            OFFSET @PageOffset ROWS 
            FETCH NEXT @PageSize ROWS ONLY";

        public const string COUNT_SCOPED_RUN_COMPARISON = @"
            SELECT COUNT(1)
            FROM   TMRPACTION a
            JOIN   TMRPACTION b ON b.PLANITEMID = a.PLANITEMID
                                AND b.MRPRUNID   = @ScopedRunId
                                AND b.TENANTID   = a.TENANTID
            WHERE  a.MRPRUNID = @MacroRunId
            AND    a.TENANTID = @TenantId";

        // ── Dynamic WHERE builders ────────────────────────────────────────

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildShortageReport(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildCommonWhere(c);
            return (string.Format(GET_SHORTAGE_REPORT, where),
                    string.Format(COUNT_SHORTAGE_REPORT, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildStageWiseShortageReport(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildCommonWhere(c);
            return (string.Format(GET_STAGEWISE_SHORTAGE_REPORT, where),
                    string.Format(COUNT_STAGEWISE_SHORTAGE_REPORT, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildMRPWorksheet(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildItemDateWhere(c);
            return (string.Format(GET_MRP_WORKSHEET, where),
                    string.Format(COUNT_MRP_WORKSHEET, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildPurchaseRequirement(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildCommonWhere(c);
            return (string.Format(GET_PURCHASE_REQUIREMENT, where),
                    string.Format(COUNT_PURCHASE_REQUIREMENT, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildScheduleReceipt(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildItemDateWhere(c);
            return (string.Format(GET_SCHEDULE_RECEIPT, where),
                    string.Format(COUNT_SCHEDULE_RECEIPT, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildMRPRunWithActions(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildCommonWhere(c);
            // JOIN ON clause always references these — must be bound even when not filtered
            if (!c.AllocationId.HasValue) p.Add("AllocationId", (int?)null);
            if (!c.MaxPriority.HasValue)  p.Add("MaxPriority",  (int?)null);
            return (string.Format(GET_MRPRUN_WITH_ACTIONS, where),
                    string.Format(COUNT_MRPRUN_WITH_ACTIONS, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildMRPWithDimension(
            MRPReportCriteriaDTO c)
        {
            var (where, p) = BuildActionWhere(c);
            return (string.Format(GET_MRP_WITH_DIMENSION, where),
                    string.Format(COUNT_MRP_WITH_DIMENSION, where), p);
        }

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildScopedRunComparison(
            ScopedRunComparisonCriteriaDTO c,LoginDTO loginDTO)
        {
            var p = new DynamicParameters();
            p.Add("MacroRunId",  c.MacroRunId);
            p.Add("ScopedRunId", c.ScopedRunId);
            p.Add("PageOffset",  c.PageOffset);
            p.Add("PageSize",    c.PageSize);
            p.Add("@TenantId", loginDTO.ClientId);
            return (GET_SCOPED_RUN_COMPARISON, COUNT_SCOPED_RUN_COMPARISON, p);
        }

        // Filters TMRPRUNDETAILS/TMRPACTION-joined queries (AllocationId on 'a', dates on 'rd')
        private static (string Where, DynamicParameters Params) BuildCommonWhere(MRPReportCriteriaDTO c)
        {
            var sb = new StringBuilder();
            var p = new DynamicParameters();

            p.Add("MrpRunId", c.MrpRunId);
            p.Add("@TenantId", c.TenantId);
            p.Add("PageOffset", c.PageOffset);
            p.Add("PageSize", c.PageSize);

            // ----------------------------
            // ITEM FILTER (SAFE)
            // ----------------------------
            if (c.ItemId.HasValue)
            {
                sb.Append(" AND rd.PLANITEMID = @ItemId");
                p.Add("ItemId", c.ItemId.Value);
            }

            // ----------------------------
            // ALLOCATION FILTER (NULL SAFE)
            // ----------------------------
            if (c.AllocationId.HasValue)
            {
                sb.Append(" AND (@AllocationId IS NULL OR a.ALLOCATIONID = @AllocationId)");
                p.Add("AllocationId", c.AllocationId.Value);
            }

            // ----------------------------
            // PRIORITY FILTER (NULL SAFE)
            // ----------------------------
            if (c.MaxPriority.HasValue)
            {
                sb.Append(" AND (@MaxPriority IS NULL OR ISNULL(a.PRIORITY,0) <= @MaxPriority)");
                p.Add("MaxPriority", c.MaxPriority.Value);
            }

            // ----------------------------
            // DATE FILTERS (OK)
            // ----------------------------
            if (c.FromDate.HasValue)
            {
                sb.Append(" AND rd.REQUIREDPERIOD >= @FromDate");
                p.Add("FromDate", c.FromDate.Value);
            }

            if (c.ToDate.HasValue)
            {
                sb.Append(" AND rd.REQUIREDPERIOD <= @ToDate");
                p.Add("ToDate", c.ToDate.Value);
            }

            return (sb.ToString(), p);
        }

        // Filters queries whose main table is TMRPACTION alias 'a' (no 'rd' alias)
        private static (string Where, DynamicParameters Params) BuildActionWhere(MRPReportCriteriaDTO c)
        {
            var sb = new StringBuilder();
            var p  = new DynamicParameters();
            p.Add("MrpRunId",   c.MrpRunId);
            p.Add("PageOffset", c.PageOffset);
            p.Add("PageSize",   c.PageSize);

            if (c.ItemId.HasValue)
            {
                sb.Append(" AND a.PLANITEMID = @ItemId");
                p.Add("ItemId", c.ItemId.Value);
            }
            if (c.AllocationId.HasValue)
            {
                sb.Append(" AND a.ALLOCATIONID = @AllocationId");
                p.Add("AllocationId", c.AllocationId.Value);
            }
            if (c.MaxPriority.HasValue)
            {
                sb.Append(" AND ISNULL(a.PRIORITY, 0) <= @MaxPriority");
                p.Add("MaxPriority", c.MaxPriority.Value);
            }
            if (c.FromDate.HasValue)
            {
                sb.Append(" AND a.PLANNEDSTARTDATE >= @FromDate");
                p.Add("FromDate", c.FromDate.Value);
            }
            if (c.ToDate.HasValue)
            {
                sb.Append(" AND a.PLANNEDSTARTDATE <= @ToDate");
                p.Add("ToDate", c.ToDate.Value);
            }
            return (sb.ToString(), p);
        }

        // Filters queries that only have TMRPRUNDETAILS (no 'a' alias for AllocationId/Priority)
        private static (string Where, DynamicParameters Params) BuildItemDateWhere(MRPReportCriteriaDTO c)
        {
            var sb = new System.Text.StringBuilder();
            var p  = new DynamicParameters();
            p.Add("MrpRunId",   c.MrpRunId);
            p.Add("PageOffset", c.PageOffset);
            p.Add("PageSize",   c.PageSize);

            if (c.ItemId.HasValue)
            {
                sb.Append(" AND rd.PLANITEMID = @ItemId");
                p.Add("ItemId", c.ItemId.Value);
            }
            if (c.FromDate.HasValue)
            {
                sb.Append(" AND rd.REQUIREDPERIOD >= @FromDate");
                p.Add("FromDate", c.FromDate.Value);
            }
            if (c.ToDate.HasValue)
            {
                sb.Append(" AND rd.REQUIREDPERIOD <= @ToDate");
                p.Add("ToDate", c.ToDate.Value);
            }
            return (sb.ToString(), p);
        }
    }
}