use std::path::Path;

use api::{
    ChangePassword, Credentials, Device, DeviceToken, Login, MAX_BATCH, MAX_TRACK_SECS, Me,
    NewDevice, NewShare, NewUser, PRECISIONS_M, Person, PersonDevice, Point, ResetPassword,
    SetDefaultDevice, SetDisplayName, SetRetention, SetRole, SetTwoFactor, SetupStatus, Share,
    ShareSettings, Shares, Uploaded,
};
use axum::extract::{Path as UrlPath, Query, State};
use axum::http::{HeaderMap, HeaderValue, Uri, header};
use axum::response::{IntoResponse, Response};
use axum::routing::{delete, get, post, put};
use axum::{Json, Router};
use base64::Engine;
use rusqlite::{Connection, OptionalExtension, Row, ToSql, params};
use serde::Deserialize;
use sha2::{Digest, Sha256};
use tower_http::services::ServeDir;

use crate::auth::{self, Admin, ClientIp, User};
use crate::passkeys::{self, Pending};
use crate::{AppState, Error, now};
use crate::{device, guest};

type Result<T> = std::result::Result<T, Error>;

pub fn router(state: AppState, web_dir: &Path) -> Router {
    let csp = HeaderValue::from_str(&content_security_policy(web_dir)).expect("CSP is ASCII");
    Router::new()
        .route("/api/setup", get(setup_status).post(setup))
        .route("/api/login", post(login))
        .route("/api/logout", post(logout))
        .route("/api/passkey/login", post(passkeys::login_begin))
        .route("/api/passkey/login/finish", post(passkeys::login_finish))
        .route("/api/me", get(me))
        .route(
            "/api/me/password",
            post(change_password).delete(delete_password),
        )
        .route("/api/me/two-factor", put(set_two_factor))
        .route("/api/me/retention", put(set_retention))
        .route("/api/me/display-name", put(set_display_name))
        .route("/api/me/default-device", put(set_default_device))
        .route("/api/passkeys", get(passkeys::list))
        .route("/api/passkeys/register", post(passkeys::register_begin))
        .route(
            "/api/passkeys/register/finish",
            post(passkeys::register_finish),
        )
        .route("/api/passkeys/{id}", delete(passkeys::delete))
        .route("/api/people", get(people))
        .route("/api/people/{id}/track", get(track))
        .route("/api/devices", get(list_devices).post(create_device))
        .route("/api/devices/pair/begin", post(device::pair_begin))
        .route("/api/devices/pair", post(device::pair_finish))
        .route("/api/device", get(device::me))
        .route("/api/device/track", get(device::track))
        .route("/api/devices/{id}", delete(delete_device))
        .route("/api/points", post(upload))
        .route("/api/shares", get(list_shares).post(create_share))
        .route("/api/shares/{id}", delete(delete_share))
        .route("/api/usernames", get(usernames))
        .route("/api/users", get(list_users).post(create_user))
        .route("/api/users/{id}", delete(delete_user))
        .route("/api/users/{id}/role", put(set_role))
        .route("/api/users/{id}/password", post(reset_user_password))
        .route("/api/links", get(guest::list).post(guest::create))
        .route("/api/links/{id}", delete(guest::delete))
        .route("/api/guest", post(guest::view))
        .route("/api/guest/track", post(guest::track))
        .route("/api/guest/unlock", post(guest::unlock))
        .route("/healthz", get(healthz))
        .fallback_service(ServeDir::new(web_dir))
        .layer(axum::middleware::map_response(move |mut res: Response| {
            let csp = csp.clone();
            async move {
                let h = res.headers_mut();
                h.insert(header::CONTENT_SECURITY_POLICY, csp);
                h.insert(header::X_FRAME_OPTIONS, HeaderValue::from_static("DENY"));
                h.insert(
                    header::X_CONTENT_TYPE_OPTIONS,
                    HeaderValue::from_static("nosniff"),
                );
                // Not no-referrer: the OpenStreetMap tile servers require a Referer.
                h.insert(
                    header::REFERRER_POLICY,
                    HeaderValue::from_static("strict-origin-when-cross-origin"),
                );
                res
            }
        }))
        .with_state(state)
}

/// Allows the inline scripts of the built index.html by hash. Trunk names them anew in each build.
fn content_security_policy(web_dir: &Path) -> String {
    let html = std::fs::read_to_string(web_dir.join("index.html")).unwrap_or_default();
    let hashes: String = html
        .split("<script")
        .skip(1)
        .filter_map(|s| {
            let (tag, rest) = s.split_once('>')?;
            let body = rest.split_once("</script>")?.0;
            let hash = base64::engine::general_purpose::STANDARD.encode(Sha256::digest(body));
            (!tag.contains("src=")).then(|| format!(" 'sha256-{hash}'"))
        })
        .collect();
    // Keep the tile hosts in sync with web/src/map.rs.
    format!(
        "default-src 'self'; script-src 'self' 'wasm-unsafe-eval'{hashes}; style-src 'self' 'unsafe-inline'; \
         img-src 'self' data: https://tile.openstreetmap.org https://*.tile.openstreetmap.fr \
         https://*.tile-cyclosm.openstreetmap.fr https://*.tile.opentopomap.org https://server.arcgisonline.com; \
         connect-src 'self'; object-src 'none'; base-uri 'none'; form-action 'self'; frame-ancestors 'none'"
    )
}

async fn healthz(State(s): State<AppState>) -> Result<&'static str> {
    s.db().query_row("SELECT 1", [], |_| Ok(()))?;
    Ok("ok")
}

