import os
import re
import sqlite3
import datetime as dt
from pathlib import Path
from urllib.parse import quote_plus, urlparse

import pandas as pd
import gradio as gr

try:
    import requests
    from bs4 import BeautifulSoup
except Exception:
    requests = None
    BeautifulSoup = None

APP_TITLE = "ZAV HQ OS v2 — Prospection Engine"
DB_PATH = Path(os.getenv("ZAV_DB_PATH", "zav_hq.sqlite3"))
BACKUP_DIR = Path(os.getenv("ZAV_BACKUP_DIR", "backups"))
BACKUP_DIR.mkdir(exist_ok=True)

RESPONSIBLES = ["Julio", "Andreina", "Asistente", "Practicante 1", "Practicante 2", "Equipo"]
PRIORITIES = ["A - Alta", "B - Media", "C - Baja"]

PIPELINES = ["B2B", "SERVICIOS", "ENTRADAS"]
B2B_STATES = [
    "Investigado", "Listo para contacto", "Contactado", "Respondió",
    "Reunión agendada", "Reunión realizada", "Propuesta enviada",
    "Negociación", "Cerrado ganado", "Cerrado perdido", "Reactivar luego"
]
SERVICES_STATES = [
    "Nuevo", "Calificado", "No calificado", "Mensaje enviado",
    "Llamada agendada", "Diagnóstico realizado", "Propuesta enviada",
    "Cerrado ganado", "Cerrado perdido", "No ahora"
]
TICKETS_STATES = [
    "Nuevo", "Contactado", "Interesado", "Reservó", "Pagó",
    "Asistió", "No respondió", "Reactivar", "VIP / Recurrente"
]
TASK_STATES = ["Backlog", "Esta semana", "En progreso", "Esperando respuesta", "Hecho", "Bloqueado"]

PRODUCTS = [
    "ZAV Solo",
    "Masterclass + Performance",
    "ZAV Colaboración",
    "ZAV Premium / Banda",
    "Evento institucional",
    "Clases / Mentoría",
    "Producción musical",
    "Arreglos / composición",
    "Bajo sesionista",
    "Diagnóstico artístico",
    "Música audiovisual / sync",
]

ORG_TYPES = [
    "Universidad / escuela de música",
    "Centro cultural",
    "Festival jazz/world/latam",
    "Embajada / alianza cultural",
    "Productora de eventos",
    "Colegio privado / premium",
    "Estudio de grabación",
    "Academia de música",
    "Medio / podcast cultural",
    "Empresa / RRHH / eventos",
    "Comunidad peruana / latina",
    "Venue / sala",
    "Artista / manager / productor",
]

COUNTRY_PRESETS = {
    "Perú": ["Lima", "Arequipa", "Cusco", "Trujillo"],
    "Colombia": ["Bogotá", "Medellín", "Cali"],
    "Chile": ["Santiago", "Valparaíso", "Concepción"],
    "Argentina": ["Buenos Aires", "Córdoba", "Rosario"],
    "España": ["Barcelona", "Madrid", "Valencia"],
    "México": ["Ciudad de México", "Guadalajara", "Monterrey"],
    "Francia": ["París", "Lyon", "Toulouse"],
    "Estados Unidos": ["Miami", "New York", "Los Angeles", "Boston"],
}

SEARCH_TEMPLATES = {
    "Universidad / escuela de música": [
        '"{city}" universidad música contacto',
        'site:.edu "{city}" música masterclass contacto',
        '"{city}" conservatorio música contacto',
        '"{city}" escuela de música jazz contacto',
        '"{city}" music school masterclass contact',
    ],
    "Centro cultural": [
        '"{city}" centro cultural programación musical contacto',
        '"{city}" centro cultural jazz contacto',
        '"{city}" programación cultural música contacto',
        '"{city}" cultural center music programming contact',
    ],
    "Festival jazz/world/latam": [
        '"{country}" festival jazz contacto programación',
        '"{city}" festival jazz contacto',
        '"{country}" world music festival contact programming',
        '"{country}" festival música latinoamericana contacto',
    ],
    "Embajada / alianza cultural": [
        '"{city}" alianza cultural música contacto',
        '"{country}" embajada Perú cultura contacto',
        '"{city}" instituto cultural programación música',
        '"{city}" cultural institute music programming contact',
    ],
    "Productora de eventos": [
        '"{city}" productora eventos corporativos música contacto',
        '"{city}" eventos premium música en vivo contacto',
        '"{city}" agencia BTL eventos música contacto',
    ],
    "Academia de música": [
        '"{city}" academia música bajo eléctrico contacto',
        '"{city}" clases bajo eléctrico escuela música',
        '"{city}" producción musical academia contacto',
    ],
    "Estudio de grabación": [
        '"{city}" estudio de grabación artistas contacto',
        '"{city}" producción musical estudio contacto',
        '"{city}" recording studio contact artists',
    ],
    "Medio / podcast cultural": [
        '"{city}" podcast música cultura contacto',
        '"{country}" medio cultural música contacto',
        '"{country}" radio cultural jazz contacto',
    ],
    "Empresa / RRHH / eventos": [
        '"{city}" eventos corporativos premium música en vivo',
        '"{city}" empresas eventos culturales contacto',
        '"{city}" RRHH actividades culturales música',
    ],
    "Venue / sala": [
        '"{city}" jazz club contacto programación',
        '"{city}" sala conciertos jazz contacto',
        '"{city}" venue live music booking contact',
    ],
    "Artista / manager / productor": [
        '"{city}" productor musical artistas contacto',
        '"{city}" manager artistas música contacto',
        '"{city}" artistas independientes producción musical',
    ],
}

SCORING_RULES = [
    ("programación musical real", 3),
    ("masterclass / educación", 3),
    ("contacto directo", 2),
    ("ha programado jazz/world/latam", 3),
    ("presupuesto institucional", 3),
    ("ciudad prioritaria", 2),
    ("relación existente", 4),
    ("deadline cercano", 2),
    ("contacto con nombre propio", 2),
    ("web profesional", 1),
]

