from os import path, environ

import csv
import json
import os
import re
from datetime import datetime

import pandas as pd
import sqlite3
import sqlglot

from sqlalchemy import create_engine
LIB_PATH = "planedecode/lib/decodec_linux_x64.so"

def clean_slowlog(content):
    """
    clean up the original slowlog to remove some unnecessary information
    :param content: the original slowlog list
    :return: slowlog list after removing the use {database} sentence (if it has one)
    """
    result = []
    for c in content:
        matches = parse(c, r"#\s*(\w+)\s*:\s*(.*?)\n")
        db = ""
        for m in matches:
            if m[0] == "DB":
                db = m[1]
        string = "use {};".format(db)
        c = c.replace(string, "")
        result.append(c)
    return result


def clean_slowlog_use_old(content):
    """
    clean up the original slowlog to remove some unnecessary information
    :param content: the original slowlog list
    :return: slowlog list after removing the use {database} sentence (if it has one)
    """
    matches = parse(content, r"# DB:\s*(.*?)\n")
    for m in matches:
        string = "use {};".format(m)
        content = content.replace(string, '')
    matches = parse(content, r"\n[0-9]{4}\-[0-9]{2}\-[0-9]{2}")
    for m in matches:
        content = content.replace(m, '@@{}'.format(m))
    return content


def clean_slowlog_use(content,serverless=False):
    """
    clean up the original slowlog to remove some unnecessary information
    :param content: the original slowlog list
    :return: slowlog list after removing the use {database} sentence (if it has one)
    """
    pattern = r"(?m)^use\s+(\w+)\s*;"
    result = re.sub(pattern, "", content)
    if serverless:
        pattern = "\n# Time"
        result = re.sub(pattern, lambda m: '@@{}'.format(m.group()), result)
    else:
        pattern = r"\n[0-9]{4}\-[0-9]{2}\-[0-9]{2}"
        result = re.sub(pattern, lambda m: '@@{}'.format(m.group()), result)
    return result


def split(content, reg,serverless=False):
    """
    use the incoming regular expression to split the content
    :param content: string content to be split
    :param reg: specified regular expression
    :return: a list of strings split by regular expressions
    """
    pattern = r'{}'.format(reg)
    regex = re.compile(pattern)
    content = clean_slowlog_use(content,serverless)
    matches = regex.split(content)
    # result = clean_slowlog(matches)
    return matches


def parse(content, reg):
    """
    use regular expressions to get what we need from the content
    :param content: string content to be parsed
    :param reg: specified regular expression
    :return: a list of strings parsed by regular expressions
    """
    pattern = r'{}'
    pattern = pattern.format(reg)
    matches = re.findall(pattern, content, re.MULTILINE)
    return matches


def convert_to_dict(li):
    """
    convert a tuple list to a dictionary, for example, [(a1, b1), (a2, b2)] to {a1: b1, a2: b2}
    :param li: a tuple list
    :return: a dictionary converted from a tuple list
    """
    dic = dict()
    for a in li:
        dic[a[0]] = a[1]
    return dic


def mapping(resource, keys, value_index):
    """
    use the given keys and value index to map the data required in the resource into a dictionary
    :param resource: data resource, which can be a list or a dictionary,
                    or other data structures that can obtain data through index
    :param keys: the desired key of the dictionary
    :param value_index: the index of the value corresponding to the key in the resource.
    :return: dictionary converted from resource
    """
    values = []
    for i in value_index:
        if isinstance(i, int):
            if i >= len(resource):
                values.append("")
            else:
                values.append(resource[i])
        elif isinstance(i, str):
            if i in ['Cop_backoff']:
                values.append(match_key(resource, i))
                continue
            if i not in resource:
                values.append("")
            else:
                values.append(resource[i])
    dictionary = dict(zip(keys, values))
    return dictionary


def match_key(dictionary, prefix):
    for key in dictionary:
        if key.startswith(prefix):
            return dictionary[key]
    return ''


def merge_map(dict1: dict, dict2: dict):
    """
    merge two dictionaries, for example, {a1: b1} and {a2: b2} to {a1: b1, a2: b2}
    :param dict1: the first dictionary
    :param dict2: the second dictionary
    :return: the merged dictionary
    """
    result = {}
    result.update(dict1)
    result.update(dict2)
    return result


def parse_multi(content, reg):
    result = []
    for c in content:
        if len(c) == 0:
            continue
        result.append(parse(c, reg))
    return result


def convert_to_dict_multi(li):
    result = []
    for l in li:
        result.append(convert_to_dict(l))

    return result


def mapping_multi(resource, keys, valuesindex):
    result = []
    for rs in resource:
        if len(rs) == 0:
            mp = {}
            for k in keys:
                mp[k] = ''
            result.append(mp)
            continue
        result.append(mapping(rs, keys, valuesindex))
    return result


def merge_map_multi(dict1, dict2):
    i = 0
    result = []

    for d in dict1:
        r = merge_map(dict1[i], dict2[i])
        i += 1
        result.append(r)
    return result

def merge_map_multi_condition(dict1, dict2,condtion):
    i = 0
    result = []

    for d in dict1:
        r = merge_map_condition(dict1[i], dict2[i],condtion)
        i += 1
        result.append(r)
    return result
