<?php
// terminal_marksledger.php - SHOWS ONLY ASSIGNED SUBJECTS (6-7 MAX)
include("includes/db.php");

// Start output buffering
ob_start();

// Initialize variables
$schoolname = '';
$schooladdress = '';
$estd = '';
$examtype = '';
$examyear = '';
$ledger = [];
$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 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 
    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
            ];
        }
    }
}

// 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
$marksSql = "
SELECT 
    m.student_id,
    s.subject_code,
    m.marks
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.subject_id IN ($subjectIdString)
AND m.class_id = $classId
AND m.mark_type = 'theory'
";

$marksRes = $conn->query($marksSql);
if ($marksRes && $marksRes->num_rows > 0) {
    while ($row = $marksRes->fetch_assoc()) {
        if (!isset($marksData[$row['student_id']])) {
            $marksData[$row['student_id']] = [];
        }
        $marksData[$row['student_id']][$row['subject_code']] = $row['marks'];
    }
}

// Also get marks for subjects not in subjects table
if (in_array(0, $subjectIds)) {
    $missingSubjects = [];
    foreach ($subjectCodesSet as $code) {
        if (isset($subjectDetailsMap[$code]) && $subjectDetailsMap[$code]['id'] == 0) {
            $missingSubjects[] = $code;
        }
    }
    
    if (!empty($missingSubjects)) {
        $missingCodesString = "'" . implode("','", $missingSubjects) . "'";
        $marksByCodeSql = "
        SELECT m.student_id, s.subject_code, m.marks
        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 s.subject_code IN ($missingCodesString)
        ";
        
        $marksByCodeRes = $conn->query($marksByCodeSql);
        if ($marksByCodeRes && $marksByCodeRes->num_rows > 0) {
            while ($row = $marksByCodeRes->fetch_assoc()) {
                if (!isset($marksData[$row['student_id']])) {
                    $marksData[$row['student_id']] = [];
                }
                $marksData[$row['student_id']][$row['subject_code']] = $row['marks'];
            }
        }
    }
}

