CREATE TABLE Projects ( 
    project_id INT PRIMARY KEY, 
    title VARCHAR(255), 
    -- ENUM Ensures that every project in your table is clearly categorized into one of three types
    type ENUM('Film', 'TV', 'Video Game'),
    release_year INT
);

CREATE TABLE Movie_Roles ( 
    role_id INT PRIMARY KEY, 
    project_id INT, 
    character_name VARCHAR(255),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

CREATE TABLE TV_Appearances ( 
  appearance_id INT PRIMARY KEY, 
    project_id INT, 
    start_year INT, 
    end_year INT, 
    FOREIGN KEY( project_id) REFERENCES Projects(project_id)  
);

CREATE TABLE TV_Roles ( 
    role_id INT PRIMARY KEY, 
    project_id INT, 
    character_name VARCHAR(255),
    FOREIGN KEY(project_id) REFERENCES Projects(project_id)
);

CREATE TABLE Productions( 
    production_id INT PRIMARY KEY, 
    project_id INT, 
    role VARCHAR(100), 
    FOREIGN KEY(project_id) REFERENCES Projects(project_id)
    );

CREATE TABLE Favorite_Roles( 
    favorite_id INT PRIMARY KEY, 
    project_id INT, 
    role_type ENUM('Movie','TV'), 
    role_id INT, 
    notes VARCHAR(255),
    FOREIGN KEY(project_id) REFERENCES Projects(project_id)
    ); 


INSERT INTO Projects VALUES 
    (1, 'Road To Perdition', 'Film', 2002), 
    (2, 'Solstice', 'Film',2008), 
    (3, 'Hall Pass', 'Film', 2011), 
    (4, 'Undrafted', 'Film', 2014), 
    (5, 'Everybody Wants Some','Film', 2016), 
    (6, 'The Domestics', 'Film', 2018), 
    (7, 'Then Came You', 'Film', 2018), 
    (8, 'Bigger', 'Film', 2018), 
    (9, 'Can You Keep A Secret?', 'Film', 2019), 
    (10, 'Palm Springs', 'Film', 2020), 
    (11, 'Teen Wolf: The Movie', 'Film', 2023), 
    (12, '7th Heaven', 'TV', 2003), 
    (13, 'Teen Wolf', 'TV', 2011),
    (14, 'SuperGirl', 'TV', 2016), 
    (15, 'Arrow', 'TV', 2018), 
    (16, 'The Flash', 'TV', 2018), 
    (17, 'Batwoman', 'TV', 2019), 
    (18, 'DCs Legends of Tomorrow', 'TV',2020), 
    (19, 'Another Life', TV, 2019),
    (20, 'Superman and Lois', 'TV', 2021),
    (21, 'Lincoln Heights', 'TV', 2009), 
    (22, 'Castle', 'TV', 2010), 
    (23, 'My Boys', 'TV', 2008),
    (24, 'CSI: Miami', 'TV', 2007), 
    (25, 'Departures', 'Film', NULL), 
    (26, 'Fifty Shades Freed', 'Film', NULL),
    (27, 'Melvin Smarty', 'Film', NULL), 
    (28, 'Stratton', 'Film', NULL), 
    (29, 'Open Gate', 'Film', NULL), 
    (30, 'Grizzly Rage', 'Film', NULL), 
    (31, 'Train Quest', 'Film', NULL), 
    (32, 'Family Tree', 'Film',NULL), 
    (33, 'The Rapture Of The Athlete Assumed into Heaven', 'Film', NULL);


INSERT INTO Movie_Roles VALUES 
    (1,1,'Michael Sullivan Jr.'),
    (2,2, 'Nick'),
    (3,3, 'Gerry'), 
    (4,4, 'John'), 
    (5,5, 'McReynolds'),
    (6,6, 'Mark'), 
    (7,7, 'Frank'), 
    (8,8, 'Joe Wieder'), 
    (9,9, 'Jack Harper'),
    (10,10, 'Abe' ), 
    (11, 11, 'Derek Hale'); 


-- Insert TV Appearances
INSERT INTO TV_Appearances VALUES 
    (1, 12, 2003, 2007), 
    (2, 13, 2011, 2017), 
    (3, 14, 2016, 2019),
    (4, 15, 2018, 2020), 
    (5, 16, 2018, 2019), 
    (6, 17, 2019, 2019), 
    (7, 18, 2020, 2020), 
    (8, 19, 2019, 2019),
    (9, 20, 2021, 2024), 
    (10, 21, 2009, 2009), 
    (11, 22, 2010, 2010), 
    (12, 23, 2008, 2008),
    (13, 24, 2007, 2007);

-- Insert TV Roles
INSERT INTO TV_Roles VALUES 
    (1, 12, 'Martin Brewer'), 
    (2, 13, 'Derek Hale'), 
    (3, 14, 'Clark Kent/Superman'),
    (4, 15, 'Clark Kent/Superman'), 
    (5, 16, 'Clark Kent/Superman'), 
    (6, 17, 'Clark Kent/Superman'), 
    (7, 18, 'Clark Kent/Superman'),
    (8, 19, 'Ian Yerxa'), 
    (9, 20, 'Clark Kent/Superman');

-- Insert Productions
INSERT INTO Productions VALUES 
    (1, 1, 'Actor'), 
    (2, 2, 'Actor'), 
    (3, 3, 'Actor'), 
    (4, 4, 'Actor'),
    (5, 5, 'Actor'), 
    (6, 6, 'Actor'),
    (7, 7, 'Actor'), 
    (8, 8, 'Actor'), 
    (9, 9, 'Actor'), 
    (10, 10, 'Actor'), 
    (11, 11, 'Actor'), 
    (12, 25, 'Actor'), 
    (13, 26, 'Actor'), 
    (14, 27, 'Actor'),
    (15, 28, 'Actor'), 
    (16, 29, 'Actor'),
    (17, 30, 'Actor'), 
    (18, 31, 'Actor'),
    (19, 32, 'Actor'),
    (20, 33, 'Actor');

INSERT INTO Favorite_Roles(project_id, role_type, role_id, notes) VALUES 
(1, 'Movie', 1, 'First Major role'), 
(5, 'Movie', 5, 'Iconic Baseball Character'), 
(13, 'TV', 2, 'Breakout Supernatural Role'), 
(20, 'TV', 9, 'Best Portrayal of Superman');