import pymssql
from datetime import datetime


def fetch_and_update_one_task(db_host='localhost',
                              db_user='sa',
                              db_password='password',
                              db_database='vat',
                              db_port=1433):
    """
    查询一条符合条件的法国包装法注册任务，并更新其 ModificationDate 为当前时间。

    返回:
        dict: 查询到的数据字典，如果没有查到则返回 None
    """
    connection = None
    cursor = None

    try:
        connection = pymssql.connect(
            server=db_host,
            user=db_user,
            password=db_password,
            database=db_database,
            port=db_port,
            charset='utf8',
            as_dict=True
        )

        cursor = connection.cursor()

        select_sql = """
            SELECT TOP 1
                br.BusinessSerialNumber,
                br.ID        AS RegisterID,
                ei.ID        AS EPRRegInfoID,
                br.BusinessCode,
                cc.NameEng,cc.NameCN,
                bu.F_Mobile,ei.RegReceiptNumber
            FROM EPRBusinessRecord br
            LEFT JOIN EPRRegInfo            ei ON ei.EPRBusinessRecordId = br.ID
            LEFT JOIN MS_OrderDetails       mo ON mo.ID = br.MSOrderDetailsId
            LEFT JOIN GeneralTemplateEPR    gt ON gt.ID = br.ID
            LEFT JOIN ProductSpecs          sp ON sp.ID = mo.SKUSpecificationID
            LEFT JOIN Base_Customer_Company cc ON cc.ID = br.CompanyId
            LEFT JOIN RecyclingType         rt ON rt.ID = br.RecyclingTypeId
            LEFT JOIN Country               c  ON c.CountryName = cc.Country
            LEFT JOIN ServiceItems          si ON si.ID = br.ServiceItemId
            LEFT JOIN Base_User AS bu ON br.BusinessCounselorId = bu.F_UserId
            LEFT JOIN SupplierInformation AS sinfo ON sinfo.Id = br.OfficialFeeRecycleMerchant
            WHERE br.Country = 'FR'
              AND ei.PushTaxBureauStatus = 3
              AND ei.PushType = '301'
              AND rt.TypeName = '包装法'
              AND si.ServiceItemName = '包装法注册'
              AND br.Status = 3
              AND sinfo.SupplierName = 'LEKO'
            ORDER BY ei.ModificationDate ASC
        """

        cursor.execute(select_sql)
        row = cursor.fetchone()

        if row is None:
            print("没有查询到符合条件的记录")
            return None

        # 更新 ModificationDate 为当前时间
        now = datetime.now()
        update_sql = """
            UPDATE EPRRegInfo
            SET ModificationDate = %s
            WHERE ID = %s
        """
        cursor.execute(update_sql, (now, row['EPRRegInfoID']))
        connection.commit()

        # 将 datetime 字段转换为 ISO 格式字符串，确保可序列化
        for key in row:
            if isinstance(row[key], datetime):
                row[key] = row[key].isoformat()

        print(f"查询到记录 EPRRegInfoID={row['EPRRegInfoID']}，已更新 ModificationDate 为 {now}")
        return row

    except pymssql.Error as e:
        print(f"连接或查询数据库时出错: {e}")
        return None
    except Exception as e:
        print(f"处理过程中出错: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()