using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using IDMSDAL.DTOs;

namespace IDMSDAL.QueryBuilders;

// Required indexes:
//   TDELIVERYITEM: (ENGAGEMENTID, TENANTID, DOMAINCODE) INCLUDE (DISPLAYORDER, STATUS)
//   TDELIVERYITEM: (TENANTID, STATUS) WHERE STATUS < 3
//   TDELIVERYITEM: (TENANTID, DUEON)  WHERE DUEON IS NOT NULL

// ── TDELIVERYITEM list query builder ─────────────────────────────────────────

public sealed class DomainItemQB : IQueryBuilder<DeliveryItemListQuery, DeliveryItemListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["domaincode"]   = "DI.DOMAINCODE",
            ["itemname"]     = "DI.ITEMNAME",
            ["status"]       = "DI.STATUS",
            ["dueon"]        = "DI.DUEON",
            ["displayorder"] = "DI.DISPLAYORDER"
        };

    public (string Sql, DynamicParameters Params) Build(DeliveryItemListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc,
            _sort, "DI.DOMAINCODE ASC, DI.DISPLAYORDER ASC");
        return BuildItemList(query.Criteria, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildItemList(
        DeliveryItemListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("deliveryitem", "DI");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("DI.TENANTID = @TenantId", new DynamicParameters()),
            SqlClauses.ExactInt(ctx,   c.EngagementId, "deliveryitem", "ENGAGEMENTID"),
            SqlClauses.ExactString(ctx, c.DomainCode,   "deliveryitem", "DOMAINCODE"),
            SqlClauses.ExactShort(ctx,  c.Status,       "deliveryitem", "STATUS"),
            SqlClauses.ExactBool(ctx,   c.IsRework,     "deliveryitem", "ISREWORK"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       DI.DELIVERYITEMID AS DeliveryItemId,
       DI.DOMAINCODE     AS DomainCode,
       DI.ITEMTYPE       AS ItemType,
       DI.ITEMNAME       AS ItemName,
       DI.STATUS         AS Status,
       DI.ASSIGNEDTOID   AS AssignedToId,
       DI.DUEON          AS DueOn,
       DI.ISIMMUTABLE    AS IsImmutable,
       DI.ISREWORK       AS IsRework
FROM   TDELIVERYITEM DI
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── CRUD SQL ──────────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
SELECT DI.DELIVERYITEMID       AS DeliveryItemId,
       DI.ENGAGEMENTID         AS EngagementId,
       DI.TENANTID             AS TenantId,
       DI.DOMAINCODE           AS DomainCode,
       DI.ITEMTYPE             AS ItemType,
       DI.ITEMSUBTYPE          AS ItemSubtype,
       DI.ITEMNAME             AS ItemName,
       DI.ITEMMETADATA         AS ItemMetadata,
       DI.STATUS               AS Status,
       DI.ASSIGNEDTOID         AS AssignedToId,
       DI.DUEON                AS DueOn,
       DI.COMPLETEDON          AS CompletedOn,
       DI.COMPLETEDBYID        AS CompletedById,
       DI.ESTIMATEDHOURS       AS EstimatedHours,
       DI.ACTUALHOURS          AS ActualHours,
       DI.ISIMMUTABLE          AS IsImmutable,
       DI.SIGNOFFHASH          AS SignOffHash,
       DI.ISREWORK             AS IsRework,
       DI.REWORKREASONCODE     AS ReworkReasonCode,
       DI.REWORKREASONDETAIL   AS ReworkReasonDetail,
       DI.PIECONTENTREF        AS PieContentRef,
       DI.KMSCONTENTREF        AS KmsContentRef,
       DI.DISPLAYORDER         AS DisplayOrder,
       DI.ISMANDATORY          AS IsMandatory,
       DI.REMOTESESSIONREF     AS RemoteSessionRef,
       DI.CLIENTSELFSERVICE    AS ClientSelfService,
       DI.VERSION              AS Version,
       DI.SORTORDER            AS SortOrder,
       DI.SOURCETYPE           AS SourceType,
       DI.CREATEDBYID          AS DeliveryItemCreatedById,
       DI.CREATEDON            AS DeliveryItemCreatedOn,
       DI.MODIFIEDBYID         AS DeliveryItemModifiedById,
       DI.MODIFIEDON           AS DeliveryItemModifiedOn
FROM   TDELIVERYITEM DI
WHERE  DI.DELIVERYITEMID = @DeliveryItemId
  AND  DI.TENANTID       = @TenantId";

    public const string GET_BY_ENGAGEMENT_DOMAIN = @"
SELECT DI.DELIVERYITEMID       AS DeliveryItemId,
       DI.ENGAGEMENTID         AS EngagementId,
       DI.TENANTID             AS TenantId,
       DI.DOMAINCODE           AS DomainCode,
       DI.ITEMTYPE             AS ItemType,
       DI.ITEMSUBTYPE          AS ItemSubtype,
       DI.ITEMNAME             AS ItemName,
       DI.ITEMMETADATA         AS ItemMetadata,
       DI.STATUS               AS Status,
       DI.ASSIGNEDTOID         AS AssignedToId,
       DI.DUEON                AS DueOn,
       DI.COMPLETEDON          AS CompletedOn,
       DI.COMPLETEDBYID        AS CompletedById,
       DI.ESTIMATEDHOURS       AS EstimatedHours,
       DI.ACTUALHOURS          AS ActualHours,
       DI.ISIMMUTABLE          AS IsImmutable,
       DI.SIGNOFFHASH          AS SignOffHash,
       DI.ISREWORK             AS IsRework,
       DI.REWORKREASONCODE     AS ReworkReasonCode,
       DI.REWORKREASONDETAIL   AS ReworkReasonDetail,
       DI.PIECONTENTREF        AS PieContentRef,
       DI.KMSCONTENTREF        AS KmsContentRef,
       DI.DISPLAYORDER         AS DisplayOrder,
       DI.ISMANDATORY          AS IsMandatory,
       DI.REMOTESESSIONREF     AS RemoteSessionRef,
       DI.CLIENTSELFSERVICE    AS ClientSelfService,
       DI.VERSION              AS Version,
       DI.SORTORDER            AS SortOrder,
       DI.SOURCETYPE           AS SourceType,
       DI.CREATEDBYID          AS DeliveryItemCreatedById,
       DI.CREATEDON            AS DeliveryItemCreatedOn,
       DI.MODIFIEDBYID         AS DeliveryItemModifiedById,
       DI.MODIFIEDON           AS DeliveryItemModifiedOn
FROM   TDELIVERYITEM DI
WHERE  DI.ENGAGEMENTID = @EngagementId
  AND  DI.DOMAINCODE   = @DomainCode
  AND  DI.TENANTID     = @TenantId
ORDER BY DI.DISPLAYORDER, DI.DELIVERYITEMID";

    public const string INSERT = @"
INSERT INTO TDELIVERYITEM
    (DELIVERYITEMID, ENGAGEMENTID, TENANTID, DOMAINCODE, ITEMTYPE, ITEMSUBTYPE,
     ITEMNAME, ITEMMETADATA, STATUS, ASSIGNEDTOID, DUEON,
     ESTIMATEDHOURS, DISPLAYORDER, ISMANDATORY, REMOTESESSIONREF,
     CLIENTSELFSERVICE, ISREWORK, REWORKREASONCODE, REWORKREASONDETAIL,
     PIECONTENTREF, KMSCONTENTREF,
     VERSION, SORTORDER, SOURCETYPE,
     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@DeliveryItemId, @EngagementId, @TenantId, @DomainCode, @ItemType, @ItemSubtype,
     @ItemName, @ItemMetadata, @Status, @AssignedToId, @DueOn,
     @EstimatedHours, @DisplayOrder, @IsMandatory, @RemoteSessionRef,
     @ClientSelfService, @IsRework, @ReworkReasonCode, @ReworkReasonDetail,
     @PieContentRef, @KmsContentRef,
     @Version, @SortOrder, @SourceType,
     @DeliveryItemCreatedById, @DeliveryItemCreatedOn, @DeliveryItemModifiedById, @DeliveryItemModifiedOn)";

    public const string UPDATE = @"
UPDATE TDELIVERYITEM SET
    ITEMNAME          = @ItemName,
    ITEMMETADATA      = @ItemMetadata,
    ASSIGNEDTOID      = @AssignedToId,
    DUEON             = @DueOn,
    ESTIMATEDHOURS    = @EstimatedHours,
    DISPLAYORDER      = @DisplayOrder,
    ISMANDATORY       = @IsMandatory,
    REMOTESESSIONREF  = @RemoteSessionRef,
    CLIENTSELFSERVICE = @ClientSelfService,
    MODIFIEDBYID      = @DeliveryItemModifiedById,
    MODIFIEDON        = @DeliveryItemModifiedOn
WHERE  DELIVERYITEMID = @DeliveryItemId AND TENANTID = @TenantId AND ISIMMUTABLE = 0";

    public const string COMPLETE = @"
UPDATE TDELIVERYITEM SET
    STATUS        = 3,
    COMPLETEDON   = @CompletedOn,
    COMPLETEDBYID = @CompletedById,
    ACTUALHOURS   = @ActualHours,
    MODIFIEDBYID  = @DeliveryItemModifiedById,
    MODIFIEDON    = @DeliveryItemModifiedOn
WHERE  DELIVERYITEMID = @DeliveryItemId AND TENANTID = @TenantId";

    public const string MARK_IMMUTABLE = @"
UPDATE TDELIVERYITEM SET
    ISIMMUTABLE  = 1,
    SIGNOFFHASH  = @SignOffHash,
    MODIFIEDBYID = @DeliveryItemModifiedById,
    MODIFIEDON   = @DeliveryItemModifiedOn
WHERE  DELIVERYITEMID = @DeliveryItemId AND TENANTID = @TenantId";

    public const string GET_DOMAIN_PROGRESS = @"
SELECT DI.DOMAINCODE                                    AS DomainCode,
       COUNT(DI.DELIVERYITEMID)                         AS TotalItems,
       SUM(CASE WHEN DI.STATUS = 3 THEN 1 ELSE 0 END)  AS CompletedItems,
       SUM(CASE WHEN DI.ISREWORK = 1 THEN 1 ELSE 0 END) AS ReworkCount,
       SUM(CASE WHEN DI.ISIMMUTABLE = 1 THEN 1 ELSE 0 END) AS SignedOffItems
FROM   TDELIVERYITEM DI
WHERE  DI.ENGAGEMENTID = @EngagementId
  AND  DI.TENANTID     = @TenantId
GROUP  BY DI.DOMAINCODE";
}