<?php

declare(strict_types=1);

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Session;
use App\Core\Tenant;

if (!function_exists('base_path')) {
    function base_path(string $path = ''): string
    {
        $root = defined('BASE_PATH') ? BASE_PATH : dirname(__DIR__, 2);
        return $path === '' ? $root : $root . '/' . ltrim($path, '/');
    }
}

if (!function_exists('config')) {
    function config(string $key, mixed $default = null): mixed
    {
        static $cache = [];

        $segments = explode('.', $key);
        $file = array_shift($segments);

        if (!isset($cache[$file])) {
            $path = base_path('config/' . $file . '.php');
            $cache[$file] = is_file($path) ? require $path : [];
        }

        $value = $cache[$file];
        foreach ($segments as $segment) {
            if (!is_array($value) || !array_key_exists($segment, $value)) {
                return $default;
            }
            $value = $value[$segment];
        }

        return $value;
    }
}

if (!function_exists('storage_path')) {
    function storage_path(string $path = ''): string
    {
        return base_path('storage/' . ltrim($path, '/'));
    }
}

if (!function_exists('url')) {
    function resolve_app_base_url(): string
    {
        $configuredBase = rtrim((string) config('app.url', ''), '/');

        if (PHP_SAPI === 'cli' || PHP_SAPI === 'phpdbg') {
            return $configuredBase;
        }

        $host = trim((string) ($_SERVER['HTTP_HOST'] ?? ''));
        if ($host === '') {
            return $configuredBase;
        }

        $isHttps = false;
        $https = strtolower((string) ($_SERVER['HTTPS'] ?? ''));
        if ($https !== '' && $https !== 'off') {
            $isHttps = true;
        }

        $forwardedProto = strtolower(trim((string) ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '')));
        if ($forwardedProto !== '') {
            $forwardedProto = explode(',', $forwardedProto)[0] ?? $forwardedProto;
            $forwardedProto = trim($forwardedProto);
            if ($forwardedProto === 'https') {
                $isHttps = true;
            } elseif ($forwardedProto === 'http') {
                $isHttps = false;
            }
        }

        $scheme = $isHttps ? 'https' : 'http';

        $basePath = parse_url($configuredBase, PHP_URL_PATH) ?: '';
        $basePath = trim((string) $basePath, '/');
        $basePath = $basePath === '' ? '' : '/' . $basePath;

        return $scheme . '://' . $host . $basePath;
    }

    function url(string $path = ''): string
    {
        $base = rtrim(resolve_app_base_url(), '/');
        $path = ltrim($path, '/');
        return $path === '' ? $base : $base . '/' . $path;
    }
}

if (!function_exists('asset')) {
    function asset(string $path): string
    {
        $normalizedPath = ltrim($path, '/');
        $assetUrl = url('assets/' . $normalizedPath);
        $assetFile = dirname(__DIR__, 2) . '/public/assets/' . $normalizedPath;

        if (is_file($assetFile)) {
            $separator = str_contains($assetUrl, '?') ? '&' : '?';
            $assetUrl .= $separator . 'v=' . (string) filemtime($assetFile);
        }

        return $assetUrl;
    }
}

if (!function_exists('is_https_request')) {
    function is_https_request(): bool
    {
        if (PHP_SAPI === 'cli' || PHP_SAPI === 'phpdbg') {
            return false;
        }

        $https = strtolower((string) ($_SERVER['HTTPS'] ?? ''));
        if ($https !== '' && $https !== 'off') {
            return true;
        }

        $serverPort = (string) ($_SERVER['SERVER_PORT'] ?? '');
        if ($serverPort === '443') {
            return true;
        }

        $forwardedProto = strtolower(trim((string) ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '')));
        if ($forwardedProto !== '') {
            $firstProto = trim((string) (explode(',', $forwardedProto)[0] ?? ''));
            if ($firstProto === 'https') {
                return true;
            }
        }

        return false;
    }
}

if (!function_exists('enforce_https_redirect_if_needed')) {
    function enforce_https_redirect_if_needed(): void
    {
        if (!((bool) config('security.https.force', false))) {
            return;
        }

        if (PHP_SAPI === 'cli' || PHP_SAPI === 'phpdbg') {
            return;
        }

        if (is_https_request()) {
            return;
        }

        if (headers_sent()) {
            return;
        }

        $host = trim((string) ($_SERVER['HTTP_HOST'] ?? ''));
        if ($host === '') {
            return;
        }

        $requestUri = (string) ($_SERVER['REQUEST_URI'] ?? '/');
        $target = 'https://' . $host . $requestUri;
        header('Location: ' . $target, true, 301);
        exit;
    }
}

