<?php
// Full SQL storage layer for Telegram member/order bot.
// Keeps the old bot_data_get_json()/bot_data_put_json() API so the bot logic stays stable,
// but stores managed data in normalized MySQL tables instead of flat JSON files.

if (!defined('BOT_SQL_ENABLED')) define('BOT_SQL_ENABLED', false);
if (!defined('BOT_SQL_AUTO_INSTALL')) define('BOT_SQL_AUTO_INSTALL', true);
// Full SQL mode: do NOT mirror managed JSON files by default.
// Set true only if you want a file backup during migration/debugging.
if (!defined('BOT_SQL_MIRROR_FILES')) define('BOT_SQL_MIRROR_FILES', false);
if(!function_exists('bot_scale_cache_get') && is_file(__DIR__ . '/../app/Services/ScaleOptimizerService.php')){ require_once __DIR__ . '/../app/Services/ScaleOptimizerService.php'; if(function_exists('bot_scale_bootstrap')) bot_scale_bootstrap(); }

function bot_storage_root(){ return str_replace('\\\\','/', dirname(__DIR__)); }

function bot_storage_normalize_path($path) {
    $path = str_replace('\\', '/', (string)$path);
    $root = rtrim(bot_storage_root(), '/');
    if (strpos($path, $root . '/') === 0) $path = substr($path, strlen($root) + 1);
    $path = preg_replace('~^/+~', '', $path);
    $path = preg_replace('~^\./+~', '', $path);
    $parts = [];
    foreach (explode('/', $path) as $part) {
        if ($part === '' || $part === '.') continue;
        if ($part === '..') { array_pop($parts); continue; }
        $parts[] = $part;
    }
    return implode('/', $parts);
}

function bot_storage_path_kind($path) {
    $path = bot_storage_normalize_path($path);
    if (preg_match('~^melat/([0-9]{1,32})\.json$~', $path, $m)) return ['kind'=>'user','id'=>$m[1],'path'=>$path];
    if (preg_match('~^ads/([A-Za-z0-9_\-\.]{1,64})\.json$~', $path, $m)) return ['kind'=>'order','id'=>$m[1],'path'=>$path];
    if (preg_match('~^lib/spam/([0-9]{1,32})\.json$~', $path, $m)) return ['kind'=>'spam','id'=>$m[1],'path'=>$path];
    if ($path === 'melat.json' || $path === 'shop-factor.json') return ['kind'=>'setting','id'=>$path,'path'=>$path];
    // Button labels, unit names, shop item labels, and custom keyboard layout files
    // are admin-editable settings. In full SQL mode they must be stored/read
    // through bot_settings; otherwise button renames appear to save but do not
    // survive or update when the file system is not writable.
    if (strpos($path, 'lib/Button/') === 0) return ['kind'=>'setting','id'=>$path,'path'=>$path];
    if ($path === 'lib/keyboard/channelkey.txt' || strpos($path, 'lib/keyboard/home/') === 0) return ['kind'=>'setting','id'=>$path,'path'=>$path];
    if (strpos($path, 'lib/kodam/') === 0) {
        if ($path === 'lib/kodam/channel_reward_users.json') return ['kind'=>'channel_rewards','id'=>$path,'path'=>$path];
        return ['kind'=>'setting','id'=>$path,'path'=>$path];
    }
    return ['kind'=>'file','id'=>$path,'path'=>$path];
}

function bot_storage_is_managed_path($path) {
    $kind = bot_storage_path_kind($path)['kind'];
    return in_array($kind, ['user','order','setting','channel_rewards','spam'], true);
}

function bot_sql_pdo() {
    static $pdo = null;
    static $failed = false;
    if ($pdo instanceof PDO) return $pdo;
    if ($failed || !defined('BOT_SQL_ENABLED') || !BOT_SQL_ENABLED) return null;
    if (!defined('DB_HOST') || !defined('DB_NAME') || !defined('DB_USER')) {
        error_log('BOT SQL: DB constants missing.');
        $failed = true;
        return null;
    }
    $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';
    $pass = defined('DB_PASS') ? DB_PASS : '';
    try {
        $pdo = new PDO(
            'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . $charset,
            DB_USER,
            $pass,
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => (defined('BOT_SQL_PERSISTENT') && BOT_SQL_PERSISTENT),
            ]
        );
        if (defined('BOT_SQL_AUTO_INSTALL') && BOT_SQL_AUTO_INSTALL) bot_sql_install_schema($pdo);
        return $pdo;
    } catch (Throwable $e) {
        error_log('BOT SQL connection failed: ' . $e->getMessage());
        $failed = true;
        return null;
    }
}

