from __future__ import annotations

from datetime import date, datetime
from uuid import uuid4

from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, JSON, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.security import utc_now
from app.db.base import Base


def uuid_str() -> str:
    return str(uuid4())


class HomeProfile(Base):
    __tablename__ = "home_profiles"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    primary_display_name: Mapped[str] = mapped_column(String(160))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="active")

    members: Mapped[list["Member"]] = relationship(back_populates="home_profile")
    devices: Mapped[list["HomeDevice"]] = relationship(back_populates="home_profile")


class HomeDevice(Base):
    __tablename__ = "home_devices"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    label: Mapped[str] = mapped_column(String(160))
    user_agent_summary: Mapped[str] = mapped_column(String(255), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    trust_level: Mapped[str] = mapped_column(String(32), default="claimed_browser")

    home_profile: Mapped[HomeProfile | None] = relationship(back_populates="devices")


class RecoveryMethod(Base):
    __tablename__ = "recovery_methods"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str] = mapped_column(ForeignKey("home_profiles.id"))
    kind: Mapped[str] = mapped_column(String(32))
    value_hash: Mapped[str | None] = mapped_column(String(128), nullable=True)
    display_hint: Mapped[str] = mapped_column(String(160), default="")
    recovery_token_hash: Mapped[str | None] = mapped_column(String(128), nullable=True)
    recovery_expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    verified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class AppSession(Base):
    __tablename__ = "sessions"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    home_device_id: Mapped[str | None] = mapped_column(ForeignKey("home_devices.id"), nullable=True)
    member_device_id: Mapped[str | None] = mapped_column(ForeignKey("member_devices.id"), nullable=True)
    csrf_token_hash: Mapped[str] = mapped_column(String(128))
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)


