<?php
declare(strict_types=1);

final class Repository
{
    public function __construct(private PDO $db)
    {
        $this->ensureRuntimeTables();
    }

    public function db(): PDO { return $this->db; }

    private function ensureRuntimeTables(): void
    {
        // این بخش در هر درخواست اجرا می‌شود؛ پس نباید حتی روی دیتابیس‌های قدیمی باعث Fatal Error شود.
        $this->ensureRequiredJoinsTable();
        $this->ensureTopupRequestsTable();

        $this->addColumnIfMissing('bots', 'deployment_path', 'VARCHAR(255) NULL');
        $this->addColumnIfMissing('bots', 'webhook_url', 'VARCHAR(500) NULL');
        $this->addColumnIfMissing('bots', 'child_db_name', 'VARCHAR(64) NULL');
        $this->addColumnIfMissing('bots', 'child_db_user', 'VARCHAR(32) NULL');
        $this->addColumnIfMissing('bots', 'child_db_user_host', 'VARCHAR(255) NULL');
        $this->addColumnIfMissing('bots', 'child_db_provider', 'VARCHAR(32) NULL');
        $this->addColumnIfMissing('bots', 'child_storage_path', 'VARCHAR(255) NULL');
        $this->addColumnIfMissing('bots', 'isolation_mode', "VARCHAR(32) NOT NULL DEFAULT 'legacy_shared'");
        $this->ensureIsolationModeColumn();
        $this->addColumnIfMissing('bots', 'isolated_at', 'DATETIME NULL');

        $this->setDefaultSetting('topup_min_amount', '200000');
        $this->setDefaultSetting('topup_max_amount', '50000000');
        $this->setDefaultSetting('bot_min_balance', '200000');
    }

