<?php
//Récupérer la liste complete des villes
function getListeVilles($db)
{
    $result = pg_query($db, "SELECT * FROM Ville;");
    return $result;
}

function reserver($db, $mail, $codetrajet){
    return pg_query_params($db, "INSERT INTO Reserver VALUES ($1, $2, false, $3);", array($mail, $codetrajet, date('Y-m-d H:i:s')));
}

function getTrajet($db, $codetrajet)
{
    return pg_query_params($db, "SELECT * FROM Trajet WHERE codetrajet = $1;", array($codetrajet));
}

//Créer un trajet en bd
function creerTrajet($db, $mail, $dateDepart, $datArrivee, $matricule)
{
    $date = date('Y-m-d H:i:s');
    $result = pg_query_params($db, "INSERT INTO Trajet (datedepart, datearrivee, dateproposition, estannule, mailproposition, matricule) VALUES ($1, $2, $3, false, $4, $5) RETURNING codeTrajet;", array($dateDepart, $datArrivee, $date, $mail, $matricule));
    return $result;
}

//Creer les étapes d'un trajet
function creerEtapes($db, $codeTrajet, $etapes)
{
    $result = true;
    for ($i = 0; $i < count($etapes) && $result; $i++) {
        $arr = explode("%%", $etapes[$i]);
        $result = pg_query_params($db, "INSERT INTO traverser (codetrajet, nomville, codepostal, numeroetape) VALUES ($1, $2, $3, $4);", array($codeTrajet, $arr[0], $arr[1], $i + 1));
    }
    return $result;
}

//Vérifier la cohérence des dates
function checkDates($db, $mail, $dateDepart, $dateArrivee)
{
    $valide = $dateDepart <= $dateArrivee && $dateDepart >= date('Y-m-d H:i:s');
    $result = pg_query_params($db, "SELECT COUNT(*) From Trajet WHERE mailproposition = $1 AND estannule = false AND $2 BETWEEN datedepart AND datearrivee;", array($mail, $dateDepart));
    $row = pg_fetch_array($result);
    return $row[0] == 0 && $valide;
}

//Récupérer les étapes d'un trajet
function getEtapesTrajet($db, $codetrajet){
    $result = pg_query_params($db, "SELECT * FROM Traverser WHERE codetrajet = $1 ;", array($codetrajet));
    return $result;
}

//Vérifier le nombre de places restantes
function checkPlacesVoiture($db, $codetrajet)
{
    $result = pg_query_params($db, "SELECT nbplaces FROM Voiture vo, Trajet ta WHERE vo.matricule = ta.matricule AND ta.codeTrajet = $1;", array($codetrajet));
    $result2 = pg_query_params($db, "SELECT COUNT(*) FROM Reserver re, Trajet ta WHERE re.codetrajet = ta.codetrajet AND ta.codetrajet = $1;", array($codetrajet));
    if(!$result || !$result2){
        return false;
    }
    $nbplaces = pg_fetch_array($result)[0];
    $nbreservations = pg_fetch_array($result)[0];
    return $nbplaces-1 > $nbreservations;
}

//Vérifier la cohérence des dates
function checkDatesReserve($db, $mail, $dateDepart)
{
    $result = pg_query_params($db, "SELECT COUNT(*) From Trajet ta, reserver re WHERE re.mailutilisateur = $1 AND re.codetrajet = ta.codetrajet AND $2 BETWEEN datedepart AND datearrivee;", array($mail, $dateDepart));
    $row = pg_fetch_array($result);
    return $row[0] == 0;
}

//Verifier reservations
function checkReservations($db, $mail, $codetrajet)
{
    $result = pg_query_params($db, "SELECT COUNT(*) From reserver WHERE mailutilisateur = $1 AND codetrajet = $2;", array($mail, $codetrajet));
    $row = pg_fetch_array($result);
    return $row[0] == 0;
}

//Vérifier que l'utilisateur est bien associé à cette voiture
function checkVoiture($db, $mail, $matricule)
{
    $result = pg_query_params($db, "SELECT matricule From Voiture WHERE mail = $1  AND matricule = $2;", array($mail, $matricule));
    $row = pg_fetch_array($result);
    return !strcmp($matricule, $row[0]);
}

//Recuperer le nombre de trajets restant proposés par l'utilisateur $mail
function getTrajetsProposes($db, $mail)
{
    $result = pg_query_params($db, "SELECT COUNT(*) from Trajet WHERE mailProposition = $1 AND estAnnule = false AND datedepart >= $2;", array($mail, date('Y-m-d H:i:s')));
    if ($result) {
        $row = pg_fetch_array($result);
        return $row[0];
    }
    return 0;
}

