<?php

class Class_newFiche
{
    private $pdo = null;

    private $month;
    private $userId;

    public function __construct(PdoGsb $pDO, string $userId, string $month)
    {
        $this->pdo = $pDO->getPdoGsb();
        $this->month = $month;
        $this->userId = $userId;

        $this->existingFile();
    }

    /**
     * Test et ajoute la fiche si elle n'existe pas 
     */
    private function existingFile(): void
    {
        $req = 'SELECT newremboursement(:idUser, :month); ';

        $result = $this->pdo->prepare($req);
        $result->bindParam('idUser', $this->userId);
        $result->bindParam('month', $this->month);
        $result->execute();
    }

    /**
     * Liste les frais forfaitaires
     */
    public function listFraisForfaitaires(): array
    {
        $req = 'SELECT "fLibelle", NULL AS "lfQuantite", round("fMontant", 2) AS "fMontant", 0 AS "fTotal", "fId" FROM forfait';
        $result = $this->pdo->prepare($req);
        $result->execute();

        return $result->fetchAll();
    }

    /**
     * Liste les frais Forfetaires d'un user pour 1 mois
     */
    public function listFraisForfaitForU(): array
    {
        $req = 'SELECT "fLibelle", "lfQuantite", ROUND("fMontant", 2) AS "fMontant", ROUND("lfQuantite" * "fMontant", 2) AS "fTotal", "fId"
            FROM remboursement
            INNER JOIN "ligne_forfait" ON "rVisiteur" = "lfVisiteur" AND "rMois" = "lfMois"
            INNER JOIN "forfait" ON "fId" = "lfForfait"
            WHERE "rVisiteur" = :idUser AND "rMois" = :monthF;';

        $result = $this->pdo->prepare($req);
        $result->bindParam('idUser', $this->userId);
        $result->bindParam('monthF', $this->month);
        $result->execute();

        return $result->fetchAll();
    }

    /**
     * Liste les frais hors forfait d'un user pour 1 mois
     */
    public function listFraisHF(): array
    {
        $req = 'SELECT to_char("lhDate", \'YYYY-mm-dd\') AS "lhDate", 
            "lhLibelle", ROUND("lhMontant",2) as "lhMontant", 
            "lhJustificatif", "lhRefus"
            FROM remboursement 
            inner join "ligne_hors_forfait" on "rVisiteur" = "lhVisiteur" 
            AND "rMois" = "lhMois"
            
            WHERE "rVisiteur" = :idVisiteur AND "rMois" = :mois 
            ORDER BY "lhDate";';

        $result = $this->pdo->prepare($req);
        $result->bindParam(':idVisiteur', $this->userId);
        $result->bindParam(':mois', $this->month);
        $result->execute();

        return $result->fetchAll();
    }

    /**
     * Fonction qui renvoie le prix total validé d'une fiche de frais
     * 
     * AJOUTER UNE FONCTION QUI CREE LA FICHE DE FRAIS LORS DU SELECT SI CELLE CI N'EXISTE PAS 
     */
    public function getMontantValide(): float
    {
        $req = 'SELECT "rMontantValide"
        FROM remboursement 
        WHERE "rVisiteur" = :idVisiteur AND "rMois" = :mois ;';

        $result = $this->pdo->prepare($req);
        $result->bindParam(':idVisiteur', $this->userId);
        $result->bindParam(':mois', $this->month);
        $result->execute();

        return $result->fetch()['rMontantValide'];
    }

    public function getStatus(): string
    {
        $req = 'select etat."eId" from remboursement 
        INNER JOIN etat on etat."eId" = remboursement."rEtat"
        WHERE "rVisiteur" = :idVisiteur AND "rMois" = :mois ;';

        $result = $this->pdo->prepare($req);
        $result->bindParam(':idVisiteur', $this->userId);
        $result->bindParam(':mois', $this->month);
        $result->execute();

        return $result->fetch()['eId'];
    }
}