-- Migration: Create payment_webhooks table
-- Description: Logs and tracks webhook events from payment providers
-- Date: 2026-02-20

CREATE TABLE IF NOT EXISTS payment_webhooks (
  id INT AUTO_INCREMENT PRIMARY KEY,

  -- Link to deposit (if webhook is for a specific deposit)
  deposit_id INT,

  -- Provider information
  provider VARCHAR(50) NOT NULL COMMENT 'kuda, opay, moniepoint, palmpay, binance, etc.',
  event_type VARCHAR(100) NOT NULL COMMENT 'payment.success, transfer.received, etc.',

  -- Webhook data
  payload TEXT NOT NULL COMMENT 'Full JSON payload from provider',
  signature VARCHAR(255) COMMENT 'Webhook signature for verification',

  -- Processing status
  is_processed BOOLEAN DEFAULT FALSE,
  processed_at TIMESTAMP NULL,
  processing_error TEXT COMMENT 'Error message if processing failed',
  retry_count INT DEFAULT 0 COMMENT 'Number of processing retry attempts',

  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  -- Foreign Keys
  FOREIGN KEY (deposit_id) REFERENCES deposits(id) ON DELETE SET NULL,

  -- Indexes
  INDEX idx_provider_processed (provider, is_processed),
  INDEX idx_deposit_id (deposit_id),
  INDEX idx_event_type (event_type),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
