<?php
declare(strict_types=1);

/**
 * /functional/VMCoins/_lib.php
 *
 * Відповідає за:
 * - читання налаштувань VM Coins з app_settings
 * - базові операції по балансу
 * - журнал транзакцій vm_coin_transactions
 * - захист від дублювання через op_key
 * - готові хелпери для:
 *   - створення поста
 *   - створення коментаря
 *   - зміни реакції на пост
 *   - зміни реакції на коментар
 * - Stage 2:
 *   - промокоди / реферальна система
 *   - генерація власного коду
 *   - застосування чужого коду
 *   - бонус тому, хто ввів код
 *   - бонус рефереру
 * - Stage 3:
 *   - денні ліміти на оплачувані пости/коментарі
 *   - антиспам-перевірки для винагород:
 *     - занадто часті дії
 *     - дубльований текст
 *     - спамоподібний текст
 *   - хелпери добової статистики по reward-подіях
 *
 * Додатково:
 * - реакції тепер можуть змінювати баланс і автора контенту, і того, хто поставив реакцію
 * - для author та actor використовуються окремі op_key, щоб не блокувати один одного дедуплікацією
 * - заборонено будь-який від’ємний баланс VM Coins
 * - додано переказ VM Coins між користувачами
 *
 * Примітка:
 * - actor reward за замовчуванням = 0 (антифарм)
 * - промокод можна застосувати лише 1 раз
 * - якщо денний ліміт = 0, то ліміт вимкнений
 * - будь-яке списання, що веде баланс нижче 0, блокується з reason=insufficient_funds
 */

if (!function_exists('vmc_table_exists')) {
    function vmc_table_exists(PDO $pdo, string $table): bool
    {
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
            return false;
        }

        $st = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = ?
        ");
        $st->execute([$table]);

        return ((int)$st->fetchColumn()) > 0;
    }
}

if (!function_exists('vmc_user_has_column')) {
    function vmc_user_has_column(PDO $pdo, string $column): bool
    {
        static $cache = [];

        if (!preg_match('/^[a-zA-Z0-9_]+$/', $column)) {
            return false;
        }

        if (array_key_exists($column, $cache)) {
            return (bool)$cache[$column];
        }

        $st = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = 'users'
              AND column_name = ?
        ");
        $st->execute([$column]);

        $cache[$column] = ((int)$st->fetchColumn()) > 0;
        return (bool)$cache[$column];
    }
}

