<?php

namespace Bot\Database;

trait PaymentDbTrait
{

    public function insertAdminToLeaderPayment($chatId, $requestId, $paidAmount)
    {
        $stmt = $this->mysqli->prepare("INSERT INTO `admin_payments` (`user_id`, `request_id`, `paid_amount`, `status`) VALUES (?, ?, ?,  'paid')");
        $stmt->bind_param("iid", $chatId, $requestId, $paidAmount);
        $stmt->execute();
    }


    public function insertUserPayment($chatId, $tourId, $amount, $authority)
    {
        $stmt = $this->mysqli->prepare("INSERT INTO `user_payments` (`chat_id`, `tour_id`, `amount`, `authority`, `payment_status`) VALUES (?, ?, ?, ?, 'pending')");
        $stmt->bind_param("iids", $chatId, $tourId, $amount, $authority);
        $stmt->execute();
    }


    public function isDiscountCodeExists($code)
    {
        $stmt = $this->mysqli->prepare("SELECT id FROM discount_codes WHERE code = ?");
        $stmt->bind_param("s", $code);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->num_rows > 0;
    }


    public function getDiscountDetails($code)
    {
        $stmt = $this->mysqli->prepare("
        SELECT discount_type, discount_value, max_uses, used_count, expires_at 
        FROM discount_codes 
        WHERE code = ? 
        AND (max_uses IS NULL OR used_count < max_uses) 
        AND (expires_at IS NULL OR expires_at >= CURRENT_DATE)
    ");
        $stmt->bind_param("s", $code);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->fetch_assoc();
    }


    public function getAdminPayments()
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM admin_payments");
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }


    public function getAdminPaymentById($id)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM admin_payments WHERE id=?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->fetch_assoc();
    }


    public function getWithdrawRequests()
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM withdraw_requests");
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }


    public function savePaymentReceipt(int $requestId, string $receiptContent, string $receiptType): bool
    {
        $stmt = $this->mysqli->prepare("UPDATE withdraw_requests SET payment_receipt = ?, receipt_type = ?, status = 'approved', updated_at = NOW() WHERE id = ?");
        $stmt->bind_param("ssi", $receiptContent, $receiptType, $requestId);
        $success = $stmt->execute();
        if (!$success) {
            error_log("Failed to save payment receipt: " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }


    public function createWalletTransaction(int $userId, float $amount, string $currency, bool $isWithdrawable, string $description, ?int $tourId = null, bool $byAdmin = false): array
    {
        $sql = "INSERT INTO wallet_transactions (user_id, amount, currency, status, is_withdrawable, description, tour_id, by_admin, created_at, updated_at)
            VALUES (?, ?, ?, 'available', ?, ?, ?, ?, NOW(), NOW())";

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            // به جای error_log از Logger استفاده می‌کنیم تا با بقیه کد یکسان باشد
            //   Logger::log("ERROR", "createWalletTransaction: Prepare failed.", '', ['error' => $this->mysqli->error]);
            return ['status' => false, 'message' => 'خطا در آماده‌سازی ثبت تراکنش.'];
        }

        $isWithdrawableInt = (int)$isWithdrawable;
        $byAdminInt = (int)$byAdmin;
        $currencyEnum = ($currency === 'toman' || $currency === 'star') ? $currency : 'toman';

        $stmt->bind_param("idsisii", $userId, $amount, $currencyEnum, $isWithdrawableInt, $description, $projectId, $byAdminInt);

        if ($stmt->execute()) {
            $transactionId = $stmt->insert_id;
            $stmt->close();
            return ['status' => true, 'message' => 'تراکنش با موفقیت ثبت شد.', 'transaction_id' => $transactionId];
        } else {
            $error = $stmt->error;
            $stmt->close();
            //  Logger::log("ERROR", "createWalletTransaction: Execute failed.", '', ['error' => $error]);
            return ['status' => false, 'message' => "خطا در ثبت تراکنش: $error"];
        }
    }


    public function getWithdrawRequestById($id)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM withdraw_requests WHERE id=?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();

        return $result->fetch_assoc();
    }


    public function saveDiscountCode($code, $type, $value, $maxUses = null, $expiresAt = null)
    {
        $stmt = $this->mysqli->prepare("
        INSERT INTO discount_codes (code, discount_type, discount_value, max_uses, expires_at) 
        VALUES (?, ?, ?, ?, ?)
    ");
        $stmt->bind_param("ssiii", $code, $type, $value, $maxUses, $expiresAt);

        if ($stmt->execute()) {
            return true;
        } else {
            error_log("Error saving discount code: " . $stmt->error);
            return false;
        }
    }


    public function getAllDiscountCodes()
    {
        $stmt = $this->mysqli->prepare("SELECT id, code, discount_type, discount_value, max_uses, used_count, expires_at FROM discount_codes");
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }


    public function deleteDiscountCode($code)
    {
        $stmt = $this->mysqli->prepare("DELETE FROM discount_codes WHERE code = ?");
        $stmt->bind_param("s", $code);
        return $stmt->execute();
    }


    public function incrementDiscountUsage($code): bool
    {
        $stmt = $this->mysqli->prepare("
        UPDATE `discount_codes` 
        SET `used_count` = `used_count` + 1 
        WHERE `code` = ? 
    ");

        if (!$stmt) {
            error_log("❌ Database Error (incrementDiscountUsage): " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("s", $code);
        $success = $stmt->execute();

        if (!$success) {
            error_log("❌ Query Execution Failed (incrementDiscountUsage): " . $stmt->error);
        }

        $stmt->close();

        return $success;
    }


    public function getUserPaymentByAuthority($authority)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM `user_payments` WHERE `authority` = ?");
        $stmt->bind_param("s", $authority);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }


    public function updateUserPaymentStatus($authority, $status, $refId = null)
    {
        $stmt = $this->mysqli->prepare("UPDATE `user_payments` SET `payment_status` = ?, `ref_id` = ? WHERE `authority` = ?");
        $stmt->bind_param("sss", $status, $refId, $authority);
        $stmt->execute();
    }


    public function updateTourPaymentStatus($chatId, $tourId, $status, $refId, $selectedVehicle, $selectedPrice = null, $quantity = 1): bool
    {
        $stmt = $this->mysqli->prepare("
        UPDATE `registers` 
        SET `payment_status` = ?, `payment_ref_id` = ?, `vehicle` = ?, `total_amount` = ?, `quantity` = ?, `updated_at` = CURRENT_TIMESTAMP 
        WHERE `explorer_id` = ? AND `tour_id` = ?
        ");

        if (!$stmt) {
            error_log("❌ Database Error: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("sssiiii", $status, $refId, $selectedVehicle, $selectedPrice, $quantity, $chatId, $tourId);
        $success = $stmt->execute();
        $stmt->close();

        return $success;
    }


    public function insertUserPaymentStar($chatId, $tourId, $amount, $refId): bool
    {
        $currency = "XTR";
        $paymentStatus = "completed";
        $stmt = $this->mysqli->prepare("
        INSERT INTO `user_payments` (`chat_id`, `tour_id`, `amount`, `ref_id`, `payment_status`, `currency`, `created_at`, `updated_at`) 
        VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    ");

        if (!$stmt) {
            error_log("❌ Database Error (insertUserPaymentStar): " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("iidsss", $chatId, $tourId, $amount, $refId, $paymentStatus, $currency);
        $success = $stmt->execute();

        if (!$success) {
            error_log("❌ Query Execution Failed (insertUserPaymentStar): " . $stmt->error);
        }

        $stmt->close();

        return $success;
    }


    public function createWithdrawRequest($userId, $paymentDetail, $amount, $currency)
    {
        $query = "INSERT INTO `withdraw_requests` (`user_id`, `payment_detail`, `amount`, `currency`, `status`, `created_at`, `updated_at`)
                  VALUES (?, ?, ?, ?, 'pending', NOW(), NOW())";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare withdraw request statement: " . $this->mysqli->error);
            return false;
        }
        $stmt->bind_param("isds", $userId, $paymentDetail, $amount, $currency);
        if (!$stmt->execute()) {
            error_log("Failed to execute withdraw request statement: " . $stmt->error);
            $stmt->close();
            return false;
        }
        $insertedId = $this->mysqli->insert_id;
        $stmt->close();
        return $insertedId;
    }


    public function updateWithdrawRequestStatus(int $id, string $status): bool
    {
        $stmt = $this->mysqli->prepare("UPDATE withdraw_requests SET status = ?, updated_at = NOW() WHERE id = ?");
        $stmt->bind_param("si", $status, $id);
        $success = $stmt->execute();
        $stmt->close();
        return $success;
    }


    public function addWalletTransactionByAdmin($userId, $amount, $currency = 'toman', $status = 'approved', $description = null, $byAdmin = true)
    {
        $query = "
        INSERT INTO `wallet_transactions` (`user_id`, `amount`, `currency`, `status`, `description`, `created_at`, `by_admin`)
        VALUES (?, ?, ?, ?, ?, NOW(), ?)
    ";

        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare insert wallet transaction: " . $this->mysqli->error);
            return false;
        }

        $adminFlag = $byAdmin ? 1 : 0;

        $stmt->bind_param("idsssi", $userId, $amount, $currency, $status, $description, $adminFlag);

        $result = $stmt->execute();
        if (!$result) {
            error_log("Failed to execute insert wallet transaction: " . $stmt->error);
        }

        $stmt->close();
        return $result;
    }


    public function AdminWalletInfo()
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM `wallet_transactions` WHERE by_admin=1");
        if (!$stmt) {
            error_log("❌ Failed to prepare AdminWalletInfo statement: " . $this->mysqli->error);
            return [];
        }

        if (!$stmt->execute()) {
            error_log("❌ Failed to execute AdminWalletInfo statement: " . $stmt->error);
            return [];
        }

        $result = $stmt->get_result();
        $stmt->close();
        return $result;
    }


    public function getAdminTotalAmount()
    {
        $stmt = $this->mysqli->prepare("SELECT 
    SUM(
        CASE 
            WHEN currency = 'toman' AND amount > 0 AND by_admin = 1 
            THEN amount 
            ELSE 0 
        END
    ) AS admin_amount
FROM wallet_transactions;
");

        if (!$stmt) {
            error_log("Failed to prepare getAdminTotalAmount query: " . $this->mysqli->error);
            return null;
        }


        if (!$stmt->execute()) {
            error_log("Failed to execute getAdminTotalAmount query: " . $stmt->error);
            $stmt->close();
            return null;
        }

        $result = $stmt->get_result();

        $stmt->close();
        return $result;
    }


    public function getLeaderPaymentSettings($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT gateway_zarinpal, gateway_card, gateway_crypto, gateway_stars, gateway_wallet, card_number, sheba, card_owner_name FROM explorers WHERE chat_id = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $result;
    }


    public function updateLeaderPaymentSettings($chatId, array $settings): bool
    {
        $stmt = $this->mysqli->prepare("UPDATE explorers SET 
            gateway_zarinpal = ?, 
            gateway_card = ?, 
            gateway_crypto = ?, 
            gateway_stars = ?, 
            gateway_wallet = ?, 
            card_number = ?, 
            sheba = ?, 
            card_owner_name = ? 
            WHERE chat_id = ?");
        $stmt->bind_param("iiiiisssi", 
            $settings['gateway_zarinpal'], 
            $settings['gateway_card'], 
            $settings['gateway_crypto'], 
            $settings['gateway_stars'], 
            $settings['gateway_wallet'], 
            $settings['card_number'], 
            $settings['sheba'], 
            $settings['card_owner_name'], 
            $chatId
        );
        $success = $stmt->execute();
        $stmt->close();
        return $success;
    }


    public function getWalletBalance($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT available_toman, available_star FROM wallets WHERE user_id = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $wallet = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if (!$wallet) {
            $stmt = $this->mysqli->prepare("INSERT INTO wallets (user_id, available_toman, available_star) VALUES (?, 0, 0)");
            $stmt->bind_param("i", $chatId);
            $stmt->execute();
            $stmt->close();
            return ['available_toman' => 0.00, 'available_star' => 0];
        }
        return $wallet;
    }


    public function updateWalletBalance($chatId, $amount, $currency = 'toman'): bool
    {
        $wallet = $this->getWalletBalance($chatId);
        if ($currency === 'star') {
            $newVal = intval($wallet['available_star'] + $amount);
            $stmt = $this->mysqli->prepare("UPDATE wallets SET available_star = ? WHERE user_id = ?");
            $stmt->bind_param("ii", $newVal, $chatId);
        } else {
            $newVal = floatval($wallet['available_toman'] + $amount);
            $stmt = $this->mysqli->prepare("UPDATE wallets SET available_toman = ? WHERE user_id = ?");
            $stmt->bind_param("di", $newVal, $chatId);
        }
        $success = $stmt->execute();
        $stmt->close();
        return $success;
    }


    public function addWalletTransaction($chatId, $amount, $currency, $status, $isWithdrawable = true, $description = '', $tourId = null, $byAdmin = false): bool
    {
        $stmt = $this->mysqli->prepare("INSERT INTO wallet_transactions (user_id, amount, currency, status, is_withdrawable, description, tour_id, by_admin) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $isW = $isWithdrawable ? 1 : 0;
        $byA = $byAdmin ? 1 : 0;
        $stmt->bind_param("idssisii", $chatId, $amount, $currency, $status, $isW, $description, $tourId, $byA);
        $success = $stmt->execute();
        $stmt->close();
        
        if ($success && $status === 'completed') {
            $this->updateWalletBalance($chatId, $amount, $currency);
        }
        return $success;
    }


    public function getExplorerStats($chatId): array
    {
        $stmt = $this->mysqli->prepare("SELECT r.*, t.title as tour_title, t.start_date FROM registers r JOIN tours t ON r.tour_id = t.id WHERE r.chat_id = ?");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $regs = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        $stmt = $this->mysqli->prepare("SELECT * FROM wallet_transactions WHERE user_id = ? ORDER BY id DESC");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $txs = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return [
            'registrations' => $regs,
            'transactions' => $txs
        ];
    }


    public function getAllLeadersStats(): array
    {
        $query = "SELECT e.chat_id, e.first_name, e.last_name, e.phone, 
                  IFNULL((SELECT SUM(amount) FROM wallet_transactions WHERE user_id = e.chat_id AND status='completed'), 0) as total_income,
                  (SELECT COUNT(*) FROM leader_chats WHERE leader_id = e.chat_id) as groups_count
                  FROM explorers e WHERE e.role_id = 17";
        $res = $this->mysqli->query($query);
        return $res->fetch_all(MYSQLI_ASSOC);
    }


    public function getLeaderCardName($leader_id)
    {
        $stmt = $this->mysqli->prepare("SELECT card_owner_name FROM explorers WHERE  chat_id = ? LIMIT 1");
        $stmt->bind_param("i", $leader_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();

        return $user['card_owner_name'];
    }


    public function getLeaderCard($leader_id)
    {
        $stmt = $this->mysqli->prepare("SELECT card_number FROM explorers WHERE  chat_id = ? LIMIT 1");
        $stmt->bind_param("i", $leader_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();

        return $user['card_number'];
    }


    public function getLeaderSheba($leader_id)
    {
        $stmt = $this->mysqli->prepare("SELECT sheba FROM explorers WHERE  chat_id = ? LIMIT 1");
        $stmt->bind_param("i", $leader_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();

        return $user['sheba'];
    }


    public function getPaymentsByUserId($userId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM user_payments WHERE chat_id = ? ");


        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getPaymentsByUserId: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $userId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in getPaymentsByUserId: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $payments = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $payments;
    }


    public function getPayDetailsById($payId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT *
        FROM user_payments 
        WHERE id = ?
    ");
        $stmt->bind_param("i", $payId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result ?: null;
    }


    public function getPaymentDetailsById($payId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT *
        FROM registers 
        WHERE id = ?
    ");
        $stmt->bind_param("i", $payId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result ?: null;
    }


    public function getPaymentsByLeaderId($leaderId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM registers WHERE chat_id = ? ");


        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getPaymentsByLeaderId: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $leaderId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in getPaymentsByLeaderId: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $payments = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $payments;
    }


    public function getPaymentDetailsByUserId($userId, $tourId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT *
        FROM registers 
        WHERE explorer_id = ? AND tour_id=?
    ");
        $stmt->bind_param("ii", $userId, $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result ?: null;
    }


    public function getOnlinePayment($userId, $tourId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM user_payments WHERE chat_id = ? AND tour_id=?");


        $stmt->bind_param("ii", $userId, $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result ?: null;
    }


    public function getOnlinePayments($tourId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM user_payments WHERE  tour_id=?");

        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getOnlinePayments: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $tourId);
        $stmt->execute();

        $result = $stmt->get_result();
        $payments = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $payments;
    }


    public function getCardToCardPayments($tourId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM registers WHERE tour_id=? AND (payment_ref_id='card_to_card' OR payment_ref_id='card_to_card_admin')");


        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getCardToCardPayments: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $tourId);
        $stmt->execute();

        $result = $stmt->get_result();
        $payments = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $payments;
    }


    public function setUserStatus(int $chatId, bool $status): bool
    {
        $isUser = $status ? 1 : 0;
        $stmt = $this->mysqli->prepare("UPDATE users SET is_registered = ? WHERE chat_id = ?");
        $stmt->bind_param("ii", $isUser, $chatId);
        return $stmt->execute();
    }


    public function updateLeaderConfirm(int $chatId): bool
    {

        $stmt = $this->mysqli->prepare("UPDATE users SET confirm_leader = 1 WHERE chat_id = ?");
        $stmt->bind_param("i", $chatId);
        return $stmt->execute();
    }


    public function getUserWallet($userId)
    {
        $query = "
        SELECT 
            SUM(CASE WHEN `currency` = 'toman' AND `status` = 'available' THEN `amount` ELSE 0 END) AS `available_toman`,
            SUM(CASE WHEN `currency` = 'toman' AND `status` = 'pending' THEN `amount` ELSE 0 END) AS `pending_toman`,
            SUM(CASE WHEN `currency` = 'star' AND `status` = 'available' THEN `amount` ELSE 0 END) AS `available_star`,
            SUM(CASE WHEN `currency` = 'star' AND `status` = 'pending' THEN `amount` ELSE 0 END) AS `pending_star`
        FROM `wallet_transactions`
        WHERE `user_id` = ?
    ";

        error_log("Executing wallet query for user ID: " . $userId);

        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare wallet query: " . $this->mysqli->error);
            return null;
        }

        $stmt->bind_param("i", $userId);

        if (!$stmt->execute()) {
            error_log("Failed to execute wallet query: " . $stmt->error);
            $stmt->close();
            return null;
        }

        $result = $stmt->get_result();
        $walletData = $result->fetch_assoc();
        error_log("Wallet Data: " . json_encode($walletData));

        $stmt->close();
        return $walletData;
    }


    public function finalizeWalletTransaction($userId, $tourId)
    {
        if (!$this->mysqli || !$this->mysqli->ping()) {
            error_log("Database mysqliection is not available.");
            return false;
        }
        $query = "UPDATE `wallet_transactions` 
              SET `status` = 'available', 
                  `updated_at` = NOW() 
              WHERE `user_id` = ? AND `tour_id` = ? AND `status` = 'pending'";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare wallet transaction update statement: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("ii", $userId, $tourId);

        if (!$stmt->execute()) {
            error_log("Failed to execute wallet transaction update statement: " . $stmt->error);
            $stmt->close();
            return false;
        }

        $stmt->close();
        return true;
    }


    public function markUserWithdrawalsAsWithdrawn($userId)
    {
        $stmt = $this->mysqli->prepare("UPDATE wallet_transactions SET status = 'withdrawn', updated_at = NOW() WHERE user_id = ? ");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $stmt->close();
    }


    public function getTotalBalance(int $userId, string $currency = 'toman'): float
    {
        $sql = "SELECT SUM(amount) as total FROM wallet_transactions
            WHERE user_id = ? AND currency = ? AND status = 'available'";

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) return 0.0;

        $stmt->bind_param("is", $userId, $currency);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return (float)($result['total'] ?? 0.0);
    }


    public function getWithdrawableBalance(int $userId, string $currency = 'toman'): float
    {
        // کوئری اصلاح شده است تا هزینه‌ها (مقادیر منفی) را نیز در محاسبه لحاظ کند
        $sql = "SELECT SUM(amount) as total FROM wallet_transactions
            WHERE user_id = ? AND currency = ? AND status = 'available' 
            AND (is_withdrawable = 1 OR amount < 0)"; // <-- تغییر کلیدی اینجاست

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            return 0.0;
        }

        $stmt->bind_param("is", $userId, $currency); // 'i' for integer, 's' for string
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return (float)($result['total'] ?? 0.0);
    }


    public function getNonWithdrawableBalance(int $userId, string $currency = 'toman'): float
    {
        // این کوئری فقط تراکنش‌های مثبت (درآمد) که غیرقابل برداشت هستند را جمع می‌زند
        $sql = "SELECT SUM(amount) as total FROM wallet_transactions
            WHERE user_id = ? 
              AND currency = ? 
              AND status = 'available' 
              AND is_withdrawable = 0 
              AND amount > 0";

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            return 0.0;
        }

        $stmt->bind_param("is", $userId, $currency);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return (float)($result['total'] ?? 0.0);
    }


    public function getPendingBalance($userId, $currency)
    {
        $stmt = $this->mysqli->prepare("SELECT SUM(amount) as total FROM wallet_transactions WHERE user_id = ? AND currency = ? AND status = 'pending'");
        if (!$stmt) {
            error_log("Failed to prepare getPendingBalance: " . $this->mysqli->error);
            return 0.0;
        }
        $stmt->bind_param("is", $userId, $currency);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        return (float) ($result['total'] ?? 0);
    }


    public function getPendingWithdrawRequest(int $userId): ?array
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM withdraw_requests WHERE user_id = ? AND status IN ('pending', 'processing') LIMIT 1");
        if (!$stmt) {
            error_log("Failed to prepare getPendingWithdrawRequest: " . $this->mysqli->error);
            return null;
        }
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $request = $result->fetch_assoc();
        $stmt->close();
        return $request ?: null;
    }


    public function getLastTransaction(int $userId): ?array
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM wallet_transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT 1");
        if (!$stmt) {
            error_log("Failed to prepare getLastTransaction: " . $this->mysqli->error);
            return null;
        }
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $transaction = $result->fetch_assoc();
        $stmt->close();
        return $transaction ?: null;
    }

}
