data_comparison.py

import json
import csv
import os
import requests
import pandas as pd
import time
from collections import defaultdict
import re # Import re for cleaning names
import traceback # Import traceback at the top level

# Configuration
SCHOOL_DATA_FILE = os.path.join('data', 'school_data_final.json')
GEOCODE_DB_FILE = os.path.join('js', 'geocode-db.js')
DOWNLOAD_DIR = os.path.join('data', 'downloads')
WORKSPACE_DIR = os.path.dirname(os.path.abspath(__file__)) # Get absolute path of the script's directory

# VCAA Achievement Data Files (Using absolute paths)
VCAA_ACHIEVEMENT_FILES = {
    2021: os.path.join(WORKSPACE_DIR, DOWNLOAD_DIR, '2021SeniorSecondaryCompletionAndAchievementInformation.xlsx'),
    2022: os.path.join(WORKSPACE_DIR, DOWNLOAD_DIR, '2022SeniorSecondaryCompletionAndAchievementInformation.xlsx'),
    2023: os.path.join(WORKSPACE_DIR, DOWNLOAD_DIR, '2023SeniorSecondaryCompletionAndAchievementInformation.xlsx'),
    # 2024: os.path.join(WORKSPACE_DIR, DOWNLOAD_DIR, '2024SeniorSecondaryCompletionAndAchievementInformation.xlsx'), # Add 2024 when schema is confirmed
}
VCAA_SEARCH_EXPORT_FILE = os.path.join(WORKSPACE_DIR, DOWNLOAD_DIR, 'vcaa-search-export.xlsx')

# Create download directory if it doesn't exist
if not os.path.exists(DOWNLOAD_DIR):
    os.makedirs(DOWNLOAD_DIR)

# URLs for official data sources (Keep existing ones)
VIC_SCHOOLS_URL = "https://discover.data.vic.gov.au/dataset/1a98e3f5-97d2-42e8-9e21-5a6223d78d9e/resource/dae3c76f-b1c7-4775-9c97-1fdd4adaf6c2/download/school-locations-2023.csv"
AUS_POSTCODES_URL = "https://raw.githubusercontent.com/Elkfox/Australian-Postcode-Data/master/au_postcodes.csv"
# Additional data sources for comprehensive school information
VIC_SCHOOLS_DIRECTORY_URL = "https://www.education.vic.gov.au/Documents/about/research/datavic/schoollocations.csv"
VIC_SCHOOLS_PERFORMANCE_URL = "https://discover.data.vic.gov.au/dataset/0260cb71-5c9f-4fef-b42a-a26c3e619fc0/resource/0e1eeec2-d485-4ff6-a068-a6f4c66c4c7e/download/school-performance-data-2023.csv"
NOMINATIM_API_URL = "https://nominatim.openstreetmap.org/search"

# --- Existing Functions (download_file, extract_victorian_suburbs_db, load_school_data, etc.) ---
def download_file(url, filename):
    """Download a file from a URL to the specified filename"""
    print(f"Downloading {filename} from {url}...")
    try:
        response = requests.get(url, timeout=30)
        response.raise_for_status()
        file_path = os.path.join(DOWNLOAD_DIR, filename)
        with open(file_path, 'wb') as f:
            f.write(response.content)
        print(f"Successfully downloaded {filename}")
        return file_path
    except requests.exceptions.RequestException as e:
        print(f"Error downloading {filename}: {e}")
        return None

