db_solutions.sql - master - lustforlife/StorePhoneCases

db_solutions.sql
704 строки34 KB
Daria Goryachko
добавлны правки
13 июн 2026, 03:32
13 июн 2026, 03:325f657f1
-- ============================================================================
-- SQL-РЕШЕНИЯ ДЛЯ Задания 9. Продвинутый Postgres
-- База данных: store_phone_cases (Магазин чехлов для телефонов)
-- ============================================================================

-- ============================================================================
-- 0. СОЗДАНИЕ ТАБЛИЦ (DDL) И НАЧАЛЬНЫХ ДАННЫХ
-- Схема таблиц соответствует структуре Django-моделей проекта
-- ============================================================================

-- Удаляем таблицы, если они существовали (для чистоты перезапуска скрипта)
DROP TABLE IF EXISTS shop_supplier_audit CASCADE;
DROP TABLE IF EXISTS shop_stocktransaction CASCADE;
DROP TABLE IF EXISTS shop_supplier CASCADE;
DROP TABLE IF EXISTS shop_productphonemodel CASCADE;
DROP TABLE IF EXISTS shop_productvariant CASCADE;
DROP TABLE IF EXISTS shop_product CASCADE;
DROP TABLE IF EXISTS shop_category CASCADE;
DROP TABLE IF EXISTS shop_phonemodel CASCADE;
DROP TABLE IF EXISTS shop_brand CASCADE;
DROP TABLE IF EXISTS shop_user CASCADE;

-- Таблица: Пользователи
CREATE TABLE shop_user (
    id SERIAL PRIMARY KEY
);

-- Таблица: Бренды телефонов

CREATE TABLE shop_brand (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    logo VARCHAR(100)
);

-- Таблица: Модели телефонов
CREATE TABLE shop_phonemodel (
    id SERIAL PRIMARY KEY,
    brand_id INT NOT NULL REFERENCES shop_brand(id) ON DELETE CASCADE,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(120) UNIQUE NOT NULL,
    year SMALLINT,
    CONSTRAINT unique_brand_model UNIQUE (brand_id, name)
);

-- Таблица: Категории чехлов
CREATE TABLE shop_category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    slug VARCHAR(80) UNIQUE NOT NULL,
    sort_order SMALLINT DEFAULT 0
);

-- Таблица: Товары (Чехлы)
CREATE TABLE shop_product (
    id SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES shop_category(id) ON DELETE RESTRICT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    article VARCHAR(50) DEFAULT '',
    base_price NUMERIC(10, 2) NOT NULL,
    description TEXT DEFAULT '',
    stock INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    main_image VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    warehouse_received_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    promotion_ends_at TIMESTAMP WITH TIME ZONE
);

-- Таблица: Варианты товаров (Цветовые решения)
CREATE TABLE shop_productvariant (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES shop_product(id) ON DELETE CASCADE,
    color_name VARCHAR(100) NOT NULL,
    color_hex VARCHAR(7) DEFAULT '',
    price_modifier NUMERIC(8, 2) DEFAULT 0.00,
    stock INT DEFAULT 0,
    sku VARCHAR(50) UNIQUE DEFAULT '',
    image VARCHAR(100)
);

-- Таблица связи: Товар <-> Модель телефона (Совместимость чехлов)
CREATE TABLE shop_productphonemodel (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES shop_product(id) ON DELETE CASCADE,
    phone_model_id INT NOT NULL REFERENCES shop_phonemodel(id) ON DELETE CASCADE,
    CONSTRAINT unique_compatibility UNIQUE (product_id, phone_model_id)
);

-- Таблица: Поставщики
CREATE TABLE shop_supplier (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    website VARCHAR(200) DEFAULT '',
    phone VARCHAR(20) DEFAULT '',
    email VARCHAR(254) DEFAULT '',
    contact_person VARCHAR(100) DEFAULT '',
    address TEXT DEFAULT '',
    contract_file VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    last_delivery_at TIMESTAMP WITH TIME ZONE
);

-- Таблица: Движение по складу (Транзакции)
CREATE TABLE shop_stocktransaction (
    id SERIAL PRIMARY KEY,
    variant_id INT NOT NULL REFERENCES shop_productvariant(id) ON DELETE CASCADE,
    supplier_id INT REFERENCES shop_supplier(id) ON DELETE RESTRICT,
    transaction_type VARCHAR(20) NOT NULL, -- 'in', 'out', 'return', 'write_off', 'inventory'
    quantity INT NOT NULL,
    price_per_unit NUMERIC(10, 2),
    document VARCHAR(100),
    comment TEXT DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by_id INT REFERENCES shop_user(id) ON DELETE SET NULL
);

