#!/usr/bin/env python3
"""
Parse PostgreSQL dump and generate Laravel seeder data arrays.
Usage: python3 parse_sql_to_seeder.py /tmp/v1-data.sql
"""

import re
import sys
import json

def parse_insert_statement(line, table_name):
    """Parse a PostgreSQL INSERT statement into a Python dict."""
    # Extract VALUES clause
    match = re.search(r'VALUES \((.*)\);', line)
    if not match:
        return None
    
    values_str = match.group(1)
    
    # Split by comma, but respect quoted strings
    values = []
    current = ''
    in_quote = False
    escape_next = False
    
    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 char == ',' and not in_quote:
            values.append(current.strip())
            current = ''
            continue
            
        current += char
    
    if current:
        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("\\\\", "\\")
            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.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']
    
    entries = []
    documents = []
    subscriptions = []
    
    print("Parsing SQL dump...")
    
    with open(sql_file, 'r') as f:
        for line in f:
            line = line.strip()
            
            if line.startswith('INSERT INTO public.docket_entries'):
                values = parse_insert_statement(line, 'docket_entries')
                if values and len(values) == len(docket_schema):
                    entries.append(values_to_php_array(values, docket_schema))
            
            elif line.startswith('INSERT INTO public.documents'):
                values = parse_insert_statement(line, 'documents')
                if values and len(values) == len(document_schema):
                    documents.append(values_to_php_array(values, document_schema))
            
            elif line.startswith('INSERT INTO public.subscriptions'):
                values = parse_insert_statement(line, 'subscriptions')
                if values and len(values) == len(subscription_schema):
                    subscriptions.append(values_to_php_array(values, subscription_schema))
    
    print(f"\nParsed:")
    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()