<?php

function getAnalysisReport(
    $db,
    $postdata,
    $base_path_to_learning_resources,
    $conn_rjllearning
) {

       // Filters
    $institution_id = $postdata['institution_id'];
    $branch_id      = $postdata['branch_id'] ?? null;
    $class_id       = $postdata['class_id'] ?? null;
    $section_id     = $postdata['section_id'] ?? null;
    $academic_id    = $postdata['academic_id'] ?? null;
    $marksheet_id   = $postdata['marksheet_id'] ?? null;

    // Pagination
    $limitRaw = $postdata['limit'] ?? 20;
    $limit = ($limitRaw === "all") ? 0 : (int)$limitRaw;
    $page  = isset($postdata['page']) ? max(1, (int)$postdata['page']) : 1;
    $offset = ($page - 1) * ($limit > 0 ? $limit : 1);

    // Academic Session Check
    if (!CheckAcademicSession($db, $institution_id)) {
        errorreturn("Access denied. Academic session not active.");
    }

    // WHERE Conditions
    $where = ["institution_id = ?"];
    $params = [$institution_id];

    if ($branch_id) { $where[] = "branch_id = ?"; $params[] = $branch_id; }
    if ($academic_id) { $where[] = "academic_id = ?"; $params[] = $academic_id; }
    if ($class_id && strtolower($class_id) !== "all") { $where[] = "class_id = ?"; $params[] = $class_id; }
    if ($section_id && strtolower($section_id) !== "all") { $where[] = "section_id = ?"; $params[] = $section_id; }

    if ($marksheet_id !== "" && $marksheet_id !== null) {
        $where[] = "marksheet_id = ?";
        $params[] = $marksheet_id;
    }

    $WHERE = implode(" AND ", $where);

    // Count rows
    $count_sql = "SELECT COUNT(*) FROM student_marksheet_data WHERE $WHERE";
    $stmtCount = $db->prepare($count_sql);
    $stmtCount->execute($params);
    $total_rows = (int)$stmtCount->fetchColumn();

    // Fetch rows
    $sql = "SELECT * FROM student_marksheet_data WHERE $WHERE ORDER BY id DESC";
    if ($limit > 0) $sql .= " LIMIT $offset, $limit";

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // FINAL FIXED SUBJECT LIST MAPPING
    $subject_map = [
        // Mathematics
        "MATHEMATICS" => "MATHEMATICS",

        // English
        "ENGLISH" => "ENGLISH",

        // Punjabi
        "PUNJABI" => "PUNJABI",

        // EVS (all variations map to one)
        "EVSGK" => "E.V.S. / GENERAL KNOWLEDGE",
        "EVSGENERALKNOWLEDGE" => "E.V.S. / GENERAL KNOWLEDGE",
        "EVS" => "E.V.S. / GENERAL KNOWLEDGE",

        // Art & Craft
        "ARTCRAFT" => "ART & CRAFT",
        "ARTANDCRAFT" => "ART & CRAFT",

        // Rhymes
        "RHYMES" => "RHYMES",

        // Story
        "STORY" => "STORY",

        // Conversation
        "CONVERSATION" => "CONVERSATION"
    ];

    // Initialize analysis
    $analysis = [];
    $gradeKeys = ['A1','A2','B1','B2','C1','C2','D','E'];

    foreach ($rows as $row) {

        $analytics = !empty($row['analytics']) ? json_decode($row['analytics'], true) : null;
        if (!is_array($analytics)) continue;
        if (!isset($analytics['subject_rankings'])) continue;

        foreach ($analytics['subject_rankings'] as $sub) {

            $subject_raw = isset($sub['subject']) ? trim($sub['subject']) : null;
            if ($subject_raw === null || $subject_raw === "") continue;

            // Normalize input subject
            $subject_norm = strtoupper($subject_raw);
            $subject_norm = preg_replace('/[^A-Z0-9]/', '', $subject_norm);

            // Map to final subject name
            $subject = $subject_map[$subject_norm] ?? $subject_raw;

            // Normalize grade
            $grade = isset($sub['grade']) ? strtoupper(trim($sub['grade'])) : "";
            $grade = preg_replace('/\s+/', '', $grade);
            $grade = preg_replace('/[^A-Z0-9]/', '', $grade);

            // Create bucket if not exist
            if (!isset($analysis[$subject])) {
                $analysis[$subject] = [
                    "total_students" => 0,
                    "PASS" => 0,
                    "FAIL" => 0
                ];
                foreach ($gradeKeys as $gk) {
                    $analysis[$subject][$gk] = 0;
                }
            }

            // Count student
            $analysis[$subject]['total_students'] += 1;

            // Grade count
            if (in_array($grade, $gradeKeys)) {
                $analysis[$subject][$grade] += 1;
                $grade === 'E' ? $analysis[$subject]['FAIL']++ : $analysis[$subject]['PASS']++;
            } else {
                $analysis[$subject]['PASS']++;
            }
        }
    }

    // Export Excel
    if (isset($postdata['type']) && strtolower($postdata['type']) === "excel") {
        require_once $base_path_to_learning_resources . "marksheet_template/export/report_excel.php";
        generateExcelReport(
            $conn_rjllearning,
            $db,
            $analysis,
            $institution_id,
            $academic_id,
            $class_id,
            $section_id,
            $marksheet_id
        );
        exit;
    }

    // Response
    $total_pages = ($limit == 0) ? 1 : ceil($total_rows / $limit);

    successreturn([
        "status" => true,
        "pagination" => [
            "page" => (int) $page,
            "limit" => $limitRaw,
            "total_rows" => $total_rows,
            "returned_rows" => count($rows),
            "total_pages" => $total_pages
        ],
        "analysis_report" => $analysis
    ]);

}
?>
