-- 配置中心标准数据库迁移脚本
-- 包含配置表 + 历史记录表，适合生产环境

-- 创建配置表
CREATE TABLE IF NOT EXISTS configs (
    id TEXT PRIMARY KEY,
    key TEXT NOT NULL UNIQUE,
    value TEXT NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('string', 'number', 'boolean', 'json', 'array')),
    namespace TEXT NOT NULL,
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    is_encrypted INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    created_by TEXT,
    updated_by TEXT
);

-- 创建配置索引
CREATE INDEX IF NOT EXISTS idx_configs_key ON configs(key);
CREATE INDEX IF NOT EXISTS idx_configs_namespace ON configs(namespace);
CREATE INDEX IF NOT EXISTS idx_configs_is_active ON configs(is_active);
CREATE INDEX IF NOT EXISTS idx_configs_created_at ON configs(created_at);

-- 创建配置变更历史表
CREATE TABLE IF NOT EXISTS config_history (
    id TEXT PRIMARY KEY,
    config_id TEXT NOT NULL,
    config_key TEXT NOT NULL,
    old_value TEXT,
    new_value TEXT,
    action TEXT NOT NULL CHECK (action IN ('CREATE', 'UPDATE', 'DELETE')),
    changed_at TEXT NOT NULL DEFAULT (datetime('now')),
    changed_by TEXT
);

-- 创建配置历史索引
CREATE INDEX IF NOT EXISTS idx_config_history_config_key ON config_history(config_key);
CREATE INDEX IF NOT EXISTS idx_config_history_changed_at ON config_history(changed_at);

-- 插入系统默认配置
INSERT OR IGNORE INTO configs (
    id, key, value, type, namespace, description, created_by, updated_by
) VALUES
('sys-001', 'system.version', '1.0.0', 'string', 'system', '系统版本号', 'system', 'system'),
('sys-002', 'system.maintenance', 'false', 'boolean', 'system', '系统维护模式', 'system', 'system'),
('sys-003', 'system.debug', 'false', 'boolean', 'system', '调试模式', 'system', 'system'),
('cache-001', 'cache.default_ttl', '3600', 'number', 'cache', '默认缓存时间（秒）', 'system', 'system'),
('cache-002', 'cache.max_size', '1000', 'number', 'cache', '最大缓存条目数', 'system', 'system'),
('api-001', 'api.rate_limit', '100', 'number', 'api', 'API速率限制（每分钟）', 'system', 'system'),
('api-002', 'api.timeout', '30000', 'number', 'api', 'API超时时间（毫秒）', 'system', 'system'),
('feature-001', 'feature.new_ui', 'false', 'boolean', 'feature', '新版UI功能开关', 'system', 'system'),
('security-001', 'security.jwt_secret', 'your-secret-key', 'string', 'security', 'JWT密钥', 'system', 'system');

-- 创建更新时间触发器
CREATE TRIGGER IF NOT EXISTS config_update_timestamp
AFTER UPDATE ON configs
BEGIN
    UPDATE configs SET updated_at = datetime('now') WHERE id = NEW.id;
END;

-- 创建历史记录触发器
CREATE TRIGGER IF NOT EXISTS config_history_insert
AFTER INSERT ON configs
BEGIN
    INSERT INTO config_history (
        id, config_id, config_key, old_value, new_value, action, changed_by
    ) VALUES (
        lower(hex(randomblob(16))),
        NEW.id,
        NEW.key,
        NULL,
        NEW.value,
        'CREATE',
        NEW.created_by
    );
END;

CREATE TRIGGER IF NOT EXISTS config_history_update
AFTER UPDATE ON configs
WHEN OLD.value != NEW.value OR OLD.is_active != NEW.is_active
BEGIN
    INSERT INTO config_history (
        id, config_id, config_key, old_value, new_value, action, changed_by
    ) VALUES (
        lower(hex(randomblob(16))),
        NEW.id,
        NEW.key,
        OLD.value,
        CASE WHEN NEW.is_active = 0 THEN NULL ELSE NEW.value END,
        CASE WHEN NEW.is_active = 0 THEN 'DELETE' ELSE 'UPDATE' END,
        NEW.updated_by
    );
END;