if (!function_exists('send_security_headers')) {
    function send_security_headers(): void
    {
        if (PHP_SAPI === 'cli' || PHP_SAPI === 'phpdbg') {
            return;
        }

        if (headers_sent()) {
            return;
        }

        if (!((bool) config('security.headers.enabled', true))) {
            return;
        }

        $xFrameOptions = trim((string) config('security.headers.x_frame_options', 'SAMEORIGIN'));
        if ($xFrameOptions !== '') {
            header('X-Frame-Options: ' . $xFrameOptions);
        }

        $xContentTypeOptions = trim((string) config('security.headers.x_content_type_options', 'nosniff'));
        if ($xContentTypeOptions !== '') {
            header('X-Content-Type-Options: ' . $xContentTypeOptions);
        }

        $referrerPolicy = trim((string) config('security.headers.referrer_policy', 'strict-origin-when-cross-origin'));
        if ($referrerPolicy !== '') {
            header('Referrer-Policy: ' . $referrerPolicy);
        }

        $permissionsPolicy = trim((string) config('security.headers.permissions_policy', ''));
        if ($permissionsPolicy !== '') {
            header('Permissions-Policy: ' . $permissionsPolicy);
        }

        $crossDomain = trim((string) config('security.headers.x_permitted_cross_domain_policies', 'none'));
        if ($crossDomain !== '') {
            header('X-Permitted-Cross-Domain-Policies: ' . $crossDomain);
        }

        $coop = trim((string) config('security.headers.cross_origin_opener_policy', 'same-origin-allow-popups'));
        if ($coop !== '') {
            header('Cross-Origin-Opener-Policy: ' . $coop);
        }

        $corp = trim((string) config('security.headers.cross_origin_resource_policy', 'same-site'));
        if ($corp !== '') {
            header('Cross-Origin-Resource-Policy: ' . $corp);
        }

        $hstsEnabled = (bool) config('security.headers.hsts_enabled', false);
        if ($hstsEnabled && is_https_request()) {
            $hstsMaxAge = max(0, (int) config('security.headers.hsts_max_age', 31536000));
            $hstsParts = ['max-age=' . $hstsMaxAge];

            if ((bool) config('security.headers.hsts_include_subdomains', true)) {
                $hstsParts[] = 'includeSubDomains';
            }

            if ((bool) config('security.headers.hsts_preload', false)) {
                $hstsParts[] = 'preload';
            }

            header('Strict-Transport-Security: ' . implode('; ', $hstsParts));
        }
    }
}

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

if (!function_exists('redirect')) {
    function redirect(string $path): never
    {
        header('Location: ' . url($path));
        exit;
    }
}

if (!function_exists('back')) {
    function back(): never
    {
        $fallback = url('dashboard');
        $location = $_SERVER['HTTP_REFERER'] ?? $fallback;
        header('Location: ' . $location);
        exit;
    }
}

if (!function_exists('csrf_token')) {
    function csrf_token(): string
    {
        return Csrf::token();
    }
}

if (!function_exists('csrf_field')) {
    function csrf_field(): string
    {
        return '<input type="hidden" name="_token" value="' . e(csrf_token()) . '">';
    }
}

if (!function_exists('flash')) {
    function flash(string $key, ?string $message = null): mixed
    {
        if ($message !== null) {
            Session::flash($key, $message);
            return null;
        }

        return Session::pullFlash($key);
    }
}

if (!function_exists('old')) {
    function old(string $key, mixed $default = ''): mixed
    {
        return Session::getOldInput($key, $default);
    }
}

if (!function_exists('auth_user')) {
    function auth_user(): ?array
    {
        return Auth::user();
    }
}

if (!function_exists('tenant_id')) {
    function tenant_id(): ?int
    {
        return Tenant::companyId();
    }
}

if (!function_exists('has_permission')) {
    function has_permission(string $permission): bool
    {
        return Auth::hasPermission($permission);
    }
}

if (!function_exists('is_superadmin')) {
    function is_superadmin(): bool
    {
        return Auth::isSuperadmin();
    }
}

if (!function_exists('money')) {
    function money(float|int|string $value): string
    {
        return '$' . number_format((float) $value, 2, ',', '.');
    }
}

if (!function_exists('support_attachment_url')) {
    function support_attachment_url(string $path): ?string
    {
        $normalized = trim(str_replace('\\', '/', $path));
        $normalized = ltrim($normalized, '/');

        if ($normalized === '' || !str_starts_with($normalized, 'uploads/support/')) {
            return null;
        }

        return url($normalized);
    }
}

if (!function_exists('support_event_message_encode')) {
    function support_event_message_encode(string $text, ?array $attachment = null): string
    {
        $payload = [
            'v' => 1,
            'text' => $text,
        ];

        if (is_array($attachment)) {
            $path = trim((string) ($attachment['path'] ?? ''));
            $name = trim((string) ($attachment['name'] ?? ''));
            $mime = trim((string) ($attachment['mime'] ?? ''));
            $sizeKb = (int) ($attachment['size_kb'] ?? 0);

            if ($path !== '' && $name !== '') {
                $payload['attachment'] = [
                    'path' => ltrim(str_replace('\\', '/', $path), '/'),
                    'name' => $name,
                    'mime' => $mime,
                    'size_kb' => $sizeKb > 0 ? $sizeKb : null,
                ];
            }
        }

        $json = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        return is_string($json) && $json !== '' ? $json : $text;
    }
}

