-- =============================================================================
-- XPERT 3PL - Esquema inicial
-- WMS multi-cliente para operador logistico (almacenamiento + manipuleo +
-- despacho para terceros). Cada empresa cliente es un tenant de primera clase.
-- =============================================================================

-- ============================================================================
-- 1. EMPRESAS CLIENTE (tenants)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `companies` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`          VARCHAR(20)  NOT NULL,
    `name`          VARCHAR(150) NOT NULL,
    `contact_name`  VARCHAR(100) NULL,
    `contact_email` VARCHAR(150) NULL,
    `contact_phone` VARCHAR(50)  NULL,
    `address`       VARCHAR(255) NULL,
    `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_companies_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Empresas cliente (tenants) que almacenan mercaderia en el deposito';

CREATE TABLE IF NOT EXISTS `company_api_keys` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `api_key_hash`  VARCHAR(255) NOT NULL,
    `label`         VARCHAR(100) NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `last_used_at`  DATETIME     NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_apikeys_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Claves de API para que cada empresa cliente integre su propio sistema';

-- ============================================================================
-- 2. ALMACENES / ZONAS / UBICACIONES
-- ============================================================================
CREATE TABLE IF NOT EXISTS `warehouses` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`       VARCHAR(20)  NOT NULL,
    `name`       VARCHAR(100) NOT NULL,
    `address`    VARCHAR(255) NULL,
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `warehouse_zones` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id`  INT UNSIGNED NOT NULL,
    `company_id`    INT UNSIGNED NULL COMMENT 'NULL = zona compartida/general, no exclusiva de una empresa',
    `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_zones_wh_code` (`warehouse_id`, `code`),
    KEY `idx_zones_company` (`company_id`),
    CONSTRAINT `fk_zones_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_zones_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Zonificacion por empresa cliente dentro de un mismo almacen fisico';

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,
    `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_references` INT UNSIGNED NULL COMMENT 'Tope de combinaciones item+lote distintas en este bin. NULL = sin limite (parametrizable por deposito)',
    `max_weight_kg`  DECIMAL(10,2) NULL,
    `max_volume_m3`  DECIMAL(10,4) NULL,
    `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_bins_wh_code` (`warehouse_id`, `code`),
    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 fisicas. max_references habilita multireferencia (varios SKU/lote por bin)';

-- ============================================================================
-- 3. CATALOGO DE ITEMS (por empresa - SKU puede repetirse entre empresas)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `items` (
    `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`         INT UNSIGNED NOT NULL,
    `sku`                VARCHAR(50)  NOT NULL,
    `description`        VARCHAR(255) NOT NULL,
    `barcode`            VARCHAR(100) NULL,
    `uom`                VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `requires_lot`       TINYINT(1)   NOT NULL DEFAULT 0,
    `requires_expiry`    TINYINT(1)   NOT NULL DEFAULT 0,
    `requires_serial`    TINYINT(1)   NOT NULL DEFAULT 0,
    `weight_kg`          DECIMAL(10,4) NULL,
    `volume_m3`          DECIMAL(10,6) NULL,
    `packaging_notes`    VARCHAR(255) NULL,
    `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,
    `created_by`         INT UNSIGNED NULL,
    UNIQUE KEY `uq_items_company_sku` (`company_id`, `sku`),
    KEY `idx_items_barcode` (`barcode`),
    CONSTRAINT `fk_items_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Catalogo de items por empresa cliente. El mismo SKU puede existir en distintas empresas sin relacion';

-- ============================================================================
-- 4. LOTES (solo aplica si el item lo requiere)
-- ============================================================================
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         NULL,
    `manufacturing_date`  DATE         NULL,
    `supplier_batch`      VARCHAR(50)  NULL,
    `status`              ENUM('AVAILABLE','QA','BLOCKED','EXPIRED','REJECTED') NOT NULL DEFAULT 'QA',
    `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_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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. STOCK (multireferencia: varios item+lote pueden compartir un mismo bin)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `stock` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL COMMENT 'Denormalizado desde items.company_id para reportes/costeo rapidos',
    `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,
    `uom`           VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `received_at`   DATETIME     NULL COMMENT 'Fecha de ingreso a este bin, base para costeo de almacenamiento por dia',
    `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_company` (`company_id`),
    KEY `idx_stock_item` (`item_id`),
    KEY `idx_stock_batch` (`batch_id`),
    KEY `idx_stock_bin` (`bin_id`),
    CONSTRAINT `fk_stock_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`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='Un mismo bin puede tener muchas filas (distintos item/lote) - multireferencia real';

-- ============================================================================
-- 6. USUARIOS Y ROLES (personal compartido entre todas las empresas)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `roles` (
    `code`      VARCHAR(30) PRIMARY KEY,
    `name`      VARCHAR(100) NOT NULL,
    `is_system` TINYINT(1)   NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`code`, `name`, `is_system`) VALUES
    ('admin',         'Administrador',           1),
    ('supervisor',    'Supervisor de Deposito',  0),
    ('op_recepcion',  'Operador de Recepcion',   0),
    ('op_almacen',    'Operador de Almacen',     0),
    ('op_picking',    'Operador de Picking',     0),
    ('op_packing',    'Operador de Packing',     0),
    ('viewer',        'Solo Lectura',            0)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

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`              VARCHAR(30)  NOT NULL,
    `warehouse_id`      INT UNSIGNED NULL COMMENT 'Almacen por defecto (personal compartido entre empresas, no por tenant)',
    `is_active`         TINYINT(1)   NOT NULL DEFAULT 1,
    `last_login_at`      DATETIME     NULL,
    `last_activity_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`),
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role`) REFERENCES `roles`(`code`),
    CONSTRAINT `fk_users_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 7. INGRESOS (multi-canal: API / archivo / manual)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `inbound_documents` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`       INT UNSIGNED NOT NULL,
    `warehouse_id`     INT UNSIGNED NOT NULL,
    `doc_number`       VARCHAR(30)  NOT NULL,
    `client_reference` VARCHAR(100) NULL COMMENT 'Nro. de PO/remito/factura del cliente',
    `source_channel`   ENUM('API','FILE_UPLOAD','MANUAL') NOT NULL DEFAULT 'MANUAL',
    `source_reference` VARCHAR(255) NULL COMMENT 'Nombre de archivo, id de llamada API, o usuario que cargo a mano',
    `status`           ENUM('DRAFT','PENDING','RECEIVING','CLOSED','CANCELLED') NOT NULL DEFAULT 'PENDING',
    `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_inbound_docnum` (`doc_number`),
    KEY `idx_inbound_company` (`company_id`),
    KEY `idx_inbound_status` (`status`),
    CONSTRAINT `fk_inbound_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_inbound_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Packing list / aviso de ingreso, sin importar el canal por el que llego';

