python/services/gs_analytics_service.py

"""
Ghost Shopper Analytics Service — Assessment-level analytics.

Provides:
    - Outlet comparison (avg scores across all outlets)
    - Category breakdown (which category is weakest/strongest)
    - Weekly trend analysis per outlet
"""

import uuid
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


CATEGORY_LABELS = {
    "A": "Pelayanan Awal",
    "B": "Kualitas Produk",
    "C": "Kebersihan & Suasana",
    "D": "Kepatuhan SOP",
    "E": "Keseluruhan Pengalaman",
}


class GhostShopperAnalyticsService:
    """Analytics service for Ghost Shopper assessment data."""

    async def get_outlet_comparison(self, db: AsyncSession) -> dict:
        """
        Compare all outlets side by side:
        - Overall avg score per outlet
        - Per-category avg score per outlet
        """
        # Overall scores per outlet
        result = await db.execute(text("""
            SELECT
                o.id, o.name,
                COUNT(DISTINCT v.id) AS visit_count,
                COALESCE(ROUND(AVG(a.score)::numeric, 2), 0) AS avg_score
            FROM outlets o
            LEFT JOIN visits v ON v.outlet_id = o.id
            LEFT JOIN assessments a ON a.visit_id = v.id
            GROUP BY o.id, o.name
            ORDER BY avg_score DESC
        """))
        outlets = []
        for row in result.fetchall():
            outlets.append({
                "id": str(row[0]),
                "name": row[1],
                "visit_count": row[2],
                "avg_score": float(row[3]),
            })

        # Per-category scores per outlet
        cat_result = await db.execute(text("""
            SELECT
                o.name, a.category,
                ROUND(AVG(a.score)::numeric, 2) AS avg_score
            FROM outlets o
            JOIN visits v ON v.outlet_id = o.id
            JOIN assessments a ON a.visit_id = v.id
            GROUP BY o.name, a.category
            ORDER BY o.name, a.category
        """))
        category_matrix = {}
        for row in cat_result.fetchall():
            outlet_name = row[0]
            if outlet_name not in category_matrix:
                category_matrix[outlet_name] = {}
            category_matrix[outlet_name][row[1]] = float(row[2])

        return {
            "status": "success",
            "data": {
                "outlets": outlets,
                "category_matrix": category_matrix,
                "category_labels": CATEGORY_LABELS,
            },
        }

    async def get_outlet_trends(
        self, db: AsyncSession, outlet_id: uuid.UUID
    ) -> dict | None:
        """
        Weekly score trends for a specific outlet.
        Returns chronological list of visits with per-category scores.
        """
        # Verify outlet exists
        result = await db.execute(
            text("SELECT name FROM outlets WHERE id = :oid"),
            {"oid": outlet_id},
        )
        row = result.fetchone()
        if not row:
            return None
        outlet_name = row[0]

        # Get per-visit scores over time
        trend_result = await db.execute(text("""
            SELECT
                v.id, v.title, v.visit_date,
                ROUND(AVG(a.score)::numeric, 2) AS avg_score,
                ROUND(AVG(CASE WHEN a.category = 'A' THEN a.score END)::numeric, 2) AS cat_a,
                ROUND(AVG(CASE WHEN a.category = 'B' THEN a.score END)::numeric, 2) AS cat_b,
                ROUND(AVG(CASE WHEN a.category = 'C' THEN a.score END)::numeric, 2) AS cat_c,
                ROUND(AVG(CASE WHEN a.category = 'D' THEN a.score END)::numeric, 2) AS cat_d,
                ROUND(AVG(CASE WHEN a.category = 'E' THEN a.score END)::numeric, 2) AS cat_e
            FROM visits v
            JOIN assessments a ON a.visit_id = v.id
            WHERE v.outlet_id = :oid
            GROUP BY v.id, v.title, v.visit_date
            ORDER BY v.visit_date ASC
        """), {"oid": outlet_id})

        trends = []
        for tr in trend_result.fetchall():
            trends.append({
                "visit_id": str(tr[0]),
                "title": tr[1],
                "visit_date": tr[2].isoformat() if tr[2] else None,
                "avg_score": float(tr[3]) if tr[3] else 0.0,
                "categories": {
                    "A": float(tr[4]) if tr[4] else None,
                    "B": float(tr[5]) if tr[5] else None,
                    "C": float(tr[6]) if tr[6] else None,
                    "D": float(tr[7]) if tr[7] else None,
                    "E": float(tr[8]) if tr[8] else None,
                },
            })

        return {
            "status": "success",
            "data": {
                "outlet_id": str(outlet_id),
                "outlet_name": outlet_name,
                "trends": trends,
                "category_labels": CATEGORY_LABELS,
            },
        }

    async def get_category_summary(self, db: AsyncSession) -> dict:
        """
        Global category breakdown: average score per assessment category
        across all outlets and visits. Identifies strengths and weaknesses.
        """
        result = await db.execute(text("""
            SELECT
                a.category,
                ROUND(AVG(a.score)::numeric, 2) AS avg_score,
                COUNT(a.id) AS total_items,
                ROUND(MIN(a.score)::numeric, 2) AS min_score,
                ROUND(MAX(a.score)::numeric, 2) AS max_score
            FROM assessments a
            GROUP BY a.category
            ORDER BY a.category
        """))

        categories = []
        for row in result.fetchall():
            categories.append({
                "category": row[0],
                "label": CATEGORY_LABELS.get(row[0], row[0]),
                "avg_score": float(row[1]),
                "total_items": row[2],
                "min_score": float(row[3]),
                "max_score": float(row[4]),
            })

        return {
            "status": "success",
            "data": {"categories": categories},
        }


# Singleton
gs_analytics_service = GhostShopperAnalyticsService()