<?php
// Start output buffering to prevent any HTML output before JSON
ob_start();

// Error handling setup
error_reporting(E_ALL);
ini_set('display_errors', 0); // Don't display errors to output
ini_set('log_errors', 1); // Log errors instead

session_start();

// Function to send JSON response and exit
function sendJsonResponse($response) {
    // Clear any previous output
    ob_clean();
    header('Content-Type: application/json');
    echo json_encode($response);
    ob_end_flush();
    exit;
}

// Check if user is logged in and is admin
if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true || $_SESSION["role"] !== 1) {
    http_response_code(403);
    sendJsonResponse(['success' => false, 'message' => 'Unauthorized access']);
}

// Check for required files
if (!file_exists('config.php')) {
    sendJsonResponse(['success' => false, 'message' => 'Configuration file not found']);
}

require_once 'config.php';

// Check if Composer autoload exists
if (!file_exists('vendor/autoload.php')) {
    sendJsonResponse(['success' => false, 'message' => 'PhpSpreadsheet library not installed. Please run: composer install']);
}

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Border;

// Check database connection
if (!isset($conn) || !$conn instanceof mysqli) {
    sendJsonResponse(['success' => false, 'message' => 'Database connection failed']);
}

// Get POST data
$rawInput = file_get_contents('php://input');
if (empty($rawInput)) {
    sendJsonResponse(['success' => false, 'message' => 'No input data received']);
}

$input = json_decode($rawInput, true);
if (json_last_error() !== JSON_ERROR_NONE) {
    sendJsonResponse(['success' => false, 'message' => 'Invalid JSON input']);
}

$reportType = $input['report_type'] ?? '';
$schoolId = $input['school_id'] ?? $_SESSION['school_id'];

// Validate inputs
if (empty($reportType)) {
    sendJsonResponse(['success' => false, 'message' => 'Report type is required']);
}

if (empty($schoolId)) {
    sendJsonResponse(['success' => false, 'message' => 'School ID is required']);
}

// Create reports directory if it doesn't exist
$reportsDir = 'reports/';
if (!file_exists($reportsDir)) {
    if (!mkdir($reportsDir, 0755, true)) {
        sendJsonResponse(['success' => false, 'message' => 'Unable to create reports directory']);
    }
}

// Initialize response
$response = ['success' => false, 'message' => '', 'pdf_file' => '', 'excel_file' => ''];

try {
    switch ($reportType) {
        case 'student_list':
            $response = generateStudentListReport($conn, $schoolId, $reportsDir);
            break;
        case 'student_by_class':
            $response = generateStudentByClassReport($conn, $schoolId, $reportsDir);
            break;
        case 'student_contacts':
            $response = generateStudentContactsReport($conn, $schoolId, $reportsDir);
            break;
        case 'teacher_list':
            $response = generateTeacherListReport($conn, $schoolId, $reportsDir);
            break;
        case 'teacher_subjects':
            $response = generateTeacherSubjectsReport($conn, $schoolId, $reportsDir);
            break;
        case 'teacher_workload':
            $response = generateTeacherWorkloadReport($conn, $schoolId, $reportsDir);
            break;
        case 'class_list':
            $response = generateClassListReport($conn, $schoolId, $reportsDir);
            break;
        case 'subject_allocation':
            $response = generateSubjectAllocationReport($conn, $schoolId, $reportsDir);
            break;
        case 'report_cards':
            $response = generateReportCardsReport($conn, $schoolId, $reportsDir);
            break;
        case 'exam_results':
            $response = generateExamResultsReport($conn, $schoolId, $reportsDir);
            break;
        case 'grade_analysis':
            $response = generateGradeAnalysisReport($conn, $schoolId, $reportsDir);
            break;
        case 'transcripts':
            $response = generateTranscriptsReport($conn, $schoolId, $reportsDir);
            break;
        default:
            sendJsonResponse(['success' => false, 'message' => 'Invalid report type: ' . $reportType]);
    }
} catch (Exception $e) {
    sendJsonResponse(['success' => false, 'message' => 'Error generating report: ' . $e->getMessage()]);
} catch (Error $e) {
    sendJsonResponse(['success' => false, 'message' => 'System error: ' . $e->getMessage()]);
}

// Send the final response
sendJsonResponse($response);

// Report generation functions

// Function to get school information
function getSchoolInfo($conn, $schoolId) {
    $sql = "SELECT school_name, abbreviation, email, motto, website, phone, address, box_number, logo, primary_color, secondary_color FROM schools WHERE school_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $schoolId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($row = $result->fetch_assoc()) {
        return $row;
    }
    
    // Default school info if not found
    return [
        'school_name' => 'SCHOOL NAME',
        'abbreviation' => '',
        'email' => '',
        'motto' => '',
        'website' => '',
        'phone' => '',
        'address' => '',
        'box_number' => '',
        'logo' => '',
        'primary_color' => '#333',
        'secondary_color' => '#666'
    ];
}

// Function to generate school header HTML
function generateSchoolHeader($schoolInfo, $reportTitle = '') {
    $html = "<div class='school-header'>";
    $html .= "<div class='school-name'>" . htmlspecialchars($schoolInfo['school_name']) . "</div>";
    
    if (!empty($schoolInfo['motto']) && $schoolInfo['motto'] !== 'N/A') {
        $html .= "<div class='school-motto'>\"" . htmlspecialchars($schoolInfo['motto']) . "\"</div>";
    }
    
    $html .= "<div class='school-contact'>";
    $contactParts = [];
    
    if (!empty($schoolInfo['address'])) {
        $contactParts[] = htmlspecialchars($schoolInfo['address']);
    }
    if (!empty($schoolInfo['box_number']) && $schoolInfo['box_number'] !== 'N/A') {
        $contactParts[] = htmlspecialchars($schoolInfo['box_number']);
    }
    if (!empty($schoolInfo['phone'])) {
        $contactParts[] = "Tel: " . htmlspecialchars($schoolInfo['phone']);
    }
    if (!empty($schoolInfo['email'])) {
        $contactParts[] = "Email: " . htmlspecialchars($schoolInfo['email']);
    }
    
    $html .= implode(' | ', $contactParts);
    $html .= "</div>";
    
    if (!empty($reportTitle)) {
        $html .= "<div class='report-title'>" . htmlspecialchars($reportTitle) . "</div>";
    }
    
    $html .= "</div>";
    
    return $html;
}

