app.py

# app.py
import os
import json
from io import BytesIO
import tempfile
import pandas as pd
import gradio as gr

# ================== BigQuery deps (opcionales) ==================
try:
    from google.cloud import bigquery
    _HAS_BQ = True
except Exception:
    _HAS_BQ = False

try:
    import db_dtypes  # noqa: F401
    _HAS_DB_DTYPES = True
except Exception:
    _HAS_DB_DTYPES = False

APP_TITLE = "Cruce CLIENTE × MMP por EVENTO (archivo o BigQuery)"
APP_DESC = """
### Fuente 1: MMP
**BigQuery (tabla única)**: `leadgenios-tech.afiliacion_datalake.daily_afiliate_datalake`

Pasos BQ:
1) **Listar App IDs (BigQuery)** y seleccionar uno.  
2) Ingresá **rango de fechas** (YYYY-MM-DD).  
3) **Obtener columnas (schema)** → sugiere **columna temporal (event_time)**, **evento (event_name)**, **ID en MMP (appsflyer_id/customer_user_id/advertising_id)** y **App ID** (app_id).  
4) **Listar eventos por rango** (usa App ID + fechas + columna de evento).  
5) **Consultar y cargar MMP** → genera CSV temporal, preview y descarga.

**Archivo**: subir archivo, detectar columnas y (opcional) **listar eventos** para filtrar. No hace falta App ID ni fechas.

### Fuente 2: CLIENTE
1) Subir **CLIENTE** → **Obtener mapeo de columnas**.  
2) Elegir **ID en CLIENTE**.  
3) **Columna de validación (opcional)** y **valores** (opcional).  
4) **Columna de métrica (CLIENTE) (opcional)**.  
5) **Columna de EVENTO (CLIENTE) (opcional)**.

### Final
- Por cada **evento** (de MMP), **Cliente, MMP, %** con `% = Cliente / MMP × 100` (1 decimal).  
- Excel: **Hoja 1** tablas por evento; **Hoja 2** `raw_merge`.
"""

# ================== Helpers de lectura ==================
def _read_excel(pathlike):
    return pd.read_excel(pathlike, engine="openpyxl")

def _read_csv_with_fallbacks(pathlike):
    try:
        return pd.read_csv(pathlike, sep=None, engine="python", on_bad_lines="skip", encoding="utf-8")
    except Exception:
        return pd.read_csv(pathlike, sep=None, engine="python", on_bad_lines="skip", encoding="latin-1")

def _safe_read(fileobj_or_path):
    if fileobj_or_path is None or (isinstance(fileobj_or_path, str) and not fileobj_or_path.strip()):
        return None
    path = fileobj_or_path.name if hasattr(fileobj_or_path, "name") else fileobj_or_path
    ext = os.path.splitext(str(path))[-1].lower()
    try:
        if ext in [".xlsx", ".xlsm", ".xltx", ".xltm"]:
            return _read_excel(path)
        elif ext == ".csv" or ext == "":
            try:
                return _read_excel(path)
            except Exception:
                return _read_csv_with_fallbacks(path)
        else:
            try:
                return _read_excel(path)
            except Exception:
                return _read_csv_with_fallbacks(path)
    except Exception as e:
        raise RuntimeError(f"No se pudo leer '{os.path.basename(str(path))}': {e}")

def _guess(cols, candidates):
    lower_map = {c.lower(): c for c in cols}
    for cand in candidates:
        if cand.lower() in lower_map:
            return lower_map[cand.lower()]
    return cols[0] if cols else None

def _guess_optional(cols, candidates):
    lower_map = {c.lower(): c for c in cols}
    for cand in candidates:
        if cand.lower() in lower_map:
            return lower_map[cand.lower()]
    return None

def _safe_file_output(path):
    if path and isinstance(path, str) and os.path.isfile(path):
        return path
    return None

