import os
import re
import sqlite3
import py7zr
import zipfile
import json
from datetime import datetime
from PIL import Image
from moviepy.video.io.VideoFileClip import VideoFileClip


def analyze_image(file_path):
    """
    分析图片文件，获取分辨率。
    :param file_path: 图片文件路径
    :return: 图片分辨率字符串 (如 '1920x1080')
    """
    try:
        with Image.open(file_path) as img:
            width, height = img.size
            return f"{width}x{height}"
    except Exception as e:
        print(f"Error analyzing image file: {file_path}")
        return f"{-1}x{-1}"


def analyze_video(file_path):
    """
    分析视频文件，获取分辨率和时长。
    :param file_path: 视频文件路径
    :return: 分辨率字符串 (如 '1920x1080') 和时长 (秒)
    """
    try:
        video = VideoFileClip(file_path)
        width, height = video.size
        duration = int(video.duration)
        return f"{width}x{height}", duration
    except Exception as e:
        print(f"Error analyzing video file: {file_path}")
        return f"{-1}x{-1}", -1



# 初始化数据库
def initialize_database(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 主表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS resources (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            type TEXT NOT NULL,
            theme TEXT,
            path TEXT NOT NULL,
            tags TEXT,
            size INTEGER,
            source TEXT,
            source_link TEXT,
            compressed INTEGER DEFAULT 0,
            archive_path TEXT,
            created_at TEXT,
            updated_at TEXT,
            attribute1 TEXT,
            attribute2 TEXT,
            attribute3 TEXT
        )
    """)

    # 主题值表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS themes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            file_count INTEGER DEFAULT 0,
            file_types TEXT,
            total_size INTEGER DEFAULT 0,
            average_file_size REAL DEFAULT 0
        )
    """)

    # 图片子表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS images (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            resource_id INTEGER NOT NULL,
            resolution TEXT,
            format TEXT,
            color_mode TEXT,
            FOREIGN KEY (resource_id) REFERENCES resources(id)
        )
    """)

    # 视频子表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            resource_id INTEGER NOT NULL,
            duration INTEGER,
            resolution TEXT,
            format TEXT,
            frame_rate REAL,
            FOREIGN KEY (resource_id) REFERENCES resources(id)
        )
    """)

    # 小说子表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS novels (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            resource_id INTEGER NOT NULL,
            title TEXT,
            author TEXT,
            word_count INTEGER,
            genre TEXT,
            FOREIGN KEY (resource_id) REFERENCES resources(id)
        )
    """)


    # 压缩包子表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS archives (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            resource_id INTEGER,
            contents TEXT,
            extracted_path TEXT,
            format TEXT,
            password TEXT,
            FOREIGN KEY (resource_id) REFERENCES resources(id)
        )
    """)

    conn.commit()
    conn.close()
    print("Database initialized successfully.")



# 添加资源
def add_resource(conn, name, resource_type, theme, path, tags, size, source, source_link):
    cursor = conn.cursor()
    created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute("""
        INSERT INTO resources (name, type, theme, path, tags, size, source, source_link, compressed, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)
    """, (name, resource_type, theme, path, tags, size, source, source_link, created_at, created_at))
    conn.commit()
    print(f"Resource '{name}' added successfully.")


# 删除资源
def delete_resource(conn, resource_id):
    cursor = conn.cursor()
    cursor.execute("SELECT path, archive_path, compressed FROM resources WHERE id = ?", (resource_id,))
    resource = cursor.fetchone()
    if not resource:
        print(f"Resource ID {resource_id} not found.")
        return

    path, archive_path, compressed = resource

    # 删除文件或压缩包
    if compressed:
        if archive_path and os.path.exists(archive_path):
            os.remove(archive_path)
            print(f"Compressed file {archive_path} deleted.")
    else:
        if path and os.path.exists(path):
            os.remove(path)
            print(f"File {path} deleted.")

    # 删除数据库记录
    cursor.execute("DELETE FROM resources WHERE id = ?", (resource_id,))
    conn.commit()
    print(f"Resource ID {resource_id} deleted successfully.")


