<?php

namespace App\Models;

class Game extends Model
{
    protected string $table = 'games';
    protected array $fillable = [
        'title',
        'game_key',
        'description',
        'genre',
        'developer',
        'publisher',
        'release_date',
        'platform',
        'platform_game_id',
        'steam_app_id',
        'image_url',
        'image_aspect_ratio',
        'banner_url',
        'rating',
        'playtime_minutes',
        'completion_percentage',
        'is_installed',
        'is_favorite',
        'metadata',
        'platform_achievements',
        'background_image',
        'cover_image',
        'cover_aspect_ratio',
        'icon',
        'genres_json',
        'developers_json',
        'publishers_json',
        'tags_json',
        'features_json',
        'links_json',
        'series_json',
        'age_ratings_json',
        'play_count',
        'install_size',
        'completion_status',
        'critic_score',
        'community_score',
        'user_score',
        'is_custom_game',
        'installation_status',
        'added_at',
        'modified_at'
    ];

    protected array $casts = [
        'rating' => 'float',
        'playtime_minutes' => 'int',
        'completion_percentage' => 'int',
        'is_installed' => 'bool',
        'is_favorite' => 'bool',
        'release_date' => 'date',
        'last_played_at' => 'datetime',
        'platform_achievements' => 'array',
        'critic_score' => 'int',
        'community_score' => 'int',
        'user_score' => 'int',
        'play_count' => 'int',
        'install_size' => 'int',
        'installation_status' => 'int',
        'is_custom_game' => 'bool',
        'added_at' => 'datetime',
        'modified_at' => 'datetime',
        'genres_json' => 'array',
        'developers_json' => 'array',
        'publishers_json' => 'array',
        'tags_json' => 'array',
        'features_json' => 'array',
        'links_json' => 'array',
        'series_json' => 'array',
        'age_ratings_json' => 'array'
    ];

    public function source()
    {
        return new Source($this->pdo);
    }

    public function markAsPlayed(int $minutes = 60): bool
    {
        $this->playtime_minutes += $minutes;
        $this->last_played_at = date('Y-m-d H:i:s');
        return $this->update($this->id, [
            'playtime_minutes' => $this->playtime_minutes,
            'last_played_at' => $this->last_played_at
        ]);
    }

    public function toggleFavorite(): bool
    {
        return $this->update($this->id, [
            'is_favorite' => !$this->is_favorite
        ]);
    }

    public function toggleInstalled(): bool
    {
        return $this->update($this->id, [
            'is_installed' => !$this->is_installed
        ]);
    }

    public function updateCompletion(int $percentage): bool
    {
        return $this->update($this->id, [
            'completion_percentage' => min(100, max(0, $percentage))
        ]);
    }

    public function updateRating(float $rating): bool
    {
        return $this->update($this->id, [
            'rating' => min(10.0, max(0.0, $rating))
        ]);
    }

    public static function getStats(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT
                COUNT(*) as total_games,
                SUM(playtime_minutes) as total_playtime,
                AVG(rating) as avg_rating,
                COUNT(CASE WHEN is_favorite = 1 THEN 1 END) as favorite_games,
                COUNT(CASE WHEN is_installed = 1 THEN 1 END) as installed_games,
                AVG(completion_percentage) as avg_completion
            FROM games
        ");
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }

