CREATE VIEW $viewName AS

SELECT
game_providers.parent_provider AS game_provider_parent_provider,
game_providers.title AS game_provider_name,
CASE
	WHEN game_offerings.slug IN ('sportsbetting', 'esports') THEN 'sportsbetting'
	ELSE game_offerings.slug
END AS game_offering_slug,
CASE
	WHEN game_types.game_offering IN ('Live Casino', 'SportsBetting', 'Esports') THEN 'game_offerings'
	ELSE 'game_types'
END AS game_type_category,
CASE
	WHEN game_types.game_offering = 'Live Casino' THEN game_types.game_offering
	WHEN game_types.game_offering IN ('Sportsbetting', 'Esports') THEN 'Sportsbetting'
	ELSE game_types.name
END AS game_type,
CASE
	WHEN game_types.game_offering = 'Live Casino' THEN game_offerings.slug
	WHEN game_types.game_offering IN ('Sportsbetting', 'Esports') THEN (SELECT slug FROM game_offerings WHERE name = 'Sportsbetting')
	ELSE game_types.slug
END AS game_type_slug,
game_providers.is_active AS game_provider_is_active,
CASE
	WHEN game_types.game_offering = 'Live Casino' THEN game_offerings.key_token
	WHEN game_types.game_offering IN ('Sportsbetting', 'Esports') THEN (SELECT key_token FROM game_offerings WHERE name = 'Sportsbetting')
	ELSE game_types.key_token
END AS game_type_key_token,
game_providers.key_token as game_provider_key_token
FROM game_providers
LEFT JOIN game_provider_games ON game_provider_games.game_provider_id = game_providers.id
LEFT JOIN game_types ON game_types.id = game_provider_games.game_type_id
LEFT JOIN game_offerings ON game_offerings.name = game_types.game_offering
WHERE game_provider_games.id IS NOT NULL
AND game_types.slug != 'lobby'
GROUP BY game_providers.key_token,
CASE
	WHEN game_types.game_offering = 'Live Casino' THEN game_types.game_offering
	WHEN game_types.game_offering IN ('Sportsbetting', 'Esports') THEN 'Sportsbetting'
	ELSE game_types.name
END