MESSAGE_TEMPLATES = {
    "B2B ES — programación": """Hola {name},

Soy Julio Zavala, bajista, compositor y productor peruano detrás de ZAV. Estoy abriendo conversaciones para programación cultural en formato concierto, masterclass y colaboración.

ZAV cruza música afroperuana, jazz/fusión, bajo eléctrico y electrónica en vivo. Te comparto el dossier:
https://zavglobal.com

¿Tendría sentido conversar 15 minutos para ver si puede encajar en la programación de {organization}?

Abrazo,
Julio Zavala""",

    "B2B EN — programming": """Hi {name},

I’m Julio Zavala, a Peruvian bassist, composer and producer behind ZAV, an Afro-Peruvian jazz/fusion project with national awards, cultural grants and presentations at major Peruvian stages and Berklee College of Music.

I’m opening conversations around ZAV Solo, Masterclass + Performance and collaboration-based programming.

Here is the dossier:
https://zavglobal.com

Would it make sense to have a brief 15-minute conversation to see if this could fit {organization}'s programming?

Best,
Julio Zavala""",

    "SERVICIOS — diagnóstico": """Hola {name}, gracias por escribirnos.

Vi que te interesa {product}. Para orientarte mejor, ¿me puedes contar en qué etapa está tu proyecto y qué resultado te gustaría conseguir en las próximas semanas?

Si tiene sentido, podemos agendar un diagnóstico breve.""",

    "ALIADO — comisión": """Hola {name}, ¿cómo estás?

Estoy abriendo una línea de servicios de ZAV para clases, mentorías, producción, arreglos y dirección artística. Si conoces artistas o músicos que necesiten ordenar su proyecto o producir material, podemos armar un esquema de referido.

Si el referido cierra un servicio, te reconocemos una comisión acordada previamente.

¿Te interesa que te pase el link y los tipos de servicio?""",

    "ENTRADAS — lista privada": """Hola {name}, gracias por sumarte a la lista de ZAV.

Te escribimos por {product}. Si quieres reservar o recibir próximas fechas, podemos ayudarte por aquí."""
}

EMAIL_RE = re.compile(r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
PHONE_RE = re.compile(r"(?:(?:\+|00)\d{1,3}[\s.-]?)?(?:\(?\d{2,4}\)?[\s.-]?)?\d{3,4}[\s.-]?\d{3,4}")

def now():
    return dt.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

def today():
    return dt.date.today().isoformat()

def connect():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def execute(sql, params=()):
    conn = connect()
    conn.execute(sql, params)
    conn.commit()
    conn.close()

def df_query(sql, params=()):
    conn = connect()
    df = pd.read_sql_query(sql, conn, params=params)
    conn.close()
    return df

def init_db():
    conn = connect()
    c = conn.cursor()

    c.execute("""
    CREATE TABLE IF NOT EXISTS leads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        updated_at TEXT,
        pipeline TEXT,
        campaign TEXT,
        name TEXT,
        organization TEXT,
        role TEXT,
        org_type TEXT,
        country TEXT,
        city TEXT,
        email TEXT,
        whatsapp TEXT,
        website TEXT,
        source TEXT,
        priority TEXT,
        score INTEGER DEFAULT 0,
        score_reason TEXT,
        product TEXT,
        angle TEXT,
        state TEXT,
        responsible TEXT,
        last_contact TEXT,
        next_action TEXT,
        next_action_date TEXT,
        estimated_value REAL DEFAULT 0,
        probability REAL DEFAULT 0,
        weighted_value REAL DEFAULT 0,
        budget TEXT,
        urgency TEXT,
        description TEXT,
        notes TEXT
    )
    """)

    c.execute("""
    CREATE TABLE IF NOT EXISTS campaigns (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        name TEXT UNIQUE,
        objective TEXT,
        market TEXT,
        product TEXT,
        landing TEXT,
        channel TEXT,
        status TEXT,
        owner TEXT,
        weekly_target INTEGER,
        notes TEXT
    )
    """)

    c.execute("""
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        updated_at TEXT,
        task TEXT,
        area TEXT,
        priority TEXT,
        responsible TEXT,
        state TEXT,
        due_date TEXT,
        dependency TEXT,
        notes TEXT
    )
    """)

    c.execute("""
    CREATE TABLE IF NOT EXISTS ads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        campaign TEXT,
        channel TEXT,
        date_start TEXT,
        date_end TEXT,
        budget REAL,
        landing TEXT,
        impressions INTEGER,
        clicks INTEGER,
        leads INTEGER,
        qualified_leads INTEGER,
        closings INTEGER,
        revenue REAL,
        notes TEXT
    )
    """)

    c.execute("""
    CREATE TABLE IF NOT EXISTS referrals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        ally_name TEXT,
        ally_contact TEXT,
        referred_name TEXT,
        referred_contact TEXT,
        product TEXT,
        commission TEXT,
        state TEXT,
        next_action TEXT,
        notes TEXT
    )
    """)

    c.execute("""
    CREATE TABLE IF NOT EXISTS finances (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        created_at TEXT,
        date TEXT,
        kind TEXT,
        line TEXT,
        client_provider TEXT,
        product_category TEXT,
        gross_amount REAL,
        cost REAL,
        net_amount REAL,
        payment_state TEXT,
        payment_date TEXT,
        notes TEXT
    )
    """)

    conn.commit()
    conn.close()

def weighted(estimated_value, probability):
    ev = float(estimated_value or 0)
    pr = float(probability or 0)
    if pr > 1:
        pr = pr / 100
    return ev, pr, ev * pr

def score_lead(org_type, city, email, website, notes, has_named_contact=False):
    text = " ".join([str(org_type or ""), str(city or ""), str(email or ""), str(website or ""), str(notes or "")]).lower()
    score = 0
    reasons = []

    if any(k in text for k in ["programación", "programming", "concierto", "concert", "festival", "jazz", "music", "música"]):
        score += 3; reasons.append("programación musical real +3")
    if any(k in text for k in ["universidad", "university", "escuela", "school", "masterclass", "workshop", "conservatorio"]):
        score += 3; reasons.append("masterclass / educación +3")
    if email:
        score += 2; reasons.append("contacto directo +2")
    if any(k in text for k in ["jazz", "world", "latam", "latin", "afro", "fusion", "fusión"]):
        score += 3; reasons.append("jazz/world/latam +3")
    if any(k in text for k in ["embajada", "alliance", "alianza", "instituto", "ministry", "ministerio", "corporativo", "empresa"]):
        score += 3; reasons.append("presupuesto institucional probable +3")
    if city and city.lower() in ["lima", "bogotá", "bogota", "medellín", "medellin", "santiago", "buenos aires", "barcelona", "madrid", "parís", "paris", "ciudad de méxico", "mexico city", "miami", "new york"]:
        score += 2; reasons.append("ciudad prioritaria +2")
    if has_named_contact:
        score += 2; reasons.append("contacto con nombre propio +2")
    if website:
        score += 1; reasons.append("web profesional +1")

    priority = "A - Alta" if score >= 10 else "B - Media" if score >= 5 else "C - Baja"
    return score, "; ".join(reasons), priority

def add_lead(pipeline, campaign, name, organization, role, org_type, country, city, email, whatsapp, website, source, product, angle, state, responsible, next_action, next_action_date, estimated_value, probability, budget, urgency, description, notes):
    score, score_reason, priority = score_lead(org_type, city, email, website, notes, bool(name))
    ev, pr, wv = weighted(estimated_value, probability)
    execute("""
    INSERT INTO leads(
        created_at, updated_at, pipeline, campaign, name, organization, role, org_type, country, city, email, whatsapp, website, source,
        priority, score, score_reason, product, angle, state, responsible, last_contact, next_action, next_action_date,
        estimated_value, probability, weighted_value, budget, urgency, description, notes
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), now(), pipeline, campaign, name, organization, role, org_type, country, city, email, whatsapp, website, source,
          priority, score, score_reason, product, angle, state, responsible, "", next_action, next_action_date, ev, pr, wv,
          budget, urgency, description, notes))
    return f"✅ Lead agregado. Score: {score} ({priority})"

def get_leads(pipeline="Todos", campaign="Todos", responsible="Todos", priority="Todos", state="Todos", search=""):
    conditions, params = [], []
    for col, val in [("pipeline", pipeline), ("campaign", campaign), ("responsible", responsible), ("priority", priority), ("state", state)]:
        if val != "Todos":
            conditions.append(f"{col} = ?")
            params.append(val)
    if search:
        conditions.append("(name LIKE ? OR organization LIKE ? OR email LIKE ? OR website LIKE ? OR notes LIKE ? OR city LIKE ? OR country LIKE ?)")
        s = f"%{search}%"
        params.extend([s, s, s, s, s, s, s])
    where = "WHERE " + " AND ".join(conditions) if conditions else ""
    return df_query(f"""
    SELECT id, created_at, pipeline, campaign, name, organization, org_type, country, city, email, whatsapp, website,
           priority, score, product, state, responsible, next_action, next_action_date, estimated_value, probability, weighted_value, score_reason, notes
    FROM leads
    {where}
    ORDER BY score DESC, weighted_value DESC, COALESCE(next_action_date, '9999-99-99') ASC, id DESC
    """, tuple(params))

def update_lead(id_, state, responsible, next_action, next_action_date, note):
    if not id_:
        return "⚠️ Falta ID."
    current = df_query("SELECT notes FROM leads WHERE id = ?", (int(id_),))
    if current.empty:
        return "⚠️ No encontré ese ID."
    notes = current.iloc[0]["notes"] or ""
    if note:
        notes += f"\n[{now()}] {note}"
    execute("""
    UPDATE leads SET updated_at=?, state=?, responsible=?, next_action=?, next_action_date=?, notes=?
    WHERE id=?
    """, (now(), state, responsible, next_action, next_action_date, notes, int(id_)))
    return f"✅ Lead {int(id_)} actualizado."

def campaign_names():
    df = df_query("SELECT name FROM campaigns ORDER BY name")
    return ["Todos"] + df["name"].tolist() if not df.empty else ["Todos"]

def add_campaign(name, objective, market, product, landing, channel, status, owner, weekly_target, notes):
    execute("""
    INSERT OR REPLACE INTO campaigns(created_at, name, objective, market, product, landing, channel, status, owner, weekly_target, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), name, objective, market, product, landing, channel, status, owner, int(weekly_target or 0), notes))
    return "✅ Campaña creada/actualizada."