def extract_victorian_suburbs_db():
    """Extract suburb data from the geocode-db.js file"""
    print("Extracting suburb data from geocode-db.js...")
    try:
        with open(GEOCODE_DB_FILE, 'r', encoding='utf-8') as f:
            content = f.read()

        # Find the start and end of the victorianSuburbsDB object
        start_marker = "const victorianSuburbsDB = {"
        end_marker = "};\n\n// Function to get coordinates"

        start_idx = content.find(start_marker) + len(start_marker)
        end_idx = content.find(end_marker)

        if start_idx == -1 or end_idx == -1:
            print("Could not find victorianSuburbsDB in geocode-db.js")
            return {}

        db_content = content[start_idx:end_idx].strip()

        # Convert the JS object to a Python dictionary
        suburbs_dict = {}
        for line in db_content.split('\n'):
            line = line.strip()
            if not line or line.startswith('//'):
                continue

            if ':' in line:
                key_part = line.split(':', 1)[0].strip()
                suburb_name = key_part.strip("'")

                # Extract lat, lon, postcode from the line
                if '{ lat:' in line and ', lon:' in line and ', postcode:' in line:
                    lat_part = line.split('{ lat:', 1)[1].split(',', 1)[0].strip()
                    lon_part = line.split(', lon:', 1)[1].split(',', 1)[0].strip()
                    postcode_part = line.split(', postcode:', 1)[1].split('}', 1)[0].strip("' ")

                    try:
                        suburbs_dict[suburb_name] = {
                            'lat': float(lat_part),
                            'lon': float(lon_part),
                            'postcode': postcode_part.strip("'")
                        }
                    except ValueError:
                        print(f"Error parsing coordinates for {suburb_name}")

        print(f"Extracted {len(suburbs_dict)} suburbs from geocode-db.js")
        return suburbs_dict
    except Exception as e:
        print(f"Error extracting suburb data: {e}")
        return {}

def load_school_data():
    """Load school data from the school_data_final.json file"""
    print(f"Loading school data from {SCHOOL_DATA_FILE}...")
    try:
        with open(SCHOOL_DATA_FILE, 'r', encoding='utf-8') as f:
            data = json.load(f)
        schools = data.get('schools', [])
        print(f"Loaded {len(schools)} schools from {SCHOOL_DATA_FILE}")
        # Create a lookup dictionary based on cleaned name and postcode
        schools_dict = {}
        for school in schools:
            name = str(school.get('name', '')).strip().lower()
            postcode = str(school.get('suburb', '')).strip() # 'suburb' key holds postcode
            key = (name, postcode)
            if name and postcode:
                schools_dict[key] = school
        print(f"Created lookup for {len(schools_dict)} schools based on name and postcode.")
        return schools, schools_dict
    except FileNotFoundError:
        print(f"Error: School data file not found at {SCHOOL_DATA_FILE}")
        return [], {}
    except Exception as e:
        print(f"Error loading school data: {e}")
        return [], {}

def load_vic_schools_csv(file_path):
    """Load Victorian schools data from CSV"""
    print(f"Loading Victorian schools data from {file_path}...")
    try:
        df = pd.read_csv(file_path)
        # Filter for secondary schools only (includes P-12 schools)
        secondary_schools = df[
            df['School_Type'].str.contains('Secondary|P-12', case=False, na=False) |
            df['School_Name'].str.contains('High School|Secondary College|Grammar|College', case=False, na=False)
        ].copy()
        print(f"Loaded {len(df)} schools from CSV, filtered to {len(secondary_schools)} secondary schools")
        return secondary_schools
    except Exception as e:
        print(f"Error loading Victorian schools data: {e}")
        return pd.DataFrame()

def load_aus_postcodes_csv(file_path):
    """Load Australian postcodes data from CSV"""
    print(f"Loading Australian postcodes data from {file_path}...")
    try:
        df = pd.read_csv(file_path)
        # Filter for Victorian postcodes only
        vic_df = df[df['state_code'] == 'VIC']
        print(f"Loaded {len(vic_df)} Victorian postcodes from CSV")
        return vic_df
    except Exception as e:
        print(f"Error loading Australian postcodes data: {e}")
        return pd.DataFrame()

