"""
Analytics tracking - search events, click-through, application funnel.
"""
import uuid
from datetime import datetime, timezone

from sqlalchemy import DateTime, Integer, String, func
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import Mapped, mapped_column

from app.core.database import Base


class AnalyticsEvent(Base):
    """Track user interactions for analytics."""
    __tablename__ = "analytics_events"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    user_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), nullable=True, index=True)
    event_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    # Event types: search, job_view, job_save, job_apply, resume_create, ai_use, login, signup
    properties: Mapped[dict | None] = mapped_column(JSONB, nullable=True)
    # Properties vary by event: {query, filters, job_id, company, source, etc.}
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True
    )


class AnalyticsService:
    """Track and query analytics events."""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def track(self, event_type: str, user_id: str | None = None, properties: dict | None = None):
        """Track an analytics event."""
        event = AnalyticsEvent(
            user_id=uuid.UUID(user_id) if user_id else None,
            event_type=event_type,
            properties=properties,
        )
        self.db.add(event)
        # Don't await commit - let the request handler commit

    async def get_search_analytics(self, days: int = 30) -> dict:
        """Get search analytics: top queries, filters used, zero-result queries."""
        from datetime import timedelta
        from sqlalchemy import select, text

        cutoff = datetime.now(timezone.utc) - timedelta(days=days)

        # Total searches
        total = await self.db.scalar(
            select(func.count(AnalyticsEvent.id)).where(
                AnalyticsEvent.event_type == "search",
                AnalyticsEvent.created_at >= cutoff,
            )
        ) or 0

        # Top queries (using JSONB extraction)
        top_queries_result = await self.db.execute(
            text("""
                SELECT properties->>'query' as query, COUNT(*) as count
                FROM analytics_events
                WHERE event_type = 'search' 
                AND created_at >= :cutoff
                AND properties->>'query' IS NOT NULL
                AND properties->>'query' != ''
                GROUP BY properties->>'query'
                ORDER BY count DESC
                LIMIT 20
            """),
            {"cutoff": cutoff},
        )
        top_queries = [{"query": r[0], "count": r[1]} for r in top_queries_result.fetchall()]

        # Top filters
        top_filters_result = await self.db.execute(
            text("""
                SELECT key, COUNT(*) as count
                FROM analytics_events, jsonb_object_keys(properties->'filters') as key
                WHERE event_type = 'search'
                AND created_at >= :cutoff
                AND properties->'filters' IS NOT NULL
                GROUP BY key
                ORDER BY count DESC
                LIMIT 10
            """),
            {"cutoff": cutoff},
        )
        top_filters = [{"filter": r[0], "count": r[1]} for r in top_filters_result.fetchall()]

        return {
            "total_searches": total,
            "top_queries": top_queries,
            "top_filters": top_filters,
            "period_days": days,
        }

    async def get_funnel_analytics(self, days: int = 30) -> dict:
        """Get application funnel: view → save → apply."""
        from datetime import timedelta
        from sqlalchemy import select

        cutoff = datetime.now(timezone.utc) - timedelta(days=days)

        views = await self.db.scalar(
            select(func.count(AnalyticsEvent.id)).where(
                AnalyticsEvent.event_type == "job_view",
                AnalyticsEvent.created_at >= cutoff,
            )
        ) or 0

        saves = await self.db.scalar(
            select(func.count(AnalyticsEvent.id)).where(
                AnalyticsEvent.event_type == "job_save",
                AnalyticsEvent.created_at >= cutoff,
            )
        ) or 0

        applies = await self.db.scalar(
            select(func.count(AnalyticsEvent.id)).where(
                AnalyticsEvent.event_type == "job_apply",
                AnalyticsEvent.created_at >= cutoff,
            )
        ) or 0

        return {
            "funnel": [
                {"stage": "Views", "count": views},
                {"stage": "Saves", "count": saves, "rate": f"{(saves/views*100):.1f}%" if views > 0 else "0%"},
                {"stage": "Applications", "count": applies, "rate": f"{(applies/views*100):.1f}%" if views > 0 else "0%"},
            ],
            "period_days": days,
        }

    async def get_user_activity(self, user_id: str, days: int = 30) -> dict:
        """Get activity summary for a specific user."""
        from datetime import timedelta
        from sqlalchemy import select

        cutoff = datetime.now(timezone.utc) - timedelta(days=days)
        uid = uuid.UUID(user_id)

        events_result = await self.db.execute(
            select(AnalyticsEvent.event_type, func.count(AnalyticsEvent.id))
            .where(
                AnalyticsEvent.user_id == uid,
                AnalyticsEvent.created_at >= cutoff,
            )
            .group_by(AnalyticsEvent.event_type)
        )

        activity = {r[0]: r[1] for r in events_result.fetchall()}

        return {
            "searches": activity.get("search", 0),
            "jobs_viewed": activity.get("job_view", 0),
            "jobs_saved": activity.get("job_save", 0),
            "applications": activity.get("job_apply", 0),
            "ai_uses": activity.get("ai_use", 0),
            "period_days": days,
        }