-- ===========================================================================
-- Migration: Alter supplier_recipe — add P&L summary field (target_margin)
-- Date: 2026-05-05
-- Context: Recipe Calculator — Tile 7 (P&L summary)
-- ===========================================================================
-- Decyzja projektowa:
--   target_margin to jedyne pole persystowane z Tile 7.
--   Wszystkie pozostałe wartości (koszt składników, koszty pracy, overhead,
--   recommended price) są wyliczane "on read" w RecipeService::computePnl().
--
--   target_margin DECIMAL(5,2) NULL
--     Przechowuje procent marży w zakresie 0.00–98.99 (np. 50.00 = 50%).
--     NULL oznacza "nie ustawione" — UI wyświetla slider w pozycji 0.
--
--   Wzory kalkulacyjne (all per packaging):
--     raw_materials = SUM(qty × price_snapshot × (1 + waste%/100)) × portions_per_packaging
--     labor         = labor_cost_per_unit × portions_per_packaging
--     overhead      = overhead_per_packaging (z Tile 5)
--     total_cost    = raw_materials + labor + overhead
--     recommended_price = total_cost / (1 − margin/100)   [margin < 99%]
--     per_portion_price = recommended_price / portions_per_packaging
--
--   Vidanges (Tile 6) jest prezentowane oddzielnie poza total_cost.
-- ===========================================================================

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'target_margin'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `target_margin` DECIMAL(5,2) NULL DEFAULT NULL
            COMMENT ''Docelowa marza (%) dla kalkulacji recommended price w Tile 7 P&L summary''
            AFTER `vidanges_deposit_per_carton`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