# --- New Function to Load VCAA Achievement Data ---
def load_vcaa_achievement_data(year, file_path):
    """Loads VCAA achievement data for a specific year from an Excel file."""
    print(f"Loading VCAA achievement data for {year} from {file_path}...")
    if not os.path.exists(file_path):
        # print(f"Error: File not found - {file_path}") # Keep print for logging
        return f"Error: File not found - {file_path}"
    try:
        # --- Dynamically find the header row ---
        header_row_index = None
        # Read the first few rows to find the header (INSIDE TRY BLOCK)
        try:
            df_peek = pd.read_excel(file_path, sheet_name=0, header=None, nrows=20) # Increased nrows to 20
        except Exception as peek_e:
            print(f"*** ERROR during initial peek read from {file_path} ***")
            print(f"Error reading initial rows (peek): {peek_e}")
            traceback.print_exc()
            return f"Error: Could not read initial rows (peek) from {file_path}: {peek_e}"
        # Broaden keywords for more flexible matching
        expected_header_keywords = ['school', 'median', 'postcode'] # Using simpler, more general keywords
        best_match_count = 0
        best_match_index = None
        for i, row in df_peek.iterrows():
            current_match_count = 0
            # Check each cell in the row individually
            for cell_value in row.dropna(): # Iterate through non-null cells
                cell_str = str(cell_value).lower()
                for keyword in expected_header_keywords:
                    if keyword.lower() in cell_str:
                        current_match_count += 1
                        # Optional: Break inner keyword loop if found in cell to avoid double counting per cell?
                        # break # Let's not break for now, count all occurrences

            # Normalize count: A keyword might appear multiple times across cells in a row.
            # We only care if *each* keyword is present *somewhere* in the row.
            # Let's refine the count logic:
            keywords_found_in_row = set()
            for cell_value in row.dropna():
                cell_str = str(cell_value).lower()
                for keyword in expected_header_keywords:
                    if keyword.lower() in cell_str:
                        keywords_found_in_row.add(keyword.lower())

            current_match_count = len(keywords_found_in_row)
            # print(f"  DEBUG: Row {i}, Keywords Found: {keywords_found_in_row}, Count: {current_match_count}") # Debug print removed

            if current_match_count > best_match_count:
                best_match_count = current_match_count
                best_match_index = i

        # Consider it a match if at least 2 keywords are found
        if best_match_count >= 2:
            header_row_index = best_match_index
            print(f"  Dynamically found potential header row at index: {header_row_index} with {best_match_count} keyword matches.")
        else:
            header_row_index = None # Explicitly set to None if not enough matches

        if header_row_index is None:
            # Use the original, more specific keywords in the error message for clarity
            original_keywords = ['School Name', 'Median VCE study score', 'Address Postcode']
            print(f"Error: Could not dynamically find a suitable header row (found less than 2 keywords like {original_keywords}) in {file_path}")
            return f"Error: Could not dynamically find a suitable header row (found less than 2 keywords like {original_keywords}) in {file_path}"

        # Reload the DataFrame using the dynamically found header row (INSIDE TRY BLOCK)
        try:
            df = pd.read_excel(file_path, sheet_name=0, header=header_row_index)
        except Exception as load_e:
            print(f"*** ERROR during DataFrame reload (header {header_row_index}) from {file_path} ***")
            print(f"Error reloading DataFrame: {load_e}")
            traceback.print_exc()
            return f"Error: Could not reload DataFrame (header {header_row_index}) from {file_path}: {load_e}"
        # Strip whitespace from column names
        df.columns = df.columns.str.strip()
        available_columns = list(df.columns) # Explicitly convert to list after stripping
        print(f"  Columns in {year} achievement data (Header={header_row_index}, Stripped): {available_columns}")

        # --- Define required column substrings ---
        required_col_substrings = {
            'name': 'School', # Name is essential for matching
            # 'postcode': 'Locality', # Postcode/Locality is unreliable, removing requirement for key
            'median_score': 'Median VCE study score', # Score data
            'high_achievers': 'Percentage of study scores of 40 and over' # Achiever data
        }
        col_mapping = {}
        # Find actual column names containing the required substrings
        for key, substring in required_col_substrings.items():
            found_col = None
            for actual_col in available_columns:
                # Prioritize exact match first, then substring match
                if substring.lower() == actual_col.lower():
                    found_col = actual_col
                    break # Exact match found, stop searching this key
                elif substring.lower() in actual_col.lower():
                    found_col = actual_col # Found a substring match, continue searching in case of exact match
            if found_col:
                 col_mapping[key] = found_col
            # No need to break outer loop, find best match for all keys

        # Check if essential column keys ('name', 'median_score', 'high_achievers') were mapped
        essential_keys = ['name', 'median_score', 'high_achievers']
        missing_keys = [key for key in essential_keys if key not in col_mapping]
        if missing_keys:
            print(f"Warning: Could not map essential keys in {year} data: {missing_keys}.")
            print(f"  Required Substrings: {{k: required_col_substrings[k] for k in essential_keys}}")
            print(f"  Found Mapping: {col_mapping}")
            print(f"  Available columns (Stripped): {available_columns}")
            return f"Error: Could not map essential keys in {year} data: {missing_keys}. Available: {available_columns}" # Cannot proceed without essential columns

        # Select and rename columns using the found mapping (excluding postcode)
        columns_to_select = [col_mapping[key] for key in essential_keys]
        df_selected = df[columns_to_select].copy()

        rename_mapping = {
            col_mapping['name']: 'name',
            col_mapping['median_score']: f'median_score_{year}',
            col_mapping['high_achievers']: f'high_achievers_{year}'
        }
        df_selected.rename(columns=rename_mapping, inplace=True)

        # Clean data
        df_selected['name'] = df_selected['name'].astype(str).str.strip().str.lower()
        # No postcode cleaning needed here anymore
        # df_selected['postcode'] = df_selected['postcode'].astype(str).str.strip()

        # Convert score/percentage columns to numeric, handling errors
        score_col_name = f'median_score_{year}'
        achiever_col_name = f'high_achievers_{year}'
        try:
            df_selected[score_col_name] = pd.to_numeric(df_selected[score_col_name], errors='coerce')
            df_selected[achiever_col_name] = pd.to_numeric(df_selected[achiever_col_name], errors='coerce')
        except Exception as e:
            print(f"  Error during numeric conversion for {year}: {e}")
            # Optionally print problematic rows/columns here if needed
            return f"Error: During numeric conversion for {year}: {e}"

        # Create a lookup dictionary: lowercase name -> {score, achievers}
        achievement_dict = {}
        processed_count = 0
        error_count = 0
        duplicate_names = set()
        processed_names = set()
        for index, row in df_selected.iterrows():
            try:
                name_key = row['name']
                # Ensure name is not empty/NaN before adding
                if name_key and pd.notna(name_key) and name_key != 'nan':
                    # Handle duplicate names in VCAA data - log and overwrite/take last?
                    if name_key in processed_names:
                        duplicate_names.add(name_key)
                    else:
                        processed_names.add(name_key)

                    achievement_dict[name_key] = {
                        score_col_name: row[score_col_name],
                        achiever_col_name: row[achiever_col_name]
                    }
                    processed_count += 1 # Count successful processing attempts
                # else: # Optional: Log skipped rows
                #     print(f"  Skipping row {index} due to invalid name: {name_key}")
            except Exception as e:
                error_count += 1
                if error_count < 5: # Limit error logging
                    print(f"  Error processing row {index} for {year}: {e}. Row data: {row.to_dict()}")
                elif error_count == 5:
                    print(f"  ... further row processing errors suppressed ...")

        if error_count > 0:
            print(f"  Encountered {error_count} errors while processing rows for {year}.")
            # Decide if partial data is acceptable or if it should fail
            # return None # Uncomment to fail if any row errors occur

        print(f"Successfully processed {processed_count} schools from {year} VCAA data.")
        return achievement_dict

    # Removed ImportError handling as openpyxl is confirmed installed
    except Exception as e:
        # This catches errors *after* successful loading and header processing
        print(f"*** ERROR processing VCAA data for {year} AFTER loading ***")
        print(f"Error: {e}")
        traceback.print_exc()
        return f"Error: Processing VCAA data for {year} AFTER loading: {e}"

