CREATE DATABASE IF NOT EXISTS books_db;
USE books_db;

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publisher VARCHAR(255) NOT NULL,
    year YEAR NOT NULL CHECK (year >= 1000 AND year <= 9999),
    must_read ENUM('Yes', 'No') NOT NULL,
    genre ENUM('Bible', 'Theology', 'Apologetics', 'Marriage', 'Reference', 'Self-help', 'Leadership', 'History', 'Historical Fiction', 'Fiction', 'Biography', 'Devotionals') NOT NULL,
    category ENUM('Fiction', 'Non-Fiction') NOT NULL,
    on_audiobook ENUM('Yes', 'No') NOT NULL,
    study_bible ENUM('Yes', 'No') NOT NULL,
    red_letter ENUM('Yes', 'No') NOT NULL,
    large_print ENUM('Yes', 'No') NOT NULL,
    cover ENUM('Hardcover', 'Paperback') NOT NULL,
    type VARCHAR(100) NOT NULL
);

-- Insert 5 sample books
INSERT INTO books (title, author, publisher, year, must_read, genre, category, on_audiobook, study_bible, red_letter, large_print, cover, type) VALUES
('Knowing God', 'J.I. Packer', 'IVP', 1973, 'Yes', 'Theology', 'Non-Fiction', 'Yes', 'No', 'No', 'No', 'Paperback', 'Christian Living'),
('Mere Christianity', 'C.S. Lewis', 'HarperOne', 1952, 'Yes', 'Apologetics', 'Non-Fiction', 'Yes', 'No', 'No', 'No', 'Hardcover', 'Philosophy'),
('The Screwtape Letters', 'C.S. Lewis', 'HarperOne', 1942, 'Yes', 'Fiction', 'Fiction', 'No', 'No', 'No', 'No', 'Paperback', 'Christian Fiction'),
('Systematic Theology', 'Wayne Grudem', 'Zondervan', 1994, 'Yes', 'Theology', 'Non-Fiction', 'No', 'No', 'No', 'No', 'Hardcover', 'Academic'),
('The Case for Christ', 'Lee Strobel', 'Zondervan', 1998, 'Yes', 'Apologetics', 'Non-Fiction', 'Yes', 'No', 'No', 'No', 'Paperback', 'Investigative Journalism');