async fn setup_status(State(s): State<AppState>) -> Result<Json<SetupStatus>> {
    Ok(Json(SetupStatus {
        needed: crate::no_users(&s.db())?,
    }))
}

/// Creates the first account, an admin. Only works while no user exists.
async fn setup(State(s): State<AppState>, Json(b): Json<Credentials>) -> Result<Response> {
    let username = crate::check_username(&b.username)?.to_owned();
    auth::check_new_password(&b.password).map_err(|m| Error::BadRequest(m.into()))?;
    let already = || Error::Conflict("the server is already set up".into());
    // Checked before hashing, so a request to a set-up server costs no Argon2 work.
    if !crate::no_users(&s.db())? {
        return Err(already());
    }
    let hash = auth::hash_password_async(b.password).await?;
    let id = {
        let db = s.db();
        // Checked again under the same lock as the insert, so two setups cannot both win.
        if !crate::no_users(&db)? {
            return Err(already());
        }
        crate::insert_user(&db, &username, &hash, true)?
    };
    passkeys::sign_in(&s, id)
}

async fn login(
    State(s): State<AppState>,
    ClientIp(ip): ClientIp,
    uri: Uri,
    headers: HeaderMap,
    Json(b): Json<Login>,
) -> Result<Response> {
    match &b.state_id {
        // The passkey already passed. This is the password step of a two-factor sign-in.
        Some(state_id) => {
            let Some(Pending::NeedsPassword { user_id }) = s.ceremonies.take(state_id) else {
                return Err(passkeys::expired());
            };
            let username: String =
                s.db()
                    .query_row("SELECT username FROM users WHERE id = ?1", [user_id], |r| {
                        r.get(0)
                    })?;
            let ok = auth::check_password(&s, ip, &username, &b.password).await?;
            passkeys::sign_in(&s, ok.id)
        }
        None => {
            let ok = auth::check_password(&s, ip, b.username.trim(), &b.password).await?;
            if ok.two_factor {
                return passkeys::second_factor(&s, &uri, &headers, ok.id);
            }
            passkeys::sign_in(&s, ok.id)
        }
    }
}

async fn logout(State(s): State<AppState>, user: User) -> Result<impl IntoResponse> {
    s.db().execute(
        "DELETE FROM sessions WHERE token_hash = ?1",
        [user.session_hash],
    )?;
    Ok(([(header::SET_COOKIE, auth::clear_session(&s))], Json(())))
}

async fn me(State(s): State<AppState>, user: User) -> Result<Json<Me>> {
    let (has_password, two_factor, retention_days, display_name, default_device) = s.db().query_row(
        "SELECT pw_hash IS NOT NULL, two_factor, retention_days, display_name, default_device_id FROM users WHERE id = ?1",
        [user.id],
        |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?, r.get(3)?, r.get(4)?)),
    )?;
    Ok(Json(Me {
        id: user.id,
        username: user.username,
        display_name,
        default_device,
        is_admin: user.is_admin,
        has_password,
        two_factor,
        retention_days,
        max_retention_days: (s.max_retention_days > 0).then_some(s.max_retention_days),
        public_url: s
            .public_url
            .as_ref()
            .map(|u| u.as_str().trim_end_matches('/').to_owned()),
    }))
}

/// Sets or changes the password. Changing an existing one needs the old one.
async fn change_password(
    State(s): State<AppState>,
    ClientIp(ip): ClientIp,
    user: User,
    Json(b): Json<ChangePassword>,
) -> Result<Json<()>> {
    auth::check_new_password(&b.new).map_err(|m| Error::BadRequest(m.into()))?;
    let has_password: bool = s.db().query_row(
        "SELECT pw_hash IS NOT NULL FROM users WHERE id = ?1",
        [user.id],
        |r| r.get(0),
    )?;
    if has_password {
        // 400, not 401: the session is still valid, only the old password is wrong.
        auth::check_password(&s, ip, &user.username, b.old.as_deref().unwrap_or_default())
            .await
            .map_err(|e| match e {
                Error::Unauthorized => Error::BadRequest("wrong current password".into()),
                e => e,
            })?;
    } else {
        user.check_recent()?;
    }
    let hash = auth::hash_password_async(b.new).await?;
    s.db().execute(
        "UPDATE users SET pw_hash = ?1 WHERE id = ?2",
        params![hash, user.id],
    )?;
    auth::end_other_sessions(&s, &user)?;
    Ok(Json(()))
}

/// Leaves the account on passkeys alone.
async fn delete_password(State(s): State<AppState>, user: User) -> Result<Json<()>> {
    user.check_recent()?;
    let db = s.db();
    if passkeys::count(&db, user.id)? == 0 {
        return Err(Error::BadRequest(
            "add a passkey before removing your password".into(),
        ));
    }
    let two_factor: bool = db.query_row(
        "SELECT two_factor FROM users WHERE id = ?1",
        [user.id],
        |r| r.get(0),
    )?;
    if two_factor {
        return Err(Error::BadRequest(
            "turn off two-factor sign-in before removing your password".into(),
        ));
    }
    db.execute("UPDATE users SET pw_hash = NULL WHERE id = ?1", [user.id])?;
    drop(db);
    auth::end_other_sessions(&s, &user)?;
    Ok(Json(()))
}

