<?php

// ############################################################################
// #                                                                          #
// # Description: Partie commune des deux ensembles de méthodes               #
// #              pour la gestion de la BDD                                   #
// #                                                                          #
// ############################################################################

// Utilisation de PDO:
// https://www.php.net/manual/fr/pdo.connections.php
// private:   accessible uniquement dans la class
// protected: accesible depuis l'extérieur et donc via extend mais non modifiable
// public:    accessible et modifiable depuis l'extérieur
// final:     ne peut etre ré-écris (overload)

class DbMain {

    // ****************************************************************************
    // INFOS GLOBAL ET DE CONNEXION
    // ****************************************************************************
    // Pour gérer si "en production"
    private $debug = True;
    // Configuration du serveur MYSQL / MARIADB
    private $conn;
    private $host = "127.0.0.1";
    private $user = "root";
    private $pass = "";
    private $database = "alphajob";

    // ****************************************************************************
    // DECLARATION DES TABLES
    // ****************************************************************************
    // UserAccount, infos du compte nécessaire à la connection
    private $createTableUserAccount = "CREATE TABLE IF NOT EXISTS `alphajob`.`userAccount` (
        `userId`          INT          UNSIGNED NOT NULL AUTO_INCREMENT,
        `email`           VARCHAR(128) NOT NULL,
        `password`        VARCHAR(128) NOT NULL,
        `tokenAutoLogin`  VARCHAR(128) NULL DEFAULT NULL,
        `inscriptionDate` TIMESTAMP    NOT NULL,
        `userStatus`      TINYINT(1)   UNSIGNED NOT NULL,
        PRIMARY KEY (`userId`)
        )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8;";

    // Infos complémentaires des utilisateur, liée à userAccount par userId
    private $createTableUserInfo = "CREATE TABLE IF NOT EXISTS `alphajob`.`userInfo` (
        `lastname`    VARCHAR(128) NOT NULL,
        `firstname`   VARCHAR(128) NOT NULL,
        `phoneNumber` VARCHAR(10)  NOT NULL,
        `adress`      VARCHAR(128) NOT NULL,
        `zipCode`     VARCHAR(6)   NOT NULL,
        `city`        VARCHAR(128) NOT NULL,
        `degree`      VARCHAR(128) NULL DEFAULT NULL,
        `capability`  VARCHAR(256) NULL DEFAULT NULL,
        `description` VARCHAR(256) NULL DEFAULT NULL,
        `userId`      INT          UNSIGNED NOT NULL,
        PRIMARY KEY (`userId`),
        INDEX `fk_userinfo_userId_idx` (`userId` ASC) VISIBLE,
        CONSTRAINT `fk_userinfo_userId`
            FOREIGN KEY (`userId`)
            REFERENCES `alphajob`.`userAccount` (`userId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
        )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8;";

    // Liste des categories d'emploie possible
    private $createTableJobCategory = "CREATE TABLE IF NOT EXISTS `alphajob`.`jobCategory` (
        `jobCategoryId`     INT          UNSIGNED NOT NULL AUTO_INCREMENT,
        `jobCategoryNameEn` VARCHAR(128) NOT NULL,
        `jobCategoryNameFr` VARCHAR(128) NOT NULL,
        PRIMARY KEY (`jobCategoryId`)
        )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8;";

    // Assosications des emploies à un utilisateur
    private $createTableUserJob = "CREATE TABLE IF NOT EXISTS `alphajob`.`userJob` (
        `userId`        INT UNSIGNED NOT NULL,
        `jobCategoryId` INT UNSIGNED NOT NULL,
        INDEX `fk_userInfo_userId_idx` (`userId` ASC) VISIBLE,
        INDEX `fk_jobCategory_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE,
        CONSTRAINT `fk_userJob_userId`
            FOREIGN KEY (`userId`)
            REFERENCES  `alphajob`.`userAccount` (`userId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
        CONSTRAINT `fk_userJob_jobCategoryId`
            FOREIGN KEY (`jobCategoryId`)
            REFERENCES  `alphajob`.`jobCategory` (`jobCategoryId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
        )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8;";

    // Infos sur les missions effectuées, liée à userAccount par userId et consultantId
    private $createTableMission = "CREATE TABLE IF NOT EXISTS `alphajob`.`mission` (
        `missionId`         INT          UNSIGNED NOT NULL AUTO_INCREMENT,
        `date`              TIMESTAMP    NOT NULL,
        `subject`           VARCHAR(128) NULL DEFAULT NULL,
        `note`              TINYINT(1)   UNSIGNED NULL DEFAULT NULL,
        `review`            VARCHAR(256) NULL DEFAULT NULL,
        `acceptedByPro`     TIMESTAMP    NULL DEFAULT NULL,
        `validatedByClient` TIMESTAMP    NULL DEFAULT NULL,
        `validatedByPro`    TIMESTAMP    NULL DEFAULT NULL,
        `clientId`          INT          UNSIGNED NOT NULL,
        `proId`             INT          UNSIGNED NOT NULL,
        `jobCategoryId`     INT          UNSIGNED NOT NULL,
        PRIMARY KEY (`missionId`),
        INDEX `fk_mission_clientId_idx` (`clientId` ASC) VISIBLE,
        INDEX `fk_mission_proId_idx` (`proId` ASC) VISIBLE,
        INDEX `fk_mission_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE,
        CONSTRAINT `fk_mission_clientId`
            FOREIGN KEY (`clientId`)
            REFERENCES  `alphajob`.`userAccount` (`userId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
        CONSTRAINT `fk_mission_proId`
            FOREIGN KEY (`proId`)
            REFERENCES  `alphajob`.`userAccount` (`userId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
        CONSTRAINT `fk_mission_jobCategoryId`
            FOREIGN KEY (`jobCategoryId`)
            REFERENCES  `alphajob`.`jobCategory` (`jobCategoryId`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
        )
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8;";

    // Noms des tables
    protected $tableUserAccount = "userAccount";
    protected $tableUserInfo = "userInfo";
    protected $tableJobCategory = "jobCategory";
    protected $tableUserJob = "userJob";
    protected $tableMission = "mission";

    // ****************************************************************************
    // FONCTIONS PRINCIPALES
    // ****************************************************************************
    // Création de la DB si elle n'existe pas
    private function create_db() {
        $reqInitDb = "CREATE DATABASE IF NOT EXISTS ".$this->database;
        $res = $this->conn->prepare($reqInitDb);
        $res->execute();
    }
    
    // Sélection de la DB
    private function select_db() {
        $reqSelectDb = "USE ".$this->database;
        $res = $this->conn->prepare($reqSelectDb);
        $res->execute();
    }

    // Création des tables si elles n'existent pas
    private function create_table() {
        // userAccount
        $reqInitUserAccount = $this->createTableUserAccount;
        $res = $this->conn->prepare($reqInitUserAccount);
        $res->execute();
        // userInfo
        $reqInitUserInfo = $this->createTableUserInfo;
        $res = $this->conn->prepare($reqInitUserInfo);
        $res->execute();
        // jobCategory
        $reqInitJobCategory = $this->createTableJobCategory;
        $res = $this->conn->prepare($reqInitJobCategory);
        $res->execute();
        // userJob
        $reqInitUserJob = $this->createTableUserJob;
        $res = $this->conn->prepare($reqInitUserJob);
        $res->execute();
        // mission
        $reqInitMission = $this->createTableMission;
        $res = $this->conn->prepare($reqInitMission);
        $res->execute();
    }

    // Déclenche les méthodes pour créer la BDD
    private function init() {
        $this->create_db();
        $this->select_db();
        $this->create_table();
    }

    // Chiffre la data reçue
    // https://www.delftstack.com/fr/howto/php/php-string-concatenation/
    final public function crypt_pass($pass) {
        // On génère une chaine de caractère aléatoire de 16 bytes en supprimant les caractères spéciaux de base64. 
        $salt = substr(str_replace("+", ".", base64_encode(random_bytes(32))), 0, 16);
        // On configure pour avoir du SHA512 avec 10 000 tours (protection contre du bruteforce ex:hashcat)
        $hash = crypt($pass, '$6'.'$rounds=10000'.'$'.$salt.'$'); 
        return $hash;
    }

    // Prépare la requète pour mitiger (se protéger) les injections SQL et
    // initialise si nécessaire la BDD à chaque requète
    final public function exec_cmd($req, $arg) {
        $this->init();
        $res = $this->conn->prepare($req);
        $res->execute($arg);
        return $res;
    }

    // Vérifie si l'email existe dans la table userAccount de la BDD
    final public function check_exist_email($email) {
        $reqCheckEmailExist = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE email = ?)";
        $data = $this->exec_cmd($reqCheckEmailExist, array($email))->fetchAll(PDO::FETCH_NUM);
        // Retourne 1 si existe, sinon 0 
        return $data[0][0];
    }

    // Vérifie si l'id est un consultant dans la table userAccount de la BDD
    final public function check_exist_pro($id) {
        $reqCheckExistConsultant = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ? AND isPro = '1')";
        $data = $this->exec_cmd($reqCheckExistConsultant, array($id))->fetchAll(PDO::FETCH_NUM);
        // Retourne 1 si existe, sinon 0 
        return $data[0][0];
    }

    // Constructeur, fonction "auto" déclenchée à l'instanciation
    final public function __construct() {
        // Essaie de se connecter a la BDD
        if ($this->debug) {
            $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
        } else {
            $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass,  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT));
        }
    }
}

?>