@router.get("/overview")
async def overview(db: AsyncSession = Depends(get_db),
                   user: User = Depends(get_current_user)):
    """Everything the Dashboard page needs in one call."""
    now = datetime.now(timezone.utc)
    week_ago = now - timedelta(days=7)

    async def count(model, *filters):
        q = select(func.count()).select_from(model)
        for f in filters:
            q = q.where(f)
        return (await db.execute(q)).scalar() or 0

    total_campaigns = await count(Campaign, Campaign.user_id == user.id)
    active_campaigns = await count(Campaign, Campaign.user_id == user.id,
                                   Campaign.status.in_(["running", "queued"]))
    accounts = (await db.execute(
        select(GmailAccount).where(GmailAccount.user_id == user.id))).scalars().all()
    account_health = [{"id": a.id, "email": a.email, "status": a.status,
                       "sends_today": a.sends_today, "daily_limit": a.daily_limit,
                       "cooldown": a.cooldown_until.isoformat() if a.cooldown_until else None,
                       "is_active": a.is_active,
                       "sending_method": a.sending_method}
                      for a in accounts]

    # aggregate campaign counters
    agg = (await db.execute(
        select(func.count(Campaign.id),
               func.coalesce(func.sum(Campaign.total_recipients), 0),
               func.coalesce(func.sum(Campaign.sent_count), 0),
               func.coalesce(func.sum(Campaign.failed_count), 0),
               func.coalesce(func.sum(Campaign.suppressed_count), 0),
               func.coalesce(func.sum(Campaign.skipped_count), 0))
        .where(Campaign.user_id == user.id))).one()

    sends_last_7d = (await db.execute(
        select(func.count()).select_from(CampaignRecipient)
        .join(Campaign, Campaign.id == CampaignRecipient.campaign_id)
        .where(Campaign.user_id == user.id,
               CampaignRecipient.status == "sent",
               CampaignRecipient.sent_at >= week_ago))).scalar() or 0

    recent_campaigns = (await db.execute(
        select(Campaign).where(Campaign.user_id == user.id)
        .order_by(Campaign.id.desc()).limit(8))).scalars().all()

    return {
        "user": {"id": user.id, "email": user.email, "name": user.full_name},
        "totals": {
            "campaigns": total_campaigns,
            "active_campaigns": active_campaigns,
            # Recipient rows are owned via their RecipientList
            "recipients": (await db.execute(
                select(func.count()).select_from(Recipient)
                .join(RecipientList, RecipientList.id == Recipient.list_id)
                .where(RecipientList.user_id == user.id))).scalar() or 0,
            "recipient_lists": await count(RecipientList, RecipientList.user_id == user.id),
            "subjects": await count(SubjectLine, SubjectLine.user_id == user.id),
            "templates": await count(EmailTemplate, EmailTemplate.user_id == user.id),
            "attachments": await count(Attachment, Attachment.user_id == user.id),
            "phones": await count(CompanyPhoneNumber, CompanyPhoneNumber.user_id == user.id),
            "suppression": await count(SuppressionEntry, SuppressionEntry.user_id == user.id),
            "gmail_accounts": len(accounts),
        },
        "send_stats": {
            "total_recipients": int(agg[1]), "sent": int(agg[2]),
            "failed": int(agg[3]), "suppressed": int(agg[4]),
            "skipped": int(agg[5]), "sends_last_7d": int(sends_last_7d),
        },
        "accounts": account_health,
        "recent_campaigns": [{
            "id": c.id, "name": c.name, "status": c.status,
            "total": c.total_recipients, "sent": c.sent_count,
            "failed": c.failed_count, "suppressed": c.suppressed_count,
            "skipped": c.skipped_count,
            "started_at": c.started_at.isoformat() if c.started_at else None,
            "finished_at": c.finished_at.isoformat() if c.finished_at else None,
        } for c in recent_campaigns],
    }


@router.get("/status")
async def system_status(user: User = Depends(get_current_user)):
    """System component health for the dashboard status strip."""
    from app.worker.celery_app import celery_app
    import redis as redis_lib
    from app.core.config import settings

    out = {"postgres": "down", "redis": "down", "celery_worker": "unknown",
           "dlq_depth": 0, "time": datetime.now(timezone.utc).isoformat()}

    try:
        out["postgres"] = "ok" if check_db_health() else "down"
    except Exception:
        out["postgres"] = "down"
    try:
        r = redis_lib.Redis.from_url(settings.REDIS_URL, socket_connect_timeout=2)
        r.ping()
        out["redis"] = "ok"
        out["dlq_depth"] = int(r.llen("dlq:send") or 0)
    except Exception:
        out["redis"] = "down"
    try:
        ping = celery_app.control.ping(timeout=2.0)
        out["celery_worker"] = "ok" if ping else "no_workers"
    except Exception:
        out["celery_worker"] = "unknown"
    return out