"""Service layer for the Visitor Monitor module.

Two responsibilities:

1. **Ingest** — accept pageview/event payloads from the frontend, normalise
   them (User-Agent parsing, UTM capture, IP extraction), and upsert into
   ``vm_sessions`` / ``vm_events``. Failures are logged and swallowed so
   tracking never impacts real user traffic.
2. **Read aggregates** — produce the data the admin portal needs: live
   sessions, KPI overview, reports grouped by day/source/campaign/device/
   browser/country, per-session journey, and per-campaign stats.

All SQL uses SQLAlchemy Core/ORM + parameterised queries — no string
interpolation of user input.
"""

from __future__ import annotations

import hashlib
import ipaddress
import json
import logging
import re
from datetime import datetime, timedelta, timezone
from typing import Any, Dict, Iterable, List, Optional, Tuple

from fastapi import Request
from models.visitor_monitor import Campaign, TrackingSetting, VisitorEvent, VisitorSession
from schemas.visitor_monitor import (
    BucketStat,
    CampaignItem,
    DailyStat,
    JourneyEventItem,
    JourneyResponse,
    OverviewStats,
    ReportResponse,
    SessionItem,
    TrackingSettingsOut,
    TrackingSettingsUpdate,
)
from sqlalchemy import and_, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

logger = logging.getLogger(__name__)


ONLINE_WINDOW_SECONDS = 120  # a session is "online" if last activity < 2 minutes ago
CONVERSION_STAGES = ("visited", "lead", "signup", "order")
CONVERSION_RANK = {stage: idx for idx, stage in enumerate(CONVERSION_STAGES)}

_SENSITIVE_KEYS = {
    "password",
    "pwd",
    "pass",
    "secret",
    "token",
    "card",
    "cvv",
    "cvc",
    "pin",
    "ssn",
    "credit_card",
}


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _client_ip(request: Optional[Request]) -> Optional[str]:
    if request is None:
        return None
    xff = request.headers.get("x-forwarded-for")
    if xff:
        candidate = xff.split(",")[0].strip()
        if candidate:
            return candidate[:64]
    client = request.client
    if client and client.host:
        return client.host[:64]
    return None


def _user_agent(request: Optional[Request]) -> Optional[str]:
    if request is None:
        return None
    ua = request.headers.get("user-agent")
    return ua[:512] if ua else None


def _hash_visitor(ip: Optional[str], ua: Optional[str]) -> Optional[str]:
    if not ip and not ua:
        return None
    raw = f"{ip or ''}|{ua or ''}"
    return hashlib.sha256(raw.encode("utf-8")).hexdigest()[:32]


def _parse_user_agent(ua: Optional[str]) -> Tuple[str, str, str]:
    """Return ``(device, browser, os)`` — tiny regex-based parser.

    We intentionally avoid adding a new dependency; this is a pragmatic
    best-effort that covers the most common cases seen in server logs.
    """
    if not ua:
        return ("unknown", "unknown", "unknown")
    low = ua.lower()

    # Device
    if "bot" in low or "crawler" in low or "spider" in low:
        device = "bot"
    elif "tablet" in low or "ipad" in low:
        device = "tablet"
    elif "mobi" in low or "android" in low or "iphone" in low:
        device = "mobile"
    else:
        device = "desktop"

    # Browser
    if "edg/" in low or "edge" in low:
        browser = "Edge"
    elif "opr/" in low or "opera" in low:
        browser = "Opera"
    elif "chrome/" in low and "chromium" not in low:
        browser = "Chrome"
    elif "firefox" in low:
        browser = "Firefox"
    elif "safari" in low and "chrome" not in low:
        browser = "Safari"
    else:
        browser = "Other"

    # OS
    if "windows" in low:
        os_name = "Windows"
    elif "mac os" in low or "macintosh" in low:
        os_name = "macOS"
    elif "iphone" in low or "ipad" in low or "ios" in low:
        os_name = "iOS"
    elif "android" in low:
        os_name = "Android"
    elif "linux" in low:
        os_name = "Linux"
    else:
        os_name = "Other"

    return (device, browser, os_name)


_COUNTRY_HINTS = {
    # Very small hint map so dev environments still show *something*.
    "127.0.0.1": ("Local", "Localhost"),
    "::1": ("Local", "Localhost"),
}


def _geo_for_ip(ip: Optional[str]) -> Tuple[Optional[str], Optional[str]]:
    """Best-effort country/city lookup.

    Without a GeoIP database we return a small built-in hint map, falling back
    to ``(None, None)``. The admin portal treats missing geo gracefully.
    """
    if not ip:
        return (None, None)
    if ip in _COUNTRY_HINTS:
        return _COUNTRY_HINTS[ip]
    try:
        addr = ipaddress.ip_address(ip)
        if addr.is_private or addr.is_loopback:
            return ("Local", None)
    except ValueError:
        return (None, None)
    return (None, None)


def _mask_ip(ip: Optional[str]) -> Optional[str]:
    if not ip:
        return ip
    try:
        addr = ipaddress.ip_address(ip)
    except ValueError:
        return ip
    if isinstance(addr, ipaddress.IPv4Address):
        parts = ip.split(".")
        if len(parts) == 4:
            parts[-1] = "xxx"
            return ".".join(parts)
    else:
        parts = ip.split(":")
        if len(parts) > 1:
            parts[-1] = "xxxx"
            return ":".join(parts)
    return ip


def _strip_sensitive(data: Optional[Dict[str, Any]]) -> Optional[Dict[str, Any]]:
    if not data:
        return None
    out: Dict[str, Any] = {}
    for k, v in data.items():
        if not isinstance(k, str):
            continue
        if k.lower() in _SENSITIVE_KEYS:
            continue
        out[k] = v
    return out or None


def _parse_excluded_ips(raw: Optional[str]) -> List[str]:
    if not raw:
        return []
    return [entry.strip() for entry in raw.split(",") if entry.strip()]


def _ip_matches_any(ip: Optional[str], patterns: Iterable[str]) -> bool:
    if not ip:
        return False
    try:
        addr = ipaddress.ip_address(ip)
    except ValueError:
        return False
    for raw in patterns:
        try:
            if "/" in raw:
                if addr in ipaddress.ip_network(raw, strict=False):
                    return True
            else:
                if ip == raw:
                    return True
        except ValueError:
            continue
    return False


def _duration_seconds(started_at: datetime, last_activity_at: datetime) -> int:
    try:
        delta = last_activity_at - started_at
        return max(0, int(delta.total_seconds()))
    except Exception:  # noqa: BLE001
        return 0


def _is_online(last_activity_at: datetime) -> bool:
    try:
        delta = _now() - last_activity_at
        return delta.total_seconds() <= ONLINE_WINDOW_SECONDS
    except Exception:  # noqa: BLE001
        return False


def _to_session_item(row: VisitorSession, mask_ip: bool) -> SessionItem:
    ip = _mask_ip(row.ip_address) if mask_ip else row.ip_address
    item = SessionItem(
        id=row.id,
        session_id=row.session_id,
        visitor_id=row.visitor_id,
        user_id=row.user_id,
        ip_address=ip,
        country=row.country,
        city=row.city,
        device=row.device,
        browser=row.browser,
        os=row.os,
        referrer=row.referrer,
        landing_page=row.landing_page,
        current_page=row.current_page,
        utm_source=row.utm_source,
        utm_medium=row.utm_medium,
        utm_campaign=row.utm_campaign,
        pageview_count=row.pageview_count or 0,
        conversion_stage=row.conversion_stage or "visited",
        started_at=row.started_at,
        last_activity_at=row.last_activity_at,
        ended_at=row.ended_at,
        is_online=_is_online(row.last_activity_at),
        duration_seconds=_duration_seconds(row.started_at, row.last_activity_at),
    )
    return item


# ---------------------------------------------------------------------------
# Settings
# ---------------------------------------------------------------------------


async def get_settings(db: AsyncSession) -> TrackingSetting:
    result = await db.execute(select(TrackingSetting).order_by(TrackingSetting.id.asc()).limit(1))
    row = result.scalar_one_or_none()
    if row is None:
        row = TrackingSetting()
        db.add(row)
        await db.commit()
        await db.refresh(row)
    return row


def settings_to_dto(row: TrackingSetting) -> TrackingSettingsOut:
    return TrackingSettingsOut(
        tracking_enabled=bool(row.tracking_enabled),
        retention_days=int(row.retention_days or 90),
        excluded_ips=_parse_excluded_ips(row.excluded_ips),
        exclude_staff=bool(row.exclude_staff),
        mask_sensitive=bool(row.mask_sensitive),
        updated_at=row.updated_at,
    )


async def update_settings(
    db: AsyncSession, payload: TrackingSettingsUpdate
) -> TrackingSettingsOut:
    row = await get_settings(db)
    if payload.tracking_enabled is not None:
        row.tracking_enabled = payload.tracking_enabled
    if payload.retention_days is not None:
        row.retention_days = payload.retention_days
    if payload.excluded_ips is not None:
        cleaned = [e.strip() for e in payload.excluded_ips if e and e.strip()]
        row.excluded_ips = ",".join(cleaned) if cleaned else None
    if payload.exclude_staff is not None:
        row.exclude_staff = payload.exclude_staff
    if payload.mask_sensitive is not None:
        row.mask_sensitive = payload.mask_sensitive
    await db.commit()
    await db.refresh(row)
    return settings_to_dto(row)


# ---------------------------------------------------------------------------
# Ingest
# ---------------------------------------------------------------------------


async def _get_or_create_session(
    db: AsyncSession,
    *,
    session_id: str,
    ip: Optional[str],
    ua: Optional[str],
    referrer: Optional[str],
    page: str,
    utm: Dict[str, Optional[str]],
) -> VisitorSession:
    result = await db.execute(
        select(VisitorSession).where(VisitorSession.session_id == session_id)
    )
    session = result.scalar_one_or_none()
    if session is not None:
        return session

    device, browser, os_name = _parse_user_agent(ua)
    country, city = _geo_for_ip(ip)
    session = VisitorSession(
        session_id=session_id,
        visitor_id=_hash_visitor(ip, ua),
        ip_address=ip,
        country=country,
        city=city,
        device=device,
        browser=browser,
        os=os_name,
        user_agent=ua,
        referrer=referrer,
        landing_page=page,
        current_page=page,
        utm_source=utm.get("utm_source"),
        utm_medium=utm.get("utm_medium"),
        utm_campaign=utm.get("utm_campaign"),
        utm_term=utm.get("utm_term"),
        utm_content=utm.get("utm_content"),
        pageview_count=0,
    )
    db.add(session)
    await db.flush()
    return session


async def record_pageview(
    db: AsyncSession,
    *,
    request: Optional[Request],
    session_id: str,
    page: str,
    referrer: Optional[str],
    utm: Dict[str, Optional[str]],
    user_id: Optional[str] = None,
    user_role: Optional[str] = None,
) -> Tuple[bool, bool]:
    """Record a pageview. Returns ``(ok, excluded)``.

    ``excluded=True`` means the session matched an exclusion rule (admin IP,
    admin role, or tracking disabled) and was flagged as excluded. The
    endpoint still returns 200 to avoid leaking which IPs are excluded.
    """
    settings = await get_settings(db)
    if not settings.tracking_enabled:
        return (True, True)

    ip = _client_ip(request)
    ua = _user_agent(request)

    excluded = False
    if settings.exclude_staff and (user_role or "").lower() in {"admin", "staff"}:
        excluded = True
    if not excluded and _ip_matches_any(ip, _parse_excluded_ips(settings.excluded_ips)):
        excluded = True

    session = await _get_or_create_session(
        db,
        session_id=session_id,
        ip=ip,
        ua=ua,
        referrer=referrer,
        page=page,
        utm=utm,
    )
    session.current_page = page
    session.last_activity_at = _now()
    session.pageview_count = (session.pageview_count or 0) + 1
    if user_id and not session.user_id:
        session.user_id = user_id
    if excluded:
        session.is_excluded = True

    db.add(
        VisitorEvent(
            session_id=session_id,
            event_type="pageview",
            label=None,
            page=page,
        )
    )
    await db.commit()
    return (True, excluded)


async def record_event(
    db: AsyncSession,
    *,
    request: Optional[Request],
    session_id: str,
    event_type: str,
    label: Optional[str],
    page: Optional[str],
    metadata: Optional[Dict[str, Any]],
    user_id: Optional[str] = None,
    user_role: Optional[str] = None,
) -> Tuple[bool, bool]:
    settings = await get_settings(db)
    if not settings.tracking_enabled:
        return (True, True)

    ip = _client_ip(request)
    ua = _user_agent(request)
    excluded = False
    if settings.exclude_staff and (user_role or "").lower() in {"admin", "staff"}:
        excluded = True
    if not excluded and _ip_matches_any(ip, _parse_excluded_ips(settings.excluded_ips)):
        excluded = True

    # Ensure session exists — events can arrive before a pageview in edge cases.
    session = await _get_or_create_session(
        db,
        session_id=session_id,
        ip=ip,
        ua=ua,
        referrer=None,
        page=page or "",
        utm={},
    )
    session.last_activity_at = _now()
    if user_id and not session.user_id:
        session.user_id = user_id
    if excluded:
        session.is_excluded = True

    # Conversion stage transitions (only move forward).
    stage_map = {
        "form_submit": "lead",
        "signup": "signup",
        "order": "order",
    }
    new_stage = stage_map.get(event_type)
    if new_stage and CONVERSION_RANK[new_stage] > CONVERSION_RANK.get(
        session.conversion_stage or "visited", 0
    ):
        session.conversion_stage = new_stage

    safe_meta = _strip_sensitive(metadata)
    db.add(
        VisitorEvent(
            session_id=session_id,
            event_type=event_type[:30],
            label=(label or None),
            page=page or None,
            metadata_json=json.dumps(safe_meta, default=str) if safe_meta else None,
        )
    )
    await db.commit()
    return (True, excluded)


# ---------------------------------------------------------------------------
# Read: sessions / overview / reports / journey
# ---------------------------------------------------------------------------


async def list_sessions(
    db: AsyncSession,
    *,
    only_online: bool = False,
    search: Optional[str] = None,
    country: Optional[str] = None,
    device: Optional[str] = None,
    utm_campaign: Optional[str] = None,
    page: int = 1,
    page_size: int = 50,
) -> Tuple[List[SessionItem], int]:
    settings = await get_settings(db)
    mask = bool(settings.mask_sensitive)

    stmt = select(VisitorSession).where(VisitorSession.is_excluded.is_(False))
    count_stmt = (
        select(func.count())
        .select_from(VisitorSession)
        .where(VisitorSession.is_excluded.is_(False))
    )

    if only_online:
        cutoff = _now() - timedelta(seconds=ONLINE_WINDOW_SECONDS)
        stmt = stmt.where(VisitorSession.last_activity_at >= cutoff)
        count_stmt = count_stmt.where(VisitorSession.last_activity_at >= cutoff)
    if search:
        pat = f"%{search.strip().lower()}%"
        clause = or_(
            func.lower(VisitorSession.ip_address).like(pat),
            func.lower(VisitorSession.country).like(pat),
            func.lower(VisitorSession.city).like(pat),
            func.lower(VisitorSession.current_page).like(pat),
            func.lower(VisitorSession.referrer).like(pat),
            func.lower(VisitorSession.utm_campaign).like(pat),
        )
        stmt = stmt.where(clause)
        count_stmt = count_stmt.where(clause)
    if country:
        stmt = stmt.where(VisitorSession.country == country)
        count_stmt = count_stmt.where(VisitorSession.country == country)
    if device:
        stmt = stmt.where(VisitorSession.device == device)
        count_stmt = count_stmt.where(VisitorSession.device == device)
    if utm_campaign:
        stmt = stmt.where(VisitorSession.utm_campaign == utm_campaign)
        count_stmt = count_stmt.where(VisitorSession.utm_campaign == utm_campaign)

    total_res = await db.execute(count_stmt)
    total = int(total_res.scalar() or 0)

    stmt = (
        stmt.order_by(VisitorSession.last_activity_at.desc(), VisitorSession.id.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
    )
    rows = (await db.execute(stmt)).scalars().all()
    return ([_to_session_item(r, mask) for r in rows], total)


async def overview_stats(db: AsyncSession) -> OverviewStats:
    base = select(VisitorSession).where(VisitorSession.is_excluded.is_(False))

    total_visits_res = await db.execute(
        select(func.coalesce(func.sum(VisitorSession.pageview_count), 0)).where(
            VisitorSession.is_excluded.is_(False)
        )
    )
    total_visits = int(total_visits_res.scalar() or 0)

    unique_res = await db.execute(
        select(func.count(func.distinct(VisitorSession.visitor_id))).where(
            and_(VisitorSession.is_excluded.is_(False), VisitorSession.visitor_id.isnot(None))
        )
    )
    unique_visitors = int(unique_res.scalar() or 0)

    campaign_clicks_res = await db.execute(
        select(func.count()).select_from(VisitorSession).where(
            and_(
                VisitorSession.is_excluded.is_(False),
                VisitorSession.utm_campaign.isnot(None),
            )
        )
    )
    campaign_clicks = int(campaign_clicks_res.scalar() or 0)

    referral_clicks_res = await db.execute(
        select(func.count()).select_from(VisitorSession).where(
            and_(
                VisitorSession.is_excluded.is_(False),
                VisitorSession.referrer.isnot(None),
                VisitorSession.referrer != "",
            )
        )
    )
    referral_clicks = int(referral_clicks_res.scalar() or 0)

    converted_res = await db.execute(
        select(func.count()).select_from(VisitorSession).where(
            and_(
                VisitorSession.is_excluded.is_(False),
                VisitorSession.conversion_stage.in_(("lead", "signup", "order")),
            )
        )
    )
    converted = int(converted_res.scalar() or 0)

    sessions_total_res = await db.execute(
        select(func.count()).select_from(VisitorSession).where(
            VisitorSession.is_excluded.is_(False)
        )
    )
    sessions_total = int(sessions_total_res.scalar() or 0)

    rate = (converted / sessions_total * 100.0) if sessions_total > 0 else 0.0

    cutoff = _now() - timedelta(seconds=ONLINE_WINDOW_SECONDS)
    active_res = await db.execute(
        select(func.count()).select_from(VisitorSession).where(
            and_(
                VisitorSession.is_excluded.is_(False),
                VisitorSession.last_activity_at >= cutoff,
            )
        )
    )
    active_now = int(active_res.scalar() or 0)

    return OverviewStats(
        total_visits=total_visits,
        unique_visitors=unique_visitors,
        campaign_clicks=campaign_clicks,
        referral_clicks=referral_clicks,
        conversion_rate=round(rate, 2),
        active_now=active_now,
    )


async def report(
    db: AsyncSession,
    *,
    start: Optional[datetime],
    end: Optional[datetime],
    campaign: Optional[str] = None,
    source: Optional[str] = None,
    country: Optional[str] = None,
    device: Optional[str] = None,
) -> ReportResponse:
    def _apply_filters(stmt):
        stmt = stmt.where(VisitorSession.is_excluded.is_(False))
        if start:
            stmt = stmt.where(VisitorSession.started_at >= start)
        if end:
            stmt = stmt.where(VisitorSession.started_at < end)
        if campaign:
            stmt = stmt.where(VisitorSession.utm_campaign == campaign)
        if source:
            stmt = stmt.where(VisitorSession.utm_source == source)
        if country:
            stmt = stmt.where(VisitorSession.country == country)
        if device:
            stmt = stmt.where(VisitorSession.device == device)
        return stmt

    # By day
    day_expr = func.date(VisitorSession.started_at)
    by_day_stmt = _apply_filters(
        select(
            day_expr.label("d"),
            func.coalesce(func.sum(VisitorSession.pageview_count), 0).label("visits"),
            func.count(func.distinct(VisitorSession.visitor_id)).label("uniques"),
        )
    ).group_by(day_expr).order_by(day_expr.asc())
    by_day_rows = (await db.execute(by_day_stmt)).all()
    by_day = [
        DailyStat(date=str(r.d), visits=int(r.visits or 0), unique_visitors=int(r.uniques or 0))
        for r in by_day_rows
    ]

    async def _bucket(column) -> List[BucketStat]:
        stmt = _apply_filters(
            select(column.label("k"), func.count().label("c"))
        ).group_by(column).order_by(func.count().desc()).limit(20)
        rows = (await db.execute(stmt)).all()
        return [BucketStat(key=str(r.k) if r.k else "unknown", count=int(r.c or 0)) for r in rows]

    by_source = await _bucket(VisitorSession.utm_source)
    by_campaign = await _bucket(VisitorSession.utm_campaign)
    by_device = await _bucket(VisitorSession.device)
    by_browser = await _bucket(VisitorSession.browser)
    by_country = await _bucket(VisitorSession.country)

    return ReportResponse(
        by_day=by_day,
        by_source=by_source,
        by_campaign=by_campaign,
        by_device=by_device,
        by_browser=by_browser,
        by_country=by_country,
    )


async def journey(db: AsyncSession, session_id: str) -> Optional[JourneyResponse]:
    res = await db.execute(
        select(VisitorSession).where(VisitorSession.session_id == session_id)
    )
    session = res.scalar_one_or_none()
    if session is None:
        return None
    settings = await get_settings(db)

    events_res = await db.execute(
        select(VisitorEvent)
        .where(VisitorEvent.session_id == session_id)
        .order_by(VisitorEvent.created_at.asc(), VisitorEvent.id.asc())
        .limit(500)
    )
    events = events_res.scalars().all()
    journey_events: List[JourneyEventItem] = []
    for e in events:
        meta: Optional[Dict[str, Any]] = None
        if e.metadata_json:
            try:
                parsed = json.loads(e.metadata_json)
                if isinstance(parsed, dict):
                    meta = parsed
            except (ValueError, TypeError):
                meta = None
        journey_events.append(
            JourneyEventItem(
                id=e.id,
                event_type=e.event_type,
                label=e.label,
                page=e.page,
                metadata=meta,
                created_at=e.created_at,
            )
        )
    return JourneyResponse(
        session=_to_session_item(session, bool(settings.mask_sensitive)),
        events=journey_events,
    )


# ---------------------------------------------------------------------------
# Campaigns
# ---------------------------------------------------------------------------


def _campaign_referral_url(campaign: Campaign, base_url: str) -> str:
    """Build a referral URL from a campaign's UTM tuple."""
    base = base_url.rstrip("/") or ""
    landing = campaign.landing_page or "/"
    if not landing.startswith("/"):
        landing = "/" + landing
    params: List[Tuple[str, str]] = []
    for key, val in (
        ("utm_source", campaign.utm_source),
        ("utm_medium", campaign.utm_medium),
        ("utm_campaign", campaign.utm_campaign),
        ("utm_term", campaign.utm_term),
        ("utm_content", campaign.utm_content),
    ):
        if val:
            params.append((key, val))
    if not params:
        return f"{base}{landing}"
    qs = "&".join(f"{k}={v}" for k, v in params)
    sep = "&" if "?" in landing else "?"
    return f"{base}{landing}{sep}{qs}"


async def _campaign_stats(
    db: AsyncSession, campaign: Campaign
) -> Dict[str, int]:
    # Match sessions whose UTM tuple matches this campaign (utm_campaign is
    # the required key; source narrows it further if distinct campaigns share
    # a name).
    base = select(VisitorSession).where(
        and_(
            VisitorSession.is_excluded.is_(False),
            VisitorSession.utm_campaign == campaign.utm_campaign,
            VisitorSession.utm_source == campaign.utm_source,
        )
    )

    total_clicks_res = await db.execute(select(func.count()).select_from(base.subquery()))
    total_clicks = int(total_clicks_res.scalar() or 0)

    unique_res = await db.execute(
        select(func.count(func.distinct(VisitorSession.visitor_id))).where(
            and_(
                VisitorSession.is_excluded.is_(False),
                VisitorSession.utm_campaign == campaign.utm_campaign,
                VisitorSession.utm_source == campaign.utm_source,
                VisitorSession.visitor_id.isnot(None),
            )
        )
    )
    unique_visitors = int(unique_res.scalar() or 0)

    async def _count_stage(stage: str) -> int:
        res = await db.execute(
            select(func.count()).select_from(VisitorSession).where(
                and_(
                    VisitorSession.is_excluded.is_(False),
                    VisitorSession.utm_campaign == campaign.utm_campaign,
                    VisitorSession.utm_source == campaign.utm_source,
                    VisitorSession.conversion_stage == stage,
                )
            )
        )
        return int(res.scalar() or 0)

    leads = await _count_stage("lead")
    signups = await _count_stage("signup")
    orders = await _count_stage("order")
    return {
        "total_clicks": total_clicks,
        "unique_visitors": unique_visitors,
        "leads": leads,
        "signups": signups,
        "orders": orders,
        "conversions": leads + signups + orders,
    }


async def campaign_to_item(
    db: AsyncSession, row: Campaign, base_url: str
) -> CampaignItem:
    stats = await _campaign_stats(db, row)
    item = CampaignItem.model_validate(row)
    item.total_clicks = stats["total_clicks"]
    item.unique_visitors = stats["unique_visitors"]
    item.leads = stats["leads"]
    item.signups = stats["signups"]
    item.orders = stats["orders"]
    item.conversions = stats["conversions"]
    item.referral_url = _campaign_referral_url(row, base_url)
    return item


async def list_campaigns(
    db: AsyncSession, *, base_url: str, status_filter: Optional[str] = None
) -> List[CampaignItem]:
    stmt = select(Campaign).order_by(Campaign.created_at.desc())
    if status_filter:
        stmt = stmt.where(Campaign.status == status_filter)
    rows = (await db.execute(stmt)).scalars().all()
    items: List[CampaignItem] = []
    for r in rows:
        items.append(await campaign_to_item(db, r, base_url))
    return items