CREATE DATABASE kedvenc_filmek CHARACTER SET utf8 COLLATE utf8_hungarian_ci;
USE kedvenc_filmek;

CREATE TABLE filmek (
    film_id INT PRIMARY KEY AUTO_INCREMENT,
    cim VARCHAR(255) NOT NULL,
    kiadas_datuma DATE,
    hossz_perc INT,
    ertekeles INT CHECK (ertekeles BETWEEN 1 AND 10)
);

CREATE TABLE rendezok (
    rendezo_id INT PRIMARY KEY AUTO_INCREMENT,
    nev VARCHAR(255) NOT NULL
);

CREATE TABLE irok (
    iro_id INT PRIMARY KEY AUTO_INCREMENT,
    nev VARCHAR(255) NOT NULL
);

CREATE TABLE szineszek (
    szinesz_id INT PRIMARY KEY AUTO_INCREMENT,
    nev VARCHAR(255) NOT NULL
);

CREATE TABLE mufajok (
    mufaj_id INT PRIMARY KEY AUTO_INCREMENT,
    megnevezes VARCHAR(100) NOT NULL
);

-- Kapcsolótáblák
CREATE TABLE film_rendezo (
    film_id INT,
    rendezo_id INT,
    PRIMARY KEY (film_id, rendezo_id),
    FOREIGN KEY (film_id) REFERENCES filmek(film_id) ON DELETE CASCADE,
    FOREIGN KEY (rendezo_id) REFERENCES rendezok(rendezo_id) ON DELETE CASCADE
);

CREATE TABLE film_iro (
    film_id INT,
    iro_id INT,
    PRIMARY KEY (film_id, iro_id),
    FOREIGN KEY (film_id) REFERENCES filmek(film_id) ON DELETE CASCADE,
    FOREIGN KEY (iro_id) REFERENCES irok(iro_id) ON DELETE CASCADE
);

CREATE TABLE film_szerepek (
    szerepek_id INT PRIMARY KEY AUTO_INCREMENT,
    film_id INT,
    szinesz_id INT,
    szerepnev VARCHAR(255),
    FOREIGN KEY (film_id) REFERENCES filmek(film_id) ON DELETE CASCADE,
    FOREIGN KEY (szinesz_id) REFERENCES szineszek(szinesz_id) ON DELETE CASCADE
);

CREATE TABLE film_mufaj (
    film_id INT,
    mufaj_id INT,
    PRIMARY KEY (film_id, mufaj_id),
    FOREIGN KEY (film_id) REFERENCES filmek(film_id) ON DELETE CASCADE,
    FOREIGN KEY (mufaj_id) REFERENCES mufajok(mufaj_id) ON DELETE CASCADE
);


INSERT INTO mufajok (megnevezes) VALUES 
('Akció'), ('Kaland'), ('Vígjáték'), ('Dráma'), ('Horror'),
('Sci-fi'), ('Fantasy'), ('Thriller'), ('Romantikus'), ('Animáció');

INSERT INTO rendezok (nev) VALUES
('Christopher Nolan'),
('Steven Spielberg'),
('Quentin Tarantino'),
('James Cameron'),
('Denis Villeneuve'),
('Martin Scorsese'),
('David Fincher'),
('Frank Darabont'),
('Ridley Scott'),
('Peter Jackson');

INSERT INTO irok (nev) VALUES
('Christopher Nolan'),
('Jonathan Nolan'),
('Quentin Tarantino'),
('Stephen King'),
('J.R.R. Tolkien'),
('James Cameron'),
('Aaron Sorkin'),
('Fran Walsh'),
('Eric Roth'),
('Philippa Boyens');

INSERT INTO szineszek (nev) VALUES
('Leonardo DiCaprio'),
('Tom Hanks'),
('Brad Pitt'),
('Robert Downey Jr.'),
('Matthew McConaughey'),
('Anne Hathaway'),
('Jessica Chastain'),
('Samuel L. Jackson'),
('Sigourney Weaver'),
('Keanu Reeves'),
('Tom Hardy'),
('Morgan Freeman'),
('Christian Bale'),
('Heath Ledger'),
('Elijah Wood'),
('Viggo Mortensen'),
('Ian McKellen'),
('Cate Blanchett'),
('Hugh Jackman'),
('Kate Winslet'),
('Matt Damon'),
('Anthony Hopkins'),
('Jamie Foxx'),
('Christoph Waltz'),
('Uma Thurman'),
('John Travolta'),
('Bruce Willis'),
('Tim Robbins'),
('Jodie Foster'),
('Carrie-Anne Moss'),
('Laurence Fishburne'),
('Timothée Chalamet'),
('Rebecca Ferguson'),
('Zendaya'),
('Jason Momoa'),
('Josh Brolin'),
('Robert De Niro'),
('Al Pacino'),
('Joe Pesci'),
('Mark Hamill'),
('Harrison Ford'),
('Carrie Fisher'),
('Daisy Ridley'),
('John Boyega'),
('Adam Driver'),
('Scarlett Johansson'),
('Chris Evans'),
('Chris Hemsworth'),
('Meryl Streep'),
('Emma Watson');

