-- 删除模特ID索引
DROP INDEX IF EXISTS idx_materials_model_id;

-- SQLite不支持DROP COLUMN，需要重建表
CREATE TABLE materials_new (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    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)
);

-- 迁移数据（排除model_id字段）
INSERT INTO materials_new 
SELECT id, project_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_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);
