from django.shortcuts import render

# Create your views here.
from django.shortcuts import render, HttpResponseRedirect, get_object_or_404
from django.http import JsonResponse, HttpResponse
from django.db import transaction, connection
from django.utils import timezone
from .models import *
from sales.models import *
from store.models import *
from stock.models import *
from material.models import *
from masters.models import *
from common.utils import *
from django.db.models import Q, Sum
import json
import base64
from django.db import IntegrityError, transaction
import os
import openpyxl
from openpyxl.styles import Font
from django.core.files.storage import default_storage
from django.conf import settings
from datetime import date
# *********************************************************************************************************************************

def internal_wholesale_sales_order(request):
    if 'user_id' not in request.session:
        return HttpResponseRedirect("/")

    if request.session.get('user_type') != 'wholesale':
        return HttpResponseRedirect("/")

    branch_id = request.session.get('branch_id')
    encoded_id = request.GET.get('id') or 0
    decoded_id = decode_base64_id(encoded_id) or 0 if encoded_id != 0 else 0
    branch = selectList(branch_table, {'is_wholesale': 1}, order_by='name').exclude(id=branch_id)

    return render(request, 'internal_wholesale/sales/details.html', {
        'branch': branch,
        'decoded_id': decoded_id
    })


def ajax_internal_wholesale_sales_view(request):
    role_id = request.session.get('role_id')
    user_type = request.session.get('user_type')
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')
    sales_id = request.POST.get('sales_id')
    filter_sales_status = request.POST.get('filter_sales_status')

    if user_type != 'wholesale':
        return JsonResponse({'message': 'permission', 'error_message': 'Unauthorized'})

    has_access, _ = check_user_access(role_id, 'sales', "read")
    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view sales orders.'})

    query = Q(
        status=1,
        is_active=1,
        branch_id=branch_id,
        current_fy=financial_year,
        wholesale_type='internal'
    )

    if sales_id:
        query &= Q(id=sales_id)
    if from_date and to_date:
        query &= Q(inv_date__range=[from_date, to_date])
    if filter_sales_status:
        query &= Q(sales_status=filter_sales_status)

    data = list(selectList(wholesale_sales_order_table, query).values())

    formatted = []
    for index, item in enumerate(data):
        row_id = item['id']
        claim_totals = wholesale_child_sales_order_table.objects.filter(
            tm_sales_id=row_id,
            status=1
        ).aggregate(total_claim=Sum('claim_amount'))

        claim = claim_totals['total_claim'] or 0.00

        sales_status = (item.get('sales_status') or '').lower()
        if sales_status == 'rejected':
            action_html = (
                '<div class="d-flex gap-1 justify-content-center align-items-center">'
                '<button type="button" onclick="showRejectionDetails(\'{}\')" class="btn btn-outline-warning btn-xs p-1" title="Rejection Info"><i class="fas fa-info-circle"></i></button>'
                '<button type="button" onclick="view_data(\'{}\')" class="btn btn-outline-primary btn-xs p-1" title="View Details"><i class="fas fa-eye"></i></button>'
                '</div>'
            ).format(row_id, row_id)
        else:
            action_html = '<div class="d-flex gap-1 justify-content-center align-items-center">'
            if sales_status == 'pending':
                action_html += (
                    '<button type="button" onclick="edit_data(\'{}\')" class="btn btn-outline-success btn-xs p-1"><i class="fas fa-edit"></i></button>'
                    '<button type="button" onclick="delete_data(\'{}\')" class="btn btn-outline-danger btn-xs p-1"><i class="fas fa-trash-alt"></i></button>'
                ).format(row_id)
            action_html += (
                '<button type="button" onclick="print_data(\'{}\')" class="btn btn-outline-info btn-xs p-1" title="Print"><i class="fas fa-print"></i></button>'
                '<button type="button" onclick="thermal_print_data(\'{}\')" class="btn btn-outline-primary btn-xs p-1" title="Thermal Print"><i class="fas fa-file-invoice"></i></button>'
                '<button type="button" onclick="view_data(\'{}\')" class="btn btn-outline-secondary btn-xs p-1" title="View Details"><i class="fas fa-eye"></i></button>'
                '</div>'
            ).format(row_id, row_id, row_id)

        formatted.append({
            'id': index + 1,
            'action': action_html,
            'date': format_date_month_year(item['inv_date']) if item.get('inv_date') else '-',
            'inv_no': item.get('inv_no') or '-',
            'customer': item.get('customer_name') or '-',
            'phone': item.get('customer_phone') or '-',
            'customer_type': item.get('customer_type') or '-',
            'quantity': item.get('total_quantity') or 0,
            'amount': format_amount(item.get('total_amount') or 0),
            'claim': format_amount(claim),
            'cash': format_amount(item.get('cash') or 0),
            'bank': format_amount(item.get('bank') or 0),
            'exchange': format_amount(0),
            'finance': format_amount(item.get('finance_amount') or 0),
            'card': format_amount(0),
            'collection': format_amount(item.get('collected_amount') or 0),
            'balance': format_amount(item.get('balance') or 0),
            'remarks': item.get('remarks') or '-',
            'status': format_badge(
                item.get('sales_status'),
                mapping={
                    'pending': 'badge text-bg-info',
                    'approved': 'badge text-bg-success',
                    'rejected': 'badge text-bg-danger'
                },
                label_mapping={
                    'pending': 'Pending',
                    'approved': 'Approved',
                    'rejected': 'Rejected'
                }
            ),
            'rejected_by': getItemNameById(employee_table, item.get('rejected_by')) if item.get('rejected_by') else '-',
            'rejected_on': format_datetime(item.get('rejected_on')) if item.get('rejected_on') else '-',
            'rejected_remarks': item.get('rejected_remarks') or '-',
        })

    return JsonResponse({'data': formatted})


def ajax_internal_wholesale_sales_items(request):
    sales_id = request.POST.get('sales_id')
    if not sales_id:
        return JsonResponse({'items': [], 'tx_transaction': []})

    main = wholesale_sales_order_table.objects.filter(id=sales_id, status=1, is_active=1).first()
    if not main:
        return JsonResponse({'items': [], 'tx_transaction': []})

    items_qs = wholesale_child_sales_order_table.objects.filter(
        tm_sales_id=sales_id,
        status=1,
        is_active=1
    ).values()

    items = []
    for index, item in enumerate(items_qs):
        items.append({
            'id': index + 1,
            'subcategory_name': getItemNameById(sub_category_table, item.get('subcategory_id')) if item.get('subcategory_id') else '-',
            'brand_name': getItemNameById(brand_table, item.get('brand_id')) if item.get('brand_id') else '-',
            'model_name': getItemNameById(model_table, item.get('model_id')) if item.get('model_id') else '-',
            'variant_name': getItemNameById(variant_table, item.get('variant_id')) if item.get('variant_id') else '-',
            'imei_no': item.get('imei_no') or '-',
            'rate': format_amount(item.get('rate') or 0),
            'quantity': item.get('quantity') or 0,
            'amount': format_amount(item.get('amount') or 0),
        })

    tx_transaction = []
    if (main.cash or 0) > 0:
        tx_transaction.append({
            'payment_type': 'cash',
            'amount': format_amount(main.cash),
            'charges': format_amount(0),
            'cashback': format_amount(0),
            'bank_name': '-',
            'charge_bank_name': '-',
            'finance_customer': '',
            'reference_no': ''
        })
    if (main.bank or 0) > 0:
        tx_transaction.append({
            'payment_type': 'bank',
            'amount': format_amount(main.bank),
            'charges': format_amount(0),
            'cashback': format_amount(0),
            'bank_name': getItemNameById(bank_table, 0) if hasattr(main, 'bank_id') and getattr(main, 'bank_id', 0) else '-',
            'charge_bank_name': '-',
            'finance_customer': '',
            'reference_no': ''
        })
    if (main.finance_amount or 0) > 0:
        tx_transaction.append({
            'payment_type': 'finance',
            'amount': format_amount(main.finance_amount),
            'charges': format_amount(main.charges or 0),
            'cashback': format_amount(0),
            'bank_name': '-',
            'charge_bank_name': '-',
            'finance_customer': '',
            'reference_no': getItemNameById(finance_table, main.finance_id) if main.finance_id else ''
        })

    return JsonResponse({'items': items, 'tx_transaction': tx_transaction})


