#!/usr/bin/env python3
"""
Parse PostgreSQL dump and generate Laravel seeder data arrays.
VERSION 2: Handles multi-line INSERT statements
Usage: python3 parse_sql_to_seeder_v2.py /tmp/v1-data.sql
"""

import re
import sys

def parse_multiline_inserts(content, table_name):
    """Parse multi-line INSERT statements from SQL content."""
    # Pattern to match complete INSERT statements (including multi-line)
    pattern = rf"INSERT INTO public\.{table_name}[^;]+VALUES\s*\((.*?)\);"
    matches = re.findall(pattern, content, re.DOTALL)
    return matches

def parse_values(values_str):
    """Parse VALUES clause into a list of cleaned values."""
    values = []
    current = ''
    in_quote = False
    escape_next = False
    paren_depth = 0
    
    for char in values_str:
        if escape_next:
            current += char
            escape_next = False
            continue
            
        if char == '\\':
            escape_next = True
            current += char
            continue
            
        if char == "'":
            in_quote = not in_quote
            current += char
            continue
        
        if not in_quote:
            if char == '(':
                paren_depth += 1
                current += char
                continue
            elif char == ')':
                paren_depth -= 1
                current += char
                continue
            elif char == ',' and paren_depth == 0:
                values.append(current.strip())
                current = ''
                continue
        
        current += char
    
    if current.strip():
        values.append(current.strip())
    
    # Clean up values
    cleaned_values = []
    for v in values:
        v = v.strip()
        if v == 'NULL':
            cleaned_values.append(None)
        elif v.startswith("'") and v.endswith("'"):
            # Remove quotes and unescape
            v = v[1:-1]
            v = v.replace("''", "'")  # PostgreSQL escapes single quotes by doubling them
            v = v.replace("\\n", "\n")  # Handle newlines
            v = v.replace("\\r", "\r")
            v = v.replace("\\t", "\t")
            v = v.replace("\\\\", "\\")
            cleaned_values.append(v)
        elif v.lower() == 'true':
            cleaned_values.append(True)
        elif v.lower() == 'false':
            cleaned_values.append(False)
        else:
            # Try to parse as number
            try:
                if '.' in v:
                    cleaned_values.append(float(v))
                else:
                    cleaned_values.append(int(v))
            except ValueError:
                cleaned_values.append(v)
    
    return cleaned_values

def values_to_php_array(values, schema):
    """Convert values list to PHP array string."""
    php_parts = []
    for key, value in zip(schema, values):
        if value is None:
            php_value = 'null'
        elif isinstance(value, bool):
            php_value = 'true' if value else 'false'
        elif isinstance(value, (int, float)):
            php_value = str(value)
        else:
            # Escape for PHP string
            value = str(value).replace('\\', '\\\\').replace("'", "\\'")
            php_value = f"'{value}'"
        php_parts.append(f"'{key}' => {php_value}")
    
    return '[' + ', '.join(php_parts) + ']'

def main():
    if len(sys.argv) != 2:
        print("Usage: python3 parse_sql_to_seeder_v2.py /tmp/v1-data.sql")
        sys.exit(1)
    
    sql_file = sys.argv[1]
    
    # Define schemas
    docket_schema = ['id', 'date', 'summary', 'created_at', 'updated_at', 'notes', 'title']
    document_schema = ['id', 'docket_entry_id', 'original_filename', 'stored_filename', 'file_path', 
                       'title', 'summary', 'notes', 'file_size', 'display_order', 'created_at', 
                       'updated_at', 'mime_type']
    subscription_schema = ['id', 'email', 'is_active', 'unsubscribe_token', 'created_at']
    
    print("Reading SQL dump...")
    with open(sql_file, 'r') as f:
        content = f.read()
    
    print("Parsing multi-line INSERT statements...")
    
    # Parse each table
    docket_matches = parse_multiline_inserts(content, 'docket_entries')
    document_matches = parse_multiline_inserts(content, 'documents')
    subscription_matches = parse_multiline_inserts(content, 'subscriptions')
    
    print(f"\nFound:")
    print(f"  - {len(docket_matches)} docket entry INSERT statements")
    print(f"  - {len(document_matches)} document INSERT statements")
    print(f"  - {len(subscription_matches)} subscription INSERT statements")
    
    # Parse values
    entries = []
    for match in docket_matches:
        values = parse_values(match)
        if len(values) == len(docket_schema):
            entries.append(values_to_php_array(values, docket_schema))
        else:
            print(f"Warning: Skipping docket entry with {len(values)} values (expected {len(docket_schema)})")
    
    documents = []
    for match in document_matches:
        values = parse_values(match)
        if len(values) == len(document_schema):
            documents.append(values_to_php_array(values, document_schema))
        else:
            print(f"Warning: Skipping document with {len(values)} values (expected {len(document_schema)})")
    
    subscriptions = []
    for match in subscription_matches:
        values = parse_values(match)
        if len(values) == len(subscription_schema):
            subscriptions.append(values_to_php_array(values, subscription_schema))
        else:
            print(f"Warning: Skipping subscription with {len(values)} values (expected {len(subscription_schema)})")
    
    print(f"\nParsed successfully:")
    print(f"  - {len(entries)} docket entries")
    print(f"  - {len(documents)} documents")
    print(f"  - {len(subscriptions)} subscriptions")
    
    # Generate PHP code
    print("\n" + "="*80)
    print("DOCKET ENTRIES ARRAY:")
    print("="*80)
    print("$entries = [")
    for entry in entries:
        print(f"    {entry},")
    print("];")
    
    print("\n" + "="*80)
    print("DOCUMENTS ARRAY:")
    print("="*80)
    print("$documents = [")
    for doc in documents:
        print(f"    {doc},")
    print("];")
    
    print("\n" + "="*80)
    print("SUBSCRIPTIONS ARRAY:")
    print("="*80)
    print("$subscriptions = [")
    for sub in subscriptions:
        print(f"    {sub},")
    print("];")
    
    print("\n✅ Done! Copy the arrays above into V1DataMigrationSeeder.php")

if __name__ == '__main__':
    main()