<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');

// Validate session
if (!isset($_SESSION['SESS_ID_ADMIN'])) {
    header("Location: Sign_in.php");
    exit;
}

// Initialize search parameters
$classes = isset($_POST['classes']) && is_array($_POST['classes']) ? $_POST['classes'] : ['All'];
$terms = isset($_POST['terms']) && is_array($_POST['terms']) ? $_POST['terms'] : ['All'];
$sessions = isset($_POST['sessions']) && is_array($_POST['sessions']) ? $_POST['sessions'] : ['All'];

// Define class sets
$class_sets = ['JSS ONE', 'JSS TWO', 'JSS THREE', 'SSS ONE', 'SSS TWO'];

// Fetch available sessions and terms
try {
    $stmt = $DBH->query("SELECT DISTINCT year FROM tbl_student_results ORDER BY year DESC");
    $available_sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (empty($available_sessions)) {
        $available_sessions = [date('Y')];
    }
    $available_terms = ['1st Term', '2nd Term', '3rd Term'];
} catch (PDOException $e) {
    error_log("Dropdown fetch error: " . $e->getMessage());
    die("Error fetching data: " . htmlspecialchars($e->getMessage()));
}

// Build SQL conditions
$params = [];
$conditions = [];

// Class condition
if ($classes != ['All']) {
    $class_conditions = [];
    foreach ($classes as $class) {
        $class_conditions[] = "class LIKE ?";
        $params[] = $class . '%';
    }
    $conditions[] = "(" . implode(" OR ", $class_conditions) . ")";
}

// Term condition
if ($terms != ['All']) {
    $conditions[] = "term IN (" . implode(",", array_fill(0, count($terms), "?")) . ")";
    $params = array_merge($params, $terms);
}

// Session condition
$active_sessions = $sessions == ['All'] ? $available_sessions : $sessions;
if (!empty($active_sessions)) {
    $conditions[] = "year IN (" . implode(",", array_fill(0, count($active_sessions), "?")) . ")";
    $params = array_merge($params, $active_sessions);
}

// Debug: Log POST data
error_log("POST data: " . json_encode($_POST));

