# !/usr/bin/env python3
# -*- coding:utf-8 -*-

import pymysql
import time
from datetime import datetime
import os
import shutil

dbUser='db_order'
dbPasswd='db_order'
dbHost='118.89.139.104'
remoteDBUser='db_order'
remoteDBPasswd='db_order'
remoteDBHost='192.168.16.125'
dbCharset = 'utf8'
backupDir = '/home/developer/mysqlbackup'
backupDate =  time.strftime("%Y%m%d")
backupDir = '/home/developer/mysqlbackup/' + backupDate

conn = None
cur = None
try:
    if not os.path.exists(backupDir):
        os.makedirs(backupDir)
    print("backupDir exists:%s"%os.path.exists(backupDir))
    conn = pymysql.connect(host=dbHost,port= 3306,user = dbUser,passwd=dbPasswd,db='db_order')
    cur = conn.cursor()
    cur.execute("show databases  like 'db_%'")
    databases = cur.fetchall()
    print("databases:%s" %databases)
    print('The database backup to start! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))
    for db in databases:
        dbName = db[0]
    print('databaseName:%s'%dbName)
    fileName = '%s/%s_%s.sql' %(backupDir,dbName,backupDate)
    print('fileName:%s'%fileName)
    if os.path.exists(fileName):
         os.remove(fileName)
    os.system("mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" %(dbHost,dbUser,dbPasswd,dbName,dbCharset,backupDir,dbName,backupDate))
    print('The database backup success! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))
    
    cur.close()
    conn.close()

    writeFileName = '%s/%s_online_%s.sql' %(backupDir,dbName,backupDate)
    print("writeFileName:%s" %writeFileName)
    if os.path.exists(writeFileName):
        os.remove(writeFileName)
    with open(fileName, 'r', encoding='utf-8') as f:
        with open(writeFileName, 'a', encoding='utf-8') as f2:
            for line in f:
                line = line.replace("DROP TABLE IF EXISTS `ord_order_detail`", "DROP TABLE IF EXISTS `ord_order_detail_online`").replace("DROP TABLE IF EXISTS `ord_order_info`", "DROP TABLE IF EXISTS `ord_order_info_online`").replace("DROP TABLE IF EXISTS `pay_flow`","DROP TABLE IF EXISTS `pay_flow_online`").replace("CREATE TABLE `ord_order_detail`","CREATE TABLE `ord_order_detail_online`").replace("CREATE TABLE `ord_order_info`", "CREATE TABLE `ord_order_info_online`").replace("CREATE TABLE `pay_flow`", "CREATE TABLE `pay_flow_online`").replace("LOCK TABLES `ord_order_info`", "LOCK TABLES `ord_order_info_online`").replace("ALTER TABLE `ord_order_info`", "ALTER TABLE `ord_order_info_online`").replace("INSERT INTO `ord_order_info`", "INSERT INTO `ord_order_info_online`").replace("LOCK TABLES `ord_order_detail`", "LOCK TABLES `ord_order_detail_online`").replace("ALTER TABLE `ord_order_detail`", "ALTER TABLE `ord_order_detail_online`").replace("INSERT INTO `ord_order_detail`", "INSERT INTO `ord_order_detail_online`").replace("LOCK TABLES `pay_flow`", "LOCK TABLES `pay_flow_online`").replace("ALTER TABLE `pay_flow`", "ALTER TABLE `pay_flow_online`").replace("INSERT INTO `pay_flow`", "INSERT INTO `pay_flow_online`")
                f2.writelines(line)
            f2.flush()
            f2.close()
            f.close()
    print("rewrite file success!")

    baseFileName = backupDir + "/base_" + backupDate + ".sql"
    print("baseFileName:%s" % baseFileName)
    if os.path.exists(baseFileName):
        os.remove(baseFileName)
    os.system("mysqldump -h%s -u%s -p%s --opt -d %s --tables ord_order_info ord_order_detail pay_flow ord_member_activity ord_table_shards --default_character-set=%s > %s" %("192.168.16.124",dbUser,dbPasswd,"db_order_sharding",dbCharset,baseFileName))
    print("dump shard table base success!")
    remoteConn = pymysql.connect(host=remoteDBHost,port= 3306,user = remoteDBUser,passwd=remoteDBPasswd,db='db_wh_sharding')
    remoteCur = remoteConn.cursor()
    remoteCur.execute("create database if not exists db_order_sharding")
    os.system("mysql -h%s -u%s -p%s %s < %s" % (remoteDBHost, remoteDBUser, remoteDBPasswd, "db_order_sharding", writeFileName))
    print("import online data success!")
    os.system("mysql -h%s -u%s -p%s db_order_sharding < %s" % (remoteDBHost, remoteDBUser, remoteDBPasswd, baseFileName))
    print("import shard table base success!")

    remoteCur.execute("use db_order_sharding")
    remoteCur.execute("select distinct store_id from ord_order_info_online")
    storeIds = remoteCur.fetchall()
    print(storeIds)

    for storeIdTuple in storeIds:
        storeId = storeIdTuple[0]
        print("storeId:%s, type:%s" %(storeId, type(storeId)))
        remoteCur.execute("create table if not exists ord_order_info_" + str(storeId) + " like ord_order_info")
        remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_order_info_%s" % str(storeId))
        ordInfoCnt = remoteCur.fetchone()
        print(ordInfoCnt)
        if ordInfoCnt[0] == 0:
            remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_order_info_%s" % str(storeId), datetime.now()))            
        remoteCur.execute("create table if not exists ord_order_detail_" + str(storeId) + " like ord_order_detail")
        remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_order_detail_%s" % str(storeId))
        ordDetailCnt = remoteCur.fetchone()
        print(ordDetailCnt)
        if ordDetailCnt[0] == 0:
            remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_order_detail_%s" % str(storeId), datetime.now()))
        remoteCur.execute("create table if not exists pay_flow_" + str(storeId) + " like pay_flow")
        remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"pay_flow_%s" % str(storeId))
        payFlowCnt = remoteCur.fetchone()
        print(payFlowCnt)
        if payFlowCnt[0] == 0:
            remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("pay_flow_%s" % str(storeId), datetime.now()))
        remoteCur.execute("create table if not exists ord_member_activity_" + str(storeId) + " like ord_member_activity")
        remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_member_activity_%s" % str(storeId))
        ordMemberCnt = remoteCur.fetchone()
        print(ordMemberCnt)
        if ordMemberCnt[0] == 0:
            remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_member_activity_%s" % str(storeId), datetime.now()))

        remoteCur.execute("INSERT into ord_order_info_%s select * from ord_order_info_online where store_id = %s", (storeId, storeId))
        remoteCur.execute("insert into ord_order_detail_%s(id,order_id,product_sku,product_name,product_format,product_unit,retail_price,product_barcode,number,rate,store_Id) select d.id,d.order_id,d.product_sku,d.product_name,d.product_format,d.product_unit,d.retail_price,d.product_barcode,d.number,d.rate,i.store_Id from ord_order_info_online i, ord_order_detail_online d where i.id = d.order_id and i.store_id = %s", (storeId, storeId))
        remoteCur.execute("insert into pay_flow_%s(id,pay_flow,pay_type,business_code,order_id,pay_status,pay_amount,create_by,create_time,store_Id) select p.id,p.pay_flow,p.pay_type,p.business_code,p.order_id,p.pay_status,p.pay_amount,p.create_by,p.create_time,i.store_id from ord_order_info_online i, pay_flow_online p where i.id = p.order_id and i.store_id = %s", (storeId, storeId))
    remoteCur.execute("drop table ord_order_info_online")
    remoteCur.execute("drop table ord_order_detail_online")
    remoteCur.execute("drop table pay_flow_online")
    remoteCur.close()
    remoteConn.close()
except Exception as  e:
    print(e)