############################################################################
#                                                                          #
# Description: Ensemble de méthodes pour étendre dbmain afin               #
#              d'effectuer des recherches dans la bdd                      #
#                                                                          #
############################################################################

# Infos pour les """join""":
# https:#www.freecodecamp.org/news/sql-joins-tutorial/

# Import de dbmain
from src.database import dbmain


# Extension de cette classe avec dbmain
class DbSearch(dbmain.DbMain):
    # ****************************************************************************
    # RECUPERATION DES INFORMATIONS RELATIVES AUX UTILISATEURS
    # ****************************************************************************
    # Récupération des infos d'un compte par son id
    def get_user_account_by_userid(self, userId: int) -> list:
        reqSearchUser = """
            SELECT 
                userId, email, inscriptionDate, userStatus
            FROM 
                """ + self.TABLEUSERACCOUNT + """
            WHERE 
                userId = %s"""
        self.exec_cmd(reqSearchUser, [userId])
        return self.cur.fetchall()

    # Récupération des infos d'un utilisateur par son id
    def get_user_info_by_userid(self, userId: int) -> list:
        reqGetUserInfo = """
            SELECT 
                userId, lastname, firstname, degree, 
                capability, description, phoneNumber,
                adress, zipCode, city 
            FROM 
                """ + self.TABLEUSERINFO + """ 
            WHERE 
                userId = %s"""
        self.exec_cmd(reqGetUserInfo, [userId])
        return self.cur.fetchall()

    # ****************************************************************************
    # RECUPERATION DES INFORMATIONS RELATIVES AUX PROS
    # ****************************************************************************
    def get_all_pro_by_jobid(self, jobId):
        reqSearchConsultant = """
            SELECT 
                """ + self.TABLEUSERINFO + """.userId, 
                lastname, firstname, capability 
            FROM 
                """ + self.TABLEUSERINFO + """
            INNER JOIN
                """ + self.TABLEUSERJOB + """
            ON
                """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERJOB + """.userId
            WHERE 
                jobCategoryId = %s"""
        self.exec_cmd(reqSearchConsultant, [jobId])
        return self.cur.fetchall()

    # Récupération des infos d'un pro par son nom
    def get_pro_info_by_lastname(self, lastname):
        reqSearchConsultant = """
            SELECT 
                """ + self.TABLEUSERINFO + """.userId, 
                lastname, firstname, capability 
            FROM 
                """ + self.TABLEUSERINFO + """
            INNER JOIN 
                """ + self.TABLEUSERACCOUNT + """ 
            ON 
                """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERACCOUNT + """.userId
            WHERE 
                userStatus = '1' 
            AND 
                lastname LIKE CONCAT('%', %s, '%')"""
        self.exec_cmd(reqSearchConsultant, [lastname])
        return self.cur.fetchall()

    # Récupération des emploies associés à un pro
    def get_pro_job_by_proid(self, proId):
        reqGetAll = """
            SELECT 
                """ + self.TABLEUSERJOB + """.jobCategoryId, 
                jobCategoryNameEn, jobCategoryNameFr 
            FROM 
                """ + self.TABLEUSERJOB + """
            INNER JOIN 
                """ + self.TABLEJOBCATEGORY + """
            ON 
                """ + self.TABLEUSERJOB + """.jobCategoryId = """ + self.TABLEJOBCATEGORY + """.jobCategoryId
            WHERE 
                userId = %s"""
        self.exec_cmd(reqGetAll, [proId])
        return self.cur.fetchall()

    # Récupération des notes d'un consultant par son nom
    def get_pro_note_by_proid(self, proId):
        reqCountMission = """
            SELECT 
                note 
            FROM 
                """ + self.TABLEMISSION + """
            INNER JOIN 
                """ + self.TABLEUSERINFO + """ 
            ON 
                """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId
            WHERE 
                proId = %s"""
        self.exec_cmd(reqCountMission, [proId])
        return self.cur.fetchall()

    # ****************************************************************************
    # RECUPERATION DES INFORMATIONS RELATIVES AUX EMPLOIS
    # ****************************************************************************
    # Récupérer toutes les infos de la table jobCategory
    def get_job_all(self) -> list:
        reqGetAllJobs = """
            SELECT 
                * 
            FROM 
                """ + self.TABLEJOBCATEGORY
        self.exec_cmd(reqGetAllJobs)
        return self.cur.fetchall()

    # Récupérer toutes les infos de la table jobCategory
    def get_job_by_jobid(self, jobId: int) -> list:
        reqGetJobinfo = """
            SELECT 
                * 
            FROM 
                """ + self.TABLEJOBCATEGORY + """
            WHERE
                jobCategoryId = %s"""
        self.exec_cmd(reqGetJobinfo, [jobId])
        return self.cur.fetchall()