-- eskuel:system=sqlite
-- eskuel:systemMinVersion=3.37.0

BEGIN TRANSACTION;

-- Create the "customer" table
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE
);

-- Create the "book" table
CREATE TABLE book (
    isbn VARCHAR(13) PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    price DECIMAL(10, 2),
    genre VARCHAR(100)
);

-- Create the "shopping_cart" table
CREATE TABLE shopping_cart (
    customer_id INT,
    isbn VARCHAR(13),
    quantity INT,
    PRIMARY KEY (customer_id, isbn),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (isbn) REFERENCES book(isbn)
);

-- Insert sample customers
INSERT INTO customer (customer_id, first_name, last_name, date_of_birth)
VALUES
    (1, 'Max', 'Mustermann', '1990-01-15'),
    (2, 'Amira', 'Haddad', '1985-06-22'),
    (3, 'David', 'Maier', '1995-09-10'),
    (4, 'Sarah', 'Müller', '1988-03-18'),
    (5, 'Michael', 'Schulz', '1992-07-01'),
    (6, 'Laura', 'Becker', '1987-11-25'),
    (7, 'Felix', 'Wagner', '1998-04-09'),
    (8, 'Sophie', 'Hofmann', '2000-09-03'),
    (9, 'Julian', 'Bauer', '1993-02-14'),
    (10, 'Lea', 'Keller', '1989-12-30'),
    (11, 'Luca', 'Peters', '1997-05-08'),
    (12, 'Emma', 'Hermann', '1994-08-17'),
    (13, 'Tim', 'Graf', '2001-03-21'),
    (14, 'Lena', 'Schmitt', '1986-10-11'),
    (15, 'Nico', 'Köhler', '1996-06-27'),
    (16, 'Mia', 'Lange', '1999-07-13'),
    (17, 'Paul', 'Krüger', '1991-04-02'),
    (18, 'Melina', 'Wolf', '1992-11-19'),
    (19, 'Jonas', 'Schröder', '1988-08-05'),
    (20, 'Emilia', 'Fuchs', '2002-01-24'),
    (21, 'Olena', 'Kovalenko', '1994-02-17'),
    (22, 'Emre', 'Yılmaz', '1989-08-06'),
    (23, 'Emily', 'Thompson', '1997-11-23'),
    (24, 'Giulia', 'Romano', '1991-05-14'),
    (25, 'Yuki', 'Tanaka', '1986-12-02');

-- Insert sample books
INSERT INTO book (isbn, title, author, price, genre)
VALUES
    ('978-3-86680-192-9', 'The Alchemist', 'Paulo Coelho', 12.99, 'Fiction'),
    ('978-3-462-04513-1', 'Harry Potter and the Philosopher''s Stone', 'J.K. Rowling', 15.50, 'Fantasy'),
    ('978-3-547-91235-3', 'The Hidden One', 'Sarah Weiss', 9.99, 'Thriller'),
    ('978-3-7897-1234-5', 'Cathedral of the Sea', 'Ildefonso Falcones', 20.00, 'Historical Fiction'),
    ('978-3-455-65012-3', 'City of Thieves', 'David Benioff', 8.95, 'Historical Fiction'),
    ('978-3-423-23034-7', 'A Song of Ice and Fire', 'George R.R. Martin', 25.00, 'Fantasy'),
    ('978-3-86541-859-0', '1984', 'George Orwell', 10.50, 'Science Fiction'),
    ('978-3-442-54702-3', 'The Lord of the Rings', 'J.R.R. Tolkien', 22.75, 'Fantasy'),
    ('978-3-492-26431-2', 'The Physician', 'Noah Gordon', 18.95, 'Historical Fiction'),
    ('978-3-7857-2177-2', 'Momo', 'Michael Ende', 14.80, 'Children''s Literature'),
    ('978-3-499-25821-5', 'The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 9.50, 'Science Fiction'),
    ('978-3-423-12314-1', 'The Little Prince', 'Antoine de Saint-Exupéry', 7.99, 'Philosophy'),
    ('978-3-596-18048-7', 'The Hobbit', 'J.R.R. Tolkien', 16.25, 'Fantasy'),
    ('978-3-453-17817-3', 'The Name of the Wind', 'Patrick Rothfuss', 19.95, 'Fantasy'),
    ('978-3-404-15431-1', 'The Dwarves', 'Markus Heitz', 13.50, 'Fantasy'),
    ('978-3-499-26602-0', 'The Swarm', 'Frank Schätzing', 21.00, 'Thriller'),
    ('978-3-453-43570-8', 'Eragon', 'Christopher Paolini', 11.75, 'Fantasy'),
    ('978-3-442-46630-0', 'The Wizard', 'Wolfgang Hohlbein', 14.25, 'Fantasy'),
    ('978-3-10-397083-5', 'The Catcher in the Rye', 'J.D. Salinger', 9.95, 'Fiction'),
    ('978-3-596-52008-3', 'The Pillars of the Earth', 'Ken Follett', 17.00, 'Historical Fiction'),
    ('978-3-00000-001-0', 'Inkheart', 'Cornelia Funke', 14.00, 'Fantasy'),
    ('978-3-00000-002-7', 'The Neverending Story', 'Michael Ende', 16.00, 'Fantasy'),
    ('978-3-00000-003-4', 'Krabat', 'Otfried Preußler', 9.00, 'Fantasy'),
    ('978-3-00000-004-1', 'QualityLand', 'Marc-Uwe Kling', 12.00, 'Science Fiction'),
    ('978-3-00000-005-8', 'The Three-Body Problem', 'Cixin Liu', 13.00, 'Science Fiction'),
    ('978-3-00000-006-5', 'Dune', 'Frank Herbert', 18.00, 'Science Fiction'),
    ('978-3-00000-007-2', 'Solaris', 'Stanisław Lem', 11.50, 'Science Fiction'),
    ('978-3-00000-008-9', 'The Martian', 'Andy Weir', 12.99, 'Science Fiction'),
    ('978-3-00000-009-6', 'The Handmaid''s Tale', 'Margaret Atwood', 14.00, 'Science Fiction'),
    ('978-3-00000-010-2', 'Fahrenheit 451', 'Ray Bradbury', 10.00, 'Science Fiction'),
    ('978-3-00000-011-9', 'Neuromancer', 'William Gibson', 13.50, 'Science Fiction'),
    ('978-3-00000-012-6', 'The Left Hand of Darkness', 'Ursula K. Le Guin', 15.00, 'Science Fiction'),
    ('978-3-00000-013-3', 'Murder on the Orient Express', 'Agatha Christie', 10.00, 'Crime'),
    ('978-3-00000-014-0', 'The Hound of the Baskervilles', 'Arthur Conan Doyle', 8.50, 'Crime'),
    ('978-3-00000-015-7', 'The Judge and His Hangman', 'Friedrich Dürrenmatt', 9.00, 'Crime'),
    ('978-3-00000-016-4', 'Snow White Must Die', 'Nele Neuhaus', 12.00, 'Crime'),
    ('978-3-00000-017-1', 'The Murder Farm', 'Andrea Maria Schenkel', 10.00, 'Crime'),
    ('978-3-00000-018-8', 'The Talented Mr. Ripley', 'Patricia Highsmith', 11.00, 'Thriller'),
    ('978-3-00000-019-5', 'Gone Girl', 'Gillian Flynn', 13.00, 'Thriller'),
    ('978-3-00000-020-1', 'Perfume', 'Patrick Süskind', 12.00, 'Historical Fiction'),
    ('978-3-00000-021-8', 'All Quiet on the Western Front', 'Erich Maria Remarque', 11.00, 'Historical Fiction'),
    ('978-3-00000-022-5', 'The Book Thief', 'Markus Zusak', 14.00, 'Historical Fiction'),
    ('978-3-00000-023-2', 'Pope Joan', 'Donna W. Cross', 13.00, 'Historical Fiction'),
    ('978-3-00000-024-9', 'The Salt of the Earth', 'Daniel Wolf', 16.00, 'Historical Fiction'),
    ('978-3-00000-025-6', 'Buddenbrooks', 'Thomas Mann', 12.00, 'Classics'),
    ('978-3-00000-026-3', 'Effi Briest', 'Theodor Fontane', 7.00, 'Classics'),
    ('978-3-00000-027-0', 'The Trial', 'Franz Kafka', 8.00, 'Classics'),
    ('978-3-00000-028-7', 'Faust, Part One', 'Johann Wolfgang von Goethe', 6.50, 'Classics'),
    ('978-3-00000-029-4', 'The Metamorphosis', 'Franz Kafka', 6.00, 'Classics'),
    ('978-3-00000-030-0', 'Steppenwolf', 'Hermann Hesse', 10.00, 'Classics'),
    ('978-3-00000-031-7', 'Siddhartha', 'Hermann Hesse', 9.00, 'Classics'),
    ('978-3-00000-032-4', 'Homo Faber', 'Max Frisch', 9.50, 'Classics'),
    ('978-3-00000-033-1', 'The Diary of a Young Girl', 'Anne Frank', 10.00, 'Biography'),
    ('978-3-00000-034-8', 'Steve Jobs', 'Walter Isaacson', 18.00, 'Biography'),
    ('978-3-00000-035-5', 'A Brief History of Time', 'Stephen Hawking', 12.00, 'Nonfiction'),
    ('978-3-00000-036-2', 'Sapiens: A Brief History of Humankind', 'Yuval Noah Harari', 16.00, 'Nonfiction'),
    ('978-3-00000-037-9', 'The Hidden Life of Trees', 'Peter Wohlleben', 11.00, 'Nonfiction'),
    ('978-3-00000-038-6', 'Gut', 'Giulia Enders', 12.00, 'Nonfiction'),
    ('978-3-00000-039-3', 'The Art of Thinking Clearly', 'Rolf Dobelli', 13.00, 'Nonfiction'),
    ('978-3-00000-040-9', 'Factfulness', 'Hans Rosling', 14.00, 'Nonfiction'),
    ('978-3-00000-041-6', 'Sophie''s World', 'Jostein Gaarder', 13.00, 'Philosophy'),
    ('978-3-00000-042-3', 'The Philosophical Backstairs', 'Wilhelm Weischedel', 11.00, 'Philosophy'),
    ('978-3-00000-043-0', 'Pippi Longstocking', 'Astrid Lindgren', 12.00, 'Children''s Literature'),
    ('978-3-00000-044-7', 'Emil and the Detectives', 'Erich Kästner', 10.00, 'Children''s Literature'),
    ('978-3-00000-045-4', 'The Robber Hotzenplotz', 'Otfried Preußler', 11.00, 'Children''s Literature'),
    ('978-3-00000-046-1', 'A Week Full of Saturdays', 'Paul Maar', 10.00, 'Children''s Literature'),
    ('978-3-00000-047-8', 'Ronia, the Robber''s Daughter', 'Astrid Lindgren', 12.50, 'Children''s Literature'),
    ('978-3-00000-048-5', 'Why We Took the Car', 'Wolfgang Herrndorf', 11.00, 'Young Adult'),
    ('978-3-00000-049-2', 'The Wave', 'Morton Rhue', 9.00, 'Young Adult'),
    ('978-3-00000-050-8', 'The Center of the World', 'Andreas Steinhöfel', 10.00, 'Young Adult'),
    ('978-3-00000-051-5', 'The Fabric of Reality', 'David Deutsch', 16.00, 'Nonfiction'),
    ('978-3-00000-052-2', 'The Beginning of Infinity', 'David Deutsch', 18.00, 'Nonfiction');

-- Insert sample shopping-cart entries
INSERT INTO shopping_cart (customer_id, isbn, quantity)
VALUES
    (1, '978-3-86680-192-9', 2),
    (1, '978-3-462-04513-1', 1),
    (2, '978-3-86680-192-9', 3),
    (3, '978-3-547-91235-3', 1),
    (4, '978-3-7897-1234-5', 2),
    (5, '978-3-455-65012-3', 1),
    (6, '978-3-423-23034-7', 1),
    (7, '978-3-86541-859-0', 2),
    (8, '978-3-442-54702-3', 1),
    (9, '978-3-492-26431-2', 1),
    (10, '978-3-7857-2177-2', 1),
    (11, '978-3-499-25821-5', 3),
    (12, '978-3-423-12314-1', 1),
    (13, '978-3-596-18048-7', 2),
    (14, '978-3-453-17817-3', 1),
    (15, '978-3-404-15431-1', 1),
    (16, '978-3-499-26602-0', 2),
    (17, '978-3-453-43570-8', 1),
    (18, '978-3-442-46630-0', 1),
    (19, '978-3-10-397083-5', 1),
    (20, '978-3-596-52008-3', 2),
    -- Some more customers have bought random books:
    (1, '978-3-423-23034-7', 1),
    (4, '978-3-596-52008-3', 1),
    (7, '978-3-442-46630-0', 1),
    (10, '978-3-453-43570-8', 1),
    (13, '978-3-453-17817-3', 1),
    (16, '978-3-596-18048-7', 1),
    (19, '978-3-499-26602-0', 1),
    (2, '978-3-453-17817-3', 1),
    (5, '978-3-86541-859-0', 1),
    (8, '978-3-453-43570-8', 1),
    (11, '978-3-596-52008-3', 1),
    (14, '978-3-442-46630-0', 1),
    (17, '978-3-423-23034-7', 1),
    (20, '978-3-442-54702-3', 1),
    (3, '978-3-596-52008-3', 1),
    (6, '978-3-453-43570-8', 1),
    (9, '978-3-86541-859-0', 1),
    (12, '978-3-596-18048-7', 1),
    (15, '978-3-442-46630-0', 1),
    (18, '978-3-423-23034-7', 1),
    (1, '978-3-00000-051-5', 1),
    (3, '978-3-00000-005-8', 2),
    (5, '978-3-00000-013-3', 1),
    (8, '978-3-00000-043-0', 1),
    (12, '978-3-00000-035-5', 1),
    (21, '978-3-00000-009-6', 2),
    (21, '978-3-00000-035-5', 1),
    (21, '978-3-00000-051-5', 1),
    (22, '978-3-00000-004-1', 1),
    (22, '978-3-00000-013-3', 2),
    (22, '978-3-00000-052-2', 1),
    (23, '978-3-00000-007-2', 1),
    (23, '978-3-00000-019-5', 1),
    (23, '978-3-00000-047-8', 2),
    (24, '978-3-00000-020-1', 1),
    (24, '978-3-00000-038-6', 1),
    (24, '978-3-00000-043-0', 1),
    (25, '978-3-00000-005-8', 2),
    (25, '978-3-00000-012-6', 1),
    (25, '978-3-00000-031-7', 1);

COMMIT;