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

base_dir = r"\\192.168.88.100\d\Downloads\0 film"
os.chdir(base_dir)

tunnel = sshtunnel.SSHTunnelForwarder(
	('62.60.211.153', 22),
	ssh_username = 'root',
	ssh_password = '09122736253',
	remote_bind_address = ('127.0.0.1', 3306)
) 
tunnel.start()


mydb = mysql.connector.connect(
		user = 'IMDB',
		password = 'IMDB@imdb123123123',
		host = '127.0.0.1',
		port = tunnel.local_bind_port,
		database = 'IMDB'
	)



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
for dir in folders:
	clear()
	print(step,"/",total_count)
	step=step+1	
	print(dir)
	tmp = dir.split("/")
	primaryTitle=tmp[-1]
	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("]","%")
	print(primaryTitle) 
	
	startYear = primaryTitle.split("%")[-1]
	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 concat(primaryTitle,' ',startYear) like %s and titleType in ('movie','tvMovie') order by startYear",("%" + primaryTitle + "%",);
	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
	for x in myresult:
		tconst=x[0]
		titleType=x[1]
		primaryTitle=x[2]
		primaryTitle=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
	#  				echo $row2['averageRating']." ".$row['tconst']." ".$row['primaryTitle']." (".$row['startYear'].") [".$row['genres']."]";
	movie_index=input("Which One?:")
	if movie_index=="":
		shutil.move(dir,"./unready/")
		continue
	if movie_index.startswith("tt"):
		tconst=movie_index	
	else:
		tconst=myresult[int(movie_index)][0]
	print(tconst)
	mycursor.execute("SELECT a.*,b.averageRating FROM basics a,ratings b where a.tconst=b.tconst and a.startYear is not null and a.tconst=%s",(tconst,))
	myresult = mycursor.fetchall()
	for x in myresult:
		tconst=x[0]
		titleType=x[1]
		primarytitle=x[2]
		primaryTitle=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(dir,file_name)