import datetime
import decimal

import sqlalchemy as sa
from fansifter_common.adapters.db.types import ChoiceType, PydanticType
from fansifter_common.auth.account import Account
from fansifter_common.core.enums import Brand
from fansifter_common.utils import timezone
from fansifter_common.utils.uuid import uuid_string
from sqlalchemy import UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from ows_text_campaigns.adapters.db import Model
from ows_text_campaigns.adapters.scanner import ContentRecommendations
from ows_text_campaigns.assets.models import Asset
from ows_text_campaigns.audiences.models import Audience
from ows_text_campaigns.campaigns.dtos import CampaignAnalytics
from ows_text_campaigns.campaigns.enums import (
    CampaignCancelReason,
    CampaignStatus,
    MessageChannel,
    MessageSendQueueStatus,
)
from ows_text_campaigns.campaigns.types import ShortenedUrl, TimezoneAdjustment
from ows_text_campaigns.campaigns.utils import extract_shortened_urls_from_markup


class UrlDomain(Model, kw_only=True):
    __tablename__ = "text_campaign_url_domain"

    id: Mapped[str] = mapped_column(primary_key=True, default_factory=uuid_string)
    domain: Mapped[str]
    brand: Mapped[Brand] = mapped_column(ChoiceType(Brand))
    vendor_id: Mapped[int | None] = mapped_column(default=None)
    subaccount_id: Mapped[int | None] = mapped_column(default=None)

    @property
    def account(self) -> Account | None:
        if self.vendor_id is not None and self.subaccount_id is not None:
            return Account(vendor_id=self.vendor_id, subaccount_id=self.subaccount_id)
        return None


class CampaignAttachment(Model, kw_only=True):
    __tablename__ = "text_campaign_attachment"

    campaign_id: Mapped[str] = mapped_column(
        sa.ForeignKey("text_campaign.id", ondelete="CASCADE"), primary_key=True
    )
    asset_id: Mapped[str] = mapped_column(
        sa.ForeignKey("text_campaign_asset.id"), primary_key=True
    )


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(default=MessageChannel.SMS)
    audience_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)
    content_scan_result: Mapped[ContentRecommendations | None] = mapped_column(
        PydanticType(ContentRecommendations | None), default=None
    )
    timezone_adjustments: Mapped[list[TimezoneAdjustment] | None] = mapped_column(
        PydanticType(list[TimezoneAdjustment] | None), default=None
    )
    send_at: Mapped[datetime.datetime | None] = mapped_column(default=None)
    send_at_timezone: Mapped[str | None] = mapped_column(default=None)
    cancel_reason: Mapped[CampaignCancelReason | None] = mapped_column(default=None)
    cancelled_at: Mapped[datetime.datetime | None] = mapped_column(default=None)
    created_at: Mapped[datetime.datetime] = mapped_column(default_factory=timezone.now)
    created_by: Mapped[str]
    updated_at: Mapped[datetime.datetime] = mapped_column(
        default_factory=timezone.now, onupdate=timezone.now
    )
    updated_by: Mapped[str]
    deleted_at: Mapped[datetime.datetime | None] = mapped_column(default=None)
    deleted_by: Mapped[str | None] = mapped_column(default=None)

    # relations
    assets: Mapped[list[Asset]] = relationship(
        secondary=CampaignAttachment.__tablename__,
        lazy="selectin",
        default_factory=list,
    )

    _audience: Audience | None = None
    _analytics: CampaignAnalytics | None = None

    @property
    def account(self) -> Account:
        return Account(vendor_id=self.vendor_id, subaccount_id=self.subaccount_id)

    @account.setter
    def account(self, account: Account) -> None:
        self.vendor_id = account.vendor_id
        self.subaccount_id = account.subaccount_id

    @property
    def is_draft(self) -> bool:
        return self.status == CampaignStatus.DRAFT

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

    @property
    def audience(self) -> Audience | None:
        return self._audience

    @audience.setter
    def audience(self, audience: Audience | None) -> None:
        if audience is None:
            self._audience = None
            self.audience_id = None
            self.recipients_count = None
            return

        if audience.id != self.id:
            self._audience = audience
            self.audience_id = audience.id
            self.recipients_count = audience.fan_count

    @property
    def shortened_urls(self) -> list[ShortenedUrl]:
        return extract_shortened_urls_from_markup(self.get_content())

    @property
    def analytics(self) -> CampaignAnalytics | None:
        return self._analytics

    @analytics.setter
    def analytics(self, analytics: CampaignAnalytics | None) -> None:
        if analytics is None:
            self._analytics = None
            return
        self._analytics = analytics

    def get_asset_or_none(self, asset_id: str) -> Asset | None:
        """Get a asset by its ID."""
        return next(
            (asset for asset in self.assets if asset.id == asset_id),
            None,
        )

    def get_media_url(self) -> str | list[str] | None:
        """Get the media URL of the first asset, if available."""
        if not self.assets:
            return None
        if len(self.assets) == 1:
            asset = self.assets[0]
            if asset.url is None:
                return None
            return asset.url
        return [asset.url for asset in self.assets if asset.url]

    def get_content(self) -> str:
        return (self.content or "").strip()

    def is_ready_for_queue(self, window_minutes: int = 10) -> bool:
        return self.status == CampaignStatus.IN_PROGRESS or (
            self.status == CampaignStatus.SCHEDULED
            and self.send_at is not None
            and timezone.now()
            >= self.send_at - datetime.timedelta(minutes=window_minutes)
        )


class MessageSendQueue(Model, kw_only=True):
    __tablename__ = "text_message_send_queue"

    id: Mapped[str] = mapped_column(primary_key=True, default_factory=uuid_string)
    campaign_id: Mapped[str]
    status: Mapped[MessageSendQueueStatus] = mapped_column(
        ChoiceType(MessageSendQueueStatus), default=MessageSendQueueStatus.QUEUED
    )
    total: Mapped[int] = mapped_column(default=0)
    offset: Mapped[int] = mapped_column(default=0)


class UrlShortenerTotalClicksAggregated(Model, kw_only=True):
    __tablename__ = "url_shortener_total_clicks_aggregated"
    __table_args__ = (UniqueConstraint("campaign_id", "redirect_url"),)

    # Only for test purposes
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)

    campaign_id: Mapped[str]
    redirect_url: Mapped[str]
    clicks: Mapped[int]


class UrlShortenerClicksGenderAgeAggregated(Model, kw_only=True):
    __tablename__ = "url_shortener_clicks_gender_age_aggregated"
    __table_args__ = (
        UniqueConstraint("campaign_id", "redirect_url", "gender", "age_range"),
    )

    # Only for test purposes
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)

    campaign_id: Mapped[str]
    redirect_url: Mapped[str]
    gender: Mapped[str | None]
    age_range: Mapped[str | None]
    clicks: Mapped[int]


class UrlShortenerClicksCountryAggregated(Model, kw_only=True):
    __tablename__ = "url_shortener_clicks_country_aggregated"
    __table_args__ = (UniqueConstraint("campaign_id", "redirect_url", "country_code"),)

    # Only for test purposes
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)

    campaign_id: Mapped[str]
    redirect_url: Mapped[str]
    country_code: Mapped[str | None]
    clicks: Mapped[int]


class CampaignUrlAttributes(Model, kw_only=True):
    __tablename__ = "text_campaign_url_attributes"

    campaign_id: Mapped[str] = mapped_column(primary_key=True)
    redirect_url: Mapped[str] = mapped_column(primary_key=True)
    is_personalized: Mapped[bool]


class CampaignSendsCountryAggregated(Model, kw_only=True):
    __tablename__ = "text_campaign_sends_country_aggregated"

    campaign_id: Mapped[str] = mapped_column(primary_key=True)
    country_code: Mapped[str | None] = mapped_column(primary_key=True)
    sends: Mapped[int]
    queued: Mapped[int]
    delivered: Mapped[int]
    failed: Mapped[int]


class TextAnalyticsBenchmarkByAccountArtist(Model, kw_only=True):
    __tablename__ = "text_analytics_benchmark_by_account_artist_dbt"

    vendor_id: Mapped[int] = mapped_column(primary_key=True)
    subaccount_id: Mapped[int] = mapped_column(primary_key=True)
    global_participant_id: Mapped[str] = mapped_column(primary_key=True)
    avg_delivery_rate: Mapped[float]
    avg_delivery_failed_rate: Mapped[float]
    avg_click_through_rate: Mapped[float]


class TextCampaignUnsubscribe(Model, kw_only=True):
    __tablename__ = "text_campaign_unsubscribes"

    campaign_id: Mapped[str] = mapped_column(primary_key=True)
    unsubscribes: Mapped[int]


class SmsTimezone(Model, kw_only=True):
    __tablename__ = "sms_timezone"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, init=False)
    iana: Mapped[str]
    utc_offset_std: Mapped[decimal.Decimal]


class SmsComplianceRule(Model, kw_only=True):
    __tablename__ = "sms_compliance_rule"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, init=False)
    country_iso2: Mapped[str]
    state_province: Mapped[str] = mapped_column(default="")
    timezone_id: Mapped[int] = mapped_column(sa.ForeignKey("sms_timezone.id"))
    is_active: Mapped[bool] = mapped_column(default=True)
    priority: Mapped[int] = mapped_column(default=100)
    safe_hours_start_weekday: Mapped[str]
    safe_hours_end_weekday: Mapped[str]
    safe_hours_start_sunday: Mapped[str]
    safe_hours_end_sunday: Mapped[str]


class SmsAreaCodeMapping(Model, kw_only=True):
    __tablename__ = "sms_area_code_mapping"

    country_iso2: Mapped[str] = mapped_column(primary_key=True)
    state_province: Mapped[str] = mapped_column(primary_key=True, default="")
    area_code: Mapped[str] = mapped_column(primary_key=True, default="")
    compliance_rule_id: Mapped[int] = mapped_column(
        sa.ForeignKey("sms_compliance_rule.id")
    )