-- ============================================================================
-- WMS PRO - Schema DDL para MySQL/MariaDB
-- Sistema de Gestión de Almacenes - Rubro Farmacéutico
-- Motor: InnoDB | Charset: utf8mb4
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- 1. USUARIOS Y AUTENTICACIÓN
-- ============================================================================

CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username`      VARCHAR(50)  NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name`     VARCHAR(150) NOT NULL,
    `email`         VARCHAR(150) NULL,
    `role`          ENUM('admin','supervisor','operator','viewer') NOT NULL DEFAULT 'operator',
    `warehouse_id`  INT UNSIGNED NULL COMMENT 'Almacén asignado por defecto',
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `last_login_at` DATETIME     NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_users_username` (`username`),
    KEY `idx_users_role` (`role`),
    KEY `idx_users_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Operadores y usuarios del WMS';

-- ============================================================================
-- 2. ALMACENES (WAREHOUSES)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `warehouses` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`            VARCHAR(20)  NOT NULL COMMENT 'Código SAP del almacén',
    `name`            VARCHAR(100) NOT NULL,
    `address`         VARCHAR(255) NULL,
    `sap_warehouse_code` VARCHAR(20) NULL COMMENT 'WhsCode en SAP B1',
    `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,
    UNIQUE KEY `uq_warehouses_code` (`code`),
    KEY `idx_warehouses_sap` (`sap_warehouse_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Almacenes/depósitos del sistema';

-- ============================================================================
-- 3. ZONAS DE ALMACÉN
-- ============================================================================

CREATE TABLE IF NOT EXISTS `warehouse_zones` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`  INT UNSIGNED NOT NULL,
    `code`          VARCHAR(20)  NOT NULL,
    `name`          VARCHAR(100) NOT NULL,
    `zone_type`     ENUM('RECEIVING','STORAGE','PICKING','SHIPPING','QA','STAGING','RETURNS') NOT NULL DEFAULT 'STORAGE',
    `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,
    UNIQUE KEY `uq_zone_code` (`warehouse_id`, `code`),
    CONSTRAINT `fk_zones_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Zonas lógicas dentro de un almacén';

-- ============================================================================
-- 4. UBICACIONES / BIN LOCATIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `bins` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`  INT UNSIGNED NOT NULL,
    `zone_id`       INT UNSIGNED NULL,
    `code`          VARCHAR(30)  NOT NULL COMMENT 'Ej: A-01-02-03 (pasillo-rack-nivel-posición)',
    `aisle`         VARCHAR(10)  NULL,
    `rack`          VARCHAR(10)  NULL,
    `level`         VARCHAR(10)  NULL,
    `position`      VARCHAR(10)  NULL,
    `bin_type`      ENUM('RECEIVING','STORAGE','PICKING','SHIPPING','QA','STAGING','BULK','RETURNS') NOT NULL DEFAULT 'STORAGE',
    `max_weight_kg` DECIMAL(10,2) NULL,
    `max_volume_m3` DECIMAL(10,4) NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `sap_bin_code`  VARCHAR(50)  NULL COMMENT 'AbsEntry de BinLocation en SAP',
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_bins_code` (`warehouse_id`, `code`),
    KEY `idx_bins_type` (`bin_type`),
    KEY `idx_bins_zone` (`zone_id`),
    CONSTRAINT `fk_bins_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_bins_zone` FOREIGN KEY (`zone_id`) REFERENCES `warehouse_zones`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Ubicaciones físicas (bin locations) dentro del almacén';

-- ============================================================================
-- 5. ARTÍCULOS / ITEMS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `items` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_code`         VARCHAR(50)  NOT NULL COMMENT 'ItemCode SAP B1',
    `item_name`         VARCHAR(255) NOT NULL,
    `barcode`           VARCHAR(100) NULL,
    `item_group`        VARCHAR(100) NULL,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN' COMMENT 'Unidad de medida base',
    `weight_kg`         DECIMAL(10,4) NULL,
    `volume_m3`         DECIMAL(10,6) NULL,
    `requires_lot`      TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Obligatorio para farmacéutica',
    `requires_serial`   TINYINT(1)   NOT NULL DEFAULT 0,
    `requires_expiry`   TINYINT(1)   NOT NULL DEFAULT 1 COMMENT 'Obligatorio para farmacéutica',
    `shelf_life_days`   INT          NULL COMMENT 'Vida útil en días',
    `min_remaining_shelf_life_days` INT NULL COMMENT 'Mínimo días restantes para aceptar recepción',
    `is_active`         TINYINT(1)   NOT NULL DEFAULT 1,
    `sap_item_code`     VARCHAR(50)  NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_items_code` (`item_code`),
    KEY `idx_items_barcode` (`barcode`),
    KEY `idx_items_sap` (`sap_item_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Maestro de artículos sincronizado desde SAP B1';

-- ============================================================================
-- 6. LOTES (BATCHES)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `batches` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_id`       INT UNSIGNED NOT NULL,
    `batch_number`  VARCHAR(50)  NOT NULL,
    `expiry_date`   DATE         NOT NULL,
    `manufacturing_date` DATE    NULL,
    `supplier_batch` VARCHAR(50) NULL COMMENT 'Lote del proveedor',
    `status`        ENUM('AVAILABLE','QA','BLOCKED','EXPIRED','REJECTED') NOT NULL DEFAULT 'QA',
    `notes`         TEXT         NULL,
    `sap_batch_num` VARCHAR(50)  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_batches_item_lot` (`item_id`, `batch_number`),
    KEY `idx_batches_expiry` (`expiry_date`),
    KEY `idx_batches_status` (`status`),
    CONSTRAINT `fk_batches_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_batches_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Lotes por artículo con vencimiento y estado';

-- ============================================================================
-- 7. STOCK (tabla central de inventario)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `stock` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`  INT UNSIGNED NOT NULL,
    `bin_id`        INT UNSIGNED NOT NULL,
    `item_id`       INT UNSIGNED NOT NULL,
    `batch_id`      INT UNSIGNED NULL,
    `serial_number` VARCHAR(100) NULL,
    `stock_status`  ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT') NOT NULL DEFAULT 'AVAILABLE',
    `quantity`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `reserved_qty`  DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Cantidad reservada para picking',
    `uom`           VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_stock_position` (`warehouse_id`, `bin_id`, `item_id`, `batch_id`, `stock_status`, `serial_number`),
    KEY `idx_stock_item` (`item_id`),
    KEY `idx_stock_batch` (`batch_id`),
    KEY `idx_stock_bin` (`bin_id`),
    KEY `idx_stock_status` (`stock_status`),
    KEY `idx_stock_wh_item` (`warehouse_id`, `item_id`),
    CONSTRAINT `fk_stock_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_stock_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_stock_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_stock_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `chk_stock_qty` CHECK (`quantity` >= 0),
    CONSTRAINT `chk_stock_reserved` CHECK (`reserved_qty` >= 0 AND `reserved_qty` <= `quantity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Stock en tiempo real por almacén+bin+item+lote+estado';

-- ============================================================================
-- 8. MOVIMIENTOS DE STOCK (auditoría completa)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `stock_movements` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `movement_type`     ENUM('RECEIPT','PUTAWAY','PICK','MOVE','ADJUSTMENT','BLOCK','UNBLOCK','RETURN','SCRAP','COUNT') NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `serial_number`     VARCHAR(100) NULL,
    `from_bin_id`       INT UNSIGNED NULL,
    `to_bin_id`         INT UNSIGNED NULL,
    `from_status`       ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT') NULL,
    `to_status`         ENUM('AVAILABLE','QA','BLOCKED','DAMAGED','IN_TRANSIT') NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `reference_type`    VARCHAR(50)  NULL COMMENT 'Tipo documento origen: ASN, PICK_LIST, TASK, COUNT, etc.',
    `reference_id`      INT UNSIGNED NULL COMMENT 'ID del documento origen',
    `reason`            VARCHAR(255) NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by`        INT UNSIGNED NULL,
    KEY `idx_movements_wh` (`warehouse_id`),
    KEY `idx_movements_item` (`item_id`),
    KEY `idx_movements_batch` (`batch_id`),
    KEY `idx_movements_type` (`movement_type`),
    KEY `idx_movements_date` (`created_at`),
    KEY `idx_movements_ref` (`reference_type`, `reference_id`),
    CONSTRAINT `fk_movements_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_movements_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_movements_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_movements_from_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_movements_to_bin` FOREIGN KEY (`to_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_movements_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registro inmutable de todos los movimientos de stock';

-- ============================================================================
-- 9. DOCUMENTOS DE INVENTARIO (ASN / Inbound / Outbound)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `inventory_documents` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `doc_type`          ENUM('ASN','GOODS_RECEIPT','GOODS_ISSUE','TRANSFER','RETURN','ADJUSTMENT','COUNT') NOT NULL,
    `doc_number`        VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('DRAFT','OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `sap_doc_entry`     INT          NULL COMMENT 'DocEntry del documento en SAP B1',
    `sap_doc_num`       INT          NULL COMMENT 'DocNum del documento en SAP B1',
    `sap_doc_type`      VARCHAR(30)  NULL COMMENT 'Tipo SAP: PurchaseOrder, DeliveryNote, etc.',
    `business_partner`  VARCHAR(100) NULL COMMENT 'Proveedor o cliente',
    `bp_code`           VARCHAR(30)  NULL COMMENT 'CardCode SAP',
    `expected_date`     DATE         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_invdoc_number` (`doc_type`, `doc_number`),
    KEY `idx_invdoc_wh` (`warehouse_id`),
    KEY `idx_invdoc_status` (`status`),
    KEY `idx_invdoc_sap` (`sap_doc_entry`),
    KEY `idx_invdoc_bp` (`bp_code`),
    CONSTRAINT `fk_invdoc_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_invdoc_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Documentos de inventario: ASN, recepciones, despachos, ajustes';

-- ============================================================================
-- 10. LÍNEAS DE DOCUMENTOS DE INVENTARIO
-- ============================================================================

CREATE TABLE IF NOT EXISTS `inventory_document_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `document_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,
    `expected_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `received_qty`      DECIMAL(15,4) NOT NULL DEFAULT 0,
    `picked_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `bin_id`            INT UNSIGNED NULL COMMENT 'Bin destino/origen según tipo documento',
    `batch_number`      VARCHAR(50)  NULL COMMENT 'Número de lote ingresado en recepción',
    `expiry_date`       DATE         NULL,
    `status`            ENUM('PENDING','PARTIAL','COMPLETED','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_invdocline` (`document_id`, `line_num`),
    KEY `idx_invdocline_item` (`item_id`),
    KEY `idx_invdocline_batch` (`batch_id`),
    CONSTRAINT `fk_invdocline_doc` FOREIGN KEY (`document_id`) REFERENCES `inventory_documents`(`id`),
    CONSTRAINT `fk_invdocline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_invdocline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_invdocline_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de documentos de inventario';

-- ============================================================================
-- 11. TAREAS DE ALMACÉN (WAREHOUSE TASKS)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `warehouse_tasks` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `task_type`         ENUM('PUTAWAY','PICK','MOVE','COUNT','QA_CHECK','REPLENISH') NOT NULL,
    `priority`          TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '1=máxima, 10=mínima',
    `status`            ENUM('PENDING','ASSIGNED','IN_PROGRESS','COMPLETED','CANCELLED','FAILED') NOT NULL DEFAULT 'PENDING',
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `serial_number`     VARCHAR(100) NULL,
    `from_bin_id`       INT UNSIGNED NULL,
    `to_bin_id`         INT UNSIGNED NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `confirmed_qty`     DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `assigned_to`       INT UNSIGNED NULL COMMENT 'Operador asignado',
    `reference_type`    VARCHAR(50)  NULL,
    `reference_id`      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,
    KEY `idx_tasks_wh` (`warehouse_id`),
    KEY `idx_tasks_type` (`task_type`),
    KEY `idx_tasks_status` (`status`),
    KEY `idx_tasks_assigned` (`assigned_to`),
    KEY `idx_tasks_priority` (`priority`, `status`),
    KEY `idx_tasks_ref` (`reference_type`, `reference_id`),
    CONSTRAINT `fk_tasks_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_tasks_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_tasks_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_tasks_from_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tasks_to_bin` FOREIGN KEY (`to_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tasks_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_tasks_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tareas de almacén: putaway, picking, movimiento, conteo';

-- ============================================================================
-- 12. PICK LISTS (listas de picking desde pedidos SAP)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `pick_lists` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pick_number`       VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `status`            ENUM('DRAFT','OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `sap_doc_entry`     INT          NULL COMMENT 'DocEntry pedido de venta SAP',
    `sap_doc_num`       INT          NULL,
    `customer_code`     VARCHAR(30)  NULL,
    `customer_name`     VARCHAR(150) NULL,
    `ship_date`         DATE         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_picklist_number` (`pick_number`),
    KEY `idx_picklist_wh` (`warehouse_id`),
    KEY `idx_picklist_status` (`status`),
    KEY `idx_picklist_sap` (`sap_doc_entry`),
    CONSTRAINT `fk_picklist_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_picklist_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_picklist_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Listas de picking generadas desde pedidos SAP';

-- ============================================================================
-- 13. LÍNEAS DE PICK LIST
-- ============================================================================

CREATE TABLE IF NOT EXISTS `pick_list_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `pick_list_id`      INT UNSIGNED NOT NULL,
    `line_num`          INT          NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL COMMENT 'Lote asignado por FEFO',
    `from_bin_id`       INT UNSIGNED NULL COMMENT 'Bin origen sugerido',
    `requested_qty`     DECIMAL(15,4) NOT NULL,
    `picked_qty`        DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`               VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `status`            ENUM('PENDING','IN_PROGRESS','PICKED','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_pickline` (`pick_list_id`, `line_num`),
    KEY `idx_pickline_item` (`item_id`),
    KEY `idx_pickline_batch` (`batch_id`),
    CONSTRAINT `fk_pickline_picklist` FOREIGN KEY (`pick_list_id`) REFERENCES `pick_lists`(`id`),
    CONSTRAINT `fk_pickline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_pickline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_pickline_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de picking con asignación FEFO';

-- ============================================================================
-- 14. BLOQUEOS DE STOCK / QA LOCKS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `stock_blocks` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `block_type`        ENUM('QA_HOLD','QUALITY_BLOCK','REGULATORY_BLOCK','DAMAGE_BLOCK','RECALL','MANUAL') NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `bin_id`            INT UNSIGNED NULL,
    `quantity_blocked`  DECIMAL(15,4) NULL COMMENT 'NULL = todo el lote',
    `reason`            VARCHAR(500) NOT NULL,
    `status`            ENUM('ACTIVE','RELEASED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    `blocked_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `blocked_by`        INT UNSIGNED NOT NULL,
    `released_at`       DATETIME     NULL,
    `released_by`       INT UNSIGNED NULL,
    `release_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_blocks_item` (`item_id`),
    KEY `idx_blocks_batch` (`batch_id`),
    KEY `idx_blocks_status` (`status`),
    KEY `idx_blocks_wh` (`warehouse_id`),
    CONSTRAINT `fk_blocks_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_blocks_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_blocks_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_blocks_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_blocks_blocker` FOREIGN KEY (`blocked_by`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_blocks_releaser` FOREIGN KEY (`released_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Bloqueos de stock: QA, calidad, regulatorio, recall';

-- ============================================================================
-- 15. CONTEOS CÍCLICOS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `cycle_counts` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `count_number`      VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `count_type`        ENUM('FULL','PARTIAL','ABC','RANDOM','BIN') NOT NULL DEFAULT 'PARTIAL',
    `status`            ENUM('PLANNED','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PLANNED',
    `planned_date`      DATE         NULL,
    `started_at`        DATETIME     NULL,
    `completed_at`      DATETIME     NULL,
    `assigned_to`       INT UNSIGNED 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_count_number` (`count_number`),
    KEY `idx_count_wh` (`warehouse_id`),
    KEY `idx_count_status` (`status`),
    CONSTRAINT `fk_count_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_count_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_count_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Cabecera de conteos cíclicos';

-- ============================================================================
-- 16. LÍNEAS DE CONTEO CÍCLICO
-- ============================================================================

CREATE TABLE IF NOT EXISTS `cycle_count_lines` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `cycle_count_id`    INT UNSIGNED NOT NULL,
    `bin_id`            INT UNSIGNED NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED NULL,
    `system_qty`        DECIMAL(15,4) NOT NULL COMMENT 'Cantidad según sistema al momento del conteo',
    `counted_qty`       DECIMAL(15,4) NULL COMMENT 'Cantidad contada por operador',
    `variance`          DECIMAL(15,4) NULL COMMENT 'Diferencia (counted - system)',
    `status`            ENUM('PENDING','COUNTED','APPROVED','ADJUSTED') NOT NULL DEFAULT 'PENDING',
    `counted_by`        INT UNSIGNED NULL,
    `counted_at`        DATETIME     NULL,
    `approved_by`       INT UNSIGNED NULL,
    `approved_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,
    KEY `idx_countline_count` (`cycle_count_id`),
    KEY `idx_countline_bin` (`bin_id`),
    KEY `idx_countline_item` (`item_id`),
    CONSTRAINT `fk_countline_count` FOREIGN KEY (`cycle_count_id`) REFERENCES `cycle_counts`(`id`),
    CONSTRAINT `fk_countline_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_countline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_countline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_countline_counter` FOREIGN KEY (`counted_by`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_countline_approver` FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Líneas de conteo cíclico con varianza';

-- ============================================================================
-- 17. LOG DE SINCRONIZACIÓN SAP
-- ============================================================================

CREATE TABLE IF NOT EXISTS `sap_sync_log` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sync_type`         ENUM('MASTER_ITEMS','MASTER_WAREHOUSES','MASTER_BP','DOC_PURCHASE','DOC_SALES','DOC_DELIVERY','DOC_RECEIPT','BATCH') NOT NULL,
    `direction`         ENUM('SAP_TO_WMS','WMS_TO_SAP') NOT NULL,
    `sap_doc_entry`     INT          NULL,
    `sap_doc_type`      VARCHAR(30)  NULL,
    `wms_reference_id`  INT UNSIGNED NULL,
    `status`            ENUM('PENDING','SUCCESS','ERROR','RETRY') NOT NULL DEFAULT 'PENDING',
    `request_payload`   JSON         NULL,
    `response_payload`  JSON         NULL,
    `error_message`     TEXT         NULL,
    `retry_count`       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `max_retries`       TINYINT UNSIGNED NOT NULL DEFAULT 3,
    `next_retry_at`     DATETIME     NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_sync_type` (`sync_type`),
    KEY `idx_sync_status` (`status`),
    KEY `idx_sync_sap` (`sap_doc_entry`, `sap_doc_type`),
    KEY `idx_sync_retry` (`status`, `next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Log de sincronización bidireccional con SAP B1';

-- ============================================================================
-- 18. AUDITORÍA GENERAL
-- ============================================================================

CREATE TABLE IF NOT EXISTS `audit_log` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `table_name`        VARCHAR(64)  NOT NULL,
    `record_id`         INT UNSIGNED NOT NULL,
    `action`            ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    `old_values`        JSON         NULL,
    `new_values`        JSON         NULL,
    `user_id`           INT UNSIGNED NULL,
    `ip_address`        VARCHAR(45)  NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_audit_table` (`table_name`, `record_id`),
    KEY `idx_audit_user` (`user_id`),
    KEY `idx_audit_date` (`created_at`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Log de auditoría general del sistema';

-- ============================================================================
-- 19. CONFIGURACIÓN DEL SISTEMA
-- ============================================================================

CREATE TABLE IF NOT EXISTS `system_config` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `config_key`    VARCHAR(100) NOT NULL,
    `config_value`  TEXT         NOT NULL,
    `description`   VARCHAR(255) NULL,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuración general del WMS';

-- ============================================================================
-- 20. SECUENCIAS DE NUMERACIÓN
-- ============================================================================

CREATE TABLE IF NOT EXISTS `doc_sequences` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sequence_key`  VARCHAR(50)  NOT NULL COMMENT 'ASN, GR, GI, PICK, COUNT, TASK',
    `prefix`        VARCHAR(10)  NOT NULL DEFAULT '',
    `current_value` INT UNSIGNED NOT NULL DEFAULT 0,
    `pad_length`    TINYINT UNSIGNED NOT NULL DEFAULT 8,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_sequence_key` (`sequence_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Secuencias para numeración automática de documentos';

-- ============================================================================
-- DATOS INICIALES
-- ============================================================================

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
('ASN',   'ASN-',   0, 8),
('GR',    'GR-',    0, 8),
('GI',    'GI-',    0, 8),
('PICK',  'PK-',    0, 8),
('COUNT', 'CC-',    0, 8),
('TASK',  'WT-',    0, 8),
('XFER',  'XF-',    0, 8);

INSERT INTO `system_config` (`config_key`, `config_value`, `description`) VALUES
('fefo_enabled',         '1',    'Activar FEFO (First Expired First Out)'),
('lot_required',         '1',    'Lote obligatorio en recepción'),
('expiry_required',      '1',    'Vencimiento obligatorio en recepción'),
('min_shelf_life_days',  '90',   'Mínimo vida útil restante para aceptar recepción (días)'),
('qa_on_receipt',        '1',    'Enviar stock a QA automáticamente al recibir'),
('auto_putaway',         '1',    'Generar tarea de putaway automáticamente al confirmar recepción'),
('sap_sync_interval',   '300',   'Intervalo de sincronización con SAP (segundos)'),
('sap_service_layer_url','https://sapserver:50000/b1s/v1', 'URL de SAP Service Layer');

SET FOREIGN_KEY_CHECKS = 1;
