using Dapper;
using MMDAL.DTO.PartLevel;

namespace MMDAL.Query.PartLevel
{
    // Index notes:
    //   IX_TPARTDETAIL_PRIORITY (PRIORITY, STATUS, TENANTID) — covers workbench + auto-allocate
    //   IX_TPARTDETAIL_ITEM     (ITEMID, TENANTID, STATUS)
    //   IX_TPARTALLOC_DETAIL    (PARTDETAILID, TENANTID)
    //   IX_TPARTPOS_KEY         (ITEMID, PARTDIMID, STOREID, LOTID, TENANTID)
    public static class PartLevelWorkbenchQB
    {
        public const string GET_PART_LEVEL_WORKBENCH = @"
            SELECT  pd.PARTDETAILID        AS PartDetailId,
                    pd.ENTITYTYPEID        AS EntityTypeId,
                    pd.ENTITYID            AS EntityId,
                    pd.ENTITYDETAILID      AS EntityDetailId,
                    pd.PARENTPARTDETAILID  AS ParentPartDetailId,
                    pd.ROOTPARTDETAILID    AS RootPartDetailId,
                    pd.ITEMID              AS ItemId,
                    mi.ITEMCODE            AS ItemCode,
                    mi.ITEMNAME            AS ItemName,
                    pd.SKUID               AS SkuId,
                    sk.SKUCODE             AS SkuCode,
                    pd.PARTDIMID           AS PartDimId,
                    dim.D1                 AS Dimension1,
                    dim.D2                 AS Dimension2,
                    dim.D3                 AS Dimension3,
                    dim.D4                 AS Dimension4,
                    dim.D5                 AS Dimension5,
                    pd.REQUIREDQTY         AS RequiredQty,
                    pd.REQUIREDNUMBERS     AS RequiredNumbers,
                    ISNULL(pal.AllocatedQty, 0)  AS AllocatedQty,
                    ISNULL(pal.IssuedQty, 0)     AS IssuedQty,
                    ISNULL(pal.ConsumedQty, 0)   AS ConsumedQty,
                    pd.REQUIREDQTY - ISNULL(pal.AllocatedQty, 0) AS RemainingQty,
                    ISNULL(pp.AVAILABLE, 0)  AS AvailableStock,
                    ISNULL(pp.RESERVED, 0)   AS ReservedStock,
                    pd.ALLOCATIONID        AS AllocationId,
                    ma.ALLOCATIONCODE      AS AllocationCode,
                    ma.ALLOCATIONNAME      AS AllocationName,
                    pd.SALESORDERID        AS SalesOrderId,
                    ISNULL(pd.PRIORITY, 0) AS Priority,
                    pd.PLANNEDSTARTDATE    AS PlannedStartDate,
                    pd.PLANNEDDUEDATE      AS PlannedDueDate,
                    pd.WIPSTOCK            AS WipStock,
                    pd.AVAILABLETOWORK     AS AvailableToWork,
                    pd.STATUS              AS Status,
                    pd.TENANTID            AS TenantId
            FROM    TPARTDETAIL pd
            JOIN    MITEM mi  ON mi.ITEMID   = pd.ITEMID
            LEFT    JOIN MSKU sk   ON sk.SKUID    = pd.SKUID
            LEFT    JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pd.PARTDIMID
            LEFT    JOIN MALLOCATION ma ON ma.ALLOCATIONID = pd.ALLOCATIONID
            LEFT    JOIN (
                        SELECT  pa.PARTDETAILID,
                                SUM(CASE WHEN pa.STATUS IN (0,1) THEN pa.ALLOCATEDQTY ELSE 0 END) AS AllocatedQty,
                                SUM(pa.ISSUEDQTY)   AS IssuedQty,
                                SUM(pa.CONSUMEDQTY) AS ConsumedQty
                        FROM    TPARTALLOCATION pa
                        WHERE   pa.TENANTID = @TenantId
                        GROUP   BY pa.PARTDETAILID
                    ) pal ON pal.PARTDETAILID = pd.PARTDETAILID
            LEFT    JOIN TPARTPOSITION pp ON pp.ITEMID    = pd.ITEMID
                                          AND (pp.PARTDIMID = pd.PARTDIMID
                                               OR (pp.PARTDIMID IS NULL AND pd.PARTDIMID IS NULL))
                                          AND pp.TENANTID  = @TenantId
            WHERE   pd.TENANTID = @TenantId
            {0}
            ORDER   BY CASE WHEN ISNULL(pd.PRIORITY,0) = 0 THEN 999
                            ELSE ISNULL(pd.PRIORITY,0) END ASC,
                       pd.PLANNEDDUEDATE ASC";

