-- Migration: Create execution logging and error tracking tables
-- Purpose: Enable detailed journey execution tracking and error monitoring

-- Journey Execution Logs
CREATE TABLE IF NOT EXISTS journey_execution_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  journey_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  execution_status ENUM('started', 'in_progress', 'completed', 'failed', 'abandoned') DEFAULT 'started',
  current_step_id INT NULL,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_journey_execution (journey_id, started_at),
  INDEX idx_user_execution (user_id, started_at),
  INDEX idx_execution_status (execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step Execution Logs
CREATE TABLE IF NOT EXISTS step_execution_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  execution_log_id INT NOT NULL,
  step_id INT NOT NULL,
  step_name VARCHAR(255) NOT NULL,
  step_type ENUM('notification', 'delay', 'condition', 'exit', 'wait', 'webhook') NOT NULL,
  execution_status ENUM('pending', 'executing', 'completed', 'skipped', 'failed') DEFAULT 'pending',
  execution_result JSON NULL,
  error_message TEXT NULL,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  duration_ms INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (execution_log_id) REFERENCES journey_execution_logs(id) ON DELETE CASCADE,
  INDEX idx_execution_step (execution_log_id, started_at),
  INDEX idx_step_status (step_id, execution_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Error Logs
CREATE TABLE IF NOT EXISTS error_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  error_type VARCHAR(100) NOT NULL,
  error_code VARCHAR(50) NULL,
  error_message TEXT NOT NULL,
  error_stack TEXT NULL,
  context JSON NULL,
  journey_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  execution_log_id INT NULL,
  step_id INT NULL,
  severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
  status ENUM('new', 'investigating', 'resolved', 'ignored') DEFAULT 'new',
  resolved_at TIMESTAMP NULL,
  resolved_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (execution_log_id) REFERENCES journey_execution_logs(id) ON DELETE SET NULL,
  INDEX idx_error_type (error_type, created_at),
  INDEX idx_error_severity (severity, status),
  INDEX idx_error_journey (journey_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Journey Analytics Summary (Materialized View)
CREATE TABLE IF NOT EXISTS journey_analytics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  journey_id BIGINT UNSIGNED NOT NULL,
  date DATE NOT NULL,
  total_executions INT DEFAULT 0,
  completed_executions INT DEFAULT 0,
  failed_executions INT DEFAULT 0,
  abandoned_executions INT DEFAULT 0,
  avg_completion_time_seconds INT DEFAULT 0,
  total_users INT DEFAULT 0,
  conversion_rate DECIMAL(5,2) DEFAULT 0,
  error_count INT DEFAULT 0,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE,
  UNIQUE KEY unique_journey_date (journey_id, date),
  INDEX idx_analytics_date (date),
  INDEX idx_analytics_journey (journey_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step Analytics Summary
CREATE TABLE IF NOT EXISTS step_analytics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  step_id INT NOT NULL,
  journey_id BIGINT UNSIGNED NOT NULL,
  date DATE NOT NULL,
  total_executions INT DEFAULT 0,
  successful_executions INT DEFAULT 0,
  failed_executions INT DEFAULT 0,
  skipped_executions INT DEFAULT 0,
  avg_duration_ms INT DEFAULT 0,
  error_count INT DEFAULT 0,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE,
  UNIQUE KEY unique_step_date (step_id, date),
  INDEX idx_step_analytics_date (date),
  INDEX idx_step_analytics_journey (journey_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notification Logs (for notification step tracking)
CREATE TABLE IF NOT EXISTS notification_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  step_execution_id INT NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  notification_type VARCHAR(50) DEFAULT 'push',
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  delivery_status ENUM('queued', 'sent', 'delivered', 'failed', 'clicked') DEFAULT 'queued',
  fcm_message_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 (step_execution_id) REFERENCES step_execution_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notification_user (user_id, created_at),
  INDEX idx_notification_status (delivery_status),
  INDEX idx_notification_delivery (sent_at, delivered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
