"""project_auth domain — SQLAlchemy models.

These models represent Core's ownership of:
- Auth configuration per project (providers, JWT settings, Google OAuth mode)
- RS256 key pairs (private key stays in Core; public key pushed to Cloud KV)
- Custom roles per project (codegen-definable, carried in app JWT payload)

App users (cloud_app_users) and sessions (cloud_app_sessions) live in Cloud D1 —
Core never reads or writes those tables directly.
"""
from __future__ import annotations

import enum
from datetime import datetime
from typing import Optional
from uuid import UUID, uuid4

from sqlalchemy import CheckConstraint, ForeignKey, Index, Integer, String, Text, UniqueConstraint, text
from sqlalchemy.dialects.postgresql import ENUM as PG_ENUM, JSONB
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func
from sqlalchemy.types import Boolean, DateTime

from src.core.database import Base


class AuthKeyStatus(str, enum.Enum):
    active = "active"
    rotated = "rotated"
    revoked = "revoked"


class AuthRoleCreator(str, enum.Enum):
    codegen = "codegen"
    user = "user"


class AuthRoleScope(str, enum.Enum):
    project = "project"
    account = "account"


class AuthAssignmentTier(str, enum.Enum):
    """Which grant/revoke permission family governs assigning a role (design §3.4).

    Not a privilege level and not derived from permissions_json. NULL means the role is
    not assignable until classified (or the role is protected and never generically assigned).
    """

    admin = "admin"
    staff = "staff"
    member = "member"


class AuthOwnerAuthority(str, enum.Enum):
    workspace = "workspace"
    app = "app"


class MembershipSource(str, enum.Enum):
    workspace = "workspace"
    local = "local"


class MembershipStatus(str, enum.Enum):
    active = "active"
    suspended = "suspended"


class MembershipRoleSource(str, enum.Enum):
    workspace = "workspace"
    local = "local"


class SuspensionSource(str, enum.Enum):
    workspace = "workspace"
    local = "local"
    legacy = "legacy"


# Alembic 034; create_type=False — do not invent authkeystatus / authrolecreator.
AUTH_KEY_STATUS_ENUM = PG_ENUM(
    "active", "rotated", "revoked",
    name="p2hs_project_auth_key_status",
    create_type=False,
)
AUTH_ROLE_CREATOR_ENUM = PG_ENUM(
    "codegen", "user",
    name="p2hs_project_auth_role_creator",
    create_type=False,
)
AUTH_ROLE_SCOPE_ENUM = PG_ENUM(
    "project", "account",
    name="p2hs_project_auth_role_scope",
    create_type=False,
)
AUTH_OWNER_AUTHORITY_ENUM = PG_ENUM(
    "workspace", "app",
    name="p2hs_project_auth_owner_authority",
    create_type=False,
)
AUTH_ASSIGNMENT_TIER_ENUM = PG_ENUM(
    "admin", "staff", "member",
    name="p2hs_project_auth_assignment_tier",
    create_type=False,
)
MEMBERSHIP_SOURCE_ENUM = PG_ENUM(
    "workspace", "local",
    name="p2hs_project_membership_source",
    create_type=False,
)
MEMBERSHIP_ROLE_SOURCE_ENUM = PG_ENUM(
    "workspace", "local",
    name="p2hs_project_membership_role_source",
    create_type=False,
)
SUSPENSION_SOURCE_ENUM = PG_ENUM(
    "workspace", "local", "legacy",
    name="p2hs_project_membership_suspension_source",
    create_type=False,
)
MEMBERSHIP_STATUS_ENUM = PG_ENUM(
    "active", "suspended",
    name="p2hs_project_membership_status",
    create_type=False,
)


