<?php
include('includes/session1.php');
error_reporting(0);
include('includes/dbconnection.php');


require_once('vendor/autoload.php');

use Mpdf\Mpdf;
use Dotenv\Dotenv;

$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

$school_name = $_ENV['SCHOOL_NAME'];
$school_address = $_ENV['SCHOOL_ADDRESS'];


// 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
// Fetch active session ordered by earliest start_date
$currentSessionQuery = "
    SELECT id, session_name 
    FROM school_sessions 
    WHERE start_date <= CURDATE() 
      AND end_date >= CURDATE() 
    ORDER BY start_date ASC 
    LIMIT 1";
$currentSessionResult = $con->query($currentSessionQuery);

if ($currentSessionResult && $currentSessionResult->num_rows > 0) {
    $currentSession = $currentSessionResult->fetch_assoc();
    $currentSessionId = $currentSession['id'];
    $currentSessionName = $currentSession['session_name'];
} else {
    $currentSessionId = null;
    $currentSessionName = null;
}

// Fetch the current term
$termQuery = "SELECT id, term_name FROM school_terms WHERE session_id = '$currentSessionId'
              AND start_date <= CURDATE() 
              AND end_date >= CURDATE() 
              ORDER BY start_date ASC 
              LIMIT 1";
$termResult = mysqli_query($con, $termQuery);
$termRow = mysqli_fetch_assoc($termResult);
$currentTermId = $termRow['id'];
$currentTermName = $termRow['term_name'];

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

// Fetch results for each subject
$results_by_subject = [];
foreach ($subjects as $subject) {
    $subject_name = $subject['subject'];

    // First try fetching end-of-term results
    $end_stmt = $con->prepare("
    SELECT s.studentName, s.studentno, r.test_score, r.test_score2, r.class_performance, r.weekly_assessment, r.exam_score, r.total_score
    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();

    if ($end_results->num_rows > 0) {
        $results_by_subject[$subject_name] = $end_results->fetch_all(MYSQLI_ASSOC);
        $result_type_by_subject[$subject_name] = 'Endterm';
    } else {
        // Fallback to midterm if no end-term result
        $mid_stmt = $con->prepare("
        SELECT s.studentName, s.studentno, r.test_score, r.test_score2, r.class_performance, r.weekly_assessment,
               (r.test_score + r.test_score2 + r.class_performance + r.weekly_assessment) AS total_score
        FROM results r
        JOIN students s ON r.student_id = s.id
        WHERE r.class = ? AND r.section = ? AND r.subject = ? 
        AND r.status_test = 'Approved' 
        AND r.term_id = ? AND r.session_id = ?
    ");
        $mid_stmt->bind_param("sssii", $class, $section, $subject_name, $currentTermId, $currentSessionId);
        $mid_stmt->execute();
        $mid_results = $mid_stmt->get_result();

        $results_by_subject[$subject_name] = $mid_results->fetch_all(MYSQLI_ASSOC);
        $result_type_by_subject[$subject_name] = 'Midterm';
    }
}
?>

<!-- HTML to display the results -->
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Class Result Broadsheet</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        @media print {
            body {
                -webkit-print-color-adjust: exact;
            }
        }
    </style>
</head>

<body>
    <div class="container mt-5">
        <div class='text-center'>
            <img src='company/logo.jpeg' alt='logo' width='70' height='70'><br>
            <h1><?= $school_name ?></h1>
            <b><i><?= $school_address ?></i></b><br>
            <hr>
            <h4 class="text-center">Class Result Broadsheet: <?= htmlspecialchars($class . " - " . $section); ?></h4>
        </div>
        <div class='text-center'>
            <strong>Term:</strong> <?= $currentTermName ?> || <strong>Session:</strong> <?= $currentSessionName ?>
        </div>
    </div>

    <div class="container">
        <?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); ?> - <?= $result_type_by_subject[$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>
                                <?php if ($result_type_by_subject[$subject] === 'Endterm'): ?>                                
                                <th>C.A. Test 1 (10)</th>
                                <th>C.A. Test 2 (10)</th>
                                <?php else: ?>
                                <th>1st C.A. Test (10)</th>
                                <th>2nd C.A. Test (10)</th>
                                <?php endif; ?>
                                    <th>Ex. & Projects (5)</th>
                                    <th>Assignment (5)</th>
                                    <?php if ($result_type_by_subject[$subject] === 'Endterm'): ?>
                                        <th>Exam (70)</th>
                                    <?php endif; ?>
                                    <th>Total (<?= $result_type_by_subject[$subject] === 'Endterm' ? '100' : '30' ?>)</th>

                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($results as $result): ?>
                                    <?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 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'];

                                    // 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;
                                    // 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 ? htmlspecialchars($result['test_score']) : '***' ?></td>
                                        <td><?= $showScores ? htmlspecialchars($result['test_score2']) : '***' ?></td>
                                        <td><?= $showScores ? htmlspecialchars($result['class_performance']) : '***' ?></td>
                                        <td><?= $showScores ? htmlspecialchars($result['weekly_assessment']) : '***' ?></td>
                                        <?php if ($result_type_by_subject[$subject] === 'Endterm'): ?>
                                            <td><?= $showScores ? htmlspecialchars($result['exam_score']) : '***' ?></td>
                                        <?php endif; ?>
                                        <td><?= $showScores ? htmlspecialchars($result['total_score']) : '***' ?></td>

                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    <?php endif; ?>
                </div>
            </div>
        <?php endforeach; ?>
    </div>
    <script>
        window.print();
    </script>
</body>

</html>