INSERT INTO filmek (cim, kiadas_datuma, hossz_perc, ertekeles) VALUES
('A sötét lovag', '2008-07-18', 152, 10),
('Csillagok között', '2014-11-07', 169, 9),
('A gyűrűk ura: A gyűrű szövetsége', '2001-12-19', 178, 9),
('Forrest Gump', '1994-07-06', 142, 8),
('A remény rabjai', '1994-09-23', 142, 10),
('A keresztapa', '1972-03-24', 175, 9),
('Ponyvaregény', '1994-10-14', 154, 8),
('Mátrix', '1999-03-31', 136, 9),
('Dűne', '2021-10-22', 155, 8),
('Eredet', '2010-07-16', 148, 9),
('A bárányok hallgatnak', '1991-02-14', 118, 8),
('Alien - A nyolcadik utas: a Halál', '1979-05-25', 117, 9),
('Vissza a jövőbe', '1985-07-03', 116, 9),
('A társaság', '1990-09-19', 146, 8),
('Szárnyas fejvadász', '1982-06-25', 117, 8),
('Schindler listája', '1993-12-15', 195, 10),
('Star Wars: A Birodalom visszavág', '1980-05-21', 124, 9),
('A keresztapa II', '1974-12-20', 202, 9),
('Avatar', '2009-12-18', 162, 7),
('Bosszúállók: Végjáték', '2019-04-26', 181, 8);

INSERT INTO film_rendezo (film_id, rendezo_id) VALUES
(1, 1), -- A sötét lovag - Christopher Nolan
(2, 1), -- Csillagok között - Christopher Nolan
(3, 10), -- A gyűrűk ura - Peter Jackson
(4, 2), -- Forrest Gump - Steven Spielberg
(5, 8), -- A remény rabjai - Frank Darabont
(6, 6), -- A keresztapa - Martin Scorsese
(7, 3), -- Ponyvaregény - Quentin Tarantino
(8, 7), -- Mátrix - David Fincher
(9, 5), -- Dűne - Denis Villeneuve
(10, 1), -- Eredet - Christopher Nolan
(11, 7), -- A bárányok hallgatnak - David Fincher
(12, 9), -- Alien - Ridley Scott
(13, 2), -- Vissza a jövőbe - Steven Spielberg
(14, 6), -- A társaság - Martin Scorsese
(15, 9), -- Szárnyas fejvadász - Ridley Scott
(16, 2), -- Schindler listája - Steven Spielberg
(17, 2), -- Star Wars: A Birodalom visszavág - Steven Spielberg
(18, 6), -- A keresztapa II - Martin Scorsese
(19, 4), -- Avatar - James Cameron
(20, 2); -- Bosszúállók: Végjáték - Steven Spielberg

INSERT INTO film_iro (film_id, iro_id) VALUES
(1, 1), (1, 2), -- A sötét lovag - Christopher Nolan, Jonathan Nolan
(2, 1), (2, 2), -- Csillagok között - Christopher Nolan, Jonathan Nolan
(3, 5), (3, 8), (3, 10), -- A gyűrűk ura - J.R.R. Tolkien, Fran Walsh, Philippa Boyens
(5, 4), -- A remény rabjai - Stephen King
(7, 3), -- Ponyvaregény - Quentin Tarantino
(9, 9), -- Dűne - Eric Roth
(10, 1), -- Eredet - Christopher Nolan
(19, 6); -- Avatar - James Cameron

