---

-- ============================================
-- 1 ЗАДАНИЕ: Функция для определения ценовой категории
-- ============================================
DELIMITER $$

CREATE FUNCTION get_price_category(price DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE category VARCHAR(20);
    
    IF price < 5000 THEN
        SET category = 'Дешевый';
    ELSEIF price >= 5000 AND price <= 80000 THEN
        SET category = 'Средний';
    ELSE
        SET category = 'Дорогой';
    END IF;
    
    RETURN category;
END$$

DELIMITER ;

-- ============================================
-- 2 ЗАДАНИЕ: Список товаров + их категория
-- ============================================
SELECT 
    name AS 'Товар',
    price AS 'Цена',
    get_price_category(price) AS 'Категория цены'
FROM products;

-- ============================================
-- 3 ЗАДАНИЕ: Только "Дорогие" товары
-- ============================================
SELECT 
    name AS 'Товар',
    price AS 'Цена',
    get_price_category(price) AS 'Категория цены'
FROM products
WHERE get_price_category(price) = 'Дорогой';

-- ============================================
-- 4 ЗАДАНИЕ: Функция - цена товара по id
-- ============================================
DELIMITER $$

CREATE FUNCTION get_product_price(product_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE prod_price DECIMAL(10,2);
    
    SELECT price INTO prod_price
    FROM products
    WHERE id = product_id;
    
    RETURN prod_price;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    id,
    name,
    get_product_price(id) AS 'Цена'
FROM products;

-- ============================================
-- 5 ЗАДАНИЕ: Функция - категория товара (через функцию из 1 задания)
-- ============================================
DELIMITER $$

CREATE FUNCTION get_product_category(product_id INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE prod_price DECIMAL(10,2);
    DECLARE prod_category VARCHAR(20);
    
    SELECT price INTO prod_price
    FROM products
    WHERE id = product_id;
    
    SET prod_category = get_price_category(prod_price);
    
    RETURN prod_category;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    id,
    name,
    price,
    get_product_category(id) AS 'Категория товара'
FROM products;

-- ============================================
-- 6 ЗАДАНИЕ: Функция - итоговая сумма (количество * цена)
-- ============================================
DELIMITER $$

CREATE FUNCTION calculate_total_amount(quantity INT, price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN quantity * price;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    product_id,
    quantity,
    get_product_price(product_id) AS 'Цена',
    calculate_total_amount(quantity, get_product_price(product_id)) AS 'Сумма'
FROM order_items;

-- ============================================
-- 7 ЗАДАНИЕ: Функция - общая сумма заказа
-- ============================================
DELIMITER $$

CREATE FUNCTION get_order_total(order_id_param INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10,2);
    
    SELECT SUM(calculate_total_amount(oi.quantity, p.price)) INTO total
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = order_id_param;
    
    RETURN IFNULL(total, 0);
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    id AS 'ID заказа',
    get_order_total(id) AS 'Общая сумма'
FROM orders;

-- ============================================
-- 8 ЗАДАНИЕ: Функция - скидка от суммы заказа
-- ============================================
DELIMITER $$

CREATE FUNCTION get_discount_by_amount(order_amount DECIMAL(10,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
    DECLARE discount_percent DECIMAL(5,2);
    
    IF order_amount >= 100000 THEN
        SET discount_percent = 10.00;
    ELSEIF order_amount >= 50000 THEN
        SET discount_percent = 5.00;
    ELSE
        SET discount_percent = 0.00;
    END IF;
    
    RETURN discount_percent;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    get_order_total(1) AS 'Сумма заказа',
    get_discount_by_amount(get_order_total(1)) AS 'Скидка %';

-- ============================================
-- 9 ЗАДАНИЕ: Функция - итог заказа с учетом скидки
-- ============================================
DELIMITER $$

CREATE FUNCTION get_final_order_total(order_id_param INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE order_total DECIMAL(10,2);
    DECLARE discount_percent DECIMAL(5,2);
    DECLARE final_total DECIMAL(10,2);
    
    -- Получаем сумму заказа
    SET order_total = get_order_total(order_id_param);
    
    -- Получаем скидку
    SET discount_percent = get_discount_by_amount(order_total);
    
    -- Рассчитываем итоговую сумму со скидкой
    SET final_total = order_total * (1 - discount_percent / 100);
    
    RETURN final_total;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    id AS 'ID заказа',
    get_order_total(id) AS 'Сумма без скидки',
    get_discount_by_amount(get_order_total(id)) AS 'Скидка %',
    get_final_order_total(id) AS 'Итог со скидкой'
FROM orders;

-- ============================================
-- 10 ЗАДАНИЕ: Функция - наличие товара
-- ============================================
DELIMITER $$

CREATE FUNCTION get_stock_status(product_id INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE product_stock INT;
    DECLARE status_message VARCHAR(20);
    
    SELECT stock INTO product_stock
    FROM products
    WHERE id = product_id;
    
    IF product_stock = 0 THEN
        SET status_message = 'Нет в наличии';
    ELSEIF product_stock < 5 THEN
        SET status_message = 'Мало';
    ELSE
        SET status_message = 'Достаточно';
    END IF;
    
    RETURN status_message;
END$$

DELIMITER ;

-- Пример использования:
SELECT 
    id,
    name,
    stock,
    get_stock_status(id) AS 'Статус наличия'
FROM products;


-- Полный отчет по товарам
SELECT 
    p.id,
    p.name,
    p.price AS 'Цена',
    get_price_category(p.price) AS 'Ценовая категория',
    get_stock_status(p.id) AS 'Наличие',
    p.stock AS 'Кол-во на складе'
FROM products p;

-- Полный отчет по заказам
SELECT 
    o.id AS 'Заказ №',
    o.order_date AS 'Дата',
    o.status AS 'Статус',
    get_order_total(o.id) AS 'Сумма',
    get_discount_by_amount(get_order_total(o.id)) AS 'Скидка',
    get_final_order_total(o.id) AS 'Итого к оплате'
FROM orders o;