comprehensive_scraper.py

import requests
import json
import os
import re
import csv
from bs4 import BeautifulSoup
from time import sleep

# Output directories and files
DATA_DIR = "data"
DOWNLOADS_DIR = os.path.join(DATA_DIR, "downloads")
SCHOOL_DATA_FILE = os.path.join(DATA_DIR, "school_data_comprehensive.json")
SUBURB_DATA_FILE = os.path.join(DATA_DIR, "victoria_suburbs.json")
GEOCODE_DB_FILE = os.path.join(DATA_DIR, "generated_geocode_for_frontend.js")

# Ensure directories exist
os.makedirs(DATA_DIR, exist_ok=True)
os.makedirs(DOWNLOADS_DIR, exist_ok=True)

# URLs for data sources
BETTER_EDUCATION_URL = "https://bettereducation.com.au/school/secondary/vic/vic_top_secondary_schools.aspx"
FIND_SCHOOL_URL = "https://www.findmyschool.vic.gov.au/"
VIC_GOVT_SCHOOLS_URL = "https://www.education.vic.gov.au/about/research/Pages/datavic.aspx"
VIC_SUBURBS_URL = "https://www.matthewproctor.com/australian_postcodes"

# User agent for requests
HEADERS = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

def fetch_better_education_data():
    """Fetch school ranking data from Better Education website."""
    print(f"Fetching school rankings from {BETTER_EDUCATION_URL}...")
    try:
        response = requests.get(BETTER_EDUCATION_URL, headers=HEADERS)
        response.raise_for_status()
    except requests.exceptions.RequestException as e:
        print(f"Error fetching Better Education data: {e}")
        return []

    soup = BeautifulSoup(response.content, 'html.parser')
    schools = []

    # Find the main table containing the school rankings
    h4_tag = soup.find('h4', string=re.compile(r'Top Secondary Schools'))
    if h4_tag:
        table = h4_tag.find_next_sibling('table')
    else:
        # Fallback: try finding any large table
        tables = soup.find_all('table')
        table = next((t for t in tables if len(t.find_all('tr')) > 20), None)

    if not table:
        print("Error: Could not find the school ranking table.")
        return []

    rows = table.find_all('tr')
    print(f"Found {len(rows) - 1} potential school entries from Better Education")

    # Skip the header row
    for row in rows[1:]:
        cols = row.find_all('td')
        if len(cols) >= 4:  # Expecting at least Rank, Name, Suburb, Score
            try:
                rank_text = cols[0].get_text(strip=True)
                rank = int(re.match(r'\d+', rank_text).group()) if re.match(r'\d+', rank_text) else None
                name = cols[1].get_text(strip=True)
                suburb_text = cols[2].get_text(strip=True)
                
                # Extract suburb name and postcode if available
                # Corrected regex to handle various formats like "Suburb", "Suburb Postcode", "Suburb, Postcode"
                suburb_match = re.match(r'^(.*?)(?:,\s*|\s+)(\d{4})$', suburb_text) # Corrected regex pattern escape
                if suburb_match:
                    suburb_name = suburb_match.group(1).strip()
                    postcode = suburb_match.group(2).strip()
                else:
                    # If no postcode found, assume the whole text is the suburb name
                    suburb_name = suburb_text.strip()
                    postcode = "" # Set postcode to empty if not found

                # Find score
                score_2023 = None
                for i in range(3, len(cols)):
                    score_text = cols[i].get_text(strip=True)
                    if re.match(r'^\d{1,2}(\.\d)?$', score_text):
                        score_2023 = float(score_text)
                        break

                if rank is not None and name and suburb_name and score_2023 is not None:
                    schools.append({
                        'id': f"BE-{rank}-{name.replace(' ', '')[:10]}",
                        'rank': rank,
                        'name': name,
                        'suburb_name': suburb_name, # Correctly assigned suburb name
                        'suburb': postcode,  # Correctly assigned postcode
                        'type': 'Unknown',  # Will try to determine later
                        'score_2023': score_2023,
                        'rank_history': [rank],
                        'data_source': 'Better Education'
                    })
            except Exception as e:
                print(f"Error parsing row: {e}")

    print(f"Successfully parsed {len(schools)} schools from Better Education.")
    return schools

