"""
Management command to remove indicator species records for codes that don't exist
in the gwynedd_nrw_ph1_intertidal_biotope table.
Usage: python manage.py clean_indicator_species
"""
from django.core.management.base import BaseCommand
from habitats_and_biotopes.models import IndicatorSpecies, GwyneddNrwPh1IntertidalBiotope


class Command(BaseCommand):
    help = 'Remove indicator species records for codes not in the biotope table'

    def handle(self, *args, **options):
        # 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')
        
        # Count records that will be deleted
        invalid_records = IndicatorSpecies.objects.exclude(f2004_code__in=valid_codes)
        count_before = invalid_records.count()
        
        if count_before == 0:
            self.stdout.write(
                self.style.SUCCESS('No invalid indicator species records found. All records have valid codes.')
            )
            return
        
        # Get some example invalid codes for reporting
        invalid_codes = invalid_records.values_list('f2004_code', flat=True).distinct()[:10]
        
        self.stdout.write(
            self.style.WARNING(
                f'Found {count_before} indicator species records with codes not in biotope table'
            )
        )
        if invalid_codes:
            self.stdout.write(f'Example invalid codes: {", ".join(invalid_codes)}')
        
        # Delete the invalid records
        deleted_count = invalid_records.delete()[0]
        
        self.stdout.write(
            self.style.SUCCESS(
                f'Successfully deleted {deleted_count} indicator species records'
            )
        )
        
        # Report remaining count
        remaining_count = IndicatorSpecies.objects.count()
        self.stdout.write(f'Remaining indicator species records: {remaining_count}')