<?php
require_once '../config/database.php';

echo "Setting up AI Performance Analyzer tables...\n\n";

try {
    // Create AI performance predictions table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS ai_performance_predictions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT NOT NULL,
            exam_type ENUM('midterm', 'final', 'test') NOT NULL,
            session VARCHAR(20) NOT NULL,
            predicted_grade VARCHAR(5),
            predicted_marks DECIMAL(5,2),
            confidence_score DECIMAL(3,2),
            analysis_date DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            INDEX idx_student_subject (student_id, subject_id),
            INDEX idx_session_exam (session, exam_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    echo "✓ Created ai_performance_predictions table\n";
    
    // Create AI learning patterns table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS ai_learning_patterns (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT NOT NULL,
            pattern_type ENUM('improvement', 'decline', 'stable', 'volatile') NOT NULL,
            trend_slope DECIMAL(5,2),
            consistency_score DECIMAL(3,2),
            last_updated DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            INDEX idx_student_pattern (student_id, pattern_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    echo "✓ Created ai_learning_patterns table\n";
    
    // Create AI recommendations table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS ai_recommendations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT,
            recommendation_type ENUM('study_tips', 'focus_areas', 'improvement_strategies', 'warning') NOT NULL,
            priority ENUM('high', 'medium', 'low') DEFAULT 'medium',
            title VARCHAR(200) NOT NULL,
            description TEXT NOT NULL,
            suggested_action TEXT,
            generated_date DATE NOT NULL,
            status ENUM('pending', 'implemented', 'dismissed') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            INDEX idx_student_rec (student_id, recommendation_type),
            INDEX idx_priority_status (priority, status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    echo "✓ Created ai_recommendations table\n";
    
    // Create AI analytics dashboard data table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS ai_dashboard_analytics (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            session VARCHAR(20) NOT NULL,
            subject_id INT,
            metric_type ENUM('average_performance', 'pass_rate', 'top_performers', 'at_risk_students') NOT NULL,
            value DECIMAL(5,2),
            comparison_value DECIMAL(5,2),
            trend_direction ENUM('up', 'down', 'stable') DEFAULT 'stable',
            analysis_period DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            INDEX idx_class_session (class_id, session),
            INDEX idx_metric_type (metric_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    echo "✓ Created ai_dashboard_analytics table\n";
    
    // Create AI student risk assessment table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS ai_risk_assessments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            risk_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
            risk_factors JSON,
            overall_score DECIMAL(3,2),
            assessment_date DATE NOT NULL,
            next_review_date DATE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            INDEX idx_student_risk (student_id, risk_level),
            INDEX idx_assessment_date (assessment_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    echo "✓ Created ai_risk_assessments table\n";
    
    echo "\n✅ All AI Performance Analyzer tables created successfully!\n";
    echo "You can now access the AI tools at: http://localhost/bidda/ai_performance_analyzer/\n";
    
} catch (Exception $e) {
    echo "❌ Error creating tables: " . $e->getMessage() . "\n";
}
?>