from django.views.generic.base import TemplateView
from django.contrib.auth.mixins import LoginRequiredMixin
from pxy_whatsapp.views import whatsapp_stats
import json
import logging
from django.contrib.auth.decorators import login_required
from pxy_whatsapp.models import WhatsAppBot
from django.shortcuts import render
from django.utils import timezone
from datetime import timedelta
import json
from pxy_whatsapp.models import Message, WhatsAppBot
from datetime import timedelta
from django.db.models import Count

logger = logging.getLogger(__name__)


class AppsView(LoginRequiredMixin, TemplateView):
    pass

# ───── Existing ──────────────────────────────────────────────────────────────

# Calendar
apps_calendar_view = AppsView.as_view(template_name="pxy_dashboard/apps/apps-calendar.html")
# Chat
apps_chat_view = AppsView.as_view(template_name="pxy_dashboard/apps/apps-chat.html")
# Email
apps_email_inbox_view = AppsView.as_view(template_name="pxy_dashboard/apps/apps-email-inbox.html")
apps_email_read = AppsView.as_view(template_name="pxy_dashboard/apps/apps-email-read.html")
# Tasks
apps_tasks = AppsView.as_view(template_name="pxy_dashboard/apps/apps-tasks.html")
apps_tasks_details = AppsView.as_view(template_name="pxy_dashboard/apps/apps-tasks-details.html")
# Kanban
apps_kanban_board = AppsView.as_view(template_name="pxy_dashboard/apps/apps-kanban.html")
# File Manager
apps_file_manager = AppsView.as_view(template_name="pxy_dashboard/apps/apps-file-manager.html")

# ───── Waste Collection Intelligence ─────────────────────────────────────────

# Pre-Operation
#apps_zone_definition = AppsView.as_view(template_name="pxy_dashboard/apps/apps-zone-definition.html")
#apps_route_optimization = AppsView.as_view(template_name="pxy_dashboard/apps/apps-route-optimization.html")
apps_dispatch_plan = AppsView.as_view(template_name="pxy_dashboard/apps/apps-dispatch-plan.html")

# Operation – Physical & Social Digital Twin
apps_urban_digital_twin = AppsView.as_view(template_name="pxy_dashboard/apps/apps-urban-digital-twin.html")
#apps_whatsapp_bot = AppsView.as_view(template_name="pxy_dashboard/apps/apps-whatsapp-bot.html")
apps_telegram_bot = AppsView.as_view(template_name="pxy_dashboard/apps/apps-telegram-bot.html")
apps_facebook_pages_bot = AppsView.as_view(template_name="pxy_dashboard/apps/apps-facebook-pages-bot.html")
apps_feedback_loop = AppsView.as_view(template_name="pxy_dashboard/apps/apps-feedback-loop.html")

# Post-Operation
apps_route_analytics = AppsView.as_view(template_name="pxy_dashboard/apps/apps-route-analytics.html")
apps_feedback_review = AppsView.as_view(template_name="pxy_dashboard/apps/apps-feedback-review.html")
apps_twin_refinement = AppsView.as_view(template_name="pxy_dashboard/apps/apps-twin-refinement.html")

# System Control
apps_sync_monitor = AppsView.as_view(template_name="pxy_dashboard/apps/apps-sync-monitor.html")
apps_logs_webhooks = AppsView.as_view(template_name="pxy_dashboard/apps/apps-logs-webhooks.html")
apps_logs_parsing = AppsView.as_view(template_name="pxy_dashboard/apps/apps-logs-parsing.html")
apps_logs_limits = AppsView.as_view(template_name="pxy_dashboard/apps/apps-logs-limits.html")
apps_config_api = AppsView.as_view(template_name="pxy_dashboard/apps/apps-config-api.html")
apps_config_map = AppsView.as_view(template_name="pxy_dashboard/apps/apps-config-map.html")
apps_config_collection = AppsView.as_view(template_name="pxy_dashboard/apps/apps-config-collection.html")