-- Таблица для аудита (используется далее в триггере Блока 5)
CREATE TABLE shop_supplier_audit (
    id SERIAL PRIMARY KEY,
    supplier_id INT NOT NULL,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    changed_by VARCHAR(100) NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    old_value TEXT,
    new_value TEXT
);


-- ============================================================================
-- ЗАПОЛНЕНИЕ ДЕМО-ДАННЫМИ
-- ============================================================================

INSERT INTO shop_user (id) VALUES (1), (2);

-- Заполняем Бренды
INSERT INTO shop_brand (id, name, slug) VALUES
(1, 'Apple', 'apple'),
(2, 'Samsung', 'samsung'),
(3, 'Xiaomi', 'xiaomi');

-- Заполняем Модели
INSERT INTO shop_phonemodel (id, brand_id, name, slug, year) VALUES
(1, 1, 'iPhone 14 Pro', 'iphone-14-pro', 2022),
(2, 1, 'iPhone 15 Pro', 'iphone-15-pro', 2023),
(3, 2, 'Galaxy S23', 'galaxy-s23', 2023),
(4, 3, 'Redmi Note 12', 'redmi-note-12', 2022);

-- Заполняем Категории
INSERT INTO shop_category (id, name, slug, sort_order) VALUES
(1, 'Силиконовые чехлы', 'silicone-cases', 1),
(2, 'Кожаные чехлы', 'leather-cases', 2),
(3, 'Противоударные чехлы', 'rugged-cases', 3);

-- Заполняем Поставщиков
INSERT INTO shop_supplier (id, name, slug, email, phone, is_active) VALUES
(1, 'Кейс-Опт Трейд', 'case-opt-trade', 'opt@casetrade.ru', '+79991112233', TRUE),
(2, 'Премиум Чехлы Дистрибьюция', 'premium-dist', 'info@premium.ru', '+79998887766', TRUE),
(3, 'Неактивный Поставщик', 'inactive-supp', 'bad@supplier.ru', '+79996665544', FALSE);

-- Заполняем Товары
INSERT INTO shop_product (id, category_id, title, slug, article, base_price, is_active, warehouse_received_at, promotion_ends_at) VALUES
(1, 1, 'Силиконовый чехол MagSafe Clear', 'silicone-magsafe-clear', 'ART-001', 1200.00, TRUE, NOW() - INTERVAL '5 days', NOW() + INTERVAL '10 days'),
(2, 2, 'Кожаный чехол Leather Case Black', 'leather-case-black', 'ART-002', 3500.00, TRUE, NOW() - INTERVAL '40 days', NULL),
(3, 3, 'Противоударный чехол Armor Guard', 'armor-guard', 'ART-003', 1800.00, TRUE, NOW() - INTERVAL '15 days', NOW() - INTERVAL '1 day'), -- Акция закончилась вчера
(4, 1, 'Ультратонкий чехол UltraThin Matte', 'ultrathin-matte', 'ART-004', 800.00, TRUE, NOW() - INTERVAL '100 days', NULL);

-- Заполняем Варианты товаров (Цвета)
INSERT INTO shop_productvariant (id, product_id, color_name, color_hex, price_modifier, stock, sku) VALUES
(1, 1, 'Прозрачный', '#FFFFFF', 0.00, 100, 'SKU-001-CLR'),
(2, 1, 'Дымчатый', '#A9A9A9', 150.00, 30, 'SKU-001-SMK'),
(3, 2, 'Черный уголь', '#000000', 0.00, 15, 'SKU-002-BLK'),
(4, 2, 'Коричневый коньяк', '#8B4513', 200.00, 8, 'SKU-002-BRN'),
(5, 3, 'Милитари', '#4B5320', 0.00, 55, 'SKU-003-MIL'),
(6, 4, 'Матовый черный', '#1A1A1A', 0.00, 60, 'SKU-004-MBLK');

-- Заполняем совместимости
INSERT INTO shop_productphonemodel (product_id, phone_model_id) VALUES
(1, 1), (1, 2),
(2, 2),
(3, 3), (3, 4),
(4, 4);

