-- Create Books Table
CREATE TABLE Books (
  ISBN VARCHAR(13) PRIMARY KEY,
  Title VARCHAR(255) NOT NULL,
  Author VARCHAR(255) NOT NULL,
  Genre VARCHAR(50) NOT NULL,
  Quantity INT NOT NULL
);

-- Create Members Table
CREATE TABLE Members (
  MemberID INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Email VARCHAR(255) NOT NULL,
  Phone VARCHAR(20) NOT NULL
);

-- Create Loans Table
CREATE TABLE Loans (
  LoanID INT AUTO_INCREMENT PRIMARY KEY,
  MemberID INT NOT NULL,
  ISBN VARCHAR(13) NOT NULL,
  LoanDate DATE NOT NULL,
  ReturnDate DATE,
  FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
  FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

-- Insert a few books
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity)
VALUES ('9780307409326', 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 5),
       ('9780140275398', 'The Lord of the Rings', 'J.R.R. Tolkien', 'Fantasy', 3),
       ('9780061237643', 'Pride and Prejudice', 'Jane Austen', 'Romance', 2);

-- Insert some members
INSERT INTO Members (MemberID, Name, Email, Phone)
VALUES ('M123', 'Chicken Waffle', 'chicken.waffle@example.com', '123-456-7890'),
       ('M456', 'Peach Cobbler', 'peach.cobbler@example.com', '987-654-3210');

-- Insert a loan record
INSERT INTO Loans (LoanID, MemberID, ISBN, LoanDate, ReturnDate)
VALUES ('L002', 'M456', '9780061237643', '2024-07-10', NULL);

-- Retrieve Books Borrowed by Member (MemberID = M456)
SELECT b.Title, b.Author, l.LoanDate, l.ReturnDate
FROM Books b
INNER JOIN Loans l ON b.ISBN = l.ISBN
WHERE l.MemberID = 'M456';

-- Update Book Quantity (ISBN = '9780061237643')
UPDATE Books
SET Quantity = Quantity - 2
WHERE ISBN = '9780061237643';

-- Delete Member (MemberID = M123)
DELETE FROM Members
WHERE MemberID = 'M123';

-- Display all tables after operations
SELECT * FROM Books;