CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    ISBN VARCHAR(20),
    publication_year INT,
    genre_id INT,
    publisher_id INT,
    q_available INT,
    author_id INT
);

-- Create the Authors table
CREATE TABLE Author (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    biography TEXT,
    birth_date DATE,
    nationality VARCHAR(50)
);

-- Create the Publishers table
CREATE TABLE Publishers (
    publisher_id INT PRIMARY KEY,
    name VARCHAR(100),
    address TEXT,
    contact VARCHAR(100)
);

-- Create the Borrowers table (Students)
CREATE TABLE Borrowers (
    borrower_id INT PRIMARY KEY,
    student_id INT,
    employee_id INT,
    checkout_date DATE,
    return_date DATE
);

-- Create the Transactions table
CREATE TABLE Transactions (
    transaction_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    checkout_date DATE,
    return_date DATE
);

-- Create the Library Staff table
CREATE TABLE Library_Staff (
    employee_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

-- Create the Late Fees/Penalties table
CREATE TABLE Late_Fees (
    penalty_id INT PRIMARY KEY,
    transaction_id INT,
    due_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE Genres (
    genre_id INT PRIMARY KEY, 
    genre_name VARCHAR(20)
);

INSERT INTO Genres (genre_id, genre_name)
VALUES
    (1, "Realism"),
    (2, "Fiction"),
    (3, "Dystopian"),
    (4, 'Modernist'),
    (5, "Fantasy");

INSERT INTO Books (book_id, title, ISBN, publication_year, genre_id, publisher_id, q_available, author_id)
VALUES
    (1, 'The Catcher in the Rye', '974', 1951, 1, 1, 10, 1),
    (2, 'To Kill a Mockingbird', '975', 1960, 2, 2, 8, 2),
    (3, '1984', '976', 1949, 3, 3, 12, 3),
    (4, 'The Great Gatsby', '977', 1925, 4, 4, 7, 4),
    (5, 'Harry Potter and the Philosopher''s Stone', '978', 1997, 5, 5, 15, 5);


-- Insert records into the Authors table
INSERT INTO Author (author_id, first_name, last_name, biography, birth_date, nationality)
VALUES
    (1, 'J.D.', 'Salinger', 'Biography of J.D. Salinger', '1919-01-01', 'American'),
    (2, 'Harper', 'Lee', 'Biography of Harper Lee', '1926-04-28', 'American'),
    (3, 'George', 'Orwell', 'Biography of George Orwell', '1903-06-25', 'English'),
    (4, 'F. Scott', 'Fitzgerald', 'Biography of F. Scott Fitzgerald', '1896-09-24', 'American'),
    (5, 'J.K.', 'Rowling', 'Biography of J.K. Rowling', '1965-07-31', 'British');

-- Insert records into the Borrowers table
INSERT INTO Borrowers (borrower_id, student_id, employee_id, checkout_date, return_date)
VALUES
    (1, 1001, 2001, '2023-10-01', '2023-10-15'),
    (2, 1002, 2002, '2023-09-15', '2023-09-30'),
    (3, 1003, 2003, '2023-09-20', '2023-10-05'),
    (4, 1004, 2004, '2023-10-10', '2023-10-25'),
    (5, 1005, 2005, '2023-09-05', '2023-09-20');

-- Insert records into the Transactions table
INSERT INTO Transactions (transaction_id, book_id, borrower_id, checkout_date, return_date)
VALUES
    (1, 1, 1, '2023-10-01', '2023-10-15'),
    (2, 2, 2, '2023-09-15', '2023-09-30'),
    (3, 3, 3, '2023-09-20', '2023-10-05'),
    (4, 4, 4, '2023-10-10', '2023-10-25'),
    (5, 5, 5, '2023-09-05', '2023-09-20');

-- Insert records into the Library Staff table
INSERT INTO Library_Staff (employee_id, username, password, first_name, last_name)
VALUES
    (2001, 'librarian1', 'hashed_password1', 'John', 'Smith'),
    (2002, 'librarian2', 'hashed_password2', 'Alice', 'Johnson'),
    (2003, 'librarian3', 'hashed_password3', 'David', 'Brown'),
    (2004, 'librarian4', 'hashed_password4', 'Sarah', 'Davis'),
    (2005, 'librarian5', 'hashed_password5', 'Michael', 'Wilson');

-- Insert records into the Late Fees/Penalties table
INSERT INTO Late_Fees (penalty_id, transaction_id, due_date, amount)
VALUES
    (1, 1, '2023-10-16', 5.00),
    (2, 2, '2023-10-01', 3.00),
    (3, 3, '2023-10-06', 4.50),
    (4, 4, '2023-10-26', 6.25),
    (5, 5, '2023-09-21', 2.50);

-- Insert records into the Publishers table
INSERT INTO Publishers (publisher_id, name, address, contact)
VALUES
    (1, 'Little, Brown and Company', '123 Book St., New York, NY', 'contact@littlebrown.com'),
    (2, 'Harper & Brothers', '456 Publishing Ave., Chicago, IL', 'info@harperbrothers.com'),
    (3, 'Secker & Warburg', '789 Literature Lane, London, UK', 'contact@seckerwarburg.com'),
    (4, 'Scribner', '101 Publishers Place, Boston, MA', 'info@scribner.com'),
    (5, 'Bloomsbury Publishing', '567 Story Street, London, UK', 'contact@bloomsbury.com');


SELECT b.book_id, b.title, b.ISBN, b.publication_year, g.genre_name, p.name, b.q_available, CONCAT(a.first_name, " ", a.last_name) as AuthorName
FROM Books as b 
JOIN Genres as g on b.genre_id = g.genre_id
JOIN Publishers as p on p.publisher_id = b.publisher_id
JOIN Author as a on a.author_id = b.author_id