import mysql.connector
from mysql.connector import Error

def connect_to_database():
    try:
        # Establish the connection
        conn = mysql.connector.connect(
#            host="localhost",
#            user="root",
#            password="root",  # Replace with your actual MySQL root password
#            database="edu_search_db",
#            auth_plugin='mysql_native_password'
	     host="localhost",
             user="root",
             password="Gg+n)hC$hP",
             database="edu_search_db"
        )

        if conn.is_connected():
            return conn
    except Error as e:
        print("Error while connecting to MySQL", e)
        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:
            print("Update successful")
        else:
            print("No rows were updated")
    
    except Error as e:
        print("Error: {}".format(e))
    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:
            print("Update successful")
        else:
            print("No rows were updated")
    
    except Error as e:
        print("Error: {}".format(e))
    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:
            print("Update successful")
        else:
            print("No rows were updated")
    
    except Error as e:
        print("Error: {}".format(e))
    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:
        print("Error: {}".format(err))
        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:
            print("Update successful")
        else:
            print("No rows were updated")
    
    except Error as e:
        print("Error: {}".format(e))
    finally:
        # Close the cursor
        if cursor:
            cursor.close()
def deleteRecord(connection, searchId, status):
    cursor = None
    try:
        query = "UPDATE search_requests SET requestStatus = %s WHERE searchId = %s;"
        data = (status, searchId)
        cursor = connection.cursor()
        cursor.execute(query, data)
        connection.commit()
        if cursor.rowcount > 0:
            print("Delete successful")
        else:
            print("No rows were updated")
    except Error as e:
        print("Error: {}".format(e))
    finally:
        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 search_criteria(SearchType,Language,Standard,Subject,Topic,SubTopic,FreeTextSearch,MaxResults,MaxResultsPerPlaylists,ChannelId,LicenceType,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
        print("Last inserted ID:", cursor.lastrowid)
        return cursor.lastrowid
    
    except Error as err:
        print("Error: {}".format(err))
        connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()