---

-- физическая схема: магазин игрушек (postgresql)
-- auto_increment -> generated by default as identity, datetime -> timestamp

create table category (
    id_category integer generated by default as identity primary key,
    name varchar(50) not null unique,
    description varchar(500)
);

create table manufacturer (
    id_manufacturer integer generated by default as identity primary key,
    name varchar(100) not null,
    country varchar(60) not null,
    phone varchar(20),
    email varchar(256)
);

create table toy (
    id_toy integer generated by default as identity primary key,
    id_category integer not null,
    id_manufacturer integer not null,
    article varchar(20) not null unique,
    name varchar(120) not null,
    description varchar(2000),
    price decimal(10,2) not null check (price > 0),
    min_age integer not null default 0 check (min_age between 0 and 18),
    material varchar(50),
    stock_quantity integer not null default 0 check (stock_quantity >= 0),
    constraint fk_idcategory_toy
        foreign key (id_category)
        references category(id_category),
    constraint fk_idmanufacturer_toy
        foreign key (id_manufacturer)
        references manufacturer(id_manufacturer)
);

create table customer (
    id_customer integer generated by default as identity primary key,
    full_name varchar(100) not null,
    phone varchar(20) not null,
    email varchar(256) not null unique,
    password varchar(255) not null, -- хеш, не сам пароль
    address varchar(300),
    registration_date timestamp not null default current_timestamp
);

create table employee (
    id_employee integer generated by default as identity primary key,
    full_name varchar(100) not null,
    position varchar(30) not null
        check (position in ('Администратор','Менеджер','Продавец','Кладовщик')),
    phone varchar(20) not null,
    email varchar(256) not null unique,
    password varchar(255) not null
);

create table orders (
    id_order integer generated by default as identity primary key,
    id_customer integer not null,
    id_employee integer, -- null, пока заказ никто не взял
    order_date timestamp not null default current_timestamp,
    status varchar(20) not null default 'Новый'
        check (status in ('Новый','Подтверждён','Собран','Доставляется','Выполнен','Отменён')),
    payment_method varchar(20) not null
        check (payment_method in ('Наличные','Карта','Онлайн')),
    delivery_address varchar(300), -- null при самовывозе
    total_amount decimal(12,2) not null default 0 check (total_amount >= 0),
    constraint fk_idcustomer_orders
        foreign key (id_customer)
        references customer(id_customer),
    constraint fk_idemployee_orders
        foreign key (id_employee)
        references employee(id_employee)
);

create table order_item (
    id_order_item integer generated by default as identity primary key,
    id_order integer not null,
    id_toy integer not null,
    quantity integer not null check (quantity >= 1),
    unit_price decimal(10,2) not null check (unit_price > 0),
    unique (id_order, id_toy), -- одна игрушка = одна позиция в заказе
    constraint fk_idorder_order_item
        foreign key (id_order)
        references orders(id_order),
    constraint fk_idtoy_order_item
        foreign key (id_toy)
        references toy(id_toy)
);

create table review (
    id_review integer generated by default as identity primary key,
    id_toy integer not null,
    id_customer integer not null,
    rating integer not null check (rating between 1 and 5),
    text varchar(1000),
    creation_date timestamp not null default current_timestamp,
    constraint fk_idtoy_review
        foreign key (id_toy)
        references toy(id_toy),
    constraint fk_idcustomer_review
        foreign key (id_customer)
        references customer(id_customer)
);

insert into category values
(1,'Конструкторы','Наборы для сборки'),
(2,'Мягкие игрушки','Плюшевые и текстильные игрушки'),
(3,'Настольные игры','Игры для всей семьи'),
(4,'Куклы','Куклы и аксессуары'),
(5,'Машинки','Машинки и транспорт'),
(6,'Развивающие','Игрушки для малышей');

insert into manufacturer values
(1,'Весна','Россия','+7 495 111-22-33','info@vesna-toys.ru'),
(2,'Огонёк','Россия','+7 812 222-33-44','sales@ogonek.ru'),
(3,'WoodKids','Германия','+49 30 1234567',null),
(4,'PlayTime','Китай',null,'export@playtime.cn'),
(5,'Kinderfun','Польша',null,'hello@kinderfun.pl');

