CREATE VIEW seasonslist
AS
SELECT
        seasons.*,
        banner.url AS BannerPath,
        fanart.url AS FanartPath,
        landscape.url AS LandscapePath,
        poster.url AS PosterPath,
        CASE WHEN seasons.Season IS NOT -1 THEN COUNT(DISTINCT episodelist.idEpisode) ELSE NULL END AS Episodes,
        COUNT(DISTINCT CASE WHEN episodelist.Playcount IS NOT NULL THEN episodelist.idEpisode ELSE NULL END) AS Playcount,
        CASE WHEN seasons.Season IS NOT -1 THEN CASE WHEN COUNT(DISTINCT episodelist.idEpisode) IS NOT 0 AND COUNT(DISTINCT episodelist.idEpisode) = COUNT(DISTINCT CASE WHEN episodelist.Playcount IS NOT NULL THEN episodelist.idEpisode ELSE NULL END) THEN 1 ELSE 0 END ELSE NULL END AS HasWatched,
        COUNT(DISTINCT CASE WHEN episodelist.New IS 1 THEN episodelist.idEpisode ELSE NULL END) AS NewEpisodes,
        CASE WHEN COUNT(DISTINCT episodelist.idEpisode) IS NOT 0 THEN 0 ELSE 1 END AS Missing
        FROM
        seasons
        LEFT OUTER JOIN art AS banner ON (banner.media_id = seasons.idSeason)
        AND (banner.media_type = 'season')
        AND (banner.type = 'banner')
        LEFT OUTER JOIN art AS fanart ON (fanart.media_id = seasons.idSeason)
        AND (fanart.media_type = 'season')
        AND (fanart.type = 'fanart')
        LEFT OUTER JOIN art AS landscape ON (landscape.media_id = seasons.idSeason)
        AND (landscape.media_type = 'season')
        AND (landscape.type = 'landscape')
        LEFT OUTER JOIN art AS poster ON (poster.media_id = seasons.idSeason)
        AND (poster.media_type = 'season')
        AND (poster.type = 'poster')
        LEFT OUTER JOIN episodelist ON (seasons.Season = episodelist.Season)
        AND (seasons.idShow = episodelist.idShow)
        AND (episodelist.Missing = 0)
        GROUP BY
        seasons.idSeason;