"""
Management command to convert all Event geometries from British National Grid (EPSG:27700) 
to WGS84 (EPSG:4326).

This should be run once to migrate existing data. Future imports should convert geometries
to WGS84 during the import process.
"""

from django.core.management.base import BaseCommand
from django.db import connection
from eventhub.models import Event


class Command(BaseCommand):
    help = 'Convert all Event geometries from BNG (EPSG:27700) to WGS84 (EPSG:4326)'

    def add_arguments(self, parser):
        parser.add_argument(
            '--dry-run',
            action='store_true',
            help='Show what would be converted without actually converting',
        )
        parser.add_argument(
            '--batch-size',
            type=int,
            default=100,
            help='Number of events to process in each batch (default: 100)',
        )

    def handle(self, *args, **options):
        dry_run = options['dry_run']
        batch_size = options['batch_size']
        
        # Get all events with geometry
        events_with_geom = Event.objects.exclude(footprintWKT__isnull=True).exclude(footprintWKT='')
        total_count = events_with_geom.count()
        
        self.stdout.write(f'Found {total_count} events with geometry to convert.')
        
        if dry_run:
            self.stdout.write(self.style.WARNING('DRY RUN MODE - No changes will be made'))
        
        if total_count == 0:
            self.stdout.write(self.style.SUCCESS('No events with geometry found. Nothing to do.'))
            return
        
        # Process in batches
        converted_count = 0
        error_count = 0
        
        for i in range(0, total_count, batch_size):
            batch = events_with_geom[i:i+batch_size]
            
            for event in batch:
                try:
                    # Transform geometry from BNG to WGS84
                    with connection.cursor() as cursor:
                        cursor.execute("""
                            SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_GeomFromText(%s), 27700), 4326)) as wgs84_wkt
                        """, [event.footprintWKT])
                        result = cursor.fetchone()
                        
                        if result and result[0]:
                            transformed_wkt = result[0]
                            
                            if not dry_run:
                                event.footprintWKT = transformed_wkt
                                event.save(update_fields=['footprintWKT'])
                            
                            converted_count += 1
                            
                            if converted_count % 10 == 0:
                                self.stdout.write(f'Converted {converted_count}/{total_count} events...')
                        else:
                            self.stdout.write(
                                self.style.WARNING(f'Warning: Could not transform geometry for event {event.id} ({event.name})')
                            )
                            error_count += 1
                            
                except Exception as e:
                    self.stdout.write(
                        self.style.ERROR(f'Error converting event {event.id} ({event.name}): {e}')
                    )
                    error_count += 1
        
        # Summary
        self.stdout.write('')
        self.stdout.write('=' * 50)
        if dry_run:
            self.stdout.write(self.style.SUCCESS(
                f'DRY RUN: Would convert {converted_count} events'
            ))
        else:
            self.stdout.write(self.style.SUCCESS(
                f'Successfully converted {converted_count} events'
            ))
        
        if error_count > 0:
            self.stdout.write(self.style.WARNING(
                f'Errors encountered: {error_count} events'
            ))
        
        self.stdout.write('=' * 50)