import uuid
from datetime import datetime
from typing import Any, Optional
from sqlalchemy import (
    BigInteger,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    JSON,
    String,
    Text,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from infrastructure.database.base import (
    Base,
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    TenantMixin,
)


class BroadcastCampaign(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Mass message broadcast campaign with scheduling, filtering, and stats."""
    __tablename__ = "broadcast_campaigns"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    # draft | scheduled | running | paused | completed | failed | canceled
    status: Mapped[str] = mapped_column(String(50), default="draft", nullable=False, index=True)
    target_filter: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    message_text: Mapped[str] = mapped_column(Text, nullable=False)
    inline_keyboard: Mapped[Optional[list[list[dict[str, Any]]]]] = mapped_column(JSON, nullable=True)
    media_type: Mapped[str] = mapped_column(String(20), default="none", nullable=False)  # none | photo | video | document
    media_url: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)
    scheduled_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    total_recipients: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    sent_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    delivered_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    failed_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    rate_limit_per_second: Mapped[int] = mapped_column(Integer, default=25, nullable=False)
    error_summary: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    recipients: Mapped[list["BroadcastRecipient"]] = relationship(
        "BroadcastRecipient", back_populates="campaign", cascade="all, delete-orphan"
    )


class BroadcastRecipient(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """Individual recipient delivery audit and retry tracking."""
    __tablename__ = "broadcast_recipients"

    campaign_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("broadcast_campaigns.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    bot_user_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bot_users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    telegram_chat_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    # pending | sent | delivered | failed | blocked
    status: Mapped[str] = mapped_column(String(50), default="pending", nullable=False, index=True)
    attempts: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    error_message: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)
    delivered_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    campaign: Mapped["BroadcastCampaign"] = relationship("BroadcastCampaign", back_populates="recipients")
