drop database shop;
create database shop;
use shop;

create table roles(
id int primary key auto_increment, 
name varchar(255));

create table users (
id int primary key auto_increment, 
login varchar(255),
password varchar(255),
id_role int,
foreign key (id_role) references roles(id),
name varchar(255),
l_name varchar(255),
surname varchar(255)
);

create table categories(
id int primary key auto_increment, 
name varchar(255));

create table factories (
id int primary key auto_increment, 
name varchar(255),
ph_number varchar(255));

create table goods (
id int primary key auto_increment, 
photo varchar(255),
name varchar(255),
dscription varchar(255),
id_categ int,
foreign key (id_categ) references categories(id),
supplier varchar(255),
id_factory int,
foreign key (id_factory) references factories(id),
price decimal(8,2),
quantity_warehouse int,
discount int,
measur_unit varchar(255)
);

create table statuses (
id int primary key auto_increment, 
name varchar(255));

create table orders (
id int primary key auto_increment, 
articul varchar(255),
address_place varchar(255),
id_user int, 
foreign key (id_user) references users(id),
dt_order date default '2026-05-31',
dt_delivery date,
total_cost decimal(8,2),
id_status int,
foreign key (id_status) references statuses(id)
);

create table order_goods(
id int primary key auto_increment, 
id_order int,
foreign key (id_order) references orders(id),
id_good int, 
foreign key (id_good) references goods(id),
quantity int
);

insert into roles (name) values 
('admin'),
('manager'),
('client');

insert into users (login, password, id_role, name,  surname, l_name) values 
('ivanov_i', '123', 3, 'Иван', 'Иванов', 'Иванович'),
('petrov_p', '123', 3, 'Петр', 'Петров', 'Петрович'),
('smirnov_a', '123', 1, 'Алексей', 'Смирнов', 'Алексеевич'),
('sidorov_m', '123', 2, 'Михаил', 'Сидоров', 'Михайлович');

insert into categories (name) values 
('настольные игры'),
('пазлы'),
('фигурки'),
('конструкторы');

insert into factories (name, ph_number) values 
('Детский мир', '+7 9123456789'),
('Фан тойс', '+7 9234567890'),
('Петровские игрушки', '+7 9345678901'),
('Джой той', '+7 9456789012');

insert into goods (photo, name, dscription, id_categ, supplier, id_factory, price, quantity_warehouse, discount, measur_unit) values 
('black_cat.jpg', 'Монополия', 'Классика', 1, 'ООО Мир игр', 1, 2500.00, 50, 10, 'шт'),
('goofy_cat.jpg', 'Рубик', 'Кубик', 2, 'ИП Петров', 2, 500.00, 100, 5, 'шт'),
('green_rabbit.jpg', 'Спайдермен', 'Фигурка', 3, 'Марвел', 3, 1500.00, 30, 15, 'шт'),
('sparkles_cat.png', 'Лего', 'Конструктор', 4, 'Лего рус', 4, 3500.00, 20, 20, 'набор');

insert into statuses (name) values 
('новый'),
('в пути'),
('доставлен'),
('отменен');

insert into orders (articul, address_place, id_user, dt_order, dt_delivery, total_cost, id_status) values 
('А00', ' ул.Пушкина 1', 1, '2026-05-15', '2026-05-20', 3000.00, 3),
('А00', ' Невский 10', 2, '2026-05-20', null, 500.00, 1),
('А00', ' Кремлевская 5', 3, '2026-05-25', null, 5000.00, 2),
('А00', ' Ленина 50', 4, '2026-05-28', '2026-06-05', 3500.00, 3);

insert into order_goods (id_order, id_good, quantity) values 
(1, 1, 1),
(1, 2, 1),
(2, 2, 1),
(3, 3, 2),
(3, 4, 1),
(4, 1, 1);

insert into goods ( name, dscription, id_categ, supplier, id_factory, price, quantity_warehouse, discount, measur_unit) values 
( '5Монополия', '5Классика', 1, 'ООО Мир игр', 1, 2500.00, 50, 510, 'шт');
insert into goods ( name, dscription, id_categ, supplier, id_factory, price, quantity_warehouse, discount, measur_unit) values 
( '544Монополия', '544Классика', 1, 'ООО Мир игр', 1, 2500.00, 50, 510, 'шт');


insert into users (login, password, id_role, name, surname, l_name) values 
('admin', 'admin123', 1, 'Дмитрий', 'Кузнецов', 'Дмитриевич'),
('manager', 'manager123', 2, 'Анна', 'Волкова', 'Андреевна'),
('client', 'client123', 3, 'Ксения', 'Егорова', 'Кирилловна');