---

-- ============================================================
-- ПРАКТИЧЕСКАЯ РАБОТА
-- Тема: «Переменные и подпрограммы в MySQL»
-- Дисциплина: МДК 11
-- Специальность: 09.02.07 Информационные системы и программирование
-- ============================================================


-- ============================================================
-- ПОДГОТОВКА: создание БД и таблицы
-- >>> СКРИНШОТ 1: выполните блок целиком, сделайте скриншот
--                вкладки Result Grid и списка таблиц слева.
-- ============================================================

CREATE DATABASE IF NOT EXISTS lab_procedures;
USE lab_procedures;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    discount_percent DECIMAL(5, 2) DEFAULT 0.00
        CHECK (discount_percent BETWEEN 0 AND 100),
    quantity INT DEFAULT 0 CHECK (quantity >= 0)
) ENGINE=InnoDB;

INSERT INTO products (name, price, discount_percent, quantity) VALUES
('Ноутбук', 85000.00, 5.00, 10),
('Мышь беспроводная', 1200.00, 0.00, 50),
('Клавиатура механическая', 4500.00, 10.00, 30),
('Монитор 27"', 22000.00, 7.50, 15),
('Веб-камера', 3200.00, 0.00, 40);

SELECT * FROM products;


-- ============================================================
-- ЗАДАНИЕ 1. Пользовательские переменные
-- >>> СКРИНШОТ 2: выполните блок целиком — в результате
--                 должна быть таблица с base_price, discount,
--                 final_price. Сделайте скриншот.
-- ============================================================

SET @base_price = 0;
SET @discount = 0;
SET @final_price = 0;

SELECT price, discount_percent
INTO @base_price, @discount
FROM products
WHERE id = 1;

SET @final_price = @base_price * (1 - @discount / 100);

SELECT @base_price AS base_price,
       @discount AS discount,
       @final_price AS final_price;

-- >>> ОТВЕТ НА КОНТРОЛЬНЫЙ ВОПРОС 1:
-- DECLARE — это конструкция для локальных переменных, которая
-- работает только внутри составных операторов BEGIN...END
-- хранимых процедур, функций и триггеров. Вне программы у MySQL
-- нет контекста (блока BEGIN), в котором эта переменная могла бы
-- существовать, поэтому сервер выдаёт синтаксическую ошибку.
-- Для работы на уровне сессии используются пользовательские
-- переменные — они начинаются с @ и объявляются через SET.


-- ============================================================
-- ЗАДАНИЕ 2. Функция calc_final_price
-- >>> СКРИНШОТ 3: сначала выполните блок создания функции
--                 (должно появиться сообщение об успехе в
--                 разделе Schemas → Functions).
-- ============================================================

DELIMITER //