if (!function_exists('support_event_message_decode')) {
    function support_event_message_decode(string $raw): array
    {
        $raw = trim($raw);
        $fallback = [
            'text' => $raw,
            'attachment' => null,
        ];

        if ($raw === '') {
            return $fallback;
        }

        $decoded = json_decode($raw, true);
        if (!is_array($decoded) || !array_key_exists('text', $decoded)) {
            return $fallback;
        }

        $text = trim((string) ($decoded['text'] ?? ''));
        $attachmentRaw = is_array($decoded['attachment'] ?? null) ? $decoded['attachment'] : null;
        if ($attachmentRaw === null) {
            return [
                'text' => $text,
                'attachment' => null,
            ];
        }

        $path = trim((string) ($attachmentRaw['path'] ?? ''));
        $name = trim((string) ($attachmentRaw['name'] ?? ''));
        $mime = trim((string) ($attachmentRaw['mime'] ?? ''));
        $sizeKb = (int) ($attachmentRaw['size_kb'] ?? 0);
        $url = $path !== '' ? support_attachment_url($path) : null;

        if ($url === null || $name === '') {
            return [
                'text' => $text,
                'attachment' => null,
            ];
        }

        $lowerName = strtolower($name);
        $isImage = str_starts_with(strtolower($mime), 'image/')
            || preg_match('/\.(jpg|jpeg|png|webp|gif)$/', $lowerName) === 1;

        return [
            'text' => $text,
            'attachment' => [
                'path' => ltrim(str_replace('\\', '/', $path), '/'),
                'url' => $url,
                'name' => $name,
                'mime' => $mime,
                'size_kb' => $sizeKb > 0 ? $sizeKb : null,
                'is_image' => $isImage,
            ],
        ];
    }
}

if (!function_exists('pending_cash_transfers_count')) {
    function pending_cash_transfers_count(): int
    {
        if (!Auth::check() || !has_permission('caja.ver')) {
            return 0;
        }

        $companyId = (int) (Tenant::companyId() ?? 0);
        $userId = (int) (Auth::id() ?? 0);
        if ($companyId <= 0 || $userId <= 0) {
            return 0;
        }

        try {
            $pdo = Database::connection();

            $openingStmt = $pdo->prepare(
                'SELECT id
                 FROM aperturas_caja
                 WHERE empresa_id = :empresa_id
                   AND usuario_id = :usuario_id
                   AND estado = "abierta"
                 ORDER BY id DESC
                 LIMIT 1'
            );
            $openingStmt->execute([
                ':empresa_id' => $companyId,
                ':usuario_id' => $userId,
            ]);
            $openingId = (int) ($openingStmt->fetchColumn() ?: 0);

            if ($openingId <= 0) {
                return 0;
            }

            $countStmt = $pdo->prepare(
                'SELECT COUNT(*)
                 FROM traspasos_caja
                 WHERE empresa_id = :empresa_id
                   AND apertura_destino_id = :apertura_id
                   AND estado = "pendiente"'
            );
            $countStmt->execute([
                ':empresa_id' => $companyId,
                ':apertura_id' => $openingId,
            ]);

            return (int) $countStmt->fetchColumn();
        } catch (\Throwable) {
            return 0;
        }
    }
}

if (!function_exists('stock_variants_table_available')) {
    function stock_variants_table_available(): bool
    {
        static $available = null;
        if ($available !== null) {
            return $available;
        }

        try {
            $stmt = Database::connection()->prepare(
                "SELECT COUNT(*)
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'producto_variantes'"
            );
            $stmt->execute();
            $available = (int) $stmt->fetchColumn() > 0;
        } catch (\Throwable) {
            $available = false;
        }

        return $available;
    }
}

if (!function_exists('notification_reads_table_available')) {
    function notification_reads_table_available(): bool
    {
        static $available = null;
        if ($available !== null) {
            return $available;
        }

        $available = notification_reads_table_exists();
        if (!$available) {
            $available = notification_reads_try_create_table();
        }

        return $available;
    }
}

if (!function_exists('notification_reads_table_exists')) {
    function notification_reads_table_exists(): bool
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT COUNT(*)
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'notificacion_vistas'"
            );
            $stmt->execute();
            return (int) $stmt->fetchColumn() > 0;
        } catch (\Throwable) {
            return false;
        }
    }
}

