-- Users table. Email is unique case-insensitive. password_hash is nullable
-- because future SSO-only users may have no local password (azure_oid set
-- instead). The CHECK ensures every user has at least one credential path.
CREATE TABLE IF NOT EXISTS users (
  id            SERIAL PRIMARY KEY,
  email         TEXT NOT NULL,
  password_hash TEXT,
  role          TEXT NOT NULL CHECK (role IN ('admin', 'user')),
  azure_oid     TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_login_at TIMESTAMPTZ,
  CONSTRAINT users_has_credential CHECK (password_hash IS NOT NULL OR azure_oid IS NOT NULL)
);

CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_uniq ON users (lower(email));
CREATE UNIQUE INDEX IF NOT EXISTS users_azure_oid_uniq   ON users (azure_oid) WHERE azure_oid IS NOT NULL;