    public static function getRecent(\PDO $pdo, int $limit = 10): array
    {
        $stmt = $pdo->prepare("
            SELECT g.*, s.display_name as source_name
            FROM games g
            JOIN sources s ON g.source_id = s.id
            WHERE g.last_played_at IS NOT NULL
            ORDER BY g.last_played_at DESC
            LIMIT :limit
        ");
        $stmt->execute(['limit' => $limit]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Generate a game key for grouping games across platforms
     */
    public static function generateGameKey(string $title, string $platform = null): string
    {
        // Normalize title for consistent grouping
        $normalized = strtolower(trim($title));
        $normalized = preg_replace('/[^\w\s]/', '', $normalized); // Remove special characters
        $normalized = preg_replace('/\s+/', ' ', $normalized); // Normalize whitespace
        $normalized = trim($normalized);

        // Add platform to make it unique if provided
        if ($platform) {
            $normalized .= ' ' . strtolower($platform);
        }

        return $normalized;
    }

    /**
     * Find all platform versions of a game
     */
    public function getPlatformVersions(): array
    {
        if (!$this->game_key) {
            return [];
        }

        $stmt = $this->pdo->prepare("
            SELECT g.*, g.platform as source_name
            FROM games g
            
            WHERE g.game_key = :game_key
            ORDER BY g.platform, g.source_id
        ");
        $stmt->execute(['game_key' => $this->game_key]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Get games grouped by title for display
     */
    public static function getGroupedGames(\PDO $pdo, int $limit = 50): array
    {
        $stmt = $pdo->prepare("
            SELECT
                game_key,
                title,
                COUNT(*) as platform_count,
                GROUP_CONCAT(DISTINCT platform ORDER BY platform) as platforms,
                GROUP_CONCAT(DISTINCT source_id ORDER BY source_id) as source_ids,
                MAX(image_url) as image_url,
                MAX(last_played_at) as last_played_at,
                SUM(playtime_minutes) as total_playtime,
                MAX(completion_percentage) as max_completion,
                GROUP_CONCAT(DISTINCT genre ORDER BY genre) as genres
            FROM games
            WHERE game_key IS NOT NULL
            GROUP BY game_key, title
            ORDER BY last_played_at DESC, total_playtime DESC
            LIMIT :limit
        ");
        $stmt->execute(['limit' => $limit]);
        $games = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // Enhance each game with platform details
        foreach ($games as &$game) {
            $game['platforms'] = !empty($game['platforms']) ? array_unique(explode(',', $game['platforms'])) : [];
            $game['source_ids'] = !empty($game['source_ids']) ? array_unique(explode(',', $game['source_ids'])) : [];
            $game['genres'] = !empty($game['genres']) ? array_unique(array_filter(explode(',', $game['genres']))) : [];
        }

        return $games;
    }

    /**
     * Update platform-specific achievements
     */
    public function updatePlatformAchievements(array $achievements): bool
    {
        return $this->update($this->id, [
            'platform_achievements' => json_encode($achievements)
        ]);
    }

    /**
     * Update platform-specific statistics
     */
    public function updatePlatformStats(array $stats): bool
    {
        return $this->update($this->id, [
            'platform_stats' => json_encode($stats)
        ]);
    }

    /**
     * Get platform-specific achievements
     */
    public function getPlatformAchievements(): array
    {
        return $this->platform_achievements ?? [];
    }

    /**
     * Get platform-specific statistics
     */
    public function getPlatformStats(): array
    {
        return $this->platform_stats ?? [];
    }

    /**
     * Get total count of games for pagination
     */
    public static function getTotalCount(\PDO $pdo, string $search = '', array $genres = [], array $platforms = [], array $features = [], string $playtimeFilter = ''): int
    {
        $sql = "SELECT COUNT(*) as count FROM games WHERE game_key IS NOT NULL";
        $params = [];

        if (!empty($search)) {
            $sql .= " AND title LIKE :search";
            $params['search'] = "%{$search}%";
        }

        if (!empty($genres)) {
            $genreConditions = [];
            foreach ($genres as $index => $genre) {
                $genreConditions[] = "JSON_SEARCH(metadata, 'one', :genre_{$index}) IS NOT NULL";
                $params["genre_{$index}"] = $genre;
            }
            $sql .= " AND (" . implode(' OR ', $genreConditions) . ")";
        }

        if (!empty($platforms)) {
            $placeholders = [];
            foreach ($platforms as $index => $platform) {
                $placeholders[] = ":platform_{$index}";
                $params["platform_{$index}"] = $platform;
            }
            $sql .= " AND platform IN (" . implode(',', $placeholders) . ")";
        }

        if (!empty($features)) {
            $featureConditions = [];
            foreach ($features as $index => $feature) {
                $featureConditions[] = "JSON_SEARCH(metadata, 'one', :feature_{$index}) IS NOT NULL";
                $params["feature_{$index}"] = $feature;
            }
            $sql .= " AND (" . implode(' OR ', $featureConditions) . ")";
        }

        if (!empty($playtimeFilter)) {
            switch ($playtimeFilter) {
                case 'none':
                    $sql .= " AND (playtime_minutes IS NULL OR playtime_minutes = 0)";
                    break;
                case 'under_1h':
                    $sql .= " AND playtime_minutes > 0 AND playtime_minutes < 60";
                    break;
                case '1h_5h':
                    $sql .= " AND playtime_minutes >= 60 AND playtime_minutes < 300";
                    break;
                case '5h_10h':
                    $sql .= " AND playtime_minutes >= 300 AND playtime_minutes < 600";
                    break;
                case '10h_20h':
                    $sql .= " AND playtime_minutes >= 600 AND playtime_minutes < 1200";
                    break;
                case 'over_20h':
                    $sql .= " AND playtime_minutes >= 1200";
                    break;
            }
        }

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetch()['count'];
    }

    /**
     * Get grouped games with pagination and search support
     */
    public static function getGroupedGamesWithPagination(\PDO $pdo, int $page, int $perPage, string $search = '', array $genres = [], array $platforms = [], array $features = [], string $playtimeFilter = '', string $sort = 'title_asc'): array
    {
        $offset = ($page - 1) * $perPage;

        $sql = "
            SELECT
                id,
                game_key,
                title,
                COUNT(*) as platform_count,
                GROUP_CONCAT(DISTINCT platform ORDER BY platform) as platforms,
                GROUP_CONCAT(DISTINCT source_id ORDER BY source_id) as source_ids,
                MAX(image_url) as image_url,
                MAX(last_played_at) as last_played_at,
                SUM(playtime_minutes) as total_playtime,
                MAX(completion_percentage) as max_completion,
                GROUP_CONCAT(DISTINCT genre ORDER BY genre) as genres,
                MAX(release_date) as release_date,
                MAX(added_at) as added_at
            FROM games
            WHERE game_key IS NOT NULL
        ";

        $params = [];

        if (!empty($search)) {
            $sql .= " AND title LIKE :search";
            $params['search'] = "%{$search}%";
        }

        if (!empty($genres)) {
            $genreConditions = [];
            foreach ($genres as $index => $genre) {
                $genreConditions[] = "JSON_SEARCH(metadata, 'one', :genre_{$index}) IS NOT NULL";
                $params["genre_{$index}"] = $genre;
            }
            $sql .= " AND (" . implode(' OR ', $genreConditions) . ")";
        }

        if (!empty($platforms)) {
            $placeholders = [];
            foreach ($platforms as $index => $platform) {
                $placeholders[] = ":platform_{$index}";
                $params["platform_{$index}"] = $platform;
            }
            $sql .= " AND platform IN (" . implode(',', $placeholders) . ")";
        }

        if (!empty($features)) {
            $featureConditions = [];
            foreach ($features as $index => $feature) {
                $featureConditions[] = "JSON_SEARCH(metadata, 'one', :feature_{$index}) IS NOT NULL";
                $params["feature_{$index}"] = $feature;
            }
            $sql .= " AND (" . implode(' OR ', $featureConditions) . ")";
        }

        if (!empty($playtimeFilter)) {
            switch ($playtimeFilter) {
                case 'none':
                    $sql .= " AND (playtime_minutes IS NULL OR playtime_minutes = 0)";
                    break;
                case 'under_1h':
                    $sql .= " AND playtime_minutes > 0 AND playtime_minutes < 60";
                    break;
                case '1h_5h':
                    $sql .= " AND playtime_minutes >= 60 AND playtime_minutes < 300";
                    break;
                case '5h_10h':
                    $sql .= " AND playtime_minutes >= 300 AND playtime_minutes < 600";
                    break;
                case '10h_20h':
                    $sql .= " AND playtime_minutes >= 600 AND playtime_minutes < 1200";
                    break;
                case 'over_20h':
                    $sql .= " AND playtime_minutes >= 1200";
                    break;
            }
        }

        // Add sorting
        $sortOptions = [
            'title_asc' => 'title ASC',
            'title_desc' => 'title DESC',
            'year_asc' => 'release_date ASC NULLS LAST',
            'year_desc' => 'release_date DESC NULLS LAST',
            'playtime_asc' => 'total_playtime ASC',
            'playtime_desc' => 'total_playtime DESC',
            'completion_asc' => 'max_completion ASC NULLS LAST',
            'completion_desc' => 'max_completion DESC NULLS LAST',
            'added_asc' => 'added_at ASC NULLS LAST',
            'added_desc' => 'added_at DESC NULLS LAST',
            'last_played_asc' => 'last_played_at ASC NULLS LAST',
            'last_played_desc' => 'last_played_at DESC NULLS LAST',
            'platforms_asc' => 'platform_count ASC',
            'platforms_desc' => 'platform_count DESC'
        ];
        
        $sortClause = $sortOptions[$sort] ?? 'title ASC';
        $sql .= " GROUP BY game_key, title ORDER BY $sortClause LIMIT :limit OFFSET :offset";

        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':limit', $perPage, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);

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

        $stmt->execute();
        $games = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // Enhance each game with platform details
        foreach ($games as &$game) {
            $game['platforms'] = !empty($game['platforms']) ? array_unique(explode(',', $game['platforms'])) : [];
            $game['source_ids'] = !empty($game['source_ids']) ? array_unique(explode(',', $game['source_ids'])) : [];
            $game['genres'] = !empty($game['genres']) ? array_unique(array_filter(explode(',', $game['genres']))) : [];
        }

        return $games;
    }

    /**
     * Get all unique genres from the games table
     * Combines both Playnite JSON genres and regular genre field
     */
    public function getGenres(): array
    {
        // First get genres from the regular genre field
        $stmt = $this->pdo->query("SELECT DISTINCT genre FROM {$this->table} WHERE genre IS NOT NULL AND genre != ''");
        $genres = [];
        $results = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        // Flatten and deduplicate genres
        foreach ($results as $genreList) {
            $genreArray = array_map('trim', explode(',', $genreList));
            $genres = array_merge($genres, $genreArray);
        }
        
        // Also get genres from Playnite JSON data
        $stmt = $this->pdo->query("SELECT genres_json FROM {$this->table} WHERE genres_json IS NOT NULL AND genres_json != '[]'");
        $jsonGenres = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        foreach ($jsonGenres as $json) {
            $decoded = json_decode($json, true);
            if (is_array($decoded)) {
                foreach ($decoded as $genre) {
                    if (is_array($genre) && isset($genre['Name'])) {
                        $genres[] = $genre['Name'];
                    } elseif (is_string($genre)) {
                        $genres[] = $genre;
                    }
                }
            }
        }
        
        $genres = array_unique($genres);
        sort($genres);
        
        return array_values(array_filter($genres));
    }

    /**
     * Get Playnite-specific developers
     */
    public function getDevelopers(): array
    {
        return $this->developers_json ?? [];
    }

    /**
     * Get Playnite-specific publishers
     */
    public function getPublishers(): array
    {
        return $this->publishers_json ?? [];
    }

    /**
     * Get Playnite-specific tags
     */
    public function getTags(): array
    {
        return $this->tags_json ?? [];
    }
    
    /**
     * Get all unique platforms from the games table
     */
    public function getPlatforms(): array
    {
        $stmt = $this->pdo->query("SELECT DISTINCT platform FROM {$this->table} WHERE platform IS NOT NULL AND platform != '' ORDER BY platform");
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }
    

    /**
     * Get Playnite-specific features
     */
    public function getFeatures(): array
    {
        return $this->features_json ?? [];
    }

    /**
     * Get Playnite-specific links
     */
    public function getLinks(): array
    {
        return $this->links_json ?? [];
    }

    /**
     * Get available genres for filtering
     */
    public static function getAvailableGenres(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT metadata
            FROM games
            WHERE metadata IS NOT NULL AND metadata != '' AND metadata != '{}'
        ");
        $genres = [];
        $results = $stmt->fetchAll(\PDO::FETCH_COLUMN);

        foreach ($results as $json) {
            $decoded = json_decode($json, true);
            if (is_array($decoded) && isset($decoded['genres']) && is_array($decoded['genres'])) {
                foreach ($decoded['genres'] as $genre) {
                    if (is_string($genre)) {
                        $genres[] = $genre;
                    }
                }
            }
        }

        $genres = array_unique($genres);
        sort($genres);

        return array_values(array_filter($genres));
    }

    /**
     * Get available platforms for filtering
     */
    public static function getAvailablePlatforms(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT DISTINCT platform
            FROM games
            WHERE platform IS NOT NULL AND platform != ''
            ORDER BY platform
        ");
        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }

    /**
     * Get available features for filtering
     */
    public static function getAvailableFeatures(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT metadata
            FROM games
            WHERE metadata IS NOT NULL AND metadata != '' AND metadata != '{}'
        ");
        $features = [];
        $results = $stmt->fetchAll(\PDO::FETCH_COLUMN);

        foreach ($results as $json) {
            $decoded = json_decode($json, true);
            if (is_array($decoded) && isset($decoded['features']) && is_array($decoded['features'])) {
                foreach ($decoded['features'] as $feature) {
                    if (is_string($feature)) {
                        $features[] = $feature;
                    }
                }
            }
        }

        $features = array_unique($features);
        sort($features);

        return array_values(array_filter($features));
    }

    /**
     * Check if game has rich Playnite data
     */
    public function hasPlayniteData(): bool
    {
        return !empty($this->genres_json) || !empty($this->tags_json) ||
               !empty($this->links_json) || !empty($this->background_image);
    }
}