<?php
require_once __DIR__ . '/../config/database.php';

class Coins {
    private $db;

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

    /**
     * Get user's coin balance
     */
    public function getUserBalance($userId) {
        // Check if user_coins table exists
        try {
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            
            if ($dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'user_coins'");
                if ($stmt->rowCount() === 0) {
                    return ['balance' => 0, 'total_earned' => 0];
                }
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='user_coins'");
                if (!$stmt->fetchColumn()) {
                    return ['balance' => 0, 'total_earned' => 0];
                }
            }
        } catch (PDOException $e) {
            return ['balance' => 0, 'total_earned' => 0];
        }

        try {
            $stmt = $this->db->prepare("SELECT balance, total_earned FROM user_coins WHERE user_id = :user_id");
            $stmt->bindParam(':user_id', $userId);
            $stmt->execute();
            
            $result = $stmt->fetch();
            if (!$result) {
                // Create initial coin record for user
                $this->createUserCoins($userId);
                return ['balance' => 0, 'total_earned' => 0];
            }
            
            return $result;
        } catch (PDOException $e) {
            error_log("Error getting user balance: " . $e->getMessage());
            return ['balance' => 0, 'total_earned' => 0];
        }
    }

    /**
     * Create initial coin record for user
     */
    private function createUserCoins($userId) {
        $currentTimestamp = date('Y-m-d H:i:s');
        $stmt = $this->db->prepare("INSERT INTO user_coins (user_id, balance, total_earned, created_at, updated_at) 
                                   VALUES (:user_id, 0, 0, :created_at, :updated_at)");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':created_at', $currentTimestamp);
        $stmt->bindParam(':updated_at', $currentTimestamp);
        $stmt->execute();
    }

    /**
     * Award coins to user for playing games
     */
    public function awardCoins($userId, $gameId, $playTimeMinutes, $pointsPerMinute = 10) {
        $coinsEarned = $playTimeMinutes * $pointsPerMinute;
        
        if ($coinsEarned <= 0) {
            return false;
        }

        try {
            $this->db->beginTransaction();

            // Update user's coin balance
            $currentTimestamp = date('Y-m-d H:i:s');
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            
            if ($dbType === 'mysql') {
                // MySQL syntax
                $stmt = $this->db->prepare("INSERT INTO user_coins (user_id, balance, total_earned, created_at, updated_at) 
                                           VALUES (?, ?, ?, ?, ?)
                                           ON DUPLICATE KEY UPDATE 
                                           balance = balance + VALUES(balance),
                                           total_earned = total_earned + VALUES(total_earned),
                                           updated_at = VALUES(updated_at)");
                $stmt->execute([$userId, $coinsEarned, $coinsEarned, $currentTimestamp, $currentTimestamp]);
            } else {
                // SQLite syntax - check if record exists first
                $checkStmt = $this->db->prepare("SELECT balance, total_earned FROM user_coins WHERE user_id = :user_id");
                $checkStmt->bindParam(':user_id', $userId);
                $checkStmt->execute();
                $existing = $checkStmt->fetch();

                if ($existing) {
                    // Update existing record
                    $newBalance = $existing['balance'] + $coinsEarned;
                    $newTotalEarned = $existing['total_earned'] + $coinsEarned;
                    $stmt = $this->db->prepare("UPDATE user_coins SET balance = :balance, total_earned = :total_earned, updated_at = :updated_at WHERE user_id = :user_id");
                    $stmt->bindParam(':balance', $newBalance);
                    $stmt->bindParam(':total_earned', $newTotalEarned);
                    $stmt->bindParam(':updated_at', $currentTimestamp);
                    $stmt->bindParam(':user_id', $userId);
                    $stmt->execute();
                } else {
                    // Insert new record
                    $stmt = $this->db->prepare("INSERT INTO user_coins (user_id, balance, total_earned, created_at, updated_at) VALUES (:user_id, :coins, :coins, :created_at, :updated_at)");
                    $stmt->bindParam(':user_id', $userId);
                    $stmt->bindParam(':coins', $coinsEarned);
                    $stmt->bindParam(':created_at', $currentTimestamp);
                    $stmt->bindParam(':updated_at', $currentTimestamp);
                    $stmt->execute();
                }
            }

            // Record the transaction
            $this->recordTransaction($userId, 'earned', $coinsEarned, "Earned from playing game", $gameId, $playTimeMinutes);

            $this->db->commit();
            return $coinsEarned;
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error awarding coins: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Record a coin transaction
     */
    private function recordTransaction($userId, $type, $amount, $description, $gameId = null, $playTime = 0) {
        $currentTimestamp = date('Y-m-d H:i:s');
        $stmt = $this->db->prepare("INSERT INTO coin_transactions (user_id, type, amount, description, game_id, play_time, created_at) 
                                   VALUES (:user_id, :type, :amount, :description, :game_id, :play_time, :created_at)");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':type', $type);
        $stmt->bindParam(':amount', $amount);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':game_id', $gameId);
        $stmt->bindParam(':play_time', $playTime);
        $stmt->bindParam(':created_at', $currentTimestamp);
        $stmt->execute();
    }

    /**
     * Deduct coins from user (for withdrawals)
     */
    public function deductCoins($userId, $amount, $description = "Withdrawal") {
        try {
            $this->db->beginTransaction();

            // Check if user has enough balance
            $balance = $this->getUserBalance($userId);
            if ($balance['balance'] < $amount) {
                $this->db->rollback();
                return false;
            }

            // Update user's coin balance
            $currentTimestamp = date('Y-m-d H:i:s');
            $stmt = $this->db->prepare("UPDATE user_coins SET balance = balance - :amount, updated_at = :updated_at WHERE user_id = :user_id");
            $stmt->bindParam(':amount', $amount);
            $stmt->bindParam(':updated_at', $currentTimestamp);
            $stmt->bindParam(':user_id', $userId);
            $stmt->execute();

            // Record the transaction
            $this->recordTransaction($userId, 'spent', $amount, $description);

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error deducting coins: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get user's transaction history
     */
    public function getUserTransactions($userId, $limit = 50, $offset = 0) {
        // Check if coin_transactions table exists
        try {
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            
            if ($dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'coin_transactions'");
                if ($stmt->rowCount() === 0) {
                    return [];
                }
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='coin_transactions'");
                if (!$stmt->fetchColumn()) {
                    return [];
                }
            }
        } catch (PDOException $e) {
            return [];
        }

        try {
            $stmt = $this->db->prepare("SELECT ct.*, g.title as game_title 
                                       FROM coin_transactions ct 
                                       LEFT JOIN games g ON ct.game_id = g.id 
                                       WHERE ct.user_id = :user_id 
                                       ORDER BY ct.created_at DESC 
                                       LIMIT :limit OFFSET :offset");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error getting user transactions: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Create withdrawal request
     */
    public function createWithdrawalRequest($userId, $amount, $paymentMethod, $paymentDetails) {
        try {
            $this->db->beginTransaction();

            // Check if user has enough balance
            $balance = $this->getUserBalance($userId);
            if ($balance['balance'] < $amount) {
                $this->db->rollback();
                return ['success' => false, 'message' => 'Insufficient balance'];
            }

            // Create withdrawal request
            $currentTimestamp = date('Y-m-d H:i:s');
            $stmt = $this->db->prepare("INSERT INTO withdrawal_requests (user_id, amount, payment_method, payment_details, created_at, updated_at) 
                                       VALUES (:user_id, :amount, :payment_method, :payment_details, :created_at, :updated_at)");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':amount', $amount);
            $stmt->bindParam(':payment_method', $paymentMethod);
            $stmt->bindParam(':payment_details', $paymentDetails);
            $stmt->bindParam(':created_at', $currentTimestamp);
            $stmt->bindParam(':updated_at', $currentTimestamp);
            $stmt->execute();

            $withdrawalId = $this->db->lastInsertId();

            // Deduct coins from user balance (hold them) - do this within the same transaction
            $stmt = $this->db->prepare("UPDATE user_coins SET balance = balance - :amount, updated_at = :updated_at WHERE user_id = :user_id");
            $stmt->bindParam(':amount', $amount);
            $stmt->bindParam(':updated_at', $currentTimestamp);
            $stmt->bindParam(':user_id', $userId);
            $stmt->execute();

            // Record the transaction
            $this->recordTransaction($userId, 'spent', $amount, "Withdrawal request #" . $withdrawalId);

            $this->db->commit();
            return ['success' => true, 'message' => 'Withdrawal request submitted successfully'];
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error creating withdrawal request: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to create withdrawal request'];
        }
    }

    /**
     * Get withdrawal requests with optional status filter
     */
    public function getWithdrawalRequests($status = null, $search = null, $limit = 20, $offset = 0) {
        // Check if withdrawal_requests table exists
        try {
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            
            if ($dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'withdrawal_requests'");
                if ($stmt->rowCount() === 0) {
                    return ['data' => [], 'total' => 0];
                }
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='withdrawal_requests'");
                if (!$stmt->fetchColumn()) {
                    return ['data' => [], 'total' => 0];
                }
            }
        } catch (PDOException $e) {
            return ['data' => [], 'total' => 0];
        }

        $whereConditions = [];
        $params = [];
        
        if ($status) {
            $whereConditions[] = "wr.status = :status";
            $params[':status'] = $status;
        }
        
        if ($search) {
            $whereConditions[] = "(u.username LIKE :search OR u.email LIKE :search OR wr.payment_method LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }
        
        $whereClause = !empty($whereConditions) ? 'WHERE ' . implode(' AND ', $whereConditions) : '';
        
        try {
            // Get total count
            $countSql = "SELECT COUNT(*) as total 
                         FROM withdrawal_requests wr 
                         JOIN users u ON wr.user_id = u.id 
                         $whereClause";
            $countStmt = $this->db->prepare($countSql);
            foreach ($params as $key => $value) {
                $countStmt->bindValue($key, $value);
            }
            $countStmt->execute();
            $total = $countStmt->fetch()['total'];
            
            // Get data with pagination
            $sql = "SELECT wr.*, u.username, u.email 
                    FROM withdrawal_requests wr 
                    JOIN users u ON wr.user_id = u.id 
                    $whereClause
                    ORDER BY wr.created_at DESC 
                    LIMIT :limit OFFSET :offset";
            
            $stmt = $this->db->prepare($sql);
            
            // Bind all parameters
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            
            $stmt->execute();
            $data = $stmt->fetchAll();
            
            return [
                'data' => $data,
                'total' => $total
            ];
        } catch (PDOException $e) {
            error_log("Error getting withdrawal requests: " . $e->getMessage());
            return ['data' => [], 'total' => 0];
        }
    }

    /**
     * Delete withdrawal request
     */
    public function deleteWithdrawalRequest($requestId) {
        try {
            $this->db->beginTransaction();
            
            // Get request details before deletion
            $requestStmt = $this->db->prepare("SELECT user_id, amount, status FROM withdrawal_requests WHERE id = :id");
            $requestStmt->bindParam(':id', $requestId);
            $requestStmt->execute();
            $request = $requestStmt->fetch();
            
            if (!$request) {
                $this->db->rollback();
                return false;
            }
            
            // If request is pending, refund the coins
            if ($request['status'] === 'pending') {
                $currentTimestamp = date('Y-m-d H:i:s');
                $updateStmt = $this->db->prepare("UPDATE user_coins SET balance = balance + :amount, updated_at = :updated_at WHERE user_id = :user_id");
                $updateStmt->bindParam(':amount', $request['amount']);
                $updateStmt->bindParam(':updated_at', $currentTimestamp);
                $updateStmt->bindParam(':user_id', $request['user_id']);
                $updateStmt->execute();
                
                // Record the transaction
                $this->recordTransaction($request['user_id'], 'admin_refund', $request['amount'], 'Withdrawal request deleted - coins refunded');
            }
            
            // Delete the withdrawal request
            $deleteStmt = $this->db->prepare("DELETE FROM withdrawal_requests WHERE id = :id");
            $deleteStmt->bindParam(':id', $requestId);
            $deleteStmt->execute();
            
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error deleting withdrawal request: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Update withdrawal request status
     */
    public function updateWithdrawalStatus($requestId, $status, $adminNotes = '') {
        try {
            $this->db->beginTransaction();

            $currentTimestamp = date('Y-m-d H:i:s');
            $stmt = $this->db->prepare("UPDATE withdrawal_requests 
                                       SET status = :status, admin_notes = :admin_notes, updated_at = :updated_at 
                                       WHERE id = :id");
            $stmt->bindParam(':status', $status);
            $stmt->bindParam(':admin_notes', $adminNotes);
            $stmt->bindParam(':updated_at', $currentTimestamp);
            $stmt->bindParam(':id', $requestId);
            $stmt->execute();

            // If request is rejected, refund the coins
            if ($status === 'rejected') {
                $requestStmt = $this->db->prepare("SELECT user_id, amount FROM withdrawal_requests WHERE id = :id");
                $requestStmt->bindParam(':id', $requestId);
                $requestStmt->execute();
                $request = $requestStmt->fetch();

                if ($request) {
                    // Refund coins
                    $updateStmt = $this->db->prepare("UPDATE user_coins SET balance = balance + :amount, updated_at = :updated_at WHERE user_id = :user_id");
                    $updateStmt->bindParam(':amount', $request['amount']);
                    $updateStmt->bindParam(':updated_at', $currentTimestamp);
                    $updateStmt->bindParam(':user_id', $request['user_id']);
                    $updateStmt->execute();

                    // Record refund transaction
                    $this->recordTransaction($request['user_id'], 'refund', $request['amount'], "Withdrawal request #$requestId rejected - refund");
                }
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error updating withdrawal status: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get user's withdrawal requests
     */
    public function getUserWithdrawalRequests($userId, $limit = 20, $offset = 0) {
        // Check if withdrawal_requests table exists
        try {
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            
            if ($dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'withdrawal_requests'");
                if ($stmt->rowCount() === 0) {
                    return [];
                }
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='withdrawal_requests'");
                if (!$stmt->fetchColumn()) {
                    return [];
                }
            }
        } catch (PDOException $e) {
            return [];
        }

        try {
            $stmt = $this->db->prepare("SELECT * FROM withdrawal_requests 
                                       WHERE user_id = :user_id 
                                       ORDER BY created_at DESC 
                                       LIMIT :limit OFFSET :offset");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error getting user withdrawal requests: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Update user's coin balance directly (for admin use)
     */
    public function updateUserBalance($userId, $newBalance, $description = 'Admin adjustment') {
        try {
            $this->db->beginTransaction();
            
            // Get current balance
            $currentBalance = $this->getUserBalance($userId);
            $currentBalanceAmount = $currentBalance['balance'];
            
            // Calculate the difference
            $difference = $newBalance - $currentBalanceAmount;
            
            // Update the balance
            $currentTimestamp = date('Y-m-d H:i:s');
            $stmt = $this->db->prepare("UPDATE user_coins SET balance = :balance, updated_at = :updated_at WHERE user_id = :user_id");
            $stmt->bindParam(':balance', $newBalance);
            $stmt->bindParam(':updated_at', $currentTimestamp);
            $stmt->bindParam(':user_id', $userId);
            
            if (!$stmt->execute()) {
                // If update failed, try to create the record
                $this->createUserCoins($userId);
                $stmt->execute();
            }
            
            // Record the transaction
            if ($difference != 0) {
                $transactionType = $difference > 0 ? 'admin_add' : 'admin_deduct';
                $this->recordTransaction($userId, $transactionType, abs($difference), $description);
            }
            
            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Error updating user balance: " . $e->getMessage());
            return false;
        }
    }
}