-- Create notification logs table for audit trail
CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` VARCHAR(36) NOT NULL DEFAULT (UUID()),
  `notification_id` VARCHAR(36) NULL,
  `action` VARCHAR(50) NOT NULL,
  `actor` VARCHAR(100) NULL,
  `actor_id` VARCHAR(36) NULL,
  `channel_type` VARCHAR(20) NULL,
  `status` VARCHAR(30) NULL,
  `details` TEXT NULL,
  `source_ip` VARCHAR(50) NULL,
  `error_code` VARCHAR(50) NULL,
  `error_message` TEXT NULL,
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_notification_logs_notification_id` (`notification_id`),
  INDEX `idx_notification_logs_action` (`action`),
  INDEX `idx_notification_logs_created_at` (`created_at`),
  INDEX `idx_notification_logs_status` (`status`),
  INDEX `idx_notification_logs_channel_type` (`channel_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;