        public const string COUNT_PART_LEVEL_WORKBENCH = @"
            SELECT COUNT(*)
            FROM   TPARTDETAIL pd
            WHERE  pd.TENANTID = @TenantId
            {0}";

        public const string GET_PART_LEVEL_WIP_SUMMARY = @"
            SELECT  pd.ITEMID              AS ItemId,
                    mi.ITEMCODE            AS ItemCode,
                    mi.ITEMNAME            AS ItemName,
                    SUM(pd.REQUIREDQTY)    AS TotalRequired,
                    SUM(ISNULL(pal.AllocatedQty, 0))  AS TotalAllocated,
                    SUM(ISNULL(pal.IssuedQty, 0))     AS TotalIssued,
                    SUM(ISNULL(pal.ConsumedQty, 0))   AS TotalConsumed,
                    SUM(pd.REQUIREDQTY - ISNULL(pal.AllocatedQty, 0)) AS TotalRemaining,
                    ISNULL(pp_agg.TotalAvailable, 0)  AS TotalAvailable,
                    SUM(pd.WIPSTOCK)       AS TotalWip,
                    SUM(CASE WHEN pd.STATUS = 0 THEN 1 ELSE 0 END)     AS OpenCount,
                    SUM(CASE WHEN pd.STATUS = 1 THEN 1 ELSE 0 END)     AS InProgressCount,
                    SUM(CASE WHEN pd.STATUS = 2 THEN 1 ELSE 0 END)     AS CompletedCount,
                    ISNULL(pd.PRIORITY, 0) AS Priority,
                    pd.ALLOCATIONID        AS AllocationId,
                    pd.TENANTID            AS TenantId
            FROM    TPARTDETAIL pd
            JOIN    MITEM mi ON mi.ITEMID = pd.ITEMID
            LEFT    JOIN (
                        SELECT  pa.PARTDETAILID,
                                SUM(CASE WHEN pa.STATUS IN (0,1) THEN pa.ALLOCATEDQTY ELSE 0 END) AS AllocatedQty,
                                SUM(pa.ISSUEDQTY)   AS IssuedQty,
                                SUM(pa.CONSUMEDQTY) AS ConsumedQty
                        FROM    TPARTALLOCATION pa
                        WHERE   pa.TENANTID = @TenantId
                        GROUP   BY pa.PARTDETAILID
                    ) pal ON pal.PARTDETAILID = pd.PARTDETAILID
            LEFT    JOIN (
                        SELECT  pp.ITEMID, SUM(pp.AVAILABLE) AS TotalAvailable
                        FROM    TPARTPOSITION pp
                        WHERE   pp.TENANTID = @TenantId
                        GROUP   BY pp.ITEMID
                    ) pp_agg ON pp_agg.ITEMID = pd.ITEMID
            WHERE   pd.TENANTID = @TenantId
            AND     pd.STATUS IN (0, 1)
            {0}
            GROUP   BY pd.ITEMID, mi.ITEMCODE, mi.ITEMNAME,
                       ISNULL(pd.PRIORITY, 0), pd.ALLOCATIONID, pd.TENANTID,
                       pp_agg.TotalAvailable
            ORDER   BY CASE WHEN ISNULL(pd.PRIORITY,0) = 0 THEN 999
                            ELSE ISNULL(pd.PRIORITY,0) END ASC";

