analyze_nsw_property_data.py

#!/usr/bin/env python3
"""
NSW Property Sales Data Analysis
Analyzes Valuer General NSW sales data to identify investment hotspots and trends
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import glob
import re
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

# Set up plotting style
plt.style.use('default')
sns.set_palette("husl")

class NSWPropertyAnalyzer:
    def __init__(self, data_path="/home/claude"):
        self.data_path = data_path
        self.raw_data = []
        self.df = None
        self.summary_stats = {}
        
        # LGA code to name mapping (major ones)
        self.lga_mapping = {
            '001': 'Cessnock',
            '002': 'Dungog', 
            '004': 'Gloucester',
            '005': 'Great Lakes',
            '007': 'Lake Macquarie',
            '008': 'Maitland',
            '010': 'Muswellbrook',
            '012': 'Newcastle',
            '018': 'Port Stephens',
            '042': 'Singleton',
            '043': 'Upper Hunter',
            '050': 'Wyong',
            '051': 'Gosford',
            '052': 'Pittwater',
            '054': 'Warringah',
            '061': 'Manly',
            '065': 'Mosman',
            '066': 'North Sydney',
            '070': 'Lane Cove',
            '074': 'Ryde',
            '081': 'Hunters Hill',
            '082': 'Hornsby',
            '083': 'Ku-ring-gai',
            '084': 'Baulkham Hills',
            '085': 'Blacktown',
            '087': 'Penrith',
            '088': 'Blue Mountains',
            '090': 'Hawkesbury',
            '092': 'Parramatta',
            '097': 'Holroyd',
            '098': 'Auburn',
            '100': 'Sydney',
            '101': 'South Sydney',
            '102': 'Marrickville',
            '103': 'Leichhardt',
            '109': 'Ashfield',
            '116': 'Burwood',
            '117': 'Strathfield',
            '118': 'Canada Bay',
            '123': 'Fairfield',
            '124': 'Liverpool',
            '137': 'Campbelltown',
            '139': 'Camden',
            '143': 'Bankstown',
            '144': 'Canterbury',
            '148': 'Hurstville',
            '149': 'Kogarah',
            '150': 'Rockdale',
            '151': 'Botany Bay',
            '152': 'Randwick',
            '157': 'Waverley',
            '158': 'Woollahra',
            '159': 'Sutherland Shire',
            '164': 'Wollongong',
            '171': 'Shellharbour',
            '187': 'Kiama',
            '188': 'Shoalhaven',
            '192': 'Wingecarribee',
            '199': 'Wollondilly',
            '207': 'Eurobodalla',
            '209': 'Bega Valley',
            '210': 'Snowy Monaro',
            '214': 'Queanbeyan-Palerang',
            '216': 'Yass Valley',
            '217': 'Hilltops',
            '218': 'Upper Lachlan',
            '219': 'Goulburn Mulwaree',
            '220': 'Blayney',
            '222': 'Oberon',
            '223': 'Bathurst',
            '224': 'Lithgow',
            '226': 'Mid-Western',
            '231': 'Orange',
            '232': 'Cabonne',
            '233': 'Cowra',
            '234': 'Weddin',
            '235': 'Parkes',
            '236': 'Forbes',
            '238': 'Lachlan',
            '239': 'Bland',
            '240': 'Temora',
            '243': 'Young',
            '244': 'Boorowa',
            '247': 'Harden',
            '250': 'Cootamundra-Gundagai',
            '251': 'Junee',
            '253': 'Coolamon',
            '254': 'Wagga Wagga',
            '255': 'Lockhart',
            '257': 'Urana',
            '258': 'Corowa',
            '259': 'Greater Hume',
            '260': 'Albury',
            '261': 'Tumut',
            '262': 'Snowy Valleys',
            '263': 'Tumbarumba',
            '264': 'Murray River',
            '265': 'Edward River',
            '266': 'Griffith',
            '267': 'Leeton',
            '268': 'Murrumbidgee',
            '269': 'Narrandera',
            '270': 'Coolamon',
            '271': 'Temora',
            '272': 'Bland',
            '273': 'Carrathool',
            '274': 'Hay',
            '275': 'Balranald',
            '276': 'Wentworth',
            '300': 'Central Coast',
            '301': 'Lake Macquarie',
            '302': 'Newcastle',
            '303': 'Maitland',
            '511': 'Lord Howe Island',
            '526': 'Broken Hill',
            '528': 'Central Darling',
            '529': 'Unincorporated Far West',
            '537': 'Walgett',
            '538': 'Brewarrina',
            '560': 'Bourke',
            '575': 'Cobar',
            '608': 'Coonamble',
            '620': 'Gilgandra',
            '656': 'Narromine',
            '666': 'Dubbo',
            '708': 'Wellington'
        }
    
    def parse_dat_files(self):
        """Parse all DAT files and extract property sales data"""
        print("Parsing DAT files...")
        
        dat_files = glob.glob(f"{self.data_path}/*_SALES_DATA_*.DAT")
        print(f"Found {len(dat_files)} DAT files")
        
        all_sales = []
        
        for file_path in dat_files:
            lga_code = file_path.split('/')[-1].split('_')[0]
            lga_name = self.lga_mapping.get(lga_code, f"LGA_{lga_code}")
            
            with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
                lines = f.readlines()
                
            # Process B records (property sales)
            for line in lines:
                if line.startswith('B;'):
                    try:
                        fields = line.strip().split(';')
                        if len(fields) >= 20:  # Ensure we have enough fields
                            sale_record = {
                                'lga_code': fields[1],
                                'lga_name': lga_name,
                                'property_id': fields[2],
                                'sequence': fields[3],
                                'extract_date': fields[4],
                                'unit_number': fields[5] if fields[5] else None,
                                'unit_suffix': fields[6] if fields[6] else None,
                                'street_number': fields[7],
                                'street_name': fields[8],
                                'suburb': fields[9],
                                'postcode': fields[10],
                                'land_area': fields[11] if fields[11] else None,
                                'land_area_unit': fields[12] if fields[12] else None,
                                'contract_date': fields[13] if fields[13] else None,
                                'settlement_date': fields[14] if fields[14] else None,
                                'sale_price': fields[15] if fields[15] else None,
                                'zone_code': fields[16] if fields[16] else None,
                                'property_type': fields[17] if fields[17] else None,
                                'property_description': fields[18] if fields[18] else None,
                                'sale_nature': fields[19] if len(fields) > 19 and fields[19] else None
                            }
                            all_sales.append(sale_record)
                    except Exception as e:
                        print(f"Error parsing line in {file_path}: {e}")
                        continue
        
        print(f"Parsed {len(all_sales)} property sale records")
        
        # Convert to DataFrame
        self.df = pd.DataFrame(all_sales)
        return self.df
    
    def clean_and_process_data(self):
        """Clean and process the raw data"""
        print("Cleaning and processing data...")
        
        if self.df is None or self.df.empty:
            print("No data to process")
            return
        
        # Convert dates
        for date_col in ['contract_date', 'settlement_date']:
            if date_col in self.df.columns:
                self.df[date_col] = pd.to_datetime(self.df[date_col], format='%Y%m%d', errors='coerce')
        
        # Convert sale price to numeric
        self.df['sale_price'] = pd.to_numeric(self.df['sale_price'], errors='coerce')
        
        # Convert land area to numeric
        self.df['land_area'] = pd.to_numeric(self.df['land_area'], errors='coerce')
        
        # Filter out invalid sales (no price or unreasonable prices)
        initial_count = len(self.df)
        self.df = self.df[
            (self.df['sale_price'].notna()) & 
            (self.df['sale_price'] > 10000) & 
            (self.df['sale_price'] < 50000000)  # Remove outliers
        ]
        print(f"Filtered from {initial_count} to {len(self.df)} valid sales")
        
        # Create full address
        self.df['full_address'] = self.df.apply(
            lambda row: f"{row['street_number']} {row['street_name']}, {row['suburb']} {row['postcode']}"
            if pd.notna(row['street_number']) and pd.notna(row['street_name']) else None, 
            axis=1
        )
        
        # Add month/year columns for time series analysis
        self.df['contract_year'] = self.df['contract_date'].dt.year
        self.df['contract_month'] = self.df['contract_date'].dt.month
        self.df['contract_year_month'] = self.df['contract_date'].dt.to_period('M')
        
        print("Data cleaning completed")
        
    def analyze_investment_hotspots(self):
        """Analyze data to identify investment hotspots"""
        print("Analyzing investment hotspots...")
        
        # Suburb-level analysis
        suburb_stats = self.df.groupby(['suburb', 'lga_name']).agg({
            'sale_price': ['count', 'mean', 'median', 'std'],
            'land_area': 'mean',
            'contract_date': ['min', 'max']
        }).round(2)
        
        suburb_stats.columns = ['_'.join(col).strip() for col in suburb_stats.columns]
        suburb_stats = suburb_stats.reset_index()
        
        # Calculate price per sqm where land area is available
        suburb_stats['avg_price_per_sqm'] = (
            suburb_stats['sale_price_mean'] / suburb_stats['land_area_mean']
        ).round(2)
        
        # Filter suburbs with at least 5 sales for statistical significance
        suburb_stats = suburb_stats[suburb_stats['sale_price_count'] >= 5]
        
        # LGA-level analysis
        lga_stats = self.df.groupby('lga_name').agg({
            'sale_price': ['count', 'mean', 'median', 'std'],
            'land_area': 'mean',
            'contract_date': ['min', 'max']
        }).round(2)
        
        lga_stats.columns = ['_'.join(col).strip() for col in lga_stats.columns]
        lga_stats = lga_stats.reset_index()
        
        # Time series analysis for growth trends
        monthly_trends = self.df.groupby(['suburb', 'contract_year_month']).agg({
            'sale_price': ['count', 'mean']
        }).reset_index()
        
        monthly_trends.columns = ['suburb', 'year_month', 'sale_count', 'avg_price']
        
        # Calculate growth rates where we have sufficient data
        growth_analysis = []
        for suburb in self.df['suburb'].unique():
            suburb_data = monthly_trends[monthly_trends['suburb'] == suburb]
            if len(suburb_data) >= 6:  # At least 6 months of data
                suburb_data = suburb_data.sort_values('year_month')
                first_half = suburb_data.iloc[:len(suburb_data)//2]['avg_price'].mean()
                second_half = suburb_data.iloc[len(suburb_data)//2:]['avg_price'].mean()
                
                if first_half > 0:
                    growth_rate = ((second_half - first_half) / first_half) * 100
                    growth_analysis.append({
                        'suburb': suburb,
                        'growth_rate': round(growth_rate, 2),
                        'data_points': len(suburb_data),
                        'avg_price_early': round(first_half, 2),
                        'avg_price_recent': round(second_half, 2)
                    })
        
        growth_df = pd.DataFrame(growth_analysis)
        
        self.summary_stats = {
            'suburb_stats': suburb_stats,
            'lga_stats': lga_stats,
            'growth_analysis': growth_df,
            'monthly_trends': monthly_trends
        }
        
        print("Investment hotspot analysis completed")
        
    def create_visualizations(self):
        """Create comprehensive visualizations"""
        print("Creating visualizations...")
        
        # Set up the plotting environment
        plt.style.use('default')
        
        # 1. Top 20 Suburbs by Average Sale Price
        top_suburbs_price = self.summary_stats['suburb_stats'].nlargest(20, 'sale_price_mean')
        
        fig, ax = plt.subplots(figsize=(12, 8))
        bars = ax.barh(range(len(top_suburbs_price)), top_suburbs_price['sale_price_mean'])
        ax.set_yticks(range(len(top_suburbs_price)))
        ax.set_yticklabels([f"{row['suburb']} ({row['lga_name']})" for _, row in top_suburbs_price.iterrows()])
        ax.set_xlabel('Average Sale Price ($)')
        ax.set_title('Top 20 Suburbs by Average Sale Price', fontsize=14, fontweight='bold')
        
        # Add value labels on bars
        for i, (_, row) in enumerate(top_suburbs_price.iterrows()):
            ax.text(row['sale_price_mean'] + 10000, i, f"${row['sale_price_mean']:,.0f}", 
                   va='center', fontsize=9)
        
        plt.tight_layout()
        plt.savefig('/home/claude/top_suburbs_by_price.png', dpi=300, bbox_inches='tight')
        plt.close()
        
        # 2. Sales Volume by LGA
        top_lgas_volume = self.summary_stats['lga_stats'].nlargest(15, 'sale_price_count')
        
        fig, ax = plt.subplots(figsize=(12, 8))
        bars = ax.bar(range(len(top_lgas_volume)), top_lgas_volume['sale_price_count'])
        ax.set_xticks(range(len(top_lgas_volume)))
        ax.set_xticklabels(top_lgas_volume['lga_name'], rotation=45, ha='right')
        ax.set_ylabel('Number of Sales')
        ax.set_title('Sales Volume by Local Government Area', fontsize=14, fontweight='bold')
        
        # Add value labels on bars
        for i, (_, row) in enumerate(top_lgas_volume.iterrows()):
            ax.text(i, row['sale_price_count'] + 5, f"{row['sale_price_count']}", 
                   ha='center', va='bottom', fontsize=9)
        
        plt.tight_layout()
        plt.savefig('/home/claude/sales_volume_by_lga.png', dpi=300, bbox_inches='tight')
        plt.close()
        
        # 3. Price Growth Analysis
        if not self.summary_stats['growth_analysis'].empty:
            top_growth = self.summary_stats['growth_analysis'].nlargest(15, 'growth_rate')
            
            fig, ax = plt.subplots(figsize=(12, 8))
            colors = ['green' if x > 0 else 'red' for x in top_growth['growth_rate']]
            bars = ax.barh(range(len(top_growth)), top_growth['growth_rate'], color=colors)
            ax.set_yticks(range(len(top_growth)))
            ax.set_yticklabels(top_growth['suburb'])
            ax.set_xlabel('Price Growth Rate (%)')
            ax.set_title('Top 15 Suburbs by Price Growth Rate', fontsize=14, fontweight='bold')
            ax.axvline(x=0, color='black', linestyle='-', alpha=0.3)
            
            # Add value labels
            for i, (_, row) in enumerate(top_growth.iterrows()):
                ax.text(row['growth_rate'] + (1 if row['growth_rate'] > 0 else -1), i, 
                       f"{row['growth_rate']:.1f}%", va='center', fontsize=9)
            
            plt.tight_layout()
            plt.savefig('/home/claude/price_growth_analysis.png', dpi=300, bbox_inches='tight')
            plt.close()
        
        # 4. Price Distribution Analysis
        fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, figsize=(15, 12))
        
        # Price distribution histogram
        ax1.hist(self.df['sale_price'], bins=50, alpha=0.7, edgecolor='black')
        ax1.set_xlabel('Sale Price ($)')
        ax1.set_ylabel('Frequency')
        ax1.set_title('Distribution of Sale Prices')
        ax1.ticklabel_format(style='plain', axis='x')
        
        # Price by property type
        if 'property_type' in self.df.columns:
            property_type_data = self.df.groupby('property_type')['sale_price'].mean().sort_values(ascending=False)
            ax2.bar(range(len(property_type_data)), property_type_data.values)
            ax2.set_xticks(range(len(property_type_data)))
            ax2.set_xticklabels(property_type_data.index, rotation=45)
            ax2.set_ylabel('Average Sale Price ($)')
            ax2.set_title('Average Price by Property Type')
        
        # Monthly sales trend
        monthly_sales = self.df.groupby('contract_year_month')['sale_price'].count()
        ax3.plot(monthly_sales.index.astype(str), monthly_sales.values, marker='o')
        ax3.set_xlabel('Month')
        ax3.set_ylabel('Number of Sales')
        ax3.set_title('Monthly Sales Volume Trend')
        ax3.tick_params(axis='x', rotation=45)
        
        # Price vs Land Area scatter
        valid_land_data = self.df[(self.df['land_area'].notna()) & (self.df['land_area'] > 0)]
        if not valid_land_data.empty:
            ax4.scatter(valid_land_data['land_area'], valid_land_data['sale_price'], alpha=0.5)
            ax4.set_xlabel('Land Area (sqm)')
            ax4.set_ylabel('Sale Price ($)')
            ax4.set_title('Sale Price vs Land Area')
        
        plt.tight_layout()
        plt.savefig('/home/claude/comprehensive_analysis.png', dpi=300, bbox_inches='tight')
        plt.close()
        
        print("Visualizations created successfully")
        
    def generate_report_data(self):
        """Generate comprehensive report data"""
        print("Generating report data...")
        
        # Overall market summary
        total_sales = len(self.df)
        total_value = self.df['sale_price'].sum()
        avg_price = self.df['sale_price'].mean()
        median_price = self.df['sale_price'].median()
        
        date_range = f"{self.df['contract_date'].min().strftime('%B %Y')} to {self.df['contract_date'].max().strftime('%B %Y')}"
        
        # Top performing areas
        top_suburbs_by_price = self.summary_stats['suburb_stats'].nlargest(10, 'sale_price_mean')
        top_suburbs_by_volume = self.summary_stats['suburb_stats'].nlargest(10, 'sale_price_count')
        top_lgas = self.summary_stats['lga_stats'].nlargest(10, 'sale_price_mean')
        
        # Growth leaders
        growth_leaders = self.summary_stats['growth_analysis'].nlargest(10, 'growth_rate') if not self.summary_stats['growth_analysis'].empty else pd.DataFrame()
        
        # Investment insights
        insights = []
        
        # High-value, low-volume areas (potential luxury markets)
        luxury_markets = top_suburbs_by_price[top_suburbs_by_price['sale_price_count'] < 20]
        if not luxury_markets.empty:
            insights.append("**Luxury Market Opportunities**: " + 
                          ", ".join(luxury_markets['suburb'].head(5).tolist()) + 
                          " show high average prices with selective trading.")
        
        # High-volume areas (liquid markets)
        liquid_markets = top_suburbs_by_volume.head(5)
        insights.append("**Liquid Markets**: " + 
                       ", ".join(liquid_markets['suburb'].tolist()) + 
                       " demonstrate strong trading activity and market liquidity.")
        
        # Growth opportunities
        if not growth_leaders.empty:
            positive_growth = growth_leaders[growth_leaders['growth_rate'] > 0]
            if not positive_growth.empty:
                insights.append("**Growth Hotspots**: " + 
                              ", ".join(positive_growth['suburb'].head(5).tolist()) + 
                              " show strong price appreciation trends.")
        
        report_data = {
            'market_summary': {
                'total_sales': total_sales,
                'total_value': total_value,
                'avg_price': avg_price,
                'median_price': median_price,
                'date_range': date_range
            },
            'top_performers': {
                'suburbs_by_price': top_suburbs_by_price,
                'suburbs_by_volume': top_suburbs_by_volume,
                'lgas': top_lgas
            },
            'growth_analysis': growth_leaders,
            'investment_insights': insights
        }
        
        return report_data

def main():
    print("=== NSW Property Investment Hotspots Analysis ===")
    print("Starting comprehensive analysis of NSW Valuer General data...\n")
    
    # Initialize analyzer
    analyzer = NSWPropertyAnalyzer()
    
    # Parse data files
    analyzer.parse_dat_files()
    
    # Clean and process data
    analyzer.clean_and_process_data()
    
    # Analyze investment hotspots
    analyzer.analyze_investment_hotspots()
    
    # Create visualizations
    analyzer.create_visualizations()
    
    # Generate report data
    report_data = analyzer.generate_report_data()
    
    # Print summary
    print("\n=== ANALYSIS SUMMARY ===")
    print(f"Total Sales Analyzed: {report_data['market_summary']['total_sales']:,}")
    print(f"Total Market Value: ${report_data['market_summary']['total_value']:,.0f}")
    print(f"Average Sale Price: ${report_data['market_summary']['avg_price']:,.0f}")
    print(f"Median Sale Price: ${report_data['market_summary']['median_price']:,.0f}")
    print(f"Data Period: {report_data['market_summary']['date_range']}")
    
    print(f"\nTop 5 Suburbs by Average Price:")
    for i, (_, row) in enumerate(report_data['top_performers']['suburbs_by_price'].head().iterrows(), 1):
        print(f"{i}. {row['suburb']} ({row['lga_name']}): ${row['sale_price_mean']:,.0f}")
    
    print(f"\nTop 5 Growth Suburbs:")
    if not report_data['growth_analysis'].empty:
        for i, (_, row) in enumerate(report_data['growth_analysis'].head().iterrows(), 1):
            print(f"{i}. {row['suburb']}: {row['growth_rate']:+.1f}% growth")
    else:
        print("Insufficient data for growth analysis")
    
    print("\n=== Analysis completed successfully ===")
    
    return analyzer, report_data

if __name__ == "__main__":
    analyzer, report_data = main()

create_simple_report.js

const { 
    Document, 
    Packer, 
    Paragraph, 
    TextRun, 
    Table, 
    TableRow, 
    TableCell, 
    Header, 
    Footer, 
    AlignmentType, 
    LevelFormat, 
    TableOfContents,
    HeadingLevel, 
    BorderStyle, 
    WidthType, 
    ShadingType,
    PageNumber,
    PageBreak
} = require('docx');

const fs = require('fs');

function createSimpleNSWReport() {
    const doc = new Document({
        styles: {
            default: { 
                document: { 
                    run: { font: "Arial", size: 24 } 
                } 
            },
            paragraphStyles: [
                { 
                    id: "Title", 
                    name: "Title", 
                    basedOn: "Normal",
                    run: { size: 48, bold: true, color: "1f4e79", font: "Arial" },
                    paragraph: { spacing: { before: 240, after: 240 }, alignment: AlignmentType.CENTER } 
                },
                { 
                    id: "Heading1", 
                    name: "Heading 1", 
                    basedOn: "Normal", 
                    run: { size: 32, bold: true, color: "1f4e79", font: "Arial" },
                    paragraph: { spacing: { before: 240, after: 180 }, outlineLevel: 0 } 
                },
                { 
                    id: "Heading2", 
                    name: "Heading 2", 
                    basedOn: "Normal", 
                    run: { size: 28, bold: true, color: "2e75b6", font: "Arial" },
                    paragraph: { spacing: { before: 180, after: 120 }, outlineLevel: 1 } 
                }
            ]
        },
        numbering: {
            config: [
                {
                    reference: "bullet-list",
                    levels: [{
                        level: 0, 
                        format: LevelFormat.BULLET, 
                        text: "•", 
                        alignment: AlignmentType.LEFT,
                        style: { paragraph: { indent: { left: 720, hanging: 360 } } }
                    }]
                }
            ]
        },
        sections: [{
            properties: {
                page: {
                    margin: { top: 1440, right: 1440, bottom: 1440, left: 1440 }
                }
            },
            headers: {
                default: new Header({
                    children: [
                        new Paragraph({
                            alignment: AlignmentType.RIGHT,
                            children: [
                                new TextRun({
                                    text: "NSW Property Investment Analysis | September 2025",
                                    size: 20,
                                    color: "666666"
                                })
                            ]
                        })
                    ]
                })
            },
            footers: {
                default: new Footer({
                    children: [
                        new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [
                                new TextRun({ text: "Page ", size: 20, color: "666666" }),
                                new TextRun({ children: [PageNumber.CURRENT], size: 20, color: "666666" })
                            ]
                        })
                    ]
                })
            },
            children: [
                // Title
                new Paragraph({
                    heading: HeadingLevel.TITLE,
                    children: [new TextRun("NSW Property Investment Hotspots Analysis")]
                }),
                
                new Paragraph({
                    alignment: AlignmentType.CENTER,
                    spacing: { before: 240, after: 480 },
                    children: [
                        new TextRun({
                            text: "Comprehensive Market Analysis | September 2025",
                            size: 28,
                            color: "2e75b6"
                        })
                    ]
                }),
                
                // Executive Summary
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Executive Summary")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("This analysis of NSW Valuer General sales data reveals significant investment opportunities across New South Wales. Our comprehensive review of "),
                        new TextRun({ text: "3,951 property transactions", bold: true }),
                        new TextRun(" totaling "),
                        new TextRun({ text: "$5.22 billion", bold: true }),
                        new TextRun(" provides critical insights for property investors.")
                    ]
                }),
                
                // Key Statistics Table
                createKeyStatsTable(),
                
                // Market Highlights
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Market Highlights")]
                }),
                
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Average sale price: $1,321,832 indicating strong market presence")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Median sale price: $935,000 showing market accessibility")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Blacktown leads growth with exceptional 1,212% appreciation")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Strong premium markets in Pymble ($4.31M average)")]
                }),
                
                // Investment Hotspots
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Top Investment Hotspots")]
                }),
                
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Growth Leaders")]
                }),
                
                createGrowthTable(),
                
                new Paragraph({
                    children: [
                        new TextRun("These suburbs demonstrate exceptional growth potential, with Blacktown showing remarkable 1,212% appreciation. Rosebery and Rouse Hill offer strong metropolitan growth opportunities at 96% and 81% respectively.")
                    ]
                }),
                
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Premium Value Markets")]
                }),
                
                createPremiumTable(),
                
                new Paragraph({
                    children: [
                        new TextRun("Premium markets offer luxury investment opportunities with high capital requirements. These areas typically attract discerning buyers and provide portfolio diversification.")
                    ]
                }),
                
                // Investment Strategies
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Investment Strategy Recommendations")]
                }),
                
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Growth-Focused Strategy")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("For capital appreciation, target high-growth suburbs:")
                    ]
                }),
                
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Blacktown for exceptional growth potential")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Rosebery and Rouse Hill for metropolitan growth")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Diversify across multiple growth areas")]
                }),
                
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Premium Investment Strategy")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("High-value markets for portfolio prestige:")
                    ]
                }),
                
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Pymble and Haberfield for premium exposure")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Consider long-term appreciation potential")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Factor in higher holding costs")]
                }),
                
                // Market Analysis
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Market Analysis Summary")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("The NSW property market presents diverse opportunities across multiple segments. With over $5.2 billion in analyzed transactions, the data reveals both high-growth potential and stable premium markets.")
                    ]
                }),
                
                new Paragraph({
                    spacing: { before: 240 },
                    children: [
                        new TextRun("Key opportunities include exceptional growth in western Sydney suburbs, particularly Blacktown, and continued strength in established premium areas. The significant difference between average and median prices indicates market segmentation with opportunities across price points.")
                    ]
                }),
                
                // Risk Considerations
                new Paragraph({
                    heading: HeadingLevel.HEADING_2,
                    children: [new TextRun("Risk Considerations")]
                }),
                
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("High-growth areas may experience volatility")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Premium markets may have lower liquidity")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Interest rate sensitivity in higher-value segments")]
                }),
                new Paragraph({
                    numbering: { reference: "bullet-list", level: 0 },
                    children: [new TextRun("Regulatory and policy change impacts")]
                }),
                
                // Methodology
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Methodology")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("This analysis is based on NSW Valuer General official sales data covering October 2014 to September 2025. We analyzed 126 DAT files representing all NSW Local Government Areas, filtering for valid transactions between $10,000 and $50 million.")
                    ]
                }),
                
                new Paragraph({
                    spacing: { before: 240 },
                    children: [
                        new TextRun("Growth calculations compare early and recent period averages for trend identification. Statistical analysis includes mean, median, and distribution analysis for market insights.")
                    ]
                }),
                
                // Conclusion
                new Paragraph({
                    heading: HeadingLevel.HEADING_1,
                    children: [new TextRun("Conclusion")]
                }),
                
                new Paragraph({
                    children: [
                        new TextRun("The NSW property market offers compelling investment opportunities across growth and premium segments. Blacktown's exceptional performance demonstrates the potential for significant capital appreciation, while established premium areas provide stability and prestige.")
                    ]
                }),
                
                new Paragraph({
                    spacing: { before: 240 },
                    children: [
                        new TextRun("Investors should consider diversification across market segments, matching strategy to risk tolerance and capital availability. Professional consultation and ongoing market monitoring are recommended for optimal investment outcomes.")
                    ]
                }),
                
                // Disclaimer
                new Paragraph({
                    spacing: { before: 480 },
                    children: [
                        new TextRun({
                            text: "Disclaimer: This analysis is for informational purposes only and does not constitute financial advice. Property investment involves risk, and past performance does not guarantee future results. Seek professional advice before making investment decisions.",
                            size: 20,
                            italics: true,
                            color: "666666"
                        })
                    ]
                })
            ]
        }]
    });

    return doc;
}

function createKeyStatsTable() {
    const tableBorder = { style: BorderStyle.SINGLE, size: 1, color: "CCCCCC" };
    const cellBorders = { top: tableBorder, bottom: tableBorder, left: tableBorder, right: tableBorder };
    
    return new Table({
        columnWidths: [4680, 4680],
        margins: { top: 100, bottom: 100, left: 180, right: 180 },
        rows: [
            new TableRow({
                tableHeader: true,
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "1f4e79", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Market Metric", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "1f4e79", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Value", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Total Transactions")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun({ text: "3,951", bold: true })] })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Total Market Value")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun({ text: "$5.22 Billion", bold: true })] })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Average Sale Price")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun({ text: "$1,321,832", bold: true })] })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Median Sale Price")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun({ text: "$935,000", bold: true })] })]
                    })
                ]
            })
        ]
    });
}

function createGrowthTable() {
    const tableBorder = { style: BorderStyle.SINGLE, size: 1, color: "CCCCCC" };
    const cellBorders = { top: tableBorder, bottom: tableBorder, left: tableBorder, right: tableBorder };
    
    return new Table({
        columnWidths: [4680, 4680],
        margins: { top: 100, bottom: 100, left: 180, right: 180 },
        rows: [
            new TableRow({
                tableHeader: true,
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "2e75b6", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Suburb", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "2e75b6", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Growth Rate", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Blacktown")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "+1,212%", bold: true, color: "007f00" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Rosebery")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "+96%", bold: true, color: "007f00" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Rouse Hill")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "+81%", bold: true, color: "007f00" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Gulgong")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "+71%", bold: true, color: "007f00" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Riverstone")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "+68%", bold: true, color: "007f00" })]
                        })]
                    })
                ]
            })
        ]
    });
}

function createPremiumTable() {
    const tableBorder = { style: BorderStyle.SINGLE, size: 1, color: "CCCCCC" };
    const cellBorders = { top: tableBorder, bottom: tableBorder, left: tableBorder, right: tableBorder };
    
    return new Table({
        columnWidths: [4680, 4680],
        margins: { top: 100, bottom: 100, left: 180, right: 180 },
        rows: [
            new TableRow({
                tableHeader: true,
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "5b9bd5", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Suburb", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        shading: { fill: "5b9bd5", type: ShadingType.CLEAR },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "Average Price", bold: true, size: 22, color: "FFFFFF" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Pymble")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "$4.31M", bold: true, color: "1f4e79" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Punchbowl")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "$4.27M", bold: true, color: "1f4e79" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Haberfield")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "$3.79M", bold: true, color: "1f4e79" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Blakehurst")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "$3.68M", bold: true, color: "1f4e79" })]
                        })]
                    })
                ]
            }),
            new TableRow({
                children: [
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({ children: [new TextRun("Balmain")] })]
                    }),
                    new TableCell({
                        borders: cellBorders,
                        width: { size: 4680, type: WidthType.DXA },
                        children: [new Paragraph({
                            alignment: AlignmentType.CENTER,
                            children: [new TextRun({ text: "$3.62M", bold: true, color: "1f4e79" })]
                        })]
                    })
                ]
            })
        ]
    });
}

// Generate the document
const doc = createSimpleNSWReport();

Packer.toBuffer(doc).then((buffer) => {
    fs.writeFileSync("/mnt/user-data/outputs/NSW_Property_Report_Fixed.docx", buffer);
    console.log("Fixed NSW Property Report generated successfully!");
}).catch((error) => {
    console.error("Error generating document:", error);
});