# -*- coding: utf-8 -*-
#
"""
1.load all data into xxx_raw schema
2.pick the table which contain/not contain given data type
3.exec insert select
"""

import random
import string
import os
import subprocess
import mysql.connector
import time
import datetime

#mysql
mys81 = {
    'user': 'user',
    'password': 'password',
    'host': '127.0.0.1',
    'port': '4100',
    'database': 'ts_coms',
    'buffered': True,
    'raise_on_warnings': True
}

mys88 = {
    'user': 'user',
    'password': 'password',
    'host': ''127.0.0.1',
    'port': '3306',
    'database': 'test',
    'buffered': False,
    'raise_on_warnings': True
}


#不支持的字段类型
#time类型，无主键，但是包含varbinary,tinytext,text,lob

tasks_dict = {}
source_schema="ts_coms_raw"
target_schema="ts_coms"

####
#search tables contain given data type
datatype_in_lst = ["bigint", "smallint", "int", "float", "mediumint", "tinyint", "decimal",
                      "tinytext", "text", "mediumtext", "longtext",
                   "varchar", "char",
                   "date", "datetime" ]
datatype_notin_lst = ["binary", "bit", "varbinary", "tinyblob", "blob", "mediumblob", "longblob",
                      "double",
                      "set", "enum", "json",
                      "time", "year" , "timestamp"]
table_contain_pk = ""
####

if table_contain_pk.lower() == "yes":
    stmt_pkflag = """ and exists 
(select 1 from INFORMATION_SCHEMA.table_constraints t4
 where t1.table_schema=t4.table_schema and t1.table_name=t4.table_name 
 and t4.constraint_type in ('PRIMARY KEY', 'UNIQUE')
)"""
elif table_contain_pk.lower() == "no":
    stmt_pkflag = """ and not exists 
(select 1 from INFORMATION_SCHEMA.table_constraints t4
 where t1.table_schema=t4.table_schema and t1.table_name=t4.table_name 
 and t4.constraint_type in ('PRIMARY KEY', 'UNIQUE')
)"""
else:
    stmt_pkflag = ""

stmt_query = """
select table_name from INFORMATION_SCHEMA.tables t1
where table_schema='%s' 
and exists
(select 1 from INFORMATION_SCHEMA.columns t2
 where t1.table_schema=t2.table_schema and t1.table_name=t2.table_name 
 and t2.data_type in ('%s')
)
and not exists 
(select 1 from INFORMATION_SCHEMA.columns t3
 where t1.table_schema=t3.table_schema and t1.table_name=t3.table_name 
 and t3.data_type in ('%s')
)
and not exists
(select 1 from INFORMATION_SCHEMA.columns t0
 where t1.table_schema=t0.table_schema and t1.table_name=t0.table_name 
 and t0.extra in ('STORED GENERATED','VIRTUAL GENERATED')
)
%s 
order by table_name
""" %( target_schema, "','".join(datatype_in_lst).lower(), "','".join(datatype_notin_lst).lower(), stmt_pkflag )

print(stmt_query)
stmt_output = []

cnx = mysql.connector.connect(**mys81)
cursor = cnx.cursor()
cursor.execute(stmt_query)

for row in cursor:
    stmt_output.append(row[0])

print("%s rows in set" %(len(stmt_output)))
time.sleep(5)

#start db dml
i = 0
status_query = "show drainer status"
status_output = {"nodeid": ["127.0.0.1:8258", "127.0.0.1:8255"], "state": ["online"]}

#ignore table , already replicate succ
ignore_tab_set = set()
ignore_tab_file = "test_pass_tabs.txt"

try:
    f = open(ignore_tab_file, mode='r')
    for line in f:
        ignore_tab_set.add(line.split()[0])
    f.close()
except FileNotFoundError:
    print("open file or set add error")
    with open(ignore_tab_file, mode='w') as f:
        f.write("# -*- coding: utf-8 -*-\n")

print("test_pass_tabs: %s" %(len(ignore_tab_set)))
time.sleep(5)

#write table name to file
f = open(ignore_tab_file, mode='a')

for tab in stmt_output:
    if tab in ignore_tab_set:
        continue
    stmt_select = "select count(1) from  %s.%s " %(target_schema, tab )
    stmt_delete = "delete from  %s.%s " %(target_schema, tab)
    stmt_insert = "insert into %s.%s select * from %s.%s" %(target_schema, tab, source_schema, tab)
    # print(stmt_delete,stmt_insert)
    i = i + 1
    rows_delete = 0
    rows_insert = 0
    run_failed = 0
    try:
        # cursor.execute(stmt_select)
        # for row in cursor:
        #     print(row[0])
        cursor.execute(stmt_delete)
        rows_delete = cursor.rowcount
        cursor.execute(stmt_insert)
        rows_insert = cursor.rowcount
        print("execute insert finish %s , %s(delete) %s(insert) rows affect, the %s tabs" % (tab, rows_delete, rows_insert, i))
        cnx.commit()

        # cursor.close()
        # exit()
    except:
        print("insert failed,exit %s" %(stmt_insert))
        run_failed =1
        #exit()

    ##check drainer status
    strtime = datetime.datetime.now() - datetime.timedelta(seconds=10)
    ttime = strtime.strftime('%Y-%m-%d %H:%M:%S')
    drainer_status = 0

    cursor.execute(status_query)
    for row in cursor:
        # print(row[0], row[1], row[2], row[3], row[4])
        if row[0] in status_output["nodeid"] and row[2] in status_output["state"] and row[4]>ttime:
            drainer_status = drainer_status + 1
        elif row[0] in status_output["nodeid"]:
            print("Drainer status: %s %s %s %s %s" %(row[0], row[1], row[2], row[3], row[4]))

    #exit when drainer pause
    if drainer_status < len(status_output["nodeid"]):
        print("Drainer not healty, stop dml, healty count %s<%s" %(drainer_status, len(status_output["nodeid"])))
        print("Pls check: %s" %(stmt_insert))
        exit()

    #write succ table name
    if run_failed == 0:
        f.write(tab+"    "+datetime.datetime.now().strftime('%Y-%m-%d-%H:%M:%S')+"\n")
    else:
        f.write("failed " + tab + "    " + datetime.datetime.now().strftime('%Y-%m-%d-%H:%M:%S') + "\n")

cursor.close()
cnx.close()
f.close()
# print("t_gt0:", t_gt0, "t_le0:", t_le0)
#print(output)
# stmt_drop = "drop table if exists %s" $(tablename)