-- ============================================================================
-- WMS PRO - Migracion 004: Gaps de Media Prioridad
-- Gap 4: Inventarios Avanzados (conteos guiados/ciegos, ajuste directo)
-- Gap 5: QC por Interfaz Externa (QC externo, config por producto)
-- Gap 6: Picking entre Zonas (sala de pesadas, carga/descarga)
-- Gap 7: Almacenamiento Mejorado (putaway agrupado/fraccionado, sugerencias)
-- Motor: InnoDB | Charset: utf8mb4
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ############################################################################
-- GAP 4: INVENTARIOS AVANZADOS
-- ############################################################################

-- ============================================================================
-- 1. ALTER cycle_counts: agregar modo conteo, rondas, origen, seleccion
-- ============================================================================

ALTER TABLE `cycle_counts`
    ADD COLUMN `count_mode` ENUM('GUIDED','BLIND') NOT NULL DEFAULT 'BLIND' COMMENT 'Modo guiado muestra item esperado, ciego no' AFTER `count_type`,
    ADD COLUMN `count_rounds` ENUM('SINGLE','TWO_CONSECUTIVE','TWO_ANY') NOT NULL DEFAULT 'SINGLE' COMMENT 'Cantidad y tipo de rondas de conteo' AFTER `count_mode`,
    ADD COLUMN `source` ENUM('PLANNED','OPPORTUNISTIC','DIRECT') NOT NULL DEFAULT 'PLANNED' COMMENT 'Origen del conteo: planificado, oportunista desde picking, o directo' AFTER `count_rounds`,
    ADD COLUMN `current_round` TINYINT NOT NULL DEFAULT 1 COMMENT 'Ronda actual del conteo' AFTER `source`,
    ADD COLUMN `selection_type` ENUM('LOCATION','STOCK_RECORD') NOT NULL DEFAULT 'LOCATION' COMMENT 'Seleccion por ubicacion o por registro de stock' AFTER `current_round`;

-- ============================================================================
-- 2. ALTER cycle_count_lines: agregar ronda, modo, ajuste, segundo conteo
--    Nota: approved_by y approved_at ya existen en 001_wms_schema.sql
-- ============================================================================

ALTER TABLE `cycle_count_lines`
    ADD COLUMN `round_number` TINYINT NOT NULL DEFAULT 1 COMMENT 'Numero de ronda de este conteo' AFTER `status`,
    ADD COLUMN `count_mode` ENUM('GUIDED','BLIND') NULL COMMENT 'Modo heredado o sobreescrito de la cabecera' AFTER `round_number`,
    ADD COLUMN `adjustment_reason` VARCHAR(200) NULL COMMENT 'Motivo del ajuste si se aprueba diferencia' AFTER `count_mode`,
    ADD COLUMN `second_count_qty` DECIMAL(15,4) NULL COMMENT 'Cantidad del segundo conteo (si aplica)' AFTER `adjustment_reason`,
    ADD COLUMN `second_counted_by` INT UNSIGNED NULL COMMENT 'Operador del segundo conteo' AFTER `second_count_qty`,
    ADD COLUMN `second_counted_at` DATETIME NULL COMMENT 'Fecha/hora del segundo conteo' AFTER `second_counted_by`;

ALTER TABLE `cycle_count_lines`
    ADD KEY `idx_countline_round` (`round_number`),
    ADD CONSTRAINT `fk_countline_second_counter` FOREIGN KEY (`second_counted_by`) REFERENCES `users`(`id`);

-- ============================================================================
-- 3. CREATE direct_adjustments: ajustes directos desde handheld sin planificar
-- ============================================================================

