Views

Code to re-generate views used in database

all_series_as_json

CREATE VIEW all_series_as_json
AS
SELECT 
    s.name, 
    '[' || GROUP_CONCAT(
        JSON_OBJECT(
            'id', g.id,
            'title', g.title,
            'videoId', g.videoId,
            'playlistId', g.playlistId,
            'duration', g.duration,
			'platform', g.platform
        )
    ) || ']' as 'items'
FROM series_games sg
INNER JOIN games g ON g.id = sg.game
INNER JOIN series s ON s.id = sg.serie
GROUP BY s.id

games_available_time

CREATE VIEW games_available_time 
AS
SELECT 
    SUM(hours) AS hours,
    SUM(minutes) + (SUM(seconds) / 60) AS minutes,
    SUM(seconds) % 60 AS seconds
FROM (
    SELECT 
        CAST(substr(duration, 1, 2) AS INTEGER) AS hours,
        CAST(substr(duration, 4, 2) AS INTEGER) AS minutes,
        CAST(substr(duration, 7, 2) AS INTEGER) AS seconds
    FROM games_in_present
)

games_genres_agg

CREATE VIEW games_genres_agg
AS
SELECT game, JSON_GROUP_ARRAY(genre) as 'genres'
FROM games_genres
GROUP BY game

games_in_future

CREATE VIEW games_in_future
AS 
SELECT *
FROM games_schedules
WHERE ( DATE('now') <= availableAt OR availableAt <= DATE('now')) AND (endAt IS NULL OR DATE('now') <= endAt)
ORDER BY availableAt ASC

games_in_present

CREATE VIEW games_in_present
AS 
SELECT 
	g.id, 
	g.title, 
	g.videoId, 
	g.playlistId, 
	g.releaseDate, 
	g.duration, 
	g.platform, 
	COALESCE(agg.genres, JSON('[]') ) as 'genres'
FROM games g
LEFT JOIN games_genres_agg agg
ON agg.game == g.id
WHERE g.id NOT IN (
	SELECT id
	FROM games_schedules
	WHERE DATE('now') <= availableAt
)
ORDER BY g.title ASC, g.releaseDate ASC, g.duration ASC

games_total_time

CREATE VIEW games_total_time 
AS
SELECT 
    SUM(hours) AS hours,
    SUM(minutes) + (SUM(seconds) / 60) AS minutes,
    SUM(seconds) % 60 AS seconds
FROM (
    SELECT 
        CAST(substr(duration, 1, 2) AS INTEGER) AS hours,
        CAST(substr(duration, 4, 2) AS INTEGER) AS minutes,
        CAST(substr(duration, 7, 2) AS INTEGER) AS seconds
    FROM games
)

games_unavailable_time

CREATE VIEW games_unavailable_time 
AS
SELECT 
    SUM(hours) AS hours,
    SUM(minutes) + (SUM(seconds) / 60) AS minutes,
    SUM(seconds) % 60 AS seconds
FROM (
    SELECT 
        CAST(substr(duration, 1, 2) AS INTEGER) AS hours,
        CAST(substr(duration, 4, 2) AS INTEGER) AS minutes,
        CAST(substr(duration, 7, 2) AS INTEGER) AS seconds
    FROM games
	WHERE id IN (SELECT id FROM games_in_future)
)

genres_stats

CREATE VIEW genres_stats AS
SELECT 
    g.id AS id,
    g.name AS genre,
    COUNT(gg.game) AS total,
    COUNT(gp.id) AS total_available,
    COUNT(DISTINCT CASE WHEN gf.id IS NOT NULL AND gp.id IS NULL THEN gf.id END) AS total_unavailable
FROM 
    genres g
LEFT JOIN 
    games_genres gg ON g.id = gg.genre
LEFT JOIN 
    games_in_present gp ON gg.game = gp.id
LEFT JOIN 
    games_in_future gf ON gg.game = gf.id AND gf.id NOT IN (SELECT id FROM games_in_present)
GROUP BY 
    g.id
ORDER BY
    total DESC,
    total_available DESC,
    total_unavailable DESC;

platforms_stats

CREATE VIEW platforms_stats AS
SELECT 
    p.id AS id,
    p.name AS platform,
    COUNT(gg.id) AS total,
    COUNT(gp.id) AS total_available,
    COUNT(DISTINCT CASE WHEN gf.id IS NOT NULL AND gp.id IS NULL THEN gf.id END) AS total_unavailable
FROM 
    platforms p
LEFT JOIN
    games gg ON gg.platform = p.id AND gg.id NOT IN (SELECT dlc FROM games_dlcs)
LEFT JOIN 
    games_in_present gp ON gp.id = gg.id
LEFT JOIN 
    games_in_future gf ON gf.id = gg.id AND gf.id NOT IN (SELECT id FROM games_in_present)
GROUP BY 
    p.id
ORDER BY
    total DESC,
    total_available DESC,
    total_unavailable DESC;

series_as_json

CREATE VIEW series_as_json AS
SELECT 
    s.name,
    (
        SELECT 
            '[' || GROUP_CONCAT(
                JSON_OBJECT(
                    'id', g.id,
                    'title', g.title,
                    'videoId', g.videoId,
                    'playlistId', g.playlistId,
                    'duration', g.duration,
                    'platform', g.platform
                ) ORDER BY sg."order"
            ) || ']' 
        FROM series_games sg
        INNER JOIN games_in_present g ON g.id = sg.game
        WHERE sg.serie = s.id
    ) AS items
FROM series s
WHERE EXISTS (
    SELECT 1
    FROM series_games sg
    INNER JOIN games_in_present g ON g.id = sg.game
    WHERE sg.serie = s.id
) 
ORDER BY s.name;

dlcs_as_json

CREATE VIEW dlcs_as_json
AS
SELECT 
    g.title AS game_title,
    (
        SELECT 
            '[' || GROUP_CONCAT(
                JSON_OBJECT(
                    'id', dlc.id,
                    'title', dlc.title,
                    'videoId', dlc.videoId,
                    'playlistId', dlc.playlistId,
                    'duration', dlc.duration,
                    'platform', dlc.platform
                ) ORDER BY gd."order"
            ) || ']' 
        FROM games_dlcs gd
        INNER JOIN games_in_present dlc ON dlc.id = gd.dlc
        WHERE gd.game = g.id
    ) AS dlcs
FROM games_in_present g
WHERE EXISTS (
    SELECT 1
    FROM games_dlcs gd
    INNER JOIN games_in_present dlc ON dlc.id = gd.dlc
    WHERE gd.game = g.id
)
ORDER BY g.title;