-- ============================================================================
-- WMS PRO - Migracion 003: Gaps de Alta Prioridad
-- Gap 1: Agenda de Recepcion (multi-OC, recepcion ciega, diferencias)
-- Gap 2: Oleadas de Picking con criterios de liberacion y rechazos
-- Gap 3: Transferencia con Etiquetas de Transito
-- Motor: InnoDB | Charset: utf8mb4
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ############################################################################
-- GAP 1: AGENDA DE RECEPCION
-- ############################################################################

-- ============================================================================
-- 1. CREATE reception_agendas: cabecera de agenda/planificacion de recepcion
-- ============================================================================

CREATE TABLE IF NOT EXISTS `reception_agendas` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agenda_number`     VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('DRAFT','OPEN','IN_PROGRESS','CLOSED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    `planned_date`      DATE         NULL,
    `opened_at`         DATETIME     NULL,
    `closed_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_agenda_number` (`agenda_number`),
    KEY `idx_agenda_wh` (`warehouse_id`),
    KEY `idx_agenda_status` (`status`),
    KEY `idx_agenda_date` (`planned_date`),
    CONSTRAINT `fk_agenda_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_agenda_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Agendas de recepcion con soporte multi-OC';

-- ============================================================================
-- 2. CREATE reception_agenda_refs: documentos OC/OD vinculados a una agenda
-- ============================================================================

CREATE TABLE IF NOT EXISTS `reception_agenda_refs` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agenda_id`         INT UNSIGNED NOT NULL,
    `sap_doc_type`      VARCHAR(10)  NOT NULL COMMENT 'Tipo documento SAP: 22=OC, etc.',
    `sap_doc_entry`     INT          NOT NULL COMMENT 'DocEntry en SAP B1',
    `sap_doc_num`       VARCHAR(20)  NULL COMMENT 'DocNum legible',
    `ref_type`          ENUM('OC','OD','RR','ODC') NOT NULL DEFAULT 'OC' COMMENT 'OC=Orden Compra, OD=Orden Devolucion, RR=Remision Recibida, ODC=Orden DC',
    `supplier_code`     VARCHAR(30)  NULL COMMENT 'CardCode del proveedor',
    `supplier_name`     VARCHAR(200) NULL,
    `status`            ENUM('PENDING','PARTIAL','COMPLETED') NOT NULL DEFAULT 'PENDING',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_aref_agenda` (`agenda_id`),
    KEY `idx_aref_sap` (`sap_doc_entry`, `sap_doc_type`),
    KEY `idx_aref_supplier` (`supplier_code`),
    KEY `idx_aref_status` (`status`),
    CONSTRAINT `fk_aref_agenda` FOREIGN KEY (`agenda_id`) REFERENCES `reception_agendas`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Documentos SAP (OC, OD, etc.) vinculados a una agenda de recepcion';

-- ============================================================================
-- 3. CREATE reception_agenda_lines: lineas esperadas (teorico) de cada OC
-- ============================================================================

CREATE TABLE IF NOT EXISTS `reception_agenda_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agenda_id`         INT UNSIGNED NOT NULL,
    `ref_id`            INT UNSIGNED NOT NULL COMMENT 'Referencia al documento OC/OD',
    `line_num`          INT          NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `item_name`         VARCHAR(200) NULL,
    `expected_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad teorica de la OC',
    `received_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad efectivamente recibida',
    `difference_qty`    DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Diferencia: received - expected',
    `batch_number`      VARCHAR(50)  NULL,
    `expiry_date`       DATE         NULL,
    `supplier_lot`      VARCHAR(50)  NULL COMMENT 'Lote del proveedor',
    `sap_line_num`      INT          NULL COMMENT 'LineNum en el documento SAP',
    `status`            ENUM('PENDING','PARTIAL','COMPLETED','OVER','SHORT') 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,
    KEY `idx_aline_agenda` (`agenda_id`),
    KEY `idx_aline_ref` (`ref_id`),
    KEY `idx_aline_item` (`item_id`),
    KEY `idx_aline_status` (`status`),
    CONSTRAINT `fk_aline_agenda` FOREIGN KEY (`agenda_id`) REFERENCES `reception_agendas`(`id`),
    CONSTRAINT `fk_aline_ref` FOREIGN KEY (`ref_id`) REFERENCES `reception_agenda_refs`(`id`),
    CONSTRAINT `fk_aline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lineas esperadas (teorico) por documento dentro de una agenda de recepcion';

-- ============================================================================
-- 4. CREATE reception_scans: escaneos ciegos individuales por operador
-- ============================================================================

CREATE TABLE IF NOT EXISTS `reception_scans` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `agenda_id`         INT UNSIGNED NOT NULL,
    `line_id`           INT UNSIGNED NULL COMMENT 'NULL si es producto inesperado',
    `item_code`         VARCHAR(50)  NOT NULL COMMENT 'Codigo escaneado/ingresado',
    `batch_number`      VARCHAR(50)  NULL,
    `expiry_date`       DATE         NULL,
    `supplier_lot`      VARCHAR(50)  NULL,
    `scanned_qty`       DECIMAL(15,4) NOT NULL,
    `operator_id`       INT UNSIGNED NOT NULL COMMENT 'Operador que escaneo',
    `scanned_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `bin_id`            INT UNSIGNED NULL COMMENT 'Ubicacion destino si ya se asigno',
    `notes`             TEXT         NULL,
    KEY `idx_scan_agenda` (`agenda_id`),
    KEY `idx_scan_line` (`line_id`),
    KEY `idx_scan_item` (`item_code`),
    KEY `idx_scan_operator` (`operator_id`),
    KEY `idx_scan_date` (`scanned_at`),
    CONSTRAINT `fk_scan_agenda` FOREIGN KEY (`agenda_id`) REFERENCES `reception_agendas`(`id`),
    CONSTRAINT `fk_scan_line` FOREIGN KEY (`line_id`) REFERENCES `reception_agenda_lines`(`id`),
    CONSTRAINT `fk_scan_operator` FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_scan_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Escaneos ciegos de recepcion por operador para comparacion contra teorico';

