-- SQLite不支持DROP COLUMN，需要重建表

-- 重建material_segments表（移除新增的usage_count, is_used, last_used_at字段，保留thumbnail_path）
CREATE TABLE material_segments_new (
    id TEXT PRIMARY KEY,
    material_id TEXT NOT NULL,
    segment_index INTEGER NOT NULL,
    start_time REAL NOT NULL,
    end_time REAL NOT NULL,
    duration REAL NOT NULL,
    file_path TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    thumbnail_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials (id) ON DELETE CASCADE,
    UNIQUE(material_id, segment_index)
);

-- 迁移数据（排除usage_count, is_used, last_used_at字段）
INSERT INTO material_segments_new
SELECT id, material_id, segment_index, start_time, end_time, duration,
       file_path, file_size, thumbnail_path, created_at
FROM material_segments;

-- 删除旧表并重命名新表
DROP TABLE material_segments;
ALTER TABLE material_segments_new RENAME TO material_segments;

-- 重新创建索引
CREATE INDEX IF NOT EXISTS idx_material_segments_material_id ON material_segments (material_id);
CREATE INDEX IF NOT EXISTS idx_material_segments_duration ON material_segments (duration);

-- 重建materials表（移除thumbnail_path字段）
CREATE TABLE materials_new (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    model_id TEXT,
    name TEXT NOT NULL,
    original_path TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    md5_hash TEXT NOT NULL,
    material_type TEXT NOT NULL,
    processing_status TEXT NOT NULL DEFAULT 'Pending',
    metadata TEXT,
    scene_detection TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME,
    error_message TEXT,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
    UNIQUE(project_id, md5_hash)
);

-- 迁移数据（排除thumbnail_path字段）
INSERT INTO materials_new 
SELECT id, project_id, model_id, name, original_path, file_size, md5_hash,
       material_type, processing_status, metadata, scene_detection,
       created_at, updated_at, processed_at, error_message
FROM materials;

-- 删除旧表并重命名新表
DROP TABLE materials;
ALTER TABLE materials_new RENAME TO materials;

-- 重新创建索引
CREATE INDEX IF NOT EXISTS idx_materials_project_id ON materials (project_id);
CREATE INDEX IF NOT EXISTS idx_materials_model_id ON materials (model_id);
CREATE INDEX IF NOT EXISTS idx_materials_processing_status ON materials (processing_status);
CREATE INDEX IF NOT EXISTS idx_materials_material_type ON materials (material_type);
CREATE INDEX IF NOT EXISTS idx_materials_created_at ON materials (created_at);

-- track_segments表的matching_rule字段在初始化时就存在，无需回滚

-- 重建template_matching_results表（移除export_count字段）
CREATE TABLE template_matching_results_new (
    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,
    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
);

-- 迁移数据（排除export_count字段）
INSERT INTO template_matching_results_new 
SELECT id, template_id, project_id, matched_segments, is_exported, 
       last_exported_at, created_at, updated_at
FROM template_matching_results;

-- 删除旧表并重命名新表
DROP TABLE template_matching_results;
ALTER TABLE template_matching_results_new RENAME TO template_matching_results;
