-- TABLE CREATION
CREATE TABLE Top100Films (
    FilmID INT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(255) NOT NULL, 
    ReleaseYear INT, 
    DurationMinutes INT, 
    Rating DECIMAL(3,1) NOT NULL,
    MetaScore INT NOT NULL, 
    Director VARCHAR(255), 
    MainActors TEXT, 
    OscarsWon INT NOT NULL, 
    OscarNominations INT NOT NULL, 
    BaftaAwards INT NOT NULL, 
    GoldenGlobes INT, 
    GoldenGlobeNominations INT NOT NULL, 
    Genre VARCHAR(100), 
    Synopsis TEXT
); 

CREATE TABLE Directors( 
    DirectorID INT AUTO_INCREMENT PRIMARY KEY, 
    Name VARCHAR(255) NOT NULL
);

CREATE TABLE Actors ( 
    ActorID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL
);

CREATE TABLE FilmActors (
    FilmID INT NOT NULL, 
    ActorID INT NOT NULL, 
    FOREIGN KEY (FilmID) REFERENCES Top100Films(FilmID), 
    FOREIGN KEY (ActorID) REFERENCES Actors(ActorID)
);

-- INSERT FILMS
INSERT INTO Top100Films (
    Title, ReleaseYear, DurationMinutes, Rating, MetaScore, Director, MainActors,
    OscarsWon, OscarNominations, BaftaAwards, GoldenGlobes, GoldenGlobeNominations, Genre, Synopsis
) VALUES
('The Godfather', 1972, 175, 9.2, 100, 'Francis Ford Coppola', 'Marlon Brando, Al Pacino, James Caan', 3, 11, 0, 6, 8, 'Crime, Drama', 'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son.'),
('The Shawshank Redemption', 1994, 142, 9.3, 82, 'Frank Darabont', 'Tim Robbins, Morgan Freeman, Bob Gunton', 0, 7, 0, 0, 2, 'Drama', 'A banker convicted of uxoricide forms a friendship over a quarter century with a hardened convict, while maintaining his innocence.'),
('Schindler''s List', 1993, 195, 9.0, 95, 'Steven Spielberg', 'Liam Neeson, Ralph Fiennes, Ben Kingsley', 7, 12, 6, 3, 6, 'Biography, Drama, History', 'Oskar Schindler becomes concerned for his Jewish workforce after witnessing their persecution by the Nazis.'),
('Raging Bull', 1980, 129, 8.1, 90, 'Martin Scorsese', 'Robert De Niro, Cathy Moriarty, Joe Pesci', 2, 8, 2, 1, 7, 'Biography, Drama, Sport', 'The life of boxer Jake LaMotta, whose violence and temper led him to the top in the ring but destroyed his life outside of it.'),
('The Dark Knight', 2008, 152, 9.0, 84, 'Christopher Nolan', 'Christian Bale, Heath Ledger, Aaron Eckhart', 2, 8, 1, 1, 1, 'Action, Crime, Drama', 'When the menace known as the Joker emerges from his mysterious past, he wreaks havoc and chaos on the people of Gotham.');

-- INSERT DIRECTORS
INSERT INTO Directors(Name) VALUES 
('Francis Ford Coppola'), 
('Frank Darabont'), 
('Steven Spielberg'), 
('Martin Scorsese'),
('Quentin Tarantino'),
('Christopher Nolan'),
('Robert Zemeckis');

-- INSERT ACTORS
INSERT INTO Actors(Name) VALUES 
('Marlon Brando'), 
('Al Pacino'), 
('James Caan'), 
('Tim Robbins'), 
('Morgan Freeman'), 
('Bob Gunton'), 
('Liam Neeson'), 
('Ralph Fiennes'), 
('Ben Kingsley'), 
('Robert De Niro'), 
('Cathy Moriarty'), 
('Joe Pesci'),
('John Travolta'),
('Uma Thurman'),
('Samuel L. Jackson'),
('Christian Bale'),
('Heath Ledger'),
('Aaron Eckhart'),
('Tom Hanks'),
('Robin Wright'),
('Gary Sinise');

-- INSERT FILM-ACTOR RELATIONSHIPS
-- The Godfather
INSERT INTO FilmActors (FilmID, ActorID) VALUES (1, 1), (1, 2), (1, 3);

-- The Shawshank Redemption
INSERT INTO FilmActors (FilmID, ActorID) VALUES (2, 4), (2, 5), (2, 6);

-- Schindler's List
INSERT INTO FilmActors (FilmID, ActorID) VALUES (3, 7), (3, 8), (3, 9);

-- Raging Bull
INSERT INTO FilmActors (FilmID, ActorID) VALUES (4, 10), (4, 11), (4, 12);

-- The Dark Knight
INSERT INTO FilmActors (FilmID, ActorID) VALUES (5, 16), (5, 17), (5, 18);

-- JOIN QUERIES
-- LEFT JOIN: All films with their directors (even if director not in Directors table)
SELECT Top100Films.Title, Directors.Name AS DirectorName 
FROM Top100Films 
LEFT JOIN Directors ON Top100Films.Director = Directors.Name;

-- INNER JOIN: Actor names with their film IDs
SELECT Actors.Name, FilmActors.FilmID
FROM Actors
JOIN FilmActors ON Actors.ActorID = FilmActors.ActorID;

-- INNER JOIN: Films with director names
SELECT Top100Films.Title, Directors.Name AS DirectorName
FROM Top100Films
INNER JOIN Directors ON Top100Films.Director = Directors.Name;