# --- New Function to Load VCAA Search Export Data ---
def integrate_vcaa_data(schools_dict, all_achievement_data):
    """Integrates VCAA achievement data into the main school dictionary."""
    print("\nIntegrating VCAA achievement data...")
    updated_count = 0
    schools_not_found = defaultdict(list)

    for year, achievement_data in all_achievement_data.items():
        # Check if achievement_data is a dictionary (valid data) or a string (error message)
        if not isinstance(achievement_data, dict):
            print(f"Skipping integration for {year}. Reason: {achievement_data}") # Print the error message
            continue

        # Original check for None (though the isinstance check might make this redundant)
        # if achievement_data is None:
        #     print(f"Skipping integration for {year} due to loading errors.")
        #     continue

        year_updated_count = 0
        year_matched_count = 0
        # Iterate through the main schools_dict (keyed by (name, postcode))
        for (school_name, school_postcode), school_data in schools_dict.items():
            # Check if the school_name exists in the current year's achievement_data (keyed by name)
            if school_name in achievement_data:
                vcaa_stats = achievement_data[school_name]
                school_data.update(vcaa_stats) # Add/update the year-specific score and achiever percentage
                year_updated_count += 1 # Count updates to existing school records
                year_matched_count += 1
            # No 'else' needed here as we are iterating through existing schools

        # Report on schools from VCAA data that weren't found in our main dict
        vcaa_school_names = set(achievement_data.keys())
        main_school_names = set(name for name, postcode in schools_dict.keys())
        unmatched_vcaa_names = vcaa_school_names - main_school_names
        schools_not_found[year] = list(unmatched_vcaa_names)

        print(f"  {year}: Matched and updated data for {year_updated_count} schools.")
        if schools_not_found[year]:
            print(f"    - Could not match {len(schools_not_found[year])} schools from {year} VCAA data.")
            # Optionally save or log unmatched schools
            # print(f"Unmatched {year}: {schools_not_found[year][:5]}...") # Print first 5 unmatched

        updated_count += year_updated_count

    print(f"Finished integrating VCAA data. Updated {updated_count} school records overall.")
    # The schools_dict is modified in-place

