<?php

namespace App\Models;

class TvShow extends Model
{
    protected string $table = 'tv_shows';
    protected array $fillable = [
        'title',
        'overview',
        'creator',
        'genre',
        'cast',
        'first_air_date',
        'last_air_date',
        'number_of_seasons',
        'number_of_episodes',
        'rating',
        'imdb_id',
        'tmdb_id',
        'tvdb_id',
        'poster_url',
        'backdrop_url',
        'is_favorite',
        'metadata',
        'source_id'
    ];

    protected array $casts = [
        'number_of_seasons' => 'int',
        'number_of_episodes' => 'int',
        'rating' => 'float',
        'is_favorite' => 'bool',
        'first_air_date' => 'date',
        'last_air_date' => 'date',
        'metadata' => 'array'
    ];

    /**
     * Remove an actor from this TV show
     */
    public function removeActor(int $actorId): bool
    {
        $stmt = $this->pdo->prepare("
            DELETE FROM actor_tv_show
            WHERE tv_show_id = :tv_show_id AND actor_id = :actor_id
        ");
        return $stmt->execute([
            'tv_show_id' => $this->id,
            'actor_id' => $actorId
        ]);
    }

    /**
     * Get TV show statistics
     */
    public static function getStats(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT
                COUNT(*) as total_tv_shows,
                COUNT(CASE WHEN is_favorite = 1 THEN 1 END) as favorite_tv_shows,
                AVG(rating) as avg_rating
            FROM tv_shows
        ");
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }

    /**
     * Get total count with optional search and filters
     */
    public static function getTotalCount(
        \PDO $pdo, 
        string $search = '', 
        array $genres = [], 
        array $networks = [],
        array $statuses = []
    ): int {
        $sql = "SELECT COUNT(*) as count FROM tv_shows t JOIN sources s ON t.source_id = s.id";
        $params = [];
        $whereClauses = [];

        if (!empty($search)) {
            $whereClauses[] = "(t.title LIKE :search OR t.overview LIKE :search)";
            $params['search'] = "%{$search}%";
        }

        if (!empty($genres)) {
            $genreConditions = [];
            foreach ($genres as $i => $genre) {
                $param = ":genre_{$i}";
                $genreConditions[] = "FIND_IN_SET({$param}, t.genre) > 0";
                $params["genre_{$i}"] = $genre;
            }
            $whereClauses[] = '(' . implode(' OR ', $genreConditions) . ')';
        }

        if (!empty($networks)) {
            $networkConditions = [];
            foreach ($networks as $i => $network) {
                $param = ":network_{$i}";
                $networkConditions[] = "t.networks LIKE {$param}";
                $params["network_{$i}"] = "%\"name\":\"{$network}\"%";
            }
            $whereClauses[] = '(' . implode(' OR ', $networkConditions) . ')';
        }

        if (!empty($statuses)) {
            $statusConditions = [];
            foreach ($statuses as $i => $status) {
                $param = ":status_{$i}";
                $statusConditions[] = "t.status = {$param}";
                $params["status_{$i}"] = $status;
            }
            $whereClauses[] = '(' . implode(' OR ', $statusConditions) . ')';
        }

        if (!empty($whereClauses)) {
            $sql .= ' WHERE ' . implode(' AND ', $whereClauses);
        }

        $stmt = $pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        return (int) $stmt->fetch(\PDO::FETCH_COLUMN);
    }

    /**
     * Get all TV shows with pagination and optional search
     */
    public static function getAllWithPagination(\PDO $pdo, int $page, int $perPage, string $search = '', array $genres = [], array $years = [], string $sort = 'title_asc'): array
    {
        $offset = ($page - 1) * $perPage;

        $sql = "
            SELECT t.*, s.display_name as source_name
            FROM tv_shows t
            JOIN sources s ON t.source_id = s.id
        ";
        $params = [];

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

        if (!empty($genres)) {
            $placeholders = [];
            foreach ($genres as $index => $genre) {
                $placeholders[] = ":genre_{$index}";
                $params["genre_{$index}"] = $genre;
            }
            $whereClause = !empty($search) ? " AND" : " WHERE";
            $sql .= $whereClause . " t.genre IN (" . implode(',', $placeholders) . ")";
        }

        if (!empty($years)) {
            $placeholders = [];
            foreach ($years as $index => $year) {
                $placeholders[] = ":year_{$index}";
                $params["year_{$index}"] = $year;
            }
            $whereClause = (!empty($search) || !empty($genres)) ? " AND" : " WHERE";
            $sql .= $whereClause . " YEAR(first_air_date) IN (" . implode(',', $placeholders) . ")";
        }

        // Add sorting
        $sortMap = [
            'title_asc' => 't.title ASC',
            'title_desc' => 't.title DESC',
            'year_desc' => 't.first_air_date DESC NULLS LAST',
            'year_asc' => 't.first_air_date ASC NULLS LAST',
            'rating_desc' => 't.rating DESC NULLS LAST',
            'rating_asc' => 't.rating ASC NULLS LAST',
            'recent' => 't.created_at DESC',
            'oldest' => 't.created_at ASC',
        ];

        $sortClause = $sortMap[$sort] ?? 't.title ASC';
        $sql .= " 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();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

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

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

    /**
     * Get the source relationship
     */
    public function source(): ?Source
    {
        $stmt = $this->pdo->prepare("SELECT * FROM sources WHERE id = :source_id");
        $stmt->execute(['source_id' => $this->source_id]);
        $sourceData = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $sourceData ? new Source($this->pdo, $sourceData) : null;
    }

    /**
     * Get paginated TV shows with filters
     */
    public static function getPaginated(
        \PDO $pdo,
        int $page,
        int $perPage,
        string $search = '',
        array $genres = [],
        array $networks = [],
        array $statuses = [],
        string $sort = 'title_asc'
    ): array {
        $offset = ($page - 1) * $perPage;
        
        $sql = "
            SELECT t.*, s.display_name as source_name
            FROM tv_shows t
            JOIN sources s ON t.source_id = s.id
        ";
        $params = [];
        $whereClauses = [];

        if (!empty($search)) {
            $whereClauses[] = "(t.title LIKE :search OR t.overview LIKE :search)";
            $params['search'] = "%{$search}%";
        }

        if (!empty($genres)) {
            $genreConditions = [];
            foreach ($genres as $i => $genre) {
                $param = ":genre_{$i}";
                $genreConditions[] = "FIND_IN_SET({$param}, t.genre) > 0";
                $params["genre_{$i}"] = $genre;
            }
            $whereClauses[] = '(' . implode(' OR ', $genreConditions) . ')';
        }

        if (!empty($networks)) {
            $networkConditions = [];
            foreach ($networks as $i => $network) {
                $param = ":network_{$i}";
                $networkConditions[] = "t.networks LIKE {$param}";
                $params["network_{$i}"] = "%\"name\":\"{$network}\"%";
            }
            $whereClauses[] = '(' . implode(' OR ', $networkConditions) . ')';
        }

        if (!empty($statuses)) {
            $statusConditions = [];
            foreach ($statuses as $i => $status) {
                $param = ":status_{$i}";
                $statusConditions[] = "t.status = {$param}";
                $params["status_{$i}"] = $status;
            }
            $whereClauses[] = '(' . implode(' OR ', $statusConditions) . ')';
        }

        if (!empty($whereClauses)) {
            $sql .= ' WHERE ' . implode(' AND ', $whereClauses);
        }

        // Add sorting
        $sortMap = [
            'title_asc' => 't.title ASC',
            'title_desc' => 't.title DESC',
            'rating_desc' => 't.vote_average DESC NULLS LAST',
            'rating_asc' => 't.vote_average ASC NULLS LAST',
            'newest' => 't.first_air_date DESC NULLS LAST',
            'oldest' => 't.first_air_date ASC NULLS LAST',
        ];
        
        $sortClause = $sortMap[$sort] ?? 't.title ASC';
        $sql .= " 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();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Get all available genres from TV shows
     */
    public static function getGenres(\PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.genre, ',', n.n), ',', -1)) as genre
            FROM tv_shows t
            JOIN (
                SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
                SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
            ) n
            WHERE n.n <= LENGTH(t.genre) - LENGTH(REPLACE(t.genre, ',', '')) + 1
            AND t.genre IS NOT NULL AND t.genre != ''
            ORDER BY genre
        ");
        
        $genres = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        return array_values(array_filter(array_unique($genres)));
    }

