"""AdminUser, AuditLog and RateLimit.

AdminUser used to carry a Google email and a role and nothing else, because v1 authenticated
through Google only (design D7). `add-admin-access-control` reverses that half of D7: an
account is now either a Google account or a username-and-password account, and the password
columns below exist for the second kind. TOTP is still absent — this change restored
passwords, not the whole of PRD FR-4.1.
"""

import uuid
from datetime import datetime
from typing import Any

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

from app.models.base import Base, TimestampTZ, created_at_col, pg_enum, updated_at_col, uuid_pk
from app.models.enums import AdminRole, AdminStatus, AuthMethod


class AdminUser(Base):
    """One administrator, of one of two kinds (add-admin-access-control design D13, D16).

    A **Google** account holds no credential here at all — Google keeps it, and every
    password column below stays null forever. It may arrive by itself: a first Google
    sign-in for an unknown verified address creates the row as `PENDING`, which grants
    nothing and waits in the roster's approval queue.

    A **password** account is created by a super admin and never self-registers. It is the
    first credential in this system that can be attacked from the open internet with a
    wordlist, which is why `failed_login_count`/`locked_until` exist and why the hash is
    Argon2id.

    `status` replaced `is_active`, `auth_method` is fixed at creation, and the role is still
    re-read from this row on every request rather than carried in the session — so demotion
    and withdrawal apply to the very next request instead of at token expiry.
    """

    __tablename__ = "admin_user"
    __table_args__ = (
        # Case-insensitive uniqueness in the database, not in the endpoint. Two concurrent
        # creates of "Fatima" and "fatima" both pass an application-level check and both
        # insert; this loses that race for them.
        Index(
            "uq_admin_user_username_lower",
            func.lower(column("username")),
            unique=True,
            postgresql_where=text("username IS NOT NULL"),
        ),
    )

    id: Mapped[uuid.UUID] = uuid_pk()
    email: Mapped[str] = mapped_column(String(320), unique=True, nullable=False)
    name: Mapped[str | None] = mapped_column(Text)
    role: Mapped[AdminRole] = mapped_column(
        pg_enum(AdminRole, "admin_role"), default=AdminRole.HOST, nullable=False
    )
    status: Mapped[AdminStatus] = mapped_column(
        pg_enum(AdminStatus, "admin_status"), default=AdminStatus.PENDING, nullable=False
    )
    auth_method: Mapped[AuthMethod] = mapped_column(
        pg_enum(AuthMethod, "auth_method"), default=AuthMethod.GOOGLE, nullable=False
    )

    #: Password accounts only. Uniqueness is the functional index above, because comparing
    #: `Username` and `username` as different logins is a phishing affordance, not a feature.
    username: Mapped[str | None] = mapped_column(String(64))

    #: Argon2id. Null on every Google account, and never rendered into any response — the
    #: roster schemas cannot serialise it and a test greps the emitted log for it.
    password_hash: Mapped[str | None] = mapped_column(Text)

    #: The confinement flag (design D14). True means a super admin set this password, so the
    #: holder gets a session and every admin surface but changing it refuses them.
    must_change_password: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    #: A temporary password nobody used is a standing credential, known to at least two
    #: people, sitting in whatever channel it was sent through. Null on a chosen password.
    password_expires_at: Mapped[datetime | None] = mapped_column(TimestampTZ)
    password_set_at: Mapped[datetime | None] = mapped_column(TimestampTZ)

    #: Per-account lockout. Paired with a per-IP bucket in `rate_limit`, because these stop
    #: two different attacks: guessing one password, and spreading a few guesses over many
    #: accounts to stay under every per-account threshold.
    failed_login_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    locked_until: Mapped[datetime | None] = mapped_column(TimestampTZ)

    #: Bumped to drop every outstanding session (design D17). The one revocation the
    #: per-request row lookup cannot express: a still-valid token for a still-active account
    #: whose password has since changed. Incremented by a self-service change, by a super
    #: admin issuing a temporary password, and by withdrawal.
    #:
    #: A counter rather than a "valid from" timestamp, because a JWT's `iat` is a whole
    #: number of seconds: any timestamp comparison either signs out the token issued
    #: alongside the change, or leaves a sub-second window in which a token survives a
    #: revocation. An integer carried in the token and compared for equality has neither
    #: problem and no clock semantics to reason about.
    session_epoch: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    #: When the account first appeared. For a pending Google account that is the sign-in
    #: that created it, which is what the roster shows as "first seen".
    first_seen_at: Mapped[datetime | None] = mapped_column(TimestampTZ)
    last_login_at: Mapped[datetime | None] = mapped_column(TimestampTZ)

    created_at: Mapped[datetime] = created_at_col()
    updated_at: Mapped[datetime] = updated_at_col()

    @property
    def is_active(self) -> bool:
        """Kept as a read-only convenience for call sites that only ask the old question.

        Deliberately not writable: setting it could only guess between PENDING and
        WITHDRAWN, and that guess is exactly the distinction `status` exists to preserve.
        """
        return self.status is AdminStatus.ACTIVE


class AuditLog(Base):
    """Append-only record of admin actions, including rejected sign-ins and every CSV
    export — that is personal data leaving the system (spec csv-export FR-5.7)."""

    __tablename__ = "audit_log"

    id: Mapped[uuid.UUID] = uuid_pk()
    admin_user_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("admin_user.id", ondelete="SET NULL"), index=True
    )
    actor_email: Mapped[str | None] = mapped_column(String(320))
    action: Mapped[str] = mapped_column(String(80), nullable=False, index=True)
    entity_type: Mapped[str | None] = mapped_column(String(60))
    entity_id: Mapped[uuid.UUID | None] = mapped_column()
    before: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
    after: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
    ip: Mapped[str | None] = mapped_column(String(64))
    created_at: Mapped[datetime] = created_at_col()


class RateLimit(Base):
    """Per-IP counters for public endpoints (design D10).

    Postgres-backed rather than in-process because the API may run more than one worker
    process — in-memory would silently allow N times the intended limit.
    """

    __tablename__ = "rate_limit"
    __table_args__ = (
        UniqueConstraint("bucket", "ip_hash", "window_start", name="uq_rate_limit_window"),
        Index("ix_rate_limit_cleanup", "window_start"),
    )

    id: Mapped[uuid.UUID] = uuid_pk()
    bucket: Mapped[str] = mapped_column(String(40), nullable=False)
    ip_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    window_start: Mapped[datetime] = mapped_column(TimestampTZ, nullable=False)
    count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    created_at: Mapped[datetime] = created_at_col()
    updated_at: Mapped[datetime] = updated_at_col()