// Function to generate common CSS styles for reports
function generateReportCSS($schoolInfo) {
    $primaryColor = $schoolInfo['primary_color'] ?? '#333';
    $secondaryColor = $schoolInfo['secondary_color'] ?? '#666';
    
    return "
    <style>
        body { font-family: 'Times New Roman', Times, serif; margin: 0; padding: 20px; background: white; color: #333; }
        .school-header { text-align: center; margin-bottom: 30px; border-bottom: 3px solid $primaryColor; padding-bottom: 20px; }
        .school-name { font-size: 28px; font-weight: bold; color: $primaryColor; margin: 0; text-transform: uppercase; letter-spacing: 1px; }
        .school-motto { font-size: 16px; color: $secondaryColor; margin: 8px 0; font-style: italic; }
        .school-contact { font-size: 14px; color: #666; margin: 10px 0; }
        .report-title { font-size: 20px; font-weight: bold; color: $primaryColor; margin: 15px 0 5px 0; text-transform: uppercase; }
        .report-subtitle { font-size: 16px; color: $secondaryColor; margin: 5px 0; }
        .content-wrapper { max-width: 1200px; margin: 0 auto; }
        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
        th { background: $primaryColor; color: white; padding: 12px 8px; font-weight: bold; text-align: left; border: 1px solid #000; }
        td { padding: 10px 8px; border: 1px solid #ddd; }
        tr:nth-child(even) { background: #f8f9fa; }
        .signature-section { margin-top: 40px; padding: 20px 0; }
        .signature-row { display: flex; justify-content: space-between; margin: 30px 0; }
        .signature-box { text-align: center; width: 200px; }
        .signature-line { border-bottom: 1px solid #333; margin-bottom: 5px; height: 40px; }
        @media print { 
            body { margin: 0; padding: 15px; }
            .page-break { page-break-before: always; }
        }
    </style>";
}

function generateStudentListReport($conn, $schoolId, $reportsDir) {
    try {
        $sql = "SELECT u.id as student_id, u.ADM as admission_number, u.username, u.email, u.phone_number, 
                       u.parent_name as guardian_name, u.parent_phone as guardian_phone, u.parent_email as guardian_email,
                       c.class_name, s.stream_name
                FROM users u
                LEFT JOIN classes c ON u.class_id = c.class_id
                LEFT JOIN streams s ON u.stream_id = s.stream_id
                WHERE u.school_id = ? AND u.role = 3 AND u.status = 'active'
                ORDER BY c.class_name, s.stream_name, u.username";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Database prepare failed: ' . $conn->error);
        }
        
        $stmt->bind_param("i", $schoolId);
        
        if (!$stmt->execute()) {
            throw new Exception('Database execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $students = [];
        while ($row = $result->fetch_assoc()) {
            $students[] = $row;
        }
        
        $stmt->close();
        
        if (empty($students)) {
            return [
                'success' => false,
                'message' => 'No students found for this school'
            ];
        }
        
        // Generate Excel file
        $excelFile = generateStudentListExcel($students, $reportsDir, 'Complete Student List');
        
        // Generate HTML for PDF conversion
        $htmlFile = generateStudentListHTML($students, $reportsDir, 'Complete Student List');
        
        return [
            'success' => true,
            'message' => 'Report generated successfully',
            'excel_file' => $excelFile,
            'pdf_file' => $htmlFile
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to generate student list: ' . $e->getMessage()
        ];
    }
}

function generateStudentByClassReport($conn, $schoolId, $reportsDir) {
    try {
        $sql = "SELECT u.id as student_id, u.ADM as admission_number, u.username, u.email,
                       c.class_name, s.stream_name
                FROM users u
                LEFT JOIN classes c ON u.class_id = c.class_id
                LEFT JOIN streams s ON u.stream_id = s.stream_id
                WHERE u.school_id = ? AND u.role = 3 AND u.status = 'active'
                ORDER BY c.class_name, s.stream_name, u.username";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Database prepare failed: ' . $conn->error);
        }
        
        $stmt->bind_param("i", $schoolId);
        
        if (!$stmt->execute()) {
            throw new Exception('Database execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $students = [];
        while ($row = $result->fetch_assoc()) {
            $classStream = ($row['class_name'] ?? 'No Class') . ' - ' . ($row['stream_name'] ?? 'No Stream');
            $students[$classStream][] = $row;
        }
        
        $stmt->close();
        
        if (empty($students)) {
            return [
                'success' => false,
                'message' => 'No students found for this school'
            ];
        }
        
        // Generate Excel file
        $excelFile = generateStudentByClassExcel($students, $reportsDir);
        
        // Generate HTML for PDF conversion
        $htmlFile = generateStudentByClassHTML($students, $reportsDir);
        
        return [
            'success' => true,
            'message' => 'Report generated successfully',
            'excel_file' => $excelFile,
            'pdf_file' => $htmlFile
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to generate student by class report: ' . $e->getMessage()
        ];
    }
}

function generateTeacherListReport($conn, $schoolId, $reportsDir) {
    try {
        $sql = "SELECT u.id as teacher_id, u.username, u.email, u.phone_number, 
                       u.created_at as hire_date, u.status
                FROM users u
                WHERE u.school_id = ? AND u.role = 2 AND u.status = 'active'
                ORDER BY u.username";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Database prepare failed: ' . $conn->error);
        }
        
        $stmt->bind_param("i", $schoolId);
        
        if (!$stmt->execute()) {
            throw new Exception('Database execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $teachers = [];
        while ($row = $result->fetch_assoc()) {
            $teachers[] = $row;
        }
        
        $stmt->close();
        
        if (empty($teachers)) {
            return [
                'success' => false,
                'message' => 'No teachers found for this school'
            ];
        }
        
        // Generate Excel file
        $excelFile = generateTeacherListExcel($teachers, $reportsDir);
        
        // Generate HTML for PDF conversion
        $htmlFile = generateTeacherListHTML($teachers, $reportsDir);
        
        return [
            'success' => true,
            'message' => 'Report generated successfully',
            'excel_file' => $excelFile,
            'pdf_file' => $htmlFile
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to generate teacher list: ' . $e->getMessage()
        ];
    }
}

function generateReportCardsReport($conn, $schoolId, $reportsDir) {
    try {
        $sql = "SELECT u.id as student_id, u.ADM as admission_number, u.username as student_name,
                       c.class_name, s.stream_name, e.exam_name, e.term, e.year,
                       sub.subject_name, m.mark, gs.grade_letter, gs.remarks
                FROM users u
                LEFT JOIN classes c ON u.class_id = c.class_id
                LEFT JOIN streams s ON u.stream_id = s.stream_id
                LEFT JOIN marks m ON u.id = m.student_id
                LEFT JOIN exams e ON m.exam_id = e.exam_id
                LEFT JOIN subjects sub ON m.subject_id = sub.subject_id
                LEFT JOIN grading_scales gs ON m.mark BETWEEN gs.min_score AND gs.max_score 
                    AND gs.school_id = ? AND gs.academic_year = e.year AND gs.term_name = e.term
                WHERE u.school_id = ? AND u.role = 3 AND u.status = 'active' AND e.status = 'Published'
                ORDER BY u.username, e.exam_name, sub.subject_name";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Database prepare failed: ' . $conn->error);
        }
        
        $stmt->bind_param("ii", $schoolId, $schoolId);
        
        if (!$stmt->execute()) {
            throw new Exception('Database execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $reportCards = [];
        while ($row = $result->fetch_assoc()) {
            if ($row['exam_name']) { // Only include rows with exam data
                $studentKey = $row['student_id'] . '_' . $row['exam_name'];
                $reportCards[$studentKey]['student_info'] = [
                    'name' => $row['student_name'],
                    'admission_number' => $row['admission_number'],
                    'class' => $row['class_name'],
                    'stream' => $row['stream_name'],
                    'exam' => $row['exam_name'],
                    'term' => $row['term'],
                    'year' => $row['year']
                ];
                $reportCards[$studentKey]['subjects'][] = [
                    'subject' => $row['subject_name'],
                    'mark' => $row['mark'],
                    'grade' => $row['grade_letter'],
                    'remarks' => $row['remarks']
                ];
            }
        }
        
        $stmt->close();
        
        if (empty($reportCards)) {
            return [
                'success' => false,
                'message' => 'No published exam results found for report cards'
            ];
        }
        
        // Generate Excel file
        $excelFile = generateReportCardsExcel($reportCards, $reportsDir);
        
        // Generate HTML for PDF conversion
        $htmlFile = generateReportCardsHTML($reportCards, $reportsDir);
        
        return [
            'success' => true,
            'message' => 'Report cards generated successfully',
            'excel_file' => $excelFile,
            'pdf_file' => $htmlFile
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to generate report cards: ' . $e->getMessage()
        ];
    }
}

// Additional report generation functions for missing cases
function generateStudentContactsReport($conn, $schoolId, $reportsDir) {
    try {
        $sql = "SELECT u.id as student_id, u.ADM as admission_number, u.username, u.email, u.phone_number, 
                       u.parent_name as guardian_name, u.parent_phone as guardian_phone, u.parent_email as guardian_email,
                       c.class_name, s.stream_name
                FROM users u
                LEFT JOIN classes c ON u.class_id = c.class_id
                LEFT JOIN streams s ON u.stream_id = s.stream_id
                WHERE u.school_id = ? AND u.role = 3 AND u.status = 'active'
                ORDER BY c.class_name, s.stream_name, u.username";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception('Database prepare failed: ' . $conn->error);
        }
        
        $stmt->bind_param("i", $schoolId);
        
        if (!$stmt->execute()) {
            throw new Exception('Database execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $students = [];
        while ($row = $result->fetch_assoc()) {
            $students[] = $row;
        }
        
        $stmt->close();
        
        if (empty($students)) {
            return [
                'success' => false,
                'message' => 'No student contact information found'
            ];
        }
        
        // Generate Excel file
        $excelFile = generateStudentContactsExcel($students, $reportsDir);
        
        // Generate HTML for PDF conversion
        $htmlFile = generateStudentContactsHTML($students, $reportsDir);
        
        return [
            'success' => true,
            'message' => 'Student contacts report generated successfully',
            'excel_file' => $excelFile,
            'pdf_file' => $htmlFile
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => 'Failed to generate student contacts report: ' . $e->getMessage()
        ];
    }
}

function generateTeacherSubjectsReport($conn, $schoolId, $reportsDir) {
    return [
        'success' => false,
        'message' => 'Teacher subjects report is not yet implemented. Please use the basic teacher list for now.'
    ];
}

function generateTeacherWorkloadReport($conn, $schoolId, $reportsDir) {
    return [
        'success' => false,
        'message' => 'Teacher workload report is not yet implemented. Please use the basic teacher list for now.'
    ];
}

function generateClassListReport($conn, $schoolId, $reportsDir) {
    // This will be the same as student by class
    return generateStudentByClassReport($conn, $schoolId, $reportsDir);
}

function generateSubjectAllocationReport($conn, $schoolId, $reportsDir) {
    return [
        'success' => false,
        'message' => 'Subject allocation report is not yet implemented.'
    ];
}

function generateExamResultsReport($conn, $schoolId, $reportsDir) {
    // This will be similar to report cards but focused on exam results
    return generateReportCardsReport($conn, $schoolId, $reportsDir);
}

function generateGradeAnalysisReport($conn, $schoolId, $reportsDir) {
    return [
        'success' => false,
        'message' => 'Grade analysis report is not yet implemented.'
    ];
}

function generateTranscriptsReport($conn, $schoolId, $reportsDir) {
    return [
        'success' => false,
        'message' => 'Transcripts report is not yet implemented.'
    ];
}

// Excel generation functions
function generateStudentListExcel($students, $reportsDir, $title) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Student List');
    
    // Set headers
    $headers = ['#', 'Admission No.', 'Student Name', 'Email', 'Phone', 'Class', 'Stream', 'Guardian Name', 'Guardian Phone'];
    $sheet->fromArray($headers, null, 'A1');
    
    // Style headers
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'fill' => ['fillType' => 'solid', 'color' => ['rgb' => '366092']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->getStyle('A1:I1')->applyFromArray($headerStyle);
    
    // Add data
    $row = 2;
    foreach ($students as $index => $student) {
        $sheet->setCellValue('A' . $row, $index + 1);
        $sheet->setCellValue('B' . $row, $student['admission_number'] ?? '');
        $sheet->setCellValue('C' . $row, $student['username'] ?? '');
        $sheet->setCellValue('D' . $row, $student['email'] ?? '');
        $sheet->setCellValue('E' . $row, $student['phone_number'] ?? '');
        $sheet->setCellValue('F' . $row, $student['class_name'] ?? '');
        $sheet->setCellValue('G' . $row, $student['stream_name'] ?? '');
        $sheet->setCellValue('H' . $row, $student['guardian_name'] ?? '');
        $sheet->setCellValue('I' . $row, $student['guardian_phone'] ?? '');
        $row++;
    }
    
    // Auto-size columns
    foreach (range('A', 'I') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = $reportsDir . 'student_list_' . date('Y-m-d_H-i-s') . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
    
    return $filename;
}

function generateStudentByClassExcel($students, $reportsDir) {
    $spreadsheet = new Spreadsheet();
    $sheetIndex = 0;
    
    foreach ($students as $classStream => $classStudents) {
        if ($sheetIndex > 0) {
            $spreadsheet->createSheet();
        }
        $sheet = $spreadsheet->setActiveSheetIndex($sheetIndex);
        $sheet->setTitle(substr($classStream, 0, 31)); // Excel sheet name limit
        
        // Set headers
        $headers = ['#', 'Admission No.', 'Student Name', 'Email'];
        $sheet->fromArray($headers, null, 'A1');
        
        // Style headers
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => 'solid', 'color' => ['rgb' => '366092']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
        ];
        $sheet->getStyle('A1:D1')->applyFromArray($headerStyle);
        
        // Add data
        $row = 2;
        foreach ($classStudents as $index => $student) {
            $sheet->setCellValue('A' . $row, $index + 1);
            $sheet->setCellValue('B' . $row, $student['admission_number'] ?? '');
            $sheet->setCellValue('C' . $row, $student['username'] ?? '');
            $sheet->setCellValue('D' . $row, $student['email'] ?? '');
            $row++;
        }
        
        // Auto-size columns
        foreach (range('A', 'D') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        
        $sheetIndex++;
    }
    
    $filename = $reportsDir . 'students_by_class_' . date('Y-m-d_H-i-s') . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
    
    return $filename;
}

function generateTeacherListExcel($teachers, $reportsDir) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Teacher List');
    
    // Set headers
    $headers = ['#', 'Teacher ID', 'Teacher Name', 'Email', 'Phone', 'Hire Date', 'Status'];
    $sheet->fromArray($headers, null, 'A1');
    
    // Style headers
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'fill' => ['fillType' => 'solid', 'color' => ['rgb' => '366092']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->getStyle('A1:G1')->applyFromArray($headerStyle);
    
    // Add data
    $row = 2;
    foreach ($teachers as $index => $teacher) {
        $sheet->setCellValue('A' . $row, $index + 1);
        $sheet->setCellValue('B' . $row, $teacher['teacher_id'] ?? '');
        $sheet->setCellValue('C' . $row, $teacher['username'] ?? '');
        $sheet->setCellValue('D' . $row, $teacher['email'] ?? '');
        $sheet->setCellValue('E' . $row, $teacher['phone_number'] ?? '');
        $sheet->setCellValue('F' . $row, $teacher['hire_date'] ?? '');
        $sheet->setCellValue('G' . $row, $teacher['status'] ?? '');
        $row++;
    }
    
    // Auto-size columns
    foreach (range('A', 'G') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = $reportsDir . 'teacher_list_' . date('Y-m-d_H-i-s') . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
    
    return $filename;
}

function generateStudentContactsExcel($students, $reportsDir) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Student Contacts');
    
    // Set headers
    $headers = ['#', 'Admission No.', 'Student Name', 'Student Email', 'Student Phone', 'Class', 'Stream', 'Guardian Name', 'Guardian Phone', 'Guardian Email'];
    $sheet->fromArray($headers, null, 'A1');
    
    // Style headers
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'fill' => ['fillType' => 'solid', 'color' => ['rgb' => '366092']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->getStyle('A1:J1')->applyFromArray($headerStyle);
    
    // Add data
    $row = 2;
    foreach ($students as $index => $student) {
        $sheet->setCellValue('A' . $row, $index + 1);
        $sheet->setCellValue('B' . $row, $student['admission_number'] ?? '');
        $sheet->setCellValue('C' . $row, $student['username'] ?? '');
        $sheet->setCellValue('D' . $row, $student['email'] ?? '');
        $sheet->setCellValue('E' . $row, $student['phone_number'] ?? '');
        $sheet->setCellValue('F' . $row, $student['class_name'] ?? '');
        $sheet->setCellValue('G' . $row, $student['stream_name'] ?? '');
        $sheet->setCellValue('H' . $row, $student['guardian_name'] ?? '');
        $sheet->setCellValue('I' . $row, $student['guardian_phone'] ?? '');
        $sheet->setCellValue('J' . $row, $student['guardian_email'] ?? '');
        $row++;
    }
    
    // Auto-size columns
    foreach (range('A', 'J') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = $reportsDir . 'student_contacts_' . date('Y-m-d_H-i-s') . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
    
    return $filename;
}

function generateReportCardsExcel($reportCards, $reportsDir) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Report Cards');
    
    // Set headers
    $headers = ['Student Name', 'Admission No.', 'Class', 'Stream', 'Exam', 'Term', 'Year', 'Subject', 'Mark', 'Grade', 'Remarks'];
    $sheet->fromArray($headers, null, 'A1');
    
    // Style headers
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'fill' => ['fillType' => 'solid', 'color' => ['rgb' => '366092']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->getStyle('A1:K1')->applyFromArray($headerStyle);
    
    // Add data
    $row = 2;
    foreach ($reportCards as $reportCard) {
        $studentInfo = $reportCard['student_info'];
        foreach ($reportCard['subjects'] as $subject) {
            $sheet->setCellValue('A' . $row, $studentInfo['name']);
            $sheet->setCellValue('B' . $row, $studentInfo['admission_number']);
            $sheet->setCellValue('C' . $row, $studentInfo['class']);
            $sheet->setCellValue('D' . $row, $studentInfo['stream']);
            $sheet->setCellValue('E' . $row, $studentInfo['exam']);
            $sheet->setCellValue('F' . $row, $studentInfo['term']);
            $sheet->setCellValue('G' . $row, $studentInfo['year']);
            $sheet->setCellValue('H' . $row, $subject['subject']);
            $sheet->setCellValue('I' . $row, $subject['mark']);
            $sheet->setCellValue('J' . $row, $subject['grade']);
            $sheet->setCellValue('K' . $row, $subject['remarks']);
            $row++;
        }
    }
    
    // Auto-size columns
    foreach (range('A', 'K') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    $filename = $reportsDir . 'report_cards_' . date('Y-m-d_H-i-s') . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
    
    return $filename;
}

// HTML generation functions for PDF
function generateStudentListHTML($students, $reportsDir, $title) {
    global $conn;
    
    // Get school information
    $schoolInfo = getSchoolInfo($conn, $_SESSION['school_id']);
    
    $html = "
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset='UTF-8'>
        <title>$title</title>
        <style>
            body { font-family: 'Times New Roman', Times, serif; margin: 20px; }
            .print-date { text-align: right; margin-bottom: 20px; font-size: 12px; color: #666; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; border: 2px solid #000000; }
            th, td { border: 1px solid #000000; padding: 8px 6px; text-align: left; font-size: 12px; }
            th { background-color: #f0f0f0; font-weight: bold; font-size: 12px; }
            tr:nth-child(even) { background-color: #f8f8f8; }
            /* Column widths for compressed layout */
            th:nth-child(1), td:nth-child(1) { width: 15%; } /* Admission Number */
            th:nth-child(2), td:nth-child(2) { width: 35%; } /* Student Name */
            th:nth-child(3), td:nth-child(3) { width: 15%; } /* Class */
            th:nth-child(4), td:nth-child(4) { width: 15%; } /* Stream */
            th:nth-child(5), td:nth-child(5) { width: 20%; } /* Empty column */
            /* Page break styles for different classes */
            .class-section { page-break-after: always; }
            .class-section:last-child { page-break-after: auto; }
            @media print {
                .class-section { page-break-after: always; }
                .class-section:last-child { page-break-after: auto; }
            }
        </style>
    </head>
    <body>
        <div class='print-date'>Generated on: " . date('Y-m-d H:i:s') . "</div>";
    
    // Add school header with logo
    $html .= generateSchoolHeaderWithLogo($schoolInfo, $title);
    
    $html .= "
        <div style='text-align: center; margin: 20px 0;'>
            <h2 style='color: #000; font-size: 18px; font-weight: bold; text-transform: uppercase; margin: 0;'>Complete Students List</h2>
        </div>
        <table>
            <thead>
                <tr>
                    <th style=\"border: 1px solid #000000;\">Admission Number</th>
                    <th style=\"border: 1px solid #000000;\">Student Name</th>
                    <th style=\"border: 1px solid #000000;\">Class</th>
                    <th style=\"border: 1px solid #000000;\">Stream</th>
                    <th style=\"border: 1px solid #000000;\"></th>
                </tr>
            </thead>
            <tbody>";
    
    foreach ($students as $index => $student) {
        $html .= "<tr>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['admission_number'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['username'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['class_name'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['stream_name'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\"></td>";
        $html .= "</tr>";
    }
    
    $html .= "
            </tbody>
        </table>
        <div style='margin-top: 30px; text-align: center; font-size: 12px; color: #666;'>
            Total Students: " . count($students) . "
        </div>
    </body>
    </html>";
    
    $filename = $reportsDir . 'student_list_' . date('Y-m-d_H-i-s') . '.html';
    file_put_contents($filename, $html);
    
    return $filename;
}

function generateStudentContactsHTML($students, $reportsDir) {
    global $conn;
    
    // Get school information
    $schoolInfo = getSchoolInfo($conn, $_SESSION['school_id']);
    
    $html = "
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset='UTF-8'>
        <title>Student Contact Information</title>
        <style>
            body { font-family: 'Times New Roman', Times, serif; margin: 20px; }
            .print-date { text-align: right; margin-bottom: 20px; font-size: 12px; color: #666; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; border: 2px solid #000000; }
            th, td { border: 1px solid #000000; padding: 8px 6px; text-align: left; font-size: 12px; }
            th { background-color: #f0f0f0; font-weight: bold; font-size: 12px; }
            tr:nth-child(even) { background-color: #f8f8f8; }
            /* Column widths for compressed layout */
            th:nth-child(1), td:nth-child(1) { width: 8%; } /* # */
            th:nth-child(2), td:nth-child(2) { width: 15%; } /* Admission No. */
            th:nth-child(3), td:nth-child(3) { width: 20%; } /* Student Name */
            th:nth-child(4), td:nth-child(4) { width: 15%; } /* Student Email */
            th:nth-child(5), td:nth-child(5) { width: 12%; } /* Student Phone */
            th:nth-child(6), td:nth-child(6) { width: 10%; } /* Class */
            th:nth-child(7), td:nth-child(7) { width: 10%; } /* Stream */
            th:nth-child(8), td:nth-child(8) { width: 15%; } /* Guardian Name */
            th:nth-child(9), td:nth-child(9) { width: 12%; } /* Guardian Phone */
            th:nth-child(10), td:nth-child(10) { width: 13%; } /* Guardian Email */
        </style>
    </head>
    <body>
        <div class='print-date'>Generated on: " . date('Y-m-d H:i:s') . "</div>";
    
    // Add school header with logo
    $html .= generateSchoolHeaderWithLogo($schoolInfo, 'Student Contact Information');
    
    $html .= "
        <div style='text-align: center; margin: 20px 0;'>
            <h2 style='color: #000; font-size: 18px; font-weight: bold; text-transform: uppercase; margin: 0;'>Student Contact Information</h2>
        </div>
        <table>
            <thead>
                <tr>
                    <th style=\"border: 1px solid #000000;\">#</th>
                    <th style=\"border: 1px solid #000000;\">Admission No.</th>
                    <th style=\"border: 1px solid #000000;\">Student Name</th>
                    <th style=\"border: 1px solid #000000;\">Student Email</th>
                    <th style=\"border: 1px solid #000000;\">Student Phone</th>
                    <th style=\"border: 1px solid #000000;\">Class</th>
                    <th style=\"border: 1px solid #000000;\">Stream</th>
                    <th style=\"border: 1px solid #000000;\">Guardian Name</th>
                    <th style=\"border: 1px solid #000000;\">Guardian Phone</th>
                    <th style=\"border: 1px solid #000000;\">Guardian Email</th>
                </tr>
            </thead>
            <tbody>";
    
    foreach ($students as $index => $student) {
        $html .= "<tr>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . ($index + 1) . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['admission_number'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['username'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['email'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['phone_number'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['class_name'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['stream_name'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['guardian_name'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['guardian_phone'] ?? '') . "</td>";
        $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['guardian_email'] ?? '') . "</td>";
        $html .= "</tr>";
    }
    
    $html .= "
            </tbody>
        </table>
        <div style='margin-top: 30px; text-align: center; font-size: 12px; color: #666;'>
            Total Students: " . count($students) . "
        </div>
    </body>
    </html>";
    
    $filename = $reportsDir . 'student_contacts_' . date('Y-m-d_H-i-s') . '.html';
    file_put_contents($filename, $html);
    
    return $filename;
}

function generateStudentByClassHTML($students, $reportsDir) {
    global $conn;
    
    // Get school information
    $schoolInfo = getSchoolInfo($conn, $_SESSION['school_id']);
    $html = "
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset='UTF-8'>
        <title>Students by Class Report</title>
        <style>
            body { font-family: 'Times New Roman', Times, serif; margin: 20px; }
            .header { text-align: center; margin-bottom: 30px; }
            .header h1 { color: #333; margin-bottom: 5px; }
            .class-section { margin-bottom: 40px; page-break-inside: avoid; page-break-after: always; }
            .class-section:last-child { page-break-after: auto; }
            .class-title { background-color: #007bff; color: white; padding: 10px; margin-bottom: 10px; }
            table { width: 100%; border-collapse: collapse; margin-bottom: 20px; border: 2px solid #000000; }
            th, td { border: 1px solid #000000; padding: 8px 6px; text-align: left; font-size: 12px; }
            th { background-color: #f0f0f0; font-weight: bold; font-size: 12px; }
            tr:nth-child(even) { background-color: #f8f8f8; }
            .print-date { text-align: right; margin-bottom: 20px; font-size: 12px; color: #666; }
            /* Column widths for compressed layout */
            th:nth-child(1), td:nth-child(1) { width: 8%; } /* # */
            th:nth-child(2), td:nth-child(2) { width: 15%; } /* Admission No. */
            th:nth-child(3), td:nth-child(3) { width: 32%; } /* Student Name */
            th:nth-child(4), td:nth-child(4) { width: 15%; } /* Email */
            th:nth-child(5), td:nth-child(5) { width: 15%; } /* Date of Birth */
            th:nth-child(6), td:nth-child(6) { width: 10%; } /* Gender */
            th:nth-child(7), td:nth-child(7) { width: 5%; } /* Empty column */
            @media print {
                .class-section { page-break-inside: avoid; page-break-after: always; }
                .class-section:last-child { page-break-after: auto; }
            }
        </style>
    </head>
    <body>
        <div class='print-date'>Generated on: " . date('Y-m-d H:i:s') . "</div>";
    

    
    // Add school header with logo
    $html .= generateSchoolHeaderWithLogo($schoolInfo, 'Students by Class Report');
    
    $html .= "
        <div style='text-align: center; margin: 20px 0;'>
            <h2 style='color: #000; font-size: 18px; font-weight: bold; text-transform: uppercase; margin: 0;'>Students by Class Report</h2>
        </div>";
    
    foreach ($students as $classStream => $classStudents) {
        $html .= "<div class='class-section'>";
        $html .= "<h2 class='class-title'>$classStream (" . count($classStudents) . " students)</h2>";
        $html .= "<table>";
        $html .= "<thead><tr><th style=\"border: 1px solid #000000;\">#</th><th style=\"border: 1px solid #000000;\">Admission No.</th><th style=\"border: 1px solid #000000;\">Student Name</th><th style=\"border: 1px solid #000000;\">Email</th><th style=\"border: 1px solid #000000;\">Date of Birth</th><th style=\"border: 1px solid #000000;\">Gender</th><th style=\"border: 1px solid #000000;\"></th></tr></thead>";
        $html .= "<tbody>";
        
        foreach ($classStudents as $index => $student) {
            $html .= "<tr>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . ($index + 1) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['admission_number']) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['username']) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['email']) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['date_of_birth']) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\">" . htmlspecialchars($student['gender']) . "</td>";
            $html .= "<td style=\"border: 1px solid #000000;\"></td>";
            $html .= "</tr>";
        }
        
        $html .= "</tbody></table></div>";
    }
    
    $html .= "</body></html>";
    
    $filename = $reportsDir . 'students_by_class_' . date('Y-m-d_H-i-s') . '.html';
    file_put_contents($filename, $html);
    
    return $filename;
}

function generateTeacherListHTML($teachers, $reportsDir) {
    $html = "
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset='UTF-8'>
        <title>Teacher List Report</title>
        <style>
            body { font-family: 'Times New Roman', Times, serif; margin: 20px; }
            .header { text-align: center; margin-bottom: 30px; }
            .header h1 { color: #333; margin-bottom: 5px; }
            table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
            th { background-color: #f8f9fa; font-weight: bold; }
            tr:nth-child(even) { background-color: #f9f9f9; }
            .print-date { text-align: right; margin-bottom: 20px; font-size: 12px; color: #666; }
        </style>
    </head>
    <body>
        <div class='print-date'>Generated on: " . date('Y-m-d H:i:s') . "</div>
        <div class='header'>
            <h1>Teacher List Report</h1>
            <p>Complete teacher database report</p>
        </div>
        <table>
            <thead>
                <tr>
                    <th>#</th>
                    <th>Teacher ID</th>
                    <th>Teacher Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Hire Date</th>
                    <th>Status</th>
                </tr>
            </thead>
            <tbody>";
    
    foreach ($teachers as $index => $teacher) {
        $html .= "<tr>";
        $html .= "<td>" . ($index + 1) . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['teacher_id'] ?? '') . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['username'] ?? '') . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['email'] ?? '') . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['phone_number'] ?? '') . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['hire_date'] ?? '') . "</td>";
        $html .= "<td>" . htmlspecialchars($teacher['status'] ?? '') . "</td>";
        $html .= "</tr>";
    }
    
    $html .= "
            </tbody>
        </table>
        <div style='margin-top: 30px; text-align: center; font-size: 12px; color: #666;'>
            Total Teachers: " . count($teachers) . "
        </div>
    </body>
    </html>";
    
    $filename = $reportsDir . 'teacher_list_' . date('Y-m-d_H-i-s') . '.html';
    file_put_contents($filename, $html);
    
    return $filename;
}

// Function to generate school header HTML with logo
function generateSchoolHeaderWithLogo($schoolInfo, $reportTitle = '') {
    $html = "
    <div style='display: flex; justify-content: space-between; align-items: flex-start; margin-bottom: 20px; padding-bottom: 15px; border-bottom: 3px solid #000000;'>
        <div style='margin-right: 20px;'>";
    
    // Add school logo if available
    if (!empty($schoolInfo['logo']) && file_exists($schoolInfo['logo'])) {
        // Convert logo to base64 for proper display in downloads
        $logoData = base64_encode(file_get_contents($schoolInfo['logo']));
        $logoType = pathinfo($schoolInfo['logo'], PATHINFO_EXTENSION);
        $html .= "<img src='data:image/" . $logoType . ";base64," . $logoData . "' alt='School Logo' style='width: 80px; height: 80px; border: none; border-radius: 0; object-fit: cover;'>";
    } else {
        $html .= "<div style='width: 80px; height: 80px; border: none; border-radius: 0; background: #f0f0f0; display: flex; align-items: center; justify-content: center; font-size: 12px; color: #666;'>LOGO</div>";
    }
    
    $html .= "
        </div>
        <div style='text-align: right; flex: 1;'>
            <div style='font-size: 28px; font-weight: bold; color: #000; margin: 0;'>" . htmlspecialchars($schoolInfo['school_name']) . "</div>";
    
    if (!empty($schoolInfo['motto'])) {
        $html .= "<div style='font-style: italic; color: #000; margin: 5px 0; font-size: 14px;'>" . htmlspecialchars($schoolInfo['motto']) . "</div>";
    }
    if (!empty($schoolInfo['email'])) {
        $html .= "<div style='font-size: 14px; color: #000; margin: 5px 0;'>" . htmlspecialchars($schoolInfo['email']) . "</div>";
    }
    
    if (!empty($reportTitle)) {
        $html .= "<div style='font-size: 18px; font-weight: bold; color: #2E8B57; margin-top: 10px; text-transform: uppercase;'>" . htmlspecialchars($reportTitle) . "</div>";
    }
    
    $html .= "
        </div>
    </div>";
    
    return $html;
}

// Function to generate performance comments based on average score
function generatePerformanceComment($averageScore) {
    if ($averageScore >= 90) {
        return "Outstanding performance! Keep up the excellent work. You have demonstrated exceptional understanding across all subjects.";
    } elseif ($averageScore >= 80) {
        return "Very good performance! You are doing well. Continue to focus on areas that need improvement to achieve even better results.";
    } elseif ($averageScore >= 70) {
        return "Good performance overall. There's room for improvement in some areas. Please seek help from your teachers where needed.";
    } elseif ($averageScore >= 60) {
        return "Fair performance. You need to put in more effort and seek additional support to improve your academic standing.";
    } elseif ($averageScore >= 50) {
        return "Below average performance. Immediate attention and extra effort required. Please work closely with your teachers.";
    } else {
        return "Poor performance. Urgent intervention needed. Please meet with your academic advisor to develop an improvement plan.";
    }
}

function generateReportCardsHTML($reportCards, $reportsDir) {
    global $conn;
    
    // Get school information
    $schoolInfo = getSchoolInfo($conn, $_SESSION['school_id']);
    
    $html = "
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset='UTF-8'>
        <title>Academic Report Form</title>
        <script src='https://cdn.jsdelivr.net/npm/chart.js'></script>
        <script src='https://cdn.jsdelivr.net/npm/qrcode@1.5.3/build/qrcode.min.js'></script>
        <style>
            @page { 
                size: A4; 
                margin: 0; 
            }
            body { 
                font-family: 'Times New Roman', Times, serif; 
                margin: 0; 
                padding: 0; 
                background: white; 
                font-size: 11px;
                line-height: 1.3;
            }
            .page-container {
                width: 210mm;
                min-height: 297mm;
                position: relative;
                margin: 0 auto;
                background: white;
                display: flex;
            }
            .green-margin {
                width: 15mm;
                background: linear-gradient(180deg, #228B22 0%, #32CD32 100%);
                position: relative;
                display: flex;
                flex-direction: column;
                align-items: center;
                padding-top: 8mm;
            }
            .margin-logo {
                width: 12mm;
                height: 12mm;
                background: white;
                border-radius: 0;
                display: flex;
                align-items: center;
                justify-content: center;
                margin-bottom: 3mm;
                box-shadow: 0 2px 5px rgba(0,0,0,0.3);
            }
            .margin-logo img {
                width: 10mm;
                height: 10mm;
                border: none;
                border-radius: 0;
                object-fit: cover;
            }
            .main-content {
                flex: 1;
                padding: 0;
                position: relative;
                background: white;
            }
            .header-banner {
                background: #0066CC;
                color: white;
                padding: 8px 15px;
                text-align: center;
                font-weight: bold;
                font-size: 12px;
                text-transform: uppercase;
                letter-spacing: 0.5px;
            }
            .content-area {
                padding: 10mm 15mm 10mm 10mm;
                position: relative;
            }
            .top-section {
                display: flex;
                gap: 15mm;
                margin-bottom: 8mm;
                align-items: flex-start;
            }
            .student-photo {
                width: 22mm;
                height: 28mm;
                border: none;
                background: #f5f5f5;
                display: flex;
                align-items: center;
                justify-content: center;
                flex-shrink: 0;
                overflow: hidden;
            }
            .student-photo img {
                width: 100%;
                height: 100%;
                object-fit: cover;
            }
            .student-photo-placeholder {
                color: #666;
                font-size: 9px;
                text-align: center;
                padding: 5px;
            }
            .performance-charts {
                flex: 1;
                height: 100px;
                background: #f9f9f9;
                border: 1px solid #ddd;
                position: relative;
                display: flex;
                align-items: center;
                justify-content: center;
            }
            .chart-container {
                width: 100%;
                height: 90px;
                position: relative;
            }
            .results-table {
                width: 100%;
                border-collapse: collapse;
                margin: 8mm 0;
                font-size: 10px;
            }
            .results-table th {
                background: #f0f0f0;
                border: 1px solid #333;
                padding: 4px 6px;
                text-align: center;
                font-weight: bold;
            }
            .results-table td {
                border: 1px solid #333;
                padding: 4px 6px;
                text-align: center;
            }
            .main-content-grid {
                display: flex;
                gap: 10mm;
                margin: 8mm 0;
                position: relative;
            }
            .subjects-performance {
                width: 40mm;
            }
            .subject-item {
                display: flex;
                align-items: center;
                margin-bottom: 3mm;
                font-size: 9px;
            }
            .performance-dot {
                width: 8px;
                height: 8px;
                border-radius: 50%;
                margin-right: 6px;
                flex-shrink: 0;
            }
            .dot-excellent { background: #00AA00; }
            .dot-good { background: #FFAA00; }
            .dot-average { background: #FF6600; }
            .dot-poor { background: #FF0000; }
            .center-content {
                flex: 1;
                position: relative;
                min-height: 120mm;
            }
            .large-school-logo {
                position: absolute;
                top: 50%;
                left: 50%;
                transform: translate(-50%, -50%);
                width: 80mm;
                height: 80mm;
                opacity: 0.15;
                z-index: 1;
                display: flex;
                align-items: center;
                justify-content: center;
            }
            .large-school-logo img {
                width: 100%;
                height: 100%;
                object-fit: contain;
            }
            .large-logo-placeholder {
                width: 100%;
                height: 100%;
                border: none;
                border-radius: 0;
                display: flex;
                align-items: center;
                justify-content: center;
                color: #999;
                font-size: 14px;
                font-weight: bold;
            }
            .grading-scale {
                width: 30mm;
                background: #f9f9f9;
                border: 1px solid #ddd;
                padding: 8px;
                font-size: 8px;
            }
            .scale-title {
                font-weight: bold;
                margin-bottom: 5px;
                text-align: center;
            }
            .scale-item {
                display: flex;
                justify-content: space-between;
                margin-bottom: 2px;
            }
            .footer-area {
                position: absolute;
                bottom: 8mm;
                left: 25mm;
                right: 15mm;
                display: flex;
                justify-content: center;
                align-items: center;
                flex-direction: column;
                gap: 8px;
            }
            .qr-code {
                width: 50px;
                height: 50px;
                border: 1px solid #ccc;
            }
            .school-motto {
                font-size: 10px;
                font-weight: bold;
                color: #228B22;
                text-align: center;
            }
            .report-card { 
                margin-bottom: 20mm; 
                page-break-inside: avoid; 
                background: white; 
            }
            @media print { 
                body { margin: 0; padding: 0; }
                .page-container { page-break-after: always; }
                .report-card:last-child .page-container { page-break-after: avoid; }
            }
        </style>
    </head>
    <body>";
    
    foreach ($reportCards as $index => $reportCard) {
        $studentId = $reportCard['student_id'];
        
        // Get student photo path (assuming photos are stored in uploads/student_photos/)
        $studentPhoto = "uploads/student_photos/" . $studentId . ".jpg";
        if (!file_exists($studentPhoto)) {
            $studentPhoto = null; // No photo available
        }
        
        // Calculate total possible marks and current total
        $totalMarks = 0;
        $maxMarks = 0;
        $subjectCount = 0;
        
        if (isset($reportCard['subjects']) && is_array($reportCard['subjects'])) {
            foreach ($reportCard['subjects'] as $subject) {
                $totalMarks += intval($subject['marks']);
                $maxMarks += 100; // Assuming each subject is out of 100
                $subjectCount++;
            }
        }
        
        $html .= "
        <div class='report-card'>
            <div class='page-container'>
                <div class='green-margin'>
                    <div class='margin-logo'>";
        
        // Add school logo if available
        if (!empty($schoolInfo['logo']) && file_exists($schoolInfo['logo'])) {
            // Convert logo to base64 for proper display in downloads
            $logoData = base64_encode(file_get_contents($schoolInfo['logo']));
            $logoType = pathinfo($schoolInfo['logo'], PATHINFO_EXTENSION);
            $html .= "<img src='data:image/" . $logoType . ";base64," . $logoData . "' alt='School Logo'>";
        } else {
            $html .= "<div style='font-size: 7px; color: #666; text-align: center; font-weight: bold;'>LOGO</div>";
        }
        
        $html .= "
                    </div>
                </div>
                
                <div class='main-content'>
                    <div class='header-banner'>
                        ACADEMIC REPORT FORM - SENIOR 4 - ASSESSMENT 1 - (2025 TERM 2)
                    </div>
                    
                    <div class='content-area'>
                        <div class='top-section'>
                            <div class='student-photo'>";
        
        if ($studentPhoto && file_exists($studentPhoto)) {
            $html .= "<img src='" . htmlspecialchars($studentPhoto) . "' alt='Student Photo'>";
        } else {
            $html .= "<div class='student-photo-placeholder'>Student<br>Photo</div>";
        }
        
        $html .= "
                            </div>
                            
                            <div class='performance-charts'>
                                <div class='chart-container'>
                                    <canvas id='chart_" . $studentId . "' width='300' height='90'></canvas>
                                </div>
                            </div>
                        </div>
                        
                        <table class='results-table'>
                            <thead>
                                <tr>
                                    <th>Result</th>
                                    <th>Total Marks</th>
                                    <th>Average Score</th>
                                    <th>Result</th>
                                </tr>
                            </thead>
                            <tbody>
                                <tr>
                                    <td>" . number_format($reportCard['average_score'], 1) . "</td>
                                    <td>" . $totalMarks . "/" . $maxMarks . "</td>
                                    <td>" . number_format($reportCard['average_score'], 0) . "%</td>
                                    <td>" . number_format($reportCard['average_score'], 1) . "</td>
                                </tr>
                            </tbody>
                        </table>
                        
                        <div class='main-content-grid'>
                            <div class='subjects-performance'>
                                <div style='font-weight: bold; margin-bottom: 5mm; font-size: 9px;'>Subject Performance</div>";
        
        // Generate subject performance list with colored dots
        if (isset($reportCard['subjects']) && is_array($reportCard['subjects'])) {
            foreach ($reportCard['subjects'] as $subject) {
                $percentage = floatval($subject['percentage']);
                $dotClass = 'dot-poor'; // Default red
                
                if ($percentage >= 90) {
                    $dotClass = 'dot-excellent'; // Green
                } elseif ($percentage >= 75) {
                    $dotClass = 'dot-good'; // Yellow/Orange
                } elseif ($percentage >= 60) {
                    $dotClass = 'dot-average'; // Orange
                }
                
                $html .= "
                                <div class='subject-item'>
                                    <div class='performance-dot " . $dotClass . "'></div>
                                    <span>" . htmlspecialchars($subject['subject_name']) . "</span>
                                </div>";
            }
        }
        
        $html .= "
                            </div>
                            
                            <div class='center-content'>
                                <div class='large-school-logo'>";
        
        // Large school logo overlay
        if (!empty($schoolInfo['logo']) && file_exists($schoolInfo['logo'])) {
            // Convert logo to base64 for proper display in downloads
            $logoData = base64_encode(file_get_contents($schoolInfo['logo']));
            $logoType = pathinfo($schoolInfo['logo'], PATHINFO_EXTENSION);
            $html .= "<img src='data:image/" . $logoType . ";base64," . $logoData . "' alt='School Logo'>";
        } else {
            $html .= "<div class='large-logo-placeholder'>SCHOOL<br>LOGO</div>";
        }
        
        $html .= "
                                </div>
                            </div>
                            
                            <div class='grading-scale'>
                                <div class='scale-title'>Grading Scale</div>
                                <div class='scale-item'><span>A</span><span>90-100</span></div>
                                <div class='scale-item'><span>B</span><span>75-89</span></div>
                                <div class='scale-item'><span>C</span><span>60-74</span></div>
                                <div class='scale-item'><span>D</span><span>45-59</span></div>
                                <div class='scale-item'><span>E</span><span>0-44</span></div>
                            </div>
                        </div>
                    </div>
                    
                    <div class='footer-area'>
                        <div class='qr-code' id='qr_" . $studentId . "'></div>
                        <div class='school-motto'>School Motto: Due in Action</div>
                    </div>
                </div>
            </div>
        </div>";
    }
    
    // Add JavaScript for charts and QR codes
    $html .= "
    <script>
        // Generate performance charts and QR codes
        document.addEventListener('DOMContentLoaded', function() {";
    
    foreach ($reportCards as $index => $reportCard) {
        $studentId = $reportCard['student_id'];
        
        // Sample performance data for bar chart (you can modify this to use real assessment data)
        $performanceData = [65, 70, 68, 75, 72, 78, $reportCard['average_score']];
        $labels = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul'];
        
        $html .= "
            // Chart for student " . $studentId . "
            var ctx" . $studentId . " = document.getElementById('chart_" . $studentId . "').getContext('2d');
            new Chart(ctx" . $studentId . ", {
                type: 'bar',
                data: {
                    labels: " . json_encode($labels) . ",
                    datasets: [{
                        label: 'Performance %',
                        data: " . json_encode($performanceData) . ",
                        backgroundColor: [
                            '#e3f2fd', '#bbdefb', '#90caf9', '#64b5f6', 
                            '#42a5f5', '#2196f3', '#1976d2'
                        ],
                        borderColor: '#1976d2',
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    scales: {
                        y: {
                            beginAtZero: true,
                            max: 100,
                            ticks: { font: { size: 8 } }
                        },
                        x: {
                            ticks: { font: { size: 8 } }
                        }
                    },
                    plugins: {
                        legend: { display: false },
                        title: {
                            display: true,
                            text: 'Performance Trend',
                            font: { size: 10 }
                        }
                    }
                }
            });
            
            // Generate QR code for student " . $studentId . "
            QRCode.toCanvas(document.getElementById('qr_" . $studentId . "'), 
                'Student: " . htmlspecialchars($reportCard['student_name']) . " | Adm: " . htmlspecialchars($reportCard['admission_number']) . " | Score: " . number_format($reportCard['average_score'], 1) . "%', 
                { width: 50, height: 50, margin: 1 }
            );";
    }
    
    $html .= "
        });
    </script>
    </body></html>";
    
    $filename = $reportsDir . 'report_cards_' . date('Y-m-d_H-i-s') . '.html';
    file_put_contents($filename, $html);
    
    return $filename;
}


?>