namespace PartnerDAL.Query.PartnerWebhook
{
    public static class PartnerWebhookQB
    {
        // Index: IX_TPARTNERWEBHOOK_PRODUCT on (PARTNERPRODUCTID) INCLUDE (EVENTTYPEID, CALLBACKURL, STATUS)
        public const string GET_PARTNER_WEBHOOK_LIST = @"
            SELECT WEBHOOKID              AS WebhookId,
                   PARTNERPRODUCTID       AS PartnerProductId,
                   EVENTTYPEID            AS EventTypeId,
                   CALLBACKURL            AS CallbackUrl,
                   MAXRETRIES             AS MaxRetries,
                   RETRYINTERVALSECONDS   AS RetryIntervalSeconds,
                   LASTDELIVERYSTATUS     AS LastDeliveryStatus,
                   LASTDELIVEREDUTC       AS LastDeliveredUtc,
                   STATUS                 AS Status,
                   CREATEDON              AS CreatedOn
            FROM   TPARTNERWEBHOOK
            WHERE  PARTNERPRODUCTID = @PartnerProductId
            ORDER  BY WEBHOOKID;";

        // Index: IX_TPARTNERWEBHOOK_EVENTTYPE on (EVENTTYPEID, STATUS) INCLUDE (PARTNERPRODUCTID, CALLBACKURL, SECRET)
        public const string GET_WEBHOOKS_BY_EVENT = @"
            SELECT WEBHOOKID              AS WebhookId,
                   PARTNERPRODUCTID       AS PartnerProductId,
                   EVENTTYPEID            AS EventTypeId,
                   CALLBACKURL            AS CallbackUrl,
                   SECRET                 AS Secret,
                   MAXRETRIES             AS MaxRetries,
                   RETRYINTERVALSECONDS   AS RetryIntervalSeconds,
                   STATUS                 AS Status
            FROM   TPARTNERWEBHOOK
            WHERE  EVENTTYPEID = @EventTypeId
              AND  STATUS      = 1;";

        public const string CHECK_EVENT_TYPE_EXISTS = @"
            SELECT COUNT(1)
            FROM   MEVENTTYPE
            WHERE  EVENTTYPEID = @EventTypeId;";

        public const string SAVE_PARTNER_WEBHOOK = @"
            INSERT INTO TPARTNERWEBHOOK
                (PARTNERPRODUCTID, EVENTTYPEID, CALLBACKURL, SECRET,
                 MAXRETRIES, RETRYINTERVALSECONDS, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@PartnerProductId, @EventTypeId, @CallbackUrl, @Secret,
                 @MaxRetries, @RetryIntervalSeconds, @Status,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);
            SELECT SCOPE_IDENTITY();";

        public const string DELETE_PARTNER_WEBHOOK = @"
            UPDATE TPARTNERWEBHOOK
            SET    STATUS       = 0,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  WEBHOOKID    = @WebhookId;";

        public const string UPDATE_WEBHOOK_DELIVERY_STATUS = @"
            UPDATE TPARTNERWEBHOOK
            SET    LASTDELIVERYSTATUS = @Status,
                   LASTDELIVEREDUTC   = GETUTCDATE()
            WHERE  WEBHOOKID          = @WebhookId;";
    }
}