# --- Existing Comparison Functions (compare_schools, compare_suburbs) ---
def compare_schools(our_schools, official_schools):
    """Compare our school data with official school data"""
    print("\nComparing school data...")

    # Create dictionaries for easier lookup
    our_schools_dict = {school.get('name', '').lower(): school for school in our_schools}

    # Convert official schools DataFrame to dictionary
    official_schools_dict = {}
    for _, row in official_schools.iterrows():
        school_name = row.get('School_Name', '').lower()
        if school_name:
            official_schools_dict[school_name] = row

    # Find missing schools
    missing_schools = []
    for name, school in official_schools_dict.items():
        if name not in our_schools_dict:
            # Extract full address components for better geocoding
            address_line1 = school.get('Address_Line_1', '')
            address_town = school.get('Address_Town', '')
            address_state = 'VIC'
            postcode = school.get('Address_Postcode')
            full_address = f"{address_line1}, {address_town}, {address_state} {postcode}".strip()

            # Determine school type more accurately
            school_type = school.get('School_Type', '')
            if 'Catholic' in school_type:
                type_category = 'Catholic'
            elif 'Independent' in school_type:
                type_category = 'Independent/Private'
            elif 'Government' in school_type:
                type_category = 'Public'
            else:
                type_category = school_type

            missing_schools.append({
                'name': school.get('School_Name'),
                'address': full_address,
                'address_line1': address_line1,
                'address_town': address_town,
                'postcode': postcode,
                'type': type_category,
                'education_sector': school.get('Education_Sector', ''),
                'school_type': school_type,
                'latitude': school.get('Latitude'),
                'longitude': school.get('Longitude')
            })

    # Print results
    print(f"Our database has {len(our_schools)} schools")
    print(f"Official data has {len(official_schools)} schools")
    print(f"Missing from our database: {len(missing_schools)} schools")

    # Save missing schools to file
    if missing_schools:
        missing_schools_file = os.path.join(DOWNLOAD_DIR, 'missing_schools.json')
        with open(missing_schools_file, 'w', encoding='utf-8') as f:
            json.dump(missing_schools, f, indent=2)
        print(f"Saved list of missing schools to {missing_schools_file}")

    return missing_schools

