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(gf.id) 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
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(gf.id) AS total_unavailable
FROM 
    platforms p
LEFT JOIN
    games gg ON gg.platform == p.id
LEFT JOIN 
    games_in_present gp ON gp.id == gg.id
LEFT JOIN 
    games_in_future gf ON gf.id == gg.id
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;