import logging

from django.db.utils import OperationalError, ProgrammingError
from django.utils import timezone as tz

from .models import Inventory, StockReservation, StockReservationInstance

logger = logging.getLogger(__name__)


def expire_reservations_for_warehouse(warehouse):
    """Lazily expire approved reservations past their expiry_date."""
    try:
        StockReservation.objects.filter(
            warehouse=warehouse,
            status="approved",
            expiry_date__lt=tz.now(),
        ).update(status="expired")
    except (ProgrammingError, OperationalError) as exc:
        logger.warning("Stock reservations unavailable, skipping expiry: %s", exc)


def get_reserved_qty(product, warehouse):
    """Return total quantity held in active (approved) reservations."""
    try:
        instances = StockReservationInstance.objects.filter(
            product=product,
            stock_reservation__warehouse=warehouse,
            stock_reservation__status="approved",
            stock_reservation__recycle_bin=False,
        )
        return sum(float(inst.quantity_requested) for inst in instances)
    except (ProgrammingError, OperationalError) as exc:
        logger.warning("Stock reservations unavailable, assuming zero reserved qty: %s", exc)
        return 0.0


def get_inventory_totals(product, warehouse):
    expire_reservations_for_warehouse(warehouse)
    try:
        inventory = Inventory.objects.get(product=product, warehouse=warehouse)
        current_qty = float(inventory.quantity)
    except Inventory.DoesNotExist:
        current_qty = 0.0
    reserved_qty = get_reserved_qty(product, warehouse)
    available_qty = max(0.0, current_qty - reserved_qty)
    return {
        "current_quantity": current_qty,
        "reserved_quantity": reserved_qty,
        "available_quantity": available_qty,
        "is_available": available_qty > 0,
    }


def get_branch_available_qty(product, company_branch):
    """Sum stock totals across all warehouses on a branch."""
    current = 0.0
    reserved = 0.0
    available = 0.0
    warehouses = company_branch.branch_warehouses.filter(recycle_bin=False)
    for warehouse in warehouses:
        totals = get_inventory_totals(product, warehouse)
        current += totals["current_quantity"]
        reserved += totals["reserved_quantity"]
        available += totals["available_quantity"]
    return {
        "current_quantity": current,
        "reserved_quantity": reserved,
        "available_quantity": available,
        "is_available": available > 0,
    }


def attach_branch_stock_fields(product_map, product, company_branch):
    totals = get_branch_available_qty(product, company_branch)
    product_map["current_quantity"] = str(totals["current_quantity"])
    product_map["reserved_quantity"] = str(totals["reserved_quantity"])
    product_map["available_quantity"] = str(totals["available_quantity"])
    product_map["is_available"] = "true" if totals["is_available"] else "false"
    if totals["available_quantity"] <= 0:
        product_map["stock_status"] = "out_of_stock"
    elif totals["available_quantity"] <= 5:
        product_map["stock_status"] = "low_stock"
    else:
        product_map["stock_status"] = "in_stock"
    return product_map


def reserve_stock_for_order(order, staff_profile=None, expiry_days=7):
    """Auto-create approved stock reservation(s) in the ERP for a CRM-pushed order.

    This is the CRM -> ERP half of the two-way stock sync: when a quote becomes an
    order, the committed quantity is reserved against branch inventory so ERP
    availability immediately reflects the sale. Reservations are created with
    status "approved" (sale is committed) and an expiry, so the scheduled
    `expire_stock_reservations` sweep releases them if the order stalls.

    Returns the set of affected product ids (for the CRM stock broadcast).
    Best-effort: never raises into the order-creation path.
    """
    from datetime import timedelta

    from django.utils import timezone as tz

    from .models import (
        Inventory,
        StockReservation,
        StockReservationInstance,
    )

    affected = set()
    branch = getattr(order, "company_branch", None)
    if branch is None:
        return affected
    warehouses = list(branch.branch_warehouses.filter(recycle_bin=False))
    if not warehouses:
        return affected

    # Group each order line into the best warehouse on the branch:
    # prefer one with enough free stock, otherwise the first that stocks it,
    # otherwise the branch's first warehouse.
    by_warehouse = {}
    for item in order.customer_order_items.all():
        product = item.product
        if product is None:
            continue
        try:
            qty = float(item.quantity)
        except (TypeError, ValueError):
            continue
        if qty <= 0:
            continue

        chosen = None
        for warehouse in warehouses:
            try:
                inventory = Inventory.objects.get(product=product, warehouse=warehouse)
            except Inventory.DoesNotExist:
                continue
            available = float(inventory.quantity) - get_reserved_qty(product, warehouse)
            if available >= qty:
                chosen = warehouse
                break
            if chosen is None:
                chosen = warehouse
        if chosen is None:
            chosen = warehouses[0]

        by_warehouse.setdefault(chosen.id, (chosen, []))[1].append((product, qty))
        affected.add(product.id)

    order_number = getattr(order, "customer_order_number", "") or ""
    for _wh_id, (warehouse, lines) in by_warehouse.items():
        reservation = StockReservation.objects.create(
            warehouse=warehouse,
            reservation_description=f"Auto-reserved for customer order {order_number}",
            status="approved",
            expiry_date=tz.now() + timedelta(days=expiry_days),
            requested_by=staff_profile,
            approved_or_rejected_by=staff_profile,
            created_by=staff_profile,
            last_updated_by=staff_profile,
        )
        for product, qty in lines:
            StockReservationInstance.objects.create(
                stock_reservation=reservation,
                product=product,
                quantity_requested=str(qty),
            )
    return affected


def validate_line_items_stock(company_branch, line_items):
    """
    Validate sales line items against branch available stock.
    line_items: iterable of dicts with product_id and quantity keys.
    Returns (ok, error_message).
    """
    from .models import Product

    for item in line_items:
        product = Product.objects.get(id=int(item["product_id"]))
        requested = float(item["quantity"])
        totals = get_branch_available_qty(product, company_branch)
        if requested > totals["available_quantity"]:
            return False, (
                f"Insufficient stock for '{product.product_name}'. "
                f"Available: {totals['available_quantity']}, Requested: {requested}"
            )
    return True, ""