-- 修复video_classification_records表结构，添加缺失的字段
-- 重建表以匹配VideoClassificationRecord模型的完整结构

-- 备份现有数据（如果有的话）
CREATE TABLE video_classification_records_backup AS 
SELECT * FROM video_classification_records;

-- 删除旧表
DROP TABLE video_classification_records;

-- 创建新的video_classification_records表，包含所有必需字段
CREATE TABLE video_classification_records (
    id TEXT PRIMARY KEY,
    segment_id TEXT NOT NULL,
    material_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    category TEXT NOT NULL,
    confidence REAL NOT NULL,
    reasoning TEXT NOT NULL,
    features TEXT NOT NULL,  -- JSON数组格式
    product_match INTEGER NOT NULL,  -- 布尔值存储为整数
    quality_score REAL NOT NULL,
    gemini_file_uri TEXT,
    raw_response TEXT,
    status TEXT NOT NULL,  -- JSON格式的ClassificationStatus
    error_message TEXT,
    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
);

-- 尝试迁移旧数据（如果备份表中有数据）
-- 注意：由于字段结构变化较大，这里只迁移基本字段
INSERT INTO video_classification_records (
    id, segment_id, material_id, project_id, category, confidence, reasoning,
    features, product_match, quality_score, gemini_file_uri, raw_response,
    status, error_message, created_at, updated_at
)
SELECT 
    COALESCE(id, 'migrated_' || rowid),
    COALESCE(segment_id, 'unknown'),
    'unknown',  -- material_id 在旧表中不存在，设为默认值
    'unknown',  -- project_id 在旧表中不存在，设为默认值
    COALESCE(classification_result, 'Unknown'),
    COALESCE(confidence_score, 0.0),
    'Migrated from old schema',  -- reasoning 在旧表中不存在
    '[]',  -- features 默认为空数组
    0,  -- product_match 默认为false
    COALESCE(confidence_score, 0.0),  -- 使用confidence_score作为quality_score
    NULL,  -- gemini_file_uri
    gemini_response,  -- 使用gemini_response作为raw_response
    CASE 
        WHEN processing_status = 'Completed' THEN '"Classified"'
        WHEN processing_status = 'Failed' THEN '"Failed"'
        ELSE '"NeedsReview"'
    END,
    error_message,
    COALESCE(created_at, CURRENT_TIMESTAMP),
    COALESCE(updated_at, CURRENT_TIMESTAMP)
FROM video_classification_records_backup
WHERE EXISTS (SELECT 1 FROM video_classification_records_backup);

-- 删除备份表
DROP TABLE video_classification_records_backup;

-- 重新创建索引
CREATE INDEX IF NOT EXISTS idx_video_classification_records_segment_id ON video_classification_records (segment_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_material_id ON video_classification_records (material_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_project_id ON video_classification_records (project_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_status ON video_classification_records (status);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_category ON video_classification_records (category);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_created_at ON video_classification_records (created_at);
