Pastein: USE shop; GO --------------------------------------------------------- -- 1. ОЧИСТКА (Сносим всё, чтобы ID пошли с единицы) ------------------------------------

Загрузка данных
USE shop;
GO

---------------------------------------------------------
-- 1. ОЧИСТКА (Сносим всё, чтобы ID пошли с единицы)
---------------------------------------------------------
IF OBJECT_ID('trg_orders_insert', 'TR') IS NOT NULL DROP TRIGGER trg_orders_insert;
GO

IF OBJECT_ID('purchase_analytics', 'U') IS NOT NULL DROP TABLE purchase_analytics;
IF OBJECT_ID('purchase_list', 'U') IS NOT NULL DROP TABLE purchase_list;
IF OBJECT_ID('orders', 'U') IS NOT NULL DROP TABLE orders;
IF OBJECT_ID('discounts', 'U') IS NOT NULL DROP TABLE discounts;
IF OBJECT_ID('stock', 'U') IS NOT NULL DROP TABLE stock;
IF OBJECT_ID('product_prices', 'U') IS NOT NULL DROP TABLE product_prices;
IF OBJECT_ID('products', 'U') IS NOT NULL DROP TABLE products;
IF OBJECT_ID('buyers', 'U') IS NOT NULL DROP TABLE buyers;
IF OBJECT_ID('categories', 'U') IS NOT NULL DROP TABLE categories;
IF OBJECT_ID('payment_methods', 'U') IS NOT NULL DROP TABLE payment_methods;
IF OBJECT_ID('order_statuses', 'U') IS NOT NULL DROP TABLE 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 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 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 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,
    purchase_date DATETIME DEFAULT GETDATE()
);

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. Считаем цену со скидкой
    UPDATE o SET price_at_order = (SELECT TOP 1 pp.price FROM product_prices pp WHERE pp.product_id = i.product_id ORDER BY pp.set_date DESC, pp.id DESC) 
           * (1.0 - ISNULL((SELECT TOP 1 d.discount_percent FROM discounts d WHERE d.buyer_id = i.buyer_id ORDER BY d.set_date DESC, d.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. ОБНОВЛЯЕМ ТРАТЫ КЛИЕНТА (Чтобы total_spent не был 0)
    UPDATE b SET total_spent = b.total_spent + (i.quantity * o.price_at_order)
    FROM buyers b INNER JOIN inserted i ON b.id = i.buyer_id INNER JOIN orders o ON o.id = i.id;

    -- 4. Пишем в лист покупок
    INSERT INTO purchase_list (buyer_id, order_id, product_id, quantity, paid_price)
    SELECT i.buyer_id, i.id, i.product_id, i.quantity, o.price_at_order
    FROM inserted i INNER JOIN orders o ON o.id = i.id;

    -- 5. Аналитика
    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, (i.quantity * o.price_at_order) 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 = target.total_sold + src.q, total_revenue = target.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'QR-код'), (N'Крипта');
INSERT INTO categories (name) VALUES 
(N'Смартфоны'), (N'Ноутбуки'), (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, 120Гц экран, камера 48Мп.'),
(N'Samsung S24 Ultra', 1, N'Экран 6.8" Dynamic AMOLED, Snapdragon 8 Gen 3, стилус S Pen.'),
(N'MacBook Air M3', 2, N'Чип Apple M3, 8 ядер CPU, 13 дюймов Retina, пассивное охлаждение.'),
(N'ASUS ROG Zephyrus', 2, N'Ryzen 9, RTX 4060, экран 165Гц, игровая подсветка Aura Sync.'),
(N'Кофемашина DeLonghi', 3, N'Автоматический капучинатор LatteCrema, 13 степеней помола.'),
(N'Робот-пылесос Roborock', 3, N'Лазерная навигация LiDAR, мощность 5500 Па, влажная уборка.'),
(N'Sony WH-1000XM5', 4, N'Лучшее активное шумоподавление, 30 часов работы, поддержка LDAC.'),
(N'AirPods Pro 2', 4, N'Чип H2, адаптивный звук, кейс с поиском через Локатор.'),
(N'Видеокарта RTX 4070 Ti', 5, N'12ГБ GDDR6X памяти, поддержка DLSS 3.0 и трассировки лучей.'),
(N'Блок питания 850W Gold', 5, N'Полностью модульный, сертификат 80 PLUS Gold, японские конденсаторы.'),
(N'Клавиатура Keychron K2', 6, N'Механическая, Brown switches, RGB, беспроводная (Bluetooth).'),
(N'Мышь Logitech G Pro X', 6, N'Беспроводная, суперлегкая (63г), сенсор HERO 25K.'),
(N'Apple Watch Ultra 2', 7, N'Корпус 49мм из титана, яркость 3000 нит, до 36 часов работы.'),
(N'Яндекс Станция Макс', 7, N'Звук 65 Вт, поддержка 4K, встроенная Алиса и Zigbee модуль.');

-- Цены
INSERT INTO product_prices (product_id, price) VALUES 
(1, 110000), (2, 125000), (3, 140000), (4, 160000), (5, 45000), (6, 55000), (7, 35000), (8, 24000), (9, 95000), (10, 15000), (11, 12000), (12, 14000), (13, 85000), (14, 32000);

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

-- Скидки
INSERT INTO discounts (buyer_id, discount_percent) VALUES (1, 15), (2, 20), (3, 5), (4, 10), (5, 0), (6, 25);

-- МНОГО ЗАКАЗОВ (Чтобы забить аналитику и кошельки)
INSERT INTO orders (product_id, buyer_id, quantity) VALUES (1, 1, 1), (3, 2, 1), (14, 3, 2), (11, 4, 1), (7, 5, 1), (5, 6, 1), (9, 1, 1), (13, 2, 1);
GO

---------------------------------------------------------
-- 5. ОТКРЫТИЕ ВСЕХ ТАБЛИЦ
---------------------------------------------------------
SELECT * FROM products;         -- Все товары с описанием
SELECT * FROM buyers;           -- Тут total_spent уже НЕ будет 0!
SELECT * FROM orders;           -- Заказы с одной ценой
SELECT * FROM purchase_list;    -- Лог покупок
SELECT * FROM stock;            -- История склада
SELECT * FROM purchase_analytics; -- Аналитика продаж
Больше возможностей при регистрации:
