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

try {
    // Check if session "2025-26" already exists
    $stmt = $pdo->prepare("SELECT id FROM academic_sessions WHERE session_name = ?");
    $stmt->execute(['2025-26']);
    $existing = $stmt->fetch();
    
    if ($existing) {
        echo "Session '2025-26' already exists with ID: {$existing['id']}\n";
    } else {
        // Add the missing session
        $stmt = $pdo->prepare("INSERT INTO academic_sessions (session_name, start_date, end_date, status) VALUES (?, ?, ?, ?)");
        // For 2025-26 session, use approximate dates
        $stmt->execute(['2025-26', '2025-01-01', '2026-12-31', 'active']);
        $newId = $pdo->lastInsertId();
        echo "Added session '2025-26' with ID: $newId\n";
    }
    
    // Also check if we need to update any students with session "2025-26" to use proper session_id
    echo "\nChecking students with session '2025-26' that might need session_id update:\n";
    $stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name FROM students WHERE session = ? AND session_id IS NULL");
    $stmt->execute(['2025-26']);
    $students = $stmt->fetchAll();
    
    if (empty($students)) {
        echo "No students found with session '2025-26' and NULL session_id\n";
    } else {
        echo "Found " . count($students) . " students with session '2025-26' and NULL session_id:\n";
        foreach ($students as $student) {
            echo "- ID: {$student['id']}, Student ID: {$student['student_id']}, Name: {$student['first_name']} {$student['last_name']}\n";
        }
        
        // Get the session_id for "2025-26"
        $stmt = $pdo->prepare("SELECT id FROM academic_sessions WHERE session_name = ?");
        $stmt->execute(['2025-26']);
        $session = $stmt->fetch();
        
        if ($session) {
            // Update students to link to the academic session
            $stmt = $pdo->prepare("UPDATE students SET session_id = ? WHERE session = ? AND session_id IS NULL");
            $stmt->execute([$session['id'], '2025-26']);
            $count = $stmt->rowCount();
            echo "\nUpdated $count students to link to academic session ID {$session['id']}\n";
        }
    }
    
    echo "\nCurrent academic sessions:\n";
    $stmt = $pdo->query("SELECT * FROM academic_sessions ORDER BY session_name");
    $sessions = $stmt->fetchAll();
    foreach ($sessions as $s) {
        echo "- ID: {$s['id']}, Name: '{$s['session_name']}', Status: {$s['status']}, Current: {$s['is_current']}\n";
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>