import gradio as gr import requests import time import re import pandas as pd import numpy as np import json import os from datetime import datetime # Firebase imports import firebase_admin from firebase_admin import credentials, firestore # --- Areas and MicroMarkets Data --- areasData = [ {"Area": "Central", "MicroMarkets": ["B Venkata Reddy Nagar", "Basavanagudi", "BTM Layout", "Chamrajapet", "Chickpet", "Fraser Town", "Jayamahal", "Jogupalya", "Kempapura Agrahara", "Lakkasandra", "Malleswaram", "Rajajinagar", "Sadashivanagar", "Shanthi Nagar", "Vasanth Nagar", "Vishveshwara Puram"]}, {"Area": "East", "MicroMarkets": ["A. Narayanapura", "Aavalahalli", "AECS Layout", "Agaram", "Avalahalli", "Balagere", "Bellandur", "Bhoganahalli", "Bidaraguppe", "Brookefield", "Byalahalli", "C V Raman Nagar", "Carmelaram", "Chikkabellandur", "Chikkakannalli", "Choodasandra", "Dodda Nekkundi", "Doddakannelli", "Domlur", "Dommasandra", "Garudachar Palya", "Gattahalli", "Gopasandra", "Gulimangala", "Gunjur", "HAL Airport", "Harlur", "Harohalli", "Hoskote", "HSR Layout", "Hudi", "Huskuru", "Indiranagar", "Indlabele", "K R Puram", "Kachamaranahalli", "Kadubeesanahalli", "Kadugodi", "Kaikondrahalli", "Kannamangala", "Kasvanahalli", "Kodathi", "Koramangala", "Kyalasanahalli", "Mahadevapura", "Marathahalli", "Mullur", "Muthanallur", "Naganathapura", "Neriga", "Panathur", "Rayasandra", "Sadaramangala", "Sarjapura", "Somsundarapalya", "Varthur", "Whitefield"]}, {"Area": "North", "MicroMarkets": ["Airport City", "Alur", "Bagaluru", "Baiyappanahalli", "Banasavadi", "Bande Bommasandra", "Bidarahalli", "Bileshivale", "Budigere", "Budigere Cross", "Byappanahalli", "Bylakere", "Byrathi", "Cheemasandra", "Chikkabanavara", "Chikkagubbi", "Dasanayakanahalli", "Devanahalli", "Dodda Gubbi", "Doddaballapur", "Gundur", "HBR Layout", "Hebbal", "Hennur", "Hesaraghatta", "Horamavu", "IISC", "Jakkur", "Jalahalli", "Kada agrahara", "Kadugondanahalli", "Kalkere", "Kannuru", "KIADB Park", "Kommasandra", "Kothanur", "Mandur", "Maralakunte", "Margondanahalli", "Mitganahalli", "Nagavara", "Narayanapura", "Nimbekaipura", "Radhakrishna Temple Ward", "Rajanukunte", "Ramamurthy Nagar", "RT Nagar", "Sahakara Nagar", "Thanisandra", "Vaderahalli", "Vidyaranyapura", "Vignana Kendra", "Vijinapura", "Visthar", "Yelahanka", "Yelahanka Satellite Town", "Yerappanahalli"]}, {"Area": "South", "MicroMarkets": ["Adigondanahalli", "Akshayanagar", "Anekal", "Anjanapura", "Attibele", "Banashankari", "Banashankari 6th Stage", "Bangalore South", "Bannerghatta", "Begur", "Bettadasanpura", "Bilekhalli", "Bommanahalli", "Bommasandra", "Electronic City", "Hemmigepura", "Hulimangala", "J P Nagar", "Jayanagar", "Jigani", "Kaggalipura", "Kengeri", "Kudlu", "Ragihalli", "Rajarajeshwari Nagar", "Uttarahalli"]}, {"Area": "West", "MicroMarkets": ["Challaghatta", "Gongadipura", "Lakshmipura", "Nagarabhavi", "Nagasandra", "Nelamangala", "Peenya", "Peenya Industrial Area", "Sulivara", "Yeshwantpur"]} ] # Configuration - USING GEMINI 1.5 FLASH FOR MICROMARKET EXTRACTION EXISTING_COLUMNS = ['Price / sqft', 'Carpet Area', 'Super Built-up Area', 'Floor', 'Unit Configuration', 'Undivided Share (UDS)', 'Address', 'Asset_Type', 'Plot_Area', 'Zone', 'Micromarket'] GEMINI_API_KEY = "AIzaSyC1FoWG1pH3xQQm7PvofFx_SqrgIhErp8c" # Main extraction API - Gemini 2.5 Flash GEMINI_MAIN_URL = f"https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key={GEMINI_API_KEY}" # Micromarket extraction API - Gemini 1.5 Flash GEMINI_MICROMARKET_URL = f"https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent?key={GEMINI_API_KEY}" # --- FIREBASE INITIALIZATION --- def initialize_firebase(): """Initialize Firebase with service account key""" try: # Create service account key file service_account_info = { "type": "service_account", "project_id": "iqol-crm", "private_key_id": "acdc77ac88c41e7202776aa0185f2626b87dad3b", "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQDA3oCeHoo9kYfg\nxihmbBb4NkzYtxOL8lBUxIa8ViCO5xgLir5KvvldP4aEYgjeteRO1e5GT6FuHHtB\nziLTKEdqwGJ2HKiMFX1aP1wauNnMt47ecBrOPEyV3roWv3Pj8L1ROmD6G0wAyG1t\nYemCoylO+qgIlQykD9gcZ1v7778aaHaXLFFM7KdgJTKHdoeh0ZIpEl/tsdK9OwUR\nt3/39eg07lv0Ak71l7DFDhff17OaVum8DK+LxZoXONO4T94CkM93jRfFMUEq/Esj\nc1bKDfK/zpYgPbj0ry8g57GRLIx7/zBdJiCSrSMZppTig3ULOvx9IsyONXZfluDA\nMJim9ogDAgMBAAECggEAEFuuz/pqIi+yNRvJeGpphlLgsK7Sbxe1vYLPpLCvYT9z\neCMj4aYR81k8eQTxmKYXv6IAbCE74WF0CNBJftxXNlQr5bWni/4UnC9sie2st2LI\nMNCUTXKq0jlKUjyZwTb9b6LCb+QZzaUyNslLq2NYURUMZHHz2QRpAXu4vwUGEeCt\nkYv6AuFWRQoelLmLM01wDhnqct2Fy8Ik2M+CtE9u8HJcqAh+lUD7ScInmXxGGGi1\nNziVZSMmuxBTGu5Zmv6PAe96W9IuVFZ994kbZMchntE2t2s0Wg1C82BfbutCWGRJ\nXjbJo9eHz+raC9Rlhh332+mrBijbrAJWzdhvWX24wQKBgQDrGC0e5iHoasvDRpJt\nsL8cyEBcdUOPeFdipstvNLwO8pJTj5/yT+ZYvPR4KrbofGMTVfTL3ZV29WrWKHLi\nirhNoeyuWnbogaQrM1TSB2aEIgaIAYFusRul8+yatUutgLlXuRdcavd6AptyRjxt\njHjZPXSuUIAczVGa20iWj4F7wQKBgQDSBRXsMkXGyyT+p5y93FFUuRZVsJLkfyzh\nifhclA8HTBwCwVFFGfUFSB64M+EAsbsdQjPFS+0h5tPyZ5zZJBAkaTZg1jhHRk9W\nTilMgj5/xj+6o2EWaqY2Fcbk4AmeOG236xdcwRzo+LVV1sVMI+/lIA/OEPKideB3\nR8Drx/ZEwwKBgHnNrdr7ewfzBR1onAcu8uWTrxz4OIfF3uii6HX2iZTpAv0+Ra2C\nziKdy7/Sya+MeryS5WEWrhDTOIY6sMNFAhZe1cTD5CW0vX0QfFrRMME269FdjlTu\neBe0WJsTYWFEd0LHCB5+4Tea0DUVxMsxY4+Scri5xpalnikwgdxX/8tBAoGABWI7\n/aIMR9xNRnnlerh7HUDhYbD1EwZvkBiJVVY2TGmXDjsGak8QCOKgZvhtfCcSNN3N\nlfEsUwInzL77NlXGVCieTD0xlCIpRn0aceukzoG2gIJPqtNxD1Hfl15m4LdxmJVe\nM/J/WzmE7H5k8F67d5Qq0417qs56wpQETgcCOSECgYBArxYUJEdAdGyGQUNtezpz\noCsWQ47SFu49ZjDmcCrhPT+BM4loB/H08A8mKPS9ck2/2VFI0GqTHEwfPxT61faK\ny+TTR8lyR3ZYtrXTXC2h1mwrNxrhEih3iNDfaxYl/EpwM4bmlosLWBbdwJnPRk9c\nD2s40XxtD+yWH+ESDfIeCA==\n-----END PRIVATE KEY-----\n", "client_email": "firebase-adminsdk-fbsvc@iqol-crm.iam.gserviceaccount.com", "client_id": "105834381785699477522", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/firebase-adminsdk-fbsvc%40iqol-crm.iam.gserviceaccount.com", "universe_domain": "googleapis.com" } # Save to temporary file with open('temp_serviceAccountKey.json', 'w') as f: json.dump(service_account_info, f) # Initialize Firebase if not firebase_admin._apps: cred = credentials.Certificate('temp_serviceAccountKey.json') firebase_admin.initialize_app(cred) # Clean up temporary file if os.path.exists('temp_serviceAccountKey.json'): os.remove('temp_serviceAccountKey.json') return firestore.client() except Exception as e: print(f"Firebase initialization error: {e}") return None def upload_to_firestore(data, collection_name="DUPLICATES"): """Upload JSON data to Firestore""" try: db = initialize_firebase() if not db: return "❌ Failed to initialize Firebase" collection_ref = db.collection(collection_name) uploaded_count = 0 updated_count = 0 for item in data: doc_id = item.get('id') if doc_id: doc_ref = collection_ref.document(doc_id) doc = doc_ref.get() if doc.exists: doc_ref.update(item) updated_count += 1 else: doc_ref.set(item) uploaded_count += 1 else: doc_ref = collection_ref.add(item) uploaded_count += 1 return f"✅ Successfully uploaded {uploaded_count} new documents and updated {updated_count} existing documents to Firestore collection '{collection_name}'" except Exception as e: return f"❌ Error uploading to Firestore: {str(e)}" # --- JSON TO INTERNAL FIELD MAPPING --- JSON_FIELD_MAPPING = { 'description': 'auction_description', # Main description field 'price_per_sqft': 'Price / sqft', 'carpet_area': 'Carpet Area', 'super_built_area': 'Super Built-up Area', 'floor': 'Floor', 'unit_configuration': 'Unit Configuration', 'undivided_share': 'Undivided Share (UDS)', 'address': 'Address', 'asset_type': 'Asset_Type', 'plot_area': 'Plot_Area' } # --- CORE PROCESSING FUNCTIONS --- def process_field_value(text, field_name): """ Universal field processing function - handles all field types """ if not text or str(text).strip() == "" or str(text).upper() in ['NA', 'NULL', 'NAN']: return "NA" text = str(text).strip() # Remove field name prefixes text = re.sub(rf'^{re.escape(field_name)}[:\s]*', '', text, flags=re.IGNORECASE).strip() text = re.sub(r'^[^:]*:\s*', '', text).strip() # Field-specific processing if field_name == 'Address': return clean_address_field(text) elif field_name in ['Carpet Area', 'Super Built-up Area', 'Plot_Area']: return process_area_field(text) elif field_name == 'Floor': match = re.search(r'([0-9]+)', text) return match.group(1) if match else "NA" elif field_name == 'Unit Configuration': return process_configuration_field(text) elif field_name == 'Asset_Type': return process_asset_type_field(text) elif field_name == 'Price / sqft': match = re.search(r'₹?\s*([0-9,]+)', text) return f"₹{match.group(1)}" if match else "NA" return text if len(text) <= 100 and text.upper() != 'NA' else 'NA' def clean_address_field(raw_address): """ Clean and trim address at Bangalore/Bengaluru + pincode """ if not raw_address or str(raw_address).upper() == "NA": return "NA" text = str(raw_address).strip() if len(text) < 10: return text # Remove junk text = re.sub(r'Asset_Type[^|]*|Plot_Area[^|]*|\|.*', '', text, flags=re.IGNORECASE) # Termination patterns patterns = [ r'(.*?(?:bangalore|bengaluru).*?karnataka.*?\d{6})', r'(.*?(?:bangalore|bengaluru).*?\d{6})', r'(.*?(?:bangalore|bengaluru).*?karnataka)', r'(.*?(?:bangalore|bengaluru))(?:\s+bounded|\s+measuring|\s+together|\s+admeasuring|\s+bearing)', r'(.*?(?:bangalore|bengaluru))' ] for pattern in patterns: match = re.search(pattern, text, re.IGNORECASE) if match: text = match.group(1).strip() break # Remove boundary descriptions boundary_patterns = [ r'\s*bounded\s*by.*$', r'\s*measuring.*?(?:sq\.?ft|sq\.?\s*mtrs?).*$', r'\s*together\s*with.*$', r'\s*inclusive\s*of.*$', r'\s*admeasuring.*$', r'\s*bearing\s*no\..*$', r'\s*with\s*undivided.*$', r'\s*flat\s*measuring.*$' ] for pattern in boundary_patterns: text = re.sub(pattern, '', text, flags=re.IGNORECASE) # Clean up text = re.sub(r'[,\s]+$|^[,\s]+|\s+', ' ', text).strip() return text if len(text) >= 5 else "NA" def process_area_field(text): """ Process area fields with unit conversion """ area_match = re.search(r'([0-9.]+)\s*(?:sq\.?\s*)?(ft|mtrs?|meters?|guntas?)', text, re.IGNORECASE) if area_match: value, unit = float(area_match.group(1)), area_match.group(2).lower() if 'mtr' in unit or 'meter' in unit: value *= 10.764 elif 'gunta' in unit: value *= 1089 return f"{value:.0f}" number_match = re.search(r'([0-9.]+)', text) return f"{number_match.group(1)}" if number_match else "NA" def process_configuration_field(text): """ Process unit configuration field """ config_patterns = [ (r'([0-9]+)\s*BHK', lambda m: f"{m.group(1)}BHK"), (r'([0-9]+)\s*RK', lambda m: f"{m.group(1)}RK"), (r'(Studio)', lambda m: 'Studio'), (r'([0-9]+)\s*bed', lambda m: f"{m.group(1)}BHK"), ] for pattern, formatter in config_patterns: match = re.search(pattern, text, re.IGNORECASE) if match: return formatter(match) return "NA" def process_asset_type_field(text): """ Process asset type field """ asset_types = ['Flat', 'Apartment', 'Villa', 'House', 'Plot', 'Commercial', 'Residential'] for asset_type in asset_types: if asset_type.lower() in text.lower(): return asset_type return "NA" # --- GEMINI API FUNCTIONS --- def test_api_key(): """Test API key validity""" try: # Test main API with longer timeout res = requests.post(GEMINI_MAIN_URL, json={'contents': [{'parts': [{'text': 'test'}]}], 'generationConfig': {'maxOutputTokens': 10}}, timeout=30) if res.status_code != 200: return False # Test micromarket API with longer timeout res = requests.post(GEMINI_MICROMARKET_URL, json={'contents': [{'parts': [{'text': 'test'}]}], 'generationConfig': {'maxOutputTokens': 10}}, timeout=30) return res.status_code == 200 except: return False def extract_micromarket_and_zone_with_gemini(address): """ Use Gemini 1.5 Flash to extract micromarket and zone from address with ENHANCED PRECISION """ if not address or str(address).upper() == "NA": return "NA", "NA" # Create detailed micromarket mapping micromarket_zone_mapping = {} zone_info = [] for area_data in areasData: zone = area_data["Area"] micromarkets = area_data["MicroMarkets"] zone_info.append(f"{zone}: {', '.join(micromarkets)}") for micromarket in micromarkets: micromarket_zone_mapping[micromarket] = zone zone_list = '\n'.join(zone_info) prompt = f"""You are an expert in Bangalore real estate geography. Your task is to identify the EXACT micromarket and corresponding zone from the given address. ADDRESS TO ANALYZE: "{address}" COMPLETE MICROMARKET-ZONE MAPPING (YOU MUST CHOOSE FROM THIS LIST ONLY): Central: B Venkata Reddy Nagar, Basavanagudi, BTM Layout, Chamrajapet, Chickpet, Fraser Town, Jayamahal, Jogupalya, Kempapura Agrahara, Lakkasandra, Malleswaram, Rajajinagar, Sadashivanagar, Shanthi Nagar, Vasanth Nagar, Vishveshwara Puram East: A. Narayanapura, Aavalahalli, AECS Layout, Agaram, Avalahalli, Balagere, Bellandur, Bhoganahalli, Bidaraguppe, Brookefield, Byalahalli, C V Raman Nagar, Carmelaram, Chikkabellandur, Chikkakannalli, Choodasandra, Dodda Nekkundi, Doddakannelli, Domlur, Dommasandra, Garudachar Palya, Gattahalli, Gopasandra, Gulimangala, Gunjur, HAL Airport, Harlur, Harohalli, Hoskote, HSR Layout, Hudi, Huskuru, Indiranagar, Indlabele, K R Puram, Kachamaranahalli, Kadubeesanahalli, Kadugodi, Kaikondrahalli, Kannamangala, Kasvanahalli, Kodathi, Koramangala, Kyalasanahalli, Mahadevapura, Marathahalli, Mullur, Muthanallur, Naganathapura, Neriga, Panathur, Rayasandra, Sadaramangala, Sarjapura, Somsundarapalya, Varthur, Whitefield North: Airport City, Alur, Bagaluru, Baiyappanahalli, Banasavadi, Bande Bommasandra, Bidarahalli, Bileshivale, Budigere, Budigere Cross, Byappanahalli, Bylakere, Byrathi, Cheemasandra, Chikkabanavara, Chikkagubbi, Dasanayakanahalli, Devanahalli, Dodda Gubbi, Doddaballapur, Gundur, HBR Layout, Hebbal, Hennur, Hesaraghatta, Horamavu, IISC, Jakkur, Jalahalli, Kada agrahara, Kadugondanahalli, Kalkere, Kannuru, KIADB Park, Kommasandra, Kothanur, Mandur, Maralakunte, Margondanahalli, Mitganahalli, Nagavara, Narayanapura, Nimbekaipura, Radhakrishna Temple Ward, Rajanukunte, Ramamurthy Nagar, RT Nagar, Sahakara Nagar, Thanisandra, Vaderahalli, Vidyaranyapura, Vignana Kendra, Vijinapura, Visthar, Yelahanka, Yelahanka Satellite Town, Yerappanahalli South: Adigondanahalli, Akshayanagar, Anekal, Anjanapura, Attibele, Banashankari, Banashankari 6th Stage, Bangalore South, Bannerghatta, Begur, Bettadasanpura, Bilekhalli, Bommanahalli, Bommasandra, Electronic City, Hemmigepura, Hulimangala, J P Nagar, Jayanagar, Jigani, Kaggalipura, Kengeri, Kudlu, Ragihalli, Rajarajeshwari Nagar, Uttarahalli West: Challaghatta, Gongadipura, Lakshmipura, Nagarabhavi, Nagasandra, Nelamangala, Peenya, Peenya Industrial Area, Sulivara, Yeshwantpur 🎯 CRITICAL ZONE AND MICROMARKET RULES (MUST FOLLOW): 1. **BEGUR** is in **SOUTH** zone (NOT North or East) 2. **HSR Layout** is in **EAST** zone 3. **Electronic City** is in **SOUTH** zone 4. **Whitefield** is in **EAST** zone 5. **Hebbal** is in **NORTH** zone 6. **Koramangala** is in **EAST** zone 7. **Malleswaram** is in **CENTRAL** zone 8. **Banashankari** is in **SOUTH** zone 9. **Indiranagar** is in **EAST** zone 10. **Jayanagar** is in **SOUTH** zone ANALYSIS INSTRUCTIONS: 1. Look for EXACT micromarket names from the list above 2. Handle common variations (e.g., "HSR" = "HSR Layout", "Koramangla" = "Koramangala") 3. Check for Village/Hobli names that might match micromarket names 4. Consider project names, nearby landmarks, or area descriptions 5. If no exact match found, extract the most specific location name from the address EXAMPLES OF CORRECT MAPPINGS: - "Begur Hobli" or "Begur Village" → Micromarket: Begur, Zone: South - "HSR Layout" or "HSR" → Micromarket: HSR Layout, Zone: East - "Electronic City Phase 1" → Micromarket: Electronic City, Zone: South - "Koramangala 5th Block" → Micromarket: Koramangala, Zone: East - "Whitefield Main Road" → Micromarket: Whitefield, Zone: East - "Hebbal Lake" → Micromarket: Hebbal, Zone: North If you cannot find a confident match from the predefined list, extract the most specific local area name from the address (like Village name, Hobli name, or locality) and set Zone to "NA". RESPONSE FORMAT (EXACTLY): Micromarket: [exact name from list above OR local area name if no match] Zone: [Central/East/North/South/West OR NA if no match] """ try: response = requests.post(GEMINI_MICROMARKET_URL, json={ 'contents': [{'parts': [{'text': prompt}]}], 'generationConfig': {'temperature': 0.1, 'maxOutputTokens': 150} }, timeout=60) # Increased timeout if response.status_code == 200: response_json = response.json() # Extract response text response_text = "" candidate = response_json['candidates'][0] # Handle different response structures content = candidate.get('content', {}) if isinstance(content, dict) and 'parts' in content: if len(content['parts']) > 0 and 'text' in content['parts'][0]: response_text = content['parts'][0]['text'] if response_text: # Parse micromarket and zone from response micromarket_match = re.search(r'Micromarket:\s*(.+)', response_text, re.IGNORECASE) zone_match = re.search(r'Zone:\s*(.+)', response_text, re.IGNORECASE) micromarket = micromarket_match.group(1).strip() if micromarket_match else "NA" zone = zone_match.group(1).strip() if zone_match else "NA" # Clean up extracted values micromarket = re.sub(r'[,\n\r\s]+$', '', micromarket).strip() zone = re.sub(r'[,\n\r\s]+$', '', zone).strip() # Validate micromarket is in our list all_micromarkets = [] for area_data in areasData: all_micromarkets.extend(area_data["MicroMarkets"]) # If micromarket is not in our predefined list, keep it as local area name if micromarket not in all_micromarkets: # Keep the extracted local area name zone = "NA" # Set zone to NA if micromarket not in predefined list # Validate zone if zone not in ['Central', 'East', 'North', 'South', 'West']: zone = "NA" # Double check zone matches micromarket (only if micromarket is in our list) if micromarket in all_micromarkets and zone != "NA": expected_zone = micromarket_zone_mapping.get(micromarket) if expected_zone and expected_zone != zone: zone = expected_zone # Correct the zone based on micromarket return micromarket, zone else: return "NA", "NA" else: return "NA", "NA" except: return "NA", "NA" def clean_extracted_value(value): """Clean extracted values from Gemini response""" if not value: return 'NA' value = str(value).strip() # Remove bullet points and dashes value = re.sub(r'^[\-•*]\s*', '', value) # Remove trailing commas, newlines, and extra spaces # value = re.sub(r'[,\n\r\s]+, '', value) value = re.sub(r'[,\n\r\s]+$', '', value) # Remove leading/trailing quotes value = value.strip('"\'') # If empty after cleaning, return NA if not value or value.upper() == 'NA': return 'NA' return value def process_with_gemini(descriptions, contexts, max_rows): """ Process data with Gemini 2.5 Flash API including retry logic """ if not test_api_key(): return [{col: 'NA' for col in EXISTING_COLUMNS} for _ in descriptions] results = [] for i, (desc, ctx) in enumerate(zip(descriptions[:max_rows], contexts[:max_rows])): existing_info = '\n'.join(f"{f}: {v}" for f, v in ctx.items()) or 'No existing data' # FIXED PROMPT with corrected rules and examples full_prompt = f"""Extract real estate data from this auction description: EXISTING: {existing_info} DESCRIPTION: "{desc}" Extract and format the following fields EXACTLY as shown below. If a field already has accurate information above, you may keep it, but verify and correct if needed: Price / sqft: [value with ₹ symbol if mentioned] Carpet Area: [area value only, no units] Super Built-up Area: [area value only, no units] Floor: [number only] Unit Configuration: [like 2BHK, 3BHK, 1RK, Studio] Undivided Share (UDS): [UDS details] Address: [CLEAN ADDRESS - STOP at Bangalore/Bengaluru + pincode] Asset_Type: [Flat/Apartment/Villa/House/Plot/Commercial/Residential] Plot_Area: [area with sq.ft units - for plots only, NA for flats] CRITICAL RULES FOR CARPET AREA VS SUPER BUILT-UP AREA: 1. **Carpet Area**: The ACTUAL USABLE area of the property (excluding walls, balconies, common areas) 2. **Super Built-up Area**: The TOTAL area including proportionate share of common areas, balconies, corridors, lifts 3. **IMPORTANT**: Carpet Area is ALWAYS SMALLER than Super Built-up Area 4. **When both are mentioned**: Extract each value to its correct field - DO NOT swap them 5. **Area calculation priority**: If text says "Carpet Area X sq mtrs" and "Super Built Up Area Y sq mtrs", then: - Carpet Area = X (converted to sq.ft) - Super Built-up Area = Y (converted to sq.ft) ADDITIONAL RULES: 1. Use EXACTLY the field names shown above (with spaces & capitalization) 2. For Address: STOP at "Bangalore"/"Bengaluru" + optional pincode. DO NOT include "Bounded by", "Measuring", "Together with", "Admeasuring", or boundary descriptions 3. For FLATS/APARTMENTS: Plot_Area = NA 4. For PLOTS: Extract plot/land area, Carpet Area and Super Built-up Area = NA 5. Convert units: sq mtrs to sq.ft (×10.764), guntas to sq.ft (×1089) 6. If information is not clearly mentioned, write "NA" Example 1 - CORRECT Carpet vs Super Built-up Processing: Input: "Flat No. T8-1304, Carpet Area Admeasuring About 75.74 Sq Mtrs, Super Built Up Area 111.81 Sq.Mtrs, Purva Zenium, Hosahalli Village, Bangalore 562157" Correct Response: Price / sqft: NA Carpet Area: 815 Super Built-up Area: 1204 Floor: 13 Unit Configuration: NA Undivided Share (UDS): NA Address: Purva Zenium, Hosahalli Village, Bangalore 562157 Asset_Type: Flat Plot_Area: NA Example 2 - Address Termination: Input: "Site no 11, Assessment no 2, Situated at Soladevanhalli Village, Hesaraghatta Hobli, New Yelahanka Taluk, Bangalore North Taluk, Bangalore, Karnataka, 560088 Bounded by East-Property belongs to Jajuraiah, West-Road..." Correct Address: "Site no 11, Assessment no 2, Soladevanhalli Village, Hesaraghatta Hobli, New Yelahanka Taluk, Bangalore, Karnataka, 560088" Example 3 - Plot Processing: Input: "Plot measuring 20 guntas or 21,780 sq. ft at Sy.no. 11/7 situated at Kammanahalli, Begur Hobli, Bangalore South Taluk" Correct Response: Price / sqft: NA Carpet Area: NA Super Built-up Area: NA Floor: NA Unit Configuration: NA Undivided Share (UDS): NA Address: Sy.no. 11/7 Kammanahalli, Begur Hobli, Bangalore Asset_Type: Plot Plot_Area: 21780 sq.ft Example 4 - CORRECTED Complete Processing: Input: "Residential Apartment Bearing No. 17192, Situated On 19 Floor/Level, Flat Measuring 977 Sq. Ft. Of Carpet Area And 1376 Sq.Ft. Of Super Built Up Area, Undivided Share, Prestige Song Of The South, Chandrashekharapura Village, Begur Hobli, Bangalore South Taluk, Bengaluru Karnataka- 560068" Correct Response: Price / sqft: NA Carpet Area: 977 Super Built-up Area: 1376 Floor: 19 Unit Configuration: NA Undivided Share (UDS): 1376 sq.ft Address: Prestige Song Of The South, Chandrashekharapura Village, Begur Hobli, Bangalore South Taluk, Bengaluru Karnataka- 560068 Asset_Type: Apartment Plot_Area: NA RESPONSE FORMAT (use exactly this format): Price / sqft: [value] Carpet Area: [value] Super Built-up Area: [value] Floor: [value] Unit Configuration: [value] Undivided Share (UDS): [value] Address: [value] Asset_Type: [value] Plot_Area: [value] """ # Shorter prompt for retry attempts short_prompt = f"""Extract real estate data from: "{desc}" Extract these fields EXACTLY: Price / sqft: [₹ value if mentioned] Carpet Area: [SMALLER usable area value only, no units] Super Built-up Area: [LARGER total area value only, no units] Floor: [number only] Unit Configuration: [2BHK, 3BHK, 1RK, Studio] Undivided Share (UDS): [UDS details] Address: [STOP at Bangalore/Bengaluru + pincode - NO boundary descriptions] Asset_Type: [Flat/Apartment/Villa/House/Plot/Commercial/Residential] Plot_Area: [for plots only, NA for flats] CRITICAL: Carpet Area < Super Built-up Area. Don't swap them. Convert sq mtrs×10.764, guntas×1089.""" # Retry logic for attempt in range(3): try: # Use shorter prompt on retry attempts current_prompt = short_prompt if attempt > 0 else full_prompt response = requests.post(GEMINI_MAIN_URL, json={ 'contents': [{'parts': [{'text': current_prompt}]}], 'generationConfig': {'temperature': 0.1, 'maxOutputTokens': 4096} }, timeout=60) # Increased timeout if response.status_code == 200: response_json = response.json() # Handle different response structures for Gemini 2.5 Flash response_text = "" # Check for finish reason first candidate = response_json['candidates'][0] finish_reason = candidate.get('finishReason', '') if finish_reason == 'MAX_TOKENS': continue elif finish_reason == 'SAFETY': results.append({col: 'NA' for col in EXISTING_COLUMNS}) break try: content = candidate.get('content', {}) # Try different parsing approaches for Gemini 2.5 Flash if isinstance(content, dict) and 'parts' in content and isinstance(content['parts'], list): if len(content['parts']) > 0 and 'text' in content['parts'][0]: response_text = content['parts'][0]['text'] except Exception as e: if attempt < 2: continue else: results.append({col: 'NA' for col in EXISTING_COLUMNS}) break if not response_text: if attempt < 2: continue else: results.append({col: 'NA' for col in EXISTING_COLUMNS}) break # Process extracted fields (excluding Zone and Micromarket) extracted = {} for field in EXISTING_COLUMNS: if field not in ['Zone', 'Micromarket']: # Skip these, handled separately pattern = rf"{re.escape(field)}\s*:\s*(.+?)(?=\n[A-Z]|$)" match = re.search(pattern, response_text, re.IGNORECASE | re.DOTALL) if match: value = match.group(1).strip() value = clean_extracted_value(value) extracted[field] = process_field_value(value, field) else: extracted[field] = 'NA' else: extracted[field] = 'NA' # Will be filled by dedicated micromarket extraction results.append(extracted) break elif response.status_code == 503: wait_time = 15 * (attempt + 1) time.sleep(wait_time) else: results.append({col: 'NA' for col in EXISTING_COLUMNS}) break except Exception as e: results.append({col: 'NA' for col in EXISTING_COLUMNS}) break else: # If all retries failed results.append({col: 'NA' for col in EXISTING_COLUMNS}) # Rate limiting - increased wait time if i < max_rows - 1: time.sleep(3) # Increased from 2 to 3 seconds return results # --- JSON DATA LOADING AND MAPPING --- def load_json_data(file_path): """ Load JSON data and convert to DataFrame with field mapping """ try: with open(file_path, 'r', encoding='utf-8') as f: data = json.load(f) # Handle both single object and array of objects if isinstance(data, dict): data = [data] elif not isinstance(data, list): raise ValueError("JSON must contain an object or array of objects") # Convert to DataFrame df = pd.DataFrame(data) # Map JSON fields to internal structure mapped_df = pd.DataFrame() # Add the main description field (required) if 'description' in df.columns: mapped_df['auction_description'] = df['description'] else: raise ValueError("JSON must contain 'description' field") # Map other fields using the mapping dictionary for json_field, internal_field in JSON_FIELD_MAPPING.items(): if json_field in df.columns: mapped_df[internal_field] = df[json_field] else: mapped_df[internal_field] = "NA" # Add Zone and Micromarket columns (will be filled later) mapped_df['Zone'] = "NA" mapped_df['Micromarket'] = "NA" # Ensure all required columns exist for col in EXISTING_COLUMNS: if col not in mapped_df.columns: mapped_df[col] = "NA" return mapped_df except Exception as e: raise Exception(f"Error loading JSON file: {str(e)}") # --- DATA CLEANING --- def clean_overlapping_data(df): """ Clean overlapping data and add missing columns """ # Add missing columns for col in EXISTING_COLUMNS: if col not in df.columns: df[col] = "" # Ensure all columns are object type for col in EXISTING_COLUMNS: if col in df.columns: df[col] = df[col].astype(object) # Process each row for idx, row in df.iterrows(): # Clean overlapping fields cleaned_data = {} for field_name in EXISTING_COLUMNS: if field_name in row.index: original_value = str(row[field_name]) if pd.notna(row[field_name]) else "" cleaned_data[field_name] = process_field_value(original_value, field_name) # Handle special overlaps # 1. Carpet Area containing Super Built-up Area carpet_text = str(row.get('Carpet Area', '')) if 'Super Built-up Area:' in carpet_text: super_match = re.search(r'Super\s*(?:Built-up\s*)?Area[:\s]*([^\\n\\r]+)', carpet_text, re.IGNORECASE) if super_match: cleaned_data['Super Built-up Area'] = process_field_value(super_match.group(1), 'Super Built-up Area') cleaned_data['Carpet Area'] = process_field_value(re.sub(r'\\n.*|Super.*', '', carpet_text), 'Carpet Area') # 2. Asset_Type containing Plot_Area asset_text = str(row.get('Asset_Type', '')) if 'Plot_Area:' in asset_text: plot_match = re.search(r'Plot[_\s]*Area[:\s]*([^\\n\\r]+)', asset_text, re.IGNORECASE) if plot_match: cleaned_data['Plot_Area'] = process_field_value(plot_match.group(1), 'Plot_Area') cleaned_data['Asset_Type'] = process_field_value(re.sub(r'\\n.*|Plot.*', '', asset_text), 'Asset_Type') # Update dataframe for field_name, clean_value in cleaned_data.items(): if field_name in df.columns: df.at[idx, field_name] = clean_value return df # --- SAVE RESULTS TO JSON WITH NEW SCHEMA --- def create_json_output(df, original_data): """ Create JSON output following the specified schema """ results = [] for idx, row in df.iterrows(): # Get original data for this row original_row = original_data[idx] if idx < len(original_data) else {} # Extract pincode from address pincode = "NA" address = row.get('Address', 'NA') if address != "NA": pincode_match = re.search(r'\b(\d{6})\b', str(address)) if pincode_match: pincode = pincode_match.group(1) # Map micromarket to area field micromarket = row.get('Micromarket', 'NA') zone = row.get('Zone', 'NA') area_field = f"{micromarket}, {zone}" if micromarket != "NA" and zone != "NA" else micromarket # Create JSON object following the specified schema json_obj = { "id": original_row.get('id', f"property_{idx+1}"), "source": original_row.get('source', "NA"), "url": original_row.get('url', "NA"), "bank": original_row.get('bank', "NA"), "description": original_row.get('description', "NA"), "property_type": original_row.get('property_type', row.get('Asset_Type', 'NA')), "area": area_field, "city": "Bangalore", "state": "Karnataka", "contact": original_row.get('contact', "NA"), "reserved_price": original_row.get('reserved_price', "NA"), "emd_amount": original_row.get('emd_amount', "NA"), "submission_emd": original_row.get('submission_emd', "NA"), "auction_start_date": original_row.get('auction_start_date', "NA"), "auction_end_date": original_row.get('auction_end_date', "NA"), "pincode": pincode, "carpet_area": row.get('Carpet Area', 'NA'), "super_built_area": row.get('Super Built-up Area', 'NA'), "floor": row.get('Floor', 'NA'), "unit_configuration": row.get('Unit Configuration', 'NA'), "address": row.get('Address', 'NA'), "asset_type": row.get('Asset_Type', 'NA'), "undivided_share": row.get('Undivided Share (UDS)', 'NA'), "price_per_sqft": row.get('Price / sqft', 'NA') } results.append(json_obj) return results def save_results_to_json(df, output_path, original_data): """ Save processed DataFrame to JSON format with the new schema """ # Create JSON output with new schema results = create_json_output(df, original_data) # Save to JSON file with open(output_path, 'w', encoding='utf-8') as f: json.dump(results, f, indent=2, ensure_ascii=False) return output_path # --- MAIN PROCESSING FUNCTION FOR GRADIO --- def process_real_estate_data(file, max_rows, progress=gr.Progress()): """Main processing function for Gradio with JSON support, new schema output, and automatic Firebase upload""" if file is None: return None, "❌ Please upload a JSON file" try: # Step 1: Load JSON data and store original progress(0.05, desc="Reading JSON file...") # Load original data for schema mapping with open(file.name, 'r', encoding='utf-8') as f: original_data = json.load(f) # Handle both single object and array of objects if isinstance(original_data, dict): original_data = [original_data] elif not isinstance(original_data, list): raise ValueError("JSON must contain an object or array of objects") df = load_json_data(file.name) if 'auction_description' not in df.columns: return None, "❌ Error: 'description' field not found in the JSON file" total_rows = len(df) max_rows = min(max_rows, total_rows) # Initialize log output log_output = f"🚀 Starting processing of {max_rows} records...\n\n" log_output += "📊 Firebase upload is ENABLED - processed data will be uploaded to Firestore automatically\n\n" progress(0.1, desc="Cleaning overlapping data...") # Step 1: Clean overlapping data df_cleaned = clean_overlapping_data(df) # Step 2: Process with Gemini 2.5 Flash (main extraction) progress(0.2, desc="Starting Gemini 2.5 Flash processing...") descriptions = [str(row['auction_description']) for _, row in df_cleaned.head(max_rows).iterrows()] contexts = [] for idx, row in df_cleaned.head(max_rows).iterrows(): ctx = {} for col in EXISTING_COLUMNS: if col in df_cleaned.columns: value = row[col] if pd.notna(value) and str(value).strip() != "" and str(value).upper() not in ['NA', 'NULL']: ctx[col] = value contexts.append(ctx) gemini_results = process_with_gemini(descriptions, contexts, max_rows) # Step 3: Update dataframe with main extraction results progress(0.5, desc="Updating main extraction results...") for i, result in enumerate(gemini_results): for col, new_value in result.items(): if col in df_cleaned.columns and new_value != 'NA': current_value = df_cleaned.at[i, col] # Special handling for critical fields - always update if Gemini extracted a valid value critical_fields = ['Carpet Area', 'Super Built-up Area', 'Address', 'Asset_Type', 'Floor', 'Unit Configuration'] if col in critical_fields: # Always update critical fields with Gemini results df_cleaned.at[i, col] = new_value else: # For other fields, only update if current value is empty/NA if (pd.isna(current_value) or str(current_value).strip() == '' or str(current_value).upper() in ['NA', 'NULL']): df_cleaned.at[i, col] = new_value # Step 4: Extract Zone and Micromarket using Gemini 1.5 Flash progress(0.6, desc="Extracting zones and micromarkets...") for idx in range(min(max_rows, len(df_cleaned))): if idx % 5 == 0: # Update progress every 5 rows progress(0.6 + 0.25 * (idx / max_rows), desc=f"Processing address {idx+1}/{max_rows}...") # Get the cleaned address address = df_cleaned.at[idx, 'Address'] # Use Gemini 1.5 Flash to extract micromarket and zone micromarket, zone = extract_micromarket_and_zone_with_gemini(address) # Update dataframe df_cleaned.at[idx, 'Micromarket'] = micromarket df_cleaned.at[idx, 'Zone'] = zone # Show COMPLETE JSON output for each row (all 22 fields) row_data = df_cleaned.iloc[idx] json_row = create_json_output(pd.DataFrame([row_data]), [original_data[idx] if idx < len(original_data) else {}])[0] log_output += f"📝 Record {idx+1}:\n" + json.dumps(json_row, indent=2, ensure_ascii=False) + "\n\n" # Rate limiting - increased wait time if idx < max_rows - 1: time.sleep(3) # Increased from 1.5 to 3 seconds # Step 5: Final address cleaning progress(0.85, desc="Final address cleaning...") for idx in range(min(max_rows, len(df_cleaned))): address = df_cleaned.at[idx, 'Address'] clean_address = process_field_value(address, 'Address') df_cleaned.at[idx, 'Address'] = clean_address # Step 6: Results validation progress(0.9, desc="Validating results...") # Validate zone-micromarket mapping for i in range(min(max_rows, len(df_cleaned))): micromarket = df_cleaned.at[i, 'Micromarket'] zone = df_cleaned.at[i, 'Zone'] if micromarket != "NA" and zone != "NA": # Find correct zone for the micromarket correct_zone = None for area_data in areasData: if micromarket in area_data["MicroMarkets"]: correct_zone = area_data["Area"] break if correct_zone and correct_zone != zone: df_cleaned.at[i, 'Zone'] = correct_zone # Step 7: Save results and upload to Firebase progress(0.95, desc="Saving results...") timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") # Save JSON with new schema json_output_path = f"Enhanced_Real_Estate_Data_{max_rows}_records_NEW_SCHEMA_{timestamp}.json" # Save JSON with new schema save_results_to_json(df_cleaned.head(max_rows), json_output_path, original_data[:max_rows]) # Step 8: Upload to Firebase automatically progress(0.97, desc="Uploading to Firebase Firestore...") # Load the saved JSON data with open(json_output_path, 'r', encoding='utf-8') as f: json_data = json.load(f) # Upload to Firestore firebase_result = upload_to_firestore(json_data, "DUPLICATES") log_output += f"\n🔥 Firebase Upload Result:\n{firebase_result}\n\n" progress(1.0, desc="Complete!") # Final summary log_output += f"✅ Processing complete!\n" log_output += f"📊 Processed {max_rows} records\n" log_output += f"💾 JSON file saved: {json_output_path}\n" log_output += f"🔥 Firebase upload: {'✅ Success' if '✅' in firebase_result else '❌ Failed'}\n" return json_output_path, log_output except Exception as e: return None, f"❌ Error: {str(e)}" # --- GRADIO INTERFACE --- def create_interface(): with gr.Blocks(title="Real Estate JSON Data Extractor with Firebase", theme=gr.themes.Soft()) as iface: gr.Markdown(""" # 🏠 Real Estate JSON Data Extractor with Firebase Integration Upload a JSON file with real estate auction descriptions and extract structured data using AI. ## Features: - 🤖 AI-powered extraction using Gemini 2.5 Flash - 📍 Automatic micromarket and zone identification for Bangalore - 🔧 Fixed carpet area vs super built-up area swapping - 📊 Clean, structured output in NEW JSON schema format - 🔥 **Automatic Firebase Firestore upload to DUPLICATES collection** - 🔄 ALL original processing rules maintained - 📁 Complete schema compliance with all required fields - 📄 JSON output logged after each row for debugging """) with gr.Row(): with gr.Column(): file_input = gr.File( label="Upload JSON File", file_types=[".json"], type="filepath" ) max_rows_input = gr.Slider( minimum=1, maximum=1000, value=5, step=1, label="Number of records to process", info="Start with a small number to test" ) process_btn = gr.Button("🚀 Start Processing", variant="primary", size="lg") with gr.Column(): output_file = gr.File(label="Download Processed JSON File (NEW SCHEMA)") # Processing log output log_output = gr.Textbox( label="Processing Log (includes JSON output for each row + Firebase status)", lines=30, max_lines=50, show_copy_button=True, interactive=False ) # Examples section gr.Markdown(""" ## 📋 Input Requirements: - JSON file with objects containing 'description' field - Each object should contain real estate auction description text - File should be in .json format - Can be a single object or array of objects ## 🔥 Firebase Integration: - **Collection**: Data will be uploaded to `DUPLICATES` collection automatically - **Document ID**: Uses the `id` field from your JSON data (auto-generated if missing) - **Update/Insert**: Existing documents are updated, new documents are inserted - **Error Handling**: Comprehensive error reporting for upload issues ## 📝 NEW OUTPUT SCHEMA: ```json { "id": "string", "source": "string", "url": "string", "bank": "string", "description": "string", "property_type": "string", "area": "string", "city": "Bangalore", "state": "Karnataka", "contact": "string", "reserved_price": "string", "emd_amount": "string", "submission_emd": "string", "auction_start_date": "string", "auction_end_date": "string", "pincode": "string", "carpet_area": "string", "super_built_area": "string", "floor": "string", "unit_configuration": "string", "address": "string", "asset_type": "string", "undivided_share": "string", "price_per_sqft": "string" } ``` ## 🎯 What it extracts and enhances: - All fields from the new schema - Price per sqft - Carpet Area & Super Built-up Area (with proper conversion) - Floor number - Unit Configuration (2BHK, 3BHK, etc.) - Clean Address (stops at Bangalore + pincode) - Asset Type - Zone & Micromarket mapping to 'area' field - Automatic pincode extraction from address - City and State auto-populated for Bangalore - All missing fields marked as "NA" - **Automatic Firebase upload with comprehensive logging** ## 🔧 Key Features: - ✅ All original processing rules preserved - ✅ Enhanced zone-micromarket mapping - ✅ Fixed carpet/super built-up area swapping - ✅ Complete schema compliance - ✅ Automatic Firebase upload - no user interaction required """) # Event handlers - UPDATED: Removed upload_to_db_checkbox parameter process_btn.click( fn=process_real_estate_data, inputs=[file_input, max_rows_input], outputs=[output_file, log_output], show_progress=True ) return iface # --- MAIN --- if __name__ == "__main__": print("🚀 Starting Real Estate JSON Data Extractor...") app = create_interface() app.launch( server_name="0.0.0.0", server_port=7860, share=True, show_error=True )