<?php
// terminal_grade_ledger.php - FIXED MARKS QUERY WITH academic_year_id
include("includes/db.php");

// Start output buffering
ob_start();

// Grading Functions
function calculateGP($MP)
{
    if ($MP >= 0.9) {
        return 4.0;
    } elseif ($MP >= 0.8) {
        return 3.6;
    } elseif ($MP >= 0.7) {
        return 3.2;
    } elseif ($MP >= 0.6) {
        return 2.8;
    } elseif ($MP >= 0.5) {
        return 2.4;
    } elseif ($MP >= 0.4) {
        return 2.0;
    } elseif ($MP >= 0.35) {
        return 1.6;
    } elseif ($MP >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs';
    }
}

function calculateGrade($MP)
{
    if ($MP >= 0.9) {
        return 'A+';
    } elseif ($MP >= 0.8) {
        return 'A';
    } elseif ($MP >= 0.7) {
        return 'B+';
    } elseif ($MP >= 0.6) {
        return 'B';
    } elseif ($MP >= 0.5) {
        return 'C+';
    } elseif ($MP >= 0.4) {
        return 'C';
    } elseif ($MP >= 0.35) {
        return 'D';
    } elseif ($MP >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs';
    }
}

// Initialize variables
$schoolname = '';
$schooladdress = '';
$estd = '';
$examtype = '';
$examyear = '';
$sectionName = '';

// Get POST data
$examId = intval($_POST['exam_id'] ?? 0);
$classId = intval($_POST['class_id'] ?? 0);
$academicYear = $_POST['academic_year'] ?? '';
$sectionIds = [];

if (!empty($_POST['section_ids']) && is_array($_POST['section_ids'])) {
    $sectionIds = array_filter(array_map('intval', $_POST['section_ids']));
}

// Validate required parameters
if ($examId <= 0 || $classId <= 0 || empty($academicYear)) {
    echo "<div class='alert alert-danger'>Missing required parameters</div>";
    exit;
}

// Get school details
$schoolQuery = "SELECT * FROM schools LIMIT 1";
$schoolResult = $conn->query($schoolQuery);
if ($schoolResult->num_rows > 0) {
    $school = $schoolResult->fetch_assoc();
    $schoolname = $school['school_name'] ?? '';
    $schooladdress = $school['address'] ?? '';
    $estd = $school['establishment_year'] ?? '';
}

// Get exam details
$examQuery = "SELECT exam_type, exm_year FROM examination_type WHERE id = $examId";
$examResult = $conn->query($examQuery);
if ($examResult->num_rows > 0) {
    $exam = $examResult->fetch_assoc();
    $examtype = $exam['exam_type'] ?? '';
    $examyear = $exam['exm_year'] ?? '';
}

// Get class details
$classQuery = "SELECT class FROM classes WHERE class_id = $classId";
$classResult = $conn->query($classQuery);
$className = '';
if ($classResult->num_rows > 0) {
    $class = $classResult->fetch_assoc();
    $className = $class['class'] ?? '';
}

// Get academic_year_id from academic_years table
$academicYearId = 0;
$academicYearQuery = "SELECT year_id FROM academic_years WHERE ay_ad = '$academicYear' LIMIT 1";
$academicYearResult = $conn->query($academicYearQuery);
if ($academicYearResult && $academicYearResult->num_rows > 0) {
    $yearData = $academicYearResult->fetch_assoc();
    $academicYearId = $yearData['year_id'] ?? 0;
}

// Get section names if sections are selected
$sectionNames = [];
if (!empty($sectionIds)) {
    $sectionIdString = implode(',', $sectionIds);
    $sectionQuery = "SELECT section_id, section_name FROM sections WHERE section_id IN ($sectionIdString)";
    $sectionResult = $conn->query($sectionQuery);
    if ($sectionResult && $sectionResult->num_rows > 0) {
        while ($section = $sectionResult->fetch_assoc()) {
            $sectionNames[$section['section_id']] = $section['section_name'];
        }
        $sectionName = implode(', ', array_values($sectionNames));
    }
}