def get_campaigns():
    return df_query("SELECT * FROM campaigns ORDER BY id DESC")

def generate_search_queries(country, city, org_type, product, limit_each=5):
    templates = SEARCH_TEMPLATES.get(org_type, [
        '"{city}" "{org_type}" contacto música',
        '"{country}" "{org_type}" programming contact',
        '"{city}" "{org_type}" cultural programming'
    ])
    rows = []
    for t in templates[:int(limit_each)]:
        q = t.format(country=country, city=city, org_type=org_type, product=product)
        rows.append({
            "query": q,
            "google": "https://www.google.com/search?q=" + quote_plus(q),
            "duckduckgo": "https://duckduckgo.com/?q=" + quote_plus(q),
            "producto_sugerido": product,
            "tipo": org_type,
            "ciudad": city,
            "pais": country
        })
    return pd.DataFrame(rows)

def batch_search_plan(countries_text, org_types_text, product):
    countries = [x.strip() for x in countries_text.splitlines() if x.strip()]
    types = [x.strip() for x in org_types_text.splitlines() if x.strip()]
    rows = []
    for country in countries:
        cities = COUNTRY_PRESETS.get(country, [country])
        for city in cities:
            for org_type in types:
                rows.extend(generate_search_queries(country, city, org_type, product, 3).to_dict("records"))
    return pd.DataFrame(rows)

