<?php
include('includes/session1.php');
error_reporting(0);
include('includes/dbconnection.php');
require_once __DIR__ . '/includes/session_term_helper.php';
// Fetch current session and term (with gap/fallback support)
$stResult = resolveCurrentSessionTerm($con);
$currentSessionId = $stResult['session_id'];
$currentSessionName = $stResult['session_name'];
$currentTermId = $stResult['term_id'];
$currentTermName = $stResult['term_name'];

if (!isset($_SESSION['sid'])) {
    header('location:logoutteacher.php');
    exit();
}
// Assuming the class teacher is viewing results for a class
$class = $_GET['class'];  // Get the class from URL parameter
$section = $_GET['section'];  // Get the section from URL parameter




// Fetch current session and term


if ($_SERVER["REQUEST_METHOD"] == "POST") {


    $session_id = $_POST['session_id'];  // Selected session
    $term_id = $_POST['term_id'];


    $sessionRow = mysqli_fetch_assoc(mysqli_query($con, "SELECT id, session_name FROM school_sessions WHERE id = '$session_id' ORDER BY id DESC LIMIT 1"));
    $currentSessionId = $sessionRow['id'];
    $currentSessionName = $sessionRow['session_name'];

    $termRow = mysqli_fetch_assoc(mysqli_query($con, "SELECT id, term_name FROM school_terms WHERE id = $term_id AND session_id = '$currentSessionId' ORDER BY id DESC LIMIT 1"));
    $currentTermId = $termRow['id'];
    $currentTermName = $termRow['term_name'];

    // Load student payment data
    $student_payment_data = [];
    $students_query = mysqli_query($con, "SELECT id, studentno, studentName, stream, class, demote_status FROM students WHERE class = '$class' AND section = '$section'");
    while ($student = mysqli_fetch_assoc($students_query)) {
        $student_id = $student['id'];
        $studentNo = $student['studentno'];
        $stream = $student['stream'];
        $student_class = $student['class'];
        $repeat = $studentData['demote_status'];

        $pay_result = mysqli_query($con, "SELECT pay_for FROM payment WHERE student_id = '$student_id' AND class = '$student_class' AND school_type = '$stream' AND session = '$currentSessionName' AND term = '$currentTermName' AND pay_for LIKE 'School Fees%' LIMIT 1");
        $pay_row = mysqli_fetch_assoc($pay_result);
        $pay_for = $pay_row['pay_for'] ?? 'School Fees';

        $student_type = ($pay_for === 'School Fees (New Student)') ? 'New' : 'Old';

        $fee_result = mysqli_query($con, "SELECT amount FROM fee_records WHERE class = '$student_class' AND stream = '$stream' AND name = '$pay_for' AND student_type = '$student_type' AND session = '$currentSessionName' AND term = '$currentTermName' ORDER BY created_at DESC LIMIT 1");
        $fee_row = mysqli_fetch_assoc($fee_result);
        $expected_fee = $fee_row['amount'] ?? 0;

        // Check for JSS 3, SS 1, SS 3 special first term fees
        if ($currentTermName === 'First Term' && in_array($student_class, ['JSS 3', 'SS 1', 'SS 3'])) {
            // Match specific fee names
            $special_fee_names = [
                'JSS 3' => 'First Term JSS 3 School Fees',
                'SS 1' => 'First Term SS 1 School Fees',
                'SS 3' => 'First Term SS 3 School Fees'
            ];

            $special_fee_name = $special_fee_names[$class];

            $feeQuery = "SELECT amount FROM fees 
                                                    WHERE class = ? 
                                                    AND stream = ? 
                                                    AND student_type = 'Old' 
                                                    AND name = ? 
                                                    LIMIT 1";

            $stmt = $con->prepare($feeQuery);
            $stmt->bind_param("sss", $student_class, $stream, $special_fee_name);
            $stmt->execute();
            $feeResult = $stmt->get_result();
            $feeRow = $feeResult->fetch_assoc();
            $expected_fee1 = $feeRow['amount'] ?? $expected_fee; // Fallback to default fee if special fee not found
            $expected_fee = $expected_fee1; // Use the special fee if applicable
        }

        if (trim($student_class) === 'SS 1' && $repeat === 1 && $currentTermName === 'First Term') {

            // Fetch expected fee
            $feeQuery = $con->prepare("SELECT amount FROM fees 
                                                            WHERE name = 'School Fees' 
                                                            AND class = ? AND stream = ? AND student_type = 'Old' 
                                                            LIMIT 1");
            $feeQuery->bind_param("ss", $student_class, $stream);
            $feeQuery->execute();
            $feeResult = $feeQuery->get_result()->fetch_assoc();
            $expected_fee1 = $feeResult['amount'] ?? $expected_fee;
            $expected_fee = $expected_fee1;
        }


        $paid_result = mysqli_query($con, "SELECT SUM(amount) AS total_paid FROM payment WHERE student_id = '$student_id' AND class = '$student_class' AND pay_for = '$pay_for' AND school_type = '$stream' AND session = '$currentSessionName' AND term = '$currentTermName'");
        $paid_row = mysqli_fetch_assoc($paid_result);
        $total_paid = $paid_row['total_paid'] ?? 0;

        $student_payment_data[$studentNo] = ($total_paid >= $expected_fee);
    }



    // Fetch all subjects for this class (that have Endterm results)
    $subject_stmt = $con->prepare("
    SELECT DISTINCT subject 
    FROM results 
    WHERE class = ? AND section = ? 
    AND term_id = ? AND session_id = ? 
    AND status = 'Approved'
");
    $subject_stmt->bind_param("ssii", $class, $section, $currentTermId, $currentSessionId);
    $subject_stmt->execute();
    $subject_result = $subject_stmt->get_result();
    $subjects = $subject_result->fetch_all(MYSQLI_ASSOC);

    // Fetch ONLY endterm results for each subject
    $results_by_subject = [];
    $result_type_by_subject = [];

    foreach ($subjects as $subject) {
        $subject_name = $subject['subject'];

        $end_stmt = $con->prepare("
        SELECT s.studentName, s.studentno, 
               r.test_score, 
               r.class_performance, r.weekly_assessment, 
               r.exam_score, r.total_score,
               r.exam_absent, r.test_absent, r.class_performance_absent, r.weekly_assessment_absent      
             FROM results r
        JOIN students s ON r.student_id = s.id
        WHERE r.class = ? AND r.section = ? AND r.subject = ? 
        AND r.status = 'Approved' 
        AND r.term_id = ? AND r.session_id = ?
    ");
        $end_stmt->bind_param("sssii", $class, $section, $subject_name, $currentTermId, $currentSessionId);
        $end_stmt->execute();
        $end_results = $end_stmt->get_result();

        $results_by_subject[$subject_name] = $end_results->fetch_all(MYSQLI_ASSOC);
        $result_type_by_subject[$subject_name] = 'Endterm';
    }

}
?>

<!DOCTYPE html>
<html>
<?php @include("includes/head.php"); ?>

<body class="hold-transition sidebar-mini layout-fixed">
    <div class="wrapper">
        <?php @include("includes/headert.php"); ?>
        <?php @include("includes/sidebart.php"); ?>



        <div class="content-wrapper">
            <!-- Content Header (Page header) -->
            <section class="content-header premium-header">
                <div class="container-fluid">
                    <div class="header-glass-card">
                        <div class="row align-items-center">
                            <div class="col-md-8">
                                <div class="title-section">
                                    <div class="icon-box bg-primary-gradient shadow-sm">
                                        <i class="fas fa-clipboard-list"></i>
                                    </div>
                                    <div>
                                        <h1 class="premium-title text-primary">
                                            Result Management
                                            <span class="premium-subtitle text-secondary">Score broadsheet -
                                                <?= $class ?> - <?= ($section) ?></span>
                                        </h1>
                                        <div class="mt-1">
                                            <?php if ($currentSessionName): ?>
                                                <span class="badge badge-primary mr-1">
                                                    <i class="fas fa-calendar-alt"></i>
                                                    <?= htmlspecialchars($currentSessionName) ?>
                                                </span>
                                            <?php endif; ?>
                                            <?php if ($currentTermName): ?>
                                                <span class="badge badge-success">
                                                    <i class="fas fa-bookmark"></i>
                                                    <?= htmlspecialchars($currentTermName) ?>
                                                </span>
                                            <?php else: ?>
                                                <span class="badge badge-warning">
                                                    <i class="fas fa-exclamation-triangle"></i> No active term
                                                </span>
                                            <?php endif; ?>
                                        </div>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <nav aria-label="breadcrumb">
                                    <ol class="breadcrumb premium-breadcrumb">
                                        <li class="breadcrumb-item"><a href="teacher_dashboard.php"
                                                class="text-primary">Home</a></li>
                                        <li class="breadcrumb-item active" aria-current="page">Broadsheet</li>
                                    </ol>
                                </nav>
                            </div>
                        </div>
                    </div>
                </div>
            </section>
            <section class="content">
                <div class="container-fluid">
                    <div class="card shadow-sm">
                        <div class="card-header bg-primary text-white">
                            <h5 class="card-title font-weight-bold">
                                <i class="fas fa-file-alt"></i> Check Previous Term and Session Broadsheet
                            </h5>

                        </div>
                        <div class="card-body">

                            <!-- Result Filter Form -->
                            <form method="POST" class="mb-4" action="">
                                <div class="row">
                                    <div class="form-group col-md-6">
                                        <label for="session_id">Academic Session</label>
                                        <select name="session_id" id="session_id" class="form-control select2" required>
                                            <option value="">-- Select Session --</option>
                                            <?php
                                            $query = "SELECT id, session_name FROM school_sessions ORDER BY session_name DESC";
                                            $stmt1 = $con->prepare($query);
                                            $stmt1->execute();
                                            $result = $stmt1->get_result();
                                            while ($row1 = $result->fetch_assoc()) {
                                                echo "<option value='{$row1['id']}'>{$row1['session_name']}</option>";
                                            }
                                            ?>
                                        </select>
                                    </div>

                                    <div class="form-group col-md-6">
                                        <label for="term_id">Term</label>
                                        <select name="term_id" id="term_id" class="form-control select2" required>
                                            <option value="">-- Select Term --</option>
                                        </select>
                                    </div>

                                </div>
                                <div class="form-group col-md-12 text-center">
                                    <button type="submit" class="btn btn-primary mt-3"><i
                                            class="fas fa-check-circle"></i> View Result</button>
                                </div>
                            </form>
                            <!-- End of Result Filter Form -->
                        </div>
                    </div>
                </div>
            </section>
            <?php if ($_SERVER["REQUEST_METHOD"] == "POST"): ?>


                <section class="content">
                    <div class="container-fluid">
                        <div class="card border-primary">
                            <div class="card-header bg-primary text-white">
                                <h5 class="mb-0"><i class="fas fa-chalkboard"></i> <?= $class ?> - <?= ($section) ?> Class
                                    Result Broadsheet </h5>
                            </div>
                            <?php if (empty($subjects)): ?>
                                <div class="alert alert-warning m-3">
                                    <strong>No results found!</strong> There are currently no subjects or results available for
                                    <?= $class ?> - <?= ($section) ?> in <?= $currentSessionName ?>, <?= $currentTermName ?>.
                                </div>
                            <?php else: ?>
                                <?php foreach ($results_by_subject as $subject => $results): ?>
                                    <div class="card mt-3">
                                        <div class="card-header font-weight-bold bg-primary text-white">
                                            <h6 class="mb-0"><i class="fas fa-book-open"></i> <?= htmlspecialchars($subject); ?> -
                                                Results</h6>
                                        </div>
                                        <div class="card-body">
                                            <?php if (empty($results)): ?>
                                                <div class="alert alert-info">
                                                    No results have been submitted for <?= htmlspecialchars($subject); ?> yet.
                                                </div>
                                            <?php else: ?>
                                                <table class="table table-bordered table-hover example11">
                                                    <thead>
                                                        <tr>
                                                            <th>Admission No.</th>
                                                            <th>Student Name</th>
                                                            <th>C.A Test (10)</th>
                                                            <th>Ex. & Projects (15)</th>
                                                            <th>Assignment (5)</th>
                                                            <th>Exam (70)</th>
                                                            <th>Total (100)</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php foreach ($results as $result): ?>
                                                            <?php
                                                            // Determine each score display
                                                            $test_score = ($result['test_absent'] == "1") ? 'AB' : (int) $result['test_score'];
                                                            $class_performance = ($result['class_performance_absent'] == "1") ? 'AB' : (int) $result['class_performance'];
                                                            $weekly_assessment = ($result['weekly_assessment_absent'] == "1") ? 'AB' : (int) $result['weekly_assessment'];
                                                            $exam = ($result['exam_absent'] == "1") ? 'AB' : (int) $result['exam_score'];
                                                            // Collect scores for numeric sum
                                                            $scores = [$test_score, $class_performance, $weekly_assessment, $exam];
                                                            $numeric_scores = array_filter($scores, 'is_numeric');

                                                            // Calculate total or show AB if all absent
                                                            $total = count($numeric_scores) ? array_sum($numeric_scores) : 'AB';

                                                            ?>
                                                            <?php
                                                            $studentName = htmlspecialchars($result['studentName']);
                                                            $studentNo = htmlspecialchars($result['studentno']);

                                                            // Step 1: Get student ID, stream, class
                                                            $student_data_stmt = $con->prepare("SELECT id, stream, class, demote_status FROM students WHERE studentno = ?");
                                                            $student_data_stmt->bind_param("s", $studentNo);
                                                            $student_data_stmt->execute();
                                                            $student_data_result = $student_data_stmt->get_result();
                                                            $student_data = $student_data_result->fetch_assoc();

                                                            $student_id = $student_data['id'];
                                                            $stream = $student_data['stream'];
                                                            $student_class = $student_data['class'];
                                                            $repeat = $studentData['demote_status'];
                                                            // Step 2: Determine student type
                                                            $payQuery = "SELECT pay_for FROM payment 
                                    WHERE student_id = ? AND class = ? AND school_type = ? 
                                    AND session = ? AND term = ? AND pay_for LIKE 'School Fees%' 
                                    LIMIT 1";
                                                            $payStmt = $con->prepare($payQuery);
                                                            $payStmt->bind_param("issss", $student_id, $student_class, $stream, $currentSessionName, $currentTermName);
                                                            $payStmt->execute();
                                                            $payResult = $payStmt->get_result()->fetch_assoc();
                                                            $pay_for = $payResult['pay_for'] ?? 'School Fees';

                                                            $student_type = ($pay_for === 'School Fees (New Student)') ? "New" : "Old";

                                                            // Step 3: Get expected fee from `fee_records`
                                                            $feeQuery = "SELECT amount FROM fee_records 
                                    WHERE class = ? AND stream = ? AND name = ? AND student_type = ? 
                                    AND session = ? AND term = ? 
                                    ORDER BY created_at DESC 
                                    LIMIT 1";
                                                            $feeStmt = $con->prepare($feeQuery);
                                                            $feeStmt->bind_param("ssssss", $student_class, $stream, $pay_for, $student_type, $currentSessionName, $currentTermName);
                                                            $feeStmt->execute();
                                                            $feeResult = $feeStmt->get_result()->fetch_assoc();
                                                            $expected_fee = $feeResult['amount'] ?? 0;

                                                            if (trim($student_class) === 'SS 1' && $repeat === 1 && $currentTermName === 'First Term') {

                                                                // Fetch expected fee
                                                                $feeQuery = $con->prepare("SELECT amount FROM fees 
                                                            WHERE name = 'School Fees' 
                                                            AND class = ? AND stream = ? AND student_type = 'Old' 
                                                            LIMIT 1");
                                                                $feeQuery->bind_param("ss", $student_class, $stream);
                                                                $feeQuery->execute();
                                                                $feeResult = $feeQuery->get_result()->fetch_assoc();
                                                                $expected_fee1 = $feeResult['amount'] ?? $expected_fee;
                                                                $expected_fee = $expected_fee1;
                                                            }

                                                            // Check for JSS 3, SS 1, SS 3 special first term fees
                                                            if ($currentTermName === 'First Term' && in_array($student_class, ['JSS 3', 'SS 1', 'SS 3'])) {
                                                                // Match specific fee names
                                                                $special_fee_names = [
                                                                    'JSS 3' => 'First Term JSS 3 School Fees',
                                                                    'SS 1' => 'First Term SS 1 School Fees',
                                                                    'SS 3' => 'First Term SS 3 School Fees'
                                                                ];

                                                                $special_fee_name = $special_fee_names[$class];

                                                                $feeQuery = "SELECT amount FROM fees 
                                                    WHERE class = ? 
                                                    AND stream = ? 
                                                    AND student_type = 'Old' 
                                                    AND name = ? 
                                                    LIMIT 1";

                                                                $stmt = $con->prepare($feeQuery);
                                                                $stmt->bind_param("sss", $student_class, $stream, $special_fee_name);
                                                                $stmt->execute();
                                                                $feeResult = $stmt->get_result();
                                                                $feeRow = $feeResult->fetch_assoc();
                                                                $expected_fee1 = $feeRow['amount'] ?? $expected_fee; // Fallback to default fee if special fee not found
                                                                $expected_fee = $expected_fee1; // Use the special fee if applicable
                                                            }


                                                            // Step 4: Sum total paid
                                                            $paidQuery = "SELECT SUM(amount) AS total_paid FROM payment 
                                    WHERE student_id = ? AND class = ? AND pay_for = ? 
                                    AND school_type = ? AND session = ? AND term = ?";
                                                            $paidStmt = $con->prepare($paidQuery);
                                                            $paidStmt->bind_param("isssss", $student_id, $student_class, $pay_for, $stream, $currentSessionName, $currentTermName);
                                                            $paidStmt->execute();
                                                            $paidResult = $paidStmt->get_result()->fetch_assoc();
                                                            $total_paid = $paidResult['total_paid'] ?? 0;

                                                            // Step 5: Check if student paid in full
                                                            $showScores = ($total_paid >= $expected_fee);
                                                            ?>
                                                            <tr>
                                                                <td><?= $studentNo ?></td>
                                                                <td><?= $studentName ?></td>
                                                                <td><?= $showScores ? $test_score : '***' ?></td>
                                                                <td><?= $showScores ? $class_performance : '***' ?></td>
                                                                <td><?= $showScores ? $weekly_assessment : '***' ?></td>
                                                                <td><?= $showScores ? $exam : '***' ?></td>
                                                                <td><?= $showScores ? $total : '***' ?></td>
                                                            </tr>
                                                        <?php endforeach; ?>
                                                    </tbody>
                                                </table>
                                            <?php endif; ?>
                                        </div>
                                    </div>
                                <?php endforeach; ?>
                            <?php endif; ?>

                            <!-- <div class="card-footer text-center">
                                <button type="button" class="btn btn-primary" onclick="PrintFunction()" name="submit" target="_blank"><i class="fas fa-print"></i>
                                    Print
                                </button>
                                <button type="button" class="btn btn-danger approve" onclick="downloadFilteredPDF()" name="submit"><i class="fas fa-file-pdf"></i>
                                    Download PDF
                                </button>
                            </div> -->
                        </div>

                    </div>
                    <!-- /.container-fluid -->
                </section>
            <?php else: ?>
                <section class="content">
                    <div class="container-fluid">
                        <div class="alert alert-info mt-5">
                            <p class="mb-0">Please select a <strong>Term</strong> and <strong>Session</strong> to view the
                                result.</p>
                        </div>
                    </div>
                </section>
            <?php endif; ?>


        </div>
        <!-- /.content-wrapper -->
        <?php @include("includes/footer.php"); ?>
        <?php @include("includes/foot.php"); ?>
    </div>
    <!-- ./wrapper -->
    <script>
        $(document).ready(function () {
            $('.select2').select2();
            $('#session_id').change(function () {
                var selectedClass = $(this).val();
                if (selectedClass) {
                    $.ajax({
                        url: 'fetch_session.php',
                        type: 'POST',
                        data: {
                            class_name: selectedClass
                        },
                        success: function (response) {
                            $('#term_id').html(response);
                        }
                    });
                } else {
                    $('#term_id').html('<option value="">Select Section</option>');
                }
            });
        });
    </script>
    <script>
        $(document).ready(function () {
            $('.example11').DataTable({
                // Options for DataTables (optional)
                "paging": true,
                "searching": true,
                "ordering": true,
                "info": true,
                "responsive": true,
                "autoWidth": false
            });
        });



        function downloadFilteredPDF() {
            var classs = "<?= $class ?>";
            var section = "<?= $section ?>";
            var session = "<?= $session_id ?>";
            var term = "<?= $term_id ?>";
            var tabid = "<?= $tabSessionID ?>";


            // Check if start and end dates are provided
            if (classs && section) {
                var url = "generate_broadsheet_pdf1.php?class=" + classs + "&section=" + section + "&tab_id=" + tabid + "&session=" + session + "&term=" + term;


                // Redirect to generate PDF
                window.location.href = url;
            } else {
                alert("Class and Section empty.");
            }
        }

        function PrintFunction() {
            var classs = "<?= $class ?>";
            var section = "<?= $section ?>";
            var tabid = "<?= $tabSessionID ?>";
            var session = "<?= $session_id ?>";
            var term = "<?= $term_id ?>";


            // Check if start and end dates are provided
            if (classs && section) {
                var url = "print_broadsheet_pdf1.php?class=" + classs + "&section=" + section + "&tab_id=" + tabid + "&session=" + session + "&term=" + term;


                // Redirect to generate PDF
                window.location.href = url;
            } else {
                alert("Class and Section empty.");
            }
        }
    </script>

</body>

</html>