<?php
require_once 'config.php';

if (!defined('BASE_PATH')) {
    define('BASE_PATH', dirname(__DIR__));
}

/**
 * Input Sanitization Helper Functions
 * Provides global helper functions for input sanitization
 */

function sanitize($input, $type = 'string', $options = []) {
    switch ($type) {
        case 'email':
            return filter_var($input, FILTER_SANITIZE_EMAIL);
        case 'url':
            return filter_var($input, FILTER_SANITIZE_URL);
        case 'int':
            $int = filter_var($input, FILTER_VALIDATE_INT);
            if ($int === false) return null;
            $min = $options['min'] ?? null;
            $max = $options['max'] ?? null;
            if ($min !== null && $int < $min) return null;
            if ($max !== null && $int > $max) return null;
            return $int;
        case 'float':
            $float = filter_var($input, FILTER_VALIDATE_FLOAT);
            if ($float === false) return null;
            $min = $options['min'] ?? null;
            $max = $options['max'] ?? null;
            if ($min !== null && $float < $min) return null;
            if ($max !== null && $float > $max) return null;
            return $float;
        case 'html':
            return strip_tags($input);
        default:
            $sanitized = trim(strip_tags($input));
            $max_length = $options['max_length'] ?? null;
            if ($max_length !== null && mb_strlen($sanitized, 'UTF-8') > $max_length) {
                $sanitized = mb_substr($sanitized, 0, $max_length, 'UTF-8');
            }
            return $sanitized;
    }
}

function sanitize_post($key, $type = 'string', $options = []) {
    return isset($_POST[$key]) ? sanitize($_POST[$key], $type, $options) : null;
}

function sanitize_get($key, $type = 'string', $options = []) {
    return isset($_GET[$key]) ? sanitize($_GET[$key], $type, $options) : null;
}

function sanitize_all_post($rules = []) {
    $sanitized = [];
    foreach ($rules as $key => $rule) {
        $type = is_array($rule) ? ($rule['type'] ?? 'string') : $rule;
        $options = is_array($rule) ? ($rule['options'] ?? []) : [];
        $sanitized[$key] = sanitize_post($key, $type, $options);
    }
    return $sanitized;
}

function sanitize_all_get($rules = []) {
    $sanitized = [];
    foreach ($rules as $key => $rule) {
        $type = is_array($rule) ? ($rule['type'] ?? 'string') : $rule;
        $options = is_array($rule) ? ($rule['options'] ?? []) : [];
        $sanitized[$key] = sanitize_get($key, $type, $options);
    }
    return $sanitized;
}

/**
 * Security Helper Functions
 */

function generate_csrf_token() {
    // Generate token only if it doesn't exist or has expired
    if (!isset($_SESSION['csrf_token']) || !isset($_SESSION['csrf_token_time']) || time() - $_SESSION['csrf_token_time'] > 3600) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        $_SESSION['csrf_token_time'] = time();
    }
    return $_SESSION['csrf_token'];
}

function validate_csrf_token($token) {
    if (!isset($_SESSION['csrf_token']) || !isset($_SESSION['csrf_token_time'])) {
        return false;
    }

    // Check if token is expired (1 hour)
    if (time() - $_SESSION['csrf_token_time'] > 3600) {
        unset($_SESSION['csrf_token'], $_SESSION['csrf_token_time']);
        return false;
    }

    if (!hash_equals($_SESSION['csrf_token'], $token)) {
        return false;
    }

    // Keep the token valid for the session duration (do NOT unset).
    // Refresh the timestamp so the token stays alive for another hour
    // from the last successful use.
    $_SESSION['csrf_token_time'] = time();
    return true;
}

function get_csrf_token() {
    return generate_csrf_token();
}

/**
 * Error Logging Helpers
 */

function log_security_event($event_type, $message, $context = []) {
    $timestamp = date('Y-m-d H:i:s');
    $ip = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
    $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? 'unknown';
    $log_message = "[$timestamp] SECURITY [$event_type]: $message - IP: $ip - UA: $user_agent - Context: " . json_encode($context) . "\n";
    $log_file = __DIR__ . '/../logs/security.log';

    // Create logs directory if it doesn't exist
    $log_dir = dirname($log_file);
    if (!is_dir($log_dir)) {
        if (!@mkdir($log_dir, 0755, true)) {
            // If we can't create the directory, log to PHP error log only
            error_log("Failed to create logs directory: $log_dir - " . error_get_last()['message'] ?? 'Unknown error');
            error_log("SECURITY [$event_type]: $message - IP: $ip - UA: $user_agent - Context: " . json_encode($context));
            return;
        }
    }

    // Try to write to file, fallback to error_log if permission denied
    if (is_writable($log_dir)) {
        $result = file_put_contents($log_file, $log_message, FILE_APPEND | LOCK_EX);
        if ($result === false) {
            error_log("Failed to write to security log file: $log_file");
        }
    } else {
        error_log("Logs directory not writable: $log_dir");
    }

    // Always log to PHP's error log for backward compatibility
    error_log("SECURITY [$event_type]: $message - IP: $ip - UA: $user_agent - Context: " . json_encode($context));
}

function log_error($message, $context = []) {
    $timestamp = date('Y-m-d H:i:s');
    $log_message = "[$timestamp] ERROR: $message - Context: " . json_encode($context) . "\n";
    $log_file = __DIR__ . '/../logs/errors.log';

    // Create logs directory if it doesn't exist
    $log_dir = dirname($log_file);
    if (!is_dir($log_dir)) {
        if (!@mkdir($log_dir, 0755, true)) {
            error_log("Failed to create logs directory: $log_dir - " . error_get_last()['message'] ?? 'Unknown error');
            error_log("ERROR: $message - Context: " . json_encode($context));
            return;
        }
    }

    // Try to write to file, fallback to error_log if permission denied
    if (is_writable($log_dir)) {
        $result = file_put_contents($log_file, $log_message, FILE_APPEND | LOCK_EX);
        if ($result === false) {
            error_log("Failed to write to error log file: $log_file");
        }
    } else {
        error_log("Logs directory not writable: $log_dir");
    }

    // Always log to PHP's error log for backward compatibility
    error_log("ERROR: $message - Context: " . json_encode($context));
}

/**
 * Rate Limiting Helpers
 */

