-- Create the Authors table
CREATE TABLE Authors (
    AuthorID VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Nationality VARCHAR(50),
    BirthYear INT
);

-- Create the Books table (Updated to reference Authors via AuthorID)
CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    AuthorID INT NOT NULL,
    Genre VARCHAR(50),
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);
 
-- Create the Members table
CREATE TABLE Members (
    MemberID VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20)
);

-- Create the Loans table
CREATE TABLE Loans (
    LoanID INT AUTO_INCREMENT PRIMARY KEY,
    MemberID VARCHAR(20) NULL, -- Changed to NULL
    ISBN VARCHAR(20) NULL,     -- Changed to NULL
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID) ON DELETE SET NULL,
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN) ON DELETE SET NULL
);


INSERT INTO Authors (AuthorID, Name, Nationality, BirthYear) VALUES 
('AUTH-001', 'Chinua Achebe', 'Nigerian', 1930),
('AUTH-002', 'Chimamanda Ngozi Adichie', 'Nigerian', 1977),
('AUTH-003', 'Buchi Emecheta', 'Nigerian', 1944),
('AUTH-004', 'Wole Soyinka', 'Nigerian', 1934),
('AUTH-005', 'Cyprian Ekwensi', 'Nigerian', 1921),
('AUTH-006', 'Arese Ugwu', 'Nigerian', 1986);

select * from Authors;