<?php
// Display all errors

use Classe\WriteWhoDidWhat;

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
header('Content-Type: text/html; charset=utf-8');
include "../Parametre/gestionvisiteur/dbConnect.php";
$conn = dbConnect(false);
//header('Content-Type: application/json');
$data = json_decode(file_get_contents("php://input"), true);


include_once("FunctionClasse.php");
//inclus les variable du site
include("../Parametre/" . getPath(1) . "/" . getPath(1) . ".php");
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (isset($_SESSION[$NomSite]['idUser']))
  $IdConnectedUser = $_SESSION[$NomSite]['idUser'];
else
  $IdConnectedUser = 0;


if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'bannirFournisseur') {


  $action = $_POST['action'] ?? null;
  $idFournisseur = $_POST['idFournisseur'];

  $sql = "UPDATE fournisseur SET banni = 1 WHERE id_fournisseur = " . $idFournisseur;

  if ($conn->exec($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'deleteFournisseur') {

  $action = $_POST['action'] ?? null;
  $idFournisseur = $_POST['idFournisseur'];

  $sql = "UPDATE fournisseur SET deleted = 1 WHERE id_fournisseur = " . $idFournisseur;

  if ($conn->exec($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'updateFournisseur') {

  file_put_contents("debug_ajax.txt", print_r($_POST, true));

  $action = $_POST['action'] ?? null;
  $id_fournisseur = $_POST['idFournisseurtodeleteorupd'];
  $companyNameFournisseur =  strtoupper(addslashes(trim($_POST['companyNameFournisseur'])));
  $champActiviteFournisseur =  strtoupper(addslashes(trim($_POST['champActiviteFournisseur'])));
  $champemailFournisseur =  strtolower(addslashes(trim($_POST['champemailFournisseur'])));
  $champtelFournisseur =  addslashes(trim($_POST['champtelFournisseur']));
  $champobservationFournisseur =  addslashes(trim($_POST['champobservationFournisseur']));


  if (isset($_POST['banCompanyCheckbox'])) {
    $banni = '1';
  } else {
    $banni = '0';
  }

  if (isset($_POST['idFournisseurtodeleteorupd']) && $_POST['companyNameFournisseur'] != '') {

    $SqlInsComp = "SELECT * FROM fournisseur WHERE UPPER(nom_compagnie) = '" . $companyNameFournisseur . "' AND id_fournisseur != " . $id_fournisseur;
    $conn->exec($SqlInsComp);
    $resultcomp = $conn->data;

    if (empty($resultcomp)) {

      $SqlUpdateFourn = "UPDATE  fournisseur SET nom_compagnie = '" . $companyNameFournisseur . "', activite = '" . $champActiviteFournisseur . "', email = '" . $champemailFournisseur . "', tel = '" . $champtelFournisseur . "', banni = " . $banni . ", deleted = 0, observation = '" . $champobservationFournisseur . "' WHERE id_fournisseur = " . $id_fournisseur . ";";

      //echo $sql;

      $conn->exec("SET CHARACTER SET utf8mb4");
      if ($conn->execQuery($SqlUpdateFourn)) {
        echo json_encode(['success' => true, 'message' => '']);
      } else {
        echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
      }
    } else {
      echo json_encode(['success' => false, 'message' => 'fournisseurfounded']);
    }
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addFournisseur') {

  $action = $_POST['action'] ?? null;
  $companyNameFournisseur =  strtoupper(addslashes(trim($_POST['companyNameFournisseur'])));
  $champActiviteFournisseur =  strtoupper(addslashes(trim($_POST['champActiviteFournisseur'])));
  $champemailFournisseur =  strtolower(addslashes(trim($_POST['champemailFournisseur'])));
  $champtelFournisseur =  addslashes(trim($_POST['champtelFournisseur']));
  $champobservationFournisseur =  addslashes(trim($_POST['champobservationFournisseur']));

  if (isset($_POST['banCompanyCheckbox'])) {
    $banni = '1';
  } else {
    $banni = '0';
  }


  $sqlCheckVisitir = "SELECT id_fournisseur FROM fournisseur WHERE nom_compagnie = '" . $companyNameFournisseur . "' ";
  $conn->exec($sqlCheckVisitir);
  $resultfv = $conn->data;


  if ($conn->rows > 0) {
    foreach ($resultfv as $rowfournisseur) {
      $id_fournisseur = $rowfournisseur["id_fournisseur"];
    }
  } else {
    $id_fournisseur = 0;
  }

  if (empty($resultfv)) {

    $sql = "INSERT INTO fournisseur (id_fournisseur, nom_compagnie, activite, email, tel, banni, deleted, observation) VALUES 
    (  null,
    '" . $companyNameFournisseur . "',
    '" . $champActiviteFournisseur . "',
    '" . $champemailFournisseur . "',
    '" . $champtelFournisseur . "',
    " . $banni . ",
    0,
    '" . $champobservationFournisseur . "') ";

    if ($conn->exec($sql)) {
      echo json_encode(['success' => true, 'message' => '']);
    } else {
      echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
    }
  } else {
    if ($id_fournisseur != 0) {
      echo json_encode(['success' => false, 'message' => 'fournisseurfounded', 'id_fournisseur' => $id_fournisseur]);
    } else {
      echo json_encode(['success' => false, 'message' => 'fournisseurfounded', 'id_fournisseur' => '0']);
    }
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getFournisseurDetails' && isset($_POST['idFournisseur']) && $_POST['idFournisseur'] != '') {

  $idFournisseur = intval($_POST['idFournisseur']); // Ensure the ID is an integer

  try {

    $sql = "SELECT id_fournisseur, nom_compagnie, activite, email, tel, banni, deleted, observation
                FROM fournisseur
                WHERE id_fournisseur = $idFournisseur";

    $conn->exec("SET CHARACTER SET utf8mb4");
    $conn->exec($sql); // Execute the query

    // Fetch the data
    $result = $conn->data;
    $json = array();

    foreach ($result as $row) {
      array_push($json, array(
        'id_fournisseur' => $row['id_fournisseur'],
        'nom_compagnie' => $row['nom_compagnie'],
        'activite' => $row['activite'],
        'email' => $row['email'],
        'tel' => $row['tel'],
        'banni' => $row['banni'],
        'deleted' => $row['deleted'],
        'observation' => $row['observation']
      ));
    }

    // Return JSON response
    echo json_encode(['success' => true, 'data' => $json]);
  } catch (Exception $e) {
    // Handle any errors
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
  exit;
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'bannirVisiteur') {


  $action = $_POST['action'] ?? null;
  $idVisiteur = $_POST['idVisiteur'];

  $sql = "UPDATE visiteurs SET banni = 1 WHERE id_visiteur = " . $idVisiteur;

  if ($conn->execQuery($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'deleteVisiteur') {

  $action = $_POST['action'] ?? null;
  $idVisiteur = $_POST['idVisiteur'];

  $sql = "UPDATE visiteurs SET deleted = 1 WHERE id_visiteur = " . $idVisiteur;

  if ($conn->execQuery($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'updateVisiteur') {

  $id_visiteur = $_POST['idVisiteurtodeleteorupd'];
  $companyNameVisiteur =  strtoupper(addslashes(trim($_POST['companyNameVisiteur'])));
  $companyIdVisiteur = $_POST['companyIdVisiteur'];
  $champCiviliteVisiteur =  strtoupper(addslashes(trim($_POST['champCiviliteVisiteur'])));
  $champnomVisiteur =  strtoupper(addslashes(trim($_POST['champnomVisiteur'])));
  $champprenomVisiteur =  strtoupper(addslashes(trim($_POST['champprenomVisiteur'])));
  $champemailVisiteur =  strtolower(addslashes(trim($_POST['champemailVisiteur'])));
  $champtelVisiteur =  strtolower(addslashes(trim($_POST['champtelVisiteur'])));
  $champobservationVisiteur =  strtolower(addslashes(trim($_POST['champobservationVisiteur'])));


  if (isset($_POST['companyIdVisiteur']) && $_POST['companyIdVisiteur'] == '0') {

    $SqlInsComp = "SELECT * FROM fournisseur WHERE UPPER(nom_compagnie) = '" . $companyNameVisiteur . "'";
    $conn->exec($SqlInsComp);
    $resultcomp = $conn->data;

    if (empty($resultcomp)) {
      $SqlInsertComp = "INSERT INTO fournisseur (nom_compagnie) VALUES ('" . strtoupper(addslashes(trim($_POST['companyNameVisiteur']))) . "')";
      $conn->exec($SqlInsertComp);

      if ($conn->rows > 0) {
        $companyIdVisiteur = mysqli_insert_id($conn->link_id);
      }
    } else {
      $companyIdVisiteur = $resultcomp[0]['id_fournisseur'];
    }
  }

  $sql = "UPDATE  visiteurs SET id_fournisseur = '" . $companyIdVisiteur . "', civilite = '" . $champCiviliteVisiteur . "', 
  nom = '" . $champnomVisiteur . "',   prenom = '" . $champprenomVisiteur . "', 
  email = '" . $champemailVisiteur . "', tel = '" . $champtelVisiteur . "', banni = 0, deleted = 0, 
  observation = '" . $champobservationVisiteur . "'
  WHERE id_visiteur = " . $id_visiteur;


  if ($conn->execQuery($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addVisiteur') {

  $action = $_POST['action'] ?? null;
  $companyNameVisiteur =  strtoupper(addslashes(trim($_POST['companyNameVisiteur'])));
  $companyIdVisiteur = $_POST['companyIdVisiteur'];
  $champCiviliteVisiteur =  strtoupper(addslashes(trim($_POST['champCiviliteVisiteur'])));
  $champnomVisiteur =  strtoupper(addslashes(trim($_POST['champnomVisiteur'])));
  $champprenomVisiteur =  strtoupper(addslashes(trim($_POST['champprenomVisiteur'])));
  $champemailVisiteur =  strtolower(addslashes(trim($_POST['champemailVisiteur'])));
  $champtelVisiteur =  strtolower(addslashes(trim($_POST['champtelVisiteur'])));
  $champobservationVisiteur =  strtolower(addslashes(trim($_POST['champobservationVisiteur'])));


  $sqlCheckVisitir = "SELECT id_visiteur FROM visiteurs WHERE id_fournisseur = " . $companyIdVisiteur . " AND nom = '" . $champnomVisiteur . "' AND prenom = '" . $champprenomVisiteur . "' ";
  $conn->exec($sqlCheckVisitir);
  $resultfv = $conn->data;


  if ($conn->rows > 0) {
    foreach ($resultfv as $rowvisiteur) {
      $id_visiteur = $rowvisiteur["id_visiteur"];
    }
  } else {
    $id_visiteur = 0;
  }

  if (empty($resultfv)) {

    if (isset($_POST['companyIdVisiteur']) && $_POST['companyIdVisiteur'] == '0') {

      $SqlInsComp = "SELECT * FROM fournisseur WHERE UPPER(nom_compagnie) = '" . $companyNameVisiteur . "'";
      $conn->exec($SqlInsComp);
      $resultcomp = $conn->data;

      if (empty($resultcomp)) {
        $SqlInsertComp = "INSERT INTO fournisseur (nom_compagnie) VALUES ('" . strtoupper(addslashes(trim($_POST['companyNameVisiteur']))) . "')";
        $conn->exec($SqlInsertComp);

        if ($conn->rows > 0) {
          $companyIdVisiteur = mysqli_insert_id($conn->link_id);
        }
      } else {
        $companyIdVisiteur = $resultcomp[0]['id_fournisseur'];
      }
    }

    $sql = "INSERT INTO visiteurs(id_fournisseur, civilite, nom, prenom, email, tel, banni, deleted, observation) VALUES 
    (  '" . $companyIdVisiteur . "',
    '" . $champCiviliteVisiteur . "',
    '" . $champnomVisiteur  . "',
    '" . $champprenomVisiteur  . "',
    '" . $champemailVisiteur . "',
    '" . $champtelVisiteur . "',
    '0',
    '0',
    '" . $champobservationVisiteur  . "') ";


    if ($conn->exec($sql)) {
      echo json_encode(['success' => true, 'message' => '']);
    } else {
      echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
    }
  } else {
    if ($id_visiteur != 0) {
      echo json_encode(['success' => false, 'message' => 'visitorfounded', 'id_visiteur' => $id_visiteur]);
    } else {
      echo json_encode(['success' => false, 'message' => 'visitorfounded', 'id_visiteur' => '0']);
    }
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addVisite') {

  $companyId = $_POST['companyId'];
  $visiteurId = $_POST['visiteurId'];
  $companyName = strtoupper(addslashes(trim($_POST['companyName'])));
  $champCivilite = strtoupper(addslashes(trim($_POST['champCivilite'])));
  $champnom = strtoupper(addslashes(trim($_POST['champnom'])));
  $champprenom = strtoupper(addslashes(trim($_POST['champprenom'])));
  $secteurId = $_POST['secteurId'];
  $champsecteur = strtoupper(addslashes(trim($_POST['champsecteur'])));
  $contactId = $_POST['contactId'];
  $champcontactPerson = strtoupper(addslashes(trim($_POST['champcontactPerson'])));
  $champDateDebut = $_POST['champDateDebut'];
  $champHeureDebut = $_POST['champHeureDebut'];

  $champDatefin = !$useOnlyFirstDate ? $_POST['champDatefin'] : $_POST['champDateDebut'];

  $champHeurefin = $_POST['champHeurefin'];
  $champobservation =  strtolower(addslashes(trim(string: $_POST['champobservation'])));

  $json = array();

  /* Création de la compagnie s'elle n'existe pas */
  if (isset($_POST['companyId']) && $_POST['companyId'] == '0') {
    $SqlInsComp = "SELECT * FROM fournisseur WHERE UPPER(nom_compagnie) = '" . $companyName . "'";
    $conn->exec($SqlInsComp);
    $resultcomp = $conn->data;

    if (empty($resultcomp)) {
      $SqlInsertComp = "INSERT INTO fournisseur (nom_compagnie) VALUES ('" . $companyName . "')";
      $conn->exec($SqlInsertComp);

      if ($conn->rows > 0) {
        $companyId = mysqli_insert_id($conn->link_id);
        array_push($json, array('addedcompagny' => $companyId, 'Nom compagnie' => $companyName));
      }
    } else {
      $companyId = $resultcomp[0]['id_fournisseur'];
    }
  }

  /* Création du secteur s'il n'existe pas */
  if (isset($_POST['secteurId']) && $_POST['secteurId'] == '0') {
    $SqlInsSect = "SELECT * FROM secteur_a_visiter WHERE UPPER(champsecteur) = '" . $champsecteur . "'";
    $conn->exec($SqlInsSect);
    $resultSect = $conn->data;

    if (empty($resultSect)) {
      $SqlInsertSect = "INSERT INTO secteur_a_visiter (champsecteur) VALUES ('" . $champsecteur . "')";
      $conn->exec($SqlInsertSect);

      if ($conn->rows > 0) {
        $secteurId = mysqli_insert_id($conn->link_id);
        array_push($json, array('addedsecteur' => $secteurId, 'nom_secteur' => $champsecteur));
      }
    } else {
      $secteurId = $resultSect[0]['id_secteur'];
    }
  }

  /* Création de la personne contact si elle n'existe pas */
  if (isset($_POST['contactId']) && $_POST['contactId'] == '0') {
    $SqlInsContact = "SELECT * FROM contact_a_visiter WHERE UPPER(nom_contact) = '" . $champcontactPerson . "'";
    $conn->exec($SqlInsContact);
    $resultContact = $conn->data;

    if (empty($resultContact)) {
      $SqlInsertContact = "INSERT INTO contact_a_visiter (nom_contact) VALUES ('" . $champcontactPerson . "')";
      $conn->exec($SqlInsertContact);

      if ($conn->rows > 0) {
        $contactId = mysqli_insert_id($conn->link_id);
      }
    } else {
      $contactId = $resultContact[0]['id_contact'];
    }
  }

  $sql = "INSERT INTO visites_visiteurs (id_visiteur,date_debut,heure_debut,date_fin,heure_fin,secteur,contact, added_by, observation) VALUES 
  ('" . $visiteurId . "', '" . $champDateDebut . "', '" . $champHeureDebut . "', '" . $champDatefin . "', '" . $champHeurefin . "', '" . $secteurId . "', '" . $contactId . "', $IdConnectedUser,  '" . $champobservation . "' ); ";

  if ($conn->exec($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['action']) && $_GET['action'] === 'deleteVisite') {

  $idVisite = $_GET["id_visite"];

  $SqlDelete = "UPDATE visites_visiteurs SET deleted = 1, deleted_by = " . $IdConnectedUser . "  WHERE id_visite = " . $idVisite;
  if ($conn->execQuery($SqlDelete)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur suppression visiste Num : ' . $idVisite]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'refreshStatutVerifie') {

  $idVisite = $_POST["idVisite"];
  $answer = $_POST["answer"];

  if ($answer == "yes") {
    $SqlUpdateVerif = "UPDATE visites_visiteurs SET verifie = 1  WHERE id_visite = " . $idVisite;
  } else {
    $SqlUpdateVerif = "UPDATE visites_visiteurs SET verifie = 0  WHERE id_visite = " . $idVisite;
  }

  //echo $SqlUpdateVerif;
  if ($conn->execQuery($SqlUpdateVerif)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur suppression visiste Num : ' . $idVisite]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] == 'updateVisite') {


  $idVisitetodeleteorupd = $_POST['idVisitetodeleteorupd'];
  $companyId = $_POST['companyId'];
  $visiteurId = $_POST['visiteurId'];
  $companyName = strtoupper(addslashes(trim($_POST['companyName'])));
  $champCivilite = strtoupper(addslashes(trim($_POST['champCivilite'])));
  $champnom = strtoupper(addslashes(trim($_POST['champnom'])));
  $champprenom = strtoupper(addslashes(trim($_POST['champprenom'])));
  $secteurId = $_POST['secteurId'];
  $champsecteur = strtoupper(addslashes(trim($_POST['champsecteur'])));
  $contactId = $_POST['contactId'];
  $champcontactPerson = strtoupper(addslashes(trim($_POST['champcontactPerson'])));
  $champDateDebut = $_POST['champDateDebut'];
  $champHeureDebut = $_POST['champHeureDebut'];

  if (!$useOnlyFirstDate)
    $champDatefin = $_POST['champDatefin'];
  else
    $champDatefin = $_POST['champDateDebut'];

  $champHeurefin = $_POST['champHeurefin'];
  $champobservation =  strtolower(addslashes(trim(string: $_POST['champobservation'])));

  if (isset($_POST['companyId']) && $_POST['companyId'] == '0') {
    $SqlInsComp = "SELECT * FROM fournisseur WHERE UPPER(nom_compagnie) = '" . strtoupper(addslashes(trim($companyName))) . "'";
    $conn->exec($SqlInsComp);
    $resultcomp = $conn->data;

    if (empty($resultcomp)) {
      $SqlInsertComp = "INSERT INTO fournisseur (nom_compagnie) VALUES ('" . strtoupper(addslashes(trim($companyName))) . "')";
      $conn->exec($SqlInsertComp);

      if ($conn->rows > 0) {
        $companyId = mysqli_insert_id($conn->link_id);
      }
    } else {
      $companyId = $resultcomp[0]['id_fournisseur'];
    }
  }

  $sql = "UPDATE visites_visiteurs SET date_debut='" . $champDateDebut . "',heure_debut='" . $champHeureDebut . "',
  date_fin='" . $champDatefin . "',heure_fin='" . $champHeurefin . "',secteur='" . $secteurId . "',contact='" . $contactId . "', edited_by = " . $IdConnectedUser . ", observation = '" . $champobservation . "' 
  WHERE id_visite=" . $idVisitetodeleteorupd . ";";


  if ($conn->execQuery($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Error execution de la requête.']);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getVisiteDetails') {
  $idVisite = intval($_POST['idVisite']); // Ensure the ID is an integer

  try {

    $sql = "SELECT SQL_CALC_FOUND_ROWS vv.id_visite, COUNT(vv.id_visite) AS N, vv.date_debut, DATE_FORMAT(vv.heure_debut, '%H:%i') AS heure_debut, vv.date_fin, DATE_FORMAT(vv.heure_fin, '%H:%i') AS heure_fin, vv.secteur, vv.contact,
                vv.observation, v.id_visiteur, v.id_fournisseur, v.civilite, v.nom, v.prenom, f.id_fournisseur, f.nom_compagnie, f.email, f.tel, sav.id_secteur, sav.nom_secteur, cav.id_contact, cav.nom_contact
                FROM visites_visiteurs vv
                LEFT JOIN visiteurs v ON vv.id_visiteur =   v.id_visiteur
                LEFT JOIN fournisseur f ON v.id_fournisseur = f.id_fournisseur 
                LEFT JOIN secteur_a_visiter sav ON vv.secteur = sav.id_secteur 
                LEFT JOIN contact_a_visiter cav ON vv.contact = cav.id_contact 
                WHERE vv.id_visite=$idVisite";

    if ($conn->exec($sql)) {

      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array(
          'id_visite' => $row['id_visite'],
          'id_fournisseur' => $row['id_fournisseur'],
          'nom_compagnie' => $row['nom_compagnie'],
          'id_visiteur' => $row['id_visiteur'],
          'civilite' => $row['civilite'],
          'prenom' => $row['prenom'],
          'nom' => $row['nom'],
          'date_debut' => $row['date_debut'],
          'heure_debut' => $row['heure_debut'],
          'date_fin' => $row['date_fin'],
          'heure_fin' => $row['heure_fin'],
          'nom_secteur' => $row['nom_secteur'],
          'nom_contact' => $row['nom_contact'],
          'id_secteur' => $row['secteur'],
          'id_contact' => $row['contact'],
          'observation' => $row['observation']
        ));
      }
      echo json_encode(['success' => true, 'data' => $json]);
    } else {
      echo json_encode(['success' => false, 'data' => $json]);
    }
  } catch (Exception $e) {
    // Handle any errors
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getVisiteurDetails' && isset($_POST['idVisiteur']) && $_POST['idVisiteur'] != '') {
  $idVisiteur = intval($_POST['idVisiteur']); // Ensure the ID is an integer

  try {

    $sql = "SELECT SQL_CALC_FOUND_ROWS v.id_visiteur, v.civilite, v.nom, v.prenom,v.email, v.tel, v.banni, v.deleted, v.observation, f.id_fournisseur, f.nom_compagnie
                FROM visiteurs v
                LEFT JOIN fournisseur f ON v.id_fournisseur = f.id_fournisseur 
                WHERE v.id_visiteur = $idVisiteur";

    $conn->exec($sql); // Execute the query

    // Fetch the data
    $result = $conn->data;
    $json = array();

    foreach ($result as $row) {
      array_push($json, array(
        'id_visiteur' => $row['id_visiteur'],
        'id_fournisseur' => $row['id_fournisseur'],
        'nom_compagnie' => $row['nom_compagnie'],
        'civilite' => $row['civilite'],
        'prenom' => $row['prenom'],
        'nom' => $row['nom'],
        'email' => $row['email'],
        'tel' => $row['tel'],
        'banni' => $row['banni'],
        'deleted' => $row['deleted'],
        'observation' => $row['observation']
      ));
    }

    // Return JSON response
    echo json_encode(['success' => true, 'data' => $json]);
  } catch (Exception $e) {
    // Handle any errors
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
  exit;
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'refreshDashboard') {

  $CounterFourn = 0;
  $CounterVisiteurs = 0;
  $CounterVisites = 0;
  $CounterVisiteAjd = 0;
  $CounterVisiteDemain = 0;

  $Sql = "SELECT COUNT(*) as CounterFourn FROM fournisseur WHERE deleted = 0";
  $conn->exec($Sql);
  $CounterFourn = $conn->data[0]['CounterFourn'];

  $Sql = "SELECT COUNT(*) as CounterVisiteurs FROM visiteurs WHERE deleted = 0";
  $conn->exec($Sql);
  $CounterVisiteurs = $conn->data[0]['CounterVisiteurs'];

  $Sql = "SELECT COUNT(*) as CounterVisites FROM visites_visiteurs WHERE deleted = 0";
  $conn->exec($Sql);
  $CounterVisites = $conn->data[0]['CounterVisites'];

  $resultlistevisiteur = null;
  $Sql = "SELECT COUNT(*) as CounterVisiteAjd FROM visites_visiteurs WHERE CURDATE() BETWEEN date_debut AND date_fin AND deleted = 0";
  $conn->exec($Sql);
  $CounterVisiteAjd = $conn->data[0]['CounterVisiteAjd'];

  if ($CounterVisiteAjd > 0) {
    $sqlListvisiteur = "SELECT SQL_CALC_FOUND_ROWS vv.id_visite, COUNT(vv.id_visite) AS N, vv.date_debut, vv.heure_debut, vv.date_fin, vv.heure_fin, vv.secteur, vv.contact,
          v.id_visiteur, v.id_fournisseur, v.civilite, v.nom, v.prenom, CONCAT(v.civilite, ' ', v.nom, ' ', v.prenom) AS nom_complet, f.id_fournisseur, f.nom_compagnie, f.email, f.tel, sav.nom_secteur, cav.nom_contact
          FROM visites_visiteurs vv
          LEFT JOIN visiteurs v ON vv.id_visiteur =   v.id_visiteur
          LEFT JOIN fournisseur f ON v.id_fournisseur = f.id_fournisseur 
          LEFT JOIN secteur_a_visiter sav ON vv.secteur = sav.id_secteur 
          LEFT JOIN contact_a_visiter cav ON vv.contact = cav.id_contact 
          WHERE CURDATE() BETWEEN vv.date_debut AND vv.date_fin AND vv.deleted = 0
          GROUP BY vv.id_visite 
          ORDER BY id_visite DESC;";

    $conn->exec($sqlListvisiteur);

    $resultlistevisiteur = $conn->data;
    $json = array();

    foreach ($resultlistevisiteur as $row) {
      //array_push($json, array('id_secteur' => $row['secteur'], 'nom_secteur' => $row['nom_secteur']));
    }
  }


  $resultlistevisiteurdemain = null;
  $Sql = "SELECT COUNT(*) as CounterVisiteAjd FROM visites_visiteurs WHERE DATE_ADD(CURDATE(), INTERVAL 1 DAY) BETWEEN date_debut AND date_fin AND deleted = 0";
  $conn->exec($Sql);
  $CounterVisiteDemain = $conn->data[0]['CounterVisiteAjd'];

  if ($CounterVisiteDemain > 0) {
    $sqlListvisiteur = "SELECT SQL_CALC_FOUND_ROWS vv.id_visite, COUNT(vv.id_visite) AS N, vv.date_debut, vv.heure_debut, vv.date_fin, vv.heure_fin, vv.secteur, vv.contact,
          v.id_visiteur, v.id_fournisseur, v.civilite, v.nom, v.prenom, CONCAT(v.civilite, ' ', v.nom, ' ', v.prenom) AS nom_complet, f.id_fournisseur, f.nom_compagnie, f.email, f.tel, sav.nom_secteur, cav.nom_contact
          FROM visites_visiteurs vv
          LEFT JOIN visiteurs v ON vv.id_visiteur =   v.id_visiteur
          LEFT JOIN fournisseur f ON v.id_fournisseur = f.id_fournisseur 
          LEFT JOIN secteur_a_visiter sav ON vv.secteur = sav.id_secteur 
          LEFT JOIN contact_a_visiter cav ON vv.contact = cav.id_contact 
          WHERE DATE_ADD(CURDATE(), INTERVAL 1 DAY) BETWEEN vv.date_debut AND vv.date_fin AND vv.deleted = 0
          GROUP BY vv.id_visite 
          ORDER BY id_visite DESC;";

    $conn->exec($sqlListvisiteur);

    $resultlistevisiteurdemain = $conn->data;
    $json2 = array();

    foreach ($resultlistevisiteurdemain as $row) {
      //array_push($json2, array('id_secteur' => $row['secteur'], 'nom_secteur' => $row['nom_secteur']));
    }
  }

  echo json_encode(['success' => true, 'CounterFourn' => $CounterFourn, 'CounterVisiteurs' => $CounterVisiteurs, 'CounterVisites' => $CounterVisites, 'CounterVisiteAjd' => $CounterVisiteAjd, 'CounterVisiteDemain' => $CounterVisiteDemain]);
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'LanguageToggle') {

  if (!isset($_SESSION['language'])) {
    $_SESSION['language'] = 'fr';
  }

  $_SESSION['language'] = $_SESSION['language'] === 'fr' ? 'en' : 'fr';

  echo json_encode(['success' => true, 'message' => $_SESSION['language']]);
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getUserDetails') {

  $idUser = intval($_POST['idUser']);

  try {


    $sql = "SELECT users.id_user,username, lastname, firstname, email, comment, valide_au, num_licence,departements.id_departement FROM users 
    LEFT JOIN departement_user ON departement_user.id_user = users.id_user
    LEFT JOIN departements ON departements.id_departement = departement_user.id_departement 
    WHERE users.id_user=$idUser";

    $json = array();
    $jsondep = array();
    $jsondepUser = array();
    $jsonRole = array();
    $jsonRoleUser = array();

    if ($conn->exec($sql)) {

      $result = $conn->data;

      foreach ($result as $row) {
        array_push($json, array(
          'id_visite' => $row['id_user'],
          'username' => $row['username'],
          'lastname' => $row['lastname'],
          'firstname' => $row['firstname'],
          'email' => $row['email'],
          'comment' => $row['comment'],
          'valide_au' => $row['valide_au'],
          'num_licence' => $row['num_licence'],
          'id_departement' => $row['id_departement']
        ));
      }

      $sqldep = "SELECT id_departement, nom_departement FROM departements ORDER BY nom_departement ASC";
      if ($conn->exec($sqldep)) {
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($jsondep, array(
            'id_departement' => $rowdep['id_departement'],
            'nom_departement' => $rowdep['nom_departement']
          ));
        }
      }

      $sqldepUser = "SELECT id_departement FROM departement_user WHERE id_user = $idUser ORDER BY id_departement ASC";
      if ($conn->exec($sqldepUser)) {
        $resultUser = $conn->data;
        foreach ($resultUser as $rowdepUser) {
          array_push($jsondepUser, array(
            'id_departement' => $rowdepUser['id_departement']
          ));
        }
      }


      $sqlRole = "SELECT id_role, role_name FROM roles ORDER BY role_name ASC";
      if ($conn->exec($sqlRole)) {
        $result = $conn->data;
        foreach ($result as $rowrole) {
          array_push($jsonRole, array(
            'id_role' => $rowrole['id_role'],
            'role_name' => $rowrole['role_name']
          ));
        }
      }


      $sqlRoleUser = "SELECT id_role FROM users_roles WHERE id_user = $idUser";
      if ($conn->exec($sqlRoleUser)) {
        $result = $conn->data;
        foreach ($result as $rowroleUser) {
          array_push($jsonRoleUser, array(
            'id_role' => $rowroleUser['id_role']
          ));
        }
      }

      echo json_encode(['success' => true, 'data' => $json, 'datadep' => $jsondep, 'datadepuser' => $jsondepUser, 'datarole' => $jsonRole, 'dataroleuser' => $jsonRoleUser]);
    } else {
      echo json_encode(['success' => false, 'message' => "Erreur execution requête."]);
    }
  } catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addEditUser') {

  $idUserToEdit = $_POST['idUserToEdit'];
  $typeSend = $_POST['typeSend'];
  $username = strtoupper(addslashes(trim($_POST['username']))) ?? null;
  $lastname = strtoupper(addslashes(trim($_POST['lastname']))) ?? null;
  $firstname = strtoupper(addslashes(trim($_POST['firstname']))) ?? null;
  $email = strtolower(addslashes(trim($_POST['email']))) ?? null;
  $comment = addslashes(trim($_POST['comment'])) ?? null;
  $num_licence = empty($_POST['num_licence']) || $_POST['num_licence'] == 0 ? "null" : $_POST['num_licence'];

  $userDepartements = $_POST['userDepartements'] ?? null;
  $userRoles = $_POST['userRoles'] ?? null;
  $finContrat = !isValidDate($_POST['finContrat']) ? "null" : $_POST['finContrat'];

  $resetPassword =  $_POST['resetPassword'] ?? null;
  $nrows = 0;
  $defaultPassword = "changepassword";
  $msg = "";

  if ($typeSend == "add")
    $sql = "SELECT username FROM users WHERE UPPER(username)='" . strtoupper($username) . "' ";
  else if ($typeSend == "edit")
    $sql = "SELECT username FROM users WHERE UPPER(username)='" . strtoupper($username) . "' AND id_user <> $idUserToEdit";

  $msg .= $typeSend . "\n";
  $msg .= $sql . "\n";

  $conn->exec($sql);
  $nrows = $conn->rows;

  if ($nrows > 0) {
    echo json_encode(['success' => false, 'message' => 'userexist']);
  } else {

    $sql1 = "";
    $sql2 = "";

    if ($typeSend == "add") {
      $sql1 = "INSERT INTO users (username, password, valide_au) VALUES ('" . $username . "', '" . md5($defaultPassword) . "', null); ";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);
      $idUserToEdit = mysqli_insert_id($conn->link_id);
      $msg .= "idUserToEdit = " . $idUserToEdit . "\n";
      $nrows = $conn->rows;
    } else {
      $nrows = 0;
    }

    if ($nrows >= 0) {

      if ($resetPassword == 1) {
        $sql2 = 'UPDATE users SET username="' . $username . '", password="' . md5($defaultPassword) . '", lastname="' . $lastname . '", firstname="' . $firstname . '", 
        email="' . $email . '", comment="' . $comment . '", valide_au=' . $finContrat . ', num_licence="' . $num_licence . '" WHERE id_user = "' . $idUserToEdit . '" ';
      } else {
        $sql2 = 'UPDATE users SET username="' . $username . '", lastname="' . $lastname . '", firstname="' . $firstname . '", email="' . $email . '", comment="' . $comment . '", 
        valide_au=' . $finContrat . ', num_licence="' . $num_licence . '" WHERE id_user = "' . $idUserToEdit . '" ';
      }

      $conn->exec($sql2);
      $msg .= $sql2 . "\n";
    }

    $sqlDepartement = "DELETE FROM departement_user WHERE id_user = $idUserToEdit";
    $msg .= $sqlDepartement . "\n";
    $conn->exec($sqlDepartement);


    if (!empty($userDepartements)) {
      foreach ($userDepartements as $departement) {
        $sqlDepartement = "INSERT INTO departement_user (id_user,id_departement) VALUES($idUserToEdit, $departement)";
        $msg .= $sqlDepartement . "\n";
        $conn->exec($sqlDepartement);
      }
    }

    $sql = "DELETE FROM users_roles WHERE id_user=$idUserToEdit";
    $msg .= $sql . "\n";
    $conn->exec($sql);

    if (!empty($userRoles)) {
      foreach ($userRoles as $role) {
        $sqlRoles = "INSERT INTO users_roles (id_user, id_role) VALUES ($idUserToEdit, $role)";
        $msg .= $sqlRoles . "\n";
        $conn->exec($sqlRoles);
      }
    }

    echo json_encode(['success' => true, 'message' => 'ok']);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'deleteUser') {

  $action = $_POST['action'] ?? null;
  $idUserToDelete = $_POST['idUserToDelete'];

  $sql = "DELETE FROM users WHERE id_user = " . $idUserToDelete;
  if ($conn->exec($sql)) {

    $sqlDepartement = "DELETE FROM departement_user WHERE id_user = $idUserToDelete";
    $conn->exec($sqlDepartement);

    $sql = "DELETE FROM users_roles WHERE id_user=$idUserToDelete";
    $conn->exec($sql);

    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'errorsupr']);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'refreshStatutVerrouillage') {

  $idUser = $_POST["idUser"];
  $answer = $_POST["answer"];

  if ($answer == "yes") {
    $SqlUpdateVerif = "UPDATE users SET locked = 1  WHERE id_user = " . $idUser;
  } else {
    $SqlUpdateVerif = "UPDATE users SET locked = 0  WHERE id_user = " . $idUser;
  }

  //echo $SqlUpdateVerif;
  if ($conn->execQuery($SqlUpdateVerif)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur suppression utilisateur Num : ' . $idUser]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getRoleDetails') {

  $idRole = intval($_POST['idRole']); // Ensure the ID is an integer

  try {

    $sqlRole = "SELECT id_role, role_name, role_comment FROM roles WHERE id_role = $idRole";

    $json = array();
    $jsonrolepage = array();
    $dataaccess = array();


    if ($conn->exec($sqlRole)) {
      $result = $conn->data;
      foreach ($result as $row) {
        array_push($json, array(
          'id_role' => $row['id_role'],
          'role_name' => $row['role_name'],
          'role_comment' => $row['role_comment']
        ));
      }

      $sql2 = "SELECT id_url FROM roles_pages WHERE id_role = $idRole";
      $conn->exec($sql2);
      $result1 = $conn->data;

      foreach ($result1 as $row) {
        array_push($jsonrolepage, array(
          'id_url' =>  $row['id_url']
        ));
      }

      $sql1 = "SELECT * FROM acces_utilisateurs WHERE id_role = $idRole";
      //echo $sql1;
      $conn->exec($sql1);
      $result2 = $conn->data;

      foreach ($result2 as $row) {
        array_push($dataaccess, array(
          'id_role' =>  $row['id_role'],
          'voir_tdb' =>  $row['voir_tdb'],
          'liste_fournisseurs' =>  $row['liste_fournisseurs'],
          'ajout_fournisseur' =>  $row['ajout_fournisseur'],
          'mod_fournisseur' =>  $row['mod_fournisseur'],
          'supp_fournisseur' =>  $row['supp_fournisseur'],
          'liste_visiteurs' =>  $row['liste_visiteurs'],
          'ajout_visiteur' =>  $row['ajout_visiteur'],
          'mod_visiteur' =>  $row['mod_visiteur'],
          'supp_visiteur' =>  $row['supp_visiteur'],
          'liste_visites_visiteurs' =>  $row['liste_visites_visiteurs'],
          'ajout_visites_visiteurs' =>  $row['ajout_visites_visiteurs'],
          'mod_visites_visiteurs' =>  $row['mod_visites_visiteurs'],
          'supp_visites_visiteurs' =>  $row['supp_visites_visiteurs'],
          'imp_visites_visiteurs' =>  $row['imp_visites_visiteurs'],
          'parametres' =>  $row['parametres'],
          'liste_utilisateurs' =>  $row['liste_utilisateurs'],
          'ajout_utilisateur' =>  $row['ajout_utilisateur'],
          'mod_utilisateur' =>  $row['mod_utilisateur'],
          'supp_utilisateur' =>  $row['supp_utilisateur']
        ));
      }

      echo json_encode(['success' => true, 'data' => $json, 'datapagerole' => $jsonrolepage, 'dataaccess' => $dataaccess]);
    } else {
      echo json_encode(['success' => false, 'message' => "Erreur"]);
    }
  } catch (Exception $e) {
    // Handle any errors
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addEditRole') {

  //$idRole = intval($_POST['idRole']); // Ensure the ID is an integer
  $idRoleToEdit = $_POST['idRoleToEdit'];
  $typeSend = $_POST['typeSend'];
  $roleName = addslashes(trim($_POST['roleName'])) ?? null;
  $roleComment = addslashes(trim($_POST['roleComment'])) ?? null;
  $rolePages = $_POST['rolePages'] ?? null;
  $msg = "";


  $voir_tdb = isset($_POST['voir_tdb']) ? 1 : 0;

  $liste_fournisseurs = isset($_POST['liste_fournisseurs']) ? 1 : 0;
  $ajout_fournisseur = isset($_POST['ajout_fournisseur']) ? 1 : 0;
  $mod_fournisseur = isset($_POST['mod_fournisseur']) ? 1 : 0;
  $supp_fournisseur = isset($_POST['supp_fournisseur']) ? 1 : 0;

  $liste_visiteurs = isset($_POST['liste_visiteurs']) ? 1 : 0;
  $ajout_visiteur = isset($_POST['ajout_visiteur']) ? 1 : 0;
  $mod_visiteur = isset($_POST['mod_visiteur']) ? 1 : 0;
  $supp_visiteur = isset($_POST['supp_visiteur']) ? 1 : 0;

  $liste_visites_visiteurs = isset($_POST['liste_visites_visiteurs']) ? 1 : 0;
  $ajout_visites_visiteurs = isset($_POST['ajout_visites_visiteurs']) ? 1 : 0;
  $mod_visites_visiteurs = isset($_POST['mod_visites_visiteurs']) ? 1 : 0;
  $supp_visites_visiteurs = isset($_POST['supp_visites_visiteurs']) ? 1 : 0;
  $imp_visites_visiteurs = isset($_POST['imp_visites_visiteurs']) ? 1 : 0;

  $parametres = isset($_POST['parametres']) ? 1 : 0;
  $liste_utilisateurs = isset($_POST['liste_utilisateurs']) ? 1 : 0;
  $ajout_utilisateur = isset($_POST['ajout_utilisateur']) ? 1 : 0;
  $mod_utilisateur = isset($_POST['mod_utilisateur']) ? 1 : 0;
  $supp_utilisateur = isset($_POST['supp_utilisateur']) ? 1 : 0;


  if ($typeSend == "add") {
    $sql = "SELECT role_name FROM roles WHERE UPPER(role_name)='" . strtoupper($roleName) . "'; ";
  } else if ($typeSend == "edit") {
    $sql = "SELECT role_name FROM roles WHERE UPPER(role_name)='" . strtoupper($roleName) . "' AND id_role <> " . $idRoleToEdit . ";";
  }

  $msg .= $typeSend . "\n";
  $msg .= $sql . "\n";

  $conn->exec($sql);
  $nrows = $conn->rows;

  if ($nrows > 0) {
    echo json_encode(['success' => false, 'message' => 'roleexist', 'msg' => $msg]);
  } else {
    if ($typeSend == "add") {
      $sql1 = "INSERT INTO roles (role_name, role_comment) VALUES ('" . $roleName . "', '" . $roleComment . "' ); ";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);
      $idRoleToEdit = mysqli_insert_id($conn->link_id);
      $msg .= "idRoleToEdit = " . $idRoleToEdit . "\n";
      $nrows = $conn->rows;



      $sql = "INSERT INTO `acces_utilisateurs` (`id_role`, `voir_tdb`, `liste_fournisseurs`, `ajout_fournisseur`, `mod_fournisseur`, 
      `supp_fournisseur`, `liste_visiteurs`, `ajout_visiteur`, `mod_visiteur`, `supp_visiteur`, `liste_visites_visiteurs`, `ajout_visites_visiteurs`, `mod_visites_visiteurs`,
      `supp_visites_visiteurs`, `imp_visites_visiteurs`, `parametres`, `liste_utilisateurs`, `ajout_utilisateur`, `mod_utilisateur`, `supp_utilisateur`)
      VALUES 
      ('$idRoleToEdit','$voir_tdb','$liste_fournisseurs','$ajout_fournisseur','$mod_fournisseur','$supp_fournisseur','$liste_visiteurs','$ajout_visiteur','$mod_visiteur','$supp_visiteur',
      '$liste_visites_visiteurs','$ajout_visites_visiteurs','$mod_visites_visiteurs','$supp_visites_visiteurs','$imp_visites_visiteurs',
      '$parametres','$liste_utilisateurs','$ajout_utilisateur','$mod_utilisateur','$supp_utilisateur');";
      $msg .= $sql . "\n";
      $conn->exec($sql);
    } else {
      $sql1 = "UPDATE roles SET role_name='" . $roleName . "',role_comment='" . $roleComment . "' WHERE id_role = '" . $idRoleToEdit . "' ;";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);


      $sql1 = "UPDATE acces_utilisateurs 
      SET 
      id_role = " . $idRoleToEdit . ",
      voir_tdb = " . $voir_tdb . ",
      liste_fournisseurs = " . $liste_fournisseurs . ",
      ajout_fournisseur = " . $ajout_fournisseur . ",
      mod_fournisseur = " . $mod_fournisseur . ",
      supp_fournisseur = " . $supp_fournisseur . ",
      liste_visiteurs = " . $liste_visiteurs . ",
      ajout_visiteur = " . $ajout_visiteur . ",
      mod_visiteur = " . $mod_visiteur . ",
      supp_visiteur = " . $supp_visiteur . ",
      liste_visites_visiteurs = " . $liste_visites_visiteurs . ",
      ajout_visites_visiteurs = " . $ajout_visites_visiteurs . ",
      mod_visites_visiteurs = " . $mod_visites_visiteurs . ",
      supp_visites_visiteurs = " . $supp_visites_visiteurs . ",
      imp_visites_visiteurs = " . $imp_visites_visiteurs . ",
      parametres = " . $parametres . ",
      liste_utilisateurs = " . $liste_utilisateurs . ",
      ajout_utilisateur = " . $ajout_utilisateur . ",
      mod_utilisateur = " . $mod_utilisateur . ",
      supp_utilisateur = " . $supp_utilisateur . "
      WHERE id_role = '" . $idRoleToEdit . "' ;";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);
    }

    $sqlPage = "DELETE FROM roles_pages WHERE id_role = $idRoleToEdit";
    $msg .= $sqlPage . "\n";
    $conn->exec($sqlPage);
    if (!empty($rolePages)) {
      foreach ($rolePages as $page) {
        $sqlRoles = "INSERT INTO roles_pages(id_role, id_url) VALUES ('" . $idRoleToEdit . "','" . $page . "');";
        $msg .= $sqlRoles . "\n";
        $conn->exec($sqlRoles);
      }
    }

    echo json_encode(['success' => true, 'message' => $msg]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'deleteRole') {

  $action = $_POST['action'] ?? null;
  $idRoleToDelete = $_POST['idRoleToDelete'];

  $sql = "DELETE FROM users WHERE id_user = " . $idRoleToDelete;
  if ($conn->exec($sql)) {

    $sqlDepartement = "DELETE FROM departement_user WHERE id_user = $idRoleToDelete";
    $conn->exec($sqlDepartement);

    $sql = "DELETE FROM users_roles WHERE id_user=$idRoleToDelete";
    $conn->exec($sql);

    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'errorsupr']);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'getDepartementDetails') {

  $idDepartement = intval($_POST['idDepartement']); // Ensure the ID is an integer

  try {

    $sqlDepartement = "SELECT * FROM departements WHERE id_departement = $idDepartement";

    $json = array();
    $jsondepartementpage = array();
    $dataaccess = array();


    if ($conn->exec($sqlDepartement)) {
      $result = $conn->data;
      foreach ($result as $row) {
        array_push($json, array(
          'id_departement' => $row['id_departement'],
          'nom_departement' => $row['nom_departement'],
          'commentaire' => $row['commentaire']
        ));
      }

      echo json_encode(['success' => true, 'data' => $json]);
    } else {
      echo json_encode(['success' => false, 'message' => "Erreur"]);
    }
  } catch (Exception $e) {
    // Handle any errors
    echo json_encode(['success' => false, 'message' => 'Error fetching data: ' . $e->getMessage()]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'addEditDepartement') {

  $idDepartementToEdit = $_POST['idDepartementToEdit'];
  $typeSend = $_POST['typeSend'];
  $departementName = addslashes(trim($_POST['departementName'])) ?? null;
  $departementComment = addslashes(trim($_POST['departementComment'])) ?? null;
  $departementPages = $_POST['departementPages'] ?? null;
  $msg = "";


  if ($typeSend == "add") {
    $sql = "SELECT nom_departement FROM departements WHERE UPPER(nom_departement)='" . strtoupper($departementName) . "'; ";
  } else if ($typeSend == "edit") {
    $sql = "SELECT nom_departement FROM departements WHERE UPPER(nom_departement)='" . strtoupper($departementName) . "' AND id_departement <> " . $idDepartementToEdit . ";";
  }

  $msg .= $typeSend . "\n";
  $msg .= $sql . "\n";

  $conn->exec($sql);
  $nrows = $conn->rows;

  if ($nrows > 0) {
    echo json_encode(['success' => false, 'message' => 'departementexist', 'msg' => $msg]);
  } else {
    if ($typeSend == "add") {
      $sql1 = "INSERT INTO departements (nom_departement, commentaire) VALUES ('" . $departementName . "', '" . $departementComment . "' ); ";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);
      $idDepartementToEdit = mysqli_insert_id($conn->link_id);
      $msg .= "idDepartementToEdit = " . $idDepartementToEdit . "\n";
      $nrows = $conn->rows;
    } else {
      $sql1 = "UPDATE departements SET nom_departement='" . $departementName . "',commentaire='" . $departementComment . "' WHERE id_departement = '" . $idDepartementToEdit . "' ;";
      $msg .= $sql1 . "\n";
      $conn->exec($sql1);
    }
    echo json_encode(['success' => true, 'message' => $msg]);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'deleteDepartement') {

  $action = $_POST['action'] ?? null;
  $idDepartementToDelete = $_POST['idDepartementToDelete'];

  $sql = "DELETE FROM departements WHERE id_departement = " . $idDepartementToDelete;

  if ($conn->exec($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'errorsupr']);
  }
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($data['action']) && $data['action'] == 'getListUser') {


  header("Content-Type: application/json");
  $jsonListeUser = array();
  $draw = isset($_POST['draw']) ? intval($_POST['draw']) : 1;

  $sqlListeUser =  "SELECT SQL_CALC_FOUND_ROWS vv.id_visite, COUNT(vv.id_visite) AS N, vv.date_debut, vv.heure_debut, vv.date_fin, vv.heure_fin, vv.secteur, vv.contact, vv.observation,
  v.id_visiteur, v.id_fournisseur, v.civilite, v.nom, v.prenom, CONCAT(v.civilite, ' ', v.nom, ' ', v.prenom) AS nom_complet, f.id_fournisseur, f.nom_compagnie, f.email, f.tel, sav.nom_secteur, cav.nom_contact
  FROM visites_visiteurs vv
  LEFT JOIN visiteurs v ON vv.id_visiteur =   v.id_visiteur
  LEFT JOIN fournisseur f ON v.id_fournisseur = f.id_fournisseur 
  LEFT JOIN secteur_a_visiter sav ON vv.secteur = sav.id_secteur 
  LEFT JOIN contact_a_visiter cav ON vv.contact = cav.id_contact 
  WHERE vv.deleted = 0
  GROUP BY vv.id_visite 
  ORDER BY id_visite DESC";


  if ($conn->exec($sqlListeUser)) {
    $result = $conn->data;
    foreach ($result as $rowlisteUser) {
      array_push($jsonListeUser, array(
        'id_visite' => $rowlisteUser['id_visite'],
        'nom_complet' => $rowlisteUser['nom_complet'],
        'civilite' => $rowlisteUser['civilite'],
        'nom' => $rowlisteUser['nom'],
        'prenom' => $rowlisteUser['prenom'],
        'nom_compagnie' => $rowlisteUser['nom_compagnie'],
        'date_debut' => $rowlisteUser['date_debut'],
        'date_fin' => $rowlisteUser['date_fin'],
        'heure_debut' => $rowlisteUser['heure_debut'],
        'heure_fin' => $rowlisteUser['heure_fin'],
        'nom_secteur' => $rowlisteUser['nom_secteur'],
        'nom_contact' => $rowlisteUser['nom_contact'],
        'observation' => $rowlisteUser['observation'],
        'action' => ''
      ));
    }
  }

  //$sql2 = "SELECT FOUND_ROWS() AS N";
  //$conn->exec($sql2);
  $N = 71;

  echo json_encode([
    'draw' => $draw,
    'recordsTotal' => $N,
    'recordsFiltered' => $N,
    'data' => $jsonListeUser
  ]);
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] == 'sendSuggestion') {

  $userId = $_POST['userId'];
  $subject = $_POST['subject'];
  $message = strtoupper(addslashes(trim($_POST['message'])));

  $sql = "INSERT INTO `suggestions`(`id_user`, `suggestion_text`, `user_comment`, `analyst_comment`) 
  VALUES 
  ('" . $userId . "', '" . $subject . "', '" . $message . "', '' ); ";


  if ($conn->exec($sql)) {
    echo json_encode(['success' => true, 'message' => '']);
  } else {
    echo json_encode(['success' => false, 'message' => 'Erreur enregistrement']);
  }
} else {

  $searchTerm = $_GET['q'] ?? '';
  $searchField = $_GET['type'] ?? '';
  $searchCompagnie = $_GET['compagnie'] ?? '';

  $json = array();
  $searchTerm = addslashes('%' . $searchTerm . '%');
  //echo $searchField;
  try {
    if ($searchField === 'companyName') {
      $sql = "SELECT id_fournisseur, nom_compagnie, activite FROM fournisseur WHERE nom_compagnie LIKE '%$searchTerm%' GROUP BY nom_compagnie ORDER BY nom_compagnie ASC";
      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array('id_fournisseur' => $row['id_fournisseur'], 'nom_compagnie' => $row['nom_compagnie'], 'activite' => $row['activite']));
      }
    } elseif ($searchField === 'champnom') {
      $sql = "SELECT id_visiteur, id_fournisseur, nom FROM visiteurs WHERE nom LIKE '%$searchTerm%' ";
      //if (isset($searchCompagnie) && $searchCompagnie != '' && $searchCompagnie != '0')
      //$sql .= " AND id_fournisseur = " . $searchCompagnie;

      $sql .= " GROUP BY nom ORDER BY nom ASC ";

      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array('id_visiteur' => $row['id_visiteur'], 'id_fournisseur' => $row['id_fournisseur'], 'nom' => $row['nom']));
      }
    } elseif ($searchField === 'champprenom') {
      $sql = "SELECT id_visiteur, id_fournisseur, prenom FROM visiteurs WHERE prenom LIKE '%$searchTerm%' GROUP BY prenom ORDER BY prenom ASC";
      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array('prenom' => $row['prenom']));
      }
    } elseif ($searchField === 'champsecteur') {
      $sql = "SELECT id_secteur, nom_secteur FROM secteur_a_visiter WHERE nom_secteur LIKE '%$searchTerm%' GROUP BY nom_secteur ORDER BY nom_secteur ASC ";
      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array('id_secteur' => $row['id_secteur'], 'nom_secteur' => $row['nom_secteur']));
      }
    } elseif ($searchField === 'champcontactPerson') {
      $sql = "SELECT id_contact, nom_contact FROM contact_a_visiter WHERE nom_contact LIKE '%$searchTerm%' GROUP BY nom_contact ORDER BY nom_contact ASC ";
      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array('id_contact' => $row['id_contact'], 'nom_contact' => $row['nom_contact']));
      }
    } elseif ($searchField === 'champVisiteur') {
      $sql = "SELECT id_visiteur, id_fournisseur, civilite, nom, prenom, CONCAT(nom, ' ', prenom) AS nom_complet 
      FROM visiteurs WHERE (CONCAT(nom, ' ', prenom) LIKE '%$searchTerm%' OR CONCAT(prenom, ' ', nom) LIKE '%$searchTerm%' )";

      if (isset($searchCompagnie) && $searchCompagnie != '' && $searchCompagnie != '0')
        $sql .= " AND id_fournisseur = " . $searchCompagnie;

      $sql .= " GROUP BY  CONCAT(nom, ' ', prenom)";
      $sql .= " ORDER BY  CONCAT(nom, ' ', prenom) ASC";

      $conn->exec($sql);
      $result = $conn->data;
      $json = array();

      foreach ($result as $row) {
        array_push($json, array(
          'id_visiteur' => $row['id_visiteur'],
          'id_fournisseur' => $row['id_fournisseur'],
          'civilite' => $row['civilite'],
          'nom' => $row['nom'],
          'prenom' => $row['prenom'],
          'nom_complet' => $row['nom_complet']
        ));
      }
    } elseif ($searchField === 'champDepartement') {
      $sqldep = "SELECT id_departement, nom_departement FROM departements ORDER BY nom_departement ASC";
      if ($conn->exec($sqldep)) {
        $json = array();
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($json, array(
            'id_departement' => $rowdep['id_departement'],
            'nom_departement' => $rowdep['nom_departement']
          ));
        }
      }
    } elseif ($searchField === 'champUserRole') {
      $sqlRole = "SELECT id_role, role_name FROM roles ORDER BY role_name ASC";
      if ($conn->exec($sqlRole)) {
        $json = array();
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($json, array(
            'id_role' => $rowdep['id_role'],
            'role_name' => $rowdep['role_name']
          ));
        }
      }
    } elseif ($searchField === 'champDepartementAndRole') {
      $jsondep = array();
      $jsonRole = array();
      $sqldep = "SELECT id_departement, nom_departement FROM departements ORDER BY nom_departement ASC";
      if ($conn->exec($sqldep)) {
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($jsondep, array(
            'id_departement' => $rowdep['id_departement'],
            'nom_departement' => $rowdep['nom_departement']
          ));
        }
      }

      $sqlRole = "SELECT id_role, role_name FROM roles ORDER BY role_name ASC";
      if ($conn->exec($sqlRole)) {
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($jsonRole, array(
            'id_role' => $rowdep['id_role'],
            'role_name' => $rowdep['role_name']
          ));
        }
      }
    } elseif ($searchField === 'champListePage') {
      $sqlPage = "SELECT id_url, nom_url FROM pages ORDER BY nom_url ASC";
      if ($conn->exec($sqlPage)) {
        $json = array();
        $result = $conn->data;
        foreach ($result as $rowdep) {
          array_push($json, array(
            'id_url' => $rowdep['id_url'],
            'nom_url' => $rowdep['nom_url']
          ));
        }
      }
    }

    if ($searchField === 'champDepartementAndRole') {
      echo json_encode(['success' => true, 'datadep' => $jsondep, 'datarole' => $jsonRole]);
    } else {
      echo json_encode($json);
    }
  } catch (PDOException $e) {
    // In case of error, return an error message in JSON format
    $json = array('error' => 'Database error: ' . $e->getMessage());
  }
  //echo $searchField;
}
