DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id                   TEXT PRIMARY KEY,
    handle               TEXT NOT NULL UNIQUE,
    full_name            TEXT,
    email                TEXT UNIQUE,
    website              TEXT,
    roles                TEXT NOT NULL, -- json array of user roles
    avatar_asset_id      TEXT,

    status               TEXT CHECK(status IN ('Invited', 'Active', 'Disabled')) NOT NULL DEFAULT 'Disabled',
    password_hash        TEXT,
    reset_password_token TEXT,
    last_login_at        DATETIME,
    created_at           DATETIME NOT NULL
);

DROP TABLE IF EXISTS user_assets;
CREATE TABLE user_assets (
    id                   TEXT PRIMARY KEY,
    user_id              TEXT NOT NULL,
    mime_type            TEXT NOT NULL,
    fingerprint          TEXT NOT NULL,
    name                 TEXT, -- file name
    content              BLOB NOT NULL,
    created_at           DATETIME NOT NULL
);

DROP TABLE IF EXISTS authorizations;
CREATE TABLE authorizations (
    id                  TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL,
    client_id           TEXT NOT NULL,
    scopes              TEXT, -- json array of app scope (permissions)
    code                TEXT,
    nonce               TEXT, -- code used to associate client session to id_token

    last_used_at        DATETIME,
    created_at          DATETIME NOT NULL
);