Pastein: --------------------------------------------------------- -- 1. СОЗДАНИЕ БАЗЫ ДАННЫХ --------------------------------------------------------- USE master; GO IF EXISTS (SELECT *

Загрузка данных
---------------------------------------------------------
-- 1. СОЗДАНИЕ БАЗЫ ДАННЫХ
---------------------------------------------------------
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'shop3') DROP DATABASE shop3;
GO
CREATE DATABASE shop3;
GO
USE shop3;
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
);

---------------------------------------------------------
-- 3. ИСТОРИЧЕСКИЕ ТАБЛИЦЫ (ЦЕНЫ, СКЛАД, СКИДКИ)
---------------------------------------------------------
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()
);

---------------------------------------------------------
-- 4. ЗАКАЗЫ И АНАЛИТИКА
---------------------------------------------------------
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_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

---------------------------------------------------------
-- 5. «УМНЫЙ» ТРИГГЕР (СКИДКИ + СКЛАД + АНАЛИТИКА)
---------------------------------------------------------
CREATE TRIGGER trg_orders_insert ON orders AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. Установка цены: Последняя цена товара МИНУС Последняя скидка юзера
    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

---------------------------------------------------------
-- 6. МАСШТАБНОЕ ЗАПОЛНЕНИЕ ДАННЫМИ
---------------------------------------------------------

-- Справочники
INSERT INTO order_statuses (name) VALUES (N'Оформлен'), (N'Доставлен'), (N'Отменён');
INSERT INTO payment_methods (name) VALUES (N'Карта'), (N'Наличные'), (N'СБП'), (N'Крипта');
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);

-- Товары с описанием
INSERT INTO products (name, category_id, specification) VALUES 
(N'iPhone 15 Pro', 1, N'Титановый корпус, чип A17 Pro, 120Гц Super Retina XDR экран, камера 48Мп.'),
(N'Samsung S24 Ultra', 1, N'Экран 6.8" Dynamic AMOLED 2X, Snapdragon 8 Gen 3, встроенный стилус S Pen, Galaxy AI.'),
(N'MacBook Air M3', 2, N'Процессор Apple M3, 13 дюймов, Liquid Retina, 18 часов работы, пассивное охлаждение.'),
(N'ASUS ROG Zephyrus G14', 2, N'Игровой ноут: Ryzen 9, RTX 4060, 16ГБ RAM, 1ТБ SSD, экран 14" Nebula 165Гц.'),
(N'Кофемашина DeLonghi', 3, N'Система LatteCrema для капучино, встроенная кофемолка с 13 степенями помола.'),
(N'Робот-пылесос Roborock', 3, N'Сила всасывания 5500 Па, лазерная навигация LiDAR, влажная уборка.'),
(N'Клавиатура Keychron K2', 4, N'Механическая (Brown switches), беспроводная, алюминиевый корпус, RGB подсветка.'),
(N'Мышь Logitech G Pro', 4, N'Беспроводная технология LIGHTSPEED, сенсор HERO 25K, вес всего 63 грамма.'),
(N'Sony WH-1000XM5', 5, N'Флагманские наушники с шумоподавлением, 30 часов работы, поддержка Hi-Res Audio.'),
(N'Apple Watch Series 9', 5, N'Корпус 45мм, новый жест Double Tap, мониторинг кислорода в крови и ЭКГ.');

-- История цен (ставим актуальные цены)
INSERT INTO product_prices (product_id, price) VALUES 
(1, 115000), (2, 125000), (3, 145000), (4, 165000), (5, 45000), (6, 55000), (7, 12000), (8, 14000), (9, 35000), (10, 48000);

-- Начальный склад
INSERT INTO stock (product_id, quantity) VALUES 
(1, 50), (2, 40), (3, 20), (4, 15), (5, 30), (6, 25), (7, 100), (8, 80), (9, 45), (10, 60);

-- История скидок (выдаем карты покупателям)
INSERT INTO discounts (buyer_id, discount_percent, set_date) VALUES 
(1, 5, '2026-05-01'), (1, 15, GETDATE()), -- У Ивана была 5%, стала 15%
(2, 5, GETDATE()),                      -- Мария 5%
(3, 10, GETDATE()),                     -- Алексей 10%
(4, 20, GETDATE()),                     -- Елена 20%
(5, 0, GETDATE());                       -- Дмитрий без скидки

---------------------------------------------------------
-- 7. ТЕСТОВЫЕ ЗАКАЗЫ (Для аналитики)
---------------------------------------------------------
INSERT INTO orders (product_id, buyer_id, quantity) VALUES 
(1, 1, 1), (3, 2, 1), (5, 3, 1), (7, 4, 2), (9, 5, 1), (2, 1, 1);
GO

---------------------------------------------------------
-- 8. КОМАНДЫ ДЛЯ ПРОВЕРКИ
---------------------------------------------------------
-- Заказы: Обычная цена vs Цена с дисконтом
SELECT 
    o.id AS [№],
    b.name_client AS [Покупатель],
    p.name AS [Товар],
    (SELECT TOP 1 price FROM product_prices WHERE product_id = p.id ORDER BY set_date DESC) AS [Базовая цена],
    o.price_at_order AS [Цена со скидкой],
    o.order_price AS [Итого]
FROM orders o
JOIN buyers b ON o.buyer_id = b.id
JOIN products p ON o.product_id = p.id;

-- Проверка аналитики
SELECT * FROM purchase_analytics;

-- Проверка склада (последние остатки)
SELECT p.name, s.quantity, s.set_date FROM products p
OUTER APPLY (SELECT TOP 1 * FROM stock WHERE product_id = p.id ORDER BY set_date DESC, id DESC) s;
Больше возможностей при регистрации:
