    
    
class CustomFields():
    
    from calibre.utils.config_base import prefs
    # DB variables
    DB = None
    CURSOR = None
    CURRENT_BOOK_ID = None
    calibre_library = prefs['library_path'] + "/"
    calibre_library = calibre_library.replace('/', os.sep)       
        
    def instanciateDatabase(self, log):
        global DB
        global CURSOR
        
        #log.info(self.calibre_library)
        
        if self.DB == None:
            try:
                self.DB = sqlite3.connect(self.calibre_library + "metadata.db")
                self.CURSOR = self.DB.cursor()
                log.info("Database connection successful!")
                return True
            except Exception as e:
                log.info(f"Error connecting to the database: {e}")
                return False
        else:
            return True
    
    def get_custom_column_id_by_label(self, label, log):
        # first instanciate the database
        if self.instanciateDatabase(log):
            label = label.lower()
            sql_query = 'SELECT id FROM `custom_columns` WHERE label = "'+ str(label) +'"'
            #log.info(sql_query)
            response = self.CURSOR.execute(sql_query)
            result = response.fetchone()
            if result != None:
                #log.info(result[0])
                return result[0]
                
            return False
        return False
        
    def insert_custom_value_db(self, column_id, cvalue, log):
        # first instanciate the database
        if self.instanciateDatabase(log):
            sql_query = 'INSERT INTO `custom_column_' + str(column_id) + '` (value) VALUES (?)' ;
            #log.info(sql_query)
            self.CURSOR.execute(sql_query, ( str(cvalue), ) )
            self.DB.commit()
            return self.check_custom_value_exists(column_id, cvalue, log)
        
    def check_custom_value_exists(self, column_id, cvalue, log):
        ## first check if the custom value exists, if it does, then return the value id 
        # if not then create a record and return the value id
        # first instanciate the database
        if self.instanciateDatabase(log):
            sql_query = 'SELECT id FROM `custom_column_' + str(column_id) + '` WHERE value = "' + str(cvalue) + '"'
            #log.info(sql_query)
            response = self.CURSOR.execute(sql_query)
            value_id = response.fetchone()
            if value_id == None:
                ## doesn't exist lets add value to database
                log.info("Inserting:" + cvalue)
                self.insert_custom_value_db(column_id, cvalue, log)
            else:
                #log.info("Value ID:")
                #log.info(value_id[0])
                return value_id[0]
                
    def insert_value_to_book_link_db(self, column_id, value_id, log):
        # first instanciate the database
        if self.instanciateDatabase(log):
            sql_query = 'INSERT INTO `books_custom_column_' + str(column_id) + '_link` (book, value) VALUES (?, ?)'
            #log.info(sql_query)
            #log.info("Book ID:")
            #log.info(self.CURRENT_BOOK_ID)
            self.CURSOR.execute(sql_query, ( self.CURRENT_BOOK_ID, value_id ) )
            self.DB.commit()
            return 
    
    def add_custom_value_link_to_book(self, column_id, value_id, log):
        ## first check if the column id and value id already match, if they do, it already exists.
        ## if it doesnt exist, we add a record
        # first instanciate the database
        if self.instanciateDatabase(log):
            sql_query = 'SELECT id FROM `books_custom_column_'+ str(column_id) +'_link` WHERE book = "' + str(self.CURRENT_BOOK_ID) + '" AND value = "' + str(value_id) + '"'
            response = self.CURSOR.execute(sql_query)
            if response.fetchone() is None:
                ## doesnt exist, lets add it
                self.insert_value_to_book_link_db(column_id, value_id, log)
            else:
                return
        
    def update_custom_value_in_db(self, label, value, log):
        
        column_id = self.get_custom_column_id_by_label(label, log)
        if column_id != False and column_id != None:
            value_id = self.check_custom_value_exists(column_id, value, log)
            #log.info(value_id)
            if value_id != False and value_id != None and column_id != False and column_id != None:
                return self.add_custom_value_link_to_book(column_id, value_id, log)
                
                
    def run_through_custom_fields(self, response, book_id):
        global CURRENT_BOOK_ID = book_id
        #run through any custom returned fields and add them to the database
        ibk = ['title', 'author', 'isbn', 'publisher', 'summary', 'genre']
        additional_keys = set(response) - set(ibk)
        for key in additional_keys:
            if self.response_check(response[key]):
                self.update_custom_value_in_db(key, str(response[key]), log)