async fn set_two_factor(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<SetTwoFactor>,
) -> Result<Json<()>> {
    user.check_recent()?;
    let db = s.db();
    if b.enabled {
        let has_password: bool = db.query_row(
            "SELECT pw_hash IS NOT NULL FROM users WHERE id = ?1",
            [user.id],
            |r| r.get(0),
        )?;
        if !has_password {
            return Err(Error::BadRequest(
                "set a password before turning on two-factor sign-in".into(),
            ));
        }
        if passkeys::count(&db, user.id)? == 0 {
            return Err(Error::BadRequest(
                "add a passkey before turning on two-factor sign-in".into(),
            ));
        }
    }
    db.execute(
        "UPDATE users SET two_factor = ?1 WHERE id = ?2",
        params![b.enabled, user.id],
    )?;
    drop(db);
    auth::end_other_sessions(&s, &user)?;
    Ok(Json(()))
}

/// Users can only keep their points for less time than the server allows, never longer.
async fn set_retention(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<SetRetention>,
) -> Result<Json<()>> {
    if let Some(days) = b.days {
        let max = s.max_retention_days;
        if days < 1 || (max > 0 && days > max) {
            let range = if max > 0 {
                format!("1 to {max}")
            } else {
                "at least 1".into()
            };
            return Err(Error::BadRequest(format!("retention must be {range} days")));
        }
    }
    let db = s.db();
    db.execute(
        "UPDATE users SET retention_days = ?1 WHERE id = ?2",
        params![b.days, user.id],
    )?;
    crate::purge_points(&db, user.id, b.days, s.max_retention_days)?;
    Ok(Json(()))
}

async fn set_display_name(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<SetDisplayName>,
) -> Result<Json<()>> {
    let name = b.name.as_deref().map(str::trim).filter(|n| !n.is_empty());
    if name.is_some_and(|n| n.chars().count() > 64) {
        return Err(Error::BadRequest(
            "the display name must have at most 64 characters".into(),
        ));
    }
    s.db().execute(
        "UPDATE users SET display_name = ?1 WHERE id = ?2",
        params![name, user.id],
    )?;
    Ok(Json(()))
}

async fn set_default_device(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<SetDefaultDevice>,
) -> Result<Json<()>> {
    let db = s.db();
    if let Some(id) = b.device {
        let mine: bool = db.query_row(
            "SELECT EXISTS (SELECT 1 FROM devices WHERE id = ?1 AND user_id = ?2)",
            [id, user.id],
            |r| r.get(0),
        )?;
        if !mine {
            return Err(Error::NotFound);
        }
    }
    db.execute(
        "UPDATE users SET default_device_id = ?1 WHERE id = ?2",
        params![b.device, user.id],
    )?;
    Ok(Json(()))
}

const POINT_COLS: &str = "ts, lat, lon, acc, alt, speed, bearing, battery";

/// POINT_COLS, with the stored cell of `m` metres in place of the exact position.
fn point_cols(m: u32) -> String {
    match m {
        0 => POINT_COLS.into(),
        m => format!("ts, lat_{m}, lon_{m}, acc, alt, speed, bearing, battery"),
    }
}

/// The cell columns of all PRECISIONS_M, in order.
pub fn cell_cols() -> String {
    PRECISIONS_M.map(|m| format!("lat_{m}, lon_{m}")).join(", ")
}

pub type Cells = [(f64, f64); PRECISIONS_M.len()];

/// The cells for a new point of a device, given the cells of its previous point.
pub fn cells(prev: Option<&Cells>, lat: f64, lon: f64) -> Cells {
    std::array::from_fn(|i| cell(prev.map(|c| c[i]), lat, lon, PRECISIONS_M[i]))
}

/// The centre of a grid cell of about `m` metres. Keeps the previous cell until the point is a quarter cell
/// past its edge. Otherwise GPS noise near an edge flips between two cells and shows where the edge is.
fn cell(prev: Option<(f64, f64)>, lat: f64, lon: f64, m: u32) -> (f64, f64) {
    let step = f64::from(m) / 111_320.0;
    // A degree of longitude shrinks toward the poles. Using the snapped latitude keeps one grid per row.
    let lon_step = |lat: f64| step / lat.to_radians().cos().max(0.01);
    if let Some((clat, clon)) = prev
        && (lat - clat).abs() <= 0.75 * step
        && (lon - clon).abs() <= 0.75 * lon_step(clat)
    {
        return (clat, clon);
    }
    let clat = ((lat / step).round() * step).clamp(-90.0, 90.0);
    let clon = ((lon / lon_step(clat)).round() * lon_step(clat)).clamp(-180.0, 180.0);
    (clat, clon)
}

/// Reads the POINT_COLS columns, starting at column `i`.
fn point_at(r: &Row, i: usize) -> rusqlite::Result<Point> {
    Ok(Point {
        ts: r.get(i)?,
        lat: r.get(i + 1)?,
        lon: r.get(i + 2)?,
        acc: r.get(i + 3)?,
        alt: r.get(i + 4)?,
        speed: r.get(i + 5)?,
        bearing: r.get(i + 6)?,
        battery: r.get(i + 7)?,
    })
}

/// Drops what would reveal more than a point read with `point_cols(m)` should. The position is already its cell.
fn coarsen(p: &mut Point, m: u32) {
    if m == 0 {
        return;
    }
    // A jump to the next cell shows when the owner crossed the cell edge, and where that edge is.
    // Rounding the time to m seconds keeps that crossing about m metres vague at walking speed.
    p.ts -= p.ts.rem_euclid(i64::from(m));
    p.acc = Some(p.acc.unwrap_or(0.0).max(m as f32));
    p.alt = None;
    p.speed = None;
    p.bearing = None;
    // The battery drains steadily, so it would date a point within its rounded time.
    p.battery = None;
}