//Recuperer le nombre de trajets restant reservés par l'utilisateur $mail
function getTrajetsReserves($db, $mail)
{
    $result = pg_query_params($db, "SELECT COUNT(*) FROM trajet tr, reserver re WHERE re.codetrajet = tr.codetrajet AND mailutilisateur = $1 AND tr.estannule = false AND re.estannule = false AND tr.datedepart >= $2;", array($mail, date('Y-m-d H:i:s')));
    if ($result) {
        $row = pg_fetch_array($result);
        return $row[0];
    }
    return 0;
}

function getMessagesNonLus($db, $mail)
{
    $result = pg_query_params($db, "SELECT COUNT(*) from Message WHERE mailRecepteur = $1 AND estLu = FALSE;", array($mail));
    if ($result) {
        $row = pg_fetch_array($result);
        $login = $row[0];
        return $login;
    }
}

function getAvisNonLus($db, $mail)
{
    global $db;
    $result = pg_query_params($db, "SELECT COUNT(tr.mailProposition) FROM Trajet tr, Avis av WHERE tr.codeTrajet = av.codeTrajet AND av.estLu = false AND tr.mailProposition = $1;", array($mail));
    if ($result) {
        $row = pg_fetch_array($result);
        $login = $row[0];
        return $login;
    }
}

//Récupérer la liste des voitures concernant un utilisateur
function getListeVoitures($db, $mail)
{
    $result = pg_query_params($db, "SELECT * FROM Voiture WHERE mail = $1;", array($mail));
    return $result;
}

//Afficher les voitures dans un select
function afficherVoitures($db, $mail)
{
    $voitures = getListeVoitures($db, $mail);
    while ($row = pg_fetch_array($voitures)) {
        $modele = $row['modele'];
        $matricule = $row['matricule'];
        echo "<option value='$matricule'>$modele</option>";
    }
}

//Afficher les voitures dans un select
function afficherVilles($db)
{
    $villes = getListeVilles($db);
    while ($row = pg_fetch_array($villes)) {
        $nom = $row['nomville'];
        $cp = $row['codepostal'];
        echo "<option value='$nom%%$cp'>$nom ($cp)</option>";
    }
}

//Récuperer la ville de départ du trajet $codetrajet
function getDepart($db, $codetajet)
{
    $result = pg_query_params($db, "SELECT * FROM ville v, traverser tr WHERE tr.nomVille = v.nomVille AND tr.codepostal = v.codepostal AND tr.codetrajet = $1 AND tr.numeroetape = (SELECT MIN(numeroetape) FROM traverser WHERE codetrajet = $1);", array($codetajet));
    $return = false;
    if ($result) {
        $return = pg_fetch_array($result);
    }
    return $return;
}

//Récuperer la ville d'arrivée du trajet $codetrajet
function getFin($db, $codetajet)
{
    $result = pg_query_params($db, "SELECT * FROM ville v, traverser tr WHERE tr.nomVille = v.nomVille AND tr.codepostal = v.codepostal AND tr.codetrajet = $1 AND tr.numeroetape = (SELECT MAX(numeroetape) FROM traverser WHERE codetrajet = $1);", array($codetajet));
    $return = false;
    if ($result) {
        $return = pg_fetch_array($result);
    }
    return $return;
}

//Annuler le trajet $codetrajet
function annulerTrajet($db, $codetrajet){
    $result = pg_query_params($db, "UPDATE Trajet SET estannule = true WHERE codetrajet = $1;", array($codetrajet));
    return $result;
}

//Annuler la reservation pour le tajet $codetrajet de l'utilisateur $mail
function annulerReservation($db, $mail,$codetrajet){
    $result = pg_query_params($db, "DELETE FROM reserver WHERE codetrajet = $1 AND mailutilisateur = $2;", array($codetrajet, $mail));
    return $result;
}

function getErrorDepotTrajet($code)
{
    switch ($code) {
        case 1:
            return "Erreur lors de la vérification des étapes.";
        case 2:
            return "Erreur lors de la vérification des dates.";
        case 3:
            return "Ce compte n'est pas associé à la voiture sélectionnée.";
        case 4:
            return "Erreur lors de l'enregistrement du trajet.";
    }
}

//Verifie l'existance d'un avis pour le trajet $codetrajet émit par l'utilisateur $mail
function avisExiste($db, $mail, $codetrajet){
    $result = pg_query_params($db, "SELECT COUNT(*) FROM Avis WHERE mailemetteur = $1 AND codetrajet = $2;", array($mail, $codetrajet));
    if(!$result){
        return false;
    }
    return pg_fetch_array($result)[0] != 0;
}

?>