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 sales.models import *

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


def format_hr_m(date):
    return date.strftime('%H:%M') if date else None


def cash_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":
            return render(
                request,
                "cash_ledger.html",
                {},
            )
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


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


def cash_ledger_view(request):
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)

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

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

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

    # Get opening balance
    res = get_opening_cash_balance(branch_id, financial_year, from_date)
    running_balance = res["opening_balance"]
    
    if res["opening_balance"] != Decimal("0.00"):
        all_rows.append({
            "date": from_date if from_date else financial_year['start_date'],
            "time": "00:00",
            "receipt": "",
            "type": "Opening Balance",
            "particulars": "Opening Cash Balance",
            "credit": Decimal("0.00"),
            "debit": Decimal("0.00"),
            "balance": running_balance,
            "employee": ""
        })

    # Collect all cash transactions
    trans_res = get_cash_transactions(branch_id, financial_year, from_date, to_date)
    all_rows.extend(trans_res["rows"])

    # Ensure all rows have sort_datetime
    for row in all_rows:
        if "sort_datetime" not in row:
            if isinstance(row['date'], str):
                row_date = row['date']
            else:
                row_date = row['date'].strftime("%Y-%m-%d")
            
            row["sort_datetime"] = datetime.strptime(
                f"{row_date} {row['time']}",
                "%Y-%m-%d %H:%M"
            )

    # Sort by datetime and priority (OB -> Credit -> Debit)
    def get_cash_priority(row):
        if row["type"] == "Opening Balance":
            return 0
        if Decimal(row["credit"]) > 0:
            return 1  # Credits first
        return 2      # Debits last

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

    # Format ledger with running balance
    formatted = []
    # Start with the opening balance calculated at the beginning
    running_balance = res["opening_balance"]

    for idx, row in enumerate(all_rows, start=1):
        # Skip opening balance row for recalculation
        if row["type"] != "Opening Balance":
            running_balance += Decimal(row["credit"]) - Decimal(row["debit"])
            row["balance"] = running_balance

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

        # Format amounts
        row["credit"] = f"{Decimal(row['credit']):,.2f}"
        row["debit"] = f"{Decimal(row['debit']):,.2f}"
        row["balance"] = f"{Decimal(row['balance']):,.2f}"

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

        formatted.append(row)

    closing_balance = f"{running_balance:.2f}"

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


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


def get_opening_cash_balance(branch_id, financial_year, from_date):
    """Calculate opening cash balance up to from_date"""
    opening_balance = Decimal("0.00")
    
    # 1. Start with Branch Capital
    branch = select_row(branch_table, {"id": branch_id})
    if branch:
        opening_balance = Decimal(branch.captial or 0)
    
    # 2. Add/Subtract transactions before from_date (within current FY)
    if from_date:
        # Helper for date filter
        bf = lambda field: {f"{field}__lt": from_date}
        common_filt = {"branch_id": branch_id, "current_fy": financial_year, "status": 1, "is_active": 1}

        # Expenses (Cash Out)
        exp = expense_table.objects.filter(**common_filt, **bf("date")).aggregate(total=Sum('cash'))['total'] or 0
        opening_balance -= Decimal(exp)

        # Purchases (Cash Out)
        pur = purchase_inward_table.objects.filter(**common_filt, **bf("pu_date")).aggregate(total=Sum('cash'))['total'] or 0
        opening_balance -= Decimal(pur)

        # Purchase Return (Cash In)
        pur_ret = purchase_return_table.objects.filter(**common_filt, **bf("pr_date"), pr_status__iexact='approved').aggregate(total=Sum('cash'))['total'] or 0
        opening_balance += Decimal(pur_ret)

        # Sales (Cash In)
        sales = transaction_table.objects.filter(**common_filt, payment_type='cash', **bf("date")).aggregate(total=Sum('amount'))['total'] or 0
        opening_balance += Decimal(sales)

        # Sales Return (Cash Out)
        sales_ret = return_transaction_table.objects.filter(**common_filt, payment_type='cash', **bf("date")).aggregate(total=Sum('amount'))['total'] or 0
        opening_balance -= Decimal(sales_ret)

        # Material In (Cash Out)
        mat = material_inward_table.objects.filter(**common_filt, cash__gt=0, **bf("transfer_date")).aggregate(total=Sum('cash'))['total'] or 0
        opening_balance -= Decimal(mat)

        # Contra
        c_b2c = contra_sales_table.objects.filter(**common_filt, payment_type='bank_to_cash', **bf("date")).aggregate(total=Sum('amount'))['total'] or 0
        opening_balance += Decimal(c_b2c)
        c_c2b = contra_sales_table.objects.filter(**common_filt, payment_type='cash_to_bank', **bf("date")).aggregate(total=Sum('amount'))['total'] or 0
        opening_balance -= Decimal(c_c2b)

        # Claims & Upgrades (Cash In)
        claims = tm_claim_table.objects.filter(**common_filt, **bf("date")).aggregate(total=Sum('total_amount'))['total'] or 0
        opening_balance += Decimal(claims)
        upgrades = tm_upgrade_table.objects.filter(**common_filt, **bf("date")).aggregate(total=Sum('total_amount'))['total'] or 0
        opening_balance += Decimal(upgrades)

        # Vouchers
        v_qs = voucher_table.objects.filter(**common_filt, payment_mode='cash', **bf("date"))
        for v in v_qs:
            if v.voucher_type in ['receipt', 'cash_receipt']:
                opening_balance += Decimal(v.receivable_amount or 0)
            else:
                opening_balance -= Decimal(v.payable_amount or 0)

    return {
        "opening_balance": opening_balance
    }


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


