---

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,
    status INT NOT NULL,
    sale_date DATE NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

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

INSERT INTO clients (last_name, email) VALUES ('Иванов', 'ivanov@example.com');

INSERT INTO categories (name) VALUES ('Собака');

INSERT INTO breeds (name, category_id) VALUES ('Лабрадор', 1);

INSERT INTO animals (name, color, breed_id, birth_date, price) VALUES ('Тузик', 'Черный', 1, '2026-04-10', 7000.00);

INSERT INTO sale_items (sale_id, animal_id) VALUES (1, 1);