-- =============================================================================
-- XPERT 3PL - Migracion 024
-- Reposicion automatica del sector de Picking: cada item puede tener un bin
-- "hogar" de picking dedicado (pick-face) con niveles min/target. Es opt-in
-- por item -- los que no tienen fila aca siguen funcionando exactamente
-- igual que hoy (chaotic, se pickea de cualquier bin, sin regresion).
-- bins.category_id (migracion 023) queda como ayuda de UI al elegir el bin,
-- no dispara reposicion por si solo.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `item_picking_levels` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`  INT UNSIGNED NOT NULL,
    `item_id`     INT UNSIGNED NOT NULL,
    `bin_id`      INT UNSIGNED NOT NULL COMMENT 'Bin de picking (pick-face) dedicado a este item',
    `min_qty`     DECIMAL(15,4) NOT NULL COMMENT 'Bajo este nivel en el bin se dispara una tarea REPLENISH',
    `target_qty`  DECIMAL(15,4) NOT NULL COMMENT 'Cantidad objetivo a reponer',
    `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by`  INT UNSIGNED NULL,
    UNIQUE KEY `uq_pickinglevels_item` (`item_id`) COMMENT '1 bin hogar por item -- sin caso de uso confirmado para multi-bin todavia',
    KEY `idx_pickinglevels_bin` (`bin_id`),
    KEY `idx_pickinglevels_company` (`company_id`),
    CONSTRAINT `fk_pickinglevels_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_pickinglevels_item`    FOREIGN KEY (`item_id`)    REFERENCES `items`(`id`),
    CONSTRAINT `fk_pickinglevels_bin`     FOREIGN KEY (`bin_id`)     REFERENCES `bins`(`id`),
    CONSTRAINT `fk_pickinglevels_user`    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
    CONSTRAINT `chk_pickinglevels_qty` CHECK (`target_qty` >= `min_qty`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `warehouse_tasks`
    MODIFY COLUMN `task_type` ENUM('PUTAWAY','PICK','MOVE','COUNT','REPLENISH') NOT NULL
        COMMENT 'REPLENISH: mueve stock de STORAGE/BULK al bin hogar de picking de un item (ver item_picking_levels)';
