"""
样本中心服务层
从 sample_view.py 提取的SQL查询逻辑
"""
import logging
import uuid
from typing import Optional, List, Dict, Any, Tuple
from app.base.async_mysql_connection import get_db_connection
from app.base.minio_connection import get_minio_manager
from app.core.config import config_handler

logger = logging.getLogger(__name__)

# 表名映射
TABLE_MAP = {
    'basis': 't_samp_standard_base_info',
    'work': 't_samp_construction_plan_base_info',
    'job': 't_samp_office_regulations'
}


def get_table_name(source_type: str) -> Optional[str]:
    """根据source_type获取表名"""
    return TABLE_MAP.get(source_type)


class SampleService:
    """样本中心服务类 - 使用 SQL 查询方式"""
    
    def __init__(self):
        """初始化服务"""
        # 使用统一的 MinIO 管理器
        self.minio_manager = get_minio_manager()

    async def get_upload_url(self, filename: str, content_type: str) -> Tuple[bool, str, Dict[str, Any]]:
        """获取 MinIO 预签名上传 URL"""
        try:
            data = self.minio_manager.get_upload_url(filename, content_type)
            return True, "成功获取上传链接", data
        except Exception as e:
            logger.exception("生成上传链接失败")
            return False, f"生成上传链接失败: {str(e)}", {}

    # ==================== 文档管理 ====================
    
    async def batch_enter_knowledge_base(self, doc_ids: List[str], username: str) -> Tuple[int, str]:
        """批量将文档加入知识库"""
        conn = get_db_connection()
        if not conn:
            return 0, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 批量更新主表 t_samp_document_main
            placeholders = ', '.join(['%s'] * len(doc_ids))
            
            # 更新主表
            sql_main = f"""
                UPDATE t_samp_document_main 
                SET whether_to_enter = 1, updated_by = %s, updated_time = NOW() 
                WHERE id IN ({placeholders}) AND whether_to_enter = 0
            """
            cursor.execute(sql_main, [username] + doc_ids)
            affected_rows = cursor.rowcount
            
            # 尝试更新对应的子表以保持同步
            try:
                cursor.execute(f"SELECT id, source_type, source_id FROM t_samp_document_main WHERE id IN ({placeholders})", doc_ids)
                docs = cursor.fetchall()
                
                for doc_row in docs:
                    d_id = doc_row['id']
                    s_type = doc_row['source_type']
                    s_id = doc_row['source_id']
                    if s_type and s_id:
                        sub_table = get_table_name(s_type)
                        if sub_table:
                            sub_sql = f"UPDATE {sub_table} SET whether_to_enter = 1, updated_time = NOW(), updated_by = %s WHERE id = %s"
                            try:
                                cursor.execute(sub_sql, (username, s_id))
                            except Exception as sub_e:
                                logger.error(f"更新子表 {sub_table} 入库状态失败: {sub_e}")
            except Exception as sync_e:
                logger.error(f"同步更新子表入库状态失败: {sync_e}")
            
            conn.commit()
            
            message = f"成功将 {affected_rows} 条数据加入知识库"
            if affected_rows < len(doc_ids):
                message += f"（跳过了 {len(doc_ids) - affected_rows} 条已入库数据或未找到数据）"
            
            return affected_rows, message
        except Exception as e:
            logger.exception("批量操作失败")
            conn.rollback()
            return 0, f"批量操作失败: {str(e)}"
        finally:
            cursor.close()
            conn.close()
    
    async def batch_delete_documents(self, doc_ids: List[str]) -> Tuple[int, str]:
        """批量删除文档"""
        conn = get_db_connection()
        if not conn:
            return 0, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            if not doc_ids:
                return 0, "未指定要删除的文档 ID"
            
            placeholders = ', '.join(['%s'] * len(doc_ids))
            
            # 尝试同步删除子表中的数据
            try:
                cursor.execute(f"SELECT source_type, source_id FROM t_samp_document_main WHERE id IN ({placeholders})", doc_ids)
                docs = cursor.fetchall()
                
                for doc_row in docs:
                    s_type = doc_row['source_type']
                    s_id = doc_row['source_id']
                    if s_type and s_id:
                        sub_table = get_table_name(s_type)
                        if sub_table:
                            sub_sql = f"DELETE FROM {sub_table} WHERE id = %s"
                            try:
                                cursor.execute(sub_sql, (s_id,))
                            except Exception as sub_e:
                                logger.error(f"删除子表 {sub_table} 数据失败: {sub_e}")
            except Exception as sync_e:
                logger.error(f"同步删除子表数据失败: {sync_e}")
            
            # 删除主表 t_samp_document_main 中的数据
            sql_main = f"DELETE FROM t_samp_document_main WHERE id IN ({placeholders})"
            cursor.execute(sql_main, doc_ids)
            affected_rows = cursor.rowcount
            
            conn.commit()
            
            return affected_rows, f"成功删除 {affected_rows} 条文档数据"
        except Exception as e:
            logger.exception("批量删除失败")
            conn.rollback()
            return 0, f"批量删除失败: {str(e)}"
        finally:
            cursor.close()
            conn.close()
    
    async def get_document_list(
        self,
        whether_to_enter: Optional[int] = None,
        keyword: Optional[str] = None,
        table_type: Optional[str] = None,
        page: int = 1,
        size: int = 50
    ) -> Tuple[List[Dict[str, Any]], int, int, int]:
        """获取文档列表（从主表查询）"""
        conn = get_db_connection()
        if not conn:
            return [], 0, 0, 0
        
        cursor = conn.cursor()
        
        try:
            where_clauses = []
            params = []
            
            if table_type:
                where_clauses.append("source_type = %s")
                params.append(table_type)
            if whether_to_enter is not None:
                where_clauses.append("whether_to_enter = %s")
                params.append(whether_to_enter)
            if keyword:
                where_clauses.append("title LIKE %s")
                params.append(f"%{keyword}%")
            
            where_sql = " WHERE " + " AND ".join(where_clauses) if where_clauses else ""
            offset = (page - 1) * size
            
            sql = f"SELECT * FROM t_samp_document_main {where_sql} ORDER BY created_time DESC LIMIT %s OFFSET %s"
            params.extend([size, offset])
            
            logger.info(f"Executing SQL: {sql} with params: {params}")
            cursor.execute(sql, tuple(params))
            items = []
            for row in cursor.fetchall():
                item = row # DictCursor already returns dict
                # 格式化时间
                for key in ['created_time', 'updated_time']:
                    if item.get(key) and hasattr(item[key], 'isoformat'):
                        item[key] = item[key].isoformat()
                items.append(item)
            
            # 总数
            count_sql = f"SELECT COUNT(*) as count FROM t_samp_document_main {where_sql}"
            cursor.execute(count_sql, tuple(params[:-2]))
            res = cursor.fetchone()
            total = res['count'] if res else 0
            
            # 统计数据
            cursor.execute("SELECT COUNT(*) as count FROM t_samp_document_main")
            res = cursor.fetchone()
            all_total = res['count'] if res else 0
            
            cursor.execute("SELECT COUNT(*) as count FROM t_samp_document_main WHERE whether_to_enter = 1")
            res = cursor.fetchone()
            total_entered = res['count'] if res else 0
            
            return items, total, all_total, total_entered
        except Exception as e:
            logger.exception("获取文档列表失败")
            return [], 0, 0, 0
        finally:
            cursor.close()
            conn.close()
    
    async def get_document_detail(self, doc_id: str) -> Optional[Dict[str, Any]]:
        """获取文档详情"""
        conn = get_db_connection()
        if not conn:
            return None
        
        cursor = conn.cursor()
        
        try:
            # 查询主表
            cursor.execute("SELECT * FROM t_samp_document_main WHERE id = %s", (doc_id,))
            doc = cursor.fetchone()
            if not doc:
                return None
            
            # 格式化时间
            for key in ['created_time', 'updated_time']:
                if doc.get(key) and hasattr(doc[key], 'isoformat'):
                    doc[key] = doc[key].isoformat()
            
            return doc
        except Exception as e:
            logger.exception("获取文档详情失败")
            return None
        finally:
            cursor.close()
            conn.close()
    
    def _to_int(self, value: Any) -> Optional[int]:
        """安全转换为整数"""
        if value is None or value == '' or str(value).lower() == 'null':
            return None
        try:
            return int(value)
        except (ValueError, TypeError):
            return None

    def _to_date(self, value: Any) -> Optional[str]:
        """安全处理日期字符串"""
        if value is None or value == '' or str(value).lower() == 'null':
            return None
        # 如果已经是 datetime.date 或 datetime.datetime 对象
        if hasattr(value, 'strftime'):
            return value.strftime('%Y-%m-%d')
        return str(value)

    async def add_document(self, doc_data: Dict[str, Any], user_id: str) -> Tuple[bool, str, Optional[str]]:
        """添加新文档（同步主表和子表）"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败", None
        
        cursor = conn.cursor()
        
        try:
            doc_id = str(uuid.uuid4())
            source_id = str(uuid.uuid4())
            table_type = doc_data.get('table_type', 'basis')
            table_name = TABLE_MAP.get(table_type, "t_basis_of_preparation")
            
            # 安全转换字段
            release_date = self._to_date(doc_data.get('release_date'))
            
            # 插入子表 (会触发数据库触发器自动向 t_samp_document_main 插入记录)
            if table_type == 'basis':
                cursor.execute(
                    f"INSERT INTO {table_name} (id, chinese_name, standard_number, issuing_authority, release_date, document_type, professional_field, validity, file_url, content, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())",
                    (source_id, doc_data.get('title'), doc_data.get('standard_no'), doc_data.get('issuing_authority'), release_date, doc_data.get('document_type'), doc_data.get('professional_field'), doc_data.get('validity'), doc_data.get('file_url'), doc_data.get('content'), user_id)
                )
            elif table_type == 'work':
                cursor.execute(
                    f"INSERT INTO {table_name} (id, plan_name, project_name, project_section, compiling_unit, compiling_date, file_url, content, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())",
                    (source_id, doc_data.get('title'), doc_data.get('project_name'), doc_data.get('project_section'), doc_data.get('issuing_authority'), release_date, doc_data.get('file_url'), doc_data.get('content'), user_id)
                )
            elif table_type == 'job':
                cursor.execute(
                    f"INSERT INTO {table_name} (id, file_name, issuing_department, document_type, publish_date, file_url, content, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())",
                    (source_id, doc_data.get('title'), doc_data.get('issuing_authority'), doc_data.get('document_type'), release_date, doc_data.get('file_url'), doc_data.get('content'), user_id)
                )
            
            # 获取触发器自动生成的 doc_id
            cursor.execute("SELECT id FROM t_samp_document_main WHERE source_id = %s AND source_type = %s", (source_id, table_type))
            row = cursor.fetchone()
            if not row:
                raise Exception("触发器未能在主表创建记录")
            doc_id = row['id']

            # 更新主表中触发器未处理的字段
            cursor.execute("""
                UPDATE t_samp_document_main 
                SET file_extension = %s
                WHERE id = %s
            """, (doc_data.get('file_extension'), doc_id))
            
            conn.commit()
            return True, "文档添加成功", doc_id
        except Exception as e:
            logger.exception("添加文档失败")
            conn.rollback()
            return False, str(e), None
        finally:
            cursor.close()
            conn.close()

    async def edit_document(self, doc_data: Dict[str, Any], updater_id: str) -> Tuple[bool, str]:
        """编辑文档（同步主表和子表）"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            doc_id = doc_data.get('id')
            source_id = doc_data.get('source_id')
            table_type = doc_data.get('table_type', 'basis')
            table_name = TABLE_MAP.get(table_type, "t_samp_standard_base_info")
            
            # 安全转换字段
            release_date = self._to_date(doc_data.get('release_date'))
            
            # 1. 更新子表 (会触发数据库触发器更新 t_samp_document_main 的基本字段)
            if table_type == 'basis':
                cursor.execute(
                    f"UPDATE {table_name} SET chinese_name = %s, standard_number = %s, issuing_authority = %s, release_date = %s, document_type = %s, professional_field = %s, validity = %s, file_url = %s, content = %s, updated_by = %s, updated_time = NOW() WHERE id = %s",
                    (doc_data.get('title'), doc_data.get('standard_no'), doc_data.get('issuing_authority'), release_date, doc_data.get('document_type'), doc_data.get('professional_field'), doc_data.get('validity'), doc_data.get('file_url'), doc_data.get('content'), updater_id, source_id)
                )
            elif table_type == 'work':
                cursor.execute(
                    f"UPDATE {table_name} SET plan_name = %s, project_name = %s, project_section = %s, compiling_unit = %s, compiling_date = %s, file_url = %s, content = %s, updated_by = %s, updated_time = NOW() WHERE id = %s",
                    (doc_data.get('title'), doc_data.get('project_name'), doc_data.get('project_section'), doc_data.get('issuing_authority'), release_date, doc_data.get('file_url'), doc_data.get('content'), updater_id, source_id)
                )
            elif table_type == 'job':
                cursor.execute(
                    f"UPDATE {table_name} SET file_name = %s, issuing_department = %s, document_type = %s, publish_date = %s, file_url = %s, content = %s, updated_by = %s, updated_time = NOW() WHERE id = %s",
                    (doc_data.get('title'), doc_data.get('issuing_authority'), doc_data.get('document_type'), release_date, doc_data.get('file_url'), doc_data.get('content'), updater_id, source_id)
                )

            # 2. 更新主表中触发器未处理的字段（或者被触发器覆盖的非子表字段）
            cursor.execute("""
                UPDATE t_samp_document_main 
                SET file_extension = %s,
                    updated_by = %s, updated_time = NOW()
                WHERE id = %s
            """, (doc_data.get('file_extension'),
                  updater_id, doc_id))

            
            conn.commit()
            return True, "文档更新成功"
        except Exception as e:
            logger.exception("编辑文档失败")
            conn.rollback()
            return False, str(e)
        finally:
            cursor.close()
            conn.close()

    async def enter_document(self, doc_id: str, username: str) -> Tuple[bool, str]:
        """文档入库（单个）"""
        affected_rows, message = await self.batch_enter_knowledge_base([doc_id], username)
        return affected_rows > 0, message
    
    async def get_basic_info_list(
        self,
        type: str,
        page: int,
        size: int,
        keyword: Optional[str] = None,
        **filters
    ) -> Tuple[List[Dict[str, Any]], int]:
        """获取基本信息列表（支持多条件检索）"""
        conn = get_db_connection()
        if not conn:
            return [], 0
        
        cursor = conn.cursor()
        
        try:
            # 根据类型选择表名和字段映射
            if type == 'basis':
                table_name = "t_samp_standard_base_info"
                fields = "id, chinese_name as title, standard_number as standard_no, issuing_authority, release_date, document_type, professional_field, validity, created_by, created_time, file_url"
                field_map = {
                    'title': 'chinese_name',
                    'standard_no': 'standard_number',
                    'issuing_authority': 'issuing_authority',
                    'release_date': 'release_date',
                    'document_type': 'document_type',
                    'professional_field': 'professional_field',
                    'validity': 'validity'
                }
            elif type == 'work':
                table_name = "t_samp_construction_plan_base_info"
                fields = "id, plan_name as title, NULL as standard_no, compiling_unit as issuing_authority, compiling_date as release_date, NULL as document_type, NULL as professional_field, NULL as validity, created_by, created_time, file_url"
                field_map = {
                    'title': 'plan_name',
                    'issuing_authority': 'compiling_unit',
                    'release_date': 'compiling_date'
                }
            elif type == 'job':
                table_name = "t_samp_office_regulations"
                fields = "id, file_name as title, NULL as standard_no, issuing_department as issuing_authority, publish_date as release_date, document_type, NULL as professional_field, NULL as validity, created_by, created_time, file_url"
                field_map = {
                    'title': 'file_name',
                    'issuing_authority': 'issuing_department',
                    'release_date': 'publish_date',
                    'document_type': 'document_type'
                }
            else:
                return [], 0
            
            where_clauses = []
            params = []
            
            # 统一关键字搜索
            if keyword:
                if type == 'basis':
                    where_clauses.append("(chinese_name LIKE %s OR standard_number LIKE %s)")
                    params.extend([f"%{keyword}%", f"%{keyword}%"])
                elif type == 'work':
                    where_clauses.append("plan_name LIKE %s")
                    params.append(f"%{keyword}%")
                elif type == 'job':
                    where_clauses.append("file_name LIKE %s")
                    params.append(f"%{keyword}%")
            
            # 精细化检索
            for filter_key, filter_value in filters.items():
                if not filter_value:
                    continue
                
                # 处理日期范围
                if filter_key == 'release_date_start':
                    where_clauses.append("release_date >= %s")
                    params.append(filter_value)
                elif filter_key == 'release_date_end':
                    where_clauses.append("release_date <= %s")
                    params.append(filter_value)
                elif filter_key in field_map:
                    db_field = field_map[filter_key]
                    where_clauses.append(f"{db_field} LIKE %s")
                    params.append(f"%{filter_value}%")
            
            where_sql = " WHERE " + " AND ".join(where_clauses) if where_clauses else ""
            
            # 分页查询
            offset = (page - 1) * size
            sql = f"SELECT {fields} FROM {table_name}{where_sql} ORDER BY created_time DESC LIMIT %s OFFSET %s"
            params.extend([size, offset])
            
            logger.info(f"Executing Basic Info SQL: {sql} with params: {params}")
            cursor.execute(sql, tuple(params))
            items = []
            for row in cursor.fetchall():
                item = row # DictCursor already returns dict
                # 格式化日期
                for key in ['release_date', 'created_time']:
                    if item.get(key) and hasattr(item[key], 'isoformat'):
                        item[key] = item[key].isoformat()
                    elif item.get(key):
                        item[key] = str(item[key])
                items.append(item)
            
            # 总数查询
            count_sql = f"SELECT COUNT(*) as count FROM {table_name}{where_sql}"
            cursor.execute(count_sql, tuple(params[:-2]))
            res = cursor.fetchone()
            total = res['count'] if res else 0
            
            return items, total
        except Exception as e:
            logger.exception("查询基本信息失败")
            return [], 0
        finally:
            cursor.close()
            conn.close()
    
    # ==================== 文档转换 ====================
    
    async def get_document_source_type(self, doc_id: str) -> Optional[str]:
        """获取文档的source_type"""
        conn = get_db_connection()
        if not conn:
            return None
        
        cursor = conn.cursor()
        
        try:
            cursor.execute("SELECT source_type FROM t_samp_document_main WHERE id = %s", (doc_id,))
            res = cursor.fetchone()
            return res['source_type'] if res else None
        except Exception as e:
            logger.exception(f"获取文档source_type失败: {e}")
            return None
        finally:
            cursor.close()
            conn.close()
    
    async def get_document_title(self, doc_id: str) -> str:
        """获取文档标题"""
        conn = get_db_connection()
        if not conn:
            return "文档"
        
        cursor = conn.cursor()
        
        try:
            cursor.execute("SELECT title FROM t_samp_document_main WHERE id = %s", (doc_id,))
            res = cursor.fetchone()
            return res['title'] if res else "文档"
        except Exception as e:
            logger.exception(f"获取文档标题失败: {e}")
            return "文档"
        finally:
            cursor.close()
            conn.close()
    
    async def update_conversion_progress(self, doc_id: str, status: int, 
                                        md_url: Optional[str] = None,
                                        json_url: Optional[str] = None,
                                        error_message: Optional[str] = None) -> bool:
        """更新文档转换进度
        
        Args:
            doc_id: 文档ID
            status: 转换状态 (0=未转换, 1=转换中, 2=已完成, 3=失败)
            md_url: Markdown文件URL
            json_url: JSON文件URL
            error_message: 错误信息
        """
        conn = get_db_connection()
        if not conn:
            return False
        
        cursor = conn.cursor()
        
        try:
            update_clauses = ["conversion_status = %s"]
            params = [status]

            if error_message:
                update_clauses.append("conversion_error = %s")
                params.append(error_message)
            
            if md_url:
                update_clauses.append("md_url = %s")
                params.append(md_url)
            
            if json_url:
                update_clauses.append("json_url = %s")
                params.append(json_url)
            
            sql = f"UPDATE t_samp_document_main SET {', '.join(update_clauses)}, updated_time = NOW() WHERE id = %s"
            params.append(doc_id)
            
            cursor.execute(sql, tuple(params))
            conn.commit()
            return True
        except Exception as e:
            logger.exception(f"更新转换进度失败: {e}")
            conn.rollback()
            return False
        finally:
            cursor.close()
            conn.close()

    # ==================== 基础信息管理 ====================

    async def add_basic_info(self, type: str, data: Dict[str, Any], user_id: str) -> Tuple[bool, str]:
        """新增基本信息"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        try:
            table_name = TABLE_MAP.get(type)
            if not table_name:
                return False, "无效的类型"
            
            source_id = str(uuid.uuid4())
            doc_id = str(uuid.uuid4())
            
            # 1. 插入子表 (触发器会自动同步到主表)
            if type == 'basis':
                sql = f"INSERT INTO {table_name} (id, chinese_name, standard_number, issuing_authority, release_date, document_type, professional_field, validity, file_url, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())"
                params = (source_id, data.get('title'), data.get('standard_no'), data.get('issuing_authority'), self._to_date(data.get('release_date')), data.get('document_type'), data.get('professional_field'), data.get('validity', '现行'), data.get('file_url'), user_id)
            elif type == 'work':
                sql = f"INSERT INTO {table_name} (id, plan_name, project_name, project_section, compiling_unit, compiling_date, file_url, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())"
                params = (source_id, data.get('title'), data.get('project_name'), data.get('project_section'), data.get('issuing_authority'), self._to_date(data.get('release_date')), data.get('file_url'), user_id)
            elif type == 'job':
                sql = f"INSERT INTO {table_name} (id, file_name, issuing_department, document_type, publish_date, file_url, created_by, created_time, updated_time) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), NOW())"
                params = (source_id, data.get('title'), data.get('issuing_authority'), data.get('document_type'), self._to_date(data.get('release_date')), data.get('file_url'), user_id)
            else:
                return False, "不支持的类型"
            
            cursor.execute(sql, params)
            
            # 2. 触发器已经同步到主表，不需要手动插入
            # 但如果需要更新主表中触发器没处理的字段，可以在这里更新
            
            conn.commit()
            return True, "新增成功"
        except Exception as e:
            logger.exception("新增基本信息失败")
            conn.rollback()
            return False, str(e)
        finally:
            cursor.close()
            conn.close()

    async def edit_basic_info(self, type: str, info_id: str, data: Dict[str, Any], updater_id: str) -> Tuple[bool, str]:
        """编辑基本信息"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        try:
            table_name = TABLE_MAP.get(type)
            if not table_name:
                return False, "无效的类型"
            
            # 1. 更新子表 (触发器会自动同步到主表)
            if type == 'basis':
                sql = f"UPDATE {table_name} SET chinese_name = %s, standard_number = %s, issuing_authority = %s, release_date = %s, document_type = %s, professional_field = %s, validity = %s, file_url = %s, updated_by = %s, updated_time = NOW() WHERE id = %s"
                params = (data.get('title'), data.get('standard_no'), data.get('issuing_authority'), self._to_date(data.get('release_date')), data.get('document_type'), data.get('professional_field'), data.get('validity'), data.get('file_url'), updater_id, info_id)
            elif type == 'work':
                sql = f"UPDATE {table_name} SET plan_name = %s, project_name = %s, project_section = %s, compiling_unit = %s, compiling_date = %s, file_url = %s, updated_by = %s, updated_time = NOW() WHERE id = %s"
                params = (data.get('title'), data.get('project_name'), data.get('project_section'), data.get('issuing_authority'), self._to_date(data.get('release_date')), data.get('file_url'), updater_id, info_id)
            elif type == 'job':
                sql = f"UPDATE {table_name} SET file_name = %s, issuing_department = %s, document_type = %s, publish_date = %s, file_url = %s, updated_by = %s, updated_time = NOW() WHERE id = %s"
                params = (data.get('title'), data.get('issuing_authority'), data.get('document_type'), self._to_date(data.get('release_date')), data.get('file_url'), updater_id, info_id)
            else:
                return False, "不支持的类型"
            
            cursor.execute(sql, params)
            
            # 2. 触发器已经同步到主表的基本字段，如果主表有额外字段需要更新，可以在这里操作
            
            conn.commit()
            return True, "编辑成功"
        except Exception as e:
            logger.exception("编辑基本信息失败")
            conn.rollback()
            return False, str(e)
        finally:
            cursor.close()
            conn.close()

    async def delete_basic_info(self, type: str, info_id: str) -> Tuple[bool, str]:
        """删除基本信息"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        try:
            table_name = TABLE_MAP.get(type)
            if not table_name:
                return False, "无效的类型"
            
            # 1. 删除子表记录 (触发器会自动删除主表记录)
            cursor.execute(f"DELETE FROM {table_name} WHERE id = %s", (info_id,))
            
            conn.commit()
            return True, "删除成功"
        except Exception as e:
            logger.exception("删除基本信息失败")
            conn.rollback()
            return False, str(e)
        finally:
            cursor.close()
            conn.close()