def compare_suburbs(our_suburbs, official_postcodes):
    """Compare our suburb data with official postcode data"""
    print("\nComparing suburb data...")

    # Create sets for easier comparison
    our_suburb_postcodes = set()
    for name, data in our_suburbs.items():
        our_suburb_postcodes.add((name.lower(), data.get('postcode')))

    official_suburb_postcodes = set()
    for _, row in official_postcodes.iterrows():
        official_suburb_postcodes.add((row.get('place_name', '').lower(), str(row.get('postcode', ''))))

    # Find differences
    missing_from_ours = official_suburb_postcodes - our_suburb_postcodes
    extra_in_ours = our_suburb_postcodes - official_suburb_postcodes

    print(f"Our database has {len(our_suburb_postcodes)} suburb/postcode pairs")
    print(f"Official data has {len(official_suburb_postcodes)} suburb/postcode pairs")
    print(f"Missing from our database: {len(missing_from_ours)}")
    print(f"Extra in our database (not in official): {len(extra_in_ours)}")

    # Save differences if needed
    if missing_from_ours:
        missing_suburbs_file = os.path.join(DOWNLOAD_DIR, 'missing_suburbs.json')
        with open(missing_suburbs_file, 'w', encoding='utf-8') as f:
            json.dump(list(missing_from_ours), f, indent=2)
        print(f"Saved list of missing suburbs to {missing_suburbs_file}")

    if extra_in_ours:
        extra_suburbs_file = os.path.join(DOWNLOAD_DIR, 'extra_suburbs.json')
        with open(extra_suburbs_file, 'w', encoding='utf-8') as f:
            json.dump(list(extra_in_ours), f, indent=2)
        print(f"Saved list of extra suburbs to {extra_suburbs_file}")

# --- Function to save updated data ---
def save_updated_school_data(schools_list, file_path):
    """Saves the updated list of schools back to the JSON file."""
    print(f"\nSaving updated school data to {file_path}...")
    output_data = {"schools": schools_list}
    try:
        with open(file_path, 'w', encoding='utf-8') as f:
            json.dump(output_data, f, indent=4, ensure_ascii=False)
        print(f"Successfully saved {len(schools_list)} schools to {file_path}")
    except Exception as e:
        print(f"Error saving updated school data: {e}")

