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


tunnel = sshtunnel.SSHTunnelForwarder(
    ('45.92.94.221', 22),
    ssh_username = 'root',
    ssh_password = 'Masoud1987',
    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'
    )


parentTconst = 'tt10986410'
season_no = 3
base_dir = "/Volumes/192.168.100.100/Series/TED LASSO/S03"
#base_dir = "/Users/masoud/Desktop/TD03"


os.chdir(base_dir)

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

#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)