function check_rate_limit_old($action, $identifier = null) {
    // Simple rate limiting using session (not production-ready)
    $identifier = $identifier ?? $_SERVER['REMOTE_ADDR'];
    $key = "rate_limit_{$action}_{$identifier}";
    $now = time();

    if (!isset($_SESSION[$key])) {
        $_SESSION[$key] = ['count' => 1, 'reset' => $now + 60]; // 1 request per minute
        return true;
    }

    if ($now > $_SESSION[$key]['reset']) {
        $_SESSION[$key] = ['count' => 1, 'reset' => $now + 60];
        return true;
    }

    if ($_SESSION[$key]['count'] >= 1) {
        return false;
    }

    $_SESSION[$key]['count']++;
    return true;
}

/**
 * HTML Escaping Helpers
 */

function h($string) {
    return htmlspecialchars($string, ENT_QUOTES | ENT_HTML5, 'UTF-8');
}

function h_decode($string) {
    return htmlspecialchars_decode($string, ENT_QUOTES | ENT_HTML5);
}

/**
 * URL and Path Helpers
 */

function url($path = '') {
    // Use APP_URL constant if defined (most reliable)
    if (defined('APP_URL')) {
        $base_url = rtrim(APP_URL, '/');
        return $base_url . '/' . ltrim($path, '/');
    }

    // Fallback to constructing URL from server variables
    $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? 'https' : 'http';
    $host = $_SERVER['HTTP_HOST'] ?? 'localhost';

    // Get the directory containing index.php relative to document root
    $script_dir = dirname($_SERVER['SCRIPT_NAME'] ?? '/');

    // If we're in a subdirectory, go up to find the application root
    if (strpos($script_dir, '/admin') !== false ||
        strpos($script_dir, '/company') !== false ||
        strpos($script_dir, '/student') !== false ||
        strpos($script_dir, '/graduate') !== false ||
        strpos($script_dir, '/teacher') !== false) {
        // We're in a subdirectory, go up one level
        $base = dirname($script_dir);
    } else {
        $base = $script_dir;
    }

    $base = rtrim($base, '/');
    return $protocol . '://' . $host . $base . '/' . ltrim($path, '/');
}

function asset($path) {
    return url('assets/' . ltrim($path, '/'));
}

function upload($path) {
    return url('uploads/' . ltrim($path, '/'));
}

/**
 * Route URL Helper - Maps named routes to URLs
 */
function route_url($route_name, $params = []) {
    static $routes = [
        // Authentication routes
        'login' => '/login',
        'login_post' => '/login',
        'logout' => '/logout',
        'register' => '/register',

        // Admin routes
        'admin_dashboard' => '/admin/dashboard',
        'admin_jobs' => '/admin/jobs',
        'admin_add_job' => '/admin/jobs/new',
        'admin_job_view' => '/admin/jobs', // Requires ID, e.g., route_url('admin_job_view', ['id' => 123])
        'admin_edit_job' => '/admin/jobs', // Requires ID and /edit, e.g., route_url('admin_edit_job', ['id' => 123])
        'admin_delete_job' => '/admin/jobs', // Requires ID and /delete, e.g., route_url('admin_delete_job', ['id' => 123])
        'admin_job_applications_id' => '/admin/job-applications',
        'admin_auto_update_job_status' => '/admin/auto-update-job-status',
        'admin_export_jobs' => '/admin/export-jobs',
        'admin_students' => '/admin/students',
        'admin_graduates' => '/admin/graduates',
        'admin_companies' => '/admin/companies',
        'admin_delete_company' => '/admin/companies', // Requires ID and /delete, e.g., route_url('admin_delete_company', ['id' => 123])
        'admin_users' => '/admin/users',
        'admin_add_user' => '/admin/users/new',
        'admin_add_student' => '/admin/students/new',
        'admin_delete_user' => '/admin/users', // Requires ID and /delete, e.g., route_url('admin_delete_user', ['id' => 123])
        'admin_student_view' => '/admin/students', // Requires ID, e.g., route_url('admin_student_view', ['id' => 123])
        'admin_student_edit' => '/admin/students', // Requires ID and /edit, e.g., route_url('admin_student_edit', ['id' => 123])
        'admin_student_delete' => '/admin/delete', // Requires ID, e.g., route_url('admin_student_delete', ['id' => 123])
        'admin_graduate_view' => '/admin/graduates/view', // Requires ID, e.g., route_url('admin_graduate_view', ['id' => 123])
        'admin_graduate_edit' => '/admin/graduates/edit', // Requires ID, e.g., route_url('admin_graduate_edit', ['id' => 123])
        'admin_graduate_delete' => '/admin/graduates/delete', // Requires ID, e.g., route_url('admin_graduate_delete', ['id' => 123])
        'admin_profile' => '/admin/profile',
        'admin_notifications' => '/admin/notifications',
        'admin_send_notification' => '/admin/notifications/send',
        'admin_careers' => '/admin/careers',
        'admin_upload_students' => '/admin/upload-students',
        'admin_custom_reports' => '/admin/reports',
        'admin_teachers' => '/admin/teachers',
        'admin_company_view' => '/admin/companies/view', // Requires ID, e.g., route_url('admin_company_view', ['id' => 123])
        'admin_company_edit' => '/admin/companies/edit', // Requires ID, e.g., route_url('admin_company_edit', ['id' => 123])
        'admin_company_delete' => '/admin/companies/delete', // Requires ID, e.g., route_url('admin_company_delete', ['id' => 123])
        'admin_toggle_graduate_status' => '/admin/toggle-graduate-status',
        'admin_resume' => '/admin/resume', // Requires ID, e.g., route_url('admin_resume', ['id' => 123])
        'admin_institution_settings' => '/admin/settings',

        // Company routes
        'company_dashboard' => '/company/dashboard',
        'company_jobs' => '/company/jobs',
        'company_job_applications' => '/company/job-applications', // Requires ID, e.g., route_url('company_job_applications', ['id' => 123])
        'company_profile' => '/company/profile',
        'company_resume' => '/company/resume', // Requires user_id, e.g., route_url('company_resume', ['user_id' => 123])
        'company_add_job' => '/company/jobs/new',

        // Student routes
        'student_dashboard' => '/student/dashboard',
        'student_jobs' => '/student/jobs',
        'student_view_job' => '/student/jobs', // Requires ID, e.g., route_url('student_view_job', ['id' => 123])
        'student_applications' => '/student/applications',
        'student_notifications' => '/student/notifications',
        'student_profile' => '/student/profile',
        'student_resume' => '/student/resume',
        'student_languages' => '/student/languages',
        'student_trainings' => '/student/trainings',
        'student_work_experience' => '/student/experience',

        // Graduate routes
        'graduate_dashboard' => '/graduate/dashboard',
        'graduate_jobs' => '/graduate/jobs',
        'graduate_applications' => '/graduate/applications',
        'graduate_notifications' => '/graduate/notifications',
        'graduate_profile' => '/graduate/profile',
        'graduate_resume' => '/graduate/resume',
        'graduate_languages' => '/graduate/languages',
        'graduate_trainings' => '/graduate/trainings',
        'graduate_work_experience' => '/graduate/experience',

        // Teacher routes
        'teacher_dashboard' => '/teacher/dashboard',
        'teacher_jobs' => '/teacher/jobs',
        'teacher_job_detail' => '/teacher/job-detail', // Requires ID
        'teacher_resume' => '/teacher/resume', // Requires ID
        'teacher_job_applications' => '/teacher/job-applications', // Requires ID
        'teacher_students' => '/teacher/students',
        'teacher_profile' => '/teacher/profile',
    ];

    if (!isset($routes[$route_name])) {
        // Fallback to index.php if route not found
        $url = url('index.php');
    } else {
        $route = $routes[$route_name];
        if (strpos($route, '.php') !== false) {
            // Direct PHP file
            $url = url($route);
        } else {
            // Friendly URL
            $url = url(ltrim($route, '/'));

            // Handle dynamic parameters like /jobs/{id}
            if (isset($params['id'])) {
                if (str_contains($route_name, '_edit')) {
                    // For edit routes like /jobs/{id}/edit
                    $url .= '/' . $params['id'] . '/edit';
                } elseif (str_contains($route_name, '_delete')) {
                    // For delete routes like /jobs/{id}/delete
                    $url .= '/' . $params['id'] . '/delete';
                } else {
                    $url .= '/' . $params['id'];
                }
            }
        }
    }

    // Add query parameters if provided
    if (!empty($params)) {
        $query_string = http_build_query($params);
        $url .= (strpos($url, '?') === false ? '?' : '&') . $query_string;
    } 

    return $url;
}

