---

-- 1. Создаём таблицу жанров
CREATE TABLE genres (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 2. Создаём таблицу игр со связью (внешний ключ)
CREATE TABLE games (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    min_age INT NOT NULL,
    genre_id INT,
    FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);

-- 3. Добавляем 5 жанров
INSERT INTO genres (name) VALUES 
('RPG'), 
('Shooter'), 
('Strategy'), 
('Adventure'), 
('Simulation');

-- 4. Добавляем по 5 игр на каждый жанр (всего 25 игр)

-- Жанр 1: RPG (genre_id = 1)
INSERT INTO games (name, min_age, genre_id) VALUES 
('The Witcher 3', 18, 1),
('Skyrim', 16, 1),
('Baldurs Gate 3', 18, 1),
('Fallout 4', 16, 1),
('Mass Effect 2', 16, 1);

-- Жанр 2: Shooter (genre_id = 2)
INSERT INTO games (name, min_age, genre_id) VALUES 
('Counter-Strike 2', 16, 2),
('Call of Duty: MW3', 18, 2),
('Battlefield 2042', 18, 2),
('DOOM Eternal', 18, 2),
('Valorant', 16, 2);

-- Жанр 3: Strategy (genre_id = 3)
INSERT INTO games (name, min_age, genre_id) VALUES 
('Civilization VI', 12, 3),
('StarCraft II', 12, 3),
('Age of Empires IV', 12, 3),
('XCOM 2', 16, 3),
('Total War: Warhammer III', 16, 3);

-- Жанр 4: Adventure (genre_id = 4)
INSERT INTO games (name, min_age, genre_id) VALUES 
('Uncharted 4', 16, 4),
('Tomb Raider', 16, 4),
('Red Dead Redemption 2', 18, 4),
('God of War', 18, 4),
('Assassins Creed Valhalla', 18, 4);

-- Жанр 5: Simulation (genre_id = 5)
INSERT INTO games (name, min_age, genre_id) VALUES 
('The Sims 4', 12, 5),
('Cities: Skylines II', 0, 5),
('Euro Truck Simulator 2', 0, 5),
('Farming Simulator 22', 0, 5),
('Microsoft Flight Simulator', 0, 5);

-- 5. Проверка (выведет все игры с названиями их жанров)
SELECT games.name AS "Игра", genres.name AS "Жанр", games.min_age AS "Мин_возраст"
FROM games
JOIN genres ON games.genre_id = genres.id;