<?php
/**
 * سكريبت لتثبيت جداول وأعمدة المعايرة
 * Script to install calibration tables and columns
 */

require_once __DIR__ . '/config/config.php';

echo "═══════════════════════════════════════════════════════════\n";
echo "   تثبيت جداول وأعمدة المعايرة\n";
echo "   Installing Calibration Tables and Columns\n";
echo "═══════════════════════════════════════════════════════════\n\n";

try {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    
    echo "✓ الاتصال بقاعدة البيانات نجح!\n\n";
    
    // التحقق من وجود الأعمدة في جدول grades
    echo "التحقق من أعمدة جدول grades...\n";
    $columns = $db->fetchAll("SHOW COLUMNS FROM grades");
    $existing_columns = array_column($columns, 'Field');
    
    $columns_to_add = [
        'calibrated_marks' => "ADD COLUMN calibrated_marks DECIMAL(5,2) NULL AFTER points",
        'calibrated_letter_grade' => "ADD COLUMN calibrated_letter_grade VARCHAR(5) NULL AFTER calibrated_marks",
        'calibrated_points' => "ADD COLUMN calibrated_points DECIMAL(3,2) NULL AFTER calibrated_letter_grade",
        'calibration_target_mean' => "ADD COLUMN calibration_target_mean DECIMAL(5,2) NULL AFTER calibrated_points",
        'calibration_target_std' => "ADD COLUMN calibration_target_std DECIMAL(5,2) NULL AFTER calibration_target_mean",
        'calibrated_at' => "ADD COLUMN calibrated_at TIMESTAMP NULL AFTER calibration_target_std",
        'calibrated_by' => "ADD COLUMN calibrated_by INT NULL AFTER calibrated_at"
    ];
    
    $added_columns = 0;
    foreach ($columns_to_add as $column_name => $sql) {
        if (!in_array($column_name, $existing_columns)) {
            try {
                $db->query("ALTER TABLE grades " . $sql);
                echo "  ✓ تم إضافة العمود: $column_name\n";
                $added_columns++;
            } catch (Exception $e) {
                echo "  ✗ خطأ في إضافة العمود $column_name: " . $e->getMessage() . "\n";
            }
        } else {
            echo "  - العمود $column_name موجود بالفعل\n";
        }
    }
    
    // إضافة Foreign Key للعمود calibrated_by إذا لم يكن موجوداً
    if (in_array('calibrated_by', $existing_columns)) {
        try {
            // التحقق من وجود Foreign Key
            $fks = $db->fetchAll("
                SELECT CONSTRAINT_NAME 
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'grades' 
                AND COLUMN_NAME = 'calibrated_by'
            ");
            
            if (empty($fks)) {
                $db->query("ALTER TABLE grades ADD FOREIGN KEY (calibrated_by) REFERENCES users(id) ON DELETE SET NULL");
                echo "  ✓ تم إضافة Foreign Key للعمود calibrated_by\n";
            } else {
                echo "  - Foreign Key للعمود calibrated_by موجود بالفعل\n";
            }
        } catch (Exception $e) {
            echo "  ⚠ تحذير: " . $e->getMessage() . "\n";
        }
    }
    
    echo "\n";
    
    // التحقق من وجود جدول semester_calibrations
    echo "التحقق من جدول semester_calibrations...\n";
    try {
        $table_exists = $db->fetchOne("SHOW TABLES LIKE 'semester_calibrations'");
        
        if (!$table_exists) {
            echo "  إنشاء جدول semester_calibrations...\n";
            $db->query("
                CREATE TABLE semester_calibrations (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    semester_id INT NOT NULL,
                    target_mean DECIMAL(5,2) NOT NULL,
                    target_std DECIMAL(5,2) NOT NULL,
                    calibrated_by INT NOT NULL,
                    calibrated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE,
                    FOREIGN KEY (calibrated_by) REFERENCES users(id) ON DELETE RESTRICT,
                    UNIQUE KEY unique_semester_calibration (semester_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
            echo "  ✓ تم إنشاء جدول semester_calibrations بنجاح!\n";
        } else {
            echo "  - جدول semester_calibrations موجود بالفعل\n";
        }
    } catch (Exception $e) {
        echo "  ✗ خطأ في إنشاء الجدول: " . $e->getMessage() . "\n";
    }
    
    echo "\n";
    echo "═══════════════════════════════════════════════════════════\n";
    echo "   ✅ تم التثبيت بنجاح!\n";
    echo "   ✅ Installation completed successfully!\n";
    echo "═══════════════════════════════════════════════════════════\n";
    
} catch (Exception $e) {
    echo "\n";
    echo "═══════════════════════════════════════════════════════════\n";
    echo "   ✗ حدث خطأ!\n";
    echo "   ✗ An error occurred!\n";
    echo "═══════════════════════════════════════════════════════════\n";
    echo "الخطأ: " . $e->getMessage() . "\n";
    echo "Error: " . $e->getMessage() . "\n";
    exit(1);
}

