# Run the seeding script on all of the CDBs
# Doug Jackson
import os
import glob
import datetime
import re
import pandas as pd
import mysql.connector
#import mysql
#from mysql import *

##########################################################################################
# Constants
##########################################################################################
workingDir = "P:/EPA_2017_NEI_CDBs/2017_CDB_Revisions/RepCountySeeding/"

dBdir = "C:/ProgramData/MySQL/MySQL Server 5.7/Data"
MySQLdir = "C:/ProgramData/MySQL/MySQL Server 5.7/Data"
user="root"
password=""
seedingScriptFile = "P:/EPA_2017_NEI_CDBs/2017_CDB_Revisions/RepCountySeeding/SeedingScript_ERG.sql"

##########################################################################################
# Functions
##########################################################################################
def queryDB(cnx, query):
    cursor = cnx.cursor()
    cursor.execute(query)
    columnNames = cursor.column_names
    result = pd.DataFrame(cursor.fetchall(), columns = columnNames)
    return result

def noReturnDB(cnx, query):
    cursor = cnx.cursor()
    cursor.execute(query)

##########################################################################################
# Run
##########################################################################################
os.chdir(workingDir)
cnx = mysql.connector.connect(user=user, password=password, host='127.0.0.1', database="seed")
noReturnDB(cnx, "flush tables;")
            
# Read the names of all of the MOVES databases
dBnames = glob.glob(os.path.join(dBdir, "c*y2017_*"))
dBnames = [os.path.basename(p) for p in dBnames]
numDBs = len(dBnames)

# Update the date stamp on the databases
dateStamp = datetime.datetime.now().strftime(format="%Y%m%d")
for d in dBnames:
    oldDateStamp = re.search("\d{8}", d).group(0)
    newDBname = d.replace(oldDateStamp, dateStamp)
    os.rename(os.path.join(dBdir, d), os.path.join(dBdir, newDBname))

# Read the new database names
dBnames = glob.glob(os.path.join(dBdir, "c*y2017_*"))
dBnames = [os.path.basename(p) for p in dBnames]
                               
 
    
# Run the seeding script on each database
count = 0
for d in dBnames:
    count+=1
    print("Processing", d, ". Database", count, " of ", numDBs)
    
    # Read in the seeding script
    with open(seedingScriptFile, "r") as fH:
        seedingScript = fH.read() 
    
    seedingScript = seedingScript.replace("CDBPLACEHOLDER", d)
    
 # Write the scripts to files and run using os.system, as the pymysql approach is unreliable
    with open("tempSeedingScript.sql", "w") as fH:
        print(seedingScript, file=fH)
    
    returnStatus = os.system("mysql --defaults-extra-file=user.cnf < " + os.path.join(workingDir, "tempSeedingScript.sql"))
    if returnStatus!=0:
        print("Failed to run tempSeedingScript. returnStatus=", returnStatus)




    
