wecome-middleware/scripts/init.sql

-- WeCom Middleware 数据库初始化脚本
-- 专注于核心需求：企业微信Bot配置和OpenClaw配对

-- 创建数据库
CREATE DATABASE IF NOT EXISTS wecom_middleware CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wecom_middleware;

-- 企业微信Bot配置表
CREATE TABLE IF NOT EXISTS wecom_bots (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL COMMENT 'Bot名称',
    bot_id VARCHAR(100) NOT NULL UNIQUE COMMENT '企业微信Bot ID',
    secret VARCHAR(255) NOT NULL COMMENT '企业微信Bot Secret',
    dm_policy VARCHAR(50) NOT NULL DEFAULT 'pairing' COMMENT '配对策略: pairing/allowlist/open/disabled',
    allow_from TEXT COMMENT '允许列表(JSON数组)，dm_policy=allowlist时使用',
    status VARCHAR(20) NOT NULL DEFAULT 'inactive' COMMENT '状态: active/inactive/error',
    webhook_url VARCHAR(500) COMMENT 'Webhook地址',
    openclaw_gateway_url VARCHAR(500) DEFAULT 'ws://localhost:18789' COMMENT 'OpenClaw网关地址',
    openclaw_gateway_token VARCHAR(500) COMMENT 'OpenClaw网关令牌',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_dm_policy (dm_policy)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='企业微信Bot配置';

-- OpenClaw配对请求表
CREATE TABLE IF NOT EXISTS openclaw_pairing_requests (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    request_id VARCHAR(100) NOT NULL UNIQUE COMMENT '请求ID',
    node_id VARCHAR(100) NOT NULL COMMENT '节点ID',
    node_name VARCHAR(100) COMMENT '节点名称',
    node_type VARCHAR(50) COMMENT '节点类型',
    node_ip VARCHAR(50) COMMENT '节点IP',
    node_version VARCHAR(50) COMMENT '节点版本',
    bot_id VARCHAR(100) COMMENT '关联的Bot ID',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/approved/rejected',
    pairing_mode VARCHAR(50) COMMENT '使用的配对方式',
    request_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
    resolve_time TIMESTAMP NULL COMMENT '处理时间',
    resolver VARCHAR(100) COMMENT '处理人',
    remark TEXT COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_node_id (node_id),
    INDEX idx_bot_id (bot_id),
    INDEX idx_request_time (request_time),
    FOREIGN KEY (bot_id) REFERENCES wecom_bots(bot_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OpenClaw配对请求';

-- 消息记录表
CREATE TABLE IF NOT EXISTS message_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message_id VARCHAR(100) NOT NULL UNIQUE COMMENT '消息ID',
    bot_id VARCHAR(100) NOT NULL COMMENT 'Bot ID',
    direction VARCHAR(20) NOT NULL COMMENT '方向: inbound/outbound',
    message_type VARCHAR(50) NOT NULL COMMENT '消息类型: text/image/file/voice',
    content TEXT COMMENT '消息内容',
    sender VARCHAR(200) COMMENT '发送方',
    receiver VARCHAR(200) COMMENT '接收方',
    status VARCHAR(20) NOT NULL DEFAULT 'sent' COMMENT '状态: sent/delivered/read/failed',
    error_message TEXT COMMENT '错误信息',
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_bot_id (bot_id),
    INDEX idx_direction (direction),
    INDEX idx_timestamp (timestamp),
    INDEX idx_status (status),
    FOREIGN KEY (bot_id) REFERENCES wecom_bots(bot_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息记录';

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    config_type VARCHAR(50) DEFAULT 'string' COMMENT '配置类型: string/number/boolean/json',
    description VARCHAR(200) COMMENT '描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置';

-- 操作日志表
CREATE TABLE IF NOT EXISTS operation_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    operator VARCHAR(100) COMMENT '操作人',
    module VARCHAR(50) NOT NULL COMMENT '模块: wecom/openclaw/system',
    action VARCHAR(100) NOT NULL COMMENT '操作',
    description TEXT COMMENT '描述',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    user_agent TEXT COMMENT '用户代理',
    request_params TEXT COMMENT '请求参数',
    response_result TEXT COMMENT '响应结果',
    error_message TEXT COMMENT '错误信息',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_operator (operator),
    INDEX idx_module (module),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志';

-- 插入默认配置
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('system.name', 'WeCom Middleware', 'string', '系统名称'),
('system.version', '1.0.0', 'string', '系统版本'),
('log.level', 'info', 'string', '日志级别'),
('openclaw.gateway.url', 'ws://localhost:18789', 'string', 'OpenClaw网关地址'),
('openclaw.pairing.timeout', '300', 'number', '配对请求超时时间(秒)'),
('wecom.message.retry.count', '3', 'number', '消息重试次数'),
('wecom.message.timeout', '30', 'number', '消息发送超时时间(秒)');

-- 插入测试Bot数据
INSERT INTO wecom_bots (name, bot_id, secret, dm_policy, allow_from, status) VALUES
('默认配对Bot', 'default_bot_001', 'secret_001', 'pairing', NULL, 'inactive'),
('白名单Bot', 'allowlist_bot_002', 'secret_002', 'allowlist', '["user1", "user2", "admin"]', 'inactive'),
('开放Bot', 'open_bot_003', 'secret_003', 'open', NULL, 'inactive'),
('禁用Bot', 'disabled_bot_004', 'secret_004', 'disabled', NULL, 'inactive');

-- 插入测试配对请求
INSERT INTO openclaw_pairing_requests (request_id, node_id, node_name, node_type, node_ip, node_version, bot_id, status) VALUES
('req_001', 'node_001', '开发节点', 'development', '192.168.1.100', 'v1.0.0', 'test_bot_001', 'pending'),
('req_002', 'node_002', '测试节点', 'testing', '192.168.1.101', 'v1.0.0', 'auto_bot_002', 'approved');

-- 插入测试消息记录
INSERT INTO message_records (message_id, bot_id, direction, message_type, content, sender, receiver, status) VALUES
('msg_001', 'test_bot_001', 'inbound', 'text', '你好，测试消息', 'user_001', 'test_bot_001', 'delivered'),
('msg_002', 'test_bot_001', 'outbound', 'text', '收到，这是回复', 'test_bot_001', 'user_001', 'sent');

-- 插入测试操作日志
INSERT INTO operation_logs (operator, module, action, description, ip_address) VALUES
('admin', 'system', '系统启动', 'WeCom Middleware系统启动完成', '127.0.0.1'),
('admin', 'wecom', '添加Bot', '添加测试Bot: test_bot_001', '127.0.0.1');