<?php
// ****************************************************************************
// 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
require_once(dirname( __FILE__ )."/dbmain.php");
// Extension de cette classe avec dbmain
class DbSearch extends DbMain {
    // Récupérer toutes les infos de la table userAccount
    final public function search_all() {
        $reqGetAll = "SELECT * FROM ".$this->tableUserAccount;
        $data = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    // Récupération des infos d'un compte par son id
    final public function search_user_by_id($id) {
        $reqSearchUser = "SELECT userId, email, inscriptionDate, isClient, isPro, isAdmin
            FROM ".$this->tableUserAccount." WHERE userId = ?";
        $data = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC);
        return $data;
    }

    // Récupération des infos d'un utilisateur par son id
    final public function get_user_info_by_id($id) {
        $reqGetUserInfo = "SELECT lastname, firstname, job, degree, 
                                capability, description, phoneNumber,
                                adress, zipCode, city 
            FROM ".$this->tableUserInfo." WHERE userId = ?";
        $data = $this->exec_cmd($reqGetUserInfo, array($id))->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    // Récupération des infos d'un consultant par son nom
    final public function search_consultant_by_name($research) {
        $reqSearchConsultant = "SELECT userId, lastname, firstname, job, capability FROM ".$this->tableUserInfo."
            INNER JOIN ".$this->tableUserAccount." 
            ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".userId
            WHERE isPro = '1' AND lastname LIKE CONCAT('%', ?, '%')";
        $data = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }
    
    // Récupération des notes d'un consultant par son nom
    final public function get_consultant_note_by_name($name) {
        $reqCountMission = "SELECT note FROM ".$this->tableMission."
            INNER JOIN ".$this->tableUserInfo." 
            ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId
            WHERE lastname = ?";
        $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    // Récupérations des missions d'un consultant par son id
    final public function get_all_mission_by_id($id) {
        $reqGetAllMission = "SELECT subject, note, review, lastname FROM ".$this->tableMission."
            LEFT JOIN ".$this->tableUserInfo." 
            ON ".$this->tableMission.".missionId = ".$this->tableUserInfo.".userId
            WHERE proId = ?";
        $data = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    // Compte le nombre de mission d'un consultant par son nom
    final public function count_consultant_mission_by_name($name) {
        $reqCountMission = "SELECT COUNT(*) FROM ".$this->tableMission."
            INNER JOIN ".$this->tableUserInfo." 
            ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId
            WHERE lastname = ?";
        $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }
}
?>