-- 创建matching_segment_results和matching_failed_segment_results表
-- 用于存储模板匹配的详细片段结果

-- 创建匹配成功片段结果表
CREATE TABLE IF NOT EXISTS matching_segment_results (
    id TEXT PRIMARY KEY,
    matching_result_id TEXT NOT NULL,
    track_segment_id TEXT NOT NULL,
    track_segment_name TEXT NOT NULL,
    material_segment_id TEXT NOT NULL,
    material_id TEXT NOT NULL,
    material_name TEXT NOT NULL,
    model_id TEXT,
    model_name TEXT,
    match_score REAL NOT NULL,
    match_reason TEXT NOT NULL,
    segment_duration INTEGER NOT NULL,  -- 片段时长（微秒）
    start_time INTEGER NOT NULL,        -- 在模板中的开始时间（微秒）
    end_time INTEGER NOT NULL,          -- 在模板中的结束时间（微秒）
    properties TEXT,                    -- JSON格式的片段属性
    created_at DATETIME NOT NULL DEFAULT (datetime('now', 'utc') || 'Z'),
    updated_at DATETIME NOT NULL DEFAULT (datetime('now', 'utc') || 'Z'),
    FOREIGN KEY (matching_result_id) REFERENCES template_matching_results (id) ON DELETE CASCADE,
    FOREIGN KEY (track_segment_id) REFERENCES track_segments (id) ON DELETE CASCADE,
    FOREIGN KEY (material_segment_id) REFERENCES material_segments (id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials (id) ON DELETE CASCADE,
    FOREIGN KEY (model_id) REFERENCES models (id) ON DELETE SET NULL
);

-- 创建匹配失败片段结果表
CREATE TABLE IF NOT EXISTS matching_failed_segment_results (
    id TEXT PRIMARY KEY,
    matching_result_id TEXT NOT NULL,
    track_segment_id TEXT NOT NULL,
    track_segment_name TEXT NOT NULL,
    matching_rule_type TEXT NOT NULL,   -- 匹配规则类型
    matching_rule_data TEXT,            -- 匹配规则数据（JSON格式）
    failure_reason TEXT NOT NULL,       -- 失败原因
    failure_details TEXT,               -- 失败详情（JSON格式）
    segment_duration INTEGER NOT NULL,  -- 片段时长（微秒）
    start_time INTEGER NOT NULL,        -- 在模板中的开始时间（微秒）
    end_time INTEGER NOT NULL,          -- 在模板中的结束时间（微秒）
    created_at DATETIME NOT NULL DEFAULT (datetime('now', 'utc') || 'Z'),
    updated_at DATETIME NOT NULL DEFAULT (datetime('now', 'utc') || 'Z'),
    FOREIGN KEY (matching_result_id) REFERENCES template_matching_results (id) ON DELETE CASCADE,
    FOREIGN KEY (track_segment_id) REFERENCES track_segments (id) ON DELETE CASCADE
);

-- 创建索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_matching_result_id ON matching_segment_results (matching_result_id);
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_track_segment_id ON matching_segment_results (track_segment_id);
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_material_segment_id ON matching_segment_results (material_segment_id);
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_material_id ON matching_segment_results (material_id);
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_model_id ON matching_segment_results (model_id);
CREATE INDEX IF NOT EXISTS idx_matching_segment_results_start_time ON matching_segment_results (start_time);

CREATE INDEX IF NOT EXISTS idx_matching_failed_segment_results_matching_result_id ON matching_failed_segment_results (matching_result_id);
CREATE INDEX IF NOT EXISTS idx_matching_failed_segment_results_track_segment_id ON matching_failed_segment_results (track_segment_id);
CREATE INDEX IF NOT EXISTS idx_matching_failed_segment_results_start_time ON matching_failed_segment_results (start_time);
