app/schemas/library.sql

-- Books table
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    genre TEXT,
    year INTEGER,
    available_copies INTEGER DEFAULT 1
);

-- Users table (added for more realistic modeling)
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    membership_date DATE
);

-- Loans table
CREATE TABLE loans (
    loan_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    user_id INTEGER,
    issue_date DATE,
    due_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO books (book_id, title, author, genre, year, available_copies) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925, 2),
(2, '1984', 'George Orwell', 'Dystopian', 1949, 3),
(3, 'Dune', 'Frank Herbert', 'Science Fiction', 1965, 4),
(4, 'Project Hail Mary', 'Andy Weir', 'Science Fiction', 2021, 1),
(5, 'Klara and the Sun', 'Kazuo Ishiguro', 'Science Fiction', 2021, 2),
(6, 'The Martian', 'Andy Weir', 'Science Fiction', 2011, 2),
(7, 'Animal Farm', 'George Orwell', 'Political Satire', 1945, 3);


INSERT INTO users (user_id, name, email, membership_date) VALUES
(101, 'Alice Johnson', 'alice.j@example.com', '2020-06-15'),
(102, 'Bob Smith', 'bob.smith@example.com', '2019-08-22'),
(103, 'Charlie Rose', 'charlie.r@example.com', '2021-01-10'),
(104, 'Diana Prince', 'diana.p@example.com', '2022-05-04'),
(105, 'Ethan Hunt', 'ethan.h@example.com', '2023-02-01');


INSERT INTO loans (loan_id, book_id, user_id, issue_date, due_date, return_date) VALUES
(1, 1, 101, '2022-11-30', '2022-12-15', '2022-12-12'),
(2, 2, 102, '2022-12-20', '2023-01-10', NULL),
(3, 3, 103, '2022-11-01', '2022-11-30', '2022-11-28'),
(4, 4, 104, '2023-01-10', '2023-02-01', NULL),
(5, 5, 105, '2023-02-10', '2023-03-01', NULL),
(6, 2, 101, '2023-01-15', '2023-02-05', '2023-02-04');