<?php
$rootPath = defined('CIX_ROOT') ? CIX_ROOT : dirname(__DIR__, 2);

if (session_status() !== PHP_SESSION_ACTIVE) {
    session_name('cix_academy_session');
    session_start();
}

if (!function_exists('e')) {
    function e($str) {
        return htmlspecialchars((string)$str, ENT_QUOTES, 'UTF-8');
    }
}

function csrf_token() {
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}

function csrf_field() {
    return '<input type="hidden" name="csrf_token" value="' . csrf_token() . '">';
}

function verify_csrf($token) {
    return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
}

function redirect($url) {
    header('Location: ' . $url);
    exit;
}

function base_url($path = '') {
    return rtrim(defined('APP_URL') ? APP_URL : 'http://localhost', '/') . '/' . ltrim($path, '/');
}

function asset_url($path) {
    return base_url('public/assets/' . ltrim($path, '/'));
}

function storage_url($path) {
    return base_url(ltrim($path, '/'));
}

function upload_url($path) {
    return base_url('public/uploads/' . ltrim($path, '/'));
}

function old($key, $default = '') {
    return $_SESSION['old'][$key] ?? $default;
}

function flash($key, $value = null) {
    if ($value === null) {
        $msg = $_SESSION['flash'][$key] ?? null;
        unset($_SESSION['flash'][$key]);
        return $msg;
    }
    $_SESSION['flash'][$key] = $value;
}

function is_logged_in() {
    return !empty($_SESSION['user_id']);
}

function current_user() {
    static $user = null;
    if ($user === null && is_logged_in()) {
        global $pdo;
        $stmt = $pdo->prepare("SELECT u.*, r.name as role_name, r.slug as role_slug FROM users u JOIN roles r ON u.role_id = r.id WHERE u.id = ?");
        $stmt->execute([$_SESSION['user_id']]);
        $user = $stmt->fetch();
    }
    return $user;
}

function has_permission($slug) {
    $user = current_user();
    if (!$user) return false;
    
    global $pdo;
    static $permissions = null;
    
    if ($permissions === null) {
        $stmt = $pdo->prepare("SELECT p.slug FROM permissions p JOIN role_permissions rp ON p.id = rp.permission_id WHERE rp.role_id = ?");
        $stmt->execute([$user['role_id']]);
        $permissions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    
    return in_array($slug, $permissions);
}

function generate_student_number($pdo) {
    $year = date('Y');
    $stmt = $pdo->prepare("SELECT student_number FROM students WHERE student_number LIKE ? ORDER BY id DESC LIMIT 1");
    $stmt->execute(["COA-$year-%"]);
    $last = $stmt->fetchColumn();
    
    $num = 1;
    if ($last) {
        $parts = explode('-', $last);
        $num = (int)$parts[2] + 1;
    }
    return 'COA-' . $year . '-' . str_pad($num, 6, '0', STR_PAD_LEFT);
}

function generate_certificate_number($pdo) {
    $year = date('Y');
    $stmt = $pdo->prepare("SELECT certificate_number FROM certificates WHERE certificate_number LIKE ? ORDER BY id DESC LIMIT 1");
    $stmt->execute(["COA-CERT-$year-%"]);
    $last = $stmt->fetchColumn();
    
    $num = 1;
    if ($last) {
        $parts = explode('-', $last);
        $num = (int)$parts[3] + 1;
    }
    return 'COA-CERT-' . $year . '-' . str_pad($num, 6, '0', STR_PAD_LEFT);
}

function format_currency($amount, $currency = 'ZAR') {
    return $currency . ' ' . number_format((float)$amount, 2);
}

function time_elapsed($datetime, $full = false) {
    $now = new DateTime();
    $ago = new DateTime($datetime);
    $diff = $now->diff($ago);
    
    $weeks = (int)floor($diff->d / 7);
    $days = $diff->d - ($weeks * 7);
    
    $string = [
        'y' => 'year',
        'm' => 'month',
        'w' => 'week',
        'd' => 'day',
        'h' => 'hour',
        'i' => 'minute',
        's' => 'second',
    ];
    
    foreach ($string as $k => &$v) {
        $value = $k === 'w' ? $weeks : ($k === 'd' ? $days : $diff->$k);
        if ($value) {
            $v = $value . ' ' . $v . ($value > 1 ? 's' : '');
        } else {
            unset($string[$k]);
        }
    }
    
    if (!$full) $string = array_slice($string, 0, 1);
    return $string ? implode(', ', $string) . ' ago' : 'just now';
}

function log_activity($user_id, $type, $description, $metadata = null, $student_id = null) {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO activity_logs (user_id, student_id, activity_type, description, metadata, ip_address) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->execute([$user_id, $student_id, $type, $description, $metadata ? json_encode($metadata) : null, $_SERVER['REMOTE_ADDR'] ?? '']);
}

function log_audit($user_id, $action, $entity_type, $entity_id, $old_values = null, $new_values = null) {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values, new_values, ip_address, user_agent) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->execute([
        $user_id,
        $action,
        $entity_type,
        $entity_id,
        $old_values ? json_encode($old_values) : null,
        $new_values ? json_encode($new_values) : null,
        $_SERVER['REMOTE_ADDR'] ?? '',
        $_SERVER['HTTP_USER_AGENT'] ?? ''
    ]);
}

function send_email($to, $subject, $body, $from_name = null, $from_email = null) {
    global $pdo;
    
    $stmt = $pdo->query("SELECT * FROM smtp_settings WHERE is_active = 1 LIMIT 1");
    $smtp = $stmt->fetch();
    
    if (!$smtp) {
        error_log("No SMTP settings configured");
        return false;
    }
    
    $mail = new PHPMailer\PHPMailer\PHPMailer(true);
    
    try {
        $mail->isSMTP();
        $mail->Host = $smtp['host'];
        $mail->SMTPAuth = true;
        $mail->Username = $smtp['username'];
        $mail->Password = decrypt_secret($smtp['password_encrypted']);
        $mail->SMTPSecure = $smtp['encryption'] === 'ssl' ? PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS : PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port = $smtp['port'];
        
        $mail->setFrom($from_email ?: $smtp['from_email'], $from_name ?: $smtp['from_name']);
        $mail->addAddress($to);
        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body = $body;
        
        return $mail->send();
    } catch (Exception $e) {
        error_log("Email error: " . $e->getMessage());
        return false;
    }
}

function get_setting($key, $default = '') {
    global $pdo;
    static $cache = [];
    if (isset($cache[$key])) return $cache[$key];
    
    $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = ?");
    $stmt->execute([$key]);
    $val = $stmt->fetchColumn();
    $cache[$key] = $val !== false ? $val : $default;
    return $cache[$key];
}

