<?php
function getAllDepartments()
{
    global $db;
    $query = "SELECT * FROM departments";
    // $db->query()    // compile & exe SQL   -- bad
    $statement = $db->prepare($query);    // compile
    $statement->execute();                // run
    $result = $statement->fetchAll();     // retrieve all rows from running the query
    $statement->closeCursor();
    return $result;
}

function addVisitor($visitDate, $visitorName, $visitorEmail, $visitorPhone, $whomToMeet, $deptId, $hasAppt)
{
    global $db;
    $query = "INSERT INTO visitors (visitDate, visitorName, visitorEmail, visitorPhone, whomToMeet, deptId, hasAppt)" .
            "VALUES (:visitDate, :visitorName, :visitorEmail, :visitorPhone, :whomToMeet, :deptId, :hasAppt)";

     try {
         // $statement = $db->query($query);    // bad: compile & run

         $statement = $db->prepare($query);     // compile, template
         // fill in the value (text)
         $statement->bindValue(':visitDate', $visitDate);
         $statement->bindValue(':visitorName', $visitorName);
         $statement->bindValue(':visitorEmail', $visitorEmail);
         $statement->bindValue(':visitorPhone', $visitorPhone);
         $statement->bindValue(':whomToMeet', $whomToMeet);
         $statement->bindValue(':deptId', $deptId);
         $statement->bindValue(':hasAppt', $hasAppt);

         // exe
         $statement->execute();

         $statement->closeCursor();

     } catch (PDOException $e) {
         $e->getMessage();       // consider using a generic message
     } catch (Exception $e) {
         $e->getMessage();       // consider using a generic message
     }


}

function getAllVisitors()
{
// retrieve all visitors
// return a list of visitors
    global $db;
    $query = "SELECT * FROM visitors";
    $statement = $db->prepare($query);    // compile
    $statement->execute();                // run
    $result = $statement->fetchAll();     // retrieve all rows from running the query
    $statement->closeCursor();
    return $result;
}

function getVisitorById($id)
{
// retrieve the row with id match
   global $db;
   $query = "SELECT * FROM visitors WHERE visitId=:id";
   $statement = $db->prepare($query);    // compile
   $statement->bindValue(':id', $id);
   $statement->execute();
   $result = $statement->fetch();    // retrieve 1 row
   $statement->closeCursor();

   return $result;
}

function updateVisitor($visitId, $visitDate, $visitorName, $visitorEmail, $visitorPhone, $whomToMeet, $deptId, $hasAppt)
{
   global $db;
   $query = "UPDATE visitors SET visitDate=:visitDate, visitorName=:visitorName, visitorEmail=:visitorEmail, visitorPhone=:visitorPhone, whomToMeet=:whomToMeet, deptId=:deptId, hasAppt=:hasAppt WHERE visitId=:visitId";

   $statement = $db->prepare($query);
   $statement->bindValue(':visitId', $visitId);
   $statement->bindValue(':visitDate', $visitDate);
   $statement->bindValue(':visitorName', $visitorName);
   $statement->bindValue(':visitorEmail',$visitorEmail);
   $statement->bindValue(':visitorPhone', $visitorPhone);
   $statement->bindValue(':whomToMeet', $whomToMeet);
   $statement->bindValue(':deptId', $deptId);
   $statement->bindValue(':hasAppt', $hasAppt);

   $statement->execute();
   $statement->closeCursor();

}

function deleteVisitor($id)
{
   global $db;

   $query = "DELETE FROM visitors WHERE visitId=:id" ;

   $statement = $db->prepare($query);
   $statement->bindValue(':id', $id);
   $statement->execute();
   $statement->closeCursor();
}


?>