<?php
session_start();
error_reporting(0);
require_once('TCPDF-main/tcpdf.php');
include 'includes/database.php';
include 'includes/functions.php';

$conn = new Functions();
$redirect = $conn->base_url();

if (isset($_GET['class']) AND isset($_GET['term']) AND isset($_GET['section']) AND isset($_GET['session'])) {

    $class = str_replace('-', ' ', $_GET['class']);
    $term = str_replace('-', ' ', $_GET['term']);
    $section = str_replace('-', ' ', $_GET['section']);
    $session = str_replace('-', '/', $_GET['session']);

    // Get all students with results for this class/term/section
    $sql = "SELECT DISTINCT sr.student_name, sr.reg_no
            FROM student_results sr
            WHERE sr.class = :class 
            AND sr.term = :term 
            AND sr.section = :section 
            AND sr.current_school_session = :session
            AND sr.total_score > 0
            ORDER BY sr.student_name ASC";

    $conn->query($sql);
    $conn->bind(':class', $class);
    $conn->bind(':term', $term);
    $conn->bind(':section', $section);
    $conn->bind(':session', $session);
    $students = $conn->fetchMultiple();

    if (empty($students)) {
        ?>
        <script>
            alert('No students found with results for the selected criteria.');
        </script>
        <meta http-equiv="refresh" content="0; <?php echo $redirect; ?>">
        <?php
        exit;
    }

    // Get all unique subjects for this class/term/section
    $sql = "SELECT DISTINCT subject
            FROM student_results
            WHERE class = :class 
            AND term = :term 
            AND section = :section 
            AND current_school_session = :session
            AND total_score > 0
            ORDER BY subject ASC";

    $conn->query($sql);
    $conn->bind(':class', $class);
    $conn->bind(':term', $term);
    $conn->bind(':section', $section);
    $conn->bind(':session', $session);
    $subjects = $conn->fetchMultiple();

    // Create custom PDF class for broadsheet
    class BroadsheetPDF extends TCPDF {
        public $class_name;
        public $term_name;
        public $section_name;
        public $session_name;

        public function Header() {
            $conn = new Functions();

            // Fetch school information
            $sql = "SELECT * FROM general_settings";
            $conn->query($sql);
            $result_set = $conn->fetchSingle();
            $school_logo = $result_set->logo;
            $school_name = $result_set->school_name;
            $school_tagline = $result_set->school_tagline;
            $school_website = $result_set->school_website;

            // Only show header on first page
            if ($this->getPage() == 1) {
                // Position logo above school name (centered)
                $imageFile = "upload/$school_logo";
                $this->Image($imageFile, 140, 5, 20, '', 'PNG', '', 'T', false, 300, 'C', false, false, 0, false, false, false);

                $this->SetFont('helvetica', 'B', 18);
                $this->SetY(30); // Adjust Y position to below logo
                $this->Cell(0, 5, $school_name, 0, 1, "C");

                $this->SetFont('helvetica', '', 10);
                $this->Cell(0, 4, $school_tagline, 0, 1, "C");

                if (!empty($school_website)) {
                    $this->SetFont('helvetica', '', 9);
                    $this->Cell(0, 4, "Website: $school_website", 0, 1, "C");
                }

                $this->Ln(2);

                // Broadsheet title and details
                $this->SetFont('helvetica', 'B', 14);
                $this->Cell(0, 5, 'BROADSHEET', 0, 1, 'C');

                $this->SetFont('helvetica', '', 9);
                $this->Cell(0, 4, "Class: {$this->class_name}  |  Term: {$this->term_name}  |  Section: {$this->section_name}  |  Session: {$this->session_name}", 0, 1, 'C');

                $this->Ln(3);
            }
        }

        public function Footer() {
            // No footer to maximize space
        }
    }

    // Create new PDF document - A3 Landscape
    $pdf = new BroadsheetPDF('L', PDF_UNIT, 'A3', true, 'UTF-8', false);

    $pdf->class_name = $class;
    $pdf->term_name = $term;
    $pdf->section_name = $section;
    $pdf->session_name = $session;

    // Set document information
    global $school_name;
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor($school_name);
    $pdf->SetTitle('Broadsheet - ' . $class);

    // Set margins - smaller for broadsheet
    $pdf->SetMargins(10, 65, 10);
    $pdf->SetHeaderMargin(5);
    $pdf->SetFooterMargin(5);

    // Set auto page breaks
    $pdf->SetAutoPageBreak(TRUE, 10);

    // Set font
    $pdf->SetFont('helvetica', '', 7);

    // Add a page
    $pdf->AddPage();

    // Calculate column widths - adjusted to give more space to subjects
    $sn_width = 8;
    $image_width = 15;
    $name_width = 45;
    $subject_count = count($subjects);
    // Further reduced width for total and position columns
    $total_width = 13;  // reduced from 15
    $avg_width = 12;    // unchanged
    $pos_width = 13;    // reduced from 15
    // Increased available width for subjects by reducing other columns
    $remaining_width = 400 - $sn_width - $image_width - $name_width - $total_width - $avg_width - $pos_width;
    // Give more width per subject
    $subject_width = $subject_count > 0 ? min(35, $remaining_width / $subject_count) : 35;

    // Table header
    $pdf->SetFillColor(220, 220, 220);
    $pdf->SetFont('helvetica', 'B', 8);

    $pdf->Cell($sn_width, 8, 'SN', 1, 0, 'C', 1);
    $pdf->Cell($image_width, 8, 'Photo', 1, 0, 'C', 1);
    $pdf->Cell($name_width, 8, 'Student Name', 1, 0, 'C', 1);

    // Subject headers
    foreach ($subjects as $subject) {
        $subject_name = $subject->subject;
        // Truncate subject names to max 9 characters
        if (strlen($subject_name) > 9) {
            // For subjects with spaces, abbreviate each word
            if (strpos($subject_name, ' ') !== false) {
                $words = explode(' ', $subject_name);
                $abbreviated = '';
                foreach ($words as $word) {
                    // Take first 2 characters and add a dot for shorter words, 3 for longer ones
                    if (strlen($word) <= 5) {
                        $abbreviated .= substr($word, 0, 1) . '. ';
                    } else {
                        $abbreviated .= substr($word, 0, min(3, strlen($word))) . '. ';
                    }
                }
                $subject_name = trim($abbreviated);
            } else {
                // For single long words, truncate with dots
                $subject_name = substr($subject_name, 0, 7) . '..';
            }
        }
        // Ensure subject name is max 9 characters
        if (strlen($subject_name) > 9) {
            $subject_name = substr($subject_name, 0, 9);
        }
        $pdf->Cell($subject_width, 8, $subject_name, 1, 0, 'C', 1);
    }

    $pdf->Cell($total_width, 8, 'Total', 1, 0, 'C', 1);
    $pdf->Cell($avg_width, 8, 'Avg', 1, 0, 'C', 1);
    $pdf->Cell($pos_width, 8, 'Position', 1, 0, 'C', 1);
    $pdf->Ln();

    // Table body
    $pdf->SetFont('helvetica', '', 9);
    $count = 1;

    foreach ($students as $student) {
        $student_name = $student->student_name;
        $reg_no = $student->reg_no;

        // Get student photo
        $sql = "SELECT photo FROM student WHERE name = :name";
        $conn->query($sql);
        $conn->bind(':name', $student_name);
        $photo_result = $conn->fetchSingle();
        $photo = $photo_result ? $photo_result->photo : '';

        // Prepare image path
        if (empty($photo)) {
            $img_file = 'upload/default.png';
        } else {
            $img_file = "upload/$photo";
        }

        // Get current Y position
        $current_y = $pdf->GetY();

        // SN
        $pdf->Cell($sn_width, 12, $count, 1, 0, 'C');

        // Image
        $pdf->Cell($image_width, 12, '', 1, 0, 'C');

        // Add image at specific position
        if (file_exists($img_file)) {
            $pdf->Image($img_file, $pdf->GetX() - $image_width + 2, $current_y + 2, $image_width - 4, 8, '', '', '', false, 300, '', false, false, 0);
        }

        // Student name - truncate if longer than 22 characters
        $display_student_name = $student_name;
        if (strlen($student_name) > 22) {
            $display_student_name = substr($student_name, 0, 19) . '...';
        }
        $pdf->Cell($name_width, 12, $display_student_name, 1, 0, 'L');

        // Get subject scores
        $subject_scores = [];
        $total_score = 0;

        foreach ($subjects as $subject) {
            $sql = "SELECT total_score FROM student_results 
                    WHERE student_name = :student_name 
                    AND subject = :subject 
                    AND class = :class 
                    AND term = :term 
                    AND section = :section 
                    AND current_school_session = :session";

            $conn->query($sql);
            $conn->bind(':student_name', $student_name);
            $conn->bind(':subject', $subject->subject);
            $conn->bind(':class', $class);
            $conn->bind(':term', $term);
            $conn->bind(':section', $section);
            $conn->bind(':session', $session);

            $score_result = $conn->fetchSingle();
            $score = $score_result ? $score_result->total_score : 0;
            $subject_scores[] = $score;
            $total_score += $score;
        }

        // Display subject scores
        foreach ($subject_scores as $score) {
            $pdf->Cell($subject_width, 12, $score, 1, 0, 'C');
        }

        // Calculate average
        $average = $subject_count > 0 ? round($total_score / $subject_count, 1) : 0;

        // Get student position
        $sql = "SELECT position FROM student_class_positions 
                WHERE name = :name 
                AND class = :class 
                AND section = :section 
                AND exam = :term 
                AND exam_year = :year";

        $conn->query($sql);
        $conn->bind(':name', $student_name);
        $conn->bind(':class', $class);
        $conn->bind(':section', $section);
        $conn->bind(':term', $term);
        $conn->bind(':year', date('Y'));
        $position_result = $conn->fetchSingle();
        $position = $position_result ? $position_result->position : '-';

        // Display total, average, and position - using new widths
        $pdf->Cell($total_width, 12, $total_score, 1, 0, 'C');
        $pdf->Cell($avg_width, 12, $average, 1, 0, 'C');
        $pdf->Cell($pos_width, 12, $position, 1, 0, 'C');
        $pdf->Ln();

        $count++;
    }

    // Output PDF
    $pdf->Output('broadsheet_' . $class . '_' . $term . '.pdf', 'I');

} else {
    // Return back to index page
    header("location: $redirect");
}
?>