// Build section filter
$sectionFilter = '';
if (!empty($sectionIds)) {
    $sectionIdString = implode(',', $sectionIds);
    $sectionFilter = " AND scsd.section_id IN ($sectionIdString)";
}

/* ================= FETCH STUDENTS ================= */
$studentSql = "
SELECT 
    s.student_id, 
    s.name, 
    s.regd_id,
    s.symbol_no,
    sec.section_name,
    scsd.section_id
FROM students s
INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
INNER JOIN sections sec ON scsd.section_id = sec.section_id
WHERE scsd.class_id = $classId
AND scsd.academic_year = '$academicYear'
AND scsd.status = 'active'
AND s.status = 'active'
$sectionFilter
ORDER BY sec.section_name, s.name
";

$studentRes = $conn->query($studentSql);
if (!$studentRes) {
    echo "<div class='alert alert-danger'>Student query error: " . $conn->error . "</div>";
    exit;
}

if ($studentRes->num_rows == 0) {
    echo "<div class='alert alert-warning'>No students found for the selected criteria</div>";
    exit;
}

$students = [];
$studentIds = [];

while ($row = $studentRes->fetch_assoc()) {
    $students[] = $row;
    $studentIds[] = $row['student_id'];
}

$studentIdString = implode(',', $studentIds);

/* ================= FETCH ALL STUDENT SUBJECTS ================= */
// Get subjects for each student from studentsub table
$allStudentSubjects = [];
$subjectCodesSet = [];

$studentSubQuery = "
SELECT student_id, sub1, sub2, sub3, sub4, sub5, sub6, sub7 
FROM studentsub 
WHERE student_id IN ($studentIdString)
AND academic_year = '$academicYear'
AND class_id = $classId
";

$studentSubRes = $conn->query($studentSubQuery);
if ($studentSubRes && $studentSubRes->num_rows > 0) {
    while ($row = $studentSubRes->fetch_assoc()) {
        $studentId = $row['student_id'];
        $allStudentSubjects[$studentId] = [];
        
        // Process all 7 subject columns
        for ($i = 1; $i <= 7; $i++) {
            $code = $row['sub' . $i] ?? '';
            if (!empty($code)) {
                $allStudentSubjects[$studentId][] = $code;
                if (!in_array($code, $subjectCodesSet)) {
                    $subjectCodesSet[] = $code;
                }
            }
        }
        
        // Ensure each student has at least 6 subjects (pad with empty if needed)
        while (count($allStudentSubjects[$studentId]) < 6) {
            $allStudentSubjects[$studentId][] = '';
        }
        
        // Limit to maximum 7 subjects
        if (count($allStudentSubjects[$studentId]) > 7) {
            $allStudentSubjects[$studentId] = array_slice($allStudentSubjects[$studentId], 0, 7);
        }
    }
}

// Determine max subjects across all students
$maxSubjects = 6; // Default minimum
foreach ($allStudentSubjects as $studentSubjects) {
    $subjectCount = count(array_filter($studentSubjects));
    if ($subjectCount > $maxSubjects) {
        $maxSubjects = $subjectCount;
    }
}
// Ensure max is between 6 and 7
$maxSubjects = max(6, min(7, $maxSubjects));

/* ================= FETCH SUBJECT DETAILS ================= */
// Get details for all unique subject codes
$subjectDetailsMap = [];

if (!empty($subjectCodesSet)) {
    $subjectCodesString = "'" . implode("','", array_filter($subjectCodesSet)) . "'";
    
    $subjectDetailsQuery = "
    SELECT id, subject_code, subject_name, theory_full_marks, theory_credit_hour
    FROM subjects 
    WHERE subject_code IN ($subjectCodesString)
    AND status = 1
    ORDER BY subject_code
    ";
    
    $subjectDetailsRes = $conn->query($subjectDetailsQuery);
    if ($subjectDetailsRes && $subjectDetailsRes->num_rows > 0) {
        while ($row = $subjectDetailsRes->fetch_assoc()) {
            $subjectDetailsMap[$row['subject_code']] = $row;
        }
    }
    
    // Handle any subject codes not found in subjects table
    foreach ($subjectCodesSet as $code) {
        if (!empty($code) && !isset($subjectDetailsMap[$code])) {
            $subjectDetailsMap[$code] = [
                'id' => 0,
                'subject_code' => $code,
                'subject_name' => $code . ' (Not in Subjects Table)',
                'theory_full_marks' => 100,
                'theory_credit_hour' => 4.00 // Default credit hour
            ];
        }
    }
}

