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

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

-- 删除旧表
DROP TABLE template_matching_results;

-- 创建新的template_matching_results表，包含所有必需字段
CREATE TABLE template_matching_results (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    template_id TEXT NOT NULL,
    binding_id TEXT NOT NULL,
    result_name TEXT NOT NULL,
    description TEXT,
    total_segments INTEGER NOT NULL DEFAULT 0,
    matched_segments INTEGER NOT NULL DEFAULT 0,
    failed_segments INTEGER NOT NULL DEFAULT 0,
    success_rate REAL NOT NULL DEFAULT 0.0,
    used_materials INTEGER NOT NULL DEFAULT 0,
    used_models INTEGER NOT NULL DEFAULT 0,
    matching_duration_ms INTEGER NOT NULL DEFAULT 0,
    quality_score REAL,
    status TEXT NOT NULL DEFAULT '"Success"',  -- JSON格式的MatchingResultStatus
    metadata TEXT,  -- JSON格式的额外元数据
    export_count INTEGER NOT NULL DEFAULT 0,
    is_exported BOOLEAN NOT NULL DEFAULT 0,
    last_exported_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
);

-- 尝试迁移旧数据（如果备份表中有数据）
-- 注意：由于字段结构变化较大，这里只迁移基本字段
INSERT INTO template_matching_results (
    id, project_id, template_id, binding_id, result_name, description,
    total_segments, matched_segments, failed_segments, success_rate,
    used_materials, used_models, matching_duration_ms, quality_score,
    status, metadata, export_count, is_exported, last_exported_at,
    created_at, updated_at, is_active
)
SELECT 
    COALESCE(id, 'migrated_' || rowid),
    COALESCE(project_id, 'unknown'),
    COALESCE(template_id, 'unknown'),
    'migrated_binding',  -- binding_id 在旧表中不存在，设为默认值
    'Migrated Result',   -- result_name 在旧表中不存在，设为默认值
    NULL,                -- description
    0,                   -- total_segments 默认为0
    0,                   -- matched_segments 默认为0
    0,                   -- failed_segments 默认为0
    0.0,                 -- success_rate 默认为0.0
    0,                   -- used_materials 默认为0
    0,                   -- used_models 默认为0
    0,                   -- matching_duration_ms 默认为0
    NULL,                -- quality_score
    '"Success"',         -- status 默认为Success
    matched_segments,    -- 使用旧的matched_segments作为metadata
    COALESCE((SELECT export_count FROM template_matching_results_backup WHERE id = template_matching_results_backup.id), 0),
    COALESCE((SELECT is_exported FROM template_matching_results_backup WHERE id = template_matching_results_backup.id), 0),
    (SELECT last_exported_at FROM template_matching_results_backup WHERE id = template_matching_results_backup.id),
    COALESCE(created_at, CURRENT_TIMESTAMP),
    COALESCE(updated_at, CURRENT_TIMESTAMP),
    1                    -- is_active 默认为true
FROM template_matching_results_backup
WHERE EXISTS (SELECT 1 FROM template_matching_results_backup);

-- 删除备份表
DROP TABLE template_matching_results_backup;

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_template_matching_results_project_id ON template_matching_results (project_id);
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_binding_id ON template_matching_results (binding_id);
CREATE INDEX IF NOT EXISTS idx_template_matching_results_status ON template_matching_results (status);
CREATE INDEX IF NOT EXISTS idx_template_matching_results_is_active ON template_matching_results (is_active);
CREATE INDEX IF NOT EXISTS idx_template_matching_results_created_at ON template_matching_results (created_at);