# 压缩资源并删除原文件
def compress_and_delete(conn, resource_id, archive_format='7z', password=None):
    cursor = conn.cursor()
    cursor.execute("SELECT path, name FROM resources WHERE id = ?", (resource_id,))
    resource = cursor.fetchone()
    if not resource:
        print(f"Resource ID {resource_id} not found.")
        return

    file_path, file_name = resource
    archive_path = f"{os.path.splitext(file_path)[0]}.{archive_format}"

    # 压缩操作
    if archive_format == '7z':
        with py7zr.SevenZipFile(archive_path, 'w', password=password) as archive:
            archive.write(file_path, arcname=file_name)
    elif archive_format == 'zip':
        with zipfile.ZipFile(archive_path, 'w', zipfile.ZIP_DEFLATED) as archive:
            archive.write(file_path, arcname=file_name)
    else:
        print(f"Unsupported archive format: {archive_format}")
        return

    # 删除原始文件
    os.remove(file_path)
    print(f"Compressed {file_path} to {archive_path} and deleted original file.")

    # 更新数据库
    cursor.execute("""
        UPDATE resources
        SET compressed = 1, archive_path = ?
        WHERE id = ?
    """, (archive_path, resource_id))
    conn.commit()


# 解压资源
def auto_decompress(conn, resource_id, password=None):
    cursor = conn.cursor()
    cursor.execute("SELECT archive_path, path, compressed FROM resources WHERE id = ?", (resource_id,))
    resource = cursor.fetchone()
    if not resource:
        print(f"Resource ID {resource_id} not found.")
        return

    archive_path, original_path, compressed = resource
    if not compressed:
        print(f"Resource ID {resource_id} is not compressed.")
        return

    # 解压操作
    extract_dir = os.path.dirname(original_path)
    if archive_path.endswith('.7z'):
        with py7zr.SevenZipFile(archive_path, 'r', password=password) as archive:
            archive.extractall(path=extract_dir)
    elif archive_path.endswith('.zip'):
        with zipfile.ZipFile(archive_path, 'r') as archive:
            archive.extractall(path=extract_dir)
    else:
        print(f"Unsupported archive format: {archive_path}")
        return

    print(f"Decompressed {archive_path} to {original_path}")

    # 删除压缩包
    if os.path.exists(archive_path):
        os.remove(archive_path)
        print(f"Deleted archive file: {archive_path}")

    # 更新数据库
    cursor.execute("""
        UPDATE resources
        SET compressed = 0, archive_path = NULL
        WHERE id = ?
    """, (resource_id,))
    conn.commit()


def clean_folder_name(folder_name, parent_folder_name=None):
    """
    清洗文件夹名或文件名：
    - 删除无意义字符（数字、特殊字符等）。
    - 如果清洗后无意义，则使用父文件夹名加前缀。

    :param folder_name: 原始文件夹名
    :param parent_folder_name: 父文件夹名，当文件名无意义时使用
    :return: 清洗后的文件夹名或 None
    """
    folder_name = re.sub(r"([u4e00-u9fa5w]+)(_+1)+", r"1", folder_name)  # 处理重复
    folder_name = re.sub(r"[.*?]", "", folder_name)  # 去掉方括号内容
    folder_name = re.sub(r"^d+[-.s]*", "", folder_name)  # 去掉数字开头的内容
    folder_name = re.sub(r"[^u4e00-u9fa5ws]", "", folder_name)  # 移除特殊字符
    folder_name = re.sub(r"s+", "_", folder_name.strip())  # 去掉空格并替换为下划线

    # 无意义名称处理
    if not folder_name or re.fullmatch(r"[_d]+", folder_name):
        folder_name = f"{parent_folder_name or 'default'}-{folder_name}".strip("-")
    folder_name = re.sub(r"_+", "_", folder_name)  # 删除连续的下划线
    folder_name = re.sub("_", " ", folder_name)  # 将下划线替换为空格

    return folder_name if folder_name else None


