-- =============================================================================
-- XPERT 3PL - Migracion 004
-- LPN (unidades fisicas: caja/pallet/bolsa) para agrupar stock recibido
-- antes del putaway, y secuencia para codigo de barras interno de items
-- que no traen uno propio.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `lpns` (
    `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`         VARCHAR(30)  NOT NULL,
    `company_id`   INT UNSIGNED NOT NULL,
    `warehouse_id` INT UNSIGNED NOT NULL,
    `lpn_type`     ENUM('BOX','PALLET','BAG') NOT NULL DEFAULT 'BOX',
    `status`       ENUM('OPEN','CLOSED','STORED') NOT NULL DEFAULT 'OPEN'
                   COMMENT 'OPEN = admite mas contenido; CLOSED = listo para putaway; STORED = ya ubicado en un bin',
    `bin_id`       INT UNSIGNED NULL COMMENT 'Ubicacion final una vez hecho el putaway (etapa futura)',
    `notes`        VARCHAR(255) NULL,
    `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `closed_at`    DATETIME NULL,
    `created_by`   INT UNSIGNED NULL,
    UNIQUE KEY `uq_lpns_code` (`code`),
    KEY `idx_lpns_company` (`company_id`),
    KEY `idx_lpns_status` (`status`),
    CONSTRAINT `fk_lpns_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_lpns_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_lpns_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Unidad fisica (caja/pallet/bolsa) armada durante clasificacion, previa al putaway';

ALTER TABLE `stock`
    ADD COLUMN `lpn_id` INT UNSIGNED NULL AFTER `bin_id`,
    ADD KEY `idx_stock_lpn` (`lpn_id`),
    ADD CONSTRAINT `fk_stock_lpn` FOREIGN KEY (`lpn_id`) REFERENCES `lpns`(`id`);

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
    ('LPN',          'LPN-', 0, 8),
    ('ITEM_BARCODE', 'INT',  0, 9)
ON DUPLICATE KEY UPDATE `prefix` = VALUES(`prefix`);
