-- Active: 1761347332490@@127.0.0.1@3306@nucleo_base
-- #######################################################################
-- # I. ERP CORE SUITE: CONFIGURACIÓN BASE Y GESTIÓN FINANCIERA (FINANCE)
-- #######################################################################

-- Tabla: Cuentas Contables (Chart of Accounts)
DROP TABLE IF EXISTS mod_gl_accounts;
CREATE TABLE IF NOT EXISTS mod_gl_accounts (
    `mod_gl_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de cuenta contable',
    `mod_gl_code` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Código de cuenta estandarizado (Ej: 1000, 2100)',
    `mod_gl_name` VARCHAR(100) NOT NULL COMMENT 'Nombre descriptivo de la cuenta',
    `mod_gl_type` ENUM('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE') NOT NULL COMMENT 'Clasificación contable principal',
    `mod_gl_parent_id` INT UNSIGNED COMMENT 'ID de la cuenta padre para la jerarquía',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Catálogo de Cuentas (Chart of Accounts)';

-- Tabla: Transacciones Generales (Eventos de Negocio)
DROP TABLE IF EXISTS mod_gl_transactions;
CREATE TABLE IF NOT EXISTS mod_gl_transactions (
    `mod_gt_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la transacción de negocio',
    `mod_gt_source_type` VARCHAR(50) NOT NULL COMMENT 'Módulo o tipo de documento que originó la transacción (e.g., ORDER, PAYOUT)',
    `mod_gt_source_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del documento de origen (e.g., ID de la Orden o ID del Payout)',
    `mod_gt_date` DATETIME NOT NULL COMMENT 'Fecha y hora de la transacción',
    `mod_gt_description` TEXT COMMENT 'Descripción del evento transaccional',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Eventos de negocio que generan movimientos contables';

-- Tabla: Asientos Contables Detallados (General Ledger Entries)
DROP TABLE IF EXISTS mod_gl_entries;
-- Tabla: Asientos Contables Detallados (General Ledger Entries)
CREATE TABLE IF NOT EXISTS mod_gl_entries (
  `mod_ge_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del asiento contable detallado',
  `mod_ge_gt_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la transacción (mod_gt_id)',
  `mod_ge_gl_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la cuenta contable afectada (mod_gl_id)',
  `mod_ge_type` ENUM('DEBIT', 'CREDIT') NOT NULL COMMENT 'Tipo de movimiento (DEBITO o CREDITO)',
  `mod_ge_amount` DECIMAL(15, 4) NOT NULL COMMENT 'Monto absoluto (siempre positivo)',
  `mod_ge_date` DATETIME NOT NULL COMMENT 'Fecha y hora de registro del asiento',
  `mod_ge_module` VARCHAR(50) COMMENT 'Módulo que insertó el asiento (e.g., OMS, FSS)',
  INDEX idx_account_date (`mod_ge_gl_id`, `mod_ge_date`)
) ENGINE=InnoDB COMMENT='Asientos contables inmutables (Journal Entries)';

-- #######################################################################
-- # I. ERP CORE SUITE: GESTIÓN DE RECURSOS HUMANOS (HRM)
-- #######################################################################

