-- Phones as a second factor (oxygenhub#356): a phone's Keystore key, kept

-- with the sign-in it made (gone when that token is revoked); the

-- approvals it is asked for, each a number to match; and when each

-- browser session last proved a second factor, for the sudo step.

CREATE TABLE authenticators (

    token_id     BIGINT PRIMARY KEY REFERENCES tokens(id) ON DELETE CASCADE,

    account_id   BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    name         TEXT NOT NULL,

    public_key   TEXT NOT NULL,

    created_ms   BIGINT NOT NULL,

    last_used_ms BIGINT NOT NULL DEFAULT 0

);

CREATE INDEX authenticators_by_account ON authenticators (account_id);

CREATE TABLE approvals (

    id          TEXT PRIMARY KEY,

    account_id  BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    kind        TEXT NOT NULL,

    what        TEXT NOT NULL,

    number      BIGINT NOT NULL,

    choices     TEXT NOT NULL,

    session_id  BIGINT,

    created_ms  BIGINT NOT NULL,

    expires_ms  BIGINT NOT NULL,

    state       TEXT NOT NULL DEFAULT 'pending',

    decided_ms  BIGINT NOT NULL DEFAULT 0,

    decided_by  BIGINT

);

CREATE INDEX approvals_by_account ON approvals (account_id, state);

ALTER TABLE sessions ADD COLUMN second_factor_ms BIGINT NOT NULL DEFAULT 0;