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

$class_name = 'Ten'; // Class with 4th subjects and results
$session = '2025'; // Current session
$exam_type = 'final'; // Correct exam type from database
$roll = '1'; // Roll number 1 has 4th subject

// First show available classes and exams
$stmt = $pdo->query('SELECT DISTINCT c.name FROM classes c JOIN students s ON c.id = s.class_id ORDER BY c.name');
$classes = $stmt->fetchAll(PDO::FETCH_COLUMN);
echo 'Available classes:' . PHP_EOL;
foreach ($classes as $class) {
    echo '  ' . $class . PHP_EOL;
}

$stmt = $pdo->query('SELECT DISTINCT exam_type FROM results ORDER BY exam_type');
$exams = $stmt->fetchAll(PDO::FETCH_COLUMN);
echo PHP_EOL . 'Available exam types:' . PHP_EOL;
foreach ($exams as $exam) {
    echo '  ' . $exam . PHP_EOL;
}

$stmt = $pdo->query('SELECT DISTINCT session FROM results ORDER BY session DESC');
$sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
echo PHP_EOL . 'Available sessions:' . PHP_EOL;
foreach ($sessions as $sess) {
    echo '  ' . $sess . PHP_EOL;
}
echo PHP_EOL;

echo "Testing marksheet with class: $class_name, session: $session, exam: $exam_type, roll: $roll\n\n";

try {
    // Get class_id from class_name
    $stmt = $pdo->prepare("SELECT id FROM classes WHERE name = ?");
    $stmt->execute([$class_name]);
    $class = $stmt->fetch();
    if (!$class) {
        echo "Class not found!\n";
        exit;
    }
    $class_id = $class['id'];

    // Get student details
    $stmt = $pdo->prepare("
        SELECT s.*, c.name as class_name, c.section
        FROM students s
        JOIN classes c ON s.class_id = c.id
        WHERE s.roll_number = ? AND s.class_id = ? AND s.session = ? AND s.status = 'active'
    ");
    $stmt->execute([$roll, $class_id, $session]);
    $student = $stmt->fetch();

    if (!$student) {
        echo "Student not found! Let me find a student in ELEVEN class with 4th subject:\n";

        // Find a student in ELEVEN class with both results and 4th subject
        $stmt = $pdo->prepare("
            SELECT DISTINCT s.roll_number, s.first_name, s.last_name
            FROM students s
            JOIN results r ON s.id = r.student_id
            JOIN student_subjects ss ON s.id = ss.student_id
            WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ? AND ss.is_fourth_subject = 1
            LIMIT 1
        ");
        $stmt->execute([$class_id, $session, $exam_type]);
        $student_with_fourth = $stmt->fetch();

        if ($student_with_fourth) {
            echo "Found student with 4th subject and results: Roll " . $student_with_fourth['roll_number'] . " - " . $student_with_fourth['first_name'] . ' ' . $student_with_fourth['last_name'] . "\n";
            echo "Please try with roll number: " . $student_with_fourth['roll_number'] . "\n";
        } else {
            echo "No students with both 4th subjects and results found in ELEVEN class.\n";

            // Find any class/session with students that have both 4th subjects and results
            $stmt = $pdo->prepare("
                SELECT DISTINCT c.name as class_name, r.session, r.exam_type, st.roll_number, st.first_name, st.last_name
                FROM students st
                JOIN classes c ON st.class_id = c.id
                JOIN results r ON st.id = r.student_id
                JOIN student_subjects ss ON st.id = ss.student_id
                WHERE ss.is_fourth_subject = 1
                LIMIT 1
            ");
            $stmt->execute();
            $found_student = $stmt->fetch();

            if ($found_student) {
                echo "Found student with 4th subject and results:\n";
                echo "  Class: " . $found_student['class_name'] . "\n";
                echo "  Session: " . $found_student['session'] . "\n";
                echo "  Exam: " . $found_student['exam_type'] . "\n";
                echo "  Student: Roll " . $found_student['roll_number'] . " - " . $found_student['first_name'] . ' ' . $found_student['last_name'] . "\n";
                echo "Please use these parameters to test the marksheet.\n";
            } else {
                echo "No students with both 4th subjects and results found anywhere.\n";
            }
        }
        exit;
    }

    echo "Found student: " . $student['first_name'] . ' ' . $student['last_name'] . "\n";

    $student_id = $student['id'];

    // Get all subjects selected by the student
    $stmt = $pdo->prepare("SELECT ss.subject_id, ss.is_fourth_subject, s.name as subject_name, s.code as subject_code FROM student_subjects ss JOIN subjects s ON ss.subject_id = s.id WHERE ss.student_id = ? AND s.status = 'active' ORDER BY s.name");
    $stmt->execute([$student_id]);
    $all_student_subjects = $stmt->fetchAll();

    echo "Student subjects:\n";
    foreach ($all_student_subjects as $subject) {
        echo "  - {$subject['subject_name']} (4th: " . ($subject['is_fourth_subject'] ? 'YES' : 'NO') . ")\n";
    }

    // Get results
    $stmt = $pdo->prepare("
        SELECT r.*, sub.name as subject_name, sub.code as subject_code
        FROM results r
        LEFT JOIN subjects sub ON r.subject_id = sub.id
        WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
        AND r.id IN (
            SELECT MAX(id)
            FROM results
            WHERE student_id = ? AND session = ? AND exam_type = ?
            GROUP BY subject_id
        )
        ORDER BY sub.name
    ");
    $stmt->execute([$student_id, $session, $exam_type, $student_id, $session, $exam_type]);
    $results_with_marks = $stmt->fetchAll();

    echo "\nResults found: " . count($results_with_marks) . "\n";
    foreach ($results_with_marks as $result) {
        echo "  - {$result['subject_name']}: {$result['marks_obtained']} marks\n";
    }

    // Create results map
    $results_map = [];
    foreach ($results_with_marks as $result) {
        $results_map[$result['subject_id']] = $result;
    }

    // Create combined results
    $results = [];
    foreach ($all_student_subjects as $subject) {
        $subject_id = $subject['subject_id'];

        if (isset($results_map[$subject_id])) {
            $result_data = $results_map[$subject_id];
            $result_data['is_fourth_subject'] = $subject['is_fourth_subject'] ?? false;
            $results[] = $result_data;
        }
    }

    // Separate subjects
    $regular_subjects = array_filter($results, function($result) {
        return empty($result['is_fourth_subject']);
    });

    $fourth_subjects = array_filter($results, function($result) {
        return !empty($result['is_fourth_subject']);
    });

    echo "\nRegular subjects: " . count($regular_subjects) . "\n";
    echo "Fourth subjects: " . count($fourth_subjects) . "\n";

    if (!empty($fourth_subjects)) {
        echo "\nFourth subjects details:\n";
        foreach ($fourth_subjects as $subject) {
            echo "  - {$subject['subject_name']} (4th subject: " . ($subject['is_fourth_subject'] ? 'YES' : 'NO') . ")\n";
        }
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>
