import logging
import logging.handlers as handlers
from logging.handlers import RotatingFileHandler
import json
from collections import defaultdict
import collections
import sqlite3
import base64
import numpy as np
from cryptography.fernet import Fernet
import cv2
import face_recognition
import pyodbc
import config
from skimage import io as iosk

logging.basicConfig(
    handlers=[
        RotatingFileHandler("TFaceCompare.log", maxBytes=10485760, backupCount=10)
    ],
    level=logging.DEBUG,
    format='"%(asctime)s -[TFaceCompare] - %(levelname)-7.7s %(message)s',
    datefmt="%Y-%m-%d %H:%M:%S",
)
if config.is_encrypted_password:
    key = config.cipher_key
    cipher_suite = Fernet(key)
    password = cipher_suite.decrypt(config.password)
    password = password.decode("utf-8")
else:
    password = config.password

server = config.server
database = config.database
username = config.username
password = config.password

profilePic_encodingData1 = defaultdict(collections.Counter)


def save_faceauth_data(
    request_date,
    response_date,
    image1_path,
    image2_path,
    originator,
    agentId,
    result,
    resultReason,
    score,
    confidence,
):
    """
    This function inserts the data to the SQL server which has the details of face comparasion
    INPUT: request_date,response_date,originator,image1_path,image2_path,result,result_reason,score,confidence
    OUTPUT:
    """
    try:
        cnxn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )

        cursor = cnxn.cursor()
        logging.info(
            agentId
            + ":save_faceauth_data: requestDate: "
            + str(request_date)
            + ", responseDate: "
            + str(response_date)
            + ", Originator: "
            + str(originator)
            + ", Image Path1: "
            + str(image1_path)
            + ", Image Path2: "
            + str(image2_path)
        )

        cursor.execute(
            """
        INSERT INTO FaceAuth_Transactions (RequestDateTime, ResponseDateTime, Originator, Image1Path, Image2Path, Result, ResultReason, Score, Confidence) 
        VALUES (?,?,?,?,?,?,?,?,?)""",
            request_date,
            response_date,
            originator,
            image1_path,
            image2_path,
            result,
            resultReason,
            score,
            confidence,
        )
        cnxn.commit()
        logging.info(agentId + ":Successfully inserted data to the table")
        cursor.close()
        cnxn.close()
        return

    except Exception as ex:
        logging.info(
            agentId + ":Exception in inserting data to the sql table " + str(ex)
        )
        return


def load_profilepic():
    """
    This method will be called based on the config set to True
    THis will load the profilepic base64 from the DB configured
    to the Sqlite DB
    """
    try:
        cnxn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )

        cursor = cnxn.cursor()

        conn = sqlite3.connect("profilePicEncoding.db")
        lite_cursor = conn.cursor()

        cursor.execute("SELECT AgentId,ProfilePicture FROM AGT_Agent_Profile")
        for row in cursor.fetchall():
            
            if row[0] is None or row[0] != "Dhamu":
                continue
            lite_cursor.execute(
                "SELECT AGENTID FROM AGENTPROFILEPICDETAILS WHERE AGENTID = ?",
                (row[0],),
            )
            profilepic_base64 = row[1].split(";")[1].split(",")[1]
            profilePicEncoding = get_face_encoding_from_base64(profilepic_base64) 
                       
            logging.info('test1')
            string = json.dumps(profilePicEncoding[0].tolist())
            data_to_be_inserted = (row[0], profilepic_base64, string)
            ROW = lite_cursor.fetchone()
            logging.info('test5')
            if ROW is None:
                sqlite_insert_query = """INSERT INTO AGENTPROFILEPICDETAILS
                            (AgentId, PROFILEPICURL, ENCODINGDATA) 
                            VALUES 
                            (?,?,?)"""
                lite_cursor.execute(sqlite_insert_query, data_to_be_inserted)
                conn.commit()
            else:
                data_to_be_inserted = (profilepic_base64, string, row[0])
                sqlite_insert_query = """UPDATE AGENTPROFILEPICDETAILS SET PROFILEPICURL=?, ENCODINGDATA=? WHERE AGENTID=?"""
                lite_cursor.execute(sqlite_insert_query, data_to_be_inserted)
                conn.commit()

        lite_cursor.close()
        conn.close()
        cursor.close()
        cnxn.close()

        return profilePic_encodingData1
    except Exception as ex:
        logging.info("Exception in loadProfilePic {}".format(ex))
        return profilePic_encodingData1