def merge_map_condition(dict1: dict, dict2: dict,condition:dict):
   
    merged_dict = dict1.copy()
    for key, value in dict2.items():
        if key in condition and dict1.get(key) == condition[key]:
            merged_dict[key] = value
        elif key not in dict1:
            merged_dict[key] = value
    return merged_dict

def convert_dict_to_tuple_multi(lst, keys):
    result = []
    for d in lst:
        r = convert_dict_to_tuple(d, keys)
        result.append(r)
    return result


def openfile(filepath: str) -> str:
    f = open(filepath)
    content = f.read()
    return content


def convert_csv_to_dataframe(filename) -> pd.DataFrame():
    tmp_lst = []
    with open(filename, 'r') as f:
        reader = csv.reader(f)
        for row in reader:
            tmp_lst.append(row)
    df = pd.DataFrame(tmp_lst[1:], columns=tmp_lst[0])
    return df


def convert_dict_to_tuple(data, keys):
    r = []
    for c in keys:
        r.append(data[c])
    return convert_to_tuple(r)


def convert_list_to_tuple(data):
    result = []
    for d in data:
        r = convert_to_tuple(d)
        result.append(r)
    return result


def convert_to_tuple(data: list):
    return tuple(data)


def create_sqlite(name, create_sql, insert_sql, data):
    conn = sqlite3.connect(name)
    c = conn.cursor()
    if create_sql:
        c.execute(create_sql)
    if insert_sql:
        c.executemany(insert_sql, data)
        conn.commit()
    conn.close()


def clean_metrics(data, start_ts, end_ts, step) -> list:
    if len(data) == 0:
        return []
    headers = clean_metrics_header(data[0])
    result = [headers]
    i = 1
    ts = start_ts
    while i < len(data):
        j = 0
        # process the missing data
        while j < len(data[i]):
            if data[i][j] == "" or data[i][j] == "NaN":
                data[i][j] = '0'
            j += 1
        # add timestamp
        data[i].append(ts)
        result.append(data[i])
        ts = start_ts + (i * step) if start_ts + (i * step) <= end_ts else end_ts
        i += 1
    labels = result[0]
    labels_mp = {}
    i = 0
    for l in labels:
        labels_mp[l] = i
        i += 1
    new_result = [('labels', 'value', 'timestamp')]
    i = 1
    while i < len(result):
        ts = result[i][-1]
        for k, v in labels_mp.items():
            t = (k, result[i][v], ts)
            new_result.append(t)
        i += 1
    return new_result


def clean_metrics_header(header):
    result = []
    for h in header:
        pattern = r'{(.*?)}'
        match = re.search(pattern, h)
        if match:
            content = match.group()
            content = content.replace('""', '')
            print('content', content)
            dic = json.loads(content)
            print('dic:', dic)
            r = []
            for key in dic:
                r.append(dic[key])
            s = '-'
            if len(r) == 0:
                r.append('value')
            result.append('{}'.format(s.join(r)))
        else:
            result.append('value')
    return result


def read_csv(file_name):
    metrics_data = []
    if os.path.exists(file_name):
        with open(file_name) as csvfile:
            csv_reader = csv.reader(csvfile)
            for row in csv_reader:
                metrics_data.append(row)
    return metrics_data


def convert_utc_time_to_timestamp(tm):
    if len(tm) > 27:
        tm = tm[:25] + "Z"
    try:
        utc_time = datetime.strptime(tm, "%Y-%m-%dT%H:%M:%S.%fZ")
    except:
        return 0
    timestamp = int((utc_time - datetime(1970, 1, 1)).total_seconds())
    return timestamp


def get_timestamps(slowlog_dict):
    """
    :param slowlog_dict:
    :return:
    """
    result = {}
    for s in slowlog_dict:
        key = '"{}"'.format(s["Digest"])
        value = convert_utc_time_to_timestamp(s["Time"])
        if key not in result:
            result[key] = []
        result[key].append(value)
    return result


def convert_to_metrics(data, start_ts, end_ts, step):
    result = {}
    for key in data:
        data[key].sort()
        index = 0
        start = start_ts
        end = start_ts
        result[key] = {}
        while end <= end_ts:
            # 计算每个时间点的 count
            index, count = calculate_count(data[key], start, end, index)
            result[key][end] = count
            if end == end_ts:
                break
            start = end
            end = end + step if end + step < end_ts else end_ts
    return result


def convert_to_metrics_v2(data, start_ts, end_ts, step):
    result = {}
    digest = []
    value = []
    timestamp = []
    for key in data:
        data[key].sort()
        index = 0
        start = start_ts
        end = start_ts
        result[key] = {}
        while end <= end_ts:
            # 计算每个时间点的 count
            index, count = calculate_count(data[key], start, end, index)
            result[key][end] = count
            digest.append(key)
            value.append(count)
            timestamp.append(end)
            if end == end_ts:
                break
            start = end
            end = end + step if end + step < end_ts else end_ts
    df = pd.DataFrame({'Digest': digest, 'value': value, 'timestamp': timestamp})
    df.set_index('timestamp', inplace=True)
    return result, df

# todo 边界
def calculate_count(data, start, end, index):
    count = 0
    while index < len(data):
        if int(data[index]) < start:
            index += 1
        elif int(data[index]) > end:
            return index, count
        else:
            count += 1
            index += 1
    return index, count


