---

create database if not exists shop_db;
use shop_db;

drop table if exists price_log;
drop table if exists order_items;
drop table if exists products;
drop table if exists categories;

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

create table products (
    id int auto_increment primary key,
    name varchar(255),
    price decimal(10,2),
    category_id int,
    stock int
);

create table order_items (
    id int auto_increment primary key,
    order_id int,
    product_id int,
    quantity int
);

create table price_log (
    id int auto_increment primary key,
    product_id int,
    old_price decimal(10,2),
    new_price decimal(10,2),
    change_date datetime
);

insert into categories (name) values ('электроника');
insert into products (name, price, category_id, stock) values ('монитор', 14000.50, 1, 5);

drop trigger if exists check_price;
drop trigger if exists reduce_stock;
drop trigger if exists check_stock;
drop trigger if exists log_price_change;

delimiter //
create trigger check_price
before insert on products
for each row
begin
    if new.price <= 0 then
        signal sqlstate '45000'
        set message_text = 'цена не может быть отрицательной';
    end if;
end //
delimiter ;

delimiter //
create trigger reduce_stock
after insert on order_items
for each row
begin
    update products
    set stock = stock - new.quantity
    where id = new.product_id;
end //
delimiter ;

delimiter //
create trigger check_stock
before insert on order_items
for each row
begin
    declare current_stock int;
    select stock into current_stock
    from products
    where id = new.product_id;
    if new.quantity > current_stock then
        signal sqlstate '45000'
        set message_text = 'недостаточно товара на складе';
    end if;
end //
delimiter ;

delimiter //
create trigger log_price_change
after update on products
for each row
begin
    if old.price <> new.price then
        insert into price_log(product_id, old_price, new_price, change_date)
        values (old.id, old.price, new.price, now());
    end if;
end //
delimiter ;

insert into products (name, price, category_id, stock) values ('клавиатура', -500.00, 1, 10);

select stock from products where id = 1;

insert into order_items (order_id, product_id, quantity) values (1, 1, 4);

select stock from products where id = 1;

insert into order_items (order_id, product_id, quantity) values (2, 1, 5);

update products set price = 10000.00 where id = 1;

select product_id, old_price, new_price from price_log;