<?php
require_once 'config/database.php';

echo "<h2>Session Data Analysis</h2>";

try {
    // Check if academic_sessions table exists and what data it contains
    echo "<h3>Academic Sessions Table:</h3>";
    try {
        $stmt = $pdo->query("SELECT * FROM academic_sessions LIMIT 5");
        $sessions = $stmt->fetchAll();
        if (!empty($sessions)) {
            echo "Found " . count($sessions) . " sessions:<br>";
            foreach ($sessions as $session) {
                echo "- " . htmlspecialchars($session['session_name']) . " (Status: " . htmlspecialchars($session['status']) . ")<br>";
            }
        } else {
            echo "No sessions found in academic_sessions table<br>";
        }
    } catch (Exception $e) {
        echo "academic_sessions table doesn't exist or is empty<br>";
    }
    
    // Check classes table for session-like information
    echo "<h3>Classes Information:</h3>";
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name");
    $classes = $stmt->fetchAll();
    echo "Active classes found: " . count($classes) . "<br>";
    foreach ($classes as $class) {
        echo "- ID: " . $class['id'] . " | Name: " . htmlspecialchars($class['name']) . " | Section: " . htmlspecialchars($class['section']) . "<br>";
    }
    
    // Check students table structure for session-related fields
    echo "<h3>Students Table Structure (Session-related fields):</h3>";
    $stmt = $pdo->query("DESCRIBE students");
    $columns = $stmt->fetchAll();
    
    $session_fields = [];
    foreach ($columns as $column) {
        $field_name = strtolower($column['Field']);
        if (strpos($field_name, 'session') !== false || 
            strpos($field_name, 'year') !== false || 
            strpos($field_name, 'academic') !== false) {
            $session_fields[] = $column;
        }
    }
    
    if (!empty($session_fields)) {
        echo "Session-related fields found:<br>";
        foreach ($session_fields as $field) {
            echo "- " . $field['Field'] . " (" . $field['Type'] . ")<br>";
            
            // Check sample data for this field
            $stmt = $pdo->query("SELECT DISTINCT " . $field['Field'] . " FROM students WHERE " . $field['Field'] . " IS NOT NULL AND " . $field['Field'] . " != '' LIMIT 5");
            $values = $stmt->fetchAll();
            if (!empty($values)) {
                echo "  Sample values: ";
                foreach ($values as $value) {
                    echo htmlspecialchars($value[$field['Field']]) . ", ";
                }
                echo "<br>";
            }
        }
    } else {
        echo "No obvious session-related fields found in students table<br>";
    }
    
    // Check if there's a student enrollments or admissions table
    echo "<h3>Checking for Enrollment/Admission Tables:</h3>";
    $tables_to_check = ['student_enrollments', 'student_admissions', 'enrollments', 'admissions'];
    
    foreach ($tables_to_check as $table) {
        try {
            $stmt = $pdo->query("SELECT COUNT(*) as count FROM " . $table);
            $count = $stmt->fetch()['count'];
            echo "$table table exists with $count records<br>";
            
            // Show sample structure
            $stmt = $pdo->query("DESCRIBE " . $table . " LIMIT 3");
            $columns = $stmt->fetchAll();
            echo "  Columns: ";
            foreach ($columns as $col) {
                echo $col['Field'] . ", ";
            }
            echo "<br>";
        } catch (Exception $e) {
            echo "$table table does not exist<br>";
        }
    }
    
    // Try to find session information through relationships
    echo "<h3>Attempting to find session information:</h3>";
    
    // Check if classes have year/session info in their names
    $stmt = $pdo->query("SELECT DISTINCT name FROM classes WHERE name LIKE '%-%' OR name LIKE '%/%' OR name LIKE '%20%'");
    $session_like_classes = $stmt->fetchAll();
    if (!empty($session_like_classes)) {
        echo "Classes with potential session info in names:<br>";
        foreach ($session_like_classes as $class) {
            echo "- " . htmlspecialchars($class['name']) . "<br>";
        }
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "<br>";
}
?>