import mysql.connector
from config import MOVIEO_DATABASE

class MovieoDatabase:
    def __init__(self):
        self.connection   = mysql.connector.connect(**MOVIEO_DATABASE)
        self.db = self.connection.cursor(buffered=True)

    def __enter__(self):
        return self

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

    def get_title(self, column: str, id, get: str = '*', order: str = 'ORDER BY id ASC'):
        sql = "SELECT {} FROM titles WHERE {}=%s {}".format(get,column,order)
        self.db.execute(sql, (id,))
        return self.db.fetchall()

    def add_title(self, imdb_id: str):
        sql = "INSERT INTO titles (imdb_id) VALUES (%s)"
        self.db.execute(sql, (imdb_id,))
        last_id = self.db.lastrowid
        self.connection.commit()
        sql = "INSERT INTO titles_rating (id) VALUES (%s)"
        self.db.execute(sql, (last_id,))
        self.connection.commit()
        sql = "INSERT INTO titles_views (id) VALUES (%s)"
        self.db.execute(sql, (last_id,))
        self.connection.commit()
        return last_id

    def update_title(self, title_id: int, column: str, entry):
        sql = "UPDATE titles SET {} = %s WHERE id = %s".format(column)
        self.db.execute(sql, (entry, title_id,))
        self.connection.commit()
        return

    def delete_title(self,title_id):
        sql = "DELETE FROM `titles` WHERE id = %s"
        self.db.execute(sql, (title_id,))
        self.connection.commit()
        sql = "DELETE FROM `titles_rating` WHERE id = %s"
        self.db.execute(sql, (title_id,))
        self.connection.commit()
        sql = "DELETE FROM `titles_views` WHERE id = %s"
        self.db.execute(sql, (title_id,))
        self.connection.commit()
        sql = "DELETE FROM `files` WHERE title_id = %s"
        self.db.execute(sql, (title_id,))
        self.connection.commit()
        return

    def get_files(self, column: str, id, get: str = '*', order: str = 'ORDER BY id ASC'):
        sql = "SELECT {} FROM files WHERE {}=%s {}".format(get,column,order)
        self.db.execute(sql, (id,))
        return self.db.fetchall()

    def get_users_requests(self, column: str, id: int,get = '*', order = 'ORDER BY id ASC'):
        sql = f"SELECT {get} FROM users_requests WHERE {column}=%s {order}"
        self.db.execute(sql, (id,))
        return self.db.fetchall()

    def get_admins_requests(self, column: str, id: int,get = '*', order = 'ORDER BY id ASC'):
        sql = f"SELECT {get} FROM admins_requests WHERE {column}=%s {order}"
        self.db.execute(sql, (id,))
        return self.db.fetchall()

    def update_admins_requests(self, request_id: int, column: str, entry):
        sql = "UPDATE admins_requests SET {} = %s WHERE id = %s".format(column)
        self.db.execute(sql, (entry, request_id,))
        self.connection.commit()
        return

    def update_users_requests(self, request_id: int, column: str, entry):
        sql = "UPDATE users_requests SET {} = %s WHERE id = %s".format(column)
        self.db.execute(sql, (entry, request_id,))
        self.connection.commit()
        return

    def get_30nama_account(self, column: str, id, get: str = '*', order: str = 'ORDER BY id ASC'):
        sql = "SELECT {} FROM `30nama_accounts` WHERE {}=%s {}".format(get,column,order)
        self.db.execute(sql, (id,))
        return self.db.fetchall()