/* zad 1 */
DROP DATABASE IF EXISTS cinema_city;
CREATE DATABASE cinema_city;
USE cinema_city;
CREATE TABLE Cinema (
	id int primary key auto_increment,
    name VARCHAR(100) NOT NULL,
    town VARCHAR(100) NOT NULL
);

CREATE TABLE Hall (
	id int primary key auto_increment,
    cinemaID int,
    number INT NOT NULL,
    type ENUM('Normal', 'VIP', 'Deluxe'),
    foreign key (cinemaID) REFERENCES Cinema(id)
);

CREATE TABLE Film (
	id int primary key auto_increment,
    name VARCHAR(255) NOT NULL,
    publish_year DATE NOT NULL,
    country VARCHAR(255) NOT NULL
);

CREATE TABLE Projection (
	id int primary key auto_increment,
    hallID int,
    filmID int,
    projection_time DATETIME NOT NULL,
    viewers int NOT NULL,
    FOREIGN KEY (hallID) REFERENCES Hall(id),
    FOREIGN KEY (filmID) REFERENCES Film(id)
);

INSERT INTO Cinema (name, town) VALUES
('Arena Mladost', 'Sofia'),
('Cineplex', 'Plovdiv'),
('Hollywood Cinema', 'Varna');

INSERT INTO Hall (number, type, cinemaID) VALUES
(1, 'Normal', 1),
(2, 'VIP', 1),
(3, 'Deluxe', 1),
(1, 'Normal', 2),
(2, 'VIP', 2),
(3, 'Deluxe', 2),
(4, 'Deluxe', 2);

INSERT INTO Film (name, publish_year, country) VALUES
('Final Destination 7', '2023-10-12', "USA"),
("Test", '2024-10-12', "Bulgaria");

INSERT INTO Projection (projection_time, viewers, hallID, filmID) VALUES
('2024-03-20 18:00:00', 100, 1, 1),
('2024-03-20 20:00:00', 50, 2, 1),
('2024-03-20 22:00:00', 20, 1, 2),
('2024-03-21 10:00:00', 150, 2, 2),
('2024-03-21 15:00:00', 30, 1, 1),
('2024-03-23 10:00:00', 50, 6, 1),
('2024-03-21 15:00:00', 150, 7, 1);

/* 2 */
SELECT Cinema.name, Hall.number, Projection.projection_time
FROM Cinema
JOIN Hall ON Cinema.id = Hall.cinemaID
JOIN Projection ON Hall.id = Projection.hallID
JOIN Film ON Projection.filmID = Film.id
WHERE Film.name = 'Final Destination 7'
AND Hall.type IN ('VIP', 'Deluxe')
ORDER BY Cinema.name, Hall.number;

/* 3 */
SELECT sum(Projection.viewers) as Viewers
FROM Cinema
JOIN Hall ON Cinema.id = Hall.cinemaID
JOIN Projection ON Hall.id = Projection.hallID
JOIN Film ON Projection.filmID = Film.id
WHERE Film.name = 'Final Destination 7'
AND Hall.type = 'VIP'
AND Cinema.name = 'Arena Mladost';

/* 4 */
USE school_sport_clubs;
SELECT DISTINCT st1.name AS student1, st2.name AS student2
FROM student_sport AS ss1
JOIN student_sport AS ss2 ON ss1.sportGroup_id = ss2.sportGroup_id
AND ss1.student_id < ss2.student_id
JOIN students AS st1 ON ss1.student_id = st1.id
JOIN students AS st2 ON ss2.student_id = st2.id
JOIN sports AS s1 ON ss1.sportGroup_id = s1.id
JOIN sports AS s2 ON ss2.sportGroup_id = s2.id
WHERE s1.name = 'Football' AND s2.name = 'Football';

/* 5 */
CREATE VIEW TrainingsAt8 AS
SELECT s.name AS student_name, s.class, sg.location, sg.dayOfWeek, sg.hourOfTraining, c.name AS coach_name
FROM students AS s
JOIN student_sport AS ss ON s.id = ss.student_id
JOIN sportGroups AS sg ON ss.sportGroup_id = sg.id
JOIN coaches AS c ON sg.coach_id = c.id
WHERE TIME(sg.hourOfTraining) = '08:00:00';

/* 6 */
SELECT s.name AS sport, COUNT(ss.student_id) AS students
FROM sports AS s
JOIN sportGroups AS sg ON s.id = sg.sport_id
JOIN student_sport AS ss ON sg.id = ss.sportGroup_id
GROUP BY s.name;