-- ===========================================================================
-- Migration: Alter supplier_recipe — add Overhead fields
-- Date: 2026-05-05
-- Context: Recipe Calculator — Tile 5 (Overhead allocation per portion)
-- ===========================================================================
-- Decyzja projektowa:
--   Overhead jest 1:1 z recepturą (3 skalary), nie wymaga osobnej tabeli.
--   Wszystkie pola opcjonalne — receptura może istnieć bez konfiguracji overhead.
--
--   forecast_cartons_per_month  : prognozowana liczba kartonów / miesiąc (sieć)
--   monthly_overhead_pool       : pula kosztów ogólnych / miesiąc (waluta dostawcy)
--   overhead_recalculated_at    : timestamp ostatniego klikniętego "Recalculate"
--                                 (v1: bez snapshotu wartości; pełne okresy zamknięte
--                                 będą w osobnej iteracji)
--
--   Pola wyliczane (NIE persystowane, zwracane "on read"):
--     overhead_per_portion   = monthly_overhead_pool /
--                              (forecast_cartons_per_month * portions_per_carton)
--     overhead_per_packaging = overhead_per_portion * portions_per_packaging
--     overhead_per_carton    = monthly_overhead_pool / forecast_cartons_per_month
-- ===========================================================================

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'forecast_cartons_per_month'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `forecast_cartons_per_month` INT UNSIGNED NULL DEFAULT NULL
            COMMENT ''Prognoza liczby kartonow / miesiac (siec)''
            AFTER `packagings_per_carton`',
    '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  = 'monthly_overhead_pool'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `monthly_overhead_pool` DECIMAL(12,2) NULL DEFAULT NULL
            COMMENT ''Miesieczna pula kosztow ogolnych (waluta dostawcy)''
            AFTER `forecast_cartons_per_month`',
    '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  = 'overhead_recalculated_at'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `overhead_recalculated_at` DATETIME NULL DEFAULT NULL
            COMMENT ''Timestamp ostatniego Recalculate on closed month (v1: bez snapshotu)''
            AFTER `monthly_overhead_pool`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
