namespace GoodBooks.PAY.PAYDAL.Query.Webhook
{
    // INDEX NOTES:
    // TPAYWEBHOOKEVENT: (IDEMPOTENCYKEY, PAYGATEWAYID) — covers idempotency check (also UK)
    // TPAYWEBHOOKEVENT: (ISPROCESSED, CREATEDON) — covers failed event queries

    // IMPORTANT: This is a log table. No UPDATE to business data — only ISPROCESSED/SIGNATUREVALID
    // status updates are permitted via UPDATE_WEBHOOK_SIGNATURE and UPDATE_WEBHOOK_PROCESSED.

    /// <summary>SQL constants for TPAYWEBHOOKEVENT.</summary>
    public static class PayWebhookEventQB
    {
        /// <summary>
        /// Inserts a raw incoming webhook event immediately upon receipt.
        /// PAYWEBHOOKEVENTID is an IDENTITY column — excluded from INSERT column list.
        /// </summary>
        public const string INSERT_WEBHOOK_EVENT = @"
            INSERT INTO TPAYWEBHOOKEVENT
            (
                PAYGATEWAYID,
                IDEMPOTENCYKEY,
                EVENTTYPE,
                GATEWAYPAYORDERID,
                GATEWAYTXNID,
                RAWPAYLOAD,
                SIGNATUREVALID,
                ISPROCESSED,
                PROCESSEDON,
                PROCESSINGERROR,
                CREATEDBYID,
                CREATEDON,
                TENANTID
            )
            VALUES
            (
                @PayGatewayId,
                @IdempotencyKey,
                @EventType,
                @GatewayPayOrderId,
                @GatewayTxnId,
                @RawPayload,
                @SignatureValid,
                @IsProcessed,
                @ProcessedOn,
                @ProcessingError,
                @CreatedById,
                GETUTCDATE(),
                @TenantId
            )";

        /// <summary>
        /// Updates only the signature-validation status of a webhook event.
        /// All other columns remain unchanged — this is a log table.
        /// </summary>
        public const string UPDATE_WEBHOOK_SIGNATURE = @"
            UPDATE TPAYWEBHOOKEVENT
            SET    SIGNATUREVALID = @SignatureValid
            WHERE  PAYWEBHOOKEVENTID = @PayWebhookEventId";

        /// <summary>
        /// Marks a webhook event as processed (success or failed) and records the timestamp and any error.
        /// PROCESSEDON is set server-side to GETUTCDATE() to avoid clock skew between app servers.
        /// </summary>
        public const string UPDATE_WEBHOOK_PROCESSED = @"
            UPDATE TPAYWEBHOOKEVENT
            SET    ISPROCESSED    = @IsProcessed,
                   PROCESSEDON    = GETUTCDATE(),
                   PROCESSINGERROR = @ProcessingError
            WHERE  PAYWEBHOOKEVENTID = @PayWebhookEventId";

        /// <summary>
        /// Idempotency guard: returns 1 if an event with this key has already been successfully
        /// processed by this gateway, 0 otherwise. Caller should abort processing when result > 0.
        /// </summary>
        public const string CHECK_IDEMPOTENCY = @"
            SELECT COUNT(1)
            FROM   TPAYWEBHOOKEVENT
            WHERE  IDEMPOTENCYKEY = @IdempotencyKey
            AND    PAYGATEWAYID   = @PayGatewayId
            AND    ISPROCESSED    = 1";

        /// <summary>
        /// Paged admin list of webhook events for a tenant.
        /// Supports filtering by ISPROCESSED and PAYGATEWAYID via nullable parameters
        /// (pass NULL to omit a filter).
        /// Ordered by CREATEDON DESC so newest events appear first.
        /// Required covering index: (TENANTID, ISPROCESSED, PAYGATEWAYID, CREATEDON DESC).
        /// </summary>
        public const string GET_WEBHOOK_LIST = @"
            SELECT  W.PAYWEBHOOKEVENTID   AS PayWebhookEventId,
                    W.PAYGATEWAYID        AS PayGatewayId,
                    W.IDEMPOTENCYKEY      AS IdempotencyKey,
                    W.EVENTTYPE           AS EventType,
                    W.GATEWAYPAYORDERID   AS GatewayPayOrderId,
                    W.GATEWAYTXNID        AS GatewayTxnId,
                    W.SIGNATUREVALID      AS SignatureValid,
                    W.ISPROCESSED         AS IsProcessed,
                    W.PROCESSEDON         AS ProcessedOn,
                    W.PROCESSINGERROR     AS ProcessingError,
                    W.CREATEDBYID         AS CreatedById,
                    W.CREATEDON           AS CreatedOn,
                    W.TENANTID            AS TenantId
            FROM    TPAYWEBHOOKEVENT W
            WHERE   W.TENANTID    = @TenantId
            AND     (@IsProcessed   IS NULL OR W.ISPROCESSED  = @IsProcessed)
            AND     (@PayGatewayId  IS NULL OR W.PAYGATEWAYID = @PayGatewayId)
            ORDER BY W.CREATEDON DESC
            OFFSET  @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";
    }
}