# @File: backend/services/whatsapp/campaign_analytics.py
# @Desc: Campaign analytics service for the WhatsApp reseller module.
#        Stores per-campaign time-series metrics in the
#        ``whatsapp_campaign_metrics`` table (hourly buckets) and
#        exposes aggregations that power the client dashboard:
#
#          - overview()       : cross-campaign KPIs + global trend
#          - timeseries()     : bucketed metrics for a single campaign
#          - funnel()         : sent → delivered → read → replied
#          - compare()        : side-by-side KPIs for multiple campaigns
#          - summary_csv()    : campaign-level summary as CSV
#          - recipients_csv() : per-recipient detail as CSV
#
#        Backfill: the first time analytics is requested for a campaign
#        that has no metric rows yet, we synthesize hourly buckets from
#        ``whatsapp_campaign_recipients`` using their ``sent_at`` (or
#        created_at fallback) timestamp. That keeps historical campaigns
#        visible on charts without requiring a data-migration script.
from __future__ import annotations

import csv
import io
import logging
from datetime import datetime, timedelta, timezone
from typing import Iterable, Optional

from fastapi import HTTPException
from models.whatsapp import (
    WhatsappCampaign,
    WhatsappCampaignMetric,
    WhatsappCampaignRecipient,
    WhatsappClientSubscription,
)
from sqlalchemy import and_, desc, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from services.whatsapp.chat import _get_active_subscription

logger = logging.getLogger(__name__)

# Safety cap for /compare to prevent abuse
MAX_COMPARE_CAMPAIGNS = 10
# Default lookback for the overview trend chart
DEFAULT_OVERVIEW_DAYS = 30
# Valid granularities (maps to truncation in Python since SQLite has no
# date_trunc; we bucket in the app layer for portability).
GRANULARITIES = {"hour", "day", "week"}


# ---------------------------------------------------------------------------
# Internal helpers
# ---------------------------------------------------------------------------


def _truncate_hour(ts: datetime) -> datetime:
    if ts.tzinfo is None:
        ts = ts.replace(tzinfo=timezone.utc)
    return ts.astimezone(timezone.utc).replace(minute=0, second=0, microsecond=0)


def _truncate_day(ts: datetime) -> datetime:
    if ts.tzinfo is None:
        ts = ts.replace(tzinfo=timezone.utc)
    return ts.astimezone(timezone.utc).replace(
        hour=0, minute=0, second=0, microsecond=0
    )


def _truncate_week(ts: datetime) -> datetime:
    d = _truncate_day(ts)
    # ISO week starts on Monday
    return d - timedelta(days=d.weekday())


def _bucket(ts: datetime, granularity: str) -> datetime:
    if granularity == "day":
        return _truncate_day(ts)
    if granularity == "week":
        return _truncate_week(ts)
    return _truncate_hour(ts)


async def _require_subscription(
    db: AsyncSession, user_id: str
) -> WhatsappClientSubscription:
    sub = await _get_active_subscription(db, user_id)
    if sub is None:
        raise HTTPException(
            status_code=404,
            detail="No active WhatsApp subscription found for this account.",
        )
    return sub


async def _owned_campaign(
    db: AsyncSession, *, campaign_id: int, subscription_id: int
) -> WhatsappCampaign:
    stmt = select(WhatsappCampaign).where(
        and_(
            WhatsappCampaign.id == campaign_id,
            WhatsappCampaign.subscription_id == subscription_id,
        )
    )
    row = (await db.execute(stmt)).scalar_one_or_none()
    if row is None:
        raise HTTPException(status_code=404, detail="Campaign not found.")
    return row


# ---------------------------------------------------------------------------
# Write path
# ---------------------------------------------------------------------------