def extract_from_urls(urls_text, default_country, default_city, default_org_type, campaign, product, responsible):
    if requests is None or BeautifulSoup is None:
        return "⚠️ Faltan requests/beautifulsoup4. Revisa requirements.txt.", pd.DataFrame()

    urls = [u.strip() for u in urls_text.splitlines() if u.strip()]
    rows = []
    headers = {"User-Agent": "Mozilla/5.0 ZAVProspector/1.0"}
    for url in urls:
        if not url.startswith("http"):
            url = "https://" + url
        try:
            r = requests.get(url, timeout=8, headers=headers)
            html = r.text[:300000]
            soup = BeautifulSoup(html, "html.parser")
            title = soup.title.get_text(" ", strip=True) if soup.title else urlparse(url).netloc
            text = soup.get_text(" ", strip=True)
            emails = sorted(set(EMAIL_RE.findall(text)))
            phones = sorted(set(PHONE_RE.findall(text)))[:5]
            email = emails[0] if emails else ""
            phone = phones[0] if phones else ""
            score, score_reason, priority = score_lead(default_org_type, default_city, email, url, text[:1000], False)
            rows.append({
                "organization": title[:120],
                "website": url,
                "email": email,
                "whatsapp": phone,
                "country": default_country,
                "city": default_city,
                "org_type": default_org_type,
                "product": product,
                "priority": priority,
                "score": score,
                "score_reason": score_reason,
                "notes": text[:600]
            })
        except Exception as e:
            rows.append({
                "organization": urlparse(url).netloc,
                "website": url,
                "email": "",
                "whatsapp": "",
                "country": default_country,
                "city": default_city,
                "org_type": default_org_type,
                "product": product,
                "priority": "C - Baja",
                "score": 0,
                "score_reason": f"Error extracción: {e}",
                "notes": ""
            })

    df = pd.DataFrame(rows)
    for _, row in df.iterrows():
        add_lead("B2B", campaign, "", row["organization"], "", row["org_type"], row["country"], row["city"], row["email"], row["whatsapp"], row["website"], "URL extractor", row["product"], "", "Investigado", responsible, "Revisar contacto y preparar mensaje", today(), 0, 0, "", "", "", row["notes"])
    return f"✅ Procesadas {len(rows)} URLs y agregadas al CRM.", df

def import_csv(file, pipeline, campaign, responsible):
    if file is None:
        return "⚠️ Sube un CSV/XLSX.", pd.DataFrame()
    try:
        if file.name.lower().endswith(".xlsx"):
            df = pd.read_excel(file.name)
        else:
            df = pd.read_csv(file.name)
    except Exception as e:
        return f"⚠️ No pude leer archivo: {e}", pd.DataFrame()

    colmap = {c.lower().strip(): c for c in df.columns}
    def pick(*names):
        for n in names:
            if n.lower() in colmap:
                return colmap[n.lower()]
        return None

    name_c = pick("nombre", "name", "full name", "persona")
    org_c = pick("organización", "organizacion", "organization", "empresa", "institución", "institucion")
    email_c = pick("email", "correo")
    wa_c = pick("whatsapp", "telefono", "teléfono", "phone")
    web_c = pick("web", "website", "url", "link")
    city_c = pick("ciudad", "city")
    country_c = pick("pais", "país", "country")
    type_c = pick("tipo", "org_type", "categoría", "categoria")
    service_c = pick("servicio", "producto", "service", "qué necesitas", "que necesitas", "concierto de interés", "concierto de interes")
    notes_c = pick("notas", "notes", "mensaje", "comentario", "description", "descripción", "descripcion")

    count = 0
    for _, row in df.iterrows():
        name = str(row[name_c]) if name_c and pd.notna(row[name_c]) else ""
        org = str(row[org_c]) if org_c and pd.notna(row[org_c]) else ""
        email = str(row[email_c]) if email_c and pd.notna(row[email_c]) else ""
        wa = str(row[wa_c]) if wa_c and pd.notna(row[wa_c]) else ""
        web = str(row[web_c]) if web_c and pd.notna(row[web_c]) else ""
        city = str(row[city_c]) if city_c and pd.notna(row[city_c]) else ""
        country = str(row[country_c]) if country_c and pd.notna(row[country_c]) else ""
        org_type = str(row[type_c]) if type_c and pd.notna(row[type_c]) else ""
        product = str(row[service_c]) if service_c and pd.notna(row[service_c]) else ""
        notes = str(row[notes_c]) if notes_c and pd.notna(row[notes_c]) else ""

        default_state = "Investigado" if pipeline == "B2B" else "Nuevo"
        add_lead(pipeline, campaign, name, org, "", org_type, country, city, email, wa, web, "CSV/Tally/Sheets", product, "", default_state, responsible, "Revisar y clasificar", today(), 0, 0, "", "", "", notes)
        count += 1
    return f"✅ Importados {count} leads a {pipeline}.", get_leads(pipeline=pipeline)

def generate_message(template, lead_id, custom_name="", custom_org="", custom_product=""):
    lead = None
    if lead_id:
        df = df_query("SELECT * FROM leads WHERE id = ?", (int(lead_id),))
        if not df.empty:
            lead = df.iloc[0].to_dict()
    name = custom_name or (lead.get("name") if lead else "") or "[Nombre]"
    org = custom_org or (lead.get("organization") if lead else "") or "[Organización]"
    product = custom_product or (lead.get("product") if lead else "") or "[Producto]"
    return MESSAGE_TEMPLATES[template].format(name=name, organization=org, product=product)

def daily_work_queue():
    leads = df_query("SELECT * FROM leads")
    tasks = df_query("SELECT * FROM tasks")
    today_s = today()

    if leads.empty:
        leads_due = pd.DataFrame()
        hot = pd.DataFrame()
    else:
        leads_due = leads[(leads["next_action_date"].fillna("") <= today_s) & (leads["next_action_date"].fillna("") != "")].sort_values(["priority", "score"], ascending=[True, False])
        hot = leads[(leads["priority"] == "A - Alta") | (leads["state"].isin(["Respondió", "Reunión agendada", "Propuesta enviada", "Negociación", "Calificado", "Reservó"]))].sort_values(["score", "weighted_value"], ascending=[False, False])

    jul = hot[hot["responsible"] == "Julio"].head(10) if not hot.empty else pd.DataFrame()
    andr = hot[hot["responsible"] == "Andreina"].head(10) if not hot.empty else pd.DataFrame()
    ops = leads_due[leads_due["responsible"].isin(["Asistente", "Practicante 1", "Practicante 2"])].head(20) if not leads_due.empty else pd.DataFrame()

    text = f"""QUEUE DIARIA ZAV — {today_s}

JULIO — decisiones / alto valor:
{_queue_lines(jul)}

ANDREINA — management / condiciones / formalización:
{_queue_lines(andr)}

ASISTENTE / PRACTICANTES — operación:
{_queue_lines(ops)}

Regla: toda fila trabajada debe terminar con nuevo estado + próxima acción + fecha.
"""
    return text, leads_due.head(50)