/// What a viewer may see of one owner.
pub struct Access {
    pub owner: i64,
    pub username: String,
    /// None for the viewer's own account.
    pub share: Option<i64>,
    pub all_devices: bool,
    pub trail_since: Option<i64>,
    pub precision_m: u32,
}

/// Columns of `shares s` that `access_at` reads, after the owner id and username.
pub const ACCESS_COLS: &str = "s.id, s.all_devices, s.trail_since, s.precision_m";

/// Reads an owner id, a username and ACCESS_COLS.
pub fn access_at(r: &Row) -> rusqlite::Result<Access> {
    Ok(Access {
        owner: r.get(0)?,
        username: r.get(1)?,
        share: r.get(2)?,
        all_devices: r.get(3)?,
        trail_since: r.get(4)?,
        precision_m: r.get(5)?,
    })
}

/// Limits `devices d` to the ones an Access allows. Binds ?2 = share, ?3 = all_devices.
const DEVICE_ALLOWED: &str =
    "(?3 OR d.id IN (SELECT device_id FROM share_devices WHERE share_id = ?2))";

/// The viewer first, then everyone with an active share to the viewer.
fn accesses(db: &Connection, viewer: i64) -> rusqlite::Result<Vec<Access>> {
    let mut list: Vec<Access> = db
        .prepare_cached(&format!(
            "SELECT id, username, NULL, 1, 0, 0 FROM users WHERE id = ?1
             UNION ALL
             SELECT u.id, u.username, {ACCESS_COLS}
             FROM shares s JOIN users u ON u.id = s.owner_id
             WHERE s.viewer_id = ?1 AND (s.expires_at IS NULL OR s.expires_at > ?2)"
        ))?
        .query_map(params![viewer, now()], access_at)?
        .collect::<rusqlite::Result<_>>()?;
    list.sort_by_key(|a| (a.owner != viewer, a.username.to_lowercase()));
    Ok(list)
}

/// The owner's allowed devices with their newest point.
pub fn person_for(db: &Connection, a: Access) -> rusqlite::Result<Person> {
    let mut devices: Vec<PersonDevice> = db
        .prepare_cached(&format!(
            "SELECT d.id, d.name, {} FROM devices d
             JOIN points p ON p.device_id = d.id AND p.ts = (SELECT MAX(ts) FROM points WHERE device_id = d.id)
             WHERE d.user_id = ?1 AND {DEVICE_ALLOWED}",
            point_cols(a.precision_m)
        ))?
        .query_map(params![a.owner, a.share, a.all_devices], |r| {
            let mut last = point_at(r, 2)?;
            coarsen(&mut last, a.precision_m);
            Ok(PersonDevice {
                id: r.get(0)?,
                name: r.get(1)?,
                last,
            })
        })?
        .collect::<rusqlite::Result<_>>()?;
    // By rounded time, so the order does not tell which device sent last within the same rounded time.
    devices.sort_by_key(|d| (std::cmp::Reverse(d.last.ts), d.id));
    let (display_name, default_device): (Option<String>, Option<i64>) = db.query_row(
        "SELECT display_name, default_device_id FROM users WHERE id = ?1",
        [a.owner],
        |r| Ok((r.get(0)?, r.get(1)?)),
    )?;
    // A hidden device's id must not reach the viewer.
    let default_device = default_device.filter(|id| devices.iter().any(|d| d.id == *id));
    Ok(Person {
        id: a.owner,
        username: a.username,
        display_name,
        default_device,
        devices,
        trail_since: a.trail_since,
        precision_m: a.precision_m,
    })
}

fn people_for(db: &Connection, viewer: i64) -> rusqlite::Result<Vec<Person>> {
    accesses(db, viewer)?
        .into_iter()
        .map(|a| person_for(db, a))
        .collect()
}

async fn people(State(s): State<AppState>, user: User) -> Result<Json<Vec<Person>>> {
    Ok(Json(people_for(&s.db(), user.id)?))
}

#[derive(Deserialize)]
struct TrackQuery {
    from: i64,
    to: i64,
    device: i64,
}

const MAX_TRACK_POINTS: i64 = 50_000;

async fn track(
    State(s): State<AppState>,
    user: User,
    UrlPath(id): UrlPath<i64>,
    Query(q): Query<TrackQuery>,
) -> Result<Json<Vec<Point>>> {
    let db = s.db();
    let a = accesses(&db, user.id)?
        .into_iter()
        .find(|a| a.owner == id)
        .ok_or(Error::NotFound)?;
    Ok(Json(track_points(&db, &a, q.device, q.from, q.to)?))
}

