//! opentracker server: one binary, one SQLite file.

mod auth;
mod device;
mod guest;
mod passkeys;
mod routes;

use std::path::PathBuf;
use std::sync::{Arc, Mutex, MutexGuard, PoisonError};
use std::time::{Duration, SystemTime, UNIX_EPOCH};

use axum::http::StatusCode;
use axum::response::{IntoResponse, Response};
use clap::{Parser, Subcommand};
use rusqlite::{Connection, OptionalExtension, params};
use webauthn_rs::prelude::Url;

/// Schema steps, applied in order. `PRAGMA user_version` counts the applied ones.
/// Never edit a released step. Append a new one.
const MIGRATIONS: &[&str] = &[
    // IF NOT EXISTS: databases from before migrations have these tables at version 0.
    "
CREATE TABLE IF NOT EXISTS users (
    id             INTEGER PRIMARY KEY,
    username       TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    -- NULL when the account signs in with passkeys only.
    pw_hash        TEXT,
    is_admin       INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0, 1)),
    two_factor     INTEGER NOT NULL DEFAULT 0 CHECK (two_factor IN (0, 1)),
    -- The WebAuthn user handle. Random, so a passkey does not reveal the user id.
    webauthn_id    TEXT    NOT NULL UNIQUE,
    retention_days INTEGER CHECK (retention_days > 0),
    created_at     INTEGER NOT NULL
) STRICT;

CREATE TABLE IF NOT EXISTS sessions (
    token_hash BLOB    PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at INTEGER NOT NULL
) STRICT;

CREATE TABLE IF NOT EXISTS passkeys (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    cred_id      BLOB    NOT NULL UNIQUE,
    -- webauthn_rs::prelude::Passkey as JSON.
    passkey      TEXT    NOT NULL,
    name         TEXT    NOT NULL,
    created_at   INTEGER NOT NULL,
    last_used_at INTEGER
) STRICT;

CREATE TABLE IF NOT EXISTS devices (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         TEXT    NOT NULL,
    token_hash   BLOB    NOT NULL UNIQUE,
    created_at   INTEGER NOT NULL,
    last_seen_at INTEGER
) STRICT;

-- The key makes uploads idempotent: a retried batch collapses into the rows already there.
CREATE TABLE IF NOT EXISTS points (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ts      INTEGER NOT NULL,
    lat     REAL    NOT NULL,
    lon     REAL    NOT NULL,
    acc     REAL,
    alt     REAL,
    speed   REAL,
    bearing REAL,
    battery INTEGER,
    PRIMARY KEY (user_id, ts)
) STRICT, WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS points_ts ON points(ts);

CREATE TABLE IF NOT EXISTS shares (
    id         INTEGER PRIMARY KEY,
    owner_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    viewer_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at INTEGER,
    created_at INTEGER NOT NULL,
    UNIQUE (owner_id, viewer_id),
    CHECK (owner_id <> viewer_id)
) STRICT;
",
    // Points belong to a device. The web UI uploads as one device per user, which has no token.
    "
CREATE TABLE devices_new (
    id           INTEGER PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         TEXT    NOT NULL,
    -- NULL for the web device.
    token_hash   BLOB    UNIQUE,
    created_at   INTEGER NOT NULL,
    last_seen_at INTEGER
) STRICT;
INSERT INTO devices_new SELECT * FROM devices;
DROP TABLE devices;
ALTER TABLE devices_new RENAME TO devices;
CREATE UNIQUE INDEX devices_web ON devices(user_id) WHERE token_hash IS NULL;

-- Older points go to the device that uploaded last, or to a new web device.
INSERT INTO devices (user_id, name, created_at)
SELECT DISTINCT user_id, 'Web', unixepoch() FROM points
WHERE user_id NOT IN (SELECT user_id FROM devices);

