-- Users directory (per ADR-0020 §"v1 scope — User list").
--
-- Persistent ledger of identities the BFF has seen sign in to either
-- portal-shell or portal-admin. Upserted by `UserDirectoryService`
-- at every sign-in. Read by the future `GET /api/admin/users` admin
-- endpoint. Entra ID is the source of truth for identity; this
-- table is the BFF's local cache so the admin UI does not have to
-- re-query the IdP at every page render.

-- CreateTable
CREATE TABLE "users" (
    "oid" TEXT NOT NULL,
    "tid" TEXT NOT NULL,
    "audience" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "display_name" TEXT NOT NULL,
    "first_seen_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "last_seen_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "users_pkey" PRIMARY KEY ("oid")
);

-- DESC index supports the default admin sort ("most recently active first")
-- without a server-side sort.
CREATE INDEX "users_last_seen_at_idx" ON "users"("last_seen_at" DESC);

-- Plain (default ASC) index supports prefix matching on the
-- admin-side username filter.
CREATE INDEX "users_username_idx" ON "users"("username");
