namespace AMPDAL.Query.ApiRequestSubscriber
{
    public static class ApiRequestSubscriberQB
    {
        // Required index:
        // IX_MAPIREQUESTSUBSCRIBER_REQUEST_STATUS on (APIREQUESTID, NOTIFYONSTATUS, ISACTIVE)

        // =========================================================
        // GET SUBSCRIBERS FOR A REQUEST
        // =========================================================
        public const string GET_BY_REQUEST = @"
SELECT SUBSCRIBERID     AS SubscriberId,
       TENANTID         AS TenantId,
       APIREQUESTID     AS ApiRequestId,
       SUBSCRIBERUSERID AS SubscriberUserId,
       NOTIFYONSTATUS   AS NotifyOnStatus,
       ISACTIVE         AS IsActive,
       CREATEDON        AS CreatedOn,
       CREATEDBYID      AS CreatedById
FROM   MAPIREQUESTSUBSCRIBER
WHERE  APIREQUESTID = @ApiRequestId
AND    TENANTID     = @TenantId
AND    ISACTIVE     = 1";

        // =========================================================
        // GET ACTIVE SUBSCRIBERS FOR A SPECIFIC STATUS CHANGE
        // (used by BLL to dispatch notifications)
        // =========================================================
        public const string GET_BY_STATUS = @"
SELECT SUBSCRIBERID     AS SubscriberId,
       TENANTID         AS TenantId,
       APIREQUESTID     AS ApiRequestId,
       SUBSCRIBERUSERID AS SubscriberUserId,
       NOTIFYONSTATUS   AS NotifyOnStatus
FROM   MAPIREQUESTSUBSCRIBER
WHERE  APIREQUESTID    = @ApiRequestId
AND    TENANTID        = @TenantId
AND    NOTIFYONSTATUS  = @NotifyOnStatus
AND    ISACTIVE        = 1";

        // =========================================================
        // SAVE (INSERT)
        // =========================================================
        public const string SAVE_SUBSCRIBER = @"
INSERT INTO MAPIREQUESTSUBSCRIBER (
    SUBSCRIBERID, TENANTID, APIREQUESTID,
    SUBSCRIBERUSERID, NOTIFYONSTATUS, ISACTIVE,
    CREATEDBYID, CREATEDON
) VALUES (
    @SubscriberId, @TenantId, @ApiRequestId,
    @SubscriberUserId, @NotifyOnStatus, 1,
    @CreatedById, GETDATE()
);
SELECT @SubscriberId";

        // =========================================================
        // DELETE (soft — set ISACTIVE = 0)
        // =========================================================
        public const string DELETE_SUBSCRIBER = @"
UPDATE MAPIREQUESTSUBSCRIBER SET
    ISACTIVE = 0
WHERE SUBSCRIBERID  = @SubscriberId
AND   APIREQUESTID  = @ApiRequestId
AND   TENANTID      = @TenantId";

        // =========================================================
        // PostgreSQL Variants
        // =========================================================
        public const string PG_GET_BY_REQUEST = @"
SELECT ""SUBSCRIBERID""     AS SubscriberId,
       ""TENANTID""         AS TenantId,
       ""APIREQUESTID""     AS ApiRequestId,
       ""SUBSCRIBERUSERID"" AS SubscriberUserId,
       ""NOTIFYONSTATUS""   AS NotifyOnStatus,
       ""ISACTIVE""         AS IsActive,
       ""CREATEDON""        AS CreatedOn,
       ""CREATEDBYID""      AS CreatedById
FROM   ""MAPIREQUESTSUBSCRIBER""
WHERE  ""APIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""     = @TenantId
AND    ""ISACTIVE""     = 1";

        public const string PG_SAVE_SUBSCRIBER = @"
INSERT INTO ""MAPIREQUESTSUBSCRIBER"" (
    ""SUBSCRIBERID"", ""TENANTID"", ""APIREQUESTID"",
    ""SUBSCRIBERUSERID"", ""NOTIFYONSTATUS"", ""ISACTIVE"",
    ""CREATEDBYID"", ""CREATEDON""
) VALUES (
    @SubscriberId, @TenantId, @ApiRequestId,
    @SubscriberUserId, @NotifyOnStatus, 1,
    @CreatedById, NOW()
)
RETURNING ""SUBSCRIBERID""";

        public const string PG_DELETE_SUBSCRIBER = @"
UPDATE ""MAPIREQUESTSUBSCRIBER"" SET
    ""ISACTIVE"" = 0
WHERE ""SUBSCRIBERID"" = @SubscriberId
AND   ""APIREQUESTID"" = @ApiRequestId
AND   ""TENANTID""     = @TenantId";
    }
}