<?php
session_start();
require_once '../config/database.php';

// Auth check
if (!isset($_SESSION['user_id']) || ($_SESSION['user_role'] != 'admin' && $_SESSION['user_role'] != 'teacher')) {
    header('Location: ../login.php');
    exit();
}

echo "<!DOCTYPE html>
<html lang='bn'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Session Diagnostic Tool</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'>
</head>
<body>
<div class='container mt-4'>
    <div class='card'>
        <div class='card-header'>
            <h4><i class='fas fa-diagnoses'></i> Session Diagnostic Tool</h4>
        </div>
        <div class='card-body'>";

try {
    // 1. Check all sessions in academic_sessions table
    echo "<h5>1. All Sessions in Database</h5>";
    $stmt = $pdo->query("SELECT session_name, status, created_at FROM academic_sessions ORDER BY session_name DESC");
    $sessions = $stmt->fetchAll();
    
    if (empty($sessions)) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No sessions found in academic_sessions table
              </div>";
    } else {
        echo "<div class='alert alert-info'>
                <i class='fas fa-info-circle'></i> 
                Found " . count($sessions) . " sessions in database
              </div>";
              
        echo "<div class='table-responsive'>
                <table class='table table-striped table-bordered'>
                    <thead>
                        <tr>
                            <th>Session Name</th>
                            <th>Status</th>
                            <th>Created At</th>
                        </tr>
                    </thead>
                    <tbody>";
        
        foreach ($sessions as $session) {
            $row_class = ($session['status'] == 'active') ? 'table-success' : 'table-warning';
            
            echo "<tr class='$row_class'>
                    <td>{$session['session_name']}</td>
                    <td>{$session['status']}</td>
                    <td>" . ($session['created_at'] ?? 'N/A') . "</td>
                  </tr>";
        }
        
        echo "      </tbody>
                </table>
              </div>";
    }
    
    // 2. Check active sessions only (what subject_enrollment_tools.php loads)
    echo "<h5>2. Active Sessions (What Appears in Dropdown)</h5>";
    $stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
    $active_sessions = $stmt->fetchAll();
    
    if (empty($active_sessions)) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No active sessions found (these are what appear in the dropdown)
              </div>";
    } else {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                Found " . count($active_sessions) . " active sessions
              </div>";
              
        echo "<ul>";
        foreach ($active_sessions as $session) {
            echo "<li>{$session['session_name']}</li>";
        }
        echo "</ul>";
    }
    
    // 3. Check what sessions students are actually using
    echo "<h5>3. Sessions Used by Students</h5>";
    $stmt = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session DESC");
    $student_sessions = $stmt->fetchAll();
    
    if (empty($student_sessions)) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                No sessions found in student records
              </div>";
    } else {
        echo "<div class='alert alert-info'>
                <i class='fas fa-info-circle'></i> 
                Found " . count($student_sessions) . " sessions in student records
              </div>";
              
        echo "<ul>";
        foreach ($student_sessions as $session) {
            echo "<li>{$session['session']}</li>";
        }
        echo "</ul>";
    }
    
    // 4. Compare sessions
    echo "<h5>4. Session Comparison</h5>";
    $db_session_names = array_column($sessions, 'session_name');
    $active_session_names = array_column($active_sessions, 'session_name');
    $student_session_names = array_column($student_sessions, 'session');
    
    // Find sessions in student records but not in academic_sessions
    $missing_from_db = array_diff($student_session_names, $db_session_names);
    if (!empty($missing_from_db)) {
        echo "<div class='alert alert-danger'>
                <i class='fas fa-exclamation-triangle'></i> 
                <strong>Critical:</strong> These sessions are used by students but missing from academic_sessions table:
                <ul>";
        foreach ($missing_from_db as $session) {
            echo "<li>$session</li>";
        }
        echo "</ul>
              </div>";
    }
    
    // Find sessions in academic_sessions but not marked as active
    $inactive_sessions = array_filter($sessions, function($s) { return $s['status'] != 'active'; });
    if (!empty($inactive_sessions)) {
        echo "<div class='alert alert-warning'>
                <i class='fas fa-exclamation-triangle'></i> 
                <strong>Warning:</strong> These sessions exist in database but are not active:
                <ul>";
        foreach ($inactive_sessions as $session) {
            echo "<li>{$session['session_name']} (Status: {$session['status']})</li>";
        }
        echo "</ul>
              </div>";
    }
    
    if (empty($missing_from_db) && empty($inactive_sessions)) {
        echo "<div class='alert alert-success'>
                <i class='fas fa-check-circle'></i> 
                All sessions appear to be properly configured
              </div>";
    }
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger'>
            <i class='fas fa-exclamation-triangle'></i> 
            Error: " . $e->getMessage() . "
          </div>";
}

echo "<div class='mt-4'>
        <a href='subject_enrollment_tools.php' class='btn btn-primary'>
            <i class='fas fa-arrow-left'></i> Back to Subject Tools
        </a>
        <a href='../admin/sessions.php' class='btn btn-secondary'>
            <i class='fas fa-cog'></i> Manage Sessions
        </a>
      </div>";

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