# Sites (new)
apps_sites_runs   = AppsView.as_view(template_name="pxy_dashboard/apps/apps-sites-runs.html")
apps_sites_viewer = AppsView.as_view(template_name="pxy_dashboard/apps/apps-sites-viewer.html")

apps_sami_explorer = AppsView.as_view(template_name="pxy_dashboard/apps/apps-sami-explorer.html")

from django.shortcuts import render
from .models import GeoScenario
import pandas as pd

def zone_definition_view(request):
    scenario = GeoScenario.objects.last()
    chart_data = {}
    viviendas_data = {}
    city_options = []
    scatter_series = {}

    if scenario and scenario.csv_file:
        df = pd.read_csv(scenario.csv_file.path)
        df = df.fillna(0)

        # Solo conservar zonas con generación total > 0
        df = df[df["GEN_TOT"] > 0]

        # Opciones de ciudad
        city_options = sorted(df["N_URBANO"].dropna().unique())
        selected_city = request.GET.get("city") or (city_options[0] if city_options else None)

        # Barras por zona (residuos)
        if selected_city:
            city_df = df[df["N_URBANO"] == selected_city]
            grouped = (
                city_df.groupby("COD_ZONA")[["GEN_ORG", "GEN_INVA", "GEN_RESTO"]]
                .sum()
                .reset_index()
                .sort_values(by="GEN_ORG", ascending=False)
            )
            chart_data = {
                "zones": grouped["COD_ZONA"].astype(str).tolist(),
                "gen_org": grouped["GEN_ORG"].round(2).tolist(),
                "gen_inva": grouped["GEN_INVA"].round(2).tolist(),
                "gen_resto": grouped["GEN_RESTO"].round(2).tolist(),
            }

            # Barras por zona (viviendas)
            viviendas_grouped = (
                city_df.groupby("COD_ZONA")["num_viviendas"]
                .sum()
                .reset_index()
                .sort_values(by="num_viviendas", ascending=False)
            )
            viviendas_data = {
                "zones": viviendas_grouped["COD_ZONA"].astype(str).tolist(),
                "viviendas": viviendas_grouped["num_viviendas"].astype(int).tolist(),
            }

        # Dispersión por ciudad
        scatter_series = {
            "GEN_ORG": [],
            "GEN_INVA": [],
            "GEN_RESTO": [],
        }

        city_grouped = (
            df.groupby("N_URBANO")[["num_viviendas", "GEN_ORG", "GEN_INVA", "GEN_RESTO"]]
            .sum()
            .reset_index()
        )

        for _, row in city_grouped.iterrows():
            viviendas = float(row["num_viviendas"])
            if viviendas == 0:
                continue
            city = row["N_URBANO"]
            if row["GEN_ORG"] > 0:
                scatter_series["GEN_ORG"].append({"x": viviendas, "y": float(row["GEN_ORG"]), "city": city})
            if row["GEN_INVA"] > 0:
                scatter_series["GEN_INVA"].append({"x": viviendas, "y": float(row["GEN_INVA"]), "city": city})
            if row["GEN_RESTO"] > 0:
                scatter_series["GEN_RESTO"].append({"x": viviendas, "y": float(row["GEN_RESTO"]), "city": city})

    return render(request, "pxy_dashboard/apps/apps-zone-definition.html", {
        "chart_data": chart_data,
        "viviendas_data": viviendas_data,
        "scatter_series": scatter_series,
        "cities": city_options,
        "selected_city": selected_city,
    })


from .models import OptScenario


def route_optimization_view(request):
    scenario = OptScenario.objects.last()
    route_data = {}
    subdivisions = []
    selected_subdivision = None
    scenario_name = scenario.name if scenario else "No scenario loaded"

    if scenario and scenario.optimized_csv:
        df = pd.read_csv(scenario.optimized_csv.path)
        df = df.fillna(0)

        # Filtrar por subdivisión
        subdivisions = sorted(df["subdivision"].dropna().unique().tolist())
        selected_subdivision = request.GET.get("subdivision") or (subdivisions[0] if subdivisions else None)

        if selected_subdivision:
            df = df[df["subdivision"] == selected_subdivision]

        # Seleccionar solo filas de tipo 'end' para obtener acumulados
        end_rows = df[df["type"] == "end"].copy()

        route_data = {
            "routes": end_rows["route_id"].astype(str).tolist(),
            "distance_km": end_rows["distance_km"].round(2).tolist(),
            "load_kg": end_rows["load_kg"].round(2).tolist(),
            "cost_clp": end_rows["step_cost_clp"].round(2).tolist(),
        }

    return render(request, "pxy_dashboard/apps/apps-route-optimization.html", {
        "route_data": route_data,
        "subdivisions": subdivisions,
        "selected_subdivision": selected_subdivision,
        "scenario_name": scenario_name,
    })

import json
import polyline
from django.shortcuts import render
from .models import OptScenario


def dispatch_plan_view(request):
    scenario = OptScenario.objects.last()
    geojson_by_subdivision = {}
    routes_by_subdivision = {}

    selected_subdivision = request.GET.get("subdivision")
    selected_route = request.GET.get("route")

    if scenario and scenario.dispatch_json:
        with open(scenario.dispatch_json.path, encoding='utf-8') as f:
            raw_data = json.load(f)

        for subdiv, result in raw_data.items():
            features = []
            route_ids = []

            for idx, route in enumerate(result.get("routes", [])):
                route_id = str(idx + 1)
                route_ids.append(route_id)

                if selected_subdivision and subdiv != selected_subdivision:
                    continue
                if selected_route and route_id != selected_route:
                    continue

                geometry = route.get("geometry")
                if geometry:
                    # decode returns [[lat, lon], …]
                    decoded = polyline.decode(geometry)
                    # swap to [lon, lat] for GeoJSON
                    coords = [[lng, lat] for lat, lng in decoded]

                    features.append({
                        "type": "Feature",
                        "geometry": {
                            "type": "LineString",
                            "coordinates": coords
                        },
                        "properties": {
                            "type": "route",
                            "subdivision": subdiv,
                            "route_id": route_id
                        }
                    })

                for step in route.get("steps", []):
                    # step["location"] is [lon, lat]
                    lat, lon = step["location"][1], step["location"][0]
                    step_type = step.get("type", "job")
                    step_id = step.get("id", "–")
                    load = step.get("load", [0])[0]
                    distance = step.get("distance", 0)
                    arrival = step.get("arrival", 0)

                    popup = (
                        f"<b>{step_type.title()}</b><br>"
                        f"Job ID: {step_id}<br>"
                        f"Load: {load} kg<br>"
                        f"Distance: {distance / 1000:.2f} km<br>"
                        f"Arrival: {arrival / 60:.1f} min"
                    )

                    features.append({
                        "type": "Feature",
                        "geometry": {
                            "type": "Point",
                            "coordinates": [lon, lat]
                        },
                        "properties": {
                            "popup": popup,
                            "step_type": step_type,
                            "subdivision": subdiv,
                            "route_id": route_id
                        }
                    })

            geojson_by_subdivision[subdiv] = {
                "type": "FeatureCollection",
                "features": features
            }
            routes_by_subdivision[subdiv] = route_ids

    return render(request, "pxy_dashboard/apps/apps-dispatch-plan.html", {
        "geojson_by_subdivision": json.dumps(geojson_by_subdivision),
        "routes_by_subdivision": json.dumps(routes_by_subdivision),   # ← JSON-encodes the route lists
        "subdivisions": list(geojson_by_subdivision.keys()),
        "selected_subdivision": selected_subdivision,
        "selected_route": selected_route,
    })



from django.db.models import Avg
from django.utils import timezone
from datetime import timedelta
import json, logging
from pxy_whatsapp.models import Conversation, Message

logger = logging.getLogger(__name__)

@login_required
def apps_whatsapp_bot(request):
    # — 1) Calcular métricas directamente —
    try:
        total_conversations = Conversation.objects.count()
        messages_in  = Message.objects.filter(direction="in").count()
        messages_out = Message.objects.filter(direction="out").count()
        # Promedio de tiempo de respuesta (solo out con tiempo)
        avg_rt = Message.objects.filter(
            direction="out", response_time_ms__isnull=False
        ).aggregate(Avg("response_time_ms"))["response_time_ms__avg"] or 0
        stats = {
            "total_conversations": total_conversations,
            "messages_in": messages_in,
            "messages_out": messages_out,
            "avg_response_time": int(avg_rt),
        }
    except Exception as e:
        logger.error(f"Error calculando métricas de WhatsApp: {e}")
        stats = {"total_conversations": 0, "messages_in": 0, "messages_out": 0, "avg_response_time": 0}

    # — 2) Cargar información de los bots —
    bots_info = []
    try:
        for bot in WhatsAppBot.objects.all():
            bots_info.append({
                "name": bot.name,
                "phone_number_id": bot.phone_number_id,
                "is_active": bot.is_active,
                "assistant": bot.assistant.name,
            })
    except Exception as e:
        logger.error(f"Error cargando bots de WhatsApp: {e}")

    # — 3) Prepare weekly data for scatter —
    today = timezone.now().date()
    start_date = today - timedelta(days=6)
    weekly_data = {}
    for bot in WhatsAppBot.objects.all():
        points = []
        for i in range(7):
            day = start_date + timedelta(days=i)
            count = Message.objects.filter(
                conversation__bot=bot,
                timestamp__date=day
            ).count()
            points.append({"x": day.strftime("%Y-%m-%d"), "y": count})
        weekly_data[bot.name] = points

    # 4) Render con todo el contexto
    context = {
        "stats": stats,
        "bots_info": bots_info,
        "weekly_data_json": json.dumps(weekly_data),
    }
    return render(request, "pxy_dashboard/apps/apps-whatsapp-bot.html", context)

from django.contrib.auth.decorators import login_required
from django.shortcuts import render
from django.db.models import Avg
from django.utils import timezone
from datetime import timedelta
import json

from pxy_bots.models import TelegramBot, TelegramConversation, TelegramMessage

@login_required
def apps_telegram_bot(request):
    # — Métricas globales —
    total_conversations = TelegramConversation.objects.count()
    messages_in = TelegramMessage.objects.filter(direction='in').count()
    messages_out = TelegramMessage.objects.filter(direction='out').count()
    avg_rt = (
        TelegramMessage.objects
        .filter(direction='out', response_time_ms__isnull=False)
        .aggregate(Avg('response_time_ms'))['response_time_ms__avg']
        or 0
    )

    stats = {
        'total_conversations': total_conversations,
        'messages_in': messages_in,
        'messages_out': messages_out,
        'avg_response_time': int(avg_rt),
    }

    # — Información de cada bot —
    bots_info = []
    for bot in TelegramBot.objects.all():
        bots_info.append({
            'name': bot.name,
            'username': getattr(bot, 'username', '–'),
            'is_active': bot.is_active,
            'assistant': bot.assistant.name,
        })

    # — Datos de los últimos 7 días para scatter —
    today = timezone.now().date()
    start_date = today - timedelta(days=6)
    weekly_data = {}
    for bot in TelegramBot.objects.all():
        series = []
        for i in range(7):
            day = start_date + timedelta(days=i)
            cnt = TelegramMessage.objects.filter(
                conversation__bot=bot,
                timestamp__date=day
            ).count()
            series.append({'x': day.strftime('%Y-%m-%d'), 'y': cnt})
        weekly_data[bot.name] = series

    return render(request, 'pxy_dashboard/apps/apps-telegram-bot.html', {
        'stats': stats,
        'bots_info': bots_info,
        'weekly_data_json': json.dumps(weekly_data),
    })




