---

create database shoes;
use shoes;

create table role (
id_role int primary key auto_increment,
name_role varchar(50) not null);

create table user(
id_user int primary key auto_increment,
surname varchar(30) not null,
name varchar(20) not null,
middle_name varchar(20) not null,
login varchar(30) not null,
role_id int not null,
foreign key (role_id) references role (id_role));

create table category(
id_category int primary key auto_increment,
category_name varchar(20) not null);

create table subcategory(
id_subcategory int primary key auto_increment,
subcategory_name varchar(20) not null);

create table manufacture(
id_manufacture int primary key auto_increment,
manufacture_name varchar(50) not null);

create table product (
id_product int primary key auto_increment,
product_name varchar(100) not null,
manufacture_id int not null,
description varchar(200) not null,
structure varchar(200) not null,
price decimal(10,2) not null,
category_id int not null,
subcategory_id int not null,
picture varchar(200),
foreign key(manufacture_id) references manufacture(id_manufacture),
foreign key(category_id) references category(id_category),
foreign key(subcategory_id) references subcategory(id_subcategory));

create table size(
id_size int primary key auto_increment,
size_product decimal(2,1) not null);

create table stock(
id_stock int primary key auto_increment,
product_id int not null,     
size_id int not null,
count int not null,
foreign key(product_id) references product(id_product),
foreign key(size_id) references size(id_size)
);

create table orders(
id_order int primary key auto_increment,
date_order date not null,
user_id int not null,
foreign key(user_id) references user(id_user)
);

create table order_item (
id_order_item int primary key auto_increment,
order_id int not null,
stock_id int not null,
quantity int not null,
foreign key(order_id) references orders(id_order),
foreign key(stock_id) references stock(id_stock)
);