<?php
/**
 * Fourth Subject System - Quick Installation Script
 * 
 * This script will:
 * 1. Create/update student_subjects table
 * 2. Add is_fourth_subject column
 * 3. Create necessary indexes
 * 4. Verify installation
 */

require_once 'config/database.php';

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

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <title>Fourth Subject System Installation</title>
    <link href='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css' rel='stylesheet'>
    <style>
        body { padding: 40px; background: #f5f5f5; }
        .container { max-width: 800px; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        .success { color: green; }
        .error { color: red; }
        .step { margin: 20px 0; padding: 15px; border-left: 4px solid #0d6efd; background: #f8f9fa; }
    </style>
</head>
<body>
<div class='container'>
    <h2><i class='fas fa-cog'></i> চতুর্থ বিষয় সিস্টেম ইনস্টলেশন</h2>
    <p class='text-muted'>Fourth Subject System Installation</p>
    <hr>";

try {
    // Step 1: Create/Check student_subjects table
    echo "<div class='step'>";
    echo "<h5>Step 1: Creating student_subjects table...</h5>";
    
    $sql = "CREATE TABLE IF NOT EXISTS 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)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "<p class='success'>✓ student_subjects table created/verified</p>";
    echo "</div>";
    
    // Step 2: Add is_fourth_subject column if it doesn't exist
    echo "<div class='step'>";
    echo "<h5>Step 2: Adding is_fourth_subject column...</h5>";
    
    try {
        $pdo->exec("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'");
        echo "<p class='success'>✓ is_fourth_subject column added</p>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') !== false) {
            echo "<p class='success'>✓ is_fourth_subject column already exists</p>";
        } else {
            throw $e;
        }
    }
    echo "</div>";
    
    // Step 3: Add updated_at column if it doesn't exist
    echo "<div class='step'>";
    echo "<h5>Step 3: Adding updated_at column...</h5>";
    
    try {
        $pdo->exec("ALTER TABLE student_subjects 
                    ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        echo "<p class='success'>✓ updated_at column added</p>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') !== false) {
            echo "<p class='success'>✓ updated_at column already exists</p>";
        } else {
            throw $e;
        }
    }
    echo "</div>";
    
    // Step 4: Create index
    echo "<div class='step'>";
    echo "<h5>Step 4: Creating indexes...</h5>";
    
    try {
        $pdo->exec("CREATE INDEX idx_fourth_subject ON student_subjects(student_id, is_fourth_subject)");
        echo "<p class='success'>✓ Index created for optimized queries</p>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key') !== false) {
            echo "<p class='success'>✓ Index already exists</p>";
        } else {
            throw $e;
        }
    }
    echo "</div>";
    
    // Step 5: Verify installation
    echo "<div class='step'>";
    echo "<h5>Step 5: Verifying installation...</h5>";
    
    // Check table structure
    $stmt = $pdo->query("DESCRIBE student_subjects");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $required_columns = ['id', 'student_id', 'subject_id', 'is_fourth_subject', 'created_at', 'updated_at'];
    $missing_columns = array_diff($required_columns, $columns);
    
    if (empty($missing_columns)) {
        echo "<p class='success'>✓ All required columns present</p>";
    } else {
        echo "<p class='error'>✗ Missing columns: " . implode(', ', $missing_columns) . "</p>";
    }
    
    // Check indexes
    $stmt = $pdo->query("SHOW INDEX FROM student_subjects");
    $indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "<p class='success'>✓ " . count($indexes) . " indexes found</p>";
    
    // Count existing fourth subject assignments
    $stmt = $pdo->query("SELECT COUNT(*) FROM student_subjects WHERE is_fourth_subject = 1");
    $count = $stmt->fetchColumn();
    echo "<p class='success'>✓ Currently " . $count . " fourth subject assignments</p>";
    
    echo "</div>";
    
    // Step 6: Test queries
    echo "<div class='step'>";
    echo "<h5>Step 6: Testing queries...</h5>";
    
    // Test insert (will rollback)
    $pdo->beginTransaction();
    try {
        $pdo->exec("INSERT INTO student_subjects (student_id, subject_id, is_fourth_subject) 
                    VALUES (99999, 99999, 1) 
                    ON DUPLICATE KEY UPDATE is_fourth_subject = is_fourth_subject");
        echo "<p class='success'>✓ Insert query works</p>";
    } catch (PDOException $e) {
        echo "<p class='success'>✓ Foreign key constraints active (expected error)</p>";
    }
    $pdo->rollBack();
    
    // Test select
    $stmt = $pdo->query("SELECT COUNT(*) FROM student_subjects");
    $total = $stmt->fetchColumn();
    echo "<p class='success'>✓ Select query works (found " . $total . " records)</p>";
    
    echo "</div>";
    
    // Success summary
    echo "<div class='alert alert-success mt-4'>";
    echo "<h4><i class='fas fa-check-circle'></i> Installation Successful!</h4>";
    echo "<p>The Fourth Subject Management System has been installed successfully.</p>";
    echo "<p><strong>Next Steps:</strong></p>";
    echo "<ul>";
    echo "<li>Navigate to <a href='students/manage_fourth_subject.php'>Fourth Subject Management</a></li>";
    echo "<li>Set up fourth subjects for your higher class students</li>";
    echo "<li>Test the API endpoint at <code>/api/check_fourth_subject.php</code></li>";
    echo "<li>Review the documentation in <code>FOURTH_SUBJECT_MANAGEMENT.md</code></li>";
    echo "</ul>";
    echo "</div>";
    
    echo "<div class='mt-3'>";
    echo "<a href='students/manage_fourth_subject.php' class='btn btn-primary'>Go to Fourth Subject Management</a> ";
    echo "<a href='index.php' class='btn btn-secondary'>Back to Dashboard</a>";
    echo "</div>";
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger mt-4'>";
    echo "<h4><i class='fas fa-exclamation-circle'></i> Installation Failed</h4>";
    echo "<p><strong>Error:</strong> " . $e->getMessage() . "</p>";
    echo "<p><strong>File:</strong> " . $e->getFile() . "</p>";
    echo "<p><strong>Line:</strong> " . $e->getLine() . "</p>";
    echo "<p>Please check your database credentials and try again.</p>";
    echo "</div>";
    
    echo "<div class='mt-3'>";
    echo "<a href='install_fourth_subject.php' class='btn btn-warning'>Retry Installation</a> ";
    echo "<a href='index.php' class='btn btn-secondary'>Back to Dashboard</a>";
    echo "</div>";
}

echo "</div>
</body>
</html>";
?>
