<?php
function vpnColumnExists(PDO $pdo, string $table, string $column): bool {
    try { $st=$pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?"); $st->execute([$column]); return (bool)$st->fetch(PDO::FETCH_ASSOC); } catch(Throwable $e){ return false; }
}
function vpnIndexExists(PDO $pdo, string $table, string $index): bool {
    try { $st=$pdo->prepare("SHOW INDEX FROM `{$table}` WHERE Key_name=?"); $st->execute([$index]); return (bool)$st->fetch(PDO::FETCH_ASSOC); } catch(Throwable $e){ return false; }
}
function vpnAddColumnIfMissing(PDO $pdo, string $table, string $column, string $definition): void {
    try { if (!vpnColumnExists($pdo,$table,$column)) $pdo->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}"); } catch(Throwable $e){}
}
function vpnAddIndexIfMissing(PDO $pdo, string $table, string $index, string $sql): void {
    try { if (!vpnIndexExists($pdo,$table,$index)) $pdo->exec($sql); } catch(Throwable $e){}
}
function vpnEnsureColumns(PDO $pdo): void {
    try { $pdo->exec("ALTER TABLE orders MODIFY duration VARCHAR(20) NOT NULL DEFAULT '1M'"); } catch(Throwable $e){}
    vpnAddColumnIfMissing($pdo, 'orders', 'requested_seats', "INT NOT NULL DEFAULT 1 AFTER `duration`");
    try { $pdo->exec("ALTER TABLE credentials MODIFY duration VARCHAR(20) NOT NULL DEFAULT '1M'"); } catch(Throwable $e){}
    try { $pdo->exec("ALTER TABLE credentials MODIFY sellable_until DATETIME NULL"); } catch(Throwable $e){}
    vpnAddColumnIfMissing($pdo, 'credentials', 'category_id', "INT NULL AFTER `id`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'assigned_user_id', "BIGINT NULL AFTER `used_seats`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'assigned_order_id', "INT NULL AFTER `assigned_user_id`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'first_connected_at', "TIMESTAMP NULL AFTER `assigned_order_id`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'expires_at', "TIMESTAMP NULL AFTER `first_connected_at`");
    vpnAddColumnIfMissing($pdo, 'plan_categories', 'traffic_type', "VARCHAR(20) NOT NULL DEFAULT 'unlimited' AFTER `months`");
    vpnAddColumnIfMissing($pdo, 'plan_categories', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'plans', 'traffic_type', "VARCHAR(20) NOT NULL DEFAULT 'unlimited' AFTER `protocol`");
    vpnAddColumnIfMissing($pdo, 'plans', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'protocol', "VARCHAR(30) NOT NULL DEFAULT 'openvpn_l2tp' AFTER `plan_id`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'config_text', "MEDIUMTEXT NULL AFTER `password`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'qr_code_file_id', "VARCHAR(255) NULL AFTER `config_text`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'qr_code_path', "VARCHAR(500) NULL AFTER `qr_code_file_id`");
    vpnAddIndexIfMissing($pdo, 'credentials', 'idx_credentials_protocol_capacity', "ALTER TABLE credentials ADD INDEX idx_credentials_protocol_capacity (protocol, duration, is_active, used_seats)");
    vpnAddColumnIfMissing($pdo, 'plan_categories', 'service_type', "VARCHAR(30) NOT NULL DEFAULT 'openvpn_l2tp' AFTER `code`");
    vpnAddColumnIfMissing($pdo, 'orders', 'service_type', "VARCHAR(30) NOT NULL DEFAULT 'openvpn_l2tp' AFTER `plan_title`");
    vpnAddColumnIfMissing($pdo, 'orders', 'traffic_type', "VARCHAR(20) NOT NULL DEFAULT 'unlimited' AFTER `requested_seats`");
    vpnAddColumnIfMissing($pdo, 'orders', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'username', "VARCHAR(128) NULL AFTER `vpn_account_id`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'password', "VARCHAR(255) NULL AFTER `username`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'config_text', "MEDIUMTEXT NULL AFTER `password`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'qr_code_file_id', "VARCHAR(255) NULL AFTER `config_text`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'qr_code_path', "VARCHAR(500) NULL AFTER `qr_code_file_id`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'service_type', "VARCHAR(30) NOT NULL DEFAULT 'openvpn_l2tp' AFTER `qr_code_path`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'traffic_type', "VARCHAR(20) NOT NULL DEFAULT 'unlimited' AFTER `service_type`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'status', "VARCHAR(30) NOT NULL DEFAULT 'delivered' AFTER `seats_allocated`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'notification_status', "VARCHAR(20) NOT NULL DEFAULT 'pending' AFTER `status`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'notification_sent_at', "TIMESTAMP NULL AFTER `notification_status`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'notification_error', "TEXT NULL AFTER `notification_sent_at`");
    vpnAddColumnIfMissing($pdo, 'deliveries', 'notification_attempts', "INT NOT NULL DEFAULT 0 AFTER `notification_error`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'requested_seats', "INT NOT NULL DEFAULT 1");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'plan_id', "INT NULL AFTER `user_id`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'category_id', "INT NULL AFTER `plan_id`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'traffic_type', "VARCHAR(20) NOT NULL DEFAULT 'unlimited' AFTER `requested_seats`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'service_type', "VARCHAR(30) NOT NULL DEFAULT 'openvpn_l2tp' AFTER `duration`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'traffic_limit_gb', "INT UNSIGNED NULL AFTER `traffic_type`");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'processed_at', "TIMESTAMP NULL");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'status', "VARCHAR(30) NOT NULL DEFAULT 'pending'");
    vpnAddColumnIfMissing($pdo, 'waiting_queue', 'updated_at', "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    try { $pdo->exec("ALTER TABLE waiting_queue MODIFY duration VARCHAR(20) NOT NULL DEFAULT '1M'"); } catch(Throwable $e){}
    vpnAddColumnIfMissing($pdo, 'credentials', 'sales_paused', "TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`");
    vpnAddColumnIfMissing($pdo, 'credentials', 'archived_at', "DATETIME NULL");
    vpnAddColumnIfMissing($pdo, 'credentials', 'archived_by', "BIGINT NULL");
    vpnAddColumnIfMissing($pdo, 'credentials', 'archive_reason', "VARCHAR(255) NULL");
    vpnAddIndexIfMissing($pdo, 'credentials', 'idx_cred_available', "CREATE INDEX idx_cred_available ON credentials(duration,is_active,used_seats,created_at)");
    vpnAddIndexIfMissing($pdo, 'waiting_queue', 'idx_waiting_queue_open', "CREATE INDEX idx_waiting_queue_open ON waiting_queue(duration,processed_at,id)");
}

function vpnOrderRequestedSeats(array $order): int {
    return max(1, (int)($order['requested_seats'] ?? $order['seats'] ?? 1));
}


function gemaccPaymentToleranceToman(array $planOrOrder): int {
    $traffic = strtolower((string)($planOrOrder['traffic_type'] ?? $planOrOrder['category_traffic_type'] ?? 'unlimited'));
    $key = $traffic === 'limited' ? 'volume_payment_tolerance_toman' : 'default_payment_tolerance_toman';
    $fallback = $traffic === 'limited' ? 30000 : 50000;
    $value = (int)getSetting($key, $fallback);
    return max(0, min(5000000, $value));
}

function vpnLogEvent(string $event, array $context = []): void {
    $dir = dirname(__DIR__, 3) . '/storage/logs';
    if (!is_dir($dir)) @mkdir($dir, 0755, true);
    $safe = $context;
    foreach (['password','api_key','token','BOT_TOKEN'] as $k) {
        if (isset($safe[$k])) $safe[$k] = '***';
    }
    @file_put_contents($dir . '/vpn_delivery.log', json_encode([
        'time' => date('c'),
        'event' => $event,
        'context' => $safe,
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . "\n", FILE_APPEND);
}

function vpnTelegramSendMessage(int $userId, string $text, array $replyMarkup = [], array $extra = []): array {
    if (!defined('BOT_TOKEN') || trim((string)BOT_TOKEN) === '') {
        $result = [
            'telegram_sent' => false,
            'telegram_error_code' => 'BOT_TOKEN_MISSING',
            'telegram_error_message' => 'BOT_TOKEN is missing from environment configuration',
            'user_id' => $userId,
            'response' => null,
        ];
        vpnLogEvent('telegram_delivery_failed', $result);
        return $result;
    }
    $payload = ['chat_id'=>$userId, 'text'=>$text, 'parse_mode'=>'HTML'];
    if ($replyMarkup) $payload['reply_markup'] = json_encode($replyMarkup, JSON_UNESCAPED_UNICODE);
    foreach ($extra as $k=>$v) {
        if ($v !== null) $payload[$k] = $v;
    }
    try {
        $res = function_exists('tgSendSafe') ? tgSendSafe('sendMessage', $payload) : tg('sendMessage', $payload);
        $ok = is_array($res) && !empty($res['ok']);
        $errorCode = is_array($res) ? ($res['error_code'] ?? null) : null;
        $errorMessage = is_array($res) ? ($res['description'] ?? null) : 'Telegram request returned no structured response';
        $result = [
            'telegram_sent' => $ok,
            'telegram_error_code' => $ok ? null : $errorCode,
            'telegram_error_message' => $ok ? null : $errorMessage,
            'user_id' => $userId,
            'response' => $res,
        ];
        if (!$ok) vpnLogEvent('telegram_delivery_failed', $result);
        return $result;
    } catch (Throwable $e) {
        $result = [
            'telegram_sent' => false,
            'telegram_error_code' => null,
            'telegram_error_message' => $e->getMessage(),
            'user_id' => $userId,
            'response' => null,
        ];
        vpnLogEvent('telegram_delivery_exception', $result);
        return $result;
    }
}


function vpnServiceType(array $row): string {
    $v = strtolower(trim((string)($row['service_type'] ?? $row['protocol'] ?? $row['plan_protocol'] ?? $row['category_service_type'] ?? '')));
    $code = strtolower((string)($row['plan_code'] ?? $row['code'] ?? $row['category_code'] ?? $row['duration'] ?? ''));
    $title = strtolower((string)($row['plan_title'] ?? $row['title'] ?? $row['category_name'] ?? $row['service_name'] ?? ''));
    if ($v === 'windscribe' || str_contains($code, 'windscribe') || str_starts_with($code, 'ws_') || str_contains($title, 'windscribe') || str_contains($title, 'ویندسکرایب')) return 'windscribe';
    if ($v === 'expressvpn' || str_contains($code, 'express') || str_starts_with($code, 'exp_') || str_contains($title, 'express') || str_contains($title, 'اکسپرس')) return 'expressvpn';
    if ($v === 'v2ray' || str_contains($code, 'v2ray') || str_starts_with($code, 'v2_') || str_contains($title, 'v2ray') || str_contains($title, 'vmess') || str_contains($title, 'vless') || str_contains($title, 'ویتوری') || str_contains($title, 'وی تو ری')) return 'v2ray';
    return 'openvpn_l2tp';
}
function vpnServiceTitle(array $row): string { $t = vpnServiceType($row); return $t==='windscribe' ? 'Windscribe' : ($t==='expressvpn' ? 'ExpressVPN' : ($t==='v2ray' ? 'V2Ray' : 'OpenVPN | L2TP')); }
function vpnServiceEmoji(array $row): string { $t = vpnServiceType($row); return $t==='windscribe' ? '🟦' : ($t==='expressvpn' ? '🚀' : ($t==='v2ray' ? '⚡' : '🧊')); }

function vpnDeliveryKeyboard(string $serviceType='openvpn_l2tp'): array {
    $serviceType = vpnServiceType(['service_type'=>$serviceType]);
    $rows = [];
    if ($serviceType === 'openvpn_l2tp') $rows[] = [['text'=>'📥 دانلود کانفیگ OpenVPN','url'=>OPENVPN_CONFIG_URL]];
    $rows[] = [['text'=>'📚 آموزش '.vpnServiceTitle(['service_type'=>$serviceType]),'callback_data'=>'TUTORIAL_SERVICE_'.$serviceType]];
    return ['inline_keyboard'=>$rows];
}

function vpnUpdateDeliveryNotification(PDO $pdo, int $orderId, array $result): void {
    try {
        vpnEnsureColumns($pdo);
        $status = !empty($result['telegram_sent']) ? 'sent' : 'failed';
        $err = $result['telegram_sent'] ? null : mb_substr((string)($result['telegram_error_message'] ?? 'unknown telegram error'), 0, 1000);
        $sql = "UPDATE deliveries
                   SET notification_status=?,
                       notification_sent_at=CASE WHEN ?='sent' THEN NOW() ELSE notification_sent_at END,
                       notification_error=?,
                       notification_attempts=COALESCE(notification_attempts,0)+1
                 WHERE order_id=? AND status='delivered'";
        $pdo->prepare($sql)->execute([$status, $status, $err, $orderId]);
    } catch (Throwable $e) {
        vpnLogEvent('notification_status_update_failed', ['order_id'=>$orderId,'error'=>$e->getMessage()]);
    }
}

function vpnNotificationAdminMessage(array $result, int $orderId): string {
    if (!empty($result['telegram_sent'])) {
        return "✅ سفارش #{$orderId} تحویل شد و پیام برای کاربر ارسال شد.";
    }
    $err = trim((string)($result['telegram_error_message'] ?? 'خطای نامشخص Telegram API'));
    return "⚠️ سفارش #{$orderId} تحویل شد و در «فیلترشکن من» کاربر ثبت شد، اما ارسال پیام تلگرام ناموفق بود. احتمالاً کاربر ربات را Start نکرده، ربات را Block کرده یا Telegram API خطا داده است.\nجزئیات: {$err}";
}

function vpnNormalizeOrderForAllocation(PDO $pdo, array $order): array {
    vpnEnsureColumns($pdo);
    if (!empty($order['plan_id'])) {
        try {
            $st = $pdo->prepare("SELECT p.category_id, p.duration, p.protocol AS plan_protocol, COALESCE(p.traffic_type, pc.traffic_type, 'unlimited') traffic_type, COALESCE(p.traffic_limit_gb, pc.traffic_limit_gb) traffic_limit_gb, pc.service_type AS category_service_type, p.title AS plan_title, p.code AS plan_code FROM plans p LEFT JOIN plan_categories pc ON pc.id=p.category_id WHERE p.id=? LIMIT 1");
            $st->execute([(int)$order['plan_id']]);
            $p = $st->fetch(PDO::FETCH_ASSOC);
            if ($p) {
                if (empty($order['category_id']) && !empty($p['category_id'])) $order['category_id'] = (int)$p['category_id'];
                if (empty($order['duration']) && !empty($p['duration'])) $order['duration'] = $p['duration'];
                if (empty($order['traffic_type']) && !empty($p['traffic_type'])) $order['traffic_type'] = $p['traffic_type'];
                if (!isset($order['traffic_limit_gb']) && isset($p['traffic_limit_gb'])) $order['traffic_limit_gb'] = $p['traffic_limit_gb'];
                if (empty($order['service_type'])) $order['service_type'] = vpnServiceType($p);
                if (empty($order['plan_title']) && !empty($p['plan_title'])) $order['plan_title'] = $p['plan_title'];
                if (empty($order['plan_code']) && !empty($p['plan_code'])) $order['plan_code'] = $p['plan_code'];
            }
        } catch (Throwable $e) { vpnLogEvent('normalize_order_plan_failed', ['order_id'=>$order['id'] ?? 0, 'error'=>$e->getMessage()]); }
    }
    if (empty($order['duration'])) $order['duration'] = '1M';
    if (empty($order['traffic_type'])) $order['traffic_type'] = 'unlimited';
    if (empty($order['service_type'])) $order['service_type'] = vpnServiceType($order);
    return $order;
}

function vpnFindAvailableCredentials(PDO $pdo, array $order, int $count): array {
    $count = max(1, $count);
    $duration = (string)($order['duration'] ?? '1M');
    $categoryId = isset($order['category_id']) ? (int)$order['category_id'] : 0;
    $trafficType = strtolower((string)($order['traffic_type'] ?? 'unlimited'));
    $trafficLimit = isset($order['traffic_limit_gb']) && $order['traffic_limit_gb'] !== null ? (int)$order['traffic_limit_gb'] : null;
    $serviceType = vpnServiceType($order);

    $where = "c.duration=? AND c.is_active=1 AND COALESCE(c.sales_paused,0)=0 AND LOWER(COALESCE(c.status,'available')) IN ('available','ready','partial') AND LOWER(COALESCE(c.protocol,'openvpn_l2tp'))=? AND COALESCE(c.used_seats,0) + ? <= COALESCE(c.max_seats,1)";
    $params = [$duration, $serviceType, $count];
    if ($categoryId > 0) { $where .= " AND c.category_id=?"; $params[] = $categoryId; }
    else {
        $where .= " AND LOWER(COALESCE(c.traffic_type,'unlimited'))=?"; $params[] = $trafficType;
        if ($trafficType === 'limited') { $where .= " AND COALESCE(c.traffic_limit_gb,0)=?"; $params[] = max(1, (int)$trafficLimit); }
    }
    $sql = "SELECT c.*, pc.months FROM credentials c LEFT JOIN plan_categories pc ON pc.id=c.category_id WHERE {$where} ORDER BY c.created_at ASC, c.id ASC LIMIT 1 FOR UPDATE";
    $stmt = $pdo->prepare($sql); $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row) return [];
    $row['seats_to_allocate'] = $count;
    return [$row];
}

function vpnAllocateCredentials(PDO $pdo, array $order, int $count): array {
    vpnEnsureColumns($pdo);
    $order = vpnNormalizeOrderForAllocation($pdo, $order);
    $count = max(1, $count);
    $serviceType = vpnServiceType($order);
    $credentials = vpnFindAvailableCredentials($pdo, $order, $count);
    vpnLogEvent('allocate_attempt', [
        'order_id'=>(int)($order['id'] ?? 0),'user_id'=>(int)($order['user_id'] ?? 0),'category_id'=>(int)($order['category_id'] ?? 0),'duration'=>$order['duration'] ?? null,'requested'=>$count,'found'=>count($credentials),'service_type'=>$serviceType,'traffic_type'=>$order['traffic_type'] ?? null,'traffic_limit_gb'=>$order['traffic_limit_gb'] ?? null
    ]);
    if (!$credentials) return [];
    $credential = $credentials[0];
    $username = (string)($credential['username'] ?? $credential['email'] ?? '');
    $password = (string)($credential['password'] ?? '');
    $configText = trim((string)($credential['config_text'] ?? ''));
    $qrFileId = trim((string)($credential['qr_code_file_id'] ?? ''));
    $qrPath = trim((string)($credential['qr_code_path'] ?? ''));
    if ($serviceType === 'v2ray') {
        if ($configText === '') throw new RuntimeException('کانفیگ V2Ray انتخاب‌شده متن کانفیگ ندارد');
        if ($username === '') $username = 'v2ray_' . (int)($credential['id'] ?? 0);
        if ($password === '') $password = '-';
    } elseif ($username === '' || $password === '') {
        throw new RuntimeException('اشتراک انتخاب‌شده یوزرنیم یا پسورد ندارد');
    }
    $maxSeats = max(1, (int)($credential['max_seats'] ?? 1));
    $newUsed = (int)($credential['used_seats'] ?? 0) + $count;
    $newStatus = $newUsed >= $maxSeats ? 'sold' : 'partial';
    $isSold = $newUsed >= $maxSeats ? 1 : 0;
    $st = $pdo->prepare("UPDATE credentials SET used_seats=used_seats+?, is_sold=?, status=?, sold_to_user_id=COALESCE(NULLIF(sold_to_user_id,0),?), order_id=COALESCE(NULLIF(order_id,0),?), delivered_at=NOW(), updated_at=NOW() WHERE id=? AND is_active=1 AND COALESCE(sales_paused,0)=0 AND COALESCE(used_seats,0)+?<=COALESCE(max_seats,1)");
    $st->execute([$count,$isSold,$newStatus,(int)$order['user_id'],(int)$order['id'],(int)$credential['id'],$count]);
    if ($st->rowCount() === 0) throw new RuntimeException('ظرفیت این اکانت کافی نیست');
    $st = $pdo->prepare("INSERT INTO deliveries (order_id,user_id,credential_id,username,password,config_text,qr_code_file_id,qr_code_path,service_type,seats_allocated,status,delivered_at) VALUES (?,?,?,?,?,?,?,?,?,?,'delivered',NOW())");
    $st->execute([(int)$order['id'],(int)$order['user_id'],(int)$credential['id'],$username,$password,$configText ?: null,$qrFileId ?: null,$qrPath ?: null,$serviceType,$count]);
    $pdo->prepare("UPDATE orders SET status='DELIVERED', payment_status='paid', delivery_status='delivered', delivered_accounts=1, delivered_at=NOW(), approved_at=COALESCE(approved_at,NOW()) WHERE id=?")->execute([(int)$order['id']]);
    $pdo->prepare("UPDATE waiting_queue SET processed_at=NOW(), status='processed', updated_at=NOW() WHERE order_id=? AND processed_at IS NULL")->execute([(int)$order['id']]);
    $credential['username']=$username; $credential['password']=$password; $credential['config_text']=$configText; $credential['qr_code_file_id']=$qrFileId; $credential['qr_code_path']=$qrPath; $credential['service_type']=$serviceType; $credential['seats_allocated']=$count; $credential['used_seats_after']=$newUsed; $credential['remaining']=max(0,$maxSeats-$newUsed);
    if ($serviceType === 'windscribe') vpnMaybeNotifyWindscribeCapacity($pdo, $credential);
    vpnLogEvent('order_delivered', ['order_id'=>(int)$order['id'],'user_id'=>(int)$order['user_id'],'service_type'=>$serviceType,'seats'=>$count]);
    return [$credential];
}

function vpnMaybeNotifyWindscribeCapacity(PDO $pdo, array $credential): void {
    try {
        if (vpnServiceType($credential) !== 'windscribe') return;
        $used=(int)($credential['used_seats_after'] ?? $credential['used_seats'] ?? 0);
        if ($used < 10 || !empty($credential['capacity_warn_sent'])) return;
        $max=max(1,(int)($credential['max_seats'] ?? 15)); $remaining=max(0,$max-$used);
        $txt="⚠️ <b>هشدار ظرفیت Windscribe</b>

اکانت زیر به بیش از ۱۰ فروش رسیده است:

👤 Username:
".tgCode($credential['username'] ?? $credential['email'] ?? '')."

👥 استفاده شده: <b>{$used}/{$max}</b>
📊 باقی‌مانده: <b>{$remaining}</b>

لطفاً برای جلوگیری از توقف فروش، اکانت جدید Windscribe آماده و اضافه کنید.";
        foreach (array_filter(array_map('trim', explode(',', ADMIN_IDS))) as $adminId) vpnTelegramSendMessage((int)$adminId,$txt, ['inline_keyboard'=>[[['text'=>'➕ افزودن اشتراک','callback_data'=>'ADMIN_ADD_CREDENTIAL']]]]);
        $pdo->prepare("UPDATE credentials SET capacity_warn_sent=1 WHERE id=?")->execute([(int)$credential['id']]);
    } catch(Throwable $e){ vpnLogEvent('windscribe_capacity_warning_failed',['error'=>$e->getMessage()]); }
}

function vpnQueueOrderWithCount(PDO $pdo, array $order, int $count): void {
    vpnEnsureColumns($pdo);
    $order = vpnNormalizeOrderForAllocation($pdo, $order);
    $count=max(1,$count);
    $categoryId = !empty($order['category_id']) ? (int)$order['category_id'] : null;
    $planId = !empty($order['plan_id']) ? (int)$order['plan_id'] : null;
    $trafficType = (string)($order['traffic_type'] ?? 'unlimited');
    $trafficLimit = $order['traffic_limit_gb'] ?? null;
    $serviceType = vpnServiceType($order);
    $st=$pdo->prepare("SELECT id FROM waiting_queue WHERE order_id=? AND processed_at IS NULL LIMIT 1");
    $st->execute([(int)$order['id']]);
    if ($st->fetch()) {
        $pdo->prepare("UPDATE waiting_queue SET requested_seats=?, required_accounts=?, duration=?, category_id=?, plan_id=?, service_type=?, traffic_type=?, traffic_limit_gb=?, status='pending', updated_at=NOW() WHERE order_id=? AND processed_at IS NULL")
            ->execute([$count,$count,(string)$order['duration'],$categoryId,$planId,$serviceType,$trafficType,$trafficLimit,(int)$order['id']]);
    } else {
        $stmt=$pdo->prepare("INSERT INTO waiting_queue(order_id,user_id,plan_id,category_id,duration,service_type,requested_seats,traffic_type,traffic_limit_gb,required_accounts,status) VALUES(?,?,?,?,?,?,?,?,?,?,'pending')");
        $stmt->execute([(int)$order['id'],(int)$order['user_id'],$planId,$categoryId,(string)$order['duration'],$serviceType,$count,$trafficType,$trafficLimit,$count]);
    }
    $pdo->prepare("UPDATE orders SET status='QUEUED', delivery_status='queued', requested_seats=?, required_accounts=GREATEST(COALESCE(required_accounts,?),?), approved_at=COALESCE(approved_at,NOW()) WHERE id=?")
        ->execute([$count,$count,$count,(int)$order['id']]);
    vpnLogEvent('order_queued', ['order_id'=>(int)$order['id'],'user_id'=>(int)$order['user_id'],'category_id'=>$categoryId,'traffic_type'=>$trafficType,'traffic_limit_gb'=>$trafficLimit]);
}

function vpnQueueOrder(PDO $pdo, array $order): void {
    vpnQueueOrderWithCount($pdo, $order, vpnOrderRequestedSeats($order));
}

function vpnAllocateCredential(PDO $pdo, array $order): ?array {
    $creds = vpnAllocateCredentials($pdo, $order, 1);
    return $creds[0] ?? null;
}

function vpnNotifyDeliveryBatch(int $userId, array $credentials, array $plan=[], ?int $orderId=null, ?PDO $pdo=null): array {
    $base = [
        'telegram_sent'=>false,
        'telegram_error_code'=>null,
        'telegram_error_message'=>null,
        'user_id'=>$userId,
        'order_id'=>$orderId,
        'delivery_ids'=>[],
        'credential_ids'=>array_values(array_filter(array_map(fn($c)=>(int)($c['id'] ?? $c['credential_id'] ?? 0), $credentials))),
    ];
    if (!$credentials) {
        $base['telegram_error_message'] = 'No credentials to notify';
        return $base;
    }
    if (in_array(vpnServiceType($plan ?: ($credentials[0] ?? [])), ['windscribe','expressvpn','v2ray'], true) || count($credentials) <= 1) {
        $result = vpnNotifyDelivery($userId, $credentials[0] ?? [], $plan, $orderId, $pdo);
        return array_merge($base, $result);
    }
    $txt = "✅ <b>سفارش شما با " . count($credentials) . " اکانت اختصاصی تحویل شد.</b>\n\n";
    foreach ($credentials as $i=>$c) {
        $u=$c['username'] ?? $c['email'] ?? '';
        $pw=$c['password'] ?? '';
        $txt .= ($i+1) . ". 👤 <b>یوزرنیم:</b> " . tgCode($u) . "\n";
        $txt .= "   🔐 <b>پسورد:</b> " . tgCode($pw) . "\n\n";
    }
    $txt .= "🌐 <b>سرور L2TP:</b> " . tgCode(L2TP_SERVER) . "\n📥 <b>کانفیگ OpenVPN:</b> " . tgHtmlEscape(OPENVPN_CONFIG_URL) . "\n\nبرای جزئیات، دکمه «فیلترشکن من» را بزنید.";
    $result = vpnTelegramSendMessage($userId, $txt, vpnDeliveryKeyboard('openvpn_l2tp'));
    $result = array_merge($base, $result, ['order_id'=>$orderId]);
    if ($pdo && $orderId) vpnUpdateDeliveryNotification($pdo, $orderId, $result);
    vpnLogEvent('delivery_notification_result', $result);
    return $result;
}

function vpnNotifyDelivery(int $userId, array $credential, array $plan=[], ?int $orderId=null, ?PDO $pdo=null): array {
    $serviceType = vpnServiceType($plan ?: $credential);
    $result = vpnTelegramSendMessage($userId, vpnDeliveryText($credential,$plan), vpnDeliveryKeyboard($serviceType));
    if ($serviceType === 'v2ray' && !empty($result['telegram_sent'])) {
        $photo = trim((string)($credential['qr_code_file_id'] ?? ''));
        if ($photo === '' && !empty($credential['qr_code_path'])) {
            $relative = ltrim((string)$credential['qr_code_path'], '/');
            $absolute = dirname(__DIR__, 3) . '/' . $relative;
            if (is_file($absolute)) $photo = $absolute;
            elseif (defined('MINIAPP_URL')) $photo = rtrim((string)MINIAPP_URL, '/') . '/' . preg_replace('#^public/mini_app/#', '', $relative);
        }
        if ($photo !== '') {
            try {
                $qrRes = tg('sendPhoto', ['chat_id'=>$userId,'photo'=>$photo,'caption'=>'📷 QR Code کانفیگ V2Ray شما']);
                if (!(is_array($qrRes) && !empty($qrRes['ok']))) vpnLogEvent('v2ray_qr_send_failed',['order_id'=>$orderId,'response'=>$qrRes]);
            } catch (Throwable $e) { vpnLogEvent('v2ray_qr_send_exception',['order_id'=>$orderId,'error'=>$e->getMessage()]); }
        }
    }
    $result['order_id'] = $orderId;
    $result['credential_id'] = (int)($credential['id'] ?? $credential['credential_id'] ?? 0);
    if ($pdo && $orderId) vpnUpdateDeliveryNotification($pdo, $orderId, $result);
    vpnLogEvent('delivery_notification_result', $result);
    return $result;
}

function vpnFetchExistingDeliveryCredentials(PDO $pdo, int $orderId): array {
    $st = $pdo->prepare("SELECT d.credential_id AS id, d.username, d.password, d.config_text, d.qr_code_file_id, d.qr_code_path, d.service_type, d.seats_allocated, c.email, c.duration, c.protocol, c.max_seats, c.used_seats, c.traffic_type, c.traffic_limit_gb
                           FROM deliveries d
                           LEFT JOIN credentials c ON c.id=d.credential_id
                          WHERE d.order_id=? AND d.status='delivered'
                          ORDER BY d.id ASC");
    $st->execute([$orderId]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}


function vpnFetchOrderForNotification(PDO $pdo, int $orderId): ?array {
    $st = $pdo->prepare("SELECT o.*, p.title plan_title, p.code plan_code, p.protocol AS plan_protocol, p.category_id AS plan_category_id, pc.service_type AS category_service_type, pc.traffic_type AS category_traffic_type, pc.traffic_limit_gb AS category_traffic_limit_gb
                           FROM orders o
                           LEFT JOIN plans p ON p.id=o.plan_id
                           LEFT JOIN plan_categories pc ON pc.id=COALESCE(o.category_id,p.category_id)
                          WHERE o.id=? LIMIT 1");
    $st->execute([$orderId]);
    $order = $st->fetch(PDO::FETCH_ASSOC);
    if (!$order) return null;
    if (empty($order['category_id']) && !empty($order['plan_category_id'])) $order['category_id'] = (int)$order['plan_category_id'];
    if (empty($order['service_type'])) $order['service_type'] = vpnServiceType($order);
        if (empty($order['traffic_type']) && !empty($order['category_traffic_type'])) $order['traffic_type'] = $order['category_traffic_type'];
    if (!isset($order['traffic_limit_gb']) && isset($order['category_traffic_limit_gb'])) $order['traffic_limit_gb'] = $order['category_traffic_limit_gb'];
    return $order;
}

function vpnResendDeliveryNotification(PDO $pdo, int $orderId, bool $force=false): array {
    vpnEnsureColumns($pdo);
    $order = vpnFetchOrderForNotification($pdo, $orderId);
    if (!$order) return ['telegram_sent'=>false,'telegram_error_message'=>'Order not found','order_id'=>$orderId];

    if (!$force) {
        $st = $pdo->prepare("SELECT COUNT(*) FROM deliveries WHERE order_id=? AND status='delivered' AND notification_status='sent'");
        $st->execute([$orderId]);
        if ((int)$st->fetchColumn() > 0) {
            return ['telegram_sent'=>true,'skipped'=>true,'telegram_error_message'=>null,'order_id'=>$orderId,'message'=>'notification already sent'];
        }
    }

    $creds = vpnFetchExistingDeliveryCredentials($pdo, $orderId);
    if (!$creds) return ['telegram_sent'=>false,'telegram_error_message'=>'No delivered credentials found for order','order_id'=>$orderId];
    return vpnNotifyDeliveryBatch((int)$order['user_id'], $creds, $order, $orderId, $pdo);
}

function vpnRetryPendingDeliveryNotifications(PDO $pdo, int $limit=20, array $onlyOrderIds=[]): array {
    vpnEnsureColumns($pdo);
    $limit = max(1, min(100, $limit));
    $processed = [];
    $params = [];
    $where = "d.status='delivered' AND COALESCE(d.notification_status,'pending') <> 'sent' AND COALESCE(d.notification_attempts,0) < 5";
    if ($onlyOrderIds) {
        $ids = array_values(array_unique(array_map('intval', $onlyOrderIds)));
        $ids = array_filter($ids, fn($v) => $v > 0);
        if ($ids) {
            $where .= " AND d.order_id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")";
            $params = array_merge($params, $ids);
        }
    }
    $sql = "SELECT d.order_id
              FROM deliveries d
              JOIN orders o ON o.id=d.order_id
             WHERE {$where}
             GROUP BY d.order_id
             ORDER BY MIN(d.delivered_at) ASC
             LIMIT {$limit}";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $orderIds = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
    foreach ($orderIds as $oid) {
        try {
            $result = vpnResendDeliveryNotification($pdo, $oid, true);
            $processed[] = [
                'order_id'=>$oid,
                'telegram_sent'=>!empty($result['telegram_sent']),
                'telegram_error_message'=>$result['telegram_error_message'] ?? null,
            ];
            vpnLogEvent('delivery_notification_retry', ['order_id'=>$oid,'result'=>$result]);
        } catch (Throwable $e) {
            vpnLogEvent('delivery_notification_retry_failed', ['order_id'=>$oid,'error'=>$e->getMessage()]);
            $processed[] = ['order_id'=>$oid,'telegram_sent'=>false,'telegram_error_message'=>$e->getMessage()];
        }
    }
    return ['processed'=>$processed,'processed_count'=>count($processed)];
}

function vpnApproveOrderAndNotify(PDO $pdo, int $orderId, bool $notify=true): array {
    vpnEnsureColumns($pdo);
    $order = null; $creds = []; $queued = false;
    $pdo->beginTransaction();
    try {
        $st=$pdo->prepare("SELECT o.*, p.title plan_title, p.code plan_code, p.protocol AS plan_protocol, p.category_id AS plan_category_id, pc.service_type AS category_service_type, pc.traffic_type AS category_traffic_type, pc.traffic_limit_gb AS category_traffic_limit_gb
                           FROM orders o
                           LEFT JOIN plans p ON p.id=o.plan_id
                           LEFT JOIN plan_categories pc ON pc.id=COALESCE(o.category_id,p.category_id)
                           WHERE o.id=? FOR UPDATE");
        $st->execute([$orderId]);
        $order=$st->fetch(PDO::FETCH_ASSOC);
        if (!$order) throw new RuntimeException('سفارش یافت نشد');
        if ($order['status'] === 'DELIVERED') {
            $creds = vpnFetchExistingDeliveryCredentials($pdo, $orderId);
            if ($pdo->inTransaction()) { $pdo->commit(); }
            return ['success'=>false,'already_delivered'=>true,'credentials'=>$creds,'message'=>'این سفارش قبلاً تحویل شده است','order'=>$order,'notification'=>null];
        }
        if (!in_array($order['status'], ['PENDING','PAID','QUEUED','WAITING_PAYMENT'], true)) throw new RuntimeException('این سفارش قابل تأیید نیست');
        if (empty($order['category_id']) && !empty($order['plan_category_id'])) $order['category_id'] = (int)$order['plan_category_id'];
        if (empty($order['service_type'])) $order['service_type'] = vpnServiceType($order);
        if (empty($order['traffic_type']) && !empty($order['category_traffic_type'])) $order['traffic_type'] = $order['category_traffic_type'];
        if (!isset($order['traffic_limit_gb']) && isset($order['category_traffic_limit_gb'])) $order['traffic_limit_gb'] = $order['category_traffic_limit_gb'];
        $requestedSeats=max(1,(int)($order['requested_seats']??1));
        $pdo->prepare("UPDATE orders SET approved_at=COALESCE(approved_at,NOW()), payment_status='paid' WHERE id=?")->execute([$orderId]);
        try { $pdo->prepare("UPDATE crypto_payments SET status='PAID', paid_at=COALESCE(paid_at,NOW()), processed_at=NOW() WHERE order_id=?")->execute([$orderId]); } catch (Throwable $ignored) {}
        $creds = vpnAllocateCredentials($pdo, $order, $requestedSeats);
        if (!$creds) { vpnQueueOrderWithCount($pdo, $order, $requestedSeats); $queued = true; }
        if ($pdo->inTransaction()) { $pdo->commit(); }
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        vpnLogEvent('approve_order_failed', ['order_id'=>$orderId,'error'=>$e->getMessage()]);
        return ['success'=>false,'queued'=>false,'message'=>$e->getMessage(),'notification'=>null];
    }

    if ($queued) {
        $queuedText = "📦 <b>سفارش شما ثبت و در صف تحویل قرار گرفت</b>

" .
            "🆔 شماره سفارش: #{$orderId}
" .
            "⚠️ موجودی این پلن فعلاً کافی نیست.
" .
            "به محض اضافه شدن اشتراک توسط مدیر، سرویس کامل در همین چت برای شما ارسال می‌شود.";
        $queuedNotice = $notify ? vpnTelegramSendMessage((int)$order['user_id'], $queuedText) : null;
        return ['success'=>false,'queued'=>true,'message'=>'موجودی کافی نیست؛ سفارش در صف تحویل قرار گرفت.','order'=>$order,'credentials'=>[],'notification'=>$queuedNotice];
    }

    $notification = $notify ? vpnNotifyDeliveryBatch((int)$order['user_id'], $creds, $order, $orderId, $pdo) : null;
    return ['success'=>true,'queued'=>false,'message'=>'سفارش با موفقیت تحویل داده شد','order'=>$order,'credential'=>$creds[0] ?? null,'credentials'=>$creds,'notification'=>$notification];
}

function vpnProcessWaitingQueue(PDO $pdo, ?string $duration=null, int $max=20, ?int $categoryId=null): array {
    vpnEnsureColumns($pdo);
    $processed=[]; $queued=[];
    $sql = "SELECT w.id AS queue_id, w.order_id AS queue_order_id, w.requested_seats AS queue_requested_seats,
                   w.category_id AS queue_category_id, w.plan_id AS queue_plan_id, w.duration AS queue_duration,
                   w.traffic_type AS queue_traffic_type, w.traffic_limit_gb AS queue_traffic_limit_gb, w.service_type AS queue_service_type,
                   o.*
            FROM waiting_queue w
            JOIN orders o ON o.id=w.order_id
            WHERE w.processed_at IS NULL AND o.status='QUEUED'";
    $params=[];
    if ($duration) { $sql .= " AND w.duration=?"; $params[]=$duration; }
    if ($categoryId) { $sql .= " AND w.category_id=?"; $params[]=$categoryId; }
    $sql .= " ORDER BY w.id ASC LIMIT ".max(1,$max);
    $st=$pdo->prepare($sql); $st->execute($params);
    $items=$st->fetchAll(PDO::FETCH_ASSOC);
    foreach($items as $order){
        $creds=[];
        $pdo->beginTransaction();
        try{
            if (empty($order['category_id']) && !empty($order['queue_category_id'])) $order['category_id'] = $order['queue_category_id'];
            if (empty($order['plan_id']) && !empty($order['queue_plan_id'])) $order['plan_id'] = $order['queue_plan_id'];
            if (empty($order['duration']) && !empty($order['queue_duration'])) $order['duration'] = $order['queue_duration'];
            if (empty($order['traffic_type']) && !empty($order['queue_traffic_type'])) $order['traffic_type'] = $order['queue_traffic_type'];
            if (empty($order['service_type']) && !empty($order['queue_service_type'])) $order['service_type'] = $order['queue_service_type'];
            if (!isset($order['traffic_limit_gb']) && isset($order['queue_traffic_limit_gb'])) $order['traffic_limit_gb'] = $order['queue_traffic_limit_gb'];
            $count = max(1,(int)($order['queue_requested_seats'] ?? $order['requested_seats'] ?? 1));
            $creds = vpnAllocateCredentials($pdo,$order,$count);
            if ($pdo->inTransaction()) { $pdo->commit(); }
            if ($creds) { $notify = vpnNotifyDeliveryBatch((int)$order['user_id'],$creds,$order,(int)$order['id'],$pdo); $processed[]=['order_id'=>(int)$order['id'],'user_id'=>(int)$order['user_id'],'count'=>count($creds),'telegram_sent'=>!empty($notify['telegram_sent']),'telegram_error_message'=>$notify['telegram_error_message'] ?? null]; vpnLogEvent('queue_processed', ['order_id'=>(int)$order['id'],'user_id'=>(int)$order['user_id'],'count'=>count($creds),'notification'=>$notify]); }
            else { $queued[]=(int)$order['id']; }
        }catch(Throwable $e){ if($pdo->inTransaction()) $pdo->rollBack(); vpnLogEvent('queue_process_failed', ['order_id'=>(int)($order['id'] ?? 0),'error'=>$e->getMessage()]); $queued[]=(int)($order['id'] ?? 0); }
    }
    return ['processed'=>$processed,'still_queued'=>$queued,'processed_count'=>count($processed),'still_waiting'=>count($queued)];
}