-- The key makes uploads idempotent: a retried batch collapses into the rows already there.
CREATE TABLE points_new (
    device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    ts        INTEGER NOT NULL,
    lat       REAL    NOT NULL,
    lon       REAL    NOT NULL,
    acc       REAL,
    alt       REAL,
    speed     REAL,
    bearing   REAL,
    battery   INTEGER,
    PRIMARY KEY (device_id, ts)
) STRICT, WITHOUT ROWID;
INSERT INTO points_new
SELECT (SELECT d.id FROM devices d WHERE d.user_id = p.user_id
        ORDER BY d.last_seen_at DESC NULLS LAST, d.id LIMIT 1),
       ts, lat, lon, acc, alt, speed, bearing, battery
FROM points p;
DROP TABLE points;
ALTER TABLE points_new RENAME TO points;
CREATE INDEX points_ts ON points(ts);

ALTER TABLE shares ADD COLUMN all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1));
ALTER TABLE shares ADD COLUMN trail INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1));
ALTER TABLE shares ADD COLUMN precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0);
-- Used only when all_devices is 0.
CREATE TABLE share_devices (
    share_id  INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
    device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    PRIMARY KEY (share_id, device_id)
) STRICT, WITHOUT ROWID;
",
    // Trails can start at a time. Guest links are shares without a viewer.
    "
CREATE TABLE shares_new (
    id          INTEGER PRIMARY KEY,
    owner_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- NULL for a guest link.
    viewer_id   INTEGER REFERENCES users(id) ON DELETE CASCADE,
    expires_at  INTEGER,
    created_at  INTEGER NOT NULL,
    all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1)),
    trail       INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1)),
    trail_since INTEGER,
    precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0),
    -- Guest links only. Kept readable, so the owner can copy the link again.
    name        TEXT,
    token       TEXT UNIQUE,
    pw_hash     TEXT,
    UNIQUE (owner_id, viewer_id),
    CHECK (owner_id <> viewer_id),
    CHECK ((viewer_id IS NULL) = (token IS NOT NULL))
) STRICT;
INSERT INTO shares_new (id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m)
SELECT id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m FROM shares;
DROP TABLE shares;
ALTER TABLE shares_new RENAME TO shares;
",
    // One column for the trail: NULL shows only the current position, 0 the full history.
    "
UPDATE shares SET trail_since = CASE WHEN trail = 0 THEN NULL ELSE COALESCE(trail_since, 0) END;
ALTER TABLE shares DROP COLUMN trail;
",
];

/// Opens the database and brings its schema up to date.
pub fn open(path: &std::path::Path) -> Result<Connection, Box<dyn std::error::Error>> {
    let mut db = Connection::open(path)?;
    db.execute_batch("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;")?;
    migrate(&mut db)?;
    Ok(db)
}

fn migrate(db: &mut Connection) -> Result<(), Box<dyn std::error::Error>> {
    let version: i64 = db.pragma_query_value(None, "user_version", |r| r.get(0))?;
    let version = usize::try_from(version)?;
    if version > MIGRATIONS.len() {
        return Err(format!(
            "the database has schema version {version}, newer than this server knows"
        )
        .into());
    }
    // Table rebuilds drop tables that others reference. SQLite ignores this pragma inside a transaction.
    db.pragma_update(None, "foreign_keys", false)?;
    for (i, sql) in MIGRATIONS.iter().enumerate().skip(version) {
        let tx = db.transaction()?;
        tx.execute_batch(sql)?;
        let broken: bool = tx.query_row(
            "SELECT EXISTS (SELECT 1 FROM pragma_foreign_key_check)",
            [],
            |r| r.get(0),
        )?;
        if broken {
            return Err(format!("migration {} breaks a foreign key", i + 1).into());
        }
        tx.pragma_update(None, "user_version", i as i64 + 1)?;
        tx.commit()?;
    }
    db.pragma_update(None, "foreign_keys", true)?;
    Ok(())
}

