"""
SQL Object Cross-Reference Tool
================================

This script cross-references SQL files in a directory tree against a list of database objects
extracted from a DDL file. It identifies which objects from the DDL have corresponding
individual SQL files in the codebase.

Purpose:
--------
- Scan directories recursively for SQL files
- Extract object definitions from each SQL file
- Match them against objects from the extracted_sql_objects.json file
- Generate a JSON report showing which objects have standalone SQL files
- Provide file path mappings for matched objects

Use Case:
---------
After extracting all objects from a master DDL file, this script helps identify which
objects have been separated into individual SQL files for version control, development,
or deployment purposes. This is useful for:
- Identifying objects that exist in both the DDL and as standalone files
- Finding discrepancies between the master DDL and individual files
- Creating deployment scripts for specific objects
- Auditing code organization and file structure

Workflow:
---------
1. Load the JSON file created by extract_sql_objects.py
2. Recursively scan the specified directory for *.sql files
3. Extract object name and type from each SQL file
4. Check if the object exists in the master DDL
5. Generate a report of matched objects with file paths

Output Format:
-------------
The script generates a JSON file with the following structure.
The format of "matched_objects" depends on the --detail-level argument:

STANDARD MODE (default, --detail-level standard):
{
  "metadata": {
    "source_json": "extracted_sql_objects.json",
    "scan_directory": "C:/path/to/search",
    "scan_date": "ISO timestamp",
    "total_sql_files_scanned": 150,
    "total_matched_objects": 45
  },
  "summary": {
    "packages": 10,
    "package_bodies": 10,
    "functions": 15,
    "procedures": 10,
    "triggers": 5
  },
  "matched_objects": {
    "packages": [
      {
        "name": "OVERNIGHT_PKG",
        "total_files": 46,
        "schools": ["ACTA", "ALB", "BUFF", "..."],
        "school_count": 45,
        "generic_count": 1
      },
      ...
    ],
    "package_bodies": [ /* same structure */ ],
    "functions": [ /* same structure */ ],
    "procedures": [ /* same structure */ ],
    "triggers": [ /* same structure */ ]
  },
  "file_mappings": [
    {
      "file": "path/to/file.sql",
      "object_type": "function",
      "object_name": "func1",
      "school": "BUFF"  // or null if generic
    },
    ...
  ]
}

MINIMAL MODE (--detail-level minimal):
  "matched_objects": {
    "packages": ["OVERNIGHT_PKG", "ARASAT_PKG", ...],
    "package_bodies": [...],
    "functions": [...],
    "procedures": [...],
    "triggers": [...]
  }

FULL MODE (--detail-level full):
  "matched_objects": {
    "packages": [
      {
        "name": "OVERNIGHT_PKG",
        "total_files": 46,
        "schools": ["ACTA", "ALB", "BUFF", "..."],
        "school_count": 45,
        "generic_count": 1,
        "files": [
          "Dev/SQL/Packages/OverNight/OverNight_pkg_ACTA.sql",
          "Dev/SQL/Packages/OverNight/OverNight_pkg_ALB.sql",
          ...
        ]
      },
      ...
    ]
  }

Note: The "file_mappings" section is always included regardless of detail level.

Usage:
------
Basic usage (searches current directory):
    python cross_reference_sql_objects.py

With custom input JSON:
    python cross_reference_sql_objects.py -i extracted_sql_objects.json

Full example with all options:
    python cross_reference_sql_objects.py -i input.json -o output.json -d ./Dev/SQL

Command-line Arguments:
----------------------
-i, --input             : Path to input JSON from extract_sql_objects.py (default: extracted_sql_objects.json)
-o, --output            : Path to output JSON file (default: matched_sql_objects.json)
-d, --directory         : Directory to search recursively for SQL files (default: current directory)
--exclude-releases      : Exclude release/version directories (supports wildcards)
--only-releases         : Only include specific release/version directories (supports wildcards)
--exclude-schools       : Exclude specific school codes
--only-schools          : Only include specific school codes
--exclude-custom        : Exclude all "Custom *" directories
--exclude-patterns      : Exclude paths matching glob patterns
--verbose               : Show detailed filtering information
--detail-level          : Output detail level (minimal/standard/full, default: standard)
                          Note: Detail level only affects output format, not filtering
--min-schools N         : Only show objects used by at least N schools
--max-schools N         : Only show objects used by at most N schools
--only-generic          : Only show objects with generic (non-school-specific) files
--only-school-specific  : Only show purely school-specific objects (no generic files)
                          Note: School count filters work consistently across all detail levels

Technical Details:
-----------------
- Recursive directory scanning using Path.rglob()
- Regex pattern matching for CREATE OR REPLACE statements
- Handles PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE, and TRIGGER
- Stores relative paths from the search directory
- Case-insensitive matching
- UTF-8 encoding with error handling for corrupted files

Author: Generated for Axium Services
Date: 2026-01-23
"""

