-- Auth: passwords on users, server-side sessions, and bot API tokens.
--
-- Sessions and api_tokens both store sha256(raw_token) as bytea. The raw
-- token is held by the client (cookie for sessions, Authorization bearer
-- header for tokens); the server only ever sees the hash at rest, so a
-- read of the DB does not yield reusable credentials.

ALTER TABLE users ADD COLUMN password_hash text NOT NULL;

CREATE TABLE sessions (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  bytea NOT NULL UNIQUE,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL
);

CREATE INDEX sessions_user_idx ON sessions (user_id);
CREATE INDEX sessions_expires_idx ON sessions (expires_at);

CREATE TABLE api_tokens (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name          text NOT NULL,
    token_hash    bytea NOT NULL UNIQUE,
    created_at    timestamptz NOT NULL DEFAULT now(),
    last_used_at  timestamptz
);

CREATE INDEX api_tokens_user_idx ON api_tokens (user_id, created_at DESC);