import os
import pandas as pd
from django.core.management.base import BaseCommand
from django.conf import settings
from orders.models import DeliveryMatrix

BATCH_SIZE = 1000


def safe_str(value):
    if pd.isna(value):
        return ""
    return str(value).strip()


def safe_int(value):
    if pd.isna(value) or value in ["#N/A", "N/A", ""]:
        return None
    try:
        return int(value)
    except:
        return None


class Command(BaseCommand):
    help = "Import Delivery Matrix Excel"

    def handle(self, *args, **kwargs):

        # ✅ SERVER-SAFE PATH (change if needed)
        file_path = os.path.join(
            settings.BASE_DIR,
            "orders",
            "Cochin_TAT_Zone_Matrix.xlsx"
        )

        self.stdout.write(f"📂 Reading file: {file_path}")

        # Read Excel
        df = pd.read_excel(file_path)

        # Clean column names
        df.columns = df.columns.str.strip()

        # Replace common invalid values
        df.replace(["#N/A", "N/A"], pd.NA, inplace=True)

        total_rows = len(df)
        self.stdout.write(f"📊 Total rows found: {total_rows}")

        objects = []
        success_count = 0
        error_count = 0

        for i, row in df.iterrows():
            try:
                obj = DeliveryMatrix(
                    origin=safe_str(row.get('Origin')),
                    pincode=safe_int(row.get('pincode')),
                    destination_city=safe_str(row.get('D city')),
                    zone=safe_str(row.get('Zones')),
                    tat_surface=safe_int(row.get('TAT Surface')),
                    tat_express=safe_int(row.get('TAT Express')),
                )

                # Skip invalid rows (important)
                if obj.pincode is None:
                    error_count += 1
                    self.stdout.write(f"⚠️ Skipping row {i} (invalid pincode)")
                    continue

                objects.append(obj)
                success_count += 1

                # Bulk insert in batches
                if len(objects) >= BATCH_SIZE:
                    DeliveryMatrix.objects.bulk_create(
                        objects,
                        ignore_conflicts=True
                    )
                    objects.clear()
                    self.stdout.write(f"✅ Inserted {success_count} rows so far")

            except Exception as e:
                error_count += 1
                self.stdout.write(f"❌ Error at row {i}: {e}")

        # Insert remaining records
        if objects:
            DeliveryMatrix.objects.bulk_create(
                objects,
                ignore_conflicts=True
            )

        self.stdout.write(self.style.SUCCESS(
            f"🚀 Import completed! ✅ Success: {success_count} | ❌ Errors: {error_count}"))