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

CREATE TABLE Authors(
    author_id INT PRIMARY KEY,
    firstname VARCHAR(100),
    lastname VARCHAR(100),
    biography TEXT,
    birthdate DATE,
    nationality VARCHAR(100)
);

CREATE TABLE Borrowers(
    borrower_id INT PRIMARY KEY,
    student_id INT,
    employee_id INT,
    checkoutdate DATE,
    returndate DATE
);

CREATE TABLE Transactions(
    transaction_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    checkoutdate DATE,
    returndate DATE
);

CREATE TABLE Staff(
    employee_id INT PRIMARY KEY,
    username VARCHAR(100),
    password VARCHAR(100),
    firstname VARCHAR(100),
    lastname VARCHAR(100)
);

CREATE TABLE Latefee(
    penalty_id INT PRIMARY KEY,
    transaction_ID INT,
    duedate DATE,
    amount DECIMAL(10,2)
);

CREATE TABLE Publisher(
    publisher_id INT PRIMARY KEY,
    name VARCHAR(100),
    address TEXT,
    contact VARCHAR(100)
);

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

INSERT INTO Authors(author_id, firstname, lastname, biography, birthdate, 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 INTO Borrowers(borrower_id, student_id, employee_id, checkoutdate, returndate)
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 INTO Transactions(transaction_id, book_id, borrower_id, checkoutdate, returndate)
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 INTO Staff(employee_id, username, password, firstname, lastname)
VALUES
    (2001, 'librarian1', '234567@8910', 'Bob', 'John'),
    (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 INTO Latefee(penalty_id, transaction_id, duedate, amount)
VALUES
    (1, 1, '2023-10-15', 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 INTO Publisher(publisher_id, name, address, contact)
VALUES
    ();