// Get subject IDs for marks query
$subjectIds = array_column($subjectDetailsMap, 'id');
$subjectIds = array_filter($subjectIds);
$subjectIdString = !empty($subjectIds) ? implode(',', $subjectIds) : '0';

/* ================= FETCH MARKS FOR ALL STUDENTS ================= */
$marksData = [];

// Get marks by subject code - USING academic_year_id
$marksSql = "
SELECT 
    m.student_id,
    s.subject_code,
    m.marks,
    m.full_marks as marks_full_marks  -- Get full_marks from marks table
FROM marks m
INNER JOIN subjects s ON m.subject_id = s.id
WHERE m.exam_type = $examId
AND m.student_id IN ($studentIdString)
AND m.class_id = $classId
AND m.mark_type = 'theory'
AND m.academic_year_id = $academicYearId
";

// Debug: Check the marks query
// echo "<!-- Marks Query: $marksSql -->";

$marksRes = $conn->query($marksSql);
if ($marksRes) {
    if ($marksRes->num_rows > 0) {
        while ($row = $marksRes->fetch_assoc()) {
            if (!isset($marksData[$row['student_id']])) {
                $marksData[$row['student_id']] = [];
            }
            // Store both marks and full_marks from marks table
            $marksData[$row['student_id']][$row['subject_code']] = [
                'marks' => $row['marks'],
                'full_marks' => $row['marks_full_marks']
            ];
        }
        // echo "<!-- Found " . $marksRes->num_rows . " marks records -->";
    } else {
        // echo "<!-- No marks found with query -->";
        // Debug: Check what data exists
        $debugSql = "SELECT COUNT(*) as count FROM marks WHERE exam_type = $examId AND student_id IN ($studentIdString) AND class_id = $classId AND academic_year_id = $academicYearId";
        $debugRes = $conn->query($debugSql);
        if ($debugRes) {
            $debug = $debugRes->fetch_assoc();
            // echo "<!-- Total marks records for criteria: " . $debug['count'] . " -->";
        }
    }
} else {
    // echo "<!-- Marks query error: " . $conn->error . " -->";
}

/* ================= FETCH SYMBOL NUMBERS ================= */
$symbolSql = "
SELECT student_id, symbol_no 
FROM symbolno_data 
WHERE exam_id = $examId 
AND student_id IN ($studentIdString)
";

$symbolRes = $conn->query($symbolSql);
$symbolData = [];

if ($symbolRes && $symbolRes->num_rows > 0) {
    while ($row = $symbolRes->fetch_assoc()) {
        $symbolData[$row['student_id']] = $row['symbol_no'];
    }
}

/* ================= FETCH ATTENDANCE ================= */
$attendanceSql = "
SELECT std_id, atten_day 
FROM attendance 
WHERE exam_type = $examId 
AND std_id IN ($studentIdString)
";

$attendanceRes = $conn->query($attendanceSql);
$attendanceData = [];

