<?php
$sql = "SELECT sr.*, at.template_name, at.total_marks as template_total_marks
        FROM student_results sr
        JOIN assessment_templates at ON sr.template_id = at.id
        WHERE sr.student_name = :student AND sr.class = :class 
        AND sr.term = :exam AND sr.section = :section 
        AND sr.current_school_session = :year 
        AND sr.total_score > 0
        ORDER BY sr.subject ASC";

$conn->query($sql);
$conn->bind(":student", $name);
$conn->bind(":class", $class);
$conn->bind(":exam", $exam);
$conn->bind(":section", $section);
$conn->bind(":year", $year);
$rowcount = $conn->rowCount();

if ($rowcount > 0) {
    $result = $conn->fetchMultiple();

    // Get template components for the first result (all subjects should use same template)
    $template_id = $result[0]->template_id;
    $sql = "SELECT * FROM assessment_components WHERE template_id = :template_id ORDER BY display_order";
    $conn->query($sql);
    $conn->bind(":template_id", $template_id);
    $components = $conn->fetchMultiple();

    // Get class ID
    $sql = "SELECT id FROM classes WHERE name = :name";
    $conn->query($sql);
    $conn->bind(":name", $class);
    $classID = $conn->fetchColumn();

    // Fetch teachers name for the student section
    $sql = "SELECT teacher_name FROM sections WHERE name = :name AND class = :class_id";
    $conn->query($sql);
    $conn->bind(":name", $section);
    $conn->bind(":class_id", $classID);
    $teacher_name = $conn->fetchColumn();

    class FlexiblePDF extends TCPDF
    {
        public $conn;
        private $student_name;
        private $header_printed = false;

        public function Header()
        {
            // Only print header on first page
            if ($this->getPage() == 1) {
                $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;
                $phone_no = $result_set->phone_no;
                $email = $result_set->email;
                $address = $result_set->address;
                $school_website = $result_set->school_website;

                global $name, $class, $exam, $section, $year;
                $this->student_name = ucwords($name);

                $imageFile = "upload/$school_logo";
                $this->Image($imageFile, 20, 2, 20, '', 'PNG', '', 'T', false, 300, '', false, false, 0, false, false, false);
                $this->Ln(2);
                $this->SetFont('helvetica', 'B', 20);
                $this->cell(205, 2, "$school_name", 0, 1, "C");
                $this->SetFont('helvetica', 'B', 10);
                $this->cell(190, 3, "$school_tagline", 0, 1, "C");

                if (!empty($school_website)) {
                    $this->SetFont('helvetica', 'B', 10);
                    $this->cell(190, 2, "Website: $school_website", 0, 1, "C");
                    $this->SetFont('helvetica', '');
                    $this->cell(86, 0, '__________________________________________________________________________________________________', 0, '', '', '');
                }

                $this->Ln();
                $this->SetFont('helvetica', '');

                // Get student information
                $sql = "SELECT sr.*, s.registerNO, s.photo, s.sex 
                        FROM student_results sr
                        JOIN student s ON sr.student_name = s.name
                        WHERE sr.student_name = :name AND sr.class = :class 
                        AND sr.term = :exam AND sr.section = :section 
                        AND sr.current_school_session = :year
                        LIMIT 1";
                $conn->query($sql);
                $conn->bind(":name", $this->student_name);
                $conn->bind(":class", $class);
                $conn->bind(":exam", $exam);
                $conn->bind(":section", $section);
                $conn->bind(":year", $year);
                $student_result = $conn->fetchSingle();

                if ($student_result) {
                    $reg_no = $student_result->registerNO;
                    $photo = $student_result->photo;
                    $gender = $student_result->sex;

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

                    $new_section = ucfirst($section);
                    $new_exam = ucfirst($exam);

                    // Calculate obtainable marks
                    $zero = 0;
                    $sql = "SELECT COUNT(DISTINCT subject) FROM student_results 
                            WHERE student_name = :name AND current_school_session = :year 
                            AND total_score > :zero AND class = :class AND term = :exam";
                    $conn->query($sql);
                    $conn->bind(":name", $this->student_name);
                    $conn->bind(":year", $year);
                    $conn->bind(":zero", $zero);
                    $conn->bind(":class", $class);
                    $conn->bind(":exam", $exam);
                    $subject_count = $conn->fetchColumn();
                    $obtainable_mark = $subject_count * 100;

                    // Student info HTML sections
                    $html = "
                      <div class='row'>
                         <div class='col-md-3'>
                           <h3>$school_name</h3>   
                           <span>$address</span>
                           <p><strong>Phone:</strong> $phone_no</p>  
                           <p><strong>Email:</strong> $email</p>  
                        </div>
                      </div>
                      <style>
                        h3{font-size: 12px; text-transform: lowercase !important;}  
                        p{font-size: 11px; line-height: 10px;} 
                        span{font-size: 12px;}
                     </style>";

                    $html2 = "
                      <div class='row'>
                         <div class='col-md-3'>
                           <p><strong>Name:</strong> {$this->student_name}</p>
                           <p><strong>Academic Year:</strong> $year</p>
                          <p><strong>Exam No:</strong> $reg_no </p>
                          <p><strong>Class:</strong> $class &nbsp; <strong>Section:</strong> $new_section</p>
                           <p><strong>Term:</strong> $new_exam </p>
                        </div>
                      </div>
                      <style>
                        h3{font-size: 12px;}  
                        h3 span{font-weight: 400 !important; font-size: 11px;}
                        p{font-size: 11px; line-height: 10px;} 
                        div{padding-right: 20px;}
                     </style>";

                    $html3 = "
                      <div class='row'>
                         <div class='col-md-3'>
                           <p><strong>Marks Obtainable:</strong> $obtainable_mark</p>
                          <p><strong>Gender:</strong> $gender <strong></p>
                        </div>
                      </div>
                      <style> 
                        p{font-size: 11px; line-height: 10px;} 
                        div{padding-right: 20px;}
                     </style>";

                    $this->WriteHtmlCell(50, 10, '', 15, $html);
                    $this->WriteHtmlCell(40, 20, 70, 16, $html2);
                    $this->WriteHtmlCell(40, 20, 120, 16, $html3);
                    $this->Image($img_file, 170, 28, 32, 32, '', '', '', false, 300, '', false, false, 0);
                }
                // Mark header as printed
                $this->header_printed = true;
            } else {
                // For subsequent pages, just add a small top margin
                $this->SetY(15);
            }
        }

        public function Footer()
        {
            // Only print footer on first page
            if ($this->getPage() == 1) {
                $this->SetY(-245);
                $this->Ln(8);
                $this->SetFont('times', 'B', '10');
                $this->MultiCell(189, 5, 'TERMINAL REPORT', 0, 'L', 0, 1, '', '', true);
            }
        }
    }

    // Create new PDF document
    $pdf = new FlexiblePDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

    // Set document information
    global $school_name;
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor("$school_name");
    $pdf->SetTitle('Flexible Terminal Report');

    // Set default header data
    $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING);

    // Set header and footer fonts
    $pdf->setHeaderFont(array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
    $pdf->setFooterFont(array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

    // Set default monospaced font
    $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

    // Set margins
    $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
    $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
    $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

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

    // Set image scale factor
    $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

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

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

    // Get general settings for background and stamps
    $sql = "SELECT * FROM general_settings";
    $conn->query($sql);
    $result_details = $conn->fetchSingle();
    $terminal_report_bg = $result_details->terminal_report_bg_image;
    $principal_name = $result_details->principal_name;
    $school_stamp = $result_details->school_stamp;

    if (!empty($terminal_report_bg)) {
        $url_bg = $admin_url . "upload/$terminal_report_bg";
        $pdf->Image("$url_bg", 60, 70, 90);
    }

    $pdf->Ln(38);
    $pdf->SetFillColor(224, 225, 255);
    $pdf->SetFont('helvetica', 'B', '9');

    // Dynamic table header based on assessment components
    $pdf->Cell(38, 4, 'SUBJECTS', 1, 0, 'L', 1);

    // Add component headers
    foreach ($components as $component) {
        $header_width = strlen($component->component_name) > 8 ? 18 : 15;
        $pdf->Cell($header_width, 4, substr($component->component_name, 0, 10), 1, 0, 'C', 1);
    }

    $pdf->Cell(18, 4, 'Total', 1, 0, 'C', 1);
    $pdf->Cell(18, 4, 'GRADE', 1, 0, 'C', 1);
    $pdf->Cell(18, 4, 'SUB POS', 1, 0, 'C', 1);
    $pdf->Cell(26, 4, 'REMARK', 1, 0, 'C', 1);

    $grand_total_scores = 0;
    $total_subjects = count($result);
    $subjects_for_previous_terms = []; // Store subjects for previous terms section

    foreach ($result as $report_details) {
        $subject_offered = $report_details->subject;
        $total_score = $report_details->total_score;
        $grade = $report_details->grade;
        $current_exam_year = $report_details->school_year;
        $result_id = $report_details->id;

        $grand_total_scores += $total_score;
        $subjects_for_previous_terms[] = $subject_offered; // Collect subjects

        // Get component scores for this result
        $sql = "SELECT sas.score, ac.component_name, ac.display_order 
                FROM student_assessment_scores sas
                JOIN assessment_components ac ON sas.component_id = ac.id
                WHERE sas.result_id = :result_id
                ORDER BY ac.display_order";
        $conn->query($sql);
        $conn->bind(":result_id", $result_id);
        $component_scores = $conn->fetchMultiple();

        // Create array for easy access
        $scores_by_order = [];
        foreach ($component_scores as $score) {
            $scores_by_order[$score->display_order] = $score->score;
        }

        // Get exam grades and remarks
        $sql = "SELECT * FROM grade WHERE :student_score BETWEEN mark_from AND mark_upto";
        $conn->query($sql);
        $conn->bind(":student_score", $total_score);
        $grade_result = $conn->fetchSingle();
        $grade = $grade_result ? $grade_result->grade_name : '';
        $remark = $grade_result ? $grade_result->note : '';

        // Get student subject position
        $sql = "SELECT position FROM subject_positions WHERE name = :name AND class = :class 
                AND section = :section AND exam = :exam AND exam_year = :exam_year AND subject = :subject";
        $conn->query($sql);
        $conn->bind(":name", $name);
        $conn->bind(":class", $class);
        $conn->bind(":exam", $exam);
        $conn->bind(":section", $section);
        $conn->bind(":exam_year", $current_exam_year);
        $conn->bind(":subject", $subject_offered);
        $subject_position = $conn->fetchColumn();

        // Display the row
        $pdf->Ln(5);
        $pdf->SetFont('helvetica', '', '9');
        $pdf->Cell(38, 4, $subject_offered, 0, 0, "L");

        // Display component scores
        for ($i = 1; $i <= count($components); $i++) {
            $score = isset($scores_by_order[$i]) ? $scores_by_order[$i] : '-';
            $cell_width = 15; // Adjust based on component name length if needed
            if ($i < count($components) && strlen($components[$i-1]->component_name) > 8) {
                $cell_width = 18;
            }
            $pdf->Cell($cell_width, 4, $score, 0, 0, "C");
        }

        $pdf->Cell(18, 4, $total_score, 0, 0, "C");
        $pdf->Cell(18, 4, $grade, 0, 0, "C");
        $pdf->Cell(18, 4, $subject_position ?: '-', 0, 0, "C");
        $pdf->Cell(26, 3, $remark, 0, 0, "C");
    }

    // Display totals
    $pdf->Ln(4);
    $pdf->MultiCell(189, 7, '_________________________________________________________________________________________________________', 0, 'L', 0, 1, '', '', true);
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'B', '11');

    // Calculate component totals
    $component_totals = [];
    foreach ($result as $report_details) {
        $result_id = $report_details->id;

        $sql = "SELECT sas.score, ac.display_order 
                FROM student_assessment_scores sas
                JOIN assessment_components ac ON sas.component_id = ac.id
                WHERE sas.result_id = :result_id
                ORDER BY ac.display_order";
        $conn->query($sql);
        $conn->bind(":result_id", $result_id);
        $component_scores = $conn->fetchMultiple();

        foreach ($component_scores as $score) {
            if (!isset($component_totals[$score->display_order])) {
                $component_totals[$score->display_order] = 0;
            }
            $component_totals[$score->display_order] += $score->score;
        }
    }

    $pdf->Cell(38, 4, 'Total', 0, 0, "L");

    // Display component totals
    for ($i = 1; $i <= count($components); $i++) {
        $total = isset($component_totals[$i]) ? $component_totals[$i] : 0;
        $cell_width = 15;
        if ($i < count($components) && strlen($components[$i-1]->component_name) > 8) {
            $cell_width = 18;
        }
        $pdf->Cell($cell_width, 4, $total, 0, 0, "C");
    }

    $pdf->Cell(18, 4, $grand_total_scores, 0, 0, "C");

    // Add Previous Terms Section (for 2nd and 3rd term only)
    $previous_terms_data = getPreviousTermsData($conn, $name, $class, $exam, $section, $year, $subjects_for_previous_terms);

    if (!empty($previous_terms_data['previous_terms'])) {
        $pdf->Ln(10);
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetFont('times', 'B', '12');
        $pdf->Cell(189, 4, 'PREVIOUS TERM(S) PERFORMANCE', 0, 1, 'C');

        foreach ($previous_terms_data['previous_terms'] as $term_data) {
            $pdf->Ln(5);
            $pdf->SetTextColor(14, 93, 117);
            $pdf->SetFont('times', 'B', '11');
            $pdf->Cell(189, 4, strtoupper($term_data['term']) . ' RESULTS', 0, 1, 'L');

            // Create subject headers and scores table
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('helvetica', 'B', '8');
            $pdf->SetFillColor(240, 240, 240);

            // Calculate how many subjects can fit per row (assume ~25 units per subject)
            $subjects_per_row = 6;
            $subject_chunks = array_chunk($term_data['subjects'], $subjects_per_row);

            foreach ($subject_chunks as $chunk_index => $subjects_chunk) {
                if ($chunk_index > 0) $pdf->Ln(2);

                // Subject headers
                $pdf->SetFont('helvetica', 'B', '8');
                $pdf->SetFillColor(240, 240, 240); // Ensure background color is set for headers
                foreach ($subjects_chunk as $subject_data) {
                    $pdf->Cell(30, 7, substr($subject_data['subject'], 0, 18), 1, 0, 'C', 1);
                }
                $pdf->Ln();

                // Subject scores
                $pdf->SetFont('helvetica', '', '8');
                $pdf->SetFillColor(255, 255, 255);
                foreach ($subjects_chunk as $subject_data) {
                    $pdf->Cell(30, 7, $subject_data['score'], 1, 0, 'C', 0);
                }
                $pdf->Ln();
            }

            // Display total and average for this term
            $pdf->Ln(2);
            $pdf->SetFont('times', 'B', '10');
            $pdf->Cell(40, 4, $term_data['term'] . ' Total: ' . $term_data['total'], 0, 0, 'L');
            $pdf->Cell(40, 4, 'Average: ' . number_format($term_data['average'], 1), 0, 0, 'L');
        }

        // Calculate and display cumulative average
        if (count($previous_terms_data['previous_terms']) > 0) {
            $current_term_average = round($grand_total_scores / $total_subjects, 1);
            $all_averages = [$current_term_average];

            foreach ($previous_terms_data['previous_terms'] as $term_data) {
                $all_averages[] = $term_data['average'];
            }

            $cumulative_average = array_sum($all_averages) / count($all_averages);

            $pdf->Ln(5);
            $pdf->SetTextColor(14, 93, 117);
            $pdf->SetFont('times', 'B', '11');
            $pdf->Cell(189, 4, 'CUMULATIVE AVERAGE: ' . number_format($cumulative_average, 1) . '%', 0, 1, 'C');
        }
    }

    $pdf->Ln(8);
    $mark_average = round($grand_total_scores / $total_subjects, 2);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(21, 4, "Percentage: ", 0, 0, "L");
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(40, 4, "$mark_average %", 0, 0, "L");

    // Get total number of students that sat for exam
    $sql = "SELECT COUNT(DISTINCT student_name) FROM student_results 
            WHERE class = :class AND term = :exam AND section = :section 
            AND current_school_session = :year AND total_score > 0";
    $conn->query($sql);
    $conn->bind(":class", $class);
    $conn->bind(":exam", $exam);
    $conn->bind(":section", $section);
    $conn->bind(":year", $year);
    $total_students = $conn->fetchColumn();

    // Get class position visibility status
    $sql = "SELECT show_position FROM classes WHERE name = :name";
    $conn->query($sql);
    $conn->bind(":name", $class);
    $show_hide_position = $conn->fetchColumn();

    // Get student position
    $sql = "SELECT position FROM student_class_positions WHERE name = :name AND class = :class 
            AND section = :section AND exam = :exam AND exam_year = :exam_year";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $conn->bind(":class", $class);
    $conn->bind(":exam", $exam);
    $conn->bind(":section", $section);
    $conn->bind(":exam_year", date('Y'));
    $student_position = $conn->fetchColumn();

    if ($show_hide_position == 'yes') {
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetFont('times', 'B', '11');
        $pdf->Cell(30, 4, "Position in class: ", 0, 0, "L");
        $pdf->SetFont('times', 'N', '11');
        $pdf->Cell(40, 4, $student_position . ' Out of ' . $total_students . " total students", 0, 0, "L");
    }

    $approximate_percentage = round($mark_average);

    // Get comments if they exist
    $sql = "SELECT * FROM comments WHERE :student_percentage BETWEEN percent_from AND percent_upto";
    $conn->query($sql);
    $conn->bind(":student_percentage", $approximate_percentage);
    $comment_result = $conn->fetchSingle();
    $teacher_comment = $comment_result ? $comment_result->class_teacher_comment : '';
    $principal_comment = $comment_result ? $comment_result->principal_comment : '';

    // Add school stamp
    if (!empty($school_stamp)) {
        $url_bg = $admin_url . "upload/$school_stamp";
        $pdf->Image("$url_bg", 170, '', 25);
    }

    $pdf->Ln(6);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(47, 4, "Class Teacher's Comment: ________________________________________________", 0, 0, "L");
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$teacher_comment", 0, 0, "L");

    $pdf->Ln(6);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(40, 4, "Class Teacher's Name: ", 0, 0, "L");
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 5, "$teacher_name", 0, 0, "L");

    $pdf->Ln(6);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(40, 4, "Principal's Comment: ____________________________________________________", 0, 0, "L");
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$principal_comment", 0, 0, "L");

    $pdf->Ln(6);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(40, 4, "Principal's Name: ", 0, 0, "L");
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(40, 4, "$principal_name", 0, 0, "L");

    // Get students' attendance details
    $sql = "SELECT * FROM attendance WHERE student_name = :name AND exam = :exam AND exam_year = :exam_year";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $conn->bind(":exam", $exam);
    $conn->bind(":exam_year", date('Y'));
    $attendance_details = $conn->fetchSingle();
    $attendance = $attendance_details ? $attendance_details->attendance : 'N/A';

    $pdf->Ln(7);
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(21, 5, "Attendance:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(20, 6, "$attendance times", 0, 0, 'L', 0);

    $sql = "SELECT * FROM general_settings";
    $conn->query($sql);
    $result_set = $conn->fetchSingle();
    $next_term_begins = $result_set->next_term_begins;
    $next_term_begins1 = date_create($next_term_begins);
    $new_next_term_begins = date_format($next_term_begins1, 'F d, Y');
    $no_times_school_open = $result_set->no_times_school_open;

    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(30, 5, "Next term begins:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(37, 5, "$new_next_term_begins", 0, 0, 'L', 0);
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'B', '11');
    $pdf->Cell(45, 5, "No of times school opened:", 0, 0, 'L', 0);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(10, 6, "$no_times_school_open times", 0, 0, 'L', 0);

    $pdf->Ln(7);
    $pdf->SetTextColor(14, 93, 117);
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(33, 3, 'Meaning of Grades:', 0, 0, 'L');

    // Fetch all available grades from the system
    $sql = "SELECT * FROM grade ORDER BY mark_from DESC LIMIT 0, 8";
    $conn->query($sql);
    $grade_result = $conn->fetchMultiple();

    foreach ($grade_result as $student_grading) {
        $mark_upto = $student_grading->mark_upto;
        $mark_from = $student_grading->mark_from;
        $student_grade = $student_grading->grade_name;
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetFont('times', 'N', '10');
        $pdf->Cell(20, 3, "$mark_from - $mark_upto($student_grade) ", 0, 0, "L");
    }

    $sql = "SELECT * FROM grade ORDER BY mark_from DESC LIMIT 8, 6";
    $conn->query($sql);
    $grade_result = $conn->fetchMultiple();
    $pdf->Ln(4);
    foreach ($grade_result as $student_grading) {
        $mark_upto = $student_grading->mark_upto;
        $mark_from = $student_grading->mark_from;
        $student_grade = $student_grading->grade_name;
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetFont('times', 'N', '10');
        $pdf->Cell(15, 7, "", 0, 0, "L");
        $pdf->Cell(4, 5, "$mark_from - $mark_upto($student_grade) ", 0, 0, "L");
    }

    // Get student weight and more details
    $sql = "SELECT * FROM student WHERE name = :name";
    $conn->query($sql);
    $conn->bind(":name", $name);
    $row_count = $conn->rowCount();

    if ($row_count > 0) {
        $student_info = $conn->fetchSingle();
        $dob = $student_info->dob;
        $dob1 = date_create($dob);
        $new_date = date_format($dob1, 'F, d Y');
        $weight = $student_info->weight;
        $height = $student_info->height;

        $pdf->Ln(5);
        if (!empty($weight)) {
            $pdf->SetFont('times', 'N', '11');
            $pdf->Cell(15, 5, "Weight: ", 0, 0, "L");
            $pdf->SetTextColor(14, 93, 117);
            $pdf->SetFont('times', 'N', '10');
            $pdf->Cell(13, 5, "$weight kg", 0, 0, "L");
        }
        if (!empty($height)) {
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('times', 'N', '11');
            $pdf->Cell(13, 6, "Height: ", 0, 0, "L");
            $pdf->SetTextColor(14, 93, 117);
            $pdf->SetFont('times', 'N', '10');
            $pdf->Cell(13, 6, "$height m", 0, 0, "L");
        }

        if (!empty($new_date)) {
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('times', 'N', '11');
            $pdf->Cell(23, 6, "Date of Birth: ", 0, 0, "L");
            $pdf->SetTextColor(14, 93, 117);
            $pdf->SetFont('times', 'N', '10');
            $pdf->Cell(30, 6, "$new_date", 0, 0, "L");
        }
    }

    // Result analysis
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '11');
    $pdf->Cell(28, 5, "Result Analysis: ", 0, 0, "L");
    $pass_percent = 50;
    if ($mark_average >= $pass_percent) {
        $pdf->SetTextColor(0, 128, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(16, 5, "Passed", 0, 0, "L");
    } else {
        $pdf->SetTextColor(255, 0, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(16, 5, "Failed", 0, 0, "L");
    }

    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFont('times', 'N', '10');
    $pdf->Cell(50, 5, "(based on 50% and above)", 0, 0, "L");
    $pdf->Ln(5);

    // Insert psychomotor skills
    $sql = "SELECT * FROM students_psychomotor WHERE student_name = :student_name AND section = :section 
            AND class = :class AND exam = :exam AND exam_year = :exam_year";
    $conn->query($sql);
    $conn->bind(':section', $section);
    $conn->bind(':class', $class);
    $conn->bind(':exam', $exam);
    $conn->bind(':exam_year', date('Y'));
    $conn->bind(':student_name', $name);
    $row_count = $conn->rowCount();

    if ($row_count > 0) {
        $psychomotor_results = $conn->fetchMultiple();
        $pdf->Ln(5);
        $pdf->SetTextColor(255, 0, 0);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(21, 4, "PSYCHOMOTOR SKILLS ", 0, 1, "L");
        $pdf->SetTextColor(255, 244, 255);
        $pdf->SetFont('times', 'B', '10');
        $pdf->SetFillColor(224, 210, 100);
        $pdf->Cell(50, 4, 'Name', 0, 0, 'L', 1);
        $pdf->Cell(18, 4, 'Value', 0, 0, 'L', 1);
        foreach ($psychomotor_results as $data) {
            $pdf->Ln(4);
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('helvetica', 'N', '8');
            $pdf->Cell(50, 4, $data->psychomotor_name, 1, 0, 'L', 0);
            $pdf->Cell(18, 4, $data->psychomotor_value, 1, 0, 'L', 0);
        }
        $pdf->Ln(5);
        $pdf->Cell(15, 4, 'Key: 1(Poor), 2(Fair), 3(Good), 4(V.Good), 5(Excellent)', 0, 0, 'L', 0);
    }

    // Insert cognitive area
    $sql = "SELECT * FROM students_cognitive_area WHERE student_name = :student_name AND section = :section 
            AND class = :class AND exam = :exam AND exam_year = :exam_year";
    $conn->query($sql);
    $conn->bind(':section', $section);
    $conn->bind(':class', $class);
    $conn->bind(':exam', $exam);
    $conn->bind(':exam_year', date('Y'));
    $conn->bind(':student_name', $name);
    $row_count = $conn->rowCount();

    if ($row_count > 0) {
        $cognitive_results = $conn->fetchMultiple();
        $pdf->Ln(-33);
        $pdf->SetX(96);
        $pdf->SetTextColor(100, 10, 90);
        $pdf->SetFont('times', 'B', '10');
        $pdf->Cell(21, 4, "COGNITIVE AREA", 0, 1, "L");
        $pdf->SetX(96);
        $pdf->SetTextColor(255, 244, 255);
        $pdf->SetFont('times', 'B', '10');
        $pdf->SetFillColor(24, 60, 100);
        $pdf->Cell(50, 4, 'Name', 0, 0, 'L', 1);
        $pdf->Cell(18, 4, 'Value', 0, 0, 'L', 1);
        foreach ($cognitive_results as $data) {
            $pdf->Ln(4);
            $pdf->SetX(96);
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFont('helvetica', 'N', '8');
            $pdf->Cell(50, 4, $data->cognitive_name, 1, 0, 'L', 0);
            $pdf->Cell(18, 4, $data->cognitive_value, 1, 0, 'L', 0);
        }
    }

} else {
    // No results found, redirect back to homepage
    $admin_url = $conn->base_url();
    ?>
    <script>
        alert('Student results details have not been added yet for the selected term. Please add marks before viewing terminal report.');
    </script>
    <meta http-equiv="refresh" content="0; <?php echo $admin_url; ?>">
    <?php
}

// Function to get previous terms data
function getPreviousTermsData($conn, $student_name, $class, $current_term, $section, $year, $subjects_for_current_term) {
    $previous_terms_data = ['previous_terms' => []];

    // Define term hierarchy
    $terms_order = ['1st term', '2nd term', '3rd term'];
    $current_term_index = array_search($current_term, $terms_order);

    if ($current_term_index === false || $current_term_index === 0) {
        return $previous_terms_data; // No previous terms for 1st term
    }

    // Get previous terms (all terms before current one)
    $previous_terms = array_slice($terms_order, 0, $current_term_index);

    foreach ($previous_terms as $term) {
        $term_data = [
            'term' => $term,
            'subjects' => [],
            'total' => 0,
            'average' => 0
        ];

        // Get results for this previous term for the same subjects
        foreach ($subjects_for_current_term as $subject) {
            $sql = "SELECT total_score FROM student_results 
                    WHERE student_name = :student_name 
                    AND class = :class 
                    AND term = :term 
                    AND section = :section 
                    AND subject = :subject 
                    AND current_school_session = :year 
                    AND total_score > 0";

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

            $score_result = $conn->fetchSingle();
            $score = $score_result ? $score_result->total_score : 0;

            $term_data['subjects'][] = [
                'subject' => $subject,
                'score' => $score
            ];

            $term_data['total'] += $score;
        }

        // Calculate average
        $subject_count = count($subjects_for_current_term);
        $term_data['average'] = $subject_count > 0 ? round($term_data['total'] / $subject_count, 1) : 0;

        // Only add term if it has some scores
        if ($term_data['total'] > 0) {
            $previous_terms_data['previous_terms'][] = $term_data;
        }
    }

    return $previous_terms_data;
}