class Group(Base):
    __tablename__ = "groups"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    server_origin: Mapped[str] = mapped_column(String(255))
    name: Mapped[str] = mapped_column(String(180))
    description: Mapped[str] = mapped_column(Text, default="")
    visibility: Mapped[str] = mapped_column(String(32), default="private")
    default_permissions_json: Mapped[dict] = mapped_column(JSON, default=dict)
    legacy_channel_status: Mapped[str] = mapped_column(String(32), default="transition")
    transition_deadline: Mapped[date | None] = mapped_column(Date, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    archived_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    members: Mapped[list["Member"]] = relationship(back_populates="group")


class Member(Base):
    __tablename__ = "members"
    __table_args__ = (UniqueConstraint("group_id", "home_profile_id", name="uq_member_group_profile"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    display_name: Mapped[str] = mapped_column(String(160))
    role: Mapped[str] = mapped_column(String(32), default="member")
    status: Mapped[str] = mapped_column(String(32), default="invited")
    joined_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    notification_enabled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    group: Mapped[Group] = relationship(back_populates="members")
    home_profile: Mapped[HomeProfile | None] = relationship(back_populates="members")
    devices: Mapped[list["MemberDevice"]] = relationship(back_populates="member")


class MemberDevice(Base):
    __tablename__ = "member_devices"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    label: Mapped[str] = mapped_column(String(160))
    user_agent_summary: Mapped[str] = mapped_column(String(255), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    trust_level: Mapped[str] = mapped_column(String(32), default="claimed_browser")

    member: Mapped[Member] = relationship(back_populates="devices")


class MemberInvite(Base):
    __tablename__ = "member_invites"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    created_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    label: Mapped[str] = mapped_column(String(160))
    scope: Mapped[str] = mapped_column(String(32), default="open_seat")
    permission_role: Mapped[str] = mapped_column(String(32), default="member")
    token_hash: Mapped[str] = mapped_column(String(128), index=True, unique=True)
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    max_uses: Mapped[int] = mapped_column(Integer, default=50)
    use_count: Mapped[int] = mapped_column(Integer, default=0)
    opened_count: Mapped[int] = mapped_column(Integer, default=0)
    consumed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Announcement(Base):
    __tablename__ = "announcements"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    author_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    title: Mapped[str] = mapped_column(String(220))
    body: Mapped[str] = mapped_column(Text, default="")
    priority: Mapped[str] = mapped_column(String(32), default="normal")
    official: Mapped[bool] = mapped_column(Boolean, default=True)
    requires_ack: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Event(Base):
    __tablename__ = "events"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    created_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    title: Mapped[str] = mapped_column(String(220))
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    starts_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    ends_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    location_name: Mapped[str | None] = mapped_column(String(220), nullable=True)
    location_address: Mapped[str | None] = mapped_column(String(255), nullable=True)
    rsvp_required: Mapped[bool] = mapped_column(Boolean, default=False)
    changed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class EventRsvp(Base):
    __tablename__ = "event_rsvps"
    __table_args__ = (UniqueConstraint("event_id", "member_id", name="uq_event_member_rsvp"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    event_id: Mapped[str] = mapped_column(ForeignKey("events.id"))
    member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    status: Mapped[str] = mapped_column(String(32), default="unknown")
    note: Mapped[str | None] = mapped_column(Text, nullable=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    created_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    assigned_to_member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    title: Mapped[str] = mapped_column(String(220))
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="open")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Poll(Base):
    __tablename__ = "polls"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    title: Mapped[str] = mapped_column(String(220))
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    closes_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="open")
    created_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class PollOption(Base):
    __tablename__ = "poll_options"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    poll_id: Mapped[str] = mapped_column(ForeignKey("polls.id"))
    label: Mapped[str] = mapped_column(String(220))
    position: Mapped[int] = mapped_column(Integer, default=0)


class PollVote(Base):
    __tablename__ = "poll_votes"
    __table_args__ = (UniqueConstraint("poll_id", "member_id", name="uq_poll_member_vote"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    poll_id: Mapped[str] = mapped_column(ForeignKey("polls.id"))
    option_id: Mapped[str] = mapped_column(ForeignKey("poll_options.id"))
    member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class FileAsset(Base):
    __tablename__ = "file_assets"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    uploaded_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    filename_original: Mapped[str] = mapped_column(String(255))
    filename_stored: Mapped[str] = mapped_column(String(255))
    content_type: Mapped[str] = mapped_column(String(160), default="application/octet-stream")
    size_bytes: Mapped[int] = mapped_column(Integer, default=0)
    storage_path: Mapped[str] = mapped_column(String(500))
    visibility: Mapped[str] = mapped_column(String(32), default="members")
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    requires_ack: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Thread(Base):
    __tablename__ = "threads"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"))
    title: Mapped[str] = mapped_column(String(220))
    kind: Mapped[str] = mapped_column(String(32), default="discussion")
    created_by_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Message(Base):
    __tablename__ = "messages"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    thread_id: Mapped[str] = mapped_column(ForeignKey("threads.id"))
    author_member_id: Mapped[str] = mapped_column(ForeignKey("members.id"))
    body: Mapped[str] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    edited_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)


class NotificationPreference(Base):
    __tablename__ = "notification_preferences"
    __table_args__ = (UniqueConstraint("home_profile_id", "member_id", "category", name="uq_preference_owner_category"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    category: Mapped[str] = mapped_column(String(80))
    delivery: Mapped[str] = mapped_column(String(32), default="immediate")
    enabled: Mapped[bool] = mapped_column(Boolean, default=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class Notification(Base):
    __tablename__ = "notifications"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    title: Mapped[str] = mapped_column(String(220))
    body: Mapped[str] = mapped_column(Text, default="")
    category: Mapped[str] = mapped_column(String(80), default="general")
    read_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class ActionItem(Base):
    __tablename__ = "action_items"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    remote_connection_id: Mapped[str | None] = mapped_column(ForeignKey("remote_server_connections.id"), nullable=True)
    source_type: Mapped[str] = mapped_column(String(32), default="local")
    source_server_origin: Mapped[str] = mapped_column(String(255))
    source_group_id: Mapped[str] = mapped_column(String(80))
    source_group_name: Mapped[str] = mapped_column(String(180))
    type: Mapped[str] = mapped_column(String(80))
    status: Mapped[str] = mapped_column(String(32), default="open")
    priority: Mapped[str] = mapped_column(String(32), default="normal")
    title: Mapped[str] = mapped_column(String(220))
    summary: Mapped[str] = mapped_column(Text, default="")
    object_type: Mapped[str] = mapped_column(String(80))
    object_id: Mapped[str] = mapped_column(String(80))
    due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class MigrationState(Base):
    __tablename__ = "migration_states"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"), unique=True)
    invited_count: Mapped[int] = mapped_column(Integer, default=0)
    opened_count: Mapped[int] = mapped_column(Integer, default=0)
    joined_count: Mapped[int] = mapped_column(Integer, default=0)
    verified_count: Mapped[int] = mapped_column(Integer, default=0)
    notification_enabled_count: Mapped[int] = mapped_column(Integer, default=0)
    not_reached_count: Mapped[int] = mapped_column(Integer, default=0)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class AuditLog(Base):
    __tablename__ = "audit_logs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    actor_member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    actor_home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    action: Mapped[str] = mapped_column(String(120))
    resource_type: Mapped[str] = mapped_column(String(80), default="")
    resource_id: Mapped[str] = mapped_column(String(80), default="")
    details_json: Mapped[dict] = mapped_column(JSON, default=dict)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class RemoteServerConnection(Base):
    __tablename__ = "remote_server_connections"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    home_profile_id: Mapped[str] = mapped_column(ForeignKey("home_profiles.id"))
    server_origin: Mapped[str] = mapped_column(String(255))
    server_name: Mapped[str] = mapped_column(String(180))
    api_base: Mapped[str] = mapped_column(String(255))
    protocol_version: Mapped[str] = mapped_column(String(32), default="0.1")
    capabilities_json: Mapped[dict] = mapped_column(JSON, default=dict)
    access_token_encrypted: Mapped[str] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(32), default="active")
    last_sync_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    last_error: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class RemoteMembership(Base):
    __tablename__ = "remote_memberships"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    remote_connection_id: Mapped[str] = mapped_column(ForeignKey("remote_server_connections.id"))
    remote_group_id: Mapped[str] = mapped_column(String(80))
    remote_member_id: Mapped[str | None] = mapped_column(String(80), nullable=True)
    group_name: Mapped[str] = mapped_column(String(180), default="")
    role: Mapped[str] = mapped_column(String(32), default="member")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class RemoteSyncCursor(Base):
    __tablename__ = "remote_sync_cursors"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    remote_connection_id: Mapped[str] = mapped_column(ForeignKey("remote_server_connections.id"))
    cursor: Mapped[str | None] = mapped_column(String(255), nullable=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class RemoteCachedObject(Base):
    __tablename__ = "remote_cached_objects"
    __table_args__ = (UniqueConstraint("remote_connection_id", "object_type", "remote_id", name="uq_remote_object"),)

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    remote_connection_id: Mapped[str] = mapped_column(ForeignKey("remote_server_connections.id"))
    object_type: Mapped[str] = mapped_column(String(80))
    remote_id: Mapped[str] = mapped_column(String(120))
    group_remote_id: Mapped[str] = mapped_column(String(120))
    group_name: Mapped[str] = mapped_column(String(180), default="")
    payload_json: Mapped[dict] = mapped_column(JSON, default=dict)
    updated_at_remote: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    cached_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class ConnectionToken(Base):
    __tablename__ = "connection_tokens"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    created_by_member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    group_id: Mapped[str | None] = mapped_column(ForeignKey("groups.id"), nullable=True)
    label: Mapped[str] = mapped_column(String(160), default="Remote connection")
    token_hash: Mapped[str] = mapped_column(String(128), index=True, unique=True)
    scopes_json: Mapped[list[str]] = mapped_column(JSON, default=list)
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)


class DeviceLinkCode(Base):
    __tablename__ = "device_link_codes"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid_str)
    code_hash: Mapped[str] = mapped_column(String(128), index=True, unique=True)
    requested_device_label: Mapped[str] = mapped_column(String(160))
    requested_user_agent: Mapped[str] = mapped_column(String(255), default="")
    approved_by_home_profile_id: Mapped[str | None] = mapped_column(ForeignKey("home_profiles.id"), nullable=True)
    approved_by_member_id: Mapped[str | None] = mapped_column(ForeignKey("members.id"), nullable=True)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now)