if (!function_exists('notification_reads_try_create_table')) {
    function notification_reads_try_create_table(): bool
    {
        try {
            Database::connection()->exec(
                'CREATE TABLE IF NOT EXISTS notificacion_vistas (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    usuario_id BIGINT UNSIGNED NOT NULL,
                    empresa_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                    notification_key VARCHAR(255) NOT NULL,
                    leido_at DATETIME NOT NULL,
                    created_at DATETIME NULL,
                    updated_at DATETIME NULL,
                    UNIQUE KEY uk_notificacion_vistas_usuario_empresa_key (usuario_id, empresa_id, notification_key),
                    INDEX idx_notificacion_vistas_empresa_usuario (empresa_id, usuario_id),
                    INDEX idx_notificacion_vistas_leido_at (leido_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
            );
        } catch (\Throwable) {
            return false;
        }

        return notification_reads_table_exists();
    }
}

if (!function_exists('notification_current_user_id')) {
    function notification_current_user_id(): int
    {
        $user = Auth::user();
        if (!is_array($user)) {
            return 0;
        }

        return (int) ($user['id'] ?? 0);
    }
}

if (!function_exists('notification_current_company_id')) {
    function notification_current_company_id(): int
    {
        if (Auth::isSuperadmin()) {
            return 0;
        }

        $companyId = (int) (Tenant::companyId() ?? 0);
        return max(0, $companyId);
    }
}

if (!function_exists('notification_normalize_numeric_key')) {
    function notification_normalize_numeric_key(mixed $value): string
    {
        $number = (float) $value;
        $formatted = number_format($number, 4, '.', '');
        return rtrim(rtrim($formatted, '0'), '.');
    }
}

if (!function_exists('notification_read_map')) {
    function notification_read_map(array $keys): array
    {
        if (!Auth::check() || !notification_reads_table_available()) {
            return [];
        }

        $userId = notification_current_user_id();
        if ($userId <= 0) {
            return [];
        }

        $normalizedKeys = array_values(array_unique(array_filter(
            array_map(static fn ($value): string => trim((string) $value), $keys),
            static fn (string $value): bool => $value !== ''
        )));

        if ($normalizedKeys === []) {
            return [];
        }

        $params = [
            ':usuario_id' => $userId,
            ':empresa_id' => notification_current_company_id(),
        ];
        $placeholders = [];
        foreach ($normalizedKeys as $index => $key) {
            $placeholder = ':key_' . $index;
            $placeholders[] = $placeholder;
            $params[$placeholder] = $key;
        }

        try {
            $stmt = Database::connection()->prepare(
                'SELECT notification_key
                 FROM notificacion_vistas
                 WHERE usuario_id = :usuario_id
                   AND empresa_id = :empresa_id
                   AND notification_key IN (' . implode(',', $placeholders) . ')'
            );
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: [];

            $readMap = [];
            foreach ($rows as $row) {
                $readMap[(string) $row] = true;
            }

            return $readMap;
        } catch (\Throwable) {
            return [];
        }
    }
}

if (!function_exists('notification_strip_internal_fields')) {
    function notification_strip_internal_fields(array $items): array
    {
        $output = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }
            unset($item['read_key']);
            $output[] = $item;
        }

        return $output;
    }
}

if (!function_exists('notification_filter_unread_items')) {
    function notification_filter_unread_items(array $items, bool $includeRead = false): array
    {
        if ($includeRead || $items === []) {
            return $items;
        }

        $keys = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }
            $readKey = trim((string) ($item['read_key'] ?? ''));
            if ($readKey !== '') {
                $keys[] = $readKey;
            }
        }

        if ($keys === []) {
            return $items;
        }

        $readMap = notification_read_map($keys);
        if ($readMap === []) {
            return $items;
        }

        $output = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }
            $readKey = trim((string) ($item['read_key'] ?? ''));
            if ($readKey !== '' && isset($readMap[$readKey])) {
                continue;
            }
            $output[] = $item;
        }

        return $output;
    }
}

if (!function_exists('notification_mark_read_keys')) {
    function notification_mark_read_keys(array $keys): int
    {
        if (!Auth::check() || !notification_reads_table_available()) {
            return 0;
        }

        $userId = notification_current_user_id();
        if ($userId <= 0) {
            return 0;
        }

        $normalizedKeys = array_values(array_unique(array_filter(
            array_map(static fn ($value): string => trim((string) $value), $keys),
            static fn (string $value): bool => $value !== ''
        )));

        if ($normalizedKeys === []) {
            return 0;
        }

        $companyId = notification_current_company_id();
        $params = [];
        $chunks = [];
        foreach ($normalizedKeys as $index => $key) {
            $userPlaceholder = ':user_' . $index;
            $companyPlaceholder = ':company_' . $index;
            $keyPlaceholder = ':key_' . $index;
            $chunks[] = '(' . $userPlaceholder . ',' . $companyPlaceholder . ',' . $keyPlaceholder . ',NOW(),NOW(),NOW())';
            $params[$userPlaceholder] = $userId;
            $params[$companyPlaceholder] = $companyId;
            $params[$keyPlaceholder] = $key;
        }

        try {
            $stmt = Database::connection()->prepare(
                'INSERT INTO notificacion_vistas
                    (usuario_id, empresa_id, notification_key, leido_at, created_at, updated_at)
                 VALUES ' . implode(',', $chunks) . '
                 ON DUPLICATE KEY UPDATE
                    leido_at = VALUES(leido_at),
                    updated_at = VALUES(updated_at)'
            );
            $stmt->execute($params);
            return count($normalizedKeys);
        } catch (\Throwable) {
            return 0;
        }
    }
}

