import bcrypt
import psycopg2
import psycopg2.extras

##Database Exceptional Class##
class invalidQueryException(Exception): pass

##Basic Connection Class for Database##
class database:
    def __init__(self):
        self.__HOST = "myawsdatabase.c7mfxxgrjakk.ap-southeast-1.rds.amazonaws.com"
        #self.__HOST = "localhost" ##**For Local Database**##
        self.__DATABASE = "crazypetData"
        self.__DBUSER = "app"
        self.__DBPASS = "2rG3RSfTZ1"
        try:
            ##Opening Database Connection##
            self.connection = psycopg2.connect(host=self.__HOST,database=self.__DATABASE, user=self.__DBUSER, password=self.__DBPASS)
            self.query = self.connection.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
        ##Cannot Connect to Database##
        except psycopg2.OperationalError:
            raise invalidQueryException("Database Connection Error!")

    def disconnect(self):
        self.connection.close()

##Database Class for Login-Related Functions##
class databaseLogin(database):

    ##Basic Login use to Login User##
    def userLogin(self, username, password):
        password = bytes(password, encoding="ascii")
        SQL = "SELECT password, user_id, user_type, username, status FROM \"GMan\".user_login WHERE username = %s"
        DATA = (username,)
        self.query.execute(SQL,DATA)
        resultset = self.query.fetchone()
        if(resultset == None):
            raise invalidQueryException("Either Username or Password is Incorrect")
        hashed = bytes(resultset.password, encoding="ascii")
        if (bcrypt.hashpw(password, hashed) == hashed):
            return True, resultset.user_id, resultset.user_type , resultset.username, resultset.status
        else:
            raise invalidQueryException("Either Username or Password is Incorrect")

    ##Create New Login for new Users##
    def createLogin(self,userid,username,email,password = "DEFAULTPASS123456",userStatus= 0, userType = 0):
        passwd_enc = bytes(password,encoding ="ascii")
        passwd_hashed = bcrypt.hashpw(passwd_enc, bcrypt.gensalt(14))
        passwd_hashed_dec = passwd_hashed.decode(encoding = "ascii")
        SQL = "INSERT INTO \"GMan\".user_login(user_id, username, password, email, status, user_type)VALUES(%s,%s,%s,%s,%s,%s)"
        DATA = (userid, username, passwd_hashed_dec, email, str(userStatus), str(userType))
        try:
            self.query.execute(SQL, DATA)
            self.connection.commit()
            SQL = "INSERT INTO \"GMan\".address (user_id, \"houseNumber\", street, \"subDistrict\", district, province, \"zipCode\") VALUES(%s, 'PLEASE UPDATE DATA', ' ', ' ', ' ', ' ', ' ')"
            DATA = (userid,)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            return (1,1)
        except psycopg2.IntegrityError as e:
            return ("EXISTS",0)

    ##Edit Login Data##
    def editLogin(self, username, email, userStatus=0):
        SQL = "UPDATE \"GMan\".user_login SET email=%s,status=%s WHERE username=%s"
        DATA = (email, str(userStatus),username)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Edit Login Password##
    def changePassword(self, username, oldPassword, newPassword):
        password = bytes(oldPassword, encoding="ascii")
        SQL = "SELECT password FROM \"GMan\".user_login WHERE username = %s"
        DATA = (username,)
        self.query.execute(SQL,DATA)
        resultset = self.query.fetchone()
        if(resultset == None):
            raise invalidQueryException("Either Username or Password is Incorrect")
        hashed = bytes(resultset.password, encoding="ascii")
        if (bcrypt.hashpw(password, hashed) == hashed):
            passwd_enc = bytes(newPassword,encoding ="ascii")
            passwd_hashed = bcrypt.hashpw(passwd_enc, bcrypt.gensalt(14))
            passwd_hashed_dec = passwd_hashed.decode(encoding = "ascii")
            SQL = "UPDATE \"GMan\".user_login SET password=%s WHERE username = %s"
            DATA = (passwd_hashed_dec,username)
            self.query.execute(SQL, DATA)
            self.connection.commit()
        else:
            raise invalidQueryException("Either Username or Password is Incorrect")

    ##Reset User Password##
    def resetPassword(self, user_id, username, email, new_pw):
        passwd_enc = bytes(new_pw, encoding="ascii")
        passwd_hashed = bcrypt.hashpw(passwd_enc, bcrypt.gensalt(14))
        passwd_hashed_dec = passwd_hashed.decode(encoding="ascii")
        SQL = "UPDATE \"GMan\".user_login SET password=%s WHERE username = %s AND user_id = %s AND email = %s"
        DATA = (passwd_hashed_dec, username, user_id, email)
        self.query.execute(SQL, DATA)
        self.connection.commit()

    ##Block and Un-Block Users##
    def blockUser(self, command, status, userid):
        if (command == "BLOCK" and status == 0):
            SQL = "UPDATE \"GMan\".user_login SET status=1 WHERE user_id=%s"
            DATA = (userid,)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            SQL = "UPDATE \"GMan\".student SET status=6 WHERE user_id=%s"
        if (command == "BLOCK" and status == 1):
            SQL = "UPDATE \"GMan\".user_login SET status=1 WHERE user_id=%s"
            DATA = (userid,)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            SQL = "UPDATE \"GMan\".professor SET status=6 WHERE user_id=%s"
        if (command == "UNBLOCK" and status == 0):
            SQL = "UPDATE \"GMan\".user_login SET status=0 WHERE user_id=%s"
            DATA = (userid,)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            SQL = "UPDATE \"GMan\".student SET status=0 WHERE user_id=%s"
        if (command == "UNBLOCK" and status == 1):
            SQL = "UPDATE \"GMan\".user_login SET status=0 WHERE user_id=%s"
            DATA = (userid,)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            SQL = "UPDATE \"GMan\".professor SET status=0 WHERE user_id=%s"
        DATA = (userid,)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Suspend and Un-Suspend Users##
    def suspendUser(self, command, type, userid):
        if(command == "SUSP" and type == "STUDENT"):
            SQL = "UPDATE \"GMan\".student SET status=4 WHERE user_id=%s"
        if (command == "SUSP" and type == "PROFESSOR"):
            SQL = "UPDATE \"GMan\".professor SET status=4 WHERE user_id=%s"
        if (command == "UNSUSP" and type == "STUDENT"):
            SQL = "UPDATE \"GMan\".student SET status=0 WHERE user_id=%s"
        if (command == "UNSUSP" and type == "PROFESSOR"):
            SQL = "UPDATE \"GMan\".professor SET status=0 WHERE user_id=%s"
        DATA = (userid,)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Retire Users##
    def retireUser(self, type, userid):
        if(type == "STUDENT"):
            SQL = "UPDATE \"GMan\".student SET status=5 WHERE user_id=%s"
        if (type == "PROFESSOR"):
            SQL = "UPDATE \"GMan\".professor SET status=5 WHERE user_id=%s"
        DATA = (userid,)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Delete Login Data##
    def deleteLogin(self,userid,username):
        SQL = "DELETE FROM \"GMan\".user_login WHERE user_id=%s AND username =%s";
        DATA = (userid, username)
        self.query.execute(SQL, DATA)
        self.connection.commit()

    ##Get User Information##
    def getInformationUser(self, userid):
        SQL = "SELECT user_type, username, user_id FROM \"GMan\".user_login WHERE user_id = %s"
        DATA = (userid,)
        self.query.execute(SQL, DATA)
        resultset = self.query.fetchone()
        if(resultset is not None):
            if(resultset.user_type == 0):
                SQL = "SELECT * FROM \"GMan\".student WHERE user_id = %s"
                DATA = (userid,)
                self.query.execute(SQL, DATA)
                resultsetData = self.query.fetchone()
            if (resultset.user_type == 1):
                SQL = "SELECT * FROM \"GMan\".professor WHERE user_id = %s"
                DATA = (userid,)
                self.query.execute(SQL, DATA)
                resultsetData = self.query.fetchone()
            if (resultset.user_type == 2):
                SQL = "SELECT * FROM \"GMan\".admin WHERE user_id = %s"
                DATA = (userid,)
                self.query.execute(SQL, DATA)
                resultsetData = self.query.fetchone()
            return (resultset,resultsetData, resultset.user_type)
        return(None, None,None)

