from abc import abstractmethod, ABC
import psycopg2



class DatabaseInterface(ABC):

    @abstractmethod
    def create_table(self):
        pass

    @abstractmethod
    def save_user(self, email_address: str, hashed_password: str):
        pass

    @abstractmethod
    def get_password_by_email(self, email_address: str):
        pass

class Database(DatabaseInterface):
    def __init__(self):
        self.host = "localhost"
        self.port = "5432"
        self.database = "postgres"
        self.user = "postgres"
        self.password = "<PASSWORD>"
        self.connection = None
        self.create_table()

    def connect(self):
        self.connection = psycopg2.connect(host=self.host,
                                           port=self.port,
                                           database=self.database,
                                           user=self.user,
                                           password=self.password)

    def close(self):
        if self.connection:
            self.connection.close()
            self.connection = None

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def create_table(self):
        self.connect()

        try:
            cursor = self.connection.cursor()
            query = """
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                email_address TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL)
            """
            cursor.execute(query)
            self.connection.commit()
        finally:
            self.close()

    def save_user(self, email_address: str, hashed_password: str):
        try:
            with self as db:
                cursor = db.connection.cursor()
                query = """INSERT INTO users (email_address, password) VALUES (%s, %s)"""
                cursor.execute(query, (email_address, hashed_password))
                db.connection.commit()
        except psycopg2.Error as e:
            print(str(e))

    def get_password_by_email(self, email_address: str):
        with self as db:
            cursor = db.connection.cursor()
            query = """SELECT password FROM users WHERE email_address = %s"""
            parameters = (email_address,)
            cursor.execute(query, parameters)
            result = cursor.fetchone()
            if result is None:
                raise ValueError(f"No user found with e-mail address '{email_address}'.")
            return result[0]

if __name__ == '__main__':
    with Database() as my_db:
        my_db.create_table()
        my_db.save_user("test@test.com", "my_hashed_password")