def get_face_encoding_from_base64(base64String):
    try:
        nparr = np.frombuffer(base64.b64decode(base64String.encode("utf-8")), np.uint8)
        img = cv2.imdecode(nparr, cv2.IMREAD_COLOR)
        rgb_img = cv2.cvtColor(img, cv2.COLOR_BGR2RGB)
        image_encodeing = face_recognition.face_encodings(rgb_img)
        return image_encodeing
    except Exception as identifier:
        logging.info('exception' + str(identifier))
        im = iosk.imread(base64String)
        image_encodeing = face_recognition.face_encodings(np.array(im))
        return image_encodeing

def insert_agent_details(userName,firstName,lastName,profilepic):
    try:
        logging.info("insert_agent_details")
        cnxn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )

        cursor = cnxn.cursor()
        cursor.fast_executemany = True

        feature = [('IsCRMEnabled',0),('IsFaxInternationalEnabled',0),('IsFaxOutEnabled',0),('IsFaxPrintEnabled',0),
        ('IsHoldVoiceCallOnChatCall',0),('IsManualInEnabled',0),('IsSecondTextChatAutoAnswer',0),('IsSMSOutEnabled',0),('IsTextChatAutoACWEnabled',0),
        ('IsTextChatAutoAnswer',0),('IsVoiceACDAutoACWEnabled',0),('IsVoiceACDAutoAnswerEnabled',0),('IsVoiceAllAutoACWEnabled',0)
        ,('IsCameraCaptureEnabled',1),('IsFaceAuthenticationEnabled',1),('IsFaceCountCheckEnabled',0),('IsFaceTouchMonitorEnabled',0),
        ('IsLocationEnabled',0),('IsScreenCaptureEnabled',0),('IsTcisConnectionEnabled',0),('IsTCIEFaceAuthenticationEnabled',0),('IsTCIEFakeFaceDetectionEnabled',1),
        ('IsTCIELivelinessDetectionEnabled',1),('IsTCIEMobileDetectionEnabled',1),('IsTCIEMultiplePersonsDetectionEnabled',1),('IsTCIENoFaceDetectionEnabled',1)]

        for row in range(len(feature)):
            feature[row] = feature[row] + (userName,)

        cursor.executemany("insert into AGT_AgentFeature(Feature,Enable,AgentId) values (?, ?, ?)", feature)
        
        cursor.execute(
            """
        INSERT INTO AGT_Agent (UserName, FirstName, LastName, AvayaLoginID, Profile, TeamID, PrimarySupervisorID, LastLoginDate, LastLoginTime,CreatedDate,CreatedTime,CreatedBy) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
            userName,
            firstName,
            lastName,
            userName,
            "A",
            1,
            1,
            "",
            "",
            "",
            "",
            "user"
        )
        cnxn.commit()
        cursor.close()

        cursor1 = cnxn.cursor() 

        cursor1.execute(
            """
        INSERT INTO AGT_Agent_Profile (AgentId, Voice, Email, SMS, TextChat, VideoChat, Role, ProfilePicture) 
        VALUES (?,?,?,?,?,?,?,?)""",
            userName,
            1,
            1,
            1,
            1,
            1,
            "Maker",
            "",
        )
        
        cnxn.commit()
        cursor1.close()
        
        cursor1 = cnxn.cursor() 
        
        cursor1.execute(
            """
        INSERT INTO CMM_User_Details (UserName, RoleID, TeamID, FirstName, LastName, LastLoginDate, LastLoginTime, CreatedDate,CreatedTime,CreatedBy) 
        VALUES (?,?,?,?,?,?,?,?,?,?)""",
            userName,
            9,
            1,
            firstName,
            lastName,
            "",
            "",
            "",
            "",
            "user"
        )

        cnxn.commit()
        cursor1.close()
        cnxn.close()
        return "Success"

    except Exception as identifier:
        logging.info("Exception: "+str(identifier))
        return "Failure"
 