def get_cash_transactions(branch_id, financial_year, from_date, to_date):
    """Collect all cash transactions from various modules"""
    rows = []
    
    # Helper for date filtering based on field name
    def get_dt_filter(field_name):
        return {f"{field_name}__range": [from_date, to_date]} if from_date and to_date else {}
    
    # --------------------------------------------------
    # 1️⃣ EXPENSES (Cash Out - Debit)
    # --------------------------------------------------
    expenses = expense_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        cash__gt=0,  # Filter where cash field has value
        **get_dt_filter("date")
    )

    for exp in expenses:
        expense_name = getItemNameById(expense_master_table, exp.expense_id)
        rows.append({
            "date": exp.date,
            "time": format_hr_m(exp.time or time(0, 0)),
            "receipt": "",  # expense_table doesn't have expense_no field
            "type": "Expense",
            "particulars": f"Expense - <span class='text-danger'>{expense_name}</span>",
            "credit": Decimal("0.00"),
            "debit": Decimal(exp.cash or 0),  # Use cash field instead of amount
            "balance": Decimal("0.00"),
            "employee": exp.created_by or ""
        })

    # --------------------------------------------------
    # 2️⃣ PURCHASE (Cash Out - Debit)
    # --------------------------------------------------
    purchases = purchase_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        cash__gt=0,
        **get_dt_filter("pu_date")
    )

    for pur in purchases:
        rows.append({
            "date": pur.pu_date,
            "time": format_hr_m(pur.time or time(0, 0)),
            "receipt": pur.pu_no or "",
            "type": "Purchase",
            "particulars": f"Purchase - Cash Payment (Inv: {pur.supplier_no or pur.pu_no})",
            "credit": Decimal("0.00"),
            "debit": Decimal(pur.cash or 0),
            "balance": Decimal("0.00"),
            "employee": pur.created_by or ""
        })

    # --------------------------------------------------
    # 2.1 Purchase Return (Cash In - Credit)
    # --------------------------------------------------
    purch_returns = purchase_return_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        pr_status__iexact='approved',
        cash__gt=0, 
        **get_dt_filter("pr_date")
    )

    for pr in purch_returns:
        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 - Cash Refund ({pr.pr_no})",
            "credit": Decimal(pr.cash or 0),
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": pr.created_by or ""
        })
    
    # --------------------------------------------------
    # 3️⃣ SALES - Cash Payments (Cash In - Credit)
    # --------------------------------------------------
    cash_sales = transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type="cash",
        **get_dt_filter("date")
    )

    for sale in cash_sales:
        sales_order = select_row(sales_order_table, {'id': sale.tm_sales_id})
        if sales_order:
            rows.append({
                "date": sale.date or sales_order.inv_date,
                "time": format_hr_m(sale.time or time(0, 0)),
                "receipt": sales_order.inv_no or "",
                "type": "Sales",
                "particulars": f"Sales - Cash Payment (Inv: {sales_order.inv_no})",
                "credit": Decimal(sale.amount or 0),
                "debit": Decimal("0.00"),
                "balance": Decimal("0.00"),
                "employee": sale.created_by or ""
            })

    # --------------------------------------------------
    # 4️⃣ SALES RETURN - Cash Refunds (Cash Out - Debit)
    # --------------------------------------------------
    cash_returns = return_transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type="cash",
        **get_dt_filter("date")
    )

    for ret in cash_returns:
        return_order = select_row(sales_return_table, {'id': ret.tm_return_id})
        if return_order:
            rows.append({
                "date": ret.date or return_order.sr_date,
                "time": format_hr_m(ret.time or time(0, 0)),
                "receipt": return_order.sr_no or "",
                "type": "Sales Return",
                "particulars": f"Sales Return - Cash Refund (Ret: {return_order.sr_no})",
                "credit": Decimal("0.00"),
                "debit": Decimal(ret.amount or 0),
                "balance": Decimal("0.00"),
                "employee": ret.created_by or ""
            })


    # --------------------------------------------------
    # 4️⃣ MATERIAL IN (Cash transactions if any)
    # --------------------------------------------------
    material_inwards = material_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        cash__gt=0,  # Filter where cash field has value
        **get_dt_filter("transfer_date")
    )

    for mat in material_inwards:
        rows.append({
            "date": mat.transfer_date,
            "time": format_hr_m(mat.time or time(0, 0)),
            "receipt": mat.transfer_no or "",
            "type": "Material In",
            "particulars": f"Material Inward - <span class='text-info'>{mat.transfer_no}</span>",
            "credit": Decimal("0.00"),
            "debit": Decimal(mat.cash or 0),  # Use cash field
            "balance": Decimal("0.00"),
            "employee": mat.created_by or ""
        })

    # --------------------------------------------------
    # 5️⃣ CONTRA SALES (Cash transactions)
    # --------------------------------------------------
    # cash_to_bank: Cash going OUT to bank (Debit)
    # bank_to_cash: Cash coming IN from bank (Credit)
    contra_sales = contra_sales_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type__in=["cash_to_bank", "bank_to_cash"],  # Both types affect cash
        **get_dt_filter("date")
    )

    for contra in contra_sales:
        bank_name = getItemNameById(bank_table, contra.bank_id)
        
        # Determine credit/debit based on payment type
        if contra.payment_type == "cash_to_bank":
            # Cash going to bank = Cash Out = Debit
            credit_amt = Decimal("0.00")
            debit_amt = Decimal(contra.amount or 0)
            particulars = f"Contra: Cash to Bank ({bank_name})"
        else:  # bank_to_cash
            # Cash coming from bank = Cash In = Credit
            credit_amt = Decimal(contra.amount or 0)
            debit_amt = Decimal("0.00")
            particulars = f"Contra: Bank to Cash ({bank_name})"
        
        rows.append({
            "date": contra.date,
            "time": format_hr_m(contra.time or time(0, 0)),
            "receipt": contra.contra_no or "",
            "type": "Contra",
            "particulars": particulars,
            "credit": credit_amt,
            "debit": debit_amt,
            "balance": Decimal("0.00"),
            "employee": contra.created_by or ""
        })

    # --------------------------------------------------
    # 6️⃣ CLAIM (No payment mode - all claims are considered)
    # --------------------------------------------------
    claims = tm_claim_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **get_dt_filter("date")
    )

    for claim in claims:
        rows.append({
            "date": claim.date,
            "time": format_hr_m(claim.time or time(0, 0)),
            "receipt": claim.claim_no or "",
            "type": "Claim",
            "particulars": f"Claim - {claim.claim_no}",
            "credit": Decimal(claim.total_amount or 0),
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": claim.created_by or ""
        })


    # --------------------------------------------------
    # 8️⃣ UPGRADE (No payment mode - all upgrades are considered)
    # --------------------------------------------------
    upgrades = tm_upgrade_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        **get_dt_filter("date")
    )

    for upg in upgrades:
        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(upg.total_amount or 0),
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": upg.created_by or ""
        })

    # --------------------------------------------------
    # 9️⃣ VOUCHER (Cash transactions)
    # --------------------------------------------------
    vouchers = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_mode="cash",  # Filter by payment_mode field
        **get_dt_filter("date")
    )

    for vouch in vouchers:
        # Determine if voucher is cash in or cash out based on voucher type
        is_receipt = vouch.voucher_type in ["receipt", "cash_receipt"]
        
        # Use receivable_amount for receipts (cash in) and payable_amount for payments (cash out)
        amount = Decimal(vouch.receivable_amount or 0) if is_receipt else Decimal(vouch.payable_amount or 0)
        
        rows.append({
            "date": vouch.date,
            "time": format_hr_m(vouch.time or time(0, 0)),
            "receipt": vouch.voucher_no or "",
            "type": "Voucher",
            "particulars": f"Voucher - {vouch.voucher_type} ({vouch.voucher_no})",
            "credit": amount if is_receipt else Decimal("0.00"),
            "debit": amount if not is_receipt else Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": vouch.created_by or ""
        })

    return {
        "rows": rows,
        "opening_balance": Decimal("0.00")
    }