#[derive(Parser)]
#[command(about = "opentracker server")]
struct Cli {
    #[arg(long, env = "OT_ADDR", default_value = "127.0.0.1:8080")]
    addr: String,
    #[arg(long, env = "OT_DB", default_value = "ot.db")]
    db: PathBuf,
    /// The built web UI.
    #[arg(long, env = "OT_WEB_DIR", default_value = "web/dist")]
    web_dir: PathBuf,
    /// The address browsers use, for example https://track.example.com.
    /// Passkeys need it behind a reverse proxy. An https URL also marks the session cookie Secure.
    #[arg(long, env = "OT_PUBLIC_URL", value_parser = parse_public_url)]
    public_url: Option<Url>,
    /// Days to keep points. Users can lower this for themselves. 0 keeps points forever.
    #[arg(long, env = "OT_RETENTION_DAYS", default_value_t = 30)]
    retention_days: i64,
    /// Set when one reverse proxy forwards all traffic. Rate limits then use the last X-Forwarded-For entry.
    /// Clients must not reach the server port directly, or they can set that header themselves.
    #[arg(long, env = "OT_BEHIND_PROXY")]
    behind_proxy: bool,
    #[command(subcommand)]
    command: Option<Command>,
}

#[derive(Subcommand)]
enum Command {
    /// Create a user or reset their password. Reads the password from OT_PASSWORD or stdin.
    ///
    /// A reset also removes all passkeys, revokes device tokens and turns off two-factor sign-in, so a lost device cannot sign in.
    Passwd { username: String },
}

fn parse_public_url(s: &str) -> Result<Url, String> {
    let url = Url::parse(s).map_err(|e| e.to_string())?;
    if !matches!(url.scheme(), "http" | "https") || url.path() != "/" {
        return Err("must be http(s)://host[:port] without a path".into());
    }
    Ok(url)
}

#[derive(Clone)]
pub struct AppState {
    // ponytail: one connection behind a global lock. Fine for a few users; use a pool if requests queue up.
    db: Arc<Mutex<Connection>>,
    limiter: Arc<auth::Limiter>,
    ceremonies: Arc<passkeys::Ceremonies>,
    pairings: Arc<device::Pairings>,
    public_url: Option<Url>,
    /// 0 means forever.
    max_retention_days: i64,
    behind_proxy: bool,
}

impl AppState {
    pub fn db(&self) -> MutexGuard<'_, Connection> {
        self.db.lock().unwrap_or_else(PoisonError::into_inner)
    }

    pub fn https(&self) -> bool {
        self.public_url
            .as_ref()
            .is_some_and(|u| u.scheme() == "https")
    }
}

#[derive(Debug)]
pub enum Error {
    BadRequest(String),
    Unauthorized,
    Forbidden,
    NotFound,
    Conflict(String),
    TooManyRequests,
    Internal(String),
}

impl IntoResponse for Error {
    fn into_response(self) -> Response {
        match self {
            Error::BadRequest(msg) => (StatusCode::BAD_REQUEST, msg).into_response(),
            Error::Unauthorized => StatusCode::UNAUTHORIZED.into_response(),
            Error::Forbidden => StatusCode::FORBIDDEN.into_response(),
            Error::NotFound => StatusCode::NOT_FOUND.into_response(),
            Error::Conflict(msg) => (StatusCode::CONFLICT, msg).into_response(),
            Error::TooManyRequests => StatusCode::TOO_MANY_REQUESTS.into_response(),
            Error::Internal(msg) => {
                eprintln!("internal error: {msg}");
                StatusCode::INTERNAL_SERVER_ERROR.into_response()
            }
        }
    }
}

impl From<rusqlite::Error> for Error {
    fn from(e: rusqlite::Error) -> Self {
        Error::Internal(e.to_string())
    }
}

pub fn now() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .unwrap()
        .as_secs() as i64
}

#[tokio::main]
async fn main() {
    let cli = Cli::parse();
    let db = open(&cli.db).unwrap_or_else(|e| {
        eprintln!("cannot open {}: {e}", cli.db.display());
        std::process::exit(1);
    });

    match &cli.command {
        Some(Command::Passwd { username }) => passwd(&db, username),
        None => serve(cli, db).await,
    }
}

