-- Required Postgres extensions:
--   pgcrypto  → gen_random_uuid() for primary keys
--   citext    → case-insensitive emails (so "Foo@x.com" == "foo@x.com")
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

-- Users table. Stores all auth + profile data.
--
-- Fields:
--   email              unique, citext (case-insensitive)
--   password_hash      bcrypt hash; never logged or returned in API responses
--   email_verified_at  NULL until the user clicks the verification link
--   avatar_url         absolute URL to a file in the R2 bucket (or NULL)
--   token_version      incremented whenever issued JWTs should be invalidated
--                      (password change, forced logout). JWTs carry this value
--                      as a "tv" claim; mismatch ⇒ middleware rejects the token.
--   role               "user" | "admin" | "moderator"
CREATE TABLE IF NOT EXISTS users (
    id                  UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    email               CITEXT       NOT NULL UNIQUE,
    password_hash       TEXT         NOT NULL,
    name                VARCHAR(120) NOT NULL,
    avatar_url          TEXT,
    role                VARCHAR(32)  NOT NULL DEFAULT 'user',
    email_verified_at   TIMESTAMPTZ,
    token_version       INTEGER      NOT NULL DEFAULT 1,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS users_created_at_idx ON users (created_at DESC);