---

USE shop;
GO

---------------------------------------------------------
-- 1. ЖЕСТКАЯ ОЧИСТКА (Сносим всё, что может мешать)
---------------------------------------------------------
-- Сначала удаляем триггеры напрямую
DROP TRIGGER IF EXISTS 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 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_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. Аналитика
    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 += 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'Наличные');
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); -- 15% и 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;