-- ############################################################################
-- GAP 2: PICKING WAVES CON CRITERIOS DE LIBERACION Y RECHAZOS
-- ############################################################################

-- ============================================================================
-- 5. ALTER picking_waves: agregar columnas de criterio de liberacion
-- ============================================================================

ALTER TABLE `picking_waves`
    ADD COLUMN `release_criteria` ENUM('ORDER','CUSTOMER','ROUTE','WAVE') NOT NULL DEFAULT 'ORDER' COMMENT 'Criterio de agrupacion para liberacion' AFTER `wave_type`,
    ADD COLUMN `route_code` VARCHAR(50) NULL COMMENT 'Codigo de ruta logistica' AFTER `release_criteria`,
    ADD COLUMN `customer_code` VARCHAR(50) NULL COMMENT 'CardCode del cliente para agrupacion' AFTER `route_code`;

ALTER TABLE `picking_waves`
    ADD KEY `idx_wave_criteria` (`release_criteria`),
    ADD KEY `idx_wave_route` (`route_code`),
    ADD KEY `idx_wave_customer` (`customer_code`);

-- ============================================================================
-- 6. CREATE picking_rejections: lineas rechazadas por falta de stock
-- ============================================================================

CREATE TABLE IF NOT EXISTS `picking_rejections` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `wave_id`           INT UNSIGNED NULL COMMENT 'Oleada origen (si aplica)',
    `pick_list_id`      INT UNSIGNED NULL COMMENT 'Pick list origen (si aplica)',
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `requested_qty`     DECIMAL(15,4) NOT NULL COMMENT 'Cantidad solicitada',
    `available_qty`     DECIMAL(15,4) NOT NULL COMMENT 'Cantidad disponible al momento del analisis',
    `shortage_qty`      DECIMAL(15,4) NOT NULL COMMENT 'Faltante: requested - available',
    `status`            ENUM('PENDING','RESOLVED','CANCELLED') NOT NULL DEFAULT 'PENDING',
    `resolution`        TEXT         NULL COMMENT 'Descripcion de la resolucion',
    `resolved_by`       INT UNSIGNED NULL,
    `resolved_at`       DATETIME     NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_reject_wave` (`wave_id`),
    KEY `idx_reject_picklist` (`pick_list_id`),
    KEY `idx_reject_item` (`item_id`),
    KEY `idx_reject_status` (`status`),
    CONSTRAINT `fk_reject_wave` FOREIGN KEY (`wave_id`) REFERENCES `picking_waves`(`id`),
    CONSTRAINT `fk_reject_picklist` FOREIGN KEY (`pick_list_id`) REFERENCES `pick_lists`(`id`),
    CONSTRAINT `fk_reject_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_reject_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lineas rechazadas en picking por falta de stock disponible';

-- ============================================================================
-- 7. CREATE manual_preparations: preparaciones manuales libres
-- ============================================================================

