<?php
/**
 * Database functions. You need to modify each of these to interact with the database and return appropriate results.
 */

/**
 * Connect to database
 * This function does not need to be edited - just update config.ini with your own
 * database connection details.
 * @param string $file Location of configuration data
 * @return PDO database object
 * @throws exception
 */
function connect($file = 'config.ini') {
    // read database seetings from config file
    if ( !$settings = parse_ini_file($file, TRUE) )
        throw new exception('Unable to open ' . $file);

    // parse contents of config.ini
    $dns = $settings['database']['driver'] . ':' .
            'host=' . $settings['database']['host'] .
            ((!empty($settings['database']['port'])) ? (';port=' . $settings['database']['port']) : '') .
            ';dbname=' . $settings['database']['schema'];
    $user= $settings['db_user']['username'];
    $pw  = $settings['db_user']['password'];

    // create new database connection
    try {
        $dbh=new PDO($dns, $user, $pw);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        print "Error Connecting to Database: " . $e->getMessage() . "<br/>";
        die();
    }
    return $dbh;
}

/**
 * Check login details
 * @param string $name Login name
 * @param string $pass Password
 * @return boolean True is login details are correct
 */
// FINISHED.
function checkLogin($name,$pass) {
    $db = connect();
    try {
        $hashed = md5($pass);
        $hashed = $pass;
        $stmt = $db->prepare('SELECT password FROM PeerPark.Member WHERE email=:username');
        $stmt->bindValue(':username', $name, PDO::PARAM_STR);
        $stmt->execute();
        $result = $stmt->fetchColumn();
        $stmt->closeCursor();
        if($result) {
            if($result == $pass) {
                return True;
            }
        }
        return False;

    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
    return False;
}

/**
 * Get details of the current user
 * @param string $user login name user
 * @return array Details of user - see index.php
 */
function getUserDetails($user) {
    // HALF DONE
    // TODO:
    // JOIN WITH BAY AND BILLING FOR NAME.
    // WHICH NAME?
    // MEMBERNO DOESNT WORK?

    $db = connect();
    try {
    $stmt = $db->prepare("SELECT memberNo AS memberNo, nickName AS name,
        adrStreetNo || ' ' ||  adrStreet || ', ' || adrCity AS address,
        email,
        prefBillingNo, 'lol' as prefBillingName,
        prefBay, 'bay' as prefBayName,
        stat_nrOfBookings AS nbookings
        FROM PeerPark.Member
        WHERE email=:username");
    $stmt->bindValue(':username', $user, PDO::PARAM_STR);
    $stmt->execute();
    } catch (PDOException $e) {
        print "Error in getting user details " . $e->getMessage();
        return False;
    }



    $results = $stmt->fetch();
    // Example user data - this should come from a query

    //$results['memberNo'] = 32;
    /*$results['name'] = $stmt->fetchColumn();
    $results['address'] = $stmt->fetchColumn();
    $results['email'] = $stmt->fetchColumn();
    $results['prefBillingNo'] = $stmt->fetchColumn();
    $results['prefBillingName'] = $stmt->fetchColumn();
    $results['prefBay'] = $stmt->fetchColumn();
    $results['prefBayName'] = $stmt->fetchColumn();
    $results['nbookings'] = $stmt->fetchColumn();*/

    $stmt->closeCursor();

    return $results;
}

/**
 * Get list of bays with silimar address
 * @param string $address address to be look up
 * @return array Various details of each bay - see baylist.php
 */
function searchBay($address) {
    // QUERY DONE
    // TODO: logic to figure out if available or not.
    $db = connect();
    try {
         $stmt = $db->prepare("SELECT
            bayId, site, address, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
            FROM PeerPark.ParkBay
            WHERE address LIKE '%' || :address || '%'");
        $stmt->bindValue(':address', $address, PDO::PARAM_STR);
        $stmt->execute();

        // Fill up the array row by row.
        $result = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            array_push($result, array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true));
        }
        $stmt->closeCursor();
        return $result;
    }
    catch (PDOException $e) {
        print "Error searching bays " . $e->getMessage();
        return False;
    }

}

/**
 * Retrieve information of all bays
  * @return array Various details of each bay - see baylist.php
 * @throws Exception
 */

function getBays() {
    // Example booking info - this should come from a query. Format is
    // (bay ID, site, address, availability of the bay)
    // MOSTLY DONE
    // TODO: LOGIC For availability.
    $db = connect();
    try{
        $stmt = $db->prepare("SELECT
            bayId, site, address, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
            FROM PeerPark.ParkBay");
        $stmt->execute();

        // Fill up the array row by row.
        $result = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            array_push($result, array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true));
        }
        $stmt->closeCursor();
        return $result;
    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
}


/**
 * Retrieve information on bays
 * @param string $memberNo ID of the member
 * @return array  details of the member preferred bay - see baylist.php
 * @throws Exception
 */

function getPrefBayInformation($memberNo) {
    // Replace lines below with code to get the information about the owner preferred bay from the database
    // Example bay info - this should come from a query. Format is
    // (bay ID, Owner, Latitude, Longitude, Address,  width, height, length, pod, site, week start, week end, weekend start, weekend end)
    // TODO: Nearly done.
    // TODO: Do we have to get owner name or just id?
    // TODO: Join with Member for PrefBay.
    $db = connect();
    try {
        $stmt = $db->prepare("SELECT
            bayID, address, pod, site, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
            FROM PeerPark.ParkBay
            JOIN PeerPark.Member ON PeerPark.ParkBay.bayID = PeerPark.Member.prefBay
            WHERE PeerPark.Member.email=:member");
        $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
        $stmt->execute();
        $results = array();

        $results = $stmt->fetch(PDO::FETCH_ASSOC);
        //$results = array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true);

        return $results;
    }
    catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
    return false;
}


/**
 * Retrieve information on bays
 * @param string $BayID ID of the bay
 * @return array Various details of the bay - see baydetail.php
 * @throws Exception
 */

function getBayInformation($BayID) {
    // TODO:
    // Check right data is in query.
    // Fill array
    // NOT DONE.
    // Replace lines below with code to get the information about a specific bay from the database
    // Example bay info - this should come from a query. Format is
    // (bay ID, Owner, Latitude, Longitude, Address,  width, height, length, pod, site, week start, week end, weekend start, weekend end)
    $db = connect();
    try {
        $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
        $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
        $stmt->execute();
        $stmt->closeCursor();
        return
        array('bayID'=>954673,'site'=>'Glebe Public School 1', 'owner'=>'Toni Collette', 'address'=> '25 Glebe Point Road','description'=>'Bay located next to Glebe Public School','gps_lat'=>456,'gps_long'=>5689,'locatedAt'=>19,'mapURL'=>'https://www.google.com.au/maps/place/Glebe+Public+School/@-33.8896527,151.186376,16z/data=!4m6!1m3!3m2!1s0x6b12b1d4ab9ef1d9:0x1d017d69037a07c0!2sThe+University+of+Sydney!3m1!1s0x0000000000000000:0x365d99076bb74a6d',
        'width'=> 45,'height'=>56,'length'=>56,'pod'=>45,'avail_wk_start'=>9,'avail_wk_end'=>17,'avail_wend_start'=>2,'avail_wend_end'=>3);


    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
    return False;
}

/**
 * Retrieve information on active bookings for a member
 * @param string $memberNo ID of member
 * @return array Various details of each booking - see bookings.php
 * @throws Exception
 */

function getOpenBookings($memberNo) {
    // Replace lines below with code to get list of bookings from the database
    // Example booking info - this should come from a query. Format is
    // (booking ID,  bay ID, Car Name, Booking start date, booking start time, booking duration)

    // TODO: query. get the bookings for a time past NOW owned by :member. Get the data as above.
    // TODO: fill the array.
    // NOT DONE.
    $db = connect();
    try {
        $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
        $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
        $stmt->execute();
        $stmt->closeCursor();
        $results = array(
        array('bookingID'=>1,'bayLocation'=>'CBD','car'=>'Jenny the Yaris','bookingDate'=>'05/03/15' ),
        array('bookingID'=>2,'bayLocation'=>'Glebe','car'=>'Garry the Getz','bookingDate'=>'11/04/15')
        );
        return $results;
    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
    return False;

}

/**
 * Make a new booking for a bay
 * @param string $memberNo Member booking the bay
 * @param string $car         Name of the car
 * @param string $bayID       ID of the bay to book
 * @param string $bookingDate the date of the booking
 * @param string $bookingHour the time of the booking
 * @param string $duration    the duration of the booking
 * @return array Various details of current visit - see newbooking.php
 * @throws Exception
 */
function makeBooking($memberNo,$car,$bayID,$bookingDate,$bookingHour,$duration) {
    // TODO: TODO: Cost
    // NOT DONE
    $db = connect();
    try {

        $stmt = $db->prepare('INSERT INTO PeerPark.Booking VALUES ((SELECT MAX(bookingID) + 1 FROM PeerPark.Booking), :bayID, :bookingDate, :bookingHour, :duration,
        (SELECT memberNo FROM PeerPark.Member WHERE email=:memberNo),
        :car);');
      $stmt->bindValue(':bayID',$bayID, PDO::PARAM_INT);
      $stmt->bindValue(':bookingDate',$bookingDate, PDO::PARAM_STR);
      $stmt->bindValue(':bookingHour', $bookingHour, PDO::PARAM_INT);
      $stmt->bindValue(':duration',$duration, PDO::PARAM_INT);
      $stmt->bindValue(':memberNo',$memberNo, PDO::PARAM_STR);
      $stmt->bindValue(':car',$car, PDO::PARAM_STR);
        $stmt->execute();
        $stmt = $db->prepare('SELECT MAX(bookingID) as id FROM PeerPark.Booking');
        $stmt->execute();
        $row = $stmt->fetch();

        $bookingID = $row['id'];
        print($bookingID);
        $stmt->closeCursor();
        return array(
        'status'=>'success',
        'bookingID'=>$bookingID,
        'bayID'=>$bayID,
        'car'=>$car,
        'bookingDate'=>$bookingDate,
        'bookingHour'=>$bookingHour,
        'duration'=>$duration,
        'cost'=>1000
         );

    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }

}

/**
 * Retrieve information on the booking
 * @param string $bookingID ID of the bay
 * @return array Various details of the booking - see bookingDetail.php
 * @throws Exception
 */
function getBookingInfo($bookingID) {
    // Replace lines below with code to get the detail about the booking.
    // Example booking info - this should come from a query. Format is
    // (bookingID, bay Location, booking Date, booking Hour, duration, car, member Name)
    // NOT DONE.
    // TODO: The query
    // TODO: Fill the array.
    $db = connect();
    try {
        $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
        $stmt->bindValue(':book', $bookingID, PDO::PARAM_STR);
        $stmt->execute();
        $stmt->closeCursor();
        return array('bookingID'=>1, 'bayLocation'=>'CBD', 'bookingDate'=> '10/05/2015','bookingHour'=>'10:01','duration'=>2,'car'=> 'Harry the Goat','memberName'=>'Uwe');

    } catch (PDOException $e) {
        print "Error checking login: " . $e->getMessage();
        return False;
    }
}

/**
 * Get details of the cars of the member
 * @param string $user ID of member
 * @return Name of the cars owned by the member - see index.php
 */
function getCars($email) {
    // DONE.
    $db = connect();
    try {
        $stmt = $db->prepare("SELECT name FROM PeerPark.Car
            NATURAL JOIN PeerPark.Member
            WHERE email=:email");
        $stmt->bindValue(':email', $email, PDO::PARAM_INT);
        $stmt->execute();
        // Fill up the array row by row.
        $result = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            array_push($result, array('car'=>$row['name']));
        }
        $stmt->closeCursor();

        return $result;

    } catch (PDOException $e) {
        print "Error getting cars";
        return False;
    }
    return false;
}

?>