<?php
declare(strict_types=1);

require_once __DIR__ . '/../library/auth.php';
require_once __DIR__ . '/helpers/result-helper.php';

allow_roles(['admin', 'teacher']);
requirePostRequest();
verifyCsrfToken();

$school_id = (int) $_SESSION['school_id'];
$session_id = (int) ($_SESSION['session_id'] ?? 0);
$page_title = 'Student Final Marksheet';

$student_id = validateId($_POST['student_id'] ?? 0);
$class_id = validateId($_POST['class_id'] ?? 0);

if ($student_id <= 0 || $class_id <= 0) {
    header('Location: result-view.php');
    exit;
}

/*
|--------------------------------------------------------------------------
| 1. FETCH SCHOOL SETTINGS & DETAILS
|--------------------------------------------------------------------------
*/
$schoolStmt = $pdo->prepare("SELECT * FROM school_settings WHERE school_id = ? LIMIT 1");
$schoolStmt->execute([$school_id]);
$school = $schoolStmt->fetch(PDO::FETCH_ASSOC);

/*
|--------------------------------------------------------------------------
| 2. FETCH STUDENT BASIC & ACADEMIC PROFILE
|--------------------------------------------------------------------------
*/
$studentStmt = $pdo->prepare("
    SELECT 
        s.*, 
        sp.father_name, 
        sp.mother_name, 
        c.class_name, 
        ss.roll_no,
        ss.id as student_session_id
    FROM students s
    INNER JOIN student_sessions ss ON s.id = ss.student_id
    INNER JOIN classes c ON ss.class_id = c.id
    LEFT JOIN student_parents sp ON s.id = sp.student_id
    WHERE s.id = ? AND ss.class_id = ? AND ss.session_id = ?
    LIMIT 1
");
$studentStmt->execute([$student_id, $class_id, $session_id]);
$student = $studentStmt->fetch(PDO::FETCH_ASSOC);

if (!$student) {
    die("Student record not found for the current session/class.");
}

/*
|--------------------------------------------------------------------------
| 3. FETCH ATTENDANCE SUMMARY (TERM-1 & TERM-2 BREAKDOWN)
|--------------------------------------------------------------------------
| Note: Dates are examples. You can adjust the date range based on your terms.
*/
$attendanceStmt = $pdo->prepare("
    SELECT 
        SUM(CASE WHEN attendance_date <= '2026-10-31' THEN 1 ELSE 0 END) as t1_total_days,
        SUM(CASE WHEN attendance_date <= '2026-10-31' AND status IN ('Present', 'Late') THEN 1 ELSE 0 END) as t1_present_days,
        SUM(CASE WHEN attendance_date > '2026-10-31' THEN 1 ELSE 0 END) as t2_total_days,
        SUM(CASE WHEN attendance_date > '2026-10-31' AND status IN ('Present', 'Late') THEN 1 ELSE 0 END) as t2_present_days
    FROM attendance 
    WHERE student_session_id = ? AND school_id = ? AND session_id = ?
");
$attendanceStmt->execute([$student['student_session_id'], $school_id, $session_id]);
$attendance = $attendanceStmt->fetch(PDO::FETCH_ASSOC);

/*
|--------------------------------------------------------------------------
| 4. FETCH SCHOLASTIC AND CO-SCHOLASTIC MARKS (PIVOT LOGIC)
|--------------------------------------------------------------------------
*/
$marksStmt = $pdo->prepare("
    SELECT 
        sub.name AS subject_name,
        sub.type AS subject_type,
        
        -- TERM 1 COMPONENTS
        SUM(CASE WHEN et.name = 'Periodic Test' AND (ex.term = 'Term-1' OR ex.exam_name LIKE '%T1%') THEN em.marks_obtained ELSE 0 END) AS t1_pt,
        SUM(CASE WHEN sub.name = 'Notebook' AND (ex.term = 'Term-1' OR ex.exam_name LIKE '%T1%') THEN em.marks_obtained ELSE 0 END) AS t1_nb,
        SUM(CASE WHEN sub.name = 'Subject Enrichment' AND (ex.term = 'Term-1' OR ex.exam_name LIKE '%T1%') THEN em.marks_obtained ELSE 0 END) AS t1_se,
        SUM(CASE WHEN et.name = 'SA1' THEN em.marks_obtained ELSE 0 END) AS t1_sa,

        -- TERM 2 COMPONENTS
        SUM(CASE WHEN et.name = 'Periodic Test' AND (ex.term = 'Term-2' OR ex.exam_name LIKE '%T2%') THEN em.marks_obtained ELSE 0 END) AS t2_pt,
        SUM(CASE WHEN sub.name = 'Notebook' AND (ex.term = 'Term-2' OR ex.exam_name LIKE '%T2%') THEN em.marks_obtained ELSE 0 END) AS t2_nb,
        SUM(CASE WHEN sub.name = 'Subject Enrichment' AND (ex.term = 'Term-2' OR ex.exam_name LIKE '%T2%') THEN em.marks_obtained ELSE 0 END) AS t2_se,
        SUM(CASE WHEN et.name = 'SA2' THEN em.marks_obtained ELSE 0 END) AS t2_sa

    FROM exam_marks em
    INNER JOIN exams ex ON em.exam_id = ex.id
    INNER JOIN exam_types et ON ex.exam_type_id = et.id
    INNER JOIN subjects sub ON em.subject_id = sub.id
    WHERE em.student_id = ? AND em.class_id = ? AND em.school_id = ? AND em.session_id = ?
    GROUP BY sub.id, sub.name, sub.type
    ORDER BY sub.type ASC, sub.name ASC
");
$marksStmt->execute([$student_id, $class_id, $school_id, $session_id]);
$all_marks = $marksStmt->fetchAll(PDO::FETCH_ASSOC);

// Separate Scholastic vs Co-Scholastic based on user definition or subject type
$scholastic_subjects = [];
$co_scholastic_subjects = [];

foreach ($all_marks as $marks) {
    if (strtolower($marks['subject_type']) === 'co-scholastic' || strtolower($marks['subject_name']) === 'discipline') {
        $co_scholastic_subjects[] = $marks;
    } else {
        $scholastic_subjects[] = $marks;
    }
}

/*
|--------------------------------------------------------------------------
| HELPER FUNCTION TO CALCULATE SCHOLASTIC GRADES (8-POINT SCALE)
|--------------------------------------------------------------------------
*/
function calculateScholasticGrade($marks) {
    if ($marks >= 91) return 'A1';
    if ($marks >= 81) return 'A2';
    if ($marks >= 71) return 'B1';
    if ($marks >= 61) return 'B2';
    if ($marks >= 51) return 'C1';
    if ($marks >= 41) return 'C2';
    if ($marks >= 33) return 'D';
    return 'E (Needs Improvement)';
}

/*
|--------------------------------------------------------------------------
| HELPER FUNCTION FOR CO-SCHOLASTIC GRADES (3-POINT SCALE A-C)
|--------------------------------------------------------------------------
*/
function calculateCoScholasticGrade($marks) {
    if ($marks >= 7) return 'A';
    if ($marks >= 4) return 'B';
    return 'C';
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title><?= htmlspecialchars($page_title); ?></title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 0;
            padding: 20px;
            color: #333;
            background-color: #fff;
        }
        .marksheet-container {
            width: 210mm;
            margin: 0 auto;
            border: 3px double #000;
            padding: 20px;
            box-sizing: border-box;
        }
        .header-table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        .header-table td {
            border: none;
        }
        .school-name {
            font-size: 24px;
            font-weight: bold;
            text-align: center;
            text-transform: uppercase;
        }
        .school-details {
            text-align: center;
            font-size: 14px;
        }
        .report-title {
            text-align: center;
            font-size: 18px;
            font-weight: bold;
            margin: 15px 0;
            text-decoration: underline;
        }
        .student-info-table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 25px;
        }
        .student-info-table td {
            padding: 6px;
            font-size: 14px;
            border: none;
        }
        .student-info-table td .label {
            font-weight: bold;
        }
        .main-data-table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
            font-size: 13px;
        }
        .main-data-table th, .main-data-table td {
            border: 1px solid #000;
            text-align: center;
            padding: 6px;
        }
        .main-data-table th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        .subject-name-col {
            text-align: left !important;
            font-weight: bold;
            padding-left: 10px !important;
        }
        .co-scholastic-section {
            margin-top: 25px;
        }
        .footer-signatures {
            margin-top: 60px;
            display: flex;
            justify-content: space-between;
        }
        .signature-block {
            text-align: center;
            width: 200px;
            border-top: 1px dashed #000;
            padding-top: 5px;
            font-weight: bold;
            font-size: 14px;
        }
        @media print {
            body { padding: 0; }
            .marksheet-container { border: 2px solid #000; }
            .main-data-table th { background-color: #f2f2f2 !important; -webkit-print-color-adjust: exact; }
        }
    </style>
</head>
<body>

<div class="marksheet-container">
    
    <table class="header-table">
        <tr>
            <td style="width: 15%; text-align: center;">
                <?php if (!empty($school['logo'])): ?>
                    <img src="uploads/logo/<?= htmlspecialchars($school['logo']); ?>" alt="Logo" style="max-height: 80px;">
                <?php endif; ?>
            </td>
            <td style="width: 85%;">
                <div class="school-name"><?= htmlspecialchars($school['school_name'] ?? 'YOUR SCHOOL NAME'); ?></div>
                <div class="school-details">
                    <?= htmlspecialchars($school['address'] ?? 'School Address Line'); ?><br>
                    Phone: <?= htmlspecialchars($school['phone'] ?? 'Contact No'); ?> | Email: <?= htmlspecialchars($school['email'] ?? 'Email'); ?>
                </div>
            </td>
        </tr>
    </table>

    <div class="report-title">ACADEMIC PERFORMANCE REPORT CARD</div>

    <table class="student-info-table">
        <tr>
            <td style="width: 50%;"><span class="label">Roll No:</span> <?= htmlspecialchars((string)($student['roll_no'] ?? '')); ?></td>
            <td style="width: 50%;"><span class="label">Class & Section:</span> <?= htmlspecialchars($student['class_name'] ?? ''); ?></td>
        </tr>
        <tr>
            <td><span class="label">Student Name:</span> <?= htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
            <td><span class="label">Admission No:</span> <?= htmlspecialchars((string)($student['admission_no'] ?? '')); ?></td>
        </tr>
        <tr>
            <td><span class="label">Father's Name:</span> <?= htmlspecialchars($student['father_name'] ?? ''); ?></td>
            <td><span class="label">Mother's Name:</span> <?= htmlspecialchars($student['mother_name'] ?? ''); ?></td>
        </tr>
    </table>

    <table class="main-data-table">
        <thead>
            <tr>
                <th rowspan="2" style="width: 25%;">Scholastic Areas (Subjects)</th>
                <th colspan="5">TERM - 1 (100 Marks)</th>
                <th colspan="5">TERM - 2 (100 Marks)</th>
            </tr>
            <tr>
                <th style="width: 7%;">PT<br>(10)</th>
                <th style="width: 7%;">NB<br>(5)</th>
                <th style="width: 7%;">SE<br>(5)</th>
                <th style="width: 7%;">HY<br>(80)</th>
                <th style="width: 8%;">Total<br>(100)</th>
                <th style="width: 7%;">PT<br>(10)</th>
                <th style="width: 7%;">NB<br>(5)</th>
                <th style="width: 7%;">SE<br>(5)</th>
                <th style="width: 7%;">Exam<br>(80)</th>
                <th style="width: 8%;">Total<br>(100)</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($scholastic_subjects as $row): 
                $t1_total = $row['t1_pt'] + $row['t1_nb'] + $row['t1_se'] + $row['t1_sa'];
                $t2_total = $row['t2_pt'] + $row['t2_nb'] + $row['t2_se'] + $row['t2_sa'];
            ?>
                <tr>
                    <td class="subject-name-col"><?= htmlspecialchars($row['subject_name']); ?></td>
                    <td><?= $row['t1_pt'] > 0 ? $row['t1_pt'] : '0'; ?></td>
                    <td><?= $row['t1_nb'] > 0 ? $row['t1_nb'] : '0'; ?></td>
                    <td><?= $row['t1_se'] > 0 ? $row['t1_se'] : '0'; ?></td>
                    <td><?= $row['t1_sa'] > 0 ? $row['t1_sa'] : '0'; ?></td>
                    <td><strong><?= $t1_total; ?></strong></td>
                    
                    <td><?= $row['t2_pt'] > 0 ? $row['t2_pt'] : '0'; ?></td>
                    <td><?= $row['t2_nb'] > 0 ? $row['t2_nb'] : '0'; ?></td>
                    <td><?= $row['t2_se'] > 0 ? $row['t2_se'] : '0'; ?></td>
                    <td><?= $row['t2_sa'] > 0 ? $row['t2_sa'] : '0'; ?></td>
                    <td><strong><?= $t2_total; ?></strong></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

    <div class="co-scholastic-section">
        <table class="main-data-table">
            <thead>
                <tr>
                    <th style="text-align: left; padding-left: 10px; width: 50%;">Co-Scholastic Activities / Discipline (Scale A-C)</th>
                    <th style="width: 25%;">Term-1 Grade</th>
                    <th style="width: 25%;">Term-2 Grade</th>
                </tr>
            </thead>
            <tbody>
                <?php if (!empty($co_scholastic_subjects)): ?>
                    <?php foreach ($co_scholastic_subjects as $co_row): ?>
                        <tr>
                            <td class="subject-name-col"><?= htmlspecialchars($co_row['subject_name']); ?></td>
                            <td><?= calculateCoScholasticGrade((float)$co_row['t1_sa']); ?></td>
                            <td><?= calculateCoScholasticGrade((float)$co_row['t2_sa']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td class="subject-name-col">Work Education (or Pre-Vocational Education)</td>
                        <td>A</td>
                        <td>A</td>
                    </tr>
                    <tr>
                        <td class="subject-name-col">Art Education</td>
                        <td>B</td>
                        <td>A</td>
                    </tr>
                    <tr>
                        <td class="subject-name-col">Health & Physical Education</td>
                        <td>A</td>
                        <td>B</td>
                    </tr>
                    <tr>
                        <td class="subject-name-col">Discipline (Attendance / Sincerity / Values)</td>
                        <td>A</td>
                        <td>A</td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>

    <table class="main-data-table" style="margin-top: 20px;">
        <tr>
            <td style="text-align: left; font-weight: bold; width: 25%;">Attendance:</td>
            <td style="width: 35%;">Term-1: <?= ($attendance['t1_present_days'] ?? 0) . ' / ' . ($attendance['t1_total_days'] ?? 0); ?></td>
            <td style="width: 40%;">Term-2: <?= ($attendance['t2_present_days'] ?? 0) . ' / ' . ($attendance['t2_total_days'] ?? 0); ?></td>
        </tr>
        <tr>
            <td style="text-align: left; font-weight: bold;">General Remarks:</td>
            <td colspan="2" style="text-align: left; padding-left: 10px;">Promoted to the next higher class. Very good performance.</td>
        </tr>
    </table>

    <div class="footer-signatures">
        <div class="signature-block">Class Teacher Signature</div>
        <div class="signature-block">Principal Signature</div>
        <div class="signature-block">Parent Signature</div>
    </div>

</div>

</body>
</html>