/**
 * Session Helpers
 */

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

function get_current_user_data() {
    if (!is_logged_in()) {
        return null;
    }

    static $user = null;
    if ($user === null) {
        global $pdo;
        $stmt = $pdo->prepare("SELECT u.*, r.name as role_name 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 require_login_redirect() {
    if (!is_logged_in()) {
        header('Location: ' . url('login.php'));
        exit;
    }
}

function require_role($role) {
    $user = get_current_user_data();
    if (!$user || $user['role_name'] !== $role) {
        header('Location: ' . url('index.php'));
        exit;
    }
}

/**
 * Flash Message Helpers
 */

function set_flash_message($type, $message) {
    $_SESSION['flash_messages'][] = [
        'type' => $type,
        'message' => $message,
        'timestamp' => time()
    ];
}

function get_flash_messages() {
    $messages = $_SESSION['flash_messages'] ?? [];
    unset($_SESSION['flash_messages']);
    return $messages;
}

function display_flash_messages() {
    $messages = get_flash_messages();
    foreach ($messages as $message) {
        $alert_class = match($message['type']) {
            'success' => 'alert-success',
            'error' => 'alert-danger',
            'warning' => 'alert-warning',
            'info' => 'alert-info',
            default => 'alert-secondary'
        };

        echo '<div class="alert ' . $alert_class . ' alert-dismissible fade show" role="alert">';
        echo h($message['message']);
        echo '<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>';
        echo '</div>';
    }
}

/**
 * Date/Time Helpers
 */

function format_date($date, $format = 'd/m/Y') {
    if (empty($date)) return '';
    return date($format, strtotime($date));
}

function time_ago($datetime) {
    $time = strtotime($datetime);
    $now = time();
    $diff = $now - $time;

    if ($diff < 60) {
        return 'hace ' . $diff . ' segundos';
    } elseif ($diff < 3600) {
        $minutes = floor($diff / 60);
        return 'hace ' . $minutes . ' minuto' . ($minutes > 1 ? 's' : '');
    } elseif ($diff < 86400) {
        $hours = floor($diff / 3600);
        return 'hace ' . $hours . ' hora' . ($hours > 1 ? 's' : '');
    } elseif ($diff < 604800) {
        $days = floor($diff / 86400);
        return 'hace ' . $days . ' día' . ($days > 1 ? 's' : '');
    } else {
        return format_date($datetime);
    }
}

/**
 * File Helpers
 */

function format_bytes($bytes, $precision = 2) {
    $units = ['B', 'KB', 'MB', 'GB', 'TB'];

    $bytes = max($bytes, 0);
    $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
    $pow = min($pow, count($units) - 1);

    $bytes /= (1 << (10 * $pow));

    return round($bytes, $precision) . ' ' . $units[$pow];
}

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

function is_image($filename) {
    $ext = get_file_extension($filename);
    return in_array($ext, ['jpg', 'jpeg', 'png', 'gif', 'webp']);
}

function is_pdf($filename) {
    return get_file_extension($filename) === 'pdf';
}

/**
 * Array Helpers
 */

function array_get($array, $key, $default = null) {
    return isset($array[$key]) ? $array[$key] : $default;
}

function array_has($array, $key) {
    return isset($array[$key]);
}

function array_only($array, $keys) {
    return array_intersect_key($array, array_flip($keys));
}

function array_except($array, $keys) {
    return array_diff_key($array, array_flip($keys));
}

/**
 * Error Monitoring and Logging Helpers
 */

function log_application_error($message, $context = [], $level = 'ERROR') {
    $timestamp = date('Y-m-d H:i:s');
    $user_id = $_SESSION['user_id'] ?? 'guest';
    $request_uri = $_SERVER['REQUEST_URI'] ?? 'unknown';
    $method = $_SERVER['REQUEST_METHOD'] ?? 'unknown';

    $log_message = "[$timestamp] [$level] [$user_id] [$method $request_uri] $message - Context: " . json_encode($context) . "\n";
    $log_file = __DIR__ . '/../logs/application.log';

    // Create logs directory if it doesn't exist
    $log_dir = dirname($log_file);
    if (!is_dir($log_dir)) {
        if (!@mkdir($log_dir, 0755, true)) {
            error_log("Failed to create logs directory: $log_dir - " . error_get_last()['message'] ?? 'Unknown error');
            error_log("$level: $message - Context: " . json_encode($context));
            return;
        }
    }

    // Try to write to file, fallback to error_log if permission denied
    if (is_writable($log_dir)) {
        $result = file_put_contents($log_file, $log_message, FILE_APPEND | LOCK_EX);
        if ($result === false) {
            error_log("Failed to write to application log file: $log_file");
        }
    } else {
        error_log("Logs directory not writable: $log_dir");
    }

    // Always log to PHP's error log for backward compatibility
    error_log("$level: $message - Context: " . json_encode($context));
}

function log_form_error($form_name, $error_message, $context = []) {
    log_application_error("Form Error in $form_name: $error_message", $context, 'FORM_ERROR');
}

function log_database_error($operation, $error_message, $context = []) {
    log_application_error("Database Error during $operation: $error_message", $context, 'DB_ERROR');
}

function log_authentication_error($action, $error_message, $context = []) {
    log_security_event('AUTH_ERROR', "$action failed: $error_message", $context);
}

function log_validation_error($field, $value, $error_message, $context = []) {
    $context['field'] = $field;
    $context['value'] = substr($value, 0, 100); // Truncate long values
    log_application_error("Validation Error for field '$field': $error_message", $context, 'VALIDATION_ERROR');
}

/**
 * Alert System for Critical Issues
 */

function send_critical_alert($subject, $message, $context = []) {
    $timestamp = date('Y-m-d H:i:s');
    $alert_message = "[$timestamp] CRITICAL ALERT: $subject\n$message\nContext: " . json_encode($context, JSON_PRETTY_PRINT) . "\n\n";

    // Log to critical alerts file
    $alert_file = __DIR__ . '/../logs/critical_alerts.log';
    $log_dir = dirname($alert_file);
    if (!is_dir($log_dir)) {
        if (!@mkdir($log_dir, 0755, true)) {
            error_log("Failed to create logs directory: $log_dir - " . error_get_last()['message'] ?? 'Unknown error');
            error_log("CRITICAL ALERT: $subject - $message - Context: " . json_encode($context));
        } else {
            file_put_contents($alert_file, $alert_message, FILE_APPEND | LOCK_EX);
        }
    } elseif (is_writable($log_dir)) {
        $result = file_put_contents($alert_file, $alert_message, FILE_APPEND | LOCK_EX);
        if ($result === false) {
            error_log("Failed to write to critical alerts log file: $alert_file");
        }
    } else {
        error_log("Logs directory not writable: $log_dir");
    }

    // Send email alert (if email configuration exists)
    send_admin_email_alert($subject, $message, $context);

    // Log to application log as well
    log_application_error("CRITICAL ALERT: $subject - $message", $context, 'CRITICAL');
}

function send_admin_email_alert($subject, $message, $context = []) {
    // Check if email configuration exists
    if (!defined('ADMIN_EMAIL') || !ADMIN_EMAIL) {
        return false;
    }

    $to = ADMIN_EMAIL;
    $headers = "From: sistema@bolsa-trabajo.com\r\n";
    $headers .= "Content-Type: text/plain; charset=UTF-8\r\n";

    $email_body = "CRITICAL SYSTEM ALERT\n\n";
    $email_body .= "Subject: $subject\n\n";
    $email_body .= "Message: $message\n\n";
    $email_body .= "Timestamp: " . date('Y-m-d H:i:s') . "\n";
    $email_body .= "Context: " . json_encode($context, JSON_PRETTY_PRINT) . "\n\n";
    $email_body .= "Please check the system logs for more details.\n";

    // Use PHP's mail function (requires proper server configuration)
    return mail($to, "CRITICAL: $subject", $email_body, $headers);
}

function check_critical_error_threshold($error_type, $threshold = 10, $time_window = 3600) {
    $log_file = __DIR__ . '/../logs/application.log';
    if (!file_exists($log_file)) {
        return false;
    }

    $lines = file($log_file);
    $error_count = 0;
    $cutoff_time = time() - $time_window;

    foreach (array_reverse($lines) as $line) {
        if (strpos($line, "[$error_type]") !== false) {
            // Extract timestamp from log line
            if (preg_match('/\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]/', $line, $matches)) {
                $log_time = strtotime($matches[1]);
                if ($log_time >= $cutoff_time) {
                    $error_count++;
                } else {
                    break; // Older than time window
                }
            }
        }
    }

    return $error_count >= $threshold;
}

function alert_on_critical_errors() {
    // Check for high frequency of authentication errors (potential brute force)
    if (check_critical_error_threshold('AUTH_ERROR', 5, 300)) { // 5 auth errors in 5 minutes
        send_critical_alert(
            'High Authentication Error Rate',
            'Detected high frequency of authentication errors. Possible brute force attack.',
            ['threshold' => '5 errors in 5 minutes']
        );
    }

    // Check for database errors
    if (check_critical_error_threshold('DB_ERROR', 3, 600)) { // 3 DB errors in 10 minutes
        send_critical_alert(
            'Database Error Spike',
            'Multiple database errors detected. Check database connectivity and queries.',
            ['threshold' => '3 errors in 10 minutes']
        );
    }

    // Check for CSRF attacks
    if (check_critical_error_threshold('AUTH_ERROR', 10, 1800)) { // 10 CSRF failures in 30 minutes
        $csrf_errors = 0;
        $log_file = __DIR__ . '/../logs/security.log';
        if (file_exists($log_file)) {
            $lines = file($log_file);
            $cutoff_time = time() - 1800;
            foreach (array_reverse($lines) as $line) {
                if (strpos($line, 'Invalid CSRF token') !== false) {
                    if (preg_match('/\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]/', $line, $matches)) {
                        $log_time = strtotime($matches[1]);
                        if ($log_time >= $cutoff_time) {
                            $csrf_errors++;
                        }
                    }
                }
            }
        }

        if ($csrf_errors >= 10) {
            send_critical_alert(
                'CSRF Attack Detected',
                'High number of CSRF token validation failures detected.',
                ['csrf_errors' => $csrf_errors, 'time_window' => '30 minutes']
            );
        }
    }
}

// Call this function periodically (e.g., via cron job or on admin dashboard load)
function monitor_system_health() {
    alert_on_critical_errors();

    // Additional health checks can be added here
    // - Check database connectivity
    // - Check file system permissions
    // - Check disk space
    // - Check memory usage
}

/**
 * Two-Factor Authentication (2FA) Functions
 */

function generate_2fa_secret() {
    return bin2hex(random_bytes(20));
}

function generate_2fa_qr_code_url($secret, $username, $issuer = 'Bolsa de Trabajo') {
    $url = 'otpauth://totp/' . urlencode($issuer) . ':' . urlencode($username) . '?secret=' . $secret . '&issuer=' . urlencode($issuer);
    return $url;
}

function verify_2fa_code($secret, $code) {
    $tolerance = 1; // Allow 30 seconds tolerance
    $time = floor(time() / 30);

    for ($i = -$tolerance; $i <= $tolerance; $i++) {
        $expected_code = generate_totp($secret, $time + $i);
        if (hash_equals($expected_code, $code)) {
            return true;
        }
    }
    return false;
}

function generate_totp($secret, $time) {
    $secret = base32_decode($secret);
    $time = pack('N*', 0, $time);
    $hmac = hash_hmac('sha1', $time, $secret, true);
    $offset = ord($hmac[19]) & 0xf;
    $code = unpack('N', substr($hmac, $offset, 4))[1] & 0x7fffffff;
    return str_pad($code % 1000000, 6, '0', STR_PAD_LEFT);
}

function base32_decode($data) {
    $data = strtoupper($data);
    $data = str_replace('=', '', $data);
    $binary = '';
    $chars = str_split($data);

    foreach ($chars as $char) {
        $val = strpos('ABCDEFGHIJKLMNOPQRSTUVWXYZ234567', $char);
        if ($val === false) {
            // Invalid character in base32 string
            return false;
        }
        $binary .= str_pad(decbin($val), 5, '0', STR_PAD_LEFT);
    }

    $binary_len = mb_strlen($binary, '8bit');
    $binary = substr($binary, 0, floor($binary_len / 8) * 8);
    $result = '';
    $binary_len = mb_strlen($binary, '8bit');
    for ($i = 0; $i < $binary_len; $i += 8) {
        $result .= chr(bindec(substr($binary, $i, 8)));
    }

    return $result;
}

function is_2fa_enabled($user_id) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT 2fa_secret FROM users WHERE id = ? AND 2fa_secret IS NOT NULL");
    $stmt->execute([$user_id]);
    return $stmt->fetch() !== false;
}

function require_2fa_for_admin($user_id) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT r.name FROM users u JOIN roles r ON u.role_id = r.id WHERE u.id = ?");
    $stmt->execute([$user_id]);
    $role = $stmt->fetch();

    return $role && $role['name'] === 'administrador';
}

