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

class User {
    private $db;

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

    /**
     * Register a new user
     */
    public function register($username, $email, $password, $name = '') {
        // Check if username already exists
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM users WHERE username = :username");
        $stmt->bindParam(':username', $username);
        $stmt->execute();

        if ($stmt->fetchColumn() > 0) {
            return ['success' => false, 'message' => 'Username already exists'];
        }

        // Check if email already exists (case-insensitive)
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM users WHERE LOWER(email) = LOWER(:email)");
        $stmt->bindParam(':email', $email);
        $stmt->execute();

        if ($stmt->fetchColumn() > 0) {
            return ['success' => false, 'message' => 'Email already exists'];
        }

        // Hash password
        $hashedPassword = password_hash($password, PASSWORD_DEFAULT);

        // Generate verification token
        $verificationToken = bin2hex(random_bytes(32));

        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        // Insert new user with explicit timestamps
        $stmt = $this->db->prepare("INSERT INTO users (username, email, password, name, role, verification_token, created_at, updated_at)
                                    VALUES (:username, :email, :password, :name, 'user', :verification_token, :created_at, :updated_at)");
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':password', $hashedPassword);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':verification_token', $verificationToken);
        $stmt->bindParam(':created_at', $currentTimestamp);
        $stmt->bindParam(':updated_at', $currentTimestamp);

