/database_utils.php

<?php

function getPDO(){
    $db_uname = 'CS1437203';
    $db_pword = 'rfershib';
    $db_src = 'mysql:host=korra.dawsoncollege.qc.ca;dbname=CS1437203';

    return new PDO($db_src, $db_uname, $db_pword);
}

function setup_database() {
    try {
        $pdo = getPDO();

        $sql_create_table_cities = 'CREATE TABLE cities (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, weight INT, city VARCHAR(255))';
        $sql_create_table_users = "CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, uname VARCHAR(255), pword VARCHAR(255), attempts INT DEFAULT 0)";
        $sql_create_table_histories = 'CREATE TABLE histories (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, uname VARCHAR(255), term VARCHAR(255), date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)';

        $pdo->exec($sql_create_table_cities);
        $pdo->exec($sql_create_table_users);
        $pdo->exec($sql_create_table_histories);
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function populate_tables(){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('INSERT INTO cities(weight, city) VALUES(?, ?)');

        echo 'Reading from file and populating, please wait...';

        $cities = fopen("res/cities.txt", "r");
        while(!feof($cities)) {
            $city =  explode(';', fgets($cities));

            $stmt->bindValue(1, trim(preg_replace('/\s+/', ' ', $city[0])));
            $stmt->bindValue(2, trim(preg_replace('/\s+/', ' ', $city[1])));

            $stmt->execute();
        }
        fclose($cities);

        echo 'Populated!';
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function get_cities_matching($pattern){
    try {
        // Get matching terms if any
        $cities = get_terms_matching($pattern);

        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT city FROM cities WHERE city LIKE ? ORDER BY weight LIMIT ?');
        $stmt->bindValue(1, $pattern."%");
        $stmt->bindValue(2, 5 - count($cities), PDO::PARAM_INT); // Limit ammount of returned cities if terms are found

        $stmt->execute();

        while($city = $stmt->fetch()[0]){
            $cities[] = $city;
        }

        return $cities;
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function get_terms_matching($pattern){
  try {
      $pdo = getPDO();

      $stmt = $pdo->prepare('SELECT term FROM histories WHERE uname = ? AND term LIKE ? ORDER BY date DESC');
      $stmt->bindValue(1, $_SESSION['uname']); // Use session var since user should only be able to get here if logged in
      $stmt->bindValue(2, $pattern."%");

      $stmt->execute();

      $terms = [];

      while($term = $stmt->fetch()[0]){
          $terms[] = $term;
      }

      return $terms;
  } catch (PDOException $e) {
      echo $e->getMessage();
  } finally {
      unset($pdo);
  }
}

function create_user($uname, $pword){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('INSERT INTO users(uname, pword) VALUES(?, ?)');
        $stmt->bindValue(1, strip_tags(trim(substr($uname, 0,255))));
        $stmt->bindValue(2, password_hash($pword, PASSWORD_DEFAULT));

        $stmt->execute();
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function is_unique($uname){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT uname FROM users WHERE uname = ?');
        $stmt->bindValue(1, trim(substr($uname, 0,255)));

        $stmt->execute();

        return empty($stmt->fetch()); // Return true if nothing returned
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function increment_attempts($uname){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT attempts FROM users WHERE uname = ?');
        $stmt->bindValue(1, $uname);

        $stmt->execute();

        $attempt = (int)$stmt->fetch()[0] + 1;

        $stmt = $pdo->prepare('UPDATE users SET attempts = ? WHERE uname = ?');
        $stmt->bindValue(1, $attempt);
        $stmt->bindValue(2, $uname);

        $stmt->execute();
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function verify($uname, $pword){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT uname, pword FROM users WHERE uname = ?');
        $stmt->bindValue(1, $uname);

        $stmt->execute();

        if(!password_verify($pword,  $stmt->fetch()[1])){
            increment_attempts($uname);
            return false; // Increment the attempts of specific user
        }

        // Need to reset login attempts to 0 if successful
        $stmt = $pdo->prepare('UPDATE users SET attempts = ? WHERE uname = ?');
        $stmt->bindValue(1, 0);
        $stmt->bindValue(2, $uname);

        $stmt->execute();

        return true;
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function is_locked_out($uname){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT attempts FROM users WHERE uname = ?');
        $stmt->bindValue(1, $uname);

        $stmt->execute();

        return ($stmt->fetch()[0] >= 5);
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function get_history(){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('SELECT term, date FROM histories WHERE uname = ? ORDER BY date DESC');
        $stmt->bindValue(1, $_SESSION['uname']); // Use session var since user should only be able to get here if logged in

        $stmt->execute();

        $terms = [];

        while($term = $stmt->fetch()){
            $terms[] = array($term[0], $term[1]);
        }

        // Cap array if not completely full
        for($i = count($terms); $i < 5; $i++){
            $terms[] = array("", "");
        }

        return $terms;
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function update_history($term){
    try {
      // Remove oldest first
      remove_oldest_term();

      $pdo = getPDO();

      $stmt = $pdo->prepare('INSERT INTO histories(uname, term) VALUES(?, ?)');
      $stmt->bindValue(1, $_SESSION['uname']);
      $stmt->bindValue(2, strip_tags($term));

      $stmt->execute();
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function is_in_history($term){
    try {
      $db_terms = get_history();

      // Check in db for match
      foreach ($db_terms as $db_term) {
          if($db_term[0] === $term){
              return true;
          }
      }

      return false;
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}

function update_term_date($term){
  try {
    $pdo = getPDO();

    $stmt = $pdo->prepare('UPDATE histories SET date = ? WHERE uname = ? AND term = ?');
    $stmt->bindValue(1, date('Y-m-d G:i:s')); // Set new date
    $stmt->bindValue(2, $_SESSION['uname']);
    $stmt->bindValue(3, $term);

    $stmt->execute();
  } catch (PDOException $e) {
      echo $e->getMessage();
  } finally {
      unset($pdo);
  }
}

function remove_oldest_term(){
    try {
        $pdo = getPDO();

        $stmt = $pdo->prepare('DELETE FROM histories WHERE uname = ? AND date = ?');
        $stmt->bindValue(1, $_SESSION['uname']); // Use session var since user should only be able to get here if logged in
        $stmt->bindValue(2, get_history()[4][1]); // Get oldest entry date

        $stmt->execute();
    } catch (PDOException $e) {
        echo $e->getMessage();
    } finally {
        unset($pdo);
    }
}