-- ===========================================================================
-- Migration: Create supplier_recipe_step
-- Date: 2026-05-13
-- Context: Recipe Steps module — kroki przygotowania receptury dostawcy
-- ===========================================================================
-- Każdy krok należy do jednej receptury dostawcy.
-- Kolejność kroków określona przez sort_order (ASC).
-- Do 3 zdjęć per krok przechowywanych jako klucze R2 (object key).
-- ===========================================================================
CREATE TABLE IF NOT EXISTS `supplier_recipe_step` (
    `id`           INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `recipe_id`    INT UNSIGNED   NOT NULL  COMMENT 'FK → supplier_recipe.id',
    `supplier_id`  INT(11)        NOT NULL  COMMENT 'FK → material_supplier.id — izolacja per dostawca',
    `sort_order`   SMALLINT       NOT NULL DEFAULT 0 COMMENT 'Kolejność kroku (ASC) — unikalna w ramach receptury',
    `description`  TEXT           NOT NULL COMMENT 'Treść kroku — wymagana',
    `photo_1_key`  VARCHAR(512)   NULL     COMMENT 'Klucz R2 zdjęcia 1 (opcjonalne)',
    `photo_2_key`  VARCHAR(512)   NULL     COMMENT 'Klucz R2 zdjęcia 2 (opcjonalne)',
    `photo_3_key`  VARCHAR(512)   NULL     COMMENT 'Klucz R2 zdjęcia 3 (opcjonalne)',
    `created_at`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_srs_recipe`   (`recipe_id`),
    KEY `idx_srs_supplier` (`supplier_id`),
    KEY `idx_srs_sort`     (`recipe_id`, `sort_order`),
    CONSTRAINT `fk_srs_recipe`
        FOREIGN KEY (`recipe_id`) REFERENCES `supplier_recipe` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_srs_supplier`
        FOREIGN KEY (`supplier_id`) REFERENCES `material_supplier` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
