-- 检查是否需要移除模板表的 project_id 字段
-- 只有在表中存在 project_id 字段时才需要重建表

-- 创建新的模板表（不包含project_id字段）
CREATE TABLE IF NOT EXISTS templates_new (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    canvas_width INTEGER NOT NULL,
    canvas_height INTEGER NOT NULL,
    canvas_ratio TEXT NOT NULL,
    duration INTEGER NOT NULL,
    fps REAL NOT NULL,
    import_status TEXT NOT NULL DEFAULT 'Pending',
    source_file_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1
);

-- 迁移数据（排除 project_id）
INSERT OR IGNORE INTO templates_new
SELECT id, name, description, canvas_width, canvas_height, canvas_ratio,
       duration, fps, import_status, source_file_path, created_at, updated_at, is_active
FROM templates;

-- 删除旧表并重命名新表
DROP TABLE IF EXISTS templates_old;
ALTER TABLE templates RENAME TO templates_old;
ALTER TABLE templates_new RENAME TO templates;

-- 修复 template_materials 表的外键约束
CREATE TABLE template_materials_new (
    id TEXT PRIMARY KEY,
    template_id TEXT NOT NULL,
    original_id TEXT NOT NULL,
    name TEXT NOT NULL,
    material_type TEXT NOT NULL,
    original_path TEXT NOT NULL,
    remote_url TEXT,
    file_size INTEGER NOT NULL,
    duration INTEGER,
    width INTEGER,
    height INTEGER,
    upload_status TEXT NOT NULL DEFAULT 'Pending',
    file_exists BOOLEAN DEFAULT FALSE,
    upload_success BOOLEAN DEFAULT FALSE,
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
);

-- 迁移 template_materials 数据
INSERT OR IGNORE INTO template_materials_new
SELECT id, template_id, original_id, name, material_type, original_path,
       remote_url, file_size, duration, width, height, upload_status,
       file_exists, upload_success, metadata, created_at, updated_at
FROM template_materials;

-- 删除旧表并重命名新表
DROP TABLE IF EXISTS template_materials_old;
ALTER TABLE template_materials RENAME TO template_materials_old;
ALTER TABLE template_materials_new RENAME TO template_materials;

-- 修复 tracks 表的外键约束
CREATE TABLE tracks_new (
    id TEXT PRIMARY KEY,
    template_id TEXT NOT NULL,
    name TEXT NOT NULL,
    track_type TEXT NOT NULL,
    track_index INTEGER NOT NULL,
    properties TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
);

-- 迁移轨道数据
INSERT OR IGNORE INTO tracks_new
SELECT id, template_id, name, track_type, track_index, properties, created_at, updated_at
FROM tracks;

-- 删除旧表并重命名新表
DROP TABLE IF EXISTS tracks_old;
ALTER TABLE tracks RENAME TO tracks_old;
ALTER TABLE tracks_new RENAME TO tracks;

-- 重建 track_segments 表，恢复正确的外键约束
CREATE TABLE track_segments_new (
    id TEXT PRIMARY KEY,
    track_id TEXT NOT NULL,
    template_material_id TEXT,
    name TEXT NOT NULL,
    start_time INTEGER NOT NULL,
    end_time INTEGER NOT NULL,
    duration INTEGER NOT NULL,
    segment_index INTEGER NOT NULL,
    properties TEXT,
    matching_rule TEXT DEFAULT '"FixedMaterial"',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES tracks (id) ON DELETE CASCADE,
    FOREIGN KEY (template_material_id) REFERENCES template_materials (id) ON DELETE SET NULL
);

-- 迁移轨道片段数据
INSERT OR IGNORE INTO track_segments_new
SELECT id, track_id, template_material_id, name, start_time, end_time,
       duration, segment_index, properties, 
       COALESCE(matching_rule, '"FixedMaterial"'), created_at, updated_at
FROM track_segments;

-- 删除旧表并重命名新表
DROP TABLE IF EXISTS track_segments_old;
ALTER TABLE track_segments RENAME TO track_segments_old;
ALTER TABLE track_segments_new RENAME TO track_segments;
