import mysql.connector
import json

# 數(shù)據(jù)庫連接配置
source_db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'CallSystemProd',
    'password': '******',
    'database': ''******','  # 源數(shù)據(jù)庫名稱
}

target_db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'CallSystemTest',
    'password': ''******',',
    'database': ''******','  # 目標(biāo)數(shù)據(jù)庫名稱
}

# 連接到源數(shù)據(jù)庫
try:
    source_conn = mysql.connector.connect(**source_db_config)
    source_cursor = source_conn.cursor(dictionary=True)
    print("成功連接到源數(shù)據(jù)庫")
except mysql.connector.Error as err:
    print(f"連接源數(shù)據(jù)庫失敗: {err}")
    exit()

# 連接到目標(biāo)數(shù)據(jù)庫
try:
    target_conn = mysql.connector.connect(**target_db_config)
    target_cursor = target_conn.cursor()
    print("成功連接到目標(biāo)數(shù)據(jù)庫")
except mysql.connector.Error as err:
    print(f"連接目標(biāo)數(shù)據(jù)庫失敗: {err}")
    exit()

# 獲取源表數(shù)據(jù)（S_SMSSendLogs）
source_cursor.execute("""
    SELECT Id, SMSId, Phone, Status, IsSucceed, Trade_id, RequestStr, 
           ResponseStr, RobotTaskId, ExtendCode, CreateTime, UpdateTime, 
           CreateUserId, UpdateUserId, IsDelete, TaskCallResultId, ProviderType
    FROM S_SMSSendLogs
""")
columns = source_cursor.fetchall()

# 獲取源表數(shù)據(jù)（S_SMSTemplates），用于RequestStr中的替換
source_cursor.execute("""
    SELECT SMSId, Content, Id
    FROM S_SMSTemplates
""")
templates = {template['SMSId']: template for template in source_cursor.fetchall()}

# 獲取與TaskCallResultId相關(guān)的數(shù)據(jù)（t_taskcallresults和t_repeattaskcallresults）
source_cursor.execute("""
    SELECT id, CreateUserId, UpdateUserId, ResidentsId, CustomerName
    FROM T_TaskCallResults
""")
taskcallresults = {result['id']: result for result in source_cursor.fetchall()}

source_cursor.execute("""
    SELECT id, CreateUserId, UpdateUserId, ResidentsId, CustomerName
    FROM T_RepeatTaskCallResults
""")
repeattaskcallresults = {result['id']: result for result in source_cursor.fetchall()}

# 獲取RobotTaskId和OrgId相關(guān)的表數(shù)據(jù)（t_taskcallresults, t_robotrepeattasks, sysuser）
source_cursor.execute("""
    SELECT id, CreateUserId
    FROM T_TaskCallResults
""")
taskcallresults_user_map = {result['id']: result['CreateUserId'] for result in source_cursor.fetchall()}

source_cursor.execute("""
    SELECT id, CreateUserId
    FROM T_RobotRepeatTasks
""")
robotrepeattasks_user_map = {result['id']: result['CreateUserId'] for result in source_cursor.fetchall()}

source_cursor.execute("""
    SELECT Id, OrgId
    FROM SysUser
""")
sysuser_map = {user['Id']: user['OrgId'] for user in source_cursor.fetchall()}

# 構(gòu)造批量插入語句
insert_query = """
    INSERT INTO S_SMSSendLogs (Id, SMSId, Phone, Status, IsSucceed, Trade_id, RequestStr, 
                               ResponseStr, RobotTaskId, ExtendCode, CreateTime, UpdateTime, 
                               CreateUserId, UpdateUserId, IsDelete, TaskCallResultId, 
                               ProviderType, OrgId, Content, CharCount, BillingCount, ResidentsId, 
                               CustomerName, TemplateId)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""

# 構(gòu)造插入的值（批量數(shù)據(jù)），應(yīng)用轉(zhuǎn)換規(guī)則
insert_values = []
for column in columns:
    # Status 字段直接遷移，無需轉(zhuǎn)換
    status = column['Status']

    # 將 IsSucceed 轉(zhuǎn)換為 tinyint類型的 0
    is_succeed = 0 if column['IsSucceed'] == 1 else column['IsSucceed']

    # RequestStr 替換操作
    request_str = column['RequestStr']
    template = templates.get(column['SMSId'])
    if template:
        template_content = template['Content']
        try:
            # 獲取 RequestStr 中的變量并替換
            request_values = json.loads(request_str).get('arguments', {})
            for key, value in request_values.items():
                template_content = template_content.replace(f"#{key}#", str(value) if value is not None else "")
        except json.JSONDecodeError:
            pass  # 如果 JSON 格式不正確，跳過替換
        request_str = template_content

    # 計算字符數(shù)
    char_count = len(request_str)
    
    # 計算計費條數(shù)
    billing_count = (char_count + 66) // 67  # 每條超過67個字算第二條

    # 獲取相關(guān)的 UserId 和 OrgId
    create_user_id = taskcallresults.get(column['TaskCallResultId'], {}).get('CreateUserId') or \
                     repeattaskcallresults.get(column['TaskCallResultId'], {}).get('CreateUserId')

    update_user_id = taskcallresults.get(column['TaskCallResultId'], {}).get('UpdateUserId') or \
                     repeattaskcallresults.get(column['TaskCallResultId'], {}).get('UpdateUserId')

    # ProviderType 轉(zhuǎn)換
    provider_type = column['ProviderType']
    if provider_type == 1:
        provider_type = 0
    elif provider_type == 2:
        provider_type = 3

    # OrgId 通過 CreateUserId 獲取
    create_user_id_for_org = taskcallresults_user_map.get(column['RobotTaskId']) or \
                             robotrepeattasks_user_map.get(column['RobotTaskId'])
    org_id = sysuser_map.get(create_user_id_for_org, None)

    # 構(gòu)造數(shù)據(jù)插入
    insert_values.append((
        column['Id'], column['SMSId'], column['Phone'], status, is_succeed, column['Trade_id'],
        request_str, column['ResponseStr'], column['RobotTaskId'], column['ExtendCode'],
        column['CreateTime'], column['UpdateTime'], create_user_id, update_user_id,
        column['IsDelete'], column['TaskCallResultId'], provider_type, org_id, request_str, char_count,
        billing_count, taskcallresults.get(column['TaskCallResultId'], {}).get('ResidentsId'),
        taskcallresults.get(column['TaskCallResultId'], {}).get('CustomerName'),
        template['Id'] if template else None
    ))

# 執(zhí)行批量插入
target_cursor.executemany(insert_query, insert_values)
target_conn.commit()
print(f"成功遷移了 {len(insert_values)} 條數(shù)據(jù)")

# 關(guān)閉數(shù)據(jù)庫連接
source_cursor.close()
source_conn.close()
target_cursor.close()
target_conn.close()