-- Task Preference Learning System Database Migration
-- Creates tables for storing user preferences and learning data

-- Table to store detailed feedback logs for machine learning
CREATE TABLE IF NOT EXISTS user_task_preferences_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    task_id VARCHAR(255) NOT NULL,
    project_name VARCHAR(255),
    priority ENUM('low', 'medium', 'high', 'urgent'),
    estimated_time INT DEFAULT 0,
    complexity INT DEFAULT 3,
    task_type VARCHAR(100) DEFAULT 'general',
    skills JSON,
    user_action ENUM('accepted', 'rejected', 'ignored') NOT NULL,
    feedback_score INT NULL CHECK (feedback_score BETWEEN 1 AND 5),
    intelligence_score DECIMAL(5,2) DEFAULT 0,
    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,
    UNIQUE KEY unique_user_task (user_id, task_id),
    INDEX idx_user_created (user_id, created_at),
    INDEX idx_action (user_action),
    INDEX idx_project (project_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to store aggregated user preferences and weights
CREATE TABLE IF NOT EXISTS user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    preferred_projects JSON DEFAULT '{}',
    preferred_priorities JSON DEFAULT '{}',
    preferred_time_ranges JSON DEFAULT '{}',
    preferred_complexity JSON DEFAULT '{}',
    preferred_task_types JSON DEFAULT '{}',
    preferred_skills JSON DEFAULT '{}',
    avoidance_patterns JSON DEFAULT '{}',
    total_feedbacks INT DEFAULT 0,
    acceptance_rate DECIMAL(5,2) DEFAULT 0,
    average_rating DECIMAL(3,2) DEFAULT 0,
    learning_confidence DECIMAL(5,2) DEFAULT 0,
    last_learning_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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,
    INDEX idx_user (user_id),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to store learning model versions and parameters
CREATE TABLE IF NOT EXISTS learning_model_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    model_version VARCHAR(50) NOT NULL,
    learning_rate DECIMAL(4,3) DEFAULT 0.1,
    weight_decay DECIMAL(4,3) DEFAULT 0.01,
    feedback_threshold INT DEFAULT 10,
    preference_threshold DECIMAL(3,2) DEFAULT 0.7,
    config_parameters JSON DEFAULT '{}',
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_version (model_version),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to track learning performance metrics
CREATE TABLE IF NOT EXISTS learning_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    metric_date DATE NOT NULL,
    recommendation_accuracy DECIMAL(5,2) DEFAULT 0,
    user_satisfaction DECIMAL(5,2) DEFAULT 0,
    task_completion_rate DECIMAL(5,2) DEFAULT 0,
    preference_stability DECIMAL(5,2) DEFAULT 0,
    learning_progress DECIMAL(5,2) DEFAULT 0,
    total_recommendations INT DEFAULT 0,
    accepted_recommendations INT DEFAULT 0,
    rejected_recommendations INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_date (user_id, metric_date),
    INDEX idx_user_date (user_id, metric_date),
    INDEX idx_date (metric_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial learning model configuration
INSERT IGNORE INTO learning_model_config (
    model_version, learning_rate, weight_decay, feedback_threshold,
    preference_threshold, is_active, config_parameters
) VALUES (
    'v1.0.0', 0.1, 0.01, 10, 0.7, TRUE,
    JSON_OBJECT(
        'algorithm', 'weighted_feedback',
        'min_samples_for_learning', 5,
        'max_preference_weight', 1.0,
        'min_preference_weight', 0.0,
        'temporal_decay_factor', 0.95,
        'skill_matching_weight', 0.15,
        'project_affinity_weight', 0.25,
        'priority_alignment_weight', 0.2
    )
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_preferences_log_user_time ON user_task_preferences_log(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_preferences_log_feedback ON user_task_preferences_log(user_action, feedback_score);
CREATE INDEX IF NOT EXISTS idx_user_preferences_updated ON user_preferences(user_id, updated_at);

-- Create view for quick preference analysis
CREATE OR REPLACE VIEW user_preference_summary AS
SELECT 
    up.user_id,
    u.full_name,
    u.designation,
    up.total_feedbacks,
    up.acceptance_rate,
    up.average_rating,
    up.learning_confidence,
    DATEDIFF(NOW(), up.last_learning_update) as days_since_update,
    COUNT(utpl.id) as recent_feedbacks
FROM user_preferences up
JOIN users u ON u.id = up.user_id
LEFT JOIN user_task_preferences_log utpl ON utpl.user_id = up.user_id 
    AND utpl.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY up.user_id, u.full_name, u.designation, up.total_feedbacks, 
         up.acceptance_rate, up.average_rating, up.learning_confidence, 
         up.last_learning_update;

-- Create view for learning analytics dashboard
CREATE OR REPLACE VIEW learning_analytics_summary AS
SELECT 
    DATE(utpl.created_at) as feedback_date,
    COUNT(*) as total_feedbacks,
    COUNT(CASE WHEN utpl.user_action = 'accepted' THEN 1 END) as accepted_count,
    COUNT(CASE WHEN utpl.user_action = 'rejected' THEN 1 END) as rejected_count,
    COUNT(CASE WHEN utpl.user_action = 'ignored' THEN 1 END) as ignored_count,
    AVG(utpl.feedback_score) as avg_rating,
    AVG(utpl.intelligence_score) as avg_intelligence_score,
    COUNT(DISTINCT utpl.user_id) as active_users
FROM user_task_preferences_log utpl
WHERE utpl.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(utpl.created_at)
ORDER BY feedback_date DESC;