# ================== Normalización de IDs ==================
def normalize_id_series(s: pd.Series) -> pd.Series:
    """
    Normaliza IDs para merges:
    - Convierte a string, quita espacios.
    - Si es float 'entero' (123.0) lo transforma a '123'.
    - Deja NaN como NaN.
    """
    def _norm(v):
        if pd.isna(v):
            return pd.NA
        # floats que representan enteros → sin .0
        if isinstance(v, float):
            if v.is_integer():
                return str(int(v))
            else:
                # si es float no entero, lo pasamos a string tal cual
                return str(v)
        # todo lo demás a str
        vs = str(v).strip()
        # si quedó como "nan" literal, considerar NA
        if vs.lower() in ("nan", "none", ""):
            return pd.NA
        return vs
    out = s.map(_norm)
    # asegura dtype string que permite NA
    return out.astype("string")

# ================== BigQuery helpers ==================
BQ_PROJECT = "leadgenios-tech"
BQ_TABLE_FQN = "leadgenios-tech.afiliacion_datalake.daily_afiliate_datalake"

def _need_bq_client():
    """
    Producción (Hugging Face): usa el secret GCP_SA_JSON (contenido del JSON de la service account).
    Local: si no hay GCP_SA_JSON, usa GOOGLE_APPLICATION_CREDENTIALS como fallback.
    """
    if not _HAS_BQ:
        raise RuntimeError("Falta dependencia 'google-cloud-bigquery'.")

    sa_json = os.getenv("GCP_SA_JSON")
    if sa_json:
        try:
            from google.oauth2 import service_account
        except Exception as e:
            raise RuntimeError(f"No se pudo importar google.oauth2.service_account: {e}")
        try:
            info = json.loads(sa_json)
            creds = service_account.Credentials.from_service_account_info(info)
            project = info.get("project_id") or BQ_PROJECT
            return bigquery.Client(project=project, credentials=creds)
        except Exception as e:
            raise RuntimeError(f"GCP_SA_JSON inválido o no utilizable: {e}")

    if os.getenv("GOOGLE_APPLICATION_CREDENTIALS"):
        try:
            return bigquery.Client(project=BQ_PROJECT)
        except Exception as e:
            raise RuntimeError(f"Error creando cliente BQ con GOOGLE_APPLICATION_CREDENTIALS: {e}")

    raise RuntimeError("No hay credenciales: seteá GCP_SA_JSON (prod) o GOOGLE_APPLICATION_CREDENTIALS (local).")

def bq_get_columns_fixed():
    client = _need_bq_client()
    table = client.get_table(BQ_TABLE_FQN)
    cols = [sch.name for sch in table.schema]

    time_guess  = _guess(cols, ["event_time", "install_time", "attributed_touch_time"])
    event_guess = _guess(cols, ["event_name"])
    # IDs típicos
    id_guess    = _guess(cols, ["appsflyer_id", "customer_user_id", "advertising_id"])
    appid_guess = _guess(cols, ["app_id"])
    return cols, time_guess, event_guess, id_guess, appid_guess

def bq_list_app_ids(limit=500):
    """Lista App IDs de la tabla BQ para el dropdown."""
    client = _need_bq_client()
    sql = f"""
    SELECT DISTINCT CAST(app_id AS STRING) AS app_id
    FROM `{BQ_TABLE_FQN}`
    WHERE app_id IS NOT NULL AND app_id <> ''
    ORDER BY app_id
    LIMIT {int(limit)}
    """
    df = client.query(sql).result().to_dataframe(create_bqstorage_client=False)
    vals = sorted(df["app_id"].dropna().astype(str).tolist())
    return vals, f"{len(vals)} App IDs encontrados."

def bq_list_events_fixed(event_col, time_col, app_id_col, app_id_value, start_date, end_date, limit=500):
    client = _need_bq_client()
    cols, t_guess, e_guess, _, a_guess = bq_get_columns_fixed()
    event_col  = event_col  or e_guess
    time_col   = time_col   or t_guess
    app_id_col = app_id_col or a_guess
    if not (event_col and time_col and app_id_col and app_id_value and start_date and end_date):
        return [], "Completá App ID, fechas y columnas (evento/fecha/App ID)."
    sql = f"""
    SELECT DISTINCT CAST({event_col} AS STRING) AS ev
    FROM `{BQ_TABLE_FQN}`
    WHERE DATE({time_col}) BETWEEN @sd AND @ed
      AND CAST({app_id_col} AS STRING) = @app_id
    ORDER BY ev
    LIMIT {int(limit)}
    """
    job = client.query(sql, job_config=bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("sd", "DATE", str(start_date)),
            bigquery.ScalarQueryParameter("ed", "DATE", str(end_date)),
            bigquery.ScalarQueryParameter("app_id", "STRING", str(app_id_value).strip()),
        ]
    ))
    df = job.result().to_dataframe(create_bqstorage_client=False)
    return sorted(df["ev"].dropna().astype(str).tolist()), f"{len(df)} eventos encontrados."

