---

USE shop;
GO

---------------------------------------------------------
-- 1. ПРАВИЛЬНАЯ ОЧИСТКА ДЛЯ SQL SERVER 2012
---------------------------------------------------------
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
);

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;
    
    -- Вычисляем цену со скидкой
    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;

    -- Новая запись в СКЛАД
    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;

    -- Аналитика
    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'Наличные');
INSERT INTO categories (name) VALUES (N'Смартфоны'), (N'Ноутбуки');

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

INSERT INTO product_prices (product_id, price) VALUES (1, 100000), (2, 140000);
INSERT INTO stock (product_id, quantity) VALUES (1, 50), (2, 20);

INSERT INTO buyers (name_client, payment_method_id) VALUES (N'Иван Смирнов', 1), (N'Анна Попова', 1);
INSERT INTO discounts (buyer_id, discount_percent) VALUES (1, 15), (2, 20);

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

---------------------------------------------------------
-- 5. ПРОВЕРКА РЕЗУЛЬТАТА
---------------------------------------------------------
SELECT 
    o.id AS [Заказ], 
    b.name_client AS [Клиент], 
    p.name 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;