import os
import pandas as pd
import csv
import plotly.express as px
import gradio as gr
import folium
from folium.plugins import MarkerCluster

# Función para cargar CSV de oficinas
def load_offices_csv(path):
    with open(path, encoding='latin-1') as f:
        sample = ''.join([next(f) for _ in range(10)])
    dialect = csv.Sniffer().sniff(sample, delimiters=[',',';','\t','|'])
    sep = dialect.delimiter
    print(f"Delimitador detectado: '{sep}'".replace('{sep}', sep))
    df = pd.read_csv(path, sep=sep, encoding='latin-1', engine='python', on_bad_lines='skip')
    df.columns = [c.strip() for c in df.columns]
    return df

# Carga y preparación de datos
DATA_XLSX = "Base de Datos Prueba.xlsx"
OFFICES_CSV = "oficinas_completas_deduccion_avanzada.csv"

df = pd.read_excel(DATA_XLSX, parse_dates=["FECHA_APERTURA"])
df["MES"] = df["FECHA_APERTURA"].dt.to_period("M").dt.to_timestamp()

off_meta = load_offices_csv(OFFICES_CSV)

# Listas estáticas iniciales (convertir a str para evitar mezclas int/str)
departamentos       = sorted(off_meta['DEPARTAMENTO'].dropna().astype(str).unique().tolist())
municipios          = sorted(off_meta['CIUDAD'].dropna().astype(str).unique().tolist())
zonas               = sorted(off_meta['ZONA'].dropna().astype(str).unique().tolist())
productos           = sorted(df['TIPO PRODUCTO'].dropna().astype(str).unique().tolist())
colaboradores       = sorted(df['SK_COLABORADOR'].dropna().astype(str).unique().tolist())
segmentos           = sorted(df['SEGMENTO_CLIENTE'].dropna().astype(str).unique().tolist())
min_amt, max_amt    = int(df['MONTO_I'].min()), int(df['MONTO_I'].max())
min_plazo, max_plazo= int(df['PLAZO'].min()), int(df['PLAZO'].max())

