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"C:\Users\nikoofar\Downloads\Video\HMT"
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()

parentTconst = 'tt5834204'
season_no = 6
base_dir = r"C:\Users\nikoofar\Downloads\Video\HMT"

os.chdir(base_dir)

#clear()
files = glob.glob("*.mkv") + glob.glob("*.mp4") + glob.glob("*.avi") 
files.sort()

print(files)
print("*************")


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()
i=1
for file in files:
      #clear()
      #print(file)
      tmp = file.split(".")
      extention=tmp[-1]
      #primaryTitle=primaryTitle.replace(".","%")
      #mycursor.execute("SELECT * FROM basics where concat(primaryTitle,' ',startYear) like %s and titleType='movie' order by startYear",("%" + primaryTitle + "%",))
      sql = """select a.tconst,a.primaryTitle,a.titleType,b.episodeNumber,b.seasonNumber 
      from basics a , episodes b 
      where 
      a.tconst=b.tconst and 
      episodeNumber={} and 
      b.parentTconst='{}' and 
      titleType='tvEpisode' and 
      b.seasonNumber={}
      order by 3""".format(i,parentTconst,season_no)
      mycursor.execute(sql)	
      myresult = mycursor.fetchall()
      i = i+1
      if len(myresult)==1:
            for x in myresult:
                  tconst=x[0]
                  primaryTitle=x[1]
                  episodeNumber=str(x[3]).zfill(2)
                  seasonNumber=str(x[4]).zfill(2)
                  file_name="S%sE%s - %s.%s" %(seasonNumber,episodeNumber,primaryTitle,extention) 	
                  file_name=file_name.replace(":","")
                  file_name=file_name.replace("/","_")
                  #print(file_name)
                  #os.rename(os.path.join(base_dir, file),os.path.join(base_dir, file_name))
                  #shutil.move(os.path.join(base_dir, file_name),"/run/media/masoud/09126907530/Movies/Fixed")
                  old=os.path.join(base_dir, file)
                  new=os.path.join(base_dir, file_name)
                  old = file
                  new = file_name
                  command = 'ren "%s" "%s"' %(old,new)
                  print(command)