insert into toy values
(1,1,4,'KN-001','Конструктор «Город»','Набор из 500 деталей',3490.00,6,'пластик',25),
(2,1,3,'KN-002','Деревянный конструктор','Кубики и арки из бука',1890.00,3,'дерево',40),
(3,2,1,'MI-001','Плюшевый медведь','Медведь 40 см',1290.00,0,'текстиль',60),
(4,2,5,'MI-002','Зайка-сплюшка','Мягкий зайка для сна',890.00,0,'текстиль',0),
(5,3,2,'NI-001','Мемори «Животные»','Игра на память, 48 карточек',590.00,4,'картон',80),
(6,3,2,'NI-002','Шашки и шахматы','Набор 2 в 1',1450.00,7,'дерево',15),
(7,4,1,'KU-001','Кукла Маша','Кукла 35 см с одеждой',1990.00,3,'пластик',30),
(8,4,4,'KU-002','Кукольный домик','Трёхэтажный домик с мебелью',6990.00,4,'дерево',5),
(9,5,4,'MA-001','Машинка на пульте','Джип 1:16 с аккумулятором',2790.00,6,'пластик',12),
(10,5,3,'MA-002','Деревянный паровозик','Паровоз с тремя вагонами',1190.00,2,'дерево',22),
(11,6,5,'RZ-001','Сортер «Домик»','Сортер с 12 фигурами',990.00,1,'пластик',35),
(12,6,3,'RZ-002','Бизиборд','Развивающая доска с замками',2490.00,2,'дерево',8);

insert into customer values
(1,'Никита Павликов','+7 900 111-11-11','nikita.pavlikov@gmail.com','5f4dcc3b5aa765d61d8327deb882cf99','Москва, ул. Ленина, 10','2026-08-01 10:00:00'),
(2,'Анна Смирнова','+7 900 222-22-22','anna.smirnova@mail.ru','d8578edf8458ce06fbc5bb76a58c5ca4','Санкт-Петербург, Невский пр., 25','2026-08-03 14:20:00'),
(3,'Дмитрий Козлов','+7 900 333-33-33','dmitry.kozlov@gmail.com','25d55ad283aa400af464c76d713c07ad',null,'2026-08-10 09:05:00'),
(4,'Елена Волкова','+7 900 444-44-44','elena.volkova@yandex.ru','e99a18c428cb38d5f260853678922e03','Казань, ул. Баумана, 5','2026-08-15 19:40:00'),
(5,'Сергей Морозов','+7 900 555-55-55','sergey.morozov@gmail.com','fcea920f7412b5da7be0cf42b8c93759','Москва, ул. Тверская, 7','2026-08-20 11:30:00'),
(6,'Ольга Новикова','+7 900 666-66-66','olga.novikova@mail.ru','827ccb0eea8a706c4c34a16891f84e7b',null,'2026-09-01 16:00:00'),
(7,'Артём Соколов','+7 900 777-77-77','artem.sokolov@gmail.com','6cb75f652a9b52798eb6cf2201057c73','Екатеринбург, ул. Мира, 3','2026-09-05 12:10:00'),
(8,'Мария Лебедева','+7 900 888-88-88','maria.lebedeva@yandex.ru','0d107d09f5bbe40cade3de5c71e9e9b7','Новосибирск, Красный пр., 50','2026-09-12 08:45:00');

insert into employee values
(1,'Иван Попов','Администратор','+7 901 100-00-01','i.popov@toyshop.ru','a3dcb4d229de6fde0db5686dee47145d'),
(2,'Павлик Никитов','Менеджер','+7 901 100-00-02','p.nikitov@toyshop.ru','1a1dc91c907325c69271ddf0c944bc72'),
(3,'Ксения Орлова','Менеджер','+7 901 100-00-03','k.orlova@toyshop.ru','b59c67bf196a4758191e42f76670ceba'),
(4,'Глеб Фёдоров','Продавец','+7 901 100-00-04','g.fedorov@toyshop.ru','c33367701511b4f6020ec61ded352059'),
(5,'Тимур Зайцев','Кладовщик','+7 901 100-00-05','t.zaitsev@toyshop.ru','8621ffdbc5698829397d97767ac13db3');

