CREATE TABLE LibraryBook (
    BookID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(150),
    Author VARCHAR(150),
    Genre VARCHAR(50),
    PublishedYear INT,
    ShelfLocation BIGINT,
    ISBN VARCHAR(13) UNIQUE,
    IsAvailable BOOLEAN DEFAULT TRUE
);

CREATE TABLE BookLoan (
    LoanID INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(100),
    BookID INT,
    LoanDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES LibraryBook(BookID)
);

INSERT INTO LibraryBook (BookID, Title, Author, Genre, PublishedYear, ISBN, IsAvailable) VALUES
    (1, 'Bridge to Terabithia', 'Tom Welling', 'Adventure', 2001, '2319039000001', TRUE),
    (2, 'Sunrise on the Reaping', 'Tyler Hoechlin', 'Drama', 2025, '3319303999901', FALSE),
    (3, 'The Lightning Thief', 'Rick Riordan', 'Fantasy', 2005, '9781423103349', TRUE),
    (4, 'To Kill a Mockingbird', 'Harper Lee', 'Classic', 1960, '9780061120084', TRUE),
    (5, '1984', 'George Orwell', 'Dystopian', 1949, '9780451524935', FALSE),
    (6, 'The Martian', 'Andy Weir', 'Memoir', 2018, '9780399590504', TRUE),
    (7, 'Educated', 'Tara Westover', 'Memoir', 2018, '9780804139021', TRUE),
    (8, 'Becoming', 'Michelle Obama', 'Biography', 2018, '9781524763138', FALSE);

INSERT INTO BookLoan (LoanID, StudentName, BookID, LoanDate, ReturnDate) VALUES
    (1, 'Jessica Dethloff', 1, '2025-06-20', NULL),
    (2, 'Michael Rosebaum', 2, '2025-01-20', '2025-01-22'),
    (3, 'Lana Lang', 3, '2025-07-01', NULL),
    (4, 'Clark Kent', 4, '2025-06-15', '2025-06-30'),
    (5, 'Chloe Sullivan', 5, '2025-05-10', '2025-05-20'),
    (6, 'Lex Luthor', 6, '2025-08-01', NULL),
    (7, 'Pete Ross', 7, '2025-09-01', NULL),
    (8, 'Lois Lane', 8, '2025-09-15', '2025-09-25');

ALTER TABLE LibraryBook
ADD `Condition` VARCHAR(20);

ALTER TABLE BookLoan
ADD DueDate DATE;

ALTER TABLE LibraryBook
RENAME COLUMN Genre TO Category;

ALTER TABLE LibraryBook
MODIFY PublishedYear SMALLINT;

SELECT * FROM LibraryBook;