def internal_wholesale_sales_order_edit(request):
    if 'user_id' not in request.session:
        return HttpResponseRedirect("/")

    user_type = request.session.get('user_type')
    branch_id = request.session.get('branch_id')

    if user_type != 'wholesale':
        return HttpResponseRedirect("/")

    encoded_id = request.GET.get('id', None)
    decoded_id = decode_base64_id(encoded_id)
    if not decoded_id:
        return HttpResponseRedirect("/wholesale/internal/sales/")

    sales = select_row(wholesale_sales_order_table, {
        'id': decoded_id,
        'branch_id': branch_id,
        'wholesale_type': 'internal',
        'status': 1,
        'is_active': 1
    })
    if not sales:
        return HttpResponseRedirect("/wholesale/internal/sales/")

    # Pending-only edit access
    if (sales.sales_status or '').lower() != 'pending':
        return HttpResponseRedirect("/wholesale/internal/sales/")

    customer = selectList(customer_table, {'branch_id': branch_id}, order_by='name')
    branch = selectList(branch_table, order_by='name').exclude(id=branch_id)
    item = selectList(item_table)
    brand = selectList(brand_table, order_by='name')
    employee = employee_list(request)
    subcategory = selectList(sub_category_table, order_by='name')
    is_ho = request.session.get('is_ho')
    if is_ho == 1:
        bank = selectList(bank_table, {'is_default': 0}, order_by='name')
    else:
        bank = selectList(bank_table, {'is_default': 1}, order_by='name')
    finance = selectList(finance_table, {'f_type': 'finance'}, order_by='name')
    card = selectList(finance_table, {'f_type': 'card'}, order_by='name')

    return render(request, 'internal_wholesale/sales/edit.html', {
        'customer': customer,
        'branch': branch,
        'item': item,
        'brand': brand,
        'employee': employee,
        'subcategory': subcategory,
        'bank': bank,
        'finance': finance,
        'card': card,
        'sales': sales,
        'decoded_id': decoded_id
    })


def ajax_internal_wholesale_sales_edit(request):
    if request.session.get('user_type') != 'wholesale':
        return JsonResponse({'items': [], 'tx_transaction': []})

    branch_id = request.session.get('branch_id')
    sales_id = request.POST.get('sales_id')
    if not sales_id:
        return JsonResponse({'items': [], 'tx_transaction': []})

    main = wholesale_sales_order_table.objects.filter(
        id=sales_id,
        branch_id=branch_id,
        wholesale_type='internal',
        status=1,
        is_active=1
    ).first()
    if not main:
        return JsonResponse({'items': [], 'tx_transaction': []})

    items_qs = wholesale_child_sales_order_table.objects.filter(
        tm_sales_id=sales_id,
        status=1,
        is_active=1
    ).values()

    items = []
    for index, item in enumerate(items_qs):
        items.append({
            'id': index + 1,
            'ean': item.get('ean_number') or '-',
            'item_id': item.get('sales_id') or 0,
            'subcategory_id': item.get('subcategory_id') or 0,
            'subcategory_name': getItemNameById(sub_category_table, item.get('subcategory_id')) if item.get('subcategory_id') else '-',
            'brand_id': item.get('brand_id') or 0,
            'brand_name': getItemNameById(brand_table, item.get('brand_id')) if item.get('brand_id') else '-',
            'model_id': item.get('model_id') or 0,
            'model_name': getItemNameById(model_table, item.get('model_id')) if item.get('model_id') else '-',
            'variant_id': item.get('variant_id') or 0,
            'variant_name': getItemNameById(variant_table, item.get('variant_id')) if item.get('variant_id') else '-',
            'color_id': item.get('color_id') or 0,
            'color_name': getItemNameById(color_table, item.get('color_id')) if item.get('color_id') else '-',
            'imei_no': item.get('imei_no') or '-',
            'rate': format_amount(item.get('rate')) if item.get('rate') else '0.00',
            'quantity': item.get('quantity') or 0,
            'amount': format_amount(item.get('amount')) if item.get('amount') else '0.00',
            'source': item.get('source') or '',
            'special_id': item.get('special_id') or 0,
            'price_id': 0,
            'special_amount': format_amount(item.get('special_amount')) if item.get('special_amount') else '0.00',
            'bop_price': format_amount(item.get('bop')) if item.get('bop') else '0.00',
            'mop_price': format_amount(item.get('mop')) if item.get('mop') else '0.00',
            'wsp_price': format_amount(item.get('wsp')) if item.get('wsp') else '0.00',
            'fsp_price': format_amount(item.get('fsp')) if item.get('fsp') else '0.00',
            'db_price': format_amount(item.get('db_price')) if item.get('db_price') else '0.00',
            'purchase_price': format_amount(item.get('purchase_price')) if item.get('purchase_price') else '0.00',
            'tm_sales_id': item.get('tm_sales_id') or 0,
            'tx_sales_id': item.get('id', 0),
            'tax_percent': item.get('tax_percent') or 0,
            'tax_amount': format_amount(item.get('tax_amount')) if item.get('tax_amount') else '0.00',
            'tax_cgst': format_amount(item.get('tax_cgst')) if item.get('tax_cgst') else '0.00',
            'tax_sgst': format_amount(item.get('tax_sgst')) if item.get('tax_sgst') else '0.00',
            'discount_amount': format_amount(item.get('discount_amount')) if item.get('discount_amount') else '0.00',
            'discount_percent': item.get('discount_percent') or 0,
            'upgrade_id': item.get('upgrade_id') or 0,
            'upgrade_amount': item.get('upgrade_amount') if item.get('upgrade_amount') else '0.00',
            'is_upgrade_approved': item.get('is_upgrade_approved') or 0,
            'upgrade_approved_on': format_datetime(item.get('upgrade_approved_on')) if item.get('upgrade_approved_on') else '',
            'upgrade_approved_by': item.get('upgrade_approved_by') or 0,
            'upgrade_collect': item.get('upgrade_collect') or 0,
            'upgrade_status': item.get('upgrade_status') or 'pending',
            'upgrade_rejected_on': format_datetime(item.get('upgrade_rejected_on')) if item.get('upgrade_rejected_on') else '',
            'upgrade_rejected_by': item.get('upgrade_rejected_by') or 0,
            'upgrade_rejected_remarks': item.get('upgrade_rejected_remarks') or '',
            'is_claim': item.get('is_claim') or 0,
            'claim_amount': item.get('claim_amount') if item.get('claim_amount') else '0.00',
            'claimed_on': format_datetime(item.get('claimed_on')) if item.get('claimed_on') else '',
        })

    tx_transaction = []
    if (main.cash or 0) > 0:
        tx_transaction.append({
            'payment_type': 'cash',
            'amount': format_amount(main.cash),
            'finance_id': 0,
            'bank_id': 0,
            'bank_name': '-',
            'card_id': 0,
            'reference_no': '',
            'finance_customer': '',
            'balance': format_amount(main.balance or 0),
            'is_collect': 0,
            'collected_on': '',
            'collect_amount': 0.00,
            'collect_status': 'pending',
            'cashback': 0.00,
            'charges': 0.00,
            'charge_mode': 'cash',
            'charge_bank_id': 0,
            'charge_bank_name': '-',
            'charge_status': 'pending',
            'charge_amount_collect': 0.00,
            'charge_collect_on': '',
            'exchange_status': 'pending',
            'exchange_claim': 0.00,
            'id': 0,
        })
    if (main.bank or 0) > 0:
        tx_transaction.append({
            'payment_type': 'bank',
            'amount': format_amount(main.bank),
            'finance_id': 0,
            'bank_id': 0,
            'bank_name': '-',
            'card_id': 0,
            'reference_no': '',
            'finance_customer': '',
            'balance': format_amount(main.balance or 0),
            'is_collect': 0,
            'collected_on': '',
            'collect_amount': 0.00,
            'collect_status': 'pending',
            'cashback': 0.00,
            'charges': 0.00,
            'charge_mode': 'cash',
            'charge_bank_id': 0,
            'charge_bank_name': '-',
            'charge_status': 'pending',
            'charge_amount_collect': 0.00,
            'charge_collect_on': '',
            'exchange_status': 'pending',
            'exchange_claim': 0.00,
            'id': 0,
        })
    if (main.finance_amount or 0) > 0:
        tx_transaction.append({
            'payment_type': 'finance',
            'amount': format_amount(main.finance_amount),
            'finance_id': main.finance_id or 0,
            'bank_id': 0,
            'bank_name': '-',
            'card_id': 0,
            'reference_no': getItemNameById(finance_table, main.finance_id) if main.finance_id else '',
            'finance_customer': '',
            'balance': format_amount(main.balance or 0),
            'is_collect': 0,
            'collected_on': '',
            'collect_amount': 0.00,
            'collect_status': 'pending',
            'cashback': 0.00,
            'charges': float(main.charges or 0),
            'charge_mode': 'cash',
            'charge_bank_id': 0,
            'charge_bank_name': '-',
            'charge_status': 'pending',
            'charge_amount_collect': 0.00,
            'charge_collect_on': '',
            'exchange_status': 'pending',
            'exchange_claim': 0.00,
            'id': 0,
        })

    return JsonResponse({'items': items, 'tx_transaction': tx_transaction})