def _queue_lines(df):
    if df is None or df.empty:
        return "- Sin pendientes."
    lines = []
    for _, r in df.iterrows():
        lines.append(f"- ID {r['id']} | {r.get('organization') or r.get('name')} | {r.get('pipeline')} | {r.get('state')} | {r.get('next_action')} ({r.get('next_action_date')})")
    return "\n".join(lines)

def add_task(task, area, priority, responsible, state, due_date, dependency, notes):
    execute("""
    INSERT INTO tasks(created_at, updated_at, task, area, priority, responsible, state, due_date, dependency, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), now(), task, area, priority, responsible, state, due_date, dependency, notes))
    return "✅ Tarea agregada."

def get_tasks(state="Todos", responsible="Todos"):
    cond, params = [], []
    if state != "Todos":
        cond.append("state=?"); params.append(state)
    if responsible != "Todos":
        cond.append("responsible=?"); params.append(responsible)
    where = "WHERE " + " AND ".join(cond) if cond else ""
    return df_query(f"SELECT * FROM tasks {where} ORDER BY COALESCE(due_date,'9999-99-99'), id DESC", tuple(params))

def add_ads(campaign, channel, date_start, date_end, budget, landing, impressions, clicks, leads, qualified_leads, closings, revenue, notes):
    execute("""
    INSERT INTO ads(created_at, campaign, channel, date_start, date_end, budget, landing, impressions, clicks, leads, qualified_leads, closings, revenue, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), campaign, channel, date_start, date_end, float(budget or 0), landing, int(impressions or 0), int(clicks or 0), int(leads or 0), int(qualified_leads or 0), int(closings or 0), float(revenue or 0), notes))
    return "✅ Experimento de ads registrado."

def get_ads():
    df = df_query("SELECT * FROM ads ORDER BY id DESC")
    if df.empty:
        return df
    df["CPL"] = df.apply(lambda r: r["budget"] / r["leads"] if r["leads"] else None, axis=1)
    df["CAC"] = df.apply(lambda r: r["budget"] / r["closings"] if r["closings"] else None, axis=1)
    df["ROAS"] = df.apply(lambda r: r["revenue"] / r["budget"] if r["budget"] else None, axis=1)
    return df