CREATE FUNCTION calc_final_price(
    p_price DECIMAL(10,2),
    p_discount_percent DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE v_discount DECIMAL(10,2);
    DECLARE v_final DECIMAL(10,2);

    SET v_discount = p_price * (p_discount_percent / 100.0);
    SET v_final = p_price - v_discount;

    RETURN v_final;
END //

DELIMITER ;

-- >>> СКРИНШОТ 3 (продолжение): теперь выполните проверочный
--                 SELECT и сделайте скриншот результата.

SELECT id, name, price, discount_percent,
       calc_final_price(price, discount_percent) AS final_price
FROM products;

-- >>> ОТВЕТ НА КОНТРОЛЬНЫЙ ВОПРОС 2:
-- Функция инкапсулирует формулу расчёта в одном месте: если
-- логику нужно изменить (например, добавить округление или
-- коэффициент), правка делается один раз, а не во всех запросах.
-- Это исключает ошибки копирования формулы и делает SQL-запросы
-- короче и читаемее. Функцию можно переиспользовать в SELECT,
-- WHERE, ORDER BY и внутри других процедур.


-- ============================================================
-- ЗАДАНИЕ 3. Процедура get_product_info
-- >>> СКРИНШОТ 4: выполните блок создания процедуры
--                 (сообщение об успехе в Schemas → Procedures).
-- ============================================================

DELIMITER //

CREATE PROCEDURE get_product_info(
    IN p_id INT,
    OUT out_name VARCHAR(100),
    OUT out_final_price DECIMAL(10,2),
    OUT out_status VARCHAR(50)
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_discount DECIMAL(5,2);
    DECLARE v_qty INT;

    SELECT name, price, discount_percent, quantity
    INTO out_name, v_price, v_discount, v_qty
    FROM products
    WHERE id = p_id;

    IF out_name IS NULL THEN
        SET out_name = 'Товар не найден';
        SET out_final_price = 0;
        SET out_status = 'Не применимо';
    ELSE
        SET out_final_price = calc_final_price(v_price, v_discount);

        IF v_qty > 10 THEN
            SET out_status = 'В наличии';
        ELSEIF v_qty >= 1 THEN
            SET out_status = 'Мало';
        ELSE
            SET out_status = 'Нет в наличии';
        END IF;
    END IF;
END //

DELIMITER ;

-- >>> СКРИНШОТ 4 (продолжение): выполните по очереди три вызова
--                 (id = 1, id = 3, id = 99) и сделайте скриншот
--                 каждого результата.

SET @name = '', @price = 0, @status = '';
CALL get_product_info(1, @name, @price, @status);
SELECT @name AS name, @price AS final_price, @status AS status;

SET @name = '', @price = 0, @status = '';
CALL get_product_info(3, @name, @price, @status);
SELECT @name AS name, @price AS final_price, @status AS status;

SET @name = '', @price = 0, @status = '';
CALL get_product_info(99, @name, @price, @status);
SELECT @name AS name, @price AS final_price, @status AS status;

-- >>> ОТВЕТ НА КОНТРОЛЬНЫЙ ВОПРОС 3:
-- Параметры OUT позволяют вернуть из процедуры несколько
-- значений одновременно, тогда как SELECT внутри процедуры
-- возвращает результат только как результирующий набор.
-- Значения через OUT можно сохранить в переменные и использовать
-- дальше в вызывающем коде (в других запросах, приложениях,
-- вложенных вызовах). Это делает интерфейс процедуры явным:
-- сразу видно, какие данные она принимает, а какие возвращает.


-- ============================================================
-- ЗАДАНИЕ 4. Процедура report_total_value
-- >>> СКРИНШОТ 5: выполните блок создания процедуры
--                 (сообщение об успехе в Schemas → Procedures).
-- ============================================================

DELIMITER //

CREATE PROCEDURE report_total_value()
BEGIN
    DECLARE v_id INT DEFAULT 1;
    DECLARE v_max_id INT;
    DECLARE v_current_price DECIMAL(10,2);
    DECLARE v_current_discount DECIMAL(5,2);
    DECLARE v_total DECIMAL(15,2) DEFAULT 0;

    SELECT MAX(id) INTO v_max_id FROM products;

    WHILE v_id <= v_max_id DO
        SELECT price, discount_percent
        INTO v_current_price, v_current_discount
        FROM products
        WHERE id = v_id;

        IF v_current_price IS NOT NULL THEN
            SET v_total = v_total +
                calc_final_price(v_current_price, v_current_discount);
        END IF;

        SET v_id = v_id + 1;
    END WHILE;

    SELECT v_total AS total_value_with_discount;
END //

DELIMITER ;

-- >>> СКРИНШОТ 5 (продолжение): выполните вызов процедуры и
--                 проверочный SELECT SUM — результаты должны
--                 совпасть. Сделайте скриншот.

CALL report_total_value();

SELECT SUM(calc_final_price(price, discount_percent)) AS total_value
FROM products;

-- >>> ОТВЕТ НА КОНТРОЛЬНЫЙ ВОПРОС 4:
-- Обычный SQL-запрос нужен, когда расчёт сводится к одной
-- агрегатной функции — он проще, быстрее и оптимизируется
-- сервером. Процедура с циклом оправдана, когда логика
-- итеративная и зависит от предыдущих шагов: накопление с
-- проверками, обработка по сложным правилам, вызовы других
-- процедур/функций, запись промежуточных результатов.
-- В нашем случае цикл — учебный пример; на практике ту же сумму
-- быстрее получить одним SELECT SUM(...).


-- ============================================================
-- САМОСТОЯТЕЛЬНАЯ РАБОТА. Вариант A (базовый)
-- >>> СКРИНШОТ 6: сначала выполните создание функции calc_vat,
--                 затем создание процедуры apply_vat_report,
--                 затем вызов процедуры. Сделайте скриншот
--                 итогового результата.
-- ============================================================

DELIMITER //

CREATE FUNCTION calc_vat(p_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_amount * 0.20;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE apply_vat_report()
BEGIN
    SELECT id,
           name,
           price,
           discount_percent,
           calc_final_price(price, discount_percent) AS final_price,
           calc_vat(calc_final_price(price, discount_percent)) AS vat_amount
    FROM products;
END //

DELIMITER ;

CALL apply_vat_report();


-- ============================================================
-- САМОСТОЯТЕЛЬНАЯ РАБОТА. Вариант B (продвинутый)
-- >>> СКРИНШОТ 7: выполните создание процедуры reserve_stock,
--                 затем три тестовых вызова. Сделайте скриншоты.
-- ============================================================

DELIMITER //

CREATE PROCEDURE reserve_stock(
    IN p_product_id INT,
    IN p_qty_to_reserve INT,
    OUT out_success BOOLEAN,
    OUT out_message VARCHAR(255)
)
BEGIN
    DECLARE v_qty INT;
    DECLARE v_name VARCHAR(100);

    SELECT name, quantity INTO v_name, v_qty
    FROM products
    WHERE id = p_product_id;

    IF v_name IS NULL THEN
        SET out_success = FALSE;
        SET out_message = CONCAT('Товар с id=', p_product_id, ' не найден');
    ELSEIF v_qty >= p_qty_to_reserve THEN
        UPDATE products
        SET quantity = quantity - p_qty_to_reserve
        WHERE id = p_product_id;

        SET out_success = TRUE;
        SET out_message = CONCAT('Зарезервировано ', p_qty_to_reserve,
                                 ' ед. товара "', v_name, '"');
    ELSE
        SET out_success = FALSE;
        SET out_message = CONCAT('Недостаточно товара "', v_name,
                                 '": на складе ', v_qty,
                                 ', запрошено ', p_qty_to_reserve);
    END IF;
END //

DELIMITER ;

-- Тест 1: успешный резерв
CALL reserve_stock(1, 3, @ok, @msg);
SELECT @ok AS success, @msg AS message;

-- Тест 2: недостаточно товара
CALL reserve_stock(1, 100, @ok, @msg);
SELECT @ok AS success, @msg AS message;

-- Тест 3: товар не существует
CALL reserve_stock(999, 1, @ok, @msg);
SELECT @ok AS success, @msg AS message;

-- Проверка, что quantity уменьшился после теста 1
SELECT id, name, quantity FROM products WHERE id = 1;


-- ============================================================
-- КОНЕЦ ФАЙЛА
-- ============================================================