import os
from os.path import join, getsize, exists
import sys
import apsw
import re

#=========================================
def GetSeasonEpisode(filename):
    filename = filename.upper()
    resp = re.search(r'(.*).S\d\dE\d\d(\.*)', filename, re.M|re.I)  
    if resp:
        showname = resp.group(1)
        shownamelength = len(showname) + 1
        se = filename[shownamelength:shownamelength+6]
        season = se[1:3]
        episode = se[4:6]
        showname = showname.replace("."," ")
        ret = [showname,season,episode]
        return True,ret
    else:
        ret = ["",-1,-1]
        return False,ret

#=========================================
def MakeDataBase():
    # IF the table does not exist, this will create the table.  
    # Otherwise, this will be ignored due to the 'IF NOT EXISTS' clause
    sql = 'CREATE TABLE IF NOT EXISTS TvShows (pkID INTEGER PRIMARY KEY, Series TEXT, RootPath TEXT, Filename TEXT, Season TEXT, EPISODE TEXT);'
    cursor.execute(sql)

#=========================================    
def WalkThePath(filepath):
 
    showname = ""
    # Open the error log file
    efile = open('errors.log',"w")
    for root, dirs, files in os.walk(filepath,topdown=True):
        for file in [f for f in files if f.endswith (('.avi','mkv','mp4','m4v'))]:
            # Combine path and filename to create a single variable.
            fn = join(root,file)   
            OriginalFilename,ext = os.path.splitext(file)
            fl = file
            isok,data = GetSeasonEpisode(fl)
            if isok:
                showname = data[0]
                season = data[1]
                episode = data[2]
                print("Season {0} Episode {1}".format(season,episode))
            else:
                print("No Season/EPisode")
                efile.writelines('---------------------------\n')
                efile.writelines('{0} has no series/episode informaiton\n'.format(file))
                efile.writelines('---------------------------\n\n')
            
            sqlquery = 'SELECT count(pkid) as rowcount from TvShows where Filename = "%s";' % fl
            print(sqlquery)
            try:
                for x in cursor.execute(sqlquery):
                    rcntr = x[0]
                if rcntr == 0:  # It's not there, so add it
                    try:
                        sql = 'INSERT INTO TvShows (Series,RootPath,Filename,Season,Episode) VALUES (?,?,?,?,?)'
                        cursor.execute(sql,(showname,root,fl,season,episode))
                    except:
                        print("Error")
                        efile.writelines('---------------------------\n')
                        efile.writelines('Error writing to database...\n')
                        efile.writelines('Filename = {0}\n'.format(file))
                        efile.writelines('---------------------------\n\n')
            except:
                print("Error")
            print('Series - {0} File - {1}'.format(showname,file))

        # Close the log file
        efile.close
    # End of WalkThePath  
    
#=========================================    
def main():
    global connection
    global cursor   

    # Create the connection and cursor.
    connection = apsw.Connection("TvShows.db3")
    cursor = connection.cursor()
    MakeDataBase()
    #=========================================
    # Set your video media paths
    #=========================================
    startfolder = ["/extramedia/tv_files","/media/freeagnt/tv_files_2"]
    for cntr in range(0,2):
        WalkThePath(startfolder[cntr])    
    # Close the cursor and the database
    cursor.close()
    connection.close()

    print("Finished")

#=======================================
if __name__ == '__main__':
    main()