---

-- =====================================================
-- БАЗА ДАННЫХ: bookstore_lab
-- ПОЛНЫЙ СКРИПТ: создание + данные + все запросы
-- =====================================================

-- =====================================================
-- ЧАСТЬ 1. СОЗДАНИЕ ТАБЛИЦ
-- =====================================================

-- Таблица авторов
CREATE TABLE authors(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

-- Таблица жанров
CREATE TABLE genres(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Таблица книг
CREATE TABLE books(
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0),
    author_id INT NOT NULL,
    genre_id INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);

-- Таблица клиентов
CREATE TABLE customers(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Таблица заказов
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    customer_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

-- =====================================================
-- ЧАСТЬ 2. ЗАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ
-- =====================================================

-- Авторы
INSERT INTO authors (first_name, last_name) VALUES
('Джордж', 'Оруэлл'),
('Джоан', 'Роулинг'),
('Лев', 'Толстой'),
('Фёдор', 'Достоевский');

-- Жанры
INSERT INTO genres (name) VALUES
('Роман'),
('Фэнтези'),
('Антиутопия'),
('Детектив');

-- Книги
INSERT INTO books (title, price, author_id, genre_id) VALUES
('1984', 350.00, 1, 3),
('Скотный двор', 250.00, 1, 3),
('Гарри Поттер и философский камень', 450.00, 2, 2),
('Война и мир', 900.00, 3, 1),
('Преступление и наказание', 550.00, 4, 1);

-- Клиенты
INSERT INTO customers (first_name, last_name, email) VALUES
('Иван', 'Петров', 'ivan@example.com'),
('Мария', 'Сидорова', 'maria@example.com'),
('Алексей', 'Смирнов', 'alex@example.com'),
('Ольга', 'Кузнецова', 'olga@example.com');

-- Заказы
INSERT INTO orders (order_date, customer_id, book_id, quantity) VALUES
('2024-01-15', 1, 1, 1),
('2024-01-20', 2, 3, 2),
('2024-02-10', 1, 4, 1),
('2024-02-15', 3, 5, 1),
('2024-03-01', 4, 2, 3);

-- Добавляем клиента без заказов (для демонстрации LEFT JOIN)
INSERT INTO customers (first_name, last_name, email) 
VALUES ('Дмитрий', 'Беззаказный', 'norders@example.com');

-- =====================================================
-- ЧАСТЬ 3. ПРОВЕРКА ДАННЫХ
-- =====================================================

SELECT 'authors' as table_name, COUNT(*) FROM authors
UNION ALL SELECT 'genres', COUNT(*) FROM genres
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'orders', COUNT(*) FROM orders;

-- =====================================================
-- ЧАСТЬ 4. АГРЕГАТНЫЕ ФУНКЦИИ
-- =====================================================

-- 1. Общее количество проданных книг
SELECT SUM(quantity) AS total_sold FROM orders;

-- 2. Средняя цена книг
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;

-- 3. Самый дорогой заказ
SELECT 
    o.id,
    b.title,
    b.price * o.quantity AS total_cost
FROM orders o
JOIN books b ON o.book_id = b.id
ORDER BY total_cost DESC
LIMIT 1;

-- 4. Самый дешёвый заказ
SELECT 
    o.id,
    b.title,
    b.price * o.quantity AS total_cost
FROM orders o
JOIN books b ON o.book_id = b.id
ORDER BY total_cost ASC
LIMIT 1;

-- 5. Сумма покупок по каждому клиенту
SELECT
    c.first_name,
    c.last_name,
    SUM(b.price * o.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id