function update_setting($key, $value) {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)");
    $stmt->execute([$key, $value]);
}

function encrypt_secret($value) {
    if ($value === '') return '';
    if (!defined('APP_KEY') || !function_exists('openssl_encrypt')) return $value;
    $key = hash('sha256', APP_KEY, true);
    $iv = random_bytes(16);
    $cipher = openssl_encrypt($value, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
    return 'enc:' . base64_encode($iv . $cipher);
}

function decrypt_secret($value) {
    if (!$value || !is_string($value) || !str_starts_with($value, 'enc:') || !defined('APP_KEY') || !function_exists('openssl_decrypt')) return $value;
    $raw = base64_decode(substr($value, 4), true);
    if ($raw === false || strlen($raw) <= 16) return '';
    $iv = substr($raw, 0, 16);
    $cipher = substr($raw, 16);
    $key = hash('sha256', APP_KEY, true);
    return openssl_decrypt($cipher, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv) ?: '';
}

function upload_file($file, $allowed_types, $upload_dir) {
    if (!isset($file['error']) || is_array($file['error'])) {
        return ['success' => false, 'error' => 'Invalid file upload'];
    }
    
    switch ($file['error']) {
        case UPLOAD_ERR_OK:
            break;
        case UPLOAD_ERR_NO_FILE:
            return ['success' => false, 'error' => 'No file uploaded'];
        case UPLOAD_ERR_INI_SIZE:
        case UPLOAD_ERR_FORM_SIZE:
            return ['success' => false, 'error' => 'File too large'];
        default:
            return ['success' => false, 'error' => 'Unknown error'];
    }
    
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, $allowed_types)) {
        return ['success' => false, 'error' => 'File type not allowed'];
    }
    if (function_exists('finfo_open')) {
        $allowedMimes = [
            'pdf' => ['application/pdf'],
            'jpg' => ['image/jpeg'],
            'jpeg' => ['image/jpeg'],
            'png' => ['image/png'],
            'doc' => ['application/msword', 'application/octet-stream'],
            'docx' => ['application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'application/zip', 'application/octet-stream'],
        ];
        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $mime = finfo_file($finfo, $file['tmp_name']);
        finfo_close($finfo);
        if (!empty($allowedMimes[$ext]) && !in_array($mime, $allowedMimes[$ext], true)) {
            return ['success' => false, 'error' => 'Uploaded file content does not match the allowed file type'];
        }
    }
    
    $filename = bin2hex(random_bytes(16)) . '.' . $ext;
    $path = $upload_dir . '/' . $filename;
    
    if (!move_uploaded_file($file['tmp_name'], $path)) {
        return ['success' => false, 'error' => 'Failed to move uploaded file'];
    }
    
    return ['success' => true, 'filename' => $filename, 'path' => $path];
}

function paginate($total, $perPage, $currentPage) {
    $pages = ceil($total / $perPage);
    $currentPage = max(1, min($currentPage, $pages));
    $offset = ($currentPage - 1) * $perPage;
    return [
        'total' => $total,
        'per_page' => $perPage,
        'current_page' => $currentPage,
        'total_pages' => $pages,
        'pages' => $pages,
        'offset' => $offset,
        'has_next' => $currentPage < $pages,
        'has_prev' => $currentPage > 1
    ];
}

// ============================================
// VALIDATION & SANITIZATION
// ============================================

function validate_email($email) {
    return filter_var(trim($email), FILTER_VALIDATE_EMAIL) !== false;
}

function sanitize_input($input) {
    if (is_array($input)) return sanitize_array($input);
    return htmlspecialchars(trim((string)$input), ENT_QUOTES, 'UTF-8');
}

function sanitize_array($array) {
    $clean = [];
    foreach ($array as $key => $value) {
        $key = htmlspecialchars((string)$key, ENT_QUOTES, 'UTF-8');
        $clean[$key] = is_array($value) ? sanitize_array($value) : htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
    return $clean;
}

function slugify($text) {
    $text = preg_replace('~[^\pL\d]+~u', '-', $text);
    $text = preg_replace('~[^-\w]+~', '', $text);
    $text = trim(trim($text, '-'), '-');
    return strtolower($text);
}

// ============================================
// SECURITY & TOKENS
// ============================================

function generate_token($length = 32) {
    return bin2hex(random_bytes((int)ceil($length / 2)));
}

function get_client_ip() {
    foreach (['HTTP_CLIENT_IP','HTTP_X_FORWARDED_FOR','HTTP_X_FORWARDED','REMOTE_ADDR'] as $h) {
        if (!empty($_SERVER[$h])) {
            $ip = trim(explode(',', $_SERVER[$h])[0]);
            if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) return $ip;
        }
    }
    return $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
}

function get_browser_info() {
    $ua = $_SERVER['HTTP_USER_AGENT'] ?? '';
    return ['user_agent' => $ua, 'ip' => get_client_ip(), 'timestamp' => date('Y-m-d H:i:s')];
}

function is_ajax_request() {
    return !empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
}

function json_response($data, $code = 200) {
    http_response_code($code);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
    exit;
}

function password_strength($password) {
    $strength = 0;
    if (strlen($password) >= 8) $strength++;
    if (preg_match('/[a-z]/', $password)) $strength++;
    if (preg_match('/[A-Z]/', $password)) $strength++;
    if (preg_match('/[0-9]/', $password)) $strength++;
    if (preg_match('/[^a-zA-Z0-9]/', $password)) $strength++;
    return min($strength, 5);
}

// ============================================
// DATE & TIME FORMATTING
// ============================================

function format_date($date, $format = 'd M Y') {
    if (empty($date)) return '';
    return (new DateTime($date))->format($format);
}

function format_datetime($datetime) {
    if (empty($datetime)) return '';
    return (new DateTime($datetime))->format('d M Y, H:i');
}

function time_ago($datetime) {
    $diff = (new DateTime())->diff(new DateTime($datetime));
    if ($diff->y > 0) return $diff->y . ' year' . ($diff->y > 1 ? 's' : '') . ' ago';
    if ($diff->m > 0) return $diff->m . ' month' . ($diff->m > 1 ? 's' : '') . ' ago';
    if ($diff->d > 0) return $diff->d . ' day' . ($diff->d > 1 ? 's' : '') . ' ago';
    if ($diff->h > 0) return $diff->h . ' hour' . ($diff->h > 1 ? 's' : '') . ' ago';
    if ($diff->i > 0) return $diff->i . ' minute' . ($diff->i > 1 ? 's' : '') . ' ago';
    return 'just now';
}