/// Inserts a user. The first user ever becomes the admin.
pub fn insert_user(
    db: &Connection,
    username: &str,
    pw_hash: &str,
    is_admin: bool,
) -> Result<i64, Error> {
    let first: bool = db.query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| r.get(0))?;
    db.execute(
        "INSERT INTO users (username, pw_hash, is_admin, webauthn_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
        params![username, pw_hash, is_admin || first, uuid::Uuid::new_v4().to_string(), now()],
    )
    .map_err(|e| match e {
        rusqlite::Error::SqliteFailure(f, _) if f.extended_code == rusqlite::ffi::SQLITE_CONSTRAINT_UNIQUE => {
            Error::Conflict("that username is taken".into())
        }
        e => e.into(),
    })?;
    Ok(db.last_insert_rowid())
}

/// Sets a password and removes every other way in: passkeys, two-factor sign-in, sessions and device tokens.
/// A reset often follows a lost device, and its passkey or token must not keep working.
pub fn reset_password(db: &Connection, user_id: i64, pw_hash: &str) -> rusqlite::Result<()> {
    db.execute(
        "UPDATE users SET pw_hash = ?1, two_factor = 0 WHERE id = ?2",
        params![pw_hash, user_id],
    )?;
    db.execute("DELETE FROM passkeys WHERE user_id = ?1", [user_id])?;
    db.execute("DELETE FROM sessions WHERE user_id = ?1", [user_id])?;
    // A random hash matches no token. The devices and their history stay, and the owner pairs them again.
    db.execute(
        "UPDATE devices SET token_hash = randomblob(32) WHERE user_id = ?1 AND token_hash IS NOT NULL",
        [user_id],
    )?;
    Ok(())
}

pub fn check_username(name: &str) -> Result<&str, Error> {
    let name = name.trim();
    if name.is_empty() || name.chars().count() > 64 {
        return Err(Error::BadRequest(
            "username must have 1 to 64 characters".into(),
        ));
    }
    Ok(name)
}

fn passwd(db: &Connection, username: &str) {
    let password = std::env::var("OT_PASSWORD").unwrap_or_else(|_| {
        // ponytail: the password echoes on the terminal. Use rpassword if that matters.
        eprint!("password for {username}: ");
        let mut line = String::new();
        std::io::stdin()
            .read_line(&mut line)
            .expect("read password");
        line.trim_end_matches(['\r', '\n']).to_owned()
    });
    if let Err(msg) = auth::check_new_password(&password) {
        eprintln!("{msg}");
        std::process::exit(1);
    }
    let hash = auth::hash_password(&password);
    let existing: Option<i64> = db
        .query_row(
            "SELECT id FROM users WHERE username = ?1",
            [username],
            |r| r.get(0),
        )
        .optional()
        .expect("query user");
    match existing {
        Some(id) => {
            reset_password(db, id, &hash).expect("update user");
            println!(
                "password reset for {username}: passkeys removed, device tokens revoked, two-factor sign-in off"
            );
        }
        None => {
            let username = check_username(username).unwrap_or_else(|e| {
                eprintln!("{e:?}");
                std::process::exit(1);
            });
            insert_user(db, username, &hash, false).expect("insert user");
            println!("created user {username}");
        }
    }
}

async fn serve(cli: Cli, db: Connection) {
    let state = AppState {
        db: Arc::new(Mutex::new(db)),
        limiter: Arc::default(),
        ceremonies: Arc::default(),
        pairings: Arc::default(),
        public_url: cli.public_url,
        max_retention_days: cli.retention_days.max(0),
        behind_proxy: cli.behind_proxy,
    };
    tokio::spawn(cleanup(state.clone()));

    let listener = tokio::net::TcpListener::bind(&cli.addr)
        .await
        .expect("bind");
    println!(
        "listening on http://{}, serving {}",
        cli.addr,
        cli.web_dir.display()
    );
    if state
        .db()
        .query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| {
            r.get::<_, bool>(0)
        })
        .unwrap_or(false)
    {
        println!("no users yet: open the web UI to create the admin account");
    }
    let app = routes::router(state, &cli.web_dir);
    axum::serve(
        listener,
        app.into_make_service_with_connect_info::<std::net::SocketAddr>(),
    )
    .with_graceful_shutdown(shutdown())
    .await
    .expect("serve");
}