insert into orders values
(1,1,2,'2026-09-02 12:00:00','Выполнен','Карта','Москва, ул. Ленина, 10',4780.00),
(2,2,3,'2026-09-10 15:30:00','Доставляется','Онлайн','Санкт-Петербург, Невский пр., 25',3980.00),
(3,3,4,'2026-09-12 11:15:00','Выполнен','Наличные',null,2760.00),
(4,4,2,'2026-09-20 18:00:00','Собран','Карта','Казань, ул. Баумана, 5',6990.00),
(5,5,null,'2026-10-01 10:20:00','Новый','Онлайн','Москва, ул. Тверская, 7',3980.00),
(6,1,3,'2026-10-03 09:40:00','Отменён','Онлайн','Москва, ул. Ленина, 10',1780.00),
(7,6,2,'2026-10-05 14:05:00','Подтверждён','Карта',null,4380.00),
(8,7,null,'2026-10-08 17:50:00','Новый','Наличные','Екатеринбург, ул. Мира, 3',1190.00),
(9,8,4,'2026-10-10 13:25:00','Выполнен','Карта','Новосибирск, Красный пр., 50',3170.00),
(10,2,3,'2026-10-12 20:10:00','Выполнен','Онлайн','Санкт-Петербург, Невский пр., 25',8260.00);

insert into order_item values
(1,1,1,1,3490.00),
(2,1,3,1,1290.00),
(3,2,7,2,1990.00),
(4,3,5,3,590.00),
(5,3,11,1,990.00),
(6,4,8,1,6990.00),
(7,5,9,1,2790.00),
(8,5,10,1,1190.00),
(9,6,4,2,890.00),
(10,7,12,1,2490.00),
(11,7,2,1,1890.00),
(12,8,10,1,1190.00),
(13,9,3,2,1290.00),
(14,9,5,1,590.00),
(15,10,1,1,3490.00),
(16,10,9,1,2790.00),
(17,10,11,2,990.00);

insert into review values
(1,1,1,5,'Сын в восторге, собирали весь вечер','2026-09-05 18:00:00'),
(2,3,1,4,'Мягкий, но немного линяет','2026-09-06 12:00:00'),
(3,5,3,5,'Отличная игра для всей семьи','2026-09-15 20:30:00'),
(4,11,3,4,'Фигурки яркие, ребёнку нравится','2026-09-15 20:35:00'),
(5,3,8,5,'Купили второго медведя в подарок','2026-10-13 10:00:00'),
(6,5,8,3,'Карточки тонковаты','2026-10-13 10:05:00'),
(7,1,2,5,'Детали качественные, ничего не потерялось','2026-10-15 19:20:00'),
(8,9,2,4,'Быстро разряжается, но ездит бодро','2026-10-15 19:25:00'),
(9,11,2,5,'Брали на годик — идеально','2026-10-15 19:30:00'),
(10,7,2,2,'Одежда порвалась через неделю','2026-09-20 11:00:00'),
(11,8,4,5,null,'2026-09-25 16:40:00'),
(12,2,6,4,'Хороший деревянный набор','2026-10-09 09:15:00');



-- 1
-- игрушки дешевле 1000
select id_toy, name, price
from toy
where price < 1000;

-- статусы, в которых больше одного заказа
select status, count(*) as cnt
from orders
group by status
having count(*) > 1;

-- заказы, которые ещё никто не взял
select id_order, id_customer, order_date
from orders
where status = 'Новый';

-- заказы, которые собраны или в доставке
select o.id_order, o.status, o.total_amount
from orders as o
where o.status in ('Собран', 'Доставляется');

-- сколько заказов в каждом статусе
select status, count(*)
from orders
group by status;

-- первые 3 покупателя
select c.id_customer, c.full_name
from customer as c
limit 3;

-- сотрудники по алфавиту
select full_name, position
from employee
order by full_name;

-- 2
-- игрушки не из пластика
select t.id_toy, t.name, t.material
from toy as t
where t.material not in ('пластик')
order by t.id_toy;

-- заказы покупателя с айди 2
select o.id_order, o.order_date, o.status
from orders as o
where o.id_customer = 2
order by o.order_date;

-- топ-3 самых дорогих игрушки
select t.id_toy, t.name, t.price
from toy as t
order by t.price desc
limit 3;

-- отзывы на игрушку с айди 3
select r.id_customer, r.rating, r.text
from review as r
where r.id_toy = 3
order by r.creation_date;

-- самый первый зарегистрированный покупатель
select c.full_name, c.email, c.registration_date
from customer as c
order by c.registration_date
limit 1;


