import pymysql
import json
import os
from datetime import datetime

# 读取配置文件
# 在 database.py 中添加项目根目录计算（与 file_importer.py 逻辑相同）
current_file = os.path.abspath(__file__)  # src/utils/database.py
utils_dir = os.path.dirname(current_file)
src_dir = os.path.dirname(utils_dir)
project_root = os.path.dirname(src_dir)
config_path = os.path.join(project_root, "config", "settings.json")

# 读取配置文件
with open(config_path, "r", encoding="utf-8") as f:
    CONFIG = json.load(f)

# 数据库配置
DB_TYPE = CONFIG["database"]["type"]
MYSQL_CONFIG = CONFIG["database"]["mysql"]
TABLE_STUDENT = CONFIG["database"]["table_student"]
TABLE_RECORD = CONFIG["database"]["table_record"]

class Database:
    """MySQL 数据库操作类（适配 db4free 免费 MySQL）"""
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.connect()  # 初始化连接
        self.create_tables()  # 初始化表结构

    def connect(self):
        """连接 MySQL 数据库（处理 db4free 连接特性）"""
        try:
            if DB_TYPE == "mysql":
                self.conn = pymysql.connect(
                    host=MYSQL_CONFIG["host"],
                    port=MYSQL_CONFIG["port"],
                    user=MYSQL_CONFIG["user"],
                    password=MYSQL_CONFIG["password"],
                    database=MYSQL_CONFIG["dbname"],
                    charset=MYSQL_CONFIG["charset"],
                    connect_timeout=30  # db4free 外网连接超时设为30秒
                )
                self.cursor = self.conn.cursor()
                print(f"成功连接 db4free MySQL 数据库：{MYSQL_CONFIG['dbname']}")
            # 保留 SQLite 兼容（可选）
            elif DB_TYPE == "sqlite":
                import sqlite3
                db_path = "config/database.db"
                os.makedirs(os.path.dirname(db_path), exist_ok=True)
                self.conn = sqlite3.connect(db_path, check_same_thread=False)
                self.cursor = self.conn.cursor()
        except pymysql.MySQLError as e:
            raise Exception(f"MySQL 连接失败：{e}")  # 抛出异常让上层处理
        except Exception as e:
            raise Exception(f"数据库连接失败：{e}")

    def create_tables(self):
        """创建表结构（适配 MySQL 语法）"""
        if DB_TYPE == "mysql":
            # 学生表：指定 ENGINE=InnoDB 支持外键，TINYINT 替代布尔类型
            create_student_sql = f"""
                CREATE TABLE IF NOT EXISTS {TABLE_STUDENT} (
                    student_id VARCHAR(20) PRIMARY KEY COMMENT '学号（唯一标识）',
                    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
                    major VARCHAR(50) NOT NULL COMMENT '所属专业',
                    total_score FLOAT DEFAULT 0 COMMENT '总积分',
                    random_call_num INT DEFAULT 0 COMMENT '随机点名次数'
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
            """

            # 点名记录表：自增主键 AUTO_INCREMENT，外键关联学生表
            create_record_sql = f"""
                CREATE TABLE IF NOT EXISTS {TABLE_RECORD} (
                    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增主键',
                    student_id VARCHAR(20) NOT NULL COMMENT '关联学生学号',
                    call_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '点名时间',
                    call_mode VARCHAR(10) NOT NULL COMMENT '点名模式：random/order',
                    is_arrived TINYINT(1) NOT NULL COMMENT '是否到课：1=是，0=否',
                    answer_score FLOAT DEFAULT 0 COMMENT '回答问题加分值',
                    total_add FLOAT DEFAULT 0 COMMENT '本次总加分',
                    FOREIGN KEY (student_id) REFERENCES {TABLE_STUDENT}(student_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='点名记录表';
            """
        else:
            # 保留 SQLite 语法（兼容用）
            create_student_sql = f"""
                CREATE TABLE IF NOT EXISTS {TABLE_STUDENT} (
                    student_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    major TEXT NOT NULL,
                    total_score FLOAT DEFAULT 0,
                    random_call_num INT DEFAULT 0
                );
            """
            create_record_sql = f"""
                CREATE TABLE IF NOT EXISTS {TABLE_RECORD} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    student_id TEXT NOT NULL,
                    call_time DATETIME DEFAULT '{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}',
                    call_mode TEXT NOT NULL,
                    is_arrived BOOLEAN NOT NULL,
                    answer_score FLOAT DEFAULT 0,
                    total_add FLOAT DEFAULT 0,
                    FOREIGN KEY (student_id) REFERENCES {TABLE_STUDENT}(student_id)
                );
            """

        try:
            self.cursor.execute(create_student_sql)
            self.cursor.execute(create_record_sql)
            self.conn.commit()
            print("表结构初始化成功")
        except pymysql.MySQLError as e:
            self.conn.rollback()  # MySQL 事务回滚
            raise Exception(f"创建表失败：{e}")
        except Exception as e:
            raise Exception(f"创建表失败：{e}")

    def add_student(self, student_id: str, name: str, major: str) -> bool:
        """添加单个学生（防重复插入）"""
        try:
            # 【修复】移除 SQL 语句中的注释，避免干扰参数替换
            sql = f"""
                INSERT IGNORE INTO {TABLE_STUDENT} (student_id, name, major)
                VALUES (%s, %s, %s)
            """
            self.cursor.execute(sql, (student_id, name, major))
            self.conn.commit()
            return True
        except pymysql.MySQLError as e:
            self.conn.rollback()
            print(f"添加学生失败：{e}")
            return False

    def batch_add_students(self, students: list) -> tuple:
        """批量添加学生（列表元素：(学号, 姓名, 专业)）"""
        success_count = 0
        fail_list = []
        for s in students:
            if self.add_student(*s):
                success_count += 1
            else:
                fail_list.append(s)
        return success_count, fail_list

    def update_student_score(self, student_id: str, add_score: float, is_random: bool = False) -> bool:
        """更新学生积分和随机点名次数"""
        try:
            # 查询当前积分和点名次数
            select_sql = f"""
                SELECT total_score, random_call_num FROM {TABLE_STUDENT} WHERE student_id = %s;
            """
            self.cursor.execute(select_sql, (student_id,))
            res = self.cursor.fetchone()
            if not res:
                return False

            # 计算新值
            new_score = res[0] + add_score
            new_call_num = res[1] + 1 if is_random else res[1]

            # 更新数据
            update_sql = f"""
                UPDATE {TABLE_STUDENT} SET total_score = %s, random_call_num = %s WHERE student_id = %s;
            """
            self.cursor.execute(update_sql, (new_score, new_call_num, student_id))
            self.conn.commit()
            return True
        except pymysql.MySQLError as e:
            self.conn.rollback()
            print(f"更新积分失败：{e}")
            return False

    def add_call_record(self, student_id: str, call_mode: str, is_arrived: bool, answer_score: float) -> bool:
        """添加点名记录并同步更新积分"""
        with open("config/settings.json", "r", encoding="utf-8") as f:
            score_rules = json.load(f)["score_rules"]
        
        # 计算本次总加分
        total_add = score_rules["arrive_score"] if is_arrived else 0
        total_add += answer_score

        try:
            # 插入点名记录（MySQL 布尔值用 1/0 存储）
            insert_sql = f"""
                INSERT INTO {TABLE_RECORD} (student_id, call_mode, is_arrived, answer_score, total_add)
                VALUES (%s, %s, %s, %s, %s);
            """
            self.cursor.execute(insert_sql, (
                student_id, call_mode, 1 if is_arrived else 0, answer_score, total_add
            ))

            # 同步更新学生积分
            self.update_student_score(student_id, total_add, call_mode == "random")
            self.conn.commit()
            return True
        except pymysql.MySQLError as e:
            self.conn.rollback()
            print(f"添加点名记录失败：{e}")
            return False

    def get_all_students(self) -> list:
        """获取所有学生信息（按学号排序）"""
        try:
            sql = f"""
                SELECT student_id, name, major, total_score, random_call_num FROM {TABLE_STUDENT} ORDER BY student_id;
            """
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except pymysql.MySQLError as e:
            print(f"查询学生失败：{e}")
            return []

    def get_student_by_id(self, student_id: str) -> tuple:
        """按学号查询学生"""
        try:
            sql = f"""
                SELECT student_id, name, major, total_score, random_call_num FROM {TABLE_STUDENT} WHERE student_id = %s;
            """
            self.cursor.execute(sql, (student_id,))
            return self.cursor.fetchone()
        except pymysql.MySQLError as e:
            print(f"查询单个学生失败：{e}")
            return None

    def reconnect(self):
        """重连数据库（解决 db4free 长时间空闲断开连接问题）"""
        if not self.conn or not self.conn.open:
            self.connect()

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        print("数据库连接已关闭")

# 单例模式：全局唯一数据库实例
try:
    db_instance = Database()
except Exception as e:
    print(f"数据库初始化失败：{e}")
    db_instance = None  # 初始化失败时设为 None