def update_internal_wholesale_sales_order(request):
    if request.method != 'POST':
        return JsonResponse({'message': 'Invalid request'}, status=400)

    if request.session.get('user_type') != 'wholesale':
        return JsonResponse({'message': 'permission', 'error_message': 'Unauthorized'})

    try:
        sales_id = int(request.POST.get('tm_sales_id') or 0)
        if sales_id <= 0:
            return JsonResponse({'message': 'warning', 'error_message': 'Sales ID missing'})

        branch_id = request.session.get('branch_id')
        company_id = request.session.get('company_id')
        user_id = request.session.get('user_id')
        fyf_name = request.session.get('fyf')
        financial_year = calculate_financial_year(fyf_name)

        main = wholesale_sales_order_table.objects.filter(
            id=sales_id,
            branch_id=branch_id,
            wholesale_type='internal',
            status=1,
            is_active=1
        ).first()
        if not main:
            return JsonResponse({'message': 'warning', 'error_message': 'Sales record not found'})
        if (main.sales_status or '').lower() != 'pending':
            return JsonResponse({'message': 'warning', 'error_message': 'Only pending sales can be edited'})

        po_date = request.POST.get('sales_date')
        customer_id = int(request.POST.get('customer_id') or 0)
        remarks = request.POST.get('description')
        total_qty = float(request.POST.get('total_quantity') or 0)
        sub_total = float(request.POST.get('sub_total') or 0)
        total_amount = float(request.POST.get('total_payable') or 0)
        total_paid = float(request.POST.get('total_paid') or 0)
        balance = float(request.POST.get('balance') or 0)
        total_discount = float(request.POST.get('total_discount') or 0)
        total_tax_cgst = float(request.POST.get('total_tax_cgst') or 0)
        total_tax_sgst = float(request.POST.get('total_tax_sgst') or 0)
        employee_id = request.POST.get('employee_id') or user_id
        now = timezone.localtime(timezone.now())

        items = json.loads(request.POST.get('items') or '[]')
        payments = json.loads(request.POST.get('payments') or '[]')
        if not items:
            return JsonResponse({'message': 'warning', 'error_message': 'No items added'})

        payment_cash = 0
        payment_bank = 0
        payment_finance = 0
        payment_finance_id = 0
        payment_charges = 0

        for pay in payments:
            amt = float(pay.get('amount') or 0)
            chg = float(pay.get('charges') or 0)
            ptype = (pay.get('type') or '').lower()

            if ptype == 'cash':
                payment_cash += amt
            elif ptype == 'finance':
                payment_finance += amt
                payment_finance_id = int(pay.get('id') or 0)
            elif ptype in ('bank', 'card', 'exchange'):
                payment_bank += amt

            payment_charges += chg

        cust = customer_table.objects.filter(id=customer_id).first() if customer_id else None

        with transaction.atomic():
            main.inv_date = po_date
            main.customer_id = customer_id
            main.customer_name = cust.name if cust else (main.customer_name or '')
            main.customer_phone = (cust.mobile or cust.phone) if cust else (main.customer_phone or '')
            main.customer_type = cust.customer_type if cust else (main.customer_type or 'retail')
            main.total_quantity = total_qty
            main.total_amount = total_amount
            main.sub_total = sub_total
            main.total_paid = total_paid
            main.total_discount = total_discount
            main.balance = balance
            main.remarks = remarks
            main.cash = payment_cash
            main.bank = payment_bank
            main.finance_id = payment_finance_id
            main.finance_amount = payment_finance
            main.charges = payment_charges
            main.total_cgst = total_tax_cgst
            main.total_sgst = total_tax_sgst
            main.employee_id = employee_id
            main.updated_on = now
            main.updated_by = user_id
            main.current_fy = financial_year
            main.save()

            wholesale_child_sales_order_table.objects.filter(
                tm_sales_id=sales_id,
                status=1
            ).update(status=0, is_active=0, updated_on=now, updated_by=user_id)

            for item in items:
                old_tx_id = item.get('tx_id')
                extra_fields = {}
                if old_tx_id:
                    old_item = wholesale_child_sales_order_table.objects.filter(id=old_tx_id).first()
                    if old_item:
                        extra_fields = {
                            'is_upgrade_approved': old_item.is_upgrade_approved,
                            'upgrade_approved_on': old_item.upgrade_approved_on,
                            'upgrade_approved_by': old_item.upgrade_approved_by,
                            'upgrade_collect': old_item.upgrade_collect,
                            'upgrade_status': old_item.upgrade_status,
                            'upgrade_rejected_on': old_item.upgrade_rejected_on,
                            'upgrade_rejected_by': old_item.upgrade_rejected_by,
                            'upgrade_rejected_remarks': old_item.upgrade_rejected_remarks,
                            'is_claim': old_item.is_claim,
                            'claim_amount': old_item.claim_amount,
                            'claimed_on': old_item.claimed_on,
                        }

                wholesale_child_sales_order_table.objects.create(
                    company_id=company_id,
                    current_fy=financial_year,
                    sales_id=item.get('item_id') or 0,
                    wholesale_type='internal',
                    branch_id=branch_id,
                    tm_sales_id=main.id,
                    customer_id=customer_id,
                    imei_no=item.get('imei') or '',
                    ean_number=item.get('ean') or '',
                    subcategory_id=item.get('subcategory_id') or 0,
                    brand_id=item.get('brand_id') or 0,
                    model_id=item.get('model_id') or 0,
                    variant_id=item.get('variant_id') or 0,
                    color_id=item.get('color_id') or 0,
                    quantity=1,
                    rate=item.get('rate') or 0,
                    amount=item.get('net_rate') or 0,
                    tax_percent=item.get('tax_percent') or 0,
                    tax_amount=item.get('tax_amount') or 0,
                    tax_cgst=item.get('tax_cgst') or 0,
                    tax_sgst=item.get('tax_sgst') or 0,
                    discount_percent=item.get('discount_percent') or 0,
                    discount_amount=item.get('discount_amount') or 0,
                    mop=item.get('mop_price') or 0,
                    bop=item.get('bop_price') or 0,
                    wsp=item.get('wsp_price') or 0,
                    fsp=item.get('fsp_price') or 0,
                    db_price=item.get('db_price') or 0,
                    mrp=item.get('rate') or 0,
                    purchase_price=item.get('purchase_price') or 0,
                    source=item.get('source') or '',
                    upgrade_id=item.get('upgrade_id') or 0,
                    upgrade_amount=item.get('upgrade_amount') or 0,
                    special_id=item.get('special_id') or 0,
                    special_amount=item.get('special_amount') or 0,
                    created_on=now,
                    updated_on=now,
                    created_by=user_id,
                    updated_by=user_id,
                    status=1,
                    is_active=1,
                    **extra_fields
                )

        return JsonResponse({'message': 'success'})
    except Exception as e:
        return JsonResponse({'message': 'exception', 'error': str(e)}, status=500)