-- 3
-- топ-3 покупателя по числу отзывов
select c.full_name, count(r.id_review) as reviews_count
from customer as c
left join review as r on r.id_customer = c.id_customer
group by c.id_customer, c.full_name
order by reviews_count desc
limit 3;

-- все категории и их игрушки
select cat.name as category, t.id_toy, t.name as toy
from category as cat
left join toy as t on t.id_category = cat.id_category
order by cat.name, t.id_toy;

-- все заказы и id игрушек в них
select o.id_order, o.status, oi.id_toy
from orders as o
left join order_item as oi on oi.id_order = o.id_order
order by o.id_order;

-- 4
-- 1. игрушки с категорией и производителем
select t.id_toy, t.name, t.price, cat.name as category, m.name as manufacturer, m.country
from toy as t
left join category as cat on cat.id_category = t.id_category
left join manufacturer as m on m.id_manufacturer = t.id_manufacturer
order by cat.name, t.id_toy;

-- 2. лента отзывов: кто, на какую игрушку, из какой категории
select r.id_review, r.creation_date, c.full_name as author, t.name as toy, cat.name as category, r.rating, r.text
from review as r
left join customer as c on c.id_customer = r.id_customer
left join toy as t on t.id_toy = r.id_toy
left join category as cat on cat.id_category = t.id_category
order by r.creation_date desc;

-- 3. состав заказов: покупатель, сотрудник, игрушка, сумма позиции
select o.id_order, c.full_name as customer, e.full_name as employee, t.name as toy,
       oi.quantity, oi.unit_price, oi.quantity * oi.unit_price as item_sum
from orders as o
left join customer as c on c.id_customer = o.id_customer
left join employee as e on e.id_employee = o.id_employee
left join order_item as oi on oi.id_order = o.id_order
left join toy as t on t.id_toy = oi.id_toy
order by o.id_order, t.name;

-- 5 вложенные запросы

-- 1. покупатели, которые платили онлайн
select c.id_customer, c.full_name
from ( select distinct id_customer from orders where payment_method = 'Онлайн' )
as online join customer as c on c.id_customer = online.id_customer;

-- 2. игрушки, у которых больше одного отзыва
select r.id_toy, r.cnt
from ( select id_toy, count(*) as cnt from review group by id_toy )
as r where r.cnt > 1
order by r.cnt desc;

-- 3. покупатели и сколько у них заказов
select c.full_name, z.cnt as orders_count
from ( select id_customer, count(*) as cnt from orders group by id_customer )
as z join customer as c on c.id_customer = z.id_customer
order by z.cnt desc;

-- 4. категории и сколько в них игрушек
select cat.name, z.cnt as toys_count
from ( select id_category, count(*) as cnt from toy group by id_category )
as z join category as cat on cat.id_category = z.id_category
order by z.cnt desc;


-- 6 вложенные запросы с <, >, in, not in, exists
-- 1. производители, у которых больше 2 игрушек
select m.id_manufacturer, m.name, z.cnt as toys_count
from ( select id_manufacturer, count(*) as cnt from toy group by id_manufacturer ) as z
left join manufacturer as m on m.id_manufacturer = z.id_manufacturer
where z.cnt > 2
order by z.cnt desc;

-- 2. игрушки со средней оценкой ниже 4
select t.id_toy, t.name, r.avg_rating
from toy as t
join ( select id_toy, round(avg(rating), 2) as avg_rating from review group by id_toy ) as r
  on r.id_toy = t.id_toy
where r.avg_rating < 4
order by r.avg_rating;

-- 3. сотрудники, у которых больше одного незакрытого заказа
select e.id_employee, e.full_name, count(o.id_order) as active_orders
from employee as e
join orders as o on o.id_employee = e.id_employee
where e.id_employee in (
    select id_employee from orders
    where status not in ('Выполнен', 'Отменён')
    group by id_employee
    having count(*) > 1
)
and o.status not in ('Выполнен', 'Отменён')
group by e.id_employee, e.full_name
order by active_orders desc;

-- 4. игрушки, которые ни разу не заказывали
select t.id_toy, t.name
from toy as t
where t.id_toy not in (select id_toy from order_item);

-- 5. категории, где есть игрушка, которой нет на складе
select cat.id_category, cat.name
from category as cat
where exists (
    select 1 from toy as t
    where t.id_category = cat.id_category and t.stock_quantity = 0
)
order by cat.id_category;