/**
 * Session Security Improvements
 */

function init_secure_session() {
    // Set secure session parameters
    ini_set('session.cookie_secure', SESSION_SECURE);
    ini_set('session.cookie_httponly', SESSION_HTTPONLY);
    ini_set('session.cookie_samesite', SESSION_SAMESITE);
    ini_set('session.gc_maxlifetime', SESSION_LIFETIME);

    // Start session if not already started
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }

    // Regenerate session ID periodically for security (only on GET requests to avoid breaking POST forms)
    if ($_SERVER['REQUEST_METHOD'] === 'GET') {
        if (!isset($_SESSION['last_regeneration'])) {
            $_SESSION['last_regeneration'] = time();
        } elseif (time() - $_SESSION['last_regeneration'] > 300) { // Regenerate every 5 minutes
            session_regenerate_id(true);
            $_SESSION['last_regeneration'] = time();
        }
    }

    // Check session expiry
    if (isset($_SESSION['user_id']) && isset($_SESSION['session_start'])) {
        if (time() - $_SESSION['session_start'] > SESSION_LIFETIME) {
            session_destroy();
            header('Location: login.php?expired=1');
            exit;
        }
    } elseif (isset($_SESSION['user_id'])) {
        $_SESSION['session_start'] = time();
    }
}

