"""
SQL Object Extractor
====================

This script extracts database objects (FUNCTIONS, PROCEDURES, PACKAGES, PACKAGE BODIES, and TRIGGERS)
from a SQL DDL (Data Definition Language) file and outputs them to a structured JSON file.

Purpose:
--------
- Parse large SQL DDL files to identify all database objects
- Categorize objects by type (package, package body, function, procedure)
- Generate a JSON file with organized lists of all found objects
- Provide metadata including extraction timestamp and object counts

Database Object Types:
---------------------
1. PACKAGE: Package specification (interface/declarations)
2. PACKAGE BODY: Package implementation (actual code)
3. FUNCTION: Standalone function definitions
4. PROCEDURE: Standalone procedure definitions
5. TRIGGER: Database triggers (event-driven code)

Output Format:
-------------
The script generates a JSON file with the following structure:
{
  "metadata": {
    "source_file": "path/to/ddl.sql",
    "extraction_date": "ISO timestamp",
    "total_objects": 276
  },
  "summary": {
    "packages": 61,
    "package_bodies": 60,
    "functions": 88,
    "procedures": 67,
    "triggers": 220
  },
  "objects": {
    "packages": ["pkg1", "pkg2", ...],
    "package_bodies": ["pkg1", "pkg2", ...],
    "functions": ["func1", "func2", ...],
    "procedures": ["proc1", "proc2", ...],
    "triggers": ["trg1", "trg2", ...]
  }
}

Usage:
------
Basic usage with defaults:
    python extract_sql_objects.py

With custom input file:
    python extract_sql_objects.py -i path/to/ddl.sql

With custom input and output:
    python extract_sql_objects.py -i ddl.sql -o output.json

Command-line Arguments:
----------------------
-i, --input   : Path to the SQL DDL file (default: SQL-Golden-Axium-DDL.sql)
-o, --output  : Path to the output JSON file (default: extracted_sql_objects.json)

Technical Details:
-----------------
- Uses regex pattern matching to identify CREATE OR REPLACE statements
- Handles both Oracle and PostgreSQL syntax
- Properly distinguishes between PACKAGE and PACKAGE BODY
- Removes duplicates and sorts results alphabetically
- UTF-8 encoding support for international characters

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

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

def extract_sql_objects(input_file, output_file):
    """
    Extract all FUNCTIONS, PROCEDURES, and PACKAGES from a SQL DDL file.
    Output as JSON for easy consumption by other scripts.
    
    Args:
        input_file: Path to the SQL DDL file
        output_file: Path to the output JSON file
    """
    
    # Regular expression to match CREATE OR REPLACE statements
    # Handles PACKAGE BODY separately to extract the correct name
    # Also handles CREATE TRIGGER (which may not have OR REPLACE)
    pattern = re.compile(
        r'^CREATE\s+(?:OR\s+REPLACE\s+)?(PACKAGE\s+BODY|PACKAGE|FUNCTION|PROCEDURE|TRIGGER)\s+([^\s(]+)',
        re.IGNORECASE | re.MULTILINE
    )
    
    # Storage for extracted objects
    packages = []
    package_bodies = []
    functions = []
    procedures = []
    triggers = []
    
    print(f"Reading SQL file: {input_file}")
    
    try:
        with open(input_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # Find all matches
        matches = pattern.finditer(content)
        
        for match in matches:
            object_type = match.group(1).upper().strip()
            object_name = match.group(2).strip()
            
            # Categorize by type
            if object_type == 'PACKAGE BODY':
                package_bodies.append(object_name)
            elif object_type == 'PACKAGE':
                packages.append(object_name)
            elif object_type == 'FUNCTION':
                functions.append(object_name)
            elif object_type == 'PROCEDURE':
                procedures.append(object_name)
            elif object_type == 'TRIGGER':
                triggers.append(object_name)
        
        # Create JSON structure
        output_data = {
            "metadata": {
                "source_file": input_file,
                "extraction_date": datetime.now().isoformat(),
                "total_objects": len(packages) + len(package_bodies) + len(functions) + len(procedures) + len(triggers)
            },
            "summary": {
                "packages": len(packages),
                "package_bodies": len(package_bodies),
                "functions": len(functions),
                "procedures": len(procedures),
                "triggers": len(triggers)
            },
            "objects": {
                "packages": sorted(set(packages)),
                "package_bodies": sorted(set(package_bodies)),
                "functions": sorted(set(functions)),
                "procedures": sorted(set(procedures)),
                "triggers": sorted(set(triggers))
            }
        }
        
        # Write JSON to output file
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(output_data, f, indent=2, ensure_ascii=False)
        
        print(f"\nExtraction complete!")
        print(f"  Packages:        {len(packages)}")
        print(f"  Package Bodies:  {len(package_bodies)}")
        print(f"  Functions:       {len(functions)}")
        print(f"  Procedures:      {len(procedures)}")
        print(f"  Triggers:        {len(triggers)}")
        print(f"  Total:           {len(packages) + len(package_bodies) + len(functions) + len(procedures) + len(triggers)}")
        print(f"\nResults written to: {output_file}")
        
    except FileNotFoundError:
        print(f"Error: File '{input_file}' not found.")
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    # Set up argument parser
    parser = argparse.ArgumentParser(
        description='Extract FUNCTIONS, PROCEDURES, PACKAGES, and TRIGGERS from a SQL DDL file to JSON.',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog='''
Examples:
  python extract_sql_objects.py
  python extract_sql_objects.py -i path/to/ddl.sql
  python extract_sql_objects.py -i ddl.sql -o output.json
        '''
    )
    
    parser.add_argument(
        '-i', '--input',
        default='SQL-Golden-Axium-DDL.ddl',
        help='Path to the SQL DDL file (default: SQL-Golden-Axium-DDL.ddl)'
    )
    
    parser.add_argument(
        '-o', '--output',
        default='extracted_sql_objects.json',
        help='Path to the output JSON file (default: extracted_sql_objects.json)'
    )
    
    # Parse arguments
    args = parser.parse_args()
    
    # Run extraction
    extract_sql_objects(args.input, args.output)