-- 7. insert on conflict

-- добавить или обновить покупателя
insert into customer (id_customer, full_name, phone, email, password, address)
values (9, 'Егор Иванов', '+7 900 999-99-99', 'egor.ivanov@gmail.com', '9f86d081884c7d659a2feaa0c55ad015', 'Москва, ул. Арбат, 12')
on conflict (id_customer) do update
set full_name = excluded.full_name,
    phone = excluded.phone,
    email = excluded.email,
    address = excluded.address;

-- добавить или обновить сотрудника
insert into employee (id_employee, full_name, position, phone, email, password)
values (6, 'Алина Белова', 'Продавец', '+7 901 100-00-06', 'a.belova@toyshop.ru', '2c26b46b68ffc68ff99b453c1d304134')
on conflict (id_employee) do update
set position = excluded.position,
    phone = excluded.phone;

-- добавить или переименовать категорию
insert into category (id_category, name, description)
values (7, 'Пазлы', 'Пазлы и мозаики')
on conflict (id_category) do update
set name = excluded.name,
    description = excluded.description;

-- та же игрушка в том же заказе -> увеличиваем количество
insert into order_item (id_order_item, id_order, id_toy, quantity, unit_price)
values (18, 1, 1, 1, 3490.00)
on conflict (id_order, id_toy) do update
set quantity = order_item.quantity + excluded.quantity;

-- добавить или переписать отзыв
insert into review (id_review, id_toy, id_customer, rating, text)
values (13, 6, 9, 5, 'Классный набор, играем с дедушкой')
on conflict (id_review) do update
set rating = excluded.rating,
    text = excluded.text;


-- 7.1

-- добавить нового производителя
insert into manufacturer (id_manufacturer, name, country, phone, email)
values (6, 'Сказка', 'Беларусь', '+375 17 123-45-67', 'info@skazka.by');

-- добавить новую игрушку
insert into toy (id_toy, id_category, id_manufacturer, article, name, description, price, min_age, material, stock_quantity)
values (13, 6, 6, 'RZ-003', 'Счёты деревянные', 'Счёты с цветными бусинами', 790.00, 3, 'дерево', 20);

-- добавить нового покупателя (дата регистрации по умолчанию)
insert into customer (id_customer, full_name, phone, email, password)
values (10, 'Олег Петров', '+7 900 101-01-01', 'oleg.petrov@mail.ru', 'fd61a03af4f77d870fc21e05e7e80678');

-- оформить заказ (дата и статус по умолчанию)
insert into orders (id_order, id_customer, payment_method, delivery_address)
values (11, 10, 'Карта', 'Тула, ул. Советская, 1');

-- добавить позицию в заказ
insert into order_item (id_order_item, id_order, id_toy, quantity, unit_price)
values (19, 11, 13, 2, 790.00);

-- оставить отзыв
insert into review (id_review, id_toy, id_customer, rating, text)
values (14, 13, 10, 5, 'Ребёнок учится считать с удовольствием');

-- 8. update
-- поднять цены на машинки на 10%
update toy
set price = price * 1.10
where id_category = 5;

-- взять заказ в работу
update orders
set status = 'Подтверждён',
    id_employee = 3
where id_order = 5;

-- пополнить склад
update toy
set stock_quantity = stock_quantity + 30
where id_toy = 4;

-- сменить хеш пароля покупателя
update customer
set password = 'b3a8e0e1f9ab1bfe3a36f231f676f78b'
where id_customer = 2;

-- пересчитать суммы всех заказов по позициям
update orders as o
set total_amount = coalesce((
    select sum(oi.quantity * oi.unit_price)
    from order_item as oi
    where oi.id_order = o.id_order
), 0);


-- 9. delete

-- убрать позицию из заказа
delete from order_item
where id_order = 7 and id_toy = 2;

-- удалить плохие отзывы
delete from review
where rating <= 2;

-- сначала позиции отменённых заказов, потом сами заказы
delete from order_item
where id_order in (select id_order from orders where status = 'Отменён');

delete from orders
where status = 'Отменён';

-- удалить пустые категории
delete from category as cat
where not exists (select 1 from toy as t where t.id_category = cat.id_category);

-- удалить сотрудника без заказов
delete from employee
where id_employee = 6;