-- V2 — user accounts, roles, and one-time tokens (activation / password reset).
-- refresh_tokens arrive in a later migration (Phase 5). All timestamps timestamptz (UTC).

CREATE TABLE users (
    id                    uuid         NOT NULL,
    email                 varchar(320) NOT NULL,
    password              varchar(255) NOT NULL,
    activated_account     boolean      NOT NULL DEFAULT false,
    activated_at          timestamptz,
    failed_login_attempts int          NOT NULL DEFAULT 0,
    locked_until          timestamptz,
    created_at            timestamptz  NOT NULL,
    updated_at            timestamptz  NOT NULL,
    version               bigint       NOT NULL,
    CONSTRAINT pk_users PRIMARY KEY (id)
);

COMMENT ON COLUMN users.password IS 'Argon2id password hash (PHC string); never a plaintext password';

-- Case-insensitive uniqueness on email (§4).
CREATE UNIQUE INDEX ux_users_email_lower ON users (lower(email));

CREATE TABLE user_roles (
    user_id uuid        NOT NULL,
    role    varchar(64) NOT NULL,
    CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE one_time_tokens (
    id          uuid        NOT NULL,
    user_id     uuid        NOT NULL,
    token_hash  varchar(64) NOT NULL,
    type        varchar(32) NOT NULL,
    expires_at  timestamptz NOT NULL,
    consumed_at timestamptz,
    created_at  timestamptz NOT NULL,
    CONSTRAINT pk_one_time_tokens PRIMARY KEY (id),
    CONSTRAINT fk_ott_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT ux_ott_token_hash UNIQUE (token_hash)
);

CREATE INDEX ix_ott_user_type ON one_time_tokens (user_id, type);