-- PatchBay SQLite schema, shared by patchbayd (embedded at build time) and the
-- web frontend (copied into the package on install). Must stay idempotent.

PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- Users besides the sysop (who lives in patchbay.conf).
CREATE TABLE IF NOT EXISTS users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email    TEXT NOT NULL,
    pwhash   TEXT NOT NULL,
    created  INTEGER NOT NULL
);

-- Pending email 2FA logins.
CREATE TABLE IF NOT EXISTS login_codes (
    token    TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    codehash TEXT NOT NULL,
    expires  INTEGER NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0
);

-- Failed login attempts for rate limiting (per remote address).
CREATE TABLE IF NOT EXISTS login_failures (
    addr TEXT NOT NULL,
    ts   INTEGER NOT NULL
);

-- Authorised clients. pubkey is "type base64" without comment.
CREATE TABLE IF NOT EXISTS clients (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL UNIQUE,
    pubkey    TEXT NOT NULL UNIQUE,
    added_by  TEXT NOT NULL,
    created   INTEGER NOT NULL,
    hostname  TEXT NOT NULL DEFAULT '',
    last_addr TEXT NOT NULL DEFAULT '',
    last_seen INTEGER NOT NULL DEFAULT 0
);

-- Patch graph. type: client_source, client_sink, public_sink, splitter,
-- tunnel_source, tunnel_sink. host: service address (sources) / bind address
-- (sinks). Tunnel nodes use iface; their client_id NULL means the target.
-- origin (sinks): '' | 'proxy_v2' (PROXY v2 header to the service) |
-- 'transparent' (source spoofing, source must be on a client).
-- Databases created before iface/origin existed are migrated by both programs.
CREATE TABLE IF NOT EXISTS nodes (
    id        INTEGER PRIMARY KEY,
    type      TEXT NOT NULL,
    client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
    host      TEXT NOT NULL DEFAULT '',
    port      INTEGER NOT NULL DEFAULT 0,
    proto     TEXT NOT NULL DEFAULT 'tcp',
    label     TEXT NOT NULL DEFAULT '',
    iface     TEXT NOT NULL DEFAULT '',
    origin    TEXT NOT NULL DEFAULT '',
    x         REAL NOT NULL DEFAULT 0,
    y         REAL NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS links (
    id        INTEGER PRIMARY KEY,
    from_node INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    to_node   INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    UNIQUE (to_node)
);

-- Listening sockets per host, reported by the daemons. client_id 0 = target.
CREATE TABLE IF NOT EXISTS services (
    client_id INTEGER NOT NULL,
    proto     TEXT NOT NULL,
    addr      TEXT NOT NULL,
    port      INTEGER NOT NULL,
    pid       INTEGER NOT NULL,
    process   TEXT NOT NULL,
    updated   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS services_client ON services (client_id);

-- tun interfaces per host as reported by the daemons. client_id 0 = target.
CREATE TABLE IF NOT EXISTS interfaces (
    client_id INTEGER NOT NULL,
    name      TEXT NOT NULL,
    addr      TEXT NOT NULL,
    updated   INTEGER NOT NULL
);

-- Traffic per connection (= per sink node). tier: 'm' minute, 'h' hour, 'd' day.
-- bytes_in: towards the source service, bytes_out: back to the connecting peer.
CREATE TABLE IF NOT EXISTS stats (
    sink_id   INTEGER NOT NULL,
    tier      TEXT NOT NULL,
    ts        INTEGER NOT NULL,
    bytes_in  INTEGER NOT NULL DEFAULT 0,
    bytes_out INTEGER NOT NULL DEFAULT 0,
    conns     INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (sink_id, tier, ts)
);
CREATE INDEX IF NOT EXISTS stats_tier_ts ON stats (tier, ts);

INSERT OR IGNORE INTO settings (key, value) VALUES
    ('stats_minute_hours', '48'),
    ('stats_hour_days', '90'),
    ('stats_day_days', '0');