if (!function_exists('vmc_user_columns_exist')) {
    function vmc_user_columns_exist(PDO $pdo): bool
    {
        $need = [
            'vm_coins_balance',
            'vm_coins_earned_total',
            'vm_coins_spent_total',
            'vm_coins_updated_at',
        ];

        foreach ($need as $col) {
            $st = $pdo->prepare("
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                  AND table_name = 'users'
                  AND column_name = ?
            ");
            $st->execute([$col]);

            if ((int)$st->fetchColumn() < 1) {
                return false;
            }
        }

        return true;
    }
}

if (!function_exists('vmc_referral_columns_exist')) {
    function vmc_referral_columns_exist(PDO $pdo): bool
    {
        $need = [
            'vm_ref_code',
            'vm_referred_by_user_id',
            'vm_ref_applied_at',
        ];

        foreach ($need as $col) {
            $st = $pdo->prepare("
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                  AND table_name = 'users'
                  AND column_name = ?
            ");
            $st->execute([$col]);

            if ((int)$st->fetchColumn() < 1) {
                return false;
            }
        }

        return true;
    }
}

if (!function_exists('vmc_strlen')) {
    function vmc_strlen(string $text): int
    {
        return function_exists('mb_strlen')
            ? (int)mb_strlen($text, 'UTF-8')
            : (int)strlen($text);
    }
}

if (!function_exists('vmc_substr')) {
    function vmc_substr(string $text, int $start, int $length): string
    {
        return function_exists('mb_substr')
            ? (string)mb_substr($text, $start, $length, 'UTF-8')
            : (string)substr($text, $start, $length);
    }
}

if (!function_exists('vmc_clean_text')) {
    function vmc_clean_text(?string $text): string
    {
        $text = (string)$text;
        $text = trim($text);
        $text = preg_replace('/\s+/u', ' ', $text) ?? $text;
        return trim($text);
    }
}

if (!function_exists('vmc_upper')) {
    function vmc_upper(string $text): string
    {
        return function_exists('mb_strtoupper')
            ? (string)mb_strtoupper($text, 'UTF-8')
            : strtoupper($text);
    }
}

if (!function_exists('vmc_lower')) {
    function vmc_lower(string $text): string
    {
        return function_exists('mb_strtolower')
            ? (string)mb_strtolower($text, 'UTF-8')
            : strtolower($text);
    }
}

if (!function_exists('vmc_setting_raw')) {
    function vmc_setting_raw(PDO $pdo, string $key, ?string $default = null): ?string
    {
        static $cache = [];

        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }

        if (!vmc_table_exists($pdo, 'app_settings')) {
            $cache[$key] = $default;
            return $cache[$key];
        }

        $st = $pdo->prepare("SELECT value_text FROM app_settings WHERE key_name = ? LIMIT 1");
        $st->execute([$key]);
        $val = $st->fetchColumn();

        $cache[$key] = ($val === false) ? $default : (string)$val;
        return $cache[$key];
    }
}

if (!function_exists('vmc_setting_int')) {
    function vmc_setting_int(PDO $pdo, string $key, int $default = 0): int
    {
        $raw = vmc_setting_raw($pdo, $key, (string)$default);
        return (int)$raw;
    }
}

if (!function_exists('vmc_enabled')) {
    function vmc_enabled(PDO $pdo): bool
    {
        return vmc_setting_int($pdo, 'vmcoins_enabled', 0) === 1;
    }
}

if (!function_exists('vmc_referrals_enabled')) {
    function vmc_referrals_enabled(PDO $pdo): bool
    {
        return vmc_enabled($pdo) && vmc_setting_int($pdo, 'vmcoins_referrals_enabled', 1) === 1;
    }
}

if (!function_exists('vmc_referral_bonus_referrer')) {
    function vmc_referral_bonus_referrer(PDO $pdo): int
    {
        return vmc_setting_int($pdo, 'vmcoins_referrer_bonus', 50);
    }
}

if (!function_exists('vmc_referral_bonus_referred')) {
    function vmc_referral_bonus_referred(PDO $pdo): int
    {
        return vmc_setting_int($pdo, 'vmcoins_referred_bonus', 25);
    }
}

if (!function_exists('vmc_referral_require_active')) {
    function vmc_referral_require_active(PDO $pdo): bool
    {
        return vmc_setting_int($pdo, 'vmcoins_ref_require_active', 1) === 1;
    }
}

if (!function_exists('vmc_detect_active_column')) {
    function vmc_detect_active_column(PDO $pdo): ?string
    {
        $candidates = ['is_active', 'active', 'verified', 'email_verified', 'is_verified'];

        foreach ($candidates as $col) {
            $st = $pdo->prepare("
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                  AND table_name = 'users'
                  AND column_name = ?
            ");
            $st->execute([$col]);

            if ((int)$st->fetchColumn() > 0) {
                return $col;
            }
        }

        return null;
    }
}

if (!function_exists('vmc_user_is_active')) {
    function vmc_user_is_active(PDO $pdo, int $userId): bool
    {
        if ($userId <= 0) {
            return false;
        }


        $activeCol = vmc_detect_active_column($pdo);
        if ($activeCol === null) {
            return true;
        }

        $sql = "SELECT `$activeCol` FROM users WHERE id = ? LIMIT 1";
        $st = $pdo->prepare($sql);
        $st->execute([$userId]);

        $val = $st->fetchColumn();
        if ($val === false) {
            return false;
        }

        return (int)$val === 1;
    }
}

if (!function_exists('vmc_is_meaningful_post')) {
    function vmc_is_meaningful_post(PDO $pdo, ?string $body): bool
    {
        $body = vmc_clean_text($body);
        $min  = vmc_setting_int($pdo, 'vmcoins_min_post_len', 5);
        return $body !== '' && vmc_strlen($body) >= max(1, $min);
    }
}

if (!function_exists('vmc_is_meaningful_comment')) {
    function vmc_is_meaningful_comment(PDO $pdo, ?string $body): bool
    {
        $body = vmc_clean_text($body);
        $min  = vmc_setting_int($pdo, 'vmcoins_min_comment_len', 3);
        return $body !== '' && vmc_strlen($body) >= max(1, $min);
    }
}

/* =========================
   Stage 3 · Anti-spam / limits
========================= */

if (!function_exists('vmc_create_event_key')) {
    function vmc_create_event_key(string $entityType): string
    {
        $entityType = strtolower(trim($entityType));
        return match ($entityType) {
            'post' => 'post_create',
            'comment' => 'comment_create',
            default => '',
        };
    }
}

if (!function_exists('vmc_daily_reward_limit')) {
    function vmc_daily_reward_limit(PDO $pdo, string $entityType): int
    {
        $entityType = strtolower(trim($entityType));

        return match ($entityType) {
            'post' => max(0, vmc_setting_int($pdo, 'vmcoins_post_daily_limit', 10)),
            'comment' => max(0, vmc_setting_int($pdo, 'vmcoins_comment_daily_limit', 20)),
            default => 0,
        };
    }
}

if (!function_exists('vmc_min_interval_seconds')) {
    function vmc_min_interval_seconds(PDO $pdo, string $entityType): int
    {
        $entityType = strtolower(trim($entityType));

        return match ($entityType) {
            'post' => max(0, vmc_setting_int($pdo, 'vmcoins_post_min_interval_seconds', 60)),
            'comment' => max(0, vmc_setting_int($pdo, 'vmcoins_comment_min_interval_seconds', 20)),
            default => 0,
        };
    }
}

if (!function_exists('vmc_duplicate_window_hours')) {
    function vmc_duplicate_window_hours(PDO $pdo, string $entityType): int
    {
        $entityType = strtolower(trim($entityType));

        return match ($entityType) {
            'post' => max(0, vmc_setting_int($pdo, 'vmcoins_post_duplicate_window_hours', 24)),
            'comment' => max(0, vmc_setting_int($pdo, 'vmcoins_comment_duplicate_window_hours', 12)),
            default => 0,
        };
    }
}

if (!function_exists('vmc_text_compare_normalized')) {
    function vmc_text_compare_normalized(?string $text): string
    {
        $text = vmc_clean_text($text);
        if ($text === '') {
            return '';
        }

        $text = vmc_lower($text);
        $text = preg_replace('/\s+/u', ' ', $text) ?? $text;

        return trim($text);
    }
}

if (!function_exists('vmc_text_hash')) {
    function vmc_text_hash(?string $text): string
    {
        $text = vmc_text_compare_normalized($text);
        return $text !== '' ? sha1($text) : '';
    }
}

if (!function_exists('vmc_text_excerpt')) {
    function vmc_text_excerpt(?string $text, int $limit = 140): string
    {
        $text = vmc_clean_text($text);
        if ($text === '') {
            return '';
        }

        $limit = max(10, $limit);

        if (vmc_strlen($text) <= $limit) {
            return $text;
        }

        return rtrim(vmc_substr($text, 0, $limit - 1)) . '…';
    }
}

if (!function_exists('vmc_json_decode_assoc')) {
    function vmc_json_decode_assoc(?string $json): array
    {
        $json = trim((string)$json);
        if ($json === '') {
            return [];
        }

        try {
            $data = json_decode($json, true, 512, JSON_THROW_ON_ERROR);
            return is_array($data) ? $data : [];
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('vmc_has_letters_or_digits')) {
    function vmc_has_letters_or_digits(string $text): bool
    {
        return (bool)preg_match('/[\p{L}\p{N}]/u', $text);
    }
}

if (!function_exists('vmc_unique_char_count')) {
    function vmc_unique_char_count(string $text): int
    {
        $chars = preg_split('//u', $text, -1, PREG_SPLIT_NO_EMPTY);
        if (!is_array($chars) || !$chars) {
            return 0;
        }

        return count(array_unique($chars));
    }
}

if (!function_exists('vmc_is_spam_like_text')) {
    function vmc_is_spam_like_text(?string $text): bool
    {
        $text = vmc_clean_text($text);
        if ($text === '') {
            return true;
        }

        if (!vmc_has_letters_or_digits($text)) {
            return true;
        }

        $compact = preg_replace('/\s+/u', '', $text) ?? $text;
        if ($compact === '') {
            return true;
        }

        /* одна й та сама літера/символ багато разів поспіль */
        if (preg_match('/(.)\1{5,}/u', $compact)) {
            return true;
        }

        /* три однакові слова поспіль і більше */
        if (preg_match('/(\b[\p{L}\p{N}_-]{1,32}\b)(?:\s+\1){2,}/iu', $text)) {
            return true;
        }

        $lenCompact = vmc_strlen($compact);
        $uniqueChars = vmc_unique_char_count($compact);

        if ($lenCompact >= 8 && $uniqueChars <= 2) {
            return true;
        }

        if ($lenCompact >= 12 && $uniqueChars > 0) {
            $sameRatio = $lenCompact / max(1, $uniqueChars);
            if ($uniqueChars <= 3 && $sameRatio >= 4.0) {
                return true;
            }
        }

        return false;
    }
}

if (!function_exists('vmc_get_user_event_reward_stats_today')) {
    function vmc_get_user_event_reward_stats_today(PDO $pdo, int $userId, string $eventKey): array
    {
        $out = [
            'count' => 0,
            'sum' => 0,
        ];

        if ($userId <= 0 || trim($eventKey) === '' || !vmc_table_exists($pdo, 'vm_coin_transactions')) {
            return $out;
        }

        $st = $pdo->prepare("
            SELECT
                COUNT(*) AS reward_count,
                COALESCE(SUM(delta_amount), 0) AS reward_sum
            FROM vm_coin_transactions
            WHERE user_id = ?
              AND event_key = ?
              AND created_at >= CURDATE()
              AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
        ");
        $st->execute([$userId, $eventKey]);
        $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

        $out['count'] = (int)($row['reward_count'] ?? 0);
        $out['sum']   = (int)($row['reward_sum'] ?? 0);

        return $out;
    }
}

if (!function_exists('vmc_get_last_user_event_reward_tx')) {
    function vmc_get_last_user_event_reward_tx(PDO $pdo, int $userId, string $eventKey): ?array
    {
        if ($userId <= 0 || trim($eventKey) === '' || !vmc_table_exists($pdo, 'vm_coin_transactions')) {
            return null;
        }

        $st = $pdo->prepare("
            SELECT id, user_id, delta_amount, balance_after, event_key, entity_type, entity_id, related_user_id, op_key, note, meta_json, created_at
            FROM vm_coin_transactions
            WHERE user_id = ?
              AND event_key = ?
            ORDER BY id DESC
            LIMIT 1
        ");
        $st->execute([$userId, $eventKey]);

        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vmc_is_too_fast_for_reward')) {
    function vmc_is_too_fast_for_reward(PDO $pdo, int $userId, string $eventKey, int $minIntervalSeconds): array
    {
        $out = [
            'too_fast' => false,
            'seconds_since_last' => null,
            'retry_after_seconds' => 0,
            'last_tx' => null,
        ];

        if ($userId <= 0 || $eventKey === '' || $minIntervalSeconds <= 0) {
            return $out;
        }

        $lastTx = vmc_get_last_user_event_reward_tx($pdo, $userId, $eventKey);
        if (!$lastTx) {
            return $out;
        }

        $lastAt = trim((string)($lastTx['created_at'] ?? ''));
        $lastTs = $lastAt !== '' ? strtotime($lastAt) : false;
        if ($lastTs === false) {
            return $out;
        }

        $elapsed = time() - (int)$lastTs;
        $out['seconds_since_last'] = $elapsed;
        $out['last_tx'] = $lastTx;

        if ($elapsed < $minIntervalSeconds) {
            $out['too_fast'] = true;
            $out['retry_after_seconds'] = max(1, $minIntervalSeconds - $elapsed);
        }

        return $out;
    }
}

if (!function_exists('vmc_find_recent_duplicate_reward_tx')) {
    function vmc_find_recent_duplicate_reward_tx(PDO $pdo, int $userId, string $eventKey, string $bodyHash, int $windowHours): ?array
    {
        if (
            $userId <= 0 ||
            $eventKey === '' ||
            $bodyHash === '' ||
            $windowHours <= 0 ||
            !vmc_table_exists($pdo, 'vm_coin_transactions')
        ) {
            return null;
        }

        $fromTs = time() - ($windowHours * 3600);
        $from = date('Y-m-d H:i:s', $fromTs);

        $st = $pdo->prepare("
            SELECT id, user_id, delta_amount, balance_after, event_key, entity_type, entity_id, related_user_id, op_key, note, meta_json, created_at
            FROM vm_coin_transactions
            WHERE user_id = ?
              AND event_key = ?
              AND created_at >= ?
            ORDER BY id DESC
            LIMIT 200
        ");
        $st->execute([$userId, $eventKey, $from]);

        $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as $row) {
            $meta = vmc_json_decode_assoc((string)($row['meta_json'] ?? ''));
            $txBodyHash = trim((string)($meta['body_hash'] ?? ''));
            if ($txBodyHash !== '' && hash_equals($txBodyHash, $bodyHash)) {
                return $row;
            }
        }

        return null;
    }
}

if (!function_exists('vmc_can_reward_create_content')) {
    function vmc_can_reward_create_content(PDO $pdo, int $userId, string $entityType, ?string $body): array
    {
        $entityType = strtolower(trim($entityType));
        $bodyClean  = vmc_clean_text($body);

        if ($userId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        if ($entityType !== 'post' && $entityType !== 'comment') {
            return ['ok' => false, 'reason' => 'bad_entity_type'];
        }

        $isMeaningful = $entityType === 'post'
            ? vmc_is_meaningful_post($pdo, $bodyClean)
            : vmc_is_meaningful_comment($pdo, $bodyClean);

        if (!$isMeaningful) {
            return [
                'ok' => false,
                'reason' => $entityType === 'post' ? 'post_not_meaningful' : 'comment_not_meaningful',
            ];
        }

        if (vmc_is_spam_like_text($bodyClean)) {
            return [
                'ok' => false,
                'reason' => 'spam_like_text',
            ];
        }

        $eventKey = vmc_create_event_key($entityType);
        if ($eventKey === '') {
            return ['ok' => false, 'reason' => 'bad_event_key'];
        }

        $minInterval = vmc_min_interval_seconds($pdo, $entityType);
        if ($minInterval > 0) {
            $fast = vmc_is_too_fast_for_reward($pdo, $userId, $eventKey, $minInterval);
            if ((bool)($fast['too_fast'] ?? false)) {
                return [
                    'ok' => false,
                    'reason' => 'too_fast',
                    'min_interval_seconds' => $minInterval,
                    'seconds_since_last' => (int)($fast['seconds_since_last'] ?? 0),
                    'retry_after_seconds' => (int)($fast['retry_after_seconds'] ?? 0),
                    'last_tx_id' => (int)(($fast['last_tx']['id'] ?? 0)),
                ];
            }
        }

        $dailyStats = vmc_get_user_event_reward_stats_today($pdo, $userId, $eventKey);
        $dailyLimit = vmc_daily_reward_limit($pdo, $entityType);
        if ($dailyLimit > 0 && (int)$dailyStats['count'] >= $dailyLimit) {
            return [
                'ok' => false,
                'reason' => 'daily_limit_reached',
                'limit' => $dailyLimit,
                'today_count' => (int)$dailyStats['count'],
                'today_sum' => (int)$dailyStats['sum'],
            ];
        }

        $bodyHash = vmc_text_hash($bodyClean);
        $dupHours = vmc_duplicate_window_hours($pdo, $entityType);
        if ($bodyHash !== '' && $dupHours > 0) {
            $dupTx = vmc_find_recent_duplicate_reward_tx($pdo, $userId, $eventKey, $bodyHash, $dupHours);
            if ($dupTx) {
                return [
                    'ok' => false,
                    'reason' => 'duplicate_content',
                    'duplicate_window_hours' => $dupHours,
                    'duplicate_tx_id' => (int)($dupTx['id'] ?? 0),
                    'duplicate_created_at' => (string)($dupTx['created_at'] ?? ''),
                ];
            }
        }

        return [
            'ok' => true,
            'reason' => 'allowed',
            'meta' => [
                'entity_type' => $entityType,
                'event_key' => $eventKey,
                'body_hash' => $bodyHash,
                'body_excerpt' => vmc_text_excerpt($bodyClean, 140),
                'content_len' => vmc_strlen($bodyClean),
                'reward_today_count' => (int)$dailyStats['count'],
                'reward_today_sum' => (int)$dailyStats['sum'],
            ],
        ];
    }
}

if (!function_exists('vmc_get_user_post_reward_stats_today')) {
    function vmc_get_user_post_reward_stats_today(PDO $pdo, int $userId): array
    {
        return vmc_get_user_event_reward_stats_today($pdo, $userId, 'post_create');
    }
}

if (!function_exists('vmc_get_user_comment_reward_stats_today')) {
    function vmc_get_user_comment_reward_stats_today(PDO $pdo, int $userId): array
    {
        return vmc_get_user_event_reward_stats_today($pdo, $userId, 'comment_create');
    }
}

if (!function_exists('vmc_reaction_amount')) {
    function vmc_reaction_amount(PDO $pdo, string $entityType, ?string $reaction, string $who = 'author'): int
    {
        $entityType = strtolower(trim($entityType));
        $reaction   = strtolower(trim((string)$reaction));
        $who        = strtolower(trim($who));

        if ($reaction !== 'like' && $reaction !== 'dislike') {
            return 0;
        }

        if ($entityType !== 'post' && $entityType !== 'comment') {
            return 0;
        }

        if ($who !== 'author' && $who !== 'actor') {
            $who = 'author';
        }

        $key = 'vmcoins_' . $entityType . '_' . $reaction . '_' . $who;
        return vmc_setting_int($pdo, $key, 0);
    }
}

if (!function_exists('vmc_get_balance')) {
    function vmc_get_balance(PDO $pdo, int $userId): int
    {
        if ($userId <= 0 || !vmc_user_columns_exist($pdo)) {
            return 0;
        }

        $st = $pdo->prepare("SELECT vm_coins_balance FROM users WHERE id = ? LIMIT 1");
        $st->execute([$userId]);

        $val = $st->fetchColumn();
        return ($val === false) ? 0 : (int)$val;
    }
}

if (!function_exists('vmc_find_user_by_username')) {
    function vmc_find_user_by_username(PDO $pdo, string $username): ?array
    {
        $username = trim($username);
        $username = ltrim($username, '@');

        if ($username === '') {
            return null;
        }

        if (!vmc_user_has_column($pdo, 'username')) {
            return null;
        }

        $needle = function_exists('mb_strtolower')
            ? (string)mb_strtolower($username, 'UTF-8')
            : strtolower($username);

        $cols = ['id', 'username'];
        if (vmc_user_has_column($pdo, 'vm_coins_balance')) {
            $cols[] = 'vm_coins_balance';
        }
        if (vmc_user_has_column($pdo, 'full_name')) {
            $cols[] = 'full_name';
        } else {
            if (vmc_user_has_column($pdo, 'first_name')) {
                $cols[] = 'first_name';
            }
            if (vmc_user_has_column($pdo, 'last_name')) {
                $cols[] = 'last_name';
            }
        }

        $sql = "
            SELECT " . implode(', ', array_unique($cols)) . "
            FROM users
            WHERE LOWER(username) = ?
            LIMIT 1
        ";

        $st = $pdo->prepare($sql);
        $st->execute([$needle]);

        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vmc_user_display_name')) {
    function vmc_user_display_name(array $user): string
    {
        $username = trim((string)($user['username'] ?? ''));
        $fullName = trim((string)($user['full_name'] ?? ''));

        if ($fullName === '') {
            $first = trim((string)($user['first_name'] ?? ''));
            $last  = trim((string)($user['last_name'] ?? ''));
            $fullName = trim($first . ' ' . $last);
        }

        if ($fullName !== '' && $username !== '') {
            return $fullName . ' (@' . $username . ')';
        }

        if ($fullName !== '') {
            return $fullName;
        }

        if ($username !== '') {
            return '@' . $username;
        }

        $id = (int)($user['id'] ?? 0);
        return $id > 0 ? ('#' . $id) : '';
    }
}

if (!function_exists('vmc_make_transfer_op_key')) {
    function vmc_make_transfer_op_key(int $fromUserId, int $toUserId): string
    {
        try {
            $rnd = bin2hex(random_bytes(12));
        } catch (Throwable $e) {
            $rnd = str_replace('.', '', uniqid('', true));
        }

        return 'vmc:transfer:' . $fromUserId . ':' . $toUserId . ':' . $rnd;
    }
}

if (!function_exists('vmc_find_tx_by_op_key')) {
    function vmc_find_tx_by_op_key(PDO $pdo, string $opKey): ?array
    {
        if ($opKey === '' || !vmc_table_exists($pdo, 'vm_coin_transactions')) {
            return null;
        }

        $st = $pdo->prepare("
            SELECT id, user_id, delta_amount, balance_after, event_key, entity_type, entity_id, related_user_id, op_key, note, meta_json, created_at
            FROM vm_coin_transactions
            WHERE op_key = ?
            LIMIT 1
        ");
        $st->execute([$opKey]);

        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vmc_add_transaction')) {
    function vmc_add_transaction(
        PDO $pdo,
        int $userId,
        int $deltaAmount,
        string $eventKey,
        ?string $entityType = null,
        ?int $entityId = null,
        ?int $relatedUserId = null,
        ?string $opKey = null,
        ?string $note = null,
        array $meta = []
    ): array {
        if ($userId <= 0) {
            return ['ok' => false, 'reason' => 'bad_user'];
        }

        if (!vmc_enabled($pdo)) {
            return ['ok' => false, 'reason' => 'disabled'];
        }

        if (!vmc_table_exists($pdo, 'vm_coin_transactions') || !vmc_user_columns_exist($pdo)) {
            return ['ok' => false, 'reason' => 'schema_missing'];
        }

        if ($deltaAmount === 0) {
            return ['ok' => false, 'reason' => 'zero_delta'];
        }

        $eventKey   = trim($eventKey);
        $entityType = $entityType !== null ? trim($entityType) : null;
        $opKey      = $opKey !== null ? trim($opKey) : null;
        $note       = $note !== null ? trim($note) : null;

        if ($opKey !== null && $opKey !== '') {
            $existing = vmc_find_tx_by_op_key($pdo, $opKey);
            if ($existing) {
                return [
                    'ok'        => true,
                    'duplicate' => true,
                    'tx_id'     => (int)$existing['id'],
                    'balance'   => (int)$existing['balance_after'],
                ];
            }
        } else {
            $opKey = null;
        }

        $ownTx = !$pdo->inTransaction();

        try {
            if ($ownTx) {
                $pdo->beginTransaction();
            }

            $st = $pdo->prepare("
                SELECT id, vm_coins_balance, vm_coins_earned_total, vm_coins_spent_total
                FROM users
                WHERE id = ?
                LIMIT 1
                FOR UPDATE
            ");
            $st->execute([$userId]);
            $u = $st->fetch(PDO::FETCH_ASSOC);

            if (!$u) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'user_not_found'];
            }

            $currentBalance = (int)$u['vm_coins_balance'];
            $earnedTotal    = (int)$u['vm_coins_earned_total'];
            $spentTotal     = (int)$u['vm_coins_spent_total'];
            $newBalance     = $currentBalance + $deltaAmount;

            if ($newBalance < 0) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }

                $requiredAmount = abs($deltaAmount);
                $missingAmount  = max(0, $requiredAmount - $currentBalance);

                return [
                    'ok' => false,
                    'reason' => 'insufficient_funds',
                    'balance' => $currentBalance,
                    'available_balance' => $currentBalance,
                    'required_amount' => $requiredAmount,
                    'missing_amount' => $missingAmount,
                    'delta_amount' => $deltaAmount,
                    'event_key' => $eventKey,
                ];
            }

            $newEarnedTotal = $earnedTotal + ($deltaAmount > 0 ? $deltaAmount : 0);
            $newSpentTotal  = $spentTotal + ($deltaAmount < 0 ? abs($deltaAmount) : 0);

            $metaJson = !empty($meta)
                ? json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES)
                : null;

            $ins = $pdo->prepare("
                INSERT INTO vm_coin_transactions
                (user_id, delta_amount, balance_after, event_key, entity_type, entity_id, related_user_id, op_key, note, meta_json, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
            ");
            $ins->execute([
                $userId,
                $deltaAmount,
                $newBalance,
                $eventKey,
                $entityType,
                $entityId,
                $relatedUserId,
                $opKey,
                $note,
                $metaJson,
            ]);

            $txId = (int)$pdo->lastInsertId();

            $up = $pdo->prepare("
                UPDATE users
                SET vm_coins_balance = ?,
                    vm_coins_earned_total = ?,
                    vm_coins_spent_total = ?,
                    vm_coins_updated_at = NOW()
                WHERE id = ?
                LIMIT 1
            ");
            $up->execute([
                $newBalance,
                $newEarnedTotal,
                $newSpentTotal,
                $userId,
            ]);

            if ($ownTx && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'ok'        => true,
                'duplicate' => false,
                'tx_id'     => $txId,
                'balance'   => $newBalance,
            ];
        } catch (Throwable $e) {
            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return [
                'ok'     => false,
                'reason' => 'exception',
                'error'  => $e->getMessage(),
            ];
        }
    }
}

if (!function_exists('vmc_transfer_between_users')) {
    function vmc_transfer_between_users(
        PDO $pdo,
        int $fromUserId,
        int $toUserId,
        int $amount,
        ?string $note = null,
        ?string $opKeyBase = null
    ): array {
        if ($fromUserId <= 0 || $toUserId <= 0) {
            return ['ok' => false, 'reason' => 'bad_user'];
        }

        if ($fromUserId === $toUserId) {
            return ['ok' => false, 'reason' => 'same_user'];
        }

        if ($amount <= 0) {
            return ['ok' => false, 'reason' => 'bad_amount'];
        }

        if (!vmc_enabled($pdo)) {
            return ['ok' => false, 'reason' => 'disabled'];
        }

        if (!vmc_table_exists($pdo, 'vm_coin_transactions') || !vmc_user_columns_exist($pdo)) {
            return ['ok' => false, 'reason' => 'schema_missing'];
        }

        $opKeyBase = trim((string)$opKeyBase);
        if ($opKeyBase === '') {
            $opKeyBase = vmc_make_transfer_op_key($fromUserId, $toUserId);
        }

        $note = trim((string)$note);
        if ($note === '') {
            $note = 'VM Coins transfer';
        }

        $ownTx = !$pdo->inTransaction();

        try {
            if ($ownTx) {
                $pdo->beginTransaction();
            }

            $lockIds = [$fromUserId, $toUserId];
            sort($lockIds, SORT_NUMERIC);

            $placeholders = implode(',', array_fill(0, count($lockIds), '?'));
            $st = $pdo->prepare("
                SELECT id
                FROM users
                WHERE id IN ($placeholders)
                ORDER BY id ASC
                FOR UPDATE
            ");
            $st->execute($lockIds);

            $lockedRows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $lockedIds = [];
            foreach ($lockedRows as $row) {
                $lockedIds[] = (int)($row['id'] ?? 0);
            }

            if (!in_array($fromUserId, $lockedIds, true)) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'sender_not_found'];
            }

            if (!in_array($toUserId, $lockedIds, true)) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'recipient_not_found'];
            }

            $senderTx = vmc_add_transaction(
                $pdo,
                $fromUserId,
                -$amount,
                'user_transfer_out',
                'user',
                $toUserId,
                $toUserId,
                $opKeyBase . ':out',
                $note,
                [
                    'transfer_direction' => 'out',
                    'counterparty_user_id' => $toUserId,
                    'amount' => $amount,
                    'op_key_base' => $opKeyBase,
                ]
            );

            if (!($senderTx['ok'] ?? false)) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return [
                    'ok' => false,
                    'reason' => (string)($senderTx['reason'] ?? 'sender_transfer_failed'),
                    'sender' => $senderTx,
                    'amount' => $amount,
                    'from_user_id' => $fromUserId,
                    'to_user_id' => $toUserId,
                ];
            }

            $recipientTx = vmc_add_transaction(
                $pdo,
                $toUserId,
                $amount,
                'user_transfer_in',
                'user',
                $fromUserId,
                $fromUserId,
                $opKeyBase . ':in',
                $note,
                [
                    'transfer_direction' => 'in',
                    'counterparty_user_id' => $fromUserId,
                    'amount' => $amount,
                    'op_key_base' => $opKeyBase,
                ]
            );

            if (!($recipientTx['ok'] ?? false)) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return [
                    'ok' => false,
                    'reason' => (string)($recipientTx['reason'] ?? 'recipient_transfer_failed'),
                    'sender' => $senderTx,
                    'recipient' => $recipientTx,
                    'amount' => $amount,
                    'from_user_id' => $fromUserId,
                    'to_user_id' => $toUserId,
                ];
            }

            if ($ownTx && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'ok' => true,
                'reason' => 'transferred',
                'amount' => $amount,
                'from_user_id' => $fromUserId,
                'to_user_id' => $toUserId,
                'sender' => $senderTx,
                'recipient' => $recipientTx,
                'sender_balance' => (int)($senderTx['balance'] ?? 0),
                'recipient_balance' => (int)($recipientTx['balance'] ?? 0),
                'op_key_base' => $opKeyBase,
            ];
        } catch (Throwable $e) {
            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return [
                'ok' => false,
                'reason' => 'exception',
                'error' => $e->getMessage(),
            ];
        }
    }
}

if (!function_exists('vmc_reward_post_create')) {
    function vmc_reward_post_create(PDO $pdo, int $userId, int $postId, ?string $body): array
    {
        if ($userId <= 0 || $postId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        $guard = vmc_can_reward_create_content($pdo, $userId, 'post', $body);
        if (!($guard['ok'] ?? false)) {
            return $guard;
        }

        $amount = vmc_setting_int($pdo, 'vmcoins_post_create', 0);
        if ($amount === 0) {
            return ['ok' => false, 'reason' => 'amount_zero'];
        }

        $meta = is_array($guard['meta'] ?? null) ? $guard['meta'] : [];
        $meta['post_id'] = $postId;

        return vmc_add_transaction(
            $pdo,
            $userId,
            $amount,
            'post_create',
            'post',
            $postId,
            null,
            'vmc:post_create:' . $userId . ':' . $postId,
            'Reward for post creation',
            $meta
        );
    }
}

if (!function_exists('vmc_reward_comment_create')) {
    function vmc_reward_comment_create(PDO $pdo, int $userId, int $commentId, int $postId, ?string $body): array
    {
        if ($userId <= 0 || $commentId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        $guard = vmc_can_reward_create_content($pdo, $userId, 'comment', $body);
        if (!($guard['ok'] ?? false)) {
            return $guard;
        }

        $amount = vmc_setting_int($pdo, 'vmcoins_comment_create', 0);
        if ($amount === 0) {
            return ['ok' => false, 'reason' => 'amount_zero'];
        }

        $meta = is_array($guard['meta'] ?? null) ? $guard['meta'] : [];
        $meta['post_id'] = $postId;
        $meta['comment_id'] = $commentId;

        return vmc_add_transaction(
            $pdo,
            $userId,
            $amount,
            'comment_create',
            'comment',
            $commentId,
            null,
            'vmc:comment_create:' . $userId . ':' . $commentId,
            'Reward for comment creation',
            $meta
        );
    }
}

if (!function_exists('vmc_reaction_event_key')) {
    function vmc_reaction_event_key(string $entityType): string
    {
        $entityType = strtolower(trim($entityType));
        return match ($entityType) {
            'post' => 'post_reaction_adjust',
            'comment' => 'comment_reaction_adjust',
            default => '',
        };
    }
}

if (!function_exists('vmc_reaction_op_key_for_side')) {
    function vmc_reaction_op_key_for_side(?string $baseOpKey, string $side): ?string
    {
        $baseOpKey = trim((string)$baseOpKey);
        $side = strtolower(trim($side));

        if ($baseOpKey === '') {
            return null;
        }

        if ($side === '') {
            return $baseOpKey;
        }

        return $baseOpKey . ':' . $side;
    }
}

if (!function_exists('vmc_apply_reaction_transition_for_side')) {
    function vmc_apply_reaction_transition_for_side(
        PDO $pdo,
        string $entityType,
        string $side,
        int $actorUserId,
        int $authorUserId,
        int $entityId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        $entityType = strtolower(trim($entityType));
        $side       = strtolower(trim($side));

        if (($entityType !== 'post' && $entityType !== 'comment') || $entityId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        if ($side !== 'author' && $side !== 'actor') {
            return ['ok' => false, 'reason' => 'bad_side'];
        }

        if ($authorUserId <= 0 || $actorUserId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        if ($authorUserId === $actorUserId) {
            return ['ok' => false, 'reason' => 'self_reaction_skip'];
        }

        $targetUserId = $side === 'author' ? $authorUserId : $actorUserId;
        if ($targetUserId <= 0) {
            return ['ok' => false, 'reason' => 'bad_user'];
        }

        $oldAmount = vmc_reaction_amount($pdo, $entityType, $oldReaction, $side);
        $newAmount = vmc_reaction_amount($pdo, $entityType, $newReaction, $side);
        $delta     = $newAmount - $oldAmount;

        if ($delta === 0) {
            return [
                'ok' => false,
                'reason' => 'zero_delta',
                'delta' => 0,
                'side' => $side,
            ];
        }

        $eventKey = vmc_reaction_event_key($entityType);
        if ($eventKey === '') {
            return ['ok' => false, 'reason' => 'bad_event_key'];
        }

        $relatedUserId = $side === 'author' ? $actorUserId : $authorUserId;

        $oldLabel = ($oldReaction !== null && trim((string)$oldReaction) !== '') ? trim((string)$oldReaction) : 'none';
        $newLabel = ($newReaction !== null && trim((string)$newReaction) !== '') ? trim((string)$newReaction) : 'none';

        $note = sprintf(
            '%s reaction transition (%s): %s -> %s',
            $entityType,
            $side,
            $oldLabel,
            $newLabel
        );

        $meta = [
            'reward_side' => $side,
            'target_user_id' => $targetUserId,
            'author_user_id' => $authorUserId,
            'actor_user_id' => $actorUserId,
            'old_reaction' => $oldReaction,
            'new_reaction' => $newReaction,
            'old_amount' => $oldAmount,
            'new_amount' => $newAmount,
            'delta' => $delta,
        ];

        $tx = vmc_add_transaction(
            $pdo,
            $targetUserId,
            $delta,
            $eventKey,
            $entityType,
            $entityId,
            $relatedUserId > 0 ? $relatedUserId : null,
            vmc_reaction_op_key_for_side($opKey, $side),
            $note,
            $meta
        );

        if (!is_array($tx)) {
            return ['ok' => false, 'reason' => 'unexpected_result', 'side' => $side];
        }

        $tx['side'] = $side;
        $tx['delta'] = $delta;
        $tx['target_user_id'] = $targetUserId;

        return $tx;
    }
}

if (!function_exists('vmc_apply_reaction_transition_for_author')) {
    function vmc_apply_reaction_transition_for_author(
        PDO $pdo,
        string $entityType,
        int $actorUserId,
        int $authorUserId,
        int $entityId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        return vmc_apply_reaction_transition_for_side(
            $pdo,
            $entityType,
            'author',
            $actorUserId,
            $authorUserId,
            $entityId,
            $oldReaction,
            $newReaction,
            $opKey
        );
    }
}

if (!function_exists('vmc_apply_reaction_transition_for_actor')) {
    function vmc_apply_reaction_transition_for_actor(
        PDO $pdo,
        string $entityType,
        int $actorUserId,
        int $authorUserId,
        int $entityId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        return vmc_apply_reaction_transition_for_side(
            $pdo,
            $entityType,
            'actor',
            $actorUserId,
            $authorUserId,
            $entityId,
            $oldReaction,
            $newReaction,
            $opKey
        );
    }
}

if (!function_exists('vmc_apply_reaction_transition')) {
    function vmc_apply_reaction_transition(
        PDO $pdo,
        string $entityType,
        int $actorUserId,
        int $authorUserId,
        int $entityId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        $entityType = strtolower(trim($entityType));

        if (($entityType !== 'post' && $entityType !== 'comment') || $entityId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        if ($actorUserId <= 0 || $authorUserId <= 0) {
            return ['ok' => false, 'reason' => 'bad_input'];
        }

        if ($actorUserId === $authorUserId) {
            return ['ok' => false, 'reason' => 'self_reaction_skip'];
        }

        $ownTx = !$pdo->inTransaction();

        try {
            if ($ownTx) {
                $pdo->beginTransaction();
            }

            $authorRes = vmc_apply_reaction_transition_for_side(
                $pdo,
                $entityType,
                'author',
                $actorUserId,
                $authorUserId,
                $entityId,
                $oldReaction,
                $newReaction,
                $opKey
            );

            $actorRes = vmc_apply_reaction_transition_for_side(
                $pdo,
                $entityType,
                'actor',
                $actorUserId,
                $authorUserId,
                $entityId,
                $oldReaction,
                $newReaction,
                $opKey
            );

            $authorApplied = (bool)($authorRes['ok'] ?? false);
            $actorApplied  = (bool)($actorRes['ok'] ?? false);

            $authorSilent = in_array((string)($authorRes['reason'] ?? ''), ['zero_delta'], true);
            $actorSilent  = in_array((string)($actorRes['reason'] ?? ''), ['zero_delta'], true);

            if (!$authorApplied && !$authorSilent) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $authorRes['where'] = 'author';
                return $authorRes;
            }

            if (!$actorApplied && !$actorSilent) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $actorRes['where'] = 'actor';
                return $actorRes;
            }

            if (!$authorApplied && !$actorApplied) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }

                return [
                    'ok' => false,
                    'reason' => 'zero_delta',
                    'author' => $authorRes,
                    'actor' => $actorRes,
                ];
            }

            if ($ownTx && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'ok' => true,
                'reason' => 'applied',
                'author' => $authorRes,
                'actor' => $actorRes,
                'author_applied' => $authorApplied,
                'actor_applied' => $actorApplied,
                'author_delta' => (int)($authorRes['delta'] ?? 0),
                'actor_delta' => (int)($actorRes['delta'] ?? 0),
            ];
        } catch (Throwable $e) {
            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return [
                'ok' => false,
                'reason' => 'exception',
                'error' => $e->getMessage(),
            ];
        }
    }
}

if (!function_exists('vmc_apply_post_reaction_transition')) {
    function vmc_apply_post_reaction_transition(
        PDO $pdo,
        int $actorUserId,
        int $postAuthorUserId,
        int $postId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        return vmc_apply_reaction_transition(
            $pdo,
            'post',
            $actorUserId,
            $postAuthorUserId,
            $postId,
            $oldReaction,
            $newReaction,
            $opKey
        );
    }
}

if (!function_exists('vmc_apply_comment_reaction_transition')) {
    function vmc_apply_comment_reaction_transition(
        PDO $pdo,
        int $actorUserId,
        int $commentAuthorUserId,
        int $commentId,
        ?string $oldReaction,
        ?string $newReaction,
        ?string $opKey = null
    ): array {
        return vmc_apply_reaction_transition(
            $pdo,
            'comment',
            $actorUserId,
            $commentAuthorUserId,
            $commentId,
            $oldReaction,
            $newReaction,
            $opKey
        );
    }
}

/* =========================
   Stage 2 · Referral codes
========================= */

if (!function_exists('vmc_ref_normalize_code')) {
    function vmc_ref_normalize_code(?string $code): string
    {
        $code = trim((string)$code);
        if ($code === '') {
            return '';
        }

        $code = preg_replace('/[^a-zA-Z0-9]+/', '', $code) ?? '';
        $code = vmc_upper($code);

        if ($code === '') {
            return '';
        }

        if (strlen($code) > 32) {
            $code = substr($code, 0, 32);
        }

        return $code;
    }
}

if (!function_exists('vmc_ref_make_candidate')) {
    function vmc_ref_make_candidate(string $username, int $userId, int $attempt = 0): string
    {
        $username = vmc_ref_normalize_code($username);
        $base = $username !== '' ? substr($username, 0, 6) : 'USER';

        if ($attempt <= 0) {
            $suffix = str_pad((string)abs($userId % 10000), 4, '0', STR_PAD_LEFT);
            return substr($base, 0, 6) . $suffix;
        }

        $rand = vmc_upper(bin2hex(random_bytes(2)));
        return substr($base, 0, 6) . $rand;
    }
}

if (!function_exists('vmc_ensure_user_ref_code')) {
    function vmc_ensure_user_ref_code(PDO $pdo, int $userId, ?string $username = null): string
    {
        if ($userId <= 0 || !vmc_referral_columns_exist($pdo)) {
            return '';
        }

        $ownTx = !$pdo->inTransaction();

        try {
            if ($ownTx) {
                $pdo->beginTransaction();
            }

            $st = $pdo->prepare("
                SELECT id, username, vm_ref_code
                FROM users
                WHERE id = ?
                LIMIT 1
                FOR UPDATE
            ");
            $st->execute([$userId]);
            $u = $st->fetch(PDO::FETCH_ASSOC);

            if (!$u) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return '';
            }

            $existingRaw = trim((string)($u['vm_ref_code'] ?? ''));
            $existing = vmc_ref_normalize_code($existingRaw);
            if ($existing !== '') {
                if ($existingRaw !== $existing) {
                    $up = $pdo->prepare("UPDATE users SET vm_ref_code = ? WHERE id = ? LIMIT 1");
                    $up->execute([$existing, $userId]);
                }

                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->commit();
                }
                return $existing;
            }

            $uname = trim((string)($username ?? ''));
            if ($uname === '') {
                $uname = trim((string)($u['username'] ?? ''));
            }

            for ($i = 0; $i < 20; $i++) {
                $candidate = vmc_ref_make_candidate($uname, $userId, $i);

                $chk = $pdo->prepare("SELECT id FROM users WHERE vm_ref_code = ? LIMIT 1");
                $chk->execute([$candidate]);
                $foundId = (int)($chk->fetchColumn() ?: 0);

                if ($foundId > 0 && $foundId !== $userId) {
                    continue;
                }

                $up = $pdo->prepare("UPDATE users SET vm_ref_code = ? WHERE id = ? LIMIT 1");
                $up->execute([$candidate, $userId]);

                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->commit();
                }

                return $candidate;
            }

            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return '';
        } catch (Throwable $e) {
            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return '';
        }
    }
}

if (!function_exists('vmc_find_user_by_ref_code')) {
    function vmc_find_user_by_ref_code(PDO $pdo, string $refCode): ?array
    {
        $refCode = vmc_ref_normalize_code($refCode);
        if ($refCode === '' || !vmc_referral_columns_exist($pdo)) {
            return null;
        }

        $activeCol = vmc_detect_active_column($pdo);
        $activeSql = $activeCol !== null ? ", `$activeCol` AS is_active" : ", 1 AS is_active";

        $sql = "
            SELECT id, username, first_name, last_name, full_name, vm_ref_code{$activeSql}
            FROM users
            WHERE vm_ref_code = ?
            LIMIT 1
        ";
        $st = $pdo->prepare($sql);
        $st->execute([$refCode]);

        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vmc_get_user_referrals_count')) {
    function vmc_get_user_referrals_count(PDO $pdo, int $userId): int
    {
        if ($userId <= 0 || !vmc_referral_columns_exist($pdo)) {
            return 0;
        }

        $st = $pdo->prepare("SELECT COUNT(*) FROM users WHERE vm_referred_by_user_id = ?");
        $st->execute([$userId]);

        return (int)$st->fetchColumn();
    }
}

if (!function_exists('vmc_get_user_recent_transactions')) {
    function vmc_get_user_recent_transactions(PDO $pdo, int $userId, int $limit = 20): array
    {
        if ($userId <= 0 || !vmc_table_exists($pdo, 'vm_coin_transactions')) {
            return [];
        }

        $limit = max(1, min(100, $limit));
        $sql = "
            SELECT
                id,
                user_id,
                delta_amount,
                balance_after,
                event_key,
                entity_type,
                entity_id,
                related_user_id,
                note,
                meta_json,
                created_at
            FROM vm_coin_transactions
            WHERE user_id = ?
            ORDER BY id DESC
            LIMIT {$limit}
        ";
        $st = $pdo->prepare($sql);
        $st->execute([$userId]);

        return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('vmc_apply_referral_code')) {
    function vmc_apply_referral_code(PDO $pdo, int $userId, string $refCode): array
    {
        if ($userId <= 0) {
            return ['ok' => false, 'reason' => 'bad_user'];
        }

        if (!vmc_referral_columns_exist($pdo)) {
            return ['ok' => false, 'reason' => 'schema_missing'];
        }

        if (!vmc_referrals_enabled($pdo)) {
            return ['ok' => false, 'reason' => 'disabled'];
        }

        $refCode = vmc_ref_normalize_code($refCode);
        if ($refCode === '') {
            return ['ok' => false, 'reason' => 'bad_code'];
        }

        $activeCol = vmc_detect_active_column($pdo);
        $activeSql = $activeCol !== null ? ", `$activeCol` AS is_active" : ", 1 AS is_active";
        $needActive = vmc_referral_require_active($pdo);

        $ownTx = !$pdo->inTransaction();

        try {
            if ($ownTx) {
                $pdo->beginTransaction();
            }

            $sqlUser = "
                SELECT id, username, vm_ref_code, vm_referred_by_user_id, vm_ref_applied_at{$activeSql}
                FROM users
                WHERE id = ?
                LIMIT 1
                FOR UPDATE
            ";
            $stUser = $pdo->prepare($sqlUser);
            $stUser->execute([$userId]);
            $user = $stUser->fetch(PDO::FETCH_ASSOC);

            if (!$user) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'user_not_found'];
            }

            if ($needActive && (int)($user['is_active'] ?? 1) !== 1) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'inactive_user'];
            }

            $alreadyReferrerId = (int)($user['vm_referred_by_user_id'] ?? 0);
            $alreadyAppliedAt  = trim((string)($user['vm_ref_applied_at'] ?? ''));

            if ($alreadyReferrerId > 0 || $alreadyAppliedAt !== '') {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'already_applied'];
            }

            $sqlRef = "
                SELECT id, username, vm_ref_code{$activeSql}
                FROM users
                WHERE vm_ref_code = ?
                LIMIT 1
                FOR UPDATE
            ";
            $stRef = $pdo->prepare($sqlRef);
            $stRef->execute([$refCode]);
            $referrer = $stRef->fetch(PDO::FETCH_ASSOC);

            if (!$referrer) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'code_not_found'];
            }

            $referrerId = (int)($referrer['id'] ?? 0);
            if ($referrerId <= 0) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'code_not_found'];
            }

            if ($referrerId === $userId) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'self_code'];
            }

            if ($needActive && (int)($referrer['is_active'] ?? 1) !== 1) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'referrer_inactive'];
            }

            $up = $pdo->prepare("
                UPDATE users
                SET vm_referred_by_user_id = ?,
                    vm_ref_applied_at = NOW()
                WHERE id = ?
                  AND (vm_referred_by_user_id IS NULL OR vm_referred_by_user_id = 0)
                  AND vm_ref_applied_at IS NULL
                LIMIT 1
            ");
            $up->execute([$referrerId, $userId]);

            if ($up->rowCount() <= 0) {
                if ($ownTx && $pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                return ['ok' => false, 'reason' => 'already_applied'];
            }

            $referredBonus = vmc_referral_bonus_referred($pdo);
            $referrerBonus = vmc_referral_bonus_referrer($pdo);

            if ($referredBonus !== 0) {
                $tx = vmc_add_transaction(
                    $pdo,
                    $userId,
                    $referredBonus,
                    'referral_bonus_referred',
                    'user',
                    $userId,
                    $referrerId,
                    'vmc:referral:referred:' . $userId,
                    'Referral bonus for applying promo code',
                    [
                        'ref_code' => $refCode,
                        'referrer_user_id' => $referrerId,
                    ]
                );

                if (!($tx['ok'] ?? false)) {
                    if ($ownTx && $pdo->inTransaction()) {
                        $pdo->rollBack();
                    }
                    return ['ok' => false, 'reason' => 'referred_bonus_failed', 'detail' => $tx];
                }
            }

            if ($referrerBonus !== 0) {
                $tx = vmc_add_transaction(
                    $pdo,
                    $referrerId,
                    $referrerBonus,
                    'referral_bonus_referrer',
                    'user',
                    $userId,
                    $userId,
                    'vmc:referral:referrer:' . $referrerId . ':' . $userId,
                    'Referral bonus for invited user',
                    [
                        'ref_code' => $refCode,
                        'applied_user_id' => $userId,
                    ]
                );

                if (!($tx['ok'] ?? false)) {
                    if ($ownTx && $pdo->inTransaction()) {
                        $pdo->rollBack();
                    }
                    return ['ok' => false, 'reason' => 'referrer_bonus_failed', 'detail' => $tx];
                }
            }

            if ($ownTx && $pdo->inTransaction()) {
                $pdo->commit();
            }

            return [
                'ok'             => true,
                'reason'         => 'applied',
                'ref_code'       => $refCode,
                'referrer_id'    => $referrerId,
                'referred_bonus' => $referredBonus,
                'referrer_bonus' => $referrerBonus,
            ];
        } catch (Throwable $e) {
            if ($ownTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }

            return [
                'ok'     => false,
                'reason' => 'exception',
                'error'  => $e->getMessage(),
            ];
        }
    }
}