import logging
from typing import Any
from db.dbconnections import _DBConnection as DBConnection
import psycopg2

log: logging.Logger = logging.getLogger('development')


class DBTransactions:
    """
    Utility class that facilitates transations 
    to/from the database.cThe business logic for
    transactions should not be handled during the 
    data modification or commit phases, rather, it
    should be handled separately such that if the 
    underlying DBMS changes, or if any dependencies 
    change, updates should only have to be made in 
    this class, versus going through the entire 
    codebase and changnig individual lines.

    This should not be used directly, instead, it should be used 
    within the scope of each data object.
    """

    def get_data(self, table: str) -> list[tuple]:
        connection: psycopg2.connection = DBConnection().con
        cursor: psycopg2.cursor = connection.cursor()
        log.info(f"Pulling data from {table}")
        try:
            cursor.execute(f"SELECT * FROM {table}")
        except Exception as e:
            raise e
        else:
            return cursor.fetchall()

    def add_data(self, table: str, data: tuple) -> None:
        connection: psycopg2.connection = DBConnection().con
        cursor: psycopg2.cursor = connection.cursor()
        log.info(f"Adding data to {table}")
        try:
            cursor.execute(f"INSERT INTO {table} VALUES {data}")
            connection.commit()
        except Exception as e:
            raise e
        else:
            log.debug(f"Added data to {table}")

    def remove_data(self, table: str, property: str, id: Any) -> None:
        connection: psycopg2.connection = DBConnection().con
        cursor: psycopg2.cursor = connection.cursor()
        log.info(f"Removing data from {table}")
        try:
            cursor.execute(f"DELETE FROM {table} WHERE {property}='{id}';")
            connection.commit()
        except Exception as e:
            raise e
        else:
            log.debug(f"Removed data from {table} from id: {id}")

    def bulk_add_data(self, table: str, data_list: list[Any]):
        connection: psycopg2.connection = DBConnection().con
        cursor: psycopg2.cursor = connection.cursor()
        log.info(f"Bulk adding data to {table}")
        for data in data_list:
            try:
                cursor.execute(f"INSERT INTO {table} VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )", data)
                connection.commit()
            except Exception as e:
                raise e
        log.debug(f"Bulk added data to {table}")

    def update_data(self, table: str, name_of_pk: str,
    id_to_update: int, data: Any) -> None:
        connection: psycopg2.connection = DBConnection().con
        cursor: psycopg2.cursor = connection.cursor()
        log.info(f"Updating data from {table}")
        try:
            cursor.execute(f"""UPDATE {table} SET 
                           {data} WHERE {name_of_pk}='{id_to_update}'""")
            connection.commit()
        except Exception as e:
            raise e
        else:
            log.debug(f"Updated data from {table} for ID: {id_to_update}")