if (!function_exists('notification_mark_all_as_read_for_scope')) {
    function notification_mark_all_as_read_for_scope(string $scope = 'auto'): int
    {
        if (!Auth::check()) {
            return 0;
        }

        $resolvedScope = strtolower(trim($scope));
        if ($resolvedScope === '' || $resolvedScope === 'auto') {
            $resolvedScope = Auth::isSuperadmin() ? 'superadmin' : 'tenant';
        }

        $items = [];
        if ($resolvedScope === 'superadmin') {
            $items = superadmin_support_notifications_list(null, true);
        } else {
            $items = array_merge(
                stock_minimum_notifications_list(null, true),
                support_ticket_notifications_list(null, true)
            );
        }

        if ($items === []) {
            return 0;
        }

        $keys = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }
            $notificationType = strtolower(trim((string) ($item['notification_type'] ?? 'stock')));
            $id = trim((string) ($item['id'] ?? ''));
            if ($id === '') {
                continue;
            }

            if ($notificationType === 'ticket') {
                $ticketId = (int) ($item['ticket_id'] ?? 0);
                if ($ticketId <= 0) {
                    continue;
                }
                $updatedAt = trim((string) ($item['updated_at'] ?? ''));
                $createdAt = trim((string) ($item['created_at'] ?? ''));
                $keys[] = ($resolvedScope === 'superadmin' ? 'ticket|superadmin|' : 'ticket|tenant|')
                    . $ticketId . '|' . ($updatedAt !== '' ? $updatedAt : $createdAt);
                continue;
            }

            $stockActual = notification_normalize_numeric_key($item['stock_actual'] ?? 0);
            $stockMinimo = notification_normalize_numeric_key($item['stock_minimo'] ?? 0);
            $keys[] = 'stock|' . $id . '|' . $stockActual . '|' . $stockMinimo;
        }

        return notification_mark_read_keys($keys);
    }
}

if (!function_exists('stock_minimum_notifications_count')) {
    function stock_minimum_notifications_count(): int
    {
        return count(stock_minimum_notifications_list(null));
    }
}

