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


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

    supply_branch_id = request.POST.get('supply_branch_id')
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')

    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
    supply_branch_id = int(supply_branch_id) if supply_branch_id not in (None, "", "null") else 0

    # -----------------------
    # Decide what to fetch
    # -----------------------
    res = outstanding_supply_branch(branch_id, supply_branch_id, financial_year, from_date, to_date)
    all_rows = res["rows"]
    
    # -----------------------
    # Sort & calculate running outstanding
    # -----------------------
    # Robust sorting using 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"])

    formatted = []
    
    running_outstanding = Decimal("0.00")
    serial_no = 1

    try:
        if is_ho == 1:
            branch_query = branch_table.objects.filter(status=1)
            if supply_branch_id != 0:
                branch_query = branch_query.filter(id=supply_branch_id)
            
            for branch in branch_query:
                br_receivable = Decimal(str(branch.receivable or 0))
                br_payable = Decimal(str(branch.payable or 0))
                if br_receivable > 0 or br_payable > 0:
                    master_bal = br_payable - br_receivable
                    running_outstanding += master_bal
                    
                    formatted.append({
                        "id": serial_no,
                        "date": "-",
                        "time": "",
                        "receipt": "-",
                        "type": "Master Balance",
                        "particulars": f"Master Balance: <span class='text-primary'>{branch.name}</span>",
                        "credit": br_receivable,
                        "debit": br_payable,
                        "outstanding": f"{abs(running_outstanding):,.2f} ({ 'CR' if running_outstanding >= 0 else 'DR' })",
                        "employee": ""
                    })
                    serial_no += 1
        else:
            # If not HO, show the balance of the selected supply branch
            if supply_branch_id != 0:
                branch_info = branch_table.objects.get(id=supply_branch_id)
                br_receivable = Decimal(str(branch_info.receivable or 0))
                br_payable = Decimal(str(branch_info.payable or 0))
                if br_receivable > 0 or br_payable > 0:
                    master_bal = br_payable - br_receivable
                    running_outstanding += master_bal
                    formatted.append({
                        "id": serial_no,
                        "date": "-",
                        "time": "",
                        "receipt": "-",
                        "type": "Master Balance",
                        "particulars": f"Master Balance: <span class='text-primary'>{branch_info.name}</span>",
                        "credit": br_receivable,
                        "debit": br_payable,
                        "outstanding": f"{abs(running_outstanding):,.2f} ({ 'CR' if running_outstanding >= 0 else 'DR' })",
                        "employee": ""
                    })
                    serial_no += 1
    except Exception as e:
        print(f"Error in Branch Balance: {e}")

    # Add transactions
    for row in all_rows:
        running_outstanding += Decimal(row["credit"]) - Decimal(row["debit"])

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

        row["id"] = serial_no
        # Format date for display
        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)
        serial_no += 1

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

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







# **********************************************************************************************************************************
# Supply Branch Outstanding  
from decimal import Decimal

