from __future__ import annotations

from datetime import datetime

import sqlalchemy as sa
from fansifter_common.adapters.db.models import Query
from fansifter_common.adapters.db.types import ChoiceType
from fansifter_common.utils import timezone
from fansifter_common.utils.uuid import uuid_string
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.adapters.postgres import PgModel
from app.adapters.snowflake import Model
from app.enums import CampaignCancelReason, CampaignStatus, MessageChannel


class CampaignQuery(Query["Campaign"]):
    def mark_sent_if_fully_delivered(self, campaign_id: str) -> bool:
        """Single conditional UPDATE: IN_PROGRESS -> SENT iff no batch is still active."""
        no_active_batches = ~sa.exists(
            sa.select(CampaignBatch.id).where(
                CampaignBatch.campaign_id == campaign_id,
                CampaignBatch.completed_at.is_(None),
                CampaignBatch.cancelled_at.is_(None),
            )
        )
        stmt = (
            sa.update(Campaign)
            .where(
                Campaign.id == campaign_id,
                Campaign.status == CampaignStatus.IN_PROGRESS,
                no_active_batches,
            )
            .values(status=CampaignStatus.SENT)
        )
        result = self.session.execute(stmt)
        return result.rowcount > 0  # ty: ignore[unresolved-attribute]


class Campaign(Model, kw_only=True):
    __tablename__ = "text_campaign"

    id: Mapped[str] = mapped_column(primary_key=True, default_factory=uuid_string)
    name: Mapped[str]
    vendor_id: Mapped[int]
    subaccount_id: Mapped[int]
    global_participant_id: Mapped[str]
    status: Mapped[CampaignStatus] = mapped_column(
        ChoiceType(CampaignStatus), default=CampaignStatus.DRAFT
    )
    channel: Mapped[MessageChannel] = mapped_column(
        ChoiceType(MessageChannel), default=MessageChannel.SMS
    )
    audience_id: Mapped[str | None] = mapped_column(default=None)
    audience_snapshot_id: Mapped[str | None] = mapped_column(default=None)
    recipients_count: Mapped[int | None] = mapped_column(default=None)
    content: Mapped[str | None] = mapped_column(default=None)
    send_at: Mapped[datetime | None] = mapped_column(default=None)
    send_at_timezone: Mapped[str | None] = mapped_column(default=None)
    prepared_at: Mapped[datetime | None] = mapped_column(default=None)
    cancel_reason: Mapped[CampaignCancelReason | None] = mapped_column(default=None)
    cancelled_at: Mapped[datetime | None] = mapped_column(default=None)
    created_at: Mapped[datetime] = mapped_column(default_factory=timezone.now)
    created_by: Mapped[str]
    updated_at: Mapped[datetime] = mapped_column(
        default_factory=timezone.now, onupdate=timezone.now
    )
    updated_by: Mapped[str]
    deleted_at: Mapped[datetime | None] = mapped_column(default=None)

    query = CampaignQuery.as_descriptor()

    @property
    def is_deleted(self) -> bool:
        return self.deleted_at is not None

    @property
    def is_scheduled(self) -> bool:
        return self.status == CampaignStatus.SCHEDULED

    @property
    def is_prepared(self) -> bool:
        return self.prepared_at is not None

    @property
    def is_in_progress(self) -> bool:
        return self.status == CampaignStatus.IN_PROGRESS

    @property
    def is_sent(self) -> bool:
        return self.status == CampaignStatus.SENT

    @property
    def is_cancelled(self) -> bool:
        return self.status == CampaignStatus.CANCELLED


class CampaignBatch(Model, kw_only=True):
    __tablename__ = "text_campaign_batch"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    campaign_id: Mapped[str] = mapped_column(
        sa.ForeignKey(Campaign.id, ondelete="RESTRICT")
    )
    country_code: Mapped[str]
    state_province: Mapped[str | None] = mapped_column(default=None)
    batch_size: Mapped[int]
    batch_offset: Mapped[int] = mapped_column(default=0, server_default=sa.text("0"))
    first_sent_at: Mapped[datetime | None] = mapped_column(default=None)
    last_sent_at: Mapped[datetime | None] = mapped_column(default=None)
    completed_at: Mapped[datetime | None] = mapped_column(default=None)
    cancelled_at: Mapped[datetime | None] = mapped_column(default=None)
    created_at: Mapped[datetime] = mapped_column(
        default_factory=timezone.now, server_default=sa.func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        default_factory=timezone.now,
        onupdate=timezone.now,
        server_default=sa.func.now(),
    )

    campaign: Mapped[Campaign] = relationship(lazy="raise", viewonly=True, init=False)

    @property
    def is_active(self) -> bool:
        return (
            self.completed_at is None
            and self.cancelled_at is None
            and self.batch_offset < self.batch_size
        )

    @property
    def is_completed(self) -> bool:
        return self.completed_at is not None


class BatchRecipientQuery(Query["BatchRecipient"]):
    def for_chunk(
        self, *, batch_id: int, offset: int, limit: int
    ) -> list[BatchRecipient]:
        """Fetch a slice of a batch's recipients, ordered deterministically."""
        return list(
            self.where(BatchRecipient.batch_id == batch_id)
            .order_by(BatchRecipient.id)
            .offset(offset)
            .limit(limit)
            .all()
        )


class BatchRecipient(Model, kw_only=True):
    __tablename__ = "text_campaign_batch_recipient"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    batch_id: Mapped[int] = mapped_column(
        sa.ForeignKey(CampaignBatch.id, ondelete="RESTRICT")
    )
    fan_id: Mapped[str]
    fan_phone_number: Mapped[str]
    fan_country: Mapped[str]
    fan_state: Mapped[str | None] = mapped_column(default=None)
    fan_area_code: Mapped[str | None] = mapped_column(default=None)

    query = BatchRecipientQuery.as_descriptor()


class BatchSendRecord(Model, kw_only=True):
    __tablename__ = "text_campaign_batch_send_record"

    id: Mapped[str] = mapped_column(primary_key=True, default_factory=uuid_string)
    batch_id: Mapped[int] = mapped_column(
        sa.ForeignKey(CampaignBatch.id, ondelete="RESTRICT")
    )
    batch_offset: Mapped[int]
    sent_count: Mapped[int]
    sent_at: Mapped[datetime]


class ArtistSettings(Model, kw_only=True):
    __tablename__ = "text_campaign_artist_settings"

    global_participant_id: Mapped[str] = mapped_column(primary_key=True)
    twilio_account_sid: Mapped[str | None] = mapped_column(default=None)
    twilio_messaging_service_sid: Mapped[str | None] = mapped_column(default=None)


class TwilioAccount(PgModel, kw_only=True):
    __tablename__ = "twilio_account"

    account_sid: Mapped[str] = mapped_column(primary_key=True)
    api_key_id: Mapped[str]
    api_secret: Mapped[str]