if (!function_exists('stock_minimum_notifications_list')) {
    function stock_minimum_notifications_list(?int $limit = 10, bool $includeRead = false): array
    {
        if (!Auth::check() || Auth::isSuperadmin()) {
            return [];
        }

        $companyId = (int) (Tenant::companyId() ?? 0);
        if ($companyId <= 0) {
            return [];
        }

        $limitValue = $limit === null ? null : max(1, min(500, $limit));

        try {
            $pdo = Database::connection();
            $rows = [];

            if (stock_variants_table_available()) {
                $simpleStmt = $pdo->prepare(
                    'SELECT
                        CONCAT("producto-", p.id) AS alerta_id,
                        "producto" AS tipo,
                        p.id AS producto_id,
                        NULL AS variante_id,
                        p.nombre AS producto_nombre,
                        NULL AS variante_nombre,
                        COALESCE(NULLIF(TRIM(p.sku), ""), CONCAT("PROD-", p.id)) AS referencia,
                        p.stock_actual AS stock_actual,
                        p.stock_minimo AS stock_minimo,
                        (p.stock_minimo - p.stock_actual) AS faltante
                     FROM productos p
                     LEFT JOIN (
                        SELECT producto_id, COUNT(*) AS total_variantes
                        FROM producto_variantes
                        WHERE empresa_id = :empresa_id_variantes
                          AND activo = 1
                        GROUP BY producto_id
                     ) pv ON pv.producto_id = p.id
                     WHERE p.empresa_id = :empresa_id_productos
                       AND p.activo = 1
                       AND COALESCE(pv.total_variantes, 0) = 0
                       AND p.stock_minimo > 0
                       AND p.stock_actual <= p.stock_minimo'
                );
                $simpleStmt->execute([
                    ':empresa_id_variantes' => $companyId,
                    ':empresa_id_productos' => $companyId,
                ]);
                $rows = array_merge($rows, $simpleStmt->fetchAll() ?: []);

                $variantStmt = $pdo->prepare(
                    'SELECT
                        CONCAT("variante-", v.id) AS alerta_id,
                        "variante" AS tipo,
                        p.id AS producto_id,
                        v.id AS variante_id,
                        p.nombre AS producto_nombre,
                        v.nombre AS variante_nombre,
                        COALESCE(NULLIF(TRIM(v.sku), ""), NULLIF(TRIM(p.sku), ""), CONCAT("VAR-", v.id)) AS referencia,
                        v.cantidad AS stock_actual,
                        v.stock_minimo AS stock_minimo,
                        (v.stock_minimo - v.cantidad) AS faltante
                     FROM producto_variantes v
                     INNER JOIN productos p
                        ON p.id = v.producto_id
                       AND p.empresa_id = v.empresa_id
                     WHERE v.empresa_id = :empresa_id_var
                       AND v.activo = 1
                       AND p.activo = 1
                       AND v.stock_minimo > 0
                       AND v.cantidad <= v.stock_minimo'
                );
                $variantStmt->execute([':empresa_id_var' => $companyId]);
                $rows = array_merge($rows, $variantStmt->fetchAll() ?: []);
            } else {
                $simpleStmt = $pdo->prepare(
                    'SELECT
                        CONCAT("producto-", id) AS alerta_id,
                        "producto" AS tipo,
                        id AS producto_id,
                        NULL AS variante_id,
                        nombre AS producto_nombre,
                        NULL AS variante_nombre,
                        COALESCE(NULLIF(TRIM(sku), ""), CONCAT("PROD-", id)) AS referencia,
                        stock_actual,
                        stock_minimo,
                        (stock_minimo - stock_actual) AS faltante
                     FROM productos
                     WHERE empresa_id = :empresa_id
                       AND activo = 1
                       AND stock_minimo > 0
                       AND stock_actual <= stock_minimo'
                );
                $simpleStmt->execute([':empresa_id' => $companyId]);
                $rows = $simpleStmt->fetchAll() ?: [];
            }

            usort($rows, static function (array $a, array $b): int {
                $missingA = (float) ($a['faltante'] ?? 0);
                $missingB = (float) ($b['faltante'] ?? 0);
                if ($missingA !== $missingB) {
                    return $missingB <=> $missingA;
                }

                $stockA = (float) ($a['stock_actual'] ?? 0);
                $stockB = (float) ($b['stock_actual'] ?? 0);
                if ($stockA !== $stockB) {
                    return $stockA <=> $stockB;
                }

                return strcmp(
                    mb_strtolower((string) ($a['producto_nombre'] ?? '')),
                    mb_strtolower((string) ($b['producto_nombre'] ?? ''))
                );
            });

            $formatStock = static function (float $value): string {
                if (abs($value - round($value)) < 0.0001) {
                    return (string) (int) round($value);
                }

                return rtrim(rtrim(number_format($value, 2, '.', ''), '0'), '.');
            };

            $items = [];
            foreach ($rows as $row) {
                $stockActualRaw = (string) ($row['stock_actual'] ?? '0');
                $stockMinimoRaw = (string) ($row['stock_minimo'] ?? '0');
                $stockActual = (float) $stockActualRaw;
                $stockMinimo = (float) $stockMinimoRaw;
                $stockActualKey = notification_normalize_numeric_key($stockActual);
                $stockMinimoKey = notification_normalize_numeric_key($stockMinimo);
                $missing = max(0, $stockMinimo - $stockActual);
                $severity = $stockActual <= 0 ? 'critical' : 'warning';
                $title = (string) ($row['producto_nombre'] ?? 'Producto');
                $variantName = trim((string) ($row['variante_nombre'] ?? ''));
                if ($variantName !== '') {
                    $title .= ' · ' . $variantName;
                }

                $items[] = [
                    'id' => (string) ($row['alerta_id'] ?? ''),
                    'notification_type' => 'stock',
                    'type' => (string) ($row['tipo'] ?? 'producto'),
                    'title' => $title,
                    'reference' => (string) ($row['referencia'] ?? ''),
                    'stock_actual' => $stockActual,
                    'stock_minimo' => $stockMinimo,
                    'missing' => $missing,
                    'severity' => $severity,
                    'message' => 'Stock actual: ' . $formatStock($stockActual) . ' · mínimo: ' . $formatStock($stockMinimo),
                    'read_key' => 'stock|' . (string) ($row['alerta_id'] ?? '') . '|' . $stockActualKey . '|' . $stockMinimoKey,
                ];
            }

            $items = notification_filter_unread_items($items, $includeRead);
            if ($limitValue !== null) {
                $items = array_slice($items, 0, $limitValue);
            }

            return notification_strip_internal_fields($items);
        } catch (\Throwable) {
            return [];
        }
    }
}

if (!function_exists('support_ticket_notifications_count')) {
    function support_ticket_notifications_count(): int
    {
        return count(support_ticket_notifications_list(null));
    }
}