def bq_query_to_temp_fixed(event_col, time_col, app_id_col, app_id_value, start_date, end_date, selected_events):
    client = _need_bq_client()
    cols, t_guess, e_guess, _, a_guess = bq_get_columns_fixed()
    event_col  = event_col  or e_guess
    time_col   = time_col   or t_guess
    app_id_col = app_id_col or a_guess
    if not (event_col and time_col and app_id_col and app_id_value and start_date and end_date):
        raise RuntimeError("Completá App ID, fechas y columnas (evento/fecha/App ID).")
    params = [
        bigquery.ScalarQueryParameter("sd", "DATE", str(start_date)),
        bigquery.ScalarQueryParameter("ed", "DATE", str(end_date)),
        bigquery.ScalarQueryParameter("app_id", "STRING", str(app_id_value).strip()),
    ]
    ev_filter = ""
    if selected_events:
        params.append(bigquery.ArrayQueryParameter("events", "STRING", [str(v) for v in selected_events]))
        ev_filter = f"AND CAST({event_col} AS STRING) IN UNNEST(@events)"
    sql = f"""
    SELECT *
    FROM `{BQ_TABLE_FQN}`
    WHERE DATE({time_col}) BETWEEN @sd AND @ed
      AND CAST({app_id_col} AS STRING) = @app_id
      {ev_filter}
    """
    job = client.query(sql, job_config=bigquery.QueryJobConfig(query_parameters=params))
    df = job.result().to_dataframe(create_bqstorage_client=False)
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".csv")
    df.to_csv(tmp.name, index=False)
    return tmp.name, df.head(20).to_dict(orient="records")

# ================== MMP por archivo ==================
def file_mmp_schema(file):
    try:
        df = _safe_read(file)
    except Exception as e:
        return (gr.update(), gr.update(), gr.update(), gr.update(), f"Error al leer MMP: {e}")
    cols = list(df.columns)
    event_guess = _guess(cols, ["event_name", "Event Name", "evento", "EVENTO", "Event"])
    id_guess    = _guess(cols, ["appsflyer_id", "customer_user_id", "advertising_id",
                                "Advertising ID", "adid", "idfa", "ID", "Id"])
    time_guess  = _guess_optional(cols, ["event_time", "install_time", "attributed_touch_time",
                                         "event_date", "timestamp", "date", "Date", "Event Time"])
    appid_guess = _guess_optional(cols, ["app_id", "bundle_id", "app", "appId", "App ID"])

    return (gr.update(choices=cols, value=time_guess),
            gr.update(choices=cols, value=event_guess),
            gr.update(choices=cols, value=id_guess),
            gr.update(choices=cols, value=appid_guess),
            "Columnas detectadas (archivo MMP).")

def file_mmp_list_events_simple(file, event_col):
    try:
        df = _safe_read(file)
    except Exception as e:
        return gr.update(choices=[], value=[]), f"Error al leer MMP: {e}"
    if not event_col or event_col not in df.columns:
        return gr.update(choices=[], value=[]), "Elegí la columna de evento (archivo MMP)."
    vals = sorted(pd.Series(df[event_col].astype(str).unique()).dropna().tolist())
    return gr.update(choices=vals, value=vals), f"{len(vals)} eventos detectados (archivo MMP)."

def file_prepare(src_file, ev_col, selected_events):
    try:
        df = _safe_read(src_file)
        if selected_events:
            df = df[df[ev_col].astype(str).isin([str(v) for v in selected_events])]
        tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".csv")
        df.to_csv(tmp.name, index=False)
        return tmp.name, df.head(20)
    except Exception as e:
        raise RuntimeError(f"Error al preparar MMP (archivo): {e}")

