-- =============================================================================
-- Floramaar — Patch migration stock / mobile / validation dépôt
-- Version  : 20260625
-- Appliquer : phpMyAdmin → Import → ce fichier (base existante, pas vide)
-- =============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------------------------
-- 1. Colonnes de validation dépôt (Phase 5)
-- -----------------------------------------------------------------------------

-- Ajouter validation_status si absent
SET @col_vs = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'validation_status');
SET @sql_vs = IF(@col_vs = 0,
    "ALTER TABLE deposits ADD COLUMN validation_status ENUM('pending','validated') NOT NULL DEFAULT 'pending' AFTER status",
    'SELECT 1');
PREPARE _s1 FROM @sql_vs; EXECUTE _s1; DEALLOCATE PREPARE _s1;

-- Ajouter validated_by_user_id si absent
SET @col_vb = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'validated_by_user_id');
SET @sql_vb = IF(@col_vb = 0,
    'ALTER TABLE deposits ADD COLUMN validated_by_user_id INT UNSIGNED NULL AFTER validation_status',
    'SELECT 1');
PREPARE _s2 FROM @sql_vb; EXECUTE _s2; DEALLOCATE PREPARE _s2;

-- Ajouter validated_at si absent
SET @col_va = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'validated_at');
SET @sql_va = IF(@col_va = 0,
    'ALTER TABLE deposits ADD COLUMN validated_at DATETIME NULL AFTER validated_by_user_id',
    'SELECT 1');
PREPARE _s3 FROM @sql_va; EXECUTE _s3; DEALLOCATE PREPARE _s3;

-- FK : uniquement si elle n'existe pas encore
SET @fk_exists = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits'
      AND CONSTRAINT_NAME = 'fk_deposits_validated_by');
SET @sql_fk = IF(@fk_exists = 0,
    'ALTER TABLE deposits ADD CONSTRAINT fk_deposits_validated_by FOREIGN KEY (validated_by_user_id) REFERENCES users(id) ON DELETE SET NULL',
    'SELECT 1');
PREPARE _s4 FROM @sql_fk; EXECUTE _s4; DEALLOCATE PREPARE _s4;

-- -----------------------------------------------------------------------------
-- 2. Mouvements de stock initiaux pour les dépôts DEP-INIT (Phase 4)
--    Sans ça, total_deposited = initial_quantity (COALESCE fallback), mais si
--    l'admin fait un restock ultérieur, le total n'inclurait pas la quantité
--    initiale. On ajoute un mouvement 'restock' initial pour chaque item DEP-INIT
--    qui n'en a pas encore.
-- -----------------------------------------------------------------------------

INSERT INTO stock_movements (deposit_item_id, type, quantity, occurred_at, user_id, note)
SELECT
    di.id,
    'restock',
    di.initial_quantity,
    d.created_at,
    1,
    'Migration automatique — stock initial DEP-INIT'
FROM deposit_items di
JOIN deposits d ON d.id = di.deposit_id
WHERE d.is_initial_snapshot = 1
  AND di.initial_quantity > 0
  AND NOT EXISTS (
      SELECT 1 FROM stock_movements sm
      WHERE sm.deposit_item_id = di.id
        AND sm.type = 'restock'
  );

SET FOREIGN_KEY_CHECKS = 1;

-- Fin de migration