<?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 subject teacher is viewing results for a specific subject
$subject = $_GET['subject'];  // Get the subject from URL parameter
$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'];




// First, try to fetch End-of-Term results
$queryEndTerm = "
    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 = ?
";
$stmt = $con->prepare($queryEndTerm);
$stmt->bind_param("sssii", $class, $section, $subject, $currentTermId, $currentSessionId);
$stmt->execute();
$result_data = $stmt->get_result();

$result_type = 'Endterm';

if ($result_data->num_rows === 0) {
    // No End-of-Term results, fallback to Midterm
    $queryMidTerm = "
        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 = ?
    ";
    $stmt = $con->prepare($queryMidTerm);
    $stmt->bind_param("sssii", $class, $section, $subject, $currentTermId, $currentSessionId);
    $stmt->execute();
    $result_data = $stmt->get_result();

    $result_type = '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>Score Sheet</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'><?= $subject ?> Scoresheet: <?= $class ?> - <?= $section ?></h4>
        </div>
        <div class='text-center'>
            <strong>Term:</strong> <?= $currentTermName ?> || <strong>Session:</strong> <?= $currentSessionName ?>
        </div>
    </div>

    <div class="container">
        <div class="card mt-3">
            <div class="card-header bg-primary text-white">
                <h5><?= htmlspecialchars($subject); ?> - Results</h5>
            </div>
            <div class="card-body">
                <table class="table table-bordered table-hover">
                    <thead>
                        <tr>
                            <th>Admission No.</th>
                            <th>Student Name</th>
                            <th>1st Test (10)</th>
                            <th>2nd Test (10)</th>
                            <th>Ex. & Projects (5)</th>
                            <th>Assignment (5)</th>
                            <?php if ($result_type === 'Endterm'): ?>
                                <th>Exam (70)</th>
                            <?php endif; ?>
                            <th>Total (<?= $result_type === 'Endterm' ? '100' : '30' ?>)</th>

                        </tr>
                    </thead>
                    <tbody>
                        <?php while ($row = $result_data->fetch_assoc()): ?>
                            <?php
                            $studentNo = $row['studentno'];
                            $studentName = $row['studentName'];

                            // Fetch student details
                            $studentDataQuery = $con->prepare("SELECT id, stream, class FROM students WHERE studentno = ?");
                            $studentDataQuery->bind_param("s", $studentNo);
                            $studentDataQuery->execute();
                            $studentDataResult = $studentDataQuery->get_result();
                            $studentData = $studentDataResult->fetch_assoc();

                            $student_id = $studentData['id'];
                            $stream = $studentData['stream'];
                            $student_class = $studentData['class'];

                            // Check if student paid - Determine student type
                            $payQuery = $con->prepare("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");
                            $payQuery->bind_param("issss", $student_id, $student_class, $stream, $currentSessionName, $currentTermName);
                            $payQuery->execute();
                            $payResult = $payQuery->get_result()->fetch_assoc();
                            $pay_for = $payResult['pay_for'] ?? 'School Fees';
                            $student_type = ($pay_for === 'School Fees (New Student)') ? 'New' : 'Old';

                            // Fetch expected fee
                            $feeQuery = $con->prepare("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");
                            $feeQuery->bind_param("ssssss", $student_class, $stream, $pay_for, $student_type, $currentSessionName, $currentTermName);
                            $feeQuery->execute();
                            $feeResult = $feeQuery->get_result()->fetch_assoc();
                            $expected_fee = $feeResult['amount'] ?? 0;

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

                            // Check if fully paid
                            $showScores = ($total_paid >= $expected_fee);
                            ?>
                            <tr>
                                <td><?= htmlspecialchars($studentNo); ?></td>
                                <td><?= htmlspecialchars($studentName); ?></td>
                                <td><?= $showScores ? htmlspecialchars($row['test_score']) : '***' ?></td>
                                <td><?= $showScores ? htmlspecialchars($row['test_score2']) : '***' ?></td>
                                <td><?= $showScores ? htmlspecialchars($row['class_performance']) : '***' ?></td>
                                <td><?= $showScores ? htmlspecialchars($row['weekly_assessment']) : '***' ?></td>
                                <?php if ($result_type === 'Endterm'): ?>
                                    <td><?= $showScores ? htmlspecialchars($row['exam_score']) : '***' ?></td>
                                <?php endif; ?>
                                <td><?= $showScores ? htmlspecialchars($row['total_score']) : '***' ?></td>

                            </tr>
                        <?php endwhile; ?>
                    </tbody>

                </table>
            </div>
        </div>
    </div>
    <script>
        window.print();
    </script>
</body>

</html>