编辑代码

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const logger = require('../utils/logger');

const DB_PATH = path.join(__dirname, '..', 'data', 'app.db');

// 确保数据目录存在
const fs = require('fs');
const dataDir = path.dirname(DB_PATH);
if (!fs.existsSync(dataDir)) {
    fs.mkdirSync(dataDir, { recursive: true });
}

let db = null;

// 初始化数据库连接
function getDatabase() {
    if (!db) {
        db = new sqlite3.Database(DB_PATH, (err) => {
            if (err) {
                logger.error('数据库连接失败:', err);
                throw err;
            }
            logger.info('SQLite数据库连接成功');
        });
    }
    return db;
}

// 初始化数据库表
async function initDatabase() {
    const db = getDatabase();
    
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            // 配置表
            db.run(`
                CREATE TABLE IF NOT EXISTS configs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    appid TEXT NOT NULL,
                    appsecret TEXT NOT NULL,
                    interval_minutes INTEGER DEFAULT 60,
                    webhook_url TEXT,
                    is_active BOOLEAN DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // AccessToken记录表
            db.run(`
                CREATE TABLE IF NOT EXISTS access_tokens (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    access_token TEXT NOT NULL,
                    expires_in INTEGER NOT NULL,
                    expire_time DATETIME NOT NULL,
                    config_id INTEGER,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (config_id) REFERENCES configs (id)
                )
            `);

            // 操作日志表
            db.run(`
                CREATE TABLE IF NOT EXISTS operation_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    type TEXT NOT NULL,
                    message TEXT NOT NULL,
                    status TEXT NOT NULL,
                    details TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // Webhook推送记录表
            db.run(`
                CREATE TABLE IF NOT EXISTS webhook_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    webhook_url TEXT NOT NULL,
                    payload TEXT NOT NULL,
                    status_code INTEGER,
                    response_body TEXT,
                    success BOOLEAN DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `, (err) => {
                if (err) {
                    logger.error('创建表失败:', err);
                    reject(err);
                } else {
                    logger.info('数据库表初始化完成');
                    resolve();
                }
            });
        });
    });
}

module.exports = {
    getDatabase,
    initDatabase
};