---

```sql
CREATE DATABASE chudo_obuv;
\c chudo_obuv;
CREATE TABLE users (id SERIAL PRIMARY KEY, login VARCHAR(50) UNIQUE NOT NULL, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, middle_name VARCHAR(50), role VARCHAR(20) NOT NULL DEFAULT 'user', CONSTRAINT check_role CHECK (role IN ('admin', 'manager', 'user')));
CREATE TABLE categories (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE subcategories (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, category_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE);
CREATE TABLE products (id SERIAL PRIMARY KEY, category_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE, subcategory_id INT NOT NULL REFERENCES subcategories(id) ON DELETE CASCADE, brand VARCHAR(100) NOT NULL, name VARCHAR(200) NOT NULL, image_url VARCHAR(500), composition TEXT, description TEXT, price DECIMAL(10, 2) NOT NULL);
CREATE TABLE sizes (id SERIAL PRIMARY KEY, size_value VARCHAR(20) NOT NULL UNIQUE);
CREATE TABLE product_items (id SERIAL PRIMARY KEY, product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE, size_id INT NOT NULL REFERENCES sizes(id) ON DELETE CASCADE, quantity INT NOT NULL DEFAULT 0, UNIQUE(product_id, size_id));
CREATE TABLE orders (id SERIAL PRIMARY KEY, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE, client_last_name VARCHAR(50) NOT NULL, client_first_name VARCHAR(50) NOT NULL, client_middle_name VARCHAR(50), total_amount DECIMAL(10, 2) DEFAULT 0, status VARCHAR(20) DEFAULT 'new', CONSTRAINT check_status CHECK (status IN ('new', 'processing', 'completed', 'cancelled')));
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_item_id INT NOT NULL REFERENCES product_items(id) ON DELETE CASCADE, quantity INT NOT NULL, price_at_order DECIMAL(10, 2) NOT NULL);
INSERT INTO categories (name) VALUES ('obuv'), ('aksessuary');
INSERT INTO subcategories (name, category_id) VALUES ('krossovki', 1), ('botinki', 1), ('tufli', 1), ('sandali', 1);
INSERT INTO sizes (size_value) VALUES ('35'), ('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'), ('45'), ('46');
INSERT INTO users (login, last_name, first_name, middle_name, role) VALUES ('admin', 'administratorov', 'admin', 'adminovich', 'admin'), ('manager1', 'menedzherov', 'ivan', 'ivanovich', 'manager'), ('user1', 'petrov', 'petr', 'petrovich', 'user'), ('user2', 'sidorova', 'anna', 'ivanovna', 'user');
INSERT INTO products (category_id, subcategory_id, brand, name, image_url, composition, description, price) VALUES (1, 1, 'nike', 'air max 270', '/images/nike_air_max_270.jpg', 'tekstil, sintetika', 'stilnye krossovki s maksimalnoy amortizatsiey', 12000.00), (1, 2, 'timberland', 'botinki klassicheskie', '/images/timberland_classic.jpg', 'naturalnaya kozha', 'klassicheskie botinki dlya povsednevnoy noski', 15000.00);
INSERT INTO product_items (product_id, size_id, quantity) VALUES (1, 4, 10), (1, 5, 15), (1, 6, 20), (2, 6, 12), (2, 7, 8);
INSERT INTO orders (user_id, client_last_name, client_first_name, client_middle_name, total_amount, status) VALUES (3, 'ivanov', 'sergey', 'alekseevich', 12000.00, 'new');
INSERT INTO order_items (order_id, product_item_id, quantity, price_at_order) VALUES (1, 1, 1, 12000.00);
```