<?php
// PhpSpreadsheet PDF Generator for Report Cards
session_start();
include 'config.php';

// Check authentication
if (!isset($_SESSION['id']) || !isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != 1 || !isset($_SESSION['role']) || $_SESSION['role'] != 1) {
    header("Location: login.php");
    exit();
}

// Include shared grading functions
require_once 'shared_grading_functions.php';

// Check if PhpSpreadsheet is available
if (!file_exists(__DIR__ . '/vendor/autoload.php')) {
    die("Composer autoload not found. Please install dependencies.");
}

require_once __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

// Get parameters
$academic_year = $_GET['academic_year'] ?? '';
$term = $_GET['term'] ?? '';
$exam_id = $_GET['exam_id'] ?? '';
$class_id = $_GET['class_id'] ?? '';
$download_type = $_GET['download_type'] ?? 'all';
$student_id = $_GET['student_id'] ?? '';

// Validate parameters
if (empty($academic_year) || empty($term) || empty($exam_id) || empty($class_id)) {
    die("Missing required parameters");
}

// Create database connection
$connection = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

try {
    // Get all necessary data
    $sql_class = "SELECT class_name, school_id FROM classes WHERE class_id = ?";
    $stmt_class = $connection->prepare($sql_class);
    $stmt_class->bind_param("i", $class_id);
    $stmt_class->execute();
    $class_result = $stmt_class->get_result();
    $class_info = $class_result->fetch_assoc();

    if (!$class_info) {
        die("Class not found");
    }

    $class_name = $class_info['class_name'];
    $class_school_id = $class_info['school_id'];

    $sql_school = "SELECT school_name, address, phone, email, motto, logo FROM schools WHERE school_id = ?";
    $stmt_school = $connection->prepare($sql_school);
    $stmt_school->bind_param("i", $class_school_id);
    $stmt_school->execute();
    $result_school = $stmt_school->get_result();
    $school = $result_school->fetch_assoc();

    $actual_exam_id_for_display = 17;
    $sql_exam = "SELECT exam_name FROM exams WHERE exam_id = ? AND school_id = ?";
    $stmt_exam = $connection->prepare($sql_exam);
    $stmt_exam->bind_param("ii", $actual_exam_id_for_display, $class_school_id);
    $stmt_exam->execute();
    $result_exam = $stmt_exam->get_result();
    $exam = $result_exam->fetch_assoc();

    // Get students
    if ($download_type === 'single' && !empty($student_id)) {
        $sql_students = "
        SELECT id as user_id, username as student_name, ADM, parent_name, parent_phone, role, class_id, school_id, photo, stream_id
        FROM users 
        WHERE id = ? AND class_id = ? AND school_id = ? AND role != 1";
        
        $stmt_students = $connection->prepare($sql_students);
        $stmt_students->bind_param("iii", $student_id, $class_id, $class_school_id);
    } else {
        $sql_students = "
        SELECT id as user_id, username as student_name, ADM, parent_name, parent_phone, role, class_id, school_id, photo, stream_id
        FROM users 
        WHERE class_id = ? AND school_id = ? AND role != 1
        ORDER by username";
        
        $stmt_students = $connection->prepare($sql_students);
        $stmt_students->bind_param("ii", $class_id, $class_school_id);
    }

    $stmt_students->execute();
    $result_students = $stmt_students->get_result();

    $students = [];
    while ($row = $result_students->fetch_assoc()) {
        $students[] = $row;
    }

    if (empty($students)) {
        die("No students found");
    }

    // Get subjects with teacher information
    $sql_subjects = "
    SELECT s.subject_id, s.subject_name, 
           COALESCE(u.username, 'Not Assigned') as teacher_name
    FROM subjects s 
    LEFT JOIN subject_assignments sa ON s.subject_id = sa.subject_id AND sa.class_id = ?
    LEFT JOIN users u ON sa.teacher_id = u.id
    WHERE s.school_id = ? AND s.class_id = ?
    ORDER BY s.subject_name";

    $stmt_subjects = $connection->prepare($sql_subjects);
    $stmt_subjects->bind_param("iii", $class_id, $class_school_id, $class_id);
    $stmt_subjects->execute();
    $result_subjects = $stmt_subjects->get_result();

    $subjects = [];
    while ($row = $result_subjects->fetch_assoc()) {
        $subjects[] = $row;
    }

    // Create new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    
    // Set PDF writer to Mpdf (most reliable for complex layouts)
    \PhpOffice\PhpSpreadsheet\IOFactory::registerWriter('Pdf', \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf::class);

    $studentIndex = 0;
    foreach ($students as $student) {
        if ($studentIndex > 0) {
            // Create new worksheet for each student after the first
            $worksheet = $spreadsheet->createSheet();
        } else {
            $worksheet = $spreadsheet->getActiveSheet();
        }
        
        $worksheet->setTitle('Report_' . $student['ADM']);

        // Set page setup
        $worksheet->getPageSetup()
            ->setOrientation(PageSetup::ORIENTATION_PORTRAIT)
            ->setPaperSize(PageSetup::PAPERSIZE_A4)
            ->setMargins(0.75, 0.75, 0.75, 0.75);

        // Get student marks
        $sql_marks = "
        SELECT m.subject_id, m.marks, s.subject_name
        FROM marks m
        JOIN subjects s ON m.subject_id = s.subject_id
        WHERE m.student_id = ? AND m.exam_id = ? AND m.academic_year = ? AND m.term = ?
        ORDER BY s.subject_name";

        $stmt_marks = $connection->prepare($sql_marks);
        $stmt_marks->bind_param("iiss", $student['user_id'], $exam_id, $academic_year, $term);
        $stmt_marks->execute();
        $result_marks = $stmt_marks->get_result();

        $student_marks = [];
        $total_marks = 0;
        $total_subjects = 0;

        while ($row = $result_marks->fetch_assoc()) {
            $student_marks[$row['subject_id']] = $row['marks'];
            if ($row['marks'] !== null && $row['marks'] !== '') {
                $total_marks += floatval($row['marks']);
                $total_subjects++;
            }
        }

        $average = $total_subjects > 0 ? round($total_marks / $total_subjects, 2) : 0;

        // Start building the report card
        $row = 1;

        // School Header (Rows 1-5)
        $worksheet->mergeCells("A{$row}:I" . ($row + 4));
        $worksheet->setCellValue("A{$row}", 
            $school['school_name'] . "\n" .
            $school['address'] . "\n" .
            "Tel: " . $school['phone'] . " | Email: " . $school['email'] . "\n" .
            '"' . $school['motto'] . '"'
        );
        
        $worksheet->getStyle("A{$row}:I" . ($row + 4))->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'font' => [
                'bold' => true,
                'size' => 12,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THICK,
                ],
            ],
        ]);

        $row += 6;

        // Academic Report Title
        $worksheet->mergeCells("A{$row}:I{$row}");
        $worksheet->setCellValue("A{$row}", "ACADEMIC REPORT FOR " . strtoupper($term) . " - " . $academic_year);
        $worksheet->getStyle("A{$row}")->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'font' => [
                'bold' => true,
                'size' => 14,
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '000000'],
            ],
            'font' => [
                'color' => ['rgb' => 'FFFFFF'],
                'bold' => true,
                'size' => 14,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THICK,
                ],
            ],
        ]);

        $row += 2;

        // Student Information Section
        $worksheet->setCellValue("A{$row}", "STUDENT NAME:");
        $worksheet->setCellValue("B{$row}", strtoupper($student['student_name']));
        $worksheet->setCellValue("E{$row}", "ADM NO:");
        $worksheet->setCellValue("F{$row}", $student['ADM']);

        $row++;
        $worksheet->setCellValue("A{$row}", "CLASS:");
        $worksheet->setCellValue("B{$row}", $class_name);
        $worksheet->setCellValue("E{$row}", "EXAM:");
        $worksheet->setCellValue("F{$row}", $exam['exam_name'] ?? 'Mid-Term Exam');

        $row++;
        $worksheet->setCellValue("A{$row}", "PARENT/GUARDIAN:");
        $worksheet->setCellValue("B{$row}", $student['parent_name']);
        $worksheet->setCellValue("E{$row}", "PHONE:");
        $worksheet->setCellValue("F{$row}", $student['parent_phone']);

        // Style student info section
        $infoStartRow = $row - 2;
        $worksheet->getStyle("A{$infoStartRow}:I{$row}")->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
            ],
        ]);

        $row += 3;

        // Subjects Header
        $headerRow = $row;
        $worksheet->setCellValue("A{$row}", "SUBJECT");
        $worksheet->setCellValue("B{$row}", "TEACHER");
        $worksheet->setCellValue("C{$row}", "MARKS");
        $worksheet->setCellValue("D{$row}", "GRADE");
        $worksheet->setCellValue("E{$row}", "REMARKS");

        $worksheet->getStyle("A{$row}:E{$row}")->applyFromArray([
            'font' => [
                'bold' => true,
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'E0E0E0'],
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THICK,
                ],
            ],
        ]);

        $row++;

        // Subject rows
        $subjectStartRow = $row;
        foreach ($subjects as $subject) {
            $mark = $student_marks[$subject['subject_id']] ?? '';
            $grade_info = getGradeFromScale($mark, $subject['subject_id'], $class_id, $academic_year, $term, $class_school_id, $connection);
            
            $worksheet->setCellValue("A{$row}", $subject['subject_name']);
            $worksheet->setCellValue("B{$row}", $subject['teacher_name']);
            $worksheet->setCellValue("C{$row}", $mark);
            $worksheet->setCellValue("D{$row}", $grade_info['grade']);
            $worksheet->setCellValue("E{$row}", $grade_info['remarks']);
            
            $row++;
        }

        // Style subject rows
        $worksheet->getStyle("A{$subjectStartRow}:E" . ($row - 1))->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);

        $row += 2;

        // Summary section
        $worksheet->setCellValue("A{$row}", "TOTAL MARKS:");
        $worksheet->setCellValue("B{$row}", $total_marks);
        $worksheet->setCellValue("D{$row}", "AVERAGE:");
        $worksheet->setCellValue("E{$row}", $average . "%");

        $worksheet->getStyle("A{$row}:E{$row}")->applyFromArray([
            'font' => [
                'bold' => true,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THICK,
                ],
            ],
        ]);

        // Auto-size columns
        foreach (range('A', 'E') as $col) {
            $worksheet->getColumnDimension($col)->setAutoSize(true);
        }

        $studentIndex++;
    }

    // Generate filename
    $filename = $download_type === 'single' 
        ? "Report_Card_" . $students[0]['ADM'] . "_" . $term . "_" . $academic_year . ".pdf"
        : "All_Report_Cards_" . $class_name . "_" . $term . "_" . $academic_year . ".pdf";

    // Set active sheet to first sheet
    $spreadsheet->setActiveSheetIndex(0);

    // Create PDF writer and save
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Pdf');
    
    // Set HTTP headers for download
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    // Output PDF
    $writer->save('php://output');

} catch (Exception $e) {
    echo "Error generating PDF: " . $e->getMessage();
    echo "<br><br>This might be because a PDF library is not installed.";
    echo "<br>Please try: <code>composer require mpdf/mpdf</code>";
} finally {
    if (isset($connection)) {
        $connection->close();
    }
}
?>