USE LibraryMiniWorld;

INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
(1, 'John Pi', '314 Main St Applewood CA 31415', '314-159-2565', 'johnpi@bruh.com'),
(2, 'Leonhard Euler', '271 Fake Cr Logan UT 27183', '271-828-1828', 'euler@math.org'),
(3, 'Ada Lovelace', '422 Ada Ave Salt Lake City UT 38382', '121-232-3443', 'ada@programming.edu'),
(4, 'Alan Turing', '314 Turing Rd Reno NV 89123', '123-571-1131', 'alan@computerscience.com'),
(5, 'Grace Hopper', '771 Hopper Ct Seattle WA 22331', '223-414-4488', 'grace@computerscience.com'),
(6, 'Charles Babbage', '314 Babbage Rd Reno NV 89123', '881-222-1818', 'bbage @computerscience.com'),
(7, 'John von Neumann', '442 Neumann St Idaho Falls ID 48323', '881-222-1818', 'john@neumann.com'),
(8, 'John Conway', '888 Conway Rd Reno NV 89123', '102-103-1004', 'game@oflife.fun'),
(9, 'Stephen Hawking', '111 Hawking Ave Seattle WA 12523', '101-102-1010', 'hawking@radiation.com');

INSERT INTO Employee (PersonID, StartDate, Salary, EmployeeType) VALUES
(1, '2001-04-02', 40000, 'Manager'),
(2, '2001-04-02', 41000, 'Manager'),
(3, '2001-04-02', 30000, 'Librarian'),
(4, '2010-06-25', 29000, 'Librarian'),
(5, '2022-12-08', 25000, 'Librarian'),
(6, '2022-11-08', 26000, 'Librarian');

INSERT IGNORE INTO Manager (EmployeeID)
SELECT PersonID
FROM Employee
WHERE EmployeeType = 'Manager';

INSERT IGNORE INTO Librarian (EmployeeID)
SELECT PersonID
FROM Employee
WHERE EmployeeType = 'Librarian';

INSERT INTO Manages(ManagerID, LibrarianID) VALUES
(1, 3),
(2, 4),
(2, 5),
(2, 6);

INSERT INTO Customer (PersonID, JoinDate) VALUES
(1, '2001-12-12'),
(3, '2011-10-25'),
(7, '2012-10-25'),
(8, '2013-10-25'),
(9, '2022-12-08');

INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
(1, 1, '2021-12-12', '2022-12-12'),
(2, 3, '2020-12-25', '2021-12-25'),
(3, 7, '2020-09-15', '2022-09-25'),
(4, 8, '2022-01-25', '2023-01-25'),
(5, 9, '2015-12-25', '2023-01-01'),
(6, 3, '2021-12-26', '2022-12-26');

-- SET sql_safe_updates=0;
-- 
-- UPDATE Customer INNER JOIN LibraryCard 
-- AS lc ON lc.CustomerID = Customer.PERSONID 
-- AND lc.ExpiryDate = 
--   (SELECT MAX(ExpiryDate) FROM LibraryCard 
--    GROUP BY CustomerID HAVING CustomerID=lc.CustomerID) 
-- SET Customer.CardNo=lc.CardNo;
-- 
-- SET sql_safe_updates=1; (commented out because we no longer store card number on the customer)

INSERT INTO Creator (CreatorID, Name) VALUES
(1, 'Fyodor Doestoyevsky'),
(2, 'J.K. Rowling'),
(3, 'Brandon Sanderson'),
(4, 'Christopher Paolini'),
(5, 'James Clear'),
(6, 'William Shakespeare'),
(7, 'Jim Dale'),
(8, 'Stefen Fangmeier'),
(9, 'Jennifer Nielsen');

