-- =============================================================================
-- XPERT 3PL - Migracion 002
-- Parametros operativos por empresa, maestros de clientes/proveedores,
-- presentaciones de producto, y soporte de zonas refrigeradas.
-- =============================================================================

-- ============================================================================
-- 1. PARAMETROS OPERATIVOS POR EMPRESA (1:1 con companies)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `company_settings` (
    `company_id`           INT UNSIGNED PRIMARY KEY,
    `storage_mode`         ENUM('CHAOTIC','DIRECTED') NOT NULL DEFAULT 'CHAOTIC'
                           COMMENT 'CHAOTIC = cualquier ubicacion libre; DIRECTED = ubicacion fija asignada por SKU',
    `picking_method`       ENUM('FEFO','FIFO') NOT NULL DEFAULT 'FEFO',
    `location_policy`      ENUM('MULTI_SKU','SINGLE_SKU_LOT') NOT NULL DEFAULT 'MULTI_SKU'
                           COMMENT 'MULTI_SKU = una ubicacion puede tener varios item/lote (multireferencia); SINGLE_SKU_LOT = 1 solo item+lote por ubicacion, cumplimiento estricto',
    `requires_quarantine`  TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Stock recibido entra en QA hasta liberacion manual (estilo DINAVISA)',
    `requires_cold_chain`  TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Requiere almacenamiento refrigerado - solo puede ubicarse en zonas con is_refrigerated=1',
    `uses_presentations`   TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Si el catalogo de esta empresa maneja multiples presentaciones (unidad/caja/pallet) por item',
    `inbound_channel`      ENUM('MANUAL','FILE_UPLOAD','API') NOT NULL DEFAULT 'MANUAL',
    `outbound_channel`     ENUM('MANUAL','FILE_UPLOAD','API') NOT NULL DEFAULT 'MANUAL',
    `inbound_api_endpoint`   VARCHAR(255) NULL COMMENT 'URL del sistema del cliente para consultar/recibir avisos de ingreso',
    `inbound_api_key_enc`    VARCHAR(255) NULL COMMENT 'Credencial cifrada para el endpoint de ingreso',
    `outbound_api_endpoint`  VARCHAR(255) NULL COMMENT 'URL del sistema del cliente para pedidos de salida',
    `outbound_api_key_enc`   VARCHAR(255) NULL COMMENT 'Credencial cifrada para el endpoint de egreso',
    `notes`                TEXT NULL,
    `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_settings_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Parametros operativos declarados por empresa - el sistema hace cumplir location_policy de forma estricta';

-- ============================================================================
-- 2. PRESENTACIONES DE PRODUCTO (unidad / caja / pallet, con su propio codigo de barras)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `item_presentations` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `item_id`          INT UNSIGNED NOT NULL,
    `presentation_name` VARCHAR(50)  NOT NULL COMMENT 'Ej: Unidad, Caja x12, Pallet',
    `uom_code`         VARCHAR(20)  NOT NULL,
    `barcode`          VARCHAR(100) NULL,
    `qty_per_base_uom` DECIMAL(15,4) NOT NULL DEFAULT 1 COMMENT 'Cuantas unidades base equivale esta presentacion',
    `is_base_unit`     TINYINT(1)   NOT NULL DEFAULT 0,
    `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_presentation_item_name` (`item_id`, `presentation_name`),
    KEY `idx_presentation_barcode` (`barcode`),
    CONSTRAINT `fk_presentation_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. CLIENTES DE LA EMPRESA (a quien le despachan) - por empresa
-- ============================================================================
CREATE TABLE IF NOT EXISTS `customers` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `code`          VARCHAR(30)  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_customers_company_code` (`company_id`, `code`),
    CONSTRAINT `fk_customers_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Clientes de cada empresa (destinatarios de despacho) - no confundir con companies (los tenants del deposito)';

-- ============================================================================
-- 4. PROVEEDORES DE LA EMPRESA (quien les manda mercaderia) - por empresa
-- ============================================================================
CREATE TABLE IF NOT EXISTS `suppliers` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `code`          VARCHAR(30)  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_suppliers_company_code` (`company_id`, `code`),
    CONSTRAINT `fk_suppliers_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 5. VINCULAR ingresos/egresos a proveedor/cliente
-- ============================================================================
ALTER TABLE `inbound_documents`
    ADD COLUMN `supplier_id` INT UNSIGNED NULL AFTER `company_id`,
    ADD CONSTRAINT `fk_inbound_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`);

ALTER TABLE `outbound_documents`
    ADD COLUMN `customer_id` INT UNSIGNED NULL AFTER `company_id`,
    ADD CONSTRAINT `fk_outbound_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`);

-- ============================================================================
-- 6. ZONAS REFRIGERADAS (para poder rutear empresas con requires_cold_chain)
-- ============================================================================
ALTER TABLE `warehouse_zones`
    ADD COLUMN `is_refrigerated` TINYINT(1) NOT NULL DEFAULT 0 AFTER `zone_type`;
