import pandas as pd
from django.core.management.base import BaseCommand
from django.db import transaction
from products.models import ProductCategory, ProductSubCategory, Products, SKU


class Command(BaseCommand):
    help = "Import Products & SKUs from Excel"

    def add_arguments(self, parser):
        parser.add_argument("file_path", type=str, help="Path to Excel file")

    def v(self, row, col, default=None):
        return row[col] if col in row and pd.notna(row[col]) else default

    def normalize_unit(self, raw):
        """Map Excel unit strings to model-accepted lowercase values."""
        mapping = {
            "gm": "gm", "g": "gm", "gram": "gm", "grams": "gm",
            "kg": "kg", "kgs": "kg", "kilogram": "kg",
            "ml": "ml", "milliliter": "ml",
            "ltr": "ltr", "l": "ltr", "liter": "ltr",
            "nos": "nos", "pcs": "nos", "piece": "nos", "pieces": "nos",
        }
        return mapping.get(str(raw).strip().lower(), "unit")

    def normalize_status(self, raw):
        mapping = {
            "visible": "Visible",
            "disabled": "Disabled",
            "out of stock": "Out of Stock",
        }
        return mapping.get(str(raw).strip().lower(), "Visible")

    def handle(self, *args, **kwargs):
        file_path = kwargs["file_path"]

        # Excel row 1 is the header (header=0)
        df = pd.read_excel(file_path, header=0)
        df.columns = df.columns.str.strip()

        product_count = 0
        sku_count = 0
        errors = 0
        products_cache = {}

        with transaction.atomic():
            for index, row in df.iterrows():
                row_no = index + 2  # +2 because header is row 1

                try:
                    # ── CATEGORY ──────────────────────────────────────────
                    category_name = self.v(row, "Category Name")
                    category_code = self.v(row, "Category Code")
                    if not category_name or not category_code:
                        raise ValueError("Category Name or Category Code missing")

                    category, _ = ProductCategory.objects.get_or_create(
                        category_code=str(category_code).strip(),
                        defaults={
                            "category_name": str(category_name).strip(),
                            "long_distance_availability": False,
                        }
                    )

                    # ── SUB-CATEGORY ───────────────────────────────────────
                    sub_name = self.v(row, "Sub-category Name")
                    sub_code = self.v(row, "Sub Category Code")
                    if not sub_name or not sub_code:
                        raise ValueError("Sub-category Name or Sub Category Code missing")

                    sub_category, _ = ProductSubCategory.objects.get_or_create(
                        sub_category_code=str(sub_code).strip(),
                        defaults={
                            "sub_category_name": str(sub_name).strip(),
                            "category": category,
                            "long_distance_availability": False,
                        }
                    )

                    # ── PRODUCT ────────────────────────────────────────────
                    item_code = str(self.v(row, "Product Code", "")).strip()
                    if not item_code or item_code.lower() in ("nan", "none", ""):
                        raise ValueError("Product Code missing")

                    if item_code not in products_cache:
                        product, created = Products.objects.get_or_create(
                            item_code=item_code,
                            defaults={
                                "product_type": "Master Product",
                                "item_name": str(self.v(row, "Product Name", "")).strip(),
                                "item_category": category,
                                "item_sub_category": sub_category,
                                "item_description": str(self.v(row, "Description", "")),
                                "veg_or_non_veg_status": str(self.v(row, "Veg / Non Veg", "Veg")).strip().title(),
                                "i_gst": float(self.v(row, "IGST (%)", 0) or 0),
                                "s_gst": float(self.v(row, "SGST (%)", 0) or 0),
                                "c_gst": float(self.v(row, "CGST (%)", 0) or 0),
                                "cess": float(self.v(row, "CESS (%)", 0) or 0),
                                "long_distance_availability": False,
                            }
                        )
                        products_cache[item_code] = product
                        if created:
                            product_count += 1
                    else:
                        product = products_cache[item_code]

                    # ── SKU ────────────────────────────────────────────────
                    sku_code = self.v(row, "SKU Code")
                    if not sku_code:
                        raise ValueError("SKU Code missing")

                    raw_unit = self.v(row, "SKU Unit (e.g., gm/ml/piece)", "")
                    sku_unit = self.normalize_unit(raw_unit)

                    sku_status = self.normalize_status(
                        self.v(row, "SKU Status (Visible / Disabled / Out of Stock)", "Visible")
                    )

                    SKU.objects.update_or_create(
                        sku_code=str(sku_code).strip(),
                        product=product,
                        defaults={
                            "sku_name": str(self.v(row, "SKU Name", "")).strip(),
                            "sku_quantity": int(self.v(row, "SKU Quantity", 1) or 1),
                            "sku_unit": sku_unit,
                            "sku_mrp": float(self.v(row, "MRP (₹)", 0) or 0),
                            "sku_expiry_duration": int(self.v(row, "Expiry Duration (in days)", 0) or 0),
                            "sku_bulk_qty_limit": int(self.v(row, "Bulk Order Limit", 0) or 0),
                            "sku_status": sku_status,
                            "long_distance_availability": False,
                            "same_day_delivery": False,
                            "customization_available": False,
                        }
                    )

                    sku_count += 1
                    self.stdout.write(f"✅ Row {row_no}: {item_code} → SKU {sku_code} ({sku_unit})")

                except Exception as e:
                    errors += 1
                    self.stdout.write(self.style.ERROR(f"❌ Row {row_no}: {e}"))

        self.stdout.write(self.style.SUCCESS("\n🎉 IMPORT COMPLETED"))
        self.stdout.write(f"📦 Products created : {product_count}")
        self.stdout.write(f"🏷️  SKUs processed   : {sku_count}")
        self.stdout.write(f"❌ Errors            : {errors}")