using Dapper;
using MMDAL.DTO.PartLevel;

namespace MMDAL.Query.PartLevel
{
    // Indexes: IX_TPARTDETAIL_ENTITY, IX_TPARTDETAIL_ITEM, IX_TPARTDETAIL_PRIORITY
    public static class PartDetailQB
    {
        public const string GET_PARTDETAIL = @"
            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,
                   i.ITEMCODE            AS ItemCode,
                   i.ITEMNAME            AS ItemName,
                   pd.SKUID              AS SkuId,
                   pd.PARTDIMID          AS PartDimId,
                   dim.D1                AS Dimension1,
                   dim.D2                AS Dimension2,
                   dim.D3                AS Dimension3,
                   pd.REQUIREDQTY        AS RequiredQty,
                   pd.REQUIREDNUMBERS    AS RequiredNumbers,
                   pd.ALLOCATIONID       AS AllocationId,
                   al.ALLOCATIONNAME     AS AllocationName,
                   pd.SALESORDERID       AS SalesOrderId,
                   pd.PRIORITY           AS Priority,
                   pd.PLANNEDSTARTDATE   AS PlannedStartDate,
                   pd.PLANNEDDUEDATE     AS PlannedDueDate,
                   pd.WIPSTOCK           AS WipStock,
                   pd.AVAILABLETOWORK    AS AvailableToWork,
                   pd.STATUS             AS Status,
                   pd.TENANTID,
                   pd.CREATEDBYID        AS CreatedById,
                   pd.CREATEDON          AS CreatedOn,
                   pd.MODIFIEDBYID       AS ModifiedById,
                   pd.MODIFIEDON         AS ModifiedOn
            FROM   TPARTDETAIL pd
            JOIN   MITEM i ON i.ITEMID = pd.ITEMID AND i.TENANTID = pd.TENANTID
            LEFT   JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pd.PARTDIMID
            LEFT   JOIN MALLOCATION al ON al.ALLOCATIONID = pd.ALLOCATIONID
                                       AND al.TENANTID    = pd.TENANTID
            WHERE  pd.PARTDETAILID = @PartDetailId
            AND    pd.TENANTID     = @TenantId";

        public const string GET_PARTDETAIL_LINEAGE = @"
            WITH CTE AS (
                SELECT pd.PARTDETAILID, pd.PARENTPARTDETAILID, pd.ROOTPARTDETAILID,
                       pd.ITEMID, pd.ENTITYTYPEID, pd.ENTITYID, pd.STATUS, pd.TENANTID
                FROM   TPARTDETAIL pd
                WHERE  pd.ROOTPARTDETAILID = @RootPartDetailId
                AND    pd.TENANTID         = @TenantId
                UNION ALL
                SELECT pd.PARTDETAILID, pd.PARENTPARTDETAILID, pd.ROOTPARTDETAILID,
                       pd.ITEMID, pd.ENTITYTYPEID, pd.ENTITYID, pd.STATUS, pd.TENANTID
                FROM   TPARTDETAIL pd
                JOIN   CTE c ON c.PARTDETAILID = pd.PARENTPARTDETAILID AND pd.TENANTID = @TenantId
            )
            SELECT c.PARTDETAILID       AS PartDetailId,
                   c.PARENTPARTDETAILID AS ParentPartDetailId,
                   c.ROOTPARTDETAILID   AS RootPartDetailId,
                   c.ITEMID             AS ItemId,
                   i.ITEMCODE           AS ItemCode,
                   i.ITEMNAME           AS ItemName,
                   c.ENTITYTYPEID       AS EntityTypeId,
                   c.ENTITYID           AS EntityId,
                   c.STATUS             AS Status,
                   c.TENANTID
            FROM   CTE c
            JOIN   MITEM i ON i.ITEMID = c.ITEMID AND i.TENANTID = c.TENANTID";