    private function ensureRequiredJoinsTable(): void
    {
        $this->db->exec("CREATE TABLE IF NOT EXISTS required_joins (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(190) NOT NULL,
            chat_id VARCHAR(80) NOT NULL,
            invite_url VARCHAR(255) NULL,
            join_type ENUM('channel','group') NOT NULL DEFAULT 'channel',
            status ENUM('active','inactive') NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NULL,
            INDEX idx_required_joins_status(status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private function ensureTopupRequestsTable(): void
    {
        // عمداً بدون FOREIGN KEY ساخته می‌شود. روی بعضی نصب‌های قبلی، جدول users از نظر Engine/Type
        // با BIGINT UNSIGNED هماهنگ نیست و FK باعث errno 150 و خاموش شدن کل ربات می‌شد.
        $this->db->exec("CREATE TABLE IF NOT EXISTS topup_requests (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT UNSIGNED NOT NULL,
            transaction_id BIGINT UNSIGNED NULL,
            amount BIGINT NOT NULL,
            method ENUM('card_to_card') NOT NULL DEFAULT 'card_to_card',
            receipt_type VARCHAR(30) NULL,
            receipt_file_id VARCHAR(255) NULL,
            receipt_caption TEXT NULL,
            status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
            admin_user_id BIGINT UNSIGNED NULL,
            reject_reason TEXT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NULL,
            INDEX idx_topup_user(user_id),
            INDEX idx_topup_status(status),
            INDEX idx_topup_created(created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // برای آپدیت از نسخه‌های قبلی: اگر جدول وجود داشت ولی ستون‌های جدید را نداشت، کاملش می‌کنیم.
        $this->addColumnIfMissing('topup_requests', 'transaction_id', 'BIGINT UNSIGNED NULL');
        $this->addColumnIfMissing('topup_requests', 'receipt_type', 'VARCHAR(30) NULL');
        $this->addColumnIfMissing('topup_requests', 'receipt_file_id', 'VARCHAR(255) NULL');
        $this->addColumnIfMissing('topup_requests', 'receipt_caption', 'TEXT NULL');
        $this->addColumnIfMissing('topup_requests', 'admin_user_id', 'BIGINT UNSIGNED NULL');
        $this->addColumnIfMissing('topup_requests', 'reject_reason', 'TEXT NULL');
        $this->addColumnIfMissing('topup_requests', 'updated_at', 'DATETIME NULL');
        $this->addIndexIfMissing('topup_requests', 'idx_topup_user', '(`user_id`)');
        $this->addIndexIfMissing('topup_requests', 'idx_topup_status', '(`status`)');
        $this->addIndexIfMissing('topup_requests', 'idx_topup_created', '(`created_at`)');
    }

    private function ensureIsolationModeColumn(): void
    {
        if (!$this->tableExists('bots')) return;
        $q = $this->db->prepare(
            'SELECT DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1'
        );
        $q->execute(['bots', 'isolation_mode']);
        $column = $q->fetch();
        if (!$column) return;
        if (strtolower((string)($column['DATA_TYPE'] ?? '')) !== 'varchar') {
            $this->db->exec("ALTER TABLE `bots` MODIFY COLUMN `isolation_mode` VARCHAR(32) NOT NULL DEFAULT 'legacy_shared'");
        }
    }

    private function tableExists(string $table): bool
    {
        $tableSafe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        if ($tableSafe === '') return false;
        $q = $this->db->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?');
        $q->execute([$tableSafe]);
        return (int)$q->fetchColumn() > 0;
    }

    private function setDefaultSetting(string $key, string $value): void
    {
        if (!$this->tableExists('settings')) return;
        $s = $this->db->prepare('INSERT IGNORE INTO settings(`key`, value, updated_at) VALUES(?,?,?)');
        $s->execute([$key, $value, now()]);
    }

    private function addColumnIfMissing(string $table, string $column, string $definition): void
    {
        $tableSafe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $columnSafe = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
        if ($tableSafe === '' || $columnSafe === '' || !$this->tableExists($tableSafe)) return;
        $q = $this->db->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?');
        $q->execute([$tableSafe, $columnSafe]);
        if ((int)$q->fetchColumn() === 0) {
            $this->db->exec('ALTER TABLE `' . $tableSafe . '` ADD COLUMN `' . $columnSafe . '` ' . $definition);
        }
    }

    private function addIndexIfMissing(string $table, string $index, string $columns): void
    {
        $tableSafe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $indexSafe = preg_replace('/[^a-zA-Z0-9_]/', '', $index);
        if ($tableSafe === '' || $indexSafe === '' || !$this->tableExists($tableSafe)) return;
        $q = $this->db->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?');
        $q->execute([$tableSafe, $indexSafe]);
        if ((int)$q->fetchColumn() === 0) {
            $this->db->exec('ALTER TABLE `' . $tableSafe . '` ADD INDEX `' . $indexSafe . '` ' . $columns);
        }
    }

    public function setting(string $key, ?string $default = null): ?string
    {
        $s = $this->db->prepare('SELECT value FROM settings WHERE `key`=? LIMIT 1');
        $s->execute([$key]);
        $v = $s->fetchColumn();
        return $v === false ? $default : (string)$v;
    }

    public function setSetting(string $key, ?string $value): void
    {
        $s = $this->db->prepare('INSERT INTO settings(`key`, value, updated_at) VALUES(?,?,?) ON DUPLICATE KEY UPDATE value=VALUES(value), updated_at=VALUES(updated_at)');
        $s->execute([$key, $value, now()]);
    }

    public function upsertUser(array $u, string $defaultRole = 'customer'): array
    {
        $tg = digits_only((string)($u['telegram_id'] ?? $u['id'] ?? ''));
        if ($tg === '') {
            throw new RuntimeException('telegram_id خالی است.');
        }
        $now = now();
        $existing = $this->findUserByTelegramId($tg);
        if ($existing) {
            $stmt = $this->db->prepare('UPDATE users SET username=?, first_name=?, last_name=?, last_seen_at=?, updated_at=? WHERE telegram_id=?');
            $stmt->execute([
                $u['username'] ?? $existing['username'] ?? null,
                $u['first_name'] ?? $existing['first_name'] ?? null,
                $u['last_name'] ?? $existing['last_name'] ?? null,
                $now, $now, $tg,
            ]);
            return $this->findUserByTelegramId($tg) ?: $existing;
        }
        $stmt = $this->db->prepare('INSERT INTO users(telegram_id, username, first_name, last_name, role, balance, status, last_seen_at, created_at, updated_at) VALUES(?,?,?,?,?,0,"active",?,?,?)');
        $stmt->execute([
            $tg,
            $u['username'] ?? null,
            $u['first_name'] ?? null,
            $u['last_name'] ?? null,
            $defaultRole,
            $now,
            $now,
            $now,
        ]);
        return $this->findUserByTelegramId($tg) ?: [];
    }

    public function findUserByTelegramId(string|int $telegramId): ?array
    {
        $s = $this->db->prepare('SELECT * FROM users WHERE telegram_id=? LIMIT 1');
        $s->execute([digits_only((string)$telegramId)]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function findUserById(int $id): ?array
    {
        $s = $this->db->prepare('SELECT * FROM users WHERE id=? LIMIT 1');
        $s->execute([$id]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function makeAdmin(string|int $telegramId, string $level = 'manager'): array
    {
        $user = $this->upsertUser(['telegram_id' => (string)$telegramId, 'first_name' => 'Admin'], 'admin');
        $this->db->prepare('UPDATE users SET role="admin", status="active", updated_at=? WHERE id=?')->execute([now(), $user['id']]);
        $this->db->prepare('INSERT INTO admins(user_id, level, created_at) VALUES(?,?,?) ON DUPLICATE KEY UPDATE level=VALUES(level)')->execute([(int)$user['id'], $level, now()]);
        return $this->findUserById((int)$user['id']) ?: $user;
    }

    public function listAdminTelegramIds(): array
    {
        $s = $this->db->query('SELECT u.telegram_id FROM admins a JOIN users u ON u.id=a.user_id WHERE u.role="admin" AND u.status="active" ORDER BY a.id ASC');
        return array_values(array_filter(array_map('strval', $s->fetchAll(PDO::FETCH_COLUMN))));
    }

    public function makeReseller(string|int $telegramId, string $name, float $commission): array
    {
        $user = $this->upsertUser(['telegram_id' => (string)$telegramId, 'first_name' => $name], 'reseller');
        $this->db->prepare('UPDATE users SET role="reseller", status="active", updated_at=? WHERE id=?')->execute([now(), $user['id']]);
        $this->db->prepare('INSERT INTO resellers(user_id, display_name, commission_percent, status, created_at, updated_at) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE display_name=VALUES(display_name), commission_percent=VALUES(commission_percent), status="active", updated_at=VALUES(updated_at)')
            ->execute([(int)$user['id'], $name, $commission, 'active', now(), now()]);
        return $this->findUserById((int)$user['id']) ?: $user;
    }

    public function resellerByUserId(int $userId): ?array
    {
        $s = $this->db->prepare('SELECT * FROM resellers WHERE user_id=? LIMIT 1');
        $s->execute([$userId]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function chargeUser(int $actorId, string|int $telegramId, int $amount, string $description): array
    {
        if ($amount === 0) throw new RuntimeException('مبلغ نباید صفر باشد.');
        $user = $this->upsertUser(['telegram_id' => (string)$telegramId], 'customer');
        $this->db->beginTransaction();
        try {
            if ($amount < 0) {
                $lock = $this->db->prepare('SELECT balance FROM users WHERE id=? FOR UPDATE');
                $lock->execute([(int)$user['id']]);
                $currentBalance = (int)$lock->fetchColumn();
                if ($currentBalance + $amount < 0) {
                    throw new RuntimeException('موجودی کاربر برای کاهش این مبلغ کافی نیست.');
                }
            }
            $this->db->prepare('UPDATE users SET balance=balance+?, updated_at=? WHERE id=?')->execute([$amount, now(), $user['id']]);
            $this->db->prepare('INSERT INTO transactions(user_id, amount, type, status, description, created_at) VALUES(?,?,?,?,?,?)')
                ->execute([(int)$user['id'], $amount, $amount > 0 ? 'charge' : 'debit', 'approved', $description, now()]);
            $this->audit($actorId, 'charge_user', ['telegram_id' => (string)$telegramId, 'amount' => $amount]);
            $this->db->commit();
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
        return $this->findUserById((int)$user['id']) ?: $user;
    }

    public function saveCard(string $holder, string $number, ?string $iban, string $title = 'کارت اصلی'): void
    {
        $numberDigits = digits_only($number);
        if (strlen($numberDigits) < 16) throw new RuntimeException('شماره کارت معتبر نیست.');
        $this->db->prepare('UPDATE payment_cards SET is_active=0')->execute();
        $this->db->prepare('INSERT INTO payment_cards(title, holder, card_number, iban, is_active, created_at, updated_at) VALUES(?,?,?,?,1,?,?)')
            ->execute([$title, $holder, $number, $iban, now(), now()]);
    }

    public function activeCard(): ?array
    {
        $s = $this->db->query('SELECT * FROM payment_cards WHERE is_active=1 ORDER BY id DESC LIMIT 1');
        $r = $s->fetch();
        return $r ?: null;
    }

    public function paymentSettings(): array
    {
        return [
            'card' => $this->activeCard(),
            'topup_min_amount' => (int)$this->setting('topup_min_amount', '200000'),
            'topup_max_amount' => (int)$this->setting('topup_max_amount', '50000000'),
            'bot_min_balance' => (int)$this->setting('bot_min_balance', '200000'),
        ];
    }

    public function savePaymentSettings(string $holder, string $number, ?string $iban, string $title, int $minAmount, int $maxAmount, int $botMinBalance): void
    {
        if ($holder === '') throw new RuntimeException('نام صاحب کارت را وارد کنید.');
        if (strlen(digits_only($number)) < 16) throw new RuntimeException('شماره کارت معتبر نیست.');
        if ($minAmount < 1000) throw new RuntimeException('حداقل مبلغ شارژ خیلی کم است.');
        if ($maxAmount < $minAmount) throw new RuntimeException('حداکثر مبلغ باید بیشتر از حداقل باشد.');
        if ($botMinBalance < 0) throw new RuntimeException('حداقل موجودی ساخت ربات نامعتبر است.');
        $this->saveCard($holder, $number, $iban, $title ?: 'کارت اصلی');
        $this->setSetting('topup_min_amount', (string)$minAmount);
        $this->setSetting('topup_max_amount', (string)$maxAmount);
        $this->setSetting('bot_min_balance', (string)$botMinBalance);
    }

    public function createTopupRequest(int $userId, int $amount, string $receiptType, string $receiptFileId, ?string $caption = null): array
    {
        $settings = $this->paymentSettings();
        $min = (int)$settings['topup_min_amount'];
        $max = (int)$settings['topup_max_amount'];
        if ($amount < $min) throw new RuntimeException('مبلغ وارد شده کمتر از حداقل مجاز است.');
        if ($amount > $max) throw new RuntimeException('مبلغ وارد شده بیشتر از حداکثر مجاز است.');
        if ($receiptFileId === '') throw new RuntimeException('رسید واریزی دریافت نشد.');
        $this->db->beginTransaction();
        try {
            $this->db->prepare('INSERT INTO transactions(user_id, amount, type, status, description, created_at) VALUES(?,?,?,?,?,?)')
                ->execute([$userId, $amount, 'manual_payment', 'pending', 'درخواست افزایش موجودی کارت به کارت', now()]);
            $txId = (int)$this->db->lastInsertId();
            $this->db->prepare('INSERT INTO topup_requests(user_id, transaction_id, amount, method, receipt_type, receipt_file_id, receipt_caption, status, created_at, updated_at) VALUES(?,?,?,?,?,?,?,?,?,?)')
                ->execute([$userId, $txId, $amount, 'card_to_card', $receiptType, $receiptFileId, $caption, 'pending', now(), now()]);
            $id = (int)$this->db->lastInsertId();
            $this->audit($userId, 'topup_request_created', ['payment_id' => $id, 'amount' => $amount]);
            $this->db->commit();
            return $this->topupRequestById($id) ?: ['id' => $id];
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function topupRequestById(int $id): ?array
    {
        $s = $this->db->prepare('SELECT tr.*, u.telegram_id, u.username, u.first_name, u.last_name, u.balance FROM topup_requests tr JOIN users u ON u.id=tr.user_id WHERE tr.id=? LIMIT 1');
        $s->execute([$id]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function listTopupRequests(int $limit = 100): array
    {
        $s = $this->db->prepare('SELECT tr.*, u.telegram_id, u.username, u.first_name, u.last_name FROM topup_requests tr JOIN users u ON u.id=tr.user_id ORDER BY tr.id DESC LIMIT ?');
        $s->bindValue(1, $limit, PDO::PARAM_INT);
        $s->execute();
        return $s->fetchAll();
    }

    public function approveTopup(int $paymentId, int $adminUserId): array
    {
        $this->db->beginTransaction();
        try {
            $p = $this->topupRequestById($paymentId);
            if (!$p) throw new RuntimeException('درخواست واریز پیدا نشد.');
            if ($p['status'] !== 'pending') throw new RuntimeException('این رسید قبلاً بررسی شده است.');
            $this->db->prepare('UPDATE topup_requests SET status="approved", admin_user_id=?, updated_at=? WHERE id=? AND status="pending"')
                ->execute([$adminUserId, now(), $paymentId]);
            if (!empty($p['transaction_id'])) {
                $this->db->prepare('UPDATE transactions SET status="approved", description=? WHERE id=?')
                    ->execute(['افزایش موجودی تایید شده توسط ادمین', (int)$p['transaction_id']]);
            }
            $this->db->prepare('UPDATE users SET balance=balance+?, updated_at=? WHERE id=?')
                ->execute([(int)$p['amount'], now(), (int)$p['user_id']]);
            $this->audit($adminUserId, 'topup_approved', ['payment_id' => $paymentId, 'user_id' => (int)$p['user_id'], 'amount' => (int)$p['amount']]);
            $this->db->commit();
            return $this->topupRequestById($paymentId) ?: $p;
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function rejectTopup(int $paymentId, int $adminUserId, string $reason): array
    {
        $reason = trim($reason);
        if ($reason === '') throw new RuntimeException('برای رد کردن رسید باید دلیل بنویسید.');
        $this->db->beginTransaction();
        try {
            $p = $this->topupRequestById($paymentId);
            if (!$p) throw new RuntimeException('درخواست واریز پیدا نشد.');
            if ($p['status'] !== 'pending') throw new RuntimeException('این رسید قبلاً بررسی شده است.');
            $this->db->prepare('UPDATE topup_requests SET status="rejected", admin_user_id=?, reject_reason=?, updated_at=? WHERE id=? AND status="pending"')
                ->execute([$adminUserId, $reason, now(), $paymentId]);
            if (!empty($p['transaction_id'])) {
                $this->db->prepare('UPDATE transactions SET status="rejected", description=? WHERE id=?')
                    ->execute(['رد رسید واریز: ' . $reason, (int)$p['transaction_id']]);
            }
            $this->audit($adminUserId, 'topup_rejected', ['payment_id' => $paymentId, 'reason' => $reason]);
            $this->db->commit();
            return $this->topupRequestById($paymentId) ?: $p;
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function debitUserBalance(int $userId, int $amount, string $description): array
    {
        if ($amount <= 0) throw new RuntimeException('مبلغ کسر باید مثبت باشد.');
        $this->db->beginTransaction();
        try {
            $s = $this->db->prepare('SELECT balance FROM users WHERE id=? FOR UPDATE');
            $s->execute([$userId]);
            $balance = (int)$s->fetchColumn();
            if ($balance < $amount) throw new RuntimeException('موجودی کافی نیست.');
            $this->db->prepare('UPDATE users SET balance=balance-?, updated_at=? WHERE id=?')->execute([$amount, now(), $userId]);
            $this->db->prepare('INSERT INTO transactions(user_id, amount, type, status, description, created_at) VALUES(?,?,?,?,?,?)')
                ->execute([$userId, -$amount, 'debit', 'approved', $description, now()]);
            $this->audit($userId, 'balance_debit', ['amount' => $amount, 'description' => $description]);
            $this->db->commit();
            return $this->findUserById($userId) ?: [];
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function createPlan(string $title, int $months, int $price, bool $custom = false): array
    {
        if (trim($title) === '') throw new RuntimeException('عنوان پلن را وارد کنید.');
        if ($months < 1) throw new RuntimeException('مدت پلن باید حداقل ۱ ماه باشد.');
        if ($price < 0) throw new RuntimeException('قیمت نمی‌تواند منفی باشد.');
        $this->db->prepare('INSERT INTO plans(title, months, price, is_custom, status, created_at, updated_at) VALUES(?,?,?,?,"active",?,?)')
            ->execute([$title, $months, $price, $custom ? 1 : 0, now(), now()]);
        return ['id' => (int)$this->db->lastInsertId(), 'title' => $title, 'months' => $months, 'price' => $price, 'is_custom' => $custom ? 1 : 0, 'status' => 'active'];
    }

    public function getPlan(int $id): ?array
    {
        $s = $this->db->prepare('SELECT * FROM plans WHERE id=? LIMIT 1');
        $s->execute([$id]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function updatePlan(int $id, string $title, int $months, int $price, bool $custom = false, string $status = 'active'): array
    {
        $title = trim($title);
        if ($id <= 0) throw new RuntimeException('شناسه پلن معتبر نیست.');
        if ($title === '') throw new RuntimeException('عنوان پلن را وارد کنید.');
        if ($months < 1) throw new RuntimeException('مدت پلن باید حداقل ۱ ماه باشد.');
        if ($price < 0) throw new RuntimeException('قیمت نمی‌تواند منفی باشد.');
        if (!in_array($status, ['active','inactive'], true)) throw new RuntimeException('وضعیت پلن نامعتبر است.');
        if (!$this->getPlan($id)) throw new RuntimeException('پلن پیدا نشد.');
        $this->db->prepare('UPDATE plans SET title=?, months=?, price=?, is_custom=?, status=?, updated_at=? WHERE id=?')
            ->execute([$title, $months, $price, $custom ? 1 : 0, $status, now(), $id]);
        return $this->getPlan($id) ?: ['id' => $id];
    }

    public function deletePlan(int $id): array
    {
        if ($id <= 0) throw new RuntimeException('شناسه پلن معتبر نیست.');
        $plan = $this->getPlan($id);
        if (!$plan) throw new RuntimeException('پلن پیدا نشد.');

        $s = $this->db->prepare('SELECT COUNT(*) FROM bots WHERE plan_id=?');
        $s->execute([$id]);
        $used = (int)$s->fetchColumn();

        if ($used > 0) {
            // برای اینکه ربات‌ها و اشتراک‌های قبلی خراب نشوند، پلن استفاده‌شده فقط غیرفعال می‌شود.
            $this->db->prepare('UPDATE plans SET status="inactive", updated_at=? WHERE id=?')->execute([now(), $id]);
            $plan['status'] = 'inactive';
            return $this->getPlan($id) ?: $plan;
        }

        $this->db->prepare('DELETE FROM plans WHERE id=?')->execute([$id]);
        return $plan;
    }

    public function registerBot(int $actorId, int $ownerUserId, string $botTokenEnc, string $webhookSecret, array $botInfo, ?int $planId, ?int $resellerId, int $price, ?string $expiresAt): array
    {
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare('INSERT INTO bots(owner_user_id, reseller_id, bot_username, bot_name, bot_token_enc, webhook_secret, plan_id, price, status, expires_at, created_at, updated_at) VALUES(?,?,?,?,?,?,?,? ,"active",?,?,?)');
            $stmt->execute([
                $ownerUserId,
                $resellerId,
                $botInfo['username'] ?? null,
                $botInfo['first_name'] ?? null,
                $botTokenEnc,
                $webhookSecret,
                $planId,
                $price,
                $expiresAt,
                now(),
                now(),
            ]);
            $botId = (int)$this->db->lastInsertId();
            if ($expiresAt) {
                $this->db->prepare('INSERT INTO subscriptions(bot_id, user_id, plan_id, started_at, expires_at, status, created_at) VALUES(?,?,?,?,?,"active",?)')
                    ->execute([$botId, $ownerUserId, $planId, now(), $expiresAt, now()]);
            }
            $this->audit($actorId, 'register_bot', ['bot_id' => $botId, 'owner_user_id' => $ownerUserId]);
            $this->db->commit();
        } catch (Throwable $e) {
            if ($this->db->inTransaction()) $this->db->rollBack();
            throw $e;
        }
        return $this->botById($botId) ?: ['id' => $botId];
    }

    public function setBotDeployment(
        int $botId,
        string $deploymentPath,
        string $webhookUrl,
        string $childDbName,
        string $childDbUser,
        string $childDbUserHost,
        string $childDbProvider,
        string $childStoragePath
    ): void {
        if ($childDbName === '' || $childDbUser === '' || $childDbProvider === '') {
            throw new RuntimeException('اطلاعات جداسازی دیتابیس ربات کامل نیست.');
        }
        $isolationMode = $childDbProvider === 'table_namespace' ? 'table_namespace' : 'dedicated_database';
        $this->db->prepare('UPDATE bots SET deployment_path=?, webhook_url=?, child_db_name=?, child_db_user=?, child_db_user_host=?, child_db_provider=?, child_storage_path=?, isolation_mode=?, isolated_at=?, updated_at=? WHERE id=?')
            ->execute([$deploymentPath, $webhookUrl, $childDbName, $childDbUser, $childDbUserHost, $childDbProvider, $childStoragePath, $isolationMode, now(), now(), $botId]);
        $this->audit(null, 'bot_deployment_saved', [
            'bot_id' => $botId,
            'deployment_path' => $deploymentPath,
            'webhook_url' => $webhookUrl,
            'isolation_mode' => $isolationMode,
            'child_db_name' => $childDbName,
            'child_db_provider' => $childDbProvider,
            'child_storage_path' => $childStoragePath,
        ]);
    }

    public function markBotIsolated(
        int $botId,
        string $childDbName,
        string $childDbUser,
        string $childDbUserHost,
        string $childDbProvider = 'unknown',
        string $childStoragePath = ''
    ): void {
        $isolationMode = $childDbProvider === 'table_namespace' ? 'table_namespace' : 'dedicated_database';
        $this->db->prepare('UPDATE bots SET child_db_name=?, child_db_user=?, child_db_user_host=?, child_db_provider=?, child_storage_path=?, isolation_mode=?, isolated_at=?, updated_at=? WHERE id=?')
            ->execute([$childDbName, $childDbUser, $childDbUserHost, $childDbProvider, $childStoragePath, $isolationMode, now(), now(), $botId]);
    }

    public function botById(int $id): ?array
    {
        $s = $this->db->prepare('SELECT b.*, u.telegram_id owner_telegram_id, u.username owner_username, u.first_name owner_name, r.display_name reseller_name, p.title plan_title, p.months plan_months FROM bots b JOIN users u ON u.id=b.owner_user_id LEFT JOIN resellers r ON r.id=b.reseller_id LEFT JOIN plans p ON p.id=b.plan_id WHERE b.id=? LIMIT 1');
        $s->execute([$id]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function botBySecret(string $secret): ?array
    {
        $s = $this->db->prepare('SELECT * FROM bots WHERE webhook_secret=? LIMIT 1');
        $s->execute([$secret]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function updateUserStatusByTelegramId(string|int $telegramId, string $status, int $actorId = 0): array
    {
        if (!in_array($status, ['active','blocked'], true)) throw new RuntimeException('وضعیت کاربر نامعتبر است.');
        $tg = digits_only((string)$telegramId);
        if ($tg === '') throw new RuntimeException('آیدی عددی کاربر معتبر نیست.');
        $user = $this->findUserByTelegramId($tg);
        if (!$user) throw new RuntimeException('کاربر پیدا نشد.');
        $this->db->prepare('UPDATE users SET status=?, updated_at=? WHERE id=?')->execute([$status, now(), (int)$user['id']]);
        $this->audit($actorId > 0 ? $actorId : null, 'user_status', ['telegram_id' => $tg, 'status' => $status]);
        return $this->findUserById((int)$user['id']) ?: $user;
    }

    public function listUsers(int $limit = 100): array
    {
        $s = $this->db->prepare('SELECT id, telegram_id, username, first_name, last_name, role, balance, status, created_at, last_seen_at FROM users ORDER BY id DESC LIMIT ?');
        $s->bindValue(1, $limit, PDO::PARAM_INT);
        $s->execute();
        return $s->fetchAll();
    }

    public function listPlans(): array
    {
        return $this->db->query('SELECT * FROM plans ORDER BY id DESC')->fetchAll();
    }

    public function listBots(?int $userId = null, ?string $role = null, int $limit = 100): array
    {
        $cols = 'SELECT b.id,b.bot_username,b.bot_name,b.status,b.expires_at,b.created_at,b.price,b.plan_id,b.deployment_path,b.webhook_url,b.child_db_name,b.child_db_provider,b.child_storage_path,b.isolation_mode,b.isolated_at,p.title plan_title,p.months plan_months,u.telegram_id owner_telegram_id,u.username owner_username,u.first_name owner_name,r.display_name reseller_name FROM bots b JOIN users u ON u.id=b.owner_user_id LEFT JOIN resellers r ON r.id=b.reseller_id LEFT JOIN plans p ON p.id=b.plan_id ';
        if ($role === 'admin' || $userId === null) {
            $s = $this->db->prepare($cols . 'ORDER BY b.id DESC LIMIT ?');
            $s->bindValue(1, $limit, PDO::PARAM_INT);
            $s->execute();
            return $s->fetchAll();
        }
        if ($role === 'reseller') {
            $reseller = $this->resellerByUserId($userId);
            if (!$reseller) return [];
            $s = $this->db->prepare($cols . 'WHERE b.reseller_id=? OR b.owner_user_id=? ORDER BY b.id DESC LIMIT ?');
            $s->bindValue(1, (int)$reseller['id'], PDO::PARAM_INT);
            $s->bindValue(2, $userId, PDO::PARAM_INT);
            $s->bindValue(3, $limit, PDO::PARAM_INT);
            $s->execute();
            return $s->fetchAll();
        }
        $s = $this->db->prepare($cols . 'WHERE b.owner_user_id=? ORDER BY b.id DESC LIMIT ?');
        $s->bindValue(1, $userId, PDO::PARAM_INT);
        $s->bindValue(2, $limit, PDO::PARAM_INT);
        $s->execute();
        return $s->fetchAll();
    }


    public function broadcastRecipients(int $limit = 5000): array
    {
        $s = $this->db->prepare('SELECT id, telegram_id, username, first_name, last_name, role, status FROM users WHERE status="active" ORDER BY id ASC LIMIT ?');
        $s->bindValue(1, $limit, PDO::PARAM_INT);
        $s->execute();
        return $s->fetchAll();
    }

    public function deleteBotById(int $botId): ?array
    {
        $bot = $this->botById($botId);
        if (!$bot) return null;
        $this->db->beginTransaction();
        try {
            $this->db->prepare('DELETE FROM subscriptions WHERE bot_id=?')->execute([$botId]);
            $this->db->prepare('DELETE FROM bots WHERE id=?')->execute([$botId]);
            $this->db->commit();
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
        return $bot;
    }

    public function updateBotStatus(int $botId, string $status): void
    {
        if (!in_array($status, ['active','suspended','expired'], true)) throw new RuntimeException('وضعیت نامعتبر است.');
        $this->db->prepare('UPDATE bots SET status=?, updated_at=? WHERE id=?')->execute([$status, now(), $botId]);
    }

    public function setCustomSubscription(int $botId, int $userId, int $months, ?string $expiresAt, int $price): array
    {
        $expires = $expiresAt ?: date('Y-m-d H:i:s', strtotime('+' . $months . ' months'));
        $this->db->beginTransaction();
        try {
            $this->db->prepare('UPDATE bots SET expires_at=?, price=?, status="active", updated_at=? WHERE id=?')->execute([$expires, $price, now(), $botId]);
            $this->db->prepare('INSERT INTO subscriptions(bot_id,user_id,plan_id,started_at,expires_at,status,created_at) VALUES(?,?,NULL,?,?,"active",?)')
                ->execute([$botId, $userId, now(), $expires, now()]);
            $this->db->commit();
        } catch (Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
        return $this->botById($botId) ?: [];
    }

    public function stats(int $userId, string $role): array
    {
        if ($role === 'admin') {
            return [
                'users' => (int)$this->db->query('SELECT COUNT(*) FROM users')->fetchColumn(),
                'bots' => (int)$this->db->query('SELECT COUNT(*) FROM bots')->fetchColumn(),
                'active_bots' => (int)$this->db->query('SELECT COUNT(*) FROM bots WHERE status="active"')->fetchColumn(),
                'resellers' => (int)$this->db->query('SELECT COUNT(*) FROM resellers')->fetchColumn(),
                'balance' => 0,
            ];
        }
        $user = $this->findUserById($userId) ?: [];
        $bots = $this->listBots($userId, $role, 200);
        return [
            'users' => 0,
            'bots' => count($bots),
            'active_bots' => count(array_filter($bots, fn($b) => ($b['status'] ?? '') === 'active')),
            'resellers' => 0,
            'balance' => (int)($user['balance'] ?? 0),
        ];
    }

    public function setState(int $userId, string $state, ?array $payload = null): void
    {
        $json = $payload ? json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
        $this->db->prepare('INSERT INTO user_states(user_id, state, payload, updated_at) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE state=VALUES(state), payload=VALUES(payload), updated_at=VALUES(updated_at)')
            ->execute([$userId, $state, $json, now()]);
    }

    public function getState(int $userId): ?array
    {
        $s = $this->db->prepare('SELECT * FROM user_states WHERE user_id=? LIMIT 1');
        $s->execute([$userId]);
        $r = $s->fetch();
        if (!$r) return null;
        $r['payload_decoded'] = $r['payload'] ? json_decode($r['payload'], true) : null;
        return $r;
    }

    public function clearState(int $userId): void
    {
        $this->db->prepare('DELETE FROM user_states WHERE user_id=?')->execute([$userId]);
    }

    public function createTicket(int $userId, string $subject, string $message): void
    {
        $this->db->prepare('INSERT INTO support_tickets(user_id, subject, message, status, created_at) VALUES(?,?,?,"open",?)')
            ->execute([$userId, $subject, $message, now()]);
    }

    public function accountDetails(int $userId): array
    {
        $user = $this->findUserById($userId) ?: [];
        $last = $this->db->prepare('SELECT s.*, b.bot_username, b.bot_name, b.price, p.title plan_title, p.months plan_months FROM subscriptions s JOIN bots b ON b.id=s.bot_id LEFT JOIN plans p ON p.id=s.plan_id WHERE s.user_id=? ORDER BY s.created_at DESC LIMIT 1');
        $last->execute([$userId]);
        $active = $this->db->prepare('SELECT s.id subscription_id, s.started_at, s.expires_at, s.status subscription_status, b.id bot_id, b.bot_username, b.bot_name, b.status bot_status, b.price, p.title plan_title, p.months plan_months FROM subscriptions s JOIN bots b ON b.id=s.bot_id LEFT JOIN plans p ON p.id=s.plan_id WHERE s.user_id=? AND s.status="active" AND s.expires_at >= NOW() ORDER BY s.expires_at ASC');
        $active->execute([$userId]);
        return [
            'user' => $user,
            'last_purchase' => $last->fetch() ?: null,
            'active_plans' => $active->fetchAll(),
            'bots' => $this->listBots($userId, (string)($user['role'] ?? 'customer'), 100),
        ];
    }

    public function saveRequiredJoin(string $title, string $chatId, string $inviteUrl, string $type = 'channel'): array
    {
        $title = trim($title);
        $chatId = trim($chatId);
        $inviteUrl = trim($inviteUrl);
        $type = in_array($type, ['channel','group'], true) ? $type : 'channel';
        if ($title === '') throw new RuntimeException('عنوان کانال/گروه را وارد کنید.');
        if ($chatId === '') throw new RuntimeException('آیدی کانال/گروه را وارد کنید.');
        $this->db->prepare('INSERT INTO required_joins(title, chat_id, invite_url, join_type, status, created_at, updated_at) VALUES(?,?,?,?,"active",?,?)')
            ->execute([$title, $chatId, $inviteUrl, $type, now(), now()]);
        return $this->requiredJoinById((int)$this->db->lastInsertId()) ?: [];
    }

    public function requiredJoinById(int $id): ?array
    {
        $s = $this->db->prepare('SELECT * FROM required_joins WHERE id=? LIMIT 1');
        $s->execute([$id]);
        $r = $s->fetch();
        return $r ?: null;
    }

    public function listRequiredJoins(bool $activeOnly = false): array
    {
        if ($activeOnly) {
            return $this->db->query('SELECT * FROM required_joins WHERE status="active" ORDER BY id DESC')->fetchAll();
        }
        return $this->db->query('SELECT * FROM required_joins ORDER BY id DESC')->fetchAll();
    }

    public function updateRequiredJoinStatus(int $id, string $status): void
    {
        if (!in_array($status, ['active','inactive'], true)) throw new RuntimeException('وضعیت جوین اجباری نامعتبر است.');
        $this->db->prepare('UPDATE required_joins SET status=?, updated_at=? WHERE id=?')->execute([$status, now(), $id]);
    }

    public function deleteRequiredJoin(int $id): void
    {
        $this->db->prepare('DELETE FROM required_joins WHERE id=?')->execute([$id]);
    }

    public function listAuditLogs(int $limit = 80): array
    {
        $s = $this->db->prepare('SELECT a.*, u.telegram_id, u.username, u.first_name FROM audit_logs a LEFT JOIN users u ON u.id=a.actor_user_id ORDER BY a.id DESC LIMIT ?');
        $s->bindValue(1, $limit, PDO::PARAM_INT);
        $s->execute();
        return $s->fetchAll();
    }

    public function audit(?int $actorId, string $action, array $payload = []): void
    {
        $ip = $_SERVER['REMOTE_ADDR'] ?? null;
        $this->db->prepare('INSERT INTO audit_logs(actor_user_id, action, payload, ip, created_at) VALUES(?,?,?,?,?)')
            ->execute([$actorId, $action, $payload ? json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null, $ip, now()]);
    }
}
