database.php

<?php


enum LogLevel: int {
  case info = 1;
  case warning = 2;
  case error = 3;
}


class Database {
  /** @var  PDO */
  private $pdo;
  public  $countryId = DEFAULT_COUNTRY_ID;
  public  $countries;
  public  $rowCount;

  public function databaseHandle(){
    return $this->pdo;
  }

  /**
   * @throws Exception
   */
  public function open(){
    // To debug on localhost with remote database use port forwarding:
    // ssh -L 3306:localhost:3306 loginname@databaseserver.com
    try {
      $options = [
        // Forces native MySQL prepares. Required before PHP 8.1 to return native fields (integer & float instead of strings)
        // See: https://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not
        PDO::ATTR_EMULATE_PREPARES => false,

        // Unicode support. utf8mb4 also supports emojis, which UTF8 does not.
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",

        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      ];

      $this->pdo = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASSWORD, $options);

    } catch (\Exception $e) {
      throw new Exception('Database error: ' . $e->getMessage());
    }
  }

  public function close(){
    unset($this->pdo);
  }

  public function beginTransaction(){
    $this->pdo->beginTransaction();
  }

  public function rollback(){
    $this->pdo->rollBack();
  }

  public function commit(){
    $this->pdo->commit();
  }

  public function inTransaction(){
    $this->pdo->inTransaction();
  }

  public function fetchAll($sql, $params=null): bool|array {
    $statement = $this->pdo->prepare($sql);
    $statement->execute($params);
    return $statement->fetchAll(PDO::FETCH_ASSOC);
  }

  public function fetchAllValues($sql, $params=null){
    try {
      $statement = $this->pdo->prepare($sql);
      $statement->execute($params);
      return $statement->fetchAll(PDO::FETCH_COLUMN);
    } catch (\Exception $e) {
      return false;
    }
  }

  public function fetchAllGroup($sql, $params=null){
    try {
      $statement = $this->pdo->prepare($sql);
      $statement->execute($params);
      return $statement->fetchAll(PDO::FETCH_GROUP);
    } catch (\Exception $e) {
      return false;
    }
  }

  public function fetchObject($sql, $params=null){
    try {
      $statement = $this->pdo->prepare($sql);
      $statement->execute($params);
      return $statement->fetchObject();
    } catch (\Exception $e) {
      return false;
    }
  }

  public function fetch($sql, $params=null){
    try {
      $statement = $this->pdo->prepare($sql);
      $statement->execute($params);
      return $statement->fetch(PDO::FETCH_ASSOC);
    } catch (\Exception $e) {
      return false;
    }
  }

  public function fetchSingleValue(string $sql, array|null $params=null): mixed{
    $statement = $this->pdo->prepare($sql);
    $statement->execute($params);
    return $statement->fetchColumn();
  }

  public function lastInsertID(): bool|string {
    return $this->pdo->lastInsertId();
  }

  public function execute(string $sql, array|null $params=null, bool $doRowCount=false): bool {
    $statement = $this->pdo->prepare($sql);
    $result = $statement->execute($params);

    if ($doRowCount) $this->rowCount = $statement->rowCount();

    return $result;
  }

  public function prepare($sql): PDOStatement|false {
    return $this->pdo->prepare($sql);
  }

  public function executePrepared(PDOStatement $statement, array|null $params=null, $doRowCount=false): bool {
    $result = $statement->execute($params);
    if ($doRowCount) $this->rowCount = $statement->rowCount();
    return $result;
  }

  public function fetchAllPrepared(PDOStatement $statement, mixed $params=null): array|false {
    $statement->execute($params);
    return $statement->fetchAll(PDO::FETCH_ASSOC);
  }

  public function log(?int $userId, LogLevel $level=LogLevel::info, string $info=''): bool {
    $ip = substr(getCallerIP(), 0, 45);

    $sql = 'INSERT INTO logs (userid, level, ip, info) VALUES (:userId, :level, :ip, :info)';
    $params = [
      ':userId' => $userId,
      ':level' => $level->value,
      ':ip' => $ip,
      ':info' => substr($info, 0, 500),
      ];
    return $this->execute($sql, $params);
  }

  public function loadCountries() {
    if (empty($this->countries)) {
      $dbCountries = $this->fetchAll("SELECT id, name, defaultlanguageid, domain FROM countries ORDER BY id;");
      $this->countries = [];
      foreach ($dbCountries as $country) {
        $flagId = strtolower($country['id']);
        $country['flagFile'] = "/images/flags/{$flagId}.svg";

        // Country id depends on domain name (e.g. nl.roaddanger.org > id=NL)
        if (str_contains($_SERVER['SERVER_NAME'], $country['domain'])) $this->countryId = $country['id'];

        $this->countries[] = $country;
      }
    }

    return $this->countries;
  }

  public function getQuestionnaires($publicOnly=false): bool|array {
    $where = $publicOnly? ' WHERE public=1 ' : '';
    $sql = "SELECT id, type, title, country_id, public, active FROM questionnaires $where ORDER BY title;";
    return $this->fetchAll($sql);
  }

  public function getQuestionnaireCountries() {
    return $this->fetchAllValues("SELECT DISTINCT country_id FROM questionnaires WHERE active=1 ORDER BY id;");
  }

  public function getCountry(string $id) {
    foreach ($this->countries AS $country) {
      if ($country['id'] === $id) return $country;
    }
    return null;
  }

}