/* ================= 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>Marks Ledger</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<style>
.table-container{overflow-x:auto;}
#marksLedger{width:100%;border-collapse:collapse;font-size:13px;}
#marksLedger th{background:#343a40;color:#fff;padding:6px;border:1px solid #444;text-align:center;white-space:nowrap;}
#marksLedger td{padding:6px;border:1px solid #ddd;text-align:center;}
#marksLedger tbody tr:nth-child(even){background:#f8f9fa;}
#marksLedger tbody tr:hover{background:#eef2f7;}
.highlight{background-color:#ffcccc!important;}
.header-info{background:#f8f9fa;padding:15px;border-radius:5px;margin-bottom:15px;}
.subject-code{font-size:11px;color:#666;}
.full-marks{font-size:11px;color:#28a745;font-weight:bold;}
.total-column{background:#e8f5e8!important;font-weight:bold;}
.attendance-column{background:#e3f2fd!important;font-weight:bold;}
#subjectInfoTable th{background:#6c757d;color:#fff;}
@media print{
 body{font-size:11px;}
 #marksLedger{font-size:10px;}
 .no-print{display:none;}
 @page{size:landscape;margin:10mm;}
}
</style>
</head>
<body>
<div class="container-fluid mt-3">

<!-- Header Information -->
<div class="header-info">
    <div class="row">
        <div class="col-md-12 text-center mb-2">
            <h4><?= htmlspecialchars($schoolname) ?></h4>
            <p class="mb-1"><?= htmlspecialchars($schooladdress) ?></p>
            <p class="mb-1">Estd: <?= htmlspecialchars($estd) ?> | Exam: <?= htmlspecialchars($examtype) ?> - <?= htmlspecialchars($examyear) ?></p>
            <h5>Marks Ledger for Class: <?= htmlspecialchars($className) ?>
                <?php if (!empty($sectionName)): ?>
                    | Section(s): <?= htmlspecialchars($sectionName) ?>
                <?php endif; ?>
            </h5>
            <p>Academic Year: <?= htmlspecialchars($academicYear) ?> | Total Students: <?= count($students) ?> | Subjects per Student: <?= $maxSubjects ?></p>
        </div>
    </div>
</div>

<div class="table-container">
<table id="marksLedger">
<thead>
<tr>
    <th rowspan="2">S.N</th>
    <th rowspan="2">Symbol No</th>
    <th rowspan="2">Student Name</th>
    <th rowspan="2">Section</th>
    <?php for ($i = 1; $i <= $maxSubjects; $i++): ?>
    <th colspan="2">Sub<?= $i ?></th>
    <?php endfor; ?>
    <th rowspan="2">Total<br>Obtained<br>Marks</th>
    <th rowspan="2">Attendance<br>Days</th>
</tr>
<tr>
    <?php for ($i = 1; $i <= $maxSubjects; $i++): ?>
    <th class="subject-code">
        Code ||
        <span class="full-marks">FM</span>
    </th>
    <th>Obtained<br>Marks</th>
    <?php endfor; ?>
</tr>
</thead>
<tbody>
<?php 
$sn = 1; 
foreach ($students as $student): 
    $studentId = $student['student_id'];
    $studentSymbol = $symbolData[$studentId] ?? $student['symbol_no'] ?? '';
    $totalMarks = 0;
    $attendanceDays = $attendanceData[$studentId] ?? '-';
    
    // Get this student's subjects
    $studentSubjects = $allStudentSubjects[$studentId] ?? [];
?>
<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] ?? '';
        $fullMarks = 100;
        $subjectName = '';
        
        if (!empty($subjectCode) && isset($subjectDetailsMap[$subjectCode])) {
            $fullMarks = $subjectDetailsMap[$subjectCode]['theory_full_marks'] ?? 100;
            $subjectName = $subjectDetailsMap[$subjectCode]['subject_name'] ?? '';
        }
        
        $marks = '';
        if (!empty($subjectCode) && isset($marksData[$studentId][$subjectCode])) {
            $marks = $marksData[$studentId][$subjectCode];
        }
        
        // Add to total if marks are numeric
        if (!empty($subjectCode) && is_numeric($marks)) {
            $totalMarks += $marks;
        }
        
        // Highlight low marks (less than 35% of full marks)
        $highlightClass = '';
        if (!empty($subjectCode) && is_numeric($marks) && $fullMarks > 0) {
            $percentage = ($marks / $fullMarks) * 100;
            if ($percentage < 35) {
                $highlightClass = 'highlight';
            }
        }
    ?>
    <td>
        <?php if (!empty($subjectCode)): ?>
            <?= $subjectCode ?> ||
            <span class="full-marks"> <?= $fullMarks ?></span>
        <?php else: ?>
            &nbsp;
        <?php endif; ?>
    </td>
    <td class="<?= $highlightClass ?>" style="font-weight:bold;">
        <?php 
        if (empty($subjectCode)) {
            echo '&nbsp;';
        } elseif ($marks !== '') {
            echo is_numeric($marks) ? number_format($marks, 2) : $marks;
        } else {
            echo '-';
        }
        ?>
    </td>
    <?php endfor; ?>
    
    <!-- Total Obtained Marks Column -->
    <td class="total-column">
        <?= number_format($totalMarks, 2) ?>
    </td>
    
    <!-- Attendance Column -->
    <td class="attendance-column">
        <?= htmlspecialchars($attendanceDays) ?>
    </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>

<!-- Subject Information Table -->
<!-- Subject Information Table - Ultra Compact Two Column Layout -->
<div class="row mt-1">
    <div class="col-md-12">
        <div style="font-size:10px; font-weight:bold; margin-bottom:2px;">Subject Reference</div>
        <div style="font-size:9px;">
            <table border="1" cellspacing="0" cellpadding="1" style="border-collapse:collapse; width:100%;">
                <thead style="background:#343a40; color:white; font-size:9px;">
                    <tr>
                        <th width="3%">SN</th>
                        <th width="8%">Code</th>
                        <th width="30%">Subject</th>
                        <th width="4%">FM</th>
                        <th width="1%" style="background:white;"></th>
                        <th width="3%">SN</th>
                        <th width="8%">Code</th>
                        <th width="30%">Subject</th>
                        <th width="4%">FM</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 style="height:16px;">
                        <!-- First Column -->
                        <td align="center"><?= $i + 1 ?></td>
                        <td align="center" style="font-weight:bold;"><?= htmlspecialchars($subjectCodesArray[$i]) ?></td>
                        <td style="padding-left:3px;"><?= htmlspecialchars($subjectArray[$i]['subject_name']) ?></td>
                        <td align="center"><?= $subjectArray[$i]['theory_full_marks'] ?? 100 ?></td>
                        
                        <!-- Gap Column -->
                        <td style="background:white;"></td>
                        
                        <!-- Second Column -->
                        <?php if ($hasSecondColumn): 
                            $secondIndex = $i + $halfCount;
                        ?>
                            <td align="center"><?= $secondIndex + 1 ?></td>
                            <td align="center" style="font-weight:bold;"><?= htmlspecialchars($subjectCodesArray[$secondIndex]) ?></td>
                            <td style="padding-left:3px;"><?= htmlspecialchars($subjectArray[$secondIndex]['subject_name']) ?></td>
                            <td align="center"><?= $subjectArray[$secondIndex]['theory_full_marks'] ?? 100 ?></td>
                        <?php else: ?>
                            <td colspan="4" style="background:#f8f9fa;"></td>
                        <?php endif; ?>
                    </tr>
                    <?php endfor; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Summary Footer -->
<div class="row mt-3">
    <div class="col-md-12">
        <div class="alert alert-info">
            <strong>Notes:</strong>
            <ul class="mb-0">
                <li>Each student shows only their assigned subjects (up to <?= $maxSubjects ?> subjects)</li>
                <li>Subjects are displayed as Sub1, Sub2, ... Sub<?= $maxSubjects ?></li>
                <li>Red highlighted cells indicate marks below 35% of full marks</li>
                <li>Blank cells indicate no subject assigned for that slot</li>
                <li>Refer to the Subject Code - Name Reference table below for subject details</li>
            </ul>
        </div>
    </div>
</div>

</div>
</body>
</html>