"""ORM model for the users table.

Lives in the harness persistence package so it is picked up by
``Base.metadata.create_all()`` alongside ``threads_meta``, ``runs``,
``run_events``, and ``feedback``. Using the shared engine means:

- One SQLite/Postgres database, one connection pool
- One schema initialisation codepath
- Consistent async sessions across auth and persistence reads
"""

from __future__ import annotations

from datetime import UTC, datetime

from sqlalchemy import Boolean, DateTime, Index, String, text
from sqlalchemy.orm import Mapped, mapped_column

from deerflow.persistence.base import Base
from deerflow.persistence.types import BeijingDateTime


class AuthInitializationRow(Base):
    """Singleton transaction claim for the public first-admin setup endpoint."""

    __tablename__ = "auth_initialization"
    id: Mapped[str] = mapped_column(String(32), primary_key=True)


class AuthUsernameRow(Base):
    """Reserve new account local-parts across concurrent email-domain variants."""

    __tablename__ = "auth_usernames"
    username: Mapped[str] = mapped_column(String(128), primary_key=True)


class AuthServiceNonceRow(Base):
    """One-time service exchange nonce shared by every gateway worker."""

    __tablename__ = "auth_service_nonces"
    nonce_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
    client_id: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)


class UserRow(Base):
    __tablename__ = "users"

    # UUIDs are stored as 36-char strings for cross-backend portability.
    id: Mapped[str] = mapped_column(String(36), primary_key=True)

    email: Mapped[str] = mapped_column(String(191), unique=True, nullable=False, index=True)
    # Canonical local part used by SSO / username login. Keeping it separately
    # avoids applying LOWER()/LIKE to every users.email row on each login.
    username_key: Mapped[str | None] = mapped_column(String(128), nullable=True)
    password_hash: Mapped[str | None] = mapped_column(String(128), nullable=True)

    # "admin" | "user" — kept as plain string to avoid ALTER TABLE pain
    # when new roles are introduced.
    system_role: Mapped[str] = mapped_column(String(16), nullable=False, default="user")

    created_at: Mapped[datetime] = mapped_column(
        BeijingDateTime(),
        nullable=False,
        default=lambda: datetime.now(UTC),
    )

    # OAuth linkage (optional). A partial unique index enforces one
    # account per (provider, oauth_id) pair, leaving NULL/NULL rows
    # unconstrained so plain password accounts can coexist.
    oauth_provider: Mapped[str | None] = mapped_column(String(32), nullable=True)
    oauth_id: Mapped[str | None] = mapped_column(String(128), nullable=True)

    # Auth lifecycle flags
    needs_setup: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    token_version: Mapped[int] = mapped_column(nullable=False, default=0)

    # 登录白名单(放行)标记 —— **黑名单模式**。仅当系统设置 ``login_whitelist.enabled``
    # 打开时生效:关 → 忽略此列,人人可登录;开 → 仅 approved=False(被管理员显式
    # 「取消放行」)的用户被拦,其余(含从未登录过的新账号)放行。新账号默认 True
    # (首次登录即放行);存量用户由迁移回填 True。server_default=1 让迁移 ADD COLUMN
    # 时把已存在行置为已放行。
    approved: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default=text("1"))

    # Unified password/passphrase. New writes store the same versioned hash as
    # password_hash; historical plaintext remains accepted until the next reset.
    # Never serialize hashes into user-list or login responses.
    login_password: Mapped[str | None] = mapped_column(String(255), nullable=True)

    # 岗位 (position) the user is assigned to (1:1). NULL = no explicit
    # position → the user inherits the ``is_default`` position's grants. Drives
    # the subscription sync engine (see ``deerflow.services.position_sync``).
    position_id: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)

    __table_args__ = (
        Index("ix_users_username_key_created_at", "username_key", "created_at"),
        Index(
            "idx_users_oauth_identity",
            "oauth_provider",
            "oauth_id",
            unique=True,
            sqlite_where=text("oauth_provider IS NOT NULL AND oauth_id IS NOT NULL"),
        ),
    )