import dbconfig as conf
import mysql.connector
import queries

class Moviedao:
    '''
        class for interacting with movie database objects,
        initializes using connection to mysql server with details stored in dbconfig conf variable
        function:
            __initialize_connect_db() - function immediately called by __init__ to set up to database with pooling (name mangled as should only be called within class)
            get_connect_db - function called in each function for interacting with database, leverages connection pool, returns connection object
            get_all_movies() - queries database movies table and returns all column names and rows
            add_movie() - inserts new row to movies table, if ID doesn't already exist, returns rows added
            update_movie() - updates an existing row in movies table, returns rows affected
            delete_movie() - deletes row from movie table
            get_ratings() - queries ratings table, returning all columns
            __repr__ - prints summary of the class and it's connection string except password
    '''
    #initial connection function
    def __initialize_connect_db(self):
        conn = mysql.connector.connect(
            host=conf.mysql['host'],
            user=conf.mysql['user'],
            password=conf.mysql['password'],
            database=conf.mysql['database'],
            pool_name=conf.mysql['default_pool_name'],
            pool_size=conf.mysql['default_pool_size'],
            port=conf.mysql['port']
        )
        return conn
    #connection function used in other class functions
    def get_connect_db(self):
        conn = mysql.connector.connect(
            pool_name=conf.mysql['default_pool_name'],
            pool_size=conf.mysql['default_pool_size']
        )
        return conn

    def __init__(self):
        #create connection and immediately close, connection pool will re-reference this later
        conn = self.__initialize_connect_db()
        conn.close() #close conn after creating it, will use get_connect_db for future connections, taking advantage of connection pool

    #Gets all movies from movies table
    def get_all_movies(self):
        with self.get_connect_db() as conn:
            cursor = conn.cursor()
            cursor.execute(queries.select_all_movie_plus_rating)
            results = cursor.fetchall()
            column_names = [i[0] for i in cursor.description]
            row_results = []
            for row in results:
                row_dict = dict(zip(column_names,row)) #zip column names with row data, put in dict
                row_results.append(row_dict) #append dict to list
            return row_results #return list of dict's

    #Adds single movie to movies table
    def add_movie(self, values):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.insert_movie, values)
                #print(cursor.statement)
                conn.commit()
                rowcount = cursor.rowcount #rowcount == 1 means that single row has been added
                return rowcount
            except mysql.connector.IntegrityError as e:
                print("Hit an integrity error, meaning a movieID was trying to be used more than once")
                print(f"Detailed Error Message:\n{e}")
                return "IntegrityError"
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                return "SomeOtherError"
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #Update existing movie in Movies table
    def update_movie(self, values):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.update_movie, values)
                conn.commit() #save result back to database
                rowcount = cursor.rowcount
                #print(cursor.statement)
                return rowcount
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #Update existing movie in Movies table
    def delete_movie(self, movie_id):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.delete_movie, [movie_id])
                conn.commit() #save result back to database
                rowcount = cursor.rowcount
                return rowcount
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                #print(cursor.statement)
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #get all rows from ratings table
    def get_ratings(self):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.select_all_ratings)
                results = cursor.fetchall()
                column_names = [i[0] for i in cursor.description]
                row_results = []
                for row in results:
                    row_dict = dict(zip(column_names,row)) #zip column names with row data, put in dict
                    row_results.append(row_dict) #append dict to list
                return row_results #return list of dict's
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                #print(cursor.statement)
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #__repr__ displays connection information without exposing password
    def __repr__(self):
        repr_str = "Database access object for querying, inserting, updating and deleting tables relating to movies"
        repr_str += f"\nConnecting to Host: {conf.mysql['host']}\nWith user: {conf.mysql['user']}\n To Database: {conf.mysql['database']}"
        return repr_str

#movie_dao = Moviedao()
import dbconfig as conf
import mysql.connector
import queries

