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

namespace IDMSDAL.QueryBuilders;

// Required indexes:
//   LREWORKREGISTER: (ENGAGEMENTID, TENANTID) INCLUDE (DOMAINCODE, CREATEDON)
//   LREWORKREGISTER: (DELIVERYITEMID, TENANTID)
//   LREWORKREGISTER: (FLAGGEDFORPIE, TENANTID) WHERE FLAGGEDFORPIE = 1 AND PIEPROPOSALCREATED = 0
//
// Note: REWORKID is IDENTITY — use ExecuteInsertReturnIdentityAsync on INSERT.

// ── LREWORKREGISTER list query builder ───────────────────────────────────────

public sealed class ReworkQB : IQueryBuilder<ReworkListQuery, ReworkListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["domaincode"]  = "RW.DOMAINCODE",
            ["createdon"]   = "RW.CREATEDON",
            ["reworkhours"] = "RW.REWORKHOURS"
        };

    public (string Sql, DynamicParameters Params) Build(ReworkListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc,
            _sort, "RW.CREATEDON DESC");
        return BuildReworkList(query.Criteria, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildReworkList(
        ReworkListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("rework", "RW");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("RW.TENANTID = @TenantId", new DynamicParameters()),
            SqlClauses.ExactInt(ctx,   c.EngagementId,  "rework", "ENGAGEMENTID"),
            SqlClauses.ExactString(ctx, c.DomainCode,    "rework", "DOMAINCODE"),
            SqlClauses.ExactBool(ctx,   c.FlaggedForPie, "rework", "FLAGGEDFORPIE"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       RW.REWORKID        AS ReworkId,
       RW.DELIVERYITEMID  AS DeliveryItemId,
       RW.DOMAINCODE      AS DomainCode,
       RW.REASONCODE      AS ReasonCode,
       RW.REWORKHOURS     AS ReworkHours,
       RW.FLAGGEDFORPIE   AS FlaggedForPie,
       RW.RESOLVEDON      AS ResolvedOn,
       RW.CREATEDON       AS CreatedOn
FROM   LREWORKREGISTER RW
{whereSql}
{pagingSql}";

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

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

    public const string GET_BY_ID = @"
SELECT RW.REWORKID             AS ReworkId,
       RW.ENGAGEMENTID         AS EngagementId,
       RW.TENANTID             AS TenantId,
       RW.DELIVERYITEMID       AS DeliveryItemId,
       RW.DOMAINCODE           AS DomainCode,
       RW.REASONCODE           AS ReasonCode,
       RW.REASONDETAIL         AS ReasonDetail,
       RW.REWORKHOURS          AS ReworkHours,
       RW.FLAGGEDFORPIE        AS FlaggedForPie,
       RW.PIEPROPOSALCREATED   AS PieProposalCreated,
       RW.RESOLVEDON           AS ResolvedOn,
       RW.RESOLVEDBYID         AS ResolvedById,
       RW.CREATEDBYID          AS CreatedById,
       RW.CREATEDON            AS CreatedOn,
       RW.STATUS               AS Status,
       DI.ITEMNAME             AS ItemName
FROM   LREWORKREGISTER RW
JOIN   TDELIVERYITEM DI
       ON  DI.DELIVERYITEMID = RW.DELIVERYITEMID
       AND DI.TENANTID       = RW.TENANTID
WHERE  RW.REWORKID  = @ReworkId
  AND  RW.TENANTID  = @TenantId";

    public const string GET_BY_ENGAGEMENT = @"
SELECT RW.REWORKID             AS ReworkId,
       RW.ENGAGEMENTID         AS EngagementId,
       RW.TENANTID             AS TenantId,
       RW.DELIVERYITEMID       AS DeliveryItemId,
       RW.DOMAINCODE           AS DomainCode,
       RW.REASONCODE           AS ReasonCode,
       RW.REASONDETAIL         AS ReasonDetail,
       RW.REWORKHOURS          AS ReworkHours,
       RW.FLAGGEDFORPIE        AS FlaggedForPie,
       RW.PIEPROPOSALCREATED   AS PieProposalCreated,
       RW.RESOLVEDON           AS ResolvedOn,
       RW.RESOLVEDBYID         AS ResolvedById,
       RW.CREATEDBYID          AS CreatedById,
       RW.CREATEDON            AS CreatedOn,
       RW.STATUS               AS Status,
       DI.ITEMNAME             AS ItemName
FROM   LREWORKREGISTER RW
JOIN   TDELIVERYITEM DI
       ON  DI.DELIVERYITEMID = RW.DELIVERYITEMID
       AND DI.TENANTID       = RW.TENANTID
WHERE  RW.ENGAGEMENTID = @EngagementId
  AND  RW.TENANTID     = @TenantId
ORDER BY RW.CREATEDON DESC";

    // REWORKID is IDENTITY — excluded from INSERT column list.
    // Use ExecuteInsertReturnIdentityAsync to retrieve the generated ID.
    public const string INSERT = @"
INSERT INTO LREWORKREGISTER
    (ENGAGEMENTID, TENANTID, DELIVERYITEMID, DOMAINCODE,
     REASONCODE, REASONDETAIL, REWORKHOURS,
     FLAGGEDFORPIE, CREATEDBYID, CREATEDON, STATUS)
VALUES
    (@EngagementId, @TenantId, @DeliveryItemId, @DomainCode,
     @ReasonCode, @ReasonDetail, @ReworkHours,
     @FlaggedForPie, @CreatedById, @CreatedOn, @Status)";

    public const string RESOLVE = @"
UPDATE LREWORKREGISTER SET
    RESOLVEDON   = @ResolvedOn,
    RESOLVEDBYID = @ResolvedById
WHERE  REWORKID = @ReworkId AND TENANTID = @TenantId";

    public const string MARK_PIE_PROPOSAL_CREATED = @"
UPDATE LREWORKREGISTER SET
    PIEPROPOSALCREATED = 1
WHERE  REWORKID = @ReworkId AND TENANTID = @TenantId";
}