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

class Game {
    private $db;

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

    public function getAllGames($limit = 12, $offset = 0) {
        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                ORDER BY g.id DESC
                LIMIT :limit OFFSET :offset";

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

        return $stmt->fetchAll();
    }

    public function getGameBySlug($slug) {
        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE g.slug = :slug";

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

        return $stmt->fetch();
    }
public function getGameById($id) {
        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE g.id = :id";

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

        return $stmt->fetch();
    }

    public function getGamesByCategory($categorySlug, $limit = 12, $offset = 0) {
        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE c.slug = :category_slug
                ORDER BY g.id DESC
                LIMIT :limit OFFSET :offset";

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

        return $stmt->fetchAll();
    }

    public function searchGames($query, $limit = 12, $offset = 0) {
        $searchTerm = "%$query%";

        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE g.title LIKE :search_term_title OR g.description LIKE :search_term_desc
                ORDER BY g.id DESC
                LIMIT :limit OFFSET :offset";

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

        return $stmt->fetchAll();
    }

    public function getRelatedGames($categoryId, $currentGameId, $limit = 6) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        // Use the appropriate random function based on database type
        $randomFunction = ($dbType === 'mysql') ? 'RAND()' : 'RANDOM()';

        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE g.category_id = :category_id AND g.id != :current_game_id
                ORDER BY $randomFunction
                LIMIT :limit";

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