function get_month_name($month) {
    $months = ['','January','February','March','April','May','June','July','August','September','October','November','December'];
    return $months[(int)$month] ?? '';
}

function get_academic_years() {
    $current = (int)date('Y');
    $years = [];
    for ($y = $current; $y >= $current - 5; $y--) {
        $years[] = $y . '/' . ($y + 1);
    }
    return $years;
}

// ============================================
// TEXT HELPERS
// ============================================

function truncate_text($text, $length = 100) {
    if (strlen($text) <= $length) return $text;
    return substr($text, 0, $length) . '...';
}

function nl2br_plus($text) {
    $text = nl2br(e($text));
    $text = preg_replace('/(https?:\/\/[^\s<]+)/', '<a href="$1" target="_blank" rel="noopener">$1</a>', $text);
    return $text;
}

function get_initials($name) {
    $parts = explode(' ', trim($name));
    $initials = '';
    foreach (array_slice($parts, 0, 2) as $part) {
        $initials .= strtoupper($part[0] ?? '');
    }
    return $initials;
}

function array_to_string($array, $glue = ', ') {
    return implode($glue, array_filter($array));
}

function generate_ref_number($prefix = 'REF') {
    return $prefix . '-' . date('Ymd') . '-' . strtoupper(bin2hex(random_bytes(3)));
}

// ============================================
// FILE HELPERS
// ============================================

function get_file_extension($filename) {
    return strtolower(pathinfo($filename, PATHINFO_EXTENSION));
}

function get_mime_type($file) {
    $ext = get_file_extension($file);
    $types = [
        'pdf' => 'application/pdf', 'jpg' => 'image/jpeg', 'jpeg' => 'image/jpeg',
        'png' => 'image/png', 'gif' => 'image/gif', 'webp' => 'image/webp',
        'doc' => 'application/msword', 'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        'ppt' => 'application/vnd.ms-powerpoint', 'pptx' => 'application/vnd.openxmlformats-officedocument.presentationml.presentation',
        'xls' => 'application/vnd.ms-excel', 'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'csv' => 'text/csv', 'txt' => 'text/plain', 'zip' => 'application/zip',
        'mp4' => 'video/mp4', 'mp3' => 'audio/mpeg',
    ];
    return $types[$ext] ?? 'application/octet-stream';
}

function format_file_size($bytes) {
    if ($bytes >= 1073741824) return round($bytes / 1073741824, 2) . ' GB';
    if ($bytes >= 1048576) return round($bytes / 1048576, 2) . ' MB';
    if ($bytes >= 1024) return round($bytes / 1024, 1) . ' KB';
    return $bytes . ' B';
}

// ============================================
// JSON HELPERS
// ============================================

function is_json($string) {
    json_decode($string);
    return json_last_error() === JSON_ERROR_NONE;
}

function get_json_value($json, $key, $default = '') {
    if (is_string($json)) $json = json_decode($json, true);
    return $json[$key] ?? $default;
}

// ============================================
// STATUS & UI HELPERS
// ============================================

function get_status_color($status) {
    $colors = [
        'pending' => 'badge-pending', 'submitted' => 'badge-submitted', 'active' => 'badge-active',
        'approved' => 'badge-active', 'issued' => 'badge-active', 'completed' => 'badge-active',
        'passed' => 'badge-active', 'resolved' => 'badge-active', 'graded' => 'badge-active',
        'rejected' => 'badge-rejected', 'failed' => 'badge-rejected', 'cancelled' => 'badge-rejected',
        'closed' => 'badge-rejected', 'in_progress' => 'badge-submitted', 'under_review' => 'badge-submitted',
        'open' => 'badge-submitted', 'draft' => 'badge-pending',
    ];
    return $colors[$status] ?? 'badge-pending';
}

function get_role_badge($role) {
    $badges = [
        'super_admin' => 'badge-rejected', 'academic_admin' => 'badge-submitted',
        'finance_officer' => 'badge-active', 'teacher' => 'badge-submitted',
        'assessor' => 'badge-pending', 'student' => 'badge-active',
        'support_officer' => 'badge-submitted', 'auditor' => 'badge-pending',
    ];
    $class = $badges[$role] ?? 'badge-pending';
    return '<span class="badge-cix ' . $class . '">' . e(ucwords(str_replace('_', ' ', $role))) . '</span>';
}

function random_color() {
    return '#' . str_pad(dechex(mt_rand(0, 0xFFFFFF)), 6, '0', STR_PAD_LEFT);
}

// ============================================
// SOUTH AFRICAN DATA
// ============================================

function get_province_list() {
    return [
        'Eastern Cape','Free State','Gauteng','KwaZulu-Natal','Limpopo',
        'Mpumalanga','North West','Northern Cape','Western Cape'
    ];
}

function get_education_levels() {
    return [
        'None','Grade 8','Grade 9','Grade 10','Grade 11','Grade 12 / Matric',
        'Certificate','Diploma','Advanced Diploma','Bachelor Degree',
        'Honours Degree','Master Degree','Doctorate','Other'
    ];
}

function get_bank_details() {
    global $pdo;
    if ($pdo instanceof PDO) {
        try {
            $rows = $pdo->query("SELECT bank_name AS bank, account_name, account_number, branch_code FROM banking_details WHERE is_active = 1 ORDER BY sort_order, id")->fetchAll();
            if ($rows) return $rows;
        } catch (Throwable $e) {
            // Fall back to defaults when running before migrations are applied.
        }
    }
    return [
        ['bank' => 'Standard Bank', 'account_name' => 'CIX CONNECT', 'account_number' => '10200349927', 'branch_code' => '051001'],
        ['bank' => 'Capitec Business', 'account_name' => 'CIX CONNECT', 'account_number' => '1051396964', 'branch_code' => '450105'],
        ['bank' => 'ABSA', 'account_name' => 'CIX CONNECT', 'account_number' => '4123963036', 'branch_code' => '632005'],
    ];
}

function get_payment_instruction() {
    return get_setting('payment_instruction', 'Use your student number as payment reference. Email proof of payment to academic@cixconnect.co.za or WhatsApp proof to 083 759 8075.');
}

function get_theme_slug_for_scope($scope = 'public') {
    $key = in_array($scope, ['admin', 'teacher', 'student', 'public'], true) ? $scope . '_default_theme' : 'default_theme';
    return get_setting($key, get_setting('default_theme', 'orbit-dark'));
}

