-- Migration: Create scheduled notifications table
-- Purpose: Store scheduled notifications for future delivery

CREATE TABLE IF NOT EXISTS scheduled_notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  notification_log_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  data JSON NULL,
  image VARCHAR(500) NULL,
  scheduled_for TIMESTAMP NOT NULL,
  processed_at TIMESTAMP NULL,
  status ENUM('pending', 'sent', 'failed', 'no_device', 'cancelled') DEFAULT 'pending',
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (notification_log_id) REFERENCES notification_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_scheduled_status (status, scheduled_for),
  INDEX idx_scheduled_user (user_id, scheduled_for)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