def fetch_vic_govt_school_data():
    """Load comprehensive Victorian government school data from the cached JSON file."""
    govt_schools_file = os.path.join(DOWNLOADS_DIR, "vic_govt_schools.json")
    print(f"Loading Victorian government school data from {govt_schools_file}...")
    
    if not os.path.exists(govt_schools_file):
        print(f"Error: Government schools data file not found at {govt_schools_file}.")
        print("Please run fetch_vic_govt_schools.py first.")
        return []
        
    try:
        with open(govt_schools_file, 'r', encoding='utf-8') as f:
            data = json.load(f)
            govt_schools = data.get('schools', [])
            print(f"Loaded {len(govt_schools)} schools from {govt_schools_file}.")
            # Ensure data_source is set correctly
            for school in govt_schools:
                school['data_source'] = school.get('data_source', 'VIC Government (Loaded)')
            return govt_schools
    except json.JSONDecodeError as e:
        print(f"Error decoding JSON from {govt_schools_file}: {e}")
        return []
    except Exception as e:
        print(f"An unexpected error occurred loading {govt_schools_file}: {e}")
        return []

def fetch_victoria_suburbs():
    """Fetch comprehensive list of Victorian suburbs with postcodes."""
    print(f"Fetching Victorian suburbs data...")
    
    # Path to the Australian postcodes CSV file
    postcodes_file = os.path.join(DOWNLOADS_DIR, "aus_postcodes.csv")
    
    # Check if we already have the postcodes file
    if not os.path.exists(postcodes_file):
        print(f"Downloading Australian postcodes data from {VIC_SUBURBS_URL}...")
        try:
            # Try to download the file
            response = requests.get("https://raw.githubusercontent.com/matthewproctor/australianpostcodes/master/australian_postcodes.csv", headers=HEADERS)
            response.raise_for_status()
            
            # Save the file
            with open(postcodes_file, 'wb') as f:
                f.write(response.content)
            print(f"Successfully downloaded postcodes data to {postcodes_file}")
        except Exception as e:
            print(f"Error downloading postcodes data: {e}")
            print("Postcodes file not found. Please download manually from:")
            print(VIC_SUBURBS_URL)
            print(f"And save to: {postcodes_file}")
            return []
    
    # Parse the CSV file to extract Victorian suburbs
    suburbs = []
    valid_count = 0
    try:
        with open(postcodes_file, 'r', encoding='utf-8', errors='replace') as f:
            # Try to determine the CSV dialect and field names
            sample = f.read(2048)
            f.seek(0)
            
            # Check if the file has headers
            sniffer = csv.Sniffer()
            has_header = sniffer.has_header(sample)
            dialect = sniffer.sniff(sample)
            
            # Read the CSV file
            reader = csv.DictReader(f, dialect=dialect)
            
            # Determine the field names for postcode, place name, latitude and longitude
            field_names = reader.fieldnames
            postcode_field = next((f for f in field_names if f.lower() in ['postcode', 'post_code', 'postal_code']), None)
            place_field = next((f for f in field_names if f.lower() in ['place_name', 'suburb', 'locality', 'place']), None)
            lat_field = next((f for f in field_names if f.lower() in ['latitude', 'lat']), None)
            lon_field = next((f for f in field_names if f.lower() in ['longitude', 'lon', 'long']), None)
            
            if not all([postcode_field, place_field, lat_field, lon_field]):
                print(f"Warning: Could not identify all required fields in CSV. Available fields: {field_names}")
                print(f"Using best guess for field names.")
                postcode_field = postcode_field or 'postcode'
                place_field = place_field or 'place_name'
                lat_field = lat_field or 'latitude'
                lon_field = lon_field or 'longitude'
            
            for row in reader:
                # Filter for Victorian suburbs (postcodes 3000-3999)
                postcode = row.get(postcode_field, '')
                if postcode and postcode.isdigit() and postcode.startswith('3'):
                    place_name = row.get(place_field, '').strip()
                    if not place_name:
                        continue
                    
                    # Try to parse latitude and longitude
                    try:
                        lat_str = row.get(lat_field, '')
                        lon_str = row.get(lon_field, '')
                        
                        latitude = float(lat_str) if lat_str and lat_str.strip() else None
                        longitude = float(lon_str) if lon_str and lon_str.strip() else None
                        
                        # Only add suburbs with valid coordinates
                        if latitude is not None and longitude is not None:
                            suburb = {
                                'name': place_name.lower(),
                                'postcode': postcode,
                                'state': 'VIC',
                                'latitude': latitude,
                                'longitude': longitude
                            }
                            suburbs.append(suburb)
                            valid_count += 1
                    except (ValueError, TypeError) as e:
                        # Skip entries with invalid coordinates
                        print(f"Warning: Invalid coordinates for {place_name}, {postcode}: {e}")
                        continue
    except Exception as e:
        print(f"Error parsing postcodes data: {e}")
        return []
    
    print(f"Found {len(suburbs)} Victorian suburbs with valid coordinates (out of {valid_count} total).")
    return suburbs

