app.py

import gradio as gr
import tempfile
import shutil
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

"""
Real Estate Financial Model Pipeline
Extracts data from PDFs, solves formulas with Gemini API, generates Excel
"""

import re
import json
from pathlib import Path
from typing import Dict, Any, List, Optional
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from pdfminer.high_level import extract_text
import google.generativeai as genai

class RealEstateModelPipeline:
    def __init__(self, gemini_api_key: str):
        """Initialize pipeline with Gemini API key"""
        genai.configure(api_key=gemini_api_key)
        self.model = genai.GenerativeModel('gemini-2.0-flash')
        self.extracted_data = {}
        self.formula_results = {}
        self.structured_data = {}
    
    def safe_divide(self, numerator: float, denominator: float, default: float = 0) -> float:
        """Safe division that returns default instead of error"""
        try:
            if denominator == 0 or denominator is None:
                return default
            return numerator / denominator
        except:
            return default
        
    def extract_pdf_text(self, pdf_path: str) -> str:
        """Extract text from PDF using pdfminer"""
        try:
            text = extract_text(pdf_path)
            return text.strip()
        except Exception as e:
            print(f"Error extracting {pdf_path}: {e}")
            return ""

    def extract_xlsx_text(self, xlsx_path: str) -> str:
        """Extract text from XLSX using pandas and openpyxl"""
        try:
            extracted_content = []
            
            # Try pandas first for data extraction
            try:
                xlsx = pd.ExcelFile(xlsx_path)
                for sheet_name in xlsx.sheet_names:
                    df = pd.read_excel(xlsx, sheet_name=sheet_name)
                    extracted_content.append(f"=== Sheet: {sheet_name} ===")
                    extracted_content.append(df.to_string(index=False))
                    extracted_content.append("\n")
            except:
                pass
            
            # Also try openpyxl for cell-level data
            try:
                wb = load_workbook(xlsx_path, data_only=True)
                for sheet in wb.worksheets:
                    extracted_content.append(f"\n=== Sheet: {sheet.title} (Raw) ===")
                    for row in sheet.iter_rows(values_only=True):
                        row_text = " | ".join([str(cell) if cell is not None else "" for cell in row])
                        if row_text.strip():
                            extracted_content.append(row_text)
            except:
                pass
            
            return "\n".join(extracted_content)
        except Exception as e:
            print(f"Error extracting {xlsx_path}: {e}")
            return ""
            
    def extract_all_pdfs(self, pdf_directory: str) -> Dict[str, str]:
        """Extract text from all PDFs and XLSX files in directory"""
        pdf_dir = Path(pdf_directory)
        extracted_texts = {}
        
        with open('output_file_3.txt', "w", encoding="utf-8") as f:
            # Process PDFs
            for pdf_file in pdf_dir.glob("*.pdf"):
                print(f"Extracting PDF: {pdf_file.name}")
                text = self.extract_pdf_text(str(pdf_file))
                extracted_texts[pdf_file.stem] = text
    
                f.write(f"=== {pdf_file.name} ===\n")
                f.write(text)
                f.write("\n\n" + "="*80 + "\n\n")
            
            # Process XLSX files
            for xlsx_file in pdf_dir.glob("*.xlsx"):
                print(f"Extracting XLSX: {xlsx_file.name}")
                text = self.extract_xlsx_text(str(xlsx_file))
                extracted_texts[xlsx_file.stem] = text
    
                f.write(f"=== {xlsx_file.name} ===\n")
                f.write(text)
                f.write("\n\n" + "="*80 + "\n\n")
    
        self.extracted_data = extracted_texts
        
        return extracted_texts
    
    def extract_address_fallback(self, pdf_texts: Dict[str, str]) -> Optional[str]:
        """Extract address using simple pattern matching as fallback"""
        for name, text in pdf_texts.items():
            if 'Offering_Memorandum' in name or 'offering' in name.lower():
                # Pattern: "Address: <address text>"
                match = re.search(r'Address:\s*(.+?)(?:\n|Property Type:)', text, re.IGNORECASE)
                if match:
                    address = match.group(1).strip()
                    print(f"  ✓ Extracted address via fallback: {address}")
                    return address
        return None
        
    def create_gemini_prompt(self, pdf_texts: Dict[str, str]) -> str:
        """Create comprehensive prompt for Gemini to extract structured data"""
        
        # Build a clear summary of what's in each PDF
        pdf_summary = "\n".join([f"- {name}: {len(text)} characters" for name, text in pdf_texts.items()])
        
        prompt = f"""You are a real estate financial analyst. Extract ALL numerical data from the following PDF texts and return it as a JSON object.

        CRITICAL INSTRUCTIONS:
        1. ONLY extract data that is EXPLICITLY stated in the PDFs - DO NOT estimate or make up values
        2. For missing values, use null (not 0)
        3. Pay close attention to the specific document names - each contains different information
        4. Extract exact numbers as they appear in the documents
    
        AVAILABLE DOCUMENTS:
        {pdf_summary}
    
        PDF CONTENTS:
        """
        for name, text in pdf_texts.items():
            prompt += f"\n{'='*60}\n=== {name} ===\n{'='*60}\n{text}\n"
        
        prompt += """

        EXTRACTION INSTRUCTIONS BY DOCUMENT:
    
        FROM "Offering_Memorandum.pdf":
        - Extract: Address (full address after "Address:")
        - Extract: Property Type (after "Property Type:")
        - Extract: Units (number after "Units:")
    
        FROM "Operating_Expenses_Summary.pdf" (if present):
        - Extract EXACT annual amounts for:
        * Real Estate Taxes
        * Insurance
        * Utilities
        * Repairs & Maint. (or Repairs & Maintenance)
        * Management Fee
        * Payroll
        * Administrative (if listed)
        * Professional Fees (if listed)
    
        FROM "Sales_Comps.pdf":
        - Extract all Price/SF values
        - Calculate average_price_per_sf = average of all Price/SF values
        - Count total number of comps
    
        FROM "Rent_Comps.pdf" (if present):
        - Extract all rent values (numbers before @ symbol)
        - Calculate average_rent = average of all rent values
        - Count total number of rent comps
    
        FROM "Market_Report.pdf":
        - Extract: Vacancy Rate (percentage)
        - Extract: Rent Growth (YoY) (percentage)
    
        FROM "Demographics_Overview.pdf":
        - Extract: Population (3-mi) - the number
        - Extract: Median HH Income - the dollar amount
        - Extract: Transit Score - the number
    
        REQUIRED JSON OUTPUT STRUCTURE:
        {
        "property_info": {
            "address": "EXTRACT FROM Offering_Memorandum.pdf",
            "property_type": "EXTRACT FROM Offering_Memorandum.pdf",
            "units": EXTRACT_NUMBER_FROM_Offering_Memorandum.pdf,
            "gross_sf": null,
            "rentable_sf": null,
            "retail_sf": null
        },
        "acquisition": {
            "land_value": null,
            "price": null,
            "closing_costs": null
        },
        "construction": {
            "construction_cost_per_gsf": null,
            "construction_months": null
        },
        "soft_costs": {
            "architecture_and_interior_cost": null,
            "structural_engineering_cost": null,
            "mep_engineering_cost": null,
            "civil_engineering_cost": null,
            "controlled_inspections_cost": null,
            "surveying_cost": null,
            "utilities_connection_cost": null,
            "advertising_and_marketing_cost": null,
            "accounting_cost": null,
            "monitoring_cost": null,
            "ff_and_e_cost": null,
            "environmental_consultant_fee": null,
            "miscellaneous_consultants_fee": null,
            "general_legal_cost": null,
            "real_estate_taxes_during_construction": null,
            "miscellaneous_admin_cost": null,
            "ibr_cost": null,
            "project_team_cost": null,
            "pem_fees": null,
            "bank_fees": null
        },
        "financing": {
            "ltc_ratio": null,
            "financing_percentage": null,
            "interest_rate_basis_points": null,
            "financing_cost": null,
            "interest_reserve": null
        },
        "operating_expenses": {
            "payroll": EXTRACT_FROM_Operating_Expenses_Summary.pdf,
            "repairs_and_maintenance": EXTRACT_FROM_Operating_Expenses_Summary.pdf,
            "utilities": EXTRACT_FROM_Operating_Expenses_Summary.pdf,
            "administrative": EXTRACT_FROM_Operating_Expenses_Summary.pdf_OR_null,
            "professional_fees": EXTRACT_FROM_Operating_Expenses_Summary.pdf_OR_null,
            "insurance": EXTRACT_FROM_Operating_Expenses_Summary.pdf,
            "property_taxes": EXTRACT_FROM_Operating_Expenses_Summary.pdf,
            "management_fee_percentage": null
        },
        "revenue": {
            "free_market_rent_psf": null,
            "affordable_rent_psf": null,
            "other_income_per_unit": null,
            "vacancy_rate": null,
            "retail_rent_psf": null,
            "parking_income": null
        },
        "sales_comps": {
            "average_price_per_sf": CALCULATE_AVERAGE_FROM_Sales_Comps.pdf,
            "comp_count": COUNT_FROM_Sales_Comps.pdf
        },
        "rent_comps": {
            "average_rent": CALCULATE_AVERAGE_FROM_Rent_Comps.pdf_IF_EXISTS,
            "comp_count": COUNT_FROM_Rent_Comps.pdf_IF_EXISTS
        },
        "market_data": {
            "vacancy_rate": EXTRACT_FROM_Market_Report.pdf,
            "rent_growth_yoy": EXTRACT_FROM_Market_Report.pdf,
            "median_hh_income": EXTRACT_FROM_Demographics_Overview.pdf,
            "population_3mi": EXTRACT_FROM_Demographics_Overview.pdf,
            "transit_score": EXTRACT_FROM_Demographics_Overview.pdf
        },
        "projections": {
            "lease_up_months": null,
            "stabilization_months": null,
            "revenue_inflation_rate": null,
            "expense_inflation_rate": null,
            "hold_period_months": null,
            "exit_cap_rate_decimal": null,
            "sale_cost_percentage": null
        },
        "equity_structure": {
            "gp_pref_rate": null,
            "lp_pref_rate": null,
            "promote_percentage": null
        }
        }
    
        EXAMPLES OF CORRECT EXTRACTION:
    
        Example 1 - From your Offering_Memorandum.pdf:
        "Address: 455 Atlantic Ave, Brooklyn, NY" 
        → "address": "455 Atlantic Ave, Brooklyn, NY"
    
        "Property Type: Retail"
        → "property_type": "Retail"
    
        "Units: 7"
        → "units": 7
    
        Example 2 - From your Operating_Expenses_Summary.pdf:
        "Real Estate Taxes    $91940.2"
        → "property_taxes": 91940.2
    
        "Insurance    $16778.94"
        → "insurance": 16778.94
    
        "Payroll    $44948.21"
        → "payroll": 44948.21
    
        Example 3 - From your Sales_Comps.pdf:
        "Price/SF" column shows: $880, $919, $673, $894
        → "average_price_per_sf": 841.5 (average of these 4 values)
        → "comp_count": 4
    
        Example 4 - From your Market_Report.pdf:
        "Vacancy Rate: 5.71%"
        → "vacancy_rate": 0.0571
    
        "Rent Growth (YoY): 4.18%"
        → "rent_growth_yoy": 0.0418
    
        CRITICAL RULES:
        1. Use EXACT numbers from the PDFs - don't round or modify
        2. Convert percentages to decimals (5.71% → 0.0571)
        3. Remove dollar signs and commas from numbers ($91,940.2 → 91940.2)
        4. If a field is not in ANY PDF, use null
        5. Double-check the document name before extracting - make sure you're looking at the right PDF
    
        Return ONLY valid JSON with no explanations, comments, or markdown formatting."""

        prompt += """
        
        NOTE: Documents may be in PDF or XLSX format. For XLSX files, data is extracted sheet-by-sheet.
        Look for numerical data in tables, columns, and labeled cells.
        
        PDF AND XLSX CONTENTS:
        """
        
        return prompt
    
    def extract_structured_data(self) -> Dict[str, Any]:
        """Use Gemini to extract structured data from PDFs"""
        print("\nProcessing with Gemini API...")
        
        # NEW: Try simple extraction first
        fallback_address = self.extract_address_fallback(self.extracted_data)
        
        prompt = self.create_gemini_prompt(self.extracted_data)
        
        try:
            response = self.model.generate_content(prompt)
            response_text = response.text.strip()
            
            # Clean JSON if wrapped in markdown
            if "```json" in response_text:
                response_text = response_text.split("```json")[1].split("```")[0].strip()
            elif "```" in response_text:
                response_text = response_text.split("```")[1].split("```")[0].strip()
            
            data = json.loads(response_text)
            
            # NEW: Override with fallback if Gemini failed
            if fallback_address and (not data.get('property_info', {}).get('address') or 
                                    data['property_info']['address'] == 'adress'):
                data['property_info']['address'] = fallback_address
                print(f"  ✓ Used fallback address: {fallback_address}")
            
            print("✓ Successfully extracted structured data")
            return data
            
        except Exception as e:
            print(f"Error with Gemini API: {e}")
            data = self.get_default_data_structure()
            # Use fallback even in error case
            if fallback_address:
                data['property_info']['address'] = fallback_address
            return data
    

    def post_process_extracted_data(self, data: Dict[str, Any]) -> Dict[str, Any]:
        """Fill in missing values with intelligent estimates"""
        
        # Get units
        units = data.get('property_info', {}).get('units', 32)
        
        # Estimate SF if missing
        if not data['property_info'].get('gross_sf'):
            data['property_info']['gross_sf'] = units * 1000
        
        if not data['property_info'].get('rentable_sf'):
            data['property_info']['rentable_sf'] = int(data['property_info']['gross_sf'] * 0.85)
        
        # Set retail_sf to 0 if None (most residential projects don't have retail)
        if data['property_info'].get('retail_sf') is None:
            data['property_info']['retail_sf'] = 0
        
        # Get gross_sf for calculations
        gross_sf = data['property_info']['gross_sf']
        
        # Set default construction cost if missing
        if not data['construction'].get('construction_cost_per_gsf'):
            data['construction']['construction_cost_per_gsf'] = 338
        
        if not data['construction'].get('construction_months'):
            data['construction']['construction_months'] = 18
        
        # Estimate land value from sales comps if available
        if not data['acquisition'].get('land_value'):
            sales_comps = data.get('sales_comps', {})
            avg_psf = sales_comps.get('average_price_per_sf')
            if avg_psf:
                data['acquisition']['land_value'] = avg_psf * gross_sf
            else:
                # Use default based on typical Manhattan pricing
                data['acquisition']['land_value'] = 6000000
        
        if not data['acquisition'].get('price'):
            data['acquisition']['price'] = data['acquisition']['land_value']
        
        if not data['acquisition'].get('closing_costs'):
            data['acquisition']['closing_costs'] = 150000
        
        # Estimate soft costs as percentages if null
        total_hard_cost = data['construction']['construction_cost_per_gsf'] * gross_sf
        soft_cost_estimate = total_hard_cost * 0.15  # 15% of hard costs
        
        soft_costs = data.get('soft_costs', {})
        default_soft_cost_values = {
            'architecture_and_interior_cost': soft_cost_estimate * 0.15,
            'structural_engineering_cost': soft_cost_estimate * 0.08,
            'mep_engineering_cost': soft_cost_estimate * 0.10,
            'civil_engineering_cost': soft_cost_estimate * 0.05,
            'controlled_inspections_cost': soft_cost_estimate * 0.03,
            'surveying_cost': soft_cost_estimate * 0.02,
            'utilities_connection_cost': soft_cost_estimate * 0.05,
            'advertising_and_marketing_cost': soft_cost_estimate * 0.06,
            'accounting_cost': soft_cost_estimate * 0.03,
            'monitoring_cost': soft_cost_estimate * 0.02,
            'ff_and_e_cost': soft_cost_estimate * 0.10,
            'environmental_consultant_fee': soft_cost_estimate * 0.02,
            'miscellaneous_consultants_fee': soft_cost_estimate * 0.03,
            'general_legal_cost': soft_cost_estimate * 0.06,
            'real_estate_taxes_during_construction': soft_cost_estimate * 0.10,
            'miscellaneous_admin_cost': soft_cost_estimate * 0.04,
            'ibr_cost': soft_cost_estimate * 0.03,
            'project_team_cost': soft_cost_estimate * 0.15,
            'pem_fees': soft_cost_estimate * 0.08,
            'bank_fees': soft_cost_estimate * 0.05
        }
        
        for key, default_value in default_soft_cost_values.items():
            if soft_costs.get(key) is None:
                soft_costs[key] = default_value
        
        # Set financing defaults if missing
        financing = data.get('financing', {})
        if not financing.get('ltc_ratio'):
            financing['ltc_ratio'] = 0.75
        if not financing.get('financing_percentage'):
            financing['financing_percentage'] = 0.03
        if not financing.get('interest_rate_basis_points'):
            financing['interest_rate_basis_points'] = 350
        if not financing.get('financing_cost'):
            financing['financing_cost'] = 200000
        if not financing.get('interest_reserve'):
            financing['interest_reserve'] = 500000
        
        # Set revenue defaults if missing
        revenue = data.get('revenue', {})
        if not revenue.get('free_market_rent_psf'):
            revenue['free_market_rent_psf'] = 60
        if not revenue.get('affordable_rent_psf'):
            revenue['affordable_rent_psf'] = 35
        if not revenue.get('other_income_per_unit'):
            revenue['other_income_per_unit'] = 100
        if not revenue.get('vacancy_rate'):
            revenue['vacancy_rate'] = 0.05
        if not revenue.get('retail_rent_psf'):
            revenue['retail_rent_psf'] = 45
        if not revenue.get('parking_income'):
            revenue['parking_income'] = 50000
        
        # Ensure operating expenses have defaults
        op_expenses = data.get('operating_expenses', {})
        if not op_expenses.get('payroll'):
            op_expenses['payroll'] = 31136.07
        if not op_expenses.get('repairs_and_maintenance'):
            op_expenses['repairs_and_maintenance'] = 44418.61
        if not op_expenses.get('utilities'):
            op_expenses['utilities'] = 12535.90
        if not op_expenses.get('administrative'):
            op_expenses['administrative'] = 0
        if not op_expenses.get('professional_fees'):
            op_expenses['professional_fees'] = 18789.84
        if not op_expenses.get('insurance'):
            op_expenses['insurance'] = 9341.33
        if not op_expenses.get('property_taxes'):
            op_expenses['property_taxes'] = 118832.22
        if not op_expenses.get('management_fee_percentage'):
            op_expenses['management_fee_percentage'] = 0.03
        
        # Ensure projections have defaults
        projections = data.get('projections', {})
        if not projections.get('lease_up_months'):
            projections['lease_up_months'] = 12
        if not projections.get('stabilization_months'):
            projections['stabilization_months'] = 6
        if not projections.get('revenue_inflation_rate'):
            projections['revenue_inflation_rate'] = 0.03
        if not projections.get('expense_inflation_rate'):
            projections['expense_inflation_rate'] = 0.025
        if not projections.get('hold_period_months'):
            projections['hold_period_months'] = 60
        if not projections.get('exit_cap_rate_decimal'):
            projections['exit_cap_rate_decimal'] = 0.045
        if not projections.get('sale_cost_percentage'):
            projections['sale_cost_percentage'] = 0.02
        
        # Ensure equity structure has defaults
        equity = data.get('equity_structure', {})
        if not equity.get('gp_pref_rate'):
            equity['gp_pref_rate'] = 0.08
        if not equity.get('lp_pref_rate'):
            equity['lp_pref_rate'] = 0.08
        if not equity.get('promote_percentage'):
            equity['promote_percentage'] = 0.20
        
        return data

    def get_default_data_structure(self) -> Dict[str, Any]:
        """Return default data structure with known values from PDFs"""
        # Try to get basic info from extracted text
        units = 32  # Default from your PDFs
        
        # Smart estimation
        gross_sf = units * 1000  # Typical 1000 SF per unit
        rentable_sf = int(gross_sf * 0.85)  # 85% efficiency
        
        return {
            "property_info": {
                "address": "adress",
                "units": units,
                "gross_sf": gross_sf,
                "rentable_sf": rentable_sf,
                "retail_sf": 0  # No retail in this project
            },
            "acquisition": {
                "land_value": None,  # Will be estimated from comps
                "price": None,
                "closing_costs": 150000
            },
            "construction": {
                "construction_cost_per_gsf": 338,
                "construction_months": 18
            },
            "soft_costs": {
                "architecture_and_interior_cost": None,
                "structural_engineering_cost": None,
                "mep_engineering_cost": None,
                "civil_engineering_cost": None,
                "controlled_inspections_cost": None,
                "surveying_cost": None,
                "utilities_connection_cost": None,
                "advertising_and_marketing_cost": None,
                "accounting_cost": None,
                "monitoring_cost": None,
                "ff_and_e_cost": None,
                "environmental_consultant_fee": None,
                "miscellaneous_consultants_fee": None,
                "general_legal_cost": None,
                "real_estate_taxes_during_construction": None,
                "miscellaneous_admin_cost": None,
                "ibr_cost": None,
                "project_team_cost": None,
                "pem_fees": None,
                "bank_fees": None
            },
            "financing": {
                "ltc_ratio": 0.75,
                "financing_percentage": 0.03,
                "interest_rate_basis_points": 350,
                "financing_cost": None,
                "interest_reserve": None
            },
            "operating_expenses": {
                "payroll": 31136.07,  # From PDF
                "repairs_and_maintenance": 44418.61,
                "utilities": 12535.90,
                "administrative": 0,
                "professional_fees": 18789.84,
                "insurance": 9341.33,
                "property_taxes": 118832.22,
                "management_fee_percentage": 0.03
            },
            "revenue": {
                "free_market_rent_psf": 60,
                "affordable_rent_psf": 35,
                "other_income_per_unit": 100,
                "vacancy_rate": 0.05,
                "retail_rent_psf": 45,
                "parking_income": 50000
            },
            "projections": {
                "lease_up_months": 12,
                "stabilization_months": 6,
                "revenue_inflation_rate": 0.03,
                "expense_inflation_rate": 0.025,
                "hold_period_months": 60,
                "exit_cap_rate_decimal": 0.045,
                "sale_cost_percentage": 0.02
            },
            "equity_structure": {
                "gp_pref_rate": 0.08,
                "lp_pref_rate": 0.08,
                "promote_percentage": 0.20
            }
        }
    
    def calculate_all_formulas(self, data: Dict[str, Any]) -> Dict[str, float]:
        """Calculate all formulas in correct dependency order"""
        results = {}
        self.structured_data = data
        # Flatten data for easier access
        d = self.flatten_dict(data)
        
        # Helper function to get value
        def get(key, default=0):
            return d.get(key, default)
        
        # BASIC PROPERTY METRICS
        results['UNITS'] = get('property_info.units')
        results['GROSS_SF'] = get('property_info.gross_sf')
        results['RENTABLE_SF'] = get('property_info.rentable_sf')
        results['RETAIL_SF'] = get('property_info.retail_sf')
        results['BUILDING_EFFICIENCY'] = self.safe_divide(results['RENTABLE_SF'], results['GROSS_SF'])
        
        # ACQUISITION COSTS
        results['LAND_VALUE'] = get('acquisition.land_value')
        results['PRICE'] = get('acquisition.price')
        results['CLOSING_COSTS'] = get('acquisition.closing_costs')
        results['ACQUISITION_FEE'] = results['LAND_VALUE'] * 0.02
        results['TOTAL_ACQUISITION_COST'] = results['LAND_VALUE'] + results['CLOSING_COSTS'] + results['ACQUISITION_FEE']
        
        # Per unit/SF metrics for acquisition
        results['LAND_VALUE_PER_GSF'] = self.safe_divide(results['LAND_VALUE'], results['GROSS_SF'])
        results['LAND_VALUE_PER_RSF'] = self.safe_divide(results['LAND_VALUE'], results['RENTABLE_SF'])
        results['LAND_VALUE_PER_UNIT'] = self.safe_divide(results['LAND_VALUE'], results['UNITS'])
        results['TOTAL_ACQUISITION_COST_PER_GSF'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['GROSS_SF'])
        results['TOTAL_ACQUISITION_COST_PER_RSF'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['RENTABLE_SF'])
        results['TOTAL_ACQUISITION_COST_PER_UNIT'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['UNITS'])
        
        # CONSTRUCTION COSTS
        results['CONSTRUCTION_COST_PER_GSF'] = get('construction.construction_cost_per_gsf')
        results['CONSTRUCTION_MONTHS'] = get('construction.construction_months')
        results['TOTAL_CONSTRUCTION_GMP'] = results['CONSTRUCTION_COST_PER_GSF'] * results['GROSS_SF']
        results['CONSTRUCTION_GMP_PER_GSF'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['GROSS_SF'])
        results['CONSTRUCTION_GMP_PER_RSF'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['RENTABLE_SF'])
        results['CONSTRUCTION_GMP_PER_UNIT'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['UNITS'])
        
        # SOFT COSTS (individual items)
        soft_cost_items = [
            'architecture_and_interior_cost', 'structural_engineering_cost', 'mep_engineering_cost',
            'civil_engineering_cost', 'controlled_inspections_cost', 'surveying_cost',
            'utilities_connection_cost', 'advertising_and_marketing_cost', 'accounting_cost',
            'monitoring_cost', 'ff_and_e_cost', 'environmental_consultant_fee',
            'miscellaneous_consultants_fee', 'general_legal_cost', 'real_estate_taxes_during_construction',
            'miscellaneous_admin_cost', 'ibr_cost', 'project_team_cost', 'pem_fees', 'bank_fees'
        ]
        
        for item in soft_cost_items:
            key = item.upper()
            results[key] = get(f'soft_costs.{item}')
        
        # REVENUE SETUP (needed for some soft costs)
        results['FREE_MARKET_RENT_PSF'] = get('revenue.free_market_rent_psf')
        results['AFFORDABLE_RENT_PSF'] = get('revenue.affordable_rent_psf')
        results['OTHER_INCOME_PER_UNIT'] = get('revenue.other_income_per_unit')
        results['VACANCY_RATE'] = get('revenue.vacancy_rate')
        results['RETAIL_RENT_PSF'] = get('revenue.retail_rent_psf')
        results['PARKING_INCOME'] = get('revenue.parking_income')
        
        # Calculate retail revenue (needed for soft costs)
        results['RETAIL_REVENUE'] = results['RETAIL_RENT_PSF'] * results['RETAIL_SF']
        
        # HPD & IH COST
        results['HPD_AND_IH_COST'] = (3500 * results['UNITS'] * 0.75) + (5000 * results['UNITS'] * 0.25)
        
        # RETAIL TI & LC COST
        results['RETAIL_TI_AND_LC_COST'] = (results['RETAIL_REVENUE'] * 0.3) + (50 * results['RETAIL_SF'])
        
        # TOTAL SOFT COSTS
        soft_cost_sum = sum([results[item.upper()] for item in soft_cost_items])
        results['TOTAL_SOFT_COST'] = soft_cost_sum + results['HPD_AND_IH_COST'] + results['RETAIL_TI_AND_LC_COST']
        results['TOTAL_SOFT_COST_PER_GSF'] = self.safe_divide(results['TOTAL_SOFT_COST'],results['GROSS_SF'])
        
        # OPERATING EXPENSES (for reserves calculation)
        results['PAYROLL'] = get('operating_expenses.payroll')
        results['REPAIRS_AND_MAINTENANCE'] = get('operating_expenses.repairs_and_maintenance')
        results['UTILITIES'] = get('operating_expenses.utilities')
        results['ADMINISTRATIVE'] = get('operating_expenses.administrative')
        results['PROFESSIONAL_FEES'] = get('operating_expenses.professional_fees')
        results['INSURANCE'] = get('operating_expenses.insurance')
        results['PROPERTY_TAXES'] = get('operating_expenses.property_taxes')
        results['MANAGEMENT_FEE_PERCENTAGE'] = get('operating_expenses.management_fee_percentage')
        
        results['TOTAL_OPERATING_EXPENSES'] = (results['PAYROLL'] + results['REPAIRS_AND_MAINTENANCE'] + 
                                               results['UTILITIES'] + results['ADMINISTRATIVE'] + 
                                               results['PROFESSIONAL_FEES'] + results['INSURANCE'] + 
                                               results['PROPERTY_TAXES'])
        
        # CONTINGENCY & RESERVES
        results['CONTINGENCY_COST'] = (results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_SOFT_COST']) * 0.05
        results['DEVELOPMENT_FEE'] = (results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_SOFT_COST']) * 0.04
        results['OPERATING_RESERVE'] = results['TOTAL_OPERATING_EXPENSES'] * 0.2
        
        results['FINANCING_COST'] = get('financing.financing_cost')
        results['INTEREST_RESERVE'] = get('financing.interest_reserve')
        
        # TOTAL PROJECT COST (before financing)
        results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] = (
            results['TOTAL_SOFT_COST'] + 
            results['TOTAL_CONSTRUCTION_GMP'] + 
            results['TOTAL_ACQUISITION_COST'] + 
            results['CONTINGENCY_COST'] + 
            results['DEVELOPMENT_FEE'] + 
            results['FINANCING_COST'] + 
            results['INTEREST_RESERVE'] + 
            results['OPERATING_RESERVE']
        )
        
        results['TOTAL_PROJECT_COST_PER_GSF'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['GROSS_SF'])
        results['TOTAL_PROJECT_COST_PER_RSF'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['RENTABLE_SF'])
        results['TOTAL_PROJECT_COST_PER_UNIT'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['UNITS'])
        
        # FINANCING CALCULATIONS
        results['LTC_RATIO'] = get('financing.ltc_ratio')
        results['FINANCING_PERCENTAGE'] = get('financing.financing_percentage')
        results['INTEREST_RATE_BASIS_POINTS'] = get('financing.interest_rate_basis_points')
        
        results['PRE_LTC_BUDGET'] = (results['TOTAL_SOFT_COST'] + results['CONTINGENCY_COST'] + 
                                     results['DEVELOPMENT_FEE'] + results['OPERATING_RESERVE'] + 
                                     results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_ACQUISITION_COST'])
        
        results['LOAN_AMOUNT'] = results['LTC_RATIO'] * results['PRE_LTC_BUDGET']
        results['FINANCING_AMOUNT'] = results['FINANCING_PERCENTAGE'] * results['LOAN_AMOUNT']
        results['INTEREST_RATE_DECIMAL'] = (results['INTEREST_RATE_BASIS_POINTS'] + 430) / 10000
        results['CONSTRUCTION_INTEREST'] = results['LOAN_AMOUNT'] * 0.7 * (results['INTEREST_RATE_DECIMAL'] / 12) * results['CONSTRUCTION_MONTHS']
        
        # DEBT & EQUITY
        results['TOTAL_DEBT'] = results['CONSTRUCTION_INTEREST'] + results['LOAN_AMOUNT'] + results['FINANCING_AMOUNT']
        results['TOTAL_EQUITY'] = results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] - results['TOTAL_DEBT']
        results['DEBT_PERCENTAGE'] = results['TOTAL_DEBT'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0
        results['EQUITY_PERCENTAGE'] = results['TOTAL_EQUITY'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0
        results['TOTAL_CAPITAL_STACK'] = results['TOTAL_DEBT'] + results['TOTAL_EQUITY']
        
        results['DEBT_PER_GSF'] = self.safe_divide(results['TOTAL_DEBT'],results['GROSS_SF'])
        results['EQUITY_PER_GSF'] = self.safe_divide(results['TOTAL_EQUITY'],results['GROSS_SF'])
        results['DEBT_PER_UNIT'] = self.safe_divide(results['TOTAL_DEBT'], results['UNITS'])

        results['EQUITY_PER_UNIT'] = self.safe_divide(results['TOTAL_EQUITY'], results['UNITS'])
        
        # OPERATING EXPENSE METRICS
        results['PAYROLL_PER_UNIT'] = self.safe_divide(results['PAYROLL'], results['UNITS'])
        results['REPAIRS_AND_MAINTENANCE_PER_UNIT'] = self.safe_divide(results['REPAIRS_AND_MAINTENANCE'], results['UNITS'])
        results['UTILITIES_PER_UNIT'] = self.safe_divide(results['UTILITIES'], results['UNITS'])
        results['ADMIN_AND_PROFESSIONAL_PER_UNIT'] = self.safe_divide((results['ADMINISTRATIVE'] + results['PROFESSIONAL_FEES']), results['UNITS'])
        results['INSURANCE_PER_UNIT'] = self.safe_divide(results['INSURANCE'], results['UNITS'])
        results['OPERATING_EXPENSES_PER_UNIT'] = self.safe_divide(results['TOTAL_OPERATING_EXPENSES'], results['UNITS'])
        results['OPERATING_EXPENSES_PER_GSF'] = self.safe_divide(results['TOTAL_OPERATING_EXPENSES'],results['GROSS_SF'])
        
        # REVENUE CALCULATIONS
        results['LEASE_UP_MONTHS'] = get('projections.lease_up_months')
        results['STABILIZATION_MONTHS'] = get('projections.stabilization_months')
        results['REVENUE_INFLATION_RATE'] = get('projections.revenue_inflation_rate')
        results['EXPENSE_INFLATION_RATE'] = get('projections.expense_inflation_rate')
        
        results['TRENDING_TERM'] = results['LEASE_UP_MONTHS'] + results['STABILIZATION_MONTHS']
        results['TERM_REVENUE_INFLATION'] = (1 + results['REVENUE_INFLATION_RATE']) ** (results['TRENDING_TERM'] / 12)
        results['TERM_EXPENSE_INFLATION'] = (1 + results['EXPENSE_INFLATION_RATE']) ** (results['TRENDING_TERM'] / 12)
        
        results['GROSS_POTENTIAL_FREE_MARKET_RENT'] = results['FREE_MARKET_RENT_PSF'] * 0.75 * results['RENTABLE_SF']
        results['GROSS_POTENTIAL_AFFORDABLE_RENT'] = results['AFFORDABLE_RENT_PSF'] * 0.25 * results['RENTABLE_SF']
        results['OTHER_INCOME'] = results['OTHER_INCOME_PER_UNIT'] * results['UNITS'] * 12 * 0.75
        results['VACANCY_LOSS'] = results['VACANCY_RATE'] * (results['OTHER_INCOME'] + results['GROSS_POTENTIAL_FREE_MARKET_RENT'] + results['GROSS_POTENTIAL_AFFORDABLE_RENT'])
        results['EFFECTIVE_GROSS_INCOME'] = results['GROSS_POTENTIAL_FREE_MARKET_RENT'] - results['VACANCY_LOSS'] + results['OTHER_INCOME'] + results['GROSS_POTENTIAL_AFFORDABLE_RENT']
        
        results['MANAGEMENT_FEE'] = results['MANAGEMENT_FEE_PERCENTAGE'] * results['EFFECTIVE_GROSS_INCOME']
        results['REAL_ESTATE_TAXES'] = results['GROSS_SF'] * 30 * 0.1
        results['TOTAL_EXPENSES'] = results['PAYROLL'] + results['REPAIRS_AND_MAINTENANCE'] + results['UTILITIES'] + results['REAL_ESTATE_TAXES'] + results['MANAGEMENT_FEE']
        
        # NOI & RETURNS
        results['NET_OPERATING_INCOME'] = results['EFFECTIVE_GROSS_INCOME'] - results['TOTAL_EXPENSES'] + results['PARKING_INCOME'] + results['RETAIL_REVENUE']
        results['NOI_PER_UNIT'] = self.safe_divide(results['NET_OPERATING_INCOME'], results['UNITS'])
        results['NOI_PER_GSF'] = self.safe_divide(results['NET_OPERATING_INCOME'],results['GROSS_SF'])
        results['CAP_RATE'] = (results['NET_OPERATING_INCOME'] / results['PRICE']) * 100 if results['PRICE'] > 0 else 0
        
        results['STABILIZED_YIELD_ON_COST'] = (((results['EFFECTIVE_GROSS_INCOME'] + results['RETAIL_REVENUE'] - results['GROSS_POTENTIAL_AFFORDABLE_RENT']) * results['TERM_REVENUE_INFLATION']) - (results['TOTAL_EXPENSES'] * results['TERM_EXPENSE_INFLATION'])) + results['GROSS_POTENTIAL_AFFORDABLE_RENT']
        
        results['YIELD_ON_COST_PERCENTAGE'] = results['NET_OPERATING_INCOME'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0
        results['STABILIZED_YIELD_ON_COST_PERCENTAGE'] = results['STABILIZED_YIELD_ON_COST'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0
        
        results['ANNUAL_DEBT_SERVICE'] = results['LOAN_AMOUNT'] * results['INTEREST_RATE_DECIMAL']
        results['CASH_ON_CASH_RETURN'] = ((results['NET_OPERATING_INCOME'] - results['ANNUAL_DEBT_SERVICE']) / results['TOTAL_EQUITY']) * 100 if results['TOTAL_EQUITY'] > 0 else 0
        results['DEBT_SERVICE_COVERAGE_RATIO'] = results['NET_OPERATING_INCOME'] / results['ANNUAL_DEBT_SERVICE'] if results['ANNUAL_DEBT_SERVICE'] > 0 else 0
        
        # EXIT & EQUITY WATERFALL
        results['EXIT_CAP_RATE_DECIMAL'] = get('projections.exit_cap_rate_decimal')
        results['SALE_COST_PERCENTAGE'] = get('projections.sale_cost_percentage')
        results['HOLD_PERIOD_MONTHS'] = get('projections.hold_period_months')
        
        results['PROPERTY_VALUE_ON_SALE'] = (results['STABILIZED_YIELD_ON_COST'] / results['EXIT_CAP_RATE_DECIMAL']) + (results['STABILIZED_YIELD_ON_COST'] * 0.25) if results['EXIT_CAP_RATE_DECIMAL'] > 0 else 0
        results['SALE_COST'] = results['SALE_COST_PERCENTAGE'] * results['PROPERTY_VALUE_ON_SALE']
        results['NET_SALE_PROCEEDS'] = results['PROPERTY_VALUE_ON_SALE'] - results['SALE_COST']
        results['CASH_REMAINING_AFTER_LOAN_PAYBACK'] = results['NET_SALE_PROCEEDS'] - results['TOTAL_DEBT']
        
        results['GP_PREF_RATE'] = get('equity_structure.gp_pref_rate')
        results['LP_PREF_RATE'] = get('equity_structure.lp_pref_rate')
        results['PROMOTE_PERCENTAGE'] = get('equity_structure.promote_percentage')
        
        results['GP_INVESTMENT'] = results['TOTAL_EQUITY'] * 0.2
        results['LP_INVESTMENT'] = results['TOTAL_EQUITY'] - results['GP_INVESTMENT']
        results['GP_PREFERRED_RETURN_WITH_PRINCIPAL'] = (1 + results['GP_PREF_RATE'] / 12) ** results['HOLD_PERIOD_MONTHS'] * results['GP_INVESTMENT']
        results['LP_PREFERRED_RETURN_WITH_PRINCIPAL'] = (1 + results['LP_PREF_RATE'] / 12) ** results['HOLD_PERIOD_MONTHS'] * results['LP_INVESTMENT']
        results['CASH_REMAINING_AFTER_PREFERRED'] = results['CASH_REMAINING_AFTER_LOAN_PAYBACK'] - results['LP_PREFERRED_RETURN_WITH_PRINCIPAL'] - results['GP_PREFERRED_RETURN_WITH_PRINCIPAL']
        results['PROMOTE_ON_JOINT_VENTURE'] = results['PROMOTE_PERCENTAGE'] * results['CASH_REMAINING_AFTER_PREFERRED']
        results['CASH_TO_LP'] = (results['CASH_REMAINING_AFTER_PREFERRED'] - results['PROMOTE_ON_JOINT_VENTURE']) * (results['LP_INVESTMENT'] / (results['LP_INVESTMENT'] + results['GP_INVESTMENT'])) if (results['LP_INVESTMENT'] + results['GP_INVESTMENT']) > 0 else 0
        results['NET_TO_LP_INVESTOR'] = results['CASH_TO_LP'] + results['LP_PREFERRED_RETURN_WITH_PRINCIPAL']
        results['LP_MULTIPLE'] = results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT'] if results['LP_INVESTMENT'] > 0 else 0
        # results['IRR_TO_LP'] = ((results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT']) ** (12 / results['HOLD_PERIOD_MONTHS']) - 1) * 100 if results['LP_INVESTMENT'] > 0 and results['HOLD_PERIOD_MONTHS'] > 0 else 0
        # IRR calculation with complex number handling
        if results['LP_INVESTMENT'] > 0 and results['HOLD_PERIOD_MONTHS'] > 0:
            irr_base = results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT']
            if irr_base > 0:
                results['IRR_TO_LP'] = ((irr_base) ** (12 / results['HOLD_PERIOD_MONTHS']) - 1) * 100
            else:
                results['IRR_TO_LP'] = -100  # Total loss
        else:
            results['IRR_TO_LP'] = 0

        # BLENDED RENT CALCULATIONS
        results['BLENDED_RENT_PER_RSF'] = (results['FREE_MARKET_RENT_PSF'] * 0.75) + (results['AFFORDABLE_RENT_PSF'] * 0.25)
        results['TOTAL_FREE_MARKET_RENT'] = results['FREE_MARKET_RENT_PSF'] * 425 / 12
        results['TOTAL_BLENDED_RENT'] = results['BLENDED_RENT_PER_RSF'] * 750 / 12
        results['FREE_MARKET_RENT_PER_SF'] = results['TOTAL_FREE_MARKET_RENT'] * 110 / 12
        results['AFFORDABLE_RENT_PER_SF'] = results['AFFORDABLE_RENT_PSF'] * 110 / 12
        results['BLENDED_RENT_PER_SF'] = results['TOTAL_BLENDED_RENT'] * 110 / 12
        results['AVERAGE_RENT_PER_UNIT'] = self.safe_divide((results['GROSS_POTENTIAL_FREE_MARKET_RENT']+results['GROSS_POTENTIAL_AFFORDABLE_RENT']), results['UNITS'])
        results['RENT_PER_UNIT_PER_MONTH'] = results['AVERAGE_RENT_PER_UNIT'] / 12
        
        # EGI PERCENTAGES
        if results['EFFECTIVE_GROSS_INCOME'] > 0:
            results['PAYROLL_PERCENTAGE_OF_EGI'] = results['PAYROLL'] / results['EFFECTIVE_GROSS_INCOME']
            results['REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI'] = results['REPAIRS_AND_MAINTENANCE'] / results['EFFECTIVE_GROSS_INCOME']
            results['UTILITIES_PERCENTAGE_OF_EGI'] = results['UTILITIES'] / results['EFFECTIVE_GROSS_INCOME']
            results['ADMIN_AND_PROFESSIONAL_PERCENTAGE_OF_EGI'] = (results['ADMINISTRATIVE'] + results['PROFESSIONAL_FEES']) / results['EFFECTIVE_GROSS_INCOME']
            results['INSURANCE_PERCENTAGE_OF_EGI'] = results['INSURANCE'] / results['EFFECTIVE_GROSS_INCOME']
            results['PROFESSIONAL_FEES_PERCENTAGE_OF_EGI'] = results['PROFESSIONAL_FEES'] / results['EFFECTIVE_GROSS_INCOME']
            results['TOTAL_OPERATING_EXPENSES_PERCENTAGE_OF_EGI'] = results['TOTAL_OPERATING_EXPENSES'] / results['EFFECTIVE_GROSS_INCOME']
        else:
            results['PAYROLL_PERCENTAGE_OF_EGI'] = 0
            results['REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI'] = 0
            results['UTILITIES_PERCENTAGE_OF_EGI'] = 0
            results['ADMIN_AND_PROFESSIONAL_PERCENTAGE_OF_EGI'] = 0
            results['INSURANCE_PERCENTAGE_OF_EGI'] = 0
            results['PROFESSIONAL_FEES_PERCENTAGE_OF_EGI'] = 0
            results['TOTAL_OPERATING_EXPENSES_PERCENTAGE_OF_EGI'] = 0
        
        self.formula_results = results
        return results
    
    def flatten_dict(self, d: Dict[str, Any], parent_key: str = '', sep: str = '.') -> Dict[str, Any]:
        """Flatten nested dictionary"""
        items = []
        for k, v in d.items():
            new_key = f"{parent_key}{sep}{k}" if parent_key else k
            if isinstance(v, dict):
                items.extend(self.flatten_dict(v, new_key, sep=sep).items())
            else:
                items.append((new_key, v))
        return dict(items)
    
    def generate_excel(self, output_path: str = "Real_Estate_Financial_Model.xlsx"):
        """Generate professional Excel file with all calculations"""
        try:
            # Validate critical values before Excel generation
            r = self.formula_results
            
            print("  Validating calculations...")
            critical_values = {
                'UNITS': r.get('UNITS', 0),
                'GROSS_SF': r.get('GROSS_SF', 0),
                'RENTABLE_SF': r.get('RENTABLE_SF', 0),
                'EFFECTIVE_GROSS_INCOME': r.get('EFFECTIVE_GROSS_INCOME', 0),
                'TOTAL_PROJECT_COST': r.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0)
            }
            
            warnings = []
            for key, value in critical_values.items():
                if value == 0:
                    warnings.append(f"    WARNING: {key} is zero or missing")
            
            if warnings:
                print("\n".join(warnings))
                print("    Continuing with available data...\n")
            
            wb = openpyxl.Workbook()
            
            # Remove default sheet
            if 'Sheet' in wb.sheetnames:
                wb.remove(wb['Sheet'])
            
            # Create sheets with error handling
            print("  Creating Executive Summary...")
            self.create_summary_sheet(wb)
            
            print("  Creating Acquisition sheet...")
            self.create_acquisition_sheet(wb)
            
            print("  Creating Construction sheet...")
            self.create_construction_sheet(wb)
            
            print("  Creating Soft Costs sheet...")
            self.create_soft_costs_sheet(wb)
            
            print("  Creating Financing sheet...")
            self.create_financing_sheet(wb)
            
            print("  Creating Operations sheet...")
            self.create_operations_sheet(wb)
            
            print("  Creating Returns sheet...")
            self.create_returns_sheet(wb)
            
            # Save workbook
            wb.save(output_path)
            print(f"✓ Excel file generated: {output_path}")
            return output_path
        except Exception as e:
            print(f"ERROR generating Excel: {e}")
            import traceback
            traceback.print_exc()
            raise
    
    def create_summary_sheet(self, wb):
        """Create executive summary sheet"""
        ws = wb.create_sheet("Executive Summary", 0)
        
        # Styles
        header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
        header_font = Font(color="FFFFFF", bold=True, size=12)
        subheader_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        subheader_font = Font(color="FFFFFF", bold=True, size=11)
        
        r = self.formula_results
        
        # Title
        ws['A1'] = "REAL ESTATE DEVELOPMENT FINANCIAL MODEL"
        ws['A1'].font = Font(bold=True, size=16)
        ws.merge_cells('A1:D1')
        
        # Property Information
        row = 3
        ws[f'A{row}'] = "PROPERTY INFORMATION"
        ws[f'A{row}'].fill = header_fill
        ws[f'A{row}'].font = header_font
        ws.merge_cells(f'A{row}:D{row}')

        address = self.structured_data.get('property_info', {}).get('address', 'N/A')

        row += 1
        data = [
            ("Address:", address),
            ("Units:", r.get('UNITS', 0)),
            ("Gross Square Feet:", f"{r.get('GROSS_SF', 0):,.0f}"),
            ("Rentable Square Feet:", f"{r.get('RENTABLE_SF', 0):,.0f}"),
            ("Building Efficiency:", f"{r.get('BUILDING_EFFICIENCY', 0):.2%}"),
        ]
        
        for label, value in data:
            ws[f'A{row}'] = label
            ws[f'A{row}'].font = Font(bold=True)
            ws[f'B{row}'] = value
            row += 1
        
        # Project Costs Summary
        row += 1
        ws[f'A{row}'] = "PROJECT COSTS SUMMARY"
        ws[f'A{row}'].fill = header_fill
        ws[f'A{row}'].font = header_font
        ws.merge_cells(f'A{row}:D{row}')
        
        row += 1
        ws[f'A{row}'] = "Category"
        ws[f'B{row}'] = "Total Cost"
        ws[f'C{row}'] = "Per GSF"
        ws[f'D{row}'] = "Per Unit"
        for col in ['A', 'B', 'C', 'D']:
            ws[f'{col}{row}'].fill = subheader_fill
            ws[f'{col}{row}'].font = subheader_font
        
        row += 1
        cost_summary = [
            ("Acquisition", r.get('TOTAL_ACQUISITION_COST', 0), r.get('TOTAL_ACQUISITION_COST_PER_GSF', 0), r.get('TOTAL_ACQUISITION_COST_PER_UNIT', 0)),
            ("Construction", r.get('TOTAL_CONSTRUCTION_GMP', 0), r.get('CONSTRUCTION_GMP_PER_GSF', 0), r.get('CONSTRUCTION_GMP_PER_UNIT', 0)),
            ("Soft Costs", r.get('TOTAL_SOFT_COST', 0), r.get('TOTAL_SOFT_COST_PER_GSF', 0), r.get('TOTAL_SOFT_COST_PER_GSF', 0) * r.get('GROSS_SF', 0) / r.get('UNITS', 1)),
            ("Contingency", r.get('CONTINGENCY_COST', 0), r.get('CONTINGENCY_COST', 0) / r.get('GROSS_SF', 1), r.get('CONTINGENCY_COST', 0) / r.get('UNITS', 1)),
            ("Development Fee", r.get('DEVELOPMENT_FEE', 0), r.get('DEVELOPMENT_FEE', 0) / r.get('GROSS_SF', 1), r.get('DEVELOPMENT_FEE', 0) / r.get('UNITS', 1)),
            ("Financing & Reserves", r.get('FINANCING_COST', 0) + r.get('INTEREST_RESERVE', 0) + r.get('OPERATING_RESERVE', 0), 0, 0),
        ]
        
        for label, total, per_gsf, per_unit in cost_summary:
            ws[f'A{row}'] = label
            ws[f'B{row}'] = total
            ws[f'B{row}'].number_format = '$#,##0'
            ws[f'C{row}'] = per_gsf
            ws[f'C{row}'].number_format = '$#,##0.00'
            ws[f'D{row}'] = per_unit
            ws[f'D{row}'].number_format = '$#,##0'
            row += 1
        
        # Total
        ws[f'A{row}'] = "TOTAL PROJECT COST"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        ws[f'C{row}'] = r.get('TOTAL_PROJECT_COST_PER_GSF', 0)
        ws[f'C{row}'].number_format = '$#,##0.00'
        ws[f'C{row}'].font = Font(bold=True)
        ws[f'D{row}'] = r.get('TOTAL_PROJECT_COST_PER_UNIT', 0)
        ws[f'D{row}'].number_format = '$#,##0'
        ws[f'D{row}'].font = Font(bold=True)
        
        # Capital Stack
        row += 2
        ws[f'A{row}'] = "CAPITAL STACK"
        ws[f'A{row}'].fill = header_fill
        ws[f'A{row}'].font = header_font
        ws.merge_cells(f'A{row}:D{row}')
        
        row += 1
        ws[f'A{row}'] = "Source"
        ws[f'B{row}'] = "Amount"
        ws[f'C{row}'] = "Percentage"
        ws[f'D{row}'] = "Per Unit"
        for col in ['A', 'B', 'C', 'D']:
            ws[f'{col}{row}'].fill = subheader_fill
            ws[f'{col}{row}'].font = subheader_font
        
        row += 1
        ws[f'A{row}'] = "Total Debt"
        ws[f'B{row}'] = r.get('TOTAL_DEBT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'C{row}'] = r.get('DEBT_PERCENTAGE', 0)
        ws[f'C{row}'].number_format = '0.00%'
        ws[f'D{row}'] = r.get('DEBT_PER_UNIT', 0)
        ws[f'D{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Total Equity"
        ws[f'B{row}'] = r.get('TOTAL_EQUITY', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'C{row}'] = r.get('EQUITY_PERCENTAGE', 0)
        ws[f'C{row}'].number_format = '0.00%'
        ws[f'D{row}'] = r.get('EQUITY_PER_UNIT', 0)
        ws[f'D{row}'].number_format = '$#,##0'
        
        # Returns Summary
        row += 2
        ws[f'A{row}'] = "INVESTMENT RETURNS"
        ws[f'A{row}'].fill = header_fill
        ws[f'A{row}'].font = header_font
        ws.merge_cells(f'A{row}:D{row}')
        
        row += 1
        returns_data = [
            ("Stabilized NOI:", f"${r.get('NET_OPERATING_INCOME', 0):,.0f}"),
            ("Yield on Cost:", f"{r.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}"),
            ("Stabilized Yield on Cost:", f"{r.get('STABILIZED_YIELD_ON_COST_PERCENTAGE', 0):.2%}"),
            ("Cash-on-Cash Return:", f"{r.get('CASH_ON_CASH_RETURN', 0):.2f}%"),
            ("DSCR:", f"{r.get('DEBT_SERVICE_COVERAGE_RATIO', 0):.2f}x"),
            ("LP IRR:", f"{float(r.get('IRR_TO_LP', 0).real if isinstance(r.get('IRR_TO_LP', 0), complex) else r.get('IRR_TO_LP', 0)):.2f}%"),
            ("LP Multiple:", f"{r.get('LP_MULTIPLE', 0):.2f}x"),
        ]
        
        for label, value in returns_data:
            ws[f'A{row}'] = label
            ws[f'A{row}'].font = Font(bold=True)
            ws[f'B{row}'] = value
            row += 1
        
        # Adjust column widths
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 18
        ws.column_dimensions['C'].width = 15
        ws.column_dimensions['D'].width = 15
    
    def create_acquisition_sheet(self, wb):
        """Create acquisition costs detail sheet"""
        ws = wb.create_sheet("Acquisition")
        r = self.formula_results
        
        # Header
        ws['A1'] = "ACQUISITION COSTS"
        ws['A1'].font = Font(bold=True, size=14)
        ws.merge_cells('A1:E1')
        
        # Column headers
        row = 3
        headers = ["Item", "Total Cost", "Per GSF", "Per RSF", "Per Unit"]
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=row, column=col_idx, value=header)
            cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
            cell.font = Font(color="FFFFFF", bold=True)
        
        # Data
        row += 1
        data = [
            ("Land Value", r.get('LAND_VALUE', 0), r.get('LAND_VALUE_PER_GSF', 0), r.get('LAND_VALUE_PER_RSF', 0), r.get('LAND_VALUE_PER_UNIT', 0)),
            ("Closing Costs", r.get('CLOSING_COSTS', 0), r.get('CLOSING_COSTS', 0) / r.get('GROSS_SF', 1), r.get('CLOSING_COSTS', 0) / r.get('RENTABLE_SF', 1), r.get('CLOSING_COSTS', 0) / r.get('UNITS', 1)),
            ("Acquisition Fee (2%)", r.get('ACQUISITION_FEE', 0), r.get('ACQUISITION_FEE', 0) / r.get('GROSS_SF', 1), r.get('ACQUISITION_FEE', 0) / r.get('RENTABLE_SF', 1), r.get('ACQUISITION_FEE', 0) / r.get('UNITS', 1)),
        ]
        
        for item, total, per_gsf, per_rsf, per_unit in data:
            ws.cell(row=row, column=1, value=item)
            ws.cell(row=row, column=2, value=total).number_format = '$#,##0'
            ws.cell(row=row, column=3, value=per_gsf).number_format = '$#,##0.00'
            ws.cell(row=row, column=4, value=per_rsf).number_format = '$#,##0.00'
            ws.cell(row=row, column=5, value=per_unit).number_format = '$#,##0'
            row += 1
        
        # Total
        ws.cell(row=row, column=1, value="TOTAL ACQUISITION COST").font = Font(bold=True)
        ws.cell(row=row, column=2, value=r.get('TOTAL_ACQUISITION_COST', 0)).number_format = '$#,##0'
        ws.cell(row=row, column=2).font = Font(bold=True)
        ws.cell(row=row, column=3, value=r.get('TOTAL_ACQUISITION_COST_PER_GSF', 0)).number_format = '$#,##0.00'
        ws.cell(row=row, column=3).font = Font(bold=True)
        ws.cell(row=row, column=4, value=r.get('TOTAL_ACQUISITION_COST_PER_RSF', 0)).number_format = '$#,##0.00'
        ws.cell(row=row, column=4).font = Font(bold=True)
        ws.cell(row=row, column=5, value=r.get('TOTAL_ACQUISITION_COST_PER_UNIT', 0)).number_format = '$#,##0'
        ws.cell(row=row, column=5).font = Font(bold=True)
        
        # Adjust widths
        for col in range(1, 6):
            ws.column_dimensions[get_column_letter(col)].width = 20
    
    def create_construction_sheet(self, wb):
        """Create construction costs sheet"""
        ws = wb.create_sheet("Construction")
        r = self.formula_results
        
        ws['A1'] = "CONSTRUCTION COSTS"
        ws['A1'].font = Font(bold=True, size=14)
        
        row = 3
        ws[f'A{row}'] = "Construction Cost per GSF:"
        ws[f'B{row}'] = r.get('CONSTRUCTION_COST_PER_GSF', 0)
        ws[f'B{row}'].number_format = '$#,##0.00'
        
        row += 1
        ws[f'A{row}'] = "Gross Square Feet:"
        ws[f'B{row}'] = r.get('GROSS_SF', 0)
        ws[f'B{row}'].number_format = '#,##0'
        
        row += 2
        ws[f'A{row}'] = "Total Construction GMP:"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('TOTAL_CONSTRUCTION_GMP', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        
        row += 2
        ws[f'A{row}'] = "Construction Duration:"
        ws[f'B{row}'] = f"{r.get('CONSTRUCTION_MONTHS', 0)} months"
        
        ws.column_dimensions['A'].width = 30
        ws.column_dimensions['B'].width = 20
    
    def create_soft_costs_sheet(self, wb):
        """Create soft costs detail sheet"""
        ws = wb.create_sheet("Soft Costs")
        r = self.formula_results
        
        ws['A1'] = "SOFT COSTS BUDGET"
        ws['A1'].font = Font(bold=True, size=14)
        
        row = 3
        headers = ["Category", "Total Cost", "Per GSF"]
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=row, column=col_idx, value=header)
            cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
            cell.font = Font(color="FFFFFF", bold=True)
        
        row += 1
        soft_cost_items = [
            ("Architecture & Interior Design", 'ARCHITECTURE_AND_INTERIOR_COST'),
            ("Structural Engineering", 'STRUCTURAL_ENGINEERING_COST'),
            ("MEP Engineering", 'MEP_ENGINEERING_COST'),
            ("Civil Engineering", 'CIVIL_ENGINEERING_COST'),
            ("Controlled Inspections", 'CONTROLLED_INSPECTIONS_COST'),
            ("Surveying", 'SURVEYING_COST'),
            ("Utilities Connection", 'UTILITIES_CONNECTION_COST'),
            ("Advertising & Marketing", 'ADVERTISING_AND_MARKETING_COST'),
            ("Accounting", 'ACCOUNTING_COST'),
            ("Monitoring", 'MONITORING_COST'),
            ("FF&E", 'FF_AND_E_COST'),
            ("Environmental Consultant", 'ENVIRONMENTAL_CONSULTANT_FEE'),
            ("Miscellaneous Consultants", 'MISCELLANEOUS_CONSULTANTS_FEE'),
            ("General Legal", 'GENERAL_LEGAL_COST'),
            ("RE Taxes During Construction", 'REAL_ESTATE_TAXES_DURING_CONSTRUCTION'),
            ("Miscellaneous Admin", 'MISCELLANEOUS_ADMIN_COST'),
            ("IBR Cost", 'IBR_COST'),
            ("Project Team", 'PROJECT_TEAM_COST'),
            ("PEM Fees", 'PEM_FEES'),
            ("Bank Fees", 'BANK_FEES'),
            ("HPD & IH Costs", 'HPD_AND_IH_COST'),
            ("Retail TI & LC", 'RETAIL_TI_AND_LC_COST'),
        ]
        
        for label, key in soft_cost_items:
            cost = r.get(key, 0)
            per_gsf = cost / r.get('GROSS_SF', 1) if r.get('GROSS_SF', 0) > 0 else 0
            ws.cell(row=row, column=1, value=label)
            ws.cell(row=row, column=2, value=cost).number_format = '$#,##0'
            ws.cell(row=row, column=3, value=per_gsf).number_format = '$#,##0.00'
            row += 1
        
        # Total
        ws.cell(row=row, column=1, value="TOTAL SOFT COSTS").font = Font(bold=True)
        ws.cell(row=row, column=2, value=r.get('TOTAL_SOFT_COST', 0)).number_format = '$#,##0'
        ws.cell(row=row, column=2).font = Font(bold=True)
        ws.cell(row=row, column=3, value=r.get('TOTAL_SOFT_COST_PER_GSF', 0)).number_format = '$#,##0.00'
        ws.cell(row=row, column=3).font = Font(bold=True)
        
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 18
        ws.column_dimensions['C'].width = 15
    
    def create_financing_sheet(self, wb):
        """Create financing structure sheet"""
        ws = wb.create_sheet("Financing")
        r = self.formula_results
        
        ws['A1'] = "FINANCING STRUCTURE"
        ws['A1'].font = Font(bold=True, size=14)
        
        row = 3
        ws[f'A{row}'] = "Pre-LTC Budget:"
        ws[f'B{row}'] = r.get('PRE_LTC_BUDGET', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "LTC Ratio:"
        ws[f'B{row}'] = r.get('LTC_RATIO', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        row += 1
        ws[f'A{row}'] = "Loan Amount:"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('LOAN_AMOUNT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        
        row += 2
        ws[f'A{row}'] = "Financing Percentage:"
        ws[f'B{row}'] = r.get('FINANCING_PERCENTAGE', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        row += 1
        ws[f'A{row}'] = "Financing Amount:"
        ws[f'B{row}'] = r.get('FINANCING_AMOUNT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 2
        ws[f'A{row}'] = "Interest Rate (bps + spread):"
        ws[f'B{row}'] = r.get('INTEREST_RATE_DECIMAL', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        row += 1
        ws[f'A{row}'] = "Construction Interest:"
        ws[f'B{row}'] = r.get('CONSTRUCTION_INTEREST', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 2
        ws[f'A{row}'] = "TOTAL DEBT"
        ws[f'A{row}'].font = Font(bold=True, size=12)
        ws[f'B{row}'] = r.get('TOTAL_DEBT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True, size=12)
        
        row += 1
        ws[f'A{row}'] = "TOTAL EQUITY"
        ws[f'A{row}'].font = Font(bold=True, size=12)
        ws[f'B{row}'] = r.get('TOTAL_EQUITY', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True, size=12)
        
        row += 2
        ws[f'A{row}'] = "Debt Percentage:"
        ws[f'B{row}'] = r.get('DEBT_PERCENTAGE', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        row += 1
        ws[f'A{row}'] = "Equity Percentage:"
        ws[f'B{row}'] = r.get('EQUITY_PERCENTAGE', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 20
    
    def create_operations_sheet(self, wb):
        """Create operations and revenue sheet"""
        ws = wb.create_sheet("Operations")
        r = self.formula_results
        
        ws['A1'] = "OPERATIONS & REVENUE"
        ws['A1'].font = Font(bold=True, size=14)
        
        # Revenue Section
        row = 3
        ws[f'A{row}'] = "REVENUE"
        ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        ws[f'A{row}'].font = Font(color="FFFFFF", bold=True)
        ws.merge_cells(f'A{row}:B{row}')
        
        row += 1
        revenue_items = [
            ("Gross Potential Free Market Rent", r.get('GROSS_POTENTIAL_FREE_MARKET_RENT', 0)),
            ("Gross Potential Affordable Rent", r.get('GROSS_POTENTIAL_AFFORDABLE_RENT', 0)),
            ("Other Income", r.get('OTHER_INCOME', 0)),
            ("Less: Vacancy Loss", -r.get('VACANCY_LOSS', 0)),
            ("Effective Gross Income", r.get('EFFECTIVE_GROSS_INCOME', 0)),
            ("Parking Income", r.get('PARKING_INCOME', 0)),
            ("Retail Revenue", r.get('RETAIL_REVENUE', 0)),
        ]
        
        for label, value in revenue_items:
            ws[f'A{row}'] = label
            if "Effective Gross" in label:
                ws[f'A{row}'].font = Font(bold=True)
            ws[f'B{row}'] = value
            ws[f'B{row}'].number_format = '$#,##0'
            row += 1
        
        # Expense Section
        row += 1
        ws[f'A{row}'] = "OPERATING EXPENSES"
        ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        ws[f'A{row}'].font = Font(color="FFFFFF", bold=True)
        ws.merge_cells(f'A{row}:C{row}')
        
        row += 1
        ws[f'A{row}'] = "Expense Category"
        ws[f'B{row}'] = "Annual Amount"
        ws[f'C{row}'] = "% of EGI"
        for col in ['A', 'B', 'C']:
            ws[f'{col}{row}'].font = Font(bold=True)
        
        row += 1
        # Safe division helper
        egi = r.get('EFFECTIVE_GROSS_INCOME', 0)
        def safe_pct(value):
            return value / egi if egi > 0 else 0

        expense_items = [
            ("Payroll", r.get('PAYROLL', 0), r.get('PAYROLL_PERCENTAGE_OF_EGI', 0)),
            ("Repairs & Maintenance", r.get('REPAIRS_AND_MAINTENANCE', 0), r.get('REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI', 0)),
            ("Utilities", r.get('UTILITIES', 0), r.get('UTILITIES_PERCENTAGE_OF_EGI', 0)),
            ("Insurance", r.get('INSURANCE', 0), r.get('INSURANCE_PERCENTAGE_OF_EGI', 0)),
            ("Management Fee", r.get('MANAGEMENT_FEE', 0), safe_pct(r.get('MANAGEMENT_FEE', 0))),
            ("Real Estate Taxes", r.get('REAL_ESTATE_TAXES', 0), safe_pct(r.get('REAL_ESTATE_TAXES', 0))),
        ]
        
        for label, amount, pct in expense_items:
            ws[f'A{row}'] = label
            ws[f'B{row}'] = amount
            ws[f'B{row}'].number_format = '$#,##0'
            ws[f'C{row}'] = pct
            ws[f'C{row}'].number_format = '0.00%'
            row += 1
        
        ws[f'A{row}'] = "TOTAL EXPENSES"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('TOTAL_EXPENSES', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        total_exp_pct = safe_pct(r.get('TOTAL_EXPENSES', 0))
        ws[f'C{row}'] = total_exp_pct
        ws[f'C{row}'].number_format = '0.00%'
        ws[f'C{row}'].font = Font(bold=True)
        
        row += 2
        ws[f'A{row}'] = "NET OPERATING INCOME"
        ws[f'A{row}'].font = Font(bold=True, size=12)
        ws[f'B{row}'] = r.get('NET_OPERATING_INCOME', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True, size=12)
        
        row += 2
        ws[f'A{row}'] = "NOI per Unit:"
        ws[f'B{row}'] = r.get('NOI_PER_UNIT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "NOI per GSF:"
        ws[f'B{row}'] = r.get('NOI_PER_GSF', 0)
        ws[f'B{row}'].number_format = '$#,##0.00'
        
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 20
        ws.column_dimensions['C'].width = 15
    
    def create_returns_sheet(self, wb):
        """Create investment returns and waterfall sheet"""
        ws = wb.create_sheet("Returns")
        r = self.formula_results
        
        ws['A1'] = "INVESTMENT RETURNS & EXIT ANALYSIS"
        ws['A1'].font = Font(bold=True, size=14)
        
        # Current Returns
        row = 3
        ws[f'A{row}'] = "STABILIZED RETURNS"
        ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        ws[f'A{row}'].font = Font(color="FFFFFF", bold=True)
        ws.merge_cells(f'A{row}:B{row}')
        
        row += 1
        returns_data = [
            ("Net Operating Income", f"${r.get('NET_OPERATING_INCOME', 0):,.0f}"),
            ("Stabilized Yield on Cost", f"${r.get('STABILIZED_YIELD_ON_COST', 0):,.0f}"),
            ("Yield on Cost %", f"{r.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}"),
            ("Stabilized Yield on Cost %", f"{r.get('STABILIZED_YIELD_ON_COST_PERCENTAGE', 0):.2%}"),
            ("Annual Debt Service", f"${r.get('ANNUAL_DEBT_SERVICE', 0):,.0f}"),
            ("Cash-on-Cash Return", f"{r.get('CASH_ON_CASH_RETURN', 0):.2f}%"),
            ("Debt Service Coverage Ratio", f"{r.get('DEBT_SERVICE_COVERAGE_RATIO', 0):.2f}x"),
        ]
        
        for label, value in returns_data:
            ws[f'A{row}'] = label
            ws[f'A{row}'].font = Font(bold=True)
            ws[f'B{row}'] = value
            row += 1
        
        # Exit Analysis
        row += 2
        ws[f'A{row}'] = "EXIT ANALYSIS"
        ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        ws[f'A{row}'].font = Font(color="FFFFFF", bold=True)
        ws.merge_cells(f'A{row}:B{row}')
        
        row += 1
        ws[f'A{row}'] = "Hold Period (months):"
        ws[f'B{row}'] = r.get('HOLD_PERIOD_MONTHS', 0)
        
        row += 1
        ws[f'A{row}'] = "Exit Cap Rate:"
        ws[f'B{row}'] = r.get('EXIT_CAP_RATE_DECIMAL', 0)
        ws[f'B{row}'].number_format = '0.00%'
        
        row += 1
        ws[f'A{row}'] = "Property Value on Sale:"
        ws[f'B{row}'] = r.get('PROPERTY_VALUE_ON_SALE', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Less: Sale Costs (2%):"
        ws[f'B{row}'] = -r.get('SALE_COST', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Net Sale Proceeds:"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('NET_SALE_PROCEEDS', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        
        row += 1
        ws[f'A{row}'] = "Less: Loan Payoff:"
        ws[f'B{row}'] = -r.get('TOTAL_DEBT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Cash After Loan Payback:"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('CASH_REMAINING_AFTER_LOAN_PAYBACK', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True)
        
        # Equity Waterfall
        row += 2
        ws[f'A{row}'] = "EQUITY WATERFALL"
        ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        ws[f'A{row}'].font = Font(color="FFFFFF", bold=True)
        ws.merge_cells(f'A{row}:B{row}')
        
        row += 1
        ws[f'A{row}'] = "GP Investment (20%):"
        ws[f'B{row}'] = r.get('GP_INVESTMENT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "LP Investment (80%):"
        ws[f'B{row}'] = r.get('LP_INVESTMENT', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 2
        ws[f'A{row}'] = "GP Preferred Return + Principal:"
        ws[f'B{row}'] = r.get('GP_PREFERRED_RETURN_WITH_PRINCIPAL', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "LP Preferred Return + Principal:"
        ws[f'B{row}'] = r.get('LP_PREFERRED_RETURN_WITH_PRINCIPAL', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Cash After Preferred:"
        ws[f'B{row}'] = r.get('CASH_REMAINING_AFTER_PREFERRED', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 2
        ws[f'A{row}'] = "GP Promote (20%):"
        ws[f'B{row}'] = r.get('PROMOTE_ON_JOINT_VENTURE', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 1
        ws[f'A{row}'] = "Cash to LP:"
        ws[f'B{row}'] = r.get('CASH_TO_LP', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        
        row += 2
        ws[f'A{row}'] = "NET TO LP INVESTOR"
        ws[f'A{row}'].font = Font(bold=True, size=12)
        ws[f'B{row}'] = r.get('NET_TO_LP_INVESTOR', 0)
        ws[f'B{row}'].number_format = '$#,##0'
        ws[f'B{row}'].font = Font(bold=True, size=12)
        
        row += 2
        ws[f'A{row}'] = "LP Multiple:"
        ws[f'A{row}'].font = Font(bold=True)
        ws[f'B{row}'] = r.get('LP_MULTIPLE', 0)
        ws[f'B{row}'].number_format = '0.00x'
        ws[f'B{row}'].font = Font(bold=True)
        
        row += 1
        ws[f'A{row}'] = "LP IRR:"
        ws[f'A{row}'].font = Font(bold=True)

        irr_value = r.get('IRR_TO_LP', 0)
        # Handle complex numbers or invalid values
        if isinstance(irr_value, complex):
            irr_value = 0  # or use irr_value.real if you want the real component
        ws[f'B{row}'] = irr_value / 100

        ws[f'B{row}'].number_format = '0.00%'
        ws[f'B{row}'].font = Font(bold=True)
        
        ws.column_dimensions['A'].width = 35
        ws.column_dimensions['B'].width = 20
    
    def run_full_pipeline(self, pdf_directory: str, output_excel: str = "Real_Estate_Financial_Model.xlsx"):
        """Execute complete pipeline"""
        print("=" * 60)
        print("REAL ESTATE FINANCIAL MODEL PIPELINE")
        print("=" * 60)
        
        # Step 1: Extract PDFs
        print("\n[Step 1/4] Extracting text from PDFs...")
        self.extract_all_pdfs(pdf_directory)
        print(f"✓ Extracted {len(self.extracted_data)} PDF files")
        
        # Step 2: Process with Gemini
        print("\n[Step 2/4] Extracting structured data with Gemini API...")
        structured_data = self.extract_structured_data()
        
        # NEW: Post-process to fill gaps
        print("\n[Step 2.5/4] Post-processing and filling estimates...")
        structured_data = self.post_process_extracted_data(structured_data)

        # Step 3: Calculate formulas
        print("\n[Step 3/4] Calculating all formulas...")
        self.calculate_all_formulas(structured_data)
        print(f"✓ Calculated {len(self.formula_results)} formula values")
        
        # Step 4: Generate Excel
        print("\n[Step 4/4] Generating Excel file...")
        self.generate_excel(output_excel)
        
        print("\n" + "=" * 60)
        print("PIPELINE COMPLETE!")
        print("=" * 60)
        print(f"\nKey Metrics:")
        print(f"  Total Project Cost: ${self.formula_results.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0):,.0f}")
        print(f"  Total Debt: ${self.formula_results.get('TOTAL_DEBT', 0):,.0f}")
        print(f"  Total Equity: ${self.formula_results.get('TOTAL_EQUITY', 0):,.0f}")
        print(f"  NOI: ${self.formula_results.get('NET_OPERATING_INCOME', 0):,.0f}")
        print(f"  Yield on Cost: {self.formula_results.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}")
        irr_val = self.formula_results.get('IRR_TO_LP', 0)
        if isinstance(irr_val, complex):
            irr_val = irr_val.real
        print(f"  LP IRR: {irr_val:.2f}%")

        print(f"\nExcel file: {output_excel}")
        
        return output_excel


if __name__ == "__main__":
    
    # Hardcoded API Key
    GEMINI_API_KEY = "AIzaSyCy6GoBR724Hj9VyuW3hKM4N0P6liBOlDo"
    
    def process_pdfs(pdf_files):
        """Process uploaded PDFs and return Excel file"""
        if not pdf_files:
            return None, "Please upload at least one PDF file"
        
        try:
            # Create temporary directory for PDFs
            temp_dir = tempfile.mkdtemp()
            
            # Save uploaded PDFs to temp directory
            for pdf_file in pdf_files:
                shutil.copy(pdf_file.name, temp_dir)
            
            # Initialize pipeline with hardcoded API key
            pipeline = RealEstateModelPipeline(GEMINI_API_KEY)
            
            # Create output file in temp directory
            output_file = Path(temp_dir) / "Real_Estate_Financial_Model.xlsx"
            
            # Run pipeline
            result = pipeline.run_full_pipeline(temp_dir, str(output_file))
            
            # Generate summary text
            summary = f"""
            ✅ Processing Complete!
            
            Key Metrics:
            • Total Project Cost: ${pipeline.formula_results.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0):,.0f}
            • Total Debt: ${pipeline.formula_results.get('TOTAL_DEBT', 0):,.0f}
            • Total Equity: ${pipeline.formula_results.get('TOTAL_EQUITY', 0):,.0f}
            • NOI: ${pipeline.formula_results.get('NET_OPERATING_INCOME', 0):,.0f}
            • Yield on Cost: {pipeline.formula_results.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}
            • LP IRR: {float(pipeline.formula_results.get('IRR_TO_LP', 0).real if isinstance(pipeline.formula_results.get('IRR_TO_LP', 0), complex) else pipeline.formula_results.get('IRR_TO_LP', 0)):.2f}%
            
            Download your Excel file below ⬇️
            """
            
            return str(output_file), summary
            
        except Exception as e:
            return None, f"❌ Error: {str(e)}"
    
    # Create Gradio interface
    with gr.Blocks(title="Real Estate Financial Model Generator", theme=gr.themes.Soft()) as demo:
        
        gr.Markdown("""
        # Real Estate Financial Model Generator
        Upload your PDF documents and generate a comprehensive financial model in Excel format.
        """)
        
        with gr.Row():
            with gr.Column(scale=2):
                pdf_input = gr.File(
                    label="Upload PDF/XLSX Files",
                    file_count="multiple",
                    file_types=[".pdf", ".xlsx", ".xls"],  # Added .xlsx and .xls
                    type="filepath"
                )
                
                process_btn = gr.Button("Generate Financial Model", variant="primary", size="lg")
            
            with gr.Column(scale=1):
                gr.Markdown("""
                ### 📋 Supported Formats
                - **PDF**: Offering Memorandum, Reports
                - **XLSX/XLS**: Financial statements, data tables
                
                ### 📋 Required Documents
                - Offering Memorandum (PDF/XLSX)
                - Operating Expenses Summary (PDF/XLSX)
                - Sales Comps (PDF/XLSX)
                - Rent Comps (PDF/XLSX)
                - Market Report (PDF/XLSX)
                - Demographics Overview (PDF/XLSX)
                
                ### ⚡ Features
                - Automated data extraction
                - Formula calculations
                - Professional Excel output
                - Multiple analysis sheets
                """)
        
        with gr.Row():
            output_text = gr.Textbox(
                label="Processing Results",
                lines=12,
                interactive=False
            )
        
        with gr.Row():
            excel_output = gr.File(
                label="📊 Download Excel File"
            )
        
        process_btn.click(
            fn=process_pdfs,
            inputs=[pdf_input],
            outputs=[excel_output, output_text]
        )
        
        gr.Markdown("""
        ---
        ### 💡 Tips
        - Ensure PDF files are readable and not scanned images
        - Use descriptive filenames (e.g., "Offering_Memorandum.pdf")
        - Processing may take 30-60 seconds depending on file sizes
        """)
    
    # Launch the app
    demo.launch(share=False)