-- 创建video_classification_tasks表
-- 用于管理AI视频分类任务的队列和状态

CREATE TABLE IF NOT EXISTS video_classification_tasks (
    id TEXT PRIMARY KEY,
    segment_id TEXT NOT NULL,
    material_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    video_file_path TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT '"Pending"',  -- JSON格式的TaskStatus
    priority INTEGER NOT NULL DEFAULT 0,
    retry_count INTEGER NOT NULL DEFAULT 0,
    max_retries INTEGER NOT NULL DEFAULT 3,
    gemini_file_uri TEXT,
    prompt_text TEXT,
    error_message TEXT,
    started_at DATETIME,
    completed_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (segment_id) REFERENCES material_segments (id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials (id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
);

-- 创建索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_segment_id ON video_classification_tasks (segment_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_material_id ON video_classification_tasks (material_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_project_id ON video_classification_tasks (project_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_status ON video_classification_tasks (status);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_priority ON video_classification_tasks (priority);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_created_at ON video_classification_tasks (created_at);
CREATE INDEX IF NOT EXISTS idx_video_classification_tasks_status_priority ON video_classification_tasks (status, priority);