def outstanding_supply_branch(branch_id, supply_branch_id, financial_year, from_date=None, to_date=None):
    rows = []
    opening_outstanding = Decimal("0.00")

    supply_filter = {}
    if supply_branch_id and int(supply_branch_id) != 0:
        supply_filter["supply_branch_id"] = supply_branch_id

    # Common Filters (Exclude Opening Balance dates as they are start of year)
    date_filter = {}
    if from_date and to_date:
        date_filter_raw = {"from_date": from_date, "to_date": to_date}
    else:
        date_filter_raw = None

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

    for ob in obs:
        debit  = Decimal(ob.debit or 0)
        credit = Decimal(ob.credit or 0)        
        
        
        rows.append({
            "date": ob.date,
            "time": format_hr_m(ob.time or time(0, 0)),
            "receipt": getattr(ob, "ob_no", "") or "",
            "type": "Opening Balance",
            "particulars": "Supply Branch Opening Balance",
            "credit": credit,
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": ob.created_by or ""
        })

    # --------------------------------------------------
    # 2️⃣ MATERIAL IN (CREDIT - We owe them)
    # --------------------------------------------------
    mi_filter = {**supply_filter}
    if date_filter_raw:
        mi_filter["transfer_date__range"] = [date_filter_raw["from_date"], date_filter_raw["to_date"]]

    material_in = material_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **mi_filter
    )

    for mi in material_in:
        credit = Decimal(mi.total_amount or 0)
        branch_name = getItemNameById(branch_table, mi.supply_branch_id)
        rows.append({
            "date": mi.transfer_date,
            "time": format_hr_m(mi.time or time(0, 0)),
            "receipt": mi.transfer_no or "",
            "type": "Transfer In",
            "particulars": f"Material In <span class='text-primary text-capitalize'>{branch_name}</span>",
            "credit": credit,
            "debit": Decimal("0.00"),
            "outstanding": Decimal("0.00"),
            "employee": mi.employee_id or ""
        })

    # --------------------------------------------------
    # 3️⃣ MATERIAL OUT (DEBIT - They owe us)
    # --------------------------------------------------
    mo_filter = {**supply_filter}
    if date_filter_raw:
        mo_filter["transfer_date__range"] = [date_filter_raw["from_date"], date_filter_raw["to_date"]]

    material_out = material_outward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **mo_filter
    )

    for mo in material_out:
        debit = Decimal(mo.total_amount or 0)
        branch_name = getItemNameById(branch_table, mo.supply_branch_id)
        rows.append({
            "date": mo.transfer_date,
            "time": format_hr_m(mo.time or time(0, 0)),
            "receipt": mo.transfer_no or "",
            "type": "Material Out",
            "particulars": f"Material Out - <span class='text-primary text-capitalize'>{branch_name}</span>",
            "credit": Decimal("0.00"),          
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": mo.employee_id or ""
        })

    # --------------------------------------------------
    # 4️⃣ VOUCHERS (Payments/Receipts)
    # --------------------------------------------------
    v_filter = {**supply_filter}
    if date_filter_raw:
        v_filter["date__range"] = [date_filter_raw["from_date"], date_filter_raw["to_date"]]

    vouchers = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **v_filter
    )

    for vouch in vouchers:
        is_receipt = vouch.voucher_type in ["receipt", "cash_receipt", "bank_receipt"]
        is_payment = vouch.voucher_type in ["payment", "cash_payment", "bank_payment"]
        
        amount = Decimal(0)
        debit = Decimal(0)
        credit = Decimal(0)
        v_type = ""

        if is_receipt:
            amount = Decimal(vouch.receivable_amount or 0)
            credit = amount # Money received reduces what they owe us (or increases what we owe them)
            v_type = "Receipt"
        elif is_payment:
            amount = Decimal(vouch.payable_amount or 0)
            debit = amount # Money paid reduces what we owe them
            v_type = "Payment"
        
        if amount > 0:
            rows.append({
                "date": vouch.date,
                "time": format_hr_m(vouch.time or time(0, 0)),
                "receipt": vouch.voucher_no or "",
                "type": f"Voucher {v_type}",
                "particulars": f"Voucher - {vouch.voucher_type} ({vouch.payment_mode})",
                "credit": credit,
                "debit": debit,
                "outstanding": Decimal("0.00"),
                "employee": vouch.created_by or ""
            })

    # --------------------------------------------------
    # 5️⃣ CLAIMS (DEBIT - Reduces what we owe)
    # --------------------------------------------------
    cl_filter = {**supply_filter}
    if date_filter_raw:
        # Note: tx_claim_table doesn't have date, we use tm_claim_id to filter by date
        cl_filter["tm_claim_id__in"] = tm_claim_table.objects.filter(
            date__range=[date_filter_raw["from_date"], date_filter_raw["to_date"]]
        ).values_list('id', flat=True)

    claims = tx_claim_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **cl_filter
    )
    
    for claim in claims:
        tm_claim = select_row(tm_claim_table, {'id': claim.tm_claim_id})
        claim_no = tm_claim.claim_no if tm_claim else ""
        
        rows.append({
            "date": tm_claim.date if tm_claim else financial_year['start_date'],
            "time": format_hr_m(tm_claim.time or time(0, 0)) if tm_claim else "00:00",
            "receipt": claim_no,
            "type": "Claim",
            "particulars": f"Claim - {claim.scheme_type}",
            "credit": Decimal("0.00"),
            "debit": Decimal(claim.amount or 0),
            "outstanding": Decimal("0.00"),
            "employee": claim.created_by or ""
        })

    # --------------------------------------------------
    # 6️⃣ COLLECTIONS (CREDIT - Money In from Branch)
    # --------------------------------------------------
    coll_filter = {**supply_filter}
    if date_filter_raw:
        coll_filter["tm_collection_id__in"] = tm_collection_table.objects.filter(
            date__range=[date_filter_raw["from_date"], date_filter_raw["to_date"]]
        ).values_list('id', flat=True)

    collections = tx_collection_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **coll_filter
    )

    for coll in collections:
        tm_coll = select_row(tm_collection_table, {'id': coll.tm_collection_id})
        coll_no = tm_coll.collection_no if tm_coll else ""
        
        rows.append({
            "date": tm_coll.date if tm_coll else financial_year['start_date'],
            "time": format_hr_m(tm_coll.time or time(0, 0)) if tm_coll else "00:00",
            "receipt": coll_no,
            "type": "Collection",
            "particulars": f"Collection - {coll_no}",
            "credit": Decimal(coll.amount or 0),
            "debit": Decimal("0.00"),
            "outstanding": Decimal("0.00"),
            "employee": coll.created_by or ""
        })

    # --------------------------------------------------
    # 7️⃣ UPGRADES (DEBIT - Value sent to Branch)
    # --------------------------------------------------
    upg_filter = {**supply_filter}
    if date_filter_raw:
        upg_filter["date__range"] = [date_filter_raw["from_date"], date_filter_raw["to_date"]]

    upgrades = tm_upgrade_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **upg_filter
    )

    for upg in upgrades:
        debit = Decimal(upg.total_amount or 0)
        rows.append({
            "date": upg.date,
            "time": format_hr_m(upg.time or time(0, 0)),
            "receipt": upg.upgrade_no or "",
            "type": "Upgrade",
            "particulars": f"Upgrade - {upg.upgrade_no}",
            "credit": Decimal("0.00"),
            "debit": debit,
            "outstanding": Decimal("0.00"),
            "employee": upg.created_by or ""
        })

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