"""
数据库模块使用示例
展示如何使用数据库连接、操作和迁移功能
"""

import os
from datetime import datetime
from backend.modules.database import (
    DatabaseConnection, DatabaseOperations, DatabaseMigration,
    User, ImageRecord, ProcessRecord, SystemConfig
)
from backend.utils.logger_config import setup_logger
from typing import Optional, Any
import json

logger = setup_logger(__name__)

def example_database_usage():
    """数据库使用示例"""
    
    # 1. 初始化数据库
    print("=== 初始化数据库 ===")
    migration = DatabaseMigration()
    if migration.init_database():
        print("数据库初始化成功")
    else:
        print("数据库初始化失败")
        return
    
    # 2. 创建数据库操作实例
    db_ops = DatabaseOperations()
    
    # 3. 用户管理示例
    print("\n=== 用户管理示例 ===")
    
    # 创建用户（需 password_hash，无 email）
    user = db_ops.create_user(
        username="test_user",  # 用户名
        password_hash="hash123",  # 示例 hash
        is_admin=False
    )
    print(f"创建用户: {user['username']}, ID: {user['id']}")
    
    # 查询用户
    found_user = db_ops.get_user_by_username("test_user")
    print(f"查询用户: {found_user['username'] if found_user else '未找到'}")
    
    # 4. 图片记录示例
    print("\n=== 图片记录示例 ===")
    
    # 创建图片记录（face）
    image_record = db_ops.create_image_record(
        user_id=user['id'],
        image_type="face",
        original_filename="face.jpg",
        stored_path="/data/images/face_001.jpg",
        file_size=1024000,
        image_hash=None
    )
    print(f"创建图片记录: {image_record['id']}")
    
    # 查询用户图片
    user_images = db_ops.get_user_images(user['id'], image_type="face")
    print(f"用户图片数量: {user_images['total']}")
    
    # 5. 处理记录示例
    print("\n=== 处理记录示例 ===")
    
    # 创建服装图片记录
    cloth_image = db_ops.create_image_record(
        user_id=user['id'],
        image_type="cloth",
        original_filename="cloth.jpg",
        stored_path="/data/images/cloth_001.jpg",
        file_size=2048000,
        image_hash=None
    )
    
    # 创建结果图片记录（必须先创建 result_image）
    result_image = db_ops.create_image_record(
        user_id=user['id'],
        image_type="result",
        original_filename="result.jpg",
        stored_path="/data/images/result_001.jpg",
        file_size=1536000,
        image_hash=None
    )
    
    # 创建AI换脸处理记录（需 result_image_id）
    process_record = db_ops.create_process_record(
        user_id=user['id'],
        face_image_id=image_record['id'],
        cloth_image_id=cloth_image['id'],
        result_image_id=result_image['id'],
        generated_text="美丽的海边风景，阳光明媚，心情愉悦"
    )
    print(f"创建AI换脸处理记录: {process_record['id']}")
    
    # 6. 系统配置示例
    print("\n=== 系统配置示例 ===")
    
    # 设置配置
    db_ops.set_config(
        "max_image_size",
        10485760,  # 10MB
        config_type="int",
        config_description="最大图片文件大小(字节)"
    )
    
    db_ops.set_config(
        "allowed_image_formats",
        ["jpg", "png", "gif"],
        config_type="json",
        config_description="允许的图片格式"
    )

    # 获取配置
    max_size = db_ops.get_config("max_image_size")
    allowed_formats = db_ops.get_config("allowed_image_formats")
    print(f"最大图片大小: {max_size['config_value']} 字节")
    print(f"允许的格式: {allowed_formats['config_value']}")
    
    # 7. 统计信息示例
    print("\n=== 统计信息示例 ===")
    stats = db_ops.get_statistics()
    print(f"总用户数: {stats['total_users']}")
    print(f"活跃用户数: {stats['active_users']}")
    print(f"总图片数: {stats['face_images']}")
    print(f"总处理数: {stats['total_processes']}")
    print(f"总服装图片数: {stats['cloth_images']}")
    print(f"总结果图片数: {stats['result_images']}")
    
    # 8. 数据库健康检查
    print("\n=== 数据库健康检查 ===")
    health = migration.check_database_health()
    print(f"状态: {health['status']}")
    print(f"当前版本: {health['current_version']}")
    print(f"总迁移数: {health['total_migrations']}")
    print(f"待执行迁移: {health['pending_migrations']}")
    
    if health['errors']:
        print(f"错误: {health['errors']}")


def example_migration_usage():
    """数据库迁移示例"""
    print("\n=== 数据库迁移示例 ===")
    
    migration = DatabaseMigration()
    
    # 创建迁移文件
    migration_file = migration.create_migration(
        version="1.2.0",
        description="添加任务类型和提示词字段",
        up_sql="""
        ALTER TABLE process_records ADD COLUMN task_type TEXT;
        ALTER TABLE process_records ADD COLUMN prompt TEXT;
        """,
        down_sql="""
        CREATE TABLE process_records_backup AS 
        SELECT id, user_id, face_image_id, cloth_image_id, result_image_id, 
               generated_text, status, completed_at
        FROM process_records;
        
        DROP TABLE process_records;
        
        ALTER TABLE process_records_backup RENAME TO process_records;
        """
    )
    print(f"创建迁移文件: {migration_file}")
    
    # 执行迁移
    if migration.migrate_up():
        print("迁移执行成功")
    else:
        print("迁移执行失败")

    # 执行迁移回滚（回滚到 1.0.0 版本）
    # if migration.migrate_down("1.0.0"):
    #     print("迁移回滚成功")
    # else:
    #     print("迁移回滚失败")

    # 执行健康检查
    health = migration.check_database_health()
    print(f"状态: {health['status']}")
    print(f"当前版本: {health['current_version']}")
    print(f"总迁移数: {health['total_migrations']}")
    print(f"待执行迁移: {health['pending_migrations']}")
    if health['errors']:
        print(f"错误: {health['errors']}")

if __name__ == "__main__":
    # 运行示例
    # example_database_usage()
    example_migration_usage()