# -*- coding: utf-8 -*-
#
"""
1.统计replicate表的成功、失败、耗时
2.统计syncdiff表的成功、失败、耗时
"""

import random
import string
import os
import re
import subprocess
import time
import datetime
from configparser import ConfigParser
import ast

import mysql.connector

def f_prepare_datatype():
    pass

def f_get_replicate_columns(sql_header='', sql_stmt=''):
    stmt_concat = ''
    cursor.execute(sql_stmt)
    for row in cursor:
        # print(row[0], row[1])
        tab_rows= "%s:%s" %(row[0], row[1])
        stmt_concat = "%s %10s, " %(stmt_concat, tab_rows)
    print("%s: %s" %(sql_header, stmt_concat))

def f_get_replicate_data(db_conn_str='', batchid=''):
    """
    get table datatype
    """
    tidb_conn_dict = ast.literal_eval(db_conn_str)
    stmt_query_total = """
    select group_concat(distinct table_schema) as table_schema,
        count(1) as cnt,
        sum(source_count) as srows,
        sum(target_count) as trows,
        sum(case when repl_status='replicate_succ' then 1 else 0 end) as succ,
        sum(case when repl_status='replicate_succ' then source_count else 0 end) as succ_rows,
        sum(case when repl_status='repl_timeout' then 1 else 0 end) as timeout,
        sum(case when repl_status='repl_timeout' then source_count else 0 end) as timeout_rows,
        sum(case when repl_status='replicate_exit' then 1 else 0 end) as exit,
        sum(case when repl_status='replicate_exit' then source_count else 0 end) as exit_rows,
        sum(case when repl_status in ('replicate_succ','repl_timeout','replicate_exit') then 0 else 1 end) as other,
        sum(case when repl_status in ('replicate_succ','repl_timeout','replicate_exit') then 0 else source_count end) as other_rows,
        min(insert_starttime),
        max(repl_endtime)
    from test.table_repl_info 
    where batchid in (%s)
    """ %(batchid)

    stmt_query_detail = """
    select repl_status,group_concat(distinct table_schema) as table_schema,count(1) as cnt,sum(source_count) as rows
    from test.table_repl_info 
    where batchid in (%s) 
    group by repl_status
    """ %(batchid)

    stmt_query_succ = """
    select t2.data_type,count(1) as col_cnt 
    from test.table_repl_info t1
    inner join information_schema.columns t2 
    on t1.table_schema=t2.table_schema 
    and t1.table_name=t2.table_name
    where t1.batchid in (%s) 
    and t1.repl_status in ('replicate_succ')
    group by t2.data_type 
    order by 2 desc
    """ %(batchid)

    stmt_query_timeout = """
    select t2.data_type,count(1) as col_cnt 
    from test.table_repl_info t1
    inner join information_schema.columns t2 
    on t1.table_schema=t2.table_schema 
    and t1.table_name=t2.table_name
    where t1.batchid in (%s) 
    and t1.repl_status in ('repl_timeout')
    group by t2.data_type 
    order by 2 desc
    """ %(batchid)

    stmt_query_exit = """
    select t2.data_type,count(1) as col_cnt 
    from test.table_repl_info t1
    inner join information_schema.columns t2 
    on t1.table_schema=t2.table_schema 
    and t1.table_name=t2.table_name
    where t1.batchid in (%s) 
    and t1.repl_status in ('replicate_exit')
    group by t2.data_type 
    order by 2 desc
    """ %(batchid)
    #debug

    # print(type(tidb_conn_dict), tidb_conn_dict)
    # print('stmt_query_total:', type(stmt_query_total), stmt_query_total)
    # print('stmt_query_detail:', type(stmt_query_detail), stmt_query_detail)
    # print('stmt_query_succ:', type(stmt_query_succ), stmt_query_succ)
    # print('stmt_query_timeout:', type(stmt_query_timeout), stmt_query_timeout)
    # print('stmt_query_exit:', type(stmt_query_exit), stmt_query_exit)

    global cursor
    cnx = mysql.connector.connect(**tidb_conn_dict)
    cursor = cnx.cursor()
    cursor.execute(stmt_query_total)

    # replicate summary
    for row in cursor:
        # print(row[0], row[1], row[2], row[3], row[4] )
        repl_summary = """
本次统计的测试批次为: %s 
测试开始时间:%s   测试结束时间:%s
测试共覆盖了 %s 模式下的 %s 张表,总计 %s 行,复制成功 %s 行;
测试覆盖的表数目/数据行数: 成功:%s/%s, 超时:%s/%s, 报错:%s/%s, 其它:%s/%s;\n""" %(
            batchid.replace('\'',''), row[12], row[13], row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11])

    print('%s\n%s [INFO] 复制批次 %s 的结果汇总:\n%s' %('-'*80, time.strftime("%Y-%m-%d-%H.%M.%S", time.localtime()), batchid, '-'*80  ))
    print(repl_summary)
    f_get_replicate_columns('复制 成功 的字段类型和字段数目', stmt_query_succ)
    f_get_replicate_columns('复制 超时 的字段类型和字段数目', stmt_query_timeout)
    f_get_replicate_columns('复制 失败 的字段类型和字段数目', stmt_query_exit)

    #replicate speed
    #大于10000行的平均速度和95%速度
    #小于10000行的平均耗时和95%耗时

    stmt_query_succ_cnt = """
    select count(1),
           sum(case when target_count>=10000 then 1 else 0 end) as 1w0,
           sum(case when target_count>=10000 then 0 else 1 end) as 1w1
    from test.table_repl_info 
    where batchid in (%s)
    and repl_status='replicate_succ'
    """ %(batchid)

    cursor.execute(stmt_query_succ_cnt)
    for row in cursor:
        succ_1w0 = row[1]
        succ_1w1 = row[2]

    # print("rows 1w+:%s, 1w-:%s" %(succ_1w0, succ_1w1))

    stmt_query_succ_avgspeed = """
    select count(1) as tab_cnt,
           avg(repl_speed) as avg_speed,max(repl_speed) as max_speed,min(repl_speed) as min_speed,
           sum(case when rid=%s then repl_speed else 0 end) as 95th_speed
    from 
    (
    select row_number() over(order by target_count/repl_duration desc) as rid,
           table_schema,table_name,target_count/repl_duration as repl_speed
    from test.table_repl_info 
    where batchid in (%s)
    and repl_status='replicate_succ'
    and target_count>=10000
    ) t
    """ %(int(int(succ_1w0)*0.95), batchid)

    # print(stmt_query_succ_avgspeed)
    cursor.execute(stmt_query_succ_avgspeed)
    for row in cursor:
        speed_1w = "\n数据量大于1w行的表共 %s 张, 平均复制速度 %6d r/s, 95th的复制速度高于 %6d r/s, 最大复制速度 %6d r/s, 最小复制速度 %6d r/s" %(
            row[0], row[1], row[4], row[2], row[3])
    print(speed_1w)


    stmt_query_succ_avgsec = """
    select count(1) as tab_cnt,
           avg(repl_duration),max(repl_duration),min(repl_duration),
           sum(case when rid=%s then repl_duration else 0 end) as 95th_duration
    from 
    (
    select row_number() over(order by repl_duration desc) as rid,
           table_schema,table_name,repl_duration
    from test.table_repl_info 
    where batchid in (%s)
    and repl_status='replicate_succ'
    and target_count<10000
    ) t
    """ %(int(int(succ_1w1)*0.95), batchid)

    # print(stmt_query_succ_avgspeed)
    cursor.execute(stmt_query_succ_avgsec)
    for row in cursor:
        sec_1w = "数据量小于1w行的表共 %s 张, 平均复制耗时 %6d s  , 95th的复制耗时低于 %6d s  , 最大复制耗时 %6d s  , 最小复制耗时 %6d s  " %(
            row[0], row[1], row[4], row[2], row[3])
    print(sec_1w)

    cursor.close()
    cnx.close()
    print('\n%s\n%s [INFO] 复制批次 %s 的结果汇总结束!\n%s' %('-'*80, time.strftime("%Y-%m-%d-%H.%M.%S", time.localtime()), batchid, '-'*80  ))


def main():
    """
    """
    #run test result
    global config
    global tbspacename
    config = ConfigParser()
    config.read('analyze_replicate_dbcfg.ini')

    # read config file
    batchid = config.get('db', 'batchid')
    db_conn_str = config.get('tidb', 'connstr')

    batchid = batchid.replace('[', '').replace(']', '')
    # display config
    print('db_conn_str:', type(db_conn_str), db_conn_str)
    print('batchid:', type(batchid), batchid)

    #analyze replicate data
    f_get_replicate_data(db_conn_str, batchid )


if __name__ == '__main__':
    main()