-- Migration: Create update system tables
-- Date: 2024-11-24
-- Description: Creates tables for online update system including versions, logs, and configuration

-- 更新版本表
CREATE TABLE IF NOT EXISTS update_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    version TEXT NOT NULL UNIQUE,
    release_date DATETIME NOT NULL,
    update_log TEXT DEFAULT '',
    package_url TEXT DEFAULT '',
    package_size INTEGER DEFAULT 0,
    package_hash TEXT DEFAULT '',
    is_current BOOLEAN DEFAULT 0,
    is_latest BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_update_versions_version ON update_versions(version);
CREATE INDEX IF NOT EXISTS idx_update_versions_is_current ON update_versions(is_current);
CREATE INDEX IF NOT EXISTS idx_update_versions_is_latest ON update_versions(is_latest);

-- 更新操作日志表
CREATE TABLE IF NOT EXISTS update_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    operation TEXT NOT NULL,
    version_from TEXT DEFAULT '',
    version_to TEXT DEFAULT '',
    status TEXT NOT NULL,
    error_message TEXT DEFAULT '',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_update_logs_user_id ON update_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_update_logs_status ON update_logs(status);
CREATE INDEX IF NOT EXISTS idx_update_logs_created_at ON update_logs(created_at);

-- 更新配置表
CREATE TABLE IF NOT EXISTS update_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    update_source_url TEXT NOT NULL,
    is_primary BOOLEAN DEFAULT 1,
    enabled BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 触发器：自动更新 updated_at
CREATE TRIGGER IF NOT EXISTS update_versions_updated_at
    AFTER UPDATE ON update_versions
    BEGIN
        UPDATE update_versions SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

CREATE TRIGGER IF NOT EXISTS update_config_updated_at
    AFTER UPDATE ON update_config
    BEGIN
        UPDATE update_config SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

-- 插入默认更新源配置（宝塔服务器）
INSERT OR IGNORE INTO update_config (update_source_url, is_primary, enabled)
VALUES ('http://updates.lingyaoai.com/updates/api', 1, 1);

SELECT 'Migration completed: Update system tables created' as status;
