import pandas as pd
from django.core.management.base import BaseCommand
from django.db import transaction
from shops.models import Shop, BankDetails
from accounts.models import Users

class Command(BaseCommand):
    help = 'Import shop data from Excel file'

    def add_arguments(self, parser):
        parser.add_argument(
            '--file',
            type=str,
            help='Path to Excel file',
            required=True
        )
        parser.add_argument(
            '--admin-email',
            type=str,
            default='admin@gmail.com',
            help='Admin user email (default: admin@gmail.com)'
        )

    def handle(self, *args, **options):
        excel_file_path = options['file']
        admin_email = options['admin_email']

        self.stdout.write(self.style.SUCCESS(f'🚀 Starting data import from {excel_file_path}...'))

        # Check if admin user exists
        try:
            admin_user = Users.objects.get(email=admin_email)
            self.stdout.write(self.style.SUCCESS(f'✅ Found admin user: {admin_user.email}'))
        except Users.DoesNotExist:
            self.stdout.write(self.style.ERROR(f'❌ Admin user {admin_email} not found'))
            return

        # Validate and import data
        if self.validate_excel_columns(excel_file_path):
            self.import_shop_data(excel_file_path, admin_user)
        else:
            self.stdout.write(self.style.ERROR('❌ Excel file validation failed'))

    def validate_excel_columns(self, excel_file_path):
        """Validate Excel file columns"""
        try:
            df = pd.read_excel(excel_file_path)
            df.columns = df.columns.str.strip().str.lower()
            
            required_columns = [
                'unit_name', 'unit_code', 'unit_location', 'street', 'city',
                'district', 'state_or_province', 'pin_code', 'contact_no', 'email',
                'status', 'delivery_mode', 'gst', 'delivery_radius', 
                'account_name', 'bank_name', 'branch_name', 'ifsc_code', 'account_number'
            ]
            
            actual_columns = [col.strip().lower() for col in df.columns]
            missing_columns = [col for col in required_columns if col not in actual_columns]
            
            if missing_columns:
                self.stdout.write(self.style.ERROR(f'Missing columns: {missing_columns}'))
                return False
            
            self.stdout.write(self.style.SUCCESS('✅ All required columns present'))
            return True
            
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Error reading Excel: {str(e)}'))
            return False

    def import_shop_data(self, excel_file_path, admin_user):
        """Import shop data with transaction safety"""
        try:
            df = pd.read_excel(excel_file_path)
            df.columns = df.columns.str.strip().str.lower()
            
            created_count = 0
            updated_count = 0
            errors = []

            # Use transaction for data safety
            with transaction.atomic():
                for index, row in df.iterrows():
                    try:
                        record = row.to_dict()
                        record = {k: v if pd.notna(v) else '' for k, v in record.items()}
                        
                        # Data processing logic
                        shop = self.create_or_update_shop(record, admin_user)
                        self.create_or_update_bank_details(shop, record)
                        
                        if shop._state.adding:
                            created_count += 1
                            self.stdout.write(f'✅ Created: {shop.unit_name}')
                        else:
                            updated_count += 1
                            self.stdout.write(f'📝 Updated: {shop.unit_name}')
                            
                    except Exception as e:
                        errors.append({'record': record.get('unit_code', f'Row {index+2}'), 'error': str(e)})
                        self.stdout.write(self.style.WARNING(f'⚠️ Skipped {record.get("unit_code")}: {str(e)}'))
                        # Continue with next record instead of failing entire import

            # Print summary
            self.stdout.write(self.style.SUCCESS(f'\n📊 IMPORT SUMMARY'))
            self.stdout.write(f'✅ Created: {created_count}')
            self.stdout.write(f'📝 Updated: {updated_count}')
            self.stdout.write(f'❌ Errors: {len(errors)}')
            
            if errors:
                self.stdout.write(self.style.ERROR('\n❌ ERRORS:'))
                for error in errors:
                    self.stdout.write(f'   {error["record"]}: {error["error"]}')

        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Import failed: {str(e)}'))

    def create_or_update_shop(self, record, admin_user):
        """Create or update shop instance"""
        status_map = {'Open': 'Open', 'Close': 'Close'}
        delivery_mode_map = {'SHOP OWN': 'Shop own', 'Third party': 'Third party'}

        # Handle pin_code
        pin_code = record.get('pin_code')
        if pin_code:
            try:
                pin_code = int(float(pin_code))
            except (ValueError, TypeError):
                pin_code = None

        # Handle delivery_radius
        delivery_radius = record.get('delivery_radius', 0.0)
        try:
            delivery_radius = float(delivery_radius)
        except (ValueError, TypeError):
            delivery_radius = 0.0

        shop, created = Shop.objects.update_or_create(
            unit_code=record['unit_code'],
            defaults={
                'unit_name': record.get('unit_name', ''),
                'unit_location': record.get('unit_location', ''),
                'street': record.get('street', ''),
                'city': record.get('city', ''),
                'district': record.get('district', ''),
                'state_or_province': record.get('state_or_province', ''),
                'pin_code': pin_code,
                'latitude': 0.0,
                'longitude': 0.0,
                'contact_no': record.get('contact_no', ''),
                'email': record.get('email', ''),
                'status': status_map.get(record.get('status', 'Open'), 'Open'),
                'delivery_mode': delivery_mode_map.get(record.get('delivery_mode', 'SHOP OWN'), 'Shop own'),
                'gst': record.get('gst', ''),
                'delivery_radius': delivery_radius,
            }
        )

        # Add admin user
        shop.unit_admin_user.add(admin_user)
        return shop

    def create_or_update_bank_details(self, shop, record):
        """Create or update bank details"""
        BankDetails.objects.update_or_create(
            shop=shop,
            defaults={
                'account_name': record.get('account_name', ''),
                'bank_name': record.get('bank_name', ''),
                'branch_name': record.get('branch_name', ''),
                'ifsc_code': record.get('ifsc_code', ''),
                'account_number': record.get('account_number', ''),
            }
        )