function encrypt_session_data($data) {
    $key = substr(hash('sha256', ENCRYPTION_KEY, true), 0, 32);
    $iv = random_bytes(16);
    $encrypted = openssl_encrypt($data, 'AES-256-CBC', $key, 0, $iv);
    return base64_encode($iv . $encrypted);
}

function decrypt_session_data($encrypted_data) {
    $key = substr(hash('sha256', ENCRYPTION_KEY, true), 0, 32);
    $data = base64_decode($encrypted_data);
    $iv = substr($data, 0, 16);
    $encrypted = substr($data, 16);
    return openssl_decrypt($encrypted, 'AES-256-CBC', $key, 0, $iv);
}

/**
 * Rate Limiting Functions
 */

function check_rate_limit($action, $identifier = null) {
    $identifier = $identifier ?? $_SERVER['REMOTE_ADDR'];
    $key = "rate_limit_{$action}_{$identifier}";
    $now = time();

    if (!isset($_SESSION[$key])) {
        $_SESSION[$key] = ['count' => 1, 'reset' => $now + RATE_LIMIT_WINDOW];
        return true;
    }

    if ($now > $_SESSION[$key]['reset']) {
        $_SESSION[$key] = ['count' => 1, 'reset' => $now + RATE_LIMIT_WINDOW];
        return true;
    }

    if ($_SESSION[$key]['count'] >= RATE_LIMIT_REQUESTS) {
        return false;
    }

    $_SESSION[$key]['count']++;
    return true;
}

function check_login_attempts($username) {
    $key = "login_attempts_" . md5($username);
    $now = time();

    if (!isset($_SESSION[$key])) {
        return true;
    }

    if ($now > $_SESSION[$key]['reset']) {
        return true;
    }

    if ($_SESSION[$key]['count'] >= MAX_LOGIN_ATTEMPTS) {
        return false;
    }

    return true;
}

