#import packages
import pandas as pd
import pymysql
#import mysql.connector
#import MySQLdb
import os
import logging
from importlib import reload
reload(logging)
import coloredlogs
import sqlalchemy
from sqlalchemy import create_engine, String, Integer
import datetime as dt
from dotenv import load_dotenv
load_dotenv()

# Get environment variables
user = os.getenv('db_user')
password = os.getenv('db_password')
localhost =  os.getenv('db_localhost')  #'127.0.0.1'
db_name = os.getenv('db_name')
table_name=os.getenv('channel_name')
data_path=os.getenv('path_of_file')

#create table_name folder and its subfolders
if not os.path.exists(table_name):
    os.makedirs(table_name)
    print(f'the folder {table_name} is created !')

    project_folders=[table_name+'_videosTs','iframe_live','jingles_found','iframes']
    for fol in project_folders:
        pat = os.path.join(table_name, fol)
        if not os.path.exists(pat):
            os.makedirs(pat)
            print(f'the {pat} is created')
else:
    print('all the folders are already exist !!!.')



#create log file
logging.getLogger('sqlalchemy').setLevel(logging.ERROR)
logger = logging.getLogger(__name__)  
fh = logging.FileHandler(table_name+'/'+table_name+'.log')
fh.setLevel(logging.INFO)
formatter = coloredlogs.ColoredFormatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
logger.addHandler(fh)
coloredlogs.install(level='INFO')


sql="CREATE DATABASE IF NOT EXISTS "+db_name

mydb = pymysql.connect(
  host=localhost,
  user=user,
  password=password
)

mycursor = mydb.cursor()

mycursor.execute(sql)

#cursor.execute(sql)
mycursor.execute("Use "+db_name)
mycursor.execute("create table IF NOT EXISTS "+table_name+" (id_diffussion INT AUTO_INCREMENT PRIMARY KEY,id_chaine VARCHAR(100), Event_Name VARCHAR(100), Arabic_Event_Name VARCHAR(100), French_Event_Name VARCHAR(255), date_naissance VARCHAR(255), Start_Date VARCHAR(100), Start_Time VARCHAR(100), End_Date VARCHAR(100), End_Time VARCHAR(100), Genre VARCHAR(100))")



#read the program and do some preprocessing
#data_path='epg_elhiwar_update_decembre.xls'
#data_path='epg_elhiwar_janvier.xls'
data=pd.read_excel(data_path)
l=['id_chaine', 'Event Name',
       'Arabic Event Name', 'French Event Name', 'Start Date', 'Start Time',
       'End Date', 'End Time','Genre']

df=data[l]
df=df.fillna('')
df['Event Name']=df['Event Name'].apply(lambda row: row.replace(' ','') if row[-1]==' ' else row)
df['Event Name']=df['Event Name'].apply(lambda row: row.replace(' ','_'))
result=[col.replace(' ','_') for col in l]
df.columns=result
df['Event_Name']=df['Event_Name'].str.lower()
# insert the program into a table 
connection = pymysql.connect(host=localhost,
                         user=user,
                         password=password,
                         db=db_name)


cursor=connection.cursor()


cols = "`,`".join([str(i) for i in df.columns.tolist()])

for i,row in df.iterrows():
    sql1 = "INSERT IGNORE INTO "+table_name+" (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
    cursor.execute(sql1, tuple(row))
    connection.commit()
logger.info('Insert data from %s into %s table.',data_path.split('/')[-1],table_name)


#get names of programs and create a folder of each one
sql_select_Query = "select Event_name from "+table_name
cursor = connection.cursor()
cursor.execute(sql_select_Query)
records = list(cursor.fetchall())

names={}
new_records=set([record[0] for record in records])
shows_name=list(new_records.difference(names))


Pa=os.path.join(table_name, 'iframes')

for show in shows_name:

    path = os.path.join(Pa, show)
    if not os.path.exists(path):
                os.makedirs(path)
logger.info('Create folders(i_frames) for %s .',table_name)