from pxy_meta_pages.models import FacebookPageAssistant, FacebookEvent

@login_required
def apps_facebook_pages_bot(request):
    """
    Dashboard de Facebook Pages Bots:
      - KPIs de páginas y eventos (comentarios/compartidos).
      - Tabla de bots configurados.
      - Scatter semanal de eventos por página.
    """
    # — KPIs básicos —
    total_pages    = FacebookPageAssistant.objects.count()
    total_events   = FacebookEvent.objects.count()
    total_comments = FacebookEvent.objects.filter(event_type__code="comment").count()
    total_shares   = FacebookEvent.objects.filter(event_type__code="share").count()

    stats = {
        "total_pages": total_pages,
        "total_events": total_events,
        "comments": total_comments,
        "shares": total_shares,
    }

    # — Información de cada bot —
    bots_info = []
    for bot in FacebookPageAssistant.objects.all():
        bots_info.append({
            "page_name":        bot.page_name,
            "page_id":          bot.page_id,
            "assistant":        bot.assistant.name,
            "is_subscribed":    bot.is_subscribed,
            "created_at":       bot.created_at.strftime("%Y-%m-%d"),
        })

    # — Datos semanales: contar eventos por día y por página —
    today = timezone.now().date()
    start_date = today - timedelta(days=6)
    weekly_data = {}
    for bot in FacebookPageAssistant.objects.all():
        series = []
        for i in range(7):
            day = start_date + timedelta(days=i)
            cnt = FacebookEvent.objects.filter(
                page=bot,
                timestamp__date=day
            ).count()
            series.append({"x": day.strftime("%Y-%m-%d"), "y": cnt})
        weekly_data[bot.page_name] = series

    return render(request,
                  "pxy_dashboard/apps/apps-facebook-pages-bot.html",
                  {
                    "stats": stats,
                    "bots_info": bots_info,
                    "weekly_data_json": json.dumps(weekly_data),
                  })

from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from django.http import JsonResponse
from pxy_city_digital_twins.models import WastePickup
from collections import defaultdict
import json

@login_required
def apps_urban_digital_twin(request):
    # ───── Ruta alterna para depuración: JSON completo ─────
    if request.GET.get("dump") == "1":
        full_table = list(WastePickup.objects.all().values(
            "id", "subdivision", "vehicle", "step_id", "quarter", "timestamp"
        ))
        return JsonResponse({"waste_pickup_table": full_table}, safe=False)

    # ───── Lógica del gráfico ─────
    subdivisions = WastePickup.objects.values_list("subdivision", flat=True).distinct()
    selected_subdivision = request.GET.get("subdivision") or (subdivisions[0] if subdivisions else None)

    raw = WastePickup.objects.filter(subdivision=selected_subdivision)
    data_by_vehicle = defaultdict(lambda: defaultdict(set))  # vehicle → date → set de cuartiles

    for pickup in raw:
        date_str = pickup.timestamp.strftime("%Y-%m-%d")
        veh = f"Vehículo {pickup.vehicle}"
        data_by_vehicle[veh][date_str].add(pickup.quarter)

    all_points = []
    unique_vehicles = set()
    for vehicle, date_map in data_by_vehicle.items():
        unique_vehicles.add(vehicle)
        for date, quarters in date_map.items():
            all_points.append({
                "x": date,
                "y": vehicle,
                "z": len(quarters)  # cuartiles únicos ese día
            })

    context = {
        "subdivisions": subdivisions,
        "selected_subdivision": selected_subdivision,
        "scatter_data_json": json.dumps(all_points),
        "vehicle_categories": json.dumps(sorted(unique_vehicles)),
    }
    return render(request, "pxy_dashboard/apps/apps-urban-digital-twin.html", context)
