VPSHUB/app/db_helper.php

<?php
require_once __DIR__ . '/logger.php';

class DBHelper {
    private $db;
    private $dbPath;
    
    public function __construct($dbPath) {
        $this->dbPath = $dbPath;
        
        $dir = dirname($dbPath);
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }
        
        try {
            $this->db = new PDO("sqlite:" . $dbPath);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            throw new Exception("数据库连接失败: " . $e->getMessage());
        }
    }
    
    /**
     * 执行查询并返回所有结果
     */
    public function query($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            Logger::error("数据库查询错误: " . $e->getMessage() . " | SQL: " . $sql, 'DBHelper::query');
            return [];
        }
    }
    
    /**
     * 执行查询并返回单条结果
     */
    public function queryOne($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetch();
        } catch (PDOException $e) {
            Logger::error("数据库查询错误: " . $e->getMessage() . " | SQL: " . $sql, 'DBHelper::queryOne');
            return false;
        }
    }
    
    /**
     * 执行插入、更新、删除操作
     */
    public function execute($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            return $stmt->execute($params);
        } catch (PDOException $e) {
            Logger::error("数据库执行错误: " . $e->getMessage() . " | SQL: " . $sql, 'DBHelper::execute');
            return false;
        }
    }
    
    /**
     * 插入数据并返回最后插入的ID
     */
    public function insert($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            return $this->db->lastInsertId();
        } catch (PDOException $e) {
            Logger::error("数据库插入错误: " . $e->getMessage() . " | SQL: " . $sql, 'DBHelper::insert');
            return false;
        }
    }
    
    public function beginTransaction() {
        return $this->db->beginTransaction();
    }
    
    public function commit() {
        return $this->db->commit();
    }
    
    public function rollBack() {
        return $this->db->rollBack();
    }
    
    public function getConnection() {
        return $this->db;
    }
}

// 便捷函数：获取vps.db的DBHelper实例
function getVpsDB() {
    static $db = null;
    if ($db === null) {
        $dbPath = __DIR__ . '/db/vps.db';
        $db = new DBHelper($dbPath);
        initVpsTables($db);
    }
    return $db;
}

// 便捷函数：获取vpslist.db的DBHelper实例
function getVpsListDB() {
    static $db = null;
    if ($db === null) {
        $dbPath = __DIR__ . '/db/vpslist.db';
        $db = new DBHelper($dbPath);
        initVpsListTables($db);
    }
    return $db;
}

// 便捷函数：获取status.db的DBHelper实例
function getStatusDB() {
    static $db = null;
    if ($db === null) {
        $dbPath = __DIR__ . '/db/status.db';
        $db = new DBHelper($dbPath);
        initStatusTables($db);
    }
    return $db;
}

/**
 * 初始化vps.db表结构
 */
function initVpsTables($db) {
    $db->execute("
        CREATE TABLE IF NOT EXISTS configs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            api_label TEXT NOT NULL UNIQUE,
            site_type TEXT NOT NULL,
            site_url TEXT,
            account TEXT NOT NULL,
            api_key TEXT NOT NULL,
            auto_monitor BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(site_url, account)
        )
    ");
}

/**
 * 初始化vpslist.db表结构
 */
function initVpsListTables($db) {
    $db->execute("
        CREATE TABLE IF NOT EXISTS vps_list (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            config_id INTEGER NOT NULL,
            vps_id INTEGER NOT NULL,
            domain TEXT,
            ip_address TEXT,
            product_name TEXT,
            cpu_cores INTEGER,
            memory_size TEXT,
            disk_size TEXT,
            bandwidth TEXT,
            os_type TEXT,
            status TEXT,
            amount TEXT,
            nextduedate INTEGER,
            section BOOLEAN DEFAULT 0,
            last_check TIMESTAMP,
            FOREIGN KEY (config_id) REFERENCES configs(id)
        )
    ");
    
    $db->execute('CREATE INDEX IF NOT EXISTS idx_vps_config ON vps_list(config_id)');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_vps_vps_id ON vps_list(vps_id)');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_vps_unique ON vps_list(vps_id, ip_address)');
}

/**
 * 初始化status.db表结构
 */
function initStatusTables($db) {
    // Ping状态记录表
    $db->execute("
        CREATE TABLE IF NOT EXISTS ping_status (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            vps_id INTEGER NOT NULL,
            target TEXT NOT NULL,
            status TEXT NOT NULL,
            latency_ms REAL,
            check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
    
    // VPS摘要统计表
    $db->execute("
        CREATE TABLE IF NOT EXISTS vps_summary (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            vps_id INTEGER NOT NULL,
            date DATE NOT NULL,
            avg_latency_ms REAL,
            max_latency_ms REAL,
            min_latency_ms REAL,
            count_under_100 INTEGER DEFAULT 0,
            count_100_to_300 INTEGER DEFAULT 0,
            count_300_to_500 INTEGER DEFAULT 0,
            count_abnormal INTEGER DEFAULT 0,
            availability TEXT,
            UNIQUE(vps_id, date)
        )
    ");
    
    // 创建索引
    $db->execute('CREATE INDEX IF NOT EXISTS idx_ping_vps ON ping_status(vps_id)');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_ping_time ON ping_status(check_time)');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_summary_vps ON vps_summary(vps_id)');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_summary_date ON vps_summary(date)');
}
?>