import json
import re
import argparse
from pathlib import Path
from datetime import datetime
from fnmatch import fnmatch

def find_sql_files(directory=".", filters=None, verbose=False):
    """
    Find all SQL files in the specified directory and its subdirectories (recursive).
    Apply filtering criteria if provided.
    
    Args:
        directory: Directory to search (default: current directory)
        filters: Dictionary containing filter criteria (optional)
        verbose: If True, print detailed filtering information
    
    Returns:
        List of Path objects for SQL files (after filtering)
    """
    path = Path(directory)
    all_sql_files = list(path.rglob("*.sql"))  # rglob for recursive search
    
    # If no filters provided, return all files
    if not filters:
        return all_sql_files
    
    # Apply filters
    included_files = []
    excluded_files = []
    exclusion_reasons = {}
    
    for sql_file in all_sql_files:
        should_exclude, reason = should_exclude_file(sql_file, filters, verbose)
        
        if should_exclude:
            excluded_files.append(sql_file)
            if reason:
                exclusion_reasons[reason] = exclusion_reasons.get(reason, 0) + 1
        else:
            included_files.append(sql_file)
    
    # Print filtering summary if verbose or if files were excluded
    if excluded_files and (verbose or len(excluded_files) > 0):
        print(f"\n{'='*60}")
        print("FILTERING SUMMARY")
        print(f"{'='*60}")
        print(f"Total SQL files found:    {len(all_sql_files)}")
        print(f"Files excluded:           {len(excluded_files)}")
        print(f"Files included:           {len(included_files)}")
        
        if verbose and exclusion_reasons:
            print(f"\nExclusion reasons:")
            for reason, count in sorted(exclusion_reasons.items()):
                print(f"  - {reason}: {count} file(s)")
        print(f"{'='*60}\n")
    
    return included_files

def extract_object_name_from_file(file_path):
    """
    Extract the database object name from a SQL file.
    Looks for CREATE OR REPLACE FUNCTION/PROCEDURE/PACKAGE/PACKAGE BODY/TRIGGER statements.
    
    Args:
        file_path: Path to the SQL file
    
    Returns:
        Tuple of (object_type, object_name) or (None, None) if not found
    """
    pattern = re.compile(
        r'CREATE\s+(?:OR\s+REPLACE\s+)?(PACKAGE\s+BODY|PACKAGE|FUNCTION|PROCEDURE|TRIGGER)\s+([^\s(]+)',
        re.IGNORECASE | re.MULTILINE
    )
    
    try:
        with open(file_path, 'r', encoding='utf-8', errors='ignore') as f:
            content = f.read()
        
        match = pattern.search(content)
        if match:
            object_type = match.group(1).upper().strip()
            object_name = match.group(2).strip()
            return (object_type, object_name)
    except Exception as e:
        print(f"Warning: Could not read {file_path}: {e}")
    
    return (None, None)

def extract_school_from_path(file_path):
    """
    Extract school code from file path or filename.
    
    Args:
        file_path: Path object for the SQL file
    
    Returns:
        School code (uppercase) or None if generic/no school found
    """
    # Common non-school directory and suffix names to exclude
    excluded_names = {'SQL', 'PKG', 'BODY', 'TRG', 'FNC', 'PROC', 'DEV', 'PROD', 'TEST'}
    
    parts = file_path.parts
    
    # Check folder names for school codes (2-6 uppercase letters)
    for part in parts:
        if re.match(r'^[A-Z]{2,6}$', part) and part.upper() not in excluded_names:
            return part
    
    # Check file suffix for school codes (e.g., overnight_pkg_BUFF.sql)
    # Look for _SCHOOLCODE pattern where SCHOOLCODE is 2-6 letters
    file_name = file_path.stem  # filename without extension
    match = re.search(r'_([A-Z]{2,6})$', file_name, re.IGNORECASE)
    if match:
        school_code = match.group(1).upper()
        # Filter out common non-school suffixes
        if school_code not in excluded_names:
            return school_code
    
    return None