class Moviedao:
    '''
        class for interacting with movie database objects,
        initializes using connection to mysql server with details stored in dbconfig conf variable
        function:
            __initialize_connect_db() - function immediately called by __init__ to set up to database with pooling (name mangled as should only be called within class)
            get_connect_db - function called in each function for interacting with database, leverages connection pool, returns connection object
            get_all_movies() - queries database movies table and returns all column names and rows
            add_movie() - inserts new row to movies table, if ID doesn't already exist, returns rows added
            update_movie() - updates an existing row in movies table, returns rows affected
            delete_movie() - deletes row from movie table
            get_ratings() - queries ratings table, returning all columns
            __repr__ - prints summary of the class and it's connection string except password
    '''
    #initial connection function
    def __initialize_connect_db(self):
        conn = mysql.connector.connect(
            host=conf.mysql['host'],
            user=conf.mysql['user'],
            password=conf.mysql['password'],
            database=conf.mysql['database'],
            pool_name=conf.mysql['default_pool_name'],
            pool_size=conf.mysql['default_pool_size'],
            port=conf.mysql['port']
        )
        return conn
    #connection function used in other class functions
    def get_connect_db(self):
        conn = mysql.connector.connect(
            pool_name=conf.mysql['default_pool_name'],
            pool_size=conf.mysql['default_pool_size']
        )
        return conn

    def __init__(self):
        #create connection and immediately close, connection pool will re-reference this later
        conn = self.__initialize_connect_db()
        conn.close() #close conn after creating it, will use get_connect_db for future connections, taking advantage of connection pool

    #Gets all movies from movies table
    def get_all_movies(self):
        with self.get_connect_db() as conn:
            cursor = conn.cursor()
            cursor.execute(queries.select_all_movie_plus_rating)
            results = cursor.fetchall()
            column_names = [i[0] for i in cursor.description]
            row_results = []
            for row in results:
                row_dict = dict(zip(column_names,row)) #zip column names with row data, put in dict
                row_results.append(row_dict) #append dict to list
            return row_results #return list of dict's

    #Adds single movie to movies table
    def add_movie(self, values):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.insert_movie, values)
                #print(cursor.statement)
                conn.commit()
                rowcount = cursor.rowcount #rowcount == 1 means that single row has been added
                return rowcount
            except mysql.connector.IntegrityError as e:
                print("Hit an integrity error, meaning a movieID was trying to be used more than once")
                print(f"Detailed Error Message:\n{e}")
                return "IntegrityError"
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                return "SomeOtherError"
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #Update existing movie in Movies table
    def update_movie(self, values):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.update_movie, values)
                conn.commit() #save result back to database
                rowcount = cursor.rowcount
                #print(cursor.statement)
                return rowcount
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #Update existing movie in Movies table
    def delete_movie(self, movie_id):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.delete_movie, [movie_id])
                conn.commit() #save result back to database
                rowcount = cursor.rowcount
                return rowcount
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                #print(cursor.statement)
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #get all rows from ratings table
    def get_ratings(self):
        with self.get_connect_db() as conn:
            try:
                cursor = conn.cursor()
                cursor.execute(queries.select_all_ratings)
                results = cursor.fetchall()
                column_names = [i[0] for i in cursor.description]
                row_results = []
                for row in results:
                    row_dict = dict(zip(column_names,row)) #zip column names with row data, put in dict
                    row_results.append(row_dict) #append dict to list
                return row_results #return list of dict's
            except mysql.connector.Error as e:
                print(f"Hit the follow mysql error: {e}")
                #print(cursor.statement)
                return str(e)
            except Exception as e:
                print(f"Hit some non-specific error: {e}")
                return str(e)

    #__repr__ displays connection information without exposing password
    def __repr__(self):
        repr_str = "Database access object for querying, inserting, updating and deleting tables relating to movies"
        repr_str += f"\nConnecting to Host: {conf.mysql['host']}\nWith user: {conf.mysql['user']}\n To Database: {conf.mysql['database']}"
        return repr_str

#movie_dao = Moviedao()
class for interacting with movie database objects,         initializes using connection to mysql server with details stored in dbconfig conf variable         function:             __initialize_connect_db() - function immediately called by __init__ to set up to database with pooling (name mangled as should only be called within class)             get_connect_db - function called in each function for interacting with database, leverages connection pool, returns connection object             get_all_movies() - queries database movies table and returns all column names and rows             add_movie() - inserts new row to movies table, if ID doesn't already exist, returns rows added             update_movie() - updates an existing row in movies table, returns rows affected             delete_movie() - deletes row from movie table             get_ratings() - queries ratings table, returning all columns             __repr__ - prints summary of the class and it's connection string except password #initial connection function #connection function used in other class functions #create connection and immediately close, connection pool will re-reference this later #close conn after creating it, will use get_connect_db for future connections, taking advantage of connection pool #Gets all movies from movies table #zip column names with row data, put in dict #append dict to list #return list of dict's #Adds single movie to movies table #print(cursor.statement) #rowcount == 1 means that single row has been added #Update existing movie in Movies table #save result back to database #print(cursor.statement) #Update existing movie in Movies table #save result back to database #print(cursor.statement) #get all rows from ratings table #zip column names with row data, put in dict #append dict to list #return list of dict's #print(cursor.statement) #__repr__ displays connection information without exposing password #movie_dao = Moviedao()