from calibre.utils.config_base import prefs
import sqlite3
import os

class SQliteAdapter():
    
    def __init__(self):
        self.calibre_library = prefs['library_path'] + "/"
        self.calibre_library = self.calibre_library.replace('/', os.sep)
        # DB variables
        self.DB = None
        self.CURSOR = None
        self.CURRENT_BOOK_ID = None
    
    def instanciateDatabase(self):
        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()
                print("Database connection successful!")
                return True
            except Exception as e:
                print(f"Error connecting to the database: {e}")
                return False
        else:
            return True

    def get_best_ebook_file(self, title):
        paths = self.get_ebook_file_by_title(title)
        BOOK_FILE = self.get_book_file_by_type(paths)
        print("Book File:" + BOOK_FILE )
        return BOOK_FILE

    def get_ebook_file_by_title(self, title):
        
        # first instanciate the database
        if self.instanciateDatabase():
            paths = {}            
            ## now do a search on the title in the database
            sql_query = 'SELECT books.id, path, format, name FROM `books` LEFT JOIN `data` ON books.id = data.book WHERE books.title = "'+ title +'" AND (data.format = "PDF" OR data.format = "EPUB" OR data.format = "TXT")'
            response = self.CURSOR.execute(sql_query)
            results = response.fetchall()
            
            for result in results:
                #log.info("Search Result:")
                #log.info(result)
                
                self.CURRENT_BOOK_ID = result[0]
                path_part = result[1]                
                file_format = result[2]                
                file_name = result[3]
                
                book_path = self.calibre_library + path_part + "/" + file_name
                book_path = book_path.replace('/', os.sep)
                format_lower = file_format.lower()
                book_path = book_path + "." + format_lower
                paths[file_format] = book_path
            
            return paths
        else:
            # couldnt connect to database
            return False

    def get_book_file_by_type(self, paths):
        if "EPUB" in paths:
            return paths["EPUB"]
        elif "PDF" in paths:
            return paths["PDF"]
        else:
            return False

    def closeDB(self):
        if self.DB != None:
            self.DB.close()
            self.DB = None