if (!function_exists('support_ticket_notifications_list')) {
    function support_ticket_notifications_list(?int $limit = 10, bool $includeRead = false): array
    {
        if (!Auth::check() || Auth::isSuperadmin()) {
            return [];
        }

        $companyId = (int) (Tenant::companyId() ?? 0);
        if ($companyId <= 0) {
            return [];
        }

        $limitValue = $limit === null ? null : max(1, min(500, $limit));

        try {
            $stmt = Database::connection()->prepare(
                'SELECT
                    id,
                    asunto,
                    estado,
                    prioridad,
                    created_at,
                    updated_at
                 FROM soporte_tickets
                 WHERE empresa_id = :empresa_id
                   AND estado IN ("abierto", "en_proceso", "pendiente_cliente")
                 ORDER BY
                    CASE estado
                        WHEN "pendiente_cliente" THEN 0
                        WHEN "abierto" THEN 1
                        WHEN "en_proceso" THEN 2
                        ELSE 3
                    END ASC,
                    updated_at DESC,
                    id DESC'
            );
            $stmt->execute([':empresa_id' => $companyId]);
            $rows = $stmt->fetchAll() ?: [];

            $priorityLabel = static function (string $value): string {
                return match (strtolower(trim($value))) {
                    'critica' => 'Crítica',
                    'alta' => 'Alta',
                    'media' => 'Media',
                    'baja' => 'Baja',
                    default => 'Media',
                };
            };

            $statusMessage = static function (string $value): string {
                return match (strtolower(trim($value))) {
                    'pendiente_cliente' => 'Soporte espera tu respuesta.',
                    'en_proceso' => 'Ticket en proceso por soporte.',
                    'abierto' => 'Ticket recibido por soporte.',
                    default => 'Ticket activo.',
                };
            };

            $items = [];
            foreach ($rows as $row) {
                $ticketId = (int) ($row['id'] ?? 0);
                if ($ticketId <= 0) {
                    continue;
                }

                $subject = trim((string) ($row['asunto'] ?? ''));
                if ($subject === '') {
                    $subject = 'Ticket de soporte';
                }

                $status = strtolower(trim((string) ($row['estado'] ?? 'abierto')));
                $priority = strtolower(trim((string) ($row['prioridad'] ?? 'media')));
                $updatedAt = trim((string) ($row['updated_at'] ?? ''));
                $createdAt = trim((string) ($row['created_at'] ?? ''));
                $dateRef = $updatedAt !== '' ? $updatedAt : $createdAt;
                $dateText = '';

                if ($dateRef !== '') {
                    $timestamp = strtotime($dateRef);
                    if ($timestamp !== false) {
                        $dateText = date('d/m H:i', $timestamp);
                    }
                }

                $message = $statusMessage($status) . ' Prioridad: ' . $priorityLabel($priority);
                if ($dateText !== '') {
                    $message .= ' · Act: ' . $dateText;
                }

                $severity = ($status === 'pendiente_cliente' || $priority === 'critica') ? 'critical' : 'warning';

                $items[] = [
                    'id' => 'ticket-' . $ticketId,
                    'notification_type' => 'ticket',
                    'type' => 'ticket',
                    'ticket_id' => $ticketId,
                    'ticket_status' => $status,
                    'ticket_priority' => $priority,
                    'title' => 'Ticket #' . $ticketId . ' · ' . $subject,
                    'severity' => $severity,
                    'message' => $message,
                    'updated_at' => $updatedAt,
                    'created_at' => $createdAt,
                    'read_key' => 'ticket|tenant|' . $ticketId . '|' . ($updatedAt !== '' ? $updatedAt : $createdAt),
                ];
            }

            $items = notification_filter_unread_items($items, $includeRead);
            if ($limitValue !== null) {
                $items = array_slice($items, 0, $limitValue);
            }

            return notification_strip_internal_fields($items);
        } catch (\Throwable) {
            return [];
        }
    }
}

if (!function_exists('tenant_notifications_payload')) {
    function tenant_notifications_payload(int $limit = 10): array
    {
        $limit = max(1, min(50, $limit));

        if (!Auth::check() || Auth::isSuperadmin()) {
            return [
                'count' => 0,
                'count_stock' => 0,
                'count_tickets' => 0,
                'items' => [],
            ];
        }

        $ticketItems = support_ticket_notifications_list(null);
        $stockItems = stock_minimum_notifications_list(null);
        $stockCount = count($stockItems);
        $ticketCount = count($ticketItems);
        $items = [];

        $maxIndex = max(count($ticketItems), count($stockItems));
        for ($index = 0; $index < $maxIndex; $index++) {
            if (isset($ticketItems[$index])) {
                $items[] = $ticketItems[$index];
                if (count($items) >= $limit) {
                    break;
                }
            }

            if (isset($stockItems[$index])) {
                $items[] = $stockItems[$index];
                if (count($items) >= $limit) {
                    break;
                }
            }
        }

        return [
            'count' => max(0, $stockCount + $ticketCount),
            'count_stock' => max(0, $stockCount),
            'count_tickets' => max(0, $ticketCount),
            'items' => $items,
        ];
    }
}

if (!function_exists('superadmin_support_notifications_count')) {
    function superadmin_support_notifications_count(): int
    {
        return count(superadmin_support_notifications_list(null));
    }
}

