<?php

if (isset($db))
  return;

class MyDB extends SQLite3 {
  function __construct() {
    parent::__construct('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
  }
  function exec($query) {
    $result = parent::exec($query);
    if (!$result)
      die();
    return $result;
  }
  function query($query) {
    $result = parent::query($query);
    if (!$result)
      die();
    return $result;
  }
  function prepare($query) {
    $result = parent::prepare($query);
    if (!$result)
      die();
    return $result;
  }
  function get_state_id($state) {
    $stm = $this -> prepare('SELECT states.id AS state_id FROM states WHERE states.name = :state');
    $stm -> bindValue('state', $state);
    $result = $stm -> execute();
    if (! $result)
      die();
    $result = $result -> fetchArray();
    if (! $result)
      die();
    return $result['state_id'];
  }
  function add_key($comment, $key = NULL) {
    $stm = $this -> prepare('INSERT INTO keys (key, comment) VALUES (:key, :comment)');
    if ($key == NULL)
      $stm -> bindValue('key', bin2hex(random_bytes(64)));
    else
      $stm -> bindValue('key', $key);
    $stm -> bindValue('comment', $comment);
    $stm -> execute();
    $key_id = $this -> lastInsertRowID();
    $stm -> close();
    return $key_id;
  }
  function get_machine_id($machine) {
    $stm = $this -> prepare('SELECT machines.id AS machine_id FROM machines WHERE machines.name=:name');
    $stm -> bindValue('name', $machine);
    $result = $stm -> execute();
    $result = $result -> fetchArray();
    $stm -> close();

    if (! $result)
      return NULL;
    else
      return $result['machine_id'];
  }
  function get_machines() {
    $result = $this -> query('SELECT machines.id AS machine_id, machines.name AS machine FROM machines');
    $return = array();
    while ($row = $result -> fetchArray())
      $return[] = $row;
    return $return;
  }
  function add_machine($machine) {
    $stm = $this -> prepare('SELECT COUNT(1) FROM machines WHERE machines.name=:name');
    $stm -> bindValue('name', $machine);
    $result = $stm -> execute();
    $result = $result -> fetchArray();
    $stm -> close();

    if ($result[0] == 0) {
      $stm = $this -> prepare('INSERT INTO machines (name) VALUES (:machine)');
      $stm -> bindValue('machine', $machine);
      $stm -> execute();
      $machine_id = $this -> lastInsertRowID();
      $stm -> close();

      $stm = $this -> prepare(
        'INSERT INTO "values" (machine_id, state_id) VALUES (:machine, :state)'
      );
      $stm -> bindValue('machine', $machine_id);
      $result = $this -> query(
        'SELECT states.name, states.id FROM states'
      );
      while ($row = $result -> fetchArray()) {
        if ($row['name'] == 'running')
          $running_id = $row['id'];
        $stm -> bindValue('state', $row['id']);
        $stm -> execute();
      }
      $stm -> close();

      $this -> add_key_for($machine_id, $running_id, 'self');
    }
  }
  function get_value_id_of($machine, $state) {
    if (is_int($machine) && is_int($state)) {
      $stm = $this -> prepare(
        'SELECT "values".id as value_id FROM "values"' .
        ' WHERE "values".state_id = :state_id' .
        ' AND "values".machine_id = :machine_id'
      );
      $stm -> bindValue('state_id', $state);
      $stm -> bindValue('machine_id', $machine);
    } else {
      $stm = $this -> prepare(
        'SELECT "values".id as value_id FROM "values"' .
        ' JOIN states ON states.id = "values".state_id' .
        ' JOIN machines ON machines.id = "values".machine_id' .
        ' WHERE states.name = :state' .
        ' AND machines.name = :machine'
      );
      $stm -> bindValue('state', $state);
      $stm -> bindValue('machine', $machine);
    }
    $result = $stm -> execute();
    if (!$result)
      die();
    $result = $result -> fetchArray();
    if (!$result)
      die();
    $stm -> close();
    return $result['value_id'];
  }
  function add_key_for($machine, $state, $comment) {
    $key_id = $this -> add_key($comment);
    $value_id = $this -> get_value_id_of($machine, $state);
    $stm = $this -> prepare(
      'INSERT INTO permissions (key_id, value_id) VALUES (:key,:value)');
    $stm -> bindValue('value', $value_id);
    $stm -> bindValue('key', $key_id);
    $stm -> execute();
    $stm -> close();
  }
}

$db = new MyDB();
if(!$db) {
  echo $db->lastErrorMsg();
  die();
}