def merge_school_data(better_education_schools, govt_schools):
    """Merge school data, prioritizing government data and adding Better Education rank/score only for Secondary/Combined schools."""
    print("Merging school data from multiple sources...")

    # Create a dictionary of Better Education schools keyed by lowercase name for quick lookup
    be_schools_dict = {school['name'].lower(): school for school in better_education_schools}

    merged_schools = []
    updated_with_be_rank = 0

    # Start with government schools as the base
    for govt_school in govt_schools:
        merged_school = govt_school.copy() # Start with the government data
        key = merged_school['name'].lower()
        school_level = merged_school.get('level')

        # Check if it's a Secondary or Combined school and if it exists in Better Education data
        if school_level in ['Secondary', 'Combined'] and key in be_schools_dict:
            be_school = be_schools_dict[key]
            # Add rank and score from Better Education
            merged_school['rank'] = be_school.get('rank')
            merged_school['score_2023'] = be_school.get('score_2023')
            # Update data source to reflect merge
            merged_school['data_source'] = f"{merged_school.get('data_source', 'VIC Govt')} + Better Education Rank"
            updated_with_be_rank += 1
        
        merged_schools.append(merged_school)

    # Identify Better Education schools not found in government list (optional, could indicate discrepancies)
    govt_school_names = {school['name'].lower() for school in govt_schools}
    be_only_schools = [school for name, school in be_schools_dict.items() if name not in govt_school_names]
    if be_only_schools:
        print(f"Warning: {len(be_only_schools)} schools found in Better Education but not in Government data (will not be included in final list).")
        # Optionally log these schools for review

    print(f"Merged data: {len(merged_schools)} total schools (based on Govt list).")
    print(f"{updated_with_be_rank} Secondary/Combined schools updated with Better Education rank/score.")
    return merged_schools

