-- ComfyUI 数据库表结构迁移脚本
-- 基于新的 SDK 数据模型设计
-- 迁移版本: 033

-- 工作流表
CREATE TABLE IF NOT EXISTS comfyui_workflows (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    workflow_data TEXT NOT NULL, -- JSON 格式的工作流数据
    version TEXT NOT NULL DEFAULT '1.0',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    enabled BOOLEAN NOT NULL DEFAULT 1,
    tags TEXT, -- JSON 数组格式的标签
    category TEXT
);

-- 工作流表索引
CREATE INDEX IF NOT EXISTS idx_comfyui_workflows_name ON comfyui_workflows(name);
CREATE INDEX IF NOT EXISTS idx_comfyui_workflows_category ON comfyui_workflows(category);
CREATE INDEX IF NOT EXISTS idx_comfyui_workflows_created_at ON comfyui_workflows(created_at);
CREATE INDEX IF NOT EXISTS idx_comfyui_workflows_enabled ON comfyui_workflows(enabled);

-- 模板表
CREATE TABLE IF NOT EXISTS comfyui_templates (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    description TEXT,
    template_data TEXT NOT NULL, -- JSON 格式的模板数据
    parameter_schema TEXT NOT NULL, -- JSON 格式的参数定义
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    enabled BOOLEAN NOT NULL DEFAULT 1,
    tags TEXT, -- JSON 数组格式的标签
    author TEXT,
    version TEXT NOT NULL DEFAULT '1.0'
);

-- 模板表索引
CREATE INDEX IF NOT EXISTS idx_comfyui_templates_name ON comfyui_templates(name);
CREATE INDEX IF NOT EXISTS idx_comfyui_templates_category ON comfyui_templates(category);
CREATE INDEX IF NOT EXISTS idx_comfyui_templates_author ON comfyui_templates(author);
CREATE INDEX IF NOT EXISTS idx_comfyui_templates_created_at ON comfyui_templates(created_at);
CREATE INDEX IF NOT EXISTS idx_comfyui_templates_enabled ON comfyui_templates(enabled);

-- 执行记录表
CREATE TABLE IF NOT EXISTS comfyui_executions (
    id TEXT PRIMARY KEY,
    workflow_id TEXT, -- 关联的工作流 ID（可选）
    template_id TEXT, -- 关联的模板 ID（可选）
    prompt_id TEXT NOT NULL, -- ComfyUI 提示 ID
    status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
    parameters TEXT, -- JSON 格式的执行参数
    results TEXT, -- JSON 格式的执行结果
    output_urls TEXT, -- JSON 数组格式的输出文件 URLs
    error_message TEXT,
    execution_time INTEGER, -- 执行时间（毫秒）
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    client_id TEXT,
    node_outputs TEXT, -- JSON 格式的节点输出详情
    FOREIGN KEY (workflow_id) REFERENCES comfyui_workflows(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES comfyui_templates(id) ON DELETE SET NULL
);

-- 执行记录表索引
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_workflow_id ON comfyui_executions(workflow_id);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_template_id ON comfyui_executions(template_id);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_prompt_id ON comfyui_executions(prompt_id);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_status ON comfyui_executions(status);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_created_at ON comfyui_executions(created_at);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_completed_at ON comfyui_executions(completed_at);
CREATE INDEX IF NOT EXISTS idx_comfyui_executions_client_id ON comfyui_executions(client_id);

-- 队列历史表
CREATE TABLE IF NOT EXISTS comfyui_queue_history (
    id TEXT PRIMARY KEY,
    running_tasks TEXT NOT NULL, -- JSON 数组格式的正在运行任务
    pending_tasks TEXT NOT NULL, -- JSON 数组格式的等待中任务
    snapshot_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_tasks INTEGER NOT NULL DEFAULT 0,
    completed_tasks INTEGER NOT NULL DEFAULT 0
);

-- 队列历史表索引
CREATE INDEX IF NOT EXISTS idx_comfyui_queue_history_snapshot_time ON comfyui_queue_history(snapshot_time);

-- 配置表（用于存储 ComfyUI 服务配置）
CREATE TABLE IF NOT EXISTS comfyui_configs (
    id TEXT PRIMARY KEY DEFAULT 'default',
    base_url TEXT NOT NULL,
    timeout_seconds INTEGER NOT NULL DEFAULT 300,
    retry_attempts INTEGER NOT NULL DEFAULT 3,
    retry_delay_ms INTEGER NOT NULL DEFAULT 1000,
    enable_websocket BOOLEAN NOT NULL DEFAULT 1,
    enable_cache BOOLEAN NOT NULL DEFAULT 1,
    max_concurrency INTEGER NOT NULL DEFAULT 4,
    custom_headers TEXT, -- JSON 格式的自定义请求头
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 插入默认配置
INSERT OR IGNORE INTO comfyui_configs (id, base_url) VALUES ('default', 'http://localhost:8188');

-- 创建触发器以自动更新 updated_at 字段
CREATE TRIGGER IF NOT EXISTS update_comfyui_workflows_updated_at
    AFTER UPDATE ON comfyui_workflows
    FOR EACH ROW
BEGIN
    UPDATE comfyui_workflows SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_comfyui_templates_updated_at
    AFTER UPDATE ON comfyui_templates
    FOR EACH ROW
BEGIN
    UPDATE comfyui_templates SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_comfyui_configs_updated_at
    AFTER UPDATE ON comfyui_configs
    FOR EACH ROW
BEGIN
    UPDATE comfyui_configs SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 创建视图以便于查询
CREATE VIEW IF NOT EXISTS comfyui_execution_summary AS
SELECT
    e.id,
    e.prompt_id,
    e.status,
    e.created_at,
    e.completed_at,
    e.execution_time,
    COALESCE(w.name, t.name) as workflow_name,
    CASE
        WHEN e.workflow_id IS NOT NULL THEN 'workflow'
        WHEN e.template_id IS NOT NULL THEN 'template'
        ELSE 'unknown'
    END as execution_type
FROM comfyui_executions e
LEFT JOIN comfyui_workflows w ON e.workflow_id = w.id
LEFT JOIN comfyui_templates t ON e.template_id = t.id;

-- 创建统计视图
CREATE VIEW IF NOT EXISTS comfyui_execution_stats AS
SELECT
    status,
    COUNT(*) as count,
    AVG(execution_time) as avg_execution_time,
    MIN(execution_time) as min_execution_time,
    MAX(execution_time) as max_execution_time
FROM comfyui_executions
WHERE execution_time IS NOT NULL
GROUP BY status;
