namespace AMPDAL.Query.ApiRequestChecklist
{
    public static class ApiRequestChecklistQB
    {
        // Required index:
        // IX_MAPIREQUESTCHECKLIST_REQUEST_STAGE on (APIREQUESTID, STAGE, ISCOMPLETE)

        // =========================================================
        // GET BY REQUEST (all stages)
        // =========================================================
        public const string GET_BY_REQUEST = @"
SELECT CHECKLISTID   AS ChecklistId,
       TENANTID      AS TenantId,
       APIREQUESTID  AS ApiRequestId,
       STAGE         AS Stage,
       ITEMTEXT      AS ItemText,
       ISCOMPLETE    AS IsComplete,
       COMPLETEDBYID AS CompletedById,
       COMPLETEDON   AS CompletedOn,
       SORTORDER     AS SortOrder,
       CREATEDON     AS CreatedOn
FROM   MAPIREQUESTCHECKLIST
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId
ORDER  BY STAGE, SORTORDER";

        // =========================================================
        // GET BY REQUEST AND STAGE
        // =========================================================
        public const string GET_BY_STAGE = @"
SELECT CHECKLISTID   AS ChecklistId,
       TENANTID      AS TenantId,
       APIREQUESTID  AS ApiRequestId,
       STAGE         AS Stage,
       ITEMTEXT      AS ItemText,
       ISCOMPLETE    AS IsComplete,
       COMPLETEDBYID AS CompletedById,
       COMPLETEDON   AS CompletedOn,
       SORTORDER     AS SortOrder,
       CREATEDON     AS CreatedOn
FROM   MAPIREQUESTCHECKLIST
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId
AND    STAGE        = @Stage
ORDER  BY SORTORDER";

        // =========================================================
        // INSERT CHECKLIST ITEM
        // =========================================================
        public const string INSERT_CHECKLIST_ITEM = @"
INSERT INTO MAPIREQUESTCHECKLIST (
    CHECKLISTID, TENANTID, APIREQUESTID,
    STAGE, ITEMTEXT, ISCOMPLETE,
    SORTORDER, CREATEDON
) VALUES (
    @ChecklistId, @TenantId, @ApiRequestId,
    @Stage, @ItemText, 0,
    @SortOrder, GETDATE()
)";

        // =========================================================
        // COMPLETE ITEM
        // =========================================================
        public const string COMPLETE_ITEM = @"
UPDATE MAPIREQUESTCHECKLIST SET
    ISCOMPLETE    = 1,
    COMPLETEDBYID = @CompletedById,
    COMPLETEDON   = GETDATE()
WHERE CHECKLISTID   = @ChecklistId
AND   APIREQUESTID  = @ApiRequestId
AND   TENANTID      = @TenantId";

        // =========================================================
        // PostgreSQL Variants
        // =========================================================
        public const string PG_GET_BY_REQUEST = @"
SELECT ""CHECKLISTID""   AS ChecklistId,
       ""TENANTID""      AS TenantId,
       ""APIREQUESTID""  AS ApiRequestId,
       ""STAGE""         AS Stage,
       ""ITEMTEXT""      AS ItemText,
       ""ISCOMPLETE""    AS IsComplete,
       ""COMPLETEDBYID"" AS CompletedById,
       ""COMPLETEDON""   AS CompletedOn,
       ""SORTORDER""     AS SortOrder,
       ""CREATEDON""     AS CreatedOn
FROM   ""MAPIREQUESTCHECKLIST""
WHERE  ""APIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""     = @TenantId
ORDER  BY ""STAGE"", ""SORTORDER""";

        public const string PG_GET_BY_STAGE = @"
SELECT ""CHECKLISTID""   AS ChecklistId,
       ""TENANTID""      AS TenantId,
       ""APIREQUESTID""  AS ApiRequestId,
       ""STAGE""         AS Stage,
       ""ITEMTEXT""      AS ItemText,
       ""ISCOMPLETE""    AS IsComplete,
       ""COMPLETEDBYID"" AS CompletedById,
       ""COMPLETEDON""   AS CompletedOn,
       ""SORTORDER""     AS SortOrder,
       ""CREATEDON""     AS CreatedOn
FROM   ""MAPIREQUESTCHECKLIST""
WHERE  ""APIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""     = @TenantId
AND    ""STAGE""        = @Stage
ORDER  BY ""SORTORDER""";

        public const string PG_INSERT_CHECKLIST_ITEM = @"
INSERT INTO ""MAPIREQUESTCHECKLIST"" (
    ""CHECKLISTID"", ""TENANTID"", ""APIREQUESTID"",
    ""STAGE"", ""ITEMTEXT"", ""ISCOMPLETE"",
    ""SORTORDER"", ""CREATEDON""
) VALUES (
    @ChecklistId, @TenantId, @ApiRequestId,
    @Stage, @ItemText, 0,
    @SortOrder, NOW()
)";

        public const string PG_COMPLETE_ITEM = @"
UPDATE ""MAPIREQUESTCHECKLIST"" SET
    ""ISCOMPLETE""    = 1,
    ""COMPLETEDBYID"" = @CompletedById,
    ""COMPLETEDON""   = NOW()
WHERE ""CHECKLISTID""  = @ChecklistId
AND   ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";
    }
}