def generate_geocode_db(suburbs):
    """Generate the geocode-db.js file with Victorian suburbs data."""
    print(f"Generating {GEOCODE_DB_FILE}...")
    
    # Validate input data
    if not suburbs:
        print("Error: No suburb data available to generate geocode database.")
        return False
    
    # Filter valid suburbs with coordinates
    valid_suburbs = [s for s in suburbs if s.get('latitude') and s.get('longitude')]
    
    if not valid_suburbs:
        print("Error: No suburbs with valid coordinates found.")
        return False
    
    print(f"Processing {len(valid_suburbs)} suburbs with valid coordinates...")
    
    # Create the JavaScript content
    js_content = """/**
 * Victorian Suburbs Geocode Database
 * 
 * This file contains a comprehensive database of Victorian suburbs with their coordinates.
 * It provides efficient lookup functions for geocoding suburb names and postcodes.
 */

// Comprehensive database of Victorian suburbs with their coordinates
const victorianSuburbsDB = {
"""
    
    # Add each suburb to the database
    for i, suburb in enumerate(valid_suburbs):
        # Ensure suburb name is properly escaped for JavaScript
        suburb_name = suburb['name'].replace("'", "\\'").replace('"', '\\"')
        
        # Format the suburb entry with proper JavaScript syntax
        suburb_entry = f"    '{suburb_name}': {{ lat: {suburb['latitude']}, lon: {suburb['longitude']}, postcode: '{suburb['postcode']}' }}"
        
        # Add comma if not the last entry
        if i < len(valid_suburbs) - 1:
            suburb_entry += ","
            
        js_content += suburb_entry + "\n"
    
    # Close the object definition
    js_content += "};"

    # Add utility functions with improved suburb matching
    js_content += """// Function to get coordinates for a suburb
function getSuburbCoordinates(suburbName, postcode) {
    // Handle null or undefined inputs
    if (!suburbName) return null;
    
    // Normalize the suburb name (lowercase, trim)
    const normalizedName = suburbName.toLowerCase().trim();
    
    // Try direct lookup
    if (victorianSuburbsDB[normalizedName]) {
        return {
            lat: victorianSuburbsDB[normalizedName].lat,
            lon: victorianSuburbsDB[normalizedName].lon
        };
    }
    
    // Try lookup with postcode
    if (postcode) {
        for (const [name, data] of Object.entries(victorianSuburbsDB)) {
            if (data.postcode === postcode && 
                (name.includes(normalizedName) || normalizedName.includes(name))) {
                return { lat: data.lat, lon: data.lon };
            }
        }
    }
    
    // Try fuzzy matching without postcode
    for (const [name, data] of Object.entries(victorianSuburbsDB)) {
        if (name.includes(normalizedName) || normalizedName.includes(name)) {
            return { lat: data.lat, lon: data.lon };
        }
    }
    
    // No match found
    return null;
}

// Function to get postcode for a suburb name
function getSuburbPostcode(suburbName) {
    if (!suburbName) return null;
    
    const normalizedName = suburbName.toLowerCase().trim();
    
    // Try direct lookup
    if (victorianSuburbsDB[normalizedName]) {
        return victorianSuburbsDB[normalizedName].postcode;
    }
    
    // Try fuzzy matching
    for (const [name, data] of Object.entries(victorianSuburbsDB)) {
        if (name.includes(normalizedName) || normalizedName.includes(name)) {
            return data.postcode;
        }
    }
    
    return null;
}

// Export the database and functions for use in other modules using ES Module syntax
export {
    victorianSuburbsDB,
    getSuburbCoordinates,
    getSuburbPostcode
};
"""
    
    # Create the js directory if it doesn't exist
    js_dir = os.path.dirname(GEOCODE_DB_FILE)
    try:
        os.makedirs(js_dir, exist_ok=True)
        print(f"Ensuring directory exists: {js_dir}")
    except Exception as e:
        print(f"Error creating directory {js_dir}: {e}")
        return False
    
    # Write the file
    try:
        with open(GEOCODE_DB_FILE, 'w', encoding='utf-8') as f:
            f.write(js_content)
        print(f"Successfully generated {GEOCODE_DB_FILE} with {len(valid_suburbs)} suburbs.")
        return True
    except Exception as e:
        print(f"Error generating geocode database: {e}")
        return False

def save_data(data, filename):
    """Save data to a JSON file."""
    print(f"Saving data to {filename}...")
    try:
        with open(filename, 'w', encoding='utf-8') as f:
            json.dump(data, f, indent=4, ensure_ascii=False)
        print(f"Successfully saved data to {filename}.")
        return True
    except Exception as e:
        print(f"Error saving data to {filename}: {e}")
        return False

def main():
    print("=== Comprehensive Victorian Schools and Suburbs Data Scraper ===")
    
    # Fetch school data from Better Education
    better_education_schools = fetch_better_education_data()
    
    # Fetch additional school data from Victorian government
    govt_schools = fetch_vic_govt_school_data()
    
    # Merge school data from different sources
    all_schools = merge_school_data(better_education_schools, govt_schools)
    
    # Save comprehensive school data
    school_data = {"schools": all_schools}
    save_data(school_data, SCHOOL_DATA_FILE)
    
    # Fetch Victorian suburbs data
    suburbs = fetch_victoria_suburbs()
    
    # Save suburbs data
    suburb_data = {"suburbs": suburbs}
    save_data(suburb_data, SUBURB_DATA_FILE)
    
    # Generate geocode-db.js file
    generate_geocode_db(suburbs)
    
    print("\n=== Data Collection Summary ===")
    print(f"Total schools collected: {len(all_schools)}")
    print(f"Total suburbs collected: {len(suburbs)}")
    print("\nData files created:")
    print(f"- {SCHOOL_DATA_FILE}")
    print(f"- {SUBURB_DATA_FILE}")
    print(f"- {GEOCODE_DB_FILE}")
    
    print("\nTo update the geocodes for schools, run:")
    print("python add_geocodes.py")

if __name__ == "__main__":
    main()