/// One device's points in a time range, as far as the access allows.
pub fn track_points(
    db: &Connection,
    a: &Access,
    device: i64,
    from: i64,
    to: i64,
) -> Result<Vec<Point>> {
    if to
        .checked_sub(from)
        .is_none_or(|d| !(0..=MAX_TRACK_SECS).contains(&d))
    {
        return Err(Error::BadRequest("range must be 0 to 31 days".into()));
    }
    let since = a.trail_since.ok_or(Error::Forbidden)?;
    // Only whole m-second buckets. A bound inside one would split its points by raw time,
    // and moving the bound would reveal the raw times that the rounding hides.
    // Saturation only matters far from any stored time.
    let m = i64::from(a.precision_m.max(1));
    let floor = |t: i64| t.saturating_sub(t.rem_euclid(m));
    let from = floor(from).max(floor(since.saturating_add(m - 1)));
    let to = floor(to).saturating_add(m - 1);
    let allowed: bool = db.query_row(
        &format!(
            "SELECT EXISTS (SELECT 1 FROM devices d WHERE d.id = ?4 AND d.user_id = ?1 AND {DEVICE_ALLOWED})"
        ),
        params![a.owner, a.share, a.all_devices, device],
        |r| r.get(0),
    )?;
    if !allowed {
        return Err(Error::NotFound);
    }
    // ponytail: past the limit the oldest points go. Thin the trail evenly if long ranges need all of it.
    let mut points: Vec<Point> = db
        .prepare_cached(&format!(
            "SELECT * FROM (SELECT {} FROM points WHERE device_id = ?1 AND ts BETWEEN ?2 AND ?3
             ORDER BY ts DESC LIMIT {MAX_TRACK_POINTS}) ORDER BY ts",
            point_cols(a.precision_m)
        ))?
        .query_map(params![device, from, to], |r| {
            let mut p = point_at(r, 0)?;
            coarsen(&mut p, a.precision_m);
            Ok(p)
        })?
        .collect::<rusqlite::Result<_>>()?;
    // The limit can split the oldest bucket, which would show raw times again.
    if points.len() as i64 == MAX_TRACK_POINTS && a.precision_m > 0 {
        let oldest = points[0].ts;
        points.retain(|p| p.ts != oldest);
    }
    points.dedup_by(|b, a| (a.ts, a.lat, a.lon) == (b.ts, b.lat, b.lon));
    Ok(points)
}

async fn list_devices(State(s): State<AppState>, user: User) -> Result<Json<Vec<Device>>> {
    let devices = s
        .db()
        .prepare_cached(
            "SELECT id, name, token_hash IS NULL, created_at, last_seen_at FROM devices
             WHERE user_id = ?1 ORDER BY created_at",
        )?
        .query_map([user.id], |r| {
            Ok(Device {
                id: r.get(0)?,
                name: r.get(1)?,
                web: r.get(2)?,
                created_at: r.get(3)?,
                last_seen_at: r.get(4)?,
            })
        })?
        .collect::<rusqlite::Result<_>>()?;
    Ok(Json(devices))
}

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

pub fn insert_device(db: &Connection, user_id: i64, name: &str) -> Result<DeviceToken> {
    let name = check_device_name(name)?;
    let (token, hash) = auth::new_secret();
    db.execute(
        "INSERT INTO devices (user_id, name, token_hash, created_at) VALUES (?1, ?2, ?3, ?4)",
        params![user_id, name, hash, now()],
    )?;
    Ok(DeviceToken { token })
}

async fn create_device(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<NewDevice>,
) -> Result<Json<DeviceToken>> {
    Ok(Json(insert_device(&s.db(), user.id, &b.name)?))
}

async fn delete_device(
    State(s): State<AppState>,
    user: User,
    UrlPath(id): UrlPath<i64>,
) -> Result<Json<()>> {
    let n = s.db().execute(
        "DELETE FROM devices WHERE id = ?1 AND user_id = ?2",
        [id, user.id],
    )?;
    if n == 0 {
        return Err(Error::NotFound);
    }
    Ok(Json(()))
}

/// Clock skew we accept from a device, so a wrong clock cannot write far into the future.
const MAX_FUTURE_SECS: i64 = 86400;

fn check_point(p: &Point, now: i64) -> std::result::Result<(), String> {
    if !(-90.0..=90.0).contains(&p.lat) || !(-180.0..=180.0).contains(&p.lon) {
        return Err(format!("point {}: coordinates out of range", p.ts));
    }
    if p.ts <= 0 || p.ts > now + MAX_FUTURE_SECS {
        return Err(format!("point {}: timestamp out of range", p.ts));
    }
    if p.battery.is_some_and(|b| b > 100) {
        return Err(format!("point {}: battery above 100", p.ts));
    }
    Ok(())
}

async fn upload(
    State(s): State<AppState>,
    uploader: auth::Uploader,
    Json(points): Json<Vec<Point>>,
) -> Result<Json<Uploaded>> {
    if points.len() > MAX_BATCH {
        return Err(Error::BadRequest(format!(
            "at most {MAX_BATCH} points per request"
        )));
    }
    let now = now();
    let total = points.len();
    let mut points: Vec<Point> = points
        .into_iter()
        .filter(|p| check_point(p, now).is_ok())
        .collect();
    // Each point's cells follow from the previous point's, so older points go first.
    points.sort_by_key(|p| p.ts);

    let mut db = s.db();
    let tx = db.transaction()?;
    let mut stored = 0;
    {
        let cols = cell_cols();
        let mut insert = tx.prepare_cached(&format!(
            "INSERT OR IGNORE INTO points (device_id, {POINT_COLS}, {cols}) VALUES ({})",
            (1..=9 + 2 * PRECISIONS_M.len())
                .map(|i| format!("?{i}"))
                .collect::<Vec<_>>()
                .join(", ")
        ))?;
        let mut prev = tx.prepare_cached(&format!(
            "SELECT {cols} FROM points WHERE device_id = ?1 AND ts < ?2 ORDER BY ts DESC LIMIT 1"
        ))?;
        for p in &points {
            let before: Option<Cells> = prev
                .query_row(params![uploader.device_id, p.ts], |r| {
                    let mut c = [(0.0, 0.0); PRECISIONS_M.len()];
                    for (i, c) in c.iter_mut().enumerate() {
                        *c = (r.get(2 * i)?, r.get(2 * i + 1)?);
                    }
                    Ok(c)
                })
                .optional()?;
            let cells = cells(before.as_ref(), p.lat, p.lon);
            let mut values: Vec<&dyn ToSql> = vec![
                &uploader.device_id,
                &p.ts,
                &p.lat,
                &p.lon,
                &p.acc,
                &p.alt,
                &p.speed,
                &p.bearing,
                &p.battery,
            ];
            values.extend(cells.iter().flat_map(|(a, b)| [a as &dyn ToSql, b]));
            stored += insert.execute(values.as_slice())?;
        }
    }
    tx.execute(
        "UPDATE devices SET last_seen_at = ?1 WHERE id = ?2",
        [now, uploader.device_id],
    )?;
    tx.commit()?;
    Ok(Json(Uploaded {
        stored,
        skipped: total - points.len(),
    }))
}

