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, required=True)
        parser.add_argument(
            '--admin-email',
            type=str,
            default='admin@gmail.com'
        )

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

        try:
            admin_user = Users.objects.get(email=admin_email)
        except Users.DoesNotExist:
            self.stdout.write(self.style.ERROR(f'Admin user {admin_email} not found'))
            return

        df = pd.read_excel(excel_file_path)
        df.columns = df.columns.str.strip().str.lower()

        self.validate_columns(df.columns)
        self.import_data(df, admin_user)

    # ------------------------------------------------------------------

    def validate_columns(self, columns):
        required_columns = [
            'unit_name', 'unit_code', 'unit_location', 'street', 'city',
            'district', 'state_or_province', 'pincode', 'contact number',
            'email', 'status(close, open, currently not accepting any order)',
            'delivery_mode(shop own/ third party)',
            'gst', 'delivery_radius',
            'account_name', 'bank_name', 'branch_name',
            'ifsc_code', 'account_number'
        ]

        missing = [c for c in required_columns if c not in columns]
        if missing:
            raise Exception(f'Missing columns: {missing}')

    # ------------------------------------------------------------------

    def import_data(self, df, admin_user):
        created, updated, errors = 0, 0, []

        with transaction.atomic():
            for idx, row in df.iterrows():
                try:
                    record = row.to_dict()

                    shop, is_created = self.create_or_update_shop(record, admin_user)
                    self.create_or_update_bank_details(shop, record)

                    created += int(is_created)
                    updated += int(not is_created)

                except Exception as e:
                    errors.append(f'Row {idx+2}: {e}')

        self.stdout.write(self.style.SUCCESS(
            f'✅ Created: {created} | 📝 Updated: {updated} | ❌ Errors: {len(errors)}'
        ))

        for err in errors:
            self.stdout.write(self.style.ERROR(err))

    # ------------------------------------------------------------------

    def create_or_update_shop(self, r, admin_user):

        status_map = {
            'open': 'Open',
            'close': 'Close',
            'currently not accepting any order': 'Currently not accepting any order'
        }

        delivery_map = {
            'shop own': 'Shop own',
            'third party': 'Third party'
        }

        # Clean values
        pin_code = int(r['pincode']) if pd.notna(r['pincode']) else None
        delivery_radius = float(r['delivery_radius']) if pd.notna(r['delivery_radius']) else 0.0

        shop, created = Shop.objects.update_or_create(
            unit_code=r['unit_code'],
            defaults={
                'unit_name': r['unit_name'],
                'unit_location': r['unit_location'],
                'street': r.get('street'),
                'city': r.get('city'),
                'district': r.get('district'),
                'state_or_province': r.get('state_or_province'),
                'pin_code': pin_code,
                'latitude': 0.0,
                'longitude': 0.0,
                'contact_no': r.get('contact number'),
                'email': r.get('email'),
                'status': status_map.get(
                    str(r['status(close, open, currently not accepting any order)']).lower(),
                    'Open'
                ),
                'delivery_mode': delivery_map.get(
                    str(r['delivery_mode(shop own/ third party)']).lower(),
                    'Shop own'
                ),
                'gst': r.get('gst'),
                'delivery_radius': delivery_radius,
            }
        )

        shop.unit_admin_user.add(admin_user)
        return shop, created

    # ------------------------------------------------------------------

    def create_or_update_bank_details(self, shop, r):
        BankDetails.objects.update_or_create(
            shop=shop,
            defaults={
                'account_name': r.get('account_name'),
                'bank_name': r.get('bank_name'),
                'branch_name': r.get('branch_name'),
                'ifsc_code': r.get('ifsc_code'),
                'account_number': r.get('account_number'),
            }
        )