# Coordenadas de oficinas
office_coords = {
    "Abrego": (8.080040, -73.219050),
    "Aguachica": (8.310229, -73.599837),
    "Aguazul": (5.171710, -72.547300),
    "Agustin Codazzi": (10.033471, -73.291284),
    "Andes": (5.655900, -75.879512),
    "Apartado": (7.885610, -76.634790),
    "Arauca": (7.086173, -70.757347),
    "Arjona": (10.297838, -75.308821),
    "Armenia": (6.167542, -75.764031),
    "Av. Caracas": (4.582934, -74.092877),
    "Ayapel": (0.000000, 0.000000),
    "Barrancabermeja": (7.059381, -73.862874),
    "Barrancas": (10.956670, -72.788870),
    "Barranquilla": (10.961041, -74.800959),
    "Belen": (5.989720, -72.913860),
    "Bello": (6.334930, -75.558280),
    "Bienestar Social Empleados": (0.000000, 0.000000),
    "Bosa": (5.930960, -73.616320),
    "Bosconia": (9.972841, -73.885721),
    "Bucaramanga Centro": (7.092868, -73.126498),
    "Buenaventura": (3.877616, -77.007365),
    "Buenos Aires": (3.013970, -76.646120),
    "Buga": (6.254484, -75.563634),
    "Cachingos": (0.000000, 0.000000),
    "Caldas": (6.089999, -75.636627),
    "Cali El Poblado (NO VIGENTE)": (3.418618, -76.497226),
    "Cali Norte": (6.300640, -70.205430),
    "Cali Sur": (0.000000, 0.000000),
    "Canal C": (0.000000, 0.000000),
    "Candelaria": (3.408325, -76.349086),
    "Carepa": (7.757548, -76.656274),
    "Carmen de Viboral": (6.082360, -75.335090),
    "Cartagena": (0.000000, 0.000000),
    "Cartagena Sur (NO VIGENTE)": (0.000000, 0.000000),
    "Cartago": (4.746743, -75.913598),
    "Castilla": (3.827220, -73.688310),
    "Caucasia": (7.987347, -75.196855),
    "Centro Bogota (NO VIGENTE)": (0.000000, 0.000000),
    "Centro MedelliAn": (6.254484, -75.563634),
    "Centro Suba": (5.828915, -73.035056),
    "Cerrito": (6.842993, -72.694730),
    "Chaparral": (3.750151, -75.340480),
    "Chia": (4.859712, -74.059663),
    "Chinacota": (7.607990, -72.600380),
    "Chinchina": (4.984375, -75.604801),
    "Chiquinquira": (5.618910, -73.819970),
    "Cienaga de Oro": (8.874380, -75.621750),
    "Cimitarra": (6.316110, -73.950540),
    "Copacabana": (6.348513, -75.507062),
    "Corozal": (9.317780, -75.295830),
    "Cucuta Atalaya": (7.907352, -72.524705),
    "Cucuta Centro": (7.884556, -72.504855),
    "Dabeiba": (7.033533, -76.167771),
    "Direccion General": (0.000000, 0.000000),
    "Duitama": (5.807690, -73.070165),
    "El Bagre": (7.596620, -74.804880),
    "El Banco": (8.998900, -73.970580),
    "El Cable (NO VIGENTE)": (0.000000, 0.000000),
    "El Tejar (NO VIGENTE)": (0.000000, 0.000000),
    "Engativa": (6.254484, -75.563634),
    "Envigado": (6.173196, -75.592097),
    "Espinal (NO VIGENTE)": (0.000000, 0.000000),
    "Facatativa": (4.811561, -74.384369),
    "Florencia": (1.682220, -77.072610),
    "Florida": (3.324420, -76.235460),
    "Floridablanca": (7.079171, -73.108311),
    "Fonseca": (10.888700, -72.851530),
    "Fontibon": (6.777790, -76.128580),
    "Fundacion": (10.521380, -74.186640),
    "Fundadores (NO VIGENTE)": (0.000000, 0.000000),
    "Funza": (4.714865, -74.212997),
    "Fusagasuga": (4.311530, -74.355406),
    "Galan": (6.637810, -73.288780),
    "Garagoa": (5.083373, -73.363727),
    "Garzon": (2.195783, -75.629006),
    "Gerencia Territorial - Sur": (0.000000, 0.000000),
    "Girardot": (4.303965, -74.804788),
    "Giron": (7.074196, -73.167534),
    "Granada": (4.519657, -74.353677),
    "Guaduas": (5.067815, -74.598816),
    "Ibague": (4.325569, -75.072920),
    "Ibague Centro (NO VIGENTE)": (0.000000, 0.000000),
    "Ipiales": (0.836103, -77.679298),
    "Itagui": (6.170260, -75.616540),
    "Jamundi": (3.111418, -76.606186),
    "Kennedy": (4.622480, -74.150010),
    "La America": (5.400098, -75.546666),
    "La Calera": (4.721216, -73.968126),
    "La Ceja": (6.032140, -75.431942),
    "La Dorada": (5.464481, -74.704063),
    "La Libertad": (2.445261, -76.632240),
    "La Plata": (2.391670, -75.891670),
    "La Union": (8.857282, -75.277048),
    "La Union - Valle": (4.537120, -76.104421),
    "La Victoria": (-0.111490, -71.110860),
    "Leticia": (-4.215596, -69.939065),
    "Libano": (4.922540, -75.063700),
    "Lorica (NO VIGENTE)": (0.000000, 0.000000),
    "Magangue": (9.186535, -74.788838),
    "Maicao": (11.350383, -72.352333),
    "Malaga": (6.702446, -72.731766),
    "Manizales": (5.050927, -75.519500),
    "Manrique": (6.484975, -75.019537),
    "Marinilla": (6.173840, -75.334800),
    "Mariquita": (5.198660, -74.896950),
    "Mesitas": (3.383561, -74.044270),
    "Minorista": (0.000000, 0.000000),
    "Mitu": (1.255250, -70.233390),
    "MoAitos": (0.000000, 0.000000),
    "Mocoa": (1.148930, -76.647750),
    "Mod Empoderados Costa Norte": (0.000000, 0.000000),
    "Mod Empoderados Plus-Bta": (0.000000, 0.000000),
    "Mod Empoderados Plus-M/llin": (0.000000, 0.000000),
    "Mod Empoderados Plus-Sur": (0.000000, 0.000000),
    "Molinos": (10.701780, -74.716750),
    "Mompox": (0.000000, 0.000000),
    "Moniquira": (5.877280, -73.570440),
    "MonteliAbano": (7.983010, -75.417260),
    "MonteriAa Centro": (8.754728, -75.881810),
    "Monteria": (8.773391, -75.817808),
    "Neiva (NO VIGENTE)": (0.000000, 0.000000),
    "Niquia": (2.649380, -75.636650),
    "OcaAa": (8.233420, -73.353310),
    "Orito": (0.665371, -76.872392),
    "PE  Soledad Malambo": (10.861604, -74.773950),
    "PE Acacias": (3.987212, -73.765837),
    "PE Anserma": (5.230240, -75.787920),
    "PE Baranoa": (10.796676, -74.914419),
    "PE Belen": (5.989720, -72.913860),
    "PE Cali La Casona": (0.000000, 0.000000),
    "PE Cartagena del ChairA": (1.334860, -74.843460),
    "PE Dosquebradas": (4.839160, -75.667270),
    "PE Istmina": (0.000000, 0.000000),
    "PE La Pintada 2": (5.749960, -75.616299),
    "PE Majagual": (8.534730, -74.657180),
    "PE Pasto (NO VIGENTE)": (0.000000, 0.000000),
    "PE Puerto Wilches": (7.348801, -73.898273),
    "PE PurificaciA3n": (3.856779, -74.932103),
    "PE Santa Rosa del Sur": (7.946502, -74.026033),
    "PE TAoquerres": (0.000000, 0.000000),
    "PE Tame": (6.470290, -71.716970),
    "PE Valle del Guamuez": (0.452500, -76.919170),
    "PE Zarzal": (4.395820, -76.069830),
    "Pailitas": (8.957360, -73.623460),
    "Palmira": (3.538908, -76.298466),
    "Pamplona": (7.377155, -72.648957),
    "Parque de la CaAa (NO VIGENTE)": (0.000000, 0.000000),
    "Parque de las Luces (NO VIGENTE)": (0.000000, 0.000000),
    "Pasto": (0.000000, 0.000000),
    "Patio Bonito": (6.205447, -75.575411),
    "Paz de Ariporo": (5.881428, -71.891972),
    "Pedregal": (6.254484, -75.563634),
    "Perdomo": (2.888170, -75.433810),
    "Pereira": (4.812216, -75.692047),
    "Piedecuesta": (6.988034, -73.050030),
    "PiendamA3 (NO VIGENTE)": (0.000000, 0.000000),
    "Pitalito": (1.790464, -76.055636),
    "Pivijay": (10.447166, -74.408568),
    "Planeta Rica": (8.408920, -75.586800),
    "Plato": (9.791910, -74.782970),
    "Popayan Norte": (2.462388, -76.535919),
    "Popayan Sur": (2.462388, -76.535919),
    "Primero de Mayo": (5.821771, -73.043034),
    "Principal": (13.375420, -81.369090),
    "Puerto Asis": (0.497650, -76.497680),
    "Puerto Berrio": (6.490949, -74.402668),
    "Puerto Boyaca": (5.971557, -74.571408),
    "Puerto CarreAo": (6.190854, -67.484779),
    "Puerto IniArida": (-2.148770, -71.754990),
    "Puerto Libertador": (7.889560, -75.672370),
    "Punto Express Neiva 2": (0.000000, 0.000000),
    "Quibdo": (0.000000, 0.000000),
    "Quirigua": (2.649380, -75.636650),
    "Restrepo": (3.826566, -76.521106),
    "Riohacha": (11.381478, -72.905309),
    "Rionegro": (7.264871, -73.147840),
    "Riosucio": (5.421350, -75.703230),
    "Robledo (NO VIGENTE)": (0.000000, 0.000000),
    "Sahagun": (8.941133, -75.495272),
    "San AndrAs Isla": (6.809803, -72.849736),
    "San Andres de Sotavento": (9.144750, -75.508770),
    "San Bernardo": (4.178771, -74.421565),
    "San Fernando": (9.218060, -74.330294),
    "San Francisco (NO VIGENTE)": (0.000000, 0.000000),
    "San Gil": (6.557700, -73.133180),
    "San Jose del Guaviare": (2.562393, -72.640344),
    "San Juan": (4.461030, -73.680480),
    "San Marcos": (8.658430, -75.131200),
    "San Onofre": (9.737530, -75.525580),
    "San Pelayo (NO VIGENTE)": (0.000000, 0.000000),
    "San Vicente del Caguan": (2.113170, -74.769180),
    "San Vicente del Chucuri": (6.881050, -73.411570),
    "Santa Helenita": (10.325410, -74.961830),
    "Santa MariAa (NO VIGENTE)": (0.000000, 0.000000),
    "Santa Marta Av. Libertador": (11.231008, -74.175841),
    "Santa Rosa de Cabal": (4.890245, -75.626971),
    "Santafe de Antioquia": (6.556870, -75.828060),
    "Santander de Quilichao": (3.008790, -76.485900),
    "Santo Domingo": (6.472363, -75.164506),
    "Sincelejo": (9.300213, -75.395603),
    "Sincelejo Centro (NO VIGENTE)": (9.303609, -75.392834),
    "Soacha": (4.582123, -74.211534),
    "Sogamoso": (5.718314, -72.930984),
    "Soledad (NO VIGENTE)": (0.000000, 0.000000),
    "Suba": (5.451280, -73.814140),
    "Suba Rincon": (4.728370, -74.088350),
    "Tesoreria": (0.000000, 0.000000),
    "Tierralta": (8.173570, -76.059210),
    "ToberiAn": (0.000000, 0.000000),
    "Tulua": (4.084320, -76.196650),
    "Tumaco": (0.000000, 0.000000),
    "Tunja": (5.538590, -73.366380),
    "Turbo": (8.098040, -76.731690),
    "Ubate": (4.482420, -73.934840),
    "Urrao": (6.340116, -76.097593),
    "Valledupar": (10.469026, -73.257035),
    "Valledupar Centro": (10.476217, -73.245945),
    "Velez": (6.012930, -73.673140),
    "Venecia": (4.088080, -74.477460),
    "Villanueva": (4.609834, -72.927380),
    "Villavicencio": (4.144229, -73.634525),
    "Villeta": (5.011370, -74.471560),
    "Yarumal": (6.962765, -75.416779),
    "Yomasa": (4.519873, -74.092186),
    "Yopal": (5.340170, -72.394240),
    "Yumbo": (3.581378, -76.494648),
    "Zipaquira": (5.025810, -73.991283),
}

