from django.db.models import Sum, Q
from .models import Stock, Order, OrderItems, Product
from organisations.models import Organisation, OrganisationBranch
from ledgers.ledgers_helper import generate_reference_no, get_chart_of_account_by_code
from ledgers.models import SystemTransactions, OrganisationSubAccount
from customers.models import Customer
from django.utils import timezone
from ledgers.models import DebtorAccounts, DebtorSupplies


def get_stock_balance_by_product(product_id, branch_id=None):
    stock_total = 0
    order_total = 0
    order_item_total = 0
    available_stock = 0
    selling_price = 0
    cost_price = 0
    total_order_quantity = 0

    # --- Filter for stock ---
    stock_filter = {"product__id": product_id}
    if branch_id:
        stock_filter['stock_branch__id'] = branch_id

    # Removed product__service_type — that field no longer exists
    stock_totals = (
        Stock.objects.filter(**stock_filter)
        .annotate(total=Sum('quantity'))
        .values('sell_price', 'purchase_price', 'total', 'quantity')
        .order_by('-id')
    )

    if stock_totals:
        stock_values = stock_totals[0]
        total = sum(float(item['quantity']) for item in stock_totals)

        seling_price = float(stock_values.get('sell_price', 0))
        purchase_price = float(stock_values.get('purchase_price', 0))
        quantity = stock_values.get('quantity')

        # Simplified: no lease-based logic since service_type is gone
        stock_total = total

        if seling_price:
            selling_price = seling_price
        if purchase_price:
            cost_price = purchase_price

    # --- Orders ---
    order_item_filter = {"product__id": product_id, "order__status": "Processed"}
    if branch_id:
        order_item_filter['order__order_branch__id'] = branch_id

    order_item_totals = (
        OrderItems.objects.filter(**order_item_filter)
        .aggregate(total=Sum('quantity'))
        .get('total')
    )

    total_order_items = OrderItems.objects.filter(**order_item_filter).values('quantity')
    if total_order_items:
        for order_item_values in total_order_items:
            total_order_quantity = order_item_values['quantity']

    if order_item_totals:
        order_item_total = order_item_totals

    available_stock = stock_total if stock_total > 0 else 0

    return {
        "stock_total": stock_total,
        "order_total": order_total,
        "available_stock": available_stock,
        "order_item_total": order_item_total,
        "selling_price": selling_price,
        "cost_price": cost_price,
        "total_order_quantity": total_order_quantity,
    }


def get_stock_balance_by_stock(stock, branch_id):
    stock_total = 0
    order_total = 0
    available_stock = 0

    stock_filter = {"id": stock.id}
    if branch_id:
        stock_filter['stock_branch__id'] = branch_id
    stock_totals = Stock.objects.filter(**stock_filter).aggregate(total=Sum('quantity'))['total']
    if stock_totals:
        stock_total = stock_totals

    order_filter = {"product": stock.product, "status": 'Processed'}
    if branch_id:
        order_filter['order_branch__id'] = branch_id
    order_totals = Order.objects.filter(**order_filter).aggregate(total=Sum('quantity'))['total']
    if order_totals:
        order_total = order_totals
    available_stock = stock_total - order_total
    return {"stock_total": stock_total, "order_total": order_total, "available_stock": available_stock}

def stock_product_transactions(request, branch_id, organisation_id):
    product_id = request.data.get('product_id')
    qty = request.data.get('qty')
    amount = request.data.get('amount')
    record_date = request.data.get('record_date')
    customer_id = request.data.get('customer_id')
    payment_method = request.data.get('payment_method')
    account = request.data.get('account')
    maturity_date = request.data.get('maturity_date')

    response = {"statusCode": "200", "message": "Success"}

    product = Product.objects.get(pk=product_id)
    heading = product.product_name

    organisation = Organisation.objects.get(pk=organisation_id)
    organisation_branch = OrganisationBranch.objects.get(pk=branch_id)

    # For receivables (advance payments), use correct accounts
    debit_chart = get_chart_of_account_by_code('sys-1133', organisation)

    income_chart_id = request.data.get('income_chart')
    income_chart = OrganisationSubAccount.objects.filter(pk=income_chart_id).first()
    if not income_chart:
        return {"statusCode": "400", "message": "income_chart is required"}

    if debit_chart and income_chart:
        if payment_method == 'advance':
            reference_no = generate_reference_no(debit_chart.account_line, organisation_id, 'ast')

            # Create receivable transaction
            transaction = SystemTransactions(amount=amount, heading=heading, record_date=record_date, payment_method=payment_method, voucher_no='', reference_no=reference_no, debit_chart=debit_chart, credit_chart=income_chart, branch_id=branch_id, added_by=request.user)
            transaction.save()

            data = {"status": "Processed", "customer": Customer.objects.get(pk=customer_id), "payment_method": payment_method, "transaction_type": 'Receivable', "transaction": transaction, "order_branch": organisation_branch, "added_by": request.user, "maturity_date": maturity_date}
            order = Order.objects.create(**data)

            product = Product.objects.get(pk=product_id)
            order_items_obj = {"quantity": qty, "total_cost": amount, "order": order, "product": product, "added_by": request.user}
            OrderItems.objects.create(**order_items_obj)
    else:
        response = {"statusCode": "500", "message": "No_charts"}

    return response


