from django.shortcuts import render
import json
from django.conf import settings
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 datetime import datetime
from django.utils import timezone
from django.db.models import Max, F, Q, Sum
import base64
from io import BytesIO
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.core.files.storage import default_storage
from claim_collect_upgrade.models import *
from voucher.models import *
from decimal import Decimal
from common.utils import *
# **********************************************************************************************************************************


def format_hr_m(date):
    return date.strftime('%H:%M') if date else None
 
def party_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":
            customer = selectList(
                customer_table, {"branch_id": branch_id}, order_by="name"
            )
            branch = selectList(branch_table, order_by="name").exclude(id=branch_id)
            supplier = selectList(supplier_table, order_by="name")
            item = selectList(item_table)
            return render(
                request,
                "party_ledger.html",
                {
                    "customer": customer,
                    "branch": branch,
                    "item": item,
                    "supplier": supplier,
                },
            )
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


# **********************************************************************************************************************************
from common.utils import *
def party_ledger_view(request):
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)

    customer_id = request.POST.get('customer_id')
    supplier_id = request.POST.get('supplier_id')
    supply_branch_id = request.POST.get('supply_branch_id')

    role_id = request.session.get('role_id')
    has_access, _ = check_user_access(role_id, 'party_ledger', "read")
    if not has_access:
        return JsonResponse({'message': 'permission'})

    # normalize ids
    supplier_id = int(supplier_id) if supplier_id not in (None, "", "null") else 0
    customer_id = int(customer_id) if customer_id not in (None, "", "null") else 0
    supply_branch_id = int(supply_branch_id) if supply_branch_id not in (None, "", "null") else 0

    all_rows = []
    running_outstanding = Decimal("0.00")

    # Count how many are selected
    selected_count = sum([1 if x > 0 else 0 for x in [supplier_id, customer_id, supply_branch_id]])

    # -----------------------
    # Decide what to fetch
    # -----------------------
   

    # Two selected → fetch only those two
    res = outstanding_supplier(branch_id, supplier_id, financial_year)
    all_rows.extend(res["rows"])
    running_outstanding += res["opening_outstanding"]

    

    

    # -----------------------
    # Sort & calculate running outstanding
    # -----------------------
    all_rows.sort(key=lambda x: (x["date"], x["time"]))

    formatted = []
    running_outstanding = Decimal("0.00")
    for idx, row in enumerate(all_rows, start=1):
        # Store POV for Suppliers: Credit increases liability (what we owe)
        # Outstanding = Credit - Debit
        running_outstanding += Decimal(row["credit"]) - Decimal(row["debit"])

        # DR/CR per row
        # Positive = CR (We Owe), Negative = DR (They Owe Us)
        dr_cr = "CR" if running_outstanding >= 0 else "DR"
        row["outstanding"] = f"{abs(running_outstanding):,.2f} ({dr_cr})"

        row["id"] = idx
        row["date"] = f"{row['date']} {row['time']}"
        row["employee"] = getItemNameById(employee_table, row["employee"])
        formatted.append(row)

    # Total outstanding
    total_type = "CR" if running_outstanding >= 0 else "DR"
    total_outstanding = f"{abs(running_outstanding):.2f} ({total_type})"

    return JsonResponse({
        "data": formatted,
        "total_outstanding": total_outstanding
    })




# **********************************************************************************************************************************
# Supplier Outstanding
from datetime import time
from datetime import time
from decimal import Decimal
from datetime import time

