namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTCHANGEREQUEST: (CLIENTID, REQUESTSTATUS)
//   MENTITLEMENTCHANGEREQUEST: (SUBSCRIPTIONID)
public static class ChangeRequestQB
{
    private const string COLUMNS = @"
       CHANGEREQUESTID         AS ChangeRequestId,
       CLIENTID                AS ClientId,
       REQUESTTYPE             AS RequestType,
       SUBSCRIPTIONID          AS SubscriptionId,
       PROPOSEDPLANID          AS ProposedPlanId,
       PROPOSEDVALIDTILL       AS ProposedValidTill,
       BETAPROGRAMNAME         AS BetaProgramName,
       REQUESTSTATUS           AS RequestStatus,
       REQUESTEDBYCLIENTUSERID AS RequestedByClientUserId,
       REQUESTEDON             AS RequestedOn,
       REVIEWEDBYUSERID        AS ReviewedByUserId,
       REVIEWEDON              AS ReviewedOn,
       REVIEWCOMMENTS          AS ReviewComments,
       VERSION                 AS Version,
       STATUS                  AS Status,
       SORTORDER               AS SortOrder,
       CREATEDBYID             AS CreatedById,
       CREATEDON               AS CreatedOn,
       MODIFIEDBYID            AS ModifiedById,
       MODIFIEDON              AS ModifiedOn,
       SOURCETYPE              AS SourceType,
       TENANTID                AS TenantId";

    private const string LIST_COLUMNS = @"
       CHANGEREQUESTID         AS ChangeRequestId,
       CLIENTID                AS ClientId,
       REQUESTTYPE             AS RequestType,
       SUBSCRIPTIONID          AS SubscriptionId,
       PROPOSEDPLANID          AS ProposedPlanId,
       PROPOSEDVALIDTILL       AS ProposedValidTill,
       BETAPROGRAMNAME         AS BetaProgramName,
       REQUESTSTATUS           AS RequestStatus,
       REQUESTEDBYCLIENTUSERID AS RequestedByClientUserId,
       REQUESTEDON             AS RequestedOn,
       REVIEWEDBYUSERID        AS ReviewedByUserId,
       REVIEWEDON              AS ReviewedOn,
       REVIEWCOMMENTS          AS ReviewComments";

    public const string GET_BY_ID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTCHANGEREQUEST
WHERE  CHANGEREQUESTID = @ChangeRequestId";

    // Filters: @ClientId (-1 = any — admin inbox), @RequestStatus (-1 = any) — paged via QueryPagedAsync
    public const string GET_LIST_PAGED = "SELECT" + LIST_COLUMNS + @"
FROM   MENTITLEMENTCHANGEREQUEST
WHERE  (@ClientId = -1 OR CLIENTID = @ClientId)
  AND  (@RequestStatus = -1 OR REQUESTSTATUS = @RequestStatus)
ORDER BY REQUESTEDON DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTCHANGEREQUEST
    (CHANGEREQUESTID, CLIENTID, REQUESTTYPE, SUBSCRIPTIONID, PROPOSEDPLANID, PROPOSEDVALIDTILL, BETAPROGRAMNAME,
     REQUESTSTATUS, REQUESTEDBYCLIENTUSERID, REQUESTEDON,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@ChangeRequestId, @ClientId, @RequestType, @SubscriptionId, @ProposedPlanId, @ProposedValidTill, @BetaProgramName,
     @RequestStatus, @RequestedByClientUserId, @RequestedOn,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE_REVIEW = @"
UPDATE MENTITLEMENTCHANGEREQUEST SET
    REQUESTSTATUS    = @RequestStatus,
    REVIEWEDBYUSERID = @ReviewedByUserId,
    REVIEWEDON       = @ReviewedOn,
    REVIEWCOMMENTS   = @ReviewComments,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE  CHANGEREQUESTID = @ChangeRequestId";
}