# ================== CLIENTE helpers ==================
def cliente_map_columns(cliente_file):
    try:
        df = _safe_read(cliente_file)
    except Exception as e:
        return (gr.update(), gr.update(), gr.update(), gr.update(), "Error al leer CLIENTE: "+str(e))
    cols = list(df.columns)
    id_guess = _guess(cols, [
        "appsflyer_id","customer_user_id","advertising_id",
        "Advertising ID","user_id","User Id",
        "transaction_id","Transaction Id","ID","Id","rut"
    ])
    valid_guess  = None
    metric_guess = _guess_optional(cols, ["revenue","amount","value","ticket","Event Revenue","importe","monto"])
    event_guess  = _guess_optional(cols, ["event_name","Event Name","evento","EVENTO","Event"])

    return (gr.update(choices=cols, value=id_guess),
            gr.update(choices=cols, value=valid_guess),
            gr.update(choices=cols, value=metric_guess),
            gr.update(choices=cols, value=event_guess),
            "Columnas de CLIENTE listas.")

def load_validation_values(cliente_file, validation_col):
    try:
        df_c = _safe_read(cliente_file) if cliente_file else None
    except Exception as e:
        return gr.update(choices=[], value=[]), f"Error al leer CLIENTE: {e}"
    if df_c is None or not validation_col or validation_col not in df_c.columns:
        return gr.update(choices=[], value=[]), "Omitido: sin columna de validación (se usará cruce de IDs)."
    vals = sorted(pd.Series(df_c[validation_col].astype(str).unique()).dropna().tolist())
    return gr.update(choices=vals, value=[]), f"{len(vals)} valores posibles de validación."


# ================== Compute ==================
from io import BytesIO
import tempfile
import re
import pandas as pd

# --- helpers ---------------------------------------------------------

def normalize_id_series(s: pd.Series) -> pd.Series:
    """
    Normalize IDs for robust equality:
    - cast to string
    - strip whitespace
    - lowercase
    - convert 'nan'/'none' to ''
    """
    x = s.astype(str).str.strip().str.lower()
    x = x.replace({"nan": "", "none": ""})
    return x.fillna("")

def _autodetect_validation_col(cols):
    """Try to find a likely validation column if user didn't pick one."""
    candidates = [
        "valid", "valido", "válido", "is_valid", "usable", "status",
        "approved", "aprobado", "ok", "flag", "validated", "validation"
    ]
    lower = {c.lower(): c for c in cols}
    for cand in candidates:
        if cand in lower:
            return lower[cand]
    return None

def _default_truthy_set():
    # NOTE: all lowercased string checks
    return {
        "true", "1", "yes", "y", "ok", "si", "sí",
        "valid", "valido", "válido", "usable", "approved", "aprobado",
        "x", "t"
    }

# --- main ------------------------------------------------------------

