"""Provider credential custody and durable, single-use OAuth sessions."""

from datetime import datetime
from uuid import UUID, uuid4

from sqlalchemy import CheckConstraint, DateTime, ForeignKey, String, Text, UniqueConstraint
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

from src.core.database import Base


class ProviderConnection(Base):
    __tablename__ = "provider_connections"
    id: Mapped[UUID] = mapped_column(PGUUID, primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(
        ForeignKey("workspaces.id", ondelete="CASCADE"), index=True
    )
    project_id: Mapped[UUID] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    provider: Mapped[str] = mapped_column(String(32), default="google")
    actor_type: Mapped[str] = mapped_column(String(16))
    actor_id: Mapped[str] = mapped_column(String(160))
    subject: Mapped[str] = mapped_column(String(255))
    status: Mapped[str] = mapped_column(String(40), default="active")
    effective_scopes: Mapped[list] = mapped_column(JSONB, default=list)
    secrets_enc: Mapped[dict] = mapped_column(JSONB, default=dict)
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    __table_args__ = (
        UniqueConstraint(
            "project_id",
            "provider",
            "actor_type",
            "actor_id",
            "subject",
            name="uq_provider_actor_subject",
        ),
        CheckConstraint("actor_type IN ('user', 'project')", name="ck_provider_actor"),
    )


class ProviderGrant(Base):
    __tablename__ = "provider_grants"
    id: Mapped[UUID] = mapped_column(PGUUID, primary_key=True, default=uuid4)
    connection_id: Mapped[UUID] = mapped_column(
        ForeignKey("provider_connections.id", ondelete="CASCADE"), index=True
    )
    family: Mapped[str] = mapped_column(String(32))
    scopes: Mapped[list] = mapped_column(JSONB, default=list)
    status: Mapped[str] = mapped_column(String(40))
    granted_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    __table_args__ = (UniqueConstraint("connection_id", "family", name="uq_provider_grant_family"),)


class AuthorizationSession(Base):
    __tablename__ = "provider_authorization_sessions"
    context_enc: Mapped[str | None] = mapped_column(Text)
    completion_hash: Mapped[str | None] = mapped_column(String(64), unique=True)
    id: Mapped[UUID] = mapped_column(PGUUID, primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(
        ForeignKey("workspaces.id", ondelete="CASCADE"), index=True
    )
    project_id: Mapped[UUID] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), index=True
    )
    actor_type: Mapped[str] = mapped_column(String(16))
    actor_id: Mapped[str] = mapped_column(String(160))
    initiated_by: Mapped[str] = mapped_column(String(160))
    purpose: Mapped[str] = mapped_column(String(32), default="authorization")
    family: Mapped[str] = mapped_column(String(32))
    connection_id: Mapped[UUID | None] = mapped_column(
        ForeignKey("provider_connections.id", ondelete="SET NULL")
    )
    state_hash: Mapped[str] = mapped_column(String(64), unique=True)
    browser_binding_hash: Mapped[str] = mapped_column(String(64))
    verifier_enc: Mapped[str] = mapped_column(Text)
    redirect_uri: Mapped[str] = mapped_column(Text)
    return_to: Mapped[str] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(40), default="pending")
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))