---

DROP DATABASE IF EXISTS petshop;
CREATE DATABASE IF NOT EXISTS petshop;
USE petshop;

CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE breeds (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE animals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(50),
    breed_id INT NOT NULL,
    birth_date DATE NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (breed_id) REFERENCES breeds(id)
);

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    sale_date DATETIME NOT NULL,
    status VARCHAR(50) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

CREATE TABLE sale_items (
    sale_id INT NOT NULL,
    animal_id INT NOT NULL,
    PRIMARY KEY (sale_id, animal_id),
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (animal_id) REFERENCES animals(id)
);

INSERT INTO clients (last_name, email) VALUES 
('Cli', 'Clil@ya.ru'),
('Cli2', 'Clil2@ya.ru');

INSERT INTO categories (name) VALUES 
('Собака'),
('Кошка'),
('Птица');

INSERT INTO breeds (name, category_id) VALUES 
('Лабрадор', 1),
('Овчарка', 1),
('Такса', 1),
('Персидская', 2),
('Британская', 2),
('Попугай', 3),
('Канарейка', 3);

INSERT INTO animals (name, color, breed_id, birth_date, price) VALUES 
('Тузик', 'Черный', 1, '2026-04-10', 7000.00),
('Мурка', 'Серый', 4, '2026-03-10', 2000.00),
('Кеша', 'Зеленый', 6, '2026-04-10', 1000.00),
('Рекс', 'Рыжий', 2, '2025-12-10', 2000.00),
('Шарик', 'Коричневая', 3, '2026-02-10', 8000.00),
('Барсик', 'Серый', 4, '2025-12-10', 1800.00),
('Борис', 'Черепаховый', 5, '2026-04-10', 6000.00),
('Томас', 'Серебристый', 5, '2026-04-10', 6500.00),
('Кешуля', 'Желтый', 7, '2026-02-10', 2200.00);

INSERT INTO sales (client_id, total_price, sale_date, status) VALUES 
(1, 1800.00, '2024-05-20 12:00:00', 'получен'),
(1, 6500.00, '2024-05-20 12:00:00', 'получен'),
(2, 2200.00, '2024-05-15 12:00:00', 'в пути');

INSERT INTO sale_items (sale_id, animal_id) VALUES 
(1, 6),
(2, 8),
(3, 9);



CREATE VIEW available_pets_by_category AS
SELECT 
    c.name AS category_name,
    a.name AS animal_name,
    a.color,
    b.name AS breed_name,
    a.birth_date,
    a.price
FROM animals a
JOIN breeds b ON a.breed_id = b.id
JOIN categories c ON b.category_id = c.id
LEFT JOIN sale_items si ON a.id = si.animal_id
WHERE si.animal_id IS NULL;

SELECT * FROM available_pets;