    /**
     * Get all available networks from TV shows
     */
    public static function getNetworks(\PDO $pdo): array
    {
        $stmt = $pdo->query("SELECT DISTINCT networks FROM tv_shows WHERE networks IS NOT NULL AND networks != ''");
        $networks = [];
        
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            $showNetworks = json_decode($row['networks'], true) ?: [];
            foreach ($showNetworks as $network) {
                if (isset($network['name'])) {
                    $networks[$network['name']] = $network['name'];
                }
            }
        }
        
        sort($networks);
        return array_values($networks);
    }

    public function getSeasonsWithEpisodes(): array
    {
        try {
            // Get all episodes for this TV show, grouped by season
            $stmt = $this->pdo->prepare("
                SELECT season_number,
                       COUNT(*) as episode_count,
                       SUM(CASE WHEN watched = 1 THEN 1 ELSE 0 END) as watched_episodes
                FROM tv_episodes
                WHERE tv_show_id = :tv_show_id
                GROUP BY season_number
                ORDER BY season_number ASC
            ");
            $stmt->execute(['tv_show_id' => $this->id]);
            $seasonStats = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $seasons = [];

            // For each season, get the episodes and create a season object
            foreach ($seasonStats as $stat) {
                $seasonNumber = $stat['season_number'];

                // Get episodes for this season
                $stmt = $this->pdo->prepare("
                    SELECT e.*
                    FROM tv_episodes e
                    WHERE e.tv_show_id = :tv_show_id AND e.season_number = :season_number
                    ORDER BY e.episode_number ASC
                ");
                $stmt->execute([
                    'tv_show_id' => $this->id,
                    'season_number' => $seasonNumber
                ]);
                $episodes = $stmt->fetchAll(\PDO::FETCH_ASSOC);

                // Add actors for each episode
                foreach ($episodes as &$episode) {
                    try {
                        $episodeStmt = $this->pdo->prepare("
                            SELECT a.*
                            FROM actors a
                            JOIN actor_tv_episode ate ON a.id = ate.actor_id
                            WHERE ate.tv_episode_id = :tv_episode_id
                            ORDER BY a.name ASC
                        ");
                        $episodeStmt->execute(['tv_episode_id' => $episode['id']]);
                        $episode['actors'] = $episodeStmt->fetchAll(\PDO::FETCH_ASSOC);
                    } catch (\Exception $e) {
                        $episode['actors'] = [];
                    }
                }

                // Create a season object (simulating the old seasons table structure)
                $seasons[] = [
                    'id' => null, // No seasons table, so no ID
                    'season_number' => $seasonNumber,
                    'episode_count' => (int)$stat['episode_count'],
                    'watched_episodes' => (int)$stat['watched_episodes'],
                    'episodes' => $episodes
                ];
            }

            return $seasons;
        } catch (\Exception $e) {
            // Return empty array if tables don't exist or query fails
            return [];
        }
    }

    /**
     * Get all actors for this TV show (from all episodes)
     */
    public function getActors(): array
    {
        try {
            $stmt = $this->pdo->prepare("
                SELECT DISTINCT a.*
                FROM actors a
                JOIN actor_tv_episode ate ON a.id = ate.actor_id
                JOIN tv_episodes te ON ate.tv_episode_id = te.id
                WHERE te.tv_show_id = :tv_show_id
                ORDER BY a.name ASC
            ");
            $stmt->execute(['tv_show_id' => $this->id]);
            return $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Exception $e) {
            // Return empty array if tables don't exist or query fails
            return [];
        }
    }

/**
 * Get similar TV shows based on genres
 */
public function getSimilarShows(int $limit = 6): array
{
    $genres = $this->genre ? explode(',', $this->genre) : [];
    $placeholders = str_repeat('?,', count($genres) - 1) . '?';
    
    $sql = "
        SELECT t.*, 
               COUNT(DISTINCT g.genre) as matching_genres
        FROM tv_shows t
        CROSS JOIN (SELECT TRIM(value) as genre 
                   FROM json_each('[\"" . str_replace(',', '","', str_replace('"', '\\"', $this->genre)) . "\"]')
                   WHERE value != '') g
        WHERE t.id != ? 
          AND t.genre LIKE '%' || g.genre || '%'
        GROUP BY t.id
        HAVING matching_genres > 0
        ORDER BY matching_genres DESC, t.rating DESC
        LIMIT ?
    ";
    
    $params = array_merge([$this->id, $limit]);
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

/**
 * Record that this TV show was viewed
 */
public function recordView(): bool
{
    $stmt = $this->pdo->prepare("
        INSERT OR REPLACE INTO tv_show_views 
        (tv_show_id, view_count, last_viewed_at) 
        VALUES (?, COALESCE((SELECT view_count FROM tv_show_views WHERE tv_show_id = ?), 0) + 1, CURRENT_TIMESTAMP)
    ");
    return $stmt->execute([$this->id, $this->id]);
}

/**
 * Get all available genres from TV shows
 */
public static function getAvailableGenres(\PDO $pdo): array
{
    $stmt = $pdo->query("
        SELECT DISTINCT genre
        FROM tv_shows
        WHERE genre IS NOT NULL AND genre != ''
        ORDER BY genre
    ");
    return $stmt->fetchAll(\PDO::FETCH_COLUMN);
}

/**
 * Get all available sources from TV shows
 */
public static function getAvailableSources(\PDO $pdo): array
{
    $stmt = $pdo->query("
        SELECT DISTINCT s.display_name
        FROM tv_shows t
        JOIN sources s ON t.source_id = s.id
        WHERE t.source_id IS NOT NULL
        ORDER BY s.display_name
    ");
    return $stmt->fetchAll(\PDO::FETCH_COLUMN);
}

/**
 * Get all TV shows with filtering and pagination
 */
public function findAll(array $filters = [], int $limit = null, int $offset = 0): array
{
    $sql = "
        SELECT t.*, s.display_name as source_name
        FROM tv_shows t
        LEFT JOIN sources s ON t.source_id = s.id
    ";
    $params = [];
    $whereClauses = [];

    // Search filter
    if (!empty($filters['search'])) {
        $whereClauses[] = "(t.title LIKE :search OR t.overview LIKE :search)";
        $params['search'] = "%{$filters['search']}%";
    }

    // Genre filter
    if (!empty($filters['genre'])) {
        $whereClauses[] = "t.genre = :genre";
        $params['genre'] = $filters['genre'];
    }

    // Year filter
    if (!empty($filters['year'])) {
        $whereClauses[] = "YEAR(t.first_air_date) = :year";
        $params['year'] = $filters['year'];
    }

    // Source filter
    if (!empty($filters['sources'])) {
        $sources = is_array($filters['sources']) ? $filters['sources'] : [$filters['sources']];
        $placeholders = [];
        foreach ($sources as $index => $source) {
            $placeholders[] = ":source_{$index}";
            $params["source_{$index}"] = $source;
        }
        $whereClauses[] = "s.display_name IN (" . implode(',', $placeholders) . ")";
    }

    // Combine WHERE clauses
    if (!empty($whereClauses)) {
        $sql .= " WHERE " . implode(' AND ', $whereClauses);
    }

    // Add ordering and pagination
    $sql .= " ORDER BY t.first_air_date DESC, t.title ASC";

    if ($limit) {
        $sql .= " LIMIT :limit OFFSET :offset";
        $params['limit'] = $limit;
        $params['offset'] = $offset;
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
    
    // Process metadata to extract additional fields
    foreach ($results as &$tvShow) {
        if (!empty($tvShow['metadata'])) {
            $metadata = json_decode($tvShow['metadata'], true);
            
            // Extract additional fields from metadata if available
            // This can be expanded based on your metadata structure
        }
    }
    
    return $results;
}

/**
 * Count TV shows with filters
 */
public function count(array $filters = []): int
{
    $sql = "
        SELECT COUNT(*) as total
        FROM tv_shows t
        LEFT JOIN sources s ON t.source_id = s.id
    ";
    $params = [];
    $whereClauses = [];

    // Search filter
    if (!empty($filters['search'])) {
        $whereClauses[] = "(t.title LIKE :search OR t.overview LIKE :search)";
        $params['search'] = "%{$filters['search']}%";
    }

    // Genre filter
    if (!empty($filters['genre'])) {
        $whereClauses[] = "t.genre = :genre";
        $params['genre'] = $filters['genre'];
    }

    // Year filter
    if (!empty($filters['year'])) {
        $whereClauses[] = "YEAR(t.first_air_date) = :year";
        $params['year'] = $filters['year'];
    }

    // Source filter
    if (!empty($filters['sources'])) {
        $sources = is_array($filters['sources']) ? $filters['sources'] : [$filters['sources']];
        $placeholders = [];
        foreach ($sources as $index => $source) {
            $placeholders[] = ":source_{$index}";
            $params["source_{$index}"] = $source;
        }
        $whereClauses[] = "s.display_name IN (" . implode(',', $placeholders) . ")";
    }

    // Combine WHERE clauses
    if (!empty($whereClauses)) {
        $sql .= " WHERE " . implode(' AND ', $whereClauses);
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    $result = $stmt->fetch(\PDO::FETCH_ASSOC);
    return (int)$result['total'];
}

/**
 * Get all available years from TV shows' first_air_date
 */
public static function getAvailableYears(\PDO $pdo): array
{
    $stmt = $pdo->query("
        SELECT DISTINCT YEAR(first_air_date) as year
        FROM tv_shows
        WHERE first_air_date IS NOT NULL
        ORDER BY year DESC
    ");
    return $stmt->fetchAll(\PDO::FETCH_COLUMN);
}

}