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

use Firebase\JWT\JWT as FirebaseJWT;
use Firebase\JWT\Key;

/**
 * MSPE Website Configuration
 * 
 * Database and application settings
 * Reads from .env file for production credentials
 */

// ── Load .env ────────────────────────────────────────────────
function loadEnv($path) {
    if (!file_exists($path)) return;
    $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#') continue;
        if (strpos($line, '=') === false) continue;
        list($key, $value) = explode('=', $line, 2);
        $key = trim($key);
        $value = trim($value);
        // Remove surrounding quotes
        if ((strlen($value) > 1) && (($value[0] === '"' && substr($value, -1) === '"') || ($value[0] === "'" && substr($value, -1) === "'"))) {
            $value = substr($value, 1, -1);
        }
        $_ENV[$key] = $value;
        putenv("$key=$value");
    }
}

// Try loading .env from project root, then from one level up (outside public_html)
loadEnv(__DIR__ . '/../.env');
loadEnv(dirname(__DIR__, 2) . '/.env');

// ── Helpers to read env values ───────────────────────────────
function env($key, $default = '') {
    return $_ENV[$key] ?? getenv($key) ?: $default;
}

// ── Error reporting ──────────────────────────────────────────
$appEnv = env('APP_ENV', 'production');
if ($appEnv === 'production') {
    error_reporting(0);
    ini_set('display_errors', 0);
} else {
    error_reporting(E_ALL);
    ini_set('display_errors', 1);
}

// Timezone
date_default_timezone_set('UTC');

// ── Database Configuration ───────────────────────────────────
define('DB_TYPE', env('DB_TYPE', 'file'));
define('DB_HOST', env('DB_HOST', 'localhost'));
define('DB_NAME', env('DB_NAME', 'mspe_website'));
define('DB_USER', env('DB_USER', ''));
define('DB_PASS', env('DB_PASS', ''));

// ── Admin credentials ────────────────────────────────────────
define('ADMIN_USER', env('ADMIN_USER', 'admin'));
define('ADMIN_PASS', env('ADMIN_PASS', ''));

// ── JWT Secret ───────────────────────────────────────────────
define('JWT_SECRET', env('JWT_SECRET', 'change-me-in-env-file'));
if (JWT_SECRET === 'change-me-in-env-file' && $appEnv === 'production') {
    http_response_code(500);
    die(json_encode(['success' => false, 'message' => 'Server misconfigured']));
}

// ── Data directory (for JSON files, cache, etc.) ────────────
define('DATA_DIR', __DIR__ . '/../data/');

// ── Upload settings ──────────────────────────────────────────
define('UPLOAD_DIR', __DIR__ . '/../uploads/');
define('MAX_UPLOAD_SIZE', 5 * 1024 * 1024); // 5MB
define('ALLOWED_EXTENSIONS', ['jpg', 'jpeg', 'png', 'gif', 'webp', 'pdf']);

// ── Site settings ────────────────────────────────────────────
define('SITE_NAME', env('SITE_NAME', 'MSPE'));
define('SITE_URL', env('SITE_URL', 'https://mspe.pro'));
define('ADMIN_EMAIL', env('ADMIN_EMAIL', 'info@mspe.pro'));

// ── SMTP settings (read from .env, used as defaults before admin settings override) ──
define('ENV_SMTP_HOST', env('SMTP_HOST', ''));
define('ENV_SMTP_PORT', env('SMTP_PORT', '465'));
define('ENV_SMTP_USER', env('SMTP_USER', ''));
define('ENV_SMTP_PASS', env('SMTP_PASS', ''));
define('ENV_SMTP_FROM_NAME', env('SMTP_FROM_NAME', 'MSPE'));
define('ENV_SMTP_FROM_EMAIL', env('SMTP_FROM_EMAIL', 'info@mspe.pro'));

