"""Identity tables: the app's OWN account row, and the invite allowlist.

The app owns both. BW Auth supplies a verified identity (username, email, name)
and nothing else — it never assigns a role and never decides who is admitted.

`users.external_username` is the BW username, and it is the join key everywhere
in this app. It is stable across email and display-name changes, which is why it
is the key rather than the email (PATTERN-B.md, "key every app row on the BW
username").
"""

from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, Index, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column

from .base import Base


class User(Base):
    """An app account, materialized the first time a BW identity signs in.

    BW-derived columns (`external_username`, `email`, `display_name`) are
    refreshed on every login. App-owned columns (`role`, and anything added
    later) are NOT — the login upsert is column-scoped so re-login can never
    clobber a role or a profile field the app set. See services/identity.py.
    """

    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)

    # The BW account username — the app-wide join key. Lower-cased on write.
    external_username: Mapped[str] = mapped_column(
        String(120), unique=True, nullable=False
    )

    # BW-derived mirror fields, refreshed on each login.
    email: Mapped[str] = mapped_column(String(320), default="", nullable=False)
    display_name: Mapped[str] = mapped_column(String(160), default="", nullable=False)

    # App-owned. One of ROLE_SUPERADMIN / ROLE_USER / ROLE_NONE (see
    # services/roles.py). ROLE_NONE means signed in but NOT invited — the
    # default-deny state, stored explicitly rather than implied by absence.
    role: Mapped[str] = mapped_column(String(32), default="none", nullable=False)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    last_login_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )


class Invite(Base):
    """The invite allowlist — this app is invite-only (plan D3).

    Keyed on the BW account's EMAIL rather than username: an invite is written
    before the person has ever signed in, and at that point their BW username
    may not exist yet or may not be known to whoever issues the invite. The
    email is what the inviter actually has. On first sign-in the invite is
    matched to the verified BW email and the resulting `users` row is keyed on
    the username from then on.
    """

    __tablename__ = "invites"

    id: Mapped[int] = mapped_column(primary_key=True)

    # Lower-cased on write; matched against the verified BW identity's email.
    email: Mapped[str] = mapped_column(String(320), unique=True, nullable=False)

    # The role this invite grants on first sign-in. Seeded once into users.role
    # and never re-applied — the app owns the role after that.
    role: Mapped[str] = mapped_column(String(32), default="user", nullable=False)

    # Free-text: who this is and why they were invited. Operator context only.
    note: Mapped[str] = mapped_column(Text, default="", nullable=False)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    # Stamped the first time this invite admits someone. A non-null value means
    # the invite has been used; it is kept (not deleted) as an audit record.
    redeemed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )


Index("ix_users_role", User.role)