-- ===========================================================================
-- Migration: Alter supplier_recipe — add Vidanges (returnable packaging) fields
-- Date: 2026-05-05
-- Context: Recipe Calculator — Tile 6 (Vidanges / recoverable pass-through deposit)
-- ===========================================================================
-- Decyzja projektowa:
--   Vidanges jest 1:1 z recepturą (2 skalary), nie wymaga osobnej tabeli.
--   Wszystkie pola opcjonalne.
--
--   vidanges_carton_type        : wolny opis typu kartonu (np. "Eurobox 60×40 cm")
--   vidanges_deposit_per_carton : kwota depozytu za 1 karton (waluta dostawcy)
--
--   Pola wyliczane (NIE persystowane, zwracane "on read"):
--     deposit_per_packaging = deposit_per_carton / packagings_per_carton  (z Sales unit)
--     deposit_per_portion   = deposit_per_packaging / portions_per_packaging (z Sales unit)
--
--   Charakter księgowy:
--     Vidanges to recoverable pass-through deposit — NIE jest kosztem produktu,
--     NOT included in total_cost w P&L summary.
--     Fakturowany jako osobna linia (oddzielnie od product cost price).
-- ===========================================================================

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'supplier_recipe'
      AND COLUMN_NAME  = 'vidanges_carton_type'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `vidanges_carton_type` VARCHAR(120) NULL DEFAULT NULL
            COMMENT ''Opis typu kartonu dla depozytu zwrotnego (vidanges)''
            AFTER `overhead_recalculated_at`',
    '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  = 'vidanges_deposit_per_carton'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `supplier_recipe`
        ADD COLUMN `vidanges_deposit_per_carton` DECIMAL(12,4) NULL DEFAULT NULL
            COMMENT ''Kwota depozytu za 1 karton (pass-through, nie koszt produktu)''
            AFTER `vidanges_carton_type`',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
