-- Migration: Create initial schema for promotional project tracker
-- Date: 2025-10-29
-- Description: Creates tables for hierarchical projects, tasks, templates, analytics, and retrospectives

-- ===== Template Projects =====
CREATE TABLE IF NOT EXISTS template_projects (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Template Phases =====
CREATE TABLE IF NOT EXISTS template_phases (
    id VARCHAR(36) PRIMARY KEY,
    template_project_id VARCHAR(36) NOT NULL,
    phase_number INT NOT NULL,
    phase_name VARCHAR(255) NOT NULL,
    description TEXT,
    FOREIGN KEY (template_project_id) REFERENCES template_projects(id) ON DELETE CASCADE,
    INDEX idx_template_phase (template_project_id, phase_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Template Tasks =====
CREATE TABLE IF NOT EXISTS template_tasks (
    id VARCHAR(36) PRIMARY KEY,
    template_phase_id VARCHAR(36) NOT NULL,
    task_name VARCHAR(255) NOT NULL,
    description TEXT,
    default_budget DECIMAL(12, 2) DEFAULT 0.00,
    is_outsourced BOOLEAN DEFAULT FALSE,
    order_index INT DEFAULT 0,
    FOREIGN KEY (template_phase_id) REFERENCES template_phases(id) ON DELETE CASCADE,
    INDEX idx_phase_order (template_phase_id, order_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Projects (Enhanced with hierarchical support) =====
CREATE TABLE IF NOT EXISTS projects (
    id VARCHAR(36) PRIMARY KEY,
    parent_project_id VARCHAR(36) DEFAULT NULL,
    template_project_id VARCHAR(36),
    project_name VARCHAR(255) NOT NULL,
    status ENUM('draft', 'active', 'completed', 'on_hold', 'cancelled') DEFAULT 'draft',
    kgi TEXT,
    kpi TEXT,
    target_persona TEXT,
    usp TEXT,
    customer_journey TEXT,
    total_budget DECIMAL(12, 2) DEFAULT 0.00,
    start_date DATE,
    end_date DATE,
    actual_start_date DATE,
    actual_end_date DATE,
    progress_percentage DECIMAL(5, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (template_project_id) REFERENCES template_projects(id) ON DELETE SET NULL,
    INDEX idx_parent (parent_project_id),
    INDEX idx_template (template_project_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Project Phases =====
CREATE TABLE IF NOT EXISTS project_phases (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    phase_number INT NOT NULL,
    phase_name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'in_progress', 'completed', 'blocked') DEFAULT 'pending',
    budget DECIMAL(12, 2) DEFAULT 0.00,
    actual_cost DECIMAL(12, 2) DEFAULT 0.00,
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_phase (project_id, phase_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Project Tasks (Enhanced with hierarchical support) =====
CREATE TABLE IF NOT EXISTS project_tasks (
    id VARCHAR(36) PRIMARY KEY,
    parent_task_id VARCHAR(36) DEFAULT NULL,
    project_id VARCHAR(36) NOT NULL,
    project_phase_id VARCHAR(36) NOT NULL,
    task_name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'in_progress', 'completed', 'blocked', 'cancelled') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    budget DECIMAL(12, 2) DEFAULT 0.00,
    actual_cost DECIMAL(12, 2) DEFAULT 0.00,
    is_outsourced BOOLEAN DEFAULT FALSE,
    assignee VARCHAR(255),
    start_date DATE,
    end_date DATE,
    due_date DATE,
    completed_date DATE,
    order_index INT DEFAULT 0,
    notes TEXT,
    progress_percentage DECIMAL(5, 2) DEFAULT 0.00,
    FOREIGN KEY (parent_task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (project_phase_id) REFERENCES project_phases(id) ON DELETE CASCADE,
    INDEX idx_parent_task (parent_task_id),
    INDEX idx_project (project_id),
    INDEX idx_phase_order (project_phase_id, order_index),
    INDEX idx_dates (start_date, end_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Analytics Data =====
CREATE TABLE IF NOT EXISTS analytics_data (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    metric_name VARCHAR(255) NOT NULL,
    metric_value VARCHAR(255),
    metric_date DATE,
    source VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_date (project_id, metric_date),
    INDEX idx_metric_name (metric_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Retrospectives =====
CREATE TABLE IF NOT EXISTS retrospectives (
    id VARCHAR(36) PRIMARY KEY,
    project_id VARCHAR(36) NOT NULL,
    keep_points TEXT,
    problem_points TEXT,
    try_points TEXT,
    lessons_learned TEXT,
    next_actions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Task Dependencies (for Gantt chart relationships) =====
CREATE TABLE IF NOT EXISTS task_dependencies (
    id VARCHAR(36) PRIMARY KEY,
    task_id VARCHAR(36) NOT NULL,
    depends_on_task_id VARCHAR(36) NOT NULL,
    dependency_type ENUM('finish_to_start', 'start_to_start', 'finish_to_finish', 'start_to_finish') DEFAULT 'finish_to_start',
    lag_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (depends_on_task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    UNIQUE KEY unique_dependency (task_id, depends_on_task_id),
    INDEX idx_task (task_id),
    INDEX idx_depends_on (depends_on_task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