INSERT INTO film_mufaj (film_id, mufaj_id) VALUES
(1, 1), (1, 8), (1, 4), -- A sötét lovag - Akció, Thriller, Dráma
(2, 6), (2, 4), (2, 2), -- Csillagok között - Sci-fi, Dráma, Kaland
(3, 7), (3, 2), (3, 1), -- A gyűrűk ura - Fantasy, Kaland, Akció
(4, 4), (4, 3), -- Forrest Gump - Dráma, Vígjáték
(5, 4), -- A remény rabjai - Dráma
(6, 4), (6, 8), -- A keresztapa - Dráma, Thriller
(7, 3), (7, 8), (7, 4), -- Ponyvaregény - Vígjáték, Thriller, Dráma
(8, 6), (8, 1), -- Mátrix - Sci-fi, Akció
(9, 6), (9, 2), (9, 4), -- Dűne - Sci-fi, Kaland, Dráma
(10, 6), (10, 1), (10, 8), -- Eredet - Sci-fi, Akció, Thriller
(11, 8), (11, 4), (11, 5), -- A bárányok hallgatnak - Thriller, Dráma, Horror
(12, 6), (12, 5), -- Alien - Sci-fi, Horror
(13, 6), (13, 2), (13, 3), -- Vissza a jövőbe - Sci-fi, Kaland, Vígjáték
(14, 4), (14, 8), -- A társaság - Dráma, Thriller
(15, 6), (15, 8), -- Szárnyas fejvadász - Sci-fi, Thriller
(16, 4), -- Schindler listája - Dráma
(17, 6), (17, 2), (17, 7), -- Star Wars: A Birodalom visszavág - Sci-fi, Kaland, Fantasy
(18, 4), (18, 8), -- A keresztapa II - Dráma, Thriller
(19, 6), (19, 2), (19, 7), -- Avatar - Sci-fi, Kaland, Fantasy
(20, 6), (20, 1), (20, 2); -- Bosszúállók: Végjáték - Sci-fi, Akció, Kaland

INSERT INTO film_szerepek (film_id, szinesz_id, szerepnev) VALUES
(1, 13, 'Bruce Wayne / Batman'),
(1, 14, 'Joker'),
(2, 5, 'Cooper'),
(2, 6, 'Amelia Brand'),
(3, 15, 'Frodo Baggins'),
(3, 16, 'Aragorn'),
(3, 17, 'Gandalf'),
(3, 18, 'Galadriel'),
(4, 2, 'Forrest Gump'),
(5, 28, 'Andy Dufresne'),
(5, 12, 'Ellis Boyd "Red" Redding'),
(6, 37, 'Vito Corleone'),
(6, 38, 'Michael Corleone'),
(7, 3, 'Vincent Vega'),
(7, 25, 'Mia Wallace'),
(7, 8, 'Jules Winnfield'),
(8, 10, 'Neo'),
(8, 30, 'Trinity'),
(8, 31, 'Morpheus'),
(9, 32, 'Paul Atreides'),
(9, 33, 'Lady Jessica'),
(9, 34, 'Chani'),
(9, 35, 'Duncan Idaho'),
(10, 1, 'Dom Cobb'),
(10, 5, 'Arthur'),
(11, 29, 'Clarice Starling'),
(11, 22, 'Dr. Hannibal Lecter'),
(12, 9, 'Ellen Ripley'),
(13, 26, 'Marty McFly'),
(14, 37, 'James Conway'),
(14, 39, 'Tommy DeVito'),
(15, 41, 'Rick Deckard'),
(16, 2, 'Oskar Schindler'),
(17, 41, 'Han Solo'),
(17, 42, 'Leia Organa'),
(17, 40, 'Luke Skywalker'),
(18, 38, 'Michael Corleone'),
(18, 37, 'Vito Corleone (fiatal)'),
(19, 8, 'Jake Sully'),
(19, 9, 'Neytiri'),
(20, 4, 'Tony Stark / Iron Man'),
(20, 47, 'Steve Rogers / Captain America'),
(20, 48, 'Thor'),
(20, 36, 'Thanos');



-- Mely filmekben játszik Leonardo DiCaprio?
SELECT f.cim, f.kiadas_datuma, fs.szerepnev
FROM filmek f
JOIN film_szerepek fs ON f.film_id = fs.film_id
JOIN szineszek s ON fs.szinesz_id = s.szinesz_id
WHERE s.nev = 'Leonardo DiCaprio'
ORDER BY f.kiadas_datuma DESC;

-- Milyen horror filmjeim vannak?
SELECT f.cim, f.kiadas_datuma, f.ertekeles
FROM filmek f
JOIN film_mufaj fm ON f.film_id = fm.film_id
JOIN mufajok m ON fm.mufaj_id = m.mufaj_id
WHERE m.megnevezes = 'Horror'
ORDER BY f.ertekeles DESC;

-- Christopher Nolan által rendezett filmek listája
SELECT f.cim, f.kiadas_datuma, f.ertekeles
FROM filmek f
JOIN film_rendezo fr ON f.film_id = fr.film_id
JOIN rendezok r ON fr.rendezo_id = r.rendezo_id
WHERE r.nev = 'Christopher Nolan'
ORDER BY f.kiadas_datuma DESC;

-- Mely filmek kaptak legalább 9-es értékelést?
SELECT cim, ertekeles, kiadas_datuma
FROM filmek
WHERE ertekeles >= 9
ORDER BY ertekeles DESC;

-- Filmek száma műfajonként
SELECT m.megnevezes, COUNT(fm.film_id) AS filmek_szama
FROM mufajok m
JOIN film_mufaj fm ON m.mufaj_id = fm.mufaj_id
GROUP BY m.megnevezes
ORDER BY filmek_szama DESC;