<?php
require_once __DIR__ . '/../../config/config.php';
header('Content-Type: application/json');
ob_start();
try {
    $academic_year = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
    $course_id = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
    $semester = isset($_GET['semester']) && $_GET['semester']!=='' ? (int)$_GET['semester'] : null;
    if(!$academic_year || !$course_id){ echo json_encode(['status'=>'error','message'=>'Missing filters']); exit; }
    global $pdo;

    // Terms: support AY already including Odd/Even or compute from semester
    // Also handle cases like "1997-1998 Odd Odd" by cleaning up duplicate terms
    $ay_term = (preg_match('/\b(Odd|Even)\b$/', $academic_year)) ? $academic_year : ($academic_year . ($semester? (' ' . (($semester % 2)===1 ? 'Odd' : 'Even')) : ''));
    // Clean up duplicate terms like "1997-1998 Odd Odd" -> "1997-1998 Odd"
    $ay_term = preg_replace('/\s+(Odd|Even)\s+\1$/', ' $1', $ay_term);

    // Students
    $sqlStu = "SELECT student_id, university_rollno, first_name, last_name FROM students WHERE course_id=?" . ($semester? " AND current_semester=?" : "") . " ORDER BY university_rollno";
    $stmt = $pdo->prepare($sqlStu);
    $stmt->execute($semester? [$course_id, $semester] : [$course_id]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Subjects
    $sqlSub = "SELECT subject_id, subject_code, subject_name FROM subjects WHERE course_id=?" . ($semester? " AND semester=?" : "") . " ORDER BY subject_code";
    $stmt = $pdo->prepare($sqlSub);
    $stmt->execute($semester? [$course_id, $semester] : [$course_id]);
    $subjects = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Internal settings per subject
    $setMap = [];
    if (!empty($subjects)){
        $ids = implode(',', array_map('intval', array_column($subjects,'subject_id')));
        $q = $pdo->query("SELECT subject_id, MAX(setting_id) as max_set FROM subject_assessment_settings WHERE exam_type='Internal' AND subject_id IN ($ids) GROUP BY subject_id");
        $latest = $q->fetchAll(PDO::FETCH_ASSOC);
        $idList = array_map(function($r){ return (int)$r['max_set']; }, $latest);
        if(!empty($idList)){
            $q2 = $pdo->query("SELECT subject_id, max_marks as int_max, pass_marks as int_pass, min_marks as int_min FROM subject_assessment_settings WHERE setting_id IN (".implode(',', $idList).")");
            while($r=$q2->fetch(PDO::FETCH_ASSOC)){ $setMap[(int)$r['subject_id']] = $r; }
        }
    }

    // External settings per subject (from exam_subjects max/pass)
    $extMap = [];
    if (!empty($subjects)){
        $ids = implode(',', array_map('intval', array_column($subjects,'subject_id')));
        $sql = "SELECT es.subject_id, es.max_marks as ext_max, es.pass_marks as ext_pass FROM exam_subjects es JOIN exams e ON es.exam_id=e.exam_id WHERE es.subject_id IN ($ids)" . ($ay_term? " AND e.academic_year=".$pdo->quote($ay_term) : "") . ($semester? " AND e.semester=".(int)$semester : "");
        $q = $pdo->query($sql);
        while($r=$q->fetch(PDO::FETCH_ASSOC)){ $extMap[(int)$r['subject_id']] = $r; }
    }

    // Build subject meta with ranges
    $subjectsMeta = array_map(function($s) use ($setMap, $extMap){
        $sid = (int)$s['subject_id'];
        $int = $setMap[$sid] ?? ['int_max'=>20,'int_min'=>0,'int_pass'=>10];
        $ext = $extMap[$sid] ?? ['ext_max'=>100,'ext_pass'=>50];
        return [
          'subject_id'=>$sid,
          'subject_code'=>$s['subject_code'],
          'subject_name'=>$s['subject_name'],
          'int_max'=>(int)($int['int_max']??20),
          'int_min'=>(int)($int['int_min']??0),
          'int_pass'=>(int)($int['int_pass']??0),
          'ext_max'=>(int)($ext['ext_max']??100),
          'ext_min'=>(int)($ext['ext_pass']??0),
          'ext_pass'=>(int)($ext['ext_pass']??0)
        ];
    }, $subjects);

    // Collect internal totals from internal_marks table (CI1, CI2, CI3)
    $rowMap = [];
    if (!empty($students) && !empty($subjects)){
        $stuIds = implode(',', array_map('intval', array_column($students,'student_id')));
        $subIds = implode(',', array_map('intval', array_column($subjects,'subject_id')));

        // 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';

        // Internal marks from internal_marks table - compute based on calculation method for each student/subject
        $sqlInt = "SELECT 
                    im.student_id, 
                    ie.subject_id,
                    ie.exam_type_id,
                    im.marks_obtained
                  FROM internal_marks im
                  JOIN internal_exams ie ON im.internal_exam_id = ie.internal_exam_id
                  WHERE im.student_id IN ($stuIds) 
                    AND ie.subject_id IN ($subIds) 
                    AND ie.academic_year = ".$pdo->quote($ay_term);
        $qInt = $pdo->query($sqlInt);
        $internalData = [];
        while($r=$qInt->fetch(PDO::FETCH_ASSOC)){
          $key = ((int)$r['student_id']).'_'.((int)$r['subject_id']);
          if(!isset($internalData[$key])) $internalData[$key] = [];
          $internalData[$key][(int)$r['exam_type_id']] = (float)$r['marks_obtained'];
        }
        
        // Compute internal marks based on calculation method for each student/subject
        foreach($internalData as $key => $marks){
          if(!isset($rowMap[$key])) $rowMap[$key] = [];
          $ciMarks = array_values($marks); // CI1, CI2, CI3 marks
          
          if(count($ciMarks) >= 1){
            $internalMark = 0;
            
            if($calculationMethod === 'best1'){
              // Best of 1
              $internalMark = max($ciMarks);
            } else if($calculationMethod === 'avg3' && count($ciMarks) >= 3){
              // Average of 3
              $internalMark = array_sum($ciMarks) / count($ciMarks);
            } else if($calculationMethod === 'best2_avg' && count($ciMarks) >= 2){
              // Best 2 average (default behavior)
              rsort($ciMarks);
              $best2 = array_slice($ciMarks, 0, 2);
              $internalMark = array_sum($best2) / count($best2);
            } else if(count($ciMarks) >= 2){
              // Fallback to best 2 average if not enough marks for selected method
              rsort($ciMarks);
              $best2 = array_slice($ciMarks, 0, 2);
              $internalMark = array_sum($best2) / count($best2);
            } else {
              // Just use the single mark if only one exists
              $internalMark = $ciMarks[0];
            }
            
            $rowMap[$key]['int_obt'] = round($internalMark, 2);
          }
        }

        // External from subject_results if present for exams matching AY/sem
        $sqlExt = "SELECT sr.student_id, sr.subject_id, sr.external_marks, sr.internal_marks, sr.result FROM subject_results sr JOIN exams e ON sr.exam_id=e.exam_id WHERE sr.student_id IN ($stuIds) AND sr.subject_id IN ($subIds) AND e.academic_year=".$pdo->quote($ay_term) . ($semester? " AND e.semester=".(int)$semester : "");
        $qExt = $pdo->query($sqlExt);
        while($r=$qExt->fetch(PDO::FETCH_ASSOC)){
          $key = ((int)$r['student_id']).'_'.((int)$r['subject_id']);
          if(!isset($rowMap[$key])) $rowMap[$key]=[];
          $rowMap[$key]['ext_obt'] = (float)$r['external_marks'];
          if(!isset($rowMap[$key]['int_obt'])) $rowMap[$key]['int_obt'] = (float)$r['internal_marks'];
          $rowMap[$key]['result'] = $r['result'];
        }

        // Fallback external from valuation_marks (latest saved) if subject_results not available
        $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
                  JOIN exams e ON es.exam_id = e.exam_id
                  WHERE sr.student_id IN ($stuIds)
                    AND es.subject_id IN ($subIds)
                    AND e.academic_year=".$pdo->quote($ay_term)
                    . ($semester? " AND e.semester=".(int)$semester : "") .
                  "";
        $qVm = $pdo->query($sqlVm);
        while($r=$qVm->fetch(PDO::FETCH_ASSOC)){
          $key = ((int)$r['student_id']).'_'.((int)$r['subject_id']);
          if(!isset($rowMap[$key])) $rowMap[$key]=[];
          if(!isset($rowMap[$key]['ext_obt'])){ // don't override subject_results
            $rowMap[$key]['ext_obt'] = (float)$r['ext_obt'];
          }
        }

        // Get grading system for the course
        $gradingSystem = null;
        $gradingSystemName = 'No Grading System';
        if ($course_id > 0) {
            $gsStmt = $pdo->prepare("SELECT grading_system_id, system_name FROM grading_systems WHERE course_id = ? AND is_active = 1 ORDER BY grading_system_id DESC LIMIT 1");
            $gsStmt->execute([$course_id]);
            $gsResult = $gsStmt->fetch(PDO::FETCH_ASSOC);
            
            if ($gsResult) {
                $gradingSystemId = $gsResult['grading_system_id'];
                $gradingSystemName = $gsResult['system_name'];
                
                $gradeStmt = $pdo->prepare("SELECT grade_name, min_marks, max_marks, grade_point, is_pass FROM grades WHERE grading_system_id = ? ORDER BY min_marks ASC");
                $gradeStmt->execute([$gradingSystemId]);
                $gradingSystem = $gradeStmt->fetchAll(PDO::FETCH_ASSOC);
                
                // Debug: Log grading system for troubleshooting
                error_log("Grading System for Course $course_id (ID: $gradingSystemId, Name: $gradingSystemName): " . json_encode($gradingSystem));
            } else {
                error_log("No grading system found for course $course_id");
                
                // Fallback: Try to get any active grading system
                $fallbackStmt = $pdo->prepare("SELECT grading_system_id, system_name FROM grading_systems WHERE is_active = 1 ORDER BY grading_system_id DESC LIMIT 1");
                $fallbackStmt->execute();
                $fallbackResult = $fallbackStmt->fetch(PDO::FETCH_ASSOC);
                
                if ($fallbackResult) {
                    $fallbackSystemId = $fallbackResult['grading_system_id'];
                    $gradingSystemName = $fallbackResult['system_name'] . ' (Fallback)';
                    
                    $fallbackGradeStmt = $pdo->prepare("SELECT grade_name, min_marks, max_marks, grade_point, is_pass FROM grades WHERE grading_system_id = ? ORDER BY min_marks ASC");
                    $fallbackGradeStmt->execute([$fallbackSystemId]);
                    $gradingSystem = $fallbackGradeStmt->fetchAll(PDO::FETCH_ASSOC);
                    error_log("Using fallback grading system (ID: $fallbackSystemId, Name: $gradingSystemName): " . json_encode($gradingSystem));
                }
            }
        }

        // Fallback result computation and grade assignment
        $subMetaById = [];
        foreach($subjectsMeta as $m){ $subMetaById[$m['subject_id']] = $m; }
        foreach($rowMap as $k=>$v){
          if(!isset($v['result'])){
            list($stuId, $subId) = array_map('intval', explode('_',$k));
            $meta = $subMetaById[$subId] ?? null; if(!$meta) continue;
            $intObt = isset($v['int_obt']) ? (float)$v['int_obt'] : 0;
            $extObt = isset($v['ext_obt']) ? (float)$v['ext_obt'] : 0;
            
            // Check if student has valid marks (not N/A or 0)
            $hasValidMarks = ($intObt > 0 || $extObt > 0);
            
            if (!$hasValidMarks) {
                $rowMap[$k]['result'] = 'Absent';
            } else {
                // Use grading system to determine pass/fail if available
                if ($gradingSystem && $extObt > 0) {
                    $grade = 'F';
                    foreach ($gradingSystem as $g) {
                        if ($extObt >= (int)$g['min_marks'] && $extObt <= (int)$g['max_marks']) {
                            $grade = $g['grade_name'];
                            break;
                        }
                    }
                    // Check if grade is pass based on is_pass field
                    $isPassGrade = false;
                    foreach ($gradingSystem as $g) {
                        if ($g['grade_name'] === $grade) {
                            $isPassGrade = (int)$g['is_pass'] === 1;
                            break;
                        }
                    }
                    $rowMap[$k]['result'] = $isPassGrade ? 'Pass' : 'Fail';
                    error_log("Result for $k: Grade=$grade, IsPass=$isPassGrade, Final=" . $rowMap[$k]['result']);
                } else {
                    // Fallback to traditional pass/fail logic
                    $pass = ($intObt >= (int)$meta['int_pass']) && ($extObt >= (int)$meta['ext_min']);
                    $rowMap[$k]['result'] = $pass ? 'Pass' : 'Fail';
                    error_log("Result for $k (fallback): Int=$intObt (min=" . $meta['int_pass'] . "), Ext=$extObt (min=" . $meta['ext_min'] . "), Pass=$pass, Final=" . $rowMap[$k]['result']);
                }
            }
          }
          
          // Calculate grade based on external marks (End Semester Examination)
          if (!isset($v['grade'])) {
            $externalMarks = isset($v['ext_obt']) ? (float)$v['ext_obt'] : 0;
            $totalMarks = (isset($v['int_obt']) ? (float)$v['int_obt'] : 0) + $externalMarks;
            $grade = 'NA';
            $gradePoint = 0;
            
            if ($gradingSystem && $externalMarks > 0) {
            // Debug: Log the marks being processed
            error_log("Processing marks for key $k: external=$externalMarks, total=$totalMarks, result=" . ($v['result'] ?? 'not set'));
                
                // Try to match based on external marks first
                foreach ($gradingSystem as $g) {
                    $minMarks = (int)$g['min_marks'];
                    $maxMarks = (int)$g['max_marks'];
                    error_log("Checking grade {$g['grade_name']}: range $minMarks-$maxMarks against external marks $externalMarks");
                    
                    if ($externalMarks >= $minMarks && $externalMarks <= $maxMarks) {
                        $grade = $g['grade_name'];
                        $gradePoint = (float)$g['grade_point'];
                        error_log("Grade matched: $grade for external marks $externalMarks (range: $minMarks-$maxMarks)");
                        break;
                    }
                }
                
                // If no match found with external marks, try with total marks
                if ($grade === 'NA' && $totalMarks > 0) {
                    error_log("No grade found for external marks $externalMarks, trying total marks $totalMarks");
                    foreach ($gradingSystem as $g) {
                        $minMarks = (int)$g['min_marks'];
                        $maxMarks = (int)$g['max_marks'];
                        error_log("Checking grade {$g['grade_name']}: range $minMarks-$maxMarks against total marks $totalMarks");
                        
                        if ($totalMarks >= $minMarks && $totalMarks <= $maxMarks) {
                            $grade = $g['grade_name'];
                            $gradePoint = (float)$g['grade_point'];
                            error_log("Grade matched: $grade for total marks $totalMarks (range: $minMarks-$maxMarks)");
                            break;
                        }
                    }
                }
            }
            
            $rowMap[$k]['grade'] = $grade;
            $rowMap[$k]['grade_point'] = $gradePoint;
          }
        }
    }

    echo json_encode(['status'=>'success','students'=>$students,'subjects'=>$subjectsMeta,'rows_by_key'=>$rowMap,'grading_system_name'=>$gradingSystemName]);
} catch (Throwable $e) {
    error_log('read_matrix error: '.$e->getMessage());
    http_response_code(500);
    echo json_encode(['status'=>'error','message'=>$e->getMessage()]);
}
ob_end_flush();
?>