Pastein: -- ============================================================================ -- НАЧАЛЬНАЯ СТРУКТУРА БАЗЫ ДАННЫХ (на основе схемы интернет-мага

Загрузка данных
-- ============================================================================
-- НАЧАЛЬНАЯ СТРУКТУРА БАЗЫ ДАННЫХ (на основе схемы интернет-магазина)
-- ============================================================================
DROP DATABASE IF EXISTS online_store;
CREATE DATABASE IF NOT EXISTS online_store;
USE online_store;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    bonus_points INT DEFAULT 0
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('Создан', 'Оплачен', 'Отправлен', 'Доставлен', 'Отменен') DEFAULT 'Создан',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE price_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    change_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE deleted_products_archive (
    id INT PRIMARY KEY AUTO_INCREMENT,
    original_product_id INT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT,
    deleted_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Наполнение тестовыми данными
INSERT INTO customers (name, bonus_points) VALUES
('Иван Петров', 0), ('Мария Сидорова', 150), ('Алексей Смирнов', 0), ('Елена Козлова', 300), ('Дмитрий Иванов', 50);

INSERT INTO products (name, price, stock) VALUES
('Ноутбук Lenovo', 45000.00, 10), ('Мышь Logitech', 1200.00, 50), ('Клавиатура Razer', 5500.00, 15),
('Монитор Samsung', 18900.00, 8), ('Наушники Sony', 8900.00, 20), ('Веб-камера Logitech', 3400.00, 12),
('SSD диск 1TB', 7200.00, 25), ('USB флешка 64GB', 800.00, 100);

INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2025-01-10 14:30:00', 'Доставлен'), (2, '2025-01-12 10:15:00', 'Отправлен'),
(3, '2025-01-15 16:45:00', 'Создан'), (1, '2025-01-18 09:20:00', 'Оплачен'),
(4, '2025-01-20 11:00:00', 'Создан'), (5, '2025-01-22 13:30:00', 'Создан');


-- ============================================================================
-- РЕАЛИЗАЦИЯ ТРИГГЕРОВ ПО ЗАДАНИЮ
-- ============================================================================
DELIMITER $$

-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №1: Автоматическая проверка остатка и уменьшение количества на складе
-- ----------------------------------------------------------------------------
-- Триггер проверяет наличие товара ДО добавления позиции в заказ.
CREATE TRIGGER before_order_items_insert
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 current_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Ошибка оформления заказа: Недостаточно товара на складе!';
    END IF;
END$$

-- Триггер уменьшает склад и пересчитывает сумму ПОСЛЕ успешной проверки и добавления
CREATE TRIGGER after_order_items_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    -- Уменьшаем количество товара на складе
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
    
    -- (ЗАДАНИЕ №3): Автоматический пересчет суммы заказа при добавлении товара
    UPDATE orders
    SET total_amount = (SELECT COALESCE(SUM(quantity * price), 0) FROM order_items WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
END$$


-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №2: Отслеживание и логирование изменения стоимости товара
-- ----------------------------------------------------------------------------
CREATE TRIGGER after_products_update_price
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- Записываем историю только если цена действительно изменилась
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, change_date)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END$$


-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №3: Пересчет итоговой суммы заказа при изменении количества или удалении
-- ----------------------------------------------------------------------------
-- Пересчет при обновлении количества/цены в позиции заказа
CREATE TRIGGER after_order_items_update
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
    -- Обновляем сумму текущего заказа
    UPDATE orders
    SET total_amount = (SELECT COALESCE(SUM(quantity * price), 0) FROM order_items WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
    
    -- На случай, если позиция была ошибочно перенесена в другой заказ, обновляем и старый заказ
    IF OLD.order_id <> NEW.order_id THEN
        UPDATE orders
        SET total_amount = (SELECT COALESCE(SUM(quantity * price), 0) FROM order_items WHERE order_id = OLD.order_id)
        WHERE id = OLD.order_id;
    END IF;
END$$

-- Пересчет при удалении позиции из заказа
CREATE TRIGGER after_order_items_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = (SELECT COALESCE(SUM(quantity * price), 0) FROM order_items WHERE order_id = OLD.order_id)
    WHERE id = OLD.order_id;
END$$


-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №4: Архивирование информации о товаре перед его удалением
-- ----------------------------------------------------------------------------
CREATE TRIGGER before_products_delete_archive
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO deleted_products_archive (original_product_id, name, price, stock, deleted_date)
    VALUES (OLD.id, OLD.name, OLD.price, OLD.stock, NOW());
END$$


-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №5: Контроль последовательности изменения статусов заказа
-- ----------------------------------------------------------------------------
CREATE TRIGGER before_orders_update_status
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    -- Реализуем проверку только при смене статуса менеджером
    IF OLD.status <> NEW.status THEN
        
        -- Правило: Отмена заказа допустима только из статуса «Создан»
        IF NEW.status = 'Отменен' AND OLD.status <> 'Создан' THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Ошибка статуса: Отменить заказ можно только из состояния "Создан"!';
            
        -- Правило: Из «Создан» можно перейти только в «Оплачен» или «Отменен»
        ELSEIF OLD.status = 'Создан' AND NEW.status NOT IN ('Оплачен', 'Отменен') THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Ошибка статуса: Из статуса "Создан" разрешен переход только в "Оплачен" или "Отменен".';
            
        -- Правило: Из «Оплачен» можно перейти только в «Отправлен»
        ELSEIF OLD.status = 'Оплачен' AND NEW.status <> 'Отправлен' THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Ошибка статуса: Оплаченный заказ должен быть переведен в статус "Отправлен".';
            
        -- Правило: Из «Отправлен» можно перейти только в «Доставлен»
        ELSEIF OLD.status = 'Отправлен' AND NEW.status <> 'Доставлен' THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Ошибка статуса: Отправленный заказ может быть изменен только на "Доставлен".';
            
        -- Правило: Из финальных статусов («Доставлен» или «Отменен») переходы запрещены
        ELSEIF OLD.status IN ('Доставлен', 'Отменен') THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Ошибка статуса: Нельзя изменить статус закрытого или отмененного заказа.';
        END IF;
        
    END IF;
END$$


-- ----------------------------------------------------------------------------
-- ЗАДАНИЕ №6: Программа лояльности (начисление бонусов)
-- ----------------------------------------------------------------------------
-- Логично начислять бонусы клиенту в тот момент, когда заказ переходит в статус 'Оплачен'
CREATE TRIGGER after_orders_update_bonuses
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status AND NEW.status = 'Оплачен' AND NEW.total_amount >= 1000 THEN
        UPDATE customers
        SET bonus_points = bonus_points + (FLOOR(NEW.total_amount / 1000) * 50)
        WHERE id = NEW.customer_id;
    END IF;
END$$

DELIMITER ;

-- 1. Проверка Заданий №1 и №3 (Попытка заказать слишком много товара - вызовет ошибку)
-- На складе Ноутбуков Lenovo всего 10 шт. Пытаемся заказать 15 шт:
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (3, 1, 15, 45000.00); 

-- Успешное добавление (Закажем 2 шт. Склад уменьшится с 10 до 8, а total_amount в заказе №3 пересчитается)
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (3, 1, 2, 45000.00);
SELECT * FROM products WHERE id = 1; -- Проверка остатка
SELECT * FROM orders WHERE id = 3;   -- Проверка пересчета суммы (станет 90000.00)

-- 2. Проверка Задания №2 (Изменение цены товара)
UPDATE products SET price = 48000.00 WHERE id = 1;
SELECT * FROM price_history; -- Появится запись о старой цене 45000 и новой 48000

-- 3. Проверка Задания №4 (Удаление товара и проверка архива)
DELETE FROM products WHERE id = 8;
SELECT * FROM deleted_products_archive; -- Появится запись об удаленной USB-флешке

-- 4. Проверка Задания №5 (Нарушение последовательности статусов - вызовет ошибку)
-- Заказ №5 сейчас в статусе 'Создан'. Пытаемся сразу перевести его в 'Доставлен':
UPDATE orders SET status = 'Доставлен' WHERE id = 5; 

-- 5. Проверка Заданий №5 и №6 (Корректный перевод в 'Оплачен' + начисление бонусов)
-- Для заказа №3 сумма равна 90000.00. При оплате клиенту №3 (Алексей Смирнов) должно начислиться: 90 * 50 = 4500 бонусов.
UPDATE orders SET status = 'Оплачен' WHERE id = 3;
SELECT * FROM customers WHERE id = 3; -- Проверяем баланс бонусов Алексея
Больше возможностей при регистрации:
