app.py

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

# --- 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}"

# --- 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:
        print("🔑 Testing Gemini API keys...")
        # Test main API
        res = requests.post(GEMINI_MAIN_URL, json={'contents': [{'parts': [{'text': 'test'}]}], 'generationConfig': {'maxOutputTokens': 10}}, timeout=10)
        if res.status_code == 200:
            print("✅ Gemini 2.5 Flash API key working!")
        else:
            print(f"❌ Main API issue: {res.status_code}")
            return False

        # Test micromarket API
        res = requests.post(GEMINI_MICROMARKET_URL, json={'contents': [{'parts': [{'text': 'test'}]}], 'generationConfig': {'maxOutputTokens': 10}}, timeout=10)
        if res.status_code == 200:
            print("✅ Gemini 1.5 Flash API key working!")
            return True
        else:
            print(f"❌ Micromarket API issue: {res.status_code}")
            return False
    except Exception as e:
        print(f"❌ API test failed: {e}")
        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=25)

        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:
            print(f"  ❌ Micromarket API Error: {response.status_code}")
            return "NA", "NA"

    except Exception as e:
        print(f"  ❌ Gemini micromarket extraction error: {e}")
        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)

    # 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():
        print("❌ Skipping Gemini processing due to API issues")
        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])):
        print(f"🔄 Processing {i+1}/{max_rows} ({(i+1)/max_rows*100:.1f}%)...")

        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=30)

                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':
                        print(f"  ⚠️ Hit token limit - trying shorter prompt (attempt {attempt+1}/3)...")
                        continue
                    elif finish_reason == 'SAFETY':
                        print(f"  ⚠️ Response blocked for safety - skipping...")
                        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:
                        print(f"  ❌ Parsing failed: {e}")
                        if attempt < 2:
                            continue
                        else:
                            results.append({col: 'NA' for col in EXISTING_COLUMNS})
                            break

                    if not response_text:
                        print(f"  ❌ Could not extract text from response")
                        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)
                    asset_type = extracted.get('Asset_Type', 'NA')
                    config = extracted.get('Unit Configuration', 'NA')
                    carpet_area = extracted.get('Carpet Area', 'NA')
                    super_area = extracted.get('Super Built-up Area', 'NA')
                    print(f"  ✓ {asset_type} | {config} | Carpet: {carpet_area} | Super: {super_area}")
                    break

                elif response.status_code == 503:
                    wait_time = 15 * (attempt + 1)
                    print(f"  ⚠️ Service Unavailable - Retry {attempt+1}/3 in {wait_time}s...")
                    time.sleep(wait_time)

                else:
                    print(f"  ❌ API Error: {response.status_code}")
                    results.append({col: 'NA' for col in EXISTING_COLUMNS})
                    break

            except Exception as e:
                print(f"  ❌ Error: {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
        if i < max_rows - 1:
            time.sleep(2)

    return results

# --- DATA CLEANING ---

def clean_overlapping_data(df):
    """
    Clean overlapping data and add missing columns
    """
    print("🧹 CLEANING: Separating overlapping fields...")

    # Add missing columns
    for col in EXISTING_COLUMNS:
        if col not in df.columns:
            df[col] = ""
            print(f"✅ Added {col} column")

    # 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():
        if idx % 50 == 0:
            print(f"  Processing row {idx}...")

        # 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

    print("✅ Data cleaning completed")
    return df

# --- MAIN PROCESSING FUNCTION FOR GRADIO ---

def process_real_estate_data(file, max_rows, progress=gr.Progress()):
    """Main processing function for Gradio with ALL original logic preserved"""

    if file is None:
        return None, "❌ Please upload an Excel file"

    try:
        # Step 1: Read into DataFrame
        progress(0.05, desc="Reading Excel file...")
        df = pd.read_excel(file.name)

        if 'auction_description' not in df.columns:
            return None, "❌ Error: 'auction_description' column not found in the file"

        total_rows = len(df)
        max_rows = min(max_rows, total_rows)

        # Display Excel structure
        log_output = f"""Excel structure:
Columns: {df.columns.tolist()}
Total rows: {len(df)}
Total columns: {len(df.columns)}
Processing {max_rows} rows...

"""

        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...")
        log_output += f"\n🤖 STARTING GEMINI 2.5 FLASH PROCESSING FOR {max_rows} ROWS...\n"
        log_output += f"📝 Note: Zone and Micromarket will be extracted separately using Gemini 1.5 Flash\n"
        log_output += f"🔧 FIXED: Carpet Area and Super Built-up Area swapping issue resolved\n"

        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...")
        log_output += f"\n📝 Updating main extraction results...\n"

        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
                        if i < 5:  # Debug first 5 rows
                            log_output += f"  Row {i+1}: Updated {col} = {new_value}\n"
                    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 (ENHANCED PRECISION)
        progress(0.6, desc="Extracting zones and micromarkets...")
        log_output += f"\n🗺️ EXTRACTING ZONES AND MICROMARKETS WITH GEMINI 1.5 FLASH FOR {max_rows} ROWS...\n"
        log_output += "📍 Using enhanced rules for precise mapping...\n"
        log_output += "🎯 Key Rules: Begur→South, HSR Layout→East, Electronic City→South, Whitefield→East, Hebbal→North\n"

        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}...")

            log_output += f"🔍 Processing address {idx+1}/{max_rows}...\n"

            # Get the cleaned address
            address = df_cleaned.at[idx, 'Address']

            # Use Gemini 1.5 Flash to extract micromarket and zone with enhanced precision
            micromarket, zone = extract_micromarket_and_zone_with_gemini(address)

            # Update dataframe
            df_cleaned.at[idx, 'Micromarket'] = micromarket
            df_cleaned.at[idx, 'Zone'] = zone

            # Display result
            if micromarket != "NA" and zone != "NA":
                log_output += f"  ✅ {micromarket} → {zone}\n"
            elif micromarket != "NA" and zone == "NA":
                log_output += f"  ⚠️ Local Area: {micromarket} (not in predefined list)\n"
            else:
                log_output += f"  ⚠️ Could not determine micromarket/zone from address\n"

            # Rate limiting for API calls
            if idx < max_rows - 1:
                time.sleep(1.5)

        # Step 5: Final address cleaning
        progress(0.85, desc="Final address cleaning...")
        log_output += f"\n🧹 Final address cleaning for {max_rows} rows...\n"

        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 and display
        progress(0.9, desc="Validating results...")
        log_output += f"\n=== VALIDATION & FINAL RESULTS FOR {max_rows} ROWS ===\n"
        log_output += "🔍 Checking zone-micromarket accuracy...\n"

        # 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:
                    log_output += f"  ⚠️ Row {i+1}: Correcting {micromarket} from {zone} to {correct_zone}\n"
                    df_cleaned.at[i, 'Zone'] = correct_zone

        # Display final results
        log_output += f"\n📊 Final Results (showing first 5 of {max_rows} processed rows):\n"
        for i in range(min(5, max_rows)):
            log_output += f"\n📋 Row {i+1}:\n"
            for col in ['Asset_Type', 'Address', 'Zone', 'Micromarket', 'Carpet Area', 'Super Built-up Area', 'Plot_Area']:
                if col in df_cleaned.columns:
                    value = df_cleaned.iloc[i][col]
                    display_value = str(value)[:60] + "..." if len(str(value)) > 60 else str(value)
                    log_output += f"  {col}: {display_value}\n"

        # Save results
        progress(0.95, desc="Saving results...")
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        output_path = f"Enhanced_Real_Estate_Data_{max_rows}_rows_FIXED_{timestamp}.xlsx"
        df_cleaned.to_excel(output_path, index=False)

        log_output += f"\n🎉 PROCESSING COMPLETE!\n"
        log_output += f"✅ File saved: {output_path}\n"
        log_output += f"📊 Processed {max_rows} rows\n"
        log_output += f"🚀 Using Gemini 2.5 Flash for main extraction\n"
        log_output += f"🎯 Using Gemini 1.5 Flash for ENHANCED micromarket/zone extraction\n"
        log_output += f"🔧 FIXED: Carpet Area and Super Built-up Area swapping issue\n"
        log_output += f"📍 Enhanced rules for better accuracy: Begur→South, HSR Layout→East, etc.\n"
        log_output += f"🔄 All existing rules and processing logic maintained\n"

        progress(1.0, desc="Complete!")

        return 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 Data Extractor", theme=gr.themes.Soft()) as iface:
        gr.Markdown("""
        # 🏠 Real Estate Data Extractor

        Upload an Excel 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 Excel format
        - 🔄 ALL original processing rules maintained
        """)

        with gr.Row():
            with gr.Column():
                file_input = gr.File(
                    label="Upload Excel File",
                    file_types=[".xlsx", ".xls"],
                    type="filepath"
                )

                max_rows_input = gr.Slider(
                    minimum=1,
                    maximum=1000,
                    value=5,
                    step=1,
                    label="Number of rows 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 File")

        # Processing log output
        log_output = gr.Textbox(
            label="Processing Log",
            lines=30,
            max_lines=50,
            show_copy_button=True,
            interactive=False
        )

        # Examples section
        gr.Markdown("""
        ## 📋 Input Requirements:
        - Excel file with 'auction_description' column
        - Each row should contain real estate auction description text
        - File should be in .xlsx or .xls format

        ## 🎯 What it extracts:
        - 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 for Bangalore (with enhanced precision)
        - Undivided Share (UDS)
        - Plot Area (for plots only)

        ## 🔧 Key Features:
        - ✅ All original processing rules preserved
        - ✅ Enhanced zone-micromarket mapping
        - ✅ Fixed carpet/super built-up area swapping
        - ✅ Comprehensive address cleaning
        - ✅ Unit conversion (sq mtrs → sq.ft, guntas → sq.ft)
        - ✅ Retry logic for API failures
        - ✅ Rate limiting for stable processing
        """)

        process_btn.click(
            fn=process_real_estate_data,
            inputs=[file_input, max_rows_input],
            outputs=[output_file, log_output],
            show_progress=True
        )

    return iface

if __name__ == "__main__":
    # Create and launch the interface
    iface = create_interface()
    iface.launch(
        server_name="0.0.0.0",
        server_port=7860,
        share=True,
        show_error=True
    )