def calculate_tuple_count(data, start, end, index):
    count = 0
    total = 0
    while index < len(data):
        if int(data[index][0]) < start:
            index += 1
        elif int(data[index][0]) > end:
            return index, count, total
        else:
            count += 1
            total += data[index][1]
            index += 1
    return index, count, total


def build_data_for_sqlite(data, start_ts, end_ts, step):
    """

    :param data:
    :param start_ts:
    :param end_ts:
    :param step:
    :return:
    """
    rows = []
    head = []

    for key in data:
        head.append(key)
    rows.append(tuple(head))

    tm = start_ts
    while tm <= end_ts:
        row = []
        for key in data:
            row.append(data[key][tm])
        row.append(tm)
        rows.append(tuple(row))
        if tm == end_ts:
            break
        tm = tm + step if tm + step < end_ts else end_ts
    return rows


def build_create_sql(data, table_name, column_type, add_ts):
    create_sql, insert_sql = "", ""
    if len(data) > 0:
        head = data[0]
        create_sql = "CREATE TABLE IF NOT EXISTS {}".format(table_name)
        column = head[0] if head[0] != '""' else '"value"'
        create_sql += "\n({} {}".format(column, column_type)
        i = 1
        while i < len(head):
            if head[i] == 'Query_time':
                create_sql += ",\n{} {} UNIQUE".format(head[i], column_type)
            else:
                create_sql += ",\n{} {}".format(head[i], column_type)
            i += 1
        if add_ts:
            create_sql += ",\ntimestamp INTEGER PRIMARY KEY NOT NULL);"
        else:
            create_sql += ");"
        insert_sql = "INSERT OR REPLACE INTO {} VALUES ({})"
        tmp = ""
        if len(data) > 1:
            if len(data[1]) > 0:
                tmp = "?"
                i = 1
                while i < len(data[1]):
                    tmp += ",?"
                    i += 1
        insert_sql = insert_sql.format(table_name, tmp)
    return create_sql, insert_sql


def create_metrics_sqlite(data, db_name, table_name):
    create_sql, insert_sql = build_create_sql(data, table_name, "INTEGER", False)
    create_sqlite(db_name, create_sql, insert_sql, data[1:])


def latency_classification(df: pd.DataFrame, sqlite_name):
    columns = df.columns
    columns.sort_values()
    columns_float = []
    for c in columns:
        if c != '+Inf':
            columns_float.append(float(c))
    columns_float.sort()
    columns_float.append('+Inf')

    i = len(columns_float) - 1
    new_df = pd.DataFrame()

    while i > 0:
        key1 = ''
        key2 = ''
        if columns_float[i] != '+Inf':
            key1 = '{}'.format(columns_float[i])
            key2 = '{}'.format(columns_float[i-1])
        else:
            key1 = columns_float[i]
            key2 = '{}'.format(columns_float[i-1])
        new_df[key1] = df[key1] - df[key2]
        i -= 1
    new_df['{}'.format(columns_float[0])] = df['{}'.format(columns_float[0])]
    engine = create_engine('sqlite:///{}'.format(sqlite_name), echo=True)

    mp = {}
    columns = new_df.columns
    for c in columns:
        if c != '+Inf':
            mp[float(c)] = new_df[c]
    latency1 = pd.DataFrame()
    latency2 = pd.DataFrame()
    latency3 = pd.DataFrame()

    for k, v in mp.items():
        if k <= 0.3:
            latency1 = pd.concat([latency1, v], axis=1)
        elif 0.3 < k <= 1:
            latency2 = pd.concat([latency2, v], axis=1)
        else:
            latency3 = pd.concat([latency3, v], axis=1)
    latency3 = pd.concat([latency3, new_df['+Inf']], axis=1)
    latency1['0.3'] = latency1.apply(lambda x: x.sum(), axis=1)
    latency2['1'] = latency2.apply(lambda x: x.sum(), axis=1)
    latency3['+Inf'] = latency3.apply(lambda x: x.sum(), axis=1)
    latency_df = pd.concat([latency1['0.3'], latency2['1'], latency3['+Inf']], axis=1)
    latency_df.index.set_names('timestamp', inplace=True)
    latency_df.to_sql("latency_rearrange", engine, if_exists='replace')


def slowlog_query_time_classification(df: pd.DataFrame, start_ts, end_ts, sqlite_name, step):
    timestamp_set = query_time_classification(df)
    count_df, time_df = convert_to_metrics_v4(timestamp_set, start_ts, end_ts, step)
    sql_type_df = slowlog_sql_type_classification(df)
    engine = create_engine('sqlite:///{}'.format(sqlite_name), echo=True)
    count_df.to_sql(name='slowlog_query_time_count', con=engine, if_exists='replace')
    time_df.to_sql(name='slowlog_query_time_total', con=engine, if_exists='replace')
    sql_type_df.to_sql(name='slowlog_with_type', con=engine, if_exists='replace')


SELECT_TYPE = 'select'
UPDATE_TYPE = 'update'
INSERT_TYPE = 'insert'
DELETE_TYPE = 'delete'
REPLACE_TYPE = 'replace'
OTHER_TYPE = 'other'