def delete_or_reverse_transaction(id, trans_type='delete'):
    order = Order.objects.filter(transaction__id=id).first()
    if not order:
        return False
    if trans_type == 'delete':
        print('****************** step 2 deleting transaction')
        # SystemTransactions.objects.filter(id=id).delete()
        deleteTransaction = SystemTransactions.objects.filter(id=id).first()
        print('****************** step 3 deleting transaction')
        if deleteTransaction:
            print('****************** step 4 deleting transaction')
            deleteTransaction.deleted = True
            deleteTransaction.save()
    elif trans_type == 'reversal':
        system_transaction_old = SystemTransactions.objects.filter(id=id).first()
        system_transaction_new = SystemTransactions.objects.filter(id=id).first()
        if system_transaction_new:
            # cron the transaction and create new
            system_transaction_new.id = None
            system_transaction_new.save()

            credit_chart = system_transaction_new.credit_chart
            debit_chart = system_transaction_new.debit_chart
            heading = system_transaction_new.heading
            reference_no = system_transaction_new.reference_no

            system_transaction_new.transaction_type = 'revesal'
            system_transaction_new.debit_chart = credit_chart
            system_transaction_new.credit_chart = debit_chart
            system_transaction_new.heading = f"{heading} - revesal"
            system_transaction_new.reference_no = f"rev-{reference_no}"
            system_transaction_new.date_added = timezone.now()
            system_transaction_new.save()

            system_transaction_old.transaction_type = 'reversed'
            system_transaction_old.heading = f"{heading} - revesal"
            system_transaction_old.save()

            order.transaction_status = 'reversed'
            order.save()
    return True


def process_credit_sale_with_debtor(request, order_id, debtor_account_id, organisation_id, branch_id):
    """
    New helper function to handle credit sales and link to debtor accounts.
    Creates DebtorSupplies record and auto-populates customer info from debtor account.
    """

    try:
        order = Order.objects.get(id=order_id)
        debtor_account = DebtorAccounts.objects.get(id=debtor_account_id)

        # Link order to debtor account
        order.debtor_account = debtor_account
        # Auto-populate customer from debtor account if not already set
        if not order.customer and debtor_account.customer:
            order.customer = debtor_account.customer
        order.save()

        # Create DebtorSupplies record
        debtor_supply = DebtorSupplies.objects.create(
            debtor=debtor_account,
            reference_transaction=order.transaction,
            maturity_date=order.maturity_date or timezone.now(),
            added_by=request.user.id
        )

        return {
            "statusCode": "200",
            "message": "Credit sale linked to debtor account successfully",
            "debtor_supply_id": debtor_supply.id,
            "customer_name": debtor_account.customer.name if debtor_account.customer else debtor_account.account_name
        }
    except Exception as e:
        return {
            "statusCode": "500",
            "message": f"Error linking credit sale to debtor: {str(e)}"
        }


def get_stock_sold_by_stock_batch(stock_obj, branch_id=None):
    """
    Calculate how much has been sold from a specific stock batch
    using the same logic as get_stock_balance_by_product but for individual stock
    """
    # Calculate original quantity from transaction amount and purchase price
    if stock_obj.purchase_price > 0:
        original_quantity = stock_obj.transaction.amount / stock_obj.purchase_price
    else:
        original_quantity = stock_obj.quantity
    
    # Current quantity is what's left in stock
    current_quantity = stock_obj.quantity
    
    # Sold quantity is the difference
    sold_quantity = max(0, original_quantity - current_quantity)
    
    return round(sold_quantity, 2)


def reduce_inventory_on_sale(order_items, branch_id):
    """
    Reduce inventory quantities when items are sold using FIFO method.
    Updated to work with product-specific stock charts.
    """
    try:
        for order_item in order_items:
            product = order_item.product
            quantity_sold = order_item.quantity

            # Get stock for this product at this branch, ordered by date (FIFO)
            stocks = Stock.objects.filter(
                product=product,
                stock_branch_id=branch_id,
                quantity__gt=0
            ).order_by('id')

            remaining_qty = quantity_sold

            # Reduce stock from oldest batches first (FIFO)
            for stock in stocks:
                if remaining_qty <= 0:
                    break

                current_stock_qty = stock.quantity
                if current_stock_qty > 0:
                    reduction = min(current_stock_qty, remaining_qty)
                    stock.quantity -= reduction
                    stock.save()
                    remaining_qty -= reduction

        return True
    except Exception as e:
        print(f"Error reducing inventory: {str(e)}")
        return False


def update_inventory_on_sale(order_items, branch_id):
    """
    Enhanced to properly reduce inventory on sale.
    Reduces stock quantities based on items sold.
    """

    try:
        for order_item in order_items:
            product = order_item.product
            quantity_sold = order_item.quantity

            # Get stock for this product at this branch, ordered by date
            stocks = Stock.objects.filter(
                product=product,
                stock_branch_id=branch_id,
                transaction__deleted=False
            ).order_by('date_added')

            remaining_qty = quantity_sold

            # Reduce stock from oldest batches first (FIFO)
            for stock in stocks:
                if remaining_qty <= 0:
                    break

                current_stock_qty = stock.quantity
                if current_stock_qty > 0:
                    reduction = min(current_stock_qty, remaining_qty)
                    stock.quantity -= reduction
                    stock.save()
                    remaining_qty -= reduction

        return True
    except Exception as e:
        print(f"Error updating inventory: {str(e)}")
        return False