# -*- coding: utf-8 -*-
#
"""

"""

import random
import string
import os
import subprocess
import time
from datetime import date, datetime
from configparser import ConfigParser
import ast

import cx_Oracle
import mysql.connector


def exetime(func):
    def newFunc(*args, **args2):
        if options.debug:
            t0 = time.time()
            print ("@%s, {%s} start") % (time.strftime("%X", time.localtime()), func.__name__)
            back = func(*args, **args2)
            print ("@%s, {%s} end") % (time.strftime("%X", time.localtime()), func.__name__)
            print ("@%.3fs taken for {%s}") % (time.time() - t0, func.__name__)
        else:
            back = func(*args, **args2)
        return back
    return newFunc


def f_get_mys_rowcount():
    # read config file
    dbtype = config.get('db', 'dbtype').lower()
    tab_schema = config.get('db', 'dbschema')
    db_conn_dict = ast.literal_eval(config.get('tidb', 'connstr'))
    conn_str = db_conn_dict
    print("%s db: %s" %(dbtype, tab_schema))

    cnx = mysql.connector.connect(**conn_str)
    cursor = cnx.cursor()

    #tidb tab list
    stmt_query_tab_detail = "select table_name from INFORMATION_SCHEMA.tables where table_schema='%s' order by table_name limit 10000" % (tab_schema.lower())
    output = []

    cursor.execute(stmt_query_tab_detail)
    for row in cursor:
        output.append(row[0])
    
    cursor = cnx.cursor()
    print("total tables in %s is %s" % (tab_schema, len(output)))

    #get table count
    t_gt0 = 0
    t_le0 = 0
    t_gt0_lst = []
    t_le0_lst = []
    t_gt0_set = set()
    t_le0_set = set()

    for tab_name in output:
        sql_tablecount = "select count(1) from %s.%s" % (tab_schema, tab_name)
        cursor = cnx.cursor()
        cursor.execute(sql_tablecount)
        for row in cursor.fetchall():
            # result = "%s" % (row[0])
            # print(result)
            if row[0] > 0:
                t_gt0 = t_gt0 + 1
                t_gt0_lst.append(tab_name)
                t_gt0_set.add("%s:%s" %(tab_name.upper(),row[0]))
                # f.write(tab_name + " " + result + "\n")
            else:
                t_le0 = t_le0 + 1
                # t_le0_lst.append(tab_name)
        cursor = cnx.cursor()
    print("total tables:",len(output),"t_gt0:",t_gt0,"t_le0:",t_le0)

    # t_gt0_str = "','".join(t_gt0_lst)
    # get datatype summary
    datatype_dict = {}
    i = 0
    stmt_query_col_type = "select table_name,data_type,column_name from information_schema.columns where table_schema='%s'" % (tab_schema.lower())
    cursor = cnx.cursor()
    cursor.execute(stmt_query_col_type)
    for row in cursor.fetchall():
        i = i + 1
        if row[0] in t_gt0_lst and row[1] in datatype_dict:
            datatype_dict[row[1]] = datatype_dict[row[1]] + 1
        elif row[0] in t_gt0_lst:
            datatype_dict[row[1]] = 1
        else:
            continue

    cursor = cnx.cursor()
    print("test contained data type %s times" %(i))
    print(datatype_dict)
    # print("test contained data type")
    # print("%20s %10s %10s" % ("data_type","table_count","col_count"))
    # print("%20s %10s %10s" % ("--------------------","----------","----------"))
    # for row in cursor.fetchall():
    #     print("%20s %10s %10s" % (row[0],row[1],row[2]))
    cnx.close()

    global tidb_rows_gt0_set
    tidb_rows_gt0_set = t_gt0_set


def f_get_ora_rowcount():
    # read config file
    dbtype = config.get('db', 'dbtype').lower()
    tab_schema = config.get('db', 'dbschema')
    db_conn_dict = ast.literal_eval(config.get('oracle', 'connstr'))
    conn_str = "%s/%s@%s:%s/%s" % (db_conn_dict['user'], db_conn_dict['password'], db_conn_dict['host'], db_conn_dict['port'], db_conn_dict['sid'])
    print("%s db: %s" %(dbtype, tab_schema))

    cnx = cx_Oracle.connect(conn_str)
    cursor = cnx.cursor()

    #oracle tab list
    stmt_query_tab_detail = "select distinct table_name from dba_tables where owner='%s' and table_name=upper(table_name) order by table_name  " % (tab_schema.upper())
    output = []

    cursor.execute(stmt_query_tab_detail)
    for row in cursor:
        output.append(row[0])

    cursor = cnx.cursor()
    print("total tables in %s is %s" % (tab_schema, len(output)))

    #get tables count
    t_gt0 = 0
    t_le0 = 0
    t_gt0_lst = []
    t_le0_lst = []
    t_gt0_set = set()
    t_le0_set = set()

    for tab_name in output:
        sql_tablecount = "select count(1) from %s.%s" % (tab_schema, tab_name)
        cursor = cnx.cursor()
        cursor.execute(sql_tablecount)
        for row in cursor.fetchall():
            # result = "%s" % (row[0])
            # print(result)
            if row[0] > 0:
                t_gt0 = t_gt0 + 1
                t_gt0_lst.append(tab_name)
                t_gt0_set.add("%s:%s" %(tab_name,row[0]))
            else:
                t_le0 = t_le0 + 1
                # t_le0_lst.append(tab_name)
                # t_le0_set.add("%s:%s" %(tab_name,row[0]))
        cursor = cnx.cursor()
    print("total tables:", len(output), "t_gt0:", t_gt0, "t_le0:", t_le0)

    # t_gt0_str = "','".join(t_gt0_lst)
    # ORA-01795: maximum number of expressions in a list is 1000
    # get datatype summary
    datatype_dict = {}
    i = 0
    stmt_query_col_type = "select table_name,data_type,column_name from dba_tab_columns where owner='%s'" % (tab_schema.upper())
    cursor = cnx.cursor()
    cursor.execute(stmt_query_col_type)

    for row in cursor.fetchall():
        i = i + 1
        if row[0] in t_gt0_lst and row[1] in datatype_dict:
            datatype_dict[row[1]] = datatype_dict[row[1]] + 1
        elif row[0] in t_gt0_lst:
            datatype_dict[row[1]] = 1
        else:
            continue

    cursor = cnx.cursor()
    print("test contained data type %s times" % (i))
    print(datatype_dict)
    cnx.close()

    global ora_rows_gt0_set
    ora_rows_gt0_set = t_gt0_set

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


    print("*********start check tidb testdata**********")
    f_get_mys_rowcount()
    print("*********start check oracle testdata**********")
    f_get_ora_rowcount()

    # check diff
    print("the difference count between source and target db")
    print(tidb_rows_gt0_set - ora_rows_gt0_set)
    print(ora_rows_gt0_set - tidb_rows_gt0_set)


if __name__ == '__main__':
    main()