"""
Servicio para generar estadísticas y métricas del dashboard
"""

from typing import List, Dict, Optional
from datetime import datetime, timedelta
from sqlalchemy.orm import Session
from sqlalchemy import func, and_, desc

from api.v1.modules.a76.invoices.models import (
    InvoiceHeader,
    InvoiceComplianceMx,
    OperationType,
)
from api.v1.modules.a76.items.models import LineItem
from api.v1.modules.a76.clients_and_providers.models import (
    ClientProvider,
    ClientOrProviderEnum,
)
from api.v1.modules.a76.general_catalogs.company.models import Company
from api.v1.modules.a76.pedmientos.models.pedimentos import Pedimentos

from .dto import (
    DashboardStats,
    KPIMetric,
    ActivityItem,
    ChartDataPoint,
    OperationsOverview,
    InventoryMetrics,
)


class DashboardService:
    """Servicio para generar estadísticas del dashboard"""

    def __init__(self, db: Session, tenant_id: int, company_id: int):
        self.db = db
        self.tenant_id = tenant_id
        self.company_id = company_id

    def _calculate_trend(self, current: int, previous: int) -> tuple[float, str]:
        """Calcula el cambio porcentual y la tendencia"""
        if previous == 0:
            return 0.0, "stable" if current == 0 else "up"

        change = ((current - previous) / previous) * 100

        if abs(change) < 1:
            trend = "stable"
        elif change > 0:
            trend = "up"
        else:
            trend = "down"

        return round(change, 2), trend

    def get_invoice_metrics(self) -> tuple[KPIMetric, List[ChartDataPoint]]:
        """Obtiene métricas de facturas"""
        # Total de facturas actuales
        current_total = (
            self.db.query(func.count(InvoiceHeader.id))
            .filter(
                InvoiceHeader.tenant_id == self.tenant_id,
                InvoiceHeader.company_id == self.company_id,
            )
            .scalar()
            or 0
        )

        # Total del mes anterior para comparación
        last_month = (datetime.utcnow() - timedelta(days=30)).date()
        previous_total = (
            self.db.query(func.count(InvoiceHeader.id))
            .filter(
                InvoiceHeader.tenant_id == self.tenant_id,
                InvoiceHeader.company_id == self.company_id,
                InvoiceHeader.invoice_date < last_month,
            )
            .scalar()
            or 0
        )

        percentage, trend = self._calculate_trend(current_total, previous_total)

        kpi = KPIMetric(
            label="Total Facturas",
            value=current_total,
            previous_value=previous_total,
            percentage_change=percentage,
            trend=trend,
        )

        # Facturas por mes (últimos 6 meses)
        six_months_ago = (datetime.utcnow() - timedelta(days=180)).date()

        monthly_data = (
            self.db.query(
                func.date_trunc("month", InvoiceHeader.invoice_date).label("month"),
                func.count(InvoiceHeader.id).label("count"),
            )
            .filter(
                InvoiceHeader.tenant_id == self.tenant_id,
                InvoiceHeader.company_id == self.company_id,
                InvoiceHeader.invoice_date >= six_months_ago,
            )
            .group_by("month")
            .order_by("month")
            .all()
        )

        chart_data = [
            ChartDataPoint(
                label=row.month.strftime("%b %Y") if row.month else "Unknown",
                value=float(row.count),
            )
            for row in monthly_data
        ]

        return kpi, chart_data

    def get_client_provider_metrics(
        self,
    ) -> tuple[KPIMetric, KPIMetric, List[ChartDataPoint], List[ChartDataPoint]]:
        """Obtiene métricas de clientes y proveedores"""
        # Total clientes
        total_clients = (
            self.db.query(func.count(ClientProvider.id))
            .filter(
                ClientProvider.tenant_id == self.tenant_id,
                ClientProvider.company_id == self.company_id,
                ClientProvider.client_or_provider.in_(
                    [ClientOrProviderEnum.CLIENT, ClientOrProviderEnum.BOTH]
                ),
            )
            .scalar()
            or 0
        )

        # Total proveedores
        total_providers = (
            self.db.query(func.count(ClientProvider.id))
            .filter(
                ClientProvider.tenant_id == self.tenant_id,
                ClientProvider.company_id == self.company_id,
                ClientProvider.client_or_provider.in_(
                    [ClientOrProviderEnum.PROVIDER, ClientOrProviderEnum.BOTH]
                ),
            )
            .scalar()
            or 0
        )

        client_kpi = KPIMetric(
            label="Total Clientes", value=total_clients, trend="stable"
        )

        provider_kpi = KPIMetric(
            label="Total Proveedores", value=total_providers, trend="stable"
        )

        # Top 5 clientes por número de facturas
        # Los clientes están en InvoiceComplianceMx.sold_to_id
        top_clients = (
            self.db.query(
                ClientProvider.name, func.count(InvoiceHeader.id).label("invoice_count")
            )
            .join(
                InvoiceComplianceMx,
                InvoiceComplianceMx.sold_to_id == ClientProvider.id,
                isouter=True,
            )
            .join(
                InvoiceHeader,
                and_(
                    InvoiceHeader.id == InvoiceComplianceMx.invoice_id,
                    InvoiceHeader.tenant_id == self.tenant_id,
                    InvoiceHeader.company_id == self.company_id,
                ),
                isouter=True,
            )
            .filter(
                ClientProvider.tenant_id == self.tenant_id,
                ClientProvider.company_id == self.company_id,
                ClientProvider.client_or_provider.in_(
                    [ClientOrProviderEnum.CLIENT, ClientOrProviderEnum.BOTH]
                ),
            )
            .group_by(ClientProvider.id, ClientProvider.name)
            .order_by(desc("invoice_count"))
            .limit(5)
            .all()
        )

        top_clients_data = [
            ChartDataPoint(
                label=row.name or f"Cliente {i+1}", value=float(row.invoice_count or 0)
            )
            for i, row in enumerate(top_clients)
        ]

        # Top 5 proveedores
        # Los proveedores están en InvoiceComplianceMx.provider_id
        top_providers = (
            self.db.query(
                ClientProvider.name, func.count(InvoiceHeader.id).label("invoice_count")
            )
            .join(
                InvoiceComplianceMx,
                InvoiceComplianceMx.provider_id == ClientProvider.id,
                isouter=True,
            )
            .join(
                InvoiceHeader,
                and_(
                    InvoiceHeader.id == InvoiceComplianceMx.invoice_id,
                    InvoiceHeader.tenant_id == self.tenant_id,
                    InvoiceHeader.company_id == self.company_id,
                ),
                isouter=True,
            )
            .filter(
                ClientProvider.tenant_id == self.tenant_id,
                ClientProvider.company_id == self.company_id,
                ClientProvider.client_or_provider.in_(
                    [ClientOrProviderEnum.PROVIDER, ClientOrProviderEnum.BOTH]
                ),
            )
            .group_by(ClientProvider.id, ClientProvider.name)
            .order_by(desc("invoice_count"))
            .limit(5)
            .all()
        )

        top_providers_data = [
            ChartDataPoint(
                label=row.name or f"Proveedor {i+1}",
                value=float(row.invoice_count or 0),
            )
            for i, row in enumerate(top_providers)
        ]

        return client_kpi, provider_kpi, top_clients_data, top_providers_data

    def get_items_metrics(self) -> KPIMetric:
        """Obtiene métricas de items/productos"""
        total_items = (
            self.db.query(func.count(LineItem.id))
            .filter(
                LineItem.tenant_id == self.tenant_id, LineItem.company_id == self.company_id
            )
            .scalar()
            or 0
        )

        return KPIMetric(label="Total Items", value=total_items, trend="stable")

    def get_operations_by_type(self) -> List[ChartDataPoint]:
        """Obtiene distribución de operaciones por tipo"""
        ops_data = (
            self.db.query(
                InvoiceHeader.operation_type,
                func.count(InvoiceHeader.id).label("count"),
            )
            .filter(
                InvoiceHeader.tenant_id == self.tenant_id,
                InvoiceHeader.company_id == self.company_id,
            )
            .group_by(InvoiceHeader.operation_type)
            .all()
        )

        type_labels = {
            OperationType.IMP: "Importación",
            OperationType.EXP: "Exportación",
            OperationType.SM_IN: "Entrada SM",
            OperationType.SM_OUT: "Salida SM",
            OperationType.CTM_SEND: "Envío CTM",
            OperationType.CTM_RECEIVE: "Recibo CTM",
        }

        return [
            ChartDataPoint(
                label=type_labels.get(row.operation_type, str(row.operation_type)),
                value=float(row.count),
            )
            for row in ops_data
            if row.operation_type
        ]

    def get_recent_activity(self, limit: int = 10) -> List[ActivityItem]:
        """Obtiene actividad reciente del sistema"""
        activities = []

        # Facturas recientes
        recent_invoices = (
            self.db.query(InvoiceHeader)
            .filter(
                InvoiceHeader.tenant_id == self.tenant_id,
                InvoiceHeader.company_id == self.company_id,
            )
            .order_by(desc(InvoiceHeader.created_at))
            .limit(limit)
            .all()
        )

        for invoice in recent_invoices:
            activities.append(
                ActivityItem(
                    id=invoice.id,
                    type="invoice",
                    title=f"Factura {invoice.invoice_number or invoice.id}",
                    description=f"Operación: {invoice.operation_type if invoice.operation_type else 'N/A'}",
                    timestamp=invoice.created_at,
                    status="active",
                    icon="FileText",
                )
            )

        # Ordenar por timestamp
        activities.sort(key=lambda x: x.timestamp, reverse=True)

        return activities[:limit]

    def get_pedimentos_metrics(self) -> tuple[KPIMetric, List[ChartDataPoint]]:
        """Obtiene métricas de pedimentos"""
        # Total de pedimentos actuales
        current_total = (
            self.db.query(func.count(Pedimentos.id))
            .filter(
                Pedimentos.tenant_id == self.tenant_id,
                Pedimentos.company_id == self.company_id,
            )
            .scalar()
            or 0
        )

        # Total del mes anterior para comparación
        last_month = datetime.utcnow() - timedelta(days=30)
        previous_total = (
            self.db.query(func.count(Pedimentos.id))
            .filter(
                Pedimentos.tenant_id == self.tenant_id,
                Pedimentos.company_id == self.company_id,
                Pedimentos.created_at < last_month,
            )
            .scalar()
            or 0
        )

        percentage, trend = self._calculate_trend(current_total, previous_total)

        kpi = KPIMetric(
            label="Total Pedimentos",
            value=current_total,
            previous_value=previous_total,
            percentage_change=percentage,
            trend=trend,
        )

        # Pedimentos por mes (últimos 6 meses)
        six_months_ago = datetime.utcnow() - timedelta(days=180)

        monthly_data = (
            self.db.query(
                func.date_trunc("month", Pedimentos.created_at).label("month"),
                func.count(Pedimentos.id).label("count"),
            )
            .filter(
                Pedimentos.tenant_id == self.tenant_id,
                Pedimentos.company_id == self.company_id,
                Pedimentos.created_at >= six_months_ago,
            )
            .group_by("month")
            .order_by("month")
            .all()
        )

        chart_data = [
            ChartDataPoint(
                label=row.month.strftime("%b %Y") if row.month else "Unknown",
                value=float(row.count),
            )
            for row in monthly_data
        ]

        return kpi, chart_data

    def get_complete_dashboard_stats(self) -> DashboardStats:
        """Genera estadísticas completas para el dashboard"""

        # Obtener nombre de la compañía
        company = (
            self.db.query(Company)
            .filter(Company.id == self.company_id, Company.tenant_id == self.tenant_id)
            .first()
        )

        # Obtener todas las métricas
        invoice_kpi, invoices_chart = self.get_invoice_metrics()
        client_kpi, provider_kpi, top_clients, top_providers = (
            self.get_client_provider_metrics()
        )
        items_kpi = self.get_items_metrics()
        operations_chart = self.get_operations_by_type()
        recent_activity = self.get_recent_activity()

        # Obtener métricas de pedimentos
        pedimento_kpi, pedimentos_chart = self.get_pedimentos_metrics()

        # KPI de aprobaciones pendientes
        pending_kpi = KPIMetric(label="Pendientes", value=0, trend="stable")

        return DashboardStats(
            total_invoices=invoice_kpi,
            total_pedimentos=pedimento_kpi,
            total_clients=client_kpi,
            total_providers=provider_kpi,
            active_items=items_kpi,
            pending_approvals=pending_kpi,
            invoices_by_month=invoices_chart,
            pedimentos_by_month=pedimentos_chart,
            operations_by_type=operations_chart,
            top_clients=top_clients,
            top_providers=top_providers,
            recent_activity=recent_activity,
            company_id=self.company_id,
            company_name=company.name if company else None,
        )
