-- ===========================================================================
-- Migration: Alter supplier_recipe — add Sales Unit fields
-- Date: 2026-05-05
-- Context: Recipe Calculator — Tile 4 (Sales unit / hierarchy portion → packaging → carton)
-- ===========================================================================
-- Decyzja projektowa:
--   Sales unit jest 1:1 z recepturą (4 skalary), nie wymaga osobnej tabeli.
--   Wszystkie pola opcjonalne — receptura może istnieć bez konfiguracji sales unit.
--
--   portion_size            : rozmiar 1 porcji w jednostce portion_unit (g lub mm)
--   portion_unit            : 'g' (waga) | 'mm' (długość, np. bagietka)
--   portions_per_packaging  : ile porcji znajduje się w 1 jednostce sprzedażowej
--   packagings_per_carton   : ile jednostek sprzedażowych mieści 1 karton
--
--   Pola wyliczane (NIE persystowane, zwracane "on read"):
--     portions_per_carton = portions_per_packaging * packagings_per_carton
--     packaging_size      = portion_size * portions_per_packaging        (w portion_unit)
--     carton_size         = packaging_size * packagings_per_carton       (w portion_unit)
--     packaging_weight_g  = packaging_size  (gdy portion_unit='g'), w przeciwnym razie NULL
--     carton_weight_g     = carton_size     (gdy portion_unit='g'), w przeciwnym razie NULL
--     carton_weight_kg    = carton_weight_g / 1000
-- ===========================================================================

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'portion_size'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `portion_size` DECIMAL(12,3) NULL DEFAULT NULL
            COMMENT ''Rozmiar 1 porcji (w jednostce portion_unit)''
            AFTER `labor_hourly_rate`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'portion_unit'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `portion_unit` ENUM(''g'',''mm'') NULL DEFAULT NULL
            COMMENT ''Jednostka miary porcji: g (waga) lub mm (dlugosc)''
            AFTER `portion_size`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'portions_per_packaging'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `portions_per_packaging` INT UNSIGNED NULL DEFAULT NULL
            COMMENT ''Liczba porcji w 1 jednostce sprzedazowej (packaging)''
            AFTER `portion_unit`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'packagings_per_carton'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `packagings_per_carton` INT UNSIGNED NULL DEFAULT NULL
            COMMENT ''Liczba jednostek sprzedazowych w 1 kartonie (logistyka)''
            AFTER `portions_per_packaging`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