        // AllocationPriorityView — single query, no N+1.
        // Shows allocations ranked by priority with open part detail counts and available stock.
        public const string GET_ALLOCATION_PRIORITY_VIEW = @"
            SELECT  ma.ALLOCATIONID   AS AllocationId,
                    ma.ALLOCATIONCODE AS AllocationCode,
                    ma.ALLOCATIONNAME AS AllocationName,
                    ISNULL(ma.PRIORITY, 0) AS Priority,
                    COUNT(pd.PARTDETAILID) AS OpenPartDetailCount,
                    SUM(pd.REQUIREDQTY - ISNULL(pal.AllocatedQty, 0)) AS TotalRemainingQty,
                    ISNULL(pp_agg.TotalAvailable, 0) AS TotalAvailableQty,
                    ma.TENANTID       AS TenantId
            FROM    MALLOCATION ma
            LEFT    JOIN TPARTDETAIL pd ON pd.ALLOCATIONID = ma.ALLOCATIONID
                                       AND pd.STATUS IN (0, 1)
                                       AND pd.TENANTID     = @TenantId
            LEFT    JOIN (
                        SELECT  pa.PARTDETAILID,
                                SUM(CASE WHEN pa.STATUS IN (0,1) THEN pa.ALLOCATEDQTY ELSE 0 END) AS AllocatedQty
                        FROM    TPARTALLOCATION pa
                        WHERE   pa.TENANTID = @TenantId
                        GROUP   BY pa.PARTDETAILID
                    ) pal ON pal.PARTDETAILID = pd.PARTDETAILID
            LEFT    JOIN (
                        SELECT  pp.ITEMID, SUM(pp.AVAILABLE) AS TotalAvailable
                        FROM    TPARTPOSITION pp
                        WHERE   pp.TENANTID = @TenantId
                        GROUP   BY pp.ITEMID
                    ) pp_agg ON pp_agg.ITEMID = pd.ITEMID
            WHERE   ma.TENANTID = @TenantId
            GROUP   BY ma.ALLOCATIONID, ma.ALLOCATIONCODE, ma.ALLOCATIONNAME,
                       ISNULL(ma.PRIORITY, 0), ma.TENANTID, pp_agg.TotalAvailable
            ORDER   BY CASE WHEN ISNULL(ma.PRIORITY, 0) = 0 THEN 999
                            ELSE ISNULL(ma.PRIORITY, 0) END ASC,
                       ma.ALLOCATIONCODE ASC";

        public static (string DataSql, string CountSql, DynamicParameters Params)
            BuildWorkbench(PartLevelWorkbenchCriteriaDTO c)
        {
            var p     = new DynamicParameters();
            var where = new System.Text.StringBuilder();

            if (c.AllocationId.HasValue)
            {
                where.Append(" AND pd.ALLOCATIONID = @AllocationId");
                p.Add("AllocationId", c.AllocationId.Value);
            }
            if (c.ItemId.HasValue)
            {
                where.Append(" AND pd.ITEMID = @ItemId");
                p.Add("ItemId", c.ItemId.Value);
            }
            if (c.SalesOrderId.HasValue)
            {
                where.Append(" AND pd.SALESORDERID = @SalesOrderId");
                p.Add("SalesOrderId", c.SalesOrderId.Value);
            }
            if (c.MaxPriority.HasValue)
            {
                where.Append(" AND (ISNULL(pd.PRIORITY,0) = 0 OR pd.PRIORITY <= @MaxPriority)");
                p.Add("MaxPriority", c.MaxPriority.Value);
            }
            if (c.Status.HasValue)
            {
                where.Append(" AND pd.STATUS = @Status");
                p.Add("Status", c.Status.Value);
            }

            string w = where.ToString();
            return (string.Format(GET_PART_LEVEL_WORKBENCH, w),
                    string.Format(COUNT_PART_LEVEL_WORKBENCH, w),
                    p);
        }
    }
}