from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from datetime import datetime, timedelta
from app.models.chat_history import ChatHistory
from app.models.feedback import Feedback
from app.models.user_session import UserSession


class AnalyticsService:
    def summary(self, db: Session):
        total_chats = db.query(func.count(ChatHistory.id)).scalar() or 0

        today = datetime.utcnow().date()
        today_start = datetime.combine(today, datetime.min.time())
        todays_chats = db.query(func.count(ChatHistory.id)).filter(ChatHistory.created_at >= today_start).scalar() or 0

        active_users = db.query(func.count(UserSession.id)).scalar() or 0

        top_category_row = (
            db.query(ChatHistory.detected_category, func.count(ChatHistory.id).label("cnt"))
            .filter(ChatHistory.detected_category.isnot(None))
            .group_by(ChatHistory.detected_category)
            .order_by(desc("cnt"))
            .first()
        )
        top_category = top_category_row[0] if top_category_row else "-"

        resolved_count = (
            db.query(func.count(Feedback.id))
            .filter(Feedback.feedback_type == "Resolved")
            .scalar() or 0
        )

        partial_count = (
            db.query(func.count(Feedback.id))
            .filter(Feedback.feedback_type == "Partially Resolved")
            .scalar() or 0
        )

        unresolved_count = (
            db.query(func.count(Feedback.id))
            .filter(Feedback.feedback_type == "Not Resolved")
            .scalar() or 0
        )

        return {
            "total_chats": total_chats,
            "todays_chats": todays_chats,
            "active_users": active_users,
            "top_category": top_category,
            "resolved_count": resolved_count,
            "partial_count": partial_count,
            "unresolved_count": unresolved_count
        }

    def category_breakdown(self, db: Session):
        rows = (
            db.query(ChatHistory.detected_category, func.count(ChatHistory.id).label("cnt"))
            .group_by(ChatHistory.detected_category)
            .order_by(desc("cnt"))
            .all()
        )
        return [{"category": r[0] or "General", "count": r[1]} for r in rows]

    def top_issues(self, db: Session):
        rows = (
            db.query(ChatHistory.issue_title, func.count(ChatHistory.id).label("cnt"))
            .filter(ChatHistory.issue_title.isnot(None))
            .group_by(ChatHistory.issue_title)
            .order_by(desc("cnt"))
            .limit(10)
            .all()
        )
        return [{"issue_title": r[0], "count": r[1]} for r in rows]

    def recent_chats(self, db: Session):
        rows = (
            db.query(ChatHistory)
            .order_by(ChatHistory.id.desc())
            .limit(20)
            .all()
        )
        return [
            {
                "id": r.id,
                "user_name": r.user_name,
                "department": r.department,
                "device_name": r.device_name,
                "user_message": r.user_message,
                "issue_title": r.issue_title,
                "detected_category": r.detected_category,
                "created_at": r.created_at.strftime("%Y-%m-%d %H:%M:%S")
            }
            for r in rows
        ]

    def daily_trend(self, db: Session, days: int = 7):
        start_date = datetime.utcnow() - timedelta(days=days - 1)
        rows = (
            db.query(
                func.date(ChatHistory.created_at).label("dt"),
                func.count(ChatHistory.id).label("cnt")
            )
            .filter(ChatHistory.created_at >= start_date)
            .group_by(func.date(ChatHistory.created_at))
            .order_by(func.date(ChatHistory.created_at))
            .all()
        )
        return [{"date": str(r[0]), "count": r[1]} for r in rows]