Pastein: --------------------------------------------------------- -- 1. УДАЛЕНИЕ СТАРЫХ ТАБЛИЦ (в правильном порядке) --------------------------------------------------

Загрузка данных
---------------------------------------------------------
-- 1. УДАЛЕНИЕ СТАРЫХ ТАБЛИЦ (в правильном порядке)
---------------------------------------------------------
DROP TABLE IF EXISTS purchase_analytics;
DROP TABLE IF EXISTS awaiting_stock;
DROP TABLE IF EXISTS purchase_list;
DROP TABLE IF EXISTS orders;
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),
    release_date   DATE,
    specification  NVARCHAR(MAX),
    availability   INT DEFAULT 0 CHECK (availability >= 0),
    current_price  DECIMAL(10,2), -- Заполняется автоматически триггером
    release_price  DECIMAL(10,2),
    lastyear_price DECIMAL(10,2)
);

-- История изменения цен
CREATE TABLE product_prices (
    id         INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    price      DECIMAL(10,2) NOT NULL CHECK (price > 0),
    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),
    payment_details   NVARCHAR(255),
    total_spent       DECIMAL(12,2) DEFAULT 0,
    reg_date          DATE 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 CHECK (quantity > 0),
    price_at_order DECIMAL(10,2),
    order_price    AS (quantity * price_at_order) PERSISTED,
    status_id      INT DEFAULT 1 REFERENCES order_statuses(id) -- 1 = Оформлен
);

-- Список купленных товаров (заполняется триггером)
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(10,2) NOT NULL,
    purchase_date DATETIME DEFAULT GETDATE()
);

-- Ожидание поступления (поставки)
CREATE TABLE awaiting_stock (
    id            INT IDENTITY(1,1) PRIMARY KEY,
    product_id    INT NOT NULL REFERENCES products(id),
    expected_date DATE,
    quantity      INT NOT NULL CHECK (quantity > 0),
    note          NVARCHAR(255)
);

-- Аналитика покупок (заполняется триггером)
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 CHECK (period_month BETWEEN 1 AND 12),
    period_year   INT NOT NULL,
    total_sold    INT DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    avg_price     AS (CASE 
                     WHEN total_sold > 0 THEN CAST(total_revenue AS DECIMAL(12,2)) / total_sold
                     ELSE 0 END) PERSISTED,
    UNIQUE (product_id, period_month, period_year)
);
GO

---------------------------------------------------------
-- 3. ТРИГГЕРЫ
---------------------------------------------------------
-- Триггер: Обновление актуальной цены в таблице товаров
CREATE TRIGGER trg_update_product_price
ON product_prices
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
    SET current_price = i.price
    FROM products p
    INNER JOIN (
        SELECT product_id, price,
               ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY set_date DESC, id DESC) as rn
        FROM inserted
    ) i ON p.id = i.product_id AND i.rn = 1;
END;
GO

-- Триггер: Создание заказа (списание склада, начисление денег, аналитика)
CREATE TRIGGER trg_orders_insert
ON orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Фиксируем цену на момент заказа
    UPDATE o
    SET price_at_order = p.current_price
    FROM orders o
    INNER JOIN inserted i ON o.id = i.id
    INNER JOIN products p ON p.id = i.product_id;

    -- Списываем товар со склада
    UPDATE p
    SET availability = availability - i.quantity
    FROM products p
    INNER JOIN inserted i ON p.id = i.product_id;

    -- Запись в историю покупок
    INSERT INTO purchase_list (buyer_id, order_id, product_id, quantity, paid_price, purchase_date)
    SELECT i.buyer_id, i.id, i.product_id, i.quantity, ISNULL(i.quantity * p.current_price, 0), i.order_date
    FROM inserted i
    INNER JOIN products p ON p.id = i.product_id;

    -- Увеличиваем общую сумму трат клиента
    UPDATE b
    SET total_spent = total_spent + ISNULL(i.quantity * p.current_price, 0)
    FROM buyers b
    INNER JOIN inserted i ON b.id = i.buyer_id
    INNER JOIN products p ON p.id = i.product_id;

    -- Обновляем аналитику продаж
    MERGE purchase_analytics AS target
    USING (
        SELECT i.product_id, p.category_id,
               MONTH(i.order_date) AS period_month,
               YEAR(i.order_date)  AS period_year,
               i.quantity AS qty,
               i.quantity * ISNULL(p.current_price, 0) AS revenue
        FROM inserted i
        INNER JOIN products p ON p.id = i.product_id
    ) AS src
    ON target.product_id = src.product_id AND target.period_month = src.period_month AND target.period_year = src.period_year
    WHEN MATCHED THEN
        UPDATE SET total_sold = target.total_sold + src.qty, total_revenue = target.total_revenue + src.revenue
    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.period_month, src.period_year, src.qty, src.revenue);
END;
GO

-- Триггер: Отмена заказа (возврат на склад и перерасчет)
CREATE TRIGGER trg_orders_cancel
ON orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(status_id)
    BEGIN
        -- 3 = ID статуса 'Отменён'
        UPDATE products
        SET availability = availability + d.quantity
        FROM products p
        INNER JOIN inserted i ON p.id = i.product_id
        INNER JOIN deleted  d ON d.id = i.id
        WHERE i.status_id = 3 AND d.status_id != 3;

        UPDATE buyers
        SET total_spent = total_spent - (d.quantity * d.price_at_order)
        FROM buyers b
        INNER JOIN inserted i ON b.id = i.buyer_id
        INNER JOIN deleted  d ON d.id = i.id
        WHERE i.status_id = 3 AND d.status_id != 3;

        UPDATE purchase_analytics
        SET total_sold    = total_sold    - d.quantity,
            total_revenue = total_revenue - (d.quantity * d.price_at_order)
        FROM purchase_analytics pa
        INNER JOIN inserted i ON pa.product_id = i.product_id AND pa.period_month = MONTH(i.order_date) AND pa.period_year = YEAR(i.order_date)
        INNER JOIN deleted d ON d.id = i.id
        WHERE i.status_id = 3 AND d.status_id != 3;
    END
END;
GO

---------------------------------------------------------
-- 4. ПЕРВИЧНОЕ ЗАПОЛНЕНИЕ ДАННЫМИ (БАЗА)
---------------------------------------------------------
INSERT INTO order_statuses (name) VALUES ('Оформлен'), ('Доставлен'), ('Отменён');
INSERT INTO payment_methods (name) VALUES ('Банковская карта'), ('Наличные'), ('PayPal'), ('Криптовалюта');
INSERT INTO categories (name) VALUES ('Смартфоны'), ('Ноутбуки'), ('Аксессуары'), ('Планшеты'), ('Комплектующие');

-- Добавляем товары (пока без актуальной цены)
INSERT INTO products (name, category_id, release_date, specification, availability, release_price, lastyear_price) VALUES
('iPhone 15 Pro',         1, '20230922', 'A17 Pro, 256GB, 48MP',        50,  99999,  95000),
('Samsung Galaxy S24',    1, '20240117', 'Snapdragon 8 Gen 3, 256GB',   40,  84999,  82000),
('MacBook Air M3',        2, '20240308', 'Apple M3, 8GB, 256GB SSD',    20, 124999, 121000),
('ASUS ROG Zephyrus G14', 2, '20240215', 'Ryzen 9, RTX 4060, 16GB',     15, 114999, 111000),
('AirPods Pro 2',         3, '20220923', 'ANC, H2 chip, MagSafe',       60,  24999,  21000),
('iPad Air M2',           4, '20240308', 'Apple M2, 11", 128GB',        25,  69999,  66000),
('RTX 4070 Ti',           5, '20230105', 'Ada Lovelace, 12GB GDDR6X',   10,  79999,  74000),
('USB-C Hub 7-in-1',      3, '20230601', 'HDMI 4K, USB 3.0 x3, SD',   100,   3499,   3100);

-- Устанавливаем цены. Триггер сам перенесет их в таблицу products!
INSERT INTO product_prices (product_id, price, set_date) VALUES
(1, 10,  '20260612'), (2, 15,  '20260613'), (3, 60,  '20260614'),
(4, 90,  '20260615'), (1, 15,  '20260616'), (2, 16,  '20260617'),
(3, 55,  '20260618'), (4, 98,  '20260619'), (1, 100, '20260620'),
(3, 90,  '20260621'), (1, 110, '20260622'),
(5, 19999, '20260623'), (6, 64999, '20260623'), (7, 69999, '20260623'), (8, 2999, '20260623');

-- Добавляем покупателей
INSERT INTO buyers (name_client, payment_method_id, payment_details, reg_date) VALUES
('Иван Петров',        1, '**** **** **** 1234',   '20240110'),
('Мария Сидорова',     1, '**** **** **** 5678',   '20240215'),
('Алексей Козлов',     2,  NULL,                   '20240301'),
('Екатерина Новикова', 3, 'e.novikova@paypal.com', '20240520'),
('Дмитрий Волков',     4, '1A2B3C...wallet',       '20240611');

-- Поставки
INSERT INTO awaiting_stock (product_id, expected_date, quantity, note) VALUES
(7, '20260415', 10, 'Поставка из Европы'),
(3, '20260420',  5, 'Новая партия M3'),
(1, '20260501', 20, 'iPhone 15 Pro — пополнение склада');

-- Первые заказы клиентов (статус 1 ставится автоматически)
INSERT INTO orders (product_id, buyer_id, quantity) VALUES
(1, 1, 1), 
(2, 2, 1);
GO

---------------------------------------------------------
-- 5. ПРИМЕР: КАК РАБОТАТЬ С БАЗОЙ (Добавление нужных таблиц)
---------------------------------------------------------
-- Шаг 1. Поступил новый товар. Добавляем его в базу (ID присвоится автоматически = 9)
INSERT INTO products (name, category_id, release_date, specification, availability) 
VALUES ('Sony PlayStation 5 Pro', 5, '20251101', 'AMD Ryzen Zen 2, 2TB SSD', 10);

-- Шаг 2. Устанавливаем цену на новый товар (ID = 9)
INSERT INTO product_prices (product_id, price, set_date) 
VALUES (9, 75000, GETDATE());

-- Шаг 3. Регистрируется новый покупатель
INSERT INTO buyers (name_client, payment_method_id, reg_date) 
VALUES ('Сергей Иванов', 1, GETDATE());

-- Шаг 4. Покупатель (Сергей, допустим его ID = 6) заказывает этот товар (ID = 9) в количестве 2 шт.
-- Триггер сам спишет их со склада и посчитает сумму.
INSERT INTO orders (product_id, buyer_id, quantity) 
VALUES (9, 6, 2);

-- Шаг 5. Курьер доставил заказ (допустим это заказ ID = 3). Меняем статус на 'Доставлен' (ID статуса = 2).
UPDATE orders SET status_id = 2 WHERE id = 3;

-- Шаг 6. Клиент передумал, заказ отменяем (ID статуса = 3).
-- Триггер вернет 2 приставки обратно на склад и заберет потраченные деньги из профиля клиента.
UPDATE orders SET status_id = 3 WHERE id = 3;
Больше возможностей при регистрации:
