<?php

const DB_FILENAME = 'beacons.sqlite';
const MAX_AGE = 50 * 3600;
const LIMIT = 100;


class BeaconDB {

    /**
     * @var SQLite3 pdo
     */
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->createSchema();
    }

    function createSchema() {
        $this->pdo->exec("PRAGMA journal_mode=WAL;");
        $this->pdo->exec("CREATE TABLE IF NOT EXISTS beacons (
            net TEXT NOT NULL,
            beacon TEXT UNIQUE NOT NULL,
            date INTEGER NOT NULL
        );");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS index_net_date ON beacons (net ASC, date DESC);");
    }

    public function add(string $beacon) {
        $stmt = $this->pdo->prepare("INSERT OR REPLACE INTO beacons (net, beacon, date) VALUES (:net, :beacon, :date);");
        $net = substr($beacon, 0, 5);
        $stmt->bindValue(':net', $net);
        $stmt->bindValue(':beacon', $beacon);
        $stmt->bindValue(':date', time(), SQLITE3_INTEGER);
        $stmt->execute();
    }

    public function find(string $net) : array {
        $stmt = $this->pdo->prepare("SELECT beacon FROM beacons WHERE net = :net AND date >= :date LIMIT :limit;");
        $stmt->bindValue(':net', $net);
        $stmt->bindValue(':date', time() - MAX_AGE, SQLITE3_INTEGER);
        $stmt->bindValue(':limit', LIMIT, SQLITE3_INTEGER);
        $res = $stmt->execute();
        $beacons = [];
        while ($row = $res->fetchArray()) {
            $beacons[] = $row[0];
        }
        return $beacons;
    }

    public function cleanup() {
        $stmt = $this->pdo->prepare("DELETE FROM beacons WHERE date < :date;");
        $stmt->bindValue(':date', time() - MAX_AGE, SQLITE3_INTEGER);
        $stmt->execute();
    }
}


$pdo = new SQLite3(DB_FILENAME);
$db = new BeaconDB($pdo);
if (isset($_POST['beacon'])) {
    $beacon = $_POST['beacon'];
    $db->add($beacon);
    http_response_code(201);
}
if (isset($_REQUEST['net'])) {
    $net = $_REQUEST['net'];
    $beacons = $db->find($net);
    foreach ($beacons as $beacon) {
        echo "$beacon\n";
    }
}
$db->cleanup();
$pdo->close();

?>