-- Authors
CREATE TABLE authors (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    country    VARCHAR(50),
    birth_year INT
);

-- Books
CREATE TABLE books (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author_id   INT REFERENCES authors(id),
    genre       VARCHAR(50),
    price       DECIMAL(8, 2) NOT NULL,
    stock       INT DEFAULT 0,
    published   DATE
);

-- Customers
CREATE TABLE customers (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) UNIQUE NOT NULL,
    city       VARCHAR(50),
    joined_at  TIMESTAMP DEFAULT NOW()
);

-- Orders
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    ordered_at  TIMESTAMP DEFAULT NOW(),
    status      VARCHAR(20) DEFAULT 'pending'
                CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

-- Order Items
CREATE TABLE order_items (
    order_id   INT REFERENCES orders(id),
    book_id    INT REFERENCES books(id),
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(8, 2) NOT NULL,
    PRIMARY KEY (order_id, book_id)
);

-- Reviews
CREATE TABLE reviews (
    id          SERIAL PRIMARY KEY,
    book_id     INT REFERENCES books(id),
    customer_id INT REFERENCES customers(id),
    rating      INT CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT,
    reviewed_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (book_id, customer_id)  -- one review per book per customer
);

INSERT INTO authors (name, country, birth_year) VALUES
    ('Jane Austen',      'England',   1775),
    ('George Orwell',    'England',   1903),
    ('Haruki Murakami',  'Japan',     1949),
    ('Toni Morrison',    'USA',       1931);

INSERT INTO books (title, author_id, genre, price, stock, published) VALUES
    ('Pride and Prejudice',  1, 'Classic', 12.99, 50,  '1813-01-28'),
    ('Sense and Sensibility',1, 'Classic', 11.99, 30,  '1811-10-30'),
    ('1984',                 2, 'Dystopian', 14.99, 45, '1949-06-08'),
    ('Animal Farm',          2, 'Satire',   9.99,  60, '1945-08-17'),
    ('Norwegian Wood',       3, 'Literary', 16.99, 25, '1987-09-04'),
    ('Kafka on the Shore',   3, 'Literary', 18.99, 20, '2002-09-12'),
    ('Beloved',              4, 'Literary', 15.99, 35, '1987-09-16');

INSERT INTO customers (name, email, city) VALUES
    ('Alice',   'alice@example.com',  'Oslo'),
    ('Bob',     'bob@example.com',    'Bergen'),
    ('Carol',   'carol@example.com',  'Oslo'),
    ('David',   'david@example.com',  'Trondheim');

INSERT INTO orders (customer_id, status) VALUES
    (1, 'delivered'),
    (1, 'shipped'),
    (2, 'delivered'),
    (3, 'pending');

INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 12.99),
    (1, 3, 2, 14.99),
    (2, 6, 1, 18.99),
    (3, 3, 1, 14.99),
    (3, 4, 1,  9.99),
    (4, 2, 3, 11.99);

INSERT INTO reviews (book_id, customer_id, rating, comment) VALUES
    (1, 1, 5, 'A timeless classic!'),
    (3, 1, 5, 'Chilling and prescient.'),
    (3, 2, 4, 'Great but dark.'),
    (6, 3, 4, 'Beautifully written.');

SELECT
    b.title,
    a.name    AS author,
    b.genre,
    b.price,
    b.stock
FROM books b
JOIN authors a ON b.author_id = a.id
ORDER BY a.name, b.title;

SELECT b.title, b.price, b.stock
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.name = 'Haruki Murakami';

SELECT
    o.id                                      AS order_id,
    c.name                                    AS customer,
    o.status,
    COUNT(oi.book_id)                         AS items,
    SUM(oi.quantity * oi.unit_price)          AS total,
    o.ordered_at::DATE                        AS date
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.status, o.ordered_at
ORDER BY o.ordered_at DESC;

SELECT
    b.title,
    a.name                     AS author,
    SUM(oi.quantity)           AS copies_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN books b    ON oi.book_id = b.id
JOIN authors a  ON b.author_id = a.id
GROUP BY b.id, b.title, a.name
ORDER BY copies_sold DESC;

SELECT
    b.title,
    ROUND(AVG(r.rating), 1)   AS avg_rating,
    COUNT(r.id)                AS review_count
FROM books b
JOIN reviews r ON r.book_id = b.id
GROUP BY b.id, b.title
HAVING COUNT(r.id) >= 1
ORDER BY avg_rating DESC;

SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

SELECT
    c.name,
    c.city,
    COUNT(DISTINCT o.id)              AS total_orders,
    SUM(oi.quantity * oi.unit_price)  AS total_spent
FROM customers c
JOIN orders o       ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status != 'cancelled'
GROUP BY c.id, c.name, c.city
ORDER BY total_spent DESC;

SELECT title, stock
FROM books
WHERE stock < 30
ORDER BY stock ASC;

SELECT
    b.genre,
    COUNT(DISTINCT b.id)              AS book_count,
    SUM(oi.quantity * oi.unit_price)  AS revenue
FROM order_items oi
JOIN books b ON oi.book_id = b.id
GROUP BY b.genre
ORDER BY revenue DESC;

SELECT DISTINCT b.title
FROM books b
JOIN order_items oi ON oi.book_id = b.id
JOIN orders o       ON oi.order_id = o.id
JOIN customers c    ON o.customer_id = c.id
WHERE c.city = 'Oslo'
ORDER BY b.title;

SELECT b.title, b.price
FROM books b
WHERE b.id NOT IN (
    SELECT DISTINCT book_id FROM order_items
)
ORDER BY b.title;

SELECT
    a.name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.id)              AS total_reviews
FROM authors a
JOIN books b   ON b.author_id = a.id
JOIN reviews r ON r.book_id = b.id
GROUP BY a.id, a.name
HAVING AVG(r.rating) > 4
ORDER BY avg_rating DESC;