jweb/ac1/src/api/main_account.php

<?php
require_once '../../db.php';

class MainAccount {
    private $conn;
    private $mainAccountId = 1;

    public function __construct() {
        $database = new Database();
        $this->conn = $database->getConnection();
        $this->initializeMainAccount();
    }

    // Initialize main account if it doesn't exist
    private function initializeMainAccount() {
        $query = "SELECT COUNT(*) as count FROM main_account";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($result['count'] == 0) {
            $query = "INSERT INTO main_account 
                     (paybill_number, account_number, created_at) 
                     VALUES ('542542', '00106664176150', NOW())";
            $this->conn->exec($query);
            $this->mainAccountId = $this->conn->lastInsertId();
        }
    }

    // Get main account details
    public function getMainAccount() {
        $query = "SELECT * FROM main_account WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":id", $this->mainAccountId);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Update main account balance
    public function updateMainAccountBalance($amount, $type = 'deposit') {
        try {
            $this->conn->beginTransaction();

            // Validate amount
            $amount = $this->validateAmount($amount);

            // Get current balance
            $mainAccount = $this->getMainAccount();
            $currentBalance = $mainAccount['total_balance'];
            
            if ($type === 'withdrawal' && $currentBalance < $amount) {
                throw new Exception("Insufficient funds in main account");
            }

            $newBalance = $type === 'deposit' 
                ? $currentBalance + $amount 
                : $currentBalance - $amount;

            // Update main account
            $query = "UPDATE main_account SET 
                      total_balance = :balance,
                      total_deposits = total_deposits + :deposits,
                      total_withdrawals = total_withdrawals + :withdrawals,
                      updated_at = NOW()
                      WHERE id = :id";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":balance", $newBalance);
            
            $depositAmount = $type === 'deposit' ? $amount : 0;
            $withdrawalAmount = $type === 'withdrawal' ? $amount : 0;
            
            $stmt->bindParam(":deposits", $depositAmount);
            $stmt->bindParam(":withdrawals", $withdrawalAmount);
            $stmt->bindParam(":id", $this->mainAccountId);
            
            $stmt->execute();
            $this->conn->commit();
            
            // Log the transaction
            $this->logTransaction('main_account_update', [
                'type' => $type,
                'amount' => $amount,
                'previous_balance' => $currentBalance,
                'new_balance' => $newBalance
            ]);

            return [
                'success' => true,
                'previous_balance' => $currentBalance,
                'new_balance' => $newBalance
            ];
        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Get user virtual account
    public function getUserAccount($userId) {
        $this->validateUser($userId);
        
        $query = "SELECT * FROM user_accounts WHERE user_id = :user_id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $userId);
        $stmt->execute();
        
        $account = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Create virtual account if doesn't exist
        if (!$account) {
            return $this->createUserAccount($userId);
        }
        
        return $account;
    }

    // Create virtual account for user
    private function createUserAccount($userId) {
        $virtualAccountNumber = 'JM' . str_pad($userId, 8, '0', STR_PAD_LEFT);
        
        $query = "INSERT INTO user_accounts (user_id, virtual_account_number) 
                  VALUES (:user_id, :account_number)";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $userId);
        $stmt->bindParam(":account_number", $virtualAccountNumber);
        
        if ($stmt->execute()) {
            $this->logTransaction('user_account_created', [
                'user_id' => $userId,
                'virtual_account' => $virtualAccountNumber
            ], $userId);
            
            return $this->getUserAccount($userId);
        }
        
        return false;
    }

    // Update user virtual account balance
    public function updateUserAccount($userId, $amount, $type = 'deposit') {
        try {
            $this->conn->beginTransaction();

            $amount = $this->validateAmount($amount);
            $userAccount = $this->getUserAccount($userId);
            $currentBalance = $userAccount['current_balance'];
            
            if ($type === 'withdrawal' && $currentBalance < $amount) {
                throw new Exception("Insufficient funds in user account");
            }

            if ($type === 'deposit') {
                $newBalance = $currentBalance + $amount;
                $updateField = "total_invested = total_invested + :amount";
            } else {
                $newBalance = $currentBalance - $amount;
                $updateField = "total_withdrawn = total_withdrawn + :amount";
            }

            $query = "UPDATE user_accounts SET 
                      current_balance = :balance,
                      {$updateField},
                      updated_at = NOW()
                      WHERE user_id = :user_id";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":balance", $newBalance);
            $stmt->bindParam(":amount", $amount);
            $stmt->bindParam(":user_id", $userId);
            
            $stmt->execute();
            $this->conn->commit();
            
            $this->logTransaction('user_account_update', [
                'type' => $type,
                'amount' => $amount,
                'previous_balance' => $currentBalance,
                'new_balance' => $newBalance
            ], $userId);
            
            return [
                'success' => true,
                'previous_balance' => $currentBalance,
                'new_balance' => $newBalance,
                'virtual_account' => $userAccount['virtual_account_number']
            ];
        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Record pending transaction (STK Push)
    public function recordPendingTransaction($userID, $amount, $phoneNumber, $checkoutRequestID) {
        try {
            // Check rate limiting
            if (!$this->checkRateLimit($userID, 'stk_push')) {
                throw new Exception("Too many attempts. Please try again later.");
            }

            // Check for duplicate transactions
            if ($this->checkDuplicateTransaction($userID, $amount, $phoneNumber)) {
                throw new Exception("Duplicate transaction detected. Please wait before retrying.");
            }

            $amount = $this->validateAmount($amount);
            $this->validateUser($userID);

            $transactionID = 'JM' . date('YmdHis') . rand(1000, 9999);
            
            // Record in recharge_transactions as pending
            $query = "INSERT INTO recharge_transactions 
                      (user_id, main_account_id, amount, phone_number, 
                       transaction_id, checkout_request_id, status, payment_method) 
                      VALUES 
                      (:user_id, :main_account_id, :amount, :phone_number, 
                       :transaction_id, :checkout_request_id, 'pending', 'M-Pesa STK Push')";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $userID);
            $stmt->bindParam(":main_account_id", $this->mainAccountId);
            $stmt->bindParam(":amount", $amount);
            $stmt->bindParam(":phone_number", $phoneNumber);
            $stmt->bindParam(":transaction_id", $transactionID);
            $stmt->bindParam(":checkout_request_id", $checkoutRequestID);

            if ($stmt->execute()) {
                $this->logTransaction('pending_transaction_created', [
                    'amount' => $amount,
                    'phone_number' => $phoneNumber,
                    'checkout_request_id' => $checkoutRequestID,
                    'transaction_id' => $transactionID
                ], $userID);
                
                return $transactionID;
            }
            
            return false;
        } catch (Exception $e) {
            error_log("Pending transaction error: " . $e->getMessage());
            return false;
        }
    }

    // Process recharge/deposit
    public function processDeposit($userId, $amount, $paymentData) {
        try {
            $this->conn->beginTransaction();

            $amount = $this->validateAmount($amount);
            $this->validateUser($userId);

            // Get current balances
            $mainAccount = $this->getMainAccount();
            $userAccount = $this->getUserAccount($userId);

            // Calculate bonus
            $bonus = $this->calculateBonus($amount);
            $totalAmount = $amount + $bonus;

            // Update main account
            $mainUpdate = $this->updateMainAccountBalance($amount, 'deposit');
            if (!$mainUpdate['success']) {
                throw new Exception("Failed to update main account: " . $mainUpdate['error']);
            }

            // Update user account
            $userUpdate = $this->updateUserAccount($userId, $totalAmount, 'deposit');
            if (!$userUpdate['success']) {
                throw new Exception("Failed to update user account: " . $userUpdate['error']);
            }

            // Record transaction
            $transactionId = $this->recordTransaction([
                'user_id' => $userId,
                'amount' => $amount,
                'bonus_amount' => $bonus,
                'virtual_balance_before' => $userAccount['current_balance'],
                'virtual_balance_after' => $userUpdate['new_balance'],
                'main_balance_before' => $mainAccount['total_balance'],
                'main_balance_after' => $mainUpdate['new_balance'],
                'payment_method' => $paymentData['method'],
                'phone_number' => $paymentData['phone'],
                'mpesa_receipt' => $paymentData['receipt'] ?? null,
                'transaction_id' => $paymentData['transaction_id'] ?? null,
                'checkout_request_id' => $paymentData['checkout_request_id'] ?? null,
                'status' => 'completed'
            ]);

            $this->conn->commit();

            $this->logTransaction('deposit_processed', [
                'amount' => $amount,
                'bonus' => $bonus,
                'total_amount' => $totalAmount,
                'transaction_id' => $transactionId
            ], $userId);

            return [
                'success' => true,
                'transaction_id' => $transactionId,
                'user_balance' => $userUpdate['new_balance'],
                'bonus_received' => $bonus,
                'virtual_account' => $userUpdate['virtual_account'],
                'main_account_balance' => $mainUpdate['new_balance']
            ];

        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Record transaction
    private function recordTransaction($data) {
        $query = "INSERT INTO recharge_transactions 
                  (user_id, main_account_id, amount, bonus_amount, 
                   virtual_balance_before, virtual_balance_after,
                   main_balance_before, main_balance_after,
                   payment_method, phone_number, mpesa_receipt, 
                   transaction_id, checkout_request_id, paybill_number, account_number, status) 
                  VALUES 
                  (:user_id, :main_account_id, :amount, :bonus_amount,
                   :virtual_before, :virtual_after,
                   :main_before, :main_after,
                   :payment_method, :phone_number, :mpesa_receipt,
                   :transaction_id, :checkout_request_id, :paybill, :account, :status)";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $data['user_id']);
        $stmt->bindParam(":main_account_id", $this->mainAccountId);
        $stmt->bindParam(":amount", $data['amount']);
        $stmt->bindParam(":bonus_amount", $data['bonus_amount']);
        $stmt->bindParam(":virtual_before", $data['virtual_balance_before']);
        $stmt->bindParam(":virtual_after", $data['virtual_balance_after']);
        $stmt->bindParam(":main_before", $data['main_balance_before']);
        $stmt->bindParam(":main_after", $data['main_balance_after']);
        $stmt->bindParam(":payment_method", $data['payment_method']);
        $stmt->bindParam(":phone_number", $data['phone_number']);
        $stmt->bindParam(":mpesa_receipt", $data['mpesa_receipt']);
        $stmt->bindParam(":transaction_id", $data['transaction_id']);
        $stmt->bindParam(":checkout_request_id", $data['checkout_request_id'] ?? null);
        $stmt->bindParam(":paybill", $data['paybill_number'] ?? '542542');
        $stmt->bindParam(":account", $data['account_number'] ?? '00106664176150');
        $stmt->bindParam(":status", $data['status']);

        if ($stmt->execute()) {
            return $this->conn->lastInsertId();
        }
        
        return false;
    }

    // Calculate bonus based on amount
    private function calculateBonus($amount) {
        $bonuses = [
            500 => 5,
            1000 => 15,
            2000 => 40,
            5000 => 120,
            10000 => 300,
            20000 => 700,
            50000 => 2000,
            100000 => 5000
        ];
        
        // Find the closest package bonus
        $closestAmount = 0;
        $minDifference = PHP_INT_MAX;
        
        foreach ($bonuses as $packageAmount => $bonus) {
            $difference = abs($packageAmount - $amount);
            if ($difference < $minDifference) {
                $minDifference = $difference;
                $closestAmount = $packageAmount;
            }
        }
        
        return $bonuses[$closestAmount] ?? 0;
    }

    // Update transaction status from M-Pesa callback
    public function updateTransactionStatus($checkoutRequestID, $status, $mpesaReceipt = null) {
        try {
            $this->conn->beginTransaction();

            // Get transaction details
            $query = "SELECT * FROM recharge_transactions 
                     WHERE checkout_request_id = :checkout_id AND status = 'pending'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":checkout_id", $checkoutRequestID);
            $stmt->execute();
            $transaction = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$transaction) {
                throw new Exception("Transaction not found");
            }

            // Update transaction status
            $updateQuery = "UPDATE recharge_transactions SET 
                           status = :status, 
                           mpesa_receipt = :receipt,
                           updated_at = NOW()
                           WHERE checkout_request_id = :checkout_id";
            
            $updateStmt = $this->conn->prepare($updateQuery);
            $updateStmt->bindParam(":status", $status);
            $updateStmt->bindParam(":receipt", $mpesaReceipt);
            $updateStmt->bindParam(":checkout_id", $checkoutRequestID);
            $updateStmt->execute();

            // If completed, process the deposit
            if ($status === 'completed') {
                $paymentData = [
                    'method' => 'M-Pesa STK Push',
                    'phone' => $transaction['phone_number'],
                    'receipt' => $mpesaReceipt,
                    'transaction_id' => $transaction['transaction_id'],
                    'checkout_request_id' => $checkoutRequestID
                ];

                $depositResult = $this->processDeposit($transaction['user_id'], $transaction['amount'], $paymentData);

                if (!$depositResult['success']) {
                    throw new Exception($depositResult['error']);
                }
            }

            $this->conn->commit();

            $this->logTransaction('transaction_status_updated', [
                'checkout_request_id' => $checkoutRequestID,
                'status' => $status,
                'mpesa_receipt' => $mpesaReceipt
            ], $transaction['user_id']);

            return ['success' => true];

        } catch (Exception $e) {
            $this->conn->rollBack();
            error_log("Error updating transaction status: " . $e->getMessage());
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Submit manual payment for admin verification
    public function submitManualPayment($user_id, $amount, $phone_number, $mpesa_code, $screenshot) {
        try {
            $this->conn->beginTransaction();

            $amount = $this->validateAmount($amount);
            $this->validateUser($user_id);

            // First record in recharge_transactions as pending
            $transactionId = $this->recordTransaction([
                'user_id' => $user_id,
                'amount' => $amount,
                'bonus_amount' => 0,
                'virtual_balance_before' => 0,
                'virtual_balance_after' => 0,
                'main_balance_before' => 0,
                'main_balance_after' => 0,
                'payment_method' => 'Manual Verification',
                'phone_number' => $phone_number,
                'mpesa_receipt' => $mpesa_code,
                'transaction_id' => 'MANUAL_' . time() . '_' . $user_id,
                'status' => 'pending'
            ]);

            // Then record in admin_payments for verification
            $query = "INSERT INTO admin_payments 
                      (user_id, amount, mpesa_code, phone_number, screenshot_path) 
                      VALUES 
                      (:user_id, :amount, :mpesa_code, :phone_number, :screenshot)";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $user_id);
            $stmt->bindParam(":amount", $amount);
            $stmt->bindParam(":mpesa_code", $mpesa_code);
            $stmt->bindParam(":phone_number", $phone_number);
            $stmt->bindParam(":screenshot", $screenshot);

            if ($stmt->execute()) {
                $this->conn->commit();
                
                $this->logTransaction('manual_payment_submitted', [
                    'amount' => $amount,
                    'mpesa_code' => $mpesa_code,
                    'transaction_id' => $transactionId
                ], $user_id);
                
                return [
                    'success' => true,
                    'payment_id' => $this->conn->lastInsertId(),
                    'transaction_id' => $transactionId
                ];
            } else {
                $this->conn->rollBack();
                return ['success' => false, 'error' => 'Failed to submit payment'];
            }

        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Verify payment and update balances
    public function verifyPayment($payment_id, $admin_id, $notes = '') {
        try {
            $this->conn->beginTransaction();

            // Get payment details
            $query = "SELECT ap.*, rt.id as transaction_id 
                      FROM admin_payments ap 
                      LEFT JOIN recharge_transactions rt ON ap.user_id = rt.user_id 
                      AND ap.amount = rt.amount 
                      AND rt.status = 'pending'
                      WHERE ap.id = :payment_id";
            
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":payment_id", $payment_id);
            $stmt->execute();
            $payment = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$payment) {
                throw new Exception("Payment not found");
            }

            // Process the deposit
            $paymentData = [
                'method' => 'Manual Verification',
                'phone' => $payment['phone_number'],
                'receipt' => $payment['mpesa_code'],
                'transaction_id' => 'MANUAL_VERIFIED_' . $payment_id
            ];

            $depositResult = $this->processDeposit($payment['user_id'], $payment['amount'], $paymentData);

            if (!$depositResult['success']) {
                throw new Exception($depositResult['error']);
            }

            // Update admin_payments status
            $updateQuery = "UPDATE admin_payments SET 
                           status = 'verified', 
                           verified_by = :admin_id, 
                           verified_at = NOW(),
                           notes = :notes 
                           WHERE id = :payment_id";
            
            $updateStmt = $this->conn->prepare($updateQuery);
            $updateStmt->bindParam(":admin_id", $admin_id);
            $updateStmt->bindParam(":notes", $notes);
            $updateStmt->bindParam(":payment_id", $payment_id);
            $updateStmt->execute();

            // Update recharge_transactions status if exists
            if ($payment['transaction_id']) {
                $updateTxQuery = "UPDATE recharge_transactions SET status = 'completed' WHERE id = :tx_id";
                $updateTxStmt = $this->conn->prepare($updateTxQuery);
                $updateTxStmt->bindParam(":tx_id", $payment['transaction_id']);
                $updateTxStmt->execute();
            }

            $this->conn->commit();

            $this->logTransaction('payment_verified', [
                'payment_id' => $payment_id,
                'admin_id' => $admin_id,
                'amount' => $payment['amount']
            ], $payment['user_id']);

            return [
                'success' => true,
                'user_balance' => $depositResult['user_balance'],
                'bonus_received' => $depositResult['bonus_received']
            ];

        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Reject payment with reason
    public function rejectPayment($payment_id, $admin_id, $notes = '') {
        try {
            $this->conn->beginTransaction();

            // Update admin_payments status
            $updateQuery = "UPDATE admin_payments SET 
                           status = 'rejected', 
                           verified_by = :admin_id, 
                           verified_at = NOW(),
                           notes = :notes 
                           WHERE id = :payment_id";
            
            $updateStmt = $this->conn->prepare($updateQuery);
            $updateStmt->bindParam(":admin_id", $admin_id);
            $updateStmt->bindParam(":notes", $notes);
            $updateStmt->bindParam(":payment_id", $payment_id);
            $updateStmt->execute();

            // Update recharge_transactions status if exists
            $txQuery = "UPDATE recharge_transactions SET status = 'failed' 
                       WHERE user_id = (SELECT user_id FROM admin_payments WHERE id = :payment_id) 
                       AND amount = (SELECT amount FROM admin_payments WHERE id = :payment_id) 
                       AND status = 'pending'";
            
            $txStmt = $this->conn->prepare($txQuery);
            $txStmt->bindParam(":payment_id", $payment_id);
            $txStmt->execute();

            $this->conn->commit();

            $payment = $this->getPaymentDetails($payment_id);
            $this->logTransaction('payment_rejected', [
                'payment_id' => $payment_id,
                'admin_id' => $admin_id,
                'notes' => $notes
            ], $payment['user_id']);

            return ['success' => true];

        } catch (Exception $e) {
            $this->conn->rollBack();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    // Get user dashboard statistics
    public function getUserDashboardData($userId) {
        $this->validateUser($userId);
        
        $userAccount = $this->getUserAccount($userId);
        $mainAccount = $this->getMainAccount();

        // Get recent transactions
        $query = "SELECT * FROM recharge_transactions 
                  WHERE user_id = :user_id 
                  ORDER BY created_at DESC 
                  LIMIT 5";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $userId);
        $stmt->execute();
        $recentTransactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'virtual_account' => $userAccount['virtual_account_number'],
            'current_balance' => $userAccount['current_balance'],
            'total_invested' => $userAccount['total_invested'],
            'total_earnings' => $userAccount['total_earnings'],
            'total_withdrawn' => $userAccount['total_withdrawn'],
            'main_account_balance' => $mainAccount['total_balance'],
            'paybill_number' => $mainAccount['paybill_number'],
            'account_number' => $mainAccount['account_number'],
            'recent_transactions' => $recentTransactions
        ];
    }

    // Get all pending payments for admin
    public function getPendingPayments() {
        $query = "SELECT ap.*, u.username, u.email 
                  FROM admin_payments ap 
                  JOIN users u ON ap.user_id = u.id 
                  WHERE ap.status = 'pending' 
                  ORDER BY ap.created_at DESC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Get payment history for user
    public function getUserPaymentHistory($user_id, $limit = 10) {
        $this->validateUser($user_id);
        
        $query = "SELECT * FROM recharge_transactions 
                  WHERE user_id = :user_id 
                  ORDER BY created_at DESC 
                  LIMIT :limit";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $user_id);
        $stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Validation methods
    private function validateAmount($amount) {
        if (!is_numeric($amount) || $amount <= 0) {
            throw new Exception("Invalid amount specified");
        }
        return floatval($amount);
    }

    private function validateUser($userId) {
        $stmt = $this->conn->prepare("SELECT id FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        if (!$stmt->fetch()) {
            throw new Exception("User not found");
        }
    }

    // Security methods
    public function checkRateLimit($userId, $action, $maxAttempts = 5, $timeFrame = 3600) {
        $stmt = $this->conn->prepare("
            SELECT COUNT(*) as attempts FROM transaction_logs 
            WHERE user_id = ? AND action = ? AND created_at > DATE_SUB(NOW(), INTERVAL ? SECOND)
        ");
        $stmt->execute([$userId, $action, $timeFrame]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $result['attempts'] < $maxAttempts;
    }

    public function checkDuplicateTransaction($userId, $amount, $phoneNumber, $timeFrame = 300) {
        $stmt = $this->conn->prepare("
            SELECT id FROM recharge_transactions 
            WHERE user_id = ? AND amount = ? AND phone_number = ? 
            AND status = 'pending' AND created_at > DATE_SUB(NOW(), INTERVAL ? SECOND)
        ");
        $stmt->execute([$userId, $amount, $phoneNumber, $timeFrame]);
        
        return $stmt->fetch() !== false;
    }

    // Logging method
    private function logTransaction($action, $details, $userId = null) {
        $stmt = $this->conn->prepare("
            INSERT INTO transaction_logs 
            (user_id, action, details, created_at) 
            VALUES (?, ?, ?, NOW())
        ");
        $stmt->execute([$userId, $action, json_encode($details)]);
    }

    // Helper method to get payment details
    private function getPaymentDetails($payment_id) {
        $query = "SELECT * FROM admin_payments WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":id", $payment_id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Get main account summary for admin
    public function getMainAccountSummary() {
        $mainAccount = $this->getMainAccount();
        
        // Get today's transactions
        $query = "SELECT COUNT(*) as today_count, COALESCE(SUM(amount), 0) as today_amount 
                  FROM recharge_transactions 
                  WHERE DATE(created_at) = CURDATE() AND status = 'completed'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $today = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Get pending transactions count
        $query = "SELECT COUNT(*) as pending_count FROM recharge_transactions WHERE status = 'pending'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $pending = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return [
            'main_account' => $mainAccount,
            'today_transactions' => $today,
            'pending_transactions' => $pending
        ];
    }
}
?>