-- opentracker initial schema.
--
-- Every table is STRICT: SQLite's default type affinity would happily store the
-- string 'north' in an INTEGER latitude column, and this database is written to
-- by a hot path that must never be the place a type error is discovered.
--
-- The central decision here: **the position stream belongs to the account.** A
-- token is a credential that authorises writing into its owner's stream. It is
-- never an identity — it does not appear in a point's key, in a share, or on the
-- map. One person is one dot, regardless of which pocket the phone is in.
--
-- Coordinates are integers (degrees × 1e7) end to end — protocol, database, JSON,
-- UI — so there is no float-formatting drift anywhere in the system.

CREATE TABLE users (
    id            INTEGER PRIMARY KEY,
    -- NOCASE so 'Marc' and 'marc' are the same account, which is what users
    -- assume, and which closes a whole class of impersonation confusion.
    username      TEXT    NOT NULL COLLATE NOCASE UNIQUE,
    -- argon2id PHC string; carries its own parameters so the policy can change
    -- without invalidating existing hashes.
    pw_hash       TEXT    NOT NULL,
    display_name  TEXT    NOT NULL,
    is_admin      INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0, 1)),
    -- Set rather than deleting the row: points and shares reference it.
    disabled_at   INTEGER,
    created_at    INTEGER NOT NULL,
    -- Sessions and tokens issued before this are treated as revoked, which is
    -- how "changing my password logs everything else out" is enforced without
    -- having to enumerate them.
    pw_changed_at INTEGER NOT NULL
) STRICT;

-- One row per login. Purely a credential plus per-phone telemetry.
CREATE TABLE tokens (
    -- The u64 that travels in every datagram header. Random, not sequential:
    -- guessing one must not be easier than guessing a key.
    token_id       INTEGER PRIMARY KEY,
    user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- The 32-byte token secret, wrapped with the server key from OT_SECRET_KEY
    -- (AAD = token_id), so a stolen .db alone yields no working keys.
    key_wrapped    BLOB    NOT NULL,
    name           TEXT    NOT NULL,
    platform       TEXT    NOT NULL DEFAULT '',
    app_version     INTEGER,
    os_api_level    INTEGER,
    config_version  INTEGER NOT NULL DEFAULT 1,
    config_json     TEXT    NOT NULL DEFAULT '{}',
    last_seen_at    INTEGER,
    last_src_ip     TEXT,
    last_src_port   INTEGER,
    last_transport  TEXT,
    created_at      INTEGER NOT NULL,
    created_ip      TEXT,
    revoked_at      INTEGER
) STRICT;

CREATE INDEX tokens_user ON tokens(user_id);
-- Drives the 30-day staleness sweep.
CREATE INDEX tokens_last_seen ON tokens(last_seen_at);

CREATE TABLE points (
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- Unix seconds from the client's wall clock, stored as sent.
    ts          INTEGER NOT NULL,
    lat         INTEGER NOT NULL,
    lon         INTEGER NOT NULL,
    acc_dm      INTEGER,
    alt_m       INTEGER,
    spd_cms     INTEGER,
    brg_cdeg    INTEGER,
    bat_pct     INTEGER,
    flags       INTEGER NOT NULL DEFAULT 0,
    recv_at     INTEGER NOT NULL,
    -- Provenance only: nullable, never in a key, never read by the UI. It exists
    -- so "which phone sent this?" is answerable while debugging. Deliberately
    -- NOT a foreign key with CASCADE — a token being deleted by the staleness
    -- sweep must not take history with it.
    src_token_id INTEGER,

    -- The dedup that makes retries and replays both harmless. A replayed
    -- datagram carries a ts that already exists and collapses into the row
    -- already there, which is why this schema needs no replay window.
    PRIMARY KEY (user_id, ts)
) STRICT, WITHOUT ROWID;

-- For the retention sweep, which scans by age across all users.
CREATE INDEX points_ts ON points(ts);

-- Current position per *account*, in its own table so the retention GC can never
-- delete the live marker.
CREATE TABLE user_latest (
    user_id      INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    ts           INTEGER NOT NULL,
    lat          INTEGER NOT NULL,
    lon          INTEGER NOT NULL,
    acc_dm       INTEGER,
    alt_m        INTEGER,
    spd_cms      INTEGER,
    brg_cdeg     INTEGER,
    bat_pct      INTEGER,
    flags        INTEGER NOT NULL DEFAULT 0,
    recv_at      INTEGER NOT NULL,
    src_token_id INTEGER
) STRICT;

-- You share *yourself*, not a phone: there is no per-device dimension here, and
-- a share targets exactly one other account.
CREATE TABLE shares (
    id              INTEGER PRIMARY KEY,
    owner_user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    viewer_user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    trail_visible   INTEGER NOT NULL DEFAULT 1 CHECK (trail_visible IN (0, 1)),
    -- Metres to round the position to before showing it. 0 = exact.
    precision_m     INTEGER NOT NULL DEFAULT 0,
    -- NULL means no expiry. Enforced at query time, never by a background job,
    -- so a stalled job can never leak a position.
    expires_at      INTEGER,
    revoked_at      INTEGER,
    created_at      INTEGER NOT NULL,

    CHECK (owner_user_id <> viewer_user_id)
) STRICT;

CREATE INDEX shares_owner ON shares(owner_user_id);
CREATE INDEX shares_viewer_user ON shares(viewer_user_id);

-- OSM tile proxy cache metadata. The bytes live on the filesystem at
-- {cache_dir}/tiles/{z}/{x}/{y}.png; this table is the index and the accounting
-- that makes max_cache_bytes enforceable.
CREATE TABLE tiles (
    z             INTEGER NOT NULL,
    x             INTEGER NOT NULL,
    y             INTEGER NOT NULL,
    etag          TEXT,
    last_modified TEXT,
    fetched_at    INTEGER NOT NULL,
    expires_at    INTEGER NOT NULL,
    bytes         INTEGER NOT NULL,
    last_access   INTEGER NOT NULL,
    PRIMARY KEY (z, x, y)
) STRICT, WITHOUT ROWID;

-- Drives least-recently-used eviction.
CREATE INDEX tiles_last_access ON tiles(last_access);

CREATE TABLE audit_log (
    id         INTEGER PRIMARY KEY,
    at         INTEGER NOT NULL,
    -- Nullable: failed logins have no authenticated user yet.
    user_id    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    action     TEXT    NOT NULL,
    detail     TEXT    NOT NULL DEFAULT '',
    src_ip     TEXT
) STRICT;

CREATE INDEX audit_log_at ON audit_log(at);

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
) STRICT, WITHOUT ROWID;