import pymssql
import uuid
import os
from datetime import datetime


def insert_attachment_and_update_reg(
    attachment_id: str,
    cos_url: str,
    local_file_path: str,
    reg_back_number: str,
    db_host: str = 'localhost',
    db_user: str = 'sa',
    db_password: str = 'password',
    db_database: str = 'vat',
    db_port = 1433,
):
    """
    插入附件记录到 Base_AnnexesFile 表，并更新 EPRRegInfo 表的 RegBackNumber 字段。

    :param attachment_id: EPRRegInfo 表的字符类型 ID（同时作为 InfoId）
    :param cos_url: 文件的 COS URL（云存储地址）
    :param local_file_path: 本地文件路径（用于获取文件名和文件大小）
    :param reg_back_number: 要更新到 EPRRegInfo.RegBackNumber 的注册码
    :param db_host: 数据库主机地址
    :param db_user: 数据库用户名
    :param db_password: 数据库密码
    :param db_database: 数据库名称
    :param db_port: 数据库端口
    """

    # 生成 UUID
    file_id = generate_guid()

    # 从本地文件获取文件名和大小
    file_name, file_size = get_file_info(local_file_path)

    creation_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    insert_sql = """
        INSERT INTO Base_AnnexesFile (
            F_Id, F_FolderId, F_FileName, F_FilePath, CreationName,
            F_FileSize, F_FileExtensions, F_FileType, F_DownloadCount,
            CreationDate, Creation_Id, InfoId, FileCover, FileUrl, FileFrom, FileGroup, FileCategoryId
        ) VALUES (
            %s, %s, %s, %s, %s,
            %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s
        )
    """

    insert_params = (
        file_id,
        '',
        file_name,
        cos_url,
        'System',
        file_size,
        '.pdf',
        'pdf',
        0,
        creation_date,
        'System',
        attachment_id,
        None,
        cos_url,
        3,
        2,
        'fa8762a6-787f-47bd-9626-2522dfdc7b80'
    )

    update_sql = """
        UPDATE EPRRegInfo
        SET RegBackNumber = %s, PushTaxBureauStatus = 4
        WHERE ID = %s
    """

    update_params = (reg_back_number, attachment_id)

    conn = pymssql.connect(
        host=db_host,
        user=db_user,
        password=db_password,
        database=db_database,
        port=db_port
    )

    try:
        with conn:
            cursor = conn.cursor()
            cursor.execute(insert_sql, insert_params)
            cursor.execute(update_sql, update_params)
            conn.commit()
            print(f"✅ 附件插入成功，FileId: {file_id}")
            print(f"✅ EPRRegInfo 更新成功，AttachmentId: {attachment_id}, RegBackNumber: {reg_back_number}")
    except Exception as e:
        conn.rollback()
        print(f"❌ 操作失败，已回滚：{e}")
        raise
    finally:
        conn.close()


def generate_guid() -> str:
    """生成标准 UUID 字符串（大写，带连字符）"""
    return str(uuid.uuid4())


def get_file_info(local_file_path: str):
    """
    从本地文件路径获取文件名（替换扩展名为 .pdf）和文件大小（字节）

    :param local_file_path: 本地文件完整路径
    :return: (file_name, file_size)
    """
    if not os.path.exists(local_file_path):
        raise FileNotFoundError(f"文件不存在：{local_file_path}")

    base_name = os.path.basename(local_file_path)
    name_without_ext = os.path.splitext(base_name)[0]
    file_name = name_without_ext + '.pdf'
    file_size = os.path.getsize(local_file_path)

    return file_name, file_size