Pastein: --------------------------------------------------------- -- 1. ПОДГОТОВКА БАЗЫ --------------------------------------------------------- USE shop; GO -- Удаляем стары

Загрузка данных
---------------------------------------------------------
-- 1. ПОДГОТОВКА БАЗЫ
---------------------------------------------------------
USE shop;
GO

-- Удаляем старые триггеры, если они остались
IF OBJECT_ID('trg_orders_insert', 'TR') IS NOT NULL DROP TRIGGER trg_orders_insert;
GO

-- Удаляем все таблицы в правильном порядке
DROP TABLE IF EXISTS purchase_analytics;
DROP TABLE IF EXISTS purchase_list;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS discounts;
DROP TABLE IF EXISTS stock;
DROP TABLE IF EXISTS product_prices;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS buyers;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS payment_methods;
DROP TABLE IF EXISTS order_statuses;
GO

---------------------------------------------------------
-- 2. СОЗДАНИЕ СТРУКТУРЫ
---------------------------------------------------------

-- Справочники
CREATE TABLE order_statuses (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50) NOT NULL UNIQUE);
CREATE TABLE payment_methods (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50) NOT NULL);
CREATE TABLE categories (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100) NOT NULL);

-- Основные таблицы
CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(200) NOT NULL,
    category_id INT NOT NULL REFERENCES categories(id),
    specification NVARCHAR(MAX)
);

CREATE TABLE buyers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name_client NVARCHAR(150) NOT NULL,
    payment_method_id INT REFERENCES payment_methods(id),
    total_spent DECIMAL(18,2) DEFAULT 0
);

-- Исторические таблицы (Цены, Склад, Скидки)
CREATE TABLE product_prices (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    price DECIMAL(18,2) NOT NULL,
    set_date DATETIME DEFAULT GETDATE()
);

CREATE TABLE stock (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    set_date DATETIME DEFAULT GETDATE()
);

CREATE TABLE discounts (
    id INT IDENTITY(1,1) PRIMARY KEY,
    buyer_id INT NOT NULL REFERENCES buyers(id),
    card_number AS (id + 10000) PERSISTED, 
    discount_percent INT DEFAULT 0,
    set_date DATETIME DEFAULT GETDATE()
);

-- Заказы и аналитика
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    buyer_id INT NOT NULL REFERENCES buyers(id),
    order_date DATETIME DEFAULT GETDATE(),
    quantity INT NOT NULL,
    -- Показываем одну цену (уже с учетом скидки)
    price_at_order DECIMAL(18,2), 
    order_price AS (quantity * price_at_order) PERSISTED,
    status_id INT DEFAULT 1 REFERENCES order_statuses(id)
);

CREATE TABLE purchase_list (
    id INT IDENTITY(1,1) PRIMARY KEY,
    buyer_id INT NOT NULL REFERENCES buyers(id),
    order_id INT NOT NULL REFERENCES orders(id),
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    paid_price DECIMAL(18,2) NOT NULL
);

CREATE TABLE purchase_analytics (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    category_id INT NOT NULL REFERENCES categories(id),
    period_month INT NOT NULL,
    period_year INT NOT NULL,
    total_sold INT DEFAULT 0,
    total_revenue DECIMAL(18,2) DEFAULT 0,
    UNIQUE (product_id, period_month, period_year)
);
GO

---------------------------------------------------------
-- 3. ТРИГГЕР (АВТОМАТИКА)
---------------------------------------------------------
CREATE TRIGGER trg_orders_insert ON orders AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. Вычисляем финальную цену (База - Скидка) и обновляем строку в orders
    UPDATE o 
    SET price_at_order = 
        (SELECT TOP 1 price FROM product_prices WHERE product_id = i.product_id ORDER BY set_date DESC, id DESC) 
        * (1.0 - ISNULL((SELECT TOP 1 discount_percent FROM discounts WHERE buyer_id = i.buyer_id ORDER BY set_date DESC, id DESC), 0) / 100.0)
    FROM orders o INNER JOIN inserted i ON o.id = i.id;

    -- 2. Списываем остаток в историю склада
    INSERT INTO stock (product_id, quantity)
    SELECT i.product_id, 
           ISNULL((SELECT TOP 1 s.quantity FROM stock s WHERE s.product_id = i.product_id ORDER BY s.set_date DESC, s.id DESC), 0) - i.quantity
    FROM inserted i;

    -- 3. Добавляем траты в кошелек покупателя
    UPDATE b SET total_spent += o.order_price
    FROM buyers b INNER JOIN inserted i ON b.id = i.buyer_id INNER JOIN orders o ON o.id = i.id;

    -- 4. Заполнение аналитики (MERGE)
    MERGE purchase_analytics AS target
    USING (SELECT i.product_id, p.category_id, MONTH(i.order_date) as m, YEAR(i.order_date) as y, i.quantity as q, o.order_price as r
           FROM inserted i JOIN products p ON p.id = i.product_id JOIN orders o ON o.id = i.id) AS src
    ON target.product_id = src.product_id AND target.period_month = src.m AND target.period_year = src.y
    WHEN MATCHED THEN UPDATE SET total_sold += src.q, total_revenue += src.r
    WHEN NOT MATCHED THEN INSERT (product_id, category_id, period_month, period_year, total_sold, total_revenue) 
    VALUES (src.product_id, src.category_id, src.m, src.y, src.q, src.r);
