import colorsys
import math
import os
from datetime import date, timedelta
import streamlit as st
import pandas as pd
import pyodbc
from dotenv import load_dotenv
import leafmap.foliumap as leafmap
import folium
from streamlit_folium import folium_static
import plotly.express as px

load_dotenv() 
#Me hace falta pedirle a bardo la consulta para limitar la geocerca y agregar una función para mostrarla en el mapa
#La parte de agregar categoría al df de la segunda vista
st.set_page_config(page_title="Mapa de establecimientos", layout="wide")
st.title("Mapa de establecimientos")

CENTER_LAT = 19.0414
CENTER_LON = -98.2063
MAP_ZOOM = 12
RADIO_METROS = 6000

COLOR_CON_PEDIDOS_MARKER = "green"
COLOR_SIN_PEDIDOS_MARKER = "red"


def distancia_km(lat1: float, lon1: float, lat2: float, lon2: float) -> float:
    r = 6371.0
    lat1_rad = math.radians(lat1)
    lat2_rad = math.radians(lat2)
    dlat = math.radians(lat2 - lat1)
    dlon = math.radians(lon2 - lon1)
    a = math.sin(dlat / 2) ** 2 + math.cos(lat1_rad) * math.cos(lat2_rad) * math.sin(dlon / 2) ** 2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    return r * c


def generar_paleta(n: int) -> list[str]:
    if n <= 0:
        return []
    colores = []
    for i in range(n):
        h = i / n
        r, g, b = colorsys.hsv_to_rgb(h, 0.75, 0.9)
        colores.append(f"#{int(r * 255):02x}{int(g * 255):02x}{int(b * 255):02x}")
    return colores


def get_db_connection():
    server = os.getenv('DB_SERVER')
    database = os.getenv('DB_DATABASE')
    username = os.getenv('DB_USERNAME')
    password = os.getenv('DB_PASSWORD')
    driver = os.getenv('DB_DRIVER')
    conn_str = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
    return pyodbc.connect(conn_str)


QUERY = """
WITH RestaurantesApp AS (
    SELECT
        r.id_restaurant,
        r.name_restaurant,
        dc.dc_name AS categoria,
        r.latitude,
        r.longitude
    FROM tbl_restaurants r
    INNER JOIN details_catalogs dc
        ON r.details_catalogs_id = dc.dc_id
    INNER JOIN dbo.schedule s
        ON r.id_restaurant = s.le_establishment_id
    WHERE EXISTS (
        SELECT 1
        FROM products_categories pc
        WHERE r.id_restaurant = pc.pg_establishments_id
          AND EXISTS (
              SELECT 1
              FROM products p
              INNER JOIN products_subcategories ps
                  ON ps.ps_id = p.pd_products_subcategories_id
              WHERE ps.ps_products_categories_id = pc.pg_id
                AND p.pd_status_id = 1
                AND p.pd_quantity > 0
          )
    )
    AND r.[status] = 1
    AND r.details_catalogs_id IS NOT NULL
),
PedidosFiltrados AS (
    SELECT
        o.id_order,
        o.restaurant,
        o.created_at,
        o.status
    FROM tbl_orders o
    WHERE o.type_order = 3
      AND o.created_at > '2025-12-02'
      AND o.status in (24,39)
)
SELECT
    ra.id_restaurant AS id_restaurante,
    ra.name_restaurant AS nombre,
    ra.categoria,
    ra.latitude AS lat,
    ra.longitude AS lon,
    COUNT(DISTINCT CASE WHEN pf.status = 24 THEN pf.id_order END) AS pedidos,
    COUNT(DISTINCT CASE WHEN pf.status = 39 THEN pf.id_order END) AS pedidos_rechazados,
    MAX(CASE WHEN pf.status = 39 THEN pf.created_at END) AS ultimo_pedido_rechazado,
    MIN(CASE WHEN pf.status = 24 THEN pf.created_at END) AS primera_orden,
    MAX(CASE WHEN pf.status = 24 THEN pf.created_at END) AS ultima_orden,
    DATEDIFF(DAY, MAX(CASE WHEN pf.status = 24 THEN pf.created_at END), GETDATE()) AS dias_sin_pedidos
FROM RestaurantesApp ra
LEFT JOIN PedidosFiltrados pf
    ON ra.id_restaurant = pf.restaurant
GROUP BY
    ra.id_restaurant,
    ra.name_restaurant,
    ra.categoria,
    ra.latitude,
    ra.longitude
ORDER BY pedidos DESC;
"""


@st.cache_data(ttl=300)
def cargar_datos():
    with get_db_connection() as conn:
        df = pd.read_sql(QUERY, conn)
    df["lat"] = pd.to_numeric(df["lat"], errors="coerce")
    df["lon"] = pd.to_numeric(df["lon"], errors="coerce")
    df = df.dropna(subset=["lat", "lon"])
    return df


GEOFENCE_QUERY = """
SELECT g.id_geofence,
       g.geofence_name,
       gc.lat,
       gc.lng
FROM [dev_apprisa_delivery].[dbo].tbl_geofences AS g
JOIN [dev_apprisa_delivery].[dbo].tbl_geofences_coordinates AS gc
  ON gc.geofence = g.id_geofence
WHERE g.status = 1
"""


@st.cache_data(ttl=300)
def cargar_geocercas():
    with get_db_connection() as conn:
        df_g = pd.read_sql(GEOFENCE_QUERY, conn)
    df_g["lat"] = pd.to_numeric(df_g["lat"], errors="coerce")
    df_g["lng"] = pd.to_numeric(df_g["lng"], errors="coerce")
    df_g = df_g.dropna(subset=["lat", "lng"])
    geocercas = []
    for id_geofence, grp in df_g.groupby("id_geofence", sort=False):
        nombre = grp["geofence_name"].iloc[0]
        puntos = list(zip(grp["lat"].tolist(), grp["lng"].tolist()))
        if len(puntos) >= 3:
            geocercas.append({"id": int(id_geofence), "nombre": nombre, "puntos": puntos})
    return geocercas


def punto_en_poligono(lat: float, lon: float, poligono: list[tuple[float, float]]) -> bool:
    n = len(poligono)
    if n < 3:
        return False
    dentro = False
    j = n - 1
    for i in range(n):
        yi, xi = poligono[i]
        yj, xj = poligono[j]
        if ((yi > lat) != (yj > lat)) and (
            lon < (xj - xi) * (lat - yi) / ((yj - yi) if (yj - yi) != 0 else 1e-12) + xi
        ):
            dentro = not dentro
        j = i
    return dentro


def punto_en_alguna_geocerca(lat: float, lon: float, geocercas: list[dict]) -> bool:
    return any(punto_en_poligono(lat, lon, g["puntos"]) for g in geocercas)


def dibujar_geocercas(mapa, geocercas):
    for g in geocercas:
        puntos = g["puntos"]
        if not puntos:
            continue
        puntos_cerrados = puntos if puntos[0] == puntos[-1] else puntos + [puntos[0]]
        folium.PolyLine(
            locations=puntos_cerrados,
            color="black",
            weight=3,
            opacity=1.0,
            tooltip=g["nombre"],
        ).add_to(mapa)


PEDIDOS_QUERY = """
WITH base AS (
    SELECT
        o.id_order,
        o.type_order,
        o.status,
        o.payment,
        o.restaurant,
        CAST(o.km_order AS DECIMAL(10,2)) AS km,
        CAST(o.[total] AS DECIMAL(18,2)) AS ticket,
        CASE
            WHEN o.status = 24
             AND o.order_acceptance_date IS NOT NULL
             AND o.order_completion_date IS NOT NULL
             AND DATEDIFF(MINUTE, o.order_acceptance_date, o.order_completion_date) >= 0
            THEN DATEDIFF(MINUTE, o.order_acceptance_date, o.order_completion_date)
        END AS tiempo
    FROM tbl_orders o
    WHERE o.created_at >= ?
      AND o.created_at <  ?
      AND o.status IN (24,39)
      AND o.restaurant NOT IN (
            212, 102, 107, 137, 140, 146, 152, 156, 174, 195, 196, 203, 231,
            10309, 10357, 10385, 10447, 10294, 186, 188, 205, 213, 215, 217,
            234, 238, 244, 272, 274, 275, 279, 10320, 10348, 102, 10557, 10558, 10294, 177
      )
      AND o.type_order IN (1,3,4)
),
medianas AS (
    SELECT DISTINCT
        type_order,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY km)
            OVER (PARTITION BY type_order) AS km_mediana,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN ticket > 0 THEN ticket END)
            OVER (PARTITION BY type_order) AS ticket_mediana,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tiempo)
            OVER (PARTITION BY type_order) AS minutos_mediana
    FROM base
    WHERE status = 24
)
SELECT
    CASE b.type_order
        WHEN 1 THEN 'Pedidos del Panel'
        WHEN 3 THEN 'Pedidos de la App'
        WHEN 4 THEN 'Pedidos de Súbito Favore'
        ELSE 'Otros tipos'
    END AS tipo_pedido,
    SUM(CASE WHEN b.status = 24 THEN 1 ELSE 0 END) AS [Pedidos exitosos],
    SUM(CASE WHEN b.status = 39 THEN 1 ELSE 0 END) AS [Pedidos rechazados],
    SUM(CASE WHEN b.status = 24 AND b.payment = 1 THEN 1 ELSE 0 END) AS [Pagado en efectivo],
    SUM(CASE WHEN b.status = 24 AND b.payment = 2 THEN 1 ELSE 0 END) AS [Pagado con tarjeta],
    SUM(CASE WHEN b.status = 24 AND b.payment IN (3,4) THEN 1 ELSE 0 END) AS [Pagado con transferencia],
    SUM(CASE WHEN b.status = 24 AND b.payment = 5 THEN 1 ELSE 0 END) AS [Pagado en la aplicación],
    SUM(CASE WHEN b.status = 24 AND b.payment = 9 THEN 1 ELSE 0 END) AS [Pagado con créditos],
    SUM(CASE WHEN b.status = 24 AND b.payment IN (6,7,8) THEN 1 ELSE 0 END) AS [Otros métodos de pago],
    AVG(CASE WHEN b.status = 24 THEN b.km END) AS [Km promedio],
    STDEV(CASE WHEN b.status = 24 THEN b.km END) AS [Km desviación estándar],
    MAX(m.km_mediana) AS [Km mediana],
    AVG(CASE WHEN b.status = 24 AND b.ticket > 0 THEN b.ticket END) AS [Ticket promedio],
    STDEV(CASE WHEN b.status = 24 AND b.ticket > 0 THEN b.ticket END) AS [Ticket desviación estándar],
    MAX(m.ticket_mediana) AS [Ticket mediana],
    AVG(CASE WHEN b.status = 24 THEN b.tiempo END) AS [Tiempo promedio del pedido],
    STDEV(CASE WHEN b.status = 24 THEN b.tiempo END) AS [Minutos desviación estándar],
    MAX(m.minutos_mediana) AS [Mediana de tiempo],
    COUNT(DISTINCT CASE WHEN b.status = 24 THEN b.restaurant END) AS [Restaurantes Activos],
    b.type_order AS _type_order
FROM base b
LEFT JOIN medianas m
    ON m.type_order = b.type_order
GROUP BY b.type_order
ORDER BY b.type_order;
"""


@st.cache_data(ttl=300)
def cargar_pedidos(fecha_inicio: date, fecha_fin: date) -> pd.DataFrame:
    with get_db_connection() as conn:
        df_p = pd.read_sql(PEDIDOS_QUERY, conn, params=(fecha_inicio, fecha_fin))
    return df_p


PEDIDOS_DIARIOS_QUERY = """
SELECT
    CAST(o.created_at AS DATE) AS fecha,
    SUM(CASE WHEN o.status = 24 THEN 1 ELSE 0 END) AS pedidos_exitosos,
    SUM(CASE WHEN o.status = 39 THEN 1 ELSE 0 END) AS pedidos_rechazados,
    COUNT(*) AS pedidos_totales
FROM tbl_orders o
WHERE o.created_at >= ?
  AND o.created_at <  ?
  AND o.status IN (24,39)
  AND o.restaurant NOT IN (
        212, 102, 107, 137, 140, 146, 152, 156, 174, 195, 196, 203, 231,
        10309, 10357, 10385, 10447, 10294, 186, 188, 205, 213, 215, 217,
        234, 238, 244, 272, 274, 275, 279, 10320, 10348, 102, 10557, 10558, 10294, 177
  )
  AND o.type_order IN (1,3,4)
GROUP BY CAST(o.created_at AS DATE)
ORDER BY fecha;
"""


@st.cache_data(ttl=300)
def cargar_pedidos_diarios(fecha_inicio: date, fecha_fin: date) -> pd.DataFrame:
    with get_db_connection() as conn:
        df_d = pd.read_sql(PEDIDOS_DIARIOS_QUERY, conn, params=(fecha_inicio, fecha_fin))
    df_d["fecha"] = pd.to_datetime(df_d["fecha"])
    return df_d


CSV_COMPETENCIAS = os.path.join(os.path.dirname(__file__), "establecimientos.csv")


@st.cache_data(ttl=300)
def cargar_competencias() -> pd.DataFrame:
    df_c = pd.read_csv(CSV_COMPETENCIAS)
    df_c["latitude"] = pd.to_numeric(df_c["latitude"], errors="coerce")
    df_c["longitude"] = pd.to_numeric(df_c["longitude"], errors="coerce")
    df_c = df_c.dropna(subset=["latitude", "longitude"])
    df_c["category"] = df_c["category"].fillna("Sin categoría").astype(str)
    # Folium renderiza el tooltip dentro de un template literal de JS (`...`),
    # así que un backtick en el texto rompe el script y deja el mapa en blanco.
    for col in ("name", "address", "category"):
        df_c[col] = df_c[col].astype(str).str.replace("`", "'", regex=False)
    return df_c.reset_index(drop=True)


df = cargar_datos()
geocercas = cargar_geocercas()

vista = st.sidebar.selectbox(
    "Vista",
    [
        "Mapa de establecimientos",
        "Búsqueda por coordenada",
        "Mapa de competencias",
        "Análisis de pedidos",
    ],
)

