"""Aggregations powering the dashboard cards and charts."""

from __future__ import annotations

from datetime import date, timedelta
from typing import Any

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from app.models import AnalyticsEvent, Campaign, Lead, OutreachTask, Reply
from app.models.enums import (
    POSITIVE_REPLIES,
    ConnectionStatus,
    LeadStatus,
    ReplyStatus,
    TaskStatus,
    TaskType,
)
from app.rules.scoring import SCORE_BANDS

CONNECTION_TASK_TYPES = [TaskType.CONNECTION_REQUEST.value, TaskType.REVIEW_CONNECTION.value]
MESSAGE_TASK_TYPES = [TaskType.FIRST_MESSAGE.value]
FOLLOW_UP_TASK_TYPES = [
    TaskType.FOLLOW_UP_1.value,
    TaskType.FOLLOW_UP_2.value,
    TaskType.FOLLOW_UP_3.value,
]


def _count(db: Session, model: Any, *conditions: Any) -> int:
    query = select(func.count()).select_from(model)
    for condition in conditions:
        query = query.where(condition)
    return int(db.scalar(query) or 0)


def _campaign_filter(model: Any, campaign_id: int | None) -> list[Any]:
    return [model.campaign_id == campaign_id] if campaign_id is not None else []


def overview(db: Session, campaign_id: int | None = None) -> dict[str, Any]:
    """Everything the dashboard cards need, in one query batch."""
    lead_filter = _campaign_filter(Lead, campaign_id)
    task_filter = _campaign_filter(OutreachTask, campaign_id)
    reply_filter = _campaign_filter(Reply, campaign_id)

    total_leads = _count(db, Lead, *lead_filter)
    by_status = {
        status.value: _count(db, Lead, Lead.status == status.value, *lead_filter)
        for status in LeadStatus
    }

    qualified = by_status[LeadStatus.QUALIFIED.value] + by_status[LeadStatus.HOT.value]
    pending_review = _count(
        db,
        OutreachTask,
        OutreachTask.task_type == TaskType.REVIEW_CONNECTION.value,
        OutreachTask.status == TaskStatus.PENDING.value,
        *task_filter,
    )

    connection_tasks = _count(
        db, OutreachTask, OutreachTask.task_type.in_(CONNECTION_TASK_TYPES), *task_filter
    )
    message_tasks = _count(
        db, OutreachTask, OutreachTask.task_type.in_(MESSAGE_TASK_TYPES), *task_filter
    )
    followup_tasks = _count(
        db, OutreachTask, OutreachTask.task_type.in_(FOLLOW_UP_TASK_TYPES), *task_filter
    )
    completed_tasks = _count(
        db, OutreachTask, OutreachTask.status == TaskStatus.COMPLETED.value, *task_filter
    )
    pending_tasks = _count(
        db, OutreachTask, OutreachTask.status == TaskStatus.PENDING.value, *task_filter
    )
    approved_tasks = _count(
        db, OutreachTask, OutreachTask.status == TaskStatus.APPROVED.value, *task_filter
    )
    due_today = _count(
        db,
        OutreachTask,
        OutreachTask.scheduled_date <= date.today(),
        OutreachTask.status.in_([TaskStatus.PENDING.value, TaskStatus.APPROVED.value]),
        *task_filter,
    )

    total_replies = _count(db, Reply, *reply_filter)
    positive_replies = _count(
        db, Reply, Reply.reply_type.in_([r.value for r in POSITIVE_REPLIES]), *reply_filter
    )
    meetings = _count(
        db, Reply, Reply.reply_type == ReplyStatus.MEETING_REQUESTED.value, *reply_filter
    )
    connected = _count(
        db, Lead, Lead.connection_status == ConnectionStatus.CONNECTED.value, *lead_filter
    )

    sent_messages = _count(
        db,
        OutreachTask,
        OutreachTask.status == TaskStatus.COMPLETED.value,
        OutreachTask.task_type.notin_(CONNECTION_TASK_TYPES),
        *task_filter,
    )
    conversion_rate = round((total_replies / sent_messages) * 100, 1) if sent_messages else 0.0
    qualification_rate = round((qualified / total_leads) * 100, 1) if total_leads else 0.0

    return {
        "totalProspects": total_leads,
        "qualifiedLeads": qualified,
        "hotLeads": by_status[LeadStatus.HOT.value],
        "warmLeads": by_status[LeadStatus.WARM.value],
        "coldLeads": by_status[LeadStatus.COLD.value],
        "rejectedLeads": by_status[LeadStatus.REJECTED.value],
        "pendingReview": pending_review,
        "connectionTasks": connection_tasks,
        "messageTasks": message_tasks,
        "followUpTasks": followup_tasks,
        "completedTasks": completed_tasks,
        "pendingTasks": pending_tasks,
        "approvedTasks": approved_tasks,
        "dueToday": due_today,
        "replies": total_replies,
        "positiveReplies": positive_replies,
        "meetings": meetings,
        "connections": connected,
        "conversionRate": conversion_rate,
        "qualificationRate": qualification_rate,
    }


def leads_by_campaign(db: Session) -> list[dict[str, Any]]:
    rows = db.execute(
        select(
            Campaign.id,
            Campaign.name,
            func.count(Lead.id),
            func.coalesce(func.avg(Lead.score), 0),
        )
        .select_from(Campaign)
        .outerjoin(Lead, Lead.campaign_id == Campaign.id)
        .group_by(Campaign.id)
        .order_by(func.count(Lead.id).desc())
    ).all()
    return [
        {
            "campaignId": row[0],
            "campaign": row[1],
            "leads": int(row[2] or 0),
            "averageScore": round(float(row[3] or 0), 1),
        }
        for row in rows
    ]


def qualified_vs_rejected(db: Session, campaign_id: int | None = None) -> list[dict[str, Any]]:
    lead_filter = _campaign_filter(Lead, campaign_id)
    labels = {
        LeadStatus.HOT: "Hot",
        LeadStatus.QUALIFIED: "Qualified",
        LeadStatus.WARM: "Warm",
        LeadStatus.COLD: "Cold",
        LeadStatus.REJECTED: "Rejected",
    }
    return [
        {
            "status": status.value,
            "label": label,
            "count": _count(db, Lead, Lead.status == status.value, *lead_filter),
        }
        for status, label in labels.items()
    ]


def score_distribution(db: Session, campaign_id: int | None = None) -> list[dict[str, Any]]:
    """Histogram in 10-point buckets, plus the band each bucket belongs to."""
    lead_filter = _campaign_filter(Lead, campaign_id)
    buckets: list[dict[str, Any]] = []
    for start in range(0, 100, 10):
        end = start + 9
        count = _count(
            db,
            Lead,
            Lead.score >= start,
            Lead.score <= end,
            Lead.is_rejected.is_(False),
            *lead_filter,
        )
        band = next((b for b in SCORE_BANDS if b.contains(start)), SCORE_BANDS[0])
        buckets.append(
            {"bucket": f"{start}-{end}", "min": start, "max": end, "count": count, "band": band.label}
        )
    # 100 is its own edge case, folded into the top bucket.
    buckets[-1]["count"] += _count(db, Lead, Lead.score == 100, Lead.is_rejected.is_(False), *lead_filter)
    buckets[-1]["bucket"] = "90-100"
    buckets[-1]["max"] = 100
    return buckets


def outreach_activity(db: Session, days: int = 14, campaign_id: int | None = None) -> list[dict[str, Any]]:
    """Daily counts of created / completed tasks and replies for the activity chart."""
    start = date.today() - timedelta(days=days - 1)
    task_filter = _campaign_filter(OutreachTask, campaign_id)
    reply_filter = _campaign_filter(Reply, campaign_id)

    series: list[dict[str, Any]] = []
    for offset in range(days):
        day = start + timedelta(days=offset)
        next_day = day + timedelta(days=1)
        created = _count(
            db,
            OutreachTask,
            OutreachTask.created_at >= day,
            OutreachTask.created_at < next_day,
            *task_filter,
        )
        completed = _count(
            db,
            OutreachTask,
            OutreachTask.completed_at >= day,
            OutreachTask.completed_at < next_day,
            *task_filter,
        )
        replies = _count(
            db, Reply, Reply.received_at >= day, Reply.received_at < next_day, *reply_filter
        )
        series.append(
            {
                "date": day.isoformat(),
                "label": day.strftime("%d %b"),
                "created": created,
                "completed": completed,
                "replies": replies,
            }
        )
    return series


def reply_breakdown(db: Session, campaign_id: int | None = None) -> list[dict[str, Any]]:
    reply_filter = _campaign_filter(Reply, campaign_id)
    return [
        {
            "type": status.value,
            "label": status.value.replace("_", " ").title(),
            "count": _count(db, Reply, Reply.reply_type == status.value, *reply_filter),
        }
        for status in ReplyStatus
        if status != ReplyStatus.NO_REPLY
    ]


def campaign_performance(db: Session) -> list[dict[str, Any]]:
    campaigns = db.scalars(select(Campaign).order_by(Campaign.id)).all()
    rows: list[dict[str, Any]] = []
    for campaign in campaigns:
        stats = overview(db, campaign.id)
        rows.append(
            {
                "campaignId": campaign.id,
                "campaign": campaign.name,
                "status": campaign.status,
                "leads": stats["totalProspects"],
                "qualified": stats["qualifiedLeads"],
                "hot": stats["hotLeads"],
                "rejected": stats["rejectedLeads"],
                "tasksCompleted": stats["completedTasks"],
                "replies": stats["replies"],
                "positiveReplies": stats["positiveReplies"],
                "meetings": stats["meetings"],
                "conversionRate": stats["conversionRate"],
            }
        )
    return rows


def lead_sources(db: Session, campaign_id: int | None = None) -> list[dict[str, Any]]:
    lead_filter = _campaign_filter(Lead, campaign_id)
    query = select(Lead.source, func.count(Lead.id)).group_by(Lead.source)
    for condition in lead_filter:
        query = query.where(condition)
    return [
        {"source": row[0] or "unknown", "count": int(row[1] or 0)}
        for row in db.execute(query).all()
    ]


def recent_events(db: Session, limit: int = 20, campaign_id: int | None = None) -> list[dict[str, Any]]:
    query = select(AnalyticsEvent).order_by(AnalyticsEvent.id.desc()).limit(limit)
    if campaign_id is not None:
        query = query.where(AnalyticsEvent.campaign_id == campaign_id)
    return [
        {
            "id": event.id,
            "type": event.event_type,
            "entityType": event.entity_type,
            "entityId": event.entity_id,
            "campaignId": event.campaign_id,
            "payload": event.payload,
            "createdAt": event.created_at.isoformat() if event.created_at else None,
        }
        for event in db.scalars(query).all()
    ]


def dashboard(db: Session, campaign_id: int | None = None) -> dict[str, Any]:
    return {
        "overview": overview(db, campaign_id),
        "leadsByCampaign": leads_by_campaign(db),
        "qualifiedVsRejected": qualified_vs_rejected(db, campaign_id),
        "scoreDistribution": score_distribution(db, campaign_id),
        "outreachActivity": outreach_activity(db, 14, campaign_id),
        "replyBreakdown": reply_breakdown(db, campaign_id),
        "campaignPerformance": campaign_performance(db),
        "leadSources": lead_sources(db, campaign_id),
        "recentEvents": recent_events(db, 12, campaign_id),
    }