if ($attendanceRes && $attendanceRes->num_rows > 0) {
    while ($row = $attendanceRes->fetch_assoc()) {
        $attendanceData[$row['std_id']] = $row['atten_day'];
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Terminal Grade Ledger</title>
<style>
.table-container { overflow-x: auto; }
#gradeLedger { width: 100%; border-collapse: collapse; font-size: 10px; }
#gradeLedger th { background: #343a40; color: #fff; padding: 4px; border: 1px solid #444; text-align: center; white-space: nowrap; }
#gradeLedger td { padding: 3px; border: 1px solid #ddd; text-align: center; }
#gradeLedger tbody tr:nth-child(even) { background: #f8f9fa; }
#gradeLedger tbody tr:hover { background: #eef2f7; }
.header-info { background: #f8f9fa; padding: 8px; border-radius: 3px; margin-bottom: 8px; }
.subject-code { font-size: 9px; color: #666; }
.gpa-column { background: #fff3cd !important; font-weight: bold; color: #856404; }
.attendance-column { background: #e3f2fd !important; font-weight: bold; }
.gp-cell { font-weight: bold; }
.grade-cell { font-weight: bold; }
.gp-ng { color: red !important; }
.gp-abs { color: #0011ff !important; }
.grade-ng { color: red !important; }
.grade-abs { color: #0011ff !important; }
.compact-row { margin: 0; padding: 2px 0; }
.compact-p { margin: 0; padding: 0; }
.dataTables_wrapper { padding: 5px 0; font-size: 10px; }
.dt-buttons { margin-bottom: 3px; }
.table-sm th, .table-sm td { padding: 2px 3px; }
@media print {
    body { font-size: 9px; padding: 0; }
    #gradeLedger { font-size: 8px; }
    .no-print, .dt-buttons, .dataTables_filter, .dataTables_length { display: none !important; }
    @page { size: landscape; margin: 5mm; }
}
</style>
</head>
<body>
<div class="container-fluid p-1">

<!-- COMPACT HEADER -->
<div class="header-info">
    <!-- School Details - Centered -->
    <div class="row compact-row mb-1">
        <div class="col-md-12 text-center p-0">
            <h5 class="mb-0"><?= htmlspecialchars($schoolname) ?></h5>
            <p class="compact-p"><?= htmlspecialchars($schooladdress) ?> | Estd: <?= htmlspecialchars($estd) ?></p>
        </div>
    </div>
    
    <!-- Ledger Details - Three Column Layout -->
    <div class="row compact-row align-items-center">
        <div class="col-md-4 text-left p-0">
            <h6 class="mb-0">Class: <?= htmlspecialchars($className) ?>
                <?php if (!empty($sectionName)): ?>
                | Section(s): <?= htmlspecialchars($sectionName) ?>
                <?php endif; ?>
            </h6>
        </div>
        <div class="col-md-4 text-center p-0">
            <h6 class="mb-0">Exam: <?= htmlspecialchars($examtype) ?> - <?= htmlspecialchars($examyear) ?></h6>
        </div>
        <div class="col-md-4 text-right p-0">
            <h6 class="mb-0">Academic Year: <?= htmlspecialchars($academicYear) ?>
                <small class="text-muted">| Students: <?= count($students) ?></small>
            </h6>
        </div>
    </div>
</div>

<div class="table-container">
<table id="gradeLedger" class="table table-sm">
<thead>
<tr>
    <th rowspan="3">S.N</th>
    <th rowspan="3">Symbol No</th>
    <th rowspan="3">Student Name</th>
    <th rowspan="3">Section</th>
    <?php for ($i = 1; $i <= $maxSubjects; $i++): ?>
    <th colspan="3">Sub<?= $i ?></th>
    <?php endfor; ?>
    <th rowspan="3">Final GPA</th>
    <th rowspan="3">Attendance<br>Days</th>
</tr>
<tr>
    <?php for ($i = 1; $i <= $maxSubjects; $i++): ?>
    <th>Code</th>
    <th>GP</th>
    <th>Grade</th>
    <?php endfor; ?>
</tr>
</thead>
<tbody>
<?php 
$sn = 1; 
foreach ($students as $student): 
    $studentId = $student['student_id'];
    $studentSymbol = $symbolData[$studentId] ?? $student['symbol_no'] ?? '';
    $attendanceDays = $attendanceData[$studentId] ?? '-';
    
    $studentSubjects = $allStudentSubjects[$studentId] ?? [];
    
    $totalGradePointCredit = 0;
    $totalCreditHours = 0;
    $hasNG = false;
    $hasAbs = false;
?>
<tr>
    <td><?= $sn++ ?></td>
    <td style="color:red;font-weight:bold;"><?= htmlspecialchars($studentSymbol) ?></td>
    <td style="text-align:left"><?= htmlspecialchars($student['name']) ?></td>
    <td><?= htmlspecialchars($student['section_name']) ?></td>
    
    <?php 
    for ($i = 0; $i < $maxSubjects; $i++): 
        $subjectCode = $studentSubjects[$i] ?? '';
        $subjectFullMarks = 100;
        $creditHour = 4.00;
        
        if (!empty($subjectCode) && isset($subjectDetailsMap[$subjectCode])) {
            $subjectFullMarks = $subjectDetailsMap[$subjectCode]['theory_full_marks'] ?? 100;
            $creditHour = $subjectDetailsMap[$subjectCode]['theory_credit_hour'] ?? 4.00;
        }
        
        $marks = '';
        $marksFullMarks = $subjectFullMarks;
        
        if (!empty($subjectCode) && isset($marksData[$studentId][$subjectCode])) {
            $marks = $marksData[$studentId][$subjectCode]['marks'];
            $marksFullMarks = $marksData[$studentId][$subjectCode]['full_marks'] ?? $subjectFullMarks;
        }
        
        $mp = 0;
        $gp = '';
        $grade = '';
        $gpClass = 'gp-cell';
        $gradeClass = 'grade-cell';
        
        if (!empty($subjectCode)) {
            if (is_numeric($marks) && $marksFullMarks > 0) {
                $mp = $marks / $marksFullMarks;
                $gp = calculateGP($mp);
                $grade = calculateGrade($mp);
                
                if ($gp === 'NG') {
                    $hasNG = true;
                    $gpClass .= ' gp-ng';
                    $gradeClass .= ' grade-ng';
                } elseif ($gp === 'Abs') {
                    $hasAbs = true;
                    $gpClass .= ' gp-abs';
                    $gradeClass .= ' grade-abs';
                } elseif (is_numeric($gp)) {
                    $totalGradePointCredit += ($gp * $creditHour);
                    $totalCreditHours += $creditHour;
                }
            } else {
                $hasAbs = true;
                $gp = 'Abs';
                $grade = 'Abs';
                $gpClass .= ' gp-abs';
                $gradeClass .= ' grade-abs';
            }
        }
    ?>
    <td><?= !empty($subjectCode) ? $subjectCode : '&nbsp;' ?></td>
    <td class="<?= $gpClass ?>"><?= !empty($subjectCode) ? $gp : '&nbsp;' ?></td>
    <td class="<?= $gradeClass ?>"><?= !empty($subjectCode) ? $grade : '&nbsp;' ?></td>
    <?php endfor; ?>
    
    <td class="gpa-column">
        <?php 
        if ($hasNG || $hasAbs) {
            echo '<span style="color:#666">-</span>';
        } elseif ($totalCreditHours > 0) {
            $finalGPA = $totalGradePointCredit / $totalCreditHours;
            echo number_format($finalGPA, 2);
        } else {
            echo '<span style="color:#666">-</span>';
        }
        ?>
    </td>
    
    <td class="attendance-column">
        <?= htmlspecialchars($attendanceDays) ?>
    </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>

<!-- Compact Subject Reference -->
<div class="row mt-1">
    <div class="col-md-12">
        <div style="font-size:9px; font-weight:bold; margin-bottom:1px;">Subject Reference</div>
        <div style="font-size:8px;">
            <table class="table table-bordered table-sm mb-0">
                <thead style="background:#343a40; color:white;">
                    <tr>
                        <th width="3%">SN</th>
                        <th width="8%">Code</th>
                        <th width="25%">Subject</th>
                        <th width="4%">FM</th>
                        <th width="5%">Credit</th>
                        <th width="1%" style="background:white;"></th>
                        <th width="3%">SN</th>
                        <th width="8%">Code</th>
                        <th width="25%">Subject</th>
                        <th width="4%">FM</th>
                        <th width="5%">Credit</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $subjectCount = count($subjectDetailsMap);
                    $halfCount = ceil($subjectCount / 2);
                    
                    ksort($subjectDetailsMap);
                    $subjectArray = array_values($subjectDetailsMap);
                    $subjectCodesArray = array_keys($subjectDetailsMap);
                    
                    for ($i = 0; $i < $halfCount; $i++): 
                        $hasSecondColumn = ($i + $halfCount) < $subjectCount;
                    ?>
                    <tr>
                        <td align="center"><?= $i + 1 ?></td>
                        <td align="center"><strong><?= htmlspecialchars($subjectCodesArray[$i]) ?></strong></td>
                        <td><?= htmlspecialchars($subjectArray[$i]['subject_name']) ?></td>
                        <td align="center"><?= $subjectArray[$i]['theory_full_marks'] ?? 100 ?></td>
                        <td align="center"><?= number_format($subjectArray[$i]['theory_credit_hour'] ?? 4.00, 2) ?></td>
                        
                        <td style="background:white;"></td>
                        
                        <?php if ($hasSecondColumn): 
                            $secondIndex = $i + $halfCount;
                        ?>
                            <td align="center"><?= $secondIndex + 1 ?></td>
                            <td align="center"><strong><?= htmlspecialchars($subjectCodesArray[$secondIndex]) ?></strong></td>
                            <td><?= htmlspecialchars($subjectArray[$secondIndex]['subject_name']) ?></td>
                            <td align="center"><?= $subjectArray[$secondIndex]['theory_full_marks'] ?? 100 ?></td>
                            <td align="center"><?= number_format($subjectArray[$secondIndex]['theory_credit_hour'] ?? 4.00, 2) ?></td>
                        <?php else: ?>
                            <td colspan="5" style="background:#f8f9fa;"></td>
                        <?php endif; ?>
                    </tr>
                    <?php endfor; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Compact Grade Legend -->
<div class="row mt-2">
    <div class="col-md-12">
        <div class="alert alert-info py-1 px-2" style="font-size:9px;">
            <strong>Grade Point System:</strong>
            <table class="table table-sm table-bordered mb-1" style="font-size:8px;">
                <thead>
                    <tr>
                        <th>MP Range</th><th>Grade</th><th>GP</th>
                        <th>MP Range</th><th>Grade</th><th>GP</th>
                        <th>MP Range</th><th>Grade</th><th>GP</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>90-100%</td><td><strong>A+</strong></td><td><strong>4.0</strong></td>
                        <td>60-70%</td><td><strong>B</strong></td><td><strong>2.8</strong></td>
                        <td>Below 35%</td><td><span style="color:red"><strong>NG</strong></span></td><td><span style="color:red"><strong>NG</strong></span></td>
                    </tr>
                    <tr>
                        <td>80-90%</td><td><strong>A</strong></td><td><strong>3.6</strong></td>
                        <td>50-60%</td><td><strong>C+</strong></td><td><strong>2.4</strong></td>
                        <td>Absent</td><td><span style="color:#0011ff"><strong>Abs</strong></span></td><td><span style="color:#0011ff"><strong>Abs</strong></span></td>
                    </tr>
                    <tr>
                        <td>70-80%</td><td><strong>B+</strong></td><td><strong>3.2</strong></td>
                        <td>40-50%</td><td><strong>C</strong></td><td><strong>2.0</strong></td>
                        <td colspan="3" class="text-center"><strong>GPA = (∑[GP × Credit]) ÷ Total Credit</strong></td>
                    </tr>
                    <tr>
                        <td colspan="3"></td>
                        <td>35-40%</td><td><strong>D</strong></td><td><strong>1.6</strong></td>
                        <td colspan="3" class="text-center"><strong>NG/Abs in any subject = No GPA</strong></td>
                    </tr>
                </tbody>
            </table>
        </div>
    </div>
</div>

</div>

</body>
</html>