# --- Main Execution Logic ---
def main():
    print("=== Starting Data Comparison and Integration ===")

    # 1. Load existing school data
    our_schools_list, our_schools_dict = load_school_data()
    if not our_schools_list:
        print("Cannot proceed without existing school data.")
        return

    # 2. Load VCAA Achievement Data for multiple years
    all_achievement_data = {}
    for year, file_path in VCAA_ACHIEVEMENT_FILES.items():
        try:
            result = load_vcaa_achievement_data(year, file_path)
            all_achievement_data[year] = result
            if result is None:
                # Explicitly print if the function returned None without an exception
                print(f"Debug: load_vcaa_achievement_data for {year} returned None.")
        except Exception as main_load_e:
            print(f"*** ERROR calling load_vcaa_achievement_data for {year} in main ***")
            print(f"Error: {main_load_e}")
            traceback.print_exc()
            all_achievement_data[year] = None # Ensure it's None if call fails

    # 3. Integrate VCAA Achievement Data
    # This modifies our_schools_dict in place
    integrate_vcaa_data(our_schools_dict, all_achievement_data)

    # 4. Process vcaa-search-export.xlsx - Temporarily commented out due to structural issues
    # vcaa_search_data = {}
    # if os.path.exists(VCAA_SEARCH_EXPORT_FILE):
    #     print(f"\nProcessing {VCAA_SEARCH_EXPORT_FILE}...")
    #     try:
    #         # Load the Excel file - Adjust header to row index 3 (4th row)
    #         df_search = pd.read_excel(VCAA_SEARCH_EXPORT_FILE, header=3)
    #         print("  First 5 rows of vcaa-search-export.xlsx (with correct header):")
    #         print(df_search.head().to_string())
    #         print(f"  Columns in vcaa-search-export: {list(df_search.columns)}")
    #
    #         # --- Adjust column names based on the actual file structure --- 
    #         # School name seems to be in the first column, Suburb in the fourth
    #         search_name_col = df_search.columns[0] 
    #         search_suburb_col = df_search.columns[3]
    #         # Postcode is missing
    #
    #         if search_name_col in df_search.columns and search_suburb_col in df_search.columns:
    #             cols_to_select = [search_name_col, search_suburb_col]
    #
    #             df_search_clean = df_search[cols_to_select].copy()
    #             df_search_clean['name_clean'] = df_search_clean[search_name_col].astype(str).str.strip().str.lower()
    #             df_search_clean['suburb_clean'] = df_search_clean[search_suburb_col].astype(str).str.strip().str.lower() # Clean suburb
    #
    #             for _, row in df_search_clean.iterrows():
    #                 # Use (name, suburb) as key for this file, as postcode is missing
    #                 key = (row['name_clean'], row['suburb_clean'])
    #                 if key[0] and key[1] and key[0] != 'nan': # Ensure name and suburb are valid
    #                     vcaa_search_data[key] = {
    #                         # Add other relevant columns if needed
    #                     }
    #             print(f"  Processed {len(vcaa_search_data)} valid entries from {VCAA_SEARCH_EXPORT_FILE}.")
    #
    #             # Integration logic would need adjustment to match on (name, suburb) or find postcode
    #             # search_integrated_count = 0
    #             # schools_not_found_search = []
    #             # print(f"  Integration for search export needs refinement.")
    #
    #         else:
    #             print(f"  Warning: Could not find expected columns ('{search_name_col}', '{search_suburb_col}') even after header adjustment. Skipping integration.")
    #
    #     except Exception as e:
    #         print(f"  Error processing {VCAA_SEARCH_EXPORT_FILE}: {e}")
    # else:
    #     print(f"\nSkipping {VCAA_SEARCH_EXPORT_FILE} - file not found.")


    # --- Optional: Run existing comparisons ---
    # Download and load official data if needed for comparison
    # vic_schools_csv_path = download_file(VIC_SCHOOLS_URL, 'vic_schools_official.csv')
    # aus_postcodes_csv_path = download_file(AUS_POSTCODES_URL, 'aus_postcodes_official.csv')
    #
    # if vic_schools_csv_path:
    #     official_schools_df = load_vic_schools_csv(vic_schools_csv_path)
    #     if not official_schools_df.empty:
    #         compare_schools(our_schools_list, official_schools_df)
    #
    # if aus_postcodes_csv_path:
    #     our_suburbs_dict = extract_victorian_suburbs_db()
    #     official_postcodes_df = load_aus_postcodes_csv(aus_postcodes_csv_path)
    #     if our_suburbs_dict and not official_postcodes_df.empty:
    #         compare_suburbs(our_suburbs_dict, official_postcodes_df)
    # ------------------------------------------

    # 5. Save the updated school data
    # Convert the updated dictionary values back to a list
    updated_schools_list = list(our_schools_dict.values())
    save_updated_school_data(updated_schools_list, SCHOOL_DATA_FILE)

    print("\n=== Data Comparison and Integration Complete ===")

if __name__ == "__main__":
    main()