-- Narrative Campaigns (Operations) Database Schema
-- Run this script to add Operations support
-- Created: 2025-12-30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

-- --------------------------------------------------------
-- Table: operations
-- Stores campaign/operation metadata
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `operations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `apt_group` VARCHAR(100) DEFAULT NULL,
  `description` TEXT,
  `story_intro` TEXT,
  `difficulty_level` ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'intermediate',
  `total_alerts` INT DEFAULT 0,
  `icon_url` VARCHAR(255) DEFAULT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `display_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table: operation_alerts
-- Links alerts to an operation in sequence order
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `operation_alerts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `operation_id` INT NOT NULL,
  `alert_id` INT NOT NULL,
  `sequence_order` INT NOT NULL DEFAULT 1,
  `intel_report_title` VARCHAR(255) DEFAULT NULL,
  `intel_report_content` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`operation_id`) REFERENCES `operations`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`alert_id`) REFERENCES `alerts`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_op_alert` (`operation_id`, `alert_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table: user_operation_progress
-- Tracks user progress through an operation
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_operation_progress` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `operation_id` INT NOT NULL,
  `current_alert_sequence` INT DEFAULT 1,
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`operation_id`) REFERENCES `operations`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_user_op` (`user_id`, `operation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

COMMIT;

-- Verify tables created
SELECT 'Operations tables created successfully!' AS status;
