"""
SQLite数据库管理类
提供数据库连接、初始化和基本操作功能
"""

import sqlite3
import os
import threading
import time
from typing import Optional, List, Dict, Any, Text


class DatabaseManager:
    """SQLite数据库管理器（单例模式）"""
    
    _instance = None
    _initialized = False
    
    def __new__(cls, db_path: str = "src/database/app_database.db"):
        """
        确保只创建一个实例
        
        Args:
            db_path: 数据库文件路径
        """
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance
    
    def __init__(self, db_path: str = "src/database/app_database.db"):
        """
        初始化数据库管理器
        
        Args:
            db_path: 数据库文件路径
        """
        if not DatabaseManager._initialized:
            self.db_path = db_path
            self.connection: Optional[sqlite3.Connection] = None
            self._lock = threading.RLock()  # 添加线程锁
            self._connection_timeout = 5.0  # 连接超时时间（秒）
            self._operation_timeout = 30.0  # 操作超时时间（秒）
            DatabaseManager._initialized = True
        
    def connect(self) -> bool:
        """
        连接到数据库
        
        Returns:
            bool: 连接是否成功
        """
        try:
            # 确保数据库文件所在目录存在
            db_dir = os.path.dirname(self.db_path)
            if db_dir and not os.path.exists(db_dir):
                os.makedirs(db_dir)
                
            # 建立数据库连接，启用线程安全检查和超时设置
            self.connection = sqlite3.connect(
                self.db_path, 
                check_same_thread=False, 
                timeout=self._connection_timeout,
                isolation_level=None  # 自动提交模式，避免事务隔离问题
            )
            self.connection.row_factory = sqlite3.Row  # 使结果可以通过列名访问
            
            # 设置 WAL 模式以改善并发性能
            cursor = self.connection.cursor()
            cursor.execute("PRAGMA journal_mode=WAL")
            cursor.execute("PRAGMA synchronous=NORMAL")
            cursor.close()
            
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            self.connection = None
            return False
            
    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            try:
                self.connection.close()
            except Exception as e:
                print(f"关闭数据库连接时出错: {e}")
            finally:
                self.connection = None

    # 添加一个方法检查连接是否有效
    def _ensure_connection(self):
        """确保数据库连接有效"""
        if not self.connection:
            self.connect()
        else:
            try:
                # 测试连接是否有效，添加超时保护
                start_time = time.time()
                cursor = self.connection.cursor()
                cursor.execute("SELECT 1")
                # 检查操作是否超时
                if time.time() - start_time > self._operation_timeout:
                    raise TimeoutError("数据库操作超时")
            except Exception as e:
                print(f"数据库连接测试失败: {e}")
                # 连接失效，重新连接
                self.connect()
            
    def execute_query(self, query: str, params: tuple = ()) -> List[Dict[str, Any]]:
        """
        执行SELECT查询
        
        Args:
            query: SQL查询语句
            params: 查询参数
            
        Returns:
            查询结果列表
        """
        start_time = time.time()
        with self._lock:
            try:
                # 检查操作是否超时（在获取锁之前可能已经等待很久）
                if time.time() - start_time > self._operation_timeout:
                    raise TimeoutError("获取数据库锁超时")
                    
                self._ensure_connection()
                if not self.connection:
                    raise Exception("数据库未连接")
                    
                cursor = self.connection.cursor()
                cursor.execute(query, params)
                rows = cursor.fetchall()
                cursor.close()
                
                # 将sqlite3.Row对象转换为字典列表
                results = []
                for row in rows:
                    results.append(dict(row))
                
                return results
            except TimeoutError:
                print("数据库查询操作超时")
                return []
            except Exception as e:
                print(f"数据库查询执行失败: {e}")
                return []
            
    def execute_update(self, query: str, params: tuple = ()) -> bool:
        """
        执行UPDATE、INSERT、DELETE等操作
        
        Args:
            query: SQL语句
            params: SQL参数
            
        Returns:
            bool: 操作是否成功
        """
        start_time = time.time()
        with self._lock:
            try:
                # 检查操作是否超时
                if time.time() - start_time > self._operation_timeout:
                    raise TimeoutError("获取数据库锁超时")
                    
                self._ensure_connection()
                if not self.connection:
                    raise Exception("数据库未连接")
                    
                cursor = self.connection.cursor()
                cursor.execute(query, params)
                # 使用自动提交模式，不需要手动 commit
                # self.connection.commit()  
                cursor.close()
                return True
            except TimeoutError:
                print("数据库更新操作超时")
                return False
            except Exception as e:
                print(f"数据库更新执行失败: {e}")
                # 回滚事务
                if self.connection:
                    try:
                        self.connection.rollback()
                    except:
                        pass
                return False

    def initialize_database(self):
        """
        初始化数据库，创建必要的表格
        """
        try:
            self._ensure_connection()
            if not self.connection:
                raise Exception("数据库未连接")
                
            # 创建任务记录表
            create_task_records_table = """
            CREATE TABLE IF NOT EXISTS task_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id TEXT NOT NULL,
                task_type TEXT NOT NULL,
                status TEXT NOT NULL,
                start_time TEXT,
                end_time TEXT,
                duration REAL,
                message TEXT,
                create_time TEXT DEFAULT CURRENT_TIMESTAMP
            );
            """
            self.execute_update(create_task_records_table)
            
            # 创建模型信息表
            create_model_info_table = """
            CREATE TABLE IF NOT EXISTS model_info (
                model_id TEXT PRIMARY KEY NOT NULL,
                model_name TEXT NOT NULL DEFAULT 'Unnamed Model',
                split_id TEXT NOT NULL,
                split_name TEXT,
                task_id TEXT,
                state INTEGER,
                assess_state INTEGER,
                export_state INTEGER,
                fail_reason TEXT,
                epochs TEXT,
                batch_size TEXT,
                lr TEXT,
                remark TEXT,
                curr_epochs INTEGER,
                curr_batch INTEGER,
                best_epoch TEXT,
                create_time TEXT DEFAULT CURRENT_TIMESTAMP,
                start_time TEXT,
                end_time TEXT,
                model_info TEXT,
                eva_metrics TEXT,
                eva_predictions TEXT,
                eva_start_time TEXT,
                eva_end_time TEXT,
                batchs TEXT,
                eva_task_id TEXT,
                loss REAL,
                eva_mean_duration TEXT,
                eva_conf TEXT,
                eva_progress TEXT
            );
            """
            self.execute_update(create_model_info_table)
            
            # 创建模型记录表
            create_model_record_table = """
            CREATE TABLE IF NOT EXISTS model_record (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                model_id TEXT NOT NULL,
                task_id TEXT NOT NULL,
                split_id TEXT NOT NULL,
                split_name TEXT,
                loss REAL,
                lr TEXT,
                epoch TEXT,
                metrics TEXT,
                type INTEGER,
                create_time TEXT DEFAULT CURRENT_TIMESTAMP
            );
            """
            self.execute_update(create_model_record_table)
            
            print("数据库初始化完成")
            return True
        except Exception as e:
            print(f"数据库初始化失败: {e}")
            return False

    def migrate_model_record_table(self):
        """
        迁移模型记录表结构
        - 增加epoch字段
        - 增加metrics字段
        - 增加type字段
        """
        try:
            self._ensure_connection()
            if not self.connection:
                raise Exception("数据库未连接")
                
            # 检查epoch字段是否存在
            cursor = self.connection.cursor()
            cursor.execute("PRAGMA table_info(model_record)")
            columns = [row[1] for row in cursor.fetchall()]
            
            # 如果epoch字段不存在，添加该字段
            if 'epoch' not in columns:
                self.execute_update("ALTER TABLE model_record ADD COLUMN epoch TEXT")
                print("添加epoch字段完成")
            
            # 如果metrics字段不存在，添加该字段
            if 'metrics' not in columns:
                self.execute_update("ALTER TABLE model_record ADD COLUMN metrics TEXT")
                print("添加metrics字段完成")
            
            # 如果type字段不存在，添加该字段
            if 'type' not in columns:
                self.execute_update("ALTER TABLE model_record ADD COLUMN type INTEGER")
                print("添加type字段完成")
            
            print("模型记录表结构迁移完成")
        except Exception as e:
            print(f"模型记录表结构迁移失败: {e}")
            raise

    def insert_model_record(self, model_id: str, task_id: str, loss: float = None,
                           lr: str = None, epoch: str = None, metrics: str = None, type: int = None) -> bool:
        """
        插入模型记录
        
        Args:
            model_id: 模型ID
            task_id: 任务ID
            loss: 损失值
            lr: 学习率
            epoch: 轮数
            metrics: 评估指标
            type: 类型
            
        Returns:
            bool: 插入是否成功
        """
        try:
            # 先根据task_id查询split_id和split_name
            model_info = self.get_model_info_by_task_id(task_id)
            if not model_info:
                print(f"未找到task_id为{task_id}的模型信息")
                return False
            
            split_id = model_info["split_id"]
            split_name = model_info["split_name"]
            
            insert_query = """
            INSERT INTO model_record (model_id, task_id, split_id, split_name, loss, lr, epoch, metrics, type)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
            
            # 插入模型记录，包含 split_id 和 split_name
            self.execute_update(insert_query, (model_id, task_id, split_id, split_name, loss, lr, epoch, metrics, type))
            return True
        except Exception as e:
            print(f"插入模型记录失败: {e}")
            return False

    def get_model_record_by_model_id(self, model_id: str) -> List[Dict[str, Any]]:
        """
        根据model_id获取模型记录，按create_time升序排序
        
        Args:
            model_id: 模型ID
            
        Returns:
            模型记录列表
        """
        query = "SELECT * FROM model_record WHERE model_id = ? ORDER BY create_time ASC"
        
        try:
            results = self.execute_query(query, (model_id,))
            return results
        except Exception as e:
            print(f"根据model_id获取模型记录失败: {e}")
            return []

    def get_model_records_by_task_id(self, task_id: str) -> List[Dict[str, Any]]:
        """
        根据task_id获取模型记录
        
        Args:
            task_id: 任务ID
            
        Returns:
            模型记录列表
        """
        query = "SELECT * FROM model_record WHERE task_id = ? ORDER BY create_time ASC"
        
        try:
            results = self.execute_query(query, (task_id,))
            return results
        except Exception as e:
            print(f"根据task_id获取模型记录失败: {e}")
            return []

    def get_model_record_by_model_id_and_task_id(self, model_id: str, task_id: str) -> List[Dict[str, Any]]:
        """
        根据model_id和task_id获取模型记录
        
        Args:
            model_id: 模型ID
            task_id: 任务ID
            
        Returns:
            模型记录列表
        """
        query = "SELECT * FROM model_record WHERE model_id = ? AND task_id = ? ORDER BY create_time ASC"
        
        try:
            results = self.execute_query(query, (model_id, task_id))
            return results
        except Exception as e:
            print(f"根据model_id和task_id获取模型记录失败: {e}")
            return []

    def update_model_record(self, id: int, **kwargs) -> bool:
        """
        更新模型记录
        
        Args:
            id: 记录ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        # 构建更新语句
        valid_fields = {'loss', 'lr', 'epoch', 'metrics', 'type'}
        
        # 过滤出有效的字段
        update_fields = {k: v for k, v in kwargs.items() if k in valid_fields and v is not None}
        
        if not update_fields:
            return False
            
        # 构建SET子句
        set_clause = ', '.join([f"{field} = ?" for field in update_fields.keys()])
        update_query = f"UPDATE model_record SET {set_clause} WHERE id = ?"
        
        # 构建参数列表
        params = list(update_fields.values()) + [id]
        
        try:
            self.execute_update(update_query, params)
            return True
        except Exception as e:
            print(f"更新模型记录失败: {e}")
            return False

    def delete_model_record(self, id: int) -> bool:
        """
        删除模型记录
        
        Args:
            id: 记录ID
            
        Returns:
            bool: 删除是否成功
        """
        delete_query = "DELETE FROM model_record WHERE id = ?"
        
        try:
            self.execute_update(delete_query, (id,))
            return True
        except Exception as e:
            print(f"删除模型记录失败: {e}")
            return False

    def get_all_model_records(self) -> List[Dict[str, Any]]:
        """
        获取所有模型记录
        
        Returns:
            所有模型记录列表
        """
        query = "SELECT * FROM model_record"
        
        try:
            results = self.execute_query(query)
            return results
        except Exception as e:
            print(f"获取所有模型记录失败: {e}")
            return []

    def insert_task_record(self, task_id: str, task_type: str, status: str,
                          start_time: str = None, end_time: str = None,
                          duration: float = None, message: str = None) -> bool:
        """
        插入任务记录
        
        Args:
            task_id: 任务ID
            task_type: 任务类型
            status: 任务状态
            start_time: 开始时间
            end_time: 结束时间
            duration: 持续时间
            message: 消息
            
        Returns:
            bool: 插入是否成功
        """
        insert_query = """
        INSERT INTO task_records 
        (task_id, task_type, status, start_time, end_time, duration, message)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """
        
        try:
            self.execute_update(insert_query, (
                task_id, task_type, status, start_time, end_time, duration, message
            ))
            return True
        except Exception as e:
            print(f"插入任务记录失败: {e}")
            return False

    def get_task_record(self, task_id: str) -> Optional[Dict[str, Any]]:
        """
        获取任务记录
        
        Args:
            task_id: 任务ID
            
        Returns:
            任务记录字典或None
        """
        query = "SELECT * FROM task_records WHERE task_id = ?"
        
        try:
            results = self.execute_query(query, (task_id,))
            return results[0] if results else None
        except Exception as e:
            print(f"获取任务记录失败: {e}")
            return None

    def get_model_info_by_model_id(self, model_id: str) -> List[Dict[str, Any]]:
        """
        根据model_id获取模型信息记录，按create_time由近到远排序
        
        Args:
            model_id: 模型ID
            
        Returns:
            模型信息记录列表
        """
        query = "SELECT * FROM model_info WHERE model_id = ? ORDER BY create_time DESC"
        
        try:
            results = self.execute_query(query, (model_id,))
            return results
        except Exception as e:
            print(f"根据model_id获取模型信息记录失败: {e}")
            return []

    def get_model_info_by_split_id(self, split_id: str) -> Optional[Dict[str, Any]]:
        """
        根据split_id获取模型信息记录
        
        Args:
            split_id: 分割ID
            
        Returns:
            模型信息记录字典或None
        """
        query = "SELECT * FROM model_info WHERE split_id = ?"
        
        try:
            results = self.execute_query(query, (split_id,))
            return results[0] if results else None
        except Exception as e:
            print(f"根据split_id获取模型信息记录失败: {e}")
            return None

    def get_model_info_by_task_id(self, task_id: str) -> Optional[Dict[str, Any]]:
        """
        根据task_id获取模型信息记录
        
        Args:
            task_id: 任务ID
            
        Returns:
            模型信息记录字典或None
        """
        query = "SELECT * FROM model_info WHERE task_id = ?"
        
        try:
            results = self.execute_query(query, (task_id,))
            return results[0] if results else None
        except Exception as e:
            print(f"根据task_id获取模型信息记录失败: {e}")
            return None

    def update_model_info_by_task_id(self, task_id: str, **kwargs) -> bool:
        """
        根据task_id更新模型信息记录中的任意字段
        
        Args:
            task_id: 任务ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        # 构建更新语句
        valid_fields = {'split_name', 'model_id', 'task_id', 'state', 'assess_state', 'export_state',
                       'fail_reason', 'start_time', 'end_time', 'model_info', 'epochs', 'batch_size', 'lr', 'remark', 'curr_epochs', 'curr_batch', 'best_epoch','eva_metrics', 'eva_predictions', 'eva_start_time', 'eva_end_time', 'batchs', 'eva_task_id', 'loss', 'eva_mean_duration','eva_conf', 'eva_progress'}
        
        # 过滤出有效的字段
        update_fields = {k: v for k, v in kwargs.items() if k in valid_fields and v is not None}
        
        if not update_fields:
            return False
            
        # 构建SET子句
        set_clause = ', '.join([f"{field} = ?" for field in update_fields.keys()])
        update_query = f"UPDATE model_info SET {set_clause} WHERE task_id = ?"
        
        # 构建参数列表
        params = list(update_fields.values()) + [task_id]
        
        try:
            self.execute_update(update_query, params)
            return True
        except Exception as e:
            print(f"根据task_id更新模型信息失败: {e}")
            return False

    def insert_model_record_by_task(self, task_id: str, loss: float = None, 
                                   lr: float = None,epoch: str = None,metrics: str = None,type: int = None) -> bool:
        """
        根据task_id、loss、lr新增模型记录
        先根据task_id查询model_info的信息，再补充split_id等，最后再插入
        
        Args:
            task_id: 任务ID
            loss: 损失值
            lr: 学习率
            
        Returns:
            bool: 插入是否成功
        """
        try:
            # 先根据task_id查询model_info的信息
            model_info = self.get_model_info_by_task_id(task_id)
            if not model_info:
                print(f"未找到task_id为{task_id}的模型信息")
                return False
            
            # 获取model_id
            model_id = model_info["model_id"]
            print("插入模型记录======:"+str(type))
            # 插入模型记录
            return self.insert_model_record(model_id=model_id, task_id=task_id, loss=loss, lr=lr,epoch=epoch,metrics=metrics,type=type)
        except Exception as e:
            print(f"根据task_id新增模型记录失败: {e}")
            return False

    def insert_model_info(self, model_id: str, model_name: str = None, split_id: str = None, split_name: str = None,
                         task_id: str = None, state: int = 0, assess_state: int = 0, 
                         export_state: int = 0, fail_reason: str = None, epochs: str = None,
                         batch_size: str = None, lr: str = None, remark: str = None,
                         curr_epochs: int = None, curr_batch: int = None, best_epoch: str = None,
                         start_time: str = None, end_time: str = None, model_info: str = None,
                         eva_metrics: str = None, eva_predictions: str = None, eva_start_time: str = None, 
                         eva_end_time: str = None, batchs: str = None, eva_task_id: str = None, 
                         loss: float = None, eva_mean_duration: str = None,eva_conf: str = None) -> bool:
        """
        插入模型信息
        
        Args:
            model_id: 模型ID（主键）
            model_name: 模型名称
            split_id: 分割ID
            split_name: 分割名称
            task_id: 任务ID
            state: 状态
            assess_state: 评估状态
            export_state: 导出状态
            fail_reason: 失败原因
            epochs: 训练轮数
            batch_size: 批次大小
            lr: 学习率
            remark: 备注
            curr_epochs: 当前轮数
            curr_batch: 当前批次
            best_epoch: 最佳轮数
            start_time: 开始时间
            end_time: 结束时间
            model_info: 模型信息大字符串
            eva_metrics: 评估指标大字符串
            eva_predictions: 评估预测结果大字符串
            eva_start_time: 评估开始时间
            eva_end_time: 评估结束时间
            batchs: 批次数
            eva_task_id: 评估任务ID
            loss: 损失值
            eva_mean_duration: 评估平均推理时间
            eva_conf: 评估配置
        Returns:
            bool: 插入是否成功
        """
        insert_query = """
        INSERT INTO model_info (
            model_id, model_name, split_id, split_name, task_id, state, 
            assess_state, export_state, fail_reason, epochs, batch_size, lr, 
            remark, curr_epochs, curr_batch, best_epoch, start_time, 
            end_time, model_info,
            eva_metrics, eva_predictions, eva_start_time, eva_end_time, batchs, eva_task_id, loss,
            eva_mean_duration,eva_conf
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        
        try:
            return self.execute_update(insert_query, (
                model_id, model_name, split_id, split_name, task_id, state, 
                assess_state, export_state, fail_reason, epochs, batch_size, lr, 
                remark, curr_epochs, curr_batch, best_epoch, start_time, 
                end_time, model_info,
                eva_metrics, eva_predictions, eva_start_time, eva_end_time, batchs, eva_task_id, loss,
                eva_mean_duration,eva_conf
            ))
        except Exception as e:
            print(f"插入模型信息失败: {e}")
            return False

    def get_model_records_by_split_id(self, split_id: str) -> List[Dict[str, Any]]:
        """
        根据split_id获取模型记录，按create_time升序排序
        
        Args:
            split_id: 分割ID
            
        Returns:
            模型记录列表
        """
        query = "SELECT * FROM model_record WHERE split_id = ? ORDER BY create_time ASC"
        
        try:
            results = self.execute_query(query, (split_id,))
            return results
        except Exception as e:
            print(f"根据split_id获取模型记录失败: {e}")
            return []

    def get_model_info(self, model_id: str) -> Optional[Dict[str, Any]]:
        """
        根据model_id获取模型信息
        
        Args:
            model_id: 模型ID
            
        Returns:
            模型信息字典或None
        """
        query = "SELECT * FROM model_info WHERE model_id = ?"
        
        try:
            results = self.execute_query(query, (model_id,))
            return results[0] if results else None
        except Exception as e:
            print(f"获取模型信息失败: {e}")
            return None

    def update_model_info(self, model_id: str, **kwargs) -> bool:
        """
        更新模型信息
        
        Args:
            model_id: 模型ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        # 构建更新语句
        valid_fields = {
            'model_name', 'split_id', 'split_name', 'task_id', 'state', 
            'assess_state', 'export_state', 'fail_reason', 'epochs', 'batch_size', 
            'lr', 'remark', 'curr_epochs', 'curr_batch', 'best_epoch', 
            'start_time', 'end_time', 'model_info','eva_metrics', 'eva_predictions',
            'eva_start_time', 'eva_end_time', 'batchs', 'eva_task_id', 'loss',
            'eva_mean_duration','eva_conf'
        }
        
        # 过滤出有效的字段
        update_fields = {k: v for k, v in kwargs.items() if k in valid_fields and v is not None}
        
        if not update_fields:
            return False
            
        # 构建SET子句
        set_clause = ', '.join([f"{field} = ?" for field in update_fields.keys()])
        update_query = f"UPDATE model_info SET {set_clause} WHERE model_id = ?"
        
        # 构建参数列表
        params = list(update_fields.values()) + [model_id]
        
        try:
            self.execute_update(update_query, params)
            return True
        except Exception as e:
            print(f"更新模型信息失败: {e}")
            return False

    def delete_model_info(self, model_id: str) -> bool:
        """
        删除模型信息
        
        Args:
            model_id: 模型ID
            
        Returns:
            bool: 删除是否成功
        """
        delete_query = "DELETE FROM model_info WHERE model_id = ?"
        
        try:
            self.execute_update(delete_query, (model_id,))
            return True
        except Exception as e:
            print(f"删除模型信息失败: {e}")
            return False

    def get_all_model_info(self) -> List[Dict[str, Any]]:
        """
        获取所有模型信息
        
        Returns:
            所有模型信息列表
        """
        query = "SELECT * FROM model_info"
        
        try:
            results = self.execute_query(query)
            return results
        except Exception as e:
            print(f"获取所有模型信息失败: {e}")
            return []

    def migrate_model_info_table(self):
        """
        迁移model_info表结构
        - 将split_id从主键改为普通字段
        - 添加model_id作为主键
        - 增加model_name字段
        """
        try:
            self._ensure_connection()
            if not self.connection:
                raise Exception("无法连接到数据库")

            # 1. 创建临时表
            create_temp_table = """
            CREATE TABLE IF NOT EXISTS temp_model_info (
                model_id TEXT PRIMARY KEY NOT NULL,
                model_name TEXT NOT NULL DEFAULT 'Unnamed Model',
                split_id TEXT NOT NULL,
                split_name TEXT,
                task_id TEXT,
                state INTEGER,
                assess_state INTEGER,
                export_state INTEGER,
                fail_reason TEXT,
                epochs TEXT,
                batch_size TEXT,
                lr TEXT,
                remark TEXT,
                curr_epochs INTEGER,
                curr_batch INTEGER,
                best_epoch TEXT,
                create_time TEXT DEFAULT CURRENT_TIMESTAMP,
                start_time TEXT,
                end_time TEXT,
                model_info TEXT,
                eva_metrics TEXT,
                eva_predictions TEXT,
                eva_start_time TEXT,
                eva_end_time TEXT,
                batchs TEXT,
                eva_task_id TEXT,
                loss REAL
            );
            """
            self.execute_update(create_temp_table)

            # 2. 复制数据到临时表
            # 假设原表中model_id字段已有值，我们将使用它作为新主键
            # 如果没有，这里需要调整逻辑生成model_id
            insert_into_temp = """
            INSERT INTO temp_model_info (
                model_id, model_name, split_id, split_name, task_id, state,
                assess_state, export_state, fail_reason, epochs, batch_size, lr,
                remark, curr_epochs, curr_batch, best_epoch, create_time,
                start_time, end_time, model_info, eva_metrics, eva_predictions,
                eva_start_time, eva_end_time, batchs, eva_task_id, loss
            )
            SELECT 
                model_id, 'Unnamed Model' as model_name, split_id, split_name, task_id, state,
                assess_state, export_state, fail_reason, epochs, batch_size, lr,
                remark, curr_epochs, curr_batch, best_epoch, create_time,
                start_time, end_time, model_info, eva_metrics, eva_predictions,
                '' as eva_start_time, '' as eva_end_time, '' as batchs, '' as eva_task_id, NULL as loss
            FROM model_info
            """
            self.execute_update(insert_into_temp)

            # 3. 删除原表
            self.execute_update("DROP TABLE IF EXISTS model_info")

            # 4. 重命名临时表
            self.execute_update("ALTER TABLE temp_model_info RENAME TO model_info")

            print("模型信息表迁移完成")
        except Exception as e:
            print(f"模型信息表迁移失败: {e}")
            raise

    def get_model_record(self, id: int) -> Optional[Dict[str, Any]]:
        """
        根据ID获取模型记录
        
        Args:
            id: 记录ID
            
        Returns:
            模型记录字典或None
        """
        query = "SELECT * FROM model_record WHERE id = ?"
        
        try:
            results = self.execute_query(query, (id,))
            return results[0] if results else None
        except Exception as e:
            print(f"获取模型记录失败: {e}")
            return None

    def update_model_info_by_eva_task_id(self, task_id: str, **kwargs) -> bool:
        """
        根据eva_task_id更新模型信息记录中的任意字段
        
        Args:
            eva_task_id: 评估任务ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        # 构建更新语句
        valid_fields = {'split_name', 'model_id', 'task_id', 'state', 'assess_state', 'export_state',
                       'fail_reason', 'start_time', 'end_time', 'model_info', 'epochs', 'batch_size',
                       'lr', 'remark', 'curr_epochs', 'curr_batch', 'best_epoch','eva_metrics',
                       'eva_predictions', 'eva_start_time', 'eva_end_time', 'batchs', 'eva_task_id',
                       'loss', 'eva_mean_duration', 'eva_progress'}

        
        # 过滤出有效的字段
        update_fields = {k: v for k, v in kwargs.items() if k in valid_fields and v is not None}
        
        if not update_fields:
            return False
            
        # 构建SET子句
        set_clause = ', '.join([f"{field} = ?" for field in update_fields.keys()])
        update_query = f"UPDATE model_info SET {set_clause} WHERE eva_task_id = ?"
        
        # 构建参数列表
        params = list(update_fields.values()) + [task_id]
        
        try:
            return self.execute_update(update_query, params)
        except Exception as e:
            print(f"根据eva_task_id更新模型信息记录失败: {e}")
            return False
            
    def get_latest_model_record_by_type_and_model_id(self, type: int, model_id: str) -> Optional[Dict[str, Any]]:
        """
        根据type和model_id查询最新的模型记录
        
        Args:
            type: 类型
            model_id: 模型ID
            
        Returns:
            最新的模型记录字典或None
        """
        query = "SELECT * FROM model_record WHERE type = ? AND model_id = ? ORDER BY create_time DESC LIMIT 1"
        
        try:
            results = self.execute_query(query, (type, model_id))
            return results[0] if results else None
        except Exception as e:
            print(f"根据type和model_id查询最新模型记录失败: {e}")
            return None

    def get_model_info_by_conditions(self, model_id: str = None, state: int = None, 
                             assess_state: int = None, export_state: int = None) -> List[Dict[str, Any]]:
        """
        根据条件获取模型信息记录
        
        Args:
            model_id: 模型ID
            state: 状态
            assess_state: 评估状态
            export_state: 导出状态
            
        Returns:
            模型信息记录列表
        """
        # 构建查询语句
        query = "SELECT * FROM model_info"
        conditions = []
        params = []
        
        # 根据传入的参数动态添加条件
        if model_id is not None:
            conditions.append("model_id = ?")
            params.append(model_id)
            
        if state is not None:
            conditions.append("state = ?")
            params.append(state)
            
        if assess_state is not None:
            conditions.append("assess_state = ?")
            params.append(assess_state)
            
        if export_state is not None:
            conditions.append("export_state = ?")
            params.append(export_state)
            
        # 如果有条件，则添加WHERE子句
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
            
        # 添加排序
        query += " ORDER BY create_time DESC"
        
        try:
            return self.execute_query(query, params)
        except Exception as e:
            print(f"根据条件获取模型信息记录失败: {e}")
            return []

    def stop_training_by_task_id(self, task_id: str) -> bool:
        """
        根据task_id停止训练，将state设置为2（停止状态）
        
        Args:
            task_id: 任务ID
            
        Returns:
            bool: 停止是否成功
        """
        try:
            success = self.update_model_info_by_task_id(task_id, state=5)
            return success
        except Exception as e:
            print(f"根据task_id停止训练失败: {e}")
            return False