---

-- Перечисления (ENUM)
CREATE TYPE order_status AS ENUM ('Новый', 'В работе', 'Выполнен', 'Отменен');
CREATE TYPE customer_status AS ENUM ('Новый', 'Зарегистрирован', 'Активный', 'Заблокирован');

-- Таблица категорий
CREATE TABLE categories (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- Таблица товаров
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id BIGINT REFERENCES categories(id) ON DELETE RESTRICT,
    name VARCHAR(255) NOT NULL UNIQUE,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 39.00)
);

-- Таблица покупателей
CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    status customer_status NOT NULL DEFAULT 'Новый',
    rating INT NOT NULL DEFAULT 0 CHECK (rating >= 0)
);

-- Таблица сотрудников
CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL
);

-- Таблица заказов
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    employee_id BIGINT NOT NULL REFERENCES employees(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status order_status NOT NULL DEFAULT 'Новый',
    total_amount NUMERIC(10,2) NOT NULL DEFAULT 0.00,
    discount_percent NUMERIC(5,2) NOT NULL DEFAULT 0.00 CHECK (discount_percent BETWEEN 0 AND 100),
    discount_amount NUMERIC(10,2) NOT NULL DEFAULT 0.00,
    final_amount NUMERIC(10,2) NOT NULL DEFAULT 0.00
);

-- Детали заказа (состав заказа)
CREATE TABLE products_orders (
    order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT REFERENCES products(id) ON DELETE RESTRICT,
    quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    price NUMERIC(10,2) CHECK (price > 0),
    total_price NUMERIC(10,2) CHECK (total_price > 0),
    PRIMARY KEY (order_id, product_id)
);