def slowlog_sql_type_classification(df: pd.DataFrame):
    type_list = []
    for index, row in df.iterrows():
        if 'select' in row['sql_statement'][:12] or 'SELECT' in row['sql_statement'][:12]:
            type_list.append(SELECT_TYPE)
        elif 'update' in row['sql_statement'][:12] or 'UPDATE' in row['sql_statement'][:12]:
            type_list.append(UPDATE_TYPE)
        elif 'insert' in row['sql_statement'][:12] or 'INSERT' in row['sql_statement'][:12]:
            type_list.append(INSERT_TYPE)
        elif 'delete' in row['sql_statement'][:12] or 'DELETE' in row['sql_statement'][:12]:
            type_list.append(DELETE_TYPE)
        elif 'replace' in row['sql_statement'][:12] or 'REPLACE' in row['sql_statement'][:12]:
            type_list.append(REPLACE_TYPE)
        else:
            type_list.append(OTHER_TYPE)
    sql_type = pd.DataFrame({'sql_type': type_list})
    columns = list(df.columns)
    new_df = pd.DataFrame()
    if 'sql_type' in columns:
        new_df = df
    else:
        new_df = pd.concat([df, sql_type], axis=1)
    if 'Plan' in columns:
        new_df.drop(columns=['Plan'], inplace=True)
    # if 'Plan_digest' in columns:
    #     new_df.drop(columns=['Plan_digest'], inplace=True)
    new_df.set_index('Time', inplace=True)
    return new_df


def query_time_classification(df: pd.DataFrame):
    '''
    遍历所有的Query_time，进行分类
    收集其对应的时间
    :param df:
    :return:
    '''
    df['Query_time'] = df['Query_time'].apply(pd.to_numeric)
    timestamp_set = {
        # '0.3': [],
        '1': [],
        '5': [],
        # '10': [],
        # '100': [],
        '+Inf': []
    }
    for index, row in df.iterrows():
        ts = row['Time']
        query_time = row['Query_time']
        if query_time <= 1:
            timestamp_set['1'].append((ts, query_time))
        elif 1 < query_time <= 5:
            timestamp_set['5'].append((ts, query_time))
        # elif 5 < row['Query_time'] <= 10:
        #     timestamp_set['10'].append(ts)
        # elif 10 < row['Query_time'] <= 100:
        #     timestamp_set['100'].append(ts)
        elif query_time > 5:
            timestamp_set['+Inf'].append((ts, query_time))
    return timestamp_set


def convert_to_metrics_v3(data: dict, start_ts, end_ts, step):
    result = {
        'timestamp': []
    }
    timestamp = {}
    for key, value in data.items():
        if key not in result.keys():
            result[key] = []
        value.sort()
        index = 0
        start = start_ts
        end = start_ts
        while end <= end_ts:
            # 计算每个时间点的 count
            index, count = calculate_count(data[key], start, end, index)
            result[key].append(count)
            timestamp[end] = True
            if end == end_ts:
                break
            start = end
            end = end + step if end + step < end_ts else end_ts
    result['timestamp'] = list(timestamp.keys())
    result['timestamp'].sort()
    print(result)

    df = pd.DataFrame(result)
    df.set_index('timestamp', inplace=True)
    return df


def convert_to_metrics_v4(data: dict, start_ts, end_ts, step):
    count_result = {
        'timestamp': []
    }
    time_result = {
        'timestamp': []
    }
    timestamp = {}
    print(data.keys())
    for key, value in data.items():
        print('key', key)
        if key not in count_result.keys():
            count_result[key] = []
            time_result[key] = []
        value.sort()
        index = 0
        start = start_ts
        end = start_ts
        while end <= end_ts:
            # 计算每个时间点的 count
            index, count, total = calculate_tuple_count(data[key], start, end, index)
            count_result[key].append(count)
            time_result[key].append(total)
            timestamp[end] = True
            if end == end_ts:
                break
            start = end
            end = end + step if end + step < end_ts else end_ts
    count_result['timestamp'] = list(timestamp.keys())
    count_result['timestamp'].sort()
    time_result['timestamp'] = list(timestamp.keys())
    time_result['timestamp'].sort()

    count_df = pd.DataFrame(count_result)
    count_df.set_index('timestamp', inplace=True)
    time_df = pd.DataFrame(time_result)
    time_df.set_index('timestamp', inplace=True)
    return count_df, time_df


def create_slowlog_sqlite(data, db_name, table_name):
    if len(data) > 0:
        columns = list(data[0].keys())
        sqlite_data = [columns]
        for d in data:
            v = []
            for c in columns:
                cv = d[c]
                if c == "Time":
                    cv = convert_utc_time_to_timestamp(d[c])
                v.append(cv)
            sqlite_data.append(tuple(v))
        # sqlite_data = tuple(sqlite_data)
        create_sql, insert_sql = build_create_sql(sqlite_data, table_name, "TEXT", False)
        create_sqlite(db_name, create_sql, insert_sql, sqlite_data[1:])
    else:
        create_sqlite(db_name, "", "", "")

def create_slowlog_count_sqlite(data, start_ts, end_ts, step, db_name, table_name):
    """

    :param data: merge_map之后的结果，是一个字典列表
    :param start_ts:
    :param end_ts:
    :param step:
    :param db_name:
    :param table_name:
    :return:
    """
    ts_dict = get_timestamps(data)
    metrics = convert_to_metrics(ts_dict, start_ts, end_ts, step)
    sqlite_data = build_data_for_sqlite(metrics, start_ts, end_ts, step)
    create_sql, insert_sql = build_create_sql(sqlite_data, table_name, "INTEGER", True)
    create_sqlite(db_name, create_sql, insert_sql, sqlite_data[1:])