/// In a container the server is process 1, which gets no default signal handling. Without this, `podman stop` waits 10 s and kills it.
async fn shutdown() {
    let mut term = tokio::signal::unix::signal(tokio::signal::unix::SignalKind::terminate())
        .expect("SIGTERM handler");
    tokio::select! {
        _ = term.recv() => {}
        _ = tokio::signal::ctrl_c() => {}
    }
}

/// The days of points to keep for a user, or None for forever.
pub fn effective_retention(user_days: Option<i64>, max_days: i64) -> Option<i64> {
    match (user_days, max_days) {
        (None, 0) => None,
        (None, max) => Some(max),
        (Some(days), 0) => Some(days),
        (Some(days), max) => Some(days.min(max)),
    }
}

/// Deletes points older than the user's retention. Each device keeps its newest point, so it stays on the map.
pub fn purge_points(
    db: &Connection,
    user_id: i64,
    user_days: Option<i64>,
    max_days: i64,
) -> rusqlite::Result<usize> {
    match effective_retention(user_days, max_days) {
        None => Ok(0),
        Some(days) => db.execute(
            "DELETE FROM points
             WHERE device_id IN (SELECT id FROM devices WHERE user_id = ?1) AND ts < ?2
               AND ts < (SELECT MAX(ts) FROM points p WHERE p.device_id = points.device_id)",
            [user_id, now() - days * 86400],
        ),
    }
}

async fn cleanup(state: AppState) {
    let mut tick = tokio::time::interval(Duration::from_secs(3600));
    loop {
        tick.tick().await;
        let now = now();
        let db = state.db();
        let result = db
            .execute_batch(&format!(
                "DELETE FROM sessions WHERE expires_at <= {now};
                 DELETE FROM shares WHERE expires_at <= {now};"
            ))
            .and_then(|()| {
                let users: Vec<(i64, Option<i64>)> = db
                    .prepare("SELECT id, retention_days FROM users")?
                    .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?
                    .collect::<rusqlite::Result<_>>()?;
                for (id, days) in users {
                    purge_points(&db, id, days, state.max_retention_days)?;
                }
                Ok(())
            });
        if let Err(e) = result {
            eprintln!("cleanup failed: {e}");
        }
        drop(db);
        state.limiter.prune();
    }
}