function bot_sql_install_schema(PDO $pdo) {
    static $done = false;
    if ($done) return;
    $schema = [
        "CREATE TABLE IF NOT EXISTS bot_users (
            user_id VARCHAR(32) NOT NULL PRIMARY KEY,
            username VARCHAR(191) NULL,
            first_name VARCHAR(191) NULL,
            step VARCHAR(191) NULL,
            points INT NOT NULL DEFAULT 0,
            referrer_id VARCHAR(32) NULL,
            data_json LONGTEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_points (points),
            KEY idx_step (step),
            KEY idx_referrer (referrer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_orders (
            order_id VARCHAR(64) NOT NULL PRIMARY KEY,
            owner_id VARCHAR(32) NULL,
            channel_username VARCHAR(191) NULL,
            channel_chat_id VARCHAR(64) NULL,
            target_count INT NOT NULL DEFAULT 0,
            reward_per_member INT NOT NULL DEFAULT 0,
            received_count INT NOT NULL DEFAULT 0,
            left_count INT NOT NULL DEFAULT 0,
            status VARCHAR(100) NULL,
            post_chat_id VARCHAR(64) NULL,
            post_message_id VARCHAR(64) NULL,
            created_ts BIGINT NULL,
            data_json LONGTEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_owner (owner_id),
            KEY idx_owner_created (owner_id, created_ts),
            KEY idx_channel (channel_username),
            KEY idx_status (status),
            KEY idx_created_ts (created_ts)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_order_members (
            order_id VARCHAR(64) NOT NULL,
            user_id VARCHAR(32) NOT NULL,
            joined_ts BIGINT NULL,
            left_ts BIGINT NULL,
            penalized TINYINT(1) NOT NULL DEFAULT 0,
            PRIMARY KEY (order_id, user_id),
            KEY idx_member_user (user_id),
            KEY idx_left (left_ts),
            CONSTRAINT fk_order_members_order FOREIGN KEY (order_id) REFERENCES bot_orders(order_id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_settings (
            setting_key VARCHAR(191) NOT NULL PRIMARY KEY,
            storage_type ENUM('json','text','raw') NOT NULL DEFAULT 'raw',
            storage_value LONGTEXT NULL,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_channel_rewards (
            user_id VARCHAR(32) NOT NULL,
            channel_key VARCHAR(191) NOT NULL,
            last_order_id VARCHAR(64) NULL,
            channel_chat_id VARCHAR(64) NULL,
            last_reward_ts BIGINT NULL,
            data_json LONGTEXT NULL,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (user_id, channel_key),
            KEY idx_channel_reward (channel_key),
            KEY idx_reward_ts (last_reward_ts)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_spam (
            user_id VARCHAR(32) NOT NULL PRIMARY KEY,
            data_json LONGTEXT NOT NULL,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_payments (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            user_id VARCHAR(32) NOT NULL,
            authority VARCHAR(128) NULL,
            ref_id VARCHAR(128) NULL,
            type VARCHAR(50) NOT NULL,
            amount INT NOT NULL DEFAULT 0,
            reward_amount INT NOT NULL DEFAULT 0,
            panel_type VARCHAR(50) NULL,
            panel_days INT NOT NULL DEFAULT 0,
            status VARCHAR(30) NOT NULL DEFAULT 'created',
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_authority (authority),
            KEY idx_user (user_id),
            KEY idx_status (status),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_order_reports (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            order_id VARCHAR(64) NOT NULL,
            reporter_id VARCHAR(32) NOT NULL,
            report_source VARCHAR(30) NOT NULL DEFAULT 'user',
            status VARCHAR(30) NOT NULL DEFAULT 'open',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_order_reporter (order_id, reporter_id),
            KEY idx_order_status (order_id, status),
            KEY idx_reporter (reporter_id),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_order_moderation_actions (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            order_id VARCHAR(64) NOT NULL,
            actor_id VARCHAR(32) NULL,
            action_type VARCHAR(40) NOT NULL,
            reason VARCHAR(191) NULL,
            report_count INT NOT NULL DEFAULT 0,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_order_action (order_id, action_type),
            KEY idx_actor (actor_id),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_anti_spam (
            user_id VARCHAR(32) NOT NULL PRIMARY KEY,
            window_start BIGINT NOT NULL DEFAULT 0,
            message_count INT NOT NULL DEFAULT 0,
            blocked_until BIGINT NOT NULL DEFAULT 0,
            last_notice_at BIGINT NOT NULL DEFAULT 0,
            data_json LONGTEXT NULL,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_blocked_until (blocked_until),
            KEY idx_updated (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_order_cancel_actions (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            order_id VARCHAR(64) NOT NULL,
            owner_id VARCHAR(32) NULL,
            actor_id VARCHAR(32) NULL,
            cancel_type VARCHAR(30) NOT NULL DEFAULT 'user',
            refund_amount INT NOT NULL DEFAULT 0,
            remaining_count INT NOT NULL DEFAULT 0,
            reason VARCHAR(191) NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_order_cancel (order_id),
            KEY idx_owner_cancel (owner_id),
            KEY idx_actor_cancel (actor_id),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_referrals (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            referrer_id VARCHAR(32) NOT NULL,
            referred_id VARCHAR(32) NOT NULL,
            status VARCHAR(32) NOT NULL DEFAULT 'started',
            signup_reward_mode VARCHAR(20) NOT NULL DEFAULT 'diamond',
            signup_reward_amount INT NOT NULL DEFAULT 0,
            signup_reward_applied TINYINT(1) NOT NULL DEFAULT 0,
            active_required_count INT NOT NULL DEFAULT 0,
            active_reward_mode VARCHAR(20) NOT NULL DEFAULT 'diamond',
            active_reward_amount INT NOT NULL DEFAULT 0,
            active_reward_applied TINYINT(1) NOT NULL DEFAULT 0,
            referred_join_count INT NOT NULL DEFAULT 0,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_referred (referred_id),
            KEY idx_referrer (referrer_id),
            KEY idx_status (status),
            KEY idx_referrer_created (referrer_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_referral_events (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            referrer_id VARCHAR(32) NOT NULL,
            referred_id VARCHAR(32) NOT NULL,
            event_type VARCHAR(40) NOT NULL,
            reward_mode VARCHAR(20) NULL,
            amount INT NOT NULL DEFAULT 0,
            before_balance INT NULL,
            after_balance INT NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_referrer (referrer_id),
            KEY idx_referred (referred_id),
            KEY idx_event_type (event_type),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_daily_rewards (
            user_id VARCHAR(32) NOT NULL PRIMARY KEY,
            last_claim_ts BIGINT NOT NULL DEFAULT 0,
            next_claim_ts BIGINT NOT NULL DEFAULT 0,
            claim_count INT NOT NULL DEFAULT 0,
            total_amount INT NOT NULL DEFAULT 0,
            data_json LONGTEXT NULL,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_next_claim (next_claim_ts),
            KEY idx_updated (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_daily_reward_events (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            user_id VARCHAR(32) NOT NULL,
            amount INT NOT NULL DEFAULT 0,
            before_balance INT NULL,
            after_balance INT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'claimed',
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_user_created (user_id, created_at),
            KEY idx_status (status),
            KEY idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_daily_notify_jobs (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            job_key VARCHAR(80) NOT NULL,
            source VARCHAR(20) NOT NULL DEFAULT 'auto',
            status VARCHAR(20) NOT NULL DEFAULT 'queued',
            message_text LONGTEXT NULL,
            total INT NOT NULL DEFAULT 0,
            processed INT NOT NULL DEFAULT 0,
            success INT NOT NULL DEFAULT 0,
            failed INT NOT NULL DEFAULT 0,
            last_user_id VARCHAR(32) NOT NULL DEFAULT '0',
            last_error VARCHAR(255) NULL,
            created_ts BIGINT NOT NULL DEFAULT 0,
            started_ts BIGINT NOT NULL DEFAULT 0,
            finished_ts BIGINT NOT NULL DEFAULT 0,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_daily_job_key (job_key),
            KEY idx_daily_status (status, id),
            KEY idx_daily_source_created (source, created_ts),
            KEY idx_daily_updated (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_gift_codes (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            code VARCHAR(128) NOT NULL,
            amount INT NOT NULL DEFAULT 0,
            status VARCHAR(24) NOT NULL DEFAULT 'active',
            created_by VARCHAR(32) NULL,
            used_by VARCHAR(32) NULL,
            used_at TIMESTAMP NULL DEFAULT NULL,
            channel_ref VARCHAR(191) NULL,
            channel_message_id VARCHAR(64) NULL,
            published_at TIMESTAMP NULL DEFAULT NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_gift_code (code),
            KEY idx_gift_status (status),
            KEY idx_gift_used_by (used_by),
            KEY idx_gift_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_gift_code_events (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            gift_id BIGINT UNSIGNED NULL,
            code VARCHAR(128) NOT NULL,
            actor_id VARCHAR(32) NULL,
            event_type VARCHAR(40) NOT NULL,
            amount INT NOT NULL DEFAULT 0,
            before_balance INT NULL,
            after_balance INT NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_gift_event_code (code),
            KEY idx_gift_event_actor (actor_id),
            KEY idx_gift_event_type (event_type),
            KEY idx_gift_event_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_admin_logs (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            actor_id VARCHAR(32) NULL,
            action_type VARCHAR(80) NOT NULL,
            target_type VARCHAR(80) NULL,
            target_id VARCHAR(191) NULL,
            description TEXT NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_actor_created (actor_id, created_at),
            KEY idx_action_created (action_type, created_at),
            KEY idx_target (target_type, target_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_wallet_ledger (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            user_id VARCHAR(32) NOT NULL,
            asset_type VARCHAR(30) NOT NULL DEFAULT 'diamond',
            delta_amount INT NOT NULL DEFAULT 0,
            balance_before INT NULL,
            balance_after INT NULL,
            reason VARCHAR(100) NULL,
            ref_type VARCHAR(80) NULL,
            ref_id VARCHAR(191) NULL,
            actor_id VARCHAR(32) NULL,
            data_json LONGTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_user_created (user_id, created_at),
            KEY idx_reason_created (reason, created_at),
            KEY idx_actor_created (actor_id, created_at),
            KEY idx_ref (ref_type, ref_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        "CREATE TABLE IF NOT EXISTS bot_cron_runs (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            task_name VARCHAR(80) NOT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'ok',
            result_json LONGTEXT NULL,
            started_ts BIGINT NOT NULL DEFAULT 0,
            finished_ts BIGINT NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_task_created (task_name, created_at),
            KEY idx_status_created (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    ];
    foreach ($schema as $sql) $pdo->exec($sql);
    if(function_exists('bot_sql_ensure_runtime_indexes')) bot_sql_ensure_runtime_indexes($pdo);
    $done = true;
}


function bot_sql_index_exists(PDO $pdo, $table, $index){
    try{
        $st = $pdo->prepare('SHOW INDEX FROM `'.$table.'` WHERE Key_name = ?');
        $st->execute([(string)$index]);
        return (bool)$st->fetch(PDO::FETCH_ASSOC);
    }catch(Throwable $e){ return true; }
}
function bot_sql_table_exists(PDO $pdo, $table){
    try{
        $st = $pdo->prepare('SHOW TABLES LIKE ?');
        $st->execute([(string)$table]);
        return (bool)$st->fetchColumn();
    }catch(Throwable $e){ return false; }
}
function bot_sql_ensure_index(PDO $pdo, $table, $index, $sql){
    if(!bot_sql_table_exists($pdo, $table)) return false;
    if(bot_sql_index_exists($pdo, $table, $index)) return false;
    try{ $pdo->exec($sql); return true; }
    catch(Throwable $e){ error_log('BOT SQL index failed '.$table.'.'.$index.': '.$e->getMessage()); return false; }
}
function bot_sql_ensure_runtime_indexes(PDO $pdo){
    $indexes = [
        ['bot_users','idx_users_updated','ALTER TABLE `bot_users` ADD INDEX `idx_users_updated` (`updated_at`)'],
        ['bot_users','idx_users_created','ALTER TABLE `bot_users` ADD INDEX `idx_users_created` (`created_at`)'],
        ['bot_users','idx_users_step_updated','ALTER TABLE `bot_users` ADD INDEX `idx_users_step_updated` (`step`, `updated_at`)'],
        ['bot_orders','idx_orders_status_created','ALTER TABLE `bot_orders` ADD INDEX `idx_orders_status_created` (`status`, `created_ts`)'],
        ['bot_orders','idx_orders_post','ALTER TABLE `bot_orders` ADD INDEX `idx_orders_post` (`post_chat_id`, `post_message_id`)'],
        ['bot_orders','idx_orders_channel_chat','ALTER TABLE `bot_orders` ADD INDEX `idx_orders_channel_chat` (`channel_chat_id`)'],
        ['bot_orders','idx_orders_updated','ALTER TABLE `bot_orders` ADD INDEX `idx_orders_updated` (`updated_at`)'],
        ['bot_order_members','idx_order_members_user_left','ALTER TABLE `bot_order_members` ADD INDEX `idx_order_members_user_left` (`user_id`, `left_ts`)'],
        ['bot_order_members','idx_order_members_order_penalized','ALTER TABLE `bot_order_members` ADD INDEX `idx_order_members_order_penalized` (`order_id`, `penalized`)'],
        ['bot_order_members','idx_order_members_joined','ALTER TABLE `bot_order_members` ADD INDEX `idx_order_members_joined` (`joined_ts`)'],
        ['bot_settings','idx_settings_updated','ALTER TABLE `bot_settings` ADD INDEX `idx_settings_updated` (`updated_at`)'],
        ['bot_gift_codes','idx_gift_status_created','ALTER TABLE `bot_gift_codes` ADD INDEX `idx_gift_status_created` (`status`, `created_at`)'],
        ['bot_wallet_ledger','idx_wallet_asset_created','ALTER TABLE `bot_wallet_ledger` ADD INDEX `idx_wallet_asset_created` (`asset_type`, `created_at`)'],
        ['bot_cron_runs','idx_cron_task_status','ALTER TABLE `bot_cron_runs` ADD INDEX `idx_cron_task_status` (`task_name`, `status`, `created_at`)'],
    ];
    $n = 0;
    foreach($indexes as $i){ if(bot_sql_ensure_index($pdo, $i[0], $i[1], $i[2])) $n++; }
    return $n;
}

function bot_json_encode($data){
    $json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
    return $json === false ? '{}' : $json;
}
function bot_json_decode_array($raw, $default = []){
    if ($raw === null || $raw === '') return $default;
    $data = json_decode((string)$raw, true);
    return (json_last_error() === JSON_ERROR_NONE && is_array($data)) ? $data : $default;
}
function bot_sql_scalar($array, $keys, $default = null){
    foreach ((array)$keys as $k) if (is_array($array) && array_key_exists($k, $array)) return $array[$k];
    return $default;
}
function bot_sql_count_array_clean($v){
    if (!is_array($v)) return 0;
    $n = 0;
    foreach ($v as $x) if ((string)$x !== '' && (string)$x !== '0') $n++;
    return $n;
}
function bot_sql_channel_key($value){
    $value = trim((string)$value);
    $value = preg_replace('~^(https?://)?(www\.)?(ble\.ir|bale\.ai|t\.me)/~i', '', $value);
    return trim($value, "@/ \t\n\r\0\x0B");
}

function bot_data_file_exists($path) {
    $path = bot_storage_normalize_path($path);
    $pdo = bot_sql_pdo();
    $meta = bot_storage_path_kind($path);
    if ($pdo && bot_storage_is_managed_path($path)) {
        try {
            if ($meta['kind'] === 'user') {
                $st = $pdo->prepare('SELECT 1 FROM bot_users WHERE user_id=? LIMIT 1'); $st->execute([$meta['id']]); return (bool)$st->fetchColumn();
            }
            if ($meta['kind'] === 'order') {
                $st = $pdo->prepare('SELECT 1 FROM bot_orders WHERE order_id=? LIMIT 1'); $st->execute([$meta['id']]); return (bool)$st->fetchColumn();
            }
            if ($meta['kind'] === 'spam') {
                $st = $pdo->prepare('SELECT 1 FROM bot_spam WHERE user_id=? LIMIT 1'); $st->execute([$meta['id']]); return (bool)$st->fetchColumn();
            }
            if ($meta['kind'] === 'channel_rewards') {
                $c = (int)$pdo->query('SELECT COUNT(*) FROM bot_channel_rewards')->fetchColumn();
                if ($c > 0) return true;
                $st = $pdo->prepare('SELECT 1 FROM bot_settings WHERE setting_key=? LIMIT 1'); $st->execute([$path]); return (bool)$st->fetchColumn();
            }
            $st = $pdo->prepare('SELECT 1 FROM bot_settings WHERE setting_key=? LIMIT 1'); $st->execute([$path]); return (bool)$st->fetchColumn();
        } catch (Throwable $e) { error_log('BOT SQL exists failed ['.$path.']: '.$e->getMessage()); }
    }
    return is_file($path);
}

function bot_data_get_raw($path, $default = '') {
    $path = bot_storage_normalize_path($path);
    $pdo = bot_sql_pdo();
    $meta = bot_storage_path_kind($path);
    if ($pdo && bot_storage_is_managed_path($path)) {
        try {
            if ($meta['kind'] === 'user') {
                $st = $pdo->prepare('SELECT data_json FROM bot_users WHERE user_id=? LIMIT 1');
                $st->execute([$meta['id']]); $row = $st->fetch();
                return $row ? (string)$row['data_json'] : (string)$default;
            }
            if ($meta['kind'] === 'order') {
                $st = $pdo->prepare('SELECT data_json FROM bot_orders WHERE order_id=? LIMIT 1');
                $st->execute([$meta['id']]); $row = $st->fetch();
                return $row ? (string)$row['data_json'] : (string)$default;
            }
            if ($meta['kind'] === 'spam') {
                $st = $pdo->prepare('SELECT data_json FROM bot_spam WHERE user_id=? LIMIT 1');
                $st->execute([$meta['id']]); $row = $st->fetch();
                return $row ? (string)$row['data_json'] : (string)$default;
            }
            if ($meta['kind'] === 'channel_rewards') {
                return bot_json_encode(bot_sql_get_channel_rewards($default));
            }
            $st = $pdo->prepare('SELECT storage_value FROM bot_settings WHERE setting_key=? LIMIT 1');
            $st->execute([$path]); $row = $st->fetch();
            return $row ? (string)$row['storage_value'] : (string)$default;
        } catch (Throwable $e) { error_log('BOT SQL read failed ['.$path.']: '.$e->getMessage()); }
    }
    return is_file($path) ? (string)@file_get_contents($path) : (string)$default;
}

function bot_data_get_json($path, $default = []) { return bot_json_decode_array(bot_data_get_raw($path, ''), $default); }
function bot_data_get_text($path, $default = '') { return bot_data_get_raw($path, $default); }

function bot_data_put_raw($path, $value, $type = 'raw', $skipMirror = false) {
    $path = bot_storage_normalize_path($path);
    $value = (string)$value;
    $pdo = bot_sql_pdo();
    $meta = bot_storage_path_kind($path);
    if ($pdo && bot_storage_is_managed_path($path)) {
        try {
            if ($meta['kind'] === 'user') bot_sql_put_user($meta['id'], bot_json_decode_array($value, []), $value);
            elseif ($meta['kind'] === 'order') bot_sql_put_order($meta['id'], bot_json_decode_array($value, []), $value);
            elseif ($meta['kind'] === 'spam') bot_sql_put_spam($meta['id'], $value);
            elseif ($meta['kind'] === 'channel_rewards') bot_sql_put_channel_rewards(bot_json_decode_array($value, []), $value);
            else bot_sql_put_setting($path, $value, $type);
        } catch (Throwable $e) { error_log('BOT SQL write failed ['.$path.']: '.$e->getMessage()); }
    }

    // Compatibility mirror for button/keyboard settings:
    // parts of the original source still read these paths directly with file_get_contents()/file_exists().
    // Therefore button renames must be written to both SQL and the legacy files.
    $forceMirror = (strpos($path, 'lib/Button/') === 0)
        || ($path === 'lib/keyboard/channelkey.txt')
        || (strpos($path, 'lib/keyboard/home/') === 0);

    if ($forceMirror || !defined('BOT_SQL_MIRROR_FILES') || BOT_SQL_MIRROR_FILES || !$pdo || !bot_storage_is_managed_path($path)) {
        $dir = dirname($path); if ($dir && $dir !== '.' && !is_dir($dir)) @mkdir($dir, 0775, true);
        @file_put_contents($path, $value);
    }
    // Any managed write must invalidate derived lists/dashboards immediately.
    // This fixes newly created orders not appearing in Tracking until an old cache expires.
    if(function_exists('bot_cache_invalidate_for_path')) bot_cache_invalidate_for_path($path);
    return true;
}
function bot_data_put_json($path, $data) { return bot_data_put_raw($path, bot_json_encode($data), 'json'); }
function bot_data_put_text($path, $text) { return bot_data_put_raw($path, (string)$text, 'text'); }

function bot_sql_put_user($userId, $data, $rawJson = null){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    if ($rawJson === null) $rawJson = bot_json_encode($data);
    $points = (int)bot_sql_scalar($data, ['Points','points','coin','money'], 0);
    $step = (string)bot_sql_scalar($data, ['step'], 'none');
    $username = (string)bot_sql_scalar($data, ['username','user_name'], '');
    $firstName = (string)bot_sql_scalar($data, ['first_name','name'], '');
    $ref = (string)bot_sql_scalar($data, ['referrer_id','sub','ref','invite','inviter','zir'], '');
    $st = $pdo->prepare('INSERT INTO bot_users (user_id, username, first_name, step, points, referrer_id, data_json) VALUES (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE username=VALUES(username), first_name=VALUES(first_name), step=VALUES(step), points=VALUES(points), referrer_id=VALUES(referrer_id), data_json=VALUES(data_json), updated_at=CURRENT_TIMESTAMP');
    $st->execute([(string)$userId, $username ?: null, $firstName ?: null, $step ?: null, $points, $ref ?: null, $rawJson]);
}

function bot_sql_put_order($orderId, $data, $rawJson = null){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    if ($rawJson === null) $rawJson = bot_json_encode($data);
    $owner = (string)bot_sql_scalar($data, ['admin','owner_id','user_id'], '');
    $channel = bot_sql_channel_key(bot_sql_scalar($data, ['channel','channel_username'], ''));
    $channelId = (string)bot_sql_scalar($data, ['channel_id','chat_id'], '');
    $target = (int)bot_sql_scalar($data, ['member','target_count'], 0);
    $reward = (int)bot_sql_scalar($data, ['coin','reward','reward_per_member'], 0);
    $received = (int)bot_sql_scalar($data, ['tedad','received_count'], bot_sql_count_array_clean($data['members'] ?? []));
    $left = bot_sql_count_array_clean($data['left'] ?? []);
    $status = (string)bot_sql_scalar($data, ['status','stats'], '');
    $postChat = (string)bot_sql_scalar($data, ['post_chat_id','chat_id'], '');
    $postMsg = (string)bot_sql_scalar($data, ['post_message_id','message_id','msg_id','postid'], '');
    $createdTs = (int)bot_sql_scalar($data, ['stamp','created_ts','timeunix'], 0);
    $st = $pdo->prepare('INSERT INTO bot_orders (order_id, owner_id, channel_username, channel_chat_id, target_count, reward_per_member, received_count, left_count, status, post_chat_id, post_message_id, created_ts, data_json) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE owner_id=VALUES(owner_id), channel_username=VALUES(channel_username), channel_chat_id=VALUES(channel_chat_id), target_count=VALUES(target_count), reward_per_member=VALUES(reward_per_member), received_count=VALUES(received_count), left_count=VALUES(left_count), status=VALUES(status), post_chat_id=VALUES(post_chat_id), post_message_id=VALUES(post_message_id), created_ts=VALUES(created_ts), data_json=VALUES(data_json), updated_at=CURRENT_TIMESTAMP');
    $st->execute([(string)$orderId, $owner ?: null, $channel ?: null, $channelId ?: null, $target, $reward, $received, $left, $status ?: null, $postChat ?: null, $postMsg ?: null, $createdTs ?: null, $rawJson]);
    bot_sql_sync_order_members((string)$orderId, $data);
}

function bot_sql_sync_order_members($orderId, $data){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    $members = is_array($data['members'] ?? null) ? $data['members'] : [];
    $leftList = is_array($data['left'] ?? null) ? array_map('strval', $data['left']) : [];
    $leftLog = is_array($data['left_log'] ?? null) ? $data['left_log'] : [];
    $joinedLog = is_array($data['joined_log'] ?? null) ? $data['joined_log'] : [];
    $pdo->prepare('DELETE FROM bot_order_members WHERE order_id=?')->execute([$orderId]);
    if (!$members) return;
    $st = $pdo->prepare('INSERT INTO bot_order_members (order_id, user_id, joined_ts, left_ts, penalized) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE joined_ts=VALUES(joined_ts), left_ts=VALUES(left_ts), penalized=VALUES(penalized)');
    foreach ($members as $userId) {
        $userId = (string)$userId; if ($userId === '' || $userId === '0') continue;
        $isLeft = in_array($userId, $leftList, true);
        $leftTs = null;
        if ($isLeft && isset($leftLog[$userId]['time'])) $leftTs = (int)$leftLog[$userId]['time'];
        $joinedTs = isset($joinedLog[$userId]) ? (int)$joinedLog[$userId] : null;
        $st->execute([$orderId, $userId, $joinedTs ?: null, $leftTs, $isLeft ? 1 : 0]);
    }
}

function bot_sql_put_spam($userId, $rawJson){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    $st = $pdo->prepare('INSERT INTO bot_spam (user_id, data_json) VALUES (?,?) ON DUPLICATE KEY UPDATE data_json=VALUES(data_json), updated_at=CURRENT_TIMESTAMP');
    $st->execute([(string)$userId, $rawJson]);
}
function bot_sql_put_setting($key, $value, $type='raw'){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    $st = $pdo->prepare('INSERT INTO bot_settings (setting_key, storage_type, storage_value) VALUES (?,?,?) ON DUPLICATE KEY UPDATE storage_type=VALUES(storage_type), storage_value=VALUES(storage_value), updated_at=CURRENT_TIMESTAMP');
    $st->execute([$key, $type, (string)$value]);
}

function bot_sql_get_channel_rewards($default = []){
    $pdo = bot_sql_pdo(); if (!$pdo) return is_array($default) ? $default : [];
    $rows = [];
    try {
        $st = $pdo->query('SELECT user_id, channel_key, last_order_id, channel_chat_id, last_reward_ts, data_json FROM bot_channel_rewards');
        foreach ($st as $r) {
            $entry = bot_json_decode_array($r['data_json'] ?? '', []);
            if (!$entry) $entry = ['order_id'=>$r['last_order_id'], 'channel'=>$r['channel_key'], 'channel_id'=>$r['channel_chat_id'], 'time'=>(int)$r['last_reward_ts']];
            if (!isset($rows[$r['channel_key']]) || !is_array($rows[$r['channel_key']])) $rows[$r['channel_key']] = [];
            $rows[$r['channel_key']][(string)$r['user_id']] = $entry;
        }
        if ($rows) return $rows;
        $st = $pdo->prepare('SELECT storage_value FROM bot_settings WHERE setting_key=? LIMIT 1');
        $st->execute(['lib/kodam/channel_reward_users.json']);
        $raw = $st->fetchColumn();
        return bot_json_decode_array($raw, is_array($default) ? $default : []);
    } catch (Throwable $e) { error_log('BOT SQL rewards read failed: '.$e->getMessage()); }
    return is_array($default) ? $default : [];
}
function bot_sql_put_channel_rewards($data, $rawJson = null){
    $pdo = bot_sql_pdo(); if (!$pdo) return;
    if ($rawJson === null) $rawJson = bot_json_encode($data);
    bot_sql_put_setting('lib/kodam/channel_reward_users.json', $rawJson, 'json');
    $pdo->exec('DELETE FROM bot_channel_rewards');
    if (!is_array($data)) return;
    $st = $pdo->prepare('INSERT INTO bot_channel_rewards (user_id, channel_key, last_order_id, channel_chat_id, last_reward_ts, data_json) VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE last_order_id=VALUES(last_order_id), channel_chat_id=VALUES(channel_chat_id), last_reward_ts=VALUES(last_reward_ts), data_json=VALUES(data_json), updated_at=CURRENT_TIMESTAMP');
    foreach ($data as $channelKey=>$users) {
        if (!is_array($users)) continue;
        $channelKey = bot_sql_channel_key($channelKey);
        foreach ($users as $userId=>$entry) {
            if (!is_array($entry)) $entry = [];
            $st->execute([(string)$userId, $channelKey, (string)($entry['order_id'] ?? ''), (string)($entry['channel_id'] ?? ''), isset($entry['time']) ? (int)$entry['time'] : null, bot_json_encode($entry)]);
        }
    }
}

function bot_data_ensure($path, $default) {
    $path = bot_storage_normalize_path($path);
    if (bot_data_file_exists($path)) return true;
    $trim = trim((string)$default);
    if ($trim !== '' && ($trim[0] === '{' || $trim[0] === '[')) return bot_data_put_raw($path, (string)$default, 'json');
    return bot_data_put_raw($path, (string)$default, 'text');
}

function bot_data_scandir($dir){
    $dirNorm = bot_storage_normalize_path($dir);
    $pdo = bot_sql_pdo();
    if ($pdo && ($dirNorm === 'melat' || $dirNorm === 'ads')) {
        $cacheKey = 'scandir_' . $dirNorm;
        if(function_exists('bot_scale_cache_get')){
            $cached = bot_scale_cache_get('storage_lists', $cacheKey, $dirNorm === 'melat' ? 300 : 45);
            if(is_array($cached)) return array_merge(['.','..'], $cached);
        }
        try {
            if($dirNorm === 'melat'){
                $ids = $pdo->query('SELECT user_id FROM bot_users ORDER BY user_id ASC')->fetchAll(PDO::FETCH_COLUMN);
                $names = array_map(function($id){ return $id.'.json'; }, $ids ?: []);
            } else {
                $ids = $pdo->query('SELECT order_id FROM bot_orders ORDER BY created_ts DESC, order_id DESC')->fetchAll(PDO::FETCH_COLUMN);
                $names = array_map(function($id){ return $id.'.json'; }, $ids ?: []);
            }
            if(function_exists('bot_scale_cache_set')) bot_scale_cache_set('storage_lists', $cacheKey, $names);
            return array_merge(['.','..'], $names);
        } catch(Throwable $e){ error_log('BOT SQL list '.$dirNorm.' failed: '.$e->getMessage()); }
    }
    return is_dir($dir) ? scandir($dir) : ['.','..'];
}
function bot_data_glob($pattern){
    $orig = (string)$pattern;
    $norm = bot_storage_normalize_path($orig);
    $abs = (strlen($orig) > 0 && $orig[0] === '/');
    $root = rtrim(bot_storage_root(), '/');
    $pdo = bot_sql_pdo();
    if ($pdo && $norm === 'melat/*.json') {
        $names = bot_data_scandir('melat'); $out=[];
        foreach ($names as $n) if ($n !== '.' && $n !== '..') $out[] = ($abs ? $root.'/' : '').'melat/'.$n;
        return $out;
    }
    if ($pdo && $norm === 'ads/*.json') {
        $names = bot_data_scandir('ads'); $out=[];
        foreach ($names as $n) if ($n !== '.' && $n !== '..') $out[] = ($abs ? $root.'/' : '').'ads/'.$n;
        return $out;
    }
    $g = glob($pattern);
    return is_array($g) ? $g : [];
}

function bot_storage_status() {
    $pdo = bot_sql_pdo();
    $out = ['sql_enabled'=>defined('BOT_SQL_ENABLED') && BOT_SQL_ENABLED, 'sql_connected'=>(bool)$pdo, 'mirror_files'=>defined('BOT_SQL_MIRROR_FILES') && BOT_SQL_MIRROR_FILES];
    if ($pdo) {
        foreach (['bot_users','bot_orders','bot_order_members','bot_settings','bot_channel_rewards','bot_spam','bot_order_reports','bot_order_moderation_actions','bot_anti_spam','bot_order_cancel_actions','bot_referrals','bot_referral_events','bot_daily_rewards','bot_daily_reward_events','bot_daily_notify_jobs','bot_gift_codes','bot_gift_code_events'] as $t) {
            try { $out[$t] = (int)$pdo->query('SELECT COUNT(*) FROM '.$t)->fetchColumn(); } catch (Throwable $e) { $out[$t.'_error'] = $e->getMessage(); }
        }
    }
    return $out;
}


// Atomic wallet helpers for SQL mode. These keep bot_users.points and data_json['Points'] in sync.
function bot_wallet_normalize_amount($value){
    if(function_exists('bot_normalize_number_digits')) $value = bot_normalize_number_digits($value);
    $value = preg_replace('/[^0-9\-]/', '', (string)$value);
    return (int)$value;
}
function bot_wallet_default_user($userId){
    return [
        'id' => (string)$userId,
        'step' => 'none',
        'Points' => 0,
        'ads' => 0,
        'ENTEQALAT' => [],
        'daryafti' => [],
    ];
}
function bot_wallet_get_user_for_update(PDO $pdo, $userId){
    $st = $pdo->prepare('SELECT data_json, points FROM bot_users WHERE user_id=? FOR UPDATE');
    $st->execute([(string)$userId]);
    $row = $st->fetch();
    if(!$row){
        $data = bot_wallet_default_user($userId);
        $json = bot_json_encode($data);
        $ins = $pdo->prepare('INSERT INTO bot_users (user_id, step, points, data_json) VALUES (?,?,?,?)');
        $ins->execute([(string)$userId, 'none', 0, $json]);
        return $data;
    }
    $data = bot_json_decode_array($row['data_json'] ?? '', []);
    if(!is_array($data)) $data = [];
    if(!array_key_exists('Points', $data)) $data['Points'] = (int)($row['points'] ?? 0);
    return $data;
}
function bot_wallet_save_user(PDO $pdo, $userId, array $data){
    $points = max(0, (int)($data['Points'] ?? 0));
    $data['Points'] = $points;
    if(!isset($data['step'])) $data['step'] = 'none';
    $json = bot_json_encode($data);
    $st = $pdo->prepare('UPDATE bot_users SET points=?, step=?, data_json=?, updated_at=CURRENT_TIMESTAMP WHERE user_id=?');
    $st->execute([$points, (string)($data['step'] ?? 'none'), $json, (string)$userId]);
    if($st->rowCount() === 0){
        $ins = $pdo->prepare('INSERT INTO bot_users (user_id, step, points, data_json) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE step=VALUES(step), points=VALUES(points), data_json=VALUES(data_json), updated_at=CURRENT_TIMESTAMP');
        $ins->execute([(string)$userId, (string)($data['step'] ?? 'none'), $points, $json]);
    }
    return $data;
}
function bot_wallet_add($userId, $delta, $reason = '', array $extra = []){
    $delta = (int)$delta;
    $pdo = bot_sql_pdo();
    if(!$pdo){
        $data = bot_data_get_json("melat/$userId.json");
        if(!is_array($data)) $data = bot_wallet_default_user($userId);
        $before = (int)($data['Points'] ?? 0);
        $after = max(0, $before + $delta);
        $data['Points'] = $after;
        if($reason !== '') $data['wallet_log'][] = ['time'=>time(), 'reason'=>$reason, 'delta'=>$delta, 'before'=>$before, 'after'=>$after] + $extra;
        bot_data_put_json("melat/$userId.json", $data);
        if(function_exists('bot_ledger_record')) bot_ledger_record($userId, 'diamond', $delta, $before, $after, $reason, $extra);
        return ['ok'=>true, 'before'=>$before, 'after'=>$after, 'delta'=>$delta, 'data'=>$data, 'fallback'=>true];
    }
    try{
        $pdo->beginTransaction();
        $data = bot_wallet_get_user_for_update($pdo, $userId);
        $before = (int)($data['Points'] ?? 0);
        $after = max(0, $before + $delta);
        $data['Points'] = $after;
        if($reason !== '') $data['wallet_log'][] = ['time'=>time(), 'reason'=>$reason, 'delta'=>$delta, 'before'=>$before, 'after'=>$after] + $extra;
        bot_wallet_save_user($pdo, $userId, $data);
        $pdo->commit();
        if(function_exists('bot_ledger_record')) bot_ledger_record($userId, 'diamond', $delta, $before, $after, $reason, $extra);
        if(function_exists('bot_cache_invalidate_for_path')) bot_cache_invalidate_for_path("melat/$userId.json");
        return ['ok'=>true, 'before'=>$before, 'after'=>$after, 'delta'=>$delta, 'data'=>$data];
    }catch(Throwable $e){
        if($pdo->inTransaction()) $pdo->rollBack();
        error_log('BOT wallet add failed: '.$e->getMessage());
        return ['ok'=>false, 'error'=>$e->getMessage()];
    }
}
function bot_wallet_transfer($fromUserId, $toUserId, $amount, $min=1, $max=100000000, $dateText=''){
    $amount = (int)$amount; $min=(int)$min; $max=(int)$max;
    if((string)$fromUserId === (string)$toUserId) return ['ok'=>false, 'error'=>'same_user'];
    if($amount <= 0 || $amount < $min || $amount > $max) return ['ok'=>false, 'error'=>'amount_out_of_range'];
    $pdo = bot_sql_pdo();
    if(!$pdo){
        $from = bot_data_get_json("melat/$fromUserId.json");
        $to = bot_data_get_json("melat/$toUserId.json");
        if(!is_array($to) || !$to) return ['ok'=>false, 'error'=>'target_not_found'];
        $beforeFrom = (int)($from['Points'] ?? 0); $beforeTo = (int)($to['Points'] ?? 0);
        if($beforeFrom < $amount) return ['ok'=>false, 'error'=>'not_enough_points', 'from_before'=>$beforeFrom];
        $from['Points'] = $beforeFrom - $amount; $to['Points'] = $beforeTo + $amount;
        $from['enteghal_to'] = (int)($from['enteghal_to'] ?? 0) + $amount;
        $to['enteghal_as'] = (int)($to['enteghal_as'] ?? 0) + $amount;
        $from['ENTEQALAT'][] = "$toUserId|$dateText|$amount";
        $to['daryafti'][] = "$fromUserId|$dateText|$amount";
        $from['step'] = 'none';
        bot_data_put_json("melat/$fromUserId.json", $from); bot_data_put_json("melat/$toUserId.json", $to);
        return ['ok'=>true,'from_before'=>$beforeFrom,'from_after'=>$from['Points'],'to_before'=>$beforeTo,'to_after'=>$to['Points']];
    }
    try{
        $pdo->beginTransaction();
        $from = bot_wallet_get_user_for_update($pdo, $fromUserId);
        $to = bot_wallet_get_user_for_update($pdo, $toUserId);
        // If the target was just auto-created but never actually used the bot, block transfer.
        $exists = bot_data_file_exists("melat/$toUserId.json");
        $beforeFrom = (int)($from['Points'] ?? 0); $beforeTo = (int)($to['Points'] ?? 0);
        if(!$exists && $beforeTo === 0 && empty($to['username']) && empty($to['first_name'])){ throw new RuntimeException('target_not_found'); }
        if($beforeFrom < $amount){ throw new RuntimeException('not_enough_points'); }
        $from['Points'] = $beforeFrom - $amount; $to['Points'] = $beforeTo + $amount;
        $from['enteghal_to'] = (int)($from['enteghal_to'] ?? 0) + $amount;
        $to['enteghal_as'] = (int)($to['enteghal_as'] ?? 0) + $amount;
        if(!isset($from['ENTEQALAT']) || !is_array($from['ENTEQALAT'])) $from['ENTEQALAT'] = [];
        if(!isset($to['daryafti']) || !is_array($to['daryafti'])) $to['daryafti'] = [];
        $from['ENTEQALAT'][] = "$toUserId|$dateText|$amount";
        $to['daryafti'][] = "$fromUserId|$dateText|$amount";
        $from['step'] = 'none';
        bot_wallet_save_user($pdo, $fromUserId, $from);
        bot_wallet_save_user($pdo, $toUserId, $to);
        $pdo->commit();
        if(function_exists('bot_cache_invalidate_for_path')){ bot_cache_invalidate_for_path("melat/$fromUserId.json"); bot_cache_invalidate_for_path("melat/$toUserId.json"); }
        return ['ok'=>true,'from_before'=>$beforeFrom,'from_after'=>$from['Points'],'to_before'=>$beforeTo,'to_after'=>$to['Points']];
    }catch(Throwable $e){
        if($pdo->inTransaction()) $pdo->rollBack();
        return ['ok'=>false, 'error'=>$e->getMessage()];
    }
}

function bot_payment_install_schema(){
    $pdo = bot_sql_pdo(); if(!$pdo) return false;
    $pdo->exec("CREATE TABLE IF NOT EXISTS bot_payments (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(32) NOT NULL,
        authority VARCHAR(128) NULL,
        ref_id VARCHAR(128) NULL,
        type VARCHAR(50) NOT NULL,
        amount INT NOT NULL DEFAULT 0,
        reward_amount INT NOT NULL DEFAULT 0,
        panel_type VARCHAR(50) NULL,
        panel_days INT NOT NULL DEFAULT 0,
        status VARCHAR(30) NOT NULL DEFAULT 'created',
        data_json LONGTEXT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_authority (authority),
        KEY idx_user (user_id),
        KEY idx_status (status),
        KEY idx_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    return true;
}
function bot_payment_create($userId, $type, $amount, $rewardAmount, array $data=[]){
    $pdo = bot_sql_pdo(); if(!$pdo) return 0; bot_payment_install_schema();
    $st = $pdo->prepare('INSERT INTO bot_payments (user_id, type, amount, reward_amount, panel_type, panel_days, status, data_json) VALUES (?,?,?,?,?,?,?,?)');
    $st->execute([(string)$userId, (string)$type, (int)$amount, (int)$rewardAmount, (string)($data['panel_type'] ?? ''), (int)($data['panel_days'] ?? 0), 'created', bot_json_encode($data)]);
    return (int)$pdo->lastInsertId();
}
function bot_payment_set_authority($paymentId, $authority, array $data=[]){
    $pdo = bot_sql_pdo(); if(!$pdo) return false; bot_payment_install_schema();
    $st = $pdo->prepare('UPDATE bot_payments SET authority=?, status=?, data_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=?');
    return $st->execute([(string)$authority, 'pending', bot_json_encode($data), (int)$paymentId]);
}
function bot_payment_get_by_authority($authority){
    $pdo = bot_sql_pdo(); if(!$pdo) return null; bot_payment_install_schema();
    $st = $pdo->prepare('SELECT * FROM bot_payments WHERE authority=? LIMIT 1');
    $st->execute([(string)$authority]);
    $row = $st->fetch();
    return $row ?: null;
}
function bot_payment_mark_paid($paymentId, $refId, array $data=[]){
    $pdo = bot_sql_pdo(); if(!$pdo) return false; bot_payment_install_schema();
    $st = $pdo->prepare('UPDATE bot_payments SET ref_id=?, status=?, data_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=? AND status<>?');
    return $st->execute([(string)$refId, 'paid', bot_json_encode($data), (int)$paymentId, 'paid']);
}
function bot_payment_mark_failed($paymentId, $status, array $data=[]){
    $pdo = bot_sql_pdo(); if(!$pdo) return false; bot_payment_install_schema();
    $st = $pdo->prepare('UPDATE bot_payments SET status=?, data_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=? AND status<>?');
    return $st->execute([(string)$status, bot_json_encode($data), (int)$paymentId, 'paid']);
}
