import mysql.connector
from mysql.connector import Error
from flask_app import app
import logging
import json
import os

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def load_db_config():
    config_path = os.path.join(os.path.dirname(__file__), 'databaseConfig.json')
    
    with open(config_path) as config_file:
        return json.load(config_file)

def connect_to_database():
    try:
        # Load the database configuration
        db_config = load_db_config()

        # Establish the connection
        conn = mysql.connector.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database']
        )

        if conn.is_connected():
            return conn
    except Error as e:
        logger.exception("Error while connecting to MySQL")
        return None

def updateProgressPercentage(connection, currentProgressPercent, searchId):
    try:
        # Define the update query with placeholders for parameters
        updateQuery = "UPDATE search_requests SET SearchPercentCompleted = %s WHERE SearchID = %s;"
        # Create a cursor object
        cursor = connection.cursor()
        data= (currentProgressPercent, searchId)

        # Execute the query with the provided parameters
        cursor.execute(updateQuery, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("Update successful")
        else:
            logger.info("No rows were updated")
    
    except Error as e:
        logger.exception("Error updating progess percentage")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()

def UpdateOutputFileIdAndName(connection, file_id, file_name,searchId):
    try:
        # Define the update query with placeholders for parameters
        updateQuery = "UPDATE search_requests SET OutputFileId = %s, OutputFileName = %s WHERE SearchID = %s;"
        #logger.info(f'UQ = {updateQuery}')
        # Create a cursor object
        cursor = connection.cursor()
        data= (file_id, file_name, searchId)
        #logger.info(f'data = {data}')
        
        # Execute the query with the provided parameters
        cursor.execute(updateQuery, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("OutputFileIdAndName Update successful")
        else:
            logger.info("No rows were updated - OutputFileIdAndName")
    
    except Error as e:
        logger.exception("Error updating OutputFileIdAndName")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()


def updateRowStatus(connection, status, searchRowId):
    try:
        # Define the update query with placeholders for parameters
        updateQuery = "UPDATE search_criteria SET status = %s WHERE SearchRowId = %s;"
        # Create a cursor object
        cursor = connection.cursor()
        data= (status, searchRowId)

        # Execute the query with the provided parameters
        cursor.execute(updateQuery, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("Update successful")
        else:
            logger.info("No rows were updated")
    
    except Error as e:
        logger.exception("Error updating row status")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()

def updateExecTimeAndQuota(connection,executionTime,quotaUsed,status,searchId):
    try:
        # Define the update query with placeholders for parameters
        updateQuery = "UPDATE search_requests SET executionTime = %s, quotaUsed=%s, requestStatus=%s  WHERE searchId = %s;"
        # Create a cursor object
        cursor = connection.cursor()
        data= (executionTime, quotaUsed, status, searchId)

        # Execute the query with the provided parameters
        cursor.execute(updateQuery, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("Update successful")
        else:
            logger.info("No rows were updated")
    
    except Error as e:
        logger.exception("updateExecTimeAndQuota Error")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()
            
def selectStatus(connection, searchId):
    cursor = None
    try:
        query = "SELECT requestStatus FROM search_requests WHERE searchId=(%s);"
        data = (searchId,)
        cursor = connection.cursor()
        
        # Execute the query
        cursor.execute(query, data)
        
        # Fetch the result
        result = cursor.fetchone()
        
        if result:
            return result[0]  # Return the 'stop' value
        else:
            return None  # Return None if no result found
    
    except Error as err:
        updateExecTimeAndQuota("selectStatus Error:")
        connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()

def updateStatus(connection, searchId,status):
    cursor = None
    try:
        query = "UPDATE search_requests SET requestStatus =(%s) WHERE searchId=(%s);"
        data = (status, searchId)
        cursor = connection.cursor()
        
        # Execute the query with the provided parameters
        cursor.execute(query, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("Update successful")
        else:
            logger.info("No rows were updated")
    
    except Error as e:
        logger.exception("updateStatus Error:")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()
def deleteRecord(connection, searchId,status):
    cursor = None
    try:
        query = "UPDATE search_requests SET requestStatus =(%s), is_deleted=1 WHERE searchId=(%s);"
        data = (status, searchId)
        cursor = connection.cursor()
        
        # Execute the query with the provided parameters
        cursor.execute(query, data)
        # Commit the transaction
        connection.commit()
        # Check if any rows were affected
        if cursor.rowcount > 0:
            logger.info("Delete successful")
        else:
            logger.info("No rows were updated")
    
    except Error as e:
        logger.exception("DeleteRecord Error:")
    finally:
        # Close the cursor
        if cursor:
            cursor.close()
def insertQuerySearchRequests(connection, fileName, percentComplete,timestamp, username):
    insertQuery="INSERT INTO edu_search_db.search_requests(InputFileName,SearchPercentCompleted,timestamp,username,quotaUsed,requestStatus) VALUES(%s,%s,%s,%s,%s,%s);"
    data=( fileName, percentComplete,timestamp,username,0,'Queued')
    return executeSqlQuery(connection,insertQuery, data)

def update_search_status(connection, search_id, status):
    updateQuery = "UPDATE edu_search_db.search_requests SET SearchStatus = %s WHERE searchId = %s"
    data =  (status, search_id)
    return executeSqlQuery(connection,updateQuery, data)

def update_filename(connection, search_id, new_filename):
    updateQuery = "UPDATE edu_search_db.search_requests SET InputFileName = %s WHERE searchId = %s"
    data = (new_filename, search_id)
    return executeSqlQuery(connection,updateQuery, data)

def insertQuerySearchCriteria(connection, searchType, langauge,standard,subject,topic, subtopic,freeTextSearch,maxResults,maxVideosPerPlaylist,channelId, licenseType,videoDuration,status,searchId):
    insertQuery="INSERT INTO edu_search_db.search_criteria(SearchType,language,standard,subject,topic,subtopic,freeTextSearch,MaxResults,MaxVideosPerPlaylist,ChannelId,licenseType,videoDuration,Status,searchId) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
    data=(searchType, langauge,standard,subject,topic, subtopic,freeTextSearch, maxResults,maxVideosPerPlaylist,channelId, licenseType,videoDuration,status,searchId)
    return executeSqlQuery(connection,insertQuery, data)


        
def executeSqlQuery(connection, query, data):
    try:
        cursor = connection.cursor()
        # Execute the query
        cursor.execute(query, data)
        
        # Commit the transaction
        connection.commit()
        
        # Print the last inserted ID
        logger.info(f"Last inserted ID: {cursor.lastrowid}")
        return cursor.lastrowid
    
    except Error as err:
        logger.exception("executeSqlQueryError:")
        connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()