-- Active: 1742323104246@@192.168.0.177@3306@validme_app_1
USE nucleo_base;

DROP TABLE IF EXISTS `mod_validme_applicants`;

CREATE TABLE `mod_validme_applicants` (
  `mod_va_id` int NOT NULL AUTO_INCREMENT,
  `mod_va_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `mod_va_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci UNIQUE,
  `mod_va_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `mod_va_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'individual',
  `mod_va_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mod_va_date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_va_date_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mod_va_ent_id` int DEFAULT NULL,
  `mod_va_status` int DEFAULT '1',
  PRIMARY KEY (`mod_va_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `mod_validme_applicants` (`mod_va_name`, `mod_va_email`, `mod_va_phone`, `mod_va_type`, `mod_va_ent_id`) VALUES 
('Jess Fido', 'jess.fido@example.com', '+1234567890', 'individual', 1),
('Sam Blanc', 'sam.blanc@example.com', '+1234567891', 'individual', 1),
('Kyle Roberts', 'kyle.roberts@example.com', '+1234567892', 'individual', 1);

DROP TABLE IF EXISTS `mod_validme_checks`;

CREATE TABLE `mod_validme_checks` (
  `mod_vc_id` int NOT NULL AUTO_INCREMENT,
  `mod_vc_applicant_id` int NOT NULL,
  `mod_vc_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'background',
  `mod_vc_progress` ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED') NOT NULL DEFAULT 'PENDING',
  `mod_vc_result` ENUM('CLEAR', 'CONSIDER', 'ALERT') DEFAULT NULL,
  `mod_vc_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mod_vc_date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `mod_vc_date_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mod_vc_ent_id` int DEFAULT NULL,
  `mod_vc_status` int DEFAULT '1',
  PRIMARY KEY (`mod_vc_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Registros de ejemplo


INSERT INTO `mod_validme_checks` (`mod_vc_applicant_id`, `mod_vc_type`, `mod_vc_progress`, `mod_vc_result`, `mod_vc_ent_id`) VALUES 
(1, 'background', 'COMPLETED', 'CLEAR', 1),
(2, 'background', 'COMPLETED', 'CONSIDER', 1),
(3, 'background', 'COMPLETED', 'CLEAR', 1);


DROP TABLE IF EXISTS mod_validme_data;

CREATE TABLE IF NOT EXISTS mod_validme_data (
    `mod_vd_id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Identificador único',
    `mod_vd_cen_id` INT NOT NULL COMMENT 'ID de cliente/proveedor console',
    `mod_vd_code_id` VARCHAR(255) NOT NULL COMMENT 'ID de cliente/proveedor API',
    `mod_vd_email` VARCHAR(255) NOT NULL COMMENT 'Correo electrónico administrador',
    `mod_vd_nit` BIGINT(25) NOT NULL COMMENT 'Nit del cliente/proveedor',
    `mod_vd_business_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Razón social',
    `mod_vd_webhookUrl` VARCHAR(255) NOT NULL COMMENT 'URL del webhook',
    `mod_vd_image` TEXT COMMENT 'Imagen asociada',
    `mod_vd_token` TEXT NOT NULL COMMENT 'Token de autenticación',
    `mod_vd_private_key` TEXT NOT NULL COMMENT 'Clave privada',
    `mod_vd_public_key` TEXT NOT NULL COMMENT 'Clave pública',
    `mod_vd_ent_id` INT NOT NULL COMMENT 'ID de la entidad',
    `mod_vd_created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
    `mod_vd_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de actualización',
    `mod_vd_status` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS mod_validme_options;

CREATE TABLE `mod_validme_options` (
  `mod_vo_id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID único de opción',
  `mod_vo_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre de la opción',
  `mod_vo_value` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Valor de la opción',
  `mod_vo_autoload` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'yes' COMMENT 'Autoload',
  `mod_vo_date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `mod_vo_date_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',
  PRIMARY KEY (`mod_vo_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Opciones de Validme';


DROP TABLE IF EXISTS `mod_validme_callback_logs`;

CREATE TABLE `mod_validme_callback_logs` (
  `mod_vcl_id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID único del log',
  `mod_vcl_applicant_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'ID del solicitante relacionado',
  `mod_vcl_applicant_email` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Correo electrónico del solicitante',
  `mod_vcl_applicant_phone_number` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Número de teléfono del solicitante',
  `mod_vcl_organization_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'ID de la organización',
  `mod_vcl_webhook_url` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'URL del webhook',
  `mod_vcl_request` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Datos enviados en la solicitud',
  `mod_vcl_files` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Archivos adjuntos',
  `mod_vcl_response` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Respuesta recibida',
  `mod_vcl_verifications_files` VARCHAR(455) NULL DEFAULT NULL COMMENT 'Archivos adjuntos',
  `mod_vcl_text_payload` VARCHAR(455) NULL DEFAULT NULL COMMENT 'Payload',
  `mod_vcl_status_code` VARCHAR(255) DEFAULT NULL COMMENT 'Código de estado HTTP',
  `mod_vcl_status_type` VARCHAR(255) DEFAULT NULL COMMENT 'Tipo de estado del callback',
  `mod_vcl_code_verification` INT(12) DEFAULT NULL COMMENT 'Código de verificación',
  `mod_vcl_success` TINYINT(1) DEFAULT '0' COMMENT 'Indicador de éxito (1=éxito, 0=error)',
  `mod_vcl_attempts` INT DEFAULT '1' COMMENT 'Número de intentos realizados',
  `mod_vcl_error_message` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Mensaje de error si lo hay',
  `mod_vcl_date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `mod_vcl_date_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',
  `mod_vcl_ent_id` INT DEFAULT NULL COMMENT 'ID de la entidad',
  `mod_vcl_state` INT DEFAULT '1' COMMENT 'Estado (1=activo, 0=inactivo)',
  PRIMARY KEY (`mod_vcl_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Log de callbacks de respuesta';


