-- Migration: Recreate notification tables with proper schema
-- Purpose: Match backend query expectations

-- Drop tables
DROP TABLE IF EXISTS scheduled_notifications;
DROP TABLE IF EXISTS notification_logs;

-- Create notification_logs
CREATE TABLE notification_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  journey_id BIGINT UNSIGNED NULL,
  step_id BIGINT UNSIGNED NULL,
  notification_type VARCHAR(50) DEFAULT 'push',
  provider ENUM('onesignal', 'firebase', 'email', 'sms') DEFAULT 'onesignal',
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  status ENUM('queued', 'sent', 'delivered', 'failed', 'clicked') DEFAULT 'queued',
  external_id VARCHAR(255) NULL,
  error_message TEXT NULL,
  sent_at TIMESTAMP NULL,
  delivered_at TIMESTAMP NULL,
  clicked_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE SET NULL,
  FOREIGN KEY (step_id) REFERENCES journey_steps(id) ON DELETE SET NULL,
  INDEX idx_notification_user (user_id, created_at),
  INDEX idx_notification_journey (journey_id, created_at),
  INDEX idx_notification_provider (provider, created_at),
  INDEX idx_notification_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create scheduled_notifications
CREATE TABLE scheduled_notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  journey_id BIGINT UNSIGNED NULL,
  step_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  scheduled_for TIMESTAMP NOT NULL,
  status ENUM('pending', 'sent', 'cancelled', 'failed') DEFAULT 'pending',
  sent_at TIMESTAMP NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE SET NULL,
  FOREIGN KEY (step_id) REFERENCES journey_steps(id) ON DELETE SET NULL,
  INDEX idx_scheduled_user (user_id, scheduled_for),
  INDEX idx_scheduled_status (status, scheduled_for)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