def scan_image_video_and_add_resources(conn, base_dir, source="local", source_link="N/A", max_file_size=15 * 1024 * 1024):
    cursor = conn.cursor()
    theme_stats = {}  # 用于统计主题的文件数量、总大小、文件类型
    large_files_log = "large_file.txt"
    for root, _, files in os.walk(base_dir):
        folder_name = os.path.basename(root)  # 当前文件夹的名字
        theme = clean_folder_name(folder_name)
        file_count = 0
        total_size = 0
        file_types = {}

        for file in files:
            file_path = os.path.join(root, file)
            file_size = os.path.getsize(file_path)
            file_type = os.path.splitext(file)[1][1:].lower() or "unknown"

            # 跳过非图片和视频文件
            if file_type not in ["jpg", "jpeg", "png", "gif", "bmp", "mp4", "avi", "mkv", "mov", "flv"]:
                continue

            created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            # 插入资源表
            cursor.execute("""
                INSERT INTO resources (name, type, theme, path, tags, size, source, source_link, compressed, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)
            """, (file, file_type, theme, file_path, "", file_size, source, source_link, created_at, created_at))
            resource_id = cursor.lastrowid

            # 图片处理
            if file_type in ["jpg", "jpeg", "png", "gif", "bmp"]:
                # 如果文件超过大小限制，记录并跳过
                if file_size > max_file_size:
                    with open(large_files_log, "a") as log_file:
                        log_file.write(f"File too large: {file_path} ({file_size} bytes)n")
                    print(f"Skipped large file: {file_path}")
                    continue
                resolution = analyze_image(file_path)
                cursor.execute("""
                    INSERT INTO images (resource_id, resolution, format, color_mode)
                    VALUES (?, ?, ?, ?)
                """, (resource_id, resolution, file_type.upper(), "RGB"))

            # 视频处理
            elif file_type in ["mp4", "avi", "mkv", "mov", "flv"]:
                resolution, duration = analyze_video(file_path)
                cursor.execute("""
                    INSERT INTO videos (resource_id, duration, resolution, format, frame_rate)
                    VALUES (?, ?, ?, ?, ?)
                """, (resource_id, duration, resolution, file_type.upper(), 30.0))  # 帧率默认30

            # 更新主题统计信息
            file_count += 1
            total_size += file_size
            file_types[file_type] = file_types.get(file_type, 0) + 1

        # 插入或更新主题子表
        if theme_stats.get(theme):
            theme_stats[theme]['file_count'] += file_count
            theme_stats[theme]['total_size'] += total_size
            for file_type, count in file_types.items():
                theme_stats[theme]['file_types'][file_type] = (
                        theme_stats[theme]['file_types'].get(file_type, 0) + count
                )
        else:
            theme_stats[theme] = {
                'file_count': file_count,
                'total_size': total_size,
                'file_types': file_types
            }

    # 写入主题子表
    for theme, stats in theme_stats.items():
        file_types_json = json.dumps(stats['file_types'])
        average_size = stats['total_size'] / stats['file_count'] if stats['file_count'] > 0 else 0
        cursor.execute("""
            INSERT INTO themes (name, file_count, file_types, total_size, average_file_size)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(name) DO UPDATE SET
                file_count=excluded.file_count,
                file_types=excluded.file_types,
                total_size=excluded.total_size,
                average_file_size=excluded.average_file_size
        """, (theme, stats['file_count'], file_types_json, stats['total_size'], average_size))

    conn.commit()
    print(f"Scanned directory '{base_dir}' and added resources to the database.")


if __name__ == "__main__":
    db_path = 'file_manager.db'
    initialize_database(db_path)
    conn = sqlite3.connect(db_path)

    try:
        # 要扫描的目录路径
        base_dir = r"E:COLORFUL"  # 替换为你的实际目录路径

        # 扫描目录并将数据添加到数据库
        scan_image_video_and_add_resources(conn, base_dir, source="local", source_link="N/A")
    finally:
        # 关闭数据库连接
        conn.close()