CardScoreDataServer/app/core/database_loader.py

import mysql.connector
from mysql.connector import errorcode
from .config import settings
from app.core.logger import get_logger

logger = get_logger(__name__)

# 全局的连接池
db_connection_pool = None


def init_database():
    """
    初始化数据库：如果数据库或表不存在，则创建它们。
    """
    logger.info("--- 开始初始化数据库 ---")

    try:
        cnx = mysql.connector.connect(**settings.DATABASE_CONFIG)
        cursor = cnx.cursor()

        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {settings.DB_NAME} DEFAULT CHARACTER SET 'utf8mb4'")
        logger.info(f"数据库 '{settings.DB_NAME}' 已准备就绪。")
        cnx.database = settings.DB_NAME

        # 1. 创建 cards 表
        cards_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_CARD_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_name` VARCHAR(255) NULL COMMENT '卡牌的通用名称',"
            "  `cardNo` VARCHAR(100) NULL COMMENT '卡牌编号',"
            "  `card_type` VARCHAR(50) NOT NULL DEFAULT 'pokemon',"
            "  `detection_score` DECIMAL(4, 2) NULL,"
            "  `modified_score` DECIMAL(4, 2) NULL,"
            "  `is_edited` BOOLEAN NOT NULL DEFAULT FALSE,"
            "  `review_state` TINYINT NOT NULL DEFAULT 1 COMMENT '审核状态(1待复检, 2已复检, 3审核未通过, 4审核通过)',"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
            ") ENGINE=InnoDB COMMENT='存储实体卡牌的核心信息'"
        )
        cursor.execute(cards_table)
        logger.info(f"数据表 '{settings.DB_CARD_TABLE_NAME}' 已准备就绪。")

        # User table: username is the login account, nickname is only for display.
        users_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_USER_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `username` VARCHAR(20) NOT NULL,"
            "  `nickname` VARCHAR(100) NOT NULL,"
            "  `password` VARCHAR(255) NOT NULL,"
            "  `is_admin` BOOLEAN NOT NULL DEFAULT FALSE,"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
            "  UNIQUE KEY `uk_users_username` (`username`)"
            ") ENGINE=InnoDB COMMENT='users'"
        )
        cursor.execute(users_table)
        logger.info(f"数据表 '{settings.DB_USER_TABLE_NAME}' 已准备就绪。")

        # Mapping table: one card can be assigned to multiple users.
        user_card_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_USER_CARD_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `user_id` INT NOT NULL,"
            "  `card_id` INT NOT NULL,"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            f"  FOREIGN KEY (`user_id`) REFERENCES `{settings.DB_USER_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            f"  FOREIGN KEY (`card_id`) REFERENCES `{settings.DB_CARD_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            "  UNIQUE KEY `uk_user_card` (`user_id`, `card_id`),"
            "  INDEX `idx_user_card_card_id` (`card_id`)"
            ") ENGINE=InnoDB COMMENT='user card bindings'"
        )
        cursor.execute(user_card_table)
        logger.info(f"数据表 '{settings.DB_USER_CARD_TABLE_NAME}' 已准备就绪。")

        # 2. 创建 card_images 表 (主要计算图)
        card_images_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_IMAGE_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_id` INT NOT NULL COMMENT '关联的卡牌ID',"
            "  `image_type` VARCHAR(50) NOT NULL COMMENT '图片类型 (front_coaxial, back_ring, etc)',"
            "  `image_name` VARCHAR(255) NULL,"
            "  `image_path` VARCHAR(512) NOT NULL,"
            "  `detection_image_path` VARCHAR(512) NULL,"
            "  `modified_image_path` VARCHAR(512) NULL,"
            "  `detection_json` JSON NOT NULL,"
            "  `modified_json` JSON NULL,"
            "  `is_edited` BOOLEAN NOT NULL DEFAULT FALSE,"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
            f"  FOREIGN KEY (`card_id`) REFERENCES `{settings.DB_CARD_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            "  UNIQUE KEY `uk_card_image_type` (`card_id`, `image_type`)"
            ") ENGINE=InnoDB COMMENT='存储主要卡牌图片及检测数据'"
        )
        cursor.execute(card_images_table)
        logger.info(f"数据表 '{settings.DB_IMAGE_TABLE_NAME}' 已准备就绪。")

        # 3. 创建 card_gray_images 表 (辅助灰度图)
        card_gray_images_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_GRAY_IMAGE_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_id` INT NOT NULL COMMENT '关联的卡牌ID',"
            "  `image_type` VARCHAR(50) NOT NULL COMMENT '类型 (front_gray, back_gray)',"
            "  `image_path` VARCHAR(512) NOT NULL,"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
            f"  FOREIGN KEY (`card_id`) REFERENCES `{settings.DB_CARD_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            "  UNIQUE KEY `uk_card_gray_type` (`card_id`, `image_type`)"
            ") ENGINE=InnoDB COMMENT='存储辅助灰度图'"
        )
        cursor.execute(card_gray_images_table)

        rating_report_history_table = (
            "CREATE TABLE IF NOT EXISTS `rating_report_history` ("
            "  `rating_id` BIGINT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_id` INT NOT NULL,"
            "  `cardNo` VARCHAR(100) NOT NULL,"
            "  `report_name` VARCHAR(255) NOT NULL,"
            "  `report_json` JSON NOT NULL,"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
            f"  FOREIGN KEY (`card_id`) REFERENCES `{settings.DB_CARD_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            "  INDEX `idx_rating_history_card_no_created` (`cardNo`, `created_at`),"
            "  INDEX `idx_rating_history_card_id_created` (`card_id`, `created_at`)"
            ") ENGINE=InnoDB COMMENT='拆卡报告历史'"
        )
        cursor.execute(rating_report_history_table)
        logger.info(f"数据表 '{settings.DB_GRAY_IMAGE_TABLE_NAME}' 已准备就绪。")

    except mysql.connector.Error as err:
        logger.error(f"数据库初始化失败: {err}")
        exit(1)
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()
        if 'cnx' in locals() and cnx.is_connected():
            cnx.close()

    logger.info("--- 数据库初始化完成 ---")


def load_database_pool():
    global db_connection_pool
    if db_connection_pool is None:
        logger.info("--- 创建数据库连接池 ---")
        try:
            db_connection_pool = mysql.connector.pooling.MySQLConnectionPool(
                pool_name="mypool",
                pool_size=5,
                **settings.DATABASE_CONFIG_WITH_DB
            )
            logger.info("--- 数据库连接池创建成功 ---")
        except mysql.connector.Error as err:
            logger.error(f"创建数据库连接池失败: {err}")
            exit(1)


def close_database_pool():
    logger.info("--- 数据库连接池将自动关闭 ---")


# --- FastAPI 依赖注入 ---
def get_db_connection():
    if db_connection_pool is None:
        raise RuntimeError("数据库连接池未初始化")

    db_conn = None
    try:
        db_conn = db_connection_pool.get_connection()

        # 强制设置当前数据库会话时区为东八区
        cursor = db_conn.cursor()
        cursor.execute("SET time_zone = '+08:00';")
        cursor.close()
        yield db_conn
    except mysql.connector.Error as err:
        logger.error(f"获取数据库连接失败: {err}")
    finally:
        if db_conn and db_conn.is_connected():
            db_conn.close()