ng-webot

-- iLink Bot 平台数据库 Schema (SQLite)

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(64) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL DEFAULT '',
    email_verified INTEGER NOT NULL DEFAULT 0,
    nickname VARCHAR(64) NOT NULL DEFAULT '',
    is_admin INTEGER NOT NULL DEFAULT 0,
    points INTEGER NOT NULL DEFAULT 0,
    checkin_at INTEGER NOT NULL DEFAULT 0,
    checkin_streak INTEGER NOT NULL DEFAULT 0,
    member_type VARCHAR(16) NOT NULL DEFAULT 'none',
    member_until INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS bots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    bot_code VARCHAR(32) NOT NULL,
    name VARCHAR(128),
    login_status VARCHAR(16) NOT NULL DEFAULT 'none',
    bot_token VARCHAR(255),
    qrcode VARCHAR(255),
    qr_content TEXT,
    base_url VARCHAR(255),
    wechat_uin VARCHAR(64),
    context_token TEXT,
    upd_buf TEXT,
    typing_ticket VARCHAR(255),
    bind_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER NOT NULL,
    direction VARCHAR(8) NOT NULL,
    peer_id VARCHAR(128),
    content TEXT,
    msg_type VARCHAR(16) NOT NULL DEFAULT 'text',
    context_token TEXT,
    status VARCHAR(16) DEFAULT 'ok',
    error_msg TEXT,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_messages_bot_dir ON messages(bot_id, direction);
CREATE INDEX IF NOT EXISTS idx_messages_peer ON messages(bot_id, peer_id);
CREATE INDEX IF NOT EXISTS idx_messages_ct ON messages(bot_id, context_token);
CREATE INDEX IF NOT EXISTS idx_messages_created ON messages(bot_id, created_at);

CREATE TABLE IF NOT EXISTS settings (
    key VARCHAR(128) NOT NULL,
    value TEXT,
    UNIQUE (key)
);

CREATE TABLE IF NOT EXISTS plugins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER NOT NULL,
    market_id VARCHAR(64) NOT NULL,
    name VARCHAR(64),
    version VARCHAR(16),
    description TEXT,
    author VARCHAR(64),
    entry VARCHAR(255),
    enabled INTEGER NOT NULL DEFAULT 1,
    installed_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS plugin_market (
    market_id VARCHAR(64) NOT NULL,
    price_week INTEGER NOT NULL DEFAULT 0,
    price_month INTEGER NOT NULL DEFAULT 0,
    price_year INTEGER NOT NULL DEFAULT 0,
    free_for_member INTEGER NOT NULL DEFAULT 1,
    updated_at INTEGER,
    listed INTEGER NOT NULL DEFAULT 1,
    UNIQUE (market_id)
);

CREATE TABLE IF NOT EXISTS plugin_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER NOT NULL,
    plugin_id INTEGER NOT NULL,
    type VARCHAR(16) NOT NULL,
    match VARCHAR(255) NOT NULL,
    reply TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS plugin_rss (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER NOT NULL,
    title VARCHAR(255),
    feed_url TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS plugin_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER NOT NULL,
    plugin_id VARCHAR(64) NOT NULL,
    config_key VARCHAR(128) NOT NULL,
    config_value TEXT
);

CREATE TABLE IF NOT EXISTS cards (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code VARCHAR(32) NOT NULL,
    type VARCHAR(16) NOT NULL,
    value INTEGER NOT NULL DEFAULT 0,
    used_by INTEGER NOT NULL DEFAULT 0,
    used_at INTEGER NOT NULL DEFAULT 0,
    created_by INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL,
    UNIQUE (code)
);

CREATE TABLE IF NOT EXISTS verify_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email VARCHAR(255) NOT NULL,
    code VARCHAR(8) NOT NULL,
    used INTEGER NOT NULL DEFAULT 0,
    expires_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS checkins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    points_earned INTEGER NOT NULL,
    streak INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
);

-- 用户自定义技能（user 级，跨 bot 共享）
CREATE TABLE IF NOT EXISTS custom_skills (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name VARCHAR(128) NOT NULL,
    icon VARCHAR(8) NOT NULL DEFAULT '',
    description TEXT NOT NULL DEFAULT '',
    prompt TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    updated_at INTEGER NOT NULL DEFAULT 0
);

-- 模型调用统计（chat / vision / transcribe 每次调用一条记录）
CREATE TABLE IF NOT EXISTS model_calls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bot_id INTEGER,
    peer_id VARCHAR(128),
    model VARCHAR(64) DEFAULT '',
    mode VARCHAR(16) DEFAULT 'chat',
    prompt_tokens INTEGER DEFAULT 0,
    completion_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    ttft_ms INTEGER DEFAULT 0,
    latency_ms INTEGER DEFAULT 0,
    success INTEGER DEFAULT 1,
    error_msg TEXT,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_model_calls_created ON model_calls(created_at);