def should_exclude_file(file_path, filters, verbose=False):
    """
    Determine if a file should be excluded based on filtering criteria.
    
    Args:
        file_path: Path object for the SQL file
        filters: Dictionary containing filter criteria:
            - exclude_releases: List of version patterns to exclude (e.g., ['7*', '2024.01'])
            - only_releases: List of version patterns to include (whitelist mode)
            - exclude_schools: List of school codes to exclude (lowercase)
            - only_schools: List of school codes to include (whitelist mode, lowercase)
            - exclude_custom: Boolean to exclude "Custom *" directories
            - exclude_patterns: List of glob patterns to exclude
        verbose: If True, print detailed filtering information
    
    Returns:
        Tuple of (should_exclude: bool, reason: str or None)
    """
    # Early return if no filters are active
    if (not filters.get('exclude_releases') and 
        not filters.get('only_releases') and
        not filters.get('exclude_schools') and 
        not filters.get('only_schools') and
        not filters.get('exclude_custom') and 
        not filters.get('exclude_patterns')):
        return (False, None)
    
    file_path_str = str(file_path)
    file_path_lower = file_path_str.lower()
    parts = file_path.parts
    parts_lower = [p.lower() for p in parts]
    
    # Check release/version directory filtering
    exclude_releases = filters.get('exclude_releases', [])
    only_releases = filters.get('only_releases', [])
    
    if exclude_releases or only_releases:
        # Look for version-like directories in the path
        found_version = None
        for part in parts:
            # Strip "Rel" prefix if present (case-insensitive)
            part_lower = part.lower()
            if part_lower.startswith('rel'):
                version = part[3:]  # Remove "Rel" prefix
            else:
                version = part
            
            # Check if this looks like a version (contains digits and dots)
            if re.match(r'^\d+\.\d+', version):
                found_version = version
                break
        
        if found_version:
            # Whitelist mode (only_releases)
            if only_releases:
                matches_whitelist = any(fnmatch(found_version, pattern) for pattern in only_releases)
                if not matches_whitelist:
                    return (True, f"version {found_version} not in whitelist")
            
            # Blacklist mode (exclude_releases)
            if exclude_releases:
                matches_blacklist = any(fnmatch(found_version, pattern) for pattern in exclude_releases)
                if matches_blacklist:
                    return (True, f"version {found_version} excluded")
    
    # Check school code filtering
    exclude_schools = filters.get('exclude_schools', [])
    only_schools = filters.get('only_schools', [])
    
    if exclude_schools or only_schools:
        found_school = None
        
        # Check folder names for school codes
        for part_lower in parts_lower:
            if part_lower in exclude_schools or part_lower in only_schools:
                found_school = part_lower
                break
        
        # Check file suffix for school codes (e.g., overnight_pkg_BUFF.sql)
        if not found_school:
            file_name = file_path.stem  # filename without extension
            # Look for _SCHOOLCODE pattern at the end
            match = re.search(r'_([A-Z]{2,6})$', file_name, re.IGNORECASE)
            if match:
                potential_school = match.group(1).lower()
                if potential_school in exclude_schools or potential_school in only_schools:
                    found_school = potential_school
        
        if found_school:
            # Whitelist mode (only_schools)
            if only_schools:
                if found_school not in only_schools:
                    return (True, f"school {found_school} not in whitelist")
            
            # Blacklist mode (exclude_schools)
            if exclude_schools:
                if found_school in exclude_schools:
                    return (True, f"school {found_school} excluded")
    
    # Check custom directory filtering
    if filters.get('exclude_custom'):
        for part_lower in parts_lower:
            if part_lower.startswith('custom '):
                return (True, "custom directory excluded")
    
    # Check pattern-based filtering
    exclude_patterns = filters.get('exclude_patterns', [])
    if exclude_patterns:
        for pattern in exclude_patterns:
            if fnmatch(file_path_str, pattern) or fnmatch(file_path_lower, pattern.lower()):
                return (True, f"matches pattern '{pattern}'")
    
    return (False, None)

