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 customer_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,
                "customer_ledger.html",
                {
                    "customer": customer,
                    "branch": branch,
                    "item": item,
                    "supplier": supplier,
                },
            )
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


# **********************************************************************************************************************************
from common.utils import *
from datetime import datetime
from decimal import Decimal

def customer_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')

    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'})

    customer_id = int(customer_id) if customer_id not in (None, "", "null") else 0

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

    res = outstanding_customer(branch_id, customer_id, financial_year)
    all_rows.extend(res["rows"])
    running_outstanding += res["opening_outstanding"]

    # ----------------------------------
    # ✅ SORT USING REAL DATETIME
    # ----------------------------------
    for row in all_rows:
        row["sort_datetime"] = datetime.strptime(
            f"{row['date']} {row['time']}",
            "%Y-%m-%d %H:%M"
        )

    all_rows.sort(key=lambda x: x["sort_datetime"])

    # ----------------------------------
    # FORMAT LEDGER
    # ----------------------------------
    formatted = []
    running_outstanding = Decimal("0.00")

    # Absolute First: All Customer Balances for this Branch (From Customer Table)
    cust_balances = customer_table.objects.filter(
        (Q(receivable__gt=0) | Q(payable__gt=0)),
        branch_id=branch_id,
        status=1
    ).order_by('name')

    for cust in cust_balances:
        c_receivable = Decimal(str(cust.receivable or 0))
        c_payable = Decimal(str(cust.payable or 0))
        
        # Standard Customer Ledger logic: Debit - Credit increases outstanding (DR)
        running_outstanding += c_receivable - c_payable
        formatted.append({
            "id": "-",
            "date": "-",
            "time": "",
            "receipt": "-",
            "type": "Master Balance",
            "particulars": f"Balance: <span class='text-primary'>{cust.name}</span>",
            "credit": c_receivable,
            "debit": c_payable,
            "outstanding": f"{abs(running_outstanding):,.2f} ({ 'DR' if running_outstanding >= 0 else 'CR' })",
            "employee": ""
        })

    for idx, row in enumerate(all_rows, start=1):
        running_outstanding += Decimal(row["debit"]) - Decimal(row["credit"])

        dr_cr = "DR" if running_outstanding >= 0 else "CR"
        row["outstanding"] = f"{abs(running_outstanding):,.2f} ({dr_cr})"

        row["id"] = idx
        row["date"] = row["sort_datetime"].strftime("%Y-%m-%d %H:%M")
        row["employee"] = getItemNameById(employee_table, row["employee"])

        # cleanup
        row.pop("sort_datetime", None)

        formatted.append(row)

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


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



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

# Customer Outstanding  
from decimal import Decimal
from datetime import time

def outstanding_customer(branch_id, customer_id, financial_year):
    print('**** ENTERED ***')
    rows = []
    opening_outstanding = Decimal("0.00")

    # --------------------------------------------------
    # 🎯 CUSTOMER FILTER
    # customer_id = 0 / None → ALL customers
    # --------------------------------------------------
    customer_filter = {}
    if customer_id and str(customer_id) != "0":
        customer_filter["customer_id"] = customer_id

    # --------------------------------------------------
    # 1️⃣ OPENING BALANCE
    # --------------------------------------------------
    obs = customer_opening_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **customer_filter
    )

    for ob in obs:
        debit  = Decimal(ob.debit or 0)
        credit = Decimal(ob.credit or 0)

        opening_outstanding += (debit - credit)

        rows.append({
            "date": ob.date,
            "time": format_hr_m(ob.time or time(0, 0)),
            "receipt": "",
            "type": "Opening Balance",
            "particulars": "Customer Opening Balance",
            "credit": credit,
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": ob.created_by or ""
        })

    # --------------------------------------------------
    # 2️⃣ SALES (DEBIT)
    # --------------------------------------------------
    sales = sales_order_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **customer_filter
    )

    for so in sales:
        customer = select_row(customer_table, {'id': so.customer_id})
        debit = Decimal(so.total_amount or 0)

        # 🔴 SALES ROW
        rows.append({
            "date": so.inv_date,
            "time": format_hr_m(so.time or time(0, 0)),
            "receipt": so.inv_no or "",
            "type": "Sales",
            "particulars": (
                f"Sales - "
                f"<span class='text-primary text-capitalize'>{customer.name}</span> "
                f"(<span class='text-danger text-capitalize'>{customer.customer_type}</span>)"
            ),
            "credit": Decimal("0.00"),
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": so.employee_id or ""
        })

        # --------------------------------------------------
        # 💰 PAYMENTS AGAINST SALES (CREDIT – MODE WISE)
        # --------------------------------------------------
        payments = transaction_table.objects.filter(
            tm_sales_id=so.id,
            branch_id=branch_id,
            current_fy=financial_year,
            status=1,
            is_active=1
        )

        for pay in payments:
            amount = Decimal(pay.amount or 0)
            if amount <= 0:
                continue

            mode = (pay.payment_type or "Payment").lower()

            credit = Decimal("0.00")
            debit  = Decimal("0.00")

            # -------------------------------
            # PAYMENT MODE RESOLUTION
            # -------------------------------
            if mode == "cash":
                text = "Cash"
                credit = amount

            elif mode == "bank":
                text = getItemNameById(bank_table, pay.bank_id)
                credit = amount

            elif mode == "card":
                text = getItemNameById(finance_table, pay.card_id)
                debit = amount

            elif mode == "finance":
                text = getItemNameById(finance_table, pay.finance_id)
                debit = amount

            elif mode == "exchange":
                text = pay.reference_no or "Exchange"
                debit = amount

            else:
                text = mode.title()
                credit = amount

            rows.append({
                "date": pay.date or so.inv_date,
                "time": format_hr_m(pay.time or time(0, 0)),
                "receipt": so.inv_no or "",
                "type": "Sales Transaction",
                "particulars": (
                    f"{mode.title()} "
                    f"- <span class='text-primary text-capitalize'>{text}</span>"
                ),
                "credit": credit,
                "debit": debit,
                "outstanding": Decimal("0.00"),
                "employee": pay.created_by or ""
            })


    # --------------------------------------------------
    # 3️⃣ SALES RETURN (CREDIT)
    # --------------------------------------------------
    returns = sales_return_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **customer_filter
    )

    for sr in returns:
        customer = select_row(customer_table, {'id': sr.customer_id})
        credit = Decimal(sr.total_amount or 0)

        rows.append({
            "date": sr.sr_date,
            "time": format_hr_m(sr.time or time(0, 0)),
            "receipt": sr.sr_no or "",
            "type": "Sales Return",
            "particulars": (
                f"Sales Return - "
                f"<span class='text-primary text-capitalize'>{customer.name}</span> "
                f"(<span class='text-danger text-capitalize'>{customer.customer_type}</span>)"
            ),
            "credit": credit,
            "debit": Decimal("0.00"),
            "outstanding": Decimal("0.00"),
            "employee": sr.employee_id or ""
        })

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