function render_theme_css($scope = 'public') {
    global $pdo;
    if (!$pdo instanceof PDO) return '';
    $stmt = $pdo->prepare("SELECT css_variables FROM themes WHERE slug = ? LIMIT 1");
    $stmt->execute([get_theme_slug_for_scope($scope)]);
    $json = $stmt->fetchColumn();
    $vars = $json ? json_decode($json, true) : null;
    if (!is_array($vars)) return '';
    $css = ':root {';
    foreach ($vars as $key => $value) {
        if (preg_match('/^--[a-z0-9-]+$/i', $key)) {
            $css .= $key . ':' . e($value) . ';';
        }
    }
    return '<style>' . $css . '}</style>';
}

function role_home_url(array $user): string {
    $role = $user['role_slug'] ?? '';
    if ($role === 'student') return 'student/dashboard';
    if ($role === 'teacher') return 'teacher/dashboard';
    return 'admin/dashboard';
}

function get_system_info() {
    return [
        'php_version' => PHP_VERSION,
        'mysql_version' => function_exists('pdo') ? 'PDO Available' : 'PDO Not Available',
        'server_software' => $_SERVER['SERVER_SOFTWARE'] ?? 'Unknown',
        'memory_limit' => ini_get('memory_limit'),
        'upload_max' => ini_get('upload_max_filesize'),
        'post_max' => ini_get('post_max_filesize'),
        'max_execution' => ini_get('max_execution_time'),
        'extensions' => [
            'pdo' => extension_loaded('pdo'), 'pdo_mysql' => extension_loaded('pdo_mysql'),
            'mbstring' => extension_loaded('mbstring'), 'json' => extension_loaded('json'),
            'gd' => extension_loaded('gd'), 'zip' => extension_loaded('zip'),
            'curl' => extension_loaded('curl'), 'openssl' => extension_loaded('openssl'),
            'fileinfo' => extension_loaded('fileinfo'),
        ]
    ];
}

// ============================================
// WHATSAPP & MESSAGING
// ============================================

function send_whatsapp_message($phone, $message) {
    $phone = preg_replace('/[^0-9]/', '', $phone);
    if (substr($phone, 0, 2) === '08') $phone = '27' . substr($phone, 1);
    elseif (substr($phone, 0, 1) === '0') $phone = '27' . substr($phone, 1);
    return 'https://wa.me/' . $phone . '?text=' . urlencode($message);
}

// ============================================
// INSTALLATION CHECK
// ============================================

function is_installed() {
    return file_exists(dirname(__DIR__, 2) . '/config.php');
}

function require_installed() {
    if (!is_installed()) {
        header('Location: ' . base_url('install/'));
        exit;
    }
}

// ============================================
// FILE CACHE
// ============================================

function cache_set($key, $value, $ttl = 300) {
    $dir = dirname(__DIR__, 2) . '/storage/cache';
    if (!is_dir($dir)) @mkdir($dir, 0755, true);
    $data = ['expires' => time() + $ttl, 'data' => $value];
    file_put_contents($dir . '/' . md5($key) . '.cache', serialize($data));
}

function cache_get($key) {
    $file = dirname(__DIR__, 2) . '/storage/cache/' . md5($key) . '.cache';
    if (!file_exists($file)) return null;
    $data = unserialize(file_get_contents($file));
    if ($data['expires'] < time()) { @unlink($file); return null; }
    return $data['data'];
}

function cache_delete($key) {
    $file = dirname(__DIR__, 2) . '/storage/cache/' . md5($key) . '.cache';
    if (file_exists($file)) @unlink($file);
}

// ============================================
// EMAIL TEMPLATE PARSER
// ============================================

function parse_email_template($templateCode, $variables = []) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT subject, body FROM email_templates WHERE code = ? AND is_active = 1");
    $stmt->execute([$templateCode]);
    $tpl = $stmt->fetch();
    if (!$tpl) return null;
    
    $vars = array_merge([
        'academy_name' => get_setting('academy_name', 'CIX Orbit Academy'),
        'company_name' => get_setting('company_name', 'CIX Connect'),
        'academy_url' => get_setting('academy_url', base_url('/')),
        'academic_email' => get_setting('academic_email', 'academic@cixconnect.co.za'),
        'office_phone' => get_setting('office_phone', '012 004 0207'),
        'whatsapp_number' => get_setting('whatsapp_number', '083 759 8075'),
        'slogan' => get_setting('slogan', 'Learn. Build. Automate. Grow.'),
        'current_date' => date('d M Y'),
        'current_year' => date('Y'),
        'login_url' => base_url('login'),
    ], $variables);
    
    $subject = $tpl['subject'];
    $body = $tpl['body'];
    foreach ($vars as $k => $v) {
        $subject = str_replace('{' . $k . '}', $v, $subject);
        $body = str_replace('{' . $k . '}', $v, $body);
    }
    return ['subject' => $subject, 'body' => $body];
}

function send_templated_email($to, $templateCode, $variables = []) {
    $tpl = parse_email_template($templateCode, $variables);
    if (!$tpl) return false;
    $html = '<!DOCTYPE html><html><head><meta charset="utf-8"></head><body style="font-family:Segoe UI,sans-serif;background:#f4f4f4;padding:20px;"><div style="max-width:600px;margin:0 auto;background:#ffffff;border-radius:8px;overflow:hidden;border:1px solid #ddd;"><div style="background:#000;padding:20px;text-align:center;"><h2 style="color:#ff6b00;margin:0;">CIX Orbit Academy</h2><p style="color:#b0b0b0;margin:5px 0 0;font-size:13px;">' . get_setting('slogan', 'Learn. Build. Automate. Grow.') . '</p></div><div style="padding:30px;">' . $tpl['body'] . '</div><div style="background:#1a1a1a;padding:15px;text-align:center;color:#666;font-size:12px;"><p style="margin:0;">&copy; ' . date('Y') . ' CIX Connect. All rights reserved.</p><p style="margin:5px 0 0;">Academic: academic@cixconnect.co.za | WhatsApp: 083 759 8075</p></div></div></body></html>';
    return send_email($to, $tpl['subject'], $html);
}

// ============================================
// AUDIT & ACTIVITY
// ============================================

function get_recent_audit_logs($limit = 20) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT al.*, u.first_name, u.last_name FROM audit_logs al LEFT JOIN users u ON al.user_id = u.id ORDER BY al.created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

function get_recent_activity($limit = 20) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT * FROM activity_logs ORDER BY created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}

// ============================================
// EXPORT HELPERS
// ============================================