class ProjectAuthConfig(Base):
    """Auth settings for a project.

    One row per project. Created when auth is first enabled (upsert pattern).
    google_client_id_enc / google_client_secret_enc are only present in BYOC mode.
    """

    __tablename__ = "project_auth_config"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("projects.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,
    )
    enabled: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    providers: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    auth_methods: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    identity_channels: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    auth_connections: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    audiences: Mapped[list] = mapped_column(JSONB, nullable=False, default=lambda: ["staff", "customer"])
    mfa_methods: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    require_mfa: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    allow_signup: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    require_email_verify: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    google_oauth_mode: Mapped[Optional[str]] = mapped_column(String(16), nullable=True)
    google_client_id_enc: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    google_client_secret_enc: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    access_token_ttl_seconds: Mapped[int] = mapped_column(Integer, nullable=False, default=3600)
    refresh_token_ttl_days: Mapped[int] = mapped_column(Integer, nullable=False, default=30)
    password_min_length: Mapped[int] = mapped_column(Integer, nullable=False, default=8)
    # NULL means "use the platform default" (/admin, /login) -- set once a user names a
    # custom path; see src/domain/projects/runtime_invariants.py. Not yet read/pushed to
    # Cloud (that lands with the auth-config push in a later phase).
    admin_panel_path: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    customer_login_path: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    projection_state: Mapped[str] = mapped_column(Text, nullable=False, default="disabled")
    # Versioned, non-secret fingerprint of the canonical policy projected to Cloud.
    # ``projection_state`` remains the rollout state; these fields describe the
    # independently verifiable policy projection.
    authz_policy_version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    authz_policy_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    # Set by _push_auth_config on every attempt (migration 197). NULL cloud_synced_at with
    # enabled=True means Cloud has never confirmed this config -- used by AuthProvisioner.status()
    # so a push failure is visible without a live Cloud round-trip.
    cloud_synced_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    cloud_sync_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    owner_authority: Mapped[AuthOwnerAuthority] = mapped_column(
        AUTH_OWNER_AUTHORITY_ENUM, nullable=False, default=AuthOwnerAuthority.workspace,
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class ProjectAuthKey(Base):
    """RS256 key pair for a project.

    private_key_enc is a Fernet-encrypted copy held by Core. Cloud also holds the active
    private key in KV and signs app JWTs with it (accepted for Phase 1; see AUTH-KMS-1).
    public_key_pem is pushed to Cloud KV (auth:pubkey:{project_id}) on creation/rotation.
    Only one key has status='active' at a time per project.
    """

    __tablename__ = "project_auth_keys"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("projects.id", ondelete="CASCADE"),
        nullable=False,
    )
    key_id: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[AuthKeyStatus] = mapped_column(
        AUTH_KEY_STATUS_ENUM, nullable=False, default=AuthKeyStatus.active,
    )
    public_key_pem: Mapped[str] = mapped_column(Text, nullable=False)
    private_key_enc: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    rotated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        UniqueConstraint("project_id", "key_id", name="uq_project_auth_key_project_kid"),
    )


class ProjectAuthRole(Base):
    """Custom role definition for a project's app users.

    is_default=True means new app users get this role on signup.
    permissions_json is an opaque list passed to Cloud and included in JWTs.
    created_by distinguishes codegen-scaffolded roles from manually created ones.
    """

    __tablename__ = "project_auth_roles"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("projects.id", ondelete="CASCADE"),
        nullable=False,
    )
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_default: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    permissions_json: Mapped[list] = mapped_column(JSONB, nullable=False, default=list)
    created_by: Mapped[AuthRoleCreator] = mapped_column(
        AUTH_ROLE_CREATOR_ENUM, nullable=False, default=AuthRoleCreator.user,
    )
    scope_type: Mapped[AuthRoleScope] = mapped_column(
        AUTH_ROLE_SCOPE_ENUM, nullable=False, default=AuthRoleScope.project,
    )
    protected: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    assignment_tier: Mapped[Optional[AuthAssignmentTier]] = mapped_column(
        AUTH_ASSIGNMENT_TIER_ENUM, nullable=True,
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint("project_id", "name", name="uq_project_auth_role_project_name"),
        CheckConstraint("NOT protected OR scope_type = 'project'", name="ck_project_auth_roles_protected_scope"),
        CheckConstraint("NOT protected OR assignment_tier IS NULL", name="ck_project_auth_roles_protected_untiered"),
    )