# Callbacks
def update_municipios(dept):
    return sorted(off_meta.dropna().astype(str).unique().tolist())

def update_zonas(dept, muni):
    df2 = off_meta
    return sorted(df2['ZONA'].dropna().astype(str).unique().tolist())

def update_oficinas(dept, muni, zona):
    if zona:
        df2 = df2[df2['ZONA']==zona]
    return sorted(df2['NOMBRE OFICINA'].dropna().astype(str).unique().tolist())

# Dashboard
def dashboard(f_inicio, f_fin, zona, tipos,
               colaborador_sel, segmento_sel):
    d = df.copy()
    if f_inicio:
        d = d[d['FECHA_APERTURA'] >= pd.to_datetime(f_inicio)]
    if f_fin:
        d = d[d['FECHA_APERTURA'] <= pd.to_datetime(f_fin)]
    if tipos:
        d = d[d['TIPO PRODUCTO'].astype(str).isin(tipos)]
    if colaborador_sel:
        d = d[d['SK_COLABORADOR'].astype(str).isin(colaborador_sel)]
    if segmento_sel:
        d = d[d['SEGMENTO_CLIENTE'].astype(str).isin(segmento_sel)]

    fig1 = px.bar(d.groupby('MES')['MONTO_I'].sum().reset_index(), x='MES', y='MONTO_I',
                  labels={'MES':'Mes','MONTO_I':'Monto (COP)'}, title='Monto desembolsado por mes')
    df2 = d['TIPO PRODUCTO'].value_counts().reset_index()
    df2.columns = ['TIPO PRODUCTO','CANT']
    fig2 = px.pie(df2, names='TIPO PRODUCTO', values='CANT', title='Distribución por producto')
    fig3 = px.box(d, x='TIPO PRODUCTO', y='TASA', title='Distribución de tasas')
    df_col = d['SK_COLABORADOR'].value_counts().reset_index()
    df_col.columns = ['Colaborador','CANT']
    fig4 = px.bar(df_col.head(10), x='Colaborador', y='CANT', title='Top 10 colaboradores')
    fig5 = px.histogram(d, x='PLAZO', nbins=20, title='Distribución de plazo (días)')
    df_seg = d['SEGMENTO_CLIENTE'].value_counts().reset_index()
    df_seg.columns = ['Segmento','CANT']
    fig6 = px.bar(df_seg, x='Segmento', y='CANT', title='Distribución por segmento')

    m = folium.Map(location=[4.6, -74.1], zoom_start=6)
    mc = MarkerCluster().add_to(m)
    for ofi, coord in office_coords.items():
        sub = d[d['OFICINA'].astype(str)==ofi]
        if sub.empty:
            continue
        total = sub['MONTO_I'].sum()
        folium.CircleMarker(location=coord, radius=6, fill=True,
                            popup=f"{ofi}<br>Total: {total:,.0f} COP").add_to(mc)
    return fig1, fig2, fig3, fig4, fig5, fig6, m._repr_html_()

# Interfaz Gradio
with gr.Blocks() as demo:
    gr.Markdown("## Dashboard Bancamía – Análisis Exploratorio")
    with gr.Row():
        with gr.Column(scale=1):
            f_inicio = gr.Textbox(label="Fecha inicio (YYYY-MM-DD)", value="2025-01-01")
            f_fin    = gr.Textbox(label="Fecha fin    (YYYY-MM-DD)", value="2025-03-31")
            zona     = gr.Dropdown(zonas, label="Zona")
            tipos    = gr.CheckboxGroup(choices=productos, label="Tipo de producto")
            colabor  = gr.Dropdown(colaboradores, label="Colaborador", multiselect=True)
            segmento = gr.Dropdown(segmentos, label="Segmento", multiselect=True)
            btn      = gr.Button("Actualizar")
        with gr.Column(scale=3):
            out1 = gr.Plot(); out2 = gr.Plot(); out3 = gr.Plot()
            out4 = gr.Plot(); out5 = gr.Plot(); out6 = gr.Plot()
            out7 = gr.HTML()
    btn.click(dashboard, [f_inicio, f_fin, zona, tipos, colabor, segmento],
              [out1, out2, out3, out4, out5, out6, out7])
if __name__ == "__main__":
    demo.launch()