#!/usr/bin/env python
"""
标准化视频文件名并生成数据库记录
"""

import os
import sqlite3
from pathlib import Path
import re

# 定义路径
BASE_DIR = Path(__file__).resolve().parent
MEDIA_DIR = BASE_DIR / 'media'
VIDEOS_DIR = MEDIA_DIR / 'videos'

def sanitize_filename(name):
    """清理文件名，移除特殊字符"""
    # 移除不允许的字符
    name = re.sub(r'[<>:"/\\|?*\x00-\x1F]', '', name)
    # 替换空格为下划线
    name = name.replace(' ', '_')
    # 限制长度
    if len(name) > 100:
        name = name[:100]
    return name

def organize_videos():
    """整理视频文件"""
    if not VIDEOS_DIR.exists():
        print("视频目录不存在")
        return
    
    video_records = []
    
    # 遍历所有视频目录
    for video_dir in VIDEOS_DIR.iterdir():
        if video_dir.is_dir():
            print(f"处理目录: {video_dir.name}")
            
            # 标准化目录名
            sanitized_dir_name = sanitize_filename(video_dir.name)
            if sanitized_dir_name != video_dir.name:
                new_dir_path = VIDEOS_DIR / sanitized_dir_name
                if not new_dir_path.exists():
                    video_dir.rename(new_dir_path)
                    video_dir = new_dir_path
                    print(f"  重命名目录: {video_dir.name} -> {sanitized_dir_name}")
            
            # 查找视频文件
            mp4_files = list(video_dir.glob("*.mp4"))
            jpg_files = list(video_dir.glob("*.jpg"))
            cover_files = list(video_dir.glob("*Cover.jpg"))
            
            if mp4_files:
                # 获取视频文件
                video_file = mp4_files[0]
                # 查找缩略图文件
                thumb_file = None
                if cover_files:
                    thumb_file = cover_files[0]
                elif jpg_files:
                    thumb_file = jpg_files[0]
                
                # 标准化文件名
                base_name = video_file.stem.split('-')[0]  # 获取基础名称
                new_video_name = f"{sanitized_dir_name}.mp4"
                new_thumb_name = f"{sanitized_dir_name}.jpg"
                
                # 重命名文件
                new_video_path = video_dir / new_video_name
                new_thumb_path = video_dir / new_thumb_name
                
                if video_file.name != new_video_name:
                    video_file.rename(new_video_path)
                    print(f"  重命名视频: {video_file.name} -> {new_video_name}")
                
                if thumb_file and thumb_file.name != new_thumb_name:
                    thumb_file.rename(new_thumb_path)
                    print(f"  重命名缩略图: {thumb_file.name} -> {new_thumb_name}")
                
                # 记录视频信息
                video_record = {
                    'name': video_dir.name,
                    'thumbnail_url': f'/media/videos/{sanitized_dir_name}/{new_thumb_name}',
                    'video_url': f'/media/videos/{sanitized_dir_name}/{new_video_name}'
                }
                video_records.append(video_record)
                
                print(f"  添加记录: {video_record}")
            else:
                print(f"  跳过目录 (无视频文件): {video_dir.name}")
    
    return video_records

def update_database(video_records):
    """更新数据库"""
    db_path = BASE_DIR / 'footviz.db'
    if not db_path.exists():
        print("数据库文件不存在")
        return
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 删除现有的MatchReview记录
    cursor.execute("DELETE FROM api_matchreview")
    
    # 插入新的视频记录
    for record in video_records:
        cursor.execute("""
            INSERT OR REPLACE INTO api_matchreview (name, thumbnail_url, video_url)
            VALUES (?, ?, ?)
        """, (record['name'], record['thumbnail_url'], record['video_url']))
    
    conn.commit()
    conn.close()
    print(f"已更新数据库，添加了 {len(video_records)} 条记录")

def main():
    print("开始整理视频文件...")
    video_records = organize_videos()
    print(f"\n整理完成，共处理 {len(video_records)} 个视频")
    
    if video_records:
        print("\n更新数据库...")
        update_database(video_records)
        print("数据库更新完成")
    
    print("\n所有操作完成!")

if __name__ == "__main__":
    main()