-- Añade la relación opcional de artículos con un contenedor (y por ende un almacén)
-- Idempotente: solo crea la columna/índice/clave si no existen.
-- Ejecuta:
--   mysql -u mihogar_user -p mihogar_db < sql/migracion_2025_add_contenedor_en_articulos.sql

ALTER TABLE articulos
    ADD COLUMN IF NOT EXISTS contenedor_id INT NULL AFTER categoria_id;

-- Índice (si falta)
SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'articulos'
      AND index_name = 'idx_articulos_contenedor'
);
SET @sql_idx := IF(@idx_exists = 0,
    'CREATE INDEX idx_articulos_contenedor ON articulos(contenedor_id)',
    'SELECT "idx_articulos_contenedor ya existe"');
PREPARE stmt_idx FROM @sql_idx; EXECUTE stmt_idx; DEALLOCATE PREPARE stmt_idx;

-- FK (si falta)
SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE table_schema = DATABASE()
      AND table_name = 'articulos'
      AND constraint_name = 'fk_art_contenedor'
      AND constraint_type = 'FOREIGN KEY'
);
SET @sql_fk := IF(@fk_exists = 0,
    'ALTER TABLE articulos ADD CONSTRAINT fk_art_contenedor FOREIGN KEY (contenedor_id) REFERENCES contenedores(id)',
    'SELECT "fk_art_contenedor ya existe"');
PREPARE stmt_fk FROM @sql_fk; EXECUTE stmt_fk; DEALLOCATE PREPARE stmt_fk;
