<?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
 */
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) {
    // STUDENT 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) {
     // TODO: logic to figure out if available or not.
    // TODO: For loop logic for all matches.
    $db = connect();
    $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 %:search%");
    $stmt->bindValue(':search', $address, PDO::PARAM_STR);
    $stmt->execute();

$results = array(
        array('bayID'=>954673, 'site'=> 'Sydney Uni Camp1', 'address'=> 'Search Add1', 'avail'=>true),
        array('bayID'=>344578, 'site'=> 'Sydney Uni Camp2', 'address'=> 'Search Add2', 'avail'=>false)
    );
    return $results;
}

/**
 * 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)
    // 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: Do we have to get owner name or just id?
    // TODO: Join with Member for PrefBay.
    $db = connect();
    $stmt = $db->prepare("SELECT
        bayId, owner, gps_lat, gps_long, address, width, height, length, pod, site, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
        FROM PeerPark.ParkBay
        WHERE bayID=0");
    $stmt->execute();
    $results = array(
        array('bayID'=>896541, 'address'=> 'Glebe Point Road', 'site'=> 'Library', 'avail'=>true)
    );
    return $results;
}


/**
 * 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) {
    // STUDENT TODO:
    // 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.
    $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: Query - needs to insert and to write.
    // TODO: Fill 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(
        'status'=>'success',
        'bookingID'=>2,
        'bayID'=>2,
        'car'=>'Garry the Getz',
        'bookingDate'=>'25/07/14',
        'bookingHour'=>10,
        'duration'=>1,
        '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)
    // 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) {
    $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;
}

?>