if vista == "Mapa de establecimientos":
    col1, col2, col3 = st.columns([2, 1, 1])
    with col1:
        filtro = st.selectbox(
            "Mostrar",
            ["Todos", "Solo con pedidos", "Solo sin pedidos"],
        )
    with col2:
        tipo_marcador = st.radio(
            "Tipo de marcador",
            ["Marcadores", "Puntos"],
            horizontal=True,
        )
    with col3:
        mostrar_radio = st.checkbox("Mostrar radio de 6 km", value=False)

    if filtro == "Solo con pedidos":
        df_filtrado = df[df["pedidos"] > 0]
    elif filtro == "Solo sin pedidos":
        df_filtrado = df[df["pedidos"] == 0]
    else:
        df_filtrado = df

    df_filtrado = df_filtrado.reset_index(drop=True)
    paleta = generar_paleta(len(df_filtrado))

    st.caption(f"Mostrando {len(df_filtrado)} establecimientos")

    m = leafmap.Map(center=(CENTER_LAT, CENTER_LON), zoom=MAP_ZOOM)
    dibujar_geocercas(m, geocercas)

    for idx, row in df_filtrado.iterrows():
        pedidos = int(row["pedidos"])
        tiene_pedidos = pedidos > 0
        color_marker = COLOR_CON_PEDIDOS_MARKER if tiene_pedidos else COLOR_SIN_PEDIDOS_MARKER
        color_radio = paleta[idx]
        popup_html = f"<b>{row['nombre']}</b><br>Categoría: {row['categoria']}<br>Pedidos: {pedidos}"
        popup = folium.Popup(popup_html, max_width=300)
        if tipo_marcador == "Puntos":
            folium.CircleMarker(
                location=[row["lat"], row["lon"]],
                radius=6,
                popup=popup,
                color=color_marker,
                fill=True,
                fill_color=color_marker,
                fill_opacity=0.9,
                weight=1,
            ).add_to(m)
        else:
            folium.Marker(
                location=[row["lat"], row["lon"]],
                popup=popup,
                icon=folium.Icon(color=color_marker, icon="info-sign"),
            ).add_to(m)
        if mostrar_radio:
            folium.Circle(
                location=[row["lat"], row["lon"]],
                radius=RADIO_METROS,
                color=color_radio,
                fill=True,
                fill_color=color_radio,
                fill_opacity=0.03,
                weight=2,
                opacity=0.9,
            ).add_to(m)

    folium_static(m, width=1200, height=700)

    if filtro == "Solo con pedidos":
        st.subheader("Establecimientos con pedidos")
        st.dataframe(df_filtrado, use_container_width=True, hide_index=True)
    elif filtro == "Solo sin pedidos":
        st.subheader("Establecimientos sin pedidos")
        st.dataframe(
            df_filtrado[["id_restaurante", "nombre", "categoria", "pedidos_rechazados", "ultimo_pedido_rechazado"]],
            use_container_width=True,
            hide_index=True,
        )

elif vista == "Búsqueda por coordenada":
    st.subheader("Búsqueda por coordenada (radio de 6 km)")

    col_lat, col_lon, col_modo = st.columns([1, 1, 2])
    with col_lat:
        lat_input = st.number_input("Latitud", value=float(CENTER_LAT), format="%.6f")
    with col_lon:
        lon_input = st.number_input("Longitud", value=float(CENTER_LON), format="%.6f")
    with col_modo:
        modo = st.radio(
            "Mostrar",
            ["Todos los establecimientos", "Sólo dentro del radio"],
            horizontal=True,
        )

    if geocercas and not punto_en_alguna_geocerca(lat_input, lon_input, geocercas):
        st.error("No puedes salirte de la geocerca. La coordenada ingresada está fuera del área permitida.")
        m_err = leafmap.Map(center=(lat_input, lon_input), zoom=MAP_ZOOM)
        dibujar_geocercas(m_err, geocercas)
        folium.Marker(
            location=[lat_input, lon_input],
            popup="Punto fuera de la geocerca",
            icon=folium.Icon(color="red", icon="remove-sign"),
        ).add_to(m_err)
        folium_static(m_err, width=1200, height=700)
        st.stop()

    df_coord = df.copy()
    df_coord["distancia_km"] = df_coord.apply(
        lambda r: distancia_km(lat_input, lon_input, r["lat"], r["lon"]),
        axis=1,
    )

    radio_km = RADIO_METROS / 1000.0
    df_en_radio = df_coord[df_coord["distancia_km"] <= radio_km]

    if modo == "Sólo dentro del radio":
        df_mapa = df_en_radio
    else:
        df_mapa = df_coord

    df_mapa = df_mapa.reset_index(drop=True)

    st.caption(f"Total de establecimientos dentro del radio de 6 km: {len(df_en_radio)}")

    m2 = leafmap.Map(center=(lat_input, lon_input), zoom=MAP_ZOOM)
    dibujar_geocercas(m2, geocercas)

    folium.Marker(
        location=[lat_input, lon_input],
        popup="Punto de búsqueda",
        icon=folium.Icon(color="blue", icon="star"),
    ).add_to(m2)

    folium.Circle(
        location=[lat_input, lon_input],
        radius=RADIO_METROS,
        color="blue",
        fill=True,
        fill_color="blue",
        fill_opacity=0.05,
        weight=2,
        opacity=0.8,
    ).add_to(m2)

    for _, row in df_mapa.iterrows():
        pedidos = int(row["pedidos"])
        color_marker = COLOR_CON_PEDIDOS_MARKER if pedidos > 0 else COLOR_SIN_PEDIDOS_MARKER
        popup_html = (
            f"<b>{row['nombre']}</b><br>"
            f"Categoría: {row['categoria']}<br>"
            f"Pedidos: {pedidos}<br>"
            f"Pedidos rechazados: {int(row['pedidos_rechazados'])}<br>"
            f"Distancia: {row['distancia_km']:.2f} km"
        )
        popup = folium.Popup(popup_html, max_width=300)
        folium.Marker(
            location=[row["lat"], row["lon"]],
            popup=popup,
            icon=folium.Icon(color=color_marker, icon="info-sign"),
        ).add_to(m2)

    folium_static(m2, width=1200, height=700)

    st.subheader("Establecimientos dentro del radio")
    df_tabla = (
        df_en_radio[["id_restaurante", "nombre", "pedidos", "pedidos_rechazados", "distancia_km"]]
        .sort_values("distancia_km")
        .reset_index(drop=True)
    )
    st.dataframe(df_tabla, use_container_width=True, hide_index=True)

elif vista == "Mapa de competencias":
    st.subheader("Mapa de competencias")

    df_comp = cargar_competencias()

    categorias = sorted(df_comp["category"].unique().tolist())

    solo_geocerca = st.checkbox("Solo dentro de la geocerca", value=True)

    with st.expander("Filtrar por categorías", expanded=True):
        cols_cat = st.columns(4)
        categorias_seleccionadas = []
        for i, cat in enumerate(categorias):
            col = cols_cat[i % 4]
            if col.checkbox(cat, value=False, key=f"comp_cat_{cat}"):
                categorias_seleccionadas.append(cat)

    df_comp_filtrado = df_comp[df_comp["category"].isin(categorias_seleccionadas)].copy()

    if solo_geocerca and geocercas:
        mask = df_comp_filtrado.apply(
            lambda r: punto_en_alguna_geocerca(r["latitude"], r["longitude"], geocercas),
            axis=1,
        )
        df_comp_filtrado = df_comp_filtrado[mask]

    df_comp_filtrado = df_comp_filtrado.reset_index(drop=True)

    st.caption(
        f"Mostrando {len(df_comp_filtrado)} competencias "
        f"({len(categorias_seleccionadas)} de {len(categorias)} categorías)"
    )

    paleta_cat = generar_paleta(len(categorias))
    color_por_categoria = dict(zip(categorias, paleta_cat))

    m_comp = leafmap.Map(center=(CENTER_LAT, CENTER_LON), zoom=MAP_ZOOM)
    dibujar_geocercas(m_comp, geocercas)

    for _, row in df_comp_filtrado.iterrows():
        color = color_por_categoria.get(row["category"], "#3388ff")
        reviews = row.get("reviews_count")
        rating = row.get("reviews_average")
        reviews_txt = f"{int(reviews)}" if pd.notna(reviews) else "—"
        rating_txt = f"{rating:.1f}" if pd.notna(rating) else "—"
        popup_html = (
            f"<b>{row['name']}</b><br>"
            f"Categoría: {row['category']}<br>"
            f"Reseñas: {reviews_txt} (★ {rating_txt})<br>"
            f"<small>{row.get('address', '')}</small>"
        )
        popup = folium.Popup(popup_html, max_width=320)
        folium.CircleMarker(
            location=[row["latitude"], row["longitude"]],
            radius=6,
            popup=popup,
            tooltip=row["name"],
            color=color,
            fill=True,
            fill_color=color,
            fill_opacity=0.9,
            weight=1,
        ).add_to(m_comp)

    folium_static(m_comp, width=1200, height=700)

    if categorias_seleccionadas:
        st.subheader("Conteo por categoría")
        conteo = (
            df_comp_filtrado.groupby("category")
            .size()
            .reset_index(name="cantidad")
            .sort_values("cantidad", ascending=False)
        )
        st.dataframe(conteo, use_container_width=True, hide_index=True)

else:
    st.subheader("Análisis de pedidos")

    hoy = date.today()
    col_f1, col_f2 = st.columns(2)
    with col_f1:
        fecha_inicio = st.date_input(
            "Fecha de inicio",
            value=hoy - timedelta(days=10),
            format="YYYY-MM-DD",
        )
    with col_f2:
        fecha_fin = st.date_input(
            "Fecha de fin",
            value=hoy + timedelta(days=1),
            format="YYYY-MM-DD",
        )

    if fecha_inicio >= fecha_fin:
        st.error("La fecha de inicio debe ser anterior a la fecha de fin.")
        st.stop()

    df_pedidos = cargar_pedidos(fecha_inicio, fecha_fin)
    df_diarios = cargar_pedidos_diarios(fecha_inicio, fecha_fin)

    if df_pedidos.empty:
        st.warning("No hay pedidos en el rango seleccionado.")
        st.stop()

    def _metric_para_tipo(df_p: pd.DataFrame, type_order: int, columna: str) -> int:
        fila = df_p[df_p["_type_order"] == type_order]
        if fila.empty:
            return 0
        valor = fila[columna].iloc[0]
        return int(valor) if pd.notna(valor) else 0

    panel_exitosos = _metric_para_tipo(df_pedidos, 1, "Pedidos exitosos")
    app_exitosos = _metric_para_tipo(df_pedidos, 3, "Pedidos exitosos")
    panel_activos = _metric_para_tipo(df_pedidos, 1, "Restaurantes Activos")
    app_activos = _metric_para_tipo(df_pedidos, 3, "Restaurantes Activos")

    st.caption(f"Rango: {fecha_inicio.isoformat()} a {fecha_fin.isoformat()}")

    k1, k2, k3, k4 = st.columns(4)
    k1.metric("Pedidos exitosos · Panel", f"{panel_exitosos:,}")
    k2.metric("Pedidos exitosos · App", f"{app_exitosos:,}")
    k3.metric("Establecimientos activos · Panel", f"{panel_activos:,}")
    k4.metric("Establecimientos activos · App", f"{app_activos:,}")

    st.markdown("### Pedidos totales diarios")
    if df_diarios.empty:
        st.info("No hay pedidos diarios para graficar en este rango.")
    else:
        fig_linea = px.line(
            df_diarios,
            x="fecha",
            y="pedidos_totales",
            markers=True,
        )
        fig_linea.update_traces(mode="lines+markers", line=dict(width=2))
        fig_linea.update_layout(
            xaxis_title="Fecha",
            yaxis_title="Pedidos totales",
            height=450,
        )
        st.plotly_chart(fig_linea, use_container_width=True)

    st.markdown("### Distribución Panel vs App (pedidos exitosos)")
    if panel_exitosos + app_exitosos == 0:
        st.info("No hay pedidos exitosos en Panel ni App para este rango.")
    else:
        df_pie = pd.DataFrame(
            {
                "Origen": ["Panel", "App"],
                "Pedidos exitosos": [panel_exitosos, app_exitosos],
            }
        )
        fig_pie = px.pie(
            df_pie,
            names="Origen",
            values="Pedidos exitosos",
            hole=0.4,
        )
        fig_pie.update_traces(textinfo="label+percent+value")
        fig_pie.update_layout(height=450)
        st.plotly_chart(fig_pie, use_container_width=True)

    st.markdown("### Detalle por tipo de pedido")
    df_pedidos_vista = df_pedidos.drop(columns=["_type_order"], errors="ignore")
    st.dataframe(df_pedidos_vista, use_container_width=True, hide_index=True)