        public const string SAVE_PARTDETAIL = @"
            INSERT INTO TPARTDETAIL (
                PARTDETAILID, ENTITYTYPEID, ENTITYID, ENTITYDETAILID,
                PARENTPARTDETAILID, ROOTPARTDETAILID,
                ITEMID, SKUID, PARTDIMID,
                REQUIREDQTY, REQUIREDNUMBERS,
                ALLOCATIONID, PEGGINGGROUPID, SALESORDERID,
                PRIORITY, PLANNEDSTARTDATE, PLANNEDDUEDATE,
                WIPSTOCK, AVAILABLETOWORK, STATUS,
                TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES (
                @PartDetailId, @EntityTypeId, @EntityId, @EntityDetailId,
                @ParentPartDetailId, @RootPartDetailId,
                @ItemId, @SkuId, @PartDimId,
                @RequiredQty, @RequiredNumbers,
                @AllocationId, @PeggingGroupId, @SalesOrderId,
                @Priority, @PlannedStartDate, @PlannedDueDate,
                0, 0, 0,
                @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string DELETE_PARTDETAILS_BY_ENTITY = @"
            DELETE FROM TPARTDETAIL
            WHERE  ENTITYTYPEID = @EntityTypeId
            AND    ENTITYID     = @EntityId
            AND    TENANTID     = @TenantId";

        public const string GET_FIRST_PARTDETAILID_BY_ENTITY = @"
            SELECT TOP 1 PARTDETAILID FROM TPARTDETAIL
            WHERE  ENTITYTYPEID = @EntityTypeId
            AND    ENTITYID     = @EntityId
            AND    TENANTID     = @TenantId
            ORDER  BY PARTDETAILID ASC";

        public const string GET_FIRST_PARTDETAILID_BY_ENTITY_PG = @"
            SELECT PARTDETAILID FROM TPARTDETAIL
            WHERE  ENTITYTYPEID = :EntityTypeId
            AND    ENTITYID     = :EntityId
            AND    TENANTID     = :TenantId
            ORDER  BY PARTDETAILID ASC
            LIMIT  1";

        public const string UPDATE_PARTDETAIL_LINEAGE = @"
            UPDATE TPARTDETAIL
            SET    PARENTPARTDETAILID = @ParentPartDetailId,
                   ROOTPARTDETAILID   = @RootPartDetailId
            WHERE  ENTITYTYPEID = @EntityTypeId
            AND    ENTITYID     = @EntityId
            AND    TENANTID     = @TenantId";

        public const string UPDATE_PARTDETAIL_LINEAGE_PG = @"
            UPDATE TPARTDETAIL
            SET    PARENTPARTDETAILID = :ParentPartDetailId,
                   ROOTPARTDETAILID   = :RootPartDetailId
            WHERE  ENTITYTYPEID = :EntityTypeId
            AND    ENTITYID     = :EntityId
            AND    TENANTID     = :TenantId";

        public const string UPDATE_PARTDETAIL_STATUS = @"
            UPDATE TPARTDETAIL
            SET    STATUS      = @Status,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  PARTDETAILID = @PartDetailId
            AND    TENANTID     = @TenantId";

        public const string UPDATE_PARTDETAIL_WIP = @"
            UPDATE TPARTDETAIL
            SET    WIPSTOCK       = @WipStock,
                   AVAILABLETOWORK = @AvailableToWork,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = @ModifiedOn
            WHERE  PARTDETAILID = @PartDetailId
            AND    TENANTID     = @TenantId";

        // {0} = dynamic WHERE extension
        public const string GET_PARTDETAIL_LIST = @"
            SELECT pd.PARTDETAILID                        AS PartDetailId,
                   pd.ENTITYTYPEID                        AS EntityTypeId,
                   pd.ENTITYID                            AS EntityId,
                   pd.ITEMID                              AS ItemId,
                   i.ITEMCODE                             AS ItemCode,
                   i.ITEMNAME                             AS ItemName,
                   pd.PARTDIMID                           AS PartDimId,
                   CONCAT_WS(' / ', dim.D1, dim.D2, dim.D3) AS DimensionLabel,
                   pd.REQUIREDQTY                         AS RequiredQty,
                   ISNULL(SUM(pa.ALLOCATEDQTY), 0)        AS AllocatedQty,
                   pd.REQUIREDQTY - ISNULL(SUM(pa.ALLOCATEDQTY), 0) AS RemainingQty,
                   pd.ALLOCATIONID                        AS AllocationId,
                   al.ALLOCATIONNAME                      AS AllocationName,
                   pd.PRIORITY                            AS Priority,
                   pd.PLANNEDDUEDATE                      AS PlannedDueDate,
                   pd.STATUS                              AS Status,
                   pd.TENANTID
            FROM   TPARTDETAIL pd
            JOIN   MITEM i ON i.ITEMID = pd.ITEMID AND i.TENANTID = pd.TENANTID
            LEFT   JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pd.PARTDIMID
            LEFT   JOIN TPARTALLOCATION pa ON pa.PARTDETAILID = pd.PARTDETAILID
                                           AND pa.STATUS IN (0, 1)
            LEFT   JOIN MALLOCATION al ON al.ALLOCATIONID = pd.ALLOCATIONID
                                       AND al.TENANTID    = pd.TENANTID
            WHERE  pd.TENANTID = @TenantId
            {0}
            GROUP BY pd.PARTDETAILID, pd.ENTITYTYPEID, pd.ENTITYID,
                     pd.ITEMID, i.ITEMCODE, i.ITEMNAME,
                     pd.PARTDIMID, dim.D1, dim.D2, dim.D3,
                     pd.REQUIREDQTY, pd.ALLOCATIONID, al.ALLOCATIONNAME,
                     pd.PRIORITY, pd.PLANNEDDUEDATE, pd.STATUS, pd.TENANTID
            ORDER BY CASE WHEN pd.PRIORITY = 0 THEN 999 ELSE pd.PRIORITY END ASC,
                     pd.PLANNEDDUEDATE ASC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

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

        // Priority-ordered open details for auto-allocate engine
        public const string GET_OPEN_PARTDETAILS_PRIORITIZED = @"
            SELECT pd.PARTDETAILID                            AS PartDetailId,
                   pd.ITEMID                                  AS ItemId,
                   pd.SKUID                                   AS SkuId,
                   pd.PARTDIMID                               AS PartDimId,
                   pd.REQUIREDQTY                             AS RequiredQty,
                   pd.PRIORITY                                AS Priority,
                   pd.ALLOCATIONID                            AS AllocationId,
                   pd.SALESORDERID                            AS SalesOrderId,
                   ISNULL(SUM(pa.ALLOCATEDQTY), 0)            AS AlreadyAllocated,
                   pd.REQUIREDQTY - ISNULL(SUM(pa.ALLOCATEDQTY), 0) AS RemainingQty
            FROM   TPARTDETAIL pd
            LEFT   JOIN TPARTALLOCATION pa ON pa.PARTDETAILID = pd.PARTDETAILID
                                           AND pa.STATUS IN (0, 1)
            WHERE  pd.STATUS   IN (0, 1)
            AND    pd.TENANTID  = @TenantId
            {0}
            GROUP BY pd.PARTDETAILID, pd.ITEMID, pd.SKUID, pd.PARTDIMID,
                     pd.REQUIREDQTY, pd.PRIORITY, pd.ALLOCATIONID, pd.SALESORDERID
            HAVING pd.REQUIREDQTY - ISNULL(SUM(pa.ALLOCATEDQTY), 0) > 0
            ORDER BY CASE WHEN pd.PRIORITY = 0 THEN 999 ELSE pd.PRIORITY END ASC,
                     pd.PLANNEDDUEDATE ASC";

        public static (string DataSql, string CountSql, DynamicParameters Params) BuildPartDetailList(
            PartDetailCriteriaDTO c)
        {
            var sb = new System.Text.StringBuilder();
            var p  = new DynamicParameters();
            p.Add("PageOffset", c.PageOffset);
            p.Add("PageSize",   c.PageSize);

            if (c.EntityTypeId.HasValue) { sb.Append(" AND pd.ENTITYTYPEID = @EntityTypeId"); p.Add("EntityTypeId", c.EntityTypeId.Value); }
            if (c.EntityId.HasValue)     { sb.Append(" AND pd.ENTITYID = @EntityId");         p.Add("EntityId",     c.EntityId.Value); }
            if (c.ItemId.HasValue)       { sb.Append(" AND pd.ITEMID = @ItemId");             p.Add("ItemId",       c.ItemId.Value); }
            if (c.AllocationId.HasValue) { sb.Append(" AND pd.ALLOCATIONID = @AllocationId"); p.Add("AllocationId", c.AllocationId.Value); }
            if (c.SalesOrderId.HasValue) { sb.Append(" AND pd.SALESORDERID = @SalesOrderId"); p.Add("SalesOrderId", c.SalesOrderId.Value); }
            if (c.Priority.HasValue)     { sb.Append(" AND pd.PRIORITY = @Priority");         p.Add("Priority",     c.Priority.Value); }
            if (c.Status.HasValue)       { sb.Append(" AND pd.STATUS = @Status");             p.Add("Status",       c.Status.Value); }

            return (
                string.Format(GET_PARTDETAIL_LIST, sb.ToString()),
                string.Format(COUNT_PARTDETAIL_LIST, sb.ToString()),
                p);
        }

        public static (string Sql, DynamicParameters Params) BuildOpenDetailsPrioritized(
            AllocationScopeDTO scope)
        {
            var sb = new System.Text.StringBuilder();
            var p  = new DynamicParameters();

            if (scope.AllocationId.HasValue)  { sb.Append(" AND pd.ALLOCATIONID = @AllocationId"); p.Add("AllocationId", scope.AllocationId.Value); }
            if (scope.SalesOrderId.HasValue)  { sb.Append(" AND pd.SALESORDERID = @SalesOrderId"); p.Add("SalesOrderId", scope.SalesOrderId.Value); }
            if (scope.FGItemIds is { Count: > 0 }) { sb.Append(" AND pd.ITEMID IN @FGItemIds"); p.Add("FGItemIds", scope.FGItemIds); }

            return (string.Format(GET_OPEN_PARTDETAILS_PRIORITIZED, sb.ToString()), p);
        }
    }
}