def outstanding_supplier(branch_id, supplier_id, financial_year):
    rows = []
    opening_outstanding = Decimal("0.00")

    supplier_filter = {}
    if supplier_id and int(supplier_id) != 0:
        supplier_filter["supplier_id"] = supplier_id

    # 1️⃣ OPENING BALANCE (NOW FROM SUPPLIER TABLE)
    current_branch = select_row(branch_table, {"id": branch_id})
    is_ho = getattr(current_branch, 'is_ho', 0) == 1 if current_branch else False

    if supplier_id and int(supplier_id) != 0:
        suppliers = supplier_table.objects.filter(id=supplier_id, status=1, is_active=1)
    else:
        suppliers = supplier_table.objects.filter(status=1, is_active=1)

    for sup in suppliers:
        supplier_name = sup.name
        # Receivable = They owe us (Asset) = Debit
        # Payable = We owe them (Liability) = Credit
        debit = Decimal(sup.receivable or 0)
        credit = Decimal(sup.payable or 0)
        
        if debit > 0 or credit > 0:
            if is_ho:
                trans_type = "Opening Balance"
                particulars = f"Opening Balance - <span class='text-primary text-capitalize'>{supplier_name}</span>"
            else:
                trans_type = "Receivable/Payable"
                particulars = f"<span class='text-primary text-capitalize'>{supplier_name}</span>"

            rows.append({
                "date": sup.created_on.date() if sup.created_on else timezone.now().date(),
                "time": format_hr_m(sup.created_on.time() if sup.created_on else timezone.now().time()),
                "receipt": "",
                "type": trans_type,
                "particulars": particulars,
                "credit": credit,
                "debit": debit,
                "outstanding": Decimal("0.00"),
                "employee": sup.created_by or ""
            })

    # 2️⃣ PURCHASE INWARD (CREDIT) - We owe supplier more
    purchases = purchase_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **supplier_filter
    )

    for pu in purchases:
        supplier_name = getItemNameById(supplier_table, pu.supplier_id)
        credit = Decimal(pu.grand_total or 0)

        rows.append({
            "date": pu.pu_date,
            "time": format_hr_m(pu.time or time(0, 0)),
            "receipt": pu.pu_no or "",
            "type": "Purchase",
            "particulars": f"Purchase Inward - <span class='text-primary text-capitalize'>{supplier_name}</span>",
            "credit": credit,
            "debit": Decimal("0.00"),
            "outstanding": Decimal("0.00"),
            "employee": pu.employee_id or ""
        })

    # 3️⃣ PURCHASE RETURN (DEBIT) - Reduces our liability
    returns = purchase_return_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        pr_status__iexact='approved',
        **supplier_filter
    )

    for pr in returns:
        supplier_name = getItemNameById(supplier_table, pr.supplier_id)
        debit = Decimal(str(pr.total_amount or "0")).quantize(Decimal("0.00"))

        rows.append({
            "date": pr.pr_date,
            "time": format_hr_m(pr.time or time(0, 0)),
            "receipt": pr.pr_no or "",
            "type": "Purchase Return",
            "particulars": f"Purchase Return - <span class='text-primary text-capitalize'>{supplier_name}</span>",
            "credit": Decimal("0.00"),
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": pr.employee_id or ""
        })

    # 4️⃣ PRICE DROP / DEBIT NOTE (DEBIT)
    price_drops = price_history_table.objects.filter(
        current_fy=financial_year,
        status=1,
        is_active=1,
        **supplier_filter
    )

    for pd in price_drops:
        supplier_name = getItemNameById(supplier_table, pd.supplier_id)
        price_drop_branch_id = pd.branch_id
        created_by_this_branch = (price_drop_branch_id == branch_id)
        price_drop_branch = select_row(branch_table, {"id": price_drop_branch_id})
        price_drop_by_ho = getattr(price_drop_branch, 'is_ho', 0) == 1 if price_drop_branch else False

        if created_by_this_branch and is_ho:
            # HO Debit Note reduces supplier liability
            debit_note_amount = Decimal(pd.debit_note or 0)
            if debit_note_amount > 0:
                rows.append({
                    "date": pd.date,
                    "time": format_hr_m(pd.time or time(0, 0)),
                    "receipt": pd.price_no or "",
                    "type": "Debit Note",
                    "particulars": f"Price Drop Debit Note - <span class='text-primary text-capitalize'>{supplier_name}</span>",
                    "credit": Decimal("0.00"),
                    "debit": debit_note_amount,
                    "outstanding": Decimal("0.00"),
                    "employee": pd.created_by or ""
                })
        
        elif not created_by_this_branch and price_drop_by_ho and not is_ho:
            tx_price_items = tx_price_history_table.objects.filter(
                tm_price_id=pd.id,
                branch_id=branch_id,
                status=1,
                is_active=1
            )
            
            if tx_price_items.exists():
                total_benefit = Decimal("0.00")
                for tx_item in tx_price_items:
                    total_benefit += Decimal(tx_item.mop_discount or 0) + Decimal(tx_item.bop_discount or 0) + \
                                     Decimal(tx_item.wsp_discount or 0) + Decimal(tx_item.fsp_discount or 0)
                
                if total_benefit > 0:
                    ho_branch_name = getItemNameById(branch_table, price_drop_branch_id)
                    # Benefit reduces what we owe to supplier/HO (Internal but show as Debit to someone)
                    # Let's keep it as Debit for consistency if Price Drop is a Debit Note context
                    rows.append({
                        "date": pd.date,
                        "time": format_hr_m(pd.time or time(0, 0)),
                        "receipt": pd.price_no or "",
                        "type": "Price Drop Benefit",
                        "particulars": f"Price Drop from <span class='text-info text-capitalize'>{ho_branch_name}</span> - <span class='text-primary text-capitalize'>{supplier_name}</span>",
                        "credit": Decimal("0.00"),
                        "debit": total_benefit,
                        "outstanding": Decimal("0.00"),
                        "employee": pd.created_by or ""
                    })

    # 5️⃣ CLAIMS (DEBIT) - Debit notes raised AGAINST supplier
    claims = tm_claim_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **supplier_filter
    )

    for claim in claims:
        supplier_name = getItemNameById(supplier_table, claim.supplier_id)
        debit_amount = Decimal(claim.debit_amount or 0)
        
        if debit_amount > 0:
            scheme_info = f"({claim.scheme_type})" if claim.scheme_type else ""
            rows.append({
                "date": claim.date,
                "time": format_hr_m(claim.time or time(0, 0)),
                "receipt": claim.debit_note_no or claim.claim_no or "",
                "type": "Claim",
                "particulars": f"Claim {scheme_info} - <span class='text-primary text-capitalize'>{supplier_name}</span>",
                "credit": Decimal("0.00"),
                "debit": debit_amount,
                "outstanding": Decimal("0.00"),
                "employee": claim.created_by or ""
            })

    # 6️⃣ PAYMENTS / VOUCHERS (DEBIT for Payment, CREDIT for Receipt)
    vouchers = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **supplier_filter
    )

    for vo in vouchers:
        supplier_name = getItemNameById(supplier_table, vo.supplier_id)
        payment = Decimal(vo.payable_amount or 0) # Money paid TO supplier
        receipt = Decimal(vo.receivable_amount or 0) # Money received FROM supplier

        if payment > 0 or receipt > 0:
            particulars = f"{vo.voucher_type} - <span class='text-primary text-capitalize'>{supplier_name}</span>"
            if vo.payment_remarks:
                particulars += f" <small>({vo.payment_remarks})</small>"

            rows.append({
                "date": vo.date,
                "time": format_hr_m(vo.time or time(0, 0)),
                "receipt": vo.voucher_no or "",
                "type": "Voucher",
                "particulars": particulars,
                "credit": receipt, # Receipt from supplier increases liability or reduces asset
                "debit": payment,    # Payment to supplier reduces liability
                "outstanding": Decimal("0.00"),
                "employee": vo.created_by or ""
            })

    return {
        "rows": rows,
        "opening_outstanding": opening_outstanding
    }


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