<?php
session_name('teacher_session');
session_start();
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'];

$section = isset($_GET['section']) ? filter_var($_GET['section'], FILTER_SANITIZE_STRING) : '';
$class = isset($_GET['class']) ? filter_var($_GET['class'], FILTER_SANITIZE_STRING) : '';

// 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 subjects
$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 per subject
$results_by_subject = [];
foreach ($subjects as $subject) {
    $subject_name = $subject['subject'];

    $result_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 = $currentTermId AND r.session_id = $currentSessionId
    ");
    $result_stmt->bind_param("sss", $class, $section, $subject_name);
    $result_stmt->execute();
    $result_data = $result_stmt->get_result();
    $results_by_subject[$subject_name] = $result_data->fetch_all(MYSQLI_ASSOC);
}

// HTML Content
$html = "
<style>
    table { width: 100%; border-collapse: collapse; font-size: 12px; }
    th, td { padding: 8px; text-align: left; border: 1px solid #ddd; }
    th { background-color: #007bff; color: white; }
    tr:nth-child(even) { background-color: #f9f9f9; }
    .text-center { text-align: center; }
    .card-header { background-color: #007bff; color: white; }
</style>
<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: $class - $section</h4>
</div>
<div class='text-center'>
    <strong>Term:</strong> $currentTermName  || <strong>Session:</strong> $currentSessionName 
</div>
<hr>";
foreach ($results_by_subject as $subject => $results) {
    $html .= "<div class='card mt-3'>
                <div class='card-header'>
                    <h6 class='mb-0'><i class='fas fa-book-open'></i> " . htmlspecialchars($subject) . " - Results</h6>
                </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>Class Ex. & Projects (5)</th>
                            <th>Assignment (5)</th>
                            <th>Exam (70)</th>
                            <th>Total (100)</th>
                        </tr>
                        </thead>
                        <tbody>";
    foreach ($results as $result) {
        $html .= "<tr>
                    <td>" . htmlspecialchars($result['studentno']) . "</td>
                    <td>" . htmlspecialchars($result['studentName']) . "</td>
                    <td>" . htmlspecialchars($result['test_score']) . "</td>
                    <td>" . htmlspecialchars($result['test_score2']) . "</td>
                    <td>" . htmlspecialchars($result['class_performance']) . "</td>
                    <td>" . htmlspecialchars($result['weekly_assessment']) . "</td>
                    <td>" . htmlspecialchars($result['exam_score']) . "</td>
                    <td>" . htmlspecialchars($result['total_score']) . "</td>
                </tr>";
    }
    $html .= "</tbody></table></div></div>";
}


// Generate PDF
$mpdf = new Mpdf();
$mpdf->SetHeader($school_name . '||'.$class .'-'.$section . 'Class Broadsheet');
$mpdf->SetFooter('Generated on ' . date("M d, Y H:i A") . '||Page {PAGENO}');
$mpdf->WriteHTML($html);
$mpdf->Output($class. '_'. $section .'_'.'class_result_broadsheet.pdf', 'D');
?>
