-- ============================================================================
-- WMS PRO - Migración 002: Funcionalidades Faltantes
-- QA Decisions, Labels/LPN, Packing, Despacho, Devoluciones, Oleadas
-- Motor: InnoDB | Charset: utf8mb4
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- 1. ALTER warehouse_zones: agregar tipo DAMAGE
-- ============================================================================

ALTER TABLE `warehouse_zones`
    MODIFY COLUMN `zone_type` ENUM('RECEIVING','STORAGE','PICKING','SHIPPING','QA','STAGING','RETURNS','DAMAGE') NOT NULL DEFAULT 'STORAGE';

-- ============================================================================
-- 2. CREATE qa_decisions: decisiones de control de calidad
-- ============================================================================

CREATE TABLE IF NOT EXISTS `qa_decisions` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id`          INT UNSIGNED NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `document_id`       INT UNSIGNED NULL COMMENT 'Documento de inventario relacionado',
    `decision`          ENUM('APPROVED','REJECTED','CONDITIONAL','PENDING') NOT NULL DEFAULT 'PENDING',
    `reason`            VARCHAR(500) NULL,
    `inspected_qty`     DECIMAL(15,4) NOT NULL DEFAULT 0,
    `approved_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `rejected_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `inspector`         INT UNSIGNED NULL COMMENT 'Usuario que realizó la inspección',
    `decided_at`        DATETIME     NULL,
    `notes`             TEXT         NULL,
    `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,
    KEY `idx_qa_batch` (`batch_id`),
    KEY `idx_qa_item` (`item_id`),
    KEY `idx_qa_warehouse` (`warehouse_id`),
    KEY `idx_qa_decision` (`decision`),
    KEY `idx_qa_document` (`document_id`),
    CONSTRAINT `fk_qa_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_qa_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_qa_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_qa_document` FOREIGN KEY (`document_id`) REFERENCES `inventory_documents`(`id`),
    CONSTRAINT `fk_qa_inspector` FOREIGN KEY (`inspector`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_qa_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Decisiones de control de calidad por lote';

-- ============================================================================
-- 3. ALTER stock: agregar estado REJECTED
-- ============================================================================

ALTER TABLE `stock`
    MODIFY COLUMN `stock_status` ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT','REJECTED') NOT NULL DEFAULT 'AVAILABLE';

-- ============================================================================
-- 4. ALTER stock_movements: agregar tipos PACK y DISPATCH
-- ============================================================================

ALTER TABLE `stock_movements`
    MODIFY COLUMN `movement_type` ENUM('RECEIPT','PUTAWAY','PICK','MOVE','ADJUSTMENT','BLOCK','UNBLOCK','RETURN','SCRAP','COUNT','PACK','DISPATCH') NOT NULL;

-- También actualizar las columnas from_status/to_status para incluir REJECTED
ALTER TABLE `stock_movements`
    MODIFY COLUMN `from_status` ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT','REJECTED') NULL,
    MODIFY COLUMN `to_status`   ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT','REJECTED') NULL;

-- ============================================================================
-- 5. CREATE labels: etiquetas LPN (License Plate Numbers)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `labels` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lpn`               VARCHAR(30)  NOT NULL COMMENT 'License Plate Number único',
    `label_type`        ENUM('PALLET','CASE','ITEM','MIXED','CONTAINER') NOT NULL DEFAULT 'PALLET',
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `bin_id`            INT UNSIGNED NULL,
    `item_id`           INT UNSIGNED NULL COMMENT 'NULL si es mixto',
    `batch_id`          INT UNSIGNED NULL,
    `quantity`          DECIMAL(15,4) NULL,
    `uom`               VARCHAR(20)  NULL DEFAULT 'UN',
    `status`            ENUM('ACTIVE','IN_TRANSIT','CONSUMED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    `parent_lpn_id`     INT UNSIGNED NULL COMMENT 'LPN padre para anidamiento',
    `notes`             TEXT         NULL,
    `printed_at`        DATETIME     NULL,
    `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_labels_lpn` (`lpn`),
    KEY `idx_labels_type` (`label_type`),
    KEY `idx_labels_warehouse` (`warehouse_id`),
    KEY `idx_labels_bin` (`bin_id`),
    KEY `idx_labels_item` (`item_id`),
    KEY `idx_labels_status` (`status`),
    KEY `idx_labels_parent` (`parent_lpn_id`),
    CONSTRAINT `fk_labels_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_labels_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_labels_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_labels_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_labels_parent` FOREIGN KEY (`parent_lpn_id`) REFERENCES `labels`(`id`),
    CONSTRAINT `fk_labels_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Etiquetas LPN (License Plate Numbers) para pallets, cajas e ítems';

-- ============================================================================
-- 6. CREATE containers: contenedores de transporte
-- ============================================================================

CREATE TABLE IF NOT EXISTS `containers` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `container_code`    VARCHAR(30)  NOT NULL,
    `container_type`    ENUM('BOX','PALLET','CRATE','TOTE','BAG','OTHER') NOT NULL DEFAULT 'BOX',
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('OPEN','CLOSED','SHIPPED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `lpn_id`            INT UNSIGNED NULL COMMENT 'LPN asignado al contenedor',
    `weight_kg`         DECIMAL(10,2) NULL,
    `volume_m3`         DECIMAL(10,4) NULL,
    `seal_number`       VARCHAR(50)  NULL COMMENT 'Número de precinto',
    `notes`             TEXT         NULL,
    `closed_at`         DATETIME     NULL,
    `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_containers_code` (`container_code`),
    KEY `idx_containers_warehouse` (`warehouse_id`),
    KEY `idx_containers_status` (`status`),
    KEY `idx_containers_lpn` (`lpn_id`),
    CONSTRAINT `fk_containers_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_containers_lpn` FOREIGN KEY (`lpn_id`) REFERENCES `labels`(`id`),
    CONSTRAINT `fk_containers_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Contenedores de transporte (cajas, pallets, etc.)';

-- ============================================================================
-- 7. CREATE container_lines: líneas de contenedor
-- ============================================================================

CREATE TABLE IF NOT EXISTS `container_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `container_id`      INT UNSIGNED NOT NULL,
    `line_num`          INT          NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `serial_number`     VARCHAR(100) NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `lpn_id`            INT UNSIGNED NULL COMMENT 'LPN del ítem dentro del contenedor',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_container_line` (`container_id`, `line_num`),
    KEY `idx_contline_item` (`item_id`),
    KEY `idx_contline_batch` (`batch_id`),
    CONSTRAINT `fk_contline_container` FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`),
    CONSTRAINT `fk_contline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_contline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_contline_lpn` FOREIGN KEY (`lpn_id`) REFERENCES `labels`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de contenido de contenedores';

-- ============================================================================
-- 8. CREATE packing_stations: estaciones de empaque
-- ============================================================================

CREATE TABLE IF NOT EXISTS `packing_stations` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`              VARCHAR(20)  NOT NULL,
    `name`              VARCHAR(100) NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `zone_id`           INT UNSIGNED NULL,
    `is_active`         TINYINT(1)   NOT NULL DEFAULT 1,
    `current_user_id`   INT UNSIGNED NULL COMMENT 'Operador actualmente asignado',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_packstation_code` (`warehouse_id`, `code`),
    KEY `idx_packstation_zone` (`zone_id`),
    CONSTRAINT `fk_packstation_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_packstation_zone` FOREIGN KEY (`zone_id`) REFERENCES `warehouse_zones`(`id`),
    CONSTRAINT `fk_packstation_user` FOREIGN KEY (`current_user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Estaciones de empaque en el almacén';

-- ============================================================================
-- 9. CREATE packing_orders: órdenes de empaque
-- ============================================================================

CREATE TABLE IF NOT EXISTS `packing_orders` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pack_number`       VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `pick_list_id`      INT UNSIGNED NULL COMMENT 'Pick list origen',
    `station_id`        INT UNSIGNED NULL COMMENT 'Estación de empaque asignada',
    `status`            ENUM('DRAFT','OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `customer_code`     VARCHAR(30)  NULL,
    `customer_name`     VARCHAR(150) NULL,
    `sap_doc_entry`     INT          NULL COMMENT 'DocEntry del pedido de venta SAP',
    `sap_doc_num`       INT          NULL,
    `assigned_to`       INT UNSIGNED NULL,
    `started_at`        DATETIME     NULL,
    `completed_at`      DATETIME     NULL,
    `notes`             TEXT         NULL,
    `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_packorder_number` (`pack_number`),
    KEY `idx_packorder_wh` (`warehouse_id`),
    KEY `idx_packorder_picklist` (`pick_list_id`),
    KEY `idx_packorder_station` (`station_id`),
    KEY `idx_packorder_status` (`status`),
    KEY `idx_packorder_sap` (`sap_doc_entry`),
    CONSTRAINT `fk_packorder_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_packorder_picklist` FOREIGN KEY (`pick_list_id`) REFERENCES `pick_lists`(`id`),
    CONSTRAINT `fk_packorder_station` FOREIGN KEY (`station_id`) REFERENCES `packing_stations`(`id`),
    CONSTRAINT `fk_packorder_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_packorder_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Órdenes de empaque vinculadas a pick lists';

-- ============================================================================
-- 10. CREATE packing_lines: líneas de empaque
-- ============================================================================

CREATE TABLE IF NOT EXISTS `packing_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `packing_order_id`  INT UNSIGNED NOT NULL,
    `line_num`          INT          NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `serial_number`     VARCHAR(100) NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `packed_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `container_id`      INT UNSIGNED NULL COMMENT 'Contenedor donde se empacó',
    `status`            ENUM('PENDING','PACKED','SHORT','CANCELLED') NOT NULL DEFAULT 'PENDING',
    `notes`             TEXT         NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_packline` (`packing_order_id`, `line_num`),
    KEY `idx_packline_item` (`item_id`),
    KEY `idx_packline_batch` (`batch_id`),
    KEY `idx_packline_container` (`container_id`),
    CONSTRAINT `fk_packline_order` FOREIGN KEY (`packing_order_id`) REFERENCES `packing_orders`(`id`),
    CONSTRAINT `fk_packline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_packline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_packline_container` FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de empaque con asignación a contenedor';

-- ============================================================================
-- 11. CREATE dispatches: despachos / entregas
-- ============================================================================

CREATE TABLE IF NOT EXISTS `dispatches` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `dispatch_number`   VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('DRAFT','OPEN','LOADING','DISPATCHED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `carrier`           VARCHAR(100) NULL COMMENT 'Transportista',
    `vehicle_plate`     VARCHAR(20)  NULL COMMENT 'Placa del vehículo',
    `driver_name`       VARCHAR(150) NULL,
    `driver_doc`        VARCHAR(30)  NULL COMMENT 'Documento de identidad del conductor',
    `seal_number`       VARCHAR(50)  NULL COMMENT 'Número de precinto',
    `customer_code`     VARCHAR(30)  NULL,
    `customer_name`     VARCHAR(150) NULL,
    `ship_to_address`   VARCHAR(500) NULL,
    `sap_doc_entry`     INT          NULL COMMENT 'DocEntry de la entrega SAP',
    `sap_doc_num`       INT          NULL,
    `expected_date`     DATE         NULL,
    `dispatched_at`     DATETIME     NULL,
    `notes`             TEXT         NULL,
    `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_dispatch_number` (`dispatch_number`),
    KEY `idx_dispatch_wh` (`warehouse_id`),
    KEY `idx_dispatch_status` (`status`),
    KEY `idx_dispatch_sap` (`sap_doc_entry`),
    KEY `idx_dispatch_customer` (`customer_code`),
    KEY `idx_dispatch_date` (`expected_date`),
    CONSTRAINT `fk_dispatch_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_dispatch_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Despachos de mercadería a clientes';

-- ============================================================================
-- 12. CREATE dispatch_lines: líneas de despacho
-- ============================================================================

CREATE TABLE IF NOT EXISTS `dispatch_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `dispatch_id`       INT UNSIGNED NOT NULL,
    `line_num`          INT          NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `serial_number`     VARCHAR(100) NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `loaded_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `container_id`      INT UNSIGNED NULL COMMENT 'Contenedor asignado',
    `packing_order_id`  INT UNSIGNED NULL COMMENT 'Orden de empaque origen',
    `pick_list_id`      INT UNSIGNED NULL COMMENT 'Pick list origen',
    `status`            ENUM('PENDING','LOADED','SHORT','CANCELLED') NOT NULL DEFAULT 'PENDING',
    `sap_line_num`      INT          NULL,
    `notes`             TEXT         NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_dispatchline` (`dispatch_id`, `line_num`),
    KEY `idx_displine_item` (`item_id`),
    KEY `idx_displine_batch` (`batch_id`),
    KEY `idx_displine_container` (`container_id`),
    KEY `idx_displine_packorder` (`packing_order_id`),
    KEY `idx_displine_picklist` (`pick_list_id`),
    CONSTRAINT `fk_displine_dispatch` FOREIGN KEY (`dispatch_id`) REFERENCES `dispatches`(`id`),
    CONSTRAINT `fk_displine_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_displine_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_displine_container` FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`),
    CONSTRAINT `fk_displine_packorder` FOREIGN KEY (`packing_order_id`) REFERENCES `packing_orders`(`id`),
    CONSTRAINT `fk_displine_picklist` FOREIGN KEY (`pick_list_id`) REFERENCES `pick_lists`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de despacho con trazabilidad a empaque y picking';

-- ============================================================================
-- 13. ALTER inventory_documents: agregar columna return_type
-- ============================================================================

ALTER TABLE `inventory_documents`
    ADD COLUMN `return_type` ENUM('CUSTOMER_RETURN','SUPPLIER_RETURN','INTERNAL_RETURN') NULL COMMENT 'Tipo de devolución (solo para doc_type = RETURN)' AFTER `doc_type`;

-- ============================================================================
-- 14. CREATE picking_waves: oleadas de picking
-- ============================================================================

CREATE TABLE IF NOT EXISTS `picking_waves` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `wave_number`       VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('DRAFT','OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `wave_type`         ENUM('MANUAL','AUTO','PRIORITY','ROUTE','CARRIER') NOT NULL DEFAULT 'MANUAL',
    `priority`          TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '1=máxima, 10=mínima',
    `planned_date`      DATE         NULL,
    `started_at`        DATETIME     NULL,
    `completed_at`      DATETIME     NULL,
    `total_lines`       INT UNSIGNED NOT NULL DEFAULT 0,
    `completed_lines`   INT UNSIGNED NOT NULL DEFAULT 0,
    `notes`             TEXT         NULL,
    `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_wave_number` (`wave_number`),
    KEY `idx_wave_wh` (`warehouse_id`),
    KEY `idx_wave_status` (`status`),
    KEY `idx_wave_date` (`planned_date`),
    CONSTRAINT `fk_wave_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_wave_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Oleadas de picking para agrupar pick lists';

-- ============================================================================
-- 15. CREATE picking_wave_lines: líneas de oleada de picking
-- ============================================================================

CREATE TABLE IF NOT EXISTS `picking_wave_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `wave_id`           INT UNSIGNED NOT NULL,
    `pick_list_id`      INT UNSIGNED NOT NULL,
    `sequence`          INT          NOT NULL DEFAULT 0 COMMENT 'Orden dentro de la oleada',
    `status`            ENUM('PENDING','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_waveline` (`wave_id`, `pick_list_id`),
    KEY `idx_waveline_picklist` (`pick_list_id`),
    CONSTRAINT `fk_waveline_wave` FOREIGN KEY (`wave_id`) REFERENCES `picking_waves`(`id`),
    CONSTRAINT `fk_waveline_picklist` FOREIGN KEY (`pick_list_id`) REFERENCES `pick_lists`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de oleada vinculando pick lists a una oleada';

-- ============================================================================
-- 16. ALTER pick_lists: agregar wave_id
-- ============================================================================

ALTER TABLE `pick_lists`
    ADD COLUMN `wave_id` INT UNSIGNED NULL COMMENT 'Oleada de picking asignada' AFTER `warehouse_id`,
    ADD KEY `idx_picklist_wave` (`wave_id`),
    ADD CONSTRAINT `fk_picklist_wave` FOREIGN KEY (`wave_id`) REFERENCES `picking_waves`(`id`);

-- ============================================================================
-- 17. ALTER batches: agregar supplier_lot_number si no existe supplier_batch
-- Nota: supplier_batch VARCHAR(50) ya existe en la tabla batches,
--       por lo que NO se agrega supplier_lot_number (sería redundante).
-- ============================================================================

-- supplier_batch ya existe en batches (001_wms_schema.sql línea 134).
-- Si se necesitara un campo adicional, descomentar la siguiente línea:
-- ALTER TABLE `batches` ADD COLUMN `supplier_lot_number` VARCHAR(50) NULL COMMENT 'Número de lote del proveedor' AFTER `supplier_batch`;

-- ============================================================================
-- 18. INSERT doc_sequences para nuevos tipos de documentos
-- ============================================================================

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
('LPN',   'LPN-',  0, 8),
('PACK',  'PK-',   0, 8),
('DISP',  'DS-',   0, 8),
('RET',   'RT-',   0, 8),
('WAVE',  'WV-',   0, 8)
ON DUPLICATE KEY UPDATE `sequence_key` = VALUES(`sequence_key`);

SET FOREIGN_KEY_CHECKS = 1;