-- Tabla: Empleados (Recurso Humano)
DROP TABLE IF EXISTS mod_hrm_employees;
CREATE TABLE IF NOT EXISTS mod_hrm_employees (
    `mod_he_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del empleado',
    `mod_he_code` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Código de identificación interna del empleado',
    `mod_he_first_name` VARCHAR(100) NOT NULL COMMENT 'Nombre del empleado',
    `mod_he_last_name` VARCHAR(100) NOT NULL COMMENT 'Apellido del empleado',
    `mod_he_email` VARCHAR(255) UNIQUE NOT NULL COMMENT 'Correo electrónico corporativo',
    `mod_he_dept_id` INT UNSIGNED COMMENT 'ID del departamento actual',
    `mod_he_hire_date` DATE NOT NULL COMMENT 'Fecha de contratación',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Información del Recurso Humano y Empleados (Auditoría)';

-- Tabla: Puestos/Posiciones de Empleado
DROP TABLE IF EXISTS mod_hrm_positions;
CREATE TABLE IF NOT EXISTS mod_hrm_positions (
    `mod_hp_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la posición',
    `mod_hp_employee_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del empleado (mod_he_id)',
    `mod_hp_title` VARCHAR(100) NOT NULL COMMENT 'Título del puesto (Ej: Gerente de Finanzas)',
    `mod_hp_start_date` DATE NOT NULL COMMENT 'Fecha de inicio en el puesto'
) ENGINE=InnoDB COMMENT='Historial de puestos y posiciones de empleados';

-- #######################################################################
-- # I. ERP CORE SUITE: GESTIÓN DE CLIENTES (CRM Base)
-- #######################################################################

-- Tabla: Clientes Finales
DROP TABLE IF EXISTS mod_crm_customers;
CREATE TABLE IF NOT EXISTS mod_crm_customers (
    `mod_cc_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del cliente',
    `mod_cc_email` VARCHAR(255) UNIQUE NOT NULL COMMENT 'Correo electrónico del cliente',
    `mod_cc_first_name` VARCHAR(100) COMMENT 'Primer nombre',
    `mod_cc_last_name` VARCHAR(100) COMMENT 'Apellido',
    `mod_cc_registration_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de registro del cliente',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Registro de Clientes (Consumidores Finales)';

-- #######################################################################
-- # II. MARKETPLACE MANAGEMENT SYSTEM (MMS)
-- #######################################################################

-- 2.1. VMS (Vendor Management System)

-- Tabla: Vendedores del Marketplace (Vendors)
DROP TABLE IF EXISTS mod_vms_vendors;
CREATE TABLE IF NOT EXISTS mod_vms_vendors (
    `mod_vv_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del vendedor (Vendor)',
    `mod_vv_legal_name` VARCHAR(255) NOT NULL COMMENT 'Nombre legal o razón social del vendedor',
    `mod_vv_contact_email` VARCHAR(255) UNIQUE NOT NULL COMMENT 'Correo electrónico de contacto',
    `mod_vv_kyc_status` ENUM('PENDING', 'APPROVED', 'REJECTED') NOT NULL COMMENT 'Estado de verificación (KYC/Compliance)',
    `mod_vv_payout_json` JSON COMMENT 'Detalles de la cuenta bancaria para liquidación (idealmente encriptados)',
    `mod_vv_onboarding_date` DATE NOT NULL COMMENT 'Fecha de alta en la plataforma',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Registro de Vendedores del Marketplace';

-- Tabla: Métricas de Rendimiento del Vendor (Scorecard)
DROP TABLE IF EXISTS mod_vms_performance;
CREATE TABLE IF NOT EXISTS mod_vms_performance (
    `mod_vp_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la métrica',
    `mod_vp_vv_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del vendedor (mod_vv_id)',
    `mod_vp_date_recorded` DATE NOT NULL COMMENT 'Fecha de registro de la métrica',
    `mod_vp_ots_rate` DECIMAL(5, 4) COMMENT 'Tasa de envío a tiempo (On-Time Shipping Rate)',
    `mod_vp_cancellation_rate` DECIMAL(5, 4) COMMENT 'Tasa de cancelación de pedidos',
    `mod_vp_avg_rating` DECIMAL(3, 2) COMMENT 'Promedio de Reseñas (desde CCM)',
    UNIQUE KEY idx_vendor_date (`mod_vp_vv_id`, `mod_vp_date_recorded`)
) ENGINE=InnoDB COMMENT='Métricas diarias de rendimiento (alimentadas por BI)';

-- Tabla: Reglas de Comisión del Marketplace
DROP TABLE IF EXISTS mod_vms_commission_rules;
CREATE TABLE IF NOT EXISTS mod_vms_commission_rules (
    `mod_vcr_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la regla de comisión',
    `mod_vcr_name` VARCHAR(100) NOT NULL COMMENT 'Nombre de la regla (Ej: Electronica_Estandar)',
    `mod_vcr_type` ENUM('FIXED', 'PERCENTAGE', 'TIERED') NOT NULL COMMENT 'Tipo de cálculo de comisión',
    `mod_vcr_percentage` DECIMAL(5, 4) COMMENT 'Porcentaje a aplicar (si es tipo PERCENTAGE)',
    `mod_vcr_fixed_amount` DECIMAL(10, 2) COMMENT 'Monto fijo a aplicar (si es tipo FIXED)',
    `mod_vcr_start_date` DATE NOT NULL COMMENT 'Fecha de inicio de vigencia',
    `mod_vcr_end_date` DATE COMMENT 'Fecha de fin de vigencia (NULL si es indefinida)',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Reglas maestras para calcular la comisión marketplace-vendor';

-- 2.2. PIM (Product Information Management)

-- Tabla: Colecciones de Productos
DROP TABLE IF EXISTS mod_pim_collections;
CREATE TABLE IF NOT EXISTS mod_pim_collections (
    `mod_pcol_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la colección',
    `mod_pcol_name` VARCHAR(100) NOT NULL COMMENT 'Nombre de la colección',
    `mod_pcol_description` TEXT COMMENT 'Descripción de la colección',
    `mod_pcol_tags` TEXT COMMENT 'Etiquetas de la colección',
    `mod_pcol_icon` VARCHAR(255) COMMENT 'Icono de la colección',
    `mod_pcol_img_desktop` VARCHAR(255) COMMENT 'Imagen de la colección para desktop',
    `mod_pcol_img_mobile` VARCHAR(255) COMMENT 'Imagen de la colección para mobile',
    `mod_pcol_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_pcol_ent_id` INT UNSIGNED COMMENT 'ID de la entidad',
    `mod_pcol_user_id` INT UNSIGNED COMMENT 'ID del usuario que creó la colección',
    `mod_pcol_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_pcol_state` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Jerarquía de Colecciones de Productos';

-- Tabla: Productos Base (Entidad Lógica)

DROP TABLE IF EXISTS mod_pim_products;
CREATE TABLE IF NOT EXISTS mod_pim_products (
    `mod_pp_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del producto base',
    `mod_pp_name` VARCHAR(255) NOT NULL COMMENT 'Nombre base del producto',
    `mod_pp_description` TEXT COMMENT 'Descripción del producto',
    `mod_pp_tags` TEXT COMMENT 'Etiquetas del producto',
    `mod_pp_ent_id` INT UNSIGNED COMMENT 'ID de la entidad',
    `mod_pp_user_id` INT UNSIGNED COMMENT 'ID del usuario que creó el producto',
    `mod_pp_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_pp_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_pp_json` TEXT COMMENT 'Json del producto',
    `mod_pp_state` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB COMMENT='Producto Base (Entidad Lógica) y sus detalles principales';

-- Tabla: Relación Productos-Colecciones (Muchos a Muchos)
DROP TABLE IF EXISTS mod_pim_products_collections;
CREATE TABLE IF NOT EXISTS mod_pim_products_collections (
    `mod_ppcol_pp_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del producto',
    `mod_ppcol_pcol_id` INT UNSIGNED NOT NULL COMMENT 'ID de la colección',
    `mod_ppcol_order` INT NOT NULL COMMENT 'Orden de la colección',
    PRIMARY KEY (`mod_ppcol_pp_id`, `mod_ppcol_pcol_id`)
) ENGINE=InnoDB COMMENT='Relación Muchos a Muchos entre Productos y Colecciones';

-- Relación con files 
DROP TABLE IF EXISTS mod_pim_products_files;
CREATE TABLE IF NOT EXISTS mod_pim_products_files (
    `mod_ppf_pp_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del producto',
    `mod_ppf_file_id` INT UNSIGNED NOT NULL COMMENT 'ID del archivo',
    `mod_ppf_order` INT NOT NULL COMMENT 'Orden del archivo',
    PRIMARY KEY (`mod_ppf_pp_id`, `mod_ppf_file_id`)
) ENGINE=InnoDB COMMENT='Relación Muchos a Muchos entre Productos y Archivos';


DROP TABLE IF EXISTS mod_pim_products_categorys;
CREATE TABLE IF NOT EXISTS mod_pim_products_categorys (
    `mod_ppc_pp_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del producto',
    `mod_ppc_cat_id` INT UNSIGNED NOT NULL COMMENT 'ID de la categoría',
    `mod_ppc_order` INT NOT NULL COMMENT 'Orden de la categoría',
    PRIMARY KEY (`mod_ppc_pp_id`, `mod_ppc_cat_id`)
) ENGINE=InnoDB COMMENT='Relación Muchos a Muchos entre Productos y Categorías';

-- Tabla: Configuraciones del módulo PIM (Key-Value)
DROP TABLE IF EXISTS mod_pim_config;
CREATE TABLE IF NOT EXISTS mod_pim_config (
    `mod_pconf_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único',
    `mod_pconf_key` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Clave de configuración',
    `mod_pconf_value` TEXT COMMENT 'Valor de configuración',
    `mod_pconf_description` VARCHAR(255) COMMENT 'Descripción de la configuración',
    `mod_pconf_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string' COMMENT 'Tipo de dato',
    `mod_pconf_user_id` INT UNSIGNED NOT NULL COMMENT 'ID del usuario que creó la configuración',
    `mod_pconf_ent_id` INT UNSIGNED NOT NULL COMMENT 'ID de la entidad',
    `mod_pconf_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de actualización'
) ENGINE=InnoDB COMMENT='Configuraciones del módulo PIM (Key-Value Store)';

-- Insertar configuraciones iniciales
INSERT INTO `mod_pim_config` (`mod_pconf_key`, `mod_pconf_value`, `mod_pconf_description`, `mod_pconf_type`, `mod_pconf_ent_id`) VALUES
('principal_category_id', '2', 'ID de la categoría principal de productos', 'number', 1),
('enable_collections', '1', 'Habilitar sistema de colecciones', 'boolean', 1),
('enable_variants', '1', 'Habilitar variantes de productos', 'boolean', 1),
('default_image_folder', 'products', 'Carpeta por defecto para imágenes de productos', 'string', 1),
('max_images_per_product', '10', 'Máximo de imágenes por producto', 'number', 1);


-- Tabla: SKUs de Producto (Unidad Vendible/Inventariable)


DROP TABLE IF EXISTS mod_pim_skus;
CREATE TABLE IF NOT EXISTS mod_pim_skus (
    `mod_ps_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del SKU / Variante',
    `mod_ps_pp_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del producto base (mod_pp_id)',
    `mod_ps_vv_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del vendedor que ofrece este SKU (mod_vv_id)',
    `mod_ps_vendor_sku` VARCHAR(100) NOT NULL COMMENT 'Código de referencia interno del vendor para este SKU',
    `mod_ps_price` DECIMAL(15, 4) NOT NULL COMMENT 'Precio de venta actual del SKU',
    `mod_ps_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_ps_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
    `mod_ps_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)',
    UNIQUE KEY idx_vendor_sku (`mod_ps_vv_id`, `mod_ps_vendor_sku`)
) ENGINE=InnoDB COMMENT='Variantes de producto - Unidad Transaccional/Inventario';

-- 2.2.2. EAV Model for Dynamic Attributes
-- Tabla: Definición de Atributos (EAV)
DROP TABLE IF EXISTS mod_pim_attributes;
CREATE TABLE IF NOT EXISTS mod_pim_attributes (
    `mod_pa_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del atributo',
    `mod_pa_code` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Código interno del atributo (Ej: COLOR, RAM, MATERIAL)',
    `mod_pa_name` VARCHAR(100) NOT NULL COMMENT 'Nombre del atributo',
    `mod_pa_data_type` ENUM('TEXT', 'NUMERIC', 'BOOLEAN', 'DATE') NOT NULL COMMENT 'Tipo de dato del valor almacenado',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Definición de atributos dinámicos (EAV)';

-- Tabla: Mapeo de Atributos por Categoría
DROP TABLE IF EXISTS mod_pim_category_attribute_map;
CREATE TABLE IF NOT EXISTS mod_pim_category_attribute_map (
    `mod_pcam_pc_id` INT UNSIGNED NOT NULL COMMENT 'ID de la categoría (mod_pc_id)',
    `mod_pcam_pa_id` INT UNSIGNED NOT NULL COMMENT 'ID del atributo (mod_pa_id)',
    `mod_pcam_is_required` TINYINT(1) DEFAULT 0 COMMENT 'Indica si este atributo es obligatorio para la categoría',
    PRIMARY KEY (`mod_pcam_pc_id`, `mod_pcam_pa_id`)
) ENGINE=InnoDB COMMENT='Mapeo de qué atributos aplican a qué categorías';

-- Tabla: Valores de Atributo de Producto (EAV Core)
DROP TABLE IF EXISTS mod_pim_attribute_values;
CREATE TABLE IF NOT EXISTS mod_pim_attribute_values (
    `mod_pv_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del valor de atributo',
    `mod_pv_ps_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del SKU (mod_ps_id)',
    `mod_pv_pa_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del atributo (mod_pa_id)',
    `mod_pv_text_value` VARCHAR(512) COMMENT 'Valor del atributo si es de tipo TEXT',
    `mod_pv_numeric_value` DECIMAL(15, 4) COMMENT 'Valor del atributo si es de tipo NUMERIC',
    `mod_pv_unit_of_measure` VARCHAR(20) COMMENT 'Unidad de medida (e.g., "GB", "cm")',
    INDEX idx_sku_attr (`mod_pv_ps_id`, `mod_pv_pa_id`),
    INDEX idx_attr_text (`mod_pv_pa_id`, `mod_pv_text_value`(255))
) ENGINE=InnoDB COMMENT='Valores de atributos dinámicos (EAV) para cada SKU';


-- 2.4. WMS (Warehouse & Inventory Management)
-- Tabla: Almacenes Físicos
DROP TABLE IF EXISTS mod_wms_warehouses;
CREATE TABLE IF NOT EXISTS mod_wms_warehouses (
    `mod_ww_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del almacén',
    `mod_ww_name` VARCHAR(100) NOT NULL COMMENT 'Nombre del almacén o centro de distribución',
    `mod_ww_address_json` JSON COMMENT 'Detalles de ubicación física (lat/long, dirección)',
    `mod_ww_is_fulfillment` TINYINT(1) DEFAULT 1 COMMENT 'Indica si se usa para cumplir órdenes de cliente',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Maestro de Almacenes Físicos';

-- Tabla: Ubicaciones de Almacenamiento (Bins)
DROP TABLE IF EXISTS mod_wms_storage_bins;
CREATE TABLE IF NOT EXISTS mod_wms_storage_bins (
    `mod_wb_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del bin (ubicación granular)',
    `mod_wb_ww_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del almacén (mod_ww_id)',
    `mod_wb_code` VARCHAR(50) NOT NULL COMMENT 'Código de ubicación (Ej: A1-03-B)',
    `mod_wb_zone` VARCHAR(50) COMMENT 'Zona o área dentro del almacén',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Ubicaciones físicas granulares dentro de los almacenes';

-- Tabla: Stock Actual
DROP TABLE IF EXISTS mod_wms_stock;
CREATE TABLE IF NOT EXISTS mod_wms_stock (
    `mod_ws_ps_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del SKU (mod_ps_id)',
    `mod_ws_wb_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del bin (mod_wb_id)',
    `mod_ws_qty` INT NOT NULL DEFAULT 0 COMMENT 'Cantidad de stock disponible en esta ubicación',
    `mod_ws_last_updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha y hora de la última actualización de cantidad',
    PRIMARY KEY (`mod_ws_ps_id`, `mod_ws_wb_id`)
) ENGINE=InnoDB COMMENT='Balance de Stock actual por SKU y Ubicación (Derivado del Ledger)';

-- Tabla: Ledger de Movimientos de Inventario (Inmutable)
DROP TABLE IF EXISTS mod_wms_movement_log;
CREATE TABLE IF NOT EXISTS mod_wms_movement_log (
    `mod_wm_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del movimiento de inventario',
    `mod_wm_ps_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del SKU (mod_ps_id)',
    `mod_wm_transaction_ref_id` BIGINT UNSIGNED COMMENT 'Referencia a Order_Item, Return, or Purchase_Order',
    `mod_wm_type` ENUM('RECEIVE', 'PICK', 'ADJUSTMENT', 'TRANSFER_IN', 'TRANSFER_OUT', 'RETURN_RESTOCK') NOT NULL COMMENT 'Tipo de evento que causó el cambio de stock',
    `mod_wm_from_bin_id` INT UNSIGNED COMMENT 'Ubicación (bin) de origen (NULL si es recepción)',
    `mod_wm_to_bin_id` INT UNSIGNED COMMENT 'Ubicación (bin) de destino (NULL si es consumo/pick)',
    `mod_wm_qty_change` INT NOT NULL COMMENT 'Cantidad neta que afecta a la ubicación de destino (+/-)',
    `mod_wm_he_id` INT UNSIGNED COMMENT 'ID del empleado responsable (para auditoría)',
    `mod_wm_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Momento exacto del movimiento',
    INDEX idx_sku_timestamp (`mod_wm_ps_id`, `mod_wm_timestamp`)
) ENGINE=InnoDB COMMENT='Ledger inmutable de movimientos de stock para trazabilidad y auditoría';


-- 2.3. OMS (Order Management System)
-- Tabla: Órdenes de Cliente (Cabecera)
DROP TABLE IF EXISTS mod_oms_orders;
CREATE TABLE IF NOT EXISTS mod_oms_orders (
    `mod_oo_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la orden de cliente',
    `mod_oo_cc_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del cliente (mod_cc_id)',
    `mod_oo_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha y hora de creación de la orden',
    `mod_oo_shipping_addr_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID de la dirección de envío del cliente',
    `mod_oo_total_amount` DECIMAL(15, 4) NOT NULL COMMENT 'Monto total de la orden',
    `mod_oo_status` ENUM('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED', 'REFUNDED') NOT NULL COMMENT 'Estado actual de la orden',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización'
) ENGINE=InnoDB COMMENT='Cabecera de la Orden de Cliente';

-- Tabla: Partidas de la Orden (Items)
DROP TABLE IF EXISTS mod_oms_order_items;
CREATE TABLE IF NOT EXISTS mod_oms_order_items (
    `mod_oi_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la partida/item de la orden',
    `mod_oi_oo_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la orden (mod_oo_id)',
    `mod_oi_ps_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del SKU (mod_ps_id)',
    `mod_oi_vv_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del vendedor responsable',
    `mod_oi_quantity` INT NOT NULL COMMENT 'Cantidad de este SKU comprado',
    `mod_oi_price_snap` DECIMAL(15, 4) NOT NULL COMMENT 'Precio unitario inmutable al momento de la compra (Snapshot)',
    `mod_oi_commission_rule_id` INT UNSIGNED COMMENT 'ID de la regla de comisión aplicada',
    `mod_oi_commission_calc` DECIMAL(15, 4) COMMENT 'Monto de la comisión calculado para este item',
    `mod_oi_status` ENUM('PENDING', 'FULFILLED', 'CANCELLED', 'RETURNED') NOT NULL COMMENT 'Estado específico de este item',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Detalle de las partidas de la Orden (Incluye Snapshot)';

-- Tabla: Unidades de Cumplimiento (Fulfillments)
DROP TABLE IF EXISTS mod_oms_fulfillments;
CREATE TABLE IF NOT EXISTS mod_oms_fulfillments (
    `mod_of_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la unidad de cumplimiento',
    `mod_of_oi_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la partida de orden (mod_oi_id)',
    `mod_of_ww_id` INT UNSIGNED COMMENT 'ID del almacén asignado para fulfillment (si aplica WMS interno)',
    `mod_of_status` ENUM('PENDING', 'PICKING', 'PACKED', 'SHIPPED') NOT NULL COMMENT 'Estado logístico de esta unidad',
    `mod_of_date_assigned` DATETIME COMMENT 'Fecha de asignación al almacén/vendor',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Unidad de Cumplimiento (una por item/vendor)';

-- Tabla: Envíos y Tracking
DROP TABLE IF EXISTS mod_oms_shipments;
CREATE TABLE IF NOT EXISTS mod_oms_shipments (
    `mod_os_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del envío físico',
    `mod_os_of_id` BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Referencia al ID de la unidad de cumplimiento (mod_of_id)',
    `mod_os_tracking_code` VARCHAR(100) UNIQUE NOT NULL COMMENT 'Código de seguimiento del courier',
    `mod_os_courier` VARCHAR(50) COMMENT 'Nombre del servicio de paquetería (Ej: FedEx, DHL)',
    `mod_os_cost` DECIMAL(10, 2) COMMENT 'Costo de envío',
    `mod_os_delivery_date_est` DATE COMMENT 'Fecha estimada de entrega',
    `mod_os_status` VARCHAR(50) NOT NULL COMMENT 'Último estado de tracking reportado',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Entidad física del Envío y Tracking';

-- Tabla: Devoluciones (Returns)
DROP TABLE IF EXISTS mod_oms_returns;
CREATE TABLE IF NOT EXISTS mod_oms_returns (
    `mod_or_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la solicitud de devolución',
    `mod_or_oi_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la partida de orden a devolver (mod_oi_id)',
    `mod_or_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de solicitud de la devolución',
    `mod_or_reason` VARCHAR(100) NOT NULL COMMENT 'Motivo de la devolución',
    `mod_or_quantity` INT NOT NULL COMMENT 'Cantidad de ítems a devolver',
    `mod_or_status` ENUM('REQUESTED', 'APPROVED', 'SHIPPED', 'RECEIVED', 'REJECTED', 'REFUNDED') NOT NULL COMMENT 'Estado de la devolución',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Gestión de Solicitudes de Devolución (Returns)';

-- Tabla: Reembolsos (Refunds)
DROP TABLE IF EXISTS mod_oms_refunds;
CREATE TABLE IF NOT EXISTS mod_oms_refunds (
    `mod_orf_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del reembolso financiero',
    `mod_orf_or_id` BIGINT UNSIGNED UNIQUE NOT NULL COMMENT 'Referencia al ID de la devolución (mod_or_id)',
    `mod_orf_amount` DECIMAL(15, 4) NOT NULL COMMENT 'Monto total reembolsado al cliente',
    `mod_orf_gt_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la transacción GL (mod_gt_id) para el asiento contable',
    `mod_orf_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de ejecución del reembolso',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Transacción financiera de Reembolso';


-- 2.5. FSS (Financial Settlement System)
-- Tabla: Sub-Ledger de Movimientos de Saldo del Vendor
DROP TABLE IF EXISTS mod_fss_vendor_balance;
CREATE TABLE IF NOT EXISTS mod_fss_vendor_balance (
    `mod_fvb_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del movimiento de saldo',
    `mod_fvb_vv_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del vendedor (mod_vv_id)',
    `mod_fvb_source_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID de la entidad origen (e.g., Order_Item_ID, Refund_ID)',
    `mod_fvb_source_type` ENUM('SALE', 'COMMISSION', 'REFUND', 'PAYOUT', 'ADJUSTMENT') NOT NULL COMMENT 'Tipo de evento que afecta el saldo',
    `mod_fvb_signed_amount` DECIMAL(15, 4) NOT NULL COMMENT 'Monto con signo (+ a favor del vendor, - en contra)',
    `mod_fvb_fp_id` BIGINT UNSIGNED COMMENT 'ID del Payout en el que este movimiento fue liquidado (NULL si es pendiente)',
    `mod_fvb_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Momento del evento',
    INDEX idx_vendor_payout (`mod_fvb_vv_id`, `mod_fvb_fp_id`)
) ENGINE=InnoDB COMMENT='Sub-Ledger de movimientos de saldo pendiente/liquidado del Vendor';

-- Tabla: Pagos Ejecutados a Vendors (Payouts)
DROP TABLE IF EXISTS mod_fss_payouts;
CREATE TABLE IF NOT EXISTS mod_fss_payouts (
    `mod_fp_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del pago a vendor',
    `mod_fp_vv_id` INT UNSIGNED NOT NULL COMMENT 'Referencia al ID del vendedor (mod_vv_id)',
    `mod_fp_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de ejecución del pago',
    `mod_fp_amount` DECIMAL(15, 4) NOT NULL COMMENT 'Monto total pagado',
    `mod_fp_status` ENUM('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED') NOT NULL COMMENT 'Estado de la transferencia bancaria',
    `mod_fp_gt_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID de la transacción GL (mod_gt_id) para el egreso contable',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro'
) ENGINE=InnoDB COMMENT='Transferencias financieras ejecutadas a Vendors';


-- 2.6. CCM (Customer & Communication Management)
-- Tabla: Reseñas de Producto (Reviews)
DROP TABLE IF EXISTS mod_ccm_reviews;
CREATE TABLE IF NOT EXISTS mod_ccm_reviews (
    `mod_cr_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único de la reseña',
    `mod_cr_ps_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del SKU reseñado (mod_ps_id)',
    `mod_cr_cc_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del cliente (mod_cc_id)',
    `mod_cr_rating` TINYINT(1) CHECK (`mod_cr_rating` BETWEEN 1 AND 5) COMMENT 'Puntuación (Rating) de 1 a 5',
    `mod_cr_text` TEXT COMMENT 'Contenido de la reseña',
    `mod_cr_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de publicación',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del registro',
    `mod_status` INT DEFAULT '1' COMMENT 'Estado (1=aprobada/visible, 0=moderada)'
) ENGINE=InnoDB COMMENT='Reseñas y ratings de productos';

-- Tabla: Tickets de Soporte
DROP TABLE IF EXISTS mod_ccm_tickets;
CREATE TABLE IF NOT EXISTS mod_ccm_tickets (
    `mod_ct_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del ticket de soporte',
    `mod_ct_cc_id` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al ID del cliente (mod_cc_id)',
    `mod_ct_he_id` INT UNSIGNED COMMENT 'Referencia al ID del empleado/agente asignado (mod_he_id)',
    `mod_ct_category` VARCHAR(50) COMMENT 'Categoría del problema (Ej: Logística, Facturación)',
    `mod_ct_subject` VARCHAR(255) NOT NULL COMMENT 'Asunto del ticket',
    `mod_ct_status` ENUM('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED') NOT NULL COMMENT 'Estado del ticket',
    `mod_ct_priority` ENUM('LOW', 'MEDIUM', 'HIGH') NOT NULL COMMENT 'Prioridad del ticket',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de apertura',
    `mod_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización'
) ENGINE=InnoDB COMMENT='Sistema de gestión de tickets de soporte';

-- Tabla: Mensajes/Chats (Comunicación cliente-vendor)
DROP TABLE IF EXISTS mod_ccm_messages;
CREATE TABLE IF NOT EXISTS mod_ccm_messages (
    `mod_cm_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único del mensaje',
    `mod_cm_thread_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID de la conversación a la que pertenece',
    `mod_cm_sender_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del remitente (Cliente o Vendor/Empleado)',
    `mod_cm_receiver_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID del receptor (Cliente o Vendor/Empleado)',
    `mod_cm_content` TEXT NOT NULL COMMENT 'Contenido del mensaje',
    `mod_cm_sent_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Marca de tiempo del envío',
    `mod_cm_is_read` TINYINT(1) DEFAULT 0 COMMENT 'Indicador de si el mensaje ha sido leído'
) ENGINE=InnoDB COMMENT='Registro detallado de mensajes/chats';

-- #######################################################################
-- # III. CAPAS DE BUSINESS INTELLIGENCE (BI)
-- #######################################################################

-- Dimension de Tiempo
DROP TABLE IF EXISTS mod_bi_dim_date;
CREATE TABLE IF NOT EXISTS mod_bi_dim_date (
    `mod_bd_key` INT PRIMARY KEY COMMENT 'Clave subrogada (YYYYMMDD)',
    `mod_bd_full_date` DATE NOT NULL COMMENT 'Fecha completa',
    `mod_bd_day_of_week` VARCHAR(10) COMMENT 'Día de la semana',
    `mod_bd_month_name` VARCHAR(10) COMMENT 'Nombre del mes',
    `mod_bd_year` INT COMMENT 'Año'
) ENGINE=InnoDB COMMENT='Dimensión de Tiempo (Usada en Fact Tables)';

-- Dimensión de Vendedor
DROP TABLE IF EXISTS mod_bi_dim_vendor;
CREATE TABLE IF NOT EXISTS mod_bi_dim_vendor (
    `mod_bv_key` INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Clave subrogada para la Dimensión de Vendor',
    `mod_bv_vv_id_oltp` INT UNSIGNED UNIQUE NOT NULL COMMENT 'ID del Vendor en el sistema transaccional (mod_vv_id)',
    `mod_bv_name` VARCHAR(255) NOT NULL COMMENT 'Nombre del Vendor',
    `mod_bv_current_score` DECIMAL(5, 4) COMMENT 'Snapshot del Scorecard de rendimiento (SCD Type 1)',
    `mod_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de carga inicial'
) ENGINE=InnoDB COMMENT='Dimensión de Vendors (Analytics)';

-- Fact Table: Ventas del Marketplace
DROP TABLE IF EXISTS mod_bi_fact_sales;
CREATE TABLE IF NOT EXISTS mod_bi_fact_sales (
    `mod_bfs_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Clave primaria subrogada para rendimiento OLAP',
    `mod_bfs_bd_key` INT NOT NULL COMMENT 'Referencia a Dim_Date (mod_bd_key)',
    `mod_bfs_bv_key` INT NOT NULL COMMENT 'Referencia a Dim_Vendor (mod_bv_key)',
    `mod_bfs_cc_key` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia a Dim_Customer/OLTP ID',
    `mod_bfs_ps_key` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia a Dim_Product/SKU (ID del SKU)',
    `mod_bfs_oi_id_oltp` BIGINT UNSIGNED NOT NULL COMMENT 'Referencia al Order_Item original (mod_oi_id) para Drill-down',
    `mod_bfs_quantity` INT NOT NULL COMMENT 'Cantidad vendida',
    `mod_bfs_gmv` DECIMAL(15, 4) NOT NULL COMMENT 'Gross Merchandise Value (Valor Bruto de la venta)',
    `mod_bfs_commission` DECIMAL(15, 4) NOT NULL COMMENT 'Comisión retenida por el Marketplace',
    `mod_bfs_net_margin` DECIMAL(15, 4) COMMENT 'Margen de ganancia neto',
    `mod_bfs_fulfillment_days` INT COMMENT 'Días transcurridos hasta el envío (KPI)',
    `mod_bfs_is_cancelled` TINYINT(1) DEFAULT 0 COMMENT 'Flag: 1 si la venta fue cancelada o devuelta',
    INDEX idx_date_vendor (`mod_bfs_bd_key`, `mod_bfs_bv_key`)
) ENGINE=InnoDB COMMENT='Fact Table para análisis de GMV y Ventas del Marketplace';