INSERT INTO Media (MediaID, DeweyNumber, Name, MediaType, CreatedYear) VALUES
(1, '891.73','Crime and Punishment', 'Text', 2003),
(2, '822.33','Hamlet', 'Text', 2003),
(3, '823.91','Harry Potter and the Sorcerers Stone', 'Text', 1997),
(4, '823.91','Harry Potter and the Sorcerers Stone', 'Audio', 1997),
(5, '813.91','Eragon', 'Text', 2002),
(6, '813.91','Eragon', 'Video', 2006),
(7, '813.6','Elantris', 'Text', 2005),
(8, '155.2','Atomic Habits', 'Audio', 2018),
(9, '813.6', 'Lines of Courage', 'Text', 2022);

INSERT INTO Text (MediaID, Pages) VALUES
(1, 492),
(2, 104),
(3, 223),
(5, 509),
(7, 496),
(9, 400);

INSERT INTO Audio (MediaID, Playtime) VALUES
(4, 29880),
(8, 13380);

INSERT INTO Video (MediaID, Runtime) VALUES
(6, 6240);


INSERT INTO Genre (GenreID, Name) VALUES
(1, 'Fantasy'),
(2, 'Science Fiction'),
(3, 'Romance'),
(4, 'Drama'),
(5, 'Non-Fiction'),
(6, 'Fiction'),
(7, 'War Story');

INSERT INTO MediaGenre (MediaID, GenreID) VALUES
(1, 4),
(2, 4),
(3, 1),
(4, 1),
(5, 2),
(6, 2),
(7, 2),
(8, 5),
(9, 6),
(9, 7);

INSERT INTO Tag (TagID, Name) VALUES
(1, 'Fun'),
(2, 'Good for Kids'),
(3, 'Young Adult'),
(4, 'Sad'),
(5, 'Educational'),
(6, 'Classic'),
(7, 'Popular'),
(8, 'New'),
(9, 'Powerful');

INSERT INTO MediaTag (MediaID, TagID) VALUES
(1, 6),
(3, 1),
(3, 2),
(3, 3),
(3, 7),
(7, 3),
(8, 5),
(8, 8),
(9, 8),
(9, 9);


INSERT INTO MediaCreator (MediaID, CreatorID) VALUES
(1, 1),
(2, 6),
(3, 2),
(4, 2),
(4, 7),
(5, 4),
(6, 4),
(6, 8),
(7, 3),
(8, 5),
(9, 9);

INSERT INTO MediaInstance (InstanceID, LibrarianID, MediaID, DateAdded) VALUES
(1,  4, 3, '2004-05-08'),
(2,  4, 4, '2004-05-08'),
(3,  4, 5, '2004-05-08'),
(4,  4, 5, '2004-05-08'),
(5,  4, 5, '2004-05-08'),
(6,  5, 6, '2011-03-31'),
(7,  4, 3, '2012-05-08'),
(8,  5, 4, '2018-10-07'),
(9,  5, 5, '2019-02-18'),
(10, 4, 6, '2020-01-20'),
(11, 4, 6, '2020-01-20'),
(12, 4, 6, '2020-01-20'),
(13, 4, 7, '2021-01-20'),
(14, 6, 8, '2021-03-20'),
(15, 6, 9, '2022-05-20'),
(16, 6, 9, '2022-05-20'),
(17, 5, 4, '2009-05-08');

INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, IsReturned, BorrowDate, ReturnDate) VALUES
(1, 1, 3, 0, CURDATE(), '2022-12-24'),
(1, 2, 3, 0, CURDATE(), '2022-12-24'),
(2, 10, 5, 1, '2021-10-01', '2022-10-24'),
(3, 1, 5, 1, '2022-01-01', '2022-01-24'),
(3, 2, 6, 1, '2022-01-01', '2022-01-24'),
(4, 15, 4, 0, '2022-10-01', '2022-10-24'),
(4, 6, 4, 1, '2021-12-01', '2022-12-07');

INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
(1, 3, 5, 'This book was great!'),
(1, 4, 5, 'The audiobook rendition was perfect too!'),
(8, 6, 2, 'The movie did not live up to the book in my opinion.');