function export_csv($data, $headers, $filename = 'export.csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    $output = fopen('php://output', 'w');
    fputcsv($output, $headers);
    foreach ($data as $row) {
        $values = [];
        foreach ($headers as $k => $v) {
            $values[] = $row[$k] ?? '';
        }
        fputcsv($output, $values);
    }
    fclose($output);
    exit;
}

function generate_csv_string($data, $headers) {
    $output = fopen('php://temp', 'r+');
    fputcsv($output, $headers);
    foreach ($data as $row) {
        $values = [];
        foreach ($headers as $k => $v) {
            $values[] = $row[$k] ?? '';
        }
        fputcsv($output, $values);
    }
    rewind($output);
    $csv = stream_get_contents($output);
    fclose($output);
    return $csv;
}

// ============================================
// BACKUP HELPER
// ============================================

function create_database_backup($pdo, $type = 'full') {
    $backupDir = dirname(__DIR__, 2) . '/storage/backups';
    if (!is_dir($backupDir)) @mkdir($backupDir, 0755, true);
    $filename = 'backup_' . date('Y-m-d_His') . '_' . $type . '.sql';
    $filepath = $backupDir . '/' . $filename;
    
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    $sql = "-- CIX Orbit Academy Backup\n-- Date: " . date('Y-m-d H:i:s') . "\n-- Type: $type\n\n";
    
    foreach ($tables as $table) {
        $create = $pdo->query("SHOW CREATE TABLE `$table`")->fetch();
        $sql .= "DROP TABLE IF EXISTS `$table`;\n" . $create[1] . ";\n\n";
        $data = $pdo->query("SELECT * FROM `$table`")->fetchAll();
        foreach ($data as $row) {
            $values = array_map(function($v) { return $v === null ? 'NULL' : "'" . addslashes($v) . "'"; }, $row);
            $sql .= "INSERT INTO `$table` VALUES (" . implode(', ', $values) . ");\n";
        }
        $sql .= "\n";
    }
    
    file_put_contents($filepath, $sql);
    return ['filename' => $filename, 'filepath' => $filepath, 'size' => filesize($sql)];
}

// ============================================
// NOTIFICATION HELPER
// ============================================

function create_notification($userId, $type, $title, $message, $link = '') {
    global $pdo;
    $stmt = $pdo->prepare("INSERT INTO notifications (user_id, type, title, message, link) VALUES (?, ?, ?, ?, ?)");
    $stmt->execute([$userId, $type, $title, $message, $link]);
}

function get_unread_notifications($userId, $limit = 10) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT * FROM notifications WHERE user_id = ? AND is_read = 0 ORDER BY created_at DESC LIMIT ?");
    $stmt->execute([$userId, $limit]);
    return $stmt->fetchAll();
}

function get_unread_count($userId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM notifications WHERE user_id = ? AND is_read = 0");
    $stmt->execute([$userId]);
    return (int)$stmt->fetchColumn();
}

function mark_notifications_read($userId) {
    global $pdo;
    $pdo->prepare("UPDATE notifications SET is_read = 1 WHERE user_id = ? AND is_read = 0")->execute([$userId]);
}

// ============================================
// COURSE PROGRESS HELPER
// ============================================

function calculate_course_progress($studentId, $courseId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM lessons l JOIN modules m ON l.module_id = m.id WHERE m.course_id = ? AND l.is_published = 1");
    $stmt->execute([$courseId]);
    $totalLessons = (int)$stmt->fetchColumn();
    if ($totalLessons === 0) return 0;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM activity_logs WHERE student_id = ? AND activity_type = 'lesson_complete'");
    $stmt->execute([$studentId]);
    $completed = (int)$stmt->fetchColumn();
    return min(100, round(($completed / max($totalLessons, 1)) * 100));
}

function update_enrollment_progress($studentId, $courseId) {
    global $pdo;
    $percent = calculate_course_progress($studentId, $courseId);
    $pdo->prepare("UPDATE enrollments SET progress_percent = ? WHERE student_id = ? AND course_id = ?")->execute([$percent, $studentId, $courseId]);
    return $percent;
}

function get_student_overall_progress($studentId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT AVG(progress_percent) FROM enrollments WHERE student_id = ? AND status = 'active'");
    $stmt->execute([$studentId]);
    return round((float)$stmt->fetchColumn());
}

function get_course_module_count($courseId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM modules WHERE course_id = ? AND is_published = 1");
    $stmt->execute([$courseId]);
    return (int)$stmt->fetchColumn();
}

function get_module_lesson_count($moduleId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM lessons WHERE module_id = ? AND is_published = 1");
    $stmt->execute([$moduleId]);
    return (int)$stmt->fetchColumn();
}

function get_question_bank_question_count($bankId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM questions WHERE bank_id = ?");
    $stmt->execute([$bankId]);
    return (int)$stmt->fetchColumn();
}

function is_lesson_completed($studentId, $lessonId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM activity_logs WHERE student_id = ? AND activity_type = 'lesson_complete' AND metadata LIKE ?");
    $stmt->execute([$studentId, '%"lesson_id":' . $lessonId . '%']);
    return (int)$stmt->fetchColumn() > 0;
}

function get_student_test_score($studentId, $testId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT * FROM test_attempts WHERE student_id = ? AND test_id = ? AND status = 'completed' ORDER BY score DESC LIMIT 1");
    $stmt->execute([$studentId, $testId]);
    return $stmt->fetch();
}

function can_student_take_test($studentId, $testId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT t.max_attempts, (SELECT COUNT(*) FROM test_attempts WHERE test_id = t.id AND student_id = ? AND status IN ('completed','auto_submitted')) as attempts_made FROM tests t WHERE t.id = ?");
    $stmt->execute([$studentId, $testId]);
    $test = $stmt->fetch();
    if (!$test) return false;
    return $test['attempts_made'] < $test['max_attempts'];
}

function is_student_enrolled($studentId, $courseId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM enrollments WHERE student_id = ? AND course_id = ? AND status = 'active'");
    $stmt->execute([$studentId, $courseId]);
    return (int)$stmt->fetchColumn() > 0;
}

function get_student_certificate($studentId, $courseId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT * FROM certificates WHERE student_id = ? AND course_id = ? AND status = 'issued' LIMIT 1");
    $stmt->execute([$studentId, $courseId]);
    return $stmt->fetch();
}

function get_total_revenue() {
    global $pdo;
    return (float)$pdo->query("SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = 'approved'")->fetchColumn();
}

function get_monthly_revenue($month, $year) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = 'approved' AND MONTH(approved_at) = ? AND YEAR(approved_at) = ?");
    $stmt->execute([$month, $year]);
    return (float)$stmt->fetchColumn();
}

