-- TABLE CREATION

CREATE TABLE Authors ( 
    AuthorID INT PRIMARY KEY, 
    AuthorName VARCHAR(50), 
    BirthYear INT
); 

CREATE TABLE Books ( 
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INT, 
    Genre VARCHAR(100), 
    YearsPublished INT, 
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
); 

CREATE TABLE Directors ( 
    DirectorID INT PRIMARY KEY, 
    DirectorName VARCHAR(100)
);

CREATE TABLE Movies ( 
    MovieID INT PRIMARY KEY, 
    DirectorID INT, 
    MovieTitle VARCHAR(255), 
    Genre VARCHAR(100), 
    YearsReleased INT, 
    FOREIGN KEY (DirectorID) REFERENCES Directors(DirectorID)
); 

CREATE TABLE Borrowers ( 
    BorrowerID INT PRIMARY KEY, 
    Name VARCHAR(255),
    Email VARCHAR(255)
);

CREATE TABLE Loans ( 
    LoanID INT PRIMARY KEY, 
    BookID INT, 
    BorrowerID INT,
    LoanDate DATE, 
    ReturnDate DATE, 
    FOREIGN KEY(BookID) REFERENCES Books(BookID),
    FOREIGN KEY(BorrowerID) REFERENCES Borrowers(BorrowerID)
);

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY, 
    FirstName VARCHAR(50), 
    LastName VARCHAR(50), 
    ReportsTo VARCHAR(50)
);

CREATE TABLE Supervisor ( 
    EmployeeID INT PRIMARY KEY, 
    FirstName VARCHAR(50), 
    LastName VARCHAR(50), 
    ReportsTo VARCHAR(50), 
    FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID)
);

-- DATA INSERTS

INSERT INTO Authors (AuthorID, AuthorName, BirthYear) VALUES 
    (1, 'Jane Austen', 1775), 
    (2, 'George Orwell', 1903), 
    (3, 'J.K Rowling', 1965);

INSERT INTO Books (BookID, Title, AuthorID, Genre, YearsPublished) VALUES 
    (1, 'Pride and Prejudice', 1, 'Romance', 1813), 
    (2, '1984', 2, 'Dystopian', 1949), 
    (3, 'Harry Potter and the Sorcerers Stone', 3, 'Fantasy', 1997), 
    (4, 'Emma', 1, 'Romance', 1815), 
    (5, 'Animal Farm', 2, 'Political Satire', 1945); 

INSERT INTO Directors(DirectorID, DirectorName) VALUES 
    (209495, 'James Gunn'), 
    (10339, 'Brian DePalma'),
    (5060, 'Stephen Spielberg');

INSERT INTO Movies (MovieID, DirectorID, MovieTitle, Genre, YearsReleased) VALUES
    (1, 209495, 'Superman', 'Action', 2025), 
    (2, 10339, 'The Blow Out', 'Horror', 1985), 
    (3, 5060, 'Jurassic Park', 'Adventure', 1979); 

INSERT INTO Borrowers (BorrowerID, Name, Email) VALUES 
    (1, 'Alice Johnson', 'Alice.Johnson@gmail.com'), 
    (2, 'Jim Smith', 'Jim.Smith@gmail.com'), 
    (3, 'Charlie Davis','Charlie.Davis@gmail.com'); 

INSERT INTO Loans(LoanID, BookID, BorrowerID, LoanDate, ReturnDate) VALUES 
    (1, 1, 1, '2025-07-01', '2025-07-15'), 
    (2, 2, 2, '2025-07-05', NULL),
    (3, 3, 1, '2025-07-10', '2025-07-20'), 
    (4, 5, 3, '2025-07-15', NULL); 

INSERT INTO Employee(EmployeeID, FirstName, LastName, ReportsTo) VALUES
    (2319, 'John', 'Smith', 'Clark Kent'), 
    (1029, 'Dakota', 'Stone', 'Reese Witherspoon'), 
    (4091, 'Sam', 'Jones', 'Mike Donaldson'),
    (4029, 'Clark', 'Kent', 'Peter Parker'), 
    (6021, 'Reese', 'Witherspoon', 'David McCall'), 
    (7012, 'Mike', 'Donaldson', 'Jimmy Olsen'),
    (1100, 'Jimmy', 'Olsen','John Wick'),
    (2330, 'John', 'Wick', 'Null');

INSERT INTO Supervisor(EmployeeID, FirstName, LastName, ReportsTo) VALUES
    (4029, 'Clark', 'Kent', 'Peter Parker'), 
    (6021, 'Reese', 'Witherspoon', 'David McCall'), 
    (7012, 'Mike', 'Donaldson', 'Jimmy Olsen'), 
    (1100, 'Jimmy', 'Olsen', 'John Wick');


SELECT * 
FROM Movies;