##Database Class for User-Related Functions##
class databaseUser(database):

    ##Get User Information##
    def getInfo(self, inp_data):
        if(inp_data[2] == 0):
            SQL = "SELECT * FROM \"GMan\".student WHERE user_id =%s"
            DATA = (inp_data[1],)
            self.query.execute(SQL, DATA)
            resultset = self.query.fetchone()
            return resultset

        if (inp_data[2] == 1):
            SQL = "SELECT * FROM \"GMan\".professor WHERE user_id =%s"
            DATA = (inp_data[1],)
            self.query.execute(SQL, DATA)
            resultset = self.query.fetchone()
            return resultset
        if (inp_data[2] == 2):
            SQL = "SELECT * FROM \"GMan\".admin WHERE user_id =%s"
            DATA = (inp_data[1],)
            self.query.execute(SQL, DATA)
            resultset = self.query.fetchone()
            return resultset

    ##Get User Address##
    def getAddress(self, inp_data):
        SQL = "SELECT * FROM \"GMan\".address WHERE user_id =%s"
        DATA = (inp_data[1],)
        self.query.execute(SQL, DATA)
        resultset = self.query.fetchone()
        return resultset

    ##Get Faculty Information##
    def getFaculty(self, inp_data):
        SQL = "SELECT * FROM \"GMan\".faculty WHERE \"facultyID\" =%s"
        DATA = (inp_data,)
        self.query.execute(SQL, DATA)
        resultset = self.query.fetchone()
        return resultset

    ##Get Major Information##
    def getMajor(self, inp_data):
        SQL = "SELECT * FROM \"GMan\".majors WHERE \"majorID\" =%s"
        DATA = (inp_data,)
        self.query.execute(SQL, DATA)
        resultset = self.query.fetchone()
        return resultset

    ##Update User Address##
    def updateAddress(self, user_id, homeNum, street, sDistrict, district, province, zip):
        SQL = "UPDATE \"GMan\".address SET \"houseNumber\"=%s, street=%s, \"subDistrict\"=%s, district=%s, province=%s, \"zipCode\"=%s WHERE user_id=%s"
        DATA = (homeNum, street, sDistrict, district, province, zip, user_id)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Update User Profile Picture as blobs##
    def editProfilePicture(self, blob, user_id):
        SQL = "UPDATE \"GMan\".user_login SET picture=%s WHERE user_id=%s"
        DATA = (blob, user_id)
        self.query.execute(SQL, DATA)
        self.connection.commit()
        return 1

    ##Get User Profile Picture##
    def getProfilePicture(self, user_id):
        SQL = "SELECT picture FROM \"GMan\".user_login WHERE user_id =%s"
        DATA = (user_id,)
        self.query.execute(SQL, DATA)
        resultset = self.query.fetchone()
        return resultset, 1

    ##Create New User Student##
    def createStudent(self, userid, name, surname, email, faculty, major):
        SQL = "SELECT term FROM \"GMan\".student"
        self.query.execute(SQL)
        resultset = self.query.fetchone()
        term = resultset.term
        try:
            SQL = "INSERT INTO \"GMan\".student (user_id, \"name\", surname, email, \"year\", status, gpa, \"facultyID\", \"majorID\", term) \
                  VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            DATA = (userid, name, surname, email, 1, 0, 0.00, faculty, major, int(term))
            print(DATA)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            return (1,1)
        except psycopg2.IntegrityError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)
        except psycopg2.DataError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)

    ##Create New User Professor##
    def createProfessor(self, userid, name, surname, email, facultyID):
        try:
            SQL = "INSERT INTO \"GMan\".professor (user_id, \"name\", surname, email, \"position\", status, \"facultyID\")\
                   VALUES(%s, %s,%s, %s, %s,%s, %s)"
            DATA = (userid, name, surname, email, 0, 0, facultyID)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            return (1,1)
        except psycopg2.IntegrityError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)
        except psycopg2.DataError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)

    ##Create New User Administrator##
    def createAdmin(self, userid, name, surname, email):
        try:
            SQL = "INSERT INTO \"GMan\".admin (user_id, \"name\", surname, email, \"position\", status)\
                    VALUES(%s, %s,%s, %s, %s,%s)"
            DATA = (userid, name, surname, email, 0, 0)
            self.query.execute(SQL, DATA)
            self.connection.commit()
            return (1, 1)
        except psycopg2.IntegrityError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)
        except psycopg2.DataError as e:
            print(e.pgcode, e.pgerror)
            return (str(e.pgcode), e.pgerror)