Spaces:
Sleeping
Sleeping
| import gradio as gr | |
| import pandas as pd | |
| import requests | |
| import re | |
| import sqlite3 | |
| import os | |
| import numpy as np | |
| from datetime import datetime, timedelta | |
| from urllib.parse import urlparse, urlunparse, quote | |
| # --- 1. CONFIGURACIÓN --- | |
| API_KEY = os.getenv("GOOGLE_API_KEY") | |
| SEARCH_ENGINE_ID = os.getenv("SEARCH_ENGINE_ID") | |
| DB_NAME = "data_cache_v14.db" | |
| # --- 2. GESTIÓN DE BASE DE DATOS --- | |
| def iniciar_db(): | |
| conn = sqlite3.connect(DB_NAME) | |
| c = conn.cursor() | |
| c.execute('''CREATE TABLE IF NOT EXISTS inmuebles | |
| (id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| query_busqueda TEXT, titulo TEXT, precio REAL, | |
| area REAL, habs INTEGER, banos INTEGER, garajes INTEGER, | |
| estrato INTEGER, seguridad INTEGER, antiguedad INTEGER, | |
| lat TEXT, lon TEXT, | |
| fuente TEXT, url TEXT, es_directo INTEGER, fecha_registro DATE)''') | |
| conn.commit() | |
| conn.close() | |
| def guardar_cache(query, datos): | |
| if not datos: return | |
| conn = sqlite3.connect(DB_NAME) | |
| c = conn.cursor() | |
| hoy = datetime.now().date() | |
| for d in datos: | |
| c.execute('''INSERT INTO inmuebles | |
| (query_busqueda, titulo, precio, area, habs, banos, garajes, estrato, seguridad, antiguedad, lat, lon, fuente, url, es_directo, fecha_registro) | |
| VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', | |
| (query, d['titulo'], d['precio'], d['area'], d['habs'], d['banos'], d['garajes'], d['estrato'], d['seguridad'], d['antiguedad'], d['lat'], d['lon'], d['fuente'], d['url'], d['es_directo'], hoy)) | |
| conn.commit() | |
| conn.close() | |
| def leer_cache(query): | |
| conn = sqlite3.connect(DB_NAME) | |
| limite = (datetime.now() - timedelta(days=7)).date() | |
| df = pd.read_sql_query("SELECT * FROM inmuebles WHERE query_busqueda = ? AND fecha_registro >= ?", | |
| conn, params=(query, limite)) | |
| conn.close() | |
| return df if not df.empty else None | |
| # --- 3. EXTRACCIÓN Y VALIDACIÓN --- | |
| def analizar_tipo_url(url): | |
| url = url.lower() | |
| positivos = ['/inmueble/', '/proyecto/', '/propiedad/', 'detalle', 'p-', 'id-', 'cod-', 'mco-', 'mla-'] | |
| if any(p in url for p in positivos): return 1 | |
| negativos = ['listado', 'resultados', 'buscar', 'search', 'ordenar', 'filtrar', 'page'] | |
| if any(n in url for n in negativos): return 0 | |
| return 0 | |
| def limpiar_url(url): | |
| try: | |
| parsed = urlparse(url) | |
| clean = urlunparse((parsed.scheme, parsed.netloc, parsed.path, '', '', '')) | |
| return clean | |
| except: | |
| return url | |
| def extraer_coordenadas(item): | |
| lat, lon = None, None | |
| pagemap = item.get('pagemap', {}) | |
| metatags = pagemap.get('metatags', [{}])[0] | |
| if 'og:latitude' in metatags: | |
| lat = metatags.get('og:latitude') | |
| lon = metatags.get('og:longitude') | |
| elif 'geo.position' in metatags: | |
| try: | |
| parts = metatags['geo.position'].split(';') | |
| lat, lon = parts[0], parts[1] | |
| except: pass | |
| if not lat and 'geocoordinates' in pagemap: | |
| geo = pagemap['geocoordinates'][0] | |
| lat = geo.get('latitude') | |
| lon = geo.get('longitude') | |
| return lat, lon | |
| def parsear_texto_completo(texto): | |
| texto = texto.lower() | |
| precio = 0 | |
| match_precio = re.search(r'\$\s?([\d.,]+)', texto) | |
| if match_precio: | |
| s = match_precio.group(1).replace('.','').replace(',','').strip() | |
| try: precio = float(s) | |
| except: pass | |
| area = 0 | |
| match_area = re.search(r'(\d+[\.,]?\d*)\s?(m2|mt|mts|metro)', texto) | |
| if match_area: | |
| s_area = match_area.group(1).replace(',', '.') | |
| try: area = float(s_area) | |
| except: pass | |
| habs = 0 | |
| match_habs = re.search(r'(\d+)\s?(hab|alcoba|dormitorio)', texto) | |
| if match_habs: | |
| try: habs = int(match_habs.group(1)) | |
| except: pass | |
| banos = 0 | |
| match_banos = re.search(r'(\d+)\s?(baño|bano)', texto) | |
| if match_banos: | |
| try: banos = int(match_banos.group(1)) | |
| except: pass | |
| garajes = 0 | |
| match_garaje = re.search(r'(\d+)\s?(parqueadero|garaje)', texto) | |
| if match_garaje: | |
| try: garajes = int(match_garaje.group(1)) | |
| except: pass | |
| elif "garaje" in texto or "parqueadero" in texto: | |
| garajes = 1 | |
| estrato = 0 | |
| match_estrato = re.search(r'estrato\s?:?\s?(\d)', texto) | |
| if match_estrato: | |
| try: estrato = int(match_estrato.group(1)) | |
| except: pass | |
| antiguedad = -1 | |
| if "estrenar" in texto or "nuevo" in texto or "sobre planos" in texto: | |
| antiguedad = 0 | |
| else: | |
| match_anos = re.search(r'(\d+)\s?(año|ano)', texto) | |
| if match_anos: | |
| try: antiguedad = int(match_anos.group(1)) | |
| except: pass | |
| seguridad = 0 | |
| if any(k in texto for k in ['conjunto', 'vigilancia', 'porteria', 'seguridad', 'club house', 'cerrado']): | |
| seguridad = 1 | |
| return precio, area, habs, banos, garajes, estrato, seguridad, antiguedad | |
| # --- 4. CONEXIÓN GOOGLE API --- | |
| def buscar_google(query): | |
| if not API_KEY or not SEARCH_ENGINE_ID: return [] | |
| url = "https://www.googleapis.com/customsearch/v1" | |
| query_optimizada = f"{query} detalle" | |
| query_optimizada = query_optimizada.replace(",", " OR ") | |
| params = {'key': API_KEY, 'cx': SEARCH_ENGINE_ID, 'q': query_optimizada, 'num': 10} | |
| try: | |
| resp = requests.get(url, params=params) | |
| if resp.status_code == 429: return [{"error": "quota"}] | |
| data = resp.json() | |
| resultados = [] | |
| if 'items' in data: | |
| for item in data['items']: | |
| raw_link = item.get('link', '') | |
| es_directo = analizar_tipo_url(raw_link) | |
| final_link = limpiar_url(raw_link) | |
| texto = f"{item.get('title')} {item.get('snippet')}" | |
| precio, area, habs, banos, garajes, estrato, seguridad, antiguedad = parsear_texto_completo(texto) | |
| lat, lon = extraer_coordenadas(item) | |
| fuente = "Web" | |
| if "fincaraiz" in raw_link: fuente = "Finca Raíz" | |
| elif "metrocuadrado" in raw_link: fuente = "Metrocuadrado" | |
| elif "wasi" in raw_link: fuente = "Wasi" | |
| if precio > 0 or area > 0: | |
| resultados.append({ | |
| 'titulo': item.get('title'), | |
| 'precio': precio, 'area': area, 'habs': habs, 'banos': banos, | |
| 'garajes': garajes, 'estrato': estrato, 'seguridad': seguridad, | |
| 'antiguedad': antiguedad, 'lat': lat, 'lon': lon, | |
| 'fuente': fuente, 'url': final_link, 'es_directo': es_directo | |
| }) | |
| return resultados | |
| except Exception as e: | |
| print(f"Error: {e}") | |
| return [] | |
| # --- 5. ALGORITMO DE AFINIDAD --- | |
| def calcular_scores(df, p_ref, a_ref, h_ref, b_ref, g_ref, e_ref, antiguedad_ref, buscar_condominio): | |
| if df.empty: return df | |
| df_f = df.copy() | |
| # Precios y Áreas | |
| df_f['diff_p'] = abs(df_f['precio'] - p_ref) / p_ref | |
| score_p = np.maximum(0, 1 - df_f['diff_p']) | |
| df_f['diff_a'] = df_f['area'].apply(lambda x: abs(x - a_ref)/a_ref if x > 0 else 1.0) | |
| score_a = np.maximum(0, 1 - df_f['diff_a']) | |
| # Habitaciones/Baños | |
| score_h = df_f['habs'].apply(lambda x: 1.0 if x == h_ref else (0.9 if x==0 else (0.5 if abs(x-h_ref)<=1 else 0))) | |
| score_b = df_f['banos'].apply(lambda x: 1.0 if x == b_ref else (0.9 if x==0 else (0.6 if abs(x-b_ref)<=1 else 0.2))) | |
| # Garajes/Estrato | |
| score_g = df_f['garajes'].apply(lambda x: 1.0 if x >= g_ref else (0.5 if x < g_ref and x > 0 else 0.8 if x==0 else 0)) | |
| score_e = df_f['estrato'].apply(lambda x: 1.0 if x == e_ref else (0.9 if x==0 else (0.5 if abs(x-e_ref)<=1 else 0))) | |
| # Antigüedad/Condominio | |
| def calc_edad(x, ref): | |
| if x == -1: return 0.8 | |
| if ref == 0: return 1.0 if x == 0 else max(0, 1 - (x/20)) | |
| return max(0, 1 - (abs(x - ref) / 20)) | |
| score_ant = df_f['antiguedad'].apply(lambda x: calc_edad(x, antiguedad_ref)) | |
| def calc_condo(x, quiere_condo): | |
| if not quiere_condo: return 1.0 | |
| return 1.0 if x == 1 else 0.2 | |
| score_c = df_f['seguridad'].apply(lambda x: calc_condo(x, buscar_condominio)) | |
| # SCORE FINAL | |
| df_f['score'] = (score_p * 25) + (score_a * 20) + (score_h * 10) + \ | |
| (score_b * 10) + (score_g * 10) + (score_e * 10) + \ | |
| (score_ant * 10) + (score_c * 5) | |
| df_f['score'] = df_f['score'].clip(0, 100).round(1) | |
| return df_f.sort_values(by='score', ascending=False) | |
| # --- 6. MOTOR PRINCIPAL --- | |
| def motor(zona, tipo, precio, area, habs, banos, garajes, estrato, antiguedad, es_condominio): | |
| css_injection = """ | |
| <style> | |
| .result-container { | |
| font-family: 'Segoe UI', sans-serif; | |
| background-color: #ffffff !important; | |
| color: #000000 !important; | |
| padding: 10px; | |
| border-radius: 8px; | |
| } | |
| .result-container * { color: #000000 !important; } | |
| .result-container .white-text { color: #ffffff !important; } | |
| .result-container a { text-decoration: none; font-weight: 700; } | |
| .card-result { | |
| background-color: #ffffff !important; | |
| border: 1px solid #e5e7eb; | |
| border-radius: 8px; | |
| padding: 15px; | |
| margin-bottom: 12px; | |
| box-shadow: 0 2px 4px rgba(0,0,0,0.05); | |
| } | |
| .btn-direct { color: #2563eb !important; } | |
| .btn-list { color: #d97706 !important; font-style: italic; } | |
| .map-btn { | |
| background-color: #ea4335 !important; | |
| color: white !important; | |
| padding: 4px 10px; | |
| border-radius: 15px; | |
| font-size: 0.8em; | |
| display: inline-block; | |
| margin-top: 5px; | |
| } | |
| .map-btn-gray { | |
| background-color: #5f6368 !important; | |
| color: white !important; | |
| padding: 4px 10px; | |
| border-radius: 15px; | |
| font-size: 0.8em; | |
| display: inline-block; | |
| margin-top: 5px; | |
| } | |
| </style> | |
| <div class="result-container"> | |
| """ | |
| if not API_KEY: return f"{css_injection}<h3 style='color:red !important;'>⚠️ Error: Faltan API Keys.</h3></div>" | |
| q = f"venta {tipo} {zona.lower()}" | |
| df = leer_cache(q) | |
| origen = "⚡ Caché" | |
| if df is None: | |
| origen = "🌐 Google API" | |
| lista = buscar_google(q) | |
| if lista and "error" in lista[0]: | |
| return f"{css_injection}<h3 style='color:red !important;'>⚠️ Límite de Cuota Excedido.</h3></div>" | |
| if lista: | |
| guardar_cache(q, lista) | |
| df = pd.DataFrame(lista) | |
| if df is None or df.empty: | |
| return f"{css_injection}<h3>❌ No se encontraron datos válidos.</h3></div>" | |
| df_similares = calcular_scores(df, precio, area, habs, banos, garajes, estrato, antiguedad, es_condominio) | |
| # Matemáticas Blindadas | |
| df_calc = df_similares[(df_similares['score'] >= 40) & (df_similares['area'] > 10) & (df_similares['precio'] > 1000000)] | |
| if df_calc.empty: df_calc = df_similares[df_similares['area'] > 10] | |
| prom_precio = df_calc['precio'].mean() if not df_calc.empty else 0 | |
| if not df_calc.empty: | |
| df_calc['m2_individual'] = df_calc['precio'] / df_calc['area'] | |
| prom_m2 = df_calc['m2_individual'].mean() | |
| else: | |
| prom_m2 = 0 | |
| html = f"{css_injection}" | |
| html += f""" | |
| <div style='background-color: #f0fdf4 !important; border-left: 5px solid #22c55e; padding: 15px; margin-bottom: 20px; border-radius: 4px;'> | |
| <h3 style='margin-top:0;'>🎯 Valuación A.V.M.: {zona}</h3> | |
| <ul> | |
| <li>Precio Sugerido: <b>${prom_precio:,.0f}</b></li> | |
| <li>Valor m²: <b>${prom_m2:,.0f}/m²</b></li> | |
| </ul> | |
| <small style='color:#555 !important;'>Fuente: {origen}</small> | |
| </div> | |
| """ | |
| for _, row in df_similares.iterrows(): | |
| score = row['score'] | |
| if score >= 80: color_bg = "#16a34a" | |
| elif score >= 60: color_bg = "#ca8a04" | |
| else: color_bg = "#dc2626" | |
| txt_area = f"{row['area']} m²" if row['area'] > 0 else "N/A" | |
| # --- LÓGICA DE MAPA INTELIGENTE --- | |
| if row['lat'] and row['lon']: | |
| # Coordenadas exactas encontradas | |
| gmaps_link = f"https://www.google.com/maps/search/?api=1&query={row['lat']},{row['lon']}" | |
| btn_mapa = f"<a href='{gmaps_link}' target='_blank' class='map-btn white-text'>📍 Ver Ubicación Exacta</a>" | |
| else: | |
| # Búsqueda Inversa por Título (Fallback) | |
| titulo_safe = quote(f"{row['titulo']} {zona}") | |
| gmaps_link = f"https://www.google.com/maps/search/?api=1&query={titulo_safe}" | |
| btn_mapa = f"<a href='{gmaps_link}' target='_blank' class='map-btn-gray white-text'>📍 Buscar en Mapa</a>" | |
| # ---------------------------------- | |
| if row['es_directo'] == 1: | |
| btn_link = f"<a href='{row['url']}' target='_blank' class='btn-direct'>🔗 Ver Inmueble</a>" | |
| else: | |
| btn_link = f"<a href='{row['url']}' target='_blank' class='btn-list'>⚠️ Ver Listado de Zona</a>" | |
| extras = [] | |
| if row['habs']>0: extras.append(f"🛏️ {row['habs']} Hb") | |
| if row['banos']>0: extras.append(f"🚿 {row['banos']} Ba") | |
| if row['garajes']>0: extras.append(f"🚗 {row['garajes']} Pq") | |
| if row['estrato']>0: extras.append(f"💎 E{row['estrato']}") | |
| if row['antiguedad']==0: extras.append("🌟 Nuevo") | |
| elif row['antiguedad']>0: extras.append(f"⏱️ {row['antiguedad']}a") | |
| str_extras = " | ".join(extras) | |
| badge_seg = "<span class='white-text' style='background:#4b5563; padding:2px 6px; border-radius:4px; font-size:0.75em; margin-left:5px;'>🛡️ Condominio</span>" if row['seguridad'] else "" | |
| html += f""" | |
| <div class="card-result"> | |
| <div style="display:flex; justify-content:space-between; align-items:start;"> | |
| <div style="flex:1;"> | |
| <div style="font-weight:bold; font-size:1.1em; margin-bottom:4px;"> | |
| {row['titulo']} {badge_seg} | |
| </div> | |
| <div style="margin-bottom:6px;"> | |
| <span style="font-size:1.2em; font-weight:800;">💰 ${row['precio']:,.0f}</span> | |
| <span style="background:#f3f4f6; padding:2px 8px; border-radius:4px; font-weight:600;">📐 {txt_area}</span> | |
| </div> | |
| <div style="font-size:0.95em;">{str_extras}</div> | |
| <div>{btn_mapa}</div> | |
| </div> | |
| <div style="text-align:right; min-width:90px;"> | |
| <span class="white-text" style="background:{color_bg}; padding:5px 10px; border-radius:20px; font-weight:bold; font-size:0.9em; display:inline-block;"> | |
| {score}% | |
| </span> | |
| <br><small style="font-size:0.75em; color:#666 !important;">{row['fuente']}</small> | |
| </div> | |
| </div> | |
| <div style="margin-top:10px; border-top:1px solid #f3f4f6; padding-top:8px;"> | |
| {btn_link} | |
| </div> | |
| </div> | |
| """ | |
| html += "</div>" | |
| return html | |
| # --- 7. INTERFAZ --- | |
| iniciar_db() | |
| with gr.Blocks(theme=gr.themes.Base()) as demo: | |
| gr.Markdown("# 🏢 Valuador Inteligente V14 (Mapa Activo)") | |
| with gr.Row(): | |
| with gr.Column(scale=2): | |
| inp_zona = gr.Textbox(label="📍 Zona", placeholder="Ej: Salitre, Modelia") | |
| with gr.Column(scale=1): | |
| inp_tipo = gr.Dropdown(["Apartamento", "Casa", "Lote", "Oficina"], label="Tipo", value="Apartamento") | |
| with gr.Row(): | |
| inp_precio = gr.Number(label="💰 Precio Ref.", value=300000000) | |
| inp_area = gr.Number(label="📐 Área (m²)", value=60) | |
| inp_estrato = gr.Dropdown([1,2,3,4,5,6], label="💎 Estrato", value=4) | |
| with gr.Row(): | |
| inp_habs = gr.Slider(1, 6, value=3, step=1, label="🛏️ Habitaciones") | |
| inp_banos = gr.Slider(1, 6, value=2, step=1, label="🚿 Baños") | |
| inp_garajes = gr.Slider(0, 3, value=1, step=1, label="🚗 Garajes") | |
| with gr.Row(): | |
| inp_antiguedad = gr.Number(label="⏱️ Antigüedad (Años)", value=5) | |
| inp_condominio = gr.Checkbox(label="🛡️ Buscar en Condominio", value=True) | |
| btn = gr.Button("🔍 Calcular Valor", variant="primary") | |
| out = gr.HTML(label="Informe") | |
| btn.click(motor, | |
| inputs=[inp_zona, inp_tipo, inp_precio, inp_area, inp_habs, inp_banos, inp_garajes, inp_estrato, inp_antiguedad, inp_condominio], | |
| outputs=out) | |
| if __name__ == "__main__": | |
| demo.launch() |