---

USE trade_company;

-- 1. Список товаров, проданных за определенный период
-- В качестве примера взят период с 2026-06-01 по 2026-06-05
SELECT 
    s.data_prod AS 'Дата продажи', 
    s.k_tov AS 'Код товара', 
    p.n_tov AS 'Наименование товара', 
    s.v_prod AS 'Объем продажи', 
    s.cena_prod AS 'Цена продажи', 
    (s.v_prod * s.cena_prod) AS 'Общая стоимость (v_prod*cena_prod)', 
    m.fio AS 'ФИО менеджера'
FROM sales s
JOIN products p ON s.k_tov = p.k_tov
JOIN managers m ON s.km = m.km
WHERE s.data_prod BETWEEN '2026-06-01' AND '2026-06-05';


-- 2. Список товаров с ценой продажи, превышающей среднюю цену товаров
SELECT 
    p.n_tov AS 'Наименование товара', 
    s.k_tov AS 'Код товара', 
    s.cena_prod AS 'Цена продажи', 
    (SELECT ROUND(AVG(cena_prod), 2) FROM sales) AS 'Средняя цена всех товаров'
FROM sales s
JOIN products p ON s.k_tov = p.k_tov
WHERE s.cena_prod > (SELECT AVG(cena_prod) FROM sales);


-- 3. Список товаров, которые есть в прайсе (номенклатуре), но отсутствуют на складе (kol = 0)
-- Чтобы запрос что-то вывел, сначала искусственно обнулим количество одного из товаров:
UPDATE products SET kol = 0 WHERE k_tov = 5;

-- Сам запрос на выборку отсутствующих товаров:
SELECT 
    k_tov AS 'Код товара', 
    n_tov AS 'Наименование товара', 
    kol AS 'Количество на складе'
FROM products 
WHERE kol = 0;


-- 4. Увеличить цену поставки товара с заданным наименованием на 15%
-- В качестве примера увеличим цену для товара 'Фильтр масляный'
UPDATE deliveries d
JOIN products p ON d.k_tov = p.k_tov
SET d.cena_post = d.cena_post * 1.15
WHERE p.n_tov = 'Фильтр масляный';

-- Проверить результат изменения цены поставки:
SELECT p.n_tov, d.cena_post 
FROM deliveries d 
JOIN products p ON d.k_tov = p.k_tov 
WHERE p.n_tov = 'Фильтр масляный';

USE trade_company;

DROP TRIGGER IF EXISTS after_delivery_insert;

DELIMITER //

CREATE TRIGGER after_delivery_insert
AFTER INSERT ON deliveries
FOR EACH ROW
BEGIN
    -- Проверяем, существует ли уже товар с таким кодом в таблице продуктов
    IF EXISTS (SELECT 1 FROM products WHERE k_tov = NEW.k_tov) THEN
        -- Если товар есть, увеличиваем его количество на складе на величину объема поставки
        UPDATE products 
        SET kol = kol + NEW.v_post 
        WHERE k_tov = NEW.k_tov;
    ELSE
        -- Если товара нет, добавляем новую запись в номенклатуру товаров
        -- (Примечание: сработает, если код товара генерируется вручную или проверка FK временно отключена)
        INSERT INTO products (k_tov, n_tov, kol) 
        VALUES (NEW.k_tov, 'Новый поступивший товар', NEW.v_post);
    END IF;
END //

DELIMITER ;

-- ====================================================================
-- ДЕМОНСТРАЦИЯ РАБОТЫ ТРИГГЕРА (Проверка)
-- ====================================================================
-- 1. Посмотрим текущее количество товара с k_tov = 1 (было 45)
SELECT n_tov, kol FROM products WHERE k_tov = 1;

-- 2. Имитируем новую поставку этого товара объемом 15 штук
INSERT INTO deliveries (k_Post, k_tov, data_past, v_post, cena_post) 
VALUES (1, 1, '2026-06-11', 15, 1200.00);

-- 3. Проверяем склад снова (количество должно автоматически стать 60)
SELECT n_tov, kol FROM products WHERE k_tov = 1;