def cross_reference_sql_files(input_json, output_json, directory=".", filters=None, verbose=False, detail_level='standard'):
    """
    Cross-reference SQL files in directory with objects from the input JSON.
    Generate a new JSON with only objects that have corresponding SQL files.
    
    Args:
        input_json: Path to the JSON file from the previous script
        output_json: Path to the output JSON file
        directory: Directory to search for SQL files (default: current directory)
        filters: Dictionary containing filter criteria (optional):
                 - File/directory filters: exclude_releases, only_releases, exclude_schools, 
                   only_schools, exclude_custom, exclude_patterns
                 - Object filters: min_schools, max_schools, only_generic, only_school_specific
        detail_level: Output detail level - 'minimal', 'standard', or 'full' (default: 'standard')
        verbose: If True, print detailed filtering information
    """
    
    print(f"Loading extracted objects from: {input_json}")
    
    try:
        # Load the input JSON
        with open(input_json, 'r', encoding='utf-8') as f:
            extracted_data = json.load(f)
        
        # Get all objects from the JSON
        # Convert to lowercase for case-insensitive comparison
        all_packages = set(name.lower() for name in extracted_data['objects']['packages'])
        all_package_bodies = set(name.lower() for name in extracted_data['objects'].get('package_bodies', []))
        all_functions = set(name.lower() for name in extracted_data['objects']['functions'])
        all_procedures = set(name.lower() for name in extracted_data['objects']['procedures'])
        all_triggers = set(name.lower() for name in extracted_data['objects'].get('triggers', []))
        
        print(f"Loaded {len(all_packages)} packages, {len(all_package_bodies)} package bodies, {len(all_functions)} functions, {len(all_procedures)} procedures, {len(all_triggers)} triggers")
        
        # Find all SQL files (with filtering if specified)
        sql_files = find_sql_files(directory, filters, verbose)
        print(f"\nProcessing {len(sql_files)} SQL files")
        
        # Storage for matched objects with school information
        object_details = {}  # key: (type, name_lower), value: {name, schools: set(), generic_count: int}
        file_mappings = []
        
        # Process each SQL file
        for sql_file in sql_files:
            object_type, object_name = extract_object_name_from_file(sql_file)
            
            if object_type and object_name:
                # Check if this object exists in our extracted data (case-insensitive)
                matched = False
                object_name_lower = object_name.lower()
                
                if object_type == 'PACKAGE' and object_name_lower in all_packages:
                    matched = True
                elif object_type == 'PACKAGE BODY' and object_name_lower in all_package_bodies:
                    matched = True
                elif object_type == 'FUNCTION' and object_name_lower in all_functions:
                    matched = True
                elif object_type == 'PROCEDURE' and object_name_lower in all_procedures:
                    matched = True
                elif object_type == 'TRIGGER' and object_name_lower in all_triggers:
                    matched = True
                
                if matched:
                    # Get relative path from the search directory
                    try:
                        relative_path = sql_file.relative_to(Path(directory).resolve())
                    except ValueError:
                        relative_path = sql_file
                    
                    # Extract school code from path
                    school = extract_school_from_path(sql_file)
                    
                    # Aggregate object details
                    key = (object_type, object_name_lower)
                    if key not in object_details:
                        object_details[key] = {
                            'name': object_name,
                            'schools': set(),
                            'generic_count': 0
                        }
                    
                    if school:
                        object_details[key]['schools'].add(school)
                    else:
                        object_details[key]['generic_count'] += 1
                    
                    file_mappings.append({
                        "file": str(relative_path),
                        "object_type": object_type.lower(),
                        "object_name": object_name,
                        "school": school
                    })
                    print(f"  [OK] Matched: {relative_path} -> {object_type} {object_name}" + (f" ({school})" if school else " (generic)"))
        
        # Filter object_details based on school count criteria
        def filter_object_details(object_details, min_schools=None, max_schools=None, 
                                  only_generic=False, only_school_specific=False, verbose=False):
            """
            Filter object_details dictionary based on school count thresholds and generic/school-specific flags.
            This happens BEFORE formatting, so it works consistently across all detail levels.
            
            Args:
                object_details: Dictionary of object details with metadata
                min_schools: Minimum number of schools (inclusive), or None for no minimum
                max_schools: Maximum number of schools (inclusive), or None for no maximum
                only_generic: If True, only show objects with generic_count > 0
                only_school_specific: If True, only show objects with school_count > 0 and generic_count = 0
                verbose: If True, print exclusion details
            
            Returns:
                Filtered object_details dictionary and count of excluded objects
            """
            if min_schools is None and max_schools is None and not only_generic and not only_school_specific:
                return object_details, 0
            
            filtered = {}
            total_excluded = 0
            
            for key, details in object_details.items():
                school_count = len(details['schools'])
                generic_count = details['generic_count']
                
                # Apply filters
                include = True
                
                # School count filters
                if min_schools is not None and school_count < min_schools:
                    include = False
                if max_schools is not None and school_count > max_schools:
                    include = False
                
                # Generic/school-specific filters
                if only_generic and generic_count == 0:
                    include = False
                if only_school_specific and (school_count == 0 or generic_count > 0):
                    include = False
                
                if include:
                    filtered[key] = details
                else:
                    total_excluded += 1
                    if verbose:
                        obj_type, _ = key
                        print(f"  [EXCLUDED] {obj_type} {details['name']} (schools: {school_count}, generic: {generic_count})")
            
            return filtered, total_excluded
        
        # Build matched_objects with school information
        def build_object_list(object_details, object_type, detail_level='standard', file_mappings=None):
            """Helper to build list of objects with school info for a specific type"""
            result = []
            for (obj_type, _), details in object_details.items():
                if obj_type == object_type:
                    if detail_level == 'minimal':
                        result.append(details['name'])
                    else:
                        obj = {
                            'name': details['name'],
                            'total_files': len(details['schools']) + details['generic_count'],
                            'schools': sorted(details['schools']),
                            'school_count': len(details['schools']),
                            'generic_count': details['generic_count']
                        }
                        
                        if detail_level == 'full' and file_mappings:
                            # Add file paths for this object
                            obj['files'] = sorted([
                                fm['file'] for fm in file_mappings 
                                if fm['object_type'] == object_type.lower() 
                                and fm['object_name'].lower() == details['name'].lower()
                            ])
                        
                        result.append(obj)
            
            if detail_level == 'minimal':
                return sorted(result, key=lambda x: x.lower())
            else:
                return sorted(result, key=lambda x: x['name'].lower())
        
        # Apply school count filters BEFORE formatting (so it works in all detail levels)
        min_schools = filters.get('min_schools')
        max_schools = filters.get('max_schools')
        only_generic = filters.get('only_generic', False)
        only_school_specific = filters.get('only_school_specific', False)
        excluded_count = 0
        
        if min_schools is not None or max_schools is not None or only_generic or only_school_specific:
            if verbose:
                print(f"\n[FILTER] Applying school count filters:")
                if min_schools is not None:
                    print(f"  - Minimum schools: {min_schools}")
                if max_schools is not None:
                    print(f"  - Maximum schools: {max_schools}")
                if only_generic:
                    print(f"  - Only generic objects")
                if only_school_specific:
                    print(f"  - Only school-specific objects")
            
            object_details, excluded_count = filter_object_details(
                object_details, min_schools, max_schools, 
                only_generic, only_school_specific, verbose
            )
            
            if verbose and excluded_count > 0:
                print(f"\n[FILTER] Excluded {excluded_count} objects based on school count filters")
        
        # Build matched_objects from filtered object_details
        matched_objects = {
            "packages": build_object_list(object_details, 'PACKAGE', detail_level, file_mappings),
            "package_bodies": build_object_list(object_details, 'PACKAGE BODY', detail_level, file_mappings),
            "functions": build_object_list(object_details, 'FUNCTION', detail_level, file_mappings),
            "procedures": build_object_list(object_details, 'PROCEDURE', detail_level, file_mappings),
            "triggers": build_object_list(object_details, 'TRIGGER', detail_level, file_mappings)
        }
        
        output_data = {
            "metadata": {
                "source_json": input_json,
                "scan_directory": str(Path(directory).resolve()),
                "scan_date": datetime.now().isoformat(),
                "total_sql_files_scanned": len(sql_files),
                "total_matched_objects": len(matched_objects['packages']) + len(matched_objects['package_bodies']) + len(matched_objects['functions']) + len(matched_objects['procedures']) + len(matched_objects['triggers'])
            },
            "summary": {
                "packages": len(matched_objects['packages']),
                "package_bodies": len(matched_objects['package_bodies']),
                "functions": len(matched_objects['functions']),
                "procedures": len(matched_objects['procedures']),
                "triggers": len(matched_objects['triggers'])
            },
            "matched_objects": matched_objects,
            "file_mappings": sorted(file_mappings, key=lambda x: x['file'])
        }
        
        # Write output JSON
        with open(output_json, 'w', encoding='utf-8') as f:
            json.dump(output_data, f, indent=2, ensure_ascii=False)
        
        print(f"\n{'='*60}")
        print("CROSS-REFERENCE SUMMARY")
        print(f"{'='*60}")
        print(f"SQL files scanned:       {len(sql_files)}")
        print(f"Matched packages:        {len(matched_objects['packages'])}")
        print(f"Matched package bodies:  {len(matched_objects['package_bodies'])}")
        print(f"Matched functions:       {len(matched_objects['functions'])}")
        print(f"Matched procedures:      {len(matched_objects['procedures'])}")
        print(f"Matched triggers:        {len(matched_objects['triggers'])}")
        print(f"Total matched objects:   {len(matched_objects['packages']) + len(matched_objects['package_bodies']) + len(matched_objects['functions']) + len(matched_objects['procedures']) + len(matched_objects['triggers'])}")
        
        # Calculate school statistics across all object types (only for standard/full detail levels)
        if detail_level != 'minimal':
            total_school_files = 0
            total_generic_files = 0
            for obj_list in matched_objects.values():
                for obj in obj_list:
                    total_school_files += obj['school_count']
                    total_generic_files += obj['generic_count']
            
            print(f"\nSchool-specific files:   {total_school_files}")
            print(f"Generic files:           {total_generic_files}")
        
        print(f"\nResults written to: {output_json}")
        
    except FileNotFoundError as e:
        print(f"Error: File not found - {e}")
    except json.JSONDecodeError as e:
        print(f"Error: Invalid JSON format - {e}")
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    # Set up argument parser
    parser = argparse.ArgumentParser(
        description='Cross-reference SQL files with extracted objects from JSON.',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog='''
Examples:
  Basic usage:
    python cross_reference_sql_objects.py
    python cross_reference_sql_objects.py -i extracted_sql_objects.json
    python cross_reference_sql_objects.py -i input.json -o output.json -d ./sql_files
  
  Filtering examples:
    # Exclude all 7.x versions
    python cross_reference_sql_objects.py -d . --exclude-releases 7* --verbose
    
    # Exclude specific versions
    python cross_reference_sql_objects.py -d . --exclude-releases 7.08 2024.01 --verbose
    
    # Only include 7.x and 8.x versions (whitelist)
    python cross_reference_sql_objects.py -d . --only-releases 7* 8* --verbose
    
    # Exclude specific school codes
    python cross_reference_sql_objects.py -d Dev --exclude-schools BUFF MICH --verbose
    
    # Only include specific school codes (whitelist)
    python cross_reference_sql_objects.py -d Dev --only-schools NYU ROCH
    
    # Exclude custom directories
    python cross_reference_sql_objects.py -d . --exclude-custom --verbose
    
    # Combine multiple filters
    python cross_reference_sql_objects.py -d . --exclude-releases 7* 6* --exclude-custom --verbose
  
  Detail level examples:
    # Minimal output (just object names)
    python cross_reference_sql_objects.py --detail-level minimal
    
    # Standard output with school info (default)
    python cross_reference_sql_objects.py --detail-level standard
    
    # Full output with file paths included
    python cross_reference_sql_objects.py --detail-level full
  
  School count filtering examples:
    # Show only rarely-used objects (1-3 schools)
    python cross_reference_sql_objects.py --min-schools 1 --max-schools 3
    
    # Show only widely-adopted objects (10+ schools)
    python cross_reference_sql_objects.py --min-schools 10
    
    # Show objects used by exactly 1 school
    python cross_reference_sql_objects.py --max-schools 1
    
    # Show only generic objects (have non-school-specific versions)
    python cross_reference_sql_objects.py --only-generic
    
    # Show only school-specific objects (no generic versions)
    python cross_reference_sql_objects.py --only-school-specific
    
    # Combine with other filters
    python cross_reference_sql_objects.py --exclude-custom --min-schools 5 --detail-level full
        '''
    )
    
    parser.add_argument(
        '-i', '--input',
        default='extracted_sql_objects.json',
        help='Path to the input JSON file from extract_sql_objects.py (default: extracted_sql_objects.json)'
    )
    
    parser.add_argument(
        '-o', '--output',
        default='matched_sql_objects.json',
        help='Path to the output JSON file (default: matched_sql_objects.json)'
    )
    
    parser.add_argument(
        '-d', '--directory',
        default='.',
        help='Directory to search for SQL files recursively (default: current directory)'
    )
    
    parser.add_argument(
        '--exclude-releases',
        nargs='+',
        metavar='VERSION',
        default=[],
        help='Exclude release/version directories (e.g., --exclude-releases 7* 2024.01). Use * for wildcards: "7*" excludes all 7.x versions, "7.08" excludes only 7.08'
    )
    
    parser.add_argument(
        '--only-releases',
        nargs='+',
        metavar='VERSION',
        default=[],
        help='Only include release/version directories (e.g., --only-releases 7* 8*). Use * for wildcards'
    )
    
    parser.add_argument(
        '--exclude-schools',
        nargs='+',
        metavar='SCHOOL',
        default=[],
        help='Exclude specific school codes (e.g., --exclude-schools BUFF UCLA)'
    )
    
    parser.add_argument(
        '--only-schools',
        nargs='+',
        metavar='SCHOOL',
        default=[],
        help='Only include specific school codes (e.g., --only-schools WDE NYU)'
    )
    
    parser.add_argument(
        '--exclude-custom',
        action='store_true',
        help='Exclude all "Custom *" directories'
    )
    
    parser.add_argument(
        '--exclude-patterns',
        nargs='+',
        metavar='PATTERN',
        default=[],
        help='Exclude paths matching patterns (e.g., "*/archive/*" "*_OLD.sql")'
    )
    
    parser.add_argument(
        '--verbose',
        action='store_true',
        help='Show detailed filtering information'
    )
    
    parser.add_argument(
        '--detail-level',
        choices=['minimal', 'standard', 'full'],
        default='standard',
        help='Output detail level: minimal (name lists), standard (with school info), full (includes file paths). Default: standard'
    )
    
    parser.add_argument(
        '--min-schools',
        type=int,
        metavar='N',
        help='Only show objects used by at least N schools (e.g., --min-schools 5)'
    )
    
    parser.add_argument(
        '--max-schools',
        type=int,
        metavar='N',
        help='Only show objects used by at most N schools (e.g., --max-schools 3)'
    )
    
    parser.add_argument(
        '--only-generic',
        action='store_true',
        help='Only show objects that have generic (non-school-specific) files (generic_count > 0)'
    )
    
    parser.add_argument(
        '--only-school-specific',
        action='store_true',
        help='Only show objects that are purely school-specific (school_count > 0 and generic_count = 0)'
    )
    
    # Parse arguments
    args = parser.parse_args()
    
    # Build filters dictionary (convert school codes to lowercase for case-insensitive matching)
    filters = {
        'exclude_releases': args.exclude_releases,
        'only_releases': args.only_releases,
        'exclude_schools': [s.lower() for s in args.exclude_schools],
        'only_schools': [s.lower() for s in args.only_schools],
        'exclude_custom': args.exclude_custom,
        'exclude_patterns': args.exclude_patterns,
        'min_schools': args.min_schools,
        'max_schools': args.max_schools,
        'only_generic': args.only_generic,
        'only_school_specific': args.only_school_specific,
    }
    
    # Run cross-reference
    cross_reference_sql_files(args.input, args.output, args.directory, filters, args.verbose, args.detail_level)