-- Access control for the API and web UI: users, groups, and glob-based
-- topic access rules (allow/deny; see shared/access.py for semantics).

-- NOTE: user/group administration happens in the web UI (or SQL). Rule
-- patterns are globs matched against the full topic/tag name, where
-- '*' matches any character sequence (including '.') and everything
-- else is literal. Example: '11001.*_Value' grants everything under
-- '11001.' ending in '_Value'; user rules can override group rules via
-- deny, which always wins over allow.

CREATE TABLE IF NOT EXISTS users (
    id                   BIGSERIAL PRIMARY KEY,
    username             TEXT        NOT NULL UNIQUE,
    password_hash        TEXT        NOT NULL,
    role                 TEXT        NOT NULL DEFAULT 'user'
                         CHECK (role IN ('user', 'admin')),
    disabled_at          TIMESTAMPTZ,
    must_change_password BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS groups (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT        NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS user_groups (
    user_id  BIGINT NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id)
);

CREATE TABLE IF NOT EXISTS access_rules (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT REFERENCES users(id)  ON DELETE CASCADE,
    group_id   BIGINT REFERENCES groups(id) ON DELETE CASCADE,
    kind       TEXT NOT NULL CHECK (kind IN ('allow', 'deny')),
    pattern    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK ((user_id IS NULL) <> (group_id IS NULL))  -- exactly one owner
);

CREATE INDEX IF NOT EXISTS idx_access_rules_user ON access_rules (user_id);
CREATE INDEX IF NOT EXISTS idx_access_rules_group ON access_rules (group_id);

-- Sessions issued by the web UI; the API validates the same table, so a
-- web login is also an API login. Only a SHA-256 of the token is stored.
CREATE TABLE IF NOT EXISTS sessions (
    token_hash TEXT        PRIMARY KEY,
    user_id    BIGINT      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    csrf_token TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    seen_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions (user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions (expires_at);

-- Login attempt throttling for the web UI.
CREATE TABLE IF NOT EXISTS login_attempts (
    ip        TEXT        PRIMARY KEY,
    attempts  INTEGER     NOT NULL DEFAULT 0,
    last_seen TIMESTAMPTZ NOT NULL DEFAULT now()
);