CREATE TABLE IF NOT EXISTS `inbound_document_lines` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `document_id`    INT UNSIGNED NOT NULL,
    `line_num`       INT          NOT NULL,
    `item_id`        INT UNSIGNED NULL COMMENT 'NULL si el item todavia no existe en el catalogo (se crea durante la recepcion)',
    `sku_text`       VARCHAR(50)  NULL COMMENT 'SKU tal como vino en el archivo/API, aunque el item no exista aun',
    `description_text` VARCHAR(255) NULL,
    `expected_qty`   DECIMAL(15,4) NOT NULL,
    `received_qty`   DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`            VARCHAR(20)  NOT NULL DEFAULT 'UN',
    `batch_number`   VARCHAR(50)  NULL,
    `expiry_date`    DATE         NULL,
    `status`         ENUM('PENDING','PARTIAL','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_inboundline` (`document_id`, `line_num`),
    KEY `idx_inboundline_item` (`item_id`),
    CONSTRAINT `fk_inboundline_doc` FOREIGN KEY (`document_id`) REFERENCES `inbound_documents`(`id`),
    CONSTRAINT `fk_inboundline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. EGRESOS (multi-canal, con confirmacion de despacho)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `outbound_documents` (
    `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`         INT UNSIGNED NOT NULL,
    `warehouse_id`       INT UNSIGNED NOT NULL,
    `doc_number`         VARCHAR(30)  NOT NULL,
    `client_reference`   VARCHAR(100) NULL,
    `source_channel`     ENUM('API','FILE_UPLOAD','MANUAL') NOT NULL DEFAULT 'MANUAL',
    `source_reference`   VARCHAR(255) NULL,
    `status`             ENUM('DRAFT','OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `requested_ship_date` DATE        NULL,
    `carrier_name`       VARCHAR(100) NULL,
    `driver_name`        VARCHAR(100) NULL,
    `vehicle_plate`      VARCHAR(20)  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_outbound_docnum` (`doc_number`),
    KEY `idx_outbound_company` (`company_id`),
    KEY `idx_outbound_status` (`status`),
    CONSTRAINT `fk_outbound_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_outbound_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Pedido de despacho. dispatched_at + carrier/driver/plate alimentan la confirmacion que se le manda al cliente';

CREATE TABLE IF NOT EXISTS `outbound_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 COMMENT 'Asignado por FEFO al liberar/pickear',
    `from_bin_id`    INT UNSIGNED NULL,
    `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',
    `notes`          VARCHAR(255) NULL,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_outboundline` (`document_id`, `line_num`),
    KEY `idx_outboundline_item` (`item_id`),
    KEY `idx_outboundline_batch` (`batch_id`),
    CONSTRAINT `fk_outboundline_doc` FOREIGN KEY (`document_id`) REFERENCES `outbound_documents`(`id`),
    CONSTRAINT `fk_outboundline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_outboundline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_outboundline_bin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. TAREAS DE DEPOSITO (mismo modelo generico que XPERT: PUTAWAY/PICK/MOVE/COUNT)
-- ============================================================================
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') NOT NULL,
    `priority`       TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `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,
    `reference_type` VARCHAR(50)  NULL COMMENT 'INBOUND_DOC, OUTBOUND_DOC, etc.',
    `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_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_frombin` FOREIGN KEY (`from_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tasks_tobin` FOREIGN KEY (`to_bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_tasks_user` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. MOVIMIENTOS DE STOCK (auditoria + base de datos para costeo)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `stock_movements` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`        INT UNSIGNED NOT NULL,
    `warehouse_id`      INT UNSIGNED NOT NULL,
    `movement_type`     ENUM('RECEIPT','PUTAWAY','PICK','MOVE','ADJUSTMENT','DISPATCH') NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `batch_id`          INT UNSIGNED 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,
    `reference_id`      INT UNSIGNED NULL,
    `reason`            VARCHAR(255) NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by`        INT UNSIGNED NULL,
    KEY `idx_movements_company` (`company_id`),
    KEY `idx_movements_wh` (`warehouse_id`),
    KEY `idx_movements_item` (`item_id`),
    KEY `idx_movements_type` (`movement_type`),
    KEY `idx_movements_date` (`created_at`),
    CONSTRAINT `fk_movements_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_movements_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_movements_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registro inmutable de movimientos - fuente base para costeo/facturacion';

-- ============================================================================
-- 11. COSTEO / FACTURACION POR EMPRESA
-- ============================================================================
CREATE TABLE IF NOT EXISTS `billing_rules` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `rule_type`     ENUM('STORAGE_PER_PALLET_DAY','STORAGE_PER_M3_DAY','HANDLING_RECEIPT','HANDLING_PICK','HANDLING_PACK','HANDLING_DISPATCH','CUSTOM') NOT NULL,
    `unit_price`    DECIMAL(12,4) NOT NULL,
    `uom_basis`     VARCHAR(30)  NULL COMMENT 'Ej: pallet, m3, movimiento, unidad',
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `valid_from`    DATE         NULL,
    `valid_to`      DATE         NULL,
    `notes`         VARCHAR(255) NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_billingrules_company` (`company_id`),
    CONSTRAINT `fk_billingrules_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tarifas configurables por empresa: almacenamiento, manipuleo, despacho';

CREATE TABLE IF NOT EXISTS `billing_events` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`        INT UNSIGNED NOT NULL,
    `warehouse_id`       INT UNSIGNED NOT NULL,
    `billing_rule_id`   INT UNSIGNED NOT NULL,
    `event_type`        VARCHAR(50)  NOT NULL,
    `reference_type`    VARCHAR(50)  NULL,
    `reference_id`      INT UNSIGNED NULL,
    `quantity`          DECIMAL(15,4) NOT NULL,
    `unit_price`        DECIMAL(12,4) NOT NULL,
    `amount`            DECIMAL(14,4) NOT NULL,
    `event_date`        DATE         NOT NULL,
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_billingevents_company` (`company_id`),
    KEY `idx_billingevents_date` (`event_date`),
    CONSTRAINT `fk_billingevents_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_billingevents_rule` FOREIGN KEY (`billing_rule_id`) REFERENCES `billing_rules`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Cargos generados, base para los reportes de costo por empresa/almacen/operacion';

-- ============================================================================
-- 12. AUDITORIA (mismo patron generico que XPERT)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `audit_log` (
    `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `table_name`   VARCHAR(50)  NOT NULL,
    `record_id`    INT UNSIGNED NOT NULL,
    `action`       VARCHAR(50)  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_record` (`table_name`, `record_id`),
    KEY `idx_audit_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 13. SECUENCIAS DE NUMERACION DE DOCUMENTOS
-- ============================================================================
CREATE TABLE IF NOT EXISTS `doc_sequences` (
    `sequence_key`   VARCHAR(30) PRIMARY KEY,
    `prefix`         VARCHAR(10) NOT NULL DEFAULT '',
    `current_value`  INT UNSIGNED NOT NULL DEFAULT 0,
    `pad_length`     TINYINT UNSIGNED NOT NULL DEFAULT 8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
    ('INBOUND',  'IN-',  0, 8),
    ('OUTBOUND', 'OUT-', 0, 8),
    ('TASK',     'TSK-', 0, 8)
ON DUPLICATE KEY UPDATE `prefix` = VALUES(`prefix`);

-- ============================================================================
-- 14. DATOS INICIALES (almacen por defecto; el usuario admin se crea aparte
-- con un script PHP para generar el hash bcrypt correctamente)
-- ============================================================================
INSERT INTO `warehouses` (`code`, `name`) VALUES ('WH01', 'Deposito Central') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);