class ProjectMembership(Base):
    """A person's membership in one generated app's project-scoped authorization.

    Keyed by normalized email in Phase 1; cloud_app_user_id is linked once the
    Cloud projection exists. is_owner is the protected owner relation (at most one
    per project, enforced by a partial unique index).
    """

    __tablename__ = "project_memberships"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("projects.id", ondelete="CASCADE"),
        nullable=False,
    )
    email_normalized: Mapped[str] = mapped_column(Text, nullable=False)
    cloud_app_user_id: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    source: Mapped[MembershipSource] = mapped_column(MEMBERSHIP_SOURCE_ENUM, nullable=False)
    status: Mapped[MembershipStatus] = mapped_column(
        MEMBERSHIP_STATUS_ENUM, nullable=False, default=MembershipStatus.active,
    )
    is_owner: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    authorization_version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint("project_id", "email_normalized", name="uq_project_memberships_project_email"),
        CheckConstraint("NOT is_owner OR status = 'active'", name="ck_project_memberships_owner_active"),
        Index(
            "uq_project_memberships_one_owner",
            "project_id",
            unique=True,
            postgresql_where=text("is_owner"),
        ),
    )


class ProjectMembershipRole(Base):
    """Binds a membership to a project-scope, non-protected role, recording the grant source.

    Workspace reconciliation may change only source=workspace bindings; local grants
    and revocations never touch workspace bindings (design D29).
    """

    __tablename__ = "project_membership_roles"

    membership_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("project_memberships.id", ondelete="CASCADE"),
        primary_key=True,
    )
    role_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("project_auth_roles.id", ondelete="RESTRICT"),
        primary_key=True,
    )
    source: Mapped[MembershipRoleSource] = mapped_column(
        MEMBERSHIP_ROLE_SOURCE_ENUM, primary_key=True,
    )


class ProjectMembershipSuspension(Base):
    """One authority's hold on a membership (design D30).

    A membership is suspended while any hold exists. Each source clears only its own hold:
    workspace reconciliation never removes local or legacy holds, and local actions never
    remove workspace holds. Legacy holds (unknown provenance) never clear automatically.
    """

    __tablename__ = "project_membership_suspensions"

    membership_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("project_memberships.id", ondelete="CASCADE"),
        primary_key=True,
    )
    source: Mapped[SuspensionSource] = mapped_column(SUSPENSION_SOURCE_ENUM, primary_key=True)
    reason_code: Mapped[str] = mapped_column(Text, nullable=False, default="unspecified")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    created_by_membership_id: Mapped[Optional[UUID]] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("project_memberships.id", ondelete="SET NULL"),
        nullable=True,
    )
    extra: Mapped[Optional[dict]] = mapped_column("metadata", JSONB, nullable=True)


class ProjectAuthAuditEvent(Base):
    """Append-only record of a project authorization mutation (Workstream F).

    Application code only inserts rows. The payload carries source-scoped state, for example
    previous_sources, remaining_sources, and authorization_version before and after.
    """

    __tablename__ = "project_auth_audit_events"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False,
    )
    event: Mapped[str] = mapped_column(Text, nullable=False)
    actor_membership_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), nullable=True)
    target_membership_id: Mapped[Optional[UUID]] = mapped_column(PGUUID(as_uuid=True), nullable=True)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    correlation_id: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class ProjectMembershipProjectionSync(Base):
    """Last digest Cloud acknowledged for a membership's projection (Workstream E4).

    A failed Cloud write leaves status pending and never changes Core authority.
    """

    __tablename__ = "project_membership_projection_syncs"

    membership_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("project_memberships.id", ondelete="CASCADE"), primary_key=True,
    )
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False,
    )
    acknowledged_digest: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    status: Mapped[str] = mapped_column(Text, nullable=False, default="pending")
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    last_attempted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    acknowledged_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)