CREATE TABLE IF NOT EXISTS provider_webhook_events (
  id CHAR(26) PRIMARY KEY,
  channel_id CHAR(26) NOT NULL,
  provider VARCHAR(40) NOT NULL,
  event_hash CHAR(64) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  status ENUM('pending','processing','processed','failed') NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NULL,
  last_error TEXT NULL,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_event_hash (channel_id, event_hash),
  KEY idx_provider_event_queue (status, next_attempt_at, created_at),
  CONSTRAINT fk_provider_event_channel FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