#[cfg(test)]
pub fn test_db() -> Connection {
    let mut db = Connection::open_in_memory().unwrap();
    migrate(&mut db).unwrap();
    db
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn retention_never_exceeds_the_server_limit() {
        assert_eq!(effective_retention(None, 0), None);
        assert_eq!(effective_retention(None, 30), Some(30));
        assert_eq!(effective_retention(Some(7), 0), Some(7));
        assert_eq!(effective_retention(Some(7), 30), Some(7));
        assert_eq!(effective_retention(Some(90), 30), Some(30));
    }

    #[test]
    fn public_url_is_an_origin() {
        assert!(parse_public_url("https://track.example.com").is_ok());
        assert!(parse_public_url("http://localhost:8080/").is_ok());
        assert!(parse_public_url("https://example.com/track").is_err());
        assert!(parse_public_url("ftp://example.com").is_err());
    }

    #[test]
    fn reset_removes_every_other_way_in() {
        let db = test_db();
        let id = insert_user(&db, "a", "old", false).unwrap();
        db.execute_batch(
            "UPDATE users SET two_factor = 1;
             INSERT INTO passkeys (user_id, cred_id, passkey, name, created_at) VALUES (1, x'01', '{}', 'k', 0);
             INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (x'02', 1, 9999999999);
             INSERT INTO devices (user_id, name, token_hash, created_at) VALUES (1, 'phone', x'03', 0), (1, 'Web', NULL, 0);",
        )
        .unwrap();
        reset_password(&db, id, "new").unwrap();
        let count = |sql: &str| -> i64 { db.query_row(sql, [], |r| r.get(0)).unwrap() };
        assert_eq!(count("SELECT COUNT(*) FROM passkeys"), 0);
        assert_eq!(count("SELECT COUNT(*) FROM sessions"), 0);
        assert_eq!(count("SELECT two_factor FROM users"), 0);
        assert_eq!(
            count("SELECT COUNT(*) FROM devices WHERE token_hash = x'03'"),
            0
        );
        assert_eq!(count("SELECT COUNT(*) FROM devices"), 2);
    }

    #[test]
    fn migrations_keep_old_points() {
        let mut db = Connection::open_in_memory().unwrap();
        db.execute_batch(MIGRATIONS[0]).unwrap();
        db.execute_batch(
            "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0);
             INSERT INTO devices (id, user_id, name, token_hash, created_at, last_seen_at)
                 VALUES (7, 1, 'old', x'01', 0, 5), (8, 1, 'new', x'02', 0, 9);
             INSERT INTO points (user_id, ts, lat, lon) VALUES (1, 100, 1, 1), (2, 100, 2, 2);",
        )
        .unwrap();
        migrate(&mut db).unwrap();
        migrate(&mut db).unwrap();
        let owners: Vec<(i64, String)> = db
            .prepare("SELECT d.user_id, d.name FROM points p JOIN devices d ON d.id = p.device_id ORDER BY d.user_id")
            .unwrap()
            .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
            .unwrap()
            .collect::<rusqlite::Result<_>>()
            .unwrap();
        assert_eq!(owners, [(1, "new".into()), (2, "Web".into())]);
    }

    #[test]
    fn trail_columns_merge_into_one() {
        let mut db = Connection::open_in_memory().unwrap();
        for sql in &MIGRATIONS[..3] {
            db.execute_batch(sql).unwrap();
        }
        db.pragma_update(None, "user_version", 3).unwrap();
        db.execute_batch(
            "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0), (3, 'c', '3', 0), (4, 'd', '4', 0);
             INSERT INTO shares (owner_id, viewer_id, created_at, trail, trail_since) VALUES (1, 2, 0, 0, NULL), (1, 3, 0, 1, NULL), (1, 4, 0, 1, 99);",
        )
        .unwrap();
        migrate(&mut db).unwrap();
        let since: Vec<Option<i64>> = db
            .prepare("SELECT trail_since FROM shares ORDER BY viewer_id")
            .unwrap()
            .query_map([], |r| r.get(0))
            .unwrap()
            .collect::<rusqlite::Result<_>>()
            .unwrap();
        assert_eq!(since, [None, Some(0), Some(99)]);
    }

    #[test]
    fn retention_keeps_each_devices_newest_point() {
        let db = test_db();
        db.execute_batch(
            "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0);
             INSERT INTO devices (id, user_id, name, token_hash, created_at) VALUES (1, 1, 'p', x'01', 0), (2, 1, 'q', x'02', 0);
             INSERT INTO points (device_id, ts, lat, lon) VALUES (1, 10, 0, 0), (1, 20, 0, 0), (2, 15, 0, 0);",
        )
        .unwrap();
        purge_points(&db, 1, Some(1), 30).unwrap();
        let left: Vec<(i64, i64)> = db
            .prepare("SELECT device_id, ts FROM points ORDER BY device_id")
            .unwrap()
            .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
            .unwrap()
            .collect::<rusqlite::Result<_>>()
            .unwrap();
        assert_eq!(left, [(1, 20), (2, 15)]);
    }

    #[test]
    fn first_user_is_admin() {
        let db = test_db();
        let a = insert_user(&db, "a", "", false).unwrap();
        let b = insert_user(&db, "b", "", false).unwrap();
        let admin = |id: i64| -> bool {
            db.query_row("SELECT is_admin FROM users WHERE id = ?1", [id], |r| {
                r.get(0)
            })
            .unwrap()
        };
        assert!(admin(a));
        assert!(!admin(b));
        assert!(matches!(
            insert_user(&db, "A", "", false),
            Err(Error::Conflict(_))
        ));
    }
}