---

/* 
	Задание 1.
    ------------------------------------------------------------------------------------
 */
delimiter //

create function get_price_category(price decimal(10,2))
returns varchar(255)
begin
	if price < 5000 then
		return 'Дерешвый';
	elseif price < 80000 then
		return 'Средний';
	else
		return 'Дорогой';
	end if;
end //

delimiter ;

/* 
	Задание 2.
    ------------------------------------------------------------------------------------
 */
select name, price, get_price_category(price) as price_category
from products;

/* 
	Задание 3.
    ------------------------------------------------------------------------------------
 */
select name, price
from products
where get_price_category(price) = 'Дорогой';

/* 
	Задание 4.
    ------------------------------------------------------------------------------------
 */
delimiter //

create function get_product_price(product_id int)
returns decimal(10,2)
begin
	declare product_price decimal(10,2);
    
    select price into product_price
    from products
    where id = product_id;
    
    return product_price;
end //

delimiter ;

/* 
	Задание 5.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function get_product_category(product_id int)
 returns varchar(255)
 begin
	declare product_price decimal(10,2);
    
    select price into product_price
    from products
    where id = product_price;
    
    return product_price;
 end //
 
 delimiter ;
 
 /* 
	Задание 6.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function calc_price(quantity int, price decimal(10,2))
 returns decimal(10,2)
 begin
	return quantity * price;
 end //
 
 delimiter ;
 
 /* 
	Задание 7.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function get_order_sum(order_id int)
 returns decimal(10,2)
 begin
	declare order_sum decimal(10,2);
    
    select sum(order_items.quantity * products.price) INTO order_sum
	from order_items
    join products on order_items.product_id = products.id
    where order_items.order_id = order_id;
    
    return order_sum;
 end //
 
 delimiter ;
 
  /* 
	Задание 8.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function get_discount(order_sum decimal(10,2))
 returns decimal(5,2)
 begin
	if order_sum >= 100000 then
		return 10;
	elseif order_sum >= 50000 then
		return 5;
	else
		return 0;
	end if;
 end //
 
 delimiter ;
 
  /* 
	Задание 9.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function get_order_sum_with_discount(order_id int)
 returns decimal(10,2)
 begin
	declare total decimal(10,2);
    declare disc decimal(5,2);
    
    set total = get_order_sum(order_id);
    set disc = get_discount(total);
    
    return total * (1 - disc / 100);
 end //
 
 delimiter ;
 
  /* 
	Задание 10.
    ------------------------------------------------------------------------------------
 */
 delimiter //
 create function get_stock_status(product_id int)
 returns varchar(255)
 begin
	declare stock_qty int;
    
    select stock into stock_qty
    from products
    where id = product_id;
    
    if stock_qty = 0 then
		return 'Нет в наличии';
	elseif stock_qty < 5 then
		return 'Мало';
	else
		return 'Достаточно';
	end if;
 end //
 
 delimiter ;
 
 /*
	Использование.
    ------------------------------------------------------------------------------------
 */
 select get_order_sum_with_discount(1);
 
 select name, get_stock_status(id)
 from products;