<?php
/**
 * Add session_id Column to Students Table
 * This enables session filtering in the students with subjects page
 */

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>Add Session Column - 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;
        }
        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;
            font-size: 0.9rem;
        }
    </style>
</head>
<body>
<div class='container'>
    <h1><i class='fas fa-database'></i> Add Session Column to Students Table</h1>
    <p class='text-muted'>সেশন কলাম ইনস্টলেশন - Students Table</p>
    <hr class='mb-4'>";

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

try {
    // Step 1: Check if session_id column already exists
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-search'></i> Step 1: Checking if session_id column exists...</h5>";
    
    $stmt = $pdo->query("SHOW COLUMNS FROM students LIKE 'session_id'");
    $column_exists = $stmt->rowCount() > 0;
    
    if ($column_exists) {
        echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> Column already exists!</p>";
        $warnings[] = "session_id column already exists in students table";
    } else {
        echo "<p class='text-info'><i class='fas fa-info-circle'></i> Column doesn't exist. Will add it.</p>";
    }
    echo "</div>";
    
    // Step 2: Check if academic_sessions table exists
    echo "<div class='step'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 2: Checking academic_sessions table...</h5>";
    
    $stmt = $pdo->query("SHOW TABLES LIKE 'academic_sessions'");
    $sessions_table_exists = $stmt->rowCount() > 0;
    
    if (!$sessions_table_exists) {
        throw new Exception("academic_sessions table doesn't exist! Please create it first.");
    }
    
    echo "<p class='text-success'><i class='fas fa-check'></i> academic_sessions table exists.</p>";
    
    // Get count of sessions
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM academic_sessions");
    $session_count = $stmt->fetch()['count'];
    echo "<p><i class='fas fa-calendar'></i> Found {$session_count} academic session(s)</p>";
    
    echo "</div>";
    
    // Step 3: Add session_id column if it doesn't exist
    if (!$column_exists) {
        echo "<div class='step'>";
        echo "<h5><i class='fas fa-plus-circle'></i> Step 3: Adding session_id column...</h5>";
        
        $sql = "ALTER TABLE students 
                ADD COLUMN session_id INT NULL 
                COMMENT 'Academic session/year' 
                AFTER class_id";
        
        $pdo->exec($sql);
        echo "<p class='text-success'><i class='fas fa-check'></i> Column added successfully!</p>";
        $messages[] = "session_id column added to students table";
        echo "</div>";
        
        // Step 4: Add foreign key constraint
        echo "<div class='step'>";
        echo "<h5><i class='fas fa-link'></i> Step 4: Adding foreign key constraint...</h5>";
        
        try {
            $sql = "ALTER TABLE students 
                    ADD CONSTRAINT fk_students_session 
                    FOREIGN KEY (session_id) REFERENCES academic_sessions(id) 
                    ON DELETE SET NULL 
                    ON UPDATE CASCADE";
            
            $pdo->exec($sql);
            echo "<p class='text-success'><i class='fas fa-check'></i> Foreign key constraint added!</p>";
            $messages[] = "Foreign key constraint added";
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate key') !== false) {
                echo "<p class='text-info'><i class='fas fa-info-circle'></i> Foreign key already exists.</p>";
            } else {
                echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> Could not add foreign key: " . $e->getMessage() . "</p>";
                $warnings[] = "Foreign key constraint could not be added (non-critical)";
            }
        }
        echo "</div>";
        
        // Step 5: Add index
        echo "<div class='step'>";
        echo "<h5><i class='fas fa-bolt'></i> Step 5: Adding index for performance...</h5>";
        
        try {
            $sql = "CREATE INDEX idx_session_id ON students(session_id)";
            $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') !== false) {
                echo "<p class='text-info'><i class='fas fa-info-circle'></i> Index already exists.</p>";
            } else {
                throw $e;
            }
        }
        echo "</div>";
        
        // Step 6: Set current session for active students
        echo "<div class='step'>";
        echo "<h5><i class='fas fa-sync'></i> Step 6: Setting current session for existing active students...</h5>";
        
        $stmt = $pdo->query("SELECT id FROM academic_sessions WHERE is_current = 1 LIMIT 1");
        $current_session = $stmt->fetch();
        
        if ($current_session) {
            $sql = "UPDATE students 
                    SET session_id = ? 
                    WHERE session_id IS NULL AND status = 'active'";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$current_session['id']]);
            $affected = $stmt->rowCount();
            
            echo "<p class='text-success'><i class='fas fa-check'></i> Updated {$affected} active student(s) with current session.</p>";
            $messages[] = "Updated {$affected} students with current session";
        } else {
            echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> No current session found. Students not updated.</p>";
            $warnings[] = "No current session found - please set one in academic sessions";
        }
        echo "</div>";
    } else {
        echo "<div class='step warning'>";
        echo "<h5><i class='fas fa-info-circle'></i> Column Already Exists</h5>";
        echo "<p>The session_id column already exists in the students table. No changes needed.</p>";
        echo "</div>";
    }
    
    // Step 7: Verification
    echo "<div class='step success'>";
    echo "<h5><i class='fas fa-check-circle'></i> Step 7: Verification</h5>";
    
    // Show table structure
    $stmt = $pdo->query("DESCRIBE students");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p class='mb-3'><strong>Current students 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></tr></thead><tbody>";
    foreach ($columns as $col) {
        $highlight = ($col['Field'] === 'session_id') ? ' style="background: #d4edda;"' : '';
        echo "<tr{$highlight}>";
        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 "</tr>";
    }
    echo "</tbody></table></div>";
    
    // Show sample data
    $stmt = $pdo->query("
        SELECT 
            s.id,
            s.student_id,
            s.first_name,
            s.last_name,
            c.name as class_name,
            ses.session_name,
            s.status
        FROM students s
        LEFT JOIN classes c ON s.class_id = c.id
        LEFT JOIN academic_sessions ses ON s.session_id = ses.id
        ORDER BY s.id DESC
        LIMIT 5
    ");
    $sample_students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($sample_students)) {
        echo "<p class='mb-3 mt-4'><strong>Sample students with session data:</strong></p>";
        echo "<div class='table-responsive'>";
        echo "<table class='table table-sm table-bordered'>";
        echo "<thead><tr><th>ID</th><th>Student ID</th><th>Name</th><th>Class</th><th>Session</th><th>Status</th></tr></thead><tbody>";
        foreach ($sample_students as $student) {
            echo "<tr>";
            echo "<td>{$student['id']}</td>";
            echo "<td>{$student['student_id']}</td>";
            echo "<td>{$student['first_name']} {$student['last_name']}</td>";
            echo "<td>{$student['class_name']}</td>";
            echo "<td>" . ($student['session_name'] ?? '<span class="text-muted">Not set</span>') . "</td>";
            echo "<td>{$student['status']}</td>";
            echo "</tr>";
        }
        echo "</tbody></table></div>";
    }
    
    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>Summary:</strong></p>";
    echo "<ul class='mb-0'>";
    if (!empty($messages)) {
        foreach ($messages as $msg) {
            echo "<li>✅ $msg</li>";
        }
    } else {
        echo "<li>✅ session_id column already existed (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>Go to <a href='students/students_with_subjects.php' class='alert-link'>Students with Subjects</a> page</li>";
    echo "<li>The <strong>Academic Session</strong> filter is now available!</li>";
    echo "<li>Filter students by session/year</li>";
    echo "<li>Assign sessions to students as needed</li>";
    echo "</ol>";
    echo "</div>";
    
    echo "<div class='d-grid gap-2 mt-4'>";
    echo "<a href='students/students_with_subjects.php' class='btn btn-primary btn-lg'>";
    echo "<i class='fas fa-arrow-right'></i> Go to Students with Subjects Page";
    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> Database Error</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 academic_sessions table exists</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 "<a href='install_session_column.php' class='btn btn-danger'>Retry Installation</a>";
    echo "</div>";
} catch (Exception $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 "</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>" . htmlspecialchars($e->getMessage()) . "</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>";
?>
