from __future__ import annotations

from collections.abc import Sequence
from datetime import datetime
from typing import Literal, Self

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.db import Model
from app.enums import CampaignCancelReason, CampaignStatus, MessageChannel
from app.types import FanGroupCount


class CampaignQuery(Query["Campaign"]):
    def find_ready_to_dispatch(self) -> Sequence[Campaign]:
        self.where(
            Campaign.status.in_([CampaignStatus.SCHEDULED, CampaignStatus.IN_PROGRESS])
        )
        return [campaign for campaign in self.all() if campaign.is_ready_to_dispatch()]


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()

    def cancel(self, reason: CampaignCancelReason) -> None:
        self.status = CampaignStatus.CANCELLED
        self.cancel_reason = reason
        self.cancelled_at = timezone.now()

    def is_ready_to_dispatch(self) -> bool:
        return (
            (self.is_scheduled or self.is_in_progress)
            and self.send_at is not None
            and self.send_at <= timezone.now()
            and not self.is_sent
            and not self.is_cancelled
            and not self.is_deleted
        )

    @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 CampaignBatchQuery(Query["CampaignBatch"]):
    def active(self) -> Self:
        """Matches `CampaignBatch.is_active` -- keep both in sync."""
        return self.join(Campaign).where(
            Campaign.status == CampaignStatus.IN_PROGRESS,
            Campaign.prepared_at.isnot(None),
            Campaign.cancelled_at.is_(None),
            Campaign.deleted_at.is_(None),
            CampaignBatch.completed_at.is_(None),
            CampaignBatch.cancelled_at.is_(None),
            CampaignBatch.batch_offset < CampaignBatch.batch_size,
        )


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)

    query = CampaignBatchQuery.as_descriptor()

    @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 bulk_create_from_audience(
        self,
        *,
        batch_id: int,
        audience_id: str,
        channel: Literal["SMS", "WHATSAPP"],
        country_code: str,
        state_province: str | None,
    ) -> int:
        state_filter = (
            AudienceTextFan.fan_state.is_(None)
            if state_province is None
            else AudienceTextFan.fan_state == state_province
        )
        select_stmt = sa.select(
            sa.literal(batch_id),
            AudienceTextFan.fan_id,
            AudienceTextFan.fan_phone_number,
            AudienceTextFan.fan_country,
            AudienceTextFan.fan_state,
            AudienceTextFan.fan_area_code,
        ).where(
            AudienceTextFan.audience_id == audience_id,
            AudienceTextFan.channel == channel,
            AudienceTextFan.fan_country == country_code,
            state_filter,
        )
        insert_stmt = sa.insert(BatchRecipient).from_select(
            [
                "batch_id",
                "fan_id",
                "fan_phone_number",
                "fan_country",
                "fan_state",
                "fan_area_code",
            ],
            select_stmt,
        )
        result = self.session.execute(insert_stmt)
        return result.rowcount  # ty: ignore[unresolved-attribute]


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 AudienceTextFanQuery(Query["AudienceTextFan"]):
    def count_by_group(
        self, *, audience_id: str, channel: Literal["SMS", "WHATSAPP"]
    ) -> Sequence[FanGroupCount]:
        """Count recipients per (country, state) group for an audience+channel."""
        stmt = (
            sa.select(
                AudienceTextFan.fan_country,
                AudienceTextFan.fan_state,
                sa.func.count(),
            )
            .where(
                AudienceTextFan.audience_id == audience_id,
                AudienceTextFan.channel == channel,
            )
            .group_by(
                AudienceTextFan.fan_country,
                AudienceTextFan.fan_state,
            )
        )
        rows = self.session.execute(stmt).all()
        return [FanGroupCount(*row) for row in rows]


class AudienceTextFan(Model, kw_only=True):
    __tablename__ = "audience_text_fan"

    audience_id: Mapped[str] = mapped_column(primary_key=True)
    fan_id: Mapped[str] = mapped_column(primary_key=True)
    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)
    channel: Mapped[str]

    query = AudienceTextFanQuery.as_descriptor()