def create_events_sqlite(data, db_name, table_name):
    create_sql, insert_sql = build_create_sql(data, table_name, "TEXT", False)
    create_sqlite(db_name, create_sql, insert_sql, data[1:])


def calculate_slowlog_detail_count(data: pd.DataFrame, start_ts, end_ts, sqlite_name, step):
    digest_data = collect_timestamps_for_digest(data)
    metric, df = convert_to_metrics_v2(digest_data, start_ts, end_ts, step)
    engine = create_engine('sqlite:///{}'.format(sqlite_name))
    df.to_sql(name='slowlog_detail', con=engine, if_exists='replace')


def calculate_slowlog_count(data: pd.DataFrame, start_ts, end_ts, sqlite_name, step):
    ts = data['Time'].values
    ts.sort()
    start = start_ts
    end = start_ts
    timestamp = []
    value = []
    index = 0
    total = 0
    while end <= end_ts:
        # 计算每个时间点的 count
        index, count = calculate_count(ts, start, end, index)
        total += count
        value.append(count)
        timestamp.append(end)
        if end == end_ts:
            break
        start = end
        end = end + step if end + step < end_ts else end_ts
    df = pd.DataFrame({'value': value, 'timestamp': timestamp})
    df.set_index('timestamp', inplace=True)
    engine = create_engine('sqlite:///{}'.format(sqlite_name))
    df.to_sql(name='slowlog_count', con=engine, if_exists='replace')


def collect_timestamps_for_digest(data: pd.DataFrame):
    result = {}
    for index, row in data.iterrows():
        key = '{}'.format(row['Digest'])
        value = int(row['Time'])
        if key not in result:
            result[key] = []
        result[key].append(value)
    return result


def parse_sql_multi(sql_dict:list,key:str):
    result = []
    for d in sql_dict:
        r = parse_sql(d,key)
        result.append(r)
    return result
def parse_sql(sql:dict,key:str):
    tables=[]
    # covert ... to ? eg: select `site_code` , `currency` , sum ( `balance` ) `thirdbalance` from `bc_game_user` where `balance_timestamp` > ? and `site_code` in ( ... ) and `currency` in ( ... ) group by `site_code` , `currency`;
    rsql = sql[key].replace("...","?")
    # cover if (...) to a single value eg: if (`tv` > ? ) to if (`tv` > 1, 1, 0)
    # attention: the pattern is not perfect, it may not be able to cover all the cases when nested so deep
    pattern = r"if\s*\((?:[^()]+|\((?:[^()]+|\([^()]*\))*\))*\)?"
    replacement = "if (`tv` > 1, 1, 0)"
    rsql = re.sub(pattern, replacement, rsql)
    # convert insert into ... values ((len:10333),( .(len:5620) to insert into ... values (?),(?)
    rsql = re.sub(r'\(\s*\.?\(len:\d+\)', '(?)', rsql)
    # convert insert into values ( ? (len:13644) to (?)
    rsql = re.sub(r'\(\s*\?\s*\(len:\d+\)', '(?)', rsql)
    # delete (_charset)
    rsql = re.sub(r'\(\s*_charset\s*\)', '', rsql)
    try:
        for table in sqlglot.parse_one(rsql,dialect="mysql").find_all(sqlglot.exp.Table):
            tables.append(table.name)
    except Exception as e:
        print(e)
        print(sql[key])
        print(rsql)
        # now we try to use re to find table name
        pattern = r"insert into `(.*?)`"
        match = re.search(pattern, rsql, re.IGNORECASE)
        # If a match is found, extract the table name
        if match:
             tables.append(match.group(1))
        else:
            tables.append("<UNKNOWN>")
    return {"tables":";".join(tables)}

def analyze_plan_multi(plan_dict:list,key:str):
    result = []
    for d in plan_dict:
        r = analyze_plan(d[key])
        result.append(r)
    return result

def analyze_plan(plan:str):
    cops=[]
    tables=[]
    #we should get string in tidb_decode_plan eg: tidb_decode_plan('....')
    match = re.search(r"tidb_decode_plan\('(.*?)'\)", plan)
    if match:
        # if plan is not empty, we should get the content in tidb_decode_plan
        content = match.group(1)
        # parse the plan
        result = parse_plan(content)
        # check  the cop_task
        if result.find("cop[tiflash]") != -1:
            cops.append("tiflash")
        if result.find("cop[tikv]") != -1:
            cops.append("tikv")
        pattern = r'\ttable:(\w+)'
        matches = re.findall(pattern, result)
        for m in matches:
            tables.append(m)
        if len(tables) == 0:
            tables.append("<UNKNOWN>")
    return {"cop_path":";".join(cops),"tables":";".join(tables)}

def parse_plan(plan:str):
    import ctypes as ct
    lib = ct.cdll.LoadLibrary(LIB_PATH)
    lib.DecodePlan.argtypes = [ct.c_char_p]
    lib.DecodePlan.restype = ct.c_void_p
    cresult=lib.DecodePlan(ct.c_char_p(plan.encode()))
    result = ct.string_at(cresult).decode()
    lib.Freestr.argtypes = [ct.c_void_p]
    lib.Freestr(cresult)
    return result

def trace_multi(trace_key_dict:list,keys:list,type:str,time_key:str,cluster_id:str):
    result = []
    for d in trace_key_dict:
        r = trace(d,keys,type,time_key,cluster_id)
        result.append(r)
    return result

def trace(trace_key:dict,keys:list,type:str,time_key:str,cluster_id:str):
    new_time = trace_key[time_key]
    target=""
    for key in keys:
        target = "{}_{}".format(target, trace_key[key])
    data_key = "{}_{}".format(type, target)
    file_name = "trace.sqlite"
    cache_path = os.environ["CACHE_PATH"]
    cache_file_path = os.path.join(cache_path, cluster_id, file_name)
    conn = sqlite3.connect(cache_file_path)
    c = conn.cursor()
    select="SELECT * FROM TRACE WHERE KEY = '{}'".format(data_key)

    c.execute(select)

    row = c.fetchone()
    if row:
        if row[1] >= new_time:
            update="UPDATE TRACE set FIRST_TIME='{}' where KEY='{}'".format(new_time, data_key)
            c.execute(update)
            conn.commit()
            conn.close()
            return {"fresh_{}".format(type):trace_key[keys[-1]]}
        else:
            conn.close()
            return {"fresh_{}".format(type):""}
    else:
        insert="INSERT INTO TRACE (KEY,FIRST_TIME) VALUES ('{}','{}')".format(data_key, new_time)
        c.execute(insert)
        conn.commit()
        conn.close()
        return {"fresh_{}".format(type):trace_key[keys[-1]]}

if __name__ == '__main__':
    # environ['CACHE_PATH']='./cache'

    # print(trace({"DB":"aaa"}, "DB", "DB"))
    # print(trace({"DB":"aaa"}, "DB", "DB"))
    # print(trace({"tables":"bbb;ccc"}, "tables", "tables"))
    # print(trace({"Digest":"dsafdsa"}, "Digest", "Digest"))
    #
    # print(parse_sql({"sql_statement":"insert into `dh_active_luckyorder_kafka_consume` ( `active_id` , `useridx` , `receive_log_id` , `currency` , `bet_order_no` , `match_key` , `reward_amount` , `valid_bet` , `settle_time` , `content_version` , `remark` , `create_time` , `update_time` , `id` ) values ( ... ) , ( ... ) , ((len:10333), ( .(len:5620);"}, "sql_statement"))
    # print(parse_sql({"sql_statement":"insert into `mysql` . `stats_top_n` ( `table_id` , `is_index` , `hist_id` , value , `count` ) values ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... ) , ( ... , (_charset) ... );"}, "sql_statement"))
    # print(parse_sql({"sql_statement":"select sum ( if (`tv` > ? ) ) from `test_table` ;"}, "sql_statement"))
    # print(parse_sql({"sql_statement":"select `pull_time` from `prod_betcenter` . `bc_bet_records_5059` where ( `pull_time` < date_sub ( now ( ) , interval ? month ) ) order by if ( `isnull` ( `pull_time` ) , ... ) , `pull_time`;"}, "sql_statement"))
    # print(parse_sql({"sql_statement":"analyze table `prod_betcenter` . `bc_order_site_category_summary_5382`;"}, "sql_statement"))
    #
    # parse_plan("1gPIMAkyOF81CTAJMAlOL0EJMAl0aW1lOjIuNjVzLCBsb29wczoyLCBsb2NrX2tleXM6IHt0ASFENi44M21zLCByZWdpb246MiwgBR40Miwgc2xvd2VzdF9ycGMBLShvdGFsOiAwLjAwNBUvGF9pZDogMTABLTx0b3JlOiBkYi10aWt2LTEuEQrIcGVlci50aWRiMTM3OTY2MTk0NDY0NjQxMzE0My5zdmM6MjAxNjAsIHRpa3Zfd2FsbF90AZeIIDMuMDZtcywgc2Nhbl9kZXRhaWw6IHtnZXRfc25hcHNob3QNKRQ3LjEywrUBk8BvY2tzZGI6IHtrZXlfc2tpcHBlZF9jb3VudDogMSwgYmxvY2s6IHtjYWNoZV9oaXRfERwYM319fSwgfSEnBGNrBfAcNi43NjcwNzkBgghycGMNRhwyLCByZXRyeQ0P2DF9CTAgQnl0ZXMJTi9BCjEJMzZfMQkwCTEJdGFibGU6c3RhdHNfbWV0YSwgaW5kZXg6dGJsKHQBHAxfaWQpCW8BLgHxMqkBJDMJTi9BCU4vQQo=")
    # parse_plan("lTDweTAJMzFfNDYJMAk2NDg4My42MwlkYXRhOkV4Y2hhbmdlU2VuZGVyXzQ1CTY1ODA4CXRpbWU6MS4xOXMsIGxvb3BzOjczLCBjb3BfdGFzazoge251bTogNjYsIG1heDogMHMsIG1pbjogMHMsIGF2ZzogMHMsIHA5NTogARvIY29wcl9jYWNoZV9oaXRfcmF0aW86IDAuMDB9CTExLjMgTUIJTi9BCjEJNDlfNDUJMV8xGagRo0BUeXBlOiBQYXNzVGhyb3VnaBWrFGZsYXNoXwWZEHtwcm9jBZQUMS4yNXMsBZYQMS4xOHMNmAwxLjIyAZsEODABIgAzAQsEOTUBCzQ0cywgaXRlcnM6MzMsIAFNDQooaHJlYWRzOjUyOH0BpQEEFAoyCTNfNjanAEBwcm9kX2JldGNlbnRlci5iYwENXF9yZWNvcmRzXzY5OS5zaXRlX2NvZGUsIIYtABhhY2NvdW50jisAKGNhdGVnb3J5X2lkji8AHHBsYXRmb3Jtmi8ADGdhbWWaKwA4Y3VycmVuY3ksIGRhdGUohuUAdHNldHRsZV90aW1lX3pvbmUpLT5Db2x1bW4jNDYsIA0LBDM5FQsENDAZCwAxGQsAMhkLADMZCwA0GQsANa4rAgA3XSsAMUEg9isCFDMJM180MTYsAv7aAB3a/vIBSvIBulAC/tcCXtcC0nkCxnQC/hsC6hsCFDQJNl80MjYbAiBncm91cCBieTpNJAQ5M47iAf4uBGYuBHnUhlsBqrIBuoUEXWQkZnVuY3M6c3VtKDEiADSZN4EsRiEAADUZIQg0MCxKIQAANh0hADFOIQAANx0hADIRIQhtYXgVhAA4HSEAM04hAAA5HSEANBUhBGluEUIIMTAwHSIANREiHGZpcnN0cm93ik0FPVMIKS0+hq4BHTDKbgCdJZJuAB0wym4AVQCSbAAVLspqAG1KkmgADSzKZgBtKpJmAA0symYAcbySZwARLUJoAG1nBDEwYQWGVwI+1Af+YAXqYAUYNQk0OF80NDZhBSAJNjgxNzgzCXRiNwoOIAoUbWluOjEuMgwIJgsIEhYIFjYKARYWNgoINTI4gjcKADYS4AoAMzaRADbgCjRIYXNoUGFydGl0aW9uLAUPDgkJIHM6IFtuYW1lOoqHBV3/NGNvbGxhdGU6IE4vQV0spkMAVdreQQAy7wPeRQAyogTeRQB1P95BAHkZMkIAgkgCADEefwwOAg0kYXZnOiAzNC45bU1HARVBRAEISUEMNDYwOYJCAhQ3CTZfMzk2QQI+MwgIMTE1IvsKBDExJkkLCDExNx0YADgdDCZiCwQxMiZjCwQxMjIMBwAx2aAAOdXC/YgEMTD9BQA51cIyIgD9BgQ5NpFqLiIAADH9BgQ5NxEi/WoEMTEuiAAAOFIiAAAyHaoAORUi/WwEMTMZIgQxMCITCPVtkfMAMSpbCAgxMDeG8wGBGoE7IdYp8wgzLjb68wEAOA5BDAA3JfMkODM1MTY5OC45MopvDghhbGwOgw6OPwoMdmFsaRK9Do4tACBuZXRfcHJvZmmSLgD+WwBqWwC2iQA+mAaO6wC9Sob5BrU0hisAMh4Fhi8AMggFhi8AlfKGKwCZ3O6yDxKyDygxMjEJNzY4NjQwNV5cBxA5MDAuMWEOMiMDDDI3LjMBFQ5oD2E4NhYFBDE2ho4RoRAEMjg+IgMEZXGKNQtNFixzdGF0dXMsIDIpLCCWOAAmzBEgIjY5OSIpLCBnzvIQUCwgMjAyMy0xMS0wNSAwODowMDoxM/5NABgyLTE5IDAwAU0MMDAuMAUBECksIGx0phEBIpMREaEMMi0yMEpUABRuZShjYXOSWQA5Mjxkb3VibGUgQklOQVJZKSwgAZ0Abo4+AXVjACIhcZIzADJrA6JpAHkYqmoAIv4SBCIwop8AdayimwAyDQSiNgA9fggiIil+qQMEODlWqQP2pwMYMTAJNDNfMsnLuDEwMTI3NjY0MzcJdGFibGU6dDEsIGtlZXAgb3JkZXI6ZmFsc2UJNDA3NDgyMDgJWq4LBDg4UqkABDYuaqsADDMzMjZeaAkELCARaUhzY2FuOntkdGZpbGU6e3RvdGFsBRQwbmVkX3BhY2tzOjYwOIGbCRoMa2lwcBUaDDIwODMOeQgNHARjYQU2KHJvd3M6NDA5OTM3DmUJDR0NOQUdIDE2NjQxNzIzMwGgBVgwcnNfaW5kZXhfbG9hZGWVCDogMaVDDSAAZRkXFDQyMjFtcwHDBTs4Y3JlYXRlX3NuYXBzaG90DT0wMG1zfQlOL0EJTi9BCg==")
    # print(parse_sql({"sql_statement":"invalid"}, "sql_statement"))
    # print(parse_sql({"sql_statement":"insert into `bc_bet_records_5127` ( `id` , `version_no` , `record_id` , `order_no` , `agent_code` , `round_id` , `platform_id` , `category_id` , `site_code` , `site_prefix` , `account` , `third_user_name` , `third_game_code` , `all_bet` , `valid_bet` , `net_profit` , `rake` , `jackpot` , `bet_time` , `settle_time` , `settle_status` , `device` , `bet_ip` , `third_group_code` , `after_balance` , `is_combo` , `odds_type` , `odds` , `order_status` , `sports_type` , `winlost_time` , `game_id` , `currency` , `settle_time_zone` , `pull_time` , `bet_time_stamp` , `settle_time_stamp` ) values ( ? , if (`tv` > 1, 1, 0) , ? , now ( ) , `unix_timestamp` ( ? ) , `unix_timestamp` ( ? ) ) , ( ? , if (`tv` > 1, 1, 0) , ? , now ( ) , `unix_timestamp` ( ? ) , `unix_timestamp` ( ? ) ) on duplicate key update `order_no` = if (`tv` > 1, 1, 0) , `agent_code` = if (`tv` > 1, 1, 0) , `round_id` = if (`tv` > 1, 1, 0) , `platform_id` = if (`tv` > 1, 1, 0) , `category_id` = if (`tv` > 1, 1, 0) , `site_code` = if (`tv` > 1, 1, 0) , `site_prefix` = if (`tv` > 1, 1, 0) , `account` = if (`tv` > 1, 1, 0) , `third_user_name` = if (`tv` > 1, 1, 0) , `all_bet` = if (`tv` > 1, 1, 0) , `valid_bet` = if (`tv` > 1, 1, 0) , `net_profit` = if (`tv` > 1, 1, 0) , `rake` = if (`tv` > 1, 1, 0) , `jackpot` = if (`tv` > 1, 1, 0) , `bet_time` = if (`tv` > 1, 1, 0) , `settle_time` = if (`tv` > 1, 1, 0) , `settle_status` = if ( values ( `settle_time` )(len:7378);"}, "sql_statement"))
    #
    # print(parse_sql_multi([{"sql_statement":"select sum ( if (`tv` > ? ) ) from `test_table` ;"},{"sql_statement":"invalid"}], "sql_statement"))
    print(parse_sql_multi([{"sql_statement":"'insert into `dh_promote_agent_report` ( `stats_day` , `useridx` , `username` , `currency` , `regpkgid` , `parent_useridx` , `parent_username` , `my_deposit` , `my_withdraw` , `my_bet` , `my_net_profit` , `my_discount` , `my_artificial_deposit` , `my_all_bet` , `my_deposit_times` , `my_withdraw_times` , `my_bet_times` , `direct_new_members` , `direct_new_deposit` , `direct_all_bet` , `direct_first_deposit` , `direct_deposit` , `direct_artificial_deposit` , `direct_withdraw` , `direct_bet` , `direct_net_profit` , `direct_discount` , `direct_first_deposit_person` , `direct_deposit_person` , `direct_artificial_deposit_person` , `direct_withdraw_person` , `direct_bet_person` , `other_new_members` , `other_new_deposit` , `other_all_bet` , `other_first_deposit` , `other_deposit` , `other_artificial_deposit` , `other_withdraw` , `other_bet` , `other_net_profit` , `other_discount` , `other_first_deposit_person` , `other_deposit_person` , `other_artificial_deposit_person` , `other_withdraw_person` , `other_bet_person` , `visits` , `update_time` ) values ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... ) , ( ... (len:13644);"}], "sql_statement"))
    analyze_plan("tidb_decode_plan('hwbwSTAJMjdfMQkwCTAJTi9BCTAJdGltZToyLjMzcywgbG9vcHM6MSwgcHJlcGFyZTogMjJtcywgY2hlY2tfaW5zZXJ0OiB7dG90YWxfBTksIDIuMzFzLCBtZW1fCSAAXxEYDDI4cywBSShmZXRjaDogMjguNgFMSHJwYzp7QmF0Y2hHZXQ6e251bV8BEww4MywgHVgcMTQyLjJtc30RFRhwcm9jZXNzDV0EMzMBSiB0aWt2X3dhbGwNFgw4NS44ARgoc2Nhbl9kZXRhaWwVrRFAJGtleXM6IDIwMDA+WgABGjBfc2l6ZTogNTk5NTk5ESEBGQg6IDMFMyhnZXRfc25hcHNobxHp8Ew3NTMuNcK1cywgcm9ja3NkYjoge2Jsb2NrOiB7Y2FjaGVfaGl0X2NvdW50OiAyOTk0NiwgcmVhZF9jb3VudDogMTAsIHJlYWRfYnl0ZSFxGDEuNiBLQiwJJSlNIDE1Ni44wrVzfQEBBCwgAV4NlwB7BSEENTghLQEzIGdpb246ODEsIAW2ADEFtRhzbG93ZXN0JWcEIHslZRg6IDAuMDQ5FTNEX2lkOiAzNDE1NjY0MSwgc3RvIf0IZGItIWQILTUuEQqQcGVlci50aWRiMTM3OTY2MTk0NDY0NjQxMzE0My5zdmM6MjAxNiFbOpsBCDUxMCUjNpsBAGdKTgEILjE0BSqKTQEcMTIxfX19LCAxEkFHJDEuOTExMTg0MzEF3QRwYy16PDgxfQk0LjQ2IE1CCU4vQQo=')")
    print(parse_sql_multi([{"sql_statement":"SELECT original_sql, bind_sql, default_db, status, create_time, update_time, 'charset', collation, source, sql_digest, plan_digest  FROM mysql.bind_info WHERE update_time > '0000-00-00 00:00:00' ORDER BY update_time, create_time;"}], "sql_statement"))