-- 创建项目表
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    path TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1
);

-- 创建项目表索引
CREATE INDEX IF NOT EXISTS idx_projects_name ON projects (name);
CREATE INDEX IF NOT EXISTS idx_projects_created_at ON projects (created_at);

-- 创建素材表
CREATE TABLE IF NOT EXISTS materials (
    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)
);

-- 创建素材表索引
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);

-- 创建素材片段表
CREATE TABLE IF NOT EXISTS material_segments (
    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)
);

-- 创建素材片段表索引
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);

-- 创建模特表
CREATE TABLE IF NOT EXISTS models (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    stage_name TEXT,
    gender TEXT NOT NULL,
    age INTEGER,
    height INTEGER,
    weight INTEGER,
    measurements TEXT,
    description TEXT,
    tags TEXT,
    avatar_path TEXT,
    contact_info TEXT,
    social_media TEXT,
    status TEXT NOT NULL DEFAULT 'Active',
    rating REAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1
);

-- 创建模特照片表
CREATE TABLE IF NOT EXISTS model_photos (
    id TEXT PRIMARY KEY,
    model_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    photo_type TEXT NOT NULL,
    description TEXT,
    tags TEXT,
    is_cover BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (model_id) REFERENCES models (id) ON DELETE CASCADE
);

-- 创建AI分类表
CREATE TABLE IF NOT EXISTS ai_classifications (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    prompt_text TEXT NOT NULL,
    description TEXT,
    is_active INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建视频分类记录表
CREATE TABLE IF NOT EXISTS video_classification_records (
    id TEXT PRIMARY KEY,
    segment_id TEXT NOT NULL,
    classification_result TEXT,
    confidence_score REAL,
    processing_status TEXT NOT NULL DEFAULT 'Pending',
    processed_at DATETIME,
    error_message TEXT,
    gemini_response TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (segment_id) REFERENCES material_segments (id) ON DELETE CASCADE
);

-- 创建视频分类记录表索引
CREATE INDEX IF NOT EXISTS idx_video_classification_records_segment_id ON video_classification_records (segment_id);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_status ON video_classification_records (processing_status);
CREATE INDEX IF NOT EXISTS idx_video_classification_records_result ON video_classification_records (classification_result);

-- 创建模板表
CREATE TABLE IF NOT EXISTS templates (
    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
);

-- 创建模板素材表
CREATE TABLE IF NOT EXISTS template_materials (
    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',
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
);

-- 创建模板素材表索引
CREATE INDEX IF NOT EXISTS idx_template_materials_template_id ON template_materials (template_id);
CREATE INDEX IF NOT EXISTS idx_template_materials_upload_status ON template_materials (upload_status);
CREATE INDEX IF NOT EXISTS idx_template_materials_original_id ON template_materials (original_id);

-- 创建轨道表
CREATE TABLE IF NOT EXISTS tracks (
    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
);

-- 创建轨道片段表
CREATE TABLE IF NOT EXISTS track_segments (
    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
);

-- 创建模板匹配结果表
CREATE TABLE IF NOT EXISTS template_matching_results (
    id TEXT PRIMARY KEY,
    template_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    matched_segments TEXT NOT NULL,
    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
);

-- 创建水印模板表
CREATE TABLE IF NOT EXISTS watermark_templates (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    thumbnail_path TEXT,
    category TEXT NOT NULL,
    watermark_type TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    width INTEGER,
    height INTEGER,
    description TEXT,
    tags TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建水印处理结果表
CREATE TABLE IF NOT EXISTS watermark_processing_results (
    id TEXT PRIMARY KEY,
    material_id TEXT NOT NULL,
    operation TEXT NOT NULL,
    success INTEGER NOT NULL,
    output_path TEXT,
    processing_time_ms INTEGER NOT NULL,
    error_message TEXT,
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials (id) ON DELETE CASCADE
);

-- 创建相关索引
CREATE INDEX IF NOT EXISTS idx_watermark_processing_results_material_id ON watermark_processing_results (material_id);
CREATE INDEX IF NOT EXISTS idx_watermark_processing_results_success ON watermark_processing_results (success);
