<?php
/**
 * Fourth Subject Column Installation Script
 * 
 * This script safely adds the is_fourth_subject column to the student_subjects table
 * Works with all MySQL/MariaDB versions in XAMPP
 */

require_once 'config/database.php';

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Fourth Subject Installation</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css' rel='stylesheet'>
    <link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css' rel='stylesheet'>
    <style>
        body { 
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 40px 20px;
        }
        .container { 
            max-width: 800px; 
            background: white; 
            padding: 40px; 
            border-radius: 15px; 
            box-shadow: 0 10px 40px rgba(0,0,0,0.2); 
        }
        .step { 
            margin: 20px 0; 
            padding: 20px; 
            border-left: 4px solid #0d6efd; 
            background: #f8f9fa;
            border-radius: 5px;
        }
        .success { 
            color: #28a745; 
            background: #d4edda;
            border-left-color: #28a745;
        }
        .error { 
            color: #dc3545; 
            background: #f8d7da;
            border-left-color: #dc3545;
        }
        .warning {
            color: #856404;
            background: #fff3cd;
            border-left-color: #ffc107;
        }
        .info {
            color: #004085;
            background: #cce5ff;
            border-left-color: #007bff;
        }
        h1 { color: #667eea; margin-bottom: 30px; }
        .btn-primary { 
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            border: none;
        }
        .code-block {
            background: #2d2d2d;
            color: #f8f8f2;
            padding: 15px;
            border-radius: 5px;
            font-family: 'Courier New', monospace;
            overflow-x: auto;
        }
    </style>
</head>
<body>
<div class='container'>
    <h1><i class='fas fa-database'></i> Fourth Subject Column Installation</h1>
    <p class='text-muted'>চতুর্থ বিষয় কলাম ইনস্টলেশন</p>
    <hr class='mb-4'>";

$messages = [];
$errors = [];
$warnings = [];

try {
    // Step 1: Check if student_subjects table exists
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 1: Checking student_subjects table...</h5>";
    
    $stmt = $pdo->query("SHOW TABLES LIKE 'student_subjects'");
    $table_exists = $stmt->rowCount() > 0;
    
    if (!$table_exists) {
        // Create the table
        echo "<p><i class='fas fa-info-circle text-info'></i> Table doesn't exist. Creating student_subjects table...</p>";
        
        $sql = "CREATE TABLE student_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT NOT NULL,
            is_fourth_subject TINYINT(1) DEFAULT 0 COMMENT 'Marks subject as fourth/optional - failure in this subject does not cause overall failure',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_subject (student_id, subject_id),
            INDEX idx_fourth_subject (student_id, is_fourth_subject)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $pdo->exec($sql);
        echo "<p class='text-success'><i class='fas fa-check'></i> Table created successfully!</p>";
        $messages[] = "student_subjects table created";
    } else {
        echo "<p class='text-success'><i class='fas fa-check'></i> Table exists.</p>";
    }
    echo "</div>";
    
    // Step 2: Check if is_fourth_subject column exists
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 2: Checking is_fourth_subject column...</h5>";
    
    $stmt = $pdo->query("SHOW COLUMNS FROM student_subjects LIKE 'is_fourth_subject'");
    $column_exists = $stmt->rowCount() > 0;
    
    if (!$column_exists) {
        echo "<p><i class='fas fa-info-circle text-info'></i> Column doesn't exist. Adding is_fourth_subject column...</p>";
        
        $sql = "ALTER TABLE student_subjects 
                ADD COLUMN is_fourth_subject TINYINT(1) DEFAULT 0 
                COMMENT 'Marks subject as fourth/optional - failure in this subject does not cause overall failure'";
        
        $pdo->exec($sql);
        echo "<p class='text-success'><i class='fas fa-check'></i> Column added successfully!</p>";
        $messages[] = "is_fourth_subject column added";
    } else {
        echo "<p class='text-success'><i class='fas fa-check'></i> Column already exists.</p>";
        $warnings[] = "is_fourth_subject column already exists (no action needed)";
    }
    echo "</div>";
    
    // Step 3: Check if updated_at column exists
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 3: Checking updated_at column...</h5>";
    
    $stmt = $pdo->query("SHOW COLUMNS FROM student_subjects LIKE 'updated_at'");
    $updated_at_exists = $stmt->rowCount() > 0;
    
    if (!$updated_at_exists) {
        echo "<p><i class='fas fa-info-circle text-info'></i> Column doesn't exist. Adding updated_at column...</p>";
        
        $sql = "ALTER TABLE student_subjects 
                ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
        
        $pdo->exec($sql);
        echo "<p class='text-success'><i class='fas fa-check'></i> Column added successfully!</p>";
        $messages[] = "updated_at column added";
    } else {
        echo "<p class='text-success'><i class='fas fa-check'></i> Column already exists.</p>";
    }
    echo "</div>";
    
    // Step 4: Create/check index
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 4: Creating index for performance...</h5>";
    
    try {
        $sql = "CREATE INDEX idx_fourth_subject ON student_subjects(student_id, is_fourth_subject)";
        $pdo->exec($sql);
        echo "<p class='text-success'><i class='fas fa-check'></i> Index created successfully!</p>";
        $messages[] = "Performance index created";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info-circle'></i> Index already exists.</p>";
        } else {
            throw $e;
        }
    }
    echo "</div>";
    
    // Step 5: Verify installation
    echo "<div class='step success'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 5: Verification</h5>";
    
    $stmt = $pdo->query("DESCRIBE student_subjects");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p class='mb-3'><strong>Current table structure:</strong></p>";
    echo "<div class='table-responsive'>";
    echo "<table class='table table-sm table-bordered'>";
    echo "<thead><tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr></thead><tbody>";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td><strong>{$col['Field']}</strong></td>";
        echo "<td>{$col['Type']}</td>";
        echo "<td>{$col['Null']}</td>";
        echo "<td>{$col['Key']}</td>";
        echo "<td>" . ($col['Default'] ?? 'NULL') . "</td>";
        echo "<td>{$col['Extra']}</td>";
        echo "</tr>";
    }
    echo "</tbody></table></div>";
    
    // Count existing records
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM student_subjects");
    $count = $stmt->fetch()['total'];
    echo "<p class='mt-3'><i class='fas fa-database'></i> Total records in student_subjects: <strong>{$count}</strong></p>";
    
    // Count fourth subjects
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM student_subjects WHERE is_fourth_subject = 1");
    $fourth_count = $stmt->fetch()['total'];
    echo "<p><i class='fas fa-star'></i> Students with fourth subject assigned: <strong>{$fourth_count}</strong></p>";
    
    echo "</div>";
    
    // Success Summary
    echo "<div class='alert alert-success mt-4' role='alert'>";
    echo "<h4 class='alert-heading'><i class='fas fa-check-circle'></i> Installation Completed Successfully!</h4>";
    echo "<hr>";
    echo "<p class='mb-0'><strong>What was done:</strong></p>";
    echo "<ul class='mb-0'>";
    foreach ($messages as $msg) {
        echo "<li>$msg</li>";
    }
    if (empty($messages)) {
        echo "<li>All components already installed (no changes needed)</li>";
    }
    echo "</ul>";
    echo "</div>";
    
    // Warnings
    if (!empty($warnings)) {
        echo "<div class='alert alert-warning mt-3' role='alert'>";
        echo "<h5><i class='fas fa-exclamation-triangle'></i> Notes:</h5>";
        echo "<ul class='mb-0'>";
        foreach ($warnings as $warning) {
            echo "<li>$warning</li>";
        }
        echo "</ul>";
        echo "</div>";
    }
    
    // Next Steps
    echo "<div class='alert alert-info mt-3' role='alert'>";
    echo "<h5><i class='fas fa-info-circle'></i> Next Steps:</h5>";
    echo "<ol class='mb-0'>";
    echo "<li>Navigate to <a href='students/manage_fourth_subject.php' class='alert-link'>Fourth Subject Management</a></li>";
    echo "<li>Ensure you have classes for grade 11 and 12 (একাদশ, দ্বাদশ)</li>";
    echo "<li>Enroll students in their subjects</li>";
    echo "<li>Start assigning fourth subjects!</li>";
    echo "</ol>";
    echo "</div>";
    
    echo "<div class='d-grid gap-2 mt-4'>";
    echo "<a href='students/manage_fourth_subject.php' class='btn btn-primary btn-lg'>";
    echo "<i class='fas fa-arrow-right'></i> Go to Fourth Subject Management";
    echo "</a>";
    echo "<a href='index.php' class='btn btn-outline-secondary'>";
    echo "<i class='fas fa-home'></i> Back to Dashboard";
    echo "</a>";
    echo "</div>";
    
} catch (PDOException $e) {
    echo "<div class='step error'>";
    echo "<h5><i class='fas fa-exclamation-triangle'></i> Error Occurred</h5>";
    echo "<p><strong>Error Message:</strong></p>";
    echo "<div class='code-block'>" . htmlspecialchars($e->getMessage()) . "</div>";
    echo "<p class='mt-3'><strong>Possible solutions:</strong></p>";
    echo "<ul>";
    echo "<li>Check database connection in <code>config/database.php</code></li>";
    echo "<li>Ensure MySQL/MariaDB server is running</li>";
    echo "<li>Verify database user has ALTER TABLE permissions</li>";
    echo "<li>Check if foreign key constraints are satisfied</li>";
    echo "</ul>";
    echo "</div>";
    
    echo "<div class='alert alert-danger mt-3' role='alert'>";
    echo "<h5><i class='fas fa-times-circle'></i> Installation Failed</h5>";
    echo "<p>Please fix the error above and try again.</p>";
    echo "</div>";
}

echo "</div>
<script src='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js'></script>
</body>
</html>";
?>
