"""Accounts: credentials, levels, members, grants, sessions, tokens, and the audit log.

Sources of truth: this module, `app/models/catalog.py` (`Account`, the one identity table and
the FK target of every who-column), `alembic/versions/b5c6d7e8f9a0_*.py` (migration #4),
the design `.logs/planning/accounts-2026-09.md` §4.2 to §4.4, §4.10 and §6.

Why the credential lives off the principal row: no query over `accounts` (the People list,
search, the mention directory, an audit join) can ever carry a password hash. Why the kit's
membership is three DFP-owned tables the vendored store maps onto: consumers later hold an
`accounts` row and no membership row, so the permission tables stay staff-sized at any scale.
Why sessions are opaque tokens hashed at rest: a second host or worker needs no shared
signing secret, and a database read of the table yields nothing a browser could present.
JSON columns are `JSON().with_variant(JSONB, "postgresql")` so the test suite can create the
same tables on an in-memory SQLite.
"""

from datetime import datetime

from sqlalchemy import (
    JSON,
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base

JSONVariant = JSON().with_variant(JSONB(), "postgresql")


class AccountCredential(Base):
    """One row per account that can sign in (or is waiting to): the argon2id hash, the
    must-change flag a handed-over password carries, the lockout counters, and the Google
    subject a later deliberate link stores. A NULL hash is an invited account."""

    __tablename__ = "account_credentials"

    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), primary_key=True)
    password_hash: Mapped[str | None] = mapped_column(Text)
    password_set_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    must_change_password: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="false"
    )
    failed_logins: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    # When the current run of failures began: 8 within 15 minutes lock for 15 minutes.
    failed_window_started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    locked_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    google_sub: Mapped[str | None] = mapped_column(String(64), unique=True)


class AccountLevel(Base):
    """A named set of permissions and the levels its holders may assign (the kit's `levels`)."""

    __tablename__ = "account_levels"

    name: Mapped[str] = mapped_column(String(64), primary_key=True)
    permissions: Mapped[list] = mapped_column(JSONVariant, nullable=False, server_default="[]")
    assignable: Mapped[list] = mapped_column(JSONVariant, nullable=False, server_default="[]")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )


class AccountMember(Base):
    """An account's app-wide level (the kit's `members`). `active` false is the kit's soft
    delete; the sink that hears `remove_member` also disables the account, because neither
    login nor the session loader reads this table."""

    __tablename__ = "account_members"

    username: Mapped[str] = mapped_column(
        String(64), ForeignKey("accounts.username"), primary_key=True
    )
    level: Mapped[str] = mapped_column(String(64), ForeignKey("account_levels.name"), nullable=False)
    all_instances: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="false"
    )
    active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default="true")
    added_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class AccountGrant(Base):
    """A per-instance level (the kit's `grants`). Instances are brands, read-only; unused
    until a brand-manager tier turns `has_instances` on."""

    __tablename__ = "account_grants"

    username: Mapped[str] = mapped_column(
        String(64), ForeignKey("accounts.username"), primary_key=True
    )
    instance_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    level: Mapped[str] = mapped_column(String(64), ForeignKey("account_levels.name"), nullable=False)


class AuthSession(Base):
    """A signed-in browser: the sha256 of an opaque token, absolute expiry, revocation, and
    the kit's View As state (`acting_as`, `acting_mode`) so impersonation survives a restart
    and is visible to the owner as one SELECT."""

    __tablename__ = "sessions"

    token_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False, index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    revoked_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    revoke_reason: Mapped[str | None] = mapped_column(String(40))
    amr: Mapped[str | None] = mapped_column(String(40))
    acting_as: Mapped[str | None] = mapped_column(String(64), ForeignKey("accounts.username"))
    acting_mode: Mapped[str | None] = mapped_column(String(12))
    ip: Mapped[str | None] = mapped_column(String(64))
    user_agent: Mapped[str | None] = mapped_column(String(300))
    data: Mapped[dict] = mapped_column(JSONVariant, nullable=False, server_default="{}")

    __table_args__ = (
        Index(
            "ix_sessions_acting_as", "acting_as",
            postgresql_where=acting_as.isnot(None), sqlite_where=acting_as.isnot(None),
        ),
    )


class AccountToken(Base):
    """A welcome or reset link's token, hashed, single use, 48 hours. The token itself travels
    only in the link's fragment and a JSON body, never a path or query (the access log prints
    every request line)."""

    __tablename__ = "account_tokens"

    token_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"), nullable=False, index=True)
    mode: Mapped[str] = mapped_column(String(12), nullable=False)  # invite | reset
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_by_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))


class AuditLog(Base):
    """Every state change that names a person: who really acted (`account_id`), who they were
    acting as (`acting_as_id`), from which session, and a detail dict. Never a password, a
    token, or a typed unknown username."""

    __tablename__ = "audit_log"

    id: Mapped[int] = mapped_column(primary_key=True)
    at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False, index=True
    )
    account_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"), index=True)
    acting_as_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    session_hash: Mapped[str | None] = mapped_column(String(64))
    action: Mapped[str] = mapped_column(String(60), nullable=False, index=True)
    entity_type: Mapped[str | None] = mapped_column(String(40))
    entity_key: Mapped[str | None] = mapped_column(String(120))
    detail: Mapped[dict | None] = mapped_column(JSONVariant)
    ip: Mapped[str | None] = mapped_column(String(64))


# `Override` is the ledger's Decision for one release (`from app.models import Override`); the
# `overrides` table was dropped by migration d2e3f4a5b6c7 (Stream K2).