CREATE TABLE IF NOT EXISTS `manual_preparations` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `prep_number`       VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `customer_code`     VARCHAR(30)  NULL,
    `customer_name`     VARCHAR(200) NULL,
    `prep_type`         ENUM('FREE','AUCTION','EXPORT','SAMPLE','SUPPLY') NOT NULL DEFAULT 'FREE',
    `status`            ENUM('DRAFT','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    `sap_order_entry`   INT          NULL COMMENT 'DocEntry de pedido SAP si se vincula posteriormente',
    `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_mprep_number` (`prep_number`),
    KEY `idx_mprep_wh` (`warehouse_id`),
    KEY `idx_mprep_status` (`status`),
    KEY `idx_mprep_type` (`prep_type`),
    KEY `idx_mprep_customer` (`customer_code`),
    CONSTRAINT `fk_mprep_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_mprep_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Preparaciones manuales libres sin reserva previa (remate, exportacion, muestra, etc.)';

-- ============================================================================
-- 8. CREATE manual_preparation_lines: lineas de preparacion manual
-- ============================================================================

CREATE TABLE IF NOT EXISTS `manual_preparation_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `preparation_id`    INT UNSIGNED NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `batch_number`      VARCHAR(50)  NULL,
    `from_bin_id`       INT UNSIGNED NOT NULL COMMENT 'Ubicacion origen de donde se toma',
    `picked_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `container_id`      INT UNSIGNED NULL COMMENT 'Contenedor de destino si aplica',
    `picked_by`         INT UNSIGNED NULL COMMENT 'Operador que realizo el pick',
    `picked_at`         DATETIME     NULL,
    `status`            ENUM('PENDING','PICKED') NOT NULL DEFAULT 'PENDING',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_mpline_prep` (`preparation_id`),
    KEY `idx_mpline_item` (`item_id`),
    KEY `idx_mpline_batch` (`batch_id`),
    KEY `idx_mpline_bin` (`from_bin_id`),
    KEY `idx_mpline_status` (`status`),
    CONSTRAINT `fk_mpline_prep` FOREIGN KEY (`preparation_id`) REFERENCES `manual_preparations`(`id`),
    CONSTRAINT `fk_mpline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_mpline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_mpline_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_mpline_container` FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`),
    CONSTRAINT `fk_mpline_picker` FOREIGN KEY (`picked_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lineas de preparacion manual con trazabilidad de operador y ubicacion';

-- ############################################################################
-- GAP 3: TRANSFERENCIA CON ETIQUETAS DE TRANSITO
-- ############################################################################

-- ============================================================================
-- 9. CREATE transfer_labels: etiquetas de transferencia (ubicacion movil)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `transfer_labels` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `label_code`        VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('EMPTY','LOADING','IN_TRANSIT','UNLOADING','COMPLETED','CANCELLED') NOT NULL DEFAULT 'EMPTY',
    `transit_bin_id`    INT UNSIGNED NULL COMMENT 'Bin virtual de transito asignado',
    `origin_description` TEXT        NULL COMMENT 'Descripcion del origen de la carga',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `completed_at`      DATETIME     NULL,
    `created_by`        INT UNSIGNED NULL,
    UNIQUE KEY `uq_tlbl_code` (`label_code`),
    KEY `idx_tlbl_wh` (`warehouse_id`),
    KEY `idx_tlbl_status` (`status`),
    KEY `idx_tlbl_transit_bin` (`transit_bin_id`),
    CONSTRAINT `fk_tlbl_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_tlbl_transit_bin` FOREIGN KEY (`transit_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tlbl_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Etiquetas de transferencia: ubicacion movil para carga/transito/descarga';

-- ============================================================================
-- 10. CREATE transfer_label_lines: items cargados en la etiqueta
-- ============================================================================

CREATE TABLE IF NOT EXISTS `transfer_label_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `label_id`          INT UNSIGNED NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `batch_number`      VARCHAR(50)  NULL,
    `from_bin_id`       INT UNSIGNED NOT NULL COMMENT 'Ubicacion origen',
    `to_bin_id`         INT UNSIGNED NULL COMMENT 'Ubicacion destino (asignado en descarga)',
    `quantity`          DECIMAL(15,4) NOT NULL COMMENT 'Cantidad solicitada',
    `loaded_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `unloaded_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `status`            ENUM('PENDING','LOADED','IN_TRANSIT','UNLOADED') NOT NULL DEFAULT 'PENDING',
    `loaded_at`         DATETIME     NULL,
    `unloaded_at`       DATETIME     NULL,
    `loaded_by`         INT UNSIGNED NULL,
    `unloaded_by`       INT UNSIGNED NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_tline_label` (`label_id`),
    KEY `idx_tline_item` (`item_id`),
    KEY `idx_tline_batch` (`batch_id`),
    KEY `idx_tline_from` (`from_bin_id`),
    KEY `idx_tline_to` (`to_bin_id`),
    KEY `idx_tline_status` (`status`),
    CONSTRAINT `fk_tline_label` FOREIGN KEY (`label_id`) REFERENCES `transfer_labels`(`id`),
    CONSTRAINT `fk_tline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_tline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_tline_from_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tline_to_bin` FOREIGN KEY (`to_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tline_loader` FOREIGN KEY (`loaded_by`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_tline_unloader` FOREIGN KEY (`unloaded_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Items cargados en etiqueta de transferencia con trazabilidad carga/descarga';

-- ############################################################################
-- ALTERACIONES A TABLAS EXISTENTES
-- ############################################################################

-- ============================================================================
-- 11. ALTER stock_movements: agregar tipos TRANSFER_LOAD y TRANSFER_UNLOAD
-- ============================================================================

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

-- ############################################################################
-- SECUENCIAS DE NUMERACION
-- ############################################################################

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

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
('AGENDA', 'AG-',  0, 8),
('MPREP',  'MP-',  0, 8),
('TLBL',   'TL-',  0, 8)
ON DUPLICATE KEY UPDATE `sequence_key` = VALUES(`sequence_key`);

SET FOREIGN_KEY_CHECKS = 1;
