import pandas as pd
import mysql.connector
import time
import os
import cv2
import sys
from dotenv import load_dotenv
from datetime import timedelta
from datetime import datetime
from pathlib import Path
from importlib import reload
import logging
reload(logging)
import coloredlogs
import sqlalchemy
import telegram
import json
from dotenv import load_dotenv
load_dotenv()

user = os.getenv('db_user')
password = os.getenv('db_password')
localhost = os.getenv('db_localhost')
db_name = os.getenv('db_name')
table_name=os.getenv('channel_name')
channel_name=table_name

# logging.getLogger('sqlalchemy').setLevel(logging.ERROR)
# logger = logging.getLogger(__name__)
# # Create a filehandler object
# fh = logging.FileHandler(table_name+'/'+table_name+'a.log')
# fh.setLevel(logging.INFO)
# # Create a ColoredFormatter to use as formatter for the FileHandler
# formatter = coloredlogs.ColoredFormatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
# fh.setFormatter(formatter)
# logger.addHandler(fh)
# coloredlogs.install(level='INFO')
#token that can be generated talking with @BotFather on telegram
my_token = '5522894419:AAE9CeONlV2lfmUGqbXGZAHC-l1Z90TMuD4'

# def send(msg, chat_id, token=my_token):
#     """
#     Send a message to a telegram user or group specified on chatId
#     chat_id must be a number!
#     """
#     bot = telegram.Bot(token=token)
#     bot.sendMessage(chat_id=chat_id, text=msg)

import requests

def telegram_bot_sendtext(bot_message):

   bot_token = '5522894419:AAE9CeONlV2lfmUGqbXGZAHC-l1Z90TMuD4'
   bot_chatID = "-1001778433367"
   send_text = 'https://api.telegram.org/bot' + bot_token + '/sendMessage?chat_id=' + bot_chatID + '&parse_mode=Markdown&text=' + bot_message

   response = requests.get(send_text)

#logging.getLogger('sqlalchemy').setLevel(logging.ERROR)
#logger = logging.getLogger(__name__)

def get_date_of_jingle(channel_name,jingle,user,password,localhost,db_name):

    current_date=time.strftime("%d:%m:%Y").replace(':','/')
    current_time=datetime.strptime(time.strftime("%H:%M:%S"),"%H:%M:%S")
    result=''

    connection = mysql.connector.connect(host=localhost,
                         user=user,
                         password=password,
                         db=db_name,
                        auth_plugin='mysql_native_password')

    cursor=connection.cursor()

    sql='select Start_Time from ' +channel_name+ ' where Start_Date='+'"'+current_date+'"'+' and Event_name='+'"'+jingle+'"'
    cursor.execute(sql)
    records = cursor.fetchall()

    if len(records)==1:
        return records[0]
    else:

        for record in records:
            #print(record)
            interval=[]
            interval.append(datetime.strptime(':'.join(record[0].split(':')[:3]),"%H:%M:%S")- timedelta(minutes=150))
            interval.append(datetime.strptime(':'.join(record[0].split(':')[:3]),"%H:%M:%S")+ timedelta(minutes=150))


            if current_time>interval[0] and current_time<interval[1]:
                result=record
                break

        #print('date of the jingle is :',result)
        return result


def update_jingle_time(channel_name,jingle,user,password,localhost,db_name):

    current_date=time.strftime("%d:%m:%Y").replace(':','/')
    current_time=(time.strftime("%H:%M:%S")+":00",)
    result=get_date_of_jingle(channel_name,jingle,user,password,localhost,db_name)
    #print('date of ', jingle,' from database is:',result)

    connection = mysql.connector.connect(host=localhost,
                         user=user,
                         password=password,
                         db=db_name,
                        auth_plugin='mysql_native_password')

    cursor=connection.cursor()
    #sql='UPDATE '+channel_name+' SET Start_Time ="'+current_time[0]+'" where Start_Date="'+current_date+'" and Event_name="'+jingle+'" and Start_Time=""'

    sql='UPDATE '+channel_name+' SET Start_Time ='+'"'+current_time[0]+'"'+' where Start_Date='+'"'+current_date+'"'+' and Event_name='+'"'+jingle+'"'+' and Start_Time='+'"'+result[0]+'"'
    #print(sql)
    #ple=(current_time[0],result[0])

    cursor.execute(sql)

    connection.commit()