function increment_login_attempts($username) {
    $key = "login_attempts_" . md5($username);
    $now = time();

    if (!isset($_SESSION[$key])) {
        $_SESSION[$key] = ['count' => 1, 'reset' => $now + ACCOUNT_LOCKOUT_TIME];
    } else {
        $_SESSION[$key]['count']++;
    }
}

function reset_login_attempts($username) {
    $key = "login_attempts_" . md5($username);
    unset($_SESSION[$key]);
}

/**
 * Database Audit Logging
 */

function log_database_change($action, $table, $record_id, $old_values = null, $new_values = null) {
    global $pdo;

    try {
        $user_id = $_SESSION['user_id'] ?? null;
        $ip_address = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
        $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? 'unknown';

        $stmt = $pdo->prepare("
            INSERT INTO audit_log (user_id, action, table_name, record_id, old_values, new_values, ip_address, user_agent)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt->execute([
            $user_id,
            $action,
            $table,
            $record_id,
            $old_values ? json_encode($old_values) : null,
            $new_values ? json_encode($new_values) : null,
            $ip_address,
            $user_agent
        ]);

        log_application_error("Database audit: $action on $table (ID: $record_id)", [
            'user_id' => $user_id,
            'old_values' => $old_values,
            'new_values' => $new_values
        ], 'AUDIT');

    } catch (Exception $e) {
        log_database_error('audit_logging', 'Failed to log database change: ' . $e->getMessage());
    }
}

function audit_user_creation($user_data) {
    log_database_change('INSERT', 'users', $user_data['id'], null, $user_data);
}

function audit_user_update($user_id, $old_data, $new_data) {
    log_database_change('UPDATE', 'users', $user_id, $old_data, $new_data);
}

function audit_user_deletion($user_id, $user_data) {
    log_database_change('DELETE', 'users', $user_id, $user_data, null);
}

/**
 * Database Optimization and Caching
 */

class DatabaseOptimizer {
    private static $cache = [];
    private static $cache_expiry = [];

    public static function get_cached_query($query, $params = [], $cache_key = null, $ttl = 300) {
        if (!$cache_key) {
            $cache_key = md5($query . serialize($params));
        }

        // Check if cached and not expired
        if (isset(self::$cache[$cache_key]) && (!isset(self::$cache_expiry[$cache_key]) || time() < self::$cache_expiry[$cache_key])) {
            return self::$cache[$cache_key];
        }

        // Execute query
        global $pdo;
        $stmt = $pdo->prepare($query);
        $stmt->execute($params);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Cache result
        self::$cache[$cache_key] = $result;
        self::$cache_expiry[$cache_key] = time() + $ttl;

        return $result;
    }

    public static function invalidate_cache($pattern = null) {
        if ($pattern) {
            foreach (self::$cache as $key => $value) {
                if (strpos($key, $pattern) !== false) {
                    unset(self::$cache[$key]);
                    unset(self::$cache_expiry[$key]);
                }
            }
        } else {
            self::$cache = [];
            self::$cache_expiry = [];
        }
    }

    public static function optimize_query($query) {
        // Add EXPLAIN analysis for SELECT queries
        if (stripos($query, 'SELECT') === 0) {
            global $pdo;
            $explain_query = "EXPLAIN " . $query;
            $stmt = $pdo->prepare($explain_query);
            $stmt->execute();
            $explain = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Log slow queries (basic implementation)
            foreach ($explain as $row) {
                if (isset($row['rows']) && $row['rows'] > 1000) {
                    log_application_error("Potentially slow query detected", [
                        'query' => $query,
                        'explain' => $row
                    ], 'SLOW_QUERY');
                }
            }
        }

        return $query;
    }
}

// Optimized frequently used queries
function get_careers_cached() {
    return DatabaseOptimizer::get_cached_query(
        "SELECT id, name FROM careers ORDER BY name",
        [],
        'careers_list',
        3600 // Cache for 1 hour
    );
}

function get_roles_cached() {
    return DatabaseOptimizer::get_cached_query(
        "SELECT id, name FROM roles ORDER BY name",
        [],
        'roles_list',
        3600
    );
}

function get_user_stats_cached() {
    return DatabaseOptimizer::get_cached_query("
        SELECT
            (SELECT COUNT(*) FROM companies) as total_companies,
            (SELECT COUNT(*) FROM students) as total_students,
            (SELECT COUNT(*) FROM graduates) as total_graduates,
            (SELECT COUNT(*) FROM teachers) as total_teachers
    ", [], 'user_stats', 600); // Cache for 10 minutes
}

function get_jobs_by_career_cached() {
    return DatabaseOptimizer::get_cached_query("
        SELECT c.name as career, COUNT(j.id) as count
        FROM careers c
        LEFT JOIN jobs j ON c.id = j.career_id
        GROUP BY c.id
        ORDER BY count DESC
    ", [], 'jobs_by_career', 1800); // Cache for 30 minutes
}

/**
 * CDN Integration Helpers
 */

function cdn_asset($path, $cdn_base = null) {
    // CDN integration disabled - always use local assets
    return asset($path);
}

function preload_critical_assets() {
    // Only preload critical assets to avoid blocking
    $critical_assets = [
        '../assets/css/dminlte.css',
        'https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css'
    ];

    $preload_tags = '';
    foreach ($critical_assets as $asset) {
        if (strpos($asset, 'http') === 0) {
            $preload_tags .= '<link rel="preload" href="' . $asset . '" as="style" crossorigin>' . "\n";
        } else {
            // Use CDN if available, otherwise local
            $asset_url = cdn_asset($asset);
            $preload_tags .= '<link rel="preload" href="' . $asset_url . '" as="style">' . "\n";
        }
    }

    return $preload_tags;
}

/**
 * Code Profiling and Performance Monitoring
 */

class PerformanceProfiler {
    private static $start_times = [];
    private static $memory_usage = [];

    public static function start_profiling($label) {
        self::$start_times[$label] = microtime(true);
        self::$memory_usage[$label] = memory_get_usage();
    }

    public static function end_profiling($label, $log_slow = true, $threshold_ms = 100) {
        if (!isset(self::$start_times[$label])) {
            return null;
        }

        $end_time = microtime(true);
        $end_memory = memory_get_usage();

        $execution_time = ($end_time - self::$start_times[$label]) * 1000; // Convert to milliseconds
        $memory_used = $end_memory - self::$memory_usage[$label];

        $profile_data = [
            'label' => $label,
            'execution_time_ms' => round($execution_time, 2),
            'memory_used_bytes' => $memory_used,
            'memory_used_mb' => round($memory_used / 1024 / 1024, 2)
        ];

        // Log slow operations
        if ($log_slow && $execution_time > $threshold_ms) {
            log_application_error("Slow operation detected", $profile_data, 'PERFORMANCE');
        }

        unset(self::$start_times[$label], self::$memory_usage[$label]);

        return $profile_data;
    }

    public static function get_page_load_time() {
        if (!isset($_SERVER['REQUEST_TIME_FLOAT'])) {
            return null;
        }

        $load_time = (microtime(true) - $_SERVER['REQUEST_TIME_FLOAT']) * 1000;
        return round($load_time, 2);
    }

    public static function log_page_performance() {
        $load_time = self::get_page_load_time();
        $memory_peak = memory_get_peak_usage();
        $uri = $_SERVER['REQUEST_URI'] ?? 'unknown';

        if ($load_time > 1000) { // Log pages taking more than 1 second
            log_application_error("Slow page load detected", [
                'uri' => $uri,
                'load_time_ms' => $load_time,
                'memory_peak_mb' => round($memory_peak / 1024 / 1024, 2)
            ], 'PAGE_PERFORMANCE');
        }
    }
}

// Auto-log page performance on shutdown
register_shutdown_function(function() {
    PerformanceProfiler::log_page_performance();
});

/**
 * Real-Time Dashboard System
 */

class RealTimeDashboard {
    private static $metrics = [];
    private static $last_update = 0;
    private static $update_interval = 30; // seconds

    public static function get_dashboard_data($force_refresh = false) {
        $now = time();

        if (!$force_refresh && ($now - self::$last_update) < self::$update_interval && !empty(self::$metrics)) {
            return self::$metrics;
        }

        self::$metrics = [
            'timestamp' => $now,
            'system' => self::get_system_metrics(),
            'users' => self::get_user_metrics(),
            'jobs' => self::get_job_metrics(),
            'applications' => self::get_application_metrics(),
            'performance' => self::get_performance_metrics(),
            'security' => self::get_security_metrics()
        ];

        self::$last_update = $now;
        return self::$metrics;
    }

    private static function get_system_metrics() {
        return [
            'server_load' => function_exists('sys_getloadavg') ? sys_getloadavg()[0] : null,
            'memory_usage' => self::get_memory_usage(),
            'disk_usage' => self::get_disk_usage(),
            'uptime' => self::get_system_uptime(),
            'php_version' => PHP_VERSION,
            'database_connections' => self::get_db_connection_count()
        ];
    }

    private static function get_user_metrics() {
        global $pdo;

        try {
            $stmt = $pdo->query("
                SELECT
                    COUNT(CASE WHEN role_id = 1 THEN 1 END) as admins,
                    COUNT(CASE WHEN role_id = 2 THEN 1 END) as teachers,
                    COUNT(CASE WHEN role_id = 3 THEN 1 END) as students,
                    COUNT(CASE WHEN role_id = 4 THEN 1 END) as companies,
                    COUNT(CASE WHEN role_id = 5 THEN 1 END) as graduates,
                    COUNT(*) as total_users,
                    COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) as new_users_24h,
                    COUNT(CASE WHEN last_login >= DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 1 END) as active_users_1h
                FROM users
            ");
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return ['error' => 'Failed to fetch user metrics'];
        }
    }

    private static function get_job_metrics() {
        global $pdo;

        try {
            $stmt = $pdo->query("
                SELECT
                    COUNT(*) as total_jobs,
                    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_jobs,
                    COUNT(CASE WHEN status = 'filled' THEN 1 END) as filled_jobs,
                    COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive_jobs,
                    COUNT(CASE WHEN posted_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) as new_jobs_24h,
                    COUNT(CASE WHEN expiration_date < CURDATE() AND status = 'active' THEN 1 END) as expired_jobs,
                    AVG(salary) as avg_salary,
                    COUNT(DISTINCT company_id) as active_companies
                FROM jobs
            ");
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return ['error' => 'Failed to fetch job metrics'];
        }
    }

    private static function get_application_metrics() {
        global $pdo;

        try {
            $stmt = $pdo->query("
                SELECT
                    COUNT(*) as total_applications,
                    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_applications,
                    COUNT(CASE WHEN status = 'reviewed' THEN 1 END) as reviewed_applications,
                    COUNT(CASE WHEN status = 'accepted' THEN 1 END) as accepted_applications,
                    COUNT(CASE WHEN status = 'rejected' THEN 1 END) as rejected_applications,
                    COUNT(CASE WHEN applied_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) as new_applications_24h,
                    AVG(DATEDIFF(NOW(), applied_date)) as avg_response_time_days
                FROM applications
            ");
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return ['error' => 'Failed to fetch application metrics'];
        }
    }

    private static function get_performance_metrics() {
        $log_file = __DIR__ . '/../logs/application.log';
        $performance_data = [
            'slow_pages_count' => 0,
            'avg_response_time' => 0,
            'error_rate' => 0,
            'cache_hit_rate' => 95 // Placeholder - would need actual cache metrics
        ];

        if (file_exists($log_file)) {
            $lines = file($log_file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            $slow_pages = 0;
            $total_response_time = 0;
            $response_count = 0;
            $errors = 0;
            $total_lines = count($lines);

            foreach (array_slice($lines, -1000) as $line) { // Last 1000 log entries
                if (strpos($line, 'load_time_ms') !== false) {
                    preg_match('/load_time_ms["\s:]+([0-9.]+)/', $line, $matches);
                    if (isset($matches[1])) {
                        $load_time = floatval($matches[1]);
                        $total_response_time += $load_time;
                        $response_count++;
                        if ($load_time > 1000) {
                            $slow_pages++;
                        }
                    }
                }
                if (strpos($line, 'ERROR') !== false || strpos($line, 'CRITICAL') !== false) {
                    $errors++;
                }
            }

            $performance_data['slow_pages_count'] = $slow_pages;
            $performance_data['avg_response_time'] = $response_count > 0 ? round($total_response_time / $response_count, 2) : 0;
            $performance_data['error_rate'] = $total_lines > 0 ? round(($errors / $total_lines) * 100, 2) : 0;
        }

        return $performance_data;
    }

    private static function get_security_metrics() {
        $security_data = [
            'failed_logins_24h' => 0,
            'blocked_ips_24h' => 0,
            'csrf_attempts_24h' => 0,
            'active_sessions' => 0,
            'security_alerts_24h' => 0
        ];

        // Check security logs
        $security_log = __DIR__ . '/../logs/security.log';
        if (file_exists($security_log)) {
            $lines = file($security_log, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            $yesterday = strtotime('-24 hours');

            foreach (array_slice($lines, -500) as $line) {
                if (preg_match('/\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]/', $line, $matches)) {
                    $log_time = strtotime($matches[1]);
                    if ($log_time >= $yesterday) {
                        if (strpos($line, 'Invalid CSRF token') !== false) {
                            $security_data['csrf_attempts_24h']++;
                        }
                        if (strpos($line, 'ACCOUNT_LOCKOUT') !== false) {
                            $security_data['blocked_ips_24h']++;
                        }
                        if (strpos($line, 'AUTH_ERROR') !== false) {
                            $security_data['failed_logins_24h']++;
                        }
                        if (strpos($line, 'CRITICAL') !== false) {
                            $security_data['security_alerts_24h']++;
                        }
                    }
                }
            }
        }

        // Count active sessions (simplified - would need session storage access)
        $session_files = glob(session_save_path() . '/sess_*');
        $security_data['active_sessions'] = count($session_files);

        return $security_data;
    }

    private static function get_memory_usage() {
        $memory_usage = memory_get_usage(true);
        $memory_limit = self::parse_size(ini_get('memory_limit'));

        return [
            'used' => $memory_usage,
            'used_mb' => round($memory_usage / 1024 / 1024, 2),
            'limit' => $memory_limit,
            'limit_mb' => round($memory_limit / 1024 / 1024, 2),
            'percentage' => $memory_limit > 0 ? round(($memory_usage / $memory_limit) * 100, 2) : 0
        ];
    }

    private static function get_disk_usage() {
        $disk_total = disk_total_space('/');
        $disk_free = disk_free_space('/');

        return [
            'total' => $disk_total,
            'total_gb' => round($disk_total / 1024 / 1024 / 1024, 2),
            'free' => $disk_free,
            'free_gb' => round($disk_free / 1024 / 1024 / 1024, 2),
            'used' => $disk_total - $disk_free,
            'used_gb' => round(($disk_total - $disk_free) / 1024 / 1024 / 1024, 2),
            'percentage' => $disk_total > 0 ? round((($disk_total - $disk_free) / $disk_total) * 100, 2) : 0
        ];
    }

    private static function get_system_uptime() {
        if (function_exists('shell_exec')) {
            $uptime = shell_exec('uptime -p 2>/dev/null');
            if ($uptime) {
                return trim($uptime);
            }
        }
        return 'Unknown';
    }

    private static function get_db_connection_count() {
        global $pdo;
        try {
            $stmt = $pdo->query("SHOW PROCESSLIST");
            return $stmt->rowCount();
        } catch (Exception $e) {
            return 0;
        }
    }

    private static function parse_size($size) {
        $unit = strtolower(substr($size, -1));
        $value = (int)$size;

        switch ($unit) {
            case 'g': $value *= 1024 * 1024 * 1024; break;
            case 'm': $value *= 1024 * 1024; break;
            case 'k': $value *= 1024; break;
        }

        return $value;
    }

    public static function get_chart_data($metric, $period = '24h') {
        // Return data formatted for charts
        $data = self::get_dashboard_data();

        switch ($metric) {
            case 'user_growth':
                return self::get_user_growth_chart($period);
            case 'job_trends':
                return self::get_job_trends_chart($period);
            case 'application_rates':
                return self::get_application_rates_chart($period);
            case 'performance_trends':
                return self::get_performance_trends_chart($period);
            default:
                return [];
        }
    }

    private static function get_user_growth_chart($period) {
        global $pdo;

        $interval = $period === '7d' ? '7 DAY' : ($period === '30d' ? '30 DAY' : '24 HOUR');

        try {
            $stmt = $pdo->prepare("
                SELECT
                    DATE(created_at) as date,
                    COUNT(*) as count
                FROM users
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL {$interval})
                GROUP BY DATE(created_at)
                ORDER BY date
            ");
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return [];
        }
    }

    private static function get_job_trends_chart($period) {
        global $pdo;

        $interval = $period === '7d' ? '7 DAY' : ($period === '30d' ? '30 DAY' : '24 HOUR');

        try {
            $stmt = $pdo->prepare("
                SELECT
                    DATE(posted_date) as date,
                    COUNT(*) as count
                FROM jobs
                WHERE posted_date >= DATE_SUB(NOW(), INTERVAL {$interval})
                GROUP BY DATE(posted_date)
                ORDER BY date
            ");
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return [];
        }
    }

    private static function get_application_rates_chart($period) {
        global $pdo;

        $interval = $period === '7d' ? '7 DAY' : ($period === '30d' ? '30 DAY' : '24 HOUR');

        try {
            $stmt = $pdo->prepare("
                SELECT
                    DATE(applied_date) as date,
                    COUNT(*) as count
                FROM applications
                WHERE applied_date >= DATE_SUB(NOW(), INTERVAL {$interval})
                GROUP BY DATE(applied_date)
                ORDER BY date
            ");
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            return [];
        }
    }

    private static function get_performance_trends_chart($period) {
        // This would aggregate performance data from logs
        // For now, return mock data
        return [
            ['date' => date('Y-m-d', strtotime('-6 days')), 'avg_response_time' => 450],
            ['date' => date('Y-m-d', strtotime('-5 days')), 'avg_response_time' => 420],
            ['date' => date('Y-m-d', strtotime('-4 days')), 'avg_response_time' => 380],
            ['date' => date('Y-m-d', strtotime('-3 days')), 'avg_response_time' => 390],
            ['date' => date('Y-m-d', strtotime('-2 days')), 'avg_response_time' => 410],
            ['date' => date('Y-m-d', strtotime('-1 day')), 'avg_response_time' => 430],
            ['date' => date('Y-m-d'), 'avg_response_time' => 400],
        ];
    }
}