def add_referral(ally_name, ally_contact, referred_name, referred_contact, product, commission, state, next_action, notes):
    execute("""
    INSERT INTO referrals(created_at, ally_name, ally_contact, referred_name, referred_contact, product, commission, state, next_action, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), ally_name, ally_contact, referred_name, referred_contact, product, commission, state, next_action, notes))
    return "✅ Referido registrado."

def get_referrals():
    return df_query("SELECT * FROM referrals ORDER BY id DESC")

def add_finance(date, kind, line, client_provider, product_category, gross_amount, cost, payment_state, payment_date, notes):
    gross = float(gross_amount or 0); c = float(cost or 0)
    execute("""
    INSERT INTO finances(created_at, date, kind, line, client_provider, product_category, gross_amount, cost, net_amount, payment_state, payment_date, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (now(), date, kind, line, client_provider, product_category, gross, c, gross-c, payment_state, payment_date, notes))
    return "✅ Movimiento agregado."

def get_finances():
    return df_query("SELECT * FROM finances ORDER BY date DESC, id DESC")

def dashboard():
    leads = df_query("SELECT * FROM leads")
    ads = get_ads()
    finances = get_finances()

    if leads.empty:
        summary = pd.DataFrame([{"Leads": 0, "B2B": 0, "Servicios": 0, "Entradas": 0, "A-Alta": 0, "Pipeline estimado": 0, "Pipeline ponderado": 0, "Acciones vencidas": 0}])
        hot = pd.DataFrame()
    else:
        due = leads[(leads["next_action_date"].fillna("") <= today()) & (leads["next_action_date"].fillna("") != "")]
        summary = pd.DataFrame([{
            "Leads": len(leads),
            "B2B": int((leads["pipeline"] == "B2B").sum()),
            "Servicios": int((leads["pipeline"] == "SERVICIOS").sum()),
            "Entradas": int((leads["pipeline"] == "ENTRADAS").sum()),
            "A-Alta": int((leads["priority"] == "A - Alta").sum()),
            "Pipeline estimado": round(leads["estimated_value"].fillna(0).sum(), 2),
            "Pipeline ponderado": round(leads["weighted_value"].fillna(0).sum(), 2),
            "Acciones vencidas": len(due)
        }])
        hot = leads.sort_values(["score", "weighted_value"], ascending=[False, False]).head(20)[["id","pipeline","campaign","name","organization","country","city","priority","score","product","state","responsible","next_action","next_action_date","weighted_value"]]

    report = generate_report()
    return summary, hot, report

def generate_report():
    leads = df_query("SELECT * FROM leads")
    campaigns = get_campaigns()
    ads = get_ads()

    if leads.empty:
        lead_part = "Sin leads todavía."
    else:
        lead_part = f"""Leads totales: {len(leads)}
B2B: {(leads['pipeline']=='B2B').sum()}
Servicios: {(leads['pipeline']=='SERVICIOS').sum()}
Entradas: {(leads['pipeline']=='ENTRADAS').sum()}
Alta prioridad: {(leads['priority']=='A - Alta').sum()}
Pipeline estimado: S/ {leads['estimated_value'].fillna(0).sum():,.2f}
Pipeline ponderado: S/ {leads['weighted_value'].fillna(0).sum():,.2f}"""

    hot = get_leads().head(10)
    hot_lines = []
    for _, r in hot.iterrows():
        hot_lines.append(f"- ID {r['id']} | {r['organization'] or r['name']} | {r['pipeline']} | score {r['score']} | {r['next_action']}")
    hot_part = "\n".join(hot_lines) if hot_lines else "Sin oportunidades."

    if ads.empty:
        ads_part = "Sin experimentos pagados registrados."
    else:
        ads_part = f"""Ads registrados: {len(ads)}
Presupuesto total: S/ {ads['budget'].fillna(0).sum():,.2f}
Leads por ads: {ads['leads'].fillna(0).sum()}
Cierres atribuidos: {ads['closings'].fillna(0).sum()}
Revenue atribuido: S/ {ads['revenue'].fillna(0).sum():,.2f}"""

    return f"""REPORTE EJECUTIVO ZAV HQ — {today()}

1. RESUMEN
{lead_part}

2. TOP OPORTUNIDADES / ACCIONES
{hot_part}

3. ADS / EXPERIMENTOS
{ads_part}

4. DECISIONES RECOMENDADAS
- Elegir 5 oportunidades A para Julio.
- Elegir 5 seguimientos formales para Andreina.
- Asignar 30 contactos nuevos a practicantes.
- Cortar campañas pagadas sin leads calificados.
- Registrar todo cierre en Finanzas.
"""

def export_all():
    stamp = dt.datetime.now().strftime("%Y%m%d_%H%M%S")
    out = BACKUP_DIR / f"zav_hq_os_v2_export_{stamp}.xlsx"
    with pd.ExcelWriter(out, engine="openpyxl") as writer:
        df_query("SELECT * FROM leads").to_excel(writer, "leads", index=False)
        df_query("SELECT * FROM campaigns").to_excel(writer, "campaigns", index=False)
        df_query("SELECT * FROM tasks").to_excel(writer, "tasks", index=False)
        df_query("SELECT * FROM ads").to_excel(writer, "ads", index=False)
        df_query("SELECT * FROM referrals").to_excel(writer, "referrals", index=False)
        df_query("SELECT * FROM finances").to_excel(writer, "finances", index=False)
    return str(out)

def backup_db():
    stamp = dt.datetime.now().strftime("%Y%m%d_%H%M%S")
    out = BACKUP_DIR / f"zav_hq_os_v2_{stamp}.sqlite3"
    if DB_PATH.exists():
        out.write_bytes(DB_PATH.read_bytes())
        return str(out)
    return None

init_db()

with gr.Blocks(title=APP_TITLE, theme=gr.themes.Soft()) as demo:
    gr.Markdown("""
    # ZAV HQ OS v2 — Prospection Engine
    **Objetivo:** encontrar, puntuar, contactar, medir y convertir contactos nuevos sin depender de redes sociales.

    Flujo: **fuentes públicas / ads / referidos / Tally → CRM → scoring → campaña → próxima acción → cierre**.
    """)

    with gr.Tab("Dashboard"):
        btn = gr.Button("Actualizar")
        summary = gr.Dataframe(label="Resumen ejecutivo", interactive=False)
        hot = gr.Dataframe(label="Top oportunidades", interactive=False)
        report = gr.Textbox(label="Reporte ejecutivo", lines=22)
        btn.click(dashboard, outputs=[summary, hot, report])
        demo.load(dashboard, outputs=[summary, hot, report])

    with gr.Tab("Lead Finder"):
        gr.Markdown("## 1) Generador de búsquedas para practicantes")
        with gr.Row():
            country = gr.Dropdown(list(COUNTRY_PRESETS.keys()) + ["Otro"], value="Colombia", label="País")
            city = gr.Textbox(value="Bogotá", label="Ciudad")
            org_type = gr.Dropdown(ORG_TYPES, value="Universidad / escuela de música", label="Tipo de organización")
            product = gr.Dropdown(PRODUCTS, value="Masterclass + Performance", label="Producto a vender")
        q_btn = gr.Button("Generar búsquedas")
        q_df = gr.Dataframe(label="Búsquedas listas para abrir", interactive=False)
        q_btn.click(generate_search_queries, inputs=[country, city, org_type, product], outputs=q_df)

        gr.Markdown("## 2) Plan masivo de prospección")
        countries_text = gr.Textbox(label="Países, uno por línea", lines=5, value="Colombia\nChile\nArgentina\nEspaña")
        types_text = gr.Textbox(label="Tipos de organización, uno por línea", lines=6, value="Universidad / escuela de música\nCentro cultural\nFestival jazz/world/latam\nEmbajada / alianza cultural")
        plan_product = gr.Dropdown(PRODUCTS, value="Masterclass + Performance", label="Producto")
        plan_btn = gr.Button("Crear plan de investigación")
        plan_df = gr.Dataframe(label="Plan para practicantes", interactive=False)
        plan_btn.click(batch_search_plan, inputs=[countries_text, types_text, plan_product], outputs=plan_df)

        gr.Markdown("## 3) Extractor desde URLs encontradas")
        urls = gr.Textbox(label="Pega URLs, una por línea", lines=8)
        with gr.Row():
            ex_country = gr.Textbox(label="País", value="Colombia")
            ex_city = gr.Textbox(label="Ciudad", value="Bogotá")
            ex_type = gr.Dropdown(ORG_TYPES, value="Universidad / escuela de música", label="Tipo")
        with gr.Row():
            ex_campaign = gr.Textbox(label="Campaña", value="Colombia universidades — masterclass")
            ex_product = gr.Dropdown(PRODUCTS, value="Masterclass + Performance", label="Producto")
            ex_resp = gr.Dropdown(RESPONSIBLES, value="Practicante 1", label="Responsable")
        ex_btn = gr.Button("Extraer emails/datos y cargar al CRM")
        ex_status = gr.Textbox(label="Resultado")
        ex_df = gr.Dataframe(label="Datos extraídos", interactive=False)
        ex_btn.click(extract_from_urls, inputs=[urls, ex_country, ex_city, ex_type, ex_campaign, ex_product, ex_resp], outputs=[ex_status, ex_df])

    with gr.Tab("CRM"):
        gr.Markdown("## Ver leads")
        campaigns_dropdown = gr.Dropdown(choices=campaign_names(), value="Todos", label="Campaña")
        with gr.Row():
            f_pipeline = gr.Dropdown(["Todos"] + PIPELINES, value="Todos", label="Pipeline")
            f_resp = gr.Dropdown(["Todos"] + RESPONSIBLES, value="Todos", label="Responsable")
            f_priority = gr.Dropdown(["Todos"] + PRIORITIES, value="Todos", label="Prioridad")
            f_state = gr.Dropdown(["Todos"] + sorted(set(B2B_STATES + SERVICES_STATES + TICKETS_STATES)), value="Todos", label="Estado")
        f_search = gr.Textbox(label="Buscar")
        leads_btn = gr.Button("Actualizar leads")
        leads_df = gr.Dataframe(label="Leads", interactive=False)
        leads_btn.click(get_leads, inputs=[f_pipeline, campaigns_dropdown, f_resp, f_priority, f_state, f_search], outputs=leads_df)
        demo.load(get_leads, inputs=[f_pipeline, campaigns_dropdown, f_resp, f_priority, f_state, f_search], outputs=leads_df)

        gr.Markdown("## Agregar lead manual")
        with gr.Row():
            l_pipeline = gr.Dropdown(PIPELINES, value="B2B", label="Pipeline")
            l_campaign = gr.Textbox(label="Campaña")
            l_name = gr.Textbox(label="Nombre")
            l_org = gr.Textbox(label="Organización")
        with gr.Row():
            l_role = gr.Textbox(label="Rol")
            l_type = gr.Dropdown(ORG_TYPES, value="Centro cultural", label="Tipo")
            l_country = gr.Textbox(label="País")
            l_city = gr.Textbox(label="Ciudad")
        with gr.Row():
            l_email = gr.Textbox(label="Email")
            l_wa = gr.Textbox(label="WhatsApp")
            l_web = gr.Textbox(label="Website")
        with gr.Row():
            l_source = gr.Textbox(label="Fuente", value="Manual")
            l_product = gr.Dropdown(PRODUCTS, value="ZAV Solo", label="Producto")
            l_state = gr.Dropdown(sorted(set(B2B_STATES + SERVICES_STATES + TICKETS_STATES)), value="Investigado", label="Estado")
            l_resp = gr.Dropdown(RESPONSIBLES, value="Practicante 1", label="Responsable")
        l_angle = gr.Textbox(label="Ángulo")
        with gr.Row():
            l_next = gr.Textbox(label="Próxima acción", value="Revisar y preparar contacto")
            l_next_date = gr.Textbox(label="Fecha próxima acción", value=today())
        with gr.Row():
            l_ev = gr.Number(label="Valor estimado", value=0)
            l_prob = gr.Number(label="Probabilidad", value=0)
            l_budget = gr.Textbox(label="Presupuesto")
            l_urgency = gr.Textbox(label="Urgencia")
        l_desc = gr.Textbox(label="Descripción", lines=2)
        l_notes = gr.Textbox(label="Notas", lines=3)
        l_add = gr.Button("Agregar lead")
        l_status = gr.Textbox(label="Resultado")
        l_add.click(add_lead, inputs=[l_pipeline,l_campaign,l_name,l_org,l_role,l_type,l_country,l_city,l_email,l_wa,l_web,l_source,l_product,l_angle,l_state,l_resp,l_next,l_next_date,l_ev,l_prob,l_budget,l_urgency,l_desc,l_notes], outputs=l_status)

        gr.Markdown("## Actualizar lead")
        with gr.Row():
            u_id = gr.Number(label="ID")
            u_state = gr.Dropdown(sorted(set(B2B_STATES + SERVICES_STATES + TICKETS_STATES)), value="Contactado", label="Estado")
            u_resp = gr.Dropdown(RESPONSIBLES, value="Julio", label="Responsable")
        u_next = gr.Textbox(label="Próxima acción")
        u_date = gr.Textbox(label="Fecha próxima acción", value=today())
        u_note = gr.Textbox(label="Nota", lines=3)
        u_btn = gr.Button("Actualizar")
        u_status = gr.Textbox(label="Resultado")
        u_btn.click(update_lead, inputs=[u_id, u_state, u_resp, u_next, u_date, u_note], outputs=u_status)

    with gr.Tab("Campañas"):
        gr.Markdown("## Crear / actualizar campaña")
        with gr.Row():
            c_name = gr.Textbox(label="Nombre campaña", value="Colombia universidades — masterclass")
            c_obj = gr.Textbox(label="Objetivo", value="Agendar llamadas con universidades para masterclass + performance")
            c_market = gr.Textbox(label="Mercado", value="Bogotá / Medellín")
        with gr.Row():
            c_product = gr.Dropdown(PRODUCTS, value="Masterclass + Performance", label="Producto")
            c_landing = gr.Textbox(label="Landing", value="https://zavglobal.com")
            c_channel = gr.Textbox(label="Canal", value="Email directo")
        with gr.Row():
            c_status = gr.Textbox(label="Estado", value="Activa")
            c_owner = gr.Dropdown(RESPONSIBLES, value="Julio", label="Owner")
            c_weekly = gr.Number(label="Meta contactos/semana", value=30)
        c_notes = gr.Textbox(label="Notas", lines=3)
        c_btn = gr.Button("Guardar campaña")
        c_status_out = gr.Textbox(label="Resultado")
        c_btn.click(add_campaign, inputs=[c_name,c_obj,c_market,c_product,c_landing,c_channel,c_status,c_owner,c_weekly,c_notes], outputs=c_status_out)
        camp_refresh = gr.Button("Ver campañas")
        camp_df = gr.Dataframe(label="Campañas", interactive=False)
        camp_refresh.click(get_campaigns, outputs=camp_df)
        demo.load(get_campaigns, outputs=camp_df)

    with gr.Tab("Importar Tally / CSV"):
        file = gr.File(label="CSV/XLSX desde Tally, Google Sheets o investigación")
        with gr.Row():
            im_pipeline = gr.Dropdown(PIPELINES, value="SERVICIOS", label="Pipeline")
            im_campaign = gr.Textbox(label="Campaña", value="Servicios web")
            im_resp = gr.Dropdown(RESPONSIBLES, value="Asistente", label="Responsable")
        im_btn = gr.Button("Importar al CRM")
        im_status = gr.Textbox(label="Resultado")
        im_df = gr.Dataframe(label="Vista", interactive=False)
        im_btn.click(import_csv, inputs=[file, im_pipeline, im_campaign, im_resp], outputs=[im_status, im_df])

    with gr.Tab("Mensajes"):
        with gr.Row():
            m_template = gr.Dropdown(list(MESSAGE_TEMPLATES.keys()), value="B2B ES — programación", label="Plantilla")
            m_id = gr.Number(label="ID lead opcional")
        with gr.Row():
            m_name = gr.Textbox(label="Nombre manual")
            m_org = gr.Textbox(label="Organización manual")
            m_product = gr.Textbox(label="Producto manual")
        m_btn = gr.Button("Generar mensaje")
        m_out = gr.Textbox(label="Mensaje", lines=16)
        m_btn.click(generate_message, inputs=[m_template, m_id, m_name, m_org, m_product], outputs=m_out)

    with gr.Tab("Work Queue"):
        q_btn = gr.Button("Generar queue diaria")
        q_text = gr.Textbox(label="Queue diaria", lines=24)
        q_due = gr.Dataframe(label="Acciones vencidas / hoy", interactive=False)
        q_btn.click(daily_work_queue, outputs=[q_text, q_due])

    with gr.Tab("Ads Tracker"):
        with gr.Row():
            a_campaign = gr.Textbox(label="Campaña")
            a_channel = gr.Dropdown(["Meta Ads", "Google Ads", "LinkedIn", "Otro"], value="Meta Ads", label="Canal")
            a_landing = gr.Textbox(label="Landing", value="https://zavglobal.com/servicios/")
        with gr.Row():
            a_start = gr.Textbox(label="Inicio", value=today())
            a_end = gr.Textbox(label="Fin")
            a_budget = gr.Number(label="Presupuesto", value=0)
        with gr.Row():
            a_impressions = gr.Number(label="Impresiones", value=0)
            a_clicks = gr.Number(label="Clicks", value=0)
            a_leads = gr.Number(label="Leads", value=0)
            a_qleads = gr.Number(label="Leads calificados", value=0)
            a_closings = gr.Number(label="Cierres", value=0)
            a_revenue = gr.Number(label="Ingreso atribuido", value=0)
        a_notes = gr.Textbox(label="Notas", lines=3)
        a_btn = gr.Button("Registrar experimento")
        a_status = gr.Textbox(label="Resultado")
        a_btn.click(add_ads, inputs=[a_campaign,a_channel,a_start,a_end,a_budget,a_landing,a_impressions,a_clicks,a_leads,a_qleads,a_closings,a_revenue,a_notes], outputs=a_status)
        a_refresh = gr.Button("Ver ads")
        a_df = gr.Dataframe(label="Ads con CPL/CAC/ROAS", interactive=False)
        a_refresh.click(get_ads, outputs=a_df)
        demo.load(get_ads, outputs=a_df)

    with gr.Tab("Referidos"):
        with gr.Row():
            r_ally = gr.Textbox(label="Aliado")
            r_contact = gr.Textbox(label="Contacto aliado")
            r_ref = gr.Textbox(label="Referido")
            r_ref_contact = gr.Textbox(label="Contacto referido")
        with gr.Row():
            r_product = gr.Dropdown(PRODUCTS, value="Producción musical", label="Producto")
            r_comm = gr.Textbox(label="Comisión", value="10% si cierra")
            r_state = gr.Textbox(label="Estado", value="Nuevo")
        r_next = gr.Textbox(label="Próxima acción")
        r_notes = gr.Textbox(label="Notas", lines=3)
        r_btn = gr.Button("Registrar referido")
        r_status = gr.Textbox(label="Resultado")
        r_btn.click(add_referral, inputs=[r_ally,r_contact,r_ref,r_ref_contact,r_product,r_comm,r_state,r_next,r_notes], outputs=r_status)
        r_refresh = gr.Button("Ver referidos")
        r_df = gr.Dataframe(label="Referidos", interactive=False)
        r_refresh.click(get_referrals, outputs=r_df)
        demo.load(get_referrals, outputs=r_df)

    with gr.Tab("Tareas"):
        t_task = gr.Textbox(label="Tarea")
        with gr.Row():
            t_area = gr.Textbox(label="Área", value="Prospección")
            t_priority = gr.Dropdown(PRIORITIES, value="B - Media", label="Prioridad")
            t_resp = gr.Dropdown(RESPONSIBLES, value="Practicante 1", label="Responsable")
            t_state = gr.Dropdown(TASK_STATES, value="Backlog", label="Estado")
        with gr.Row():
            t_due = gr.Textbox(label="Fecha límite", value=today())
            t_dep = gr.Textbox(label="Dependencia")
        t_notes = gr.Textbox(label="Notas", lines=3)
        t_btn = gr.Button("Agregar tarea")
        t_status = gr.Textbox(label="Resultado")
        t_btn.click(add_task, inputs=[t_task,t_area,t_priority,t_resp,t_state,t_due,t_dep,t_notes], outputs=t_status)
        t_refresh = gr.Button("Ver tareas")
        t_df = gr.Dataframe(label="Tareas", interactive=False)
        t_refresh.click(get_tasks, inputs=[gr.Dropdown(["Todos"]+TASK_STATES, value="Todos", visible=False), gr.Dropdown(["Todos"]+RESPONSIBLES, value="Todos", visible=False)], outputs=t_df)

    with gr.Tab("Finanzas / Export"):
        gr.Markdown("## Finanzas")
        with gr.Row():
            f_date = gr.Textbox(label="Fecha", value=today())
            f_kind = gr.Dropdown(["Ingreso", "Egreso"], value="Ingreso", label="Tipo")
            f_line = gr.Dropdown(["Escenario", "Academia", "Estudio", "B2B", "Ads", "Operaciones", "Otros"], value="Academia", label="Línea")
        with gr.Row():
            f_client = gr.Textbox(label="Cliente/proveedor")
            f_prod = gr.Textbox(label="Producto/categoría")
            f_gross = gr.Number(label="Bruto", value=0)
            f_cost = gr.Number(label="Costo", value=0)
        with gr.Row():
            f_pstate = gr.Dropdown(["Pendiente", "Pagado", "Parcial", "Por cobrar", "Por pagar"], value="Pendiente", label="Estado pago")
            f_pdate = gr.Textbox(label="Fecha pago")
        f_notes = gr.Textbox(label="Notas", lines=3)
        f_btn = gr.Button("Agregar movimiento")
        f_status = gr.Textbox(label="Resultado")
        f_btn.click(add_finance, inputs=[f_date,f_kind,f_line,f_client,f_prod,f_gross,f_cost,f_pstate,f_pdate,f_notes], outputs=f_status)
        f_refresh = gr.Button("Ver finanzas")
        f_df = gr.Dataframe(label="Finanzas", interactive=False)
        f_refresh.click(get_finances, outputs=f_df)

        gr.Markdown("## Export / Backup")
        x_btn = gr.Button("Exportar Excel")
        x_file = gr.File(label="Excel")
        x_btn.click(export_all, outputs=x_file)
        b_btn = gr.Button("Backup SQLite")
        b_file = gr.File(label="DB")
        b_btn.click(backup_db, outputs=b_file)

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