def compute(cliente_file, mmp_final_path,
            id_cliente_col, id_mmp_col,
            validation_col_client, validation_values,   # optional
            metric_col_client,                          # ignored in this logic
            client_event_col,                           # ignored (denominator is MMP)
            mmp_event_col,                              # required
            selected_events_mmp):

    if not mmp_final_path:
        return None, None, "Primero completá la fuente MMP."
    if not cliente_file:
        return None, None, "Subí CLIENTE y mapeá las columnas."

    # Read sources
    try:
        df_c = _safe_read(cliente_file)
        df_m = _safe_read(mmp_final_path)
    except Exception as e:
        return None, None, f"Error al leer fuentes: {e}"

    # Required columns present?
    for name, col, df in [
        ("ID en CLIENTE", id_cliente_col, df_c),
        ("ID en MMP",     id_mmp_col,     df_m),
        ("EVENTO en MMP", mmp_event_col,  df_m),
    ]:
        if not col or col not in df.columns:
            return None, None, f"Columna inválida: {name} = {col}"

    # Normalize IDs
    try:
        ids_cli_norm = normalize_id_series(df_c[id_cliente_col])
        ids_mmp_norm = normalize_id_series(df_m[id_mmp_col])
    except Exception as e:
        return None, None, f"Error normalizando IDs: {e}"

    # If user didn't select a validation col, try to autodetect one
    if not validation_col_client or validation_col_client not in df_c.columns:
        auto_val_col = _autodetect_validation_col(df_c.columns)
        validation_col_client = auto_val_col if auto_val_col else None

    # If a validation column exists but user didn't pick values, use default “truthy” set
    truthy = _default_truthy_set()
    use_validation = validation_col_client is not None
    if use_validation:
        cand_vals = validation_values or []
        if cand_vals:
            truthy = {str(v).strip().lower() for v in cand_vals}

    # Build set of CLIENTE IDs that are considered valid
    try:
        if use_validation:
            val_series = df_c[validation_col_client].astype(str).str.strip().str.lower()
            mask_valid = val_series.isin(truthy)
            valid_client_ids = set(ids_cli_norm[mask_valid][ids_cli_norm[mask_valid] != ""])
        else:
            # No validation column → any presence in CLIENTE counts as valid
            valid_client_ids = set(ids_cli_norm[ids_cli_norm != ""])
    except Exception as e:
        return None, None, f"Error aplicando validación en CLIENTE: {e}"

    # Create VALIDO flag in MMP: True if MMP id ∈ valid_client_ids
    df_m = df_m.copy()
    df_m["VALIDO"] = ids_mmp_norm.isin(valid_client_ids)

    # Events to process (if none selected, use all present in MMP)
    if not selected_events_mmp:
        try:
            selected_events_mmp = (
                df_m[mmp_event_col].astype(str).dropna().unique().tolist()
            )
            selected_events_mmp = sorted(map(str, selected_events_mmp))
        except Exception as e:
            return None, None, f"Error obteniendo lista de eventos MMP: {e}"

    # B: total rows in MMP per event
    mmp_counts = df_m.groupby(df_m[mmp_event_col].astype(str), dropna=False).size()

    # A: total rows in MMP per event with VALIDO=True
    cliente_counts = (
        df_m[df_m["VALIDO"]]
        .groupby(df_m.loc[df_m["VALIDO"], mmp_event_col].astype(str), dropna=False)
        .size()
    )

    # Build event tables
    tables_by_event = {}
    for ev in selected_events_mmp:
        ev_str = str(ev)
        B = int(mmp_counts.get(ev_str, 0))
        A = int(cliente_counts.get(ev_str, 0))
        pct = round((A / B * 100), 1) if B else 0.0
        tables_by_event[ev] = pd.DataFrame([{"Cliente": A, "MMP": B, "%": pct}])

    # ===== Excel output =====
    xls_bytes = BytesIO()
    with pd.ExcelWriter(xls_bytes, engine="xlsxwriter") as writer:
        # Sheet 1: tables by EVENT
        sheet_name = "tablas_por_EVENTO"
        start_row = 0
        for ev, table_df in tables_by_event.items():
            pd.DataFrame([[ev]]).to_excel(
                writer, sheet_name=sheet_name, startrow=start_row,
                index=False, header=False
            )
            start_row += 1
            table_df.to_excel(
                writer, sheet_name=sheet_name, startrow=start_row, index=False
            )
            start_row += len(table_df) + 2

        # Sheet 2: raw MMP + only VALIDO (explicitly drop the ID columns)
        cols_front = ["VALIDO"]  # first column
        # Keep event column visible & useful
        if mmp_event_col in df_m.columns:
            cols_front.insert(0, mmp_event_col)

        # Exclude ID & any helper columns from raw output
        drop_cols = {id_mmp_col, "_id_norm_mmp"}  # (we never created _id_norm_mmp here)
        cols_rest = [c for c in df_m.columns if c not in set(cols_front) | drop_cols]
        df_m[cols_front + cols_rest].to_excel(writer, sheet_name="raw_mmp", index=False)

    xls_bytes.seek(0)
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    tmp.write(xls_bytes.getvalue()); tmp.flush(); tmp.close()
    download_path = tmp.name

    # Preview: first event table
    preview = None
    if tables_by_event:
        first_ev = list(tables_by_event.keys())[0]
        preview = tables_by_event[first_ev]

    return preview, download_path, "Listo ✅"

