"""
For reference only. GwyneddNrwPh1IntertidalBiotope is now empty

This was created before it was realised that 
1. all that is needed is the biotope code and species name. 
2. moving to WFS meant GwyneddNrwPh1IntertidalBiotope was not needed hence truncated to reduce data size
3. Instead of Extended Species Table-Table 1, could perhaps read codes from Littoral Rock-Table 1 and Littoral Sediment-Table 1

It loaded all the data from the CSV file into the database.
Management command to load indicator species from Extended Species Table CSV file.
Only loads species for codes that exist in the gwynedd_nrw_ph1_intertidal_biotope table.
Usage: python manage.py load_indicator_species /path/to/csv/file.csv
"""
import csv
from django.core.management.base import BaseCommand, CommandError
from habitats_and_biotopes.models import IndicatorSpecies, GwyneddNrwPh1IntertidalBiotope


class Command(BaseCommand):
    help = 'Load indicator species from Extended Species Table CSV file'

    def add_arguments(self, parser):
        parser.add_argument('csv_file', type=str, help='Path to the CSV file')

    def handle(self, *args, **options):
        csv_file = options['csv_file']
        
        try:
            with open(csv_file, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)
                
                # Clear all existing data (will reload from CSV)
                IndicatorSpecies.objects.all().delete()
                self.stdout.write(self.style.WARNING('Cleared existing indicator species'))
                
                # Get all valid f2004_codes from the biotope table
                valid_codes = set(
                    GwyneddNrwPh1IntertidalBiotope.objects.exclude(
                        f2004_code__isnull=True
                    ).exclude(
                        f2004_code=''
                    ).values_list('f2004_code', flat=True).distinct()
                )
                
                self.stdout.write(f'Found {len(valid_codes)} valid biotope codes in gwynedd_nrw_ph1_intertidal_biotope table')
                
                created_count = 0
                skipped_count = 0
                skipped_invalid_code = 0
                
                for row in reader:
                    # Get biotope code (f2004_code)
                    biotope_code = row.get('Biotope Code', '').strip()
                    if not biotope_code:
                        skipped_count += 1
                        continue
                    
                    # Check if this code exists in the biotope table
                    if biotope_code not in valid_codes:
                        skipped_invalid_code += 1
                        continue
                    
                    # Get species name
                    species_name = row.get('Species Name', '').strip()
                    if not species_name:
                        skipped_count += 1
                        continue
                    
                    # Create the indicator species record
                    indicator_species = IndicatorSpecies.objects.create(
                        f2004_code=biotope_code,
                        species_name=species_name,
                    )
                    
                    created_count += 1
                    
                    if created_count % 1000 == 0:
                        self.stdout.write(f'Processed {created_count} records...')
                
                self.stdout.write(
                    self.style.SUCCESS(
                        f'Successfully loaded {created_count} indicator species records'
                    )
                )
                if skipped_count > 0:
                    self.stdout.write(f'Skipped {skipped_count} rows with missing data')
                if skipped_invalid_code > 0:
                    self.stdout.write(
                        self.style.WARNING(
                            f'Skipped {skipped_invalid_code} rows with codes not in biotope table'
                        )
                    )
                
        except FileNotFoundError:
            raise CommandError(f'CSV file not found: {csv_file}')
        except Exception as e:
            raise CommandError(f'Error loading CSV: {str(e)}')