-- 回滚：恢复template_matching_results表到旧的结构
-- 注意：这个回滚会丢失新字段的数据

-- 备份当前数据
CREATE TABLE template_matching_results_new_backup AS 
SELECT * FROM template_matching_results;

-- 删除新表
DROP TABLE template_matching_results;

-- 重新创建旧的template_matching_results表结构
CREATE TABLE IF NOT EXISTS template_matching_results (
    id TEXT PRIMARY KEY,
    template_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    matched_segments TEXT NOT NULL,
    is_exported BOOLEAN DEFAULT 0,
    last_exported_at DATETIME,
    export_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
);

-- 尝试迁移回旧格式的数据
INSERT INTO template_matching_results (
    id, template_id, project_id, matched_segments, is_exported, 
    last_exported_at, export_count, created_at, updated_at
)
SELECT 
    id,
    template_id,
    project_id,
    COALESCE(metadata, '[]'),  -- 使用metadata作为matched_segments
    is_exported,
    last_exported_at,
    export_count,
    created_at,
    updated_at
FROM template_matching_results_new_backup
WHERE EXISTS (SELECT 1 FROM template_matching_results_new_backup);

-- 删除备份表
DROP TABLE template_matching_results_new_backup;

-- 重新创建旧的索引
CREATE INDEX IF NOT EXISTS idx_template_matching_results_template_id ON template_matching_results (template_id);
CREATE INDEX IF NOT EXISTS idx_template_matching_results_project_id ON template_matching_results (project_id);
