from django.shortcuts import render
import json
from django.conf import settings
from django.shortcuts import render
from inventory.models import *
from expenses.models import *
from store.models import *
from company.models import *
from masters.models import *
from supplier.models import *
from material.models import *
from scheme.models import *
from django.db import IntegrityError, transaction
import hashlib
import os
from django.conf import settings
from datetime import datetime
from django.utils import timezone
from django.db.models import Max, F, Q,Sum
import base64
from django.shortcuts import render
from io import BytesIO
from django.conf import settings
from django.template.loader import get_template
from django.http import HttpResponse,HttpResponseRedirect,JsonResponse,FileResponse
from django.shortcuts import get_object_or_404
from django.templatetags.static import static
from xhtml2pdf import pisa
from num2words import num2words
from django.contrib.staticfiles import finders  
from common.utils import *
from django.db import connection
import openpyxl
from openpyxl.styles import Font
from django.http import HttpResponse
from django.core.files.storage import default_storage
from claim_collect_upgrade.models import *
from voucher.models import *
from decimal import Decimal
from slab.models import *
# **********************************************************************************************************************************

def store_ledger(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')                   
        if user_type == 'stores':
            is_ho = request.session.get('is_ho')
            customer = selectList(customer_table, {'branch_id':branch_id}, order_by='name')
            if is_ho == 1:
                branch = selectList(branch_table, order_by='name')
            else:
                branch = selectList(branch_table, {'id': branch_id})
            item = selectList(item_table)
            return render(request, 'store_ledger.html',{'customer':customer,'branch':branch,'item':item, 'is_ho': is_ho, 'logged_branch_id': branch_id})
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


from django.http import JsonResponse
from django.db.models import Q, Sum
from datetime import datetime
def format_outstanding(amount):   
    amount = round(amount or 0, 2)
    if amount == 0:
        return amount, ''
    return amount, 'credit' if amount > 0 else 'debit'
def format_outstanding_with_type(amount):
    amount = round(amount or 0, 2)

    if amount == 0:
        return ''

    if amount > 0:
        return (
            f"{amount} "
            f"<span class='text-danger fw-semibold'>(Cr)</span>"
        )
    else:
        return (
            f"{abs(amount)} "
            f"<span class='text-success fw-semibold'>(Dr)</span>"
        )
    
    from django.http import JsonResponse
from django.db.models import Q, Sum, F
from datetime import datetime
from django.http import JsonResponse
from django.db.models import Sum, F
from datetime import datetime

# -------------------------
# Helper function to format Cr/Dr amount
# -------------------------
def format_outstanding_with_type(amount):
    amount = round(amount or 0, 2)
    if amount == 0:
        return ''
    if amount > 0:
        return f"{amount} <span class='text-danger fw-semibold'>(Cr)</span>"
    else:
        return f"{abs(amount)} <span class='text-success fw-semibold'>(Dr)</span>"

def append_transaction(ledger, row_id, receipt, date_str, tx_type, particulars,
                       credit_amount, debit_amount, running_outstanding,
                       employee_name):
    
    outstanding_display = format_outstanding_with_type(running_outstanding)

    ledger.append({
        'id': row_id,
        'receipt': receipt,
        'date': date_str,
        'type': tx_type,
        'particulars': particulars,
        'credit': credit_amount if credit_amount else '',
        'debit': debit_amount if debit_amount else '',
        'outstanding': outstanding_display,
        'employee': employee_name
    })

    return ledger

def store_ledger_view(request):
    from_date = request.POST.get('from_date')
    to_date   = request.POST.get('to_date')
    is_ho = request.session.get('is_ho')
    # Use branch from POST
    login_branch_id = request.POST.get('branch_id')
    
    if not login_branch_id:
        # If HO hasn't selected a branch, return empty
        if is_ho == 1:
            return JsonResponse({'data': [], 'total_outstanding': 0.00})
        # For regular branch, use session
        login_branch_id = request.session.get('branch_id')

    if not login_branch_id:
        return JsonResponse({'data': [], 'total_outstanding': 0.00})
    
    login_branch_id = int(login_branch_id)
    role_id = request.session.get('role_id')
    has_access, error_message = check_user_access(role_id, 'store_ledger', "read")
    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view the Store Ledger details'})

    from_date = datetime.strptime(from_date, '%Y-%m-%d').date()
    to_date   = datetime.strptime(to_date, '%Y-%m-%d').date()

    ledger = []
    row_id = 1

    # -------------------------
    # Helper list to store datetime for sorting
    # -------------------------
    ledger_tmp = []

    # -------------------------
    # 1) OPENING BALANCE
    # -------------------------
    ob = branch_table.objects.filter(
        id=login_branch_id,
        status=1,
    ).first()
    print(ob)

    
    ledger_tmp.append({
        'row_id': row_id,
        'receipt': '-',
        'date_obj': datetime.combine(from_date, datetime.min.time()),
        'tx_type': 'Opening Balance',
        'particulars': 'Opening Balance',
        'credit_amount': ob.captial if ob else 0.00,
        'debit_amount': 0.00,
        'employee_id': '',
    })
    row_id += 1

    # -------------------------
    # 2) PRICE DROP TRANSACTIONS   
    # -------------------------
    price_qs = price_history_table.objects.filter(
        Q(branch_id=login_branch_id) | Q(id__in=tx_price_history_table.objects.filter(supply_branch_id=login_branch_id, status=1, is_active=1).values_list('tm_price_id', flat=True)),
        date__range=[from_date, to_date],
        status=1,
        is_active=1
    ).distinct()

    for tm in price_qs:
        date_obj = datetime.combine(tm.date, tm.time or datetime.min.time())
        supplier = getItemNameById(supplier_table, tm.supplier_id)
        
        # Creator Side (HO / Branch who created it)
        if tm.branch_id == login_branch_id:
            debit_amount = tm.debit_note or 0
            if debit_amount > 0:
                ledger_tmp.append({
                    'row_id': row_id,
                    'receipt': tm.price_no,
                    'date_obj': date_obj,
                    'tx_type': 'Price Drop',
                    'particulars': f"{supplier} - <small class='text-primary'>(Price Drop)</small>",
                    'credit_amount': 0,
                    'debit_amount': round(Decimal(str(debit_amount)), 2),
                    'employee_id': tm.employee_id
                })
                row_id += 1
        
        # Against Side (Store affected by Price Drop)
        # Check tx table if login_branch is in supply_branch_id
        tx_price_summary = tx_price_history_table.objects.filter(
            tm_price_id=tm.id, 
            supply_branch_id=login_branch_id, 
            status=1, 
            is_active=1
        ).aggregate(
            fsp_sum=Sum('credit_fsp'),
            wsp_sum=Sum('credit_wsp')
        )
        total_credit = (tx_price_summary['fsp_sum'] or 0) + (tx_price_summary['wsp_sum'] or 0)
        if total_credit > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': tm.price_no,
                'date_obj': date_obj,
                'tx_type': 'Price Drop',
                'particulars': f"Credit Note - <small class='text-primary'>Price Drop Effect</small>",
                'credit_amount': round(Decimal(str(total_credit)), 2),
                'debit_amount': 0,
                'employee_id': tm.employee_id
            })
            row_id += 1

    # -------------------------
    # 3) PURCHASE INWARD TRANSACTIONS
    # -------------------------
    purchase_qs = purchase_inward_table.objects.filter(
        pu_date__range=[from_date, to_date],
        status=1,
        is_active=1,
        branch_id=login_branch_id
    )

    for pu in purchase_qs:
        supplier_name = getItemNameById(supplier_table, pu.supplier_id)
        date_obj = datetime.combine(pu.pu_date, pu.time or datetime.min.time())
        debit_amount = pu.grand_total or 0
        if debit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': pu.pu_no,
                'date_obj': date_obj,
                'tx_type': 'Purchase Inward',
                'particulars': supplier_name,
                'credit_amount': 0,
                'debit_amount': round(Decimal(str(debit_amount)), 2),
                'employee_id': pu.employee_id,
            })
            row_id += 1

    # -------------------------
    # 4) PURCHASE RETURN TRANSACTIONS (Approved Only)
    # -------------------------
    pr_qs = purchase_return_table.objects.filter(
        pr_date__range=[from_date, to_date],
        status=1,
        is_active=1,
        branch_id=login_branch_id,
        pr_status__iexact='approved'
    )

    for pr in pr_qs:
        supplier_name = getItemNameById(supplier_table, pr.supplier_id)
        date_obj = datetime.combine(pr.pr_date, pr.time or datetime.min.time())
        credit_amount = pr.total_amount or 0
        if credit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': pr.pr_no,
                'date_obj': date_obj,
                'tx_type': 'Purchase Return',
                'particulars': supplier_name,
                'credit_amount': round(Decimal(str(credit_amount)), 2),
                'debit_amount': 0,
                'employee_id': pr.employee_id,
            })
            row_id += 1

    # -------------------------
    # 5) TRANSFER REQUEST (TM MATERIAL PO)
    # -------------------------
    tr_qs = tm_material_request_table.objects.filter(
        po_date__range=[from_date, to_date],
        status=1,
        is_active=1,
        branch_id=login_branch_id
    )

    for tr in tr_qs:
        date_obj = datetime.combine(tr.po_date, tr.time or datetime.min.time())
        debit_amount = tr.total_amount or 0
        if debit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': tr.po_no,
                'date_obj': date_obj,
                'tx_type': 'Transfer Request',
                'particulars': f"Request to: {getItemNameById(branch_table, tr.supply_branch_id)}",
                'credit_amount': 0,
                'debit_amount': round(Decimal(str(debit_amount)), 2),
                'employee_id': tr.employee_id,
            })
            row_id += 1

    # -------------------------
    # 6) CLAIM TRANSACTIONS
    # -------------------------
    claim_qs = tm_claim_table.objects.filter(
        Q(branch_id=login_branch_id) | Q(id__in=tx_claim_table.objects.filter(supply_branch_id=login_branch_id, status=1, is_active=1).values_list('tm_claim_id', flat=True)),
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    ).distinct()

    for claim in claim_qs:
        date_obj = datetime.combine(claim.date, claim.time or datetime.min.time())
        
        # Creator Side
        if claim.branch_id == login_branch_id:
            # "no need to show on ledger" if created branch also have claim? 
            # The user said: "if created branch also have claim no need to show on ledger"
            # I interpret this as "skip if it's purely internal to this branch" but usually a claim is AGAINST another.
            # I will show Debit for Creator.
            debit_amount = claim.debit_amount or 0
            if debit_amount > 0:
                ledger_tmp.append({
                    'row_id': row_id,
                    'receipt': claim.claim_no or claim.debit_note_no,
                    'date_obj': date_obj,
                    'tx_type': 'Claim',
                    'particulars': f"Claim Created - <small class='text-primary'>{claim.scheme_type}</small>",
                    'credit_amount': 0,
                    'debit_amount': round(Decimal(str(debit_amount)), 2),
                    'employee_id': claim.employee_id
                })
                row_id += 1
        
        # Against Side
        tx_claims = tx_claim_table.objects.filter(tm_claim_id=claim.id, supply_branch_id=login_branch_id, status=1, is_active=1)
        total_credit = tx_claims.aggregate(total=Sum('amount'))['total'] or 0
        if total_credit > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': claim.claim_no or claim.debit_note_no,
                'date_obj': date_obj,
                'tx_type': 'Claim',
                'particulars': f"Claim Received - <small class='text-primary'>{claim.scheme_type}</small>",
                'credit_amount': round(Decimal(str(total_credit)), 2),
                'debit_amount': 0,
                'employee_id': claim.employee_id
            })
            row_id += 1


    # -------------------------
    # 7) CONTRA SALES
    # -------------------------
    contra_qs = contra_sales_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )

    for contra in contra_qs:
        date_obj = datetime.combine(contra.date, contra.time or datetime.min.time())
        amount = Decimal(str(contra.amount or 0))
        if amount <= 0: continue

        if contra.payment_type == 'cash_to_bank':
            # Cash out, Bank in -> Credit for bank inward? 
            # Usually internal, but for consistency:
            particulars = f"Contra: Cash to {getItemNameById(bank_table, contra.bank_id)}"
            credit_amount = amount
            debit_amount = 0
        else:
            particulars = f"Contra: Bank to Cash"
            credit_amount = 0
            debit_amount = amount

        ledger_tmp.append({
            'row_id': row_id,
            'receipt': contra.contra_no,
            'date_obj': date_obj,
            'tx_type': 'Contra',
            'particulars': particulars,
            'credit_amount': round(amount, 2),
            'debit_amount': round(amount, 2),
            'employee_id': contra.employee_id
        })
        row_id += 1


    # -------------------------
    # 8) SALES TRANSACTIONS
    # -------------------------
    sales_qs = sales_order_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        sales_status='approved',
        inv_date__range=[from_date, to_date]
    )

    for sale in sales_qs:
        date_obj = datetime.combine(sale.inv_date, sale.time or datetime.min.time())
        debit_amount = Decimal(str(sale.total_amount or 0))
        if debit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': sale.inv_no,
                'date_obj': date_obj,
                'tx_type': 'Sales',
                'particulars': f"{sale.customer_name}- <span class='text-danger'> {sale.customer_type}</span><small class='text-primary'> ({sale.customer_phone})</small>",
                'credit_amount': 0,
                'debit_amount': round(debit_amount, 2),
                'employee_id': sale.employee_id
            })
            row_id += 1

   
    # -------------------------
    # 7) SALES TRANSACTIONS (TX TRANSACTION)
    # -------------------------
    is_ho = request.session.get('is_ho', 0)  # 1 = HO, 0 = branch
    transactions_qs = transaction_table.objects.filter(
        tm_sales_id__in=[s.id for s in sales_qs],
        is_active=1,
        status=1,
        date__range=[from_date, to_date]
    )

    for tx in transactions_qs:
        sale = select_row(sales_order_table, {'id': tx.tm_sales_id})
        employee = getItemNameById(employee_table, sale.employee_id)
        date_obj = datetime.combine(tx.date, tx.time or datetime.min.time())

        payment_type = (tx.payment_type or '').lower()

        # ----------------------
        # Particulars
        # ----------------------
        if payment_type == 'cash':
            particulars = "Cash Payment - <small class='text-primary'>Cash</small>"

        elif payment_type == 'bank':
            name = getItemNameById(bank_table, tx.bank_id)
            particulars = f"{name} - <small class='text-primary'>Bank</small>"

        elif payment_type == 'exchange':
            particulars = f"Exchange Payment - <small class='text-primary'>{tx.reference_no}</small>"

        elif payment_type == 'card':
            name = getItemNameById(finance_table, tx.card_id)
            particulars = f"{name} - <small class='text-primary'>Card</small>"

        elif payment_type == 'finance':
            name = getItemNameById(finance_table, tx.finance_id)
            particulars = f"{name} - <small class='text-primary'>Finance</small>"

        else:
            particulars = "Other Payment"

        amount = Decimal(str(tx.collect_amount or tx.amount or 0))
        if amount <= 0:
            continue

        credit_amount = Decimal('0.00')
        debit_amount  = Decimal('0.00')

        # ----------------------
        # Ledger side logic
        # ----------------------

        # 1️⃣ Exchange → ALWAYS Debit
        if payment_type == 'exchange':
            debit_amount = amount

        # 2️⃣ HO logic → Card / Finance = Debit
        elif is_ho == 1 and payment_type in ['card', 'finance']:
            debit_amount = amount

        # 3️⃣ Normal case → Credit
        else:
            credit_amount = amount

        ledger_tmp.append({
            'row_id': row_id,
            'receipt': sale.inv_no,
            'date_obj': date_obj,
            'tx_type': 'Sales Transaction',
            'particulars': particulars,
            'credit_amount': credit_amount,
            'debit_amount': debit_amount,
            'employee_id': sale.employee_id
        })

        row_id += 1

    # -------------------------
    # 10) SALES RETURN TRANSACTIONS
    # -------------------------
    sr_qs = sales_return_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        sr_date__range=[from_date, to_date]
    )

    for sale in sr_qs:
        date_obj = datetime.combine(sale.sr_date, sale.time or datetime.min.time())
        customer = select_row(customer_table, {'id': sale.customer_id})
        credit_amount = Decimal(str(sale.total_amount or 0))
        if credit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': sale.sr_no,
                'date_obj': date_obj,
                'tx_type': 'Sales Return',
                'particulars': f"{customer.name} - <span class='text-danger text-capitalize'> {customer.customer_type}</span><small class='text-primary'> ({customer.phone})</small>",
                'credit_amount': round(credit_amount, 2),
                'debit_amount': 0,
                'employee_id': sale.employee_id
            })
            row_id += 1


    # -------------------------
    # 11) TRANSFER IN TRANSACTIONS (MATERIAL IN)
    # -------------------------
    mi_qs = material_inward_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        transfer_date__range=[from_date, to_date]
    )

    for sale in mi_qs:
        date_obj = datetime.combine(sale.transfer_date, sale.time or datetime.min.time())
        supplier = select_row(branch_table, {'id': sale.supply_branch_id})
        debit_amount = Decimal(str(sale.total_amount or 0))
        if debit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': sale.transfer_no,
                'date_obj': date_obj,
                'tx_type': 'Transfer In',
                'particulars': f"{supplier.name}",
                'credit_amount': 0,
                'debit_amount': round(debit_amount, 2),
                'employee_id': sale.employee_id
            })
            row_id += 1

    # -------------------------
    # 12) TRANSFER OUT TRANSACTIONS (MATERIAL OUT)
    # -------------------------
    mo_qs = material_outward_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        transfer_date__range=[from_date, to_date]
    )

    for sale in mo_qs:
        date_obj = datetime.combine(sale.transfer_date, sale.time or datetime.min.time())
        supplier = select_row(branch_table, {'id': sale.supply_branch_id})
        credit_amount = Decimal(str(sale.total_amount or 0))
        if credit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': sale.transfer_no,
                'date_obj': date_obj,
                'tx_type': 'Transfer Out',
                'particulars': f"{supplier.name}",
                'credit_amount': round(credit_amount, 2),
                'debit_amount': 0,
                'employee_id': sale.employee_id
            })
            row_id += 1

    # -------------------------
    # 11) COLLECTIONS
    # -------------------------
    collection_qs = tm_collection_table.objects.filter(
        Q(branch_id=login_branch_id) | Q(id__in=tx_collection_table.objects.filter(supply_branch_id=login_branch_id, status=1, is_active=1).values_list('tm_collection_id', flat=True)),
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    ).distinct()

    for coll in collection_qs:
        date_obj = datetime.combine(coll.date, coll.time or datetime.min.time())
        
        # Creator Side
        if coll.branch_id == login_branch_id:
            debit_amount = coll.receipt or 0
            if debit_amount > 0:
                ledger_tmp.append({
                    'row_id': row_id,
                    'receipt': coll.collection_no,
                    'date_obj': date_obj,
                    'tx_type': 'Collection',
                    'particulars': f"Collection Sent - <small class='text-primary'>{coll.f_type}</small>",
                    'credit_amount': round(Decimal(str(coll.receipt or 0)), 2)    ,
                    'debit_amount': 0.00,
                    'employee_id': coll.employee_id
                })
                row_id += 1
        
        # Against Side
        tx_colls = tx_collection_table.objects.filter(tm_collection_id=coll.id, supply_branch_id=login_branch_id, status=1, is_active=1)
        total_credit = tx_colls.aggregate(total=Sum('amount'))['total'] or 0
        if total_credit > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': coll.collection_no,
                'date_obj': date_obj,
                'tx_type': 'Collection',
                'particulars': f"Collection Received - <small class='text-primary'>{coll.f_type}</small>",
                'credit_amount': round(Decimal(str(total_credit)), 2),
                'debit_amount': 0,
                'employee_id': coll.employee_id
            })
            row_id += 1

    # -------------------------
    # 12) VOUCHERS
    # -------------------------
    voucher_qs = voucher_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )

    for v in voucher_qs:
        date_obj = datetime.combine(v.date, v.time or datetime.min.time())
        employee = getItemNameById(employee_table, v.employee_id)

        credit_amount = 0
        debit_amount = 0

        voucher_type = v.voucher_type.lower()

        if voucher_type in ['receipt', 'credit']:
            credit_amount = v.receivable_amount or v.advance_amount or 0

        elif voucher_type in ['payment', 'debit']:
            debit_amount = v.payable_amount or v.advance_amount or 0

        # skip empty vouchers
        if credit_amount <= 0 and debit_amount <= 0:
            continue

        particulars = (
            f"{voucher_type.title()} Voucher"
            f" <small class='text-primary'>({v.payment_mode})</small>"
        )

        ledger_tmp.append({
            'row_id': row_id,
            'receipt': v.voucher_no,
            'date_obj': date_obj,
            'tx_type': 'Voucher',
            'particulars': particulars,
            'credit_amount': Decimal(str(credit_amount)),
            'debit_amount': Decimal(str(debit_amount)),
            'employee_id': v.employee_id
        })
        row_id += 1

    # -------------------------
    # 13) UPGRADE SALES
    # -------------------------
    upgrade_sales_qs = tm_upgrade_table.objects.filter(    
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )

    for tx in upgrade_sales_qs:
        date_obj = datetime.combine(tx.date, tx.time or datetime.min.time())
        particulars = f"Upgrade Sale"
        credit_amount = Decimal(str(tx.total_amount or 0))
        if credit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': tx.upgrade_no,
                'date_obj': date_obj,
                'tx_type': 'Upgrade Sale',
                'particulars': particulars,
                'credit_amount': round(credit_amount, 2),
                'debit_amount': 0,
                'employee_id': tx.employee_id
            })
            row_id += 1

    # -------------------------
    # 14) EXPENSES
    # -------------------------
    expense_qs = expense_table.objects.filter(
        branch_id=login_branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )

    for exp in expense_qs:
        date_obj = datetime.combine(exp.date, exp.time or datetime.min.time())
        expense_type = getItemNameById(expense_master_table, exp.expense_id)
        
        # Build particulars based on payment mode
        payment_details = []
        if exp.cash and exp.cash > 0:
            payment_details.append(f"Cash")
        if exp.bank and exp.bank > 0:
            bank_name = getItemNameById(bank_table, exp.bank_id)
            payment_details.append(f"Bank: {bank_name}")
            print(payment_details)
        
        payment_info = " | ".join(payment_details) if payment_details else ""
        particulars = f"{expense_type}"
        if payment_info:
            particulars += f" - <span class='text-primary'>{payment_info}</span>"
        
        credit_amount = Decimal(str(exp.amount or 0))
        if credit_amount > 0:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': '-',
                'date_obj': date_obj,
                'tx_type': 'Expense',
                'particulars': particulars,
                'credit_amount': round(credit_amount, 2),
                'debit_amount':0,
                'employee_id': exp.employee_id
            })
            row_id += 1

    # -------------------------
    # 15) SALES RETURN TRANSACTIONS (REFUND PAYMENTS)
    # -------------------------
    sr_tx_qs = return_transaction_table.objects.filter(
        tm_return_id__in=[sr.id for sr in sr_qs],
        is_active=1,
        status=1,
        date__range=[from_date, to_date]
    )

    for tx in sr_tx_qs:
        sr = select_row(sales_return_table, {'id': tx.tm_return_id})
        date_obj = datetime.combine(tx.date, tx.time or datetime.min.time())

        payment_type = (tx.payment_type or '').lower()

        # Build particulars
        if payment_type == 'cash':
            particulars = "Cash Refund - <small class='text-primary'>Cash</small>"
        elif payment_type == 'bank':
            name = getItemNameById(bank_table, tx.bank_id)
            particulars = f"{name} - <small class='text-primary'>Bank Refund</small>"
        elif payment_type == 'exchange':
            particulars = f"Exchange Refund - <small class='text-primary'>{tx.reference_no}</small>"
        elif payment_type == 'card':
            name = getItemNameById(finance_table, tx.card_id)
            particulars = f"{name} - <small class='text-primary'>Card Refund</small>"
        elif payment_type == 'finance':
            name = getItemNameById(finance_table, tx.finance_id)
            particulars = f"{name} - <small class='text-primary'>Finance Refund</small>"
        else:
            particulars = "Other Refund"

        amount = Decimal(str(tx.collect_amount or tx.amount or 0))
        if amount <= 0:
            continue

        # Refunds are opposite of sales transactions
        # For HO: Card/Finance refunds = Credit, others = Debit
        # For Branch: All refunds = Debit (money going out)
        credit_amount = Decimal('0.00')
        debit_amount = Decimal('0.00')

        if is_ho == 1 and payment_type in ['card', 'finance']:
            credit_amount = amount
        else:
            debit_amount = amount

        ledger_tmp.append({
            'row_id': row_id,
            'receipt': sr.sr_no if sr else '-',
            'date_obj': date_obj,
            'tx_type': 'Sales Return Transaction',
            'particulars': particulars,
            'credit_amount': credit_amount,
            'debit_amount': debit_amount,
            'employee_id': sr.employee_id if sr else ''
        })
        row_id += 1

    # -------------------------
    # 17) SLAB SALES
    # -------------------------
    slab_qs = slab_sales_table.objects.filter(
        Q(branch_id=login_branch_id) | Q(supply_branch_id=login_branch_id),
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    ).distinct()

    for s in slab_qs:
        date_obj = datetime.combine(s.date, s.time or datetime.min.time())
        amount = Decimal(str(s.credit_note or 0))
        if amount <= 0: continue

        # Case 1: Self-Transaction (HO Coverage for itself)
        if s.branch_id == login_branch_id and s.supply_branch_id == login_branch_id:
            ledger_tmp.append({
                'row_id': row_id,
                'receipt': s.slab_no,
                'date_obj': date_obj,
                'tx_type': 'Slab Sales',
                'particulars': f"Slab Credit Note (Self) - <small class='text-primary'>{s.slab_no}</small>",
                'credit_amount': round(amount, 2),
                'debit_amount': 0,
                'employee_id': s.employee_id
            })
            row_id += 1

        else:
            # Case 2: HO/Creator Side (Distributing to others) -> Debit
            if s.branch_id == login_branch_id:
                ledger_tmp.append({
                    'row_id': row_id,
                    'receipt': s.slab_no,
                    'date_obj': date_obj,
                    'tx_type': 'Slab Sales',
                    'particulars': f"Slab Credit Note Distributed - <small class='text-primary'>{s.slab_no}</small>",
                    'credit_amount': 0,
                    'debit_amount': round(amount, 2),
                    'employee_id': s.employee_id
                })
                row_id += 1

            # Case 3: Supply/Recipient Side (Benefit received) -> Credit
            if s.supply_branch_id == login_branch_id:
                ledger_tmp.append({
                    'row_id': row_id,
                    'receipt': s.slab_no,
                    'date_obj': date_obj,
                    'tx_type': 'Slab Sales',
                    'particulars': f"Slab Credit Note Received - <small class='text-primary'>{s.slab_no}</small>",
                    'credit_amount': round(amount, 2),
                    'debit_amount': 0,
                    'employee_id': s.employee_id
                })
                row_id += 1

    # -------------------------
    # 16) PURCHASE ORDER
    # -------------------------
    # po_qs = purchase_order_table.objects.filter(
    #     branch_id=login_branch_id,
    #     status=1,
    #     is_active=1,
    #     po_date__range=[from_date, to_date]
    # )

    # for po in po_qs:
    #     date_obj = datetime.combine(po.po_date, po.time or datetime.min.time())
    #     supplier_name = getItemNameById(supplier_table, po.supplier_id)
    #     debit_amount = Decimal(str(po.total_amount or 0))
    #     if debit_amount > 0:
    #         ledger_tmp.append({
    #             'row_id': row_id,
    #             'receipt': po.po_no,
    #             'date_obj': date_obj,
    #             'tx_type': 'Purchase Order',
    #             'particulars': f"{supplier_name} - <small class='text-primary'>PO Placed</small>",
    #             'credit_amount': 0,
    #             'debit_amount': round(debit_amount, 2),
    #             'employee_id': po.employee_id
    #         })
    #         row_id += 1

    # -------------------------
    # 4️⃣ SORT LEDGER BY DATE AND TYPE
    # -------------------------
    # Priority for sorting when timestamps are identical:
    # 0: Opening Balance
    # 1: Debits (bills/requests/expenses)
    # 2: Credits (payments/returns/receipts)
    
    # Sort by datetime and priority (OB -> Inward/Debit -> Outward/Credit)
    def get_priority(item):
        if item['tx_type'] == 'Opening Balance':
            return 0
        if item['debit_amount'] > 0 and item['credit_amount'] == 0:
            return 1  # Inwards first
        return 2      # Outwards last

    ledger_tmp.sort(key=lambda x: (x['date_obj'], get_priority(x)))

    running_available = Decimal('0.00')

    # Transactions that affect physical/available cash/bank balance
    CASH_AFFECTING_TYPES = [
        'Opening Balance',
        'Expense',
        'Sales Transaction',
        'Collection',
        'Voucher',
        'Contra',
        'Sales Return Transaction',
        'Slab Sales',
        'Purchase Inward',
        'Purchase Return',
        'Transfer In',
        'Transfer Out',
        'Sales',
        'Sales Return',
        'Claim',
        'Upgrade Sale',
        'Transfer Request',
        'Price Drop'
    ]

    ledger = []
    
    # Use enumerate to generate a clean 1, 2, 3... sequence after sorting
    for index, item in enumerate(ledger_tmp, start=1):
        credit = Decimal(item['credit_amount'] or 0)
        debit  = Decimal(item['debit_amount'] or 0)
        
        # Only update available amount for specific cash-affecting transaction types
        if item['tx_type'] in CASH_AFFECTING_TYPES:
            running_available += credit - debit
        
        ledger = append_transaction(
            ledger=ledger,
            row_id=index,
            receipt=item['receipt'],
            date_str=f"<span>{item['date_obj'].strftime('%d-%m-%Y %H:%M')}</span>",
            tx_type=item['tx_type'],
            particulars=item['particulars'],
            credit_amount=item['credit_amount'],
            debit_amount=item['debit_amount'],
            running_outstanding=running_available, # Passed as running_outstanding to preserve function signature
            employee_name=getItemNameById(employee_table, item['employee_id']) if item['employee_id'] else ''
        )

    return JsonResponse({'data': ledger, 'total_outstanding': running_available})