CREATE TABLE IF NOT EXISTS `direct_adjustments` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `adjustment_number` VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `bin_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,
    `adjustment_type`   ENUM('CONSUMPTION','PRODUCTION') NOT NULL COMMENT 'Consumo reduce stock, produccion aumenta',
    `quantity`          DECIMAL(15,4) NOT NULL,
    `reason`            VARCHAR(200) NULL COMMENT 'Motivo del ajuste',
    `operator_id`       INT UNSIGNED NOT NULL COMMENT 'Operador que realizo el ajuste',
    `sap_doc_entry`     INT          NULL COMMENT 'DocEntry del ajuste de inventario en SAP',
    `synced_to_sap`     TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_dadj_number` (`adjustment_number`),
    KEY `idx_dadj_wh` (`warehouse_id`),
    KEY `idx_dadj_bin` (`bin_id`),
    KEY `idx_dadj_item` (`item_id`),
    KEY `idx_dadj_batch` (`batch_id`),
    KEY `idx_dadj_type` (`adjustment_type`),
    KEY `idx_dadj_operator` (`operator_id`),
    KEY `idx_dadj_sap` (`sap_doc_entry`),
    KEY `idx_dadj_synced` (`synced_to_sap`),
    CONSTRAINT `fk_dadj_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_dadj_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_dadj_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_dadj_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_dadj_operator` FOREIGN KEY (`operator_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Ajustes directos desde handheld sin planificacion previa (consumo/produccion)';

-- ############################################################################
-- GAP 5: QC POR INTERFAZ EXTERNA
-- ############################################################################

-- ============================================================================
-- 4. CREATE qc_product_config: configuracion de QC automatico por producto
-- ============================================================================

CREATE TABLE IF NOT EXISTS `qc_product_config` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `requires_qc`       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Producto requiere QC en recepcion',
    `qc_blocking`       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Si bloqueante, impide putaway hasta aprobacion',
    `auto_assign`       TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Asignar QC automaticamente en recepcion',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_qcpc_item` (`item_id`),
    KEY `idx_qcpc_code` (`item_code`),
    KEY `idx_qcpc_requires` (`requires_qc`),
    KEY `idx_qcpc_blocking` (`qc_blocking`),
    CONSTRAINT `fk_qcpc_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuracion de QC automatico por producto para recepcion';

-- ============================================================================
-- 5. CREATE qc_external_requests: solicitudes QC desde sistema externo
-- ============================================================================

CREATE TABLE IF NOT EXISTS `qc_external_requests` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `request_type`      ENUM('ASSIGN','APPROVE','REJECT') NOT NULL COMMENT 'Tipo de solicitud QC externa',
    `batch_id`          INT UNSIGNED NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `batch_number`      VARCHAR(50)  NOT NULL,
    `lot_qty`           DECIMAL(15,4) NULL COMMENT 'Cantidad del lote afectado',
    `source_system`     VARCHAR(50)  NOT NULL DEFAULT 'EXTERNAL' COMMENT 'Sistema origen de la solicitud',
    `external_reference` VARCHAR(100) NULL COMMENT 'Referencia en el sistema externo',
    `reason`            TEXT         NULL COMMENT 'Motivo de aprobacion/rechazo',
    `status`            ENUM('PENDING','PROCESSED','ERROR') NOT NULL DEFAULT 'PENDING',
    `processed_at`      DATETIME     NULL,
    `error_message`     TEXT         NULL COMMENT 'Detalle del error si status = ERROR',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_qcer_type` (`request_type`),
    KEY `idx_qcer_batch` (`batch_id`),
    KEY `idx_qcer_item` (`item_id`),
    KEY `idx_qcer_code` (`item_code`),
    KEY `idx_qcer_status` (`status`),
    KEY `idx_qcer_source` (`source_system`),
    KEY `idx_qcer_extref` (`external_reference`),
    CONSTRAINT `fk_qcer_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_qcer_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Solicitudes de QC desde sistema externo de calidad (asignar, aprobar, rechazar)';

-- ############################################################################
-- GAP 6: PICKING ENTRE ZONAS (SALA DE PESADAS)
-- ############################################################################

-- ============================================================================
-- 6. CREATE zone_picks: tareas de picking inter-zona (ej: almacen a pesadas)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `zone_picks` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pick_number`       VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `source_zone_id`    INT UNSIGNED NULL COMMENT 'Zona origen (almacen)',
    `destination_zone_id` INT UNSIGNED NULL COMMENT 'Zona destino (ej: sala de pesadas)',
    `destination_bin_id` INT UNSIGNED NULL COMMENT 'Bin especifico de la sala de pesadas',
    `pick_mode`         ENUM('BY_ORDER_TYPE','BY_PREPARATION') NOT NULL DEFAULT 'BY_ORDER_TYPE' COMMENT 'Modo de carga: por tipo de pedido o por preparacion',
    `order_type_filter` VARCHAR(50)  NULL COMMENT 'Filtro de tipo de pedido (si pick_mode = BY_ORDER_TYPE)',
    `preparation_id`    INT UNSIGNED NULL COMMENT 'ID de preparacion vinculada (si pick_mode = BY_PREPARATION)',
    `status`            ENUM('DRAFT','LOADING','LOADED','UNLOADING','COMPLETED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    `notes`             TEXT         NULL,
    `created_by`        INT UNSIGNED NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_zpck_number` (`pick_number`),
    KEY `idx_zpck_wh` (`warehouse_id`),
    KEY `idx_zpck_src_zone` (`source_zone_id`),
    KEY `idx_zpck_dst_zone` (`destination_zone_id`),
    KEY `idx_zpck_dst_bin` (`destination_bin_id`),
    KEY `idx_zpck_mode` (`pick_mode`),
    KEY `idx_zpck_status` (`status`),
    KEY `idx_zpck_prep` (`preparation_id`),
    CONSTRAINT `fk_zpck_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_zpck_src_zone` FOREIGN KEY (`source_zone_id`) REFERENCES `warehouse_zones`(`id`),
    CONSTRAINT `fk_zpck_dst_zone` FOREIGN KEY (`destination_zone_id`) REFERENCES `warehouse_zones`(`id`),
    CONSTRAINT `fk_zpck_dst_bin` FOREIGN KEY (`destination_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_zpck_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tareas de picking inter-zona (ej: almacen a sala de pesadas)';

-- ============================================================================
-- 7. CREATE zone_pick_lines: lineas de items a recoger para zona destino
-- ============================================================================

CREATE TABLE IF NOT EXISTS `zone_pick_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `zone_pick_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 recoge',
    `requested_qty`     DECIMAL(15,4) NOT NULL,
    `collected_qty`     DECIMAL(15,4) NOT NULL DEFAULT 0,
    `returned_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad devuelta por sobrante',
    `status`            ENUM('PENDING','COLLECTED','DELIVERED','RETURNED','PARTIAL') NOT NULL DEFAULT 'PENDING',
    `collected_by`      INT UNSIGNED NULL COMMENT 'Operador que recogio',
    `collected_at`      DATETIME     NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_zpln_pick` (`zone_pick_id`),
    KEY `idx_zpln_item` (`item_id`),
    KEY `idx_zpln_batch` (`batch_id`),
    KEY `idx_zpln_from` (`from_bin_id`),
    KEY `idx_zpln_status` (`status`),
    KEY `idx_zpln_collector` (`collected_by`),
    CONSTRAINT `fk_zpln_pick` FOREIGN KEY (`zone_pick_id`) REFERENCES `zone_picks`(`id`),
    CONSTRAINT `fk_zpln_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_zpln_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_zpln_from_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_zpln_collector` FOREIGN KEY (`collected_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lineas de picking inter-zona con trazabilidad de recoleccion y devolucion';

-- ############################################################################
-- GAP 7: ALMACENAMIENTO MEJORADO
-- ############################################################################

-- ============================================================================
-- 8. CREATE putaway_suggestions: sugerencias inteligentes de ubicacion
-- ============================================================================

CREATE TABLE IF NOT EXISTS `putaway_suggestions` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_code`         VARCHAR(50)  NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `suggested_bin_id`  INT UNSIGNED NOT NULL COMMENT 'Ubicacion sugerida para almacenar',
    `suggestion_reason` ENUM('SAME_ITEM','SAME_ZONE','FEFO','EMPTY_BIN','NEAREST') NOT NULL DEFAULT 'SAME_ITEM' COMMENT 'Razon de la sugerencia',
    `priority`          INT          NOT NULL DEFAULT 5 COMMENT 'Prioridad de la sugerencia (1=mejor)',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_psug_item` (`item_id`),
    KEY `idx_psug_batch` (`batch_id`),
    KEY `idx_psug_wh` (`warehouse_id`),
    KEY `idx_psug_bin` (`suggested_bin_id`),
    KEY `idx_psug_reason` (`suggestion_reason`),
    KEY `idx_psug_priority` (`priority`),
    CONSTRAINT `fk_psug_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_psug_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_psug_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_psug_bin` FOREIGN KEY (`suggested_bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Sugerencias inteligentes de ubicacion para putaway (mismo item, FEFO, vacio, etc.)';

-- ============================================================================
-- 9. ALTER warehouse_zones: agregar flags de remate/exportacion y prioridad
-- ============================================================================

ALTER TABLE `warehouse_zones`
    ADD COLUMN `is_auction` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Zona para productos de remate' AFTER `is_active`,
    ADD COLUMN `is_export` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Zona para productos de exportacion' AFTER `is_auction`,
    ADD COLUMN `putaway_priority` INT NOT NULL DEFAULT 5 COMMENT 'Prioridad para sugerencia de putaway (1=preferida)' AFTER `is_export`;

ALTER TABLE `warehouse_zones`
    ADD KEY `idx_zone_auction` (`is_auction`),
    ADD KEY `idx_zone_export` (`is_export`),
    ADD KEY `idx_zone_putaway_pri` (`putaway_priority`);

-- ============================================================================
-- 10. ALTER warehouse_tasks: agregar modo putaway y referencia a etiqueta
-- ============================================================================

ALTER TABLE `warehouse_tasks`
    ADD COLUMN `putaway_mode` ENUM('GROUPED','BY_PRODUCT','BY_LOCATION','FREE') NULL COMMENT 'Modo de almacenamiento: agrupado, por producto, por ubicacion, libre' AFTER `notes`,
    ADD COLUMN `label_id` INT UNSIGNED NULL COMMENT 'Referencia a etiqueta de transferencia para putaway agrupado' AFTER `putaway_mode`;

ALTER TABLE `warehouse_tasks`
    ADD KEY `idx_tasks_putaway_mode` (`putaway_mode`),
    ADD KEY `idx_tasks_label` (`label_id`),
    ADD CONSTRAINT `fk_tasks_label` FOREIGN KEY (`label_id`) REFERENCES `transfer_labels`(`id`);

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

-- ============================================================================
-- 11. ALTER stock_movements: agregar tipo ZONE_PICK
-- ============================================================================

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','ZONE_PICK') 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
('DADJ',  'DA-',  0, 8),
('ZPCK',  'ZP-',  0, 8)
ON DUPLICATE KEY UPDATE `sequence_key` = VALUES(`sequence_key`);

SET FOREIGN_KEY_CHECKS = 1;