/// Reads ACCESS_COLS from column `i` on.
pub fn settings_at(db: &Connection, r: &Row, i: usize) -> rusqlite::Result<ShareSettings> {
    let id: i64 = r.get(i)?;
    let devices = match r.get::<_, bool>(i + 1)? {
        true => None,
        false => Some(
            db.prepare_cached("SELECT device_id FROM share_devices WHERE share_id = ?1")?
                .query_map([id], |r| r.get(0))?
                .collect::<rusqlite::Result<_>>()?,
        ),
    };
    Ok(ShareSettings {
        devices,
        trail_since: r.get(i + 2)?,
        precision_m: r.get(i + 3)?,
    })
}

pub fn check_settings(set: &ShareSettings, expires_at: Option<i64>) -> Result<()> {
    if expires_at.is_some_and(|t| t <= now()) {
        return Err(Error::BadRequest("expiry must be in the future".into()));
    }
    if set.precision_m != 0 && !PRECISIONS_M.contains(&set.precision_m) {
        return Err(Error::BadRequest(
            "precision must be 0, 100, 1000, 10000 or 100000 metres".into(),
        ));
    }
    if set.devices.as_ref().is_some_and(Vec::is_empty) {
        return Err(Error::BadRequest("select at least one device".into()));
    }
    Ok(())
}

/// Writes the settings columns and the device selection of a share or link.
pub fn save_settings(db: &Connection, id: i64, owner: i64, set: &ShareSettings) -> Result<()> {
    db.execute(
        "UPDATE shares SET all_devices = ?2, trail_since = ?3, precision_m = ?4 WHERE id = ?1",
        params![id, set.devices.is_none(), set.trail_since, set.precision_m],
    )?;
    db.execute("DELETE FROM share_devices WHERE share_id = ?1", [id])?;
    for device in set.devices.iter().flatten() {
        let added = db.execute(
            "INSERT OR IGNORE INTO share_devices SELECT ?1, id FROM devices WHERE id = ?2 AND user_id = ?3",
            [id, *device, owner],
        )?;
        if added == 0 {
            return Err(Error::BadRequest("no such device".into()));
        }
    }
    Ok(())
}

async fn list_shares(State(s): State<AppState>, user: User) -> Result<Json<Shares>> {
    let db = s.db();
    let query = |other: &str, me: &str| -> rusqlite::Result<Vec<Share>> {
        db.prepare_cached(&format!(
            "SELECT u.username, s.expires_at, s.created_at, {ACCESS_COLS}
             FROM shares s JOIN users u ON u.id = s.{other} WHERE s.{me} = ?1 ORDER BY u.username"
        ))?
        .query_map([user.id], |r| {
            Ok(Share {
                id: r.get(3)?,
                username: r.get(0)?,
                expires_at: r.get(1)?,
                created_at: r.get(2)?,
                settings: settings_at(&db, r, 3)?,
            })
        })?
        .collect()
    };
    Ok(Json(Shares {
        outgoing: query("viewer_id", "owner_id")?,
        incoming: query("owner_id", "viewer_id")?,
    }))
}