        return $stmt->fetchAll();
    }

    public function getFeaturedGames($limit = 6) {
        // First, get games marked as featured
        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE g.featured = 1
                ORDER BY g.updated_at DESC
                LIMIT :limit";

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

        // If we don't have enough featured games, fill with random games
        $remainingLimit = $limit - count($featuredGames);
        if ($remainingLimit > 0) {
            // Check database type
            $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
            // Use the appropriate random function based on database type
            $randomFunction = ($dbType === 'mysql') ? 'RAND()' : 'RANDOM()';

            $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                    FROM games g
                    LEFT JOIN categories c ON g.category_id = c.id
                    WHERE g.featured = 0
                    ORDER BY $randomFunction
                    LIMIT :remaining_limit";

            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':remaining_limit', $remainingLimit, PDO::PARAM_INT);
            $stmt->execute();
            $randomGames = $stmt->fetchAll();

            // Merge featured and random games
            $featuredGames = array_merge($featuredGames, $randomGames);
        }

        return $featuredGames;
    }

    public function getRandomGames($limit = 6) {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';
        // Use the appropriate random function based on database type
        $randomFunction = ($dbType === 'mysql') ? 'RAND()' : 'RANDOM()';

        $sql = "SELECT g.*, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                ORDER BY $randomFunction
                LIMIT :limit";

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

        return $stmt->fetchAll();
    }

    public function getTotalGames() {
        $sql = "SELECT COUNT(*) FROM games";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return $stmt->fetchColumn();
    }

    public function getTotalGamesByCategory($categorySlug) {
        $sql = "SELECT COUNT(*)
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id
                WHERE c.slug = :category_slug";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':category_slug', $categorySlug);
        $stmt->execute();

        return $stmt->fetchColumn();
    }

    public function getTotalSearchResults($query) {
        $searchTerm = "%$query%";

        $sql = "SELECT COUNT(*)
                FROM games
                WHERE title LIKE :search_term_title OR description LIKE :search_term_desc";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':search_term_title', $searchTerm);
        $stmt->bindParam(':search_term_desc', $searchTerm);
        $stmt->execute();

        return $stmt->fetchColumn();
    }

    // Admin methods

    public function getGamesForAdmin($limit = 20, $offset = 0, $search = '') {
        $sql = "SELECT g.*, COALESCE(g.featured, 0) as featured, c.name as category_name, c.slug as category_slug
                FROM games g
                LEFT JOIN categories c ON g.category_id = c.id";

        $params = [];

        if (!empty($search)) {
            $sql .= " WHERE g.title LIKE :search_term_title OR g.description LIKE :search_term_desc";
            $params[':search_term_title'] = "%$search%";
            $params[':search_term_desc'] = "%$search%";
        }

        $sql .= " ORDER BY g.id DESC LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function getTotalGamesForAdmin($search = '') {
        $sql = "SELECT COUNT(*) FROM games g";

        $params = [];

        if (!empty($search)) {
            $sql .= " WHERE g.title LIKE :search_term_title OR g.description LIKE :search_term_desc";
            $params[':search_term_title'] = "%$search%";
            $params[':search_term_desc'] = "%$search%";
        }

        $stmt = $this->db->prepare($sql);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        $stmt->execute();

        return $stmt->fetchColumn();
    }

    public function deleteGame($id) {
        try {
            // First, get the game details to check if it's a manual game with a thumbnail
            $sql = "SELECT source, thumbnail FROM games WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->execute();
            $game = $stmt->fetch(PDO::FETCH_ASSOC);

            // Delete the game from the database
            $sql = "DELETE FROM games WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $result = $stmt->execute();

            // If it's a manual game and has a thumbnail, delete the thumbnail file
            if ($result && $game && $game['source'] === 'manual' && !empty($game['thumbnail'])) {
                $thumbnailPath = __DIR__ . '/..' . $game['thumbnail'];
                if (file_exists($thumbnailPath)) {
                    unlink($thumbnailPath);
                }
            }

            return $result;
        } catch (Exception $e) {
            error_log('Error deleting game: ' . $e->getMessage());
            return false;
        }
    }

    public function updateGameCategory($id, $categoryId) {
        $sql = "UPDATE games SET category_id = :category_id, updated_at = CURRENT_TIMESTAMP WHERE id = :id";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->bindParam(':category_id', $categoryId, PDO::PARAM_INT);

        return $stmt->execute();
    }

    public function updateGameFeatured($id, $featured) {
        $sql = "UPDATE games SET featured = :featured, updated_at = CURRENT_TIMESTAMP WHERE id = :id";

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

        return $stmt->execute();
    }

    /**
     * Get games by source
     *
     * @param string $source The source (e.g., 'gamepix', 'gamemonetize')
     * @return array Array of games from the specified source
     */
    public function getGamesBySource($source) {
        $sql = "SELECT * FROM games WHERE source = :source";

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

        return $stmt->fetchAll();
    }

    /**
     * Update game embed code
     *
     * @param int $id Game ID
     * @param string $embedCode New embed code
     * @return bool Success status
     */
    public function updateGameEmbed($id, $embedCode) {
        $sql = "UPDATE games SET embed_code = :embed_code, updated_at = CURRENT_TIMESTAMP WHERE id = :id";

        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_INT);
        $stmt->bindParam(':embed_code', $embedCode);

        return $stmt->execute();
    }

    /**
     * Delete all games from a specific source
     *
     * @param string $source The source to delete games from (e.g., 'gamepix', 'gamemonetize', 'gamezop', 'manual')
     * @return bool Success status
     */
    public function deleteGamesBySource($source) {
        try {
            // If deleting manual games, we need to delete the thumbnail files first
            if ($source === 'manual') {
                // Get all manual games with thumbnails
                $sql = "SELECT id, thumbnail FROM games WHERE source = :source AND thumbnail IS NOT NULL AND thumbnail != ''";
                $stmt = $this->db->prepare($sql);
                $stmt->bindParam(':source', $source, PDO::PARAM_STR);
                $stmt->execute();
                $games = $stmt->fetchAll(PDO::FETCH_ASSOC);

                // Delete thumbnail files
                foreach ($games as $game) {
                    if (!empty($game['thumbnail'])) {
                        $thumbnailPath = __DIR__ . '/..' . $game['thumbnail'];
                        if (file_exists($thumbnailPath)) {
                            unlink($thumbnailPath);
                        }
                    }
                }
            }

            // Delete games from database
            $sql = "DELETE FROM games WHERE source = :source";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':source', $source, PDO::PARAM_STR);

            return $stmt->execute();
        } catch (Exception $e) {
            error_log('Error deleting games by source: ' . $e->getMessage());
            return false;
        }
    }

    public function updateGame($gameData) {
        $sql = "UPDATE games
                SET title = :title,
                    description = :description,
                    thumbnail = :thumbnail,
                    category_id = :category_id,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = :id";

        $stmt = $this->db->prepare($sql);

        $stmt->bindParam(':id', $gameData['id']);
        $stmt->bindParam(':title', $gameData['title']);
        $stmt->bindParam(':description', $gameData['description']);
        $stmt->bindParam(':thumbnail', $gameData['thumbnail']);
        $stmt->bindParam(':category_id', $gameData['category_id']); // Can be null

        return $stmt->execute();
    }

    /**
     * Delete all games from all sources
     *
     * @return bool Success status
     */
    public function deleteAllGames() {
        try {
            // First, get all manual games with thumbnails
            $sql = "SELECT id, thumbnail FROM games WHERE source = 'manual' AND thumbnail IS NOT NULL AND thumbnail != ''";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $games = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Delete thumbnail files for manual games
            foreach ($games as $game) {
                if (!empty($game['thumbnail'])) {
                    $thumbnailPath = __DIR__ . '/..' . $game['thumbnail'];
                    if (file_exists($thumbnailPath)) {
                        unlink($thumbnailPath);
                    }
                }
            }

            // Delete all games from database
            $sql = "DELETE FROM games";
            $stmt = $this->db->prepare($sql);

            return $stmt->execute();
        } catch (Exception $e) {
            error_log('Error deleting all games: ' . $e->getMessage());
            return false;
        }
    }
}