        if ($stmt->execute()) {
            return [
                'success' => true,
                'user_id' => $this->db->lastInsertId(),
                'verification_token' => $verificationToken
            ];
        } else {
            return ['success' => false, 'message' => 'Registration failed'];
        }
    }

    /**
     * Authenticate a user
     */
    public function authenticate($username, $password) {
        // Use LOWER() for case-insensitive email comparison
        $stmt = $this->db->prepare("SELECT * FROM users WHERE (username = :username OR LOWER(email) = LOWER(:email)) AND is_active = 1");
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':email', $username); // Allow login with email too
        $stmt->execute();

        $user = $stmt->fetch();

        if ($user && password_verify($password, $user['password'])) {
            // Check if email is verified
            if ($user['email_verified'] == 0) {
                return ['error' => 'email_not_verified', 'user_id' => $user['id']];
            }
            return $user;
        }

        return false;
    }

    /**
     * Get user by ID
     */
    public function getUserById($id) {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetch();
    }

    /**
     * Get user with coin balance
     */
    public function getUserWithCoins($id) {
        $stmt = $this->db->prepare("SELECT u.*, uc.balance as coin_balance, uc.total_earned 
                                   FROM users u 
                                   LEFT JOIN user_coins uc ON u.id = uc.user_id 
                                   WHERE u.id = :id AND u.is_active = 1");
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $user = $stmt->fetch();
        if ($user && $user['coin_balance'] === null) {
            $user['coin_balance'] = 0;
            $user['total_earned'] = 0;
        }
        
        return $user;
    }

    /**
     * Get user by email
     */
    public function getUserByEmail($email) {
        // Use LOWER() for case-insensitive email comparison
        $stmt = $this->db->prepare("SELECT * FROM users WHERE LOWER(email) = LOWER(:email)");
        $stmt->bindParam(':email', $email);
        $stmt->execute();

        return $stmt->fetch();
    }

    /**
     * Get user by Google ID
     */
    public function getUserByGoogleId($googleId) {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE google_id = :google_id");
        $stmt->bindParam(':google_id', $googleId);
        $stmt->execute();

        return $stmt->fetch();
    }

    /**
     * Update user profile
     */
    public function updateProfile($userId, $data) {
        $allowedFields = ['name', 'email', 'username', 'profile_image'];
        $updates = [];
        $params = [':id' => $userId];

        foreach ($data as $key => $value) {
            if (in_array($key, $allowedFields)) {
                $updates[] = "$key = :$key";
                $params[":$key"] = $value;
            }
        }

        if (empty($updates)) {
            return false;
        }

        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        // Add updated_at to updates
        $updates[] = "updated_at = :updated_at";
        $params[":updated_at"] = $currentTimestamp;

        $sql = "UPDATE users SET " . implode(', ', $updates) . " WHERE id = :id";
        $stmt = $this->db->prepare($sql);

        return $stmt->execute($params);
    }

    /**
     * Update user password
     */
    public function updatePassword($userId, $newPassword) {
        $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);

        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        $stmt = $this->db->prepare("UPDATE users SET password = :password, updated_at = :updated_at WHERE id = :id");
        $stmt->bindParam(':password', $hashedPassword);
        $stmt->bindParam(':updated_at', $currentTimestamp);
        $stmt->bindParam(':id', $userId);

        return $stmt->execute();
    }

    /**
     * Create password reset token
     */
    public function createPasswordResetToken($email) {
        // Check if user exists
        $user = $this->getUserByEmail($email);

        if (!$user) {
            return false;
        }

        // Generate token
        $token = bin2hex(random_bytes(32));

        // Set expiry time (1 hour from now)
        $expiresAt = date('Y-m-d H:i:s', strtotime('+1 hour'));

        // Delete any existing tokens for this email (case-insensitive)
        $stmt = $this->db->prepare("DELETE FROM password_resets WHERE LOWER(email) = LOWER(:email)");
        $stmt->bindParam(':email', $email);
        $stmt->execute();

        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        // Insert new token with explicit created_at timestamp
        $stmt = $this->db->prepare("INSERT INTO password_resets (email, token, created_at, expires_at) VALUES (:email, :token, :created_at, :expires_at)");
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':token', $token);
        $stmt->bindParam(':created_at', $currentTimestamp);
        $stmt->bindParam(':expires_at', $expiresAt);

        if ($stmt->execute()) {
            return $token;
        }

        return false;
    }

    /**
     * Verify password reset token
     */
    public function verifyPasswordResetToken($email, $token) {
        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        // Compare with explicit timestamp (case-insensitive email)
        $stmt = $this->db->prepare("SELECT * FROM password_resets WHERE LOWER(email) = LOWER(:email) AND token = :token AND expires_at > :current_time");
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':token', $token);
        $stmt->bindParam(':current_time', $currentTimestamp);
        $stmt->execute();

        return $stmt->fetch() ? true : false;
    }

    /**
     * Reset password using token
     */
    public function resetPassword($email, $token, $newPassword) {
        // Verify token
        if (!$this->verifyPasswordResetToken($email, $token)) {
            return false;
        }

        // Get user
        $user = $this->getUserByEmail($email);

        if (!$user) {
            return false;
        }

        // Update password
        $result = $this->updatePassword($user['id'], $newPassword);

        if ($result) {
            // Delete used token (case-insensitive)
            $stmt = $this->db->prepare("DELETE FROM password_resets WHERE LOWER(email) = LOWER(:email)");
            $stmt->bindParam(':email', $email);
            $stmt->execute();

            return true;
        }

        return false;
    }

    /**
     * Verify email with token
     */
    public function verifyEmail($userId, $token) {
        $stmt = $this->db->prepare("SELECT * FROM users WHERE id = :id AND verification_token = :token");
        $stmt->bindParam(':id', $userId);
        $stmt->bindParam(':token', $token);
        $stmt->execute();

        $user = $stmt->fetch();

        if ($user) {
            $stmt = $this->db->prepare("UPDATE users SET email_verified = 1, verification_token = NULL WHERE id = :id");
            $stmt->bindParam(':id', $userId);
            return $stmt->execute();
        }

        return false;
    }

    /**
     * Get user game history
     */
    public function getUserGameHistory($userId, $limit = 10) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_plays'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist yet, return empty array
                    return [];
                }
            } catch (PDOException $e) {
                // Error checking table, return empty array
                return [];
            }
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_plays'");
            if (!$stmt->fetchColumn()) {
                // Table doesn't exist yet, return empty array
                return [];
            }
        }

        $sql = "SELECT gp.*, g.title, g.slug, g.thumbnail, c.name as category_name
                FROM game_plays gp
                JOIN games g ON gp.game_id = g.id
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE gp.user_id = :user_id
                ORDER BY gp.created_at DESC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Record game play
     */
    public function recordGamePlay($userId, $gameId) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_plays'");
                if ($stmt->rowCount() === 0) {
                    try {
                        // Table doesn't exist yet, create it
                        $this->db->exec("CREATE TABLE game_plays (
                            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                            user_id INT UNSIGNED NOT NULL,
                            game_id INT UNSIGNED NOT NULL,
                            play_time INT DEFAULT 0,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
                        error_log("Created game_plays table from recordGamePlay method");
                    } catch (PDOException $e) {
                        error_log("Error creating game_plays table: " . $e->getMessage());
                        return false;
                    }
                }
            } catch (PDOException $e) {
                error_log("Error checking game_plays table: " . $e->getMessage());
                return false;
            }

            // Insert with MySQL timestamp handling
            $stmt = $this->db->prepare("INSERT INTO game_plays (user_id, game_id) VALUES (:user_id, :game_id)");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':game_id', $gameId);

            return $stmt->execute();
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_plays'");
            if (!$stmt->fetchColumn()) {
                try {
                    // Table doesn't exist yet, create it without CURRENT_TIMESTAMP default
                    $this->db->exec("CREATE TABLE game_plays (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_id INTEGER NOT NULL,
                        game_id INTEGER NOT NULL,
                        play_time INTEGER DEFAULT 0,
                        created_at TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                        FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
                    )");
                    error_log("Created game_plays table from recordGamePlay method");
                } catch (PDOException $e) {
                    error_log("Error creating game_plays table: " . $e->getMessage());
                    return false;
                }
            }

            // Get current timestamp
            $currentTimestamp = date('Y-m-d H:i:s');

            // Insert with explicit timestamp
            $stmt = $this->db->prepare("INSERT INTO game_plays (user_id, game_id, created_at) VALUES (:user_id, :game_id, :created_at)");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':game_id', $gameId);
            $stmt->bindParam(':created_at', $currentTimestamp);

            return $stmt->execute();
        }
    }

    /**
     * Add a game to user's favorites
     */
    public function addGameToFavorites($userId, $gameId) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_favorites'");
                if ($stmt->rowCount() === 0) {
                    try {
                        // Table doesn't exist yet, create it
                        $this->db->exec("CREATE TABLE game_favorites (
                            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                            user_id INT UNSIGNED NOT NULL,
                            game_id INT UNSIGNED NOT NULL,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
                            UNIQUE(user_id, game_id)
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
                        error_log("Created game_favorites table from addGameToFavorites method");
                    } catch (PDOException $e) {
                        error_log("Error creating game_favorites table: " . $e->getMessage());
                        return false;
                    }
                }
            } catch (PDOException $e) {
                error_log("Error checking game_favorites table: " . $e->getMessage());
                return false;
            }

            // Insert with MySQL timestamp handling and ON DUPLICATE KEY UPDATE to handle unique constraint
            $stmt = $this->db->prepare("INSERT INTO game_favorites (user_id, game_id) VALUES (:user_id, :game_id) ON DUPLICATE KEY UPDATE id=id");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':game_id', $gameId);

            return $stmt->execute();
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_favorites'");
            if (!$stmt->fetchColumn()) {
                try {
                    // Table doesn't exist yet, create it without CURRENT_TIMESTAMP default
                    $this->db->exec("CREATE TABLE game_favorites (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_id INTEGER NOT NULL,
                        game_id INTEGER NOT NULL,
                        created_at TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                        FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
                        UNIQUE(user_id, game_id)
                    )");
                    error_log("Created game_favorites table from addGameToFavorites method");
                } catch (PDOException $e) {
                    error_log("Error creating game_favorites table: " . $e->getMessage());
                    return false;
                }
            }

            // Get current timestamp
            $currentTimestamp = date('Y-m-d H:i:s');

            // SQLite doesn't have ON DUPLICATE KEY UPDATE, so we need to check if the record exists first
            $checkStmt = $this->db->prepare("SELECT id FROM game_favorites WHERE user_id = :user_id AND game_id = :game_id");
            $checkStmt->bindParam(':user_id', $userId);
            $checkStmt->bindParam(':game_id', $gameId);
            $checkStmt->execute();

            if ($checkStmt->fetch()) {
                // Record already exists, do nothing
                return true;
            }

            // Insert with explicit timestamp
            $stmt = $this->db->prepare("INSERT INTO game_favorites (user_id, game_id, created_at) VALUES (:user_id, :game_id, :created_at)");
            $stmt->bindParam(':user_id', $userId);
            $stmt->bindParam(':game_id', $gameId);
            $stmt->bindParam(':created_at', $currentTimestamp);

            return $stmt->execute();
        }
    }

    /**
     * Remove a game from user's favorites
     */
    public function removeGameFromFavorites($userId, $gameId) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_favorites'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist yet, nothing to remove
                    return true;
                }
            } catch (PDOException $e) {
                error_log("Error checking game_favorites table: " . $e->getMessage());
                return false;
            }
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_favorites'");
            if (!$stmt->fetchColumn()) {
                // Table doesn't exist yet, nothing to remove
                return true;
            }
        }

        // Delete the favorite
        $stmt = $this->db->prepare("DELETE FROM game_favorites WHERE user_id = :user_id AND game_id = :game_id");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':game_id', $gameId);

        return $stmt->execute();
    }

    /**
     * Check if a game is in user's favorites
     */
    public function isGameFavorited($userId, $gameId) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_favorites'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist yet, return false
                    return false;
                }
            } catch (PDOException $e) {
                error_log("Error checking game_favorites table: " . $e->getMessage());
                return false;
            }
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_favorites'");
            if (!$stmt->fetchColumn()) {
                // Table doesn't exist yet, return false
                return false;
            }
        }

        // Check if the game is favorited
        $stmt = $this->db->prepare("SELECT id FROM game_favorites WHERE user_id = :user_id AND game_id = :game_id");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':game_id', $gameId);
        $stmt->execute();

        return $stmt->fetch() ? true : false;
    }

    /**
     * Get user's favorite games
     */
    public function getUserFavoriteGames($userId, $limit = 10) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_favorites'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist yet, return empty array
                    return [];
                }
            } catch (PDOException $e) {
                // Error checking table, return empty array
                return [];
            }
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_favorites'");
            if (!$stmt->fetchColumn()) {
                // Table doesn't exist yet, return empty array
                return [];
            }
        }

        $sql = "SELECT gf.*, g.title, g.slug, g.thumbnail, c.name as category_name
                FROM game_favorites gf
                JOIN games g ON gf.game_id = g.id
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE gf.user_id = :user_id
                ORDER BY gf.created_at DESC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Toggle favorite status for a game
     */
    public function toggleGameFavorite($userId, $gameId) {
        if ($this->isGameFavorited($userId, $gameId)) {
            // Game is already favorited, remove it
            $result = $this->removeGameFromFavorites($userId, $gameId);
            return ['success' => $result, 'status' => 'removed'];
        } else {
            // Game is not favorited, add it
            $result = $this->addGameToFavorites($userId, $gameId);
            return ['success' => $result, 'status' => 'added'];
        }
    }

    /**
     * Get leaderboard data
     */
    public function getLeaderboard($limit = 10) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax for checking if table exists
            try {
                $stmt = $this->db->query("SHOW TABLES LIKE 'game_plays'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist yet, return empty array
                    return [];
                }

                // Check if users table has the name column
                $stmt = $this->db->query("SHOW COLUMNS FROM users LIKE 'name'");
                $hasNameColumn = $stmt->rowCount() > 0;

                $stmt = $this->db->query("SHOW COLUMNS FROM users LIKE 'profile_image'");
                $hasProfileImageColumn = $stmt->rowCount() > 0;

                $nameColumn = $hasNameColumn ? 'u.name' : 'NULL as name';
                $profileImageColumn = $hasProfileImageColumn ? 'u.profile_image' : 'NULL as profile_image';
            } catch (PDOException $e) {
                // Error checking table, return empty array
                error_log("Error checking leaderboard tables: " . $e->getMessage());
                return [];
            }
        } else {
            // SQLite syntax for checking if table exists
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_plays'");
            if (!$stmt->fetchColumn()) {
                // Table doesn't exist yet, return empty array
                return [];
            }

            // Check if users table has the name column
            $stmt = $this->db->query("PRAGMA table_info(users)");
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $columnNames = array_column($columns, 'name');

            $nameColumn = in_array('name', $columnNames) ? 'u.name' : 'NULL as name';
            $profileImageColumn = in_array('profile_image', $columnNames) ? 'u.profile_image' : 'NULL as profile_image';
        }

        $sql = "SELECT u.id, u.username, $nameColumn, $profileImageColumn, COUNT(gp.id) as play_count
                FROM users u
                JOIN game_plays gp ON u.id = gp.user_id
                WHERE u.role = 'user'
                GROUP BY u.id
                ORDER BY play_count DESC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Register or login with Google
     */
    public function registerOrLoginWithGoogle($googleUser) {
        // Check if user already exists with this Google ID
        $existingUser = $this->getUserByGoogleId($googleUser->getId());

        if ($existingUser) {
            // User exists, return user data
            return $existingUser;
        }

        // Check if user exists with this email
        $email = $googleUser->getEmail();
        $existingUserByEmail = $this->getUserByEmail($email);

        if ($existingUserByEmail) {
            // User exists with this email, update Google ID
            $this->updateGoogleId($existingUserByEmail['id'], $googleUser->getId());
            return $this->getUserById($existingUserByEmail['id']); // Get updated user data
        }

        // Create new user
        $username = $this->generateUniqueUsername($googleUser->getName());
        $name = $googleUser->getName();
        $password = bin2hex(random_bytes(16)); // Generate random password

        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        // Insert new user with Google ID and verified email
        $stmt = $this->db->prepare("INSERT INTO users (username, email, password, name, role, google_id, email_verified, created_at, updated_at)
                                VALUES (:username, :email, :password, :name, 'user', :google_id, 1, :created_at, :updated_at)");
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':password', password_hash($password, PASSWORD_DEFAULT));
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':google_id', $googleUser->getId());
        $stmt->bindParam(':created_at', $currentTimestamp);
        $stmt->bindParam(':updated_at', $currentTimestamp);

        if ($stmt->execute()) {
            return $this->getUserById($this->db->lastInsertId());
        }

        return false;
    }

    /**
     * Update Google ID for existing user
     */
    private function updateGoogleId($userId, $googleId) {
        // Get current timestamp
        $currentTimestamp = date('Y-m-d H:i:s');

        $stmt = $this->db->prepare("UPDATE users SET google_id = :google_id, updated_at = :updated_at WHERE id = :id");
        $stmt->bindParam(':google_id', $googleId);
        $stmt->bindParam(':updated_at', $currentTimestamp);
        $stmt->bindParam(':id', $userId);

        return $stmt->execute();
    }

    /**
     * Generate unique username based on name
     */
    private function generateUniqueUsername($name) {
        // Convert name to lowercase and replace spaces with underscores
        $baseUsername = strtolower(str_replace(' ', '_', $name));

        // Remove any non-alphanumeric characters
        $baseUsername = preg_replace('/[^a-z0-9_]/', '', $baseUsername);

        // If empty after cleaning, use a default
        if (empty($baseUsername)) {
            $baseUsername = 'user';
        }

        // Check if username exists
        $username = $baseUsername;
        $counter = 1;

        while ($this->usernameExists($username)) {
            $username = $baseUsername . $counter;
            $counter++;
        }

        return $username;
    }

    /**
     * Check if username exists
     */
    private function usernameExists($username) {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM users WHERE username = :username");
        $stmt->bindParam(':username', $username);
        $stmt->execute();

        return $stmt->fetchColumn() > 0;
    }

    /**
     * Export user data as JSON
     */
    public function exportUserData($userId) {
        $user = $this->getUserById($userId);
        $gameHistory = $this->getUserGameHistory($userId, 9999);
        $favoriteGames = $this->getUserFavoriteGames($userId, 9999);

        $data = [
            'profile' => [
                'username' => $user['username'],
                'email' => $user['email'],
                'name' => $user['name'],
                'created_at' => $user['created_at']
            ],
            'game_history' => $gameHistory,
            'favorite_games' => $favoriteGames
        ];

        header('Content-Type: application/json');
        header('Content-Disposition: attachment; filename=user_data.json');
        echo json_encode($data, JSON_PRETTY_PRINT);
    }

    /**
     * Delete a user account
     */
    public function deleteAccount($userId) {
        $stmt = $this->db->prepare("DELETE FROM users WHERE id = :id");
        $stmt->bindParam(':id', $userId);
        return $stmt->execute();
    }
}
