-- Missing project tables extracted from project.sql
-- Run this on your target DB to add tables that were present in project.sql but missing from your admin dump
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `projects` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `branch_id` BIGINT UNSIGNED NULL,
    `department_id` BIGINT UNSIGNED NULL,
    `project_code` VARCHAR(50) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `estimated_duration_days` INT,
    `budget` DECIMAL(15,2),
    `actual_cost` DECIMAL(15,2) DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `status` ENUM('planning', 'active', 'on_hold', 'completed', 'cancelled') DEFAULT 'planning',
    `progress_percentage` DECIMAL(5,2) DEFAULT 0,
    `project_manager_id` BIGINT UNSIGNED NULL,
    `sponsor` VARCHAR(255),
    `customer_id` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`project_manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_projects_company` (`company_id`),
    INDEX `idx_projects_status` (`status`),
    INDEX `idx_projects_manager` (`project_manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_tasks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `parent_task_id` BIGINT UNSIGNED NULL,
    `task_code` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `task_type` ENUM('task', 'milestone', 'phase', 'subtask') DEFAULT 'task',
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `status` ENUM('not_started', 'in_progress', 'completed', 'blocked', 'cancelled') DEFAULT 'not_started',
    `start_date` DATE,
    `due_date` DATE,
    `completed_date` DATE NULL,
    `estimated_hours` DECIMAL(10,2),
    `actual_hours` DECIMAL(10,2) DEFAULT 0,
    `progress_percentage` DECIMAL(5,2) DEFAULT 0,
    `assigned_to` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NOT NULL,
    `completed_by` BIGINT UNSIGNED NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_task_id`) REFERENCES `project_tasks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`completed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_task_code_project` (`project_id`, `task_code`),
    INDEX `idx_project_tasks_project` (`project_id`),
    INDEX `idx_project_tasks_assigned` (`assigned_to`),
    INDEX `idx_project_tasks_status` (`status`),
    INDEX `idx_project_tasks_dates` (`start_date`, `due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_team` (
    `project_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `role` VARCHAR(100),
    `assignment_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `allocation_percentage` DECIMAL(5,2) DEFAULT 100,
    `is_lead` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`project_id`, `user_id`),
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_project_team_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `time_entries` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `project_id` BIGINT UNSIGNED NULL,
    `task_id` BIGINT UNSIGNED NULL,
    `date` DATE NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME,
    `duration_minutes` INT,
    `description` TEXT,
    `billable` BOOLEAN DEFAULT TRUE,
    `overtime` BOOLEAN DEFAULT FALSE,
    `status` ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft',
    `approved_by` BIGINT UNSIGNED NULL,
    `approved_at` TIMESTAMP NULL,
    `rejection_reason` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`task_id`) REFERENCES `project_tasks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_time_entries_user` (`user_id`),
    INDEX `idx_time_entries_project` (`project_id`),
    INDEX `idx_time_entries_task` (`task_id`),
    INDEX `idx_time_entries_date` (`date`),
    INDEX `idx_time_entries_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_budgets` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `category` VARCHAR(100) NOT NULL,
    `budgeted_amount` DECIMAL(15,2) NOT NULL,
    `committed_amount` DECIMAL(15,2) DEFAULT 0,
    `actual_amount` DECIMAL(15,2) DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `notes` 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,
    UNIQUE KEY `unique_project_category` (`project_id`, `category`),
    INDEX `idx_project_budgets_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_risks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `project_id` BIGINT UNSIGNED NOT NULL,
    `risk_code` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `category` VARCHAR(100),
    `probability` ENUM('very_low', 'low', 'medium', 'high', 'very_high') DEFAULT 'medium',
    `impact` ENUM('very_low', 'low', 'medium', 'high', 'very_high') DEFAULT 'medium',
    `risk_score` DECIMAL(5,2) GENERATED ALWAYS AS (
        CASE 
            WHEN probability = 'very_low' THEN 1
            WHEN probability = 'low' THEN 2
            WHEN probability = 'medium' THEN 3
            WHEN probability = 'high' THEN 4
            WHEN probability = 'very_high' THEN 5
        END * 
        CASE 
            WHEN impact = 'very_low' THEN 1
            WHEN impact = 'low' THEN 2
            WHEN impact = 'medium' THEN 3
            WHEN impact = 'high' THEN 4
            WHEN impact = 'very_high' THEN 5
        END
    ) STORED,
    `mitigation_plan` TEXT,
    `contingency_plan` TEXT,
    `owner` BIGINT UNSIGNED NULL,
    `status` ENUM('identified', 'analyzing', 'mitigating', 'monitoring', 'closed') DEFAULT 'identified',
    `closure_date` DATE NULL,
    `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,
    FOREIGN KEY (`owner`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_risk_code_project` (`project_id`, `risk_code`),
    INDEX `idx_project_risks_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;