-- Next Task Intelligence System Database Schema
-- Run this migration to add tables for the intelligent task recommendation system

-- Table to log task recommendations for learning algorithm
CREATE TABLE IF NOT EXISTS task_recommendations_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    task_id VARCHAR(255) NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    task_name TEXT NOT NULL,
    intelligence_score DECIMAL(5,2) NOT NULL,
    factors_json JSON NOT NULL,
    recommended_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_action ENUM('accepted', 'rejected', 'ignored') DEFAULT NULL,
    action_at TIMESTAMP NULL,
    feedback_score INT NULL, -- 1-5 rating from user
    INDEX idx_user_id (user_id),
    INDEX idx_recommended_at (recommended_at),
    INDEX idx_user_action (user_action)
);

-- Table to track user task preferences and patterns
CREATE TABLE IF NOT EXISTS user_task_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    preference_type ENUM('project', 'task_type', 'complexity', 'duration', 'skill_area') NOT NULL,
    preference_value VARCHAR(255) NOT NULL,
    preference_score DECIMAL(3,2) NOT NULL DEFAULT 1.00, -- 0.00 to 1.00
    learned_from_actions INT DEFAULT 0, -- Count of actions this preference was learned from
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_preference (user_id, preference_type, preference_value),
    INDEX idx_user_id (user_id),
    INDEX idx_preference_type (preference_type)
);

-- Table to track user workload patterns
CREATE TABLE IF NOT EXISTS user_workload_patterns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date DATE NOT NULL,
    total_tasks INT DEFAULT 0,
    completed_tasks INT DEFAULT 0,
    in_progress_tasks INT DEFAULT 0,
    average_task_duration INT DEFAULT 0, -- in minutes
    productivity_score DECIMAL(3,2) DEFAULT 0, -- calculated metric
    workload_level ENUM('none', 'low', 'medium', 'high', 'overloaded') DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_date (user_id, date),
    INDEX idx_user_id (user_id),
    INDEX idx_date (date)
);

-- Table to store task complexity analysis
CREATE TABLE IF NOT EXISTS task_complexity_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(255) NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    estimated_complexity DECIMAL(2,1) NOT NULL, -- 1.0 to 5.0 scale
    actual_complexity DECIMAL(2,1) NULL, -- Updated based on completion time/feedback
    complexity_factors JSON NOT NULL, -- Factors used in complexity calculation
    keyword_matches JSON NULL, -- Keywords that influenced complexity
    analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_task_id (task_id),
    INDEX idx_project_name (project_name),
    INDEX idx_estimated_complexity (estimated_complexity)
);

-- Table to track project health and team performance metrics
CREATE TABLE IF NOT EXISTS project_intelligence_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    metric_date DATE NOT NULL,
    total_tasks INT DEFAULT 0,
    completed_tasks INT DEFAULT 0,
    overdue_tasks INT DEFAULT 0,
    average_completion_time DECIMAL(6,2) DEFAULT 0, -- in hours
    team_productivity_score DECIMAL(3,2) DEFAULT 0,
    deadline_compliance_rate DECIMAL(3,2) DEFAULT 0, -- percentage
    task_assignment_efficiency DECIMAL(3,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_project_date (project_id, metric_date),
    INDEX idx_project_id (project_id),
    INDEX idx_metric_date (metric_date)
);

-- Add some sample data for testing (optional)
-- INSERT INTO user_task_preferences (user_id, preference_type, preference_value, preference_score) VALUES
-- (1, 'task_type', 'bug_fix', 0.8),
-- (1, 'complexity', 'medium', 0.9),
-- (1, 'project', 'web_development', 0.7);

-- Create indexes for better performance
CREATE INDEX idx_task_recommendations_user_project ON task_recommendations_log (user_id, project_name);
CREATE INDEX idx_user_preferences_score ON user_task_preferences (preference_score DESC);
CREATE INDEX idx_workload_patterns_level ON user_workload_patterns (workload_level, date);