import threading

class Database:
    __instance = None

    @staticmethod
    def get_instance():
        """Static method to get the singleton instance of the class"""
        if Database.__instance is None:
            Database()
        return Database.__instance

    def __init__(self):
        dbpath = 'temp\db.sqlite'

        """Private constructor to create the singleton instance of the class"""
        if Database.__instance is not None:
            raise Exception("Singleton instance already exists. Use get_instance() to get the instance.")
        else:
            self.connection = sqlite3.connect(dbpath, check_same_thread=False)
            Database.__instance = self

        # Create a lock object to ensure thread safety
        self.lock = threading.Lock()

        # Create the ChannelName table if it doesn't exist
        with self.lock:
            self.connection.execute('''
                CREATE TABLE IF NOT EXISTS ChannelName (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL UNIQUE
                )
            ''')

        # Create the Settings table if it doesn't exist
        with self.lock:
            self.connection.execute('''
                CREATE TABLE IF NOT EXISTS Settings (
                    id INTEGER PRIMARY KEY,
                    image_path TEXT NOT NULL,
                    font_path TEXT NOT NULL,
                    highlight_color TEXT NOT NULL,
                    text_color TEXT NOT NULL,
                    channel_id INTEGER UNIQUE,
                    FOREIGN KEY (channel_id) REFERENCES ChannelName(id)
                )
            ''')

        # Commit the changes
        with self.lock:
            self.connection.commit()

    def close(self):
        # Close the connection
        with self.lock:
            self.connection.close()

    def LoadData(self): 
        # Retrieve data from database
        with self.lock:
            data = self.connection.execute('''
                SELECT cn.id, cn.name, s.image_path, s.font_path, s.highlight_color, s.text_color
                FROM ChannelName cn
                JOIN Settings s
                ON cn.id = s.channel_id
            ''').fetchall()

        return data