<?php
require_once __DIR__ . '/../../config/config.php';
header('Content-Type: application/json');

try {
    // Validate required parameters
    $academic_year = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $exam_id = isset($_GET['exam_id']) ? (int)$_GET['exam_id'] : 0;
    $course_id = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
    $semester = isset($_GET['semester']) && $_GET['semester'] !== '' ? (int)$_GET['semester'] : null;
    
    if (!$academic_year || !$exam_id || !$course_id || !$semester) { 
        http_response_code(400);
        echo json_encode(['status' => 'error', 'message' => 'Missing required filters: academic_year, exam_id, course_id, and semester are required']); 
        exit; 
    }
    
    global $pdo;

    // Fetch students
    $sqlStu = "SELECT student_id, university_rollno, first_name, last_name 
               FROM students 
               WHERE course_id = ? AND current_semester = ? 
               ORDER BY university_rollno";
    $stmt = $pdo->prepare($sqlStu);
    $stmt->execute([$course_id, $semester]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($students)) {
        echo json_encode([
            'status' => 'success', 
            'students' => [], 
            'subjects' => [], 
            'rows_by_key' => [],
            'message' => 'No students found for the given criteria'
        ]);
        exit;
    }

    // Fetch subjects for this exam
    $sqlSub = "SELECT es.subject_id, s.subject_code, s.subject_name, 
                      es.max_marks as ext_max, es.pass_marks as ext_pass
               FROM exam_subjects es
               JOIN subjects s ON es.subject_id = s.subject_id
               WHERE es.exam_id = ? AND s.course_id = ?
               ORDER BY s.subject_code";
    $stmt = $pdo->prepare($sqlSub);
    $stmt->execute([$exam_id, $course_id]);
    $subjects = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($subjects)) {
        echo json_encode([
            'status' => 'success', 
            'students' => $students, 
            'subjects' => [], 
            'rows_by_key' => [],
            'message' => 'No subjects found for this exam'
        ]);
        exit;
    }

    // Build subject metadata
    $subjectsMeta = [];
    $subMetaById = [];
    foreach ($subjects as $subject) {
        $meta = [
            'subject_id' => (int)$subject['subject_id'],
            'subject_code' => $subject['subject_code'],
            'subject_name' => $subject['subject_name'],
            'ext_max' => (int)($subject['ext_max'] ?? 100),
            'ext_pass' => (int)($subject['ext_pass'] ?? 50)
        ];
        $subjectsMeta[] = $meta;
        $subMetaById[$meta['subject_id']] = $meta;
    }

    // Get CI settings for calculation method
    $stmt = $pdo->prepare("SELECT calculation_method 
                           FROM ci_assessment_settings 
                           WHERE course_id = ? AND exam_type = 'Internal' 
                           ORDER BY setting_id DESC LIMIT 1");
    $stmt->execute([$course_id]);
    $ciSettings = $stmt->fetch(PDO::FETCH_ASSOC);
    $calculationMethod = $ciSettings['calculation_method'] ?? 'best2_avg';

    // Prepare student and subject IDs for queries
    $studentIds = array_map('intval', array_column($students, 'student_id'));
    $subjectIds = array_map('intval', array_column($subjects, 'subject_id'));
    $studentIdsPlaceholder = implode(',', $studentIds);
    $subjectIdsPlaceholder = implode(',', $subjectIds);

    $rowMap = [];

    // Fetch internal marks with corrected JOIN logic
    $sqlInt = "SELECT im.student_id, es.subject_id, et.type_name, im.marks_obtained
               FROM internal_marks im
               JOIN internal_exams ie ON im.internal_exam_id = ie.internal_exam_id
               JOIN exam_types et ON ie.exam_type_id = et.exam_type_id
               JOIN exam_subjects es ON ie.subject_id = es.subject_id
               WHERE im.student_id IN ($studentIdsPlaceholder)
                 AND es.subject_id IN ($subjectIdsPlaceholder)
                 AND et.type_name IN ('CI1', 'CI2', 'CI3')";
    
    $stmt = $pdo->prepare($sqlInt);
    $stmt->execute();
    $internalData = [];
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $key = $row['student_id'] . '_' . $row['subject_id'];
        if (!isset($internalData[$key])) {
            $internalData[$key] = [];
        }
        $internalData[$key][$row['type_name']] = (float)$row['marks_obtained'];
    }

    // Compute internal marks based on calculation method
    foreach ($internalData as $key => $marks) {
        if (!isset($rowMap[$key])) {
            $rowMap[$key] = [];
        }
        
        $ci1 = $marks['CI1'] ?? 0;
        $ci2 = $marks['CI2'] ?? 0;
        $ci3 = $marks['CI3'] ?? 0;
        
        $ciMarks = array_filter([$ci1, $ci2, $ci3], function($mark) { 
            return $mark > 0; 
        });
        
        $internalMark = 0;
        
        switch ($calculationMethod) {
            case 'best1':
                $internalMark = !empty($ciMarks) ? max($ciMarks) : 0;
                break;
                
            case 'avg3':
                $internalMark = count($ciMarks) >= 3 ? array_sum($ciMarks) / count($ciMarks) : 0;
                break;
                
            case 'best2_avg':
            default:
                if (count($ciMarks) >= 2) {
                    rsort($ciMarks);
                    $best2 = array_slice($ciMarks, 0, 2);
                    $internalMark = array_sum($best2) / count($best2);
                } elseif (!empty($ciMarks)) {
                    $internalMark = array_sum($ciMarks) / count($ciMarks);
                }
                break;
        }
        
        $rowMap[$key]['int_obt'] = round($internalMark, 2);
    }

    // Fetch external marks from subject_results
    $sqlExt = "SELECT sr.student_id, sr.subject_id, sr.external_marks, 
                      sr.internal_marks, sr.result, g.grade_name, sr.grade_point
               FROM subject_results sr 
               LEFT JOIN grades g ON sr.grade_id = g.grade_id
               WHERE sr.student_id IN ($studentIdsPlaceholder) 
                 AND sr.subject_id IN ($subjectIdsPlaceholder) 
                 AND sr.exam_id = ?";
    
    $stmt = $pdo->prepare($sqlExt);
    $stmt->execute([$exam_id]);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $key = $row['student_id'] . '_' . $row['subject_id'];
        if (!isset($rowMap[$key])) {
            $rowMap[$key] = [];
        }
        
        $rowMap[$key]['ext_obt'] = (float)$row['external_marks'];
        
        // Only set internal marks if not already computed from internal_marks table
        if (!isset($rowMap[$key]['int_obt'])) {
            $rowMap[$key]['int_obt'] = (float)$row['internal_marks'];
        }
        
        $rowMap[$key]['result'] = $row['result'];
        $rowMap[$key]['grade'] = $row['grade_name'];
        $rowMap[$key]['grade_point'] = (float)$row['grade_point'];
    }

    // Fallback: Fetch external marks from valuation_marks if not found in subject_results
    $sqlVm = "SELECT sr.student_id, es.subject_id, vm.total_mark AS ext_obt
              FROM valuation_marks vm
              JOIN student_registrations sr ON vm.registration_id = sr.registration_id
              JOIN exam_subjects es ON vm.exam_subject_id = es.exam_subject_id
              WHERE sr.student_id IN ($studentIdsPlaceholder)
                AND es.subject_id IN ($subjectIdsPlaceholder)
                AND es.exam_id = ?";
    
    $stmt = $pdo->prepare($sqlVm);
    $stmt->execute([$exam_id]);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $key = $row['student_id'] . '_' . $row['subject_id'];
        if (!isset($rowMap[$key])) {
            $rowMap[$key] = [];
        }
        
        // Don't override existing external marks from subject_results
        if (!isset($rowMap[$key]['ext_obt'])) {
            $rowMap[$key]['ext_obt'] = (float)$row['ext_obt'];
        }
    }

    // Calculate missing results and grades
    foreach ($rowMap as $key => $data) {
        list($studentId, $subjectId) = explode('_', $key);
        $subjectId = (int)$subjectId;
        
        $meta = $subMetaById[$subjectId] ?? null;
        if (!$meta) continue;

        $intObt = $data['int_obt'] ?? 0;
        $extObt = $data['ext_obt'] ?? 0;
        
        // Set result if missing
        if (!isset($data['result'])) {
            $hasValidMarks = ($intObt > 0 || $extObt > 0);
            $rowMap[$key]['result'] = $hasValidMarks ? 
                (($intObt >= 10) && ($extObt >= $meta['ext_pass']) ? 'Pass' : 'Fail') : 
                'Absent';
        }
        
        // Set grade if missing
        if (!isset($data['grade'])) {
            $externalMarks = $extObt;
            $grade = 'NA';
            
            if ($externalMarks >= 90) $grade = 'O';
            elseif ($externalMarks >= 80) $grade = 'A+';
            elseif ($externalMarks >= 70) $grade = 'A';
            elseif ($externalMarks >= 60) $grade = 'B+';
            elseif ($externalMarks >= 50) $grade = 'B';
            elseif ($externalMarks >= 40) $grade = 'C';
            elseif ($externalMarks > 0) $grade = 'F';
            
            $rowMap[$key]['grade'] = $grade;
        }
    }

    echo json_encode([
        'status' => 'success',
        'students' => $students,
        'subjects' => $subjectsMeta,
        'rows_by_key' => $rowMap
    ]);

} catch (Throwable $e) {
    error_log('read_exam_based_matrix error: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'status' => 'error',
        'message' => 'Internal server error'
    ]);
}
?>