---

---------------------------------------------------------
-- 1. СОЗДАНИЕ НОВОЙ БАЗЫ SHOP3
---------------------------------------------------------
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(15,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(15,2) NOT NULL,
    set_date DATETIME DEFAULT GETDATE()
);

-- ИСТОРИЯ СКЛАДА (PK на ID, а не на товаре!)
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(15,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(15,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'Наличные');
INSERT INTO categories (name) VALUES (N'Смартфоны');

INSERT INTO products (name, category_id, specification) VALUES (N'iPhone 15 Pro', 1, N'Титановый корпус');

-- ЦЕНА: сначала была 1000, потом стала 1200
INSERT INTO product_prices (product_id, price, set_date) VALUES (1, 1000.00, '2026-05-01'), (1, 1200.00, '2026-05-10');

-- СКЛАД: завезли 100 штук
INSERT INTO stock (product_id, quantity, set_date) VALUES (1, 100, '2026-05-01');

-- ПОКУПАТЕЛЬ
INSERT INTO buyers (name_client, payment_method_id) VALUES (N'Иван Петров', 1);

-- СКИДКА: Сначала дали 5%, потом (сегодня) дали 15%. Триггер возьмет 15%!
INSERT INTO discounts (buyer_id, discount_percent, set_date) VALUES 
(1, 5, '2026-05-01'), 
(1, 15, GETDATE()); 

---------------------------------------------------------
-- 7. ТЕСТОВЫЙ ЗАКАЗ
---------------------------------------------------------
INSERT INTO orders (product_id, buyer_id, quantity) VALUES (1, 1, 2);
GO

---------------------------------------------------------
-- 8. ПРОВЕРКА ВСЕГО
---------------------------------------------------------
SELECT * FROM product_prices;  -- Видим историю цен
SELECT * FROM stock;           -- Видим 100 -> 98 (новая строка)
SELECT * FROM discounts;       -- Видим историю скидок Ивана
SELECT * FROM orders;          -- Цена должна быть 1020 (1200 - 15%)
SELECT * FROM purchase_analytics;