import mysql.connector 
import glob
import os
from os import system, name 
import shutil
import sshtunnel

with open("config.conf","r") as configfile:
	data=configfile.read()

configs = eval(data)

base_dir = r"\\192.168.88.100\a\Movies\00 Seen Movies\Unfixed"
os.chdir(base_dir)

tunnel = sshtunnel.SSHTunnelForwarder(
	(configs['SERVER_IP_ADDRESS'], 22),
	ssh_username = configs['SERVER_USER'],
	ssh_password = configs['SERVER_PASS'],
	remote_bind_address = ('127.0.0.1', 3306)
) 
tunnel.start()

mydb = mysql.connector.connect(
		user = configs['DB_USER'],
		password = configs['DB_PASS'],
		host = '127.0.0.1',
		port = tunnel.local_bind_port,
		database = configs['DB_NAME']
	)

def clear(): 
	_ = system('clear') 

clear()
folders = glob.glob("./*")


titleType_array = {
'1':'short',
'2':'movie',
'3':'tvMovie',
'4':'tvSeries',
'5':'tvEpisode',
'6':'tvShort',
'7':'tvMiniSeries',
'8':'tvSpecial',
'9':'video',
'10':'videoGame'
}

mycursor = mydb.cursor()
total_count = len(folders)
step = 1
step_fixed = 0
step_unfixed = 0
for dir in folders:
	clear()
	print(step,"/",total_count)
	print("fixed no:",step_fixed)
	print("unfixed no:",step_unfixed)
	step=step+1
	print(dir)
	primaryTitle=os.path.basename(dir)
	print(primaryTitle)
	primaryTitle=primaryTitle.replace(".","%")
	primaryTitle=primaryTitle.replace("_","%")
	primaryTitle=primaryTitle.replace("-","%")

	primaryTitle=primaryTitle.replace(" ","%")
	primaryTitle=primaryTitle.replace("(","%")
	primaryTitle=primaryTitle.replace(")","")
	primaryTitle=primaryTitle.replace("[","%")
	primaryTitle=primaryTitle.replace("]","%")
	startYear = primaryTitle.split("%")[-1]
	print(startYear.isdigit())
	if (startYear.isdigit()==True):
		primaryTitle = primaryTitle[:-4]
		sql_query = "SELECT * FROM basics where startYear = {} and titleType in ('movie','tvMovie') and MATCH(primaryTitle) AGAINST('\"{}\"') order by startYear".format(startYear,primaryTitle)
		sql_query = "SELECT * FROM basics where startYear = {} and titleType in ('movie','tvMovie') and primaryTitle like '{}' order by startYear".format(startYear,primaryTitle)
		print(sql_query)
	else:
		sql_query = "SELECT * FROM basics where titleType in ('movie','tvMovie') and MATCH(primaryTitle) AGAINST('\"{}\"') order by startYear".format(primaryTitle)
	mycursor.execute(sql_query)
	myresult = mycursor.fetchall()
	i = 0
	if len(myresult)==1:
		step_fixed=step_fixed+1
		for x in myresult:
			tconst=x[0]
			titleType=x[1]
			primaryTitle=x[2]
			originalTitle=x[3]
			isAdult=x[4]
			startYear=x[5]
			endYear=x[6]
			runtimeMinutes=x[7]
			genres=x[8]
			print(i," ----> ",tconst,titleType,primaryTitle,startYear)
			i = i+1
		tconst=myresult[0][0]
		print(tconst)
		mycursor.execute("SELECT a.*,b.averageRating FROM basics a,ratings b where a.tconst=b.tconst and a.tconst=%s",(tconst,))
		myresult = mycursor.fetchall()
		for x in myresult:
			tconst=x[0]
			titleType=x[1]
			primaryTitle=x[2]
			originalTitle=x[3]
			isAdult=x[4]
			startYear=x[5]
			endYear=x[6]
			runtimeMinutes=x[7]
			genres=x[8]
			averageRating=x[9]
			print(averageRating,"",tconst,"",primaryTitle,"(",startYear,") [",genres,"]")
			file_name="%s %s %s (%s) [%s]" %(averageRating,tconst,primaryTitle,startYear,genres)
			file_name=file_name.replace(":","")
			print(file_name)
			os.rename(os.path.join(base_dir, dir),os.path.join(base_dir, file_name))
			if not os.path.exists("Fixed"):
				os.makedirs("Fixed")
			shutil.move(file_name, "Fixed")
	else:
			if not os.path.exists("unready"):
				os.makedirs("unready")
			shutil.move(dir,"unready")
			step_unfixed=step_unfixed+1