if (!function_exists('superadmin_support_notifications_list')) {
    function superadmin_support_notifications_list(?int $limit = 10, bool $includeRead = false): array
    {
        if (!Auth::check() || !Auth::isSuperadmin()) {
            return [];
        }

        $limitValue = $limit === null ? null : max(1, min(500, $limit));

        try {
            $stmt = Database::connection()->prepare(
                'SELECT
                    t.id,
                    t.empresa_id,
                    e.nombre AS empresa_nombre,
                    t.asunto,
                    t.prioridad,
                    t.estado,
                    t.created_at,
                    t.updated_at
                 FROM soporte_tickets t
                 INNER JOIN empresas e ON e.id = t.empresa_id
                 WHERE t.estado IN ("abierto", "en_proceso", "pendiente_cliente")
                 ORDER BY
                    CASE t.estado
                        WHEN "abierto" THEN 0
                        WHEN "pendiente_cliente" THEN 1
                        WHEN "en_proceso" THEN 2
                        ELSE 3
                    END ASC,
                    t.updated_at DESC,
                    t.id DESC'
            );
            $stmt->execute();
            $rows = $stmt->fetchAll() ?: [];

            $priorityLabel = static function (string $value): string {
                return match (strtolower(trim($value))) {
                    'critica' => 'Crítica',
                    'alta' => 'Alta',
                    'media' => 'Media',
                    'baja' => 'Baja',
                    default => 'Media',
                };
            };

            $statusLabel = static function (string $value): string {
                return match (strtolower(trim($value))) {
                    'abierto' => 'Nuevo ticket',
                    'pendiente_cliente' => 'Pendiente cliente',
                    'en_proceso' => 'En proceso',
                    default => 'Activo',
                };
            };

            $items = [];
            foreach ($rows as $row) {
                $ticketId = (int) ($row['id'] ?? 0);
                if ($ticketId <= 0) {
                    continue;
                }

                $companyName = trim((string) ($row['empresa_nombre'] ?? 'Empresa'));
                $subject = trim((string) ($row['asunto'] ?? 'Ticket de soporte'));
                $status = strtolower(trim((string) ($row['estado'] ?? 'abierto')));
                $priority = strtolower(trim((string) ($row['prioridad'] ?? 'media')));
                $updatedAt = trim((string) ($row['updated_at'] ?? ''));
                $createdAt = trim((string) ($row['created_at'] ?? ''));

                $timestamp = strtotime($updatedAt !== '' ? $updatedAt : $createdAt);
                $dateText = $timestamp !== false ? date('d/m H:i', $timestamp) : '';

                $message = $statusLabel($status) . ' · ' . $companyName . ' · Prioridad: ' . $priorityLabel($priority);
                if ($dateText !== '') {
                    $message .= ' · Act: ' . $dateText;
                }

                $severity = ($status === 'abierto' || $priority === 'critica') ? 'critical' : 'warning';

                $items[] = [
                    'id' => 'superadmin-ticket-' . $ticketId,
                    'notification_type' => 'ticket',
                    'type' => 'ticket',
                    'ticket_id' => $ticketId,
                    'empresa_id' => (int) ($row['empresa_id'] ?? 0),
                    'ticket_status' => $status,
                    'ticket_priority' => $priority,
                    'title' => 'Ticket #' . $ticketId . ' · ' . $subject,
                    'message' => $message,
                    'severity' => $severity,
                    'updated_at' => $updatedAt,
                    'created_at' => $createdAt,
                    'read_key' => 'ticket|superadmin|' . $ticketId . '|' . ($updatedAt !== '' ? $updatedAt : $createdAt),
                ];
            }

            $items = notification_filter_unread_items($items, $includeRead);
            if ($limitValue !== null) {
                $items = array_slice($items, 0, $limitValue);
            }

            return notification_strip_internal_fields($items);
        } catch (\Throwable) {
            return [];
        }
    }
}

if (!function_exists('superadmin_support_notifications_payload')) {
    function superadmin_support_notifications_payload(int $limit = 10): array
    {
        $limit = max(1, min(50, $limit));

        if (!Auth::check() || !Auth::isSuperadmin()) {
            return [
                'count' => 0,
                'count_stock' => 0,
                'count_tickets' => 0,
                'items' => [],
            ];
        }

        $ticketItems = superadmin_support_notifications_list(null);
        $ticketCount = count($ticketItems);

        return [
            'count' => max(0, $ticketCount),
            'count_stock' => 0,
            'count_tickets' => max(0, $ticketCount),
            'items' => array_slice($ticketItems, 0, $limit),
        ];
    }
}

if (!function_exists('product_image_url')) {
    function product_image_url(?string $path = null): string
    {
        $default = asset('img/default-producto.jpg');
        $normalized = trim((string) $path);

        if ($normalized === '') {
            return $default;
        }

        if (preg_match('#^https?://#i', $normalized) === 1) {
            return $normalized;
        }

        $normalized = ltrim(str_replace('\\', '/', $normalized), '/');
        $allowedPrefixes = ['uploads/productos/', 'assets/'];

        foreach ($allowedPrefixes as $prefix) {
            if (str_starts_with($normalized, $prefix)) {
                return url($normalized);
            }
        }

        return $default;
    }
}