async fn create_share(
    State(s): State<AppState>,
    user: User,
    Json(b): Json<NewShare>,
) -> Result<Json<Share>> {
    check_settings(&b.settings, b.expires_at)?;
    let mut db = s.db();
    let (viewer_id, username): (i64, String) = db
        .query_row(
            "SELECT id, username FROM users WHERE username = ?1",
            [b.viewer.trim()],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .optional()?
        .ok_or_else(|| Error::BadRequest("no such user".into()))?;
    if viewer_id == user.id {
        return Err(Error::BadRequest("you cannot share with yourself".into()));
    }
    let tx = db.transaction()?;
    let (id, created_at) = tx.query_row(
        "INSERT INTO shares (owner_id, viewer_id, expires_at, created_at) VALUES (?1, ?2, ?3, ?4)
         ON CONFLICT (owner_id, viewer_id) DO UPDATE SET expires_at = excluded.expires_at
         RETURNING id, created_at",
        params![user.id, viewer_id, b.expires_at, now()],
        |r| Ok((r.get(0)?, r.get(1)?)),
    )?;
    save_settings(&tx, id, user.id, &b.settings)?;
    tx.commit()?;
    Ok(Json(Share {
        id,
        username,
        expires_at: b.expires_at,
        created_at,
        settings: b.settings,
    }))
}

/// Either side can end a share. Guest links have their own endpoint.
async fn delete_share(
    State(s): State<AppState>,
    user: User,
    UrlPath(id): UrlPath<i64>,
) -> Result<Json<()>> {
    let n = s.db().execute(
        "DELETE FROM shares WHERE id = ?1 AND viewer_id IS NOT NULL AND (owner_id = ?2 OR viewer_id = ?2)",
        [id, user.id],
    )?;
    if n == 0 {
        return Err(Error::NotFound);
    }
    Ok(Json(()))
}

/// Everyone else's username, for picking whom to share with.
async fn usernames(State(s): State<AppState>, user: User) -> Result<Json<Vec<String>>> {
    let names = s
        .db()
        .prepare_cached("SELECT username FROM users WHERE id <> ?1 ORDER BY username")?
        .query_map([user.id], |r| r.get(0))?
        .collect::<rusqlite::Result<_>>()?;
    Ok(Json(names))
}

async fn list_users(State(s): State<AppState>, _: Admin) -> Result<Json<Vec<api::User>>> {
    let users = s
        .db()
        .prepare_cached("SELECT id, username, is_admin, created_at FROM users ORDER BY username")?
        .query_map([], |r| {
            Ok(api::User {
                id: r.get(0)?,
                username: r.get(1)?,
                is_admin: r.get(2)?,
                created_at: r.get(3)?,
            })
        })?
        .collect::<rusqlite::Result<_>>()?;
    Ok(Json(users))
}

async fn create_user(
    State(s): State<AppState>,
    Admin(admin): Admin,
    Json(b): Json<NewUser>,
) -> Result<Json<api::User>> {
    admin.check_recent()?;
    let username = crate::check_username(&b.username)?.to_owned();
    auth::check_new_password(&b.password).map_err(|m| Error::BadRequest(m.into()))?;
    let hash = auth::hash_password_async(b.password).await?;
    let db = s.db();
    let id = crate::insert_user(&db, &username, &hash, b.is_admin)?;
    Ok(Json(api::User {
        id,
        username,
        is_admin: b.is_admin,
        created_at: now(),
    }))
}

/// Admins cannot change their own role, so at least one admin always remains.
/// The statements check that the caller is still an admin, so two admins cannot demote or delete each other at once.
async fn set_role(
    State(s): State<AppState>,
    Admin(admin): Admin,
    UrlPath(id): UrlPath<i64>,
    Json(b): Json<SetRole>,
) -> Result<Json<()>> {
    if id == admin.id {
        return Err(Error::BadRequest("you cannot change your own role".into()));
    }
    admin.check_recent()?;
    if s.db().execute(
        "UPDATE users SET is_admin = ?1 WHERE id = ?2 AND (SELECT is_admin FROM users WHERE id = ?3)",
        params![b.is_admin, id, admin.id],
    )? == 0
    {
        return Err(Error::NotFound);
    }
    Ok(Json(()))
}

async fn delete_user(
    State(s): State<AppState>,
    Admin(admin): Admin,
    UrlPath(id): UrlPath<i64>,
) -> Result<Json<()>> {
    if id == admin.id {
        return Err(Error::BadRequest(
            "you cannot delete your own account".into(),
        ));
    }
    if s.db().execute(
        "DELETE FROM users WHERE id = ?1 AND (SELECT is_admin FROM users WHERE id = ?2)",
        [id, admin.id],
    )? == 0
    {
        return Err(Error::NotFound);
    }
    Ok(Json(()))
}

/// The recovery path for a user who lost their password or passkey.
async fn reset_user_password(
    State(s): State<AppState>,
    Admin(admin): Admin,
    UrlPath(id): UrlPath<i64>,
    Json(b): Json<ResetPassword>,
) -> Result<Json<()>> {
    admin.check_recent()?;
    auth::check_new_password(&b.password).map_err(|m| Error::BadRequest(m.into()))?;
    let hash = auth::hash_password_async(b.password).await?;
    let mut db = s.db();
    if db
        .query_row("SELECT 1 FROM users WHERE id = ?1", [id], |_| Ok(()))
        .optional()?
        .is_none()
    {
        return Err(Error::NotFound);
    }
    crate::reset_password(&mut db, id, &hash)?;
    Ok(Json(()))
}

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

    #[test]
    fn point_validation() {
        let now = 1_800_000_000;
        let p = |ts, lat, lon| Point {
            ts,
            lat,
            lon,
            ..Default::default()
        };
        assert!(check_point(&p(now, 48.1, 11.5), now).is_ok());
        assert!(check_point(&p(now, 91.0, 0.0), now).is_err());
        assert!(check_point(&p(now, 0.0, -180.1), now).is_err());
        assert!(check_point(&p(now + 2 * MAX_FUTURE_SECS, 0.0, 0.0), now).is_err());
        assert!(
            check_point(
                &Point {
                    battery: Some(101),
                    ..p(now, 0.0, 0.0)
                },
                now
            )
            .is_err()
        );
    }

    #[test]
    fn coarse_points_hide_motion() {
        let exact = Point {
            ts: 1_800_000_999,
            lat: 48.137_15,
            lon: 11.575_49,
            acc: Some(5.0),
            speed: Some(3.0),
            battery: Some(80),
            ..Default::default()
        };
        let mut p = exact.clone();
        coarsen(&mut p, 0);
        assert_eq!(p, exact);
        coarsen(&mut p, 1000);
        assert_eq!(
            (p.acc, p.speed, p.battery, p.ts),
            (Some(1000.0), None, None, 1_800_000_000)
        );
    }

    #[test]
    fn cells_stay_near_and_stick_through_noise() {
        let (lat, lon) = (48.137_15, 11.575_49);
        let (clat, clon) = cell(None, lat, lon, 1000);
        let (dy, dx) = (
            (clat - lat) * 111_320.0,
            (clon - lon) * 111_320.0 * lat.to_radians().cos(),
        );
        assert!(
            dy.abs() <= 500.0 && dx.abs() <= 510.0,
            "moved {dy} m, {dx} m"
        );
        // A point 0.1 m past the south edge, then noise of 10 m around the edge.
        let edge = clat - 500.0 / 111_320.0;
        let first = cell(None, edge - 0.1 / 111_320.0, clon, 1000);
        let mut c = first;
        for i in 0..20 {
            let noise = if i % 2 == 0 { 10.0 } else { -10.0 };
            c = cell(Some(c), edge + noise / 111_320.0, clon, 1000);
            assert_eq!(c, first);
        }
        // Clearly in the next cell.
        let moved = cell(Some(c), clat, clon, 1000);
        assert_eq!(moved, (clat, clon));
    }

    fn coarse_db() -> Connection {
        let db = crate::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);
             INSERT INTO points (device_id, ts, lat, lon, lat_100, lon_100) VALUES
                 (1, 1000, 0, 0, 0, 0), (1, 1042, 1, 1, 1, 1), (1, 1099, 2, 2, 2, 2), (1, 1100, 3, 3, 3, 3);",
        )
        .unwrap();
        db
    }

    fn coarse(trail_since: i64) -> Access {
        Access {
            owner: 1,
            username: "a".into(),
            share: None,
            all_devices: true,
            trail_since: Some(trail_since),
            precision_m: 100,
        }
    }

    #[test]
    fn coarse_tracks_cover_whole_buckets_only() {
        let db = coarse_db();
        let lats = |a: &Access, from, to| -> Vec<f64> {
            track_points(&db, a, 1, from, to)
                .unwrap()
                .iter()
                .map(|p| p.lat)
                .collect()
        };
        // Any bound inside a bucket gives the whole bucket, so it cannot split 1042 from 1099.
        for to in [1000, 1041, 1042, 1099] {
            assert_eq!(lats(&coarse(0), 1000, to), [0.0, 1.0, 2.0], "to {to}");
        }
        for from in [1001, 1042, 1043, 1099] {
            assert_eq!(lats(&coarse(0), from, 1099), [0.0, 1.0, 2.0], "from {from}");
        }
        // A trail start inside a bucket leaves out the whole bucket.
        assert_eq!(lats(&coarse(1042), 0, 2000), [3.0]);
    }

    #[test]
    fn huge_ranges_are_refused() {
        let db = coarse_db();
        assert!(matches!(
            track_points(&db, &coarse(0), 1, i64::MIN, i64::MAX),
            Err(Error::BadRequest(_))
        ));
        assert!(track_points(&db, &coarse(i64::MIN), 1, i64::MAX - 10, i64::MAX).is_ok());
    }

    async fn admin(s: &AppState, id: i64) -> Result<Json<()>> {
        let caller = Admin(User {
            id,
            username: String::new(),
            is_admin: true,
            session_hash: vec![],
            signed_in_at: now(),
        });
        set_role(
            State(s.clone()),
            caller,
            UrlPath(3 - id),
            Json(SetRole { is_admin: false }),
        )
        .await
    }

    #[tokio::test]
    async fn two_admins_cannot_demote_each_other() {
        let s = crate::test_state();
        s.db()
            .execute_batch(
                "INSERT INTO users (id, username, webauthn_id, is_admin, created_at) VALUES (1, 'a', '1', 1, 0), (2, 'b', '2', 1, 0);",
            )
            .unwrap();
        assert!(admin(&s, 1).await.is_ok());
        // User 2's request passed the extractor before user 1 demoted them.
        assert!(admin(&s, 2).await.is_err());
        let admins: i64 = s
            .db()
            .query_row("SELECT COUNT(*) FROM users WHERE is_admin", [], |r| {
                r.get(0)
            })
            .unwrap();
        assert_eq!(admins, 1);
    }

    #[test]
    fn people_shows_only_shared_devices() {
        let db = crate::test_db();
        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) VALUES (10, 2, 'phone', x'01', 0), (11, 2, 'car', x'02', 0);
             INSERT INTO points (device_id, ts, lat, lon) VALUES (10, 100, 1, 1), (11, 200, 2, 2);
             INSERT INTO shares (id, owner_id, viewer_id, created_at, all_devices) VALUES (5, 2, 1, 0, 1);",
        )
        .unwrap();
        let devices = |db: &Connection| -> Vec<String> {
            people_for(db, 1).unwrap()[1]
                .devices
                .iter()
                .map(|d| d.name.clone())
                .collect()
        };
        let default = |db: &Connection| people_for(db, 1).unwrap()[1].default_device;
        db.execute("UPDATE users SET default_device_id = 11 WHERE id = 2", [])
            .unwrap();
        assert_eq!(devices(&db), ["car", "phone"]);
        assert_eq!(default(&db), Some(11));
        db.execute_batch(
            "UPDATE shares SET all_devices = 0; INSERT INTO share_devices VALUES (5, 10);",
        )
        .unwrap();
        assert_eq!(devices(&db), ["phone"]);
        assert_eq!(default(&db), None);
        db.execute("DELETE FROM devices WHERE id = 11", []).unwrap();
        let cleared: Option<i64> = db
            .query_row(
                "SELECT default_device_id FROM users WHERE id = 2",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(cleared, None);
    }

    #[test]
    fn people_respects_share_expiry() {
        let db = crate::test_db();
        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);
             INSERT INTO shares (owner_id, viewer_id, expires_at, created_at) VALUES (2, 1, NULL, 0), (3, 1, 1, 0);",
        )
        .unwrap();
        let names: Vec<_> = people_for(&db, 1)
            .unwrap()
            .into_iter()
            .map(|p| p.username)
            .collect();
        assert_eq!(names, ["a", "b"]);
    }
}