function get_dashboard_stats() {
    global $pdo;
    $stats = [];
    $stats['total_students'] = (int)$pdo->query("SELECT COUNT(*) FROM students")->fetchColumn();
    $stats['new_applications'] = (int)$pdo->query("SELECT COUNT(*) FROM applications WHERE status = 'pending'")->fetchColumn();
    $stats['active_students'] = (int)$pdo->query("SELECT COUNT(*) FROM students WHERE enrollment_status = 'active'")->fetchColumn();
    $stats['pending_payments'] = (int)$pdo->query("SELECT COUNT(*) FROM payments WHERE status = 'submitted'")->fetchColumn();
    $stats['total_courses'] = (int)$pdo->query("SELECT COUNT(*) FROM courses WHERE is_published = 1")->fetchColumn();
    $stats['total_enrollments'] = (int)$pdo->query("SELECT COUNT(*) FROM enrollments WHERE status = 'active'")->fetchColumn();
    $stats['certificates_issued'] = (int)$pdo->query("SELECT COUNT(*) FROM certificates WHERE status = 'issued'")->fetchColumn();
    $stats['total_tests'] = (int)$pdo->query("SELECT COUNT(*) FROM test_attempts WHERE status = 'completed'")->fetchColumn();
    $passed = (int)$pdo->query("SELECT COUNT(*) FROM test_attempts WHERE status = 'completed' AND passed = 1")->fetchColumn();
    $stats['pass_rate'] = $stats['total_tests'] > 0 ? round(($passed / $stats['total_tests']) * 100, 1) : 0;
    $stats['total_revenue'] = (float)$pdo->query("SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = 'approved'")->fetchColumn();
    return $stats;
}

// ============================================
// QR CODE HELPER
// ============================================

function generate_qr_code($data, $filepath, $size = 200) {
    try {
        if (class_exists('Endroid\\QrCode\\Writer\\PngWriter') && method_exists('Endroid\\QrCode\\QrCode', 'create')) {
            $qrCodeClass = 'Endroid\\QrCode\\QrCode';
            $writerClass = 'Endroid\\QrCode\\Writer\\PngWriter';
            $qr = $qrCodeClass::create($data)->setSize($size)->setMargin(10);
            (new $writerClass())->write($qr)->saveToFile($filepath);
        } else {
            $qrCodeClass = 'Endroid\\QrCode\\QrCode';
            if (!class_exists($qrCodeClass)) return false;
            $qr = new $qrCodeClass($data);
            if (method_exists($qr, 'setSize')) $qr->setSize($size);
            if (method_exists($qr, 'setMargin')) $qr->setMargin(10);
            if (method_exists($qr, 'writeFile')) $qr->writeFile($filepath);
        }
        return true;
    } catch (\Exception $e) {
        error_log("QR Code error: " . $e->getMessage());
        return false;
    }
}

// ============================================
// PDF HELPER
// ============================================

function generate_pdf($html, $filepath, $options = []) {
    try {
        $defaults = ['margin_left' => 15, 'margin_right' => 15, 'margin_top' => 20, 'margin_bottom' => 20];
        $opts = array_merge($defaults, $options);
        $mpdf = new \Mpdf\Mpdf($opts);
        $mpdf->WriteHTML($html);
        $mpdf->Output($filepath, \Mpdf\Output\Destination::FILE);
        return true;
    } catch (\Exception $e) {
        error_log("PDF generation error: " . $e->getMessage());
        return false;
    }
}

function generate_pdf_output($html, $filename = 'document.pdf', $options = []) {
    try {
        $defaults = ['margin_left' => 15, 'margin_right' => 15, 'margin_top' => 20, 'margin_bottom' => 20];
        $opts = array_merge($defaults, $options);
        $mpdf = new \Mpdf\Mpdf($opts);
        $mpdf->WriteHTML($html);
        $mpdf->Output($filename, \Mpdf\Output\Destination::INLINE);
        exit;
    } catch (\Exception $e) {
        error_log("PDF error: " . $e->getMessage());
        return false;
    }
}

// ============================================
// REPORT GENERATION
// ============================================

