<?php
// Always return JSON
header('Content-Type: application/json');

// Database connection
include('../includes/dbconnection.php');

// Get filter parameters
$session_id = $_POST['session'] ?? '';
$term_id = $_POST['term'] ?? '';
$class = $_POST['class'] ?? '';
$section = $_POST['section'] ?? '';
$subject = $_POST['subject'] ?? '';

// Initialize response array
$response = array('success' => false, 'message' => '', 'data' => array());

try {
    if (empty($session_id)) {
        throw new Exception("Session is required.");
    }
    // --- Build WHERE clause ---
    $where_conditions = array();
    $where_conditions[] = "r.session_id = '" . $con->real_escape_string($session_id) . "'";
    $where_conditions[] = "r.status = 'Approved'";

    if (!empty($term_id) && $term_id != 'all') {
        $where_conditions[] = "r.term_id = '" . $con->real_escape_string($term_id) . "'";
    }
    if (!empty($class) && $class != 'all') {
        $where_conditions[] = "r.class = '" . $con->real_escape_string($class) . "'";
    }
    if (!empty($section) && $section != 'all') {
        $where_conditions[] = "r.section = '" . $con->real_escape_string($section) . "'";
    }
    if (!empty($subject) && $subject != 'all') {
        $where_conditions[] = "r.subject = '" . $con->real_escape_string($subject) . "'";
    }

    $where_clause = implode(' AND ', $where_conditions);

    // Helper function to check query results
    function safe_query($con, $sql, $errorLabel)
    {
        $result = $con->query($sql);
        if ($result === false) {
            throw new Exception("$errorLabel failed: " . $con->error);
        }
        return $result;
    }

    // ------------------------------
    // 1. Top 10 students overall
    // ------------------------------
    $overview_sql = "
        SELECT 
            t.student_id,
            s.studentName,
            s.studentno,
            t.class,
            t.section,
            AVG(t.term_avg) AS average_score
        FROM (
            SELECT 
                r.class,
                r.section,
                r.student_id,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.subject) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.class, r.section, r.student_id, r.term_id
        ) AS t
        INNER JOIN students s ON s.id = t.student_id
        GROUP BY t.student_id, t.class, t.section
        ORDER BY average_score DESC
        LIMIT 10
    ";
    $result = safe_query($con, $overview_sql, "Top students query");
    $topStudents = [];
    while ($row = $result->fetch_assoc()) {
        $topStudents[] = $row;
    }

    // ------------------------------
    // 2. Overall stats
    // ------------------------------
    $stats_sql = "
        SELECT 
            COUNT(DISTINCT t.student_id) AS total_students,
            AVG(t.term_avg) AS overall_average
        FROM (
            SELECT 
                r.student_id,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.subject) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.student_id, r.term_id
        ) AS t
    ";

    $result = safe_query($con, $stats_sql, "Stats query");
    $stats = $result->fetch_assoc() ?? ['total_students' => 0, 'overall_average' => 0];

    // ------------------------------
    // 3. Top class (highest average)
    // ------------------------------
    $class_sql = "
        SELECT 
            t.class,
            t.section,
            AVG(t.term_avg) AS class_average
        FROM (
            SELECT 
                r.class,
                r.section,
                r.student_id,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.subject) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.class, r.section, r.student_id, r.term_id
        ) AS t
        GROUP BY t.class, t.section
        ORDER BY class_average DESC
        LIMIT 1
    ";

    $result = safe_query($con, $class_sql, "Top class query");
    $topClass = $result->fetch_assoc() ?? ['class' => '-', 'section' => '-', 'class_average' => 0];

    // ------------------------------
    // 4. Best subject
    // ------------------------------
    $subject_sql = "
        SELECT 
            t.subject,
            AVG(t.term_avg) AS subject_average
        FROM (
            SELECT 
                r.subject,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.student_id) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.subject, r.term_id
        ) AS t
        GROUP BY t.subject
        ORDER BY subject_average DESC
        LIMIT 1
    ";

    $result = safe_query($con, $subject_sql, "Best subject query");
    $bestSubject = $result->fetch_assoc() ?? ['subject' => '-', 'subject_average' => 0];


    // ------------------------------
    // 4b. Weakest class
    // ------------------------------
    $worst_class_sql = "
        SELECT 
            t.class,
            t.section,
            AVG(t.term_avg) AS class_average
        FROM (
            SELECT 
                r.class,
                r.section,
                r.student_id,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.subject) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.class, r.section, r.student_id, r.term_id
        ) AS t
        GROUP BY t.class, t.section
        ORDER BY class_average ASC
        LIMIT 1
    ";

    $result = safe_query($con, $worst_class_sql, "Worst class query");
    $worstClass = $result->fetch_assoc() ?? ['class' => '-', 'section' => '-', 'class_average' => 0];

    // ------------------------------
    // 4c. Weakest subject
    // ------------------------------
    $worst_subject_sql = "
        SELECT 
            t.subject,
            AVG(t.term_avg) AS subject_average
        FROM (
            SELECT 
                r.subject,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.student_id) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.subject, r.term_id
        ) AS t
        GROUP BY t.subject
        ORDER BY subject_average ASC
        LIMIT 1
    ";

    $result = safe_query($con, $worst_subject_sql, "Worst subject query");
    $worstSubject = $result->fetch_assoc() ?? ['subject' => '-', 'subject_average' => 0];

    // Build overview data
    $response['data']['overview'] = array(
        'totalStudents' => $stats['total_students'],
        'overallAverage' => round($stats['overall_average'], 2),
        'topClass' => $topClass['class'] . ' - ' . $topClass['section'],
        'topClassAverage' => round($topClass['class_average'], 2),
        'bestSubject' => $bestSubject['subject'],
        'bestSubjectAverage' => round($bestSubject['subject_average'], 2),
        'worstClass' => $worstClass['class'] . ' - ' . $worstClass['section'],
        'worstClassAverage' => round($worstClass['class_average'], 2),
        'worstSubject' => $worstSubject['subject'],
        'worstSubjectAverage' => round($worstSubject['subject_average'], 2),
        'topStudents' => $topStudents
    );

    // ------------------------------
    // 5. Class performance (top 3 students per class)
    // ------------------------------
    $class_performance_sql = "
        SELECT 
            r.class,
            r.section,
            r.student_id,
            s.studentName,
            s.studentno,
            ROUND(
                (SUM(r.total_score) / (COUNT(DISTINCT r.subject) * 100 * COUNT(DISTINCT r.term_id))) * 100,
                2
            ) AS average_score
        FROM results r
        INNER JOIN students s ON s.id = r.student_id
        INNER JOIN (
            SELECT 
                id AS student_id,
                CASE admission_term
                    WHEN 'First Term' THEN 1
                    WHEN 'Second Term' THEN 2
                    WHEN 'Third Term' THEN 3
                    ELSE 1
                END AS admission_term_id
            FROM students
        ) AS st ON st.student_id = r.student_id
        WHERE $where_clause
          AND r.term_id >= st.admission_term_id
        GROUP BY r.class, r.section, r.student_id, s.studentName, s.studentno
        ORDER BY r.class, r.section, average_score DESC
    ";

    // Execute query
    $result = safe_query($con, $class_performance_sql, "Class-section performance query");

    $classPerformance = [];
    while ($row = $result->fetch_assoc()) {
        $classKey = $row['class'] . '-' . $row['section'];
        if (!isset($classPerformance[$classKey])) {
            $classPerformance[$classKey] = [
                'class' => $row['class'],
                'section' => $row['section'],
                'students' => []
            ];
        }
        $classPerformance[$classKey]['students'][] = $row;
    }

    // // Keep only top 3 per class-section
    // foreach ($classPerformance as $key => $classData) {
    //     $classPerformance[$key]['students'] = array_slice($classData['students'], 0, 3);
    // }

    $response['data']['classPerformance'] = array_values($classPerformance);

    // ------------------------------
    // 5b. Class performance per class only (top 3 students per class, ignore sections)
    // ------------------------------
    $class_only_sql = "
        SELECT 
            r.class,
            r.student_id,
            r.section,
            s.studentName,
            s.studentno,
            ROUND(
                (SUM(r.total_score) / (COUNT(DISTINCT r.subject) * 100 * COUNT(DISTINCT r.term_id))) * 100,
                2
            ) AS average_score
        FROM results r
        INNER JOIN students s ON s.id = r.student_id
        INNER JOIN (
            SELECT 
                id AS student_id,
                CASE admission_term
                    WHEN 'First Term' THEN 1
                    WHEN 'Second Term' THEN 2
                    WHEN 'Third Term' THEN 3
                    ELSE 1
                END AS admission_term_id
            FROM students
        ) AS st ON st.student_id = r.student_id
        WHERE $where_clause
          AND r.term_id >= st.admission_term_id
        GROUP BY r.class, r.student_id, s.studentName, s.studentno
        ORDER BY r.class, average_score DESC
    ";

    $result = safe_query($con, $class_only_sql, "Class-only performance query");

    $classOnlyPerformance = [];
    while ($row = $result->fetch_assoc()) {
        $classKey = $row['class'];
        if (!isset($classOnlyPerformance[$classKey])) {
            $classOnlyPerformance[$classKey] = [
                'class' => $row['class'],
                'students' => []
            ];
        }
        $classOnlyPerformance[$classKey]['students'][] = $row;
    }

    $response['data']['classPerformanceOverall'] = array_values($classOnlyPerformance);

    // ------------------------------
    // 5c. Top 3 per class (all arms combined)
    // ------------------------------
    $classTop3 = [];
    foreach ($classOnlyPerformance as $classKey => $classData) {
        $classTop3[$classKey] = [
            'class' => $classData['class'],
            'students' => array_slice($classData['students'], 0, 3),
        ];
    }
    $response['data']['classPerformanceTop3'] = array_values($classTop3);

    // ------------------------------
    // 6. Subject performance (top performer per subject & class)
    // ------------------------------
    $subject_performance_sql = "
        SELECT sub.subject, sub.class, sub.student_id, sub.studentName, sub.studentno, sub.score
        FROM (
            SELECT 
                r.subject, 
                r.class, 
                s.id AS student_id, 
                s.studentName, 
                s.studentno, 
                SUM(r.total_score) / COUNT(DISTINCT r.term_id) AS score
            FROM results r
            INNER JOIN students s ON r.student_id = s.id
            WHERE $where_clause
            GROUP BY r.subject, r.class, s.id
        ) AS sub
        INNER JOIN (
            SELECT subject, class, MAX(score) AS max_score
            FROM (
                SELECT 
                    r.subject, 
                    r.class, 
                    s.id, 
                    SUM(r.total_score) / COUNT(DISTINCT r.term_id) AS score
                FROM results r
                INNER JOIN students s ON r.student_id = s.id
                WHERE $where_clause
                GROUP BY r.subject, r.class, s.id
            ) AS scores
            GROUP BY subject, class
        ) AS max_sub
        ON sub.subject = max_sub.subject 
        AND sub.class = max_sub.class 
        AND sub.score = max_sub.max_score
        ORDER BY sub.subject, sub.class, sub.score DESC
    ";

    $result = safe_query($con, $subject_performance_sql, "Subject performance query");
    $subjectPerformance = [];
    while ($row = $result->fetch_assoc()) {
        $subjectKey = $row['subject'];
        if (!isset($subjectPerformance[$subjectKey])) {
            $subjectPerformance[$subjectKey] = [
                'subject' => $row['subject'],
                'topPerformers' => []
            ];
        }
        $subjectPerformance[$subjectKey]['topPerformers'][] = $row;
    }
    $response['data']['subjectPerformance'] = array_values($subjectPerformance);

    // ------------------------------
    // 7. Weak students (avg < 40)
    // ------------------------------
    $weak_students_sql = "
        SELECT 
            t.class,
            t.section,
            t.student_id,
            s.studentName,
            s.studentno,
            AVG(t.term_avg) AS average_score
        FROM (
            SELECT 
                r.class,
                r.section,
                r.student_id,
                r.term_id,
                SUM(r.total_score) / COUNT(DISTINCT r.subject) AS term_avg
            FROM results r
            WHERE $where_clause
            GROUP BY r.class, r.section, r.student_id, r.term_id
        ) AS t
        INNER JOIN students s ON s.id = t.student_id
        GROUP BY t.class, t.section, t.student_id
        HAVING average_score < 40
        ORDER BY average_score ASC
    ";

    $result = safe_query($con, $weak_students_sql, "Weak students query");
    $weakStudents = [];
    while ($row = $result->fetch_assoc()) {
        $weakStudents[] = $row;
    }
    $response['data']['weakStudents'] = $weakStudents;

    // ------------------------------
    // 8. STUDENT RESULTS - NEW SECTION
    // Calculate results for all students in each class
    // ------------------------------
    $student_results_sql = "
        SELECT 
            s.id as student_id,
            s.studentName,
            s.studentno,
            r.class,
            r.section,
            (COUNT(DISTINCT r.subject) * 100 * COUNT(DISTINCT r.term_id)) as total_obtainable,
            SUM(r.total_score) as total_obtained,
            ROUND(
                (SUM(
                    CASE 
                        WHEN r.total_score > 100 THEN 100 
                        ELSE r.total_score 
                    END
                ) / (COUNT(DISTINCT r.subject) * 100 * COUNT(DISTINCT r.term_id))) * 100, 
                2
            ) as percentage
        FROM students s
        INNER JOIN results r ON s.id = r.student_id
        WHERE $where_clause
        GROUP BY s.id, s.studentName, s.studentno, r.class, r.section
        ORDER BY r.class, r.section, percentage DESC
    ";

    $result = safe_query($con, $student_results_sql, "Student results query");
    $studentResults = [];
    while ($row = $result->fetch_assoc()) {
        $studentResults[] = [
            'student_id' => $row['student_id'],
            'studentName' => $row['studentName'],
            'studentno' => $row['studentno'],
            'class' => $row['class'],
            'section' => $row['section'],
            'total_obtainable' => (int) $row['total_obtainable'],
            'total_obtained' => (float) $row['total_obtained'],
            'percentage' => (float) $row['percentage']
        ];
    }

    // Group by Class and Section
    $resultsByClassSection = [];
    foreach ($studentResults as $result) {
        $key = $result['class'] . '|' . $result['section'];
        if (!isset($resultsByClassSection[$key])) {
            $resultsByClassSection[$key] = [];
        }
        $resultsByClassSection[$key][] = $result;
    }

    // Sort each group by percentage descending and assign positions
    foreach ($resultsByClassSection as $key => &$group) {
        usort($group, function ($a, $b) {
            return $b['percentage'] <=> $a['percentage'];
        });

        // Assign positions (handling ties)
        $position = 1;
        $prevPercentage = null;
        $sameCount = 1;

        foreach ($group as $index => &$student) {
            if ($prevPercentage !== null && $student['percentage'] < $prevPercentage) {
                $position += $sameCount;
                $sameCount = 1;
            } elseif ($prevPercentage !== null && $student['percentage'] == $prevPercentage) {
                $sameCount++;
            }

            $student['position'] = $position;
            $prevPercentage = $student['percentage'];
        }
        // IMPORTANT: Unset the reference to the last student
        unset($student);
    }
    // IMPORTANT: Unset the reference to the last group
    unset($group);

    // Flatten the array back
    $studentResultsWithPositions = [];
    foreach ($resultsByClassSection as $group) {
        foreach ($group as $student) {
            $studentResultsWithPositions[] = $student;
        }
    }

    $response['data']['studentResults'] = $studentResultsWithPositions;

    // Done
    $response['success'] = true;

} catch (Exception $e) {
    $response['success'] = false;
    $response['message'] = $e->getMessage();
}

echo json_encode($response);
$con->close();
?>