END;
GO

---------------------------------------------------------
-- 4. МАСШТАБНОЕ ЗАПОЛНЕНИЕ
---------------------------------------------------------

-- Справочники
INSERT INTO order_statuses (name) VALUES (N'Оформлен'), (N'Доставлен'), (N'Отменён');
INSERT INTO payment_methods (name) VALUES (N'Карта'), (N'Наличные'), (N'Крипта'), (N'QR-код');
INSERT INTO categories (name) VALUES (N'Смартфоны'), (N'Ноутбуки'), (N'Бытовая техника'), (N'Аудио'), (N'Гаджеты');

-- Покупатели
INSERT INTO buyers (name_client, payment_method_id) VALUES 
(N'Александр Смирнов', 1), (N'Елена Кузнецова', 1), (N'Игорь Лебедев', 2), 
(N'Анна Попова', 3), (N'Виктор Соколов', 4), (N'Ольга Новикова', 1);

-- Товары с описанием
INSERT INTO products (name, category_id, specification) VALUES 
(N'iPhone 15 Pro', 1, N'Титановый корпус, чип A17 Pro, камера 48Мп, экран 120Гц.'),
(N'Samsung S24 Ultra', 1, N'Snapdragon 8 Gen 3, стилус S Pen, Galaxy AI, камера 200Мп.'),
(N'MacBook Air M3', 2, N'Процессор Apple M3, 13 дюймов, Liquid Retina, 18 часов работы.'),
(N'ASUS ROG Zephyrus', 2, N'Ryzen 9, RTX 4060, экран 14 дюймов 165Гц, игровой.'),
(N'Кофемашина DeLonghi', 3, N'Автоматический капучинатор, система LatteCrema, 13 степеней помола.'),
(N'Пылесос Dyson V15', 3, N'Лазерная подсветка пыли, мощность 240 аэроватт, до 60 мин работы.'),
(N'Sony WH-1000XM5', 4, N'Шумоподавление нового поколения, 30 часов автономности, Hi-Res Audio.'),
(N'AirPods Pro 2', 4, N'Активное шумоподавление, адаптивный звук, кейс MagSafe с динамиком.'),
(N'Apple Watch Ultra 2', 5, N'Титановый корпус 49мм, яркость 3000 нит, до 36 часов работы.'),
(N'Яндекс Станция Макс', 5, N'Звук 65 Вт, экран, поддержка 4K, встроенная Алиса и Zigbee.');

-- Цены
INSERT INTO product_prices (product_id, price) VALUES 
(1, 115000), (2, 128000), (3, 142000), (4, 168000), (5, 48000), (6, 75000), (7, 34000), (8, 22000), (9, 88000), (10, 28000);

-- Склад
INSERT INTO stock (product_id, quantity) VALUES 
(1, 40), (2, 35), (3, 20), (4, 12), (5, 25), (6, 18), (7, 50), (8, 70), (9, 15), (10, 30);

-- Скидки (история)
INSERT INTO discounts (buyer_id, discount_percent, set_date) VALUES 
(1, 10, '2026-05-01'), (1, 15, GETDATE()), -- Смирнов: скидка выросла до 15%
(2, 5, GETDATE()),                      -- Кузнецова 5%
(3, 8, GETDATE()),                      -- Лебедев 8%
(4, 20, GETDATE()),                     -- Попова 20%
(5, 12, GETDATE());                     -- Соколов 12%

---------------------------------------------------------
-- 5. ТЕСТОВЫЕ ЗАКАЗЫ
---------------------------------------------------------
INSERT INTO orders (product_id, buyer_id, quantity) VALUES 
(1, 1, 1), -- Смирнов покупает iPhone (скидка 15%)
(3, 4, 1), -- Попова покупает MacBook (скидка 20%)
(10, 2, 2); -- Кузнецова покупает 2 станции (скидка 5%)
GO
Больше возможностей при регистрации:
