import asyncio
import uuid
from datetime import datetime, timedelta, timezone
from sqlalchemy import case, func, select
from fastapi import HTTPException
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.calendrier import Calendrier
from app.models.poste import Poste
from app.models.discussion import Discussion
from app.models.invitation import Invitation
from app.models.signalement import Signalement
from app.models.utilisateur import Utilisateur


class StatsService:
    def __init__(self, db: AsyncSession) -> None:
        self.db = db

    @staticmethod
    def _normalize_range(start_date: datetime, end_date: datetime) -> tuple[datetime, datetime]:
        if start_date.tzinfo is None:
            start_date = start_date.replace(tzinfo=timezone.utc)
        if end_date.tzinfo is None:
            end_date = end_date.replace(tzinfo=timezone.utc)
        # Include full end day
        end_date = end_date.replace(hour=23, minute=59, second=59)
        return start_date, end_date

    @staticmethod
    def _week_start_from_datetime(value: datetime) -> datetime:
        return value - timedelta(days=value.weekday())

    @staticmethod
    def _build_week_keys(start_date: datetime, end_date: datetime) -> list[str]:
        start_week = StatsService._week_start_from_datetime(start_date)
        end_week = StatsService._week_start_from_datetime(end_date)
        keys: list[str] = []
        current = start_week
        while current <= end_week:
            keys.append(f"Week_{current.date().isoformat()}")
            current = current + timedelta(days=7)
        return keys

    @staticmethod
    def _serialize_alert(signalement: Signalement) -> dict:
        severity = "critical" if signalement.statut == "NOUVEAU" else "warning"
        return {
            "id": str(signalement.id),
            "titre": signalement.titre,
            "type": signalement.type,
            "statut": signalement.statut,
            "dateCreation": signalement.date_creation.isoformat() if signalement.date_creation else None,
            "priorite": "HAUTE" if signalement.statut == "NOUVEAU" else "MOYENNE",
            "title": signalement.titre,
            "message": signalement.description,
            "severity": severity,
            "createdAt": signalement.date_creation.isoformat() if signalement.date_creation else None,
            "isDismissed": False,
        }

    @staticmethod
    def _serialize_recent_case(signalement: Signalement) -> dict:
        return {
            "id": str(signalement.id),
            "titre": signalement.titre,
            "type": signalement.type,
            "statut": signalement.statut,
            "dateCreation": signalement.date_creation.isoformat() if signalement.date_creation else None,
        }

    async def get_dashboard(self, current_user: Utilisateur | None = None) -> dict:
        now = datetime.now(timezone.utc)

        sig_stmt = select(
            func.count(Signalement.id).label("total_s"),
            func.count(case((Signalement.statut == "NOUVEAU", 1))).label("nouveaux"),
            func.count(case((Signalement.statut.in_(["ASSIGNE", "EN_COURS", "EN_ATTENTE"]), 1))).label("en_cours"),
            func.count(case((Signalement.statut == "TRAITE", 1))).label("traites"),
            func.count(case((Signalement.statut == "CLOTURE", 1))).label("clotures"),
            func.count(case((Signalement.statut.in_(["NOUVEAU", "EN_ATTENTE"]), 1))).label("urgent_cases"),
        )
        user_stmt = select(func.count(Utilisateur.id)).where(Utilisateur.est_actif == True)
        cal_stmt = select(
            func.count(case((Calendrier.statut != "ANNULE", 1))).label("total_events"),
            func.count(case(((Calendrier.date_debut > now) & (Calendrier.statut != "ANNULE"), 1))).label("upcoming_events"),
        )
        disc_stmt = select(func.count(Discussion.id))
        poste_stmt = select(func.count(Poste.id)).where(Poste.statut.in_(["NOUVEAU", "REJETER_IA"]))

        sig_res, user_res, cal_res, disc_res, poste_res = await asyncio.gather(
            self.db.execute(sig_stmt),
            self.db.execute(user_stmt),
            self.db.execute(cal_stmt),
            self.db.execute(disc_stmt),
            self.db.execute(poste_stmt),
        )

        sig_row = sig_res.one()
        total_s = sig_row.total_s or 0
        nouveaux = sig_row.nouveaux or 0
        en_cours = sig_row.en_cours or 0
        traites = sig_row.traites or 0
        clotures = sig_row.clotures or 0
        urgent_cases = sig_row.urgent_cases or 0

        total_users = user_res.scalar_one() or 0
        active_users = total_users

        cal_row = cal_res.one()
        total_events = cal_row.total_events or 0
        upcoming_events = cal_row.upcoming_events or 0

        total_messages = disc_res.scalar_one() or 0
        pending_moderation = poste_res.scalar_one() or 0
        team_alerts = urgent_cases
        avg_resolution_days = 0.0

        taux = round((traites + clotures) / total_s * 100, 2) if total_s > 0 else 0.0

        user_name = ""
        unread_count = 0
        if current_user is not None:
            user_name = f"{(current_user.prenom or '').strip()} {(current_user.nom or '').strip()}".strip()
            if current_user.type_utilisateur in ("GESTIONNAIRE", "ADMIN_SYSTEME"):
                unread_stmt = select(func.coalesce(func.sum(Discussion.non_lus_gestionnaire), 0))
            else:
                unread_stmt = select(func.coalesce(func.sum(Discussion.non_lus_utilisateur), 0)).where(
                    Discussion.utilisateur_id == current_user.id
                )
            unread_count = (await self.db.execute(unread_stmt)).scalar_one()

        return {
            "userName": user_name or "Utilisateur",
            "totalSignalements": total_s,
            "signalementsNouveaux": nouveaux,
            "signalementsEnCours": en_cours,
            "signalementsTraites": traites,
            "signalementsCloturesAutres": clotures,
            "totalUtilisateurs": total_users,
            "activeUsers": active_users,
            "totalEvenements": total_events,
            "totalMessages": total_messages,
            "tauxTraitement": taux,
            "signalementsEnAttente": nouveaux,
            "evenementsAVenir": upcoming_events,
            "equipeAlertes": team_alerts,
            "moderationEnAttente": pending_moderation,
            "urgentCases": urgent_cases,
            "pendingTestimonials": pending_moderation,
            "averageResolutionDays": avg_resolution_days,
            "unreadMessagesCount": int(unread_count or 0),
            "pendingReportsCount": nouveaux,
            "upcomingEventsCount": upcoming_events,
            "announcements": [],
            "pendingCasesCount": nouveaux,
            "casesCount": nouveaux,
            "eventsCount": upcoming_events,
            "teamAlertsCount": team_alerts,
            "teamPendingCount": team_alerts,
            "pendingModerationCount": pending_moderation,
            "moderationCount": pending_moderation,
        }

    async def get_dashboard_for_user(self, user_id: uuid.UUID | str) -> dict:
        user_uuid = user_id if isinstance(user_id, uuid.UUID) else uuid.UUID(str(user_id))
        base_scope = Signalement.utilisateur_id == user_uuid

        total_s = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope))).scalar_one()
        nouveaux = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.statut == "NOUVEAU"))).scalar_one()
        en_cours = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.statut.in_(["ASSIGNE", "EN_COURS", "EN_ATTENTE"])))).scalar_one()
        traites = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.statut == "TRAITE"))).scalar_one()
        clotures = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.statut == "CLOTURE"))).scalar_one()
        total_messages = (await self.db.execute(select(func.count(Discussion.id)).where(Discussion.utilisateur_id == user_uuid))).scalar_one()
        unread_messages = (
            await self.db.execute(
                select(func.coalesce(func.sum(Discussion.non_lus_utilisateur), 0)).where(Discussion.utilisateur_id == user_uuid)
            )
        ).scalar_one()
        upcoming_events = (
            await self.db.execute(
                select(func.count(Calendrier.id)).where(
                    Calendrier.date_debut > datetime.now(timezone.utc),
                    Calendrier.statut != "ANNULE",
                )
            )
        ).scalar_one()

        user = (
            await self.db.execute(select(Utilisateur).where(Utilisateur.id == user_uuid))
        ).scalar_one_or_none()
        user_name = ""
        if user is not None:
            user_name = f"{(user.prenom or '').strip()} {(user.nom or '').strip()}".strip()

        taux = round((traites + clotures) / total_s * 100, 2) if total_s > 0 else 0.0

        return {
            "userName": user_name or "Utilisateur",
            "totalSignalements": total_s,
            "signalementsNouveaux": nouveaux,
            "signalementsEnCours": en_cours,
            "signalementsTraites": traites,
            "signalementsCloturesAutres": clotures,
            "totalUtilisateurs": 1,
            "activeUsers": 1,
            "totalEvenements": upcoming_events,
            "totalMessages": total_messages,
            "tauxTraitement": taux,
            "signalementsEnAttente": nouveaux,
            "evenementsAVenir": upcoming_events,
            "equipeAlertes": 0,
            "moderationEnAttente": 0,
            "urgentCases": nouveaux,
            "pendingTestimonials": 0,
            "averageResolutionDays": 0.0,
            "unreadMessagesCount": int(unread_messages or 0),
            "pendingReportsCount": nouveaux,
            "upcomingEventsCount": upcoming_events,
            "announcements": [],
            "pendingCasesCount": nouveaux,
            "casesCount": nouveaux,
            "eventsCount": upcoming_events,
            "teamAlertsCount": 0,
            "teamPendingCount": 0,
            "pendingModerationCount": 0,
            "moderationCount": 0,
        }

    async def get_signalements_report(self) -> dict:
        total = (await self.db.execute(select(func.count(Signalement.id)))).scalar_one()

        # By statut
        statuts = ["NOUVEAU", "ASSIGNE", "EN_COURS", "EN_ATTENTE", "TRAITE", "CLOTURE"]
        par_statut: dict[str, int] = {}
        for s in statuts:
            count = (await self.db.execute(select(func.count(Signalement.id)).where(Signalement.statut == s))).scalar_one()
            par_statut[s] = count

        # By type
        types = ["HARCELEMENT_MORAL", "HARCELEMENT_SEXUEL", "VBG", "DISCRIMINATION", "VIOLENCE", "AUTRE"]
        par_type = []
        for t in types:
            count = (await self.db.execute(select(func.count(Signalement.id)).where(Signalement.type == t))).scalar_one()
            pourcentage = round(count / total * 100, 1) if total > 0 else 0.0
            par_type.append({"type": t, "count": count, "pourcentage": pourcentage})

        return {
            "total": total,
            "parStatut": par_statut,
            "parType": par_type,
            "evolution": [],  # Requires date bucketing — extend as needed
        }

    async def get_signalements_report_for_user(self, user_id) -> dict:
        base_scope = Signalement.utilisateur_id == user_id
        total = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope))).scalar_one()

        statuts = ["NOUVEAU", "ASSIGNE", "EN_COURS", "EN_ATTENTE", "TRAITE", "CLOTURE"]
        par_statut: dict[str, int] = {}
        for s in statuts:
            count = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.statut == s))).scalar_one()
            par_statut[s] = count

        types = ["HARCELEMENT_MORAL", "HARCELEMENT_SEXUEL", "VBG", "DISCRIMINATION", "VIOLENCE", "AUTRE"]
        par_type = []
        for t in types:
            count = (await self.db.execute(select(func.count(Signalement.id)).where(base_scope, Signalement.type == t))).scalar_one()
            pourcentage = round(count / total * 100, 1) if total > 0 else 0.0
            par_type.append({"type": t, "count": count, "pourcentage": pourcentage})

        return {
            "total": total,
            "parStatut": par_statut,
            "parType": par_type,
            "evolution": [],
        }

    async def get_team_stats(self) -> dict:
        """Get team members list plus summary for dashboard widgets."""
        total_users = (
            await self.db.execute(
                select(func.count(Utilisateur.id)).where(Utilisateur.est_actif == True)
            )
        ).scalar_one()
        gestionnaires = (
            await self.db.execute(
                select(func.count(Utilisateur.id)).where(
                    Utilisateur.type_utilisateur.in_(["GESTIONNAIRE", "ADMIN_SYSTEME"]),
                    Utilisateur.est_actif == True,
                )
            )
        ).scalar_one()
        utilisateurs = (
            await self.db.execute(
                select(func.count(Utilisateur.id)).where(
                    Utilisateur.type_utilisateur == "UTILISATEUR",
                    Utilisateur.est_actif == True,
                )
            )
        ).scalar_one()

        members_stmt = (
            select(Utilisateur)
            .where(
                Utilisateur.est_actif == True,
                Utilisateur.type_utilisateur.in_(["GESTIONNAIRE", "ADMIN_SYSTEME"]),
            )
            .order_by(Utilisateur.nom.asc(), Utilisateur.prenom.asc())
        )
        members = (await self.db.execute(members_stmt)).scalars().all()

        now_utc = datetime.now(timezone.utc)
        online_threshold = now_utc - timedelta(minutes=15)
        team_list = []

        for member in members:
            assigned_count = (
                await self.db.execute(
                    select(func.count(Signalement.id)).where(
                        Signalement.gestionnaire_id == member.id
                    )
                )
            ).scalar_one()
            treated_count = (
                await self.db.execute(
                    select(func.count(Signalement.id)).where(
                        Signalement.gestionnaire_id == member.id,
                        Signalement.statut.in_(["TRAITE", "CLOTURE"]),
                    )
                )
            ).scalar_one()

            response_rows = (
                await self.db.execute(
                    select(Signalement.date_creation, Signalement.date_assignation).where(
                        Signalement.gestionnaire_id == member.id,
                        Signalement.date_creation.is_not(None),
                        Signalement.date_assignation.is_not(None),
                    )
                )
            ).all()
            if response_rows:
                durations = [
                    (date_assignation - date_creation).total_seconds() / 3600
                    for date_creation, date_assignation in response_rows
                    if date_creation is not None and date_assignation is not None
                ]
                avg_response_hours = (sum(durations) / len(durations)) if durations else None
            else:
                avg_response_hours = None

            last_activity = member.derniere_connexion
            if last_activity is not None and last_activity.tzinfo is None:
                last_activity = last_activity.replace(tzinfo=timezone.utc)
            en_ligne = bool(last_activity and last_activity >= online_threshold)

            taux_traitement = round((treated_count / assigned_count) * 100, 1) if assigned_count > 0 else 0.0
            full_name = f"{member.prenom} {member.nom}".strip()

            team_list.append(
                {
                    "id": str(member.id),
                    "nom": full_name,
                    "typeUtilisateur": member.type_utilisateur,
                    "signalementsAssignes": int(assigned_count),
                    "signalementsTraites": int(treated_count),
                    "tauxTraitement": taux_traitement,
                    "tempsReponseMoyenHeures": float(round(avg_response_hours, 1)) if avg_response_hours is not None else None,
                    "enLigne": en_ligne,
                    "derniereActivite": last_activity.isoformat() if last_activity else None,
                    "photoUrl": member.avatar_url,
                }
            )

        resume = {
            "total": int(total_users),
            "gestionnaires": int(gestionnaires),
            "utilisateurs": int(utilisateurs),
            "actifs": int(total_users),
        }

        return {
            "team": team_list,
            "teamStats": team_list,
            "equipe": team_list,
            "resume": resume,
        }

    async def get_alerts(self, limit: int = 10) -> dict:
        """Get critical/alert cases."""
        # Get newest critical cases (NOUVEAU, EN_COURS statuts priority)
        stmt = select(Signalement).where(
            Signalement.statut.in_(["NOUVEAU", "EN_ATTENTE"])
        ).order_by(Signalement.date_creation.desc()).limit(limit)
        result = await self.db.execute(stmt)
        signalements = result.scalars().all()
        
        alerts = [self._serialize_alert(s) for s in signalements]
        return {"alertes": alerts, "total": len(alerts)}

    async def get_alerts_for_user(self, user_id, limit: int = 10) -> dict:
        stmt = (
            select(Signalement)
            .where(
                Signalement.utilisateur_id == user_id,
                Signalement.statut.in_(["NOUVEAU", "EN_ATTENTE"]),
            )
            .order_by(Signalement.date_creation.desc())
            .limit(limit)
        )
        result = await self.db.execute(stmt)
        signalements = result.scalars().all()

        alerts = [self._serialize_alert(s) for s in signalements]
        return {"alertes": alerts, "total": len(alerts)}

    async def get_recent_cases(self, limit: int = 5) -> dict:
        """Get recent cases."""
        stmt = select(Signalement).order_by(Signalement.date_creation.desc()).limit(limit)
        result = await self.db.execute(stmt)
        cases = result.scalars().all()
        
        recent = [self._serialize_recent_case(c) for c in cases]
        return {"dossiers": recent, "total": len(recent)}

    async def get_recent_cases_for_user(self, user_id, limit: int = 5) -> dict:
        stmt = (
            select(Signalement)
            .where(Signalement.utilisateur_id == user_id)
            .order_by(Signalement.date_creation.desc())
            .limit(limit)
        )
        result = await self.db.execute(stmt)
        cases = result.scalars().all()

        recent = [self._serialize_recent_case(c) for c in cases]
        return {"dossiers": recent, "total": len(recent)}

    async def get_category_distribution(self) -> dict:
        """Get distribution of cases by category."""
        types = ["HARCELEMENT_MORAL", "HARCELEMENT_SEXUEL", "VBG", "DISCRIMINATION", "VIOLENCE", "AUTRE"]
        distribution = []
        total = (await self.db.execute(select(func.count(Signalement.id)))).scalar_one()
        
        for t in types:
            count = (await self.db.execute(select(func.count(Signalement.id)).where(Signalement.type == t))).scalar_one()
            pourcentage = round(count / total * 100, 1) if total > 0 else 0.0
            distribution.append({
                "categorie": t,
                "nombre": count,
                "pourcentage": pourcentage,
            })
        
        return {"distribution": distribution}

    async def get_cases_trends(self, start_date: datetime, end_date: datetime, gestionnaire_id: str | None = None) -> dict:
        """Get case creation trends by week.
        
        Normalizes dates to UTC and aggregates by ISO week.
        """
        start_date, end_date = self._normalize_range(start_date, end_date)
        
        stmt = select(Signalement).where(
            Signalement.date_creation >= start_date,
            Signalement.date_creation <= end_date,
        )
        if gestionnaire_id:
            stmt = stmt.where(Signalement.gestionnaire_id == gestionnaire_id)
            
        stmt = stmt.order_by(Signalement.date_creation.asc())
        result = await self.db.execute(stmt)
        cases = result.scalars().all()
        
        # Aggregate by week and pre-fill requested weeks with zero.
        trends_by_week = {week_key: 0 for week_key in self._build_week_keys(start_date, end_date)}
        for case in cases:
            if case.date_creation:
                # Get ISO week start date (Monday)
                date = case.date_creation.date() if hasattr(case.date_creation, 'date') else case.date_creation
                week_start = date - timedelta(days=date.weekday())  # Monday of this week
                week_key = f"Week_{week_start.isoformat()}"
                trends_by_week[week_key] = trends_by_week.get(week_key, 0) + 1
        
        return {
            "tendances": [
                {"semaine": week, "nombre": count}
                for week, count in sorted(trends_by_week.items())
            ]
        }

    async def get_resolution_trends(self, start_date: datetime, end_date: datetime, gestionnaire_id: str | None = None) -> dict:
        """Get resolution trends by week.
        
        Shows resolved cases (TRAITE, CLOTURE) vs total cases by week.
        """
        start_date, end_date = self._normalize_range(start_date, end_date)
        
        stmt = select(Signalement).where(
            Signalement.date_creation >= start_date,
            Signalement.date_creation <= end_date,
        )
        if gestionnaire_id:
            stmt = stmt.where(Signalement.gestionnaire_id == gestionnaire_id)
            
        stmt = stmt.order_by(Signalement.date_creation.asc())
        result = await self.db.execute(stmt)
        cases = result.scalars().all()
        
        # Aggregate by week with resolution status
        trends_by_week = {
            week_key: {"total": 0, "resolus": 0, "en_cours": 0, "nouveau": 0}
            for week_key in self._build_week_keys(start_date, end_date)
        }
        for case in cases:
            if case.date_creation:
                date = case.date_creation.date() if hasattr(case.date_creation, 'date') else case.date_creation
                week_start = date - timedelta(days=date.weekday())
                week_key = f"Week_{week_start.isoformat()}"
                
                if week_key not in trends_by_week:
                    trends_by_week[week_key] = {"total": 0, "resolus": 0, "en_cours": 0, "nouveau": 0}
                
                trends_by_week[week_key]["total"] += 1
                if case.statut in ("TRAITE", "CLOTURE"):
                    trends_by_week[week_key]["resolus"] += 1
                elif case.statut in ("EN_COURS", "ASSIGNE", "EN_ATTENTE"):
                    trends_by_week[week_key]["en_cours"] += 1
                elif case.statut == "NOUVEAU":
                    trends_by_week[week_key]["nouveau"] += 1
        
        # Calculate resolution rate per week
        result_list = []
        for week, stats in sorted(trends_by_week.items()):
            taux = round(stats["resolus"] / stats["total"] * 100, 1) if stats["total"] > 0 else 0.0
            result_list.append({
                "semaine": week,
                "total": stats["total"],
                "resolus": stats["resolus"],
                "en_cours": stats["en_cours"],
                "nouveau": stats["nouveau"],
                "tauxResolution": taux,
            })
        
        return {"tendances": result_list}

    async def get_quick_actions(self, role: str = None) -> dict:
        """Get quick action links/stats based on role."""
        role_value = (role or "GESTIONNAIRE").upper()
        base_prefix = "/admin-systeme" if role_value == "ADMIN_SYSTEME" else "/gestionnaire"
        actions = [
            {
                "id": "view_alerts",
                "label": "Voir les alertes",
                "iconName": "alert-circle",
                "route": f"{base_prefix}/dashboard/alerts",
                "badge": (await self.db.execute(select(func.count(Signalement.id)).where(Signalement.statut.in_(["NOUVEAU", "EN_ATTENTE"])))).scalar_one(),
                "isEnabled": True,
            },
            {
                "id": "user-profile",
                "label": "Mon profil",
                "iconName": "user",
                "route": f"{base_prefix}/profil",
                "badge": 0,
                "isEnabled": True,
            },
            {
                "id": "pending_review",
                "label": "Moderation en attente",
                "iconName": "clock",
                "route": f"{base_prefix}/moderation",
                "badge": (await self.db.execute(select(func.count(Poste.id)).where(Poste.statut.in_(["NOUVEAU", "REJETER_IA"])))).scalar_one(),
                "isEnabled": True,
            },
        ]

        actions.append(
            {
                "id": "export_data",
                "label": "Exporter les donnees",
                "iconName": "download",
                "route": f"{base_prefix}/exports",
                "badge": 0,
                "isEnabled": role_value in ("ADMIN_SYSTEME", "GESTIONNAIRE"),
            }
        )

        return {"actions": actions}

    async def dismiss_alert(self, signalement_id: str | uuid.UUID) -> dict:
        signalement_uuid = signalement_id if isinstance(signalement_id, uuid.UUID) else uuid.UUID(signalement_id)
        signalement = (
            await self.db.execute(select(Signalement).where(Signalement.id == signalement_uuid))
        ).scalar_one_or_none()
        if signalement is None:
            raise HTTPException(status_code=404, detail="Alerte non trouvee")

        if signalement.statut == "NOUVEAU":
            signalement.statut = "ASSIGNE"
        elif signalement.statut == "EN_ATTENTE":
            signalement.statut = "EN_COURS"

        await self.db.commit()
        return {"id": str(signalement.id), "dismissed": True}

    async def get_pending_invitations_stats(self) -> dict:
        now = datetime.now(timezone.utc)
        pending = (
            await self.db.execute(
                select(func.count(Invitation.id)).where(
                    Invitation.statut == "PENDING",
                    Invitation.date_expiration > now,
                )
            )
        ).scalar_one()
        return {
            "invitationsEnAttente": int(pending),
            "pendingInvitations": int(pending),
        }