def delete_internal_wholesale_sales_order(request):
    if request.method != 'POST':
        return JsonResponse({'message': 'Invalid request method'})

    if request.session.get('user_type') != 'wholesale':
        return JsonResponse({'message': 'permission', 'error_message': 'Unauthorized'})

    data_id = request.POST.get('id')
    branch_id = request.session.get('branch_id')
    user_id = request.session.get('user_id')
    now = timezone.localtime(timezone.now())

    try:
        main_qs = wholesale_sales_order_table.objects.filter(
            id=data_id,
            branch_id=branch_id,
            wholesale_type='internal',
            status=1,
            is_active=1,
            sales_status='pending'
        )
        if not main_qs.exists():
            return JsonResponse({'message': 'no such data'})

        main_qs.update(status=0, is_active=0, updated_on=now, updated_by=user_id)
        wholesale_child_sales_order_table.objects.filter(
            tm_sales_id=data_id,
            status=1,
            is_active=1
        ).update(status=0, is_active=0, updated_on=now, updated_by=user_id)

        return JsonResponse({'message': 'yes'})
    except Exception as e:
        return JsonResponse({'message': 'exception', 'error': str(e)})

def add_internal_wholesale_sales_order(request):
    if request.method != 'POST':
        return JsonResponse({'message': 'Invalid request'}, status=400)

    try:
        if 'user_id' not in request.session:
            return JsonResponse({'message': 'permission', 'error_message': 'Access denied'})

        user_type = request.session.get('user_type')
        if user_type != 'wholesale':
            return JsonResponse({'message': 'permission', 'error_message': 'Access denied'})

        company_id = request.session.get('company_id')
        branch_id = request.session.get('branch_id')
        user_id = request.session.get('user_id')
        role_id = request.session.get('role_id')
        fyf_name = request.session.get('fyf')
        financial_year = calculate_financial_year(fyf_name)

        po_date = request.POST.get('sales_date')
        customer_name = request.POST.get('customer_name')
        customer_type = request.POST.get('customer_type') or None
        customer_id = int(request.POST.get('customer_id') or 0)
        phone = request.POST.get('customer_phone') or None
        remarks = request.POST.get('description')
        total_qty = float(request.POST.get('total_quantity') or 0)
        sub_total = float(request.POST.get('sub_total') or 0)
        total_amount = float(request.POST.get('total_payable') or 0)
        total_paid = float(request.POST.get('total_paid') or 0)
        balance = float(request.POST.get('balance') or 0)
        total_discount = float(request.POST.get('total_discount') or 0)
        total_tax_cgst = float(request.POST.get('total_tax_cgst') or 0)
        total_tax_sgst = float(request.POST.get('total_tax_sgst') or 0)
        employee_id = request.POST.get('employee_id') or user_id
        sales_channel = request.POST.get('sales_channel') or 'wholesale_internal'
        now = timezone.localtime(timezone.now())

        if not check_user_access(role_id, 'sales', "create"):
            return JsonResponse({'message': 'permission', 'error_message': 'Access denied'})

        items = json.loads(request.POST.get('items') or '[]')
        if not items:
            return JsonResponse({'message': 'warning', 'error_message': 'No items added'})

        payments = json.loads(request.POST.get('payments') or '[]')

        inv_no = generate_serial_number(
            model=wholesale_sales_order_table,
            number_field='inv_no',
            financial_year_field='current_fy',
            financial_year=financial_year,
            branch_id=branch_id,
            prefix="INV_"
        )

        cash_amount = 0.0
        bank_amount = 0.0
        finance_amount = 0.0
        finance_id = 0
        total_charges = 0.0

        for pay in payments:
            pay_type = (pay.get('type') or '').lower()
            amount = float(pay.get('amount') or 0)
            charges = float(pay.get('charges') or 0)
            total_charges += charges

            if pay_type == 'cash':
                cash_amount += amount
            elif pay_type == 'finance':
                finance_amount += amount
                finance_id = int(pay.get('id') or 0)
            elif pay_type in ('bank', 'card', 'exchange'):
                bank_amount += amount

        with transaction.atomic():
            main = wholesale_sales_order_table.objects.create(
                company_id=company_id,
                branch_id=branch_id,
                current_fy=financial_year,
                inv_no=inv_no,
                num_series=generate_num_series(wholesale_sales_order_table),
                inv_date=po_date,
                time=now.time(),
                customer_id=customer_id,
                customer_name=customer_name,
                customer_phone=phone,
                customer_type=customer_type,
                wholesale_type='internal',
                total_quantity=total_qty,
                total_amount=total_amount,
                sub_total=sub_total,
                total_paid=total_paid,
                total_discount=total_discount,
                total_cgst=total_tax_cgst,
                total_sgst=total_tax_sgst,
                cash=cash_amount,
                bank=bank_amount,
                finance_id=finance_id,
                finance_amount=finance_amount,
                charges=total_charges,
                balance=balance,
                remarks=remarks,
                employee_id=employee_id,
                created_on=now,
                updated_on=now,
                created_by=user_id,
                updated_by=user_id,
                status=1,
                is_active=1,
                sales_status='pending' if sales_channel == 'wholesale_internal' else 'approved'
            )

            for item in items:
                wholesale_child_sales_order_table.objects.create(
                    company_id=company_id,
                    current_fy=financial_year,
                    branch_id=branch_id,
                    tm_sales_id=main.id,
                    sales_id=item.get('item_id') or 0,
                    wholesale_type='internal',
                    customer_id=customer_id,
                    imei_no=item.get('imei') or '',
                    ean_number=item.get('ean') or '',
                    subcategory_id=item.get('subcategory_id') or 0,
                    brand_id=item.get('brand_id') or 0,
                    model_id=item.get('model_id') or 0,
                    variant_id=item.get('variant_id') or 0,
                    color_id=item.get('color_id') or 0,
                    quantity=1,
                    rate=item.get('rate') or 0,
                    amount=item.get('net_rate') or 0,
                    tax_percent=item.get('tax_percent') or 0,
                    tax_amount=item.get('tax_amount') or 0,
                    tax_cgst=item.get('tax_cgst') or 0,
                    tax_sgst=item.get('tax_sgst') or 0,
                    discount_percent=item.get('discount_percent') or 0,
                    discount_amount=item.get('discount_amount') or 0,
                    mop=item.get('mop_price') or 0,
                    bop=item.get('bop_price') or 0,
                    wsp=item.get('wsp_price') or 0,
                    fsp=item.get('fsp_price') or 0,
                    db_price=item.get('db_price') or 0,
                    mrp=item.get('rate') or 0,
                    upgrade_id=item.get('upgrade_id') or 0,
                    upgrade_amount=item.get('upgrade_amount') or 0,
                    special_id=item.get('special_id') or 0,
                    special_amount=item.get('special_amount') or 0,
                    purchase_price=item.get('purchase_price') or 0,
                    created_on=now,
                    updated_on=now,
                    created_by=user_id,
                    updated_by=user_id,
                    status=1,
                    is_active=1
                )

        return JsonResponse({'message': 'success'})

    except IntegrityError as e:
        return JsonResponse({'message': 'exception', 'error': str(e)}, status=200)
    except Exception as e:
        return JsonResponse({'message': 'exception', 'error': f'Unexpected error: {e}'}, status=500)