// ── CORS Headers ─────────────────────────────────────────────
$allowedOrigins = [SITE_URL, 'https://www.mspe.pro'];
// Allow localhost for local development
if ($appEnv !== 'production') {
    $allowedOrigins[] = 'http://localhost:8080';
    $allowedOrigins[] = 'http://localhost:8000';
    $allowedOrigins[] = 'http://127.0.0.1:8080';
}
$origin = $_SERVER['HTTP_ORIGIN'] ?? '';
if (in_array($origin, $allowedOrigins)) {
    header('Access-Control-Allow-Origin: ' . $origin);
} else {
    header('Access-Control-Allow-Origin: ' . SITE_URL);
}
header('Vary: Origin');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');
header('Access-Control-Allow-Credentials: true');
header('Content-Type: application/json; charset=UTF-8');

// Handle preflight requests
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}

// ── Security Headers ─────────────────────────────────────────
header('X-Content-Type-Options: nosniff');
header('X-Frame-Options: SAMEORIGIN');
header('X-XSS-Protection: 0');
header('Referrer-Policy: strict-origin-when-cross-origin');
header("Content-Security-Policy: default-src 'none'; frame-ancestors 'none'");
header('Permissions-Policy: camera=(), microphone=(), geolocation=(), payment=()');
if ((!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') || ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https') {
    header('Strict-Transport-Security: max-age=31536000; includeSubDomains; preload');
}

/**
 * MySQL Database Helper
 */
class MySQLDB {
    private $pdo;

    public function __construct() {
        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';
        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ];
        $this->pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
    }

    /** Ensure the table exists (auto-create a generic key-value/json table) */
    private function ensureTable($table) {
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        if ($safe === '' || strlen($safe) > 64) {
            throw new Exception('Invalid table name');
        }
        $this->pdo->exec("CREATE TABLE IF NOT EXISTS `{$safe}` (
            `id` VARCHAR(64) NOT NULL PRIMARY KEY,
            `data` JSON NOT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    public function getAll($table) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $stmt = $this->pdo->query("SELECT `data` FROM `$safe` ORDER BY `created_at` ASC");
        $rows = $stmt->fetchAll();
        return array_map(function($r) { return json_decode($r['data'], true); }, $rows);
    }

    /**
     * Get records with SQL-level pagination (LIMIT/OFFSET).
     * Returns ['data' => [...], 'total' => int].
     */
    public function getAllPaginated($table, $limit = 50, $offset = 0, $orderDir = 'ASC') {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $dir = strtoupper($orderDir) === 'DESC' ? 'DESC' : 'ASC';
        $total = (int)$this->pdo->query("SELECT COUNT(*) FROM `{$safe}`")->fetchColumn();
        $stmt = $this->pdo->prepare("SELECT `data` FROM `{$safe}` ORDER BY `created_at` {$dir} LIMIT ? OFFSET ?");
        $stmt->execute([(int)$limit, (int)$offset]);
        $rows = $stmt->fetchAll();
        return [
            'data' => array_map(function($r) { return json_decode($r['data'], true); }, $rows),
            'total' => $total
        ];
    }

    public function get($table, $id) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $stmt = $this->pdo->prepare("SELECT `data` FROM `$safe` WHERE `id` = ? LIMIT 1");
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return $row ? json_decode($row['data'], true) : null;
    }

    public function insert($table, $data) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $data['id'] = $data['id'] ?? bin2hex(random_bytes(16));
        $data['created_at'] = $data['created_at'] ?? date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare("INSERT INTO `$safe` (`id`, `data`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?)");
        $stmt->execute([$data['id'], json_encode($data, JSON_UNESCAPED_UNICODE), $data['created_at'], $data['updated_at']]);
        return $data;
    }

    public function update($table, $id, $data) {
        $existing = $this->get($table, $id);
        if (!$existing) return null;
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $data['id'] = $id;
        $data['created_at'] = $existing['created_at'];
        $data['updated_at'] = date('Y-m-d H:i:s');
        $merged = array_merge($existing, $data);
        $stmt = $this->pdo->prepare("UPDATE `$safe` SET `data` = ?, `updated_at` = ? WHERE `id` = ?");
        $stmt->execute([json_encode($merged, JSON_UNESCAPED_UNICODE), $merged['updated_at'], $id]);
        return $merged;
    }

    public function delete($table, $id) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $stmt = $this->pdo->prepare("DELETE FROM `$safe` WHERE `id` = ?");
        $stmt->execute([$id]);
        return true;
    }

    public function query($table, $conditions = []) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);

        if (empty($conditions)) {
            return $this->getAll($table);
        }

        // Use MySQL JSON_EXTRACT for server-side filtering when possible
        $where = [];
        $params = [];
        foreach ($conditions as $key => $value) {
            $safeKey = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $where[] = "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeKey}')) = ?";
            $params[] = (string)$value;
        }

        $sql = "SELECT `data` FROM `{$safe}` WHERE " . implode(' AND ', $where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        return array_map(function($r) { return json_decode($r['data'], true); }, $rows);
    }

    /** Count rows matching conditions (avoids loading all data into memory) */
    public function count($table, $conditions = []) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);

        if (empty($conditions)) {
            return (int)$this->pdo->query("SELECT COUNT(*) FROM `{$safe}`")->fetchColumn();
        }

        $where = [];
        $params = [];
        foreach ($conditions as $key => $value) {
            $safeKey = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $where[] = "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeKey}')) = ?";
            $params[] = (string)$value;
        }

        $sql = "SELECT COUNT(*) FROM `{$safe}` WHERE " . implode(' AND ', $where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /** Count rows where a JSON date-like field is between two bounds (inclusive) */
    public function countByDateRange($table, $dateField, $fromValue, $toValue, $conditions = []) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $safeDateField = preg_replace('/[^a-zA-Z0-9_]/', '', $dateField);

        $where = [
            "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeDateField}')) >= ?",
            "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeDateField}')) <= ?"
        ];
        $params = [(string)$fromValue, (string)$toValue];

        foreach ($conditions as $key => $value) {
            $safeKey = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $where[] = "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeKey}')) = ?";
            $params[] = (string)$value;
        }

        $sql = "SELECT COUNT(*) FROM `{$safe}` WHERE " . implode(' AND ', $where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /** Count rows where a single JSON field matches a comparison operator */
    public function countByFieldComparison($table, $field, $operator, $value, $conditions = []) {
        $this->ensureTable($table);
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $safeField = preg_replace('/[^a-zA-Z0-9_]/', '', $field);

        $allowedOps = ['=', '!=', '>', '>=', '<', '<='];
        $op = in_array($operator, $allowedOps, true) ? $operator : '=';

        $where = ["JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeField}')) {$op} ?"];
        $params = [(string)$value];

        foreach ($conditions as $key => $condValue) {
            $safeKey = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $where[] = "JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.{$safeKey}')) = ?";
            $params[] = (string)$condValue;
        }

        $sql = "SELECT COUNT(*) FROM `{$safe}` WHERE " . implode(' AND ', $where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }
}

/**
 * Simple JWT Helper
 */
class JWT {
    public static function encode($payload, $ttlSeconds = 86400, $secret = JWT_SECRET) {
        $payload['exp'] = time() + max(300, (int)$ttlSeconds);
        return FirebaseJWT::encode($payload, $secret, 'HS256');
    }
    
    public static function decode($token, $secret = JWT_SECRET) {
        try {
            $decoded = FirebaseJWT::decode($token, new Key($secret, 'HS256'));
            return (array) $decoded;
        } catch (Exception $e) {
            return null;
        }
    }
}

/**
 * Authentication Check
 */
function checkAuth() {
    // Prefer HttpOnly cookie token for admin authentication
    $cookieToken = $_COOKIE['mspe_admin_token'] ?? '';
    if (is_string($cookieToken) && $cookieToken !== '') {
        $payload = JWT::decode($cookieToken);
        if ($payload) {
            return $payload;
        }
    }

    // Fallback: Authorization header (for API clients)
    $authHeader = '';
    if (function_exists('getallheaders')) {
        $headers = getallheaders();
        $authHeader = $headers['Authorization'] ?? $headers['authorization'] ?? '';
    }

    if ($authHeader === '' && isset($_SERVER['HTTP_AUTHORIZATION'])) {
        $authHeader = $_SERVER['HTTP_AUTHORIZATION'];
    }

    if (preg_match('/Bearer\s+(.*)$/i', $authHeader, $matches)) {
        $payload = JWT::decode($matches[1]);
        if ($payload) {
            return $payload;
        }
    }
    
    return null;
}

/**
 * Require Authentication
 */
function requireAuth() {
    $user = checkAuth();
    if (!$user) {
        http_response_code(401);
        echo json_encode(['success' => false, 'message' => 'Unauthorized']);
        exit();
    }
    return $user;
}

/**
 * JSON Response Helper
 */
function jsonResponse($data, $code = 200) {
    http_response_code($code);
    ob_clean();
    echo json_encode($data, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    die();
}

/**
 * Sanitize Input (HTML Context)
 * Note: For JavaScript context, use json_encode().
 * For URL context, use urlencode().
 */
function sanitize($input) {
    if (is_array($input)) {
        return array_map('sanitize', $input);
    }
    // ENT_QUOTES | ENT_SUBSTITUTE handles both single/double quotes and invalid characters
    return htmlspecialchars(trim($input ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

/**
 * Sanitize Rich Text (HTML Context)
 * Allows only a whitelist of safe formatting tags and strips everything else.
 * Removes all event handler attributes (onclick, onerror, etc.) from allowed tags.
 */
function sanitizeRichText($input) {
    if (!is_string($input) || trim($input) === '') return '';
    // Allow only safe formatting tags
    $allowed = '<b><strong><i><em><u><br><p><ul><ol><li><a><span><h3><h4><h5><h6><sub><sup><blockquote>';
    $cleaned = strip_tags(trim($input), $allowed);
    // Remove any event handler attributes (on*) and dangerous attributes
    $cleaned = preg_replace('/\s+on\w+\s*=\s*(?:"[^"]*"|\'[^\']*\'|[^\s>]+)/i', '', $cleaned);
    // Remove javascript: and data: from href/src attributes
    $cleaned = preg_replace('/\b(href|src)\s*=\s*(?:"(?:javascript|data):.*?"|\'(?:javascript|data):.*?\')/i', '$1=""', $cleaned);
    // Remove style attributes that could contain expressions
    $cleaned = preg_replace('/\s+style\s*=\s*(?:"[^"]*"|\'[^\']*\')/i', '', $cleaned);
    return $cleaned;
}

/**
 * Get Request Body
 */
function getRequestBody() {
    return json_decode(file_get_contents('php://input'), true) ?? [];
}

/**
 * Basic CSRF mitigation for public POST endpoints.
 * Allows only same-origin browser requests by validating Origin/Referer.
 */
function requireSameOriginRequest() {
    $appEnv = env('APP_ENV', 'production');

    $allowedOrigins = [
        rtrim((string)SITE_URL, '/'),
        'https://www.mspe.pro'
    ];

    if ($appEnv !== 'production') {
        $allowedOrigins[] = 'http://localhost:8080';
        $allowedOrigins[] = 'http://localhost:8000';
        $allowedOrigins[] = 'http://127.0.0.1:8080';
        $allowedOrigins[] = 'http://localhost';
        $allowedOrigins[] = 'http://127.0.0.1';
    }

    $origin = trim((string)($_SERVER['HTTP_ORIGIN'] ?? ''));
    if ($origin !== '') {
        if (in_array(rtrim($origin, '/'), $allowedOrigins, true)) {
            return;
        }
        jsonResponse(['success' => false, 'message' => 'Forbidden origin'], 403);
    }

    $referer = trim((string)($_SERVER['HTTP_REFERER'] ?? ''));
    if ($referer !== '') {
        foreach ($allowedOrigins as $allowedOrigin) {
            if (stripos($referer, $allowedOrigin . '/') === 0 || rtrim($referer, '/') === $allowedOrigin) {
                return;
            }
        }
        jsonResponse(['success' => false, 'message' => 'Forbidden referer'], 403);
    }

    // In development mode, allow requests without Origin/Referer headers (e.g., for curl testing)
    if ($appEnv !== 'production') {
        return;
    }

    // Browser request with neither header is suspicious for public form submissions.
    jsonResponse(['success' => false, 'message' => 'CSRF validation failed'], 403);
}

/**
 * Read settings as key => value map
 */
function getSettingsMap() {
    global $db;

    $result = [];
    $rows = $db->getAll('settings');

    foreach ($rows as $row) {
        if (isset($row['key'])) {
            $result[$row['key']] = $row['value'] ?? '';
        }
    }

    return $result;
}

/**
 * Get a single setting with fallback
 */
function getSetting($key, $default = null) {
    $settings = getSettingsMap();
    return array_key_exists($key, $settings) ? $settings[$key] : $default;
}

/**
 * Send email (mail() or SMTP based on admin settings)
 */
function sendEmail($to, $subject, $htmlBody, $plainBody = '', $replyTo = null) {
    // Validate required parameters
    if (!filter_var($to, FILTER_VALIDATE_EMAIL)) {
        return ['success' => false, 'message' => 'Invalid recipient email address'];
    }
    
    if (trim((string)$subject) === '') {
        return ['success' => false, 'message' => 'Subject cannot be empty'];
    }
    
    if (trim((string)$htmlBody) === '') {
        return ['success' => false, 'message' => 'Email body cannot be empty'];
    }
    
    if (!empty($replyTo) && !filter_var($replyTo, FILTER_VALIDATE_EMAIL)) {
        return ['success' => false, 'message' => 'Invalid reply-to email address'];
    }

    // Determine transport: admin setting → auto-detect from .env
    $defaultTransport = (ENV_SMTP_HOST !== '') ? 'smtp' : 'mail';
    $transport = strtolower((string)getSetting('email_transport', $defaultTransport));

    $fromEmail = trim((string)getSetting('smtp_from_email', ENV_SMTP_FROM_EMAIL ?: ADMIN_EMAIL));
    if ($fromEmail === '' || !filter_var($fromEmail, FILTER_VALIDATE_EMAIL)) {
        $fromEmail = ADMIN_EMAIL;
    }

    $fromName = trim((string)getSetting('smtp_from_name', ENV_SMTP_FROM_NAME ?: SITE_NAME));
    if ($fromName === '') {
        $fromName = SITE_NAME;
    }

    if ($plainBody === '') {
        $plainBody = trim(strip_tags(str_replace(['<br>', '<br/>', '<br />'], "\n", $htmlBody)));
    }

    if ($transport === 'smtp') {
        // Admin settings override .env values
        $smtpHost = trim((string)getSetting('smtp_host', ENV_SMTP_HOST));
        $smtpPort = (int)getSetting('smtp_port', ENV_SMTP_PORT ?: 465);
        $smtpEncryption = strtolower((string)getSetting('smtp_encryption', ((int)($smtpPort) === 465 ? 'ssl' : 'tls')));
        $smtpUser = trim((string)getSetting('smtp_username', ENV_SMTP_USER));
        $smtpPass = (string)getSetting('smtp_password', ENV_SMTP_PASS);

        if ($smtpHost !== '' && $smtpPort > 0 && $smtpUser !== '' && $smtpPass !== '') {
            return sendEmailViaSmtp([
                'host' => $smtpHost,
                'port' => $smtpPort,
                'encryption' => $smtpEncryption,
                'username' => $smtpUser,
                'password' => $smtpPass,
                'from_email' => $fromEmail,
                'from_name' => $fromName,
                'to' => $to,
                'subject' => $subject,
                'html' => $htmlBody,
                'plain' => $plainBody,
                'reply_to' => $replyTo
            ]);
        }
    }

    // Fallback to PHP mail()
    $boundary = 'mspe_' . md5((string)microtime(true));
    $headers = [];
    $headers[] = 'MIME-Version: 1.0';
    $headers[] = 'From: ' . formatEmailAddress($fromName, $fromEmail);
    if ($replyTo) {
        $headers[] = 'Reply-To: ' . $replyTo;
    }
    $headers[] = 'Content-Type: multipart/alternative; boundary="' . $boundary . '"';

    $body = "--{$boundary}\r\n";
    $body .= "Content-Type: text/plain; charset=UTF-8\r\n\r\n";
    $body .= $plainBody . "\r\n\r\n";
    $body .= "--{$boundary}\r\n";
    $body .= "Content-Type: text/html; charset=UTF-8\r\n\r\n";
    $body .= $htmlBody . "\r\n\r\n";
    $body .= "--{$boundary}--\r\n";

    $ok = @mail($to, $subject, $body, implode("\r\n", $headers));

    return [
        'success' => $ok,
        'message' => $ok ? 'Sent via mail()' : 'mail() failed'
    ];
}

function sendEmailViaSmtp($payload) {
    $host = $payload['host'];
    $port = (int)$payload['port'];
    $encryption = $payload['encryption'];

    // Validate port range
    if ($port < 1 || $port > 65535) {
        return ['success' => false, 'message' => 'Invalid SMTP port'];
    }

    $remote = $encryption === 'ssl' ? 'ssl://' . $host : $host;
    $errno = 0;
    $errstr = '';
    $socket = @stream_socket_client($remote . ':' . $port, $errno, $errstr, 30, STREAM_CLIENT_CONNECT);

    if (!$socket) {
        return ['success' => false, 'message' => 'SMTP connect failed: ' . ($errstr ?: 'Unknown error')];
    }

    stream_set_timeout($socket, 30);

    $expect = function($codes) use ($socket) {
        $response = '';
        $timeout = false;
        while (($line = fgets($socket, 515)) !== false) {
            $response .= $line;
            if (preg_match('/^\d{3}\s/', $line)) {
                break;
            }
        }
        
        // Check for timeout
        if ($line === false) {
            $metadata = stream_get_meta_data($socket);
            if (isset($metadata['timed_out']) && $metadata['timed_out']) {
                throw new Exception('SMTP timeout - no response from server');
            }
        }

        if ($response === '') {
            throw new Exception('No response from SMTP server');
        }

        $code = (int)substr($response, 0, 3);
        if (!in_array($code, (array)$codes, true)) {
            throw new Exception(trim($response ?: 'Unknown SMTP error'));
        }

        return $response;
    };

    $send = function($command) use ($socket) {
        if (!is_resource($socket)) {
            throw new Exception('Socket connection lost');
        }
        $bytes = fwrite($socket, $command . "\r\n");
        if ($bytes === false) {
            throw new Exception('Failed to write to socket');
        }
    };

    try {
        $expect([220]);

        $send('EHLO ' . ($_SERVER['SERVER_NAME'] ?? parse_url(SITE_URL, PHP_URL_HOST) ?? 'mspe.pro'));
        $expect([250]);

        if ($encryption === 'tls') {
            $send('STARTTLS');
            $expect([220]);

            if (!stream_socket_enable_crypto($socket, true, STREAM_CRYPTO_METHOD_TLS_CLIENT)) {
                throw new Exception('Failed to start TLS encryption');
            }

            $send('EHLO ' . ($_SERVER['SERVER_NAME'] ?? parse_url(SITE_URL, PHP_URL_HOST) ?? 'mspe.pro'));
            $expect([250]);
        }

        $send('AUTH LOGIN');
        $expect([334]);
        $send(base64_encode($payload['username']));
        $expect([334]);
        $send(base64_encode($payload['password']));
        $expect([235]);

        $send('MAIL FROM:<' . $payload['from_email'] . '>');
        $expect([250]);

        $send('RCPT TO:<' . $payload['to'] . '>');
        $expect([250, 251]);

        $send('DATA');
        $expect([354]);

        $boundary = 'mspe_' . md5((string)microtime(true));
        $headers = [];
        $headers[] = 'From: ' . formatEmailAddress($payload['from_name'], $payload['from_email']);
        $headers[] = 'To: ' . $payload['to'];
        $headers[] = 'Subject: ' . $payload['subject'];
        $headers[] = 'MIME-Version: 1.0';
        if (!empty($payload['reply_to'])) {
            $headers[] = 'Reply-To: ' . $payload['reply_to'];
        }
        $headers[] = 'Content-Type: multipart/alternative; boundary="' . $boundary . '"';

        $message = implode("\r\n", $headers) . "\r\n\r\n";
        $message .= "--{$boundary}\r\n";
        $message .= "Content-Type: text/plain; charset=UTF-8\r\n\r\n";
        $message .= $payload['plain'] . "\r\n\r\n";
        $message .= "--{$boundary}\r\n";
        $message .= "Content-Type: text/html; charset=UTF-8\r\n\r\n";
        $message .= $payload['html'] . "\r\n\r\n";
        $message .= "--{$boundary}--\r\n.";

        if (!is_resource($socket)) {
            throw new Exception('Socket connection lost before sending message');
        }
        $bytes = fwrite($socket, $message . "\r\n");
        if ($bytes === false) {
            throw new Exception('Failed to send message to SMTP server');
        }
        $expect([250]);

        $send('QUIT');
        if (is_resource($socket)) {
            fclose($socket);
        }

        return ['success' => true, 'message' => 'Sent via SMTP'];
    } catch (Exception $e) {
        if (is_resource($socket)) {
            fclose($socket);
        }
        error_log('MSPE SMTP error: ' . $e->getMessage());
        return ['success' => false, 'message' => 'Email delivery failed'];
    }
}

function formatEmailAddress($name, $email) {
    $cleanName = str_replace(['"', "\r", "\n"], '', $name);
    $cleanEmail = str_replace(["\r", "\n"], '', $email);
    return sprintf('"%s" <%s>', $cleanName, $cleanEmail);
}

/**
 * Handle File Upload
 */
function handleFileUpload($file, $subdir = '') {
    if (!isset($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) {
        return ['success' => false, 'message' => 'No file uploaded'];
    }
    
    // Check file size
    if ($file['size'] > MAX_UPLOAD_SIZE) {
        return ['success' => false, 'message' => 'File too large'];
    }
    
    // Check extension
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, ALLOWED_EXTENSIONS)) {
        return ['success' => false, 'message' => 'File type not allowed'];
    }

    // Validate MIME type from file contents (not just extension)
    $allowedMimeByExt = [
        'jpg' => ['image/jpeg'],
        'jpeg' => ['image/jpeg'],
        'png' => ['image/png'],
        'gif' => ['image/gif'],
        'webp' => ['image/webp'],
        'pdf' => ['application/pdf']
    ];

    if (function_exists('finfo_open')) {
        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $detectedMime = $finfo ? finfo_file($finfo, $file['tmp_name']) : false;
        if ($finfo) {
            finfo_close($finfo);
        }

        $allowedMimes = $allowedMimeByExt[$ext] ?? [];
        if (!$detectedMime || !in_array($detectedMime, $allowedMimes, true)) {
            return ['success' => false, 'message' => 'Invalid file content type'];
        }
    }
    
    // Create upload directory
    $uploadDir = UPLOAD_DIR . ($subdir ? $subdir . '/' : '');
    if (!is_dir($uploadDir)) {
        if (!mkdir($uploadDir, 0755, true)) {
            return ['success' => false, 'message' => 'Failed to create upload directory'];
        }
    }
    
    if (!is_writable($uploadDir)) {
        return ['success' => false, 'message' => 'Upload directory is not writable'];
    }
    
    // Generate unique filename (cryptographically random)
    $filename = bin2hex(random_bytes(16)) . '_' . preg_replace('/[^a-zA-Z0-9.]/', '', $file['name']);
    $filepath = $uploadDir . $filename;
    
    if (move_uploaded_file($file['tmp_name'], $filepath)) {
        return [
            'success' => true,
            'filename' => $filename,
            'path' => 'uploads/' . ($subdir ? $subdir . '/' : '') . $filename
        ];
    }
    
    return ['success' => false, 'message' => 'Failed to save file'];
}

// ── Security Audit Logging ─────────────────────────────────────
function auditLog($event, $details = [], $userId = null) {
    $entry = [
        'timestamp' => date('c'),
        'event'     => $event,
        'ip'        => $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0',
        'user_id'   => $userId,
        'uri'       => $_SERVER['REQUEST_URI'] ?? '',
        'method'    => $_SERVER['REQUEST_METHOD'] ?? '',
        'details'   => $details
    ];
    error_log('[MSPE_AUDIT] ' . json_encode($entry, JSON_UNESCAPED_SLASHES));
}

// Initialize database based on DB_TYPE from .env
if (DB_TYPE === 'mysql') {
    try {
        $db = new MySQLDB();
    } catch (Exception $e) {
        error_log('MSPE MySQL connection failed: ' . $e->getMessage());
        http_response_code(500);
        die(json_encode(['success' => false, 'message' => 'Database connection failed']));
    }
} else {
    http_response_code(500);
    die(json_encode(['success' => false, 'message' => 'Only MySQL is supported in production']));
}