function generate_student_report_html($studentId, $pdo) {
    $stmt = $pdo->prepare("SELECT s.*, u.first_name, u.last_name, u.email FROM students s JOIN users u ON s.user_id = u.id WHERE s.id = ?");
    $stmt->execute([$studentId]);
    $student = $stmt->fetch();
    if (!$student) return null;
    
    $stmt = $pdo->prepare("SELECT e.*, c.title as course_title FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE e.student_id = ?");
    $stmt->execute([$studentId]);
    $enrollments = $stmt->fetchAll();
    
    $stmt = $pdo->prepare("SELECT ta.*, t.title as test_title, c.title as course_title FROM test_attempts ta JOIN tests t ON ta.test_id = t.id JOIN courses c ON t.course_id = c.id WHERE ta.student_id = ? AND ta.status = 'completed' ORDER BY ta.ended_at DESC");
    $stmt->execute([$studentId]);
    $results = $stmt->fetchAll();
    
    $html = '<!DOCTYPE html><html><head><style>
        body{font-family:DejaVu Sans,sans-serif;font-size:12px;color:#333;}
        .header{text-align:center;border-bottom:3px solid #ff6b00;padding-bottom:15px;margin-bottom:20px;}
        .header h1{color:#ff6b00;font-size:24px;margin:0;}
        .header p{color:#666;margin:5px 0 0;}
        table{width:100%;border-collapse:collapse;margin:15px 0;}
        th{background:#f5f5f5;padding:8px 10px;text-align:left;border-bottom:2px solid #ddd;font-size:11px;}
        td{padding:8px 10px;border-bottom:1px solid #eee;}
        .badge-pass{color:#00aa00;font-weight:bold;}
        .badge-fail{color:#cc0000;font-weight:bold;}
        .footer{text-align:center;border-top:2px solid #ff6b00;padding-top:15px;margin-top:30px;color:#999;font-size:10px;}
    </style></head><body>
    <div class="header"><h1>CIX Orbit Academy</h1><p>Student Progress Report</p></div>
    <h3>Student Details</h3>
    <table><tr><td><strong>Name:</strong></td><td>' . e($student['first_name'] . ' ' . $student['last_name']) . '</td>
    <td><strong>Student #:</strong></td><td>' . e($student['student_number']) . '</td></tr>
    <tr><td><strong>Email:</strong></td><td>' . e($student['email']) . '</td>
    <td><strong>Date:</strong></td><td>' . date('d M Y') . '</td></tr></table>';
    
    if (!empty($enrollments)) {
        $html .= '<h3>Enrolled Courses</h3><table><thead><tr><th>Course</th><th>Status</th><th>Progress</th><th>Enrolled</th></tr></thead><tbody>';
        foreach ($enrollments as $en) {
            $html .= '<tr><td>' . e($en['course_title']) . '</td><td>' . e(ucfirst($en['status'])) . '</td><td>' . ($en['progress_percent'] ?? 0) . '%</td><td>' . e($en['enrolled_at']) . '</td></tr>';
        }
        $html .= '</tbody></table>';
    }
    
    if (!empty($results)) {
        $html .= '<h3>Test Results</h3><table><thead><tr><th>Test</th><th>Course</th><th>Score</th><th>Status</th><th>Date</th></tr></thead><tbody>';
        foreach ($results as $r) {
            $badge = $r['passed'] ? '<span class="badge-pass">Passed</span>' : '<span class="badge-fail">Failed</span>';
            $html .= '<tr><td>' . e($r['test_title']) . '</td><td>' . e($r['course_title']) . '</td><td>' . e($r['score']) . '%</td><td>' . $badge . '</td><td>' . e($r['ended_at']) . '</td></tr>';
        }
        $html .= '</tbody></table>';
    }
    
    $html .= '<div class="footer"><p>Powered by CIX Connect | ' . get_setting('academy_url', '') . '</p><p>Generated on ' . date('d M Y H:i') . '</p></div></body></html>';
    return $html;
}

function generate_class_report_html($courseId, $pdo) {
    $stmt = $pdo->prepare("SELECT * FROM courses WHERE id = ?");
    $stmt->execute([$courseId]);
    $course = $stmt->fetch();
    if (!$course) return null;
    
    $stmt = $pdo->prepare("SELECT s.student_number, u.first_name, u.last_name, e.progress_percent, e.enrolled_at FROM enrollments e JOIN students s ON e.student_id = s.id JOIN users u ON s.user_id = u.id WHERE e.course_id = ? AND e.status = 'active' ORDER BY u.last_name");
    $stmt->execute([$courseId]);
    $students = $stmt->fetchAll();
    
    $stmt = $pdo->prepare("SELECT AVG(ta.score) as avg_score, COUNT(*) as total, SUM(CASE WHEN ta.passed = 1 THEN 1 ELSE 0 END) as passed FROM test_attempts ta JOIN tests t ON ta.test_id = t.id WHERE t.course_id = ? AND ta.status = 'completed'");
    $stmt->execute([$courseId]);
    $stats = $stmt->fetch();
    
    $html = '<!DOCTYPE html><html><head><style>
        body{font-family:DejaVu Sans,sans-serif;font-size:12px;color:#333;}
        .header{text-align:center;border-bottom:3px solid #ff6b00;padding-bottom:15px;margin-bottom:20px;}
        .header h1{color:#ff6b00;font-size:24px;margin:0;}
        table{width:100%;border-collapse:collapse;margin:15px 0;}
        th{background:#f5f5f5;padding:8px;text-align:left;border-bottom:2px solid #ddd;font-size:11px;}
        td{padding:8px;border-bottom:1px solid #eee;}
        .stat-box{display:inline-block;text-align:center;padding:15px 25px;margin:5px;border:1px solid #ddd;border-radius:8px;}
        .stat-value{font-size:24px;font-weight:bold;color:#ff6b00;}
        .stat-label{font-size:10px;color:#666;}
        .footer{text-align:center;border-top:2px solid #ff6b00;padding-top:15px;margin-top:30px;color:#999;font-size:10px;}
    </style></head><body>
    <div class="header"><h1>CIX Orbit Academy</h1><p>Class Performance Report</p></div>
    <h3>' . e($course['title']) . '</h3>
    <div style="text-align:center;margin:20px 0;">
        <div class="stat-box"><div class="stat-value">' . count($students) . '</div><div class="stat-label">Students</div></div>
        <div class="stat-box"><div class="stat-value">' . ($stats['avg_score'] ? round($stats['avg_score'], 1) . '%' : 'N/A') . '</div><div class="stat-label">Average Score</div></div>
        <div class="stat-box"><div class="stat-value">' . ($stats['total'] ? round(($stats['passed'] / $stats['total']) * 100, 1) . '%' : 'N/A') . '</div><div class="stat-label">Pass Rate</div></div>
    </div>
    <table><thead><tr><th>Student #</th><th>Name</th><th>Progress</th><th>Enrolled</th></tr></thead><tbody>';
    foreach ($students as $s) {
        $html .= '<tr><td>' . e($s['student_number']) . '</td><td>' . e($s['first_name'] . ' ' . $s['last_name']) . '</td><td>' . ($s['progress_percent'] ?? 0) . '%</td><td>' . e($s['enrolled_at']) . '</td></tr>';
    }
    $html .= '</tbody></table><div class="footer"><p>Powered by CIX Connect | ' . get_setting('academy_url', '') . '</p><p>Generated on ' . date('d M Y H:i') . '</p></div></body></html>';
    return $html;
}

function generate_payment_report_html($pdo) {
    $stmt = $pdo->prepare("SELECT p.*, s.student_number, u.first_name, u.last_name, pk.name as package_name FROM payments p JOIN students s ON p.student_id = s.id JOIN users u ON s.user_id = u.id LEFT JOIN packages pk ON p.package_id = pk.id ORDER BY p.created_at DESC");
    $stmt->execute();
    $payments = $stmt->fetchAll();
    
    $totalApproved = 0;
    $totalPending = 0;
    foreach ($payments as $p) {
        if ($p['status'] === 'approved') $totalApproved += $p['amount'];
        elseif (in_array($p['status'], ['pending','submitted'])) $totalPending += $p['amount'];
    }
    
    $html = '<!DOCTYPE html><html><head><style>
        body{font-family:DejaVu Sans,sans-serif;font-size:11px;color:#333;}
        .header{text-align:center;border-bottom:3px solid #ff6b00;padding-bottom:15px;margin-bottom:20px;}
        .header h1{color:#ff6b00;font-size:22px;margin:0;}
        table{width:100%;border-collapse:collapse;margin:10px 0;}
        th{background:#f5f5f5;padding:6px 8px;text-align:left;border-bottom:2px solid #ddd;font-size:10px;}
        td{padding:6px 8px;border-bottom:1px solid #eee;font-size:10px;}
        .stat-box{display:inline-block;text-align:center;padding:10px 20px;margin:5px;border:1px solid #ddd;border-radius:8px;}
        .stat-value{font-size:20px;font-weight:bold;color:#ff6b00;}
        .stat-label{font-size:9px;color:#666;}
        .footer{text-align:center;border-top:2px solid #ff6b00;padding-top:15px;margin-top:30px;color:#999;font-size:9px;}
    </style></head><body>
    <div class="header"><h1>CIX Orbit Academy</h1><p>Payment Report</p></div>
    <div style="text-align:center;margin:15px 0;">
        <div class="stat-box"><div class="stat-value">R' . number_format($totalApproved, 2) . '</div><div class="stat-label">Total Approved</div></div>
        <div class="stat-box"><div class="stat-value">R' . number_format($totalPending, 2) . '</div><div class="stat-label">Pending</div></div>
        <div class="stat-box"><div class="stat-value">' . count($payments) . '</div><div class="stat-label">Total Payments</div></div>
    </div>
    <table><thead><tr><th>Student #</th><th>Name</th><th>Package</th><th>Amount</th><th>Status</th><th>Date</th></tr></thead><tbody>';
    foreach ($payments as $p) {
        $html .= '<tr><td>' . e($p['student_number']) . '</td><td>' . e($p['first_name'] . ' ' . $p['last_name']) . '</td><td>' . e($p['package_name'] ?? '-') . '</td><td>R' . number_format($p['amount'], 2) . '</td><td>' . e(ucfirst($p['status'])) . '</td><td>' . e($p['created_at']) . '</td></tr>';
    }
    $html .= '</tbody></table><div class="footer"><p>Powered by CIX Connect | Generated on ' . date('d M Y H:i') . '</p></div></body></html>';
    return $html;
}

function generate_certificate_report_html($pdo) {
    $stmt = $pdo->prepare("SELECT c.*, s.student_number, u.first_name, u.last_name, cr.title as course_title FROM certificates c JOIN students s ON c.student_id = s.id JOIN users u ON s.user_id = u.id JOIN courses cr ON c.course_id = cr.id ORDER BY c.created_at DESC");
    $stmt->execute();
    $certs = $stmt->fetchAll();
    
    $html = '<!DOCTYPE html><html><head><style>
        body{font-family:DejaVu Sans,sans-serif;font-size:11px;color:#333;}
        .header{text-align:center;border-bottom:3px solid #ff6b00;padding-bottom:15px;margin-bottom:20px;}
        .header h1{color:#ff6b00;font-size:22px;margin:0;}
        table{width:100%;border-collapse:collapse;margin:10px 0;}
        th{background:#f5f5f5;padding:6px 8px;text-align:left;border-bottom:2px solid #ddd;font-size:10px;}
        td{padding:6px 8px;border-bottom:1px solid #eee;font-size:10px;}
        .footer{text-align:center;border-top:2px solid #ff6b00;padding-top:15px;margin-top:30px;color:#999;font-size:9px;}
    </style></head><body>
    <div class="header"><h1>CIX Orbit Academy</h1><p>Certificates Issued Report</p></div>
    <p><strong>Total Certificates:</strong> ' . count($certs) . '</p>
    <table><thead><tr><th>Certificate #</th><th>Student</th><th>Course</th><th>Issue Date</th><th>Status</th></tr></thead><tbody>';
    foreach ($certs as $c) {
        $html .= '<tr><td>' . e($c['certificate_number']) . '</td><td>' . e($c['student_name']) . '</td><td>' . e($c['course_name']) . '</td><td>' . e($c['issue_date']) . '</td><td>' . e(ucfirst($c['status'])) . '</td></tr>';
    }
    $html .= '</tbody></table><div class="footer"><p>Powered by CIX Connect | Generated on ' . date('d M Y H:i') . '</p></div></body></html>';
    return $html;
}

function generate_application_report_html($pdo) {
    $stmt = $pdo->query("SELECT * FROM applications ORDER BY created_at DESC");
    $apps = $stmt->fetchAll();
    $pending = $approved = $rejected = 0;
    foreach ($apps as $a) {
        if ($a['status'] === 'pending') $pending++;
        elseif ($a['status'] === 'accepted') $approved++;
        elseif ($a['status'] === 'rejected') $rejected++;
    }
    
    $html = '<!DOCTYPE html><html><head><style>
        body{font-family:DejaVu Sans,sans-serif;font-size:11px;color:#333;}
        .header{text-align:center;border-bottom:3px solid #ff6b00;padding-bottom:15px;margin-bottom:20px;}
        .header h1{color:#ff6b00;font-size:22px;margin:0;}
        .stat-box{display:inline-block;text-align:center;padding:10px 20px;margin:5px;border:1px solid #ddd;border-radius:8px;}
        .stat-value{font-size:20px;font-weight:bold;color:#ff6b00;}
        .stat-label{font-size:9px;color:#666;}
        table{width:100%;border-collapse:collapse;margin:10px 0;}
        th{background:#f5f5f5;padding:6px 8px;text-align:left;border-bottom:2px solid #ddd;font-size:10px;}
        td{padding:6px 8px;border-bottom:1px solid #eee;font-size:10px;}
        .footer{text-align:center;border-top:2px solid #ff6b00;padding-top:15px;margin-top:30px;color:#999;font-size:9px;}
    </style></head><body>
    <div class="header"><h1>CIX Orbit Academy</h1><p>Applications Report</p></div>
    <div style="text-align:center;margin:15px 0;">
        <div class="stat-box"><div class="stat-value">' . count($apps) . '</div><div class="stat-label">Total Applications</div></div>
        <div class="stat-box"><div class="stat-value">' . $pending . '</div><div class="stat-label">Pending</div></div>
        <div class="stat-box"><div class="stat-value">' . $approved . '</div><div class="stat-label">Accepted</div></div>
        <div class="stat-box"><div class="stat-value">' . $rejected . '</div><div class="stat-label">Rejected</div></div>
    </div>
    <table><thead><tr><th>Name</th><th>Email</th><th>Phone</th><th>Status</th><th>Date</th></tr></thead><tbody>';
    foreach (array_slice($apps, 0, 100) as $a) {
        $html .= '<tr><td>' . e($a['first_name'] . ' ' . $a['last_name']) . '</td><td>' . e($a['email']) . '</td><td>' . e($a['phone']) . '</td><td>' . e(ucfirst($a['status'])) . '</td><td>' . e($a['created_at']) . '</td></tr>';
    }
    $html .= '</tbody></table><div class="footer"><p>Powered by CIX Connect | Generated on ' . date('d M Y H:i') . '</p></div></body></html>';
    return $html;
}