def wholesale_purchase_inward(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        if user_type == 'wholesale':
            branch = selectList(branch_table, order_by='name').exclude(id=branch_id)
            return render(request, 'internal_wholesale/purchase/inward/details.html', {
                'branch': branch,
            })
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")

def ajax_pending_wholesale_inward(request):
    branch_id = request.session.get('branch_id')
    
    # Logic to fetch pending internal wholesale sales
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                s.id, 
                s.inv_no, 
                s.inv_date, 
                s.total_quantity, 
                s.total_amount,
                b.name as branch_name
            FROM tm_sales s
            INNER JOIN branches b ON s.branch_id = b.id
            WHERE s.sales_channel = 'wholesale_internal'
              AND s.customer_id = (SELECT id FROM branches WHERE id = %s)
              AND s.sales_status = 'pending'
              AND s.status = 1 AND s.is_active = 1
        """, [branch_id])
        rows = cursor.fetchall()
    
    data = []
    for row in rows:
        data.append({
            'id': row[0],
            'outward_no': row[1],
            'outward_date': format_date_month_year(row[2]),
            'total_qty': row[3],
            'total_amount': format_amount(row[4]),
            'branch_name': row[5],
            'status': 'Pending'
        })
    
    return JsonResponse({'data': data})

def ajax_get_wholesale_inward_items(request):
    sales_id = request.POST.get('po_id')
    items_qs = child_sales_order_table.objects.filter(tm_sales_id=sales_id, status=1).values()
    
    items = []
    for item in items_qs:
        items.append({
            'subcategory': getItemNameById(sub_category_table, item['subcategory_id']),
            'brand': getItemNameById(brand_table, item['brand_id']),
            'model': getItemNameById(model_table, item['model_id']),
            'variant': getItemNameById(variant_table, item['variant_id']),
            'color': getItemNameById(color_table, item['color_id']),
            'imei': item['imei_no'],
            'rate': item['rate'],
            'quantity': item['quantity'],
            'amount': item['amount']
        })
    return JsonResponse({'data': items})

@transaction.atomic
def approve_wholesale_inward(request):
    sales_id = request.POST.get('id')
    user_id = request.session.get('user_id')
    branch_id = request.session.get('branch_id')
    company_id = request.session.get('company_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
    
    try:
        sales_main = sales_order_table.objects.get(id=sales_id)
        sales_child = child_sales_order_table.objects.filter(tm_sales_id=sales_id, status=1)
        
        now = timezone.localtime(timezone.now())
        
        # 1. Create wholesale_inward_table entry
        inward_no = generate_serial_number(
            model=wholesale_inward_table,
            number_field='inward_no',
            financial_year_field='current_fy',
            financial_year=financial_year,
            branch_id=branch_id
        )
        
        main_obj = wholesale_inward_table.objects.create(
            company_id=company_id,
            inward_date=now.date(),
            inward_no=inward_no,
            time=now.time().strftime('%H:%M:%S'),
            num_series=generate_num_series(wholesale_inward_table),
            current_fy=financial_year,
            branch_id=branch_id,
            supply_branch_id=sales_main.branch_id,
            total_quantity=sales_main.total_quantity,
            sub_total=sales_main.sub_total,
            total_amount=sales_main.total_amount,
            roundoff=0,
            payment_type='credit',
            cash=0,
            bank=0,
            balance=sales_main.total_amount,
            inward_status='completed',
            is_active=1,
            status=1,
            created_on=now,
            updated_on=now,
            created_by=user_id,
            updated_by=user_id
        )
        
        # 2. Create wholesale_child_material_inward_table entries
        for item in sales_child:
            is_demo = 0
            imei = (item.imei_no or "").strip()
            
            if imei:
                # Check is_demo status from supply branch stock records
                # Mirroring logic from approve_outward_transfer in material_in.py
                
                # Check in Purchase Inward
                purchase_item = child_purchase_inward_table.objects.filter(
                    imei_no=imei, branch_id=sales_main.branch_id, status=1, is_active=1
                ).order_by('-created_on').first()
                
                if purchase_item and getattr(purchase_item, 'is_demo', 0) == 1:
                    is_demo = 1
                else:
                    # Check in Material Inward
                    material_item = child_material_inward_table.objects.filter(
                        imei_no=imei, branch_id=sales_main.branch_id, status=1, is_active=1
                    ).order_by('-created_on').first()
                    
                    if material_item and getattr(material_item, 'is_demo', 0) == 1:
                        is_demo = 1
                    else:
                        # Check in Opening Stock
                        opening_item = opening_stock_table.objects.filter(
                            imei_no=imei, branch_id=sales_main.branch_id, status=1, is_active=1
                        ).order_by('-created_on').first()
                        
                        if opening_item and getattr(opening_item, 'is_demo', 0) == 1:
                            is_demo = 1

            wholesale_child_material_inward_table.objects.create(
                company_id=company_id,
                current_fy=financial_year,
                imei_no=item.imei_no,
                ean=item.ean_number, # Note: sales child uses ean_number
                hsn_code=getattr(item, 'hsn_code', ''),
                sales_id=sales_id,
                tm_wholesale_in_id=main_obj.id,
                supply_branch_id=sales_main.branch_id,
                subcategory_id=item.subcategory_id,
                branch_id=branch_id,
                brand_id=item.brand_id,
                model_id=item.model_id,
                variant_id=item.variant_id,
                color_id=item.color_id,
                price_list_id=item.price_id, # Note: sales child uses price_id
                rate=item.rate,
                quantity=item.quantity,
                amount=item.amount,
                db_price=item.db_price,
                mrp=item.mrp,
                fsp=item.fsp,
                wsp=item.wsp,
                mop=item.mop,
                bop=item.bop,
                is_demo=is_demo,
                is_active=1,
                status=1,
                created_on=now,
                updated_on=now,
                created_by=user_id,
                updated_by=user_id
            )
        
        # 3. Update sales status
        sales_main.sales_status = 'approved'
        sales_main.save()
        
        return JsonResponse({'status': 'success', 'message': 'Internal wholesale inwarded successfully'})
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)})

def reject_wholesale_inward(request):
    sales_id = request.POST.get('id')
    remarks = request.POST.get('remarks')
    
    try:
        sales_main = sales_order_table.objects.get(id=sales_id)
        sales_main.sales_status = 'rejected'
        sales_main.remarks = remarks
        sales_main.save()
        return JsonResponse({'status': 'success', 'message': 'Internal wholesale rejected'})
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)})

def ajax_list_wholesale_inward(request):
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
    
    query = Q(status=1, branch_id=branch_id, current_fy=financial_year)
    
    supply_branch = request.POST.get('supply_branch')
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')
    keyword = request.POST.get('keyword_search', '').strip()
    
    if supply_branch:
        query &= Q(supply_branch_id=supply_branch)
    if from_date and to_date:
        query &= Q(inward_date__range=[from_date, to_date])
    if keyword:
        query &= Q(inward_no__icontains=keyword)
        
    data = list(selectList(wholesale_inward_table, query).values())
    
    formatted = []
    for index, item in enumerate(data):
        supply_branch_name = getItemNameById(branch_table, item['supply_branch_id'])
        
        # Fetch original sale info
        child = wholesale_child_material_inward_table.objects.filter(tm_wholesale_in_id=item['id']).first()
        outward_no = '-'
        outward_date = '-'
        if child:
            sale = sales_order_table.objects.filter(id=child.sales_id).first()
            if sale:
                outward_no = sale.inv_no
                outward_date = format_date_month_year(sale.inv_date)

        action_buttons = (
            f'<button type="button" onclick=\'view_data({item["id"]}, "{supply_branch_name}", "{outward_no}", "{outward_date}")\' class="btn btn-outline-primary btn-xs p-1" title="View">'
            f'<i class="fas fa-eye"></i></button> '
        )
        
        formatted.append({
            'id': index + 1,
            'action': action_buttons,
            'outward_no': outward_no,
            'outward_date': outward_date,
            'inward_no': item['inward_no'],
            'inward_date': format_date_month_year(item['inward_date']),
            'supply_branch': supply_branch_name,
            'quantity': item['total_quantity'],
            'amount': format_amount(item['total_amount']),
            'remarks': item['remarks'] or '-',
            'status': '<span class="badge text-bg-success">Active</span>' if item['is_active'] else '<span class="badge text-bg-danger">Inactive</span>'
        })
        
    return JsonResponse({'data': formatted})

def ajax_view_tx_wholesale_inward(request):
    tm_id = request.POST.get('po_id')
    items_qs = wholesale_child_material_inward_table.objects.filter(tm_wholesale_in_id=tm_id, status=1).values()
    
    items = []
    for index, item in enumerate(items_qs):
        items.append({
            'id': index + 1,
            'subcategory_name': getItemNameById(sub_category_table, item['subcategory_id']),
            'brand_name': getItemNameById(brand_table, item['brand_id']),
            'model_name': getItemNameById(model_table, item['model_id']),
            'variant_name': getItemNameById(variant_table, item['variant_id']),
            'color_name': getItemNameById(color_table, item['color_id']),
            'imei_no': item['imei_no'],
            'rate': format_amount(item['rate']),
            'qty': item['quantity'],
            'amount': format_amount(item['amount'])
        })
    return JsonResponse({'items': items})


# Purchase Order



def ajax_wholesale_order_request(request):
    role_id = request.session.get('role_id')
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
             
    has_access, error_message = check_user_access(role_id, 'purchase_order', "read")

    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view Purchase Order details'})
    
    supplier_id = request.POST.get('supplier')
    supply_id = request.POST.get('branch')
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')
    keyword = request.POST.get('keyword_search', '').strip()

    query = Q(status=1, branch_id=branch_id, current_fy = financial_year)

    if supplier_id:
        query &= Q(supplier_id=supplier_id)

    if supply_id:
        query &= Q(supply_branch_id=supply_id)


    if from_date and to_date:
        query &= Q(po_date__range=[from_date, to_date])

    if keyword:
        query &= Q(po_no__icontains=keyword)
       
    
    
    data = list(selectList(wholesale_po_table, query).values())

    formatted = []
    for index, item in enumerate(data):       
       
        formatted.append({
            'id': index + 1,
            'action': '<button type="button" onclick="edit_data(\'{}\')" class="btn btn-outline-success btn-xs p-1"><i class="fas fa-edit"></i></button> \
                      <button type="button" onclick="delete_data(\'{}\')" class="btn btn-outline-danger btn-xs p-1"> <i class="fas fa-trash-alt"></i></button>'.format(item['id'], item['id']), 
            'po_no': item['po_no'] if item['po_no'] else '-', 
            'po_date': format_date_month_year(item['po_date']),
            'supply_branch': getItemNameById(branch_table, item['supply_branch_id']) if item['supply_branch_id'] else '-', 
            'quantity': item['total_quantity'] if item['total_quantity'] else '-', 
            'amount': format_amount(item['total_amount']),            
            'remarks': item['remarks'] if item['remarks'] else '-', 
            'status': '<span class="badge text-bg-success">Active</span>' if item['is_active'] else '<span class="badge text-bg-danger">Inactive</span>'
        })


    return JsonResponse({'data': formatted})





def add_wholesale_po(request):
    if request.method == 'POST':
        try:

            company_id = request.session.get('company_id')
            branch_id = request.session.get('branch_id')
            user_id = request.session.get('user_id')
            fyf_name = request.session.get('fyf')
            financial_year = calculate_financial_year(fyf_name)
            role_id = request.session.get('role_id')
            date_times = timezone.localtime(timezone.now())

           

            # Permission check
            has_access = check_user_access(role_id, 'purchase_order', "create")

            if not has_access:
                print("⛔ Permission denied")
                return JsonResponse({
                    'message': 'permission',
                    'error_message': 'You do not have permission to add Transfer Request details'
                })

            po_date = request.POST.get('po_date')
            supply_branch_id = request.POST.get('supply_branch_id') or 0
            remarks = request.POST.get('description')
            employee_id = request.POST.get('employee_id') or user_id
            total_qty = request.POST.get('total_quantity')
            sub_total = request.POST.get('sub_total')
            grand_total = request.POST.get('grand_total')
            round_off = request.POST.get('round_off') or 0
            items_json = request.POST.get('items')


            items = json.loads(items_json)

            purchase_status = 'pending'

            negative_check_fields = ['total_quantity', 'sub_total', 'grand_total']
            has_negative, error_message = has_negative_values(request.POST, negative_check_fields)

            if has_negative:
                return JsonResponse({'message': 'infos', 'error_message': error_message})

            if not supply_branch_id:
                return JsonResponse({
                    'message': 'warning',
                    'error_message': 'Please select either a Supplier or a Supply Store.'
                })

            if not items:
                return JsonResponse({
                    'message': 'warning',
                    'error_message': 'Please add at least one item to the order.'
                })

            generated_po_no = generate_serial_number(
                model=wholesale_po_table,
                number_field='po_no',
                financial_year_field='current_fy',
                financial_year=financial_year,
                branch_id=branch_id,
                prefix="PO_"
            )


            with transaction.atomic():

                main_obj = wholesale_po_table.objects.create(
                    company_id=company_id,
                    po_date=po_date,
                    time=date_times.strftime('%H:%M:%S'),
                    po_no=generated_po_no,
                    num_series=generate_num_series(wholesale_po_table),
                    current_fy=financial_year,
                    branch_id=branch_id,
                    supply_branch_id=supply_branch_id,
                    remarks=remarks if remarks else 'Purchase Order requested',
                    total_quantity=total_qty,
                    sub_total=sub_total,
                    total_amount=grand_total,
                    roundoff=round_off,
                    po_status=purchase_status,
                    is_active=1,
                    status=1,
                    created_on=date_times,
                    updated_on=date_times,
                    created_by=user_id,
                    updated_by=user_id,
                    employee_id=employee_id
                )


                for idx, item in enumerate(items, start=1):

                    wholesale_tx_po_table.objects.create(
                        company_id=company_id,
                        current_fy=financial_year,
                        branch_id=branch_id,
                        supply_branch_id=supply_branch_id,
                        tm_po_id=main_obj.id,
                        subcategory_id=item['subcategory_id'] or 0,
                        brand_id=item['brand_id'] or 0,
                        model_id=item['model_id'] or 0,
                        variant_id=item['variant_id'] or 0,
                        color_id=item['color_id'] or 0,
                        ean=item['ean_number'] or '',
                        hsn_code=item['hsn_code'] or '',
                        rate=item['rate'],
                        quantity=item['quantity'],                      
                        amount=item['amount'],
                        mrp=item['mrp'],
                        fsp=item['fsp'],
                        wsp=item['wsp'],
                        mop=item['mop'],
                        bop=item['bop'],
                        db_price=item['db_price'],
                        is_active=1,
                        status=1,
                        created_on=date_times,
                        updated_on=date_times,
                        created_by=user_id,
                        updated_by=user_id
                    )


            return JsonResponse({'message': 'success'})

        except IntegrityError as e:
            return JsonResponse({
                'message': 'exception',
                'error': 'Database error occurred: ' + str(e)
            }, status=500)

        except Exception as e:
            return JsonResponse({
                'message': 'exception',
                'error': 'An error occurred: ' + str(e)
            }, status=500)




def ajax_wholesale_po_edit(request):  
    role_id = request.session.get('role_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
    
    has_access, error_message = check_user_access(role_id, 'purchase_order', "read")
    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view the Purchase Order details'})

    po_id = request.POST.get('po_id')
    data = list(selectList(wholesale_tx_po_table,{'tm_po_id':po_id,'current_fy':financial_year}).values())
    
   
    formatted = []
    for index, item in enumerate(data):
        formatted.append({
            'id': index + 1,
            'tx_id': item['id'],
            'action': '<button type="button" onclick="delete_data(\'{}\')" class="btn btn-outline-danger btn-xs p-1"> <i class="fas fa-trash-alt"></i></button>'.format(item['id']),
            'ean': item['ean'] if item['ean'] else '-',  # ✅ With SKU
            'subcategory_id': item['subcategory_id'],
            'brand_id': item['brand_id'],
            'model_id': item['model_id'],
            'variant_id': item['variant_id'],
            'color_id': item['color_id'],
            'subcategory': getItemNameById(sub_category_table, item['subcategory_id']) if item['subcategory_id'] else '-',
            'brand': getItemNameById(brand_table, item['brand_id']) if item['brand_id'] else '-',
            'model': getItemNameById(model_table, item['model_id']) if item['model_id'] else '-',
            'variant': getItemNameById(variant_table, item['variant_id']) if item['variant_id'] else '-',
            'color': getItemNameById(color_table, item['color_id']) if item['color_id'] else '-',
            'rate': format_amount(item['rate']) if item['rate'] else '-',
            'qty': item['quantity'] if item['quantity'] else '-',
            'hsn_code': item['hsn_code'] if item['hsn_code'] else '-',
            'amt': format_amount(item['amount']) if item['amount'] else '-',
        })

    return JsonResponse({'data': formatted})





def update_wholesale_po(request):
    if request.method == 'POST':
        try:
            role_id = request.session.get('role_id')
            has_access, error_message = check_user_access(role_id, 'purchase_order', "update")
            if not has_access:
                return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to update the Purchase Order details'})

            data = json.loads(request.body)

            def _to_float(value, default=0.0):
                try:
                    if value in (None, ''):
                        return default
                    return float(value)
                except (TypeError, ValueError):
                    return default

            def _to_int(value, default=0):
                try:
                    if value in (None, ''):
                        return default
                    return int(value)
                except (TypeError, ValueError):
                    return default

            user_id = request.session.get('user_id')
            po_id = data.get('tm_id')
            ean_number = data.get('ean_number')
            subcategory_id = data.get('subcategory_id')
            hsn_code = data.get('hsn_code')
            db_price = _to_float(data.get('db_price'))
            fsp = _to_float(data.get('fsp'))
            wsp = _to_float(data.get('wsp'))
            mop = _to_float(data.get('mop'))
            bop = _to_float(data.get('bop'))
            mrp = _to_float(data.get('mrp'))
            brand_id = data.get('brand_id')
            model_id = data.get('model_id')
            variant_id = data.get('variant_id')
            color_id = data.get('color_id')
            ean_number = data.get('ean_number')
            quantity = _to_int(data.get('quantity', 1), 1)
            rate = _to_float(data.get('rate'))
            edit_id = data.get('tx_id', 0)
            date_times = timezone.localtime(timezone.now())
            amount = quantity * rate

            negative_check_fields = ['rate', 'quantity', 'amount']
            has_negative, error_message = has_negative_values(request.POST, negative_check_fields)
            if has_negative:
                return JsonResponse({'message': 'infos', 'error_message': error_message})

           
            purchase = select_row(wholesale_tx_po_table, {'id': po_id})
            if not purchase:
                return JsonResponse({'success': False, 'message': 'Purchase Order not found.'})

            

            # ✅ Proceed with child table logic
            if edit_id and int(edit_id) != 0:
                item_row = wholesale_tx_po_table.objects.filter(id=edit_id, tm_po_id=po_id).first()
                if item_row:
                    item_row.subcategory_id = subcategory_id
                    item_row.ean = ean_number
                    item_row.brand_id = brand_id
                    item_row.model_id = model_id
                    item_row.variant_id = variant_id
                    item_row.color_id = color_id
                    item_row.quantity = quantity
                    item_row.rate = rate
                    item_row.hsn_code = hsn_code
                    item_row.db_price = db_price
                    item_row.fsp = fsp
                    item_row.wsp = wsp
                    item_row.mop = mop
                    item_row.bop = bop
                    item_row.mrp = mrp
                    item_row.ean = ean_number
                    item_row.amount = amount
                    item_row.updated_on = date_times
                    item_row.updated_by = user_id
                    item_row.save()
                    action = 'edited'
                else:
                    return JsonResponse({'success': False, 'message': 'Item not found for editing.'})
            else:
                existing_item = wholesale_tx_po_table.objects.filter(
                    tm_po_id=po_id, ean=ean_number,subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, rate=rate
                ).first()

                if existing_item:
                    existing_item.quantity += quantity
                    existing_item.amount = existing_item.quantity * existing_item.rate
                    existing_item.updated_on = date_times
                    existing_item.updated_by = user_id
                    existing_item.save()
                    action = 'updated'
                else:
                    wholesale_tx_po_table.objects.create(
                        company_id=purchase.company_id,
                        current_fy=purchase.current_fy,
                        branch_id=purchase.branch_id,
                        supply_branch_id=purchase.supply_branch_id,
                        tm_po_id=po_id,
                        subcategory_id=subcategory_id,
                        brand_id=brand_id,
                        model_id=model_id,
                        variant_id=variant_id,
                        color_id=color_id,
                        ean=ean_number,
                        quantity=quantity,
                        rate=rate,
                        hsn_code=hsn_code,
                        db_price=db_price,
                        fsp=fsp,
                        wsp=wsp,
                        mop=mop,
                        bop=bop,
                        mrp=mrp,
                        amount=amount,
                        remarks='',
                        created_on=date_times,
                        updated_on=date_times,
                        created_by=user_id,
                        updated_by=user_id
                    )
                    action = 'added'

            # ✅ Update parent totals (tm_po)
            totals = wholesale_tx_po_table.objects.filter(tm_po_id=po_id).aggregate(
                total_qty=Sum('quantity'),
                total_amt=Sum('amount')
            )

            purchase.total_quantity = totals['total_qty'] or 0
            purchase.total_amount = totals['total_amt'] or 0
            purchase.updated_on = date_times
            purchase.updated_by = user_id
            purchase.save()

            return JsonResponse({'success': True, 'action': action})

        except Exception as e:
            return JsonResponse({'success': False, 'message': str(e)})

    return JsonResponse({'success': False, 'message': 'Invalid request'})


def delete_wholesale_tx_po(request):
    if request.method != 'POST':
        return JsonResponse({'message': 'Invalid request method'})

    role_id = request.session.get('role_id')
    has_access, error_message = check_user_access(role_id, 'transfer_po', 'delete')

    if not has_access:
        return JsonResponse({
            'message': 'permission',
            'error_message': 'You do not have permission to delete Transfer Request details'
        })

    data_id = request.POST.get('id')
    if not data_id:
        return JsonResponse({'message': 'no such data'})

    try:
        # 🔹 Fetch request item
        request_item = wholesale_tx_po_table.objects.filter(
            id=data_id,
            status=1
        ).first()

        if not request_item:
            return JsonResponse({'message': 'no such data'})

        po_id = request_item.tm_po_id

        # 🔒 Rule: If any outward exists → block delete
       

        # 🔹 Parent PO must exist
        parent_po = wholesale_tx_po_table.objects.filter(
            id=po_id,
            status=1
        ).first()

        if not parent_po:
            return JsonResponse({'message': 'no such data'})

        # 🔹 Soft delete
        wholesale_tx_po_table.objects.filter(id=data_id).update(status=0)

        # 🔹 Update summary after delete
        update_summary(
            wholesale_po_table,
            wholesale_tx_po_table,
            'tm_po_id',
            po_id
        )

        return JsonResponse({'message': 'yes'})

    except Exception as e:
        return JsonResponse({
            'message': 'exception',
            'error': str(e)
        })



def edit_wholesale_po(request):
    if request.method == "POST":
        data = wholesale_tx_po_table.objects.filter(id=request.POST.get('id'))    
    return JsonResponse(data.values()[0])


# *********************************************************************************************************************************
# Wholesale Opening Stock Import / Sample
# *********************************************************************************************************************************

def download_wholesale_sample_excel(request):
    if request.session.get('user_type') != 'wholesale':
        return JsonResponse({'message': 'Unauthorized'}, status=403)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Wholesale Stock Sample"

    headers = [
        'S.NO', 'Sub Category', 'Brand', 'Model', 'Variant', 'Color',
        'IMEI', 'Purchase Price', 'MRP', 'MOP', 'BOP'
    ]
    ws.append(headers)

    for cell in ws[1]:
        cell.font = Font(bold=True)

    items = selectList(item_table, order_by='sku')
    sno = 1
    for item in items:
        sub_category_name = getItemNameById(sub_category_table, item.sub_category_id) if item.sub_category_id else ''
        brand_name = getItemNameById(brand_table, item.brand_id) if item.brand_id else ''
        model_name = getItemNameById(model_table, item.model_id) if item.model_id else ''
        variant_name = getItemNameById(variant_table, item.variant_id) if item.variant_id else ''
        color_name = getItemNameById(color_table, item.color_id) if item.color_id else ''

        ws.append([
            sno,
            sub_category_name,
            brand_name,
            model_name,
            variant_name,
            color_name,
            '',
            item.db_price,
            item.mrp,
            item.mop_price,
            item.bop_price,
        ])
        sno += 1

    today = format_date_month_year(date.today())
    filename = f'wholesale_opening_stock_{today}.xlsx'

    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename="{filename}"'
    wb.save(response)
    return response


def generate_wholesale_opening_batch_no(branch_id, current_fy, prefix="OPN", padding=3):
    last_entry = wholesale_opening_stock_table.objects.filter(
        branch_id=branch_id,
        current_fy=current_fy,
        status=1
    ).order_by('-id').first()

    if last_entry and last_entry.stock_no:
        import re
        match = re.search(rf"{prefix}(\d+)$", last_entry.stock_no)
        if match:
            new_number = int(match.group(1)) + 1
        else:
            new_number = 1
    else:
        new_number = 1

    return f"{prefix}{str(new_number).zfill(padding)}"


def get_master_id_by_name(model, name_field, name_value):
    if not name_value:
        return None
    obj = model.objects.filter(
        **{name_field: name_value},
        status=1,
        is_active=1
    ).first()
    return obj.id if obj else None


def wholesale_imei_exists(imei, branch_id):
    if wholesale_opening_stock_table.objects.filter(
        imei_no=imei,
        branch_id=branch_id,
        status=1,
        is_active=1
    ).exists():
        return True

    if wholesale_child_material_inward_table.objects.filter(
        imei_no=imei,
        branch_id=branch_id,
        status=1,
        is_active=1
    ).exists():
        return True

    return False


def import_wholesale_stock(request):
    if request.method != 'POST' or 'file' not in request.FILES:
        return JsonResponse({'message': 'Invalid request'}, status=400)

    if request.session.get('user_type') != 'wholesale':
        return JsonResponse({'message': 'Unauthorized'}, status=403)

    try:
        excel_file = request.FILES['file']

        company_id = request.session.get('company_id')
        branch_id = request.session.get('branch_id')
        user_id = request.session.get('user_id')
        is_ho = request.session.get('is_ho')
        fyf_name = request.session.get('fyf')
        current_fy = calculate_financial_year(fyf_name)

        if not all([company_id, branch_id, user_id, current_fy]):
            return JsonResponse({'message': 'Session data missing'}, status=400)

        date_times = timezone.localtime(timezone.now())

        # Save file under MEDIA/wholesale/
        filename = default_storage.save(f"wholesale/{excel_file.name}", excel_file)
        file_path = os.path.join(settings.MEDIA_ROOT, filename)

        stock_log = wholesale_stock_log_table.objects.create(
            company_id=company_id,
            branch_id=branch_id,
            current_fy=current_fy,
            uploaded_by=user_id,
            uploaded_on=date_times,
            excel_file=filename,
            item_type='opening_stock',
            created_on=date_times,
            updated_on=date_times,
            created_by=user_id,
            updated_by=user_id,
            status=1,
            is_active=1
        )

        wb = openpyxl.load_workbook(file_path)
        sheet = wb.active
        rows = list(sheet.iter_rows(min_row=2, values_only=True))

        opening_entries = []
        skipped_rows = []
        skipped_imeis = []

        for row_no, row in enumerate(rows, start=2):
            try:
                # Supports both layouts:
                # 13 cols: ... MRP, FSP, WSP, MOP, BOP
                # 11 cols: ... MRP, MOP, BOP
                if len(row) >= 13:
                    (
                        _sno, sub_category, brand, model, variant, color, imei,
                        purchase_price, mrp, fsp, wsp, mop, bop
                    ) = row[:13]
                elif len(row) >= 11:
                    (
                        _sno, sub_category, brand, model, variant, color, imei,
                        purchase_price, mrp, mop, bop
                    ) = row[:11]
                    fsp = 0
                    wsp = 0
                else:
                    skipped_rows.append(f"Row {row_no}: column mismatch")
                    continue
            except Exception:
                skipped_rows.append(f"Row {row_no}: column mismatch")
                continue

            if not imei:
                skipped_rows.append(f"Row {row_no}: IMEI missing")
                continue

            imei = str(imei).strip()
            if ',' in imei:
                skipped_imeis.append(f"{imei} (Row {row_no}: multiple IMEIs)")
                continue

            if wholesale_imei_exists(imei, branch_id):
                skipped_imeis.append(f"{imei} (already exists)")
                continue

            subcategory_id = get_master_id_by_name(sub_category_table, 'name', sub_category)
            brand_id = get_master_id_by_name(brand_table, 'name', brand)
            model_id = get_master_id_by_name(model_table, 'name', model)
            variant_id = get_master_id_by_name(variant_table, 'name', variant)
            color_id = get_master_id_by_name(color_table, 'name', color)

            missing = []
            if not subcategory_id: missing.append('SubCategory')
            if not brand_id: missing.append('Brand')
            if not model_id: missing.append('Model')
            if not variant_id: missing.append('Variant')
            if not color_id: missing.append('Color')
            if missing:
                skipped_imeis.append(f"{imei} (Row {row_no}: missing {', '.join(missing)})")
                continue

            generated_no = generate_wholesale_opening_batch_no(
                branch_id=branch_id,
                current_fy=current_fy,
                prefix="WOPN",
                padding=3
            )
            branch_code = 'BM' if is_ho == 1 else str(branch_id).zfill(3)
            stock_no = f"{branch_code}/{generated_no}"

            data = select_row(
                item_table,
                {
                    'sub_category_id': subcategory_id,
                    'brand_id': brand_id,
                    'model_id': model_id,
                    'variant_id': variant_id,
                    'color_id': color_id
                }
            )
            if not data:
                skipped_imeis.append(f"{imei} (Row {row_no}: item master not found)")
                continue

            entry = wholesale_opening_stock_table(
                company_id=company_id,
                branch_id=branch_id,
                current_fy=current_fy,
                ean=data.ean,
                hsn_code=getattr(data, 'hsn_code', '') or '',
                date=format_date(date_times),
                time=format_time(date_times),
                stock_no=stock_no,
                subcategory_id=subcategory_id,
                brand_id=brand_id,
                model_id=model_id,
                variant_id=variant_id,
                color_id=color_id,
                imei_no=imei,
                quantity=1,
                db_price=float(purchase_price or 0),
                mrp=float(mrp or 0),
                fsp=float(fsp or 0),
                wsp=float(wsp or 0),
                mop=float(mop or 0),
                bop=float(bop or 0),
                employee_id=user_id,
                stock_log_id=stock_log.id,
                created_on=date_times,
                updated_on=date_times,
                created_by=user_id,
                updated_by=user_id,
                status=1,
                is_active=1
            )
            opening_entries.append(entry)

        if opening_entries:
            wholesale_opening_stock_table.objects.bulk_create(opening_entries, batch_size=500)

        return JsonResponse({
            'message': f'{len(opening_entries)} opening stock records imported successfully',
            'skipped_rows': skipped_rows or None,
            'skipped_imeis': skipped_imeis or None
        })
    except Exception as e:
        return JsonResponse({'message': str(e)}, status=500)


# *********************************************************************************************************************************