def compare_image(img1,img2):
    img1=cv2.imread("hiwar_tounsi/iframe_live/"+img1)
    img2=cv2.imread(img2)

    sift = cv2.xfeatures2d.SIFT_create()
    kp_1, desc_1 = sift.detectAndCompute(img1, None)
    kp_2, desc_2 = sift.detectAndCompute(img2, None)

    index_params = dict(algorithm=0, trees=5)
    search_params = dict()
    flann = cv2.FlannBasedMatcher(index_params, search_params)
    matches = flann.knnMatch(desc_1, desc_2, k=2)
    
    good_points = []
    ratio = 0.2
    for m, n in matches:
        if m.distance < ratio*n.distance:
            good_points.append(m)

    return len(good_points)




    #img1=cv2.imread(i_frame)
    #img2=cv2.imread(jingle)
    #print(img1)
    #print(img2)

    #orb= cv2.ORB_create()

    #kb_a, desc_a= orb.detectAndCompute(img1,None)
    #kb_b, desc_b= orb.detectAndCompute(img2,None)

    #bf = cv2.BFMatcher(cv2.NORM_HAMMING, crossCheck=True)
    #matches=bf.match(desc_a, desc_b)
    #similar_regions=[i for i in matches if i.distance < 50]

    #if len(matches) ==0:
        #return 0
    #return len(similar_regions)/len(matches)


############## there is a case to handle when we handle the beginning of a show
def get_folders_to_handle(table_name,user,password,localhost,db_name):

    current_date=time.strftime("%d:%m:%Y").replace(':','/')
    connection = mysql.connector.connect(host=localhost,
                         user=user,
                         password=password,
                         db=db_name,
                        auth_plugin='mysql_native_password')

    sql='select Start_Time, id_diffussion, Event_Name from '+table_name+' where Start_Date='+'"'+current_date+'"'
    #print('##########################################################""')
    #print(sql)
    cursor=connection.cursor()
    cursor.execute(sql)
    records = cursor.fetchall()
    idd,times, event=[],[],[]
    

    for rec in records:
        times.append(datetime.strptime(':'.join(rec[0].split(':')[:-1]), "%H:%M:%S"))
        idd.append(rec[1])
        event.append(rec[2])

    data={'id_diffussion':idd,'Start_Time':times,'Event_Name':event}
    df=pd.DataFrame(data)

    curent_time_minus=datetime.strptime(time.strftime("%H:%M:%S"),"%H:%M:%S")- timedelta(hours=2)
    curent_time_plus=datetime.strptime(time.strftime("%H:%M:%S"),"%H:%M:%S")+ timedelta(hours=2)

    get_idd_frame=df[(df.Start_Time<curent_time_plus) & (df.Start_Time>curent_time_minus)]

    return list(get_idd_frame.Event_Name)



def get_next_show(channel_name,user,localhost,password,db_name,jingle):

    current_date=current_date=time.strftime("%d:%m:%Y").replace(':','/')
    connection = mysql.connector.connect(host=localhost,
                         user=user,
                         password=password,
                         db=db_name,
                        auth_plugin='mysql_native_password')

    cursor=connection.cursor()
    date_show=get_date_of_jingle(channel_name,jingle,user,password,localhost,db_name)[0]

    sql='select End_Time from '+channel_name+' where Event_name="'+jingle+'" and Start_Time="'+date_show+'" and Start_Date="'+current_date+'"'
    cursor.execute(sql)
    records = cursor.fetchall()

    End_time=records[0]

    sql1='select Event_name from '+channel_name+' where Start_Time="'+End_time[0]+'" and Start_Date="'+current_date+'"'
    cursor.execute(sql1)
    records1 = cursor.fetchall()

    return records1[0][0]



def update_detective(channel_name,user,localhost,password,db_name,jingle):

    with open("detective.json", "r+") as jsonFile1:
        data_detective = json.load(jsonFile1)

        data_detective['current_name']=data_detective['next_name']
        data_detective['next_name']=get_next_show(channel_name,user,localhost,password,db_name,jingle)

        jsonFile1 = open("detective.json", "w+")
        jsonFile1.write(json.dumps(data_detective))
        jsonFile1.close()
    #logger.info('update is done replacing %s by %s',data_detective['current_name'],data_detective['next_name'])