async def record_recipient_event(
    db: AsyncSession,
    *,
    campaign: WhatsappCampaign,
    event: str,
    occurred_at: Optional[datetime] = None,
    delta: int = 1,
) -> None:
    """Upsert the hourly metric bucket for a recipient state transition.

    ``event`` must be one of: sent, delivered, read, replied, failed,
    opted_out. Unknown events are ignored — we don't want a typo to
    raise inside the hot path.

    The caller is responsible for committing the transaction.
    """
    columns = {
        "sent": WhatsappCampaignMetric.sent,
        "delivered": WhatsappCampaignMetric.delivered,
        "read": WhatsappCampaignMetric.read_count,
        "replied": WhatsappCampaignMetric.replied,
        "failed": WhatsappCampaignMetric.failed,
        "opted_out": WhatsappCampaignMetric.opted_out,
    }
    if event not in columns:
        logger.debug("record_recipient_event: ignoring unknown event %r", event)
        return

    bucket = _truncate_hour(occurred_at or datetime.now(timezone.utc))

    stmt = select(WhatsappCampaignMetric).where(
        and_(
            WhatsappCampaignMetric.campaign_id == campaign.id,
            WhatsappCampaignMetric.bucket_ts == bucket,
        )
    )
    row = (await db.execute(stmt)).scalar_one_or_none()
    if row is None:
        row = WhatsappCampaignMetric(
            campaign_id=campaign.id,
            subscription_id=campaign.subscription_id,
            user_id=campaign.user_id,
            bucket_ts=bucket,
        )
        db.add(row)

    field_name = "read_count" if event == "read" else event
    setattr(row, field_name, (getattr(row, field_name) or 0) + delta)


# ---------------------------------------------------------------------------
# Backfill
# ---------------------------------------------------------------------------


async def _backfill_if_empty(
    db: AsyncSession, campaign: WhatsappCampaign
) -> None:
    """If the campaign has no metric rows, synthesize them from recipients.

    This is a one-shot, idempotent operation: we only run it when the
    metrics table is empty for the given campaign. The commit is
    deferred to the caller.
    """
    exists_stmt = select(func.count(WhatsappCampaignMetric.id)).where(
        WhatsappCampaignMetric.campaign_id == campaign.id
    )
    count = int((await db.execute(exists_stmt)).scalar_one() or 0)
    if count > 0:
        return

    rec_stmt = select(WhatsappCampaignRecipient).where(
        WhatsappCampaignRecipient.campaign_id == campaign.id
    )
    recipients = (await db.execute(rec_stmt)).scalars().all()
    if not recipients:
        return

    # Build aggregated bucket counts in-memory first, then insert in bulk.
    agg: dict[datetime, dict[str, int]] = {}
    for r in recipients:
        ts = r.sent_at or campaign.started_at or campaign.created_at
        if ts is None:
            continue
        bucket = _truncate_hour(ts)
        slot = agg.setdefault(
            bucket,
            {"sent": 0, "delivered": 0, "read": 0, "replied": 0, "failed": 0, "opted_out": 0},
        )
        status = (r.status or "").lower()
        # Recipient states in the recipients table are cumulative — a
        # "replied" recipient was also "sent" and "delivered" — so we
        # derive each counter from the terminal state.
        if status in {"sent", "delivered", "replied", "failed"}:
            slot["sent"] += 1
        if status in {"delivered", "replied"}:
            slot["delivered"] += 1
        if status == "replied":
            slot["replied"] += 1
        if status == "failed":
            slot["failed"] += 1

    for bucket, counts in agg.items():
        db.add(
            WhatsappCampaignMetric(
                campaign_id=campaign.id,
                subscription_id=campaign.subscription_id,
                user_id=campaign.user_id,
                bucket_ts=bucket,
                sent=counts["sent"],
                delivered=counts["delivered"],
                read_count=counts["read"],
                replied=counts["replied"],
                failed=counts["failed"],
                opted_out=counts["opted_out"],
            )
        )
    await db.flush()


# ---------------------------------------------------------------------------
# Read path — aggregations
# ---------------------------------------------------------------------------


def _empty_kpis() -> dict:
    return {
        "total_campaigns": 0,
        "total_recipients": 0,
        "sent": 0,
        "delivered": 0,
        "read": 0,
        "replied": 0,
        "failed": 0,
        "delivery_rate": 0.0,
        "reply_rate": 0.0,
        "failure_rate": 0.0,
    }


def _kpis_from_counters(
    campaigns: int,
    recipients: int,
    sent: int,
    delivered: int,
    read: int,
    replied: int,
    failed: int,
) -> dict:
    def pct(n: int, d: int) -> float:
        return round((n / d) * 100, 2) if d else 0.0

    return {
        "total_campaigns": campaigns,
        "total_recipients": recipients,
        "sent": sent,
        "delivered": delivered,
        "read": read,
        "replied": replied,
        "failed": failed,
        "delivery_rate": pct(delivered, sent),
        "reply_rate": pct(replied, delivered if delivered else sent),
        "failure_rate": pct(failed, sent),
    }


def _parse_range(
    start: Optional[str], end: Optional[str], default_days: int
) -> tuple[datetime, datetime]:
    now = datetime.now(timezone.utc)
    end_dt = _parse_iso(end) or now
    start_dt = _parse_iso(start) or (end_dt - timedelta(days=default_days))
    if start_dt > end_dt:
        start_dt, end_dt = end_dt, start_dt
    return start_dt, end_dt


def _parse_iso(value: Optional[str]) -> Optional[datetime]:
    if not value:
        return None
    try:
        dt = datetime.fromisoformat(value.replace("Z", "+00:00"))
    except ValueError:
        return None
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt


async def overview(
    db: AsyncSession,
    user_id: str,
    *,
    start: Optional[str] = None,
    end: Optional[str] = None,
    granularity: str = "day",
) -> dict:
    """KPIs + trend aggregated across all of the user's campaigns."""
    sub = await _require_subscription(db, user_id)
    if granularity not in GRANULARITIES:
        granularity = "day"

    # Campaign-level counters (cached on the campaign row, always up-to-date).
    totals_stmt = select(
        func.count(WhatsappCampaign.id),
        func.coalesce(func.sum(WhatsappCampaign.total_recipients), 0),
        func.coalesce(func.sum(WhatsappCampaign.sent_count), 0),
        func.coalesce(func.sum(WhatsappCampaign.delivered_count), 0),
        func.coalesce(func.sum(WhatsappCampaign.replied_count), 0),
        func.coalesce(func.sum(WhatsappCampaign.failed_count), 0),
    ).where(WhatsappCampaign.subscription_id == sub.id)
    t_row = (await db.execute(totals_stmt)).one()
    kpis = _kpis_from_counters(
        campaigns=int(t_row[0] or 0),
        recipients=int(t_row[1] or 0),
        sent=int(t_row[2] or 0),
        delivered=int(t_row[3] or 0),
        read=0,
        replied=int(t_row[4] or 0),
        failed=int(t_row[5] or 0),
    )

    # Ensure backfill for each campaign missing metrics so the trend
    # chart has data even for pre-analytics campaigns.
    camp_rows = (
        await db.execute(
            select(WhatsappCampaign).where(WhatsappCampaign.subscription_id == sub.id)
        )
    ).scalars().all()
    for c in camp_rows:
        await _backfill_if_empty(db, c)
    await db.commit()

    # Trend: aggregate metric rows by bucket.
    start_dt, end_dt = _parse_range(start, end, DEFAULT_OVERVIEW_DAYS)
    m_stmt = select(WhatsappCampaignMetric).where(
        and_(
            WhatsappCampaignMetric.subscription_id == sub.id,
            WhatsappCampaignMetric.bucket_ts >= start_dt,
            WhatsappCampaignMetric.bucket_ts <= end_dt,
        )
    )
    metrics = (await db.execute(m_stmt)).scalars().all()

    trend_map: dict[datetime, dict[str, int]] = {}
    for m in metrics:
        bucket = _bucket(m.bucket_ts, granularity)
        slot = trend_map.setdefault(
            bucket,
            {"sent": 0, "delivered": 0, "read": 0, "replied": 0, "failed": 0, "opted_out": 0},
        )
        slot["sent"] += m.sent or 0
        slot["delivered"] += m.delivered or 0
        slot["read"] += m.read_count or 0
        slot["replied"] += m.replied or 0
        slot["failed"] += m.failed or 0
        slot["opted_out"] += m.opted_out or 0

    trend = []
    for bucket_ts in sorted(trend_map.keys()):
        c = trend_map[bucket_ts]
        sent = c["sent"]
        delivered = c["delivered"]
        replied = c["replied"]
        trend.append(
            {
                "bucket_ts": bucket_ts.isoformat(),
                "sent": sent,
                "delivered": delivered,
                "read": c["read"],
                "replied": replied,
                "failed": c["failed"],
                "opted_out": c["opted_out"],
                "delivery_rate": round((delivered / sent) * 100, 2) if sent else 0.0,
                "reply_rate": round((replied / delivered) * 100, 2)
                if delivered
                else 0.0,
            }
        )

    return {
        "kpis": kpis,
        "trend": trend,
        "range": {
            "start": start_dt.isoformat(),
            "end": end_dt.isoformat(),
            "granularity": granularity,
        },
    }


async def timeseries(
    db: AsyncSession,
    user_id: str,
    campaign_id: int,
    *,
    start: Optional[str] = None,
    end: Optional[str] = None,
    granularity: str = "hour",
) -> dict:
    """Per-campaign time-series metrics, optionally bucketed by day/week."""
    sub = await _require_subscription(db, user_id)
    campaign = await _owned_campaign(
        db, campaign_id=campaign_id, subscription_id=sub.id
    )
    if granularity not in GRANULARITIES:
        granularity = "hour"

    await _backfill_if_empty(db, campaign)
    await db.commit()

    default_days = 7 if granularity == "hour" else 90
    start_dt, end_dt = _parse_range(start, end, default_days)

    stmt = (
        select(WhatsappCampaignMetric)
        .where(
            and_(
                WhatsappCampaignMetric.campaign_id == campaign.id,
                WhatsappCampaignMetric.bucket_ts >= start_dt,
                WhatsappCampaignMetric.bucket_ts <= end_dt,
            )
        )
        .order_by(WhatsappCampaignMetric.bucket_ts)
    )
    rows = (await db.execute(stmt)).scalars().all()

    buckets: dict[datetime, dict[str, int]] = {}
    for r in rows:
        b = _bucket(r.bucket_ts, granularity)
        slot = buckets.setdefault(
            b,
            {"sent": 0, "delivered": 0, "read": 0, "replied": 0, "failed": 0, "opted_out": 0},
        )
        slot["sent"] += r.sent or 0
        slot["delivered"] += r.delivered or 0
        slot["read"] += r.read_count or 0
        slot["replied"] += r.replied or 0
        slot["failed"] += r.failed or 0
        slot["opted_out"] += r.opted_out or 0

    series = []
    for b in sorted(buckets.keys()):
        c = buckets[b]
        series.append(
            {
                "bucket_ts": b.isoformat(),
                "sent": c["sent"],
                "delivered": c["delivered"],
                "read": c["read"],
                "replied": c["replied"],
                "failed": c["failed"],
                "opted_out": c["opted_out"],
                "delivery_rate": round((c["delivered"] / c["sent"]) * 100, 2)
                if c["sent"]
                else 0.0,
                "reply_rate": round((c["replied"] / c["delivered"]) * 100, 2)
                if c["delivered"]
                else 0.0,
            }
        )

    return {
        "campaign": {
            "id": campaign.id,
            "name": campaign.name,
            "status": campaign.status,
            "total_recipients": campaign.total_recipients,
            "sent_count": campaign.sent_count,
            "delivered_count": campaign.delivered_count,
            "replied_count": campaign.replied_count,
            "failed_count": campaign.failed_count,
        },
        "range": {
            "start": start_dt.isoformat(),
            "end": end_dt.isoformat(),
            "granularity": granularity,
        },
        "series": series,
    }


async def funnel(
    db: AsyncSession, user_id: str, campaign_id: int
) -> dict:
    """Sent → Delivered → Read → Replied funnel for a single campaign."""
    sub = await _require_subscription(db, user_id)
    campaign = await _owned_campaign(
        db, campaign_id=campaign_id, subscription_id=sub.id
    )
    await _backfill_if_empty(db, campaign)
    await db.commit()

    stmt = select(
        func.coalesce(func.sum(WhatsappCampaignMetric.sent), 0),
        func.coalesce(func.sum(WhatsappCampaignMetric.delivered), 0),
        func.coalesce(func.sum(WhatsappCampaignMetric.read_count), 0),
        func.coalesce(func.sum(WhatsappCampaignMetric.replied), 0),
        func.coalesce(func.sum(WhatsappCampaignMetric.failed), 0),
    ).where(WhatsappCampaignMetric.campaign_id == campaign.id)
    row = (await db.execute(stmt)).one()
    sent = int(row[0] or 0) or campaign.sent_count
    delivered = int(row[1] or 0) or campaign.delivered_count
    read = int(row[2] or 0)
    replied = int(row[3] or 0) or campaign.replied_count
    failed = int(row[4] or 0) or campaign.failed_count
    recipients = campaign.total_recipients or sent

    def pct(n: int, d: int) -> float:
        return round((n / d) * 100, 2) if d else 0.0

    return {
        "campaign": {"id": campaign.id, "name": campaign.name},
        "steps": [
            {"label": "Recipients", "value": recipients, "rate": 100.0},
            {"label": "Sent", "value": sent, "rate": pct(sent, recipients)},
            {
                "label": "Delivered",
                "value": delivered,
                "rate": pct(delivered, recipients),
            },
            {"label": "Read", "value": read, "rate": pct(read, recipients)},
            {"label": "Replied", "value": replied, "rate": pct(replied, recipients)},
        ],
        "failed": failed,
    }


async def compare(
    db: AsyncSession, user_id: str, campaign_ids: Iterable[int]
) -> dict:
    """Side-by-side KPI comparison for up to MAX_COMPARE_CAMPAIGNS campaigns."""
    sub = await _require_subscription(db, user_id)
    ids = [int(i) for i in campaign_ids if i][:MAX_COMPARE_CAMPAIGNS]
    if not ids:
        return {"campaigns": []}

    stmt = select(WhatsappCampaign).where(
        and_(
            WhatsappCampaign.subscription_id == sub.id,
            WhatsappCampaign.id.in_(ids),
        )
    )
    rows = (await db.execute(stmt)).scalars().all()

    items: list[dict] = []
    for c in rows:
        sent = c.sent_count or 0
        delivered = c.delivered_count or 0
        replied = c.replied_count or 0
        failed = c.failed_count or 0

        def pct(n: int, d: int) -> float:
            return round((n / d) * 100, 2) if d else 0.0

        items.append(
            {
                "id": c.id,
                "name": c.name,
                "status": c.status,
                "total_recipients": c.total_recipients or 0,
                "sent": sent,
                "delivered": delivered,
                "replied": replied,
                "failed": failed,
                "delivery_rate": pct(delivered, sent),
                "reply_rate": pct(replied, delivered if delivered else sent),
                "failure_rate": pct(failed, sent),
                "created_at": c.created_at.isoformat() if c.created_at else None,
                "completed_at": c.completed_at.isoformat() if c.completed_at else None,
            }
        )
    # Preserve caller order
    items.sort(key=lambda x: ids.index(x["id"]) if x["id"] in ids else 0)
    return {"campaigns": items}


# ---------------------------------------------------------------------------
# CSV exports
# ---------------------------------------------------------------------------


async def summary_csv(db: AsyncSession, user_id: str) -> str:
    sub = await _require_subscription(db, user_id)
    stmt = (
        select(WhatsappCampaign)
        .where(WhatsappCampaign.subscription_id == sub.id)
        .order_by(desc(WhatsappCampaign.created_at))
    )
    rows = (await db.execute(stmt)).scalars().all()

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(
        [
            "id",
            "name",
            "status",
            "created_at",
            "started_at",
            "completed_at",
            "total_recipients",
            "sent",
            "delivered",
            "replied",
            "failed",
            "delivery_rate_pct",
            "reply_rate_pct",
        ]
    )
    for c in rows:
        sent = c.sent_count or 0
        delivered = c.delivered_count or 0
        replied = c.replied_count or 0
        delivery_rate = round((delivered / sent) * 100, 2) if sent else 0.0
        reply_rate = (
            round((replied / delivered) * 100, 2) if delivered else 0.0
        )
        writer.writerow(
            [
                c.id,
                c.name,
                c.status,
                c.created_at.isoformat() if c.created_at else "",
                c.started_at.isoformat() if c.started_at else "",
                c.completed_at.isoformat() if c.completed_at else "",
                c.total_recipients or 0,
                sent,
                delivered,
                replied,
                c.failed_count or 0,
                delivery_rate,
                reply_rate,
            ]
        )
    return buf.getvalue()


async def recipients_csv(
    db: AsyncSession, user_id: str, campaign_id: int
) -> tuple[str, str]:
    """Per-recipient detail CSV for one campaign.

    Returns (filename, csv_text).
    """
    sub = await _require_subscription(db, user_id)
    campaign = await _owned_campaign(
        db, campaign_id=campaign_id, subscription_id=sub.id
    )
    stmt = (
        select(WhatsappCampaignRecipient)
        .where(WhatsappCampaignRecipient.campaign_id == campaign.id)
        .order_by(WhatsappCampaignRecipient.id)
    )
    rows = (await db.execute(stmt)).scalars().all()

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(["id", "phone_number", "contact_id", "status", "sent_at", "error_detail"])
    for r in rows:
        writer.writerow(
            [
                r.id,
                r.phone_number,
                r.contact_id or "",
                r.status,
                r.sent_at.isoformat() if r.sent_at else "",
                r.error_detail or "",
            ]
        )

    safe_name = "".join(
        ch if ch.isalnum() or ch in "-_" else "_" for ch in (campaign.name or "campaign")
    ).strip("_") or f"campaign_{campaign.id}"
    filename = f"whatsapp_campaign_{campaign.id}_{safe_name}_recipients.csv"
    return filename, buf.getvalue()