from function import RestFunc
restfunc = RestFunc()

class MyQuery:
    def auth_query(self, request_data):
        try:
            result = restfunc.query_select('''
            SELECT * FROM rest_user
            WHERE (rest_user.name=%s OR rest_user.email=%s) AND rest_user.password=%s;
            ''', (request_data['login_email'], request_data['login_email'], request_data['password'],), True)
            return result 
        except Exception as ex:
            return {"msg":str(ex)}, 500
    
    def add_query(self, request_data):
        try:
            proverka = restfunc.query_proverka(''' 
            SELECT * FROM rest_user
            WHERE rest_user.name=%s OR rest_user.email=%s;
            ''', (request_data['name'], request_data['email']))
            if(proverka):
                return {"msg":"User exist!"}, 400
            else:
                result = restfunc.query_insert('''
                INSERT INTO rest_user (name, birthday, lastlogintime, insys, idrole, email, password) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                RETURNING id;
                ''', \
                    (request_data['name'], request_data['reg_date'],\
                        request_data['log_time'], request_data['in_sys'],\
                            request_data['role_id'], request_data['email'], request_data['password']))
                return result, 201
        except Exception as ex:
            return {"msg":str(ex)}, 500
    
    def get_user_query(self, id):
        try:
            result = restfunc.query_select('''
            SELECT * FROM rest_user WHERE id = %s;
            ''', (id,), True)
            return result, 200
        except Exception as ex:
            return {"msg":str(ex)}, 500
    
    def get_all_user_query(self):
        try:
            result = restfunc.query_select('''
            SELECT rest_user.id, rest_user.name, rest_user.birthday, rest_user.insys, rest_role.name AS role_name, rest_user.lastlogintime, rest_user.email, rest_user.password
            FROM rest_user, rest_role
            WHERE rest_user.idrole = rest_role.id
            ORDER BY id ASC;
            ''', (), False)
            return result, 200
        except Exception as ex:
            return {"msg":str(ex)}, 500
    
    def delete_user_query(self, request_data):
        try:
            result = restfunc.query_delete_update('''
            DELETE FROM rest_user
            WHERE id = %s;
            ''', (request_data['id'],))
            if(result):
                return {"msg":"Successful removal!"}, 200
            else:
                return {"msg":"User does not exist!"}, 400
        except Exception as ex:
            return {"msg":str(ex)}, 500
    
    def update_user_query(self, request_data, id):
        try:
            result = restfunc.query_delete_update(f'''
            UPDATE rest_user
            SET name = %s
            WHERE id = %s;
            ''', (request_data['name'], id))
            if(result):
                return {"msg":"Successful update!"}, 200
            else:
                return {"msg":"User does not exist!"}, 400
        except Exception as ex:
            return {"msg":str(ex)}, 500