-- 创建素材使用记录表
-- 用于跟踪素材片段在各种场景下的使用情况

CREATE TABLE IF NOT EXISTS material_usage_records (
    id TEXT PRIMARY KEY,
    material_segment_id TEXT NOT NULL,
    material_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    template_matching_result_id TEXT NOT NULL,
    template_id TEXT NOT NULL,
    binding_id TEXT NOT NULL,
    track_segment_id TEXT NOT NULL,
    usage_type TEXT NOT NULL,  -- JSON格式的MaterialUsageType枚举
    usage_context TEXT,        -- JSON格式的使用上下文信息
    created_at DATETIME NOT NULL DEFAULT (datetime('now', 'utc') || 'Z'),
    FOREIGN KEY (material_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,
    FOREIGN KEY (template_matching_result_id) REFERENCES template_matching_results (id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE,
    FOREIGN KEY (binding_id) REFERENCES project_template_bindings (id) ON DELETE CASCADE,
    FOREIGN KEY (track_segment_id) REFERENCES track_segments (id) ON DELETE CASCADE
);

-- 创建索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_material_usage_records_material_segment_id ON material_usage_records (material_segment_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_material_id ON material_usage_records (material_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_project_id ON material_usage_records (project_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_template_matching_result_id ON material_usage_records (template_matching_result_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_template_id ON material_usage_records (template_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_binding_id ON material_usage_records (binding_id);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_usage_type ON material_usage_records (usage_type);
CREATE INDEX IF NOT EXISTS idx_material_usage_records_created_at ON material_usage_records (created_at);