// Fetch top 5 students per class set
$results = [];
foreach ($class_sets as $class_set) {
    try {
        $query = "
            SELECT 
                u.fullname,
                s.registration_no,
                TRIM(SUBSTRING_INDEX(s.class, ' ', 2)) as base_class,
                s.class as original_class,
                s.year,
                AVG(CASE WHEN s.term = '1st Term' THEN s.total END) as avg_term1,
                AVG(CASE WHEN s.term = '2nd Term' THEN s.total END) as avg_term2,
                AVG(CASE WHEN s.term = '3rd Term' THEN s.total END) as avg_term3,
                SUM(s.total) as cum_total,
                AVG(s.total) as cum_avg
            FROM tbl_student_results s
            JOIN tbl_user u ON s.registration_no = u.registration_no
            " . (empty($conditions) ? "" : "WHERE " . implode(" AND ", $conditions)) . "
            AND TRIM(SUBSTRING_INDEX(s.class, ' ', 2)) = ?
            GROUP BY s.registration_no, u.fullname, base_class, original_class, s.year
            ORDER BY s.year DESC, cum_avg DESC
            LIMIT 5
        ";
        $local_params = array_merge($params, [$class_set]);
        error_log("Query for $class_set: $query");
        error_log("Params for $class_set: " . json_encode($local_params));
        $rows = sql($DBH, $query, $local_params, "rows");
        error_log("Rows for $class_set: " . json_encode($rows));
        $results[$class_set] = $rows;
    } catch (PDOException $e) {
        error_log("Error fetching results for $class_set: " . $e->getMessage());
        $results[$class_set] = [];
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>British Learner's College: Cumulative Result Analysis</title>
    <link rel="icon" type="image/png" href="https://bkms.com.ng/portal/check_Coll/blc_logo.png">
    <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet">
    <link href="css/sb-admin.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css" rel="stylesheet">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f8f9fc;
        }
        .container-fluid {
            padding: 20px;
        }
        .card {
            border: 1px solid #ddd;
            margin-bottom: 15px;
            border-radius: 5px;
        }
        .card-header {
            background-color: #a8e6cf;
            color: #2d6a4f;
            padding: 10px;
            font-weight: bold;
            text-align: center;
        }
        .card-body {
            padding: 15px;
        }
        .search-form {
            background-color: #e6f4ea;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .select2-container {
            width: 100% !important;
        }
        .btn-search {
            background-color: #2d6a4f;
            color: white;
            border: none;
            padding: 8px 16px;
            border-radius: 5px;
        }
        .btn-search:hover {
            background-color: #1a3c34;
        }
        @media print {
            .no-print {
                display: none;
            }
        }
        .table-responsive {
            overflow-x: auto;
        }
        .table th, .table td {
            vertical-align: middle;
        }
    </style>
</head>
<body class="fixed-nav sticky-footer bg-dark" id="page-top">
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark fixed-top" id="mainNav">
        <a class="navbar-brand" href="main.php">
            <img src="https://bkms.com.ng/portal/check_Nur/main_logo.png" alt="BLC Logo" height="40">
        </a>
        <h4 style="color: lime;">British Learner's College: Cumulative Result Analysis</h4>
        <button class="navbar-toggler navbar-toggler-right" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarResponsive">
            <ul class="navbar-nav navbar-sidenav" id="exampleAccordion">
                <li class="nav-item">
                    <a class="nav-link" href="main.php">
                        <i class="fa fa-fw fa-dashboard" style="color: #3498db;"></i>
                        <span class="nav-link-text" style="color: white;">Dashboard</span>
                    </a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="result.php">
                        <i class="fa fa-fw fa-clock-o" style="color: #3498db;"></i>
                        <span class="nav-link-text" style="color: white;">Results</span>
                    </a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="logout.php">
                        <i class="fa fa-fw fa-sign-out" style="color: #3498db;"></i>
                        <span class="nav-link-text" style="color: white;">Logout</span>
                    </a>
                </li>
            </ul>
            <ul class="navbar-nav ml-auto">
                <li class="nav-item">
                    <a class="nav-link no-print" href="logout.php" style="color: white;">
                        <i class="fa fa-fw fa-sign-out"></i> Logout
                    </a>
                </li>
            </ul>
        </div>
    </nav>

    <div class="content-wrapper">
        <div class="container-fluid">
            <div class="text-center mb-3">
                <img src="BLC_Banner_Result.png" alt="British Learner's College" class="img-fluid" style="max-width: 950px;">
            </div>

            <div class="alert alert-info no-print">
                Selected: Classes = <?php echo htmlspecialchars(json_encode($classes)); ?>,
                Terms = <?php echo htmlspecialchars(json_encode($terms)); ?>,
                Sessions = <?php echo htmlspecialchars(json_encode($sessions)); ?>
            </div>

            <div class="card search-form no-print">
                <div class="card-header">Search Cumulative Results</div>
                <div class="card-body">
                    <form method="POST" action="cumm_assess.php">
                        <div class="row">
                            <div class="col-md-4 form-group">
                                <label for="classes">Class</label>
                                <select name="classes[]" id="classes" class="form-control" multiple>
                                    <option value="All" <?php echo in_array('All', $classes) ? 'selected' : ''; ?>>All Classes</option>
                                    <?php foreach ($class_sets as $class): ?>
                                        <option value="<?php echo htmlspecialchars($class); ?>" <?php echo in_array($class, $classes) ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($class); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-4 form-group">
                                <label for="terms">Term</label>
                                <select name="terms[]" id="terms" class="form-control" multiple>
                                    <option value="All" <?php echo in_array('All', $terms) ? 'selected' : ''; ?>>All Terms</option>
                                    <?php foreach ($available_terms as $term): ?>
                                        <option value="<?php echo htmlspecialchars($term); ?>" <?php echo in_array($term, $terms) ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($term); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="col-md-4 form-group">
                                <label for="sessions">Session</label>
                                <select name="sessions[]" id="sessions" class="form-control" multiple>
                                    <option value="All" <?php echo in_array('All', $sessions) ? 'selected' : ''; ?>>All Sessions</option>
                                    <?php foreach ($available_sessions as $session): ?>
                                        <option value="<?php echo htmlspecialchars($session); ?>" <?php echo in_array($session, $sessions) ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($session); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="text-center">
                            <button type="submit" class="btn btn-search">Search</button>
                        </div>
                    </form>
                </div>
            </div>

            <?php if (array_sum(array_map('count', $results)) == 0): ?>
                <div class="alert alert-warning">
                    No results found. Possible reasons:
                    <ul>
                        <li>No data for the selected class (e.g., JSS ONE A/B), term, or session.</li>
                        <li>Session format may differ (e.g., '2023/2024' instead of '2024').</li>
                        <li>Class names may not match (expected: 'JSS ONE A', 'JSS ONE B').</li>
                        <li>Database tables are empty.</li>
                        <li>Check error log for details.</li>
                    </ul>
                </div>
            <?php else: ?>
                <?php foreach ($results as $class_set => $students): ?>
                    <?php if (!empty($students)): ?>
                        <div class="card">
                            <div class="card-header">
                                <?php echo htmlspecialchars($class_set); ?> Top Performers
                                <small>(Combined <?php echo htmlspecialchars($class_set); ?> A/B)</small>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-bordered">
                                        <thead>
                                            <tr>
                                                <th>Position</th>
                                                <th>Name</th>
                                                <th>Reg No</th>
                                                <th>Actual Class</th>
                                                <th>Session</th>
                                                <th>1st Term Avg</th>
                                                <th>2nd Term Avg</th>
                                                <th>3rd Term Avg</th>
                                                <th>Cumulative Total</th>
                                                <th>Cumulative Avg</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($students as $index => $student): ?>
                                                <tr>
                                                    <td><?php echo $index + 1; ?></td>
                                                    <td><?php echo htmlspecialchars($student['fullname']); ?></td>
                                                    <td><?php echo htmlspecialchars($student['registration_no']); ?></td>
                                                    <td><?php echo htmlspecialchars($student['original_class']); ?></td>
                                                    <td><?php echo htmlspecialchars($student['year']); ?></td>
                                                    <td><?php echo $student['avg_term1'] ? round($student['avg_term1'], 2) : 'N/A'; ?>%</td>
                                                    <td><?php echo $student['avg_term2'] ? round($student['avg_term2'], 2) : 'N/A'; ?>%</td>
                                                    <td><?php echo $student['avg_term3'] ? round($student['avg_term3'], 2) : 'N/A'; ?>%</td>
                                                    <td><?php echo $student['cum_total'] ? round($student['cum_total'], 2) : 'N/A'; ?></td>
                                                    <td><?php echo $student['cum_avg'] ? round($student['cum_avg'], 2) : 'N/A'; ?>%</td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    <?php endif; ?>
                <?php endforeach; ?>
            <?php endif; ?>
        </div>

        <footer class="sticky-footer bg-dark text-white text-center py-3">
            <small><strong>Copyright © 2022-2025 British Learner's College, Owerri, Imo State: All Rights Reserved!</strong></small>
        </footer>
    </div>

    <script src="vendor/jquery/jquery.min.js"></script>
    <script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js"></script>
    <script>
        $(document).ready(function() {
            try {
                $('#classes, #terms, #sessions').select2({
                    placeholder: "Select options",
                    allowClear: true,
                    width: '100%'
                });

                // Prevent 'All' with specific options
                $('#classes').on('change', function() {
                    var values = $(this).val() || [];
                    if (values.includes('All') && values.length > 1) {
                        $(this).val(['All']).trigger('change');
                    }
                });
                $('#terms').on('change', function() {
                    var values = $(this).val() || [];
                    if (values.includes('All') && values.length > 1) {
                        $(this).val(['All']).trigger('change');
                    }
                });
                $('#sessions').on('change', function() {
                    var values = $(this).val() || [];
                    if (values.includes('All') && values.length > 1) {
                        $(this).val(['All']).trigger('change');
                    }
                });
            } catch (e) {
                console.error('Select2 initialization failed:', e);
            }
        });
    </script>
</body>
</html>