# ================== UI ==================
with gr.Blocks(title=APP_TITLE) as demo:
    gr.Markdown(f"# {APP_TITLE}\n\n{APP_DESC}")

    # ===== MMP: Selección de fuente =====
    gr.Markdown("## Fuente 1: MMP")
    mmp_source = gr.Radio(choices=["Subir archivo", "BigQuery"], value="Subir archivo", label="Fuente de MMP")

    # --- BigQuery Panel ---
    with gr.Column(visible=False) as bq_panel:
        gr.Markdown("**Paso MMP-BQ 1**: App ID y Fechas")
        with gr.Row():
            bq_app_id_value = gr.Dropdown(choices=[], value=None, label="App ID (BigQuery)")
            list_app_ids_btn = gr.Button("Listar App IDs (BigQuery)")
        list_app_ids_msg = gr.Markdown()

        with gr.Row():
            bq_start = gr.Textbox(label="Fecha desde (YYYY-MM-DD)", placeholder="YYYY-MM-DD")
            bq_end = gr.Textbox(label="Fecha hasta (YYYY-MM-DD)", placeholder="YYYY-MM-DD")

        gr.Markdown("**Paso MMP-BQ 2**: Obtener columnas (schema)")
        with gr.Row():
            bq_time_col = gr.Dropdown(choices=[], value=None, label="Columna temporal (ej: event_time)")
            mmp_event_col_bq = gr.Dropdown(choices=[], value=None, label="Columna de EVENTO en MMP (ej: event_name)")
            id_mmp_col_bq = gr.Dropdown(choices=[], value=None, label="ID en MMP (para cruce)")
            bq_app_id_col = gr.Dropdown(choices=[], value=None, label="Columna App ID (ej: app_id)")
        bq_schema_btn = gr.Button("Obtener columnas (schema)")
        bq_schema_msg = gr.Markdown()

        gr.Markdown("**Paso MMP-BQ 3**: Listar eventos por rango")
        mmp_events_bq = gr.CheckboxGroup(choices=[], value=[], label="Eventos detectados (BigQuery)")
        bq_events_btn = gr.Button("Listar eventos por rango (BigQuery)")
        bq_events_msg = gr.Markdown()

        gr.Markdown("**Paso MMP-BQ 4**: Consultar y cargar MMP")
        mmp_preview_bq = gr.Dataframe(label="Preview MMP (BQ)", interactive=False)
        mmp_bq_download = gr.File(label="Descargar MMP (resultado de BigQuery)", interactive=False)
        mmp_final_path_bq = gr.Textbox(label="Ruta MMP final (temporal BQ)", visible=False)
        bq_query_btn = gr.Button("Consultar y cargar MMP (BigQuery)")
        bq_query_msg = gr.Markdown()

    # --- File Panel ---
    with gr.Column(visible=True) as file_panel:
        gr.Markdown("**Paso MMP-Archivo 1**: Subir y detectar columnas")
        mmp_file = gr.File(label="Subí MMP.xlsx/csv", file_types=[".xlsx", ".csv"])
        with gr.Row():
            file_time_col = gr.Dropdown(choices=[], value=None, label="Columna temporal (archivo)")
            mmp_event_col_file = gr.Dropdown(choices=[], value=None, label="Columna de EVENTO (archivo)")
            id_mmp_col_file = gr.Dropdown(choices=[], value=None, label="ID en MMP (archivo)")
            file_app_id_col = gr.Dropdown(choices=[], value=None, label="Columna App ID (archivo)")
        file_schema_btn = gr.Button("Obtener columnas (archivo)")
        file_schema_msg = gr.Markdown()

        gr.Markdown("**Paso MMP-Archivo 2**: (opcional) Listar eventos del archivo y filtrar")
        mmp_events_file = gr.CheckboxGroup(choices=[], value=[], label="Eventos detectados (archivo)")
        file_events_btn = gr.Button("Listar eventos (archivo)")
        file_events_msg = gr.Markdown()

        gr.Markdown("**Paso MMP-Archivo 3**: Cargar & previsualizar")
        mmp_preview_file = gr.Dataframe(label="Preview MMP (archivo)", interactive=False)
        mmp_file_download = gr.File(label="Descargar MMP (archivo filtrado)", interactive=False)
        mmp_final_path_file = gr.Textbox(label="Ruta MMP final (temporal archivo)", visible=False)
        file_query_btn = gr.Button("Cargar MMP (archivo)")
        file_query_msg = gr.Markdown()

    # Toggle panels
    def _toggle_source(src):
        return (gr.update(visible=(src=="Subir archivo")), gr.update(visible=(src=="BigQuery")))
    mmp_source.change(_toggle_source, inputs=[mmp_source], outputs=[file_panel, bq_panel])

    # BQ: listar App IDs
    def _bq_list_app_ids():
        try:
            vals, msg = bq_list_app_ids()
            return gr.update(choices=vals, value=(vals[0] if vals else None)), msg
        except Exception as e:
            return gr.update(choices=[], value=None), f"Error listando App IDs: {e}"
    list_app_ids_btn.click(_bq_list_app_ids, inputs=[], outputs=[bq_app_id_value, list_app_ids_msg])

    # BQ: schema
    def _bq_schema_fixed():
        try:
            cols, t_guess, e_guess, id_guess, appid_guess = bq_get_columns_fixed()
            return (gr.update(choices=cols, value=t_guess),
                    gr.update(choices=cols, value=e_guess),
                    gr.update(choices=cols, value=id_guess),
                    gr.update(choices=cols, value=appid_guess),
                    "Schema cargado (tabla fija BQ).")
        except Exception as e:
            return (gr.update(choices=[], value=None),
                    gr.update(choices=[], value=None),
                    gr.update(choices=[], value=None),
                    gr.update(choices=[], value=None),
                    f"Error schema: {e}")
    bq_schema_btn.click(_bq_schema_fixed, inputs=[], outputs=[bq_time_col, mmp_event_col_bq, id_mmp_col_bq, bq_app_id_col, bq_schema_msg])

    # BQ: listar eventos
    def _bq_list_events_fixed(ev_col, t_col, app_col, app_val, ds, de):
        try:
            vals, msg = bq_list_events_fixed(ev_col, t_col, app_col, app_val, ds, de)
            return gr.update(choices=vals, value=vals), msg
        except Exception as e:
            return gr.update(choices=[], value=[]), f"Error al listar eventos: {e}"
    bq_events_btn.click(_bq_list_events_fixed,
                        inputs=[mmp_event_col_bq, bq_time_col, bq_app_id_col, bq_app_id_value, bq_start, bq_end],
                        outputs=[mmp_events_bq, bq_events_msg])

    # BQ: query final
    def _bq_query_fixed(ev_col, t_col, app_col, app_val, ds, de, evs):
        try:
            path, preview_rows = bq_query_to_temp_fixed(ev_col, t_col, app_col, app_val, ds, de, evs or [])
            preview_df = pd.DataFrame(preview_rows)
            file_path = _safe_file_output(path)
            return preview_df, file_path, path, "OK: MMP desde BigQuery cargado."
        except Exception as e:
            return gr.update(), None, "", f"Error consulta BQ: {e}"
    bq_query_btn.click(_bq_query_fixed,
                       inputs=[mmp_event_col_bq, bq_time_col, bq_app_id_col, bq_app_id_value, bq_start, bq_end, mmp_events_bq],
                       outputs=[mmp_preview_bq, mmp_bq_download, mmp_final_path_bq, bq_query_msg])

    # File: schema & events
    file_schema_btn.click(file_mmp_schema,
                          inputs=[mmp_file],
                          outputs=[file_time_col, mmp_event_col_file, id_mmp_col_file, file_app_id_col, file_schema_msg])
    file_events_btn.click(file_mmp_list_events_simple,
                          inputs=[mmp_file, mmp_event_col_file],
                          outputs=[mmp_events_file, file_events_msg])

    # File: final
    def _file_query(src_file, ev_col, evs):
        try:
            path, preview = file_prepare(src_file, ev_col, evs or [])
            file_path = _safe_file_output(path)
            return preview, file_path, path, "OK: MMP desde archivo cargado."
        except Exception as e:
            return gr.update(), None, "", f"Error archivo MMP: {e}"
    file_query_btn.click(_file_query,
                         inputs=[mmp_file, mmp_event_col_file, mmp_events_file],
                         outputs=[mmp_preview_file, mmp_file_download, mmp_final_path_file, file_query_msg])

    # ===== CLIENTE =====
    gr.Markdown("## Fuente 2: CLIENTE")
    with gr.Row():
        cliente_file = gr.File(label="CLIENTE.xlsx/csv", file_types=[".xlsx", ".csv"])
        map_cliente_btn = gr.Button("Obtener mapeo de columnas (CLIENTE)")
    with gr.Row():
        id_cliente_col = gr.Dropdown(choices=[], value=None, label="ID en CLIENTE (para cruce)")
        validation_col_client = gr.Dropdown(choices=[], value=None, label="Columna de validación (CLIENTE) — opcional")
    with gr.Row():
        metric_col_client = gr.Dropdown(choices=[], value=None, label="Columna de métrica (CLIENTE) — opcional")
        client_event_col = gr.Dropdown(choices=[], value=None, label="Columna de EVENTO (CLIENTE) — opcional")
    cliente_msg = gr.Markdown()
    map_cliente_btn.click(cliente_map_columns,
                          inputs=[cliente_file],
                          outputs=[id_cliente_col, validation_col_client, metric_col_client, client_event_col, cliente_msg])

    gr.Markdown("### Opcional: valores de validación")
    valid_vals = gr.CheckboxGroup(choices=[], value=[], label="Valores que significan VALIDADO (CLIENTE)")
    load_valid_btn = gr.Button("Cargar valores de validación (CLIENTE)")
    valid_msg = gr.Markdown()
    load_valid_btn.click(load_validation_values,
                         inputs=[cliente_file, validation_col_client],
                         outputs=[valid_vals, valid_msg])

    # ===== Generar =====
    gr.Markdown("## Generar tablas y Excel")
    run_btn = gr.Button("Generar tablas")
    preview_out = gr.Dataframe(label="Preview: primera tabla por EVENTO", interactive=False)
    xls_file = gr.File(label="Descargar Excel (tablas_por_EVENTO + raw_merge)", interactive=False)
    gen_msg = gr.Markdown()

    def _compute_router(cliente,
                        source,
                        mmp_final_file_panel, mmp_final_bq_panel,
                        id_cli, id_mmp_file, id_mmp_bq,
                        val_col, val_vals,
                        metric_cli, cli_evt,
                        mmp_evt_file, mmp_evt_bq,
                        events_file, events_bq):

        if source == "Subir archivo":
            mmp_path = mmp_final_file_panel
            id_mmp = id_mmp_file
            mmp_evt_col = mmp_evt_file
            selected_events = events_file
        else:
            mmp_path = mmp_final_bq_panel
            id_mmp = id_mmp_bq
            mmp_evt_col = mmp_evt_bq
            selected_events = events_bq

        if not id_cli:
            return None, None, "Elegí el ID en CLIENTE."
        if not id_mmp or not mmp_evt_col:
            return None, None, "Elegí ID y EVENTO en MMP."

        return compute(cliente, mmp_path,
                       id_cli, id_mmp,
                       val_col, val_vals,
                       metric_cli,
                       cli_evt,           # puede ser None
                       mmp_evt_col,       # requerido
                       selected_events)

    run_btn.click(
        _compute_router,
        inputs=[cliente_file,
                mmp_source,
                mmp_final_path_file, mmp_final_path_bq,
                id_cliente_col, id_mmp_col_file, id_mmp_col_bq,
                validation_col_client, valid_vals,
                metric_col_client, client_event_col,
                mmp_event_col_file, mmp_event_col_bq,
                mmp_events_file, mmp_events_bq],
        outputs=[preview_out, xls_file, gen_msg]
    )

if __name__ == "__main__":
    gr.close_all()
    demo.launch()