# project/utils/order_sync.py

import time
import logging
from django.db import transaction, OperationalError
from project.models import BouquetVariant, BouquetStem
from project.utils.woocommerce_service import update_variant_stock
from project.models import OrderRecord

logger = logging.getLogger(__name__)

MAX_RETRIES = 5
RETRY_DELAY = 0.2  # seconds


def recalculate_all_variant_stock():
    """
    Recalculate the available stock for all bouquet variants based on stems,
    then update WooCommerce.
    """
    for variant in BouquetVariant.objects.all():
        bouquet = variant.bouquet
        stems = BouquetStem.objects.filter(bouquet=bouquet)

        min_possible = None
        for bs in stems:
            units = getattr(bs, f"units_{variant.size.lower()}", 0)
            if units == 0:
                continue
            available = bs.stem.stock_quantity // units
            min_possible = available if min_possible is None else min(min_possible, available)

        if min_possible is None:
            min_possible = 0

        try:
            update_variant_stock(variant.woo_variation_id, min_possible)
        except Exception as e:
            logger.exception("Failed to update Woo stock for variant %s: %s", variant.id, e)


def process_woo_order(woo_order):
    """
    Deduct stems from Django stock when a Woo order is completed.
    Retries safely for SQLite if the database is locked.
    Returns (success: bool, message: str)
    """
    order_id = woo_order.get("id", "unknown")
    
    OrderRecord.objects.get_or_create(
    woo_order_id=str(order_id),
    defaults={
        "customer_name": woo_order.get("billing", {}).get("first_name", ""),
        "total_amount": woo_order.get("total", 0),
    }
    )
        
    for attempt in range(1, MAX_RETRIES + 1):
        try:
            with transaction.atomic():
                for item in woo_order.get("line_items", []):
                    variation_id = item.get("variation_id")
                    quantity = item.get("quantity", 1)

                    try:
                        variant = BouquetVariant.objects.get(woo_variation_id=variation_id)
                    except BouquetVariant.DoesNotExist:
                        logger.warning("Variant %s not found, skipping", variation_id)
                        continue

                    bouquet_stems = BouquetStem.objects.filter(bouquet=variant.bouquet)

                    # Deduct stems per variant
                    for bs in bouquet_stems:
                        units = getattr(bs, f"units_{variant.size.lower()}", 0)
                        total_required = units * quantity
                        stem = bs.stem
                        stem.stock_quantity = max(stem.stock_quantity - total_required, 0)
                        stem.save()

                # Recalculate all variants and push to Woo
                recalculate_all_variant_stock()

            return True, f"Order {order_id} processed successfully"

        except OperationalError as e:
            if "database is locked" in str(e):
                logger.warning(
                    "SQLite locked during order %s, retrying %d/%d", order_id, attempt, MAX_RETRIES
                )
                time.sleep(RETRY_DELAY)
                continue
            else:
                logger.exception("OperationalError processing order %s: %s", order_id, e)
                return False, str(e)
        except Exception as e:
            logger.exception("Unexpected error processing order %s: %s", order_id, e)
            return False, str(e)

    return False, f"Failed to process order {order_id} after {MAX_RETRIES} retries"