-- 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, 'Meike', 'Musterfrau', '2000-02-12');

-- 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');

-- 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-86680-192-9', 1);

COMMIT;