-- Заполняем Движение по складу (Транзакции)
INSERT INTO shop_stocktransaction (id, variant_id, supplier_id, transaction_type, quantity, price_per_unit, comment) VALUES
(1, 1, 1, 'in', 100, 500.00, 'Первая поставка прозрачных чехлов'),
(2, 3, 2, 'in', 15, 1800.00, 'Поставка кожаных черных чехлов'),
(3, 5, 1, 'in', 55, 900.00, 'Поставка противоударных милитари чехлов');

-- Синхронизируем последовательности (SERIAL) для таблиц после ручной вставки ID
SELECT setval('shop_brand_id_seq', COALESCE((SELECT MAX(id) FROM shop_brand), 1));
SELECT setval('shop_phonemodel_id_seq', COALESCE((SELECT MAX(id) FROM shop_phonemodel), 1));
SELECT setval('shop_category_id_seq', COALESCE((SELECT MAX(id) FROM shop_category), 1));
SELECT setval('shop_supplier_id_seq', COALESCE((SELECT MAX(id) FROM shop_supplier), 1));
SELECT setval('shop_product_id_seq', COALESCE((SELECT MAX(id) FROM shop_product), 1));
SELECT setval('shop_productvariant_id_seq', COALESCE((SELECT MAX(id) FROM shop_productvariant), 1));
SELECT setval('shop_stocktransaction_id_seq', COALESCE((SELECT MAX(id) FROM shop_stocktransaction), 1));


-- ============================================================================
-- БЛОК 1. ФУНКЦИИ И ПРОЦЕДУРЫ (Не менее 4 примеров с разным синтаксисом)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Пример 1: Табличная хранимая функция (RETURNS TABLE)
-- КЕЙС: Выборка вариантов товаров с критически низким запасом (меньше заданного порога).
-- ОБОСНОВАНИЕ: Позволяет менеджеру склада одной простой командой получить список
-- товаров, которые необходимо срочно дозаказать, с понятной структурой и сортировкой.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_low_stock_variants(p_threshold INT)
RETURNS TABLE (
    variant_id INT,
    product_title VARCHAR,
    color_name VARCHAR,
    sku VARCHAR,
    current_stock INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        pv.id,
        p.title,
        pv.color_name,
        pv.sku,
        pv.stock
    FROM shop_productvariant pv
    JOIN shop_product p ON pv.product_id = p.id
    WHERE pv.stock < p_threshold
    ORDER BY pv.stock ASC;
END;
$$ LANGUAGE plpgsql;


-- ----------------------------------------------------------------------------
-- Пример 2: Хранимая процедура с транзакционным контролем (COMMIT / ROLLBACK)
-- КЕЙС: Проведение новой поставки от поставщика.
-- ОБОСНОВАНИЕ: Бизнес-правило требует, чтобы приходные операции совершались только
-- для активных поставщиков. Если поставщик неактивен, транзакция должна быть
-- прервана с ошибкой. Процедуры в PostgreSQL в отличие от функций могут управлять
-- транзакциями, генерировать уведомления или откатывать операции.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE add_delivery_transaction(
    p_variant_id INT,
    p_supplier_id INT,
    p_quantity INT,
    p_price_per_unit NUMERIC,
    p_comment TEXT
) AS $$
DECLARE
    v_supplier_active BOOLEAN;
    v_supplier_name VARCHAR(150);
BEGIN
    -- Проверяем активность поставщика
    SELECT name, is_active INTO v_supplier_name, v_supplier_active 
    FROM shop_supplier 
    WHERE id = p_supplier_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Поставщик с ID % не найден.', p_supplier_id;
    END IF;
    
    -- Бизнес-проверка
    IF NOT v_supplier_active THEN
        RAISE EXCEPTION 'Поставщик "%" (ID %) неактивен. Невозможно провести поставку!', 
            v_supplier_name, p_supplier_id;
    END IF;
    
    -- Вставляем транзакцию
    INSERT INTO shop_stocktransaction (
        variant_id,
        supplier_id,
        transaction_type,
        quantity,
        price_per_unit,
        comment,
        created_at
    ) VALUES (
        p_variant_id,
        p_supplier_id,
        'in',
        p_quantity,
        p_price_per_unit,
        p_comment,
        NOW()
    );
    
    -- Обновляем дату последней поставки у поставщика
    UPDATE shop_supplier
    SET last_delivery_at = NOW()
    WHERE id = p_supplier_id;
    
    -- Обновляем остаток в таблице вариантов
    UPDATE shop_productvariant
    SET stock = stock + p_quantity
    WHERE id = p_variant_id;
    
    RAISE NOTICE 'Поставка от % на % шт. успешно добавлена.', v_supplier_name, p_quantity;
END;
$$ LANGUAGE plpgsql;


-- ----------------------------------------------------------------------------
-- Пример 3: Скалярная функция с условным ветвлением (IF / ELSIF / ELSE)
-- КЕЙС: Вычисление финальной розничной цены конкретного варианта чехла с учетом
-- скидки по активной промо-акции.
-- ОБОСНОВАНИЕ: Финальная цена складывается из базовой стоимости товара, наценки
-- за цвет (price_modifier) и скидки 15%, если акция активна (проверяется дата).
-- Инкапсулирует сложную логику проверки дат на стороне СУБД.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_discounted_price(p_variant_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_base_price NUMERIC(10, 2);
    v_modifier NUMERIC(8, 2);
    v_promo_ends TIMESTAMP WITH TIME ZONE;
    v_final_price NUMERIC(10, 2);
BEGIN
    SELECT 
        p.base_price, 
        pv.price_modifier, 
        p.promotion_ends_at
    INTO 
        v_base_price, 
        v_modifier, 
        v_promo_ends
    FROM shop_productvariant pv
    JOIN shop_product p ON pv.product_id = p.id
    WHERE pv.id = p_variant_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Вариант товара с ID % не найден.', p_variant_id;
    END IF;

    -- Цена с наценкой за цвет
    v_final_price := v_base_price + v_modifier;

    -- Проверяем активность акции
    IF v_promo_ends IS NOT NULL AND v_promo_ends >= NOW() THEN
        -- Применяем скидку 15% на акционный товар
        v_final_price := v_final_price * 0.85;
        RAISE NOTICE 'К товару применена акционная скидка 15%%. Дата окончания акции: %', v_promo_ends;
    ELSIF v_promo_ends IS NOT NULL AND v_promo_ends < NOW() THEN
        RAISE NOTICE 'Акция на данный товар уже завершилась: %', v_promo_ends;
    ELSE
        RAISE NOTICE 'Акция на данный товар не проводится.';
    END IF;

    RETURN ROUND(v_final_price, 2);
END;
$$ LANGUAGE plpgsql;


-- ----------------------------------------------------------------------------
-- Пример 4: Процедура с выходными параметрами (OUT)
-- КЕЙС: Автоматическое архивирование (выключение) лежалых товаров.
-- ОБОСНОВАНИЕ: Отключает товары, которые не поступали на склад дольше заданного
-- количества дней. Позволяет СУБД автоматически рассчитать количество
-- затронутых строк и вернуть его клиенту в качестве OUT-переменной.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE archive_stale_products(
    p_days_stale INT,
    OUT p_archived_count INT
) AS $$
BEGIN
    WITH updated_rows AS (
        UPDATE shop_product
        SET is_active = FALSE
        WHERE is_active = TRUE
          AND warehouse_received_at < (NOW() - (p_days_stale || ' days')::INTERVAL)
        RETURNING id
    )
    SELECT COUNT(*) INTO p_archived_count FROM updated_rows;
    
    RAISE NOTICE 'Деактивировано лежалых товаров: % шт.', p_archived_count;
END;
$$ LANGUAGE plpgsql;


-- ============================================================================
-- БЛОК 2. ЦИКЛЫ (По одному примеру на FOR и WHILE)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Пример 1: Цикл FOR для перебора результирующего набора
-- КЕЙС: Генерация сводного JSON-отчета по брендам и общему остатку их чехлов.
-- ОБОСНОВАНИЕ: Демонстрирует перебор записей из запроса (бренды), подсчет общего
-- остатка совместимых чехлов через циклическую агрегацию и сборку JSON-массива.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION generate_brand_stock_report()
RETURNS JSONB AS $$
DECLARE
    v_brand_record RECORD;
    v_total_stock INT;
    v_report_item JSONB;
    v_final_report JSONB := '[]'::jsonb;
BEGIN
    -- Перебираем все бренды телефонов в цикле FOR
    FOR v_brand_record IN 
        SELECT id, name FROM shop_brand ORDER BY name
    LOOP
        -- Считаем суммарный остаток всех вариантов чехлов, 
        -- совместимых с моделями телефонов текущего бренда
        SELECT COALESCE(SUM(pv.stock), 0)
        INTO v_total_stock
        FROM shop_productvariant pv
        JOIN shop_product p ON pv.product_id = p.id
        JOIN shop_productphonemodel ppm ON ppm.product_id = p.id
        JOIN shop_phonemodel pm ON ppm.phone_model_id = pm.id
        WHERE pm.brand_id = v_brand_record.id;

        -- Формируем элемент JSON для бренда
        v_report_item := jsonb_build_object(
            'brand_id', v_brand_record.id,
            'brand_name', v_brand_record.name,
            'total_stock_available', v_total_stock
        );
        
        -- Объединяем в общий массив
        v_final_report := v_final_report || v_report_item;
    END LOOP;
    
    RETURN v_final_report;
END;
$$ LANGUAGE plpgsql;


-- ----------------------------------------------------------------------------
-- Пример 2: Цикл WHILE с неизвестным числом повторений
-- КЕЙС: Пакетная корректировка цен (снижение наценки на лежалый товар).
-- ОБОСНОВАНИЕ: Избегает блокировок всей таблицы `shop_productvariant` при массовых 
-- апдейтах. Цикл WHILE итерируется и обновляет цены небольшими пакетами (по 2 записи),
-- пока есть записи, удовлетворяющие условию (остаток > 20 и наценка > 0).
-- ----------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE batch_apply_clearance_discount(p_max_runs INT)
AS $$
DECLARE
    v_run_count INT := 0;
    v_updated_rows INT := 1;
BEGIN
    -- Пока счетчик итераций меньше лимита И на прошлом шаге были обновленные записи
    WHILE v_run_count < p_max_runs AND v_updated_rows > 0 LOOP
        -- Выбираем пакет из 2 вариантов с остатком > 20 шт и наценкой > 0
        -- и снижаем наценку на 50 рублей
        WITH target_variants AS (
            SELECT id FROM shop_productvariant
            WHERE stock > 20 AND price_modifier > 0.00
            LIMIT 2
        )
        UPDATE shop_productvariant
        SET price_modifier = GREATEST(0.00, price_modifier - 50.00)
        WHERE id IN (SELECT id FROM target_variants);
        
        -- Получаем количество измененных строк во временной выборке
        GET DIAGNOSTICS v_updated_rows = ROW_COUNT;
        
        v_run_count := v_run_count + 1;
        RAISE NOTICE 'Итерация WHILE-цикла %: обновлено % вариантов товаров.', v_run_count, v_updated_rows;
    END LOOP;
END;
$$ LANGUAGE plpgsql;


-- ============================================================================
-- БЛОК 3. ИНДЕКСЫ (2 примера)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Индекс 1: Индекс по выражению (Function-Based Index)
-- ОБОСНОВАНИЕ: Пользователи часто ищут чехлы без учета регистра букв 
-- (например, `WHERE lower(title) LIKE '%magsafe%'`). Обычный индекс по колонке `title`
-- не будет использоваться СУБД в таких случаях. Данный индекс решает эту проблему.
-- ----------------------------------------------------------------------------
CREATE INDEX idx_shop_product_title_lower ON shop_product (lower(title));


-- ----------------------------------------------------------------------------
-- Индекс 2: Частичный индекс (Partial Index)
-- ОБОСНОВАНИЕ: Нам нужно часто выводить товары с активными промо-акциями на главной.
-- Чтобы не индексировать все товары (которых могут быть тысячи), мы строим индекс
-- только по тем, которые активны и имеют заполненное поле `promotion_ends_at`.
-- Это экономит дисковое пространство и ускоряет вставки в таблицу.
-- ----------------------------------------------------------------------------
CREATE INDEX idx_shop_product_active_promo 
ON shop_product (promotion_ends_at) 
WHERE is_active = TRUE AND promotion_ends_at IS NOT NULL;


-- ============================================================================
-- БЛОК 4. ПРАВА ДОСТУПА И ВРЕМЕННОЕ ПРЕДСТАВЛЕНИЕ
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Создание временного представления (CREATE TEMP VIEW)
-- КЕЙС: Временная витрина товаров для текущей сессии менеджера склада.
-- Позволяет быстро проанализировать общие остатки по категориям в рамках сессии.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE TEMP VIEW temp_active_catalog AS
SELECT 
    p.id, 
    p.title, 
    c.name AS category_name, 
    p.base_price,
    COALESCE(SUM(pv.stock), 0) AS total_stock
FROM shop_product p
JOIN shop_category c ON p.category_id = c.id
LEFT JOIN shop_productvariant pv ON pv.product_id = p.id
WHERE p.is_active = TRUE
GROUP BY p.id, p.title, c.name, p.base_price;


-- ----------------------------------------------------------------------------
-- Права доступа (Access Rights)
-- ОБОСНОВАНИЕ: Демонстрирует разделение прав. Создается роль `store_operator`
-- для контент-менеджера, которому даются права только на работу с транзакциями 
-- и просмотр товаров (без возможности удаления или изменения структуры).
-- ----------------------------------------------------------------------------

-- Демонстрационный блок (создание роли и выдача прав)
-- Заворачиваем в анонимный блок, чтобы избежать ошибки "role already exists"
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'store_operator') THEN
        CREATE ROLE store_operator;
    END IF;
END
$$;

-- Назначаем права доступа к таблицам и витрине данных
GRANT SELECT, INSERT, UPDATE ON shop_stocktransaction TO store_operator;
GRANT SELECT ON shop_product TO store_operator;
GRANT SELECT ON shop_productvariant TO store_operator;
GRANT SELECT ON shop_brand TO store_operator;


-- ============================================================================
-- БЛОК 5. ТРИГГЕРЫ (Минимум 3 примера)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Триггер 1: Валидация остатков (BEFORE INSERT на транзакции)
-- КЕЙС: Проверка наличия достаточного количества чехлов на складе перед продажей.
-- ОБОСНОВАНИЕ: Если транзакция имеет тип расходной (`out` или `write_off`), 
-- СУБД должна проверить, хватает ли остатка. Если нет - возбудить исключение
-- и откатить транзакцию, гарантируя консистентность данных.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tg_check_stock_before_transaction()
RETURNS TRIGGER AS $$
DECLARE
    v_current_stock INT;
BEGIN
    -- Проверяем только расходные операции
    IF NEW.transaction_type IN ('out', 'write_off') THEN
        SELECT stock INTO v_current_stock 
        FROM shop_productvariant 
        WHERE id = NEW.variant_id;
        
        IF v_current_stock IS NULL THEN
            RAISE EXCEPTION 'Вариант товара с ID % не найден на складе.', NEW.variant_id;
        END IF;
        
        -- Если списываем больше, чем есть на остатке
        IF v_current_stock < NEW.quantity THEN
            RAISE EXCEPTION 'Недостаточно товара на складе для варианта ID %. Запрошено: %, Доступно: %', 
                NEW.variant_id, NEW.quantity, v_current_stock;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_check_stock_before_transaction
BEFORE INSERT ON shop_stocktransaction
FOR EACH ROW
EXECUTE FUNCTION tg_check_stock_before_transaction();


-- ----------------------------------------------------------------------------
-- Триггер 2: Валидация цен при изменении наценки (BEFORE INSERT OR UPDATE)
-- КЕЙС: Нельзя установить такую наценку/скидку на цвет (price_modifier), которая
-- приведет к тому, что суммарная цена товара станет отрицательной.
-- ОБОСНОВАНИЕ: Страхует базу данных от ошибок контент-менеджеров, которые могут
-- ошибочно ввести слишком большую скидку в модификаторе цены.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tg_validate_price_modifier()
RETURNS TRIGGER AS $$
DECLARE
    v_base_price NUMERIC(10, 2);
BEGIN
    SELECT base_price INTO v_base_price FROM shop_product WHERE id = NEW.product_id;
    
    IF v_base_price IS NULL THEN
        RAISE EXCEPTION 'Товар с ID % не найден.', NEW.product_id;
    END IF;
    
    -- Проверяем, чтобы итоговая цена не ушла в минус
    IF (v_base_price + NEW.price_modifier) < 0 THEN
        RAISE EXCEPTION 'Недопустимый модификатор цены %. Базовая цена: %, итоговая цена не может быть отрицательной.', 
            NEW.price_modifier, v_base_price;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_validate_price_modifier
BEFORE INSERT OR UPDATE ON shop_productvariant
FOR EACH ROW
EXECUTE FUNCTION tg_validate_price_modifier();


-- ----------------------------------------------------------------------------
-- Триггер 3: Логирование критических изменений (AFTER UPDATE на поставщиках)
-- КЕЙС: Запись истории изменений контактных данных поставщиков в таблицу аудита.
-- ОБОСНОВАНИЕ: Для бизнеса критично отслеживать, кто и когда менял контакты поставщиков.
-- Триггер автоматически фиксирует старое и новое значения в `shop_supplier_audit`.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tg_audit_supplier_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Если изменился телефон
    IF OLD.phone IS DISTINCT FROM NEW.phone THEN
        INSERT INTO shop_supplier_audit(supplier_id, changed_by, field_name, old_value, new_value)
        VALUES (OLD.id, CURRENT_USER, 'phone', OLD.phone, NEW.phone);
    END IF;
    
    -- Если изменился email
    IF OLD.email IS DISTINCT FROM NEW.email THEN
        INSERT INTO shop_supplier_audit(supplier_id, changed_by, field_name, old_value, new_value)
        VALUES (OLD.id, CURRENT_USER, 'email', OLD.email, NEW.email);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_audit_supplier_changes
AFTER UPDATE ON shop_supplier
FOR EACH ROW
EXECUTE FUNCTION tg_audit_supplier_changes();


-- ============================================================================
-- ТЕСТОВЫЕ СЦЕНАРИИ (Проверка работоспособности решений)
-- ============================================================================

-- Анонимный блок для запуска тестов
DO $$
DECLARE
    v_price NUMERIC;
    v_archived INT;
    v_report JSONB;
BEGIN
    RAISE NOTICE '--- НАЧАЛО ТЕСТИРОВАНИЯ ---';

    -- 1. Тест функции get_discounted_price (Блок 1, Пример 3)
    -- Вариант 1 (товар 1 - идет акция, базовая 1200, наценка 0. Финальная со скидкой 15% должна быть 1020)
    v_price := get_discounted_price(1);
    RAISE NOTICE 'Тест 1 (Акционный товар): Итоговая цена варианта 1 = % (ожидается 1020.00)', v_price;

    -- Вариант 3 (товар 2 - акции нет, базовая 3500, наценка 0. Финальная должна быть 3500)
    v_price := get_discounted_price(3);
    RAISE NOTICE 'Тест 1 (Без акции): Итоговая цена варианта 3 = % (ожидается 3500.00)', v_price;

    -- 2. Тест процедуры add_delivery_transaction (Блок 1, Пример 2)
    -- Проводим валидную поставку
    CALL add_delivery_transaction(2, 1, 10, 600.00, 'Тестовая поставка дымчатых чехлов');
    
    -- 3. Тест процедуры archive_stale_products (Блок 1, Пример 4)
    CALL archive_stale_products(90, v_archived);
    RAISE NOTICE 'Тест 3: Архивировано старых продуктов: % шт. (ожидается 1, так как UltraThin Matte поступил 100 дней назад)', v_archived;

    -- 4. Тест цикла FOR (Блок 2, Пример 1)
    v_report := generate_brand_stock_report();
    RAISE NOTICE 'Тест 4: Сгенерированный отчет по остаткам брендов в JSON:';
    RAISE NOTICE '%', v_report;

    -- 5. Тест цикла WHILE (Блок 2, Пример 2)
    -- Снижаем наценку на залежавшийся на складе товар
    CALL batch_apply_clearance_discount(3);

    RAISE NOTICE '--- ТЕСТИРОВАНИЕ ЗАВЕРШЕНО УСПЕШНО ---';
END
$$;

-- 6. Проверка работы Триггера 1 (Блок 5) — Ожидается падение с ошибкой (нехватка товара)

-- INSERT INTO shop_stocktransaction (variant_id, transaction_type, quantity) VALUES (4, 'out', 50);

-- 7. Проверка работы Триггера 2 (Блок 5) — Ожидается ошибка (цена уходит в минус из-за модификатора)

-- UPDATE shop_productvariant SET price_modifier = -2000.00 WHERE id = 1;

-- 8. Проверка работы Временного представления (Блок 4)
SELECT * FROM temp_active_catalog;

-- 9. Проверка работы EXPLAIN для созданных индексов (Блок 3)
SET enable_seqscan = off;
EXPLAIN ANALYZE 
SELECT * FROM shop_product 
WHERE lower(title) = 'прозрачный чехол magsafe clear';
RESET enable_seqscan;