"""
Analytics Service — Visit Data Statistics & Aggregation

Provides:
- Overall summary (total visits, status breakdown, completion rates)
- Per-visit analytics
- Trend data (visits per day/week/month)
- Checklist completion statistics

Uses raw SQLAlchemy async queries for efficient aggregation.
"""

import logging
from datetime import datetime, timedelta
from typing import Optional

from sqlalchemy import func, select, case
from sqlalchemy.ext.asyncio import AsyncSession

from models.visit_model import Visit
from models.checklist_model import Checklist
from models.photo_model import Photo

logger = logging.getLogger(__name__)


class AnalyticsService:
    """Computes visit and checklist statistics from the database."""

    # ── Overall Summary ───────────────────────────────────────────────────

    async def get_summary(self, db: AsyncSession) -> dict:
        """
        Overall analytics summary across all visits.

        Returns:
            {
              "visits": { total, by_status: {pending, in_progress, completed, cancelled} },
              "checklists": { total_items, checked_items, completion_rate_pct },
              "photos": { total },
              "generated_at": ISO timestamp
            }
        """
        logger.info("Computing analytics summary")

        # ── Visit counts by status ──
        visit_q = await db.execute(
            select(
                func.count(Visit.id).label("total"),
                func.sum(case((Visit.status == "pending", 1), else_=0)).label("pending"),
                func.sum(case((Visit.status == "in_progress", 1), else_=0)).label("in_progress"),
                func.sum(case((Visit.status == "completed", 1), else_=0)).label("completed"),
                func.sum(case((Visit.status == "cancelled", 1), else_=0)).label("cancelled"),
            )
        )
        visit_row = visit_q.one()

        # ── Checklist stats ──
        checklist_q = await db.execute(
            select(
                func.count(Checklist.id).label("total"),
                func.sum(case((Checklist.is_checked == True, 1), else_=0)).label("checked"),  # noqa: E712
            )
        )
        checklist_row = checklist_q.one()

        total_items = checklist_row.total or 0
        checked_items = checklist_row.checked or 0
        completion_rate = round((checked_items / total_items * 100), 1) if total_items > 0 else 0.0

        # ── Photo count ──
        photo_q = await db.execute(select(func.count(Photo.id)))
        total_photos = photo_q.scalar() or 0

        return {
            "status": "success",
            "data": {
                "visits": {
                    "total": visit_row.total or 0,
                    "by_status": {
                        "pending": visit_row.pending or 0,
                        "in_progress": visit_row.in_progress or 0,
                        "completed": visit_row.completed or 0,
                        "cancelled": visit_row.cancelled or 0,
                    },
                },
                "checklists": {
                    "total_items": total_items,
                    "checked_items": checked_items,
                    "completion_rate_pct": completion_rate,
                },
                "photos": {
                    "total": total_photos,
                },
            },
            "meta": {
                "generated_at": datetime.utcnow().isoformat(),
            },
        }

    # ── Per-Visit Analytics ───────────────────────────────────────────────

    async def get_visit_analytics(self, db: AsyncSession, visit_id) -> dict:
        """
        Analytics for a single visit.

        Returns checklist completion rate, item counts, photo count,
        and a breakdown of checked vs unchecked items.
        """
        # Visit exists check
        visit_q = await db.execute(select(Visit).where(Visit.id == visit_id))
        visit = visit_q.scalar_one_or_none()
        if not visit:
            return None

        # Checklist breakdown
        cl_q = await db.execute(
            select(
                func.count(Checklist.id).label("total"),
                func.sum(case((Checklist.is_checked == True, 1), else_=0)).label("checked"),  # noqa: E712
            ).where(Checklist.visit_id == visit_id)
        )
        cl_row = cl_q.one()
        total = cl_row.total or 0
        checked = cl_row.checked or 0
        rate = round((checked / total * 100), 1) if total > 0 else 0.0

        # Photo count
        photo_q = await db.execute(
            select(func.count(Photo.id)).where(Photo.visit_id == visit_id)
        )
        photo_count = photo_q.scalar() or 0

        return {
            "status": "success",
            "data": {
                "visit_id": str(visit_id),
                "title": visit.title,
                "status": visit.status,
                "checklists": {
                    "total_items": total,
                    "checked_items": checked,
                    "unchecked_items": total - checked,
                    "completion_rate_pct": rate,
                },
                "photos": {"total": photo_count},
            },
            "meta": {
                "generated_at": datetime.utcnow().isoformat(),
            },
        }

    # ── Trend Data ────────────────────────────────────────────────────────

    async def get_visit_trends(
        self, db: AsyncSession, days: int = 30
    ) -> dict:
        """
        Number of visits created per day over the last [days] days.

        Useful for charts in the Flutter analytics dashboard.
        """
        since = datetime.utcnow() - timedelta(days=days)

        # Use a literal column reference for GROUP BY to satisfy PostgreSQL
        day_col = func.date_trunc("day", Visit.created_at)
        trend_q = await db.execute(
            select(
                day_col.label("day"),
                func.count(Visit.id).label("count"),
            )
            .where(Visit.created_at >= since)
            .group_by(day_col)
            .order_by(day_col)
        )
        rows = trend_q.all()

        return {
            "status": "success",
            "data": {
                "period_days": days,
                "trends": [
                    {"date": str(r.day.date()), "count": r.count}
                    for r in rows
                ],
            },
            "meta": {
                "generated_at": datetime.utcnow().isoformat(),
                "since": since.date().isoformat(),
            },
        }


# Singleton
analytics_service = AnalyticsService()