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 bank_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")
            
            
            if is_ho == 1:
                # HO: Show only non-default banks
                banks = selectList(bank_table, {'is_default': 0}, order_by="name")
            else:
                # Branch: Show only the default bank linked to this branch
                banks = selectList(bank_table, {'is_default': 1}, order_by="name")

            
            return render(
                request,
                "bank_ledger.html",
                {
                    "banks": banks,
                },
            )
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


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


def bank_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, 'bank_ledger', "read")
    if not has_access:
        return JsonResponse({'message': 'permission'})

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

    # Bank ID is required
    if not bank_id or bank_id == "":
        return JsonResponse({'message': 'permission_bank', "error_message":"Please Select Bank","data": [],"closing_balance": "0.00"})
       

    bank_id = int(bank_id)

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

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

    # Collect all bank transactions
    trans_res = get_bank_transactions(branch_id, financial_year, from_date, to_date, bank_id)
    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_bank_priority(row):
        if row["type"] == "Opening Balance":
            return 0
        if Decimal(row["credit"]) > 0:
            return 1  # Bank In comes first
        return 2      # Bank Out comes last

    all_rows.sort(key=lambda x: (x["sort_datetime"], get_bank_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_bank_balance(branch_id, financial_year, from_date, bank_id):
    """Calculate opening bank balance"""
    opening_balance = Decimal("0.00")
    
    # Get branch info to determine if HO or not
    branch = select_row(branch_table, {"id": branch_id})
    is_ho = branch.is_ho if branch else 0
    
    if is_ho == 1:
        # HO: Get opening balance from bank_table (non-default banks only)
        bank = select_row(bank_table, {"id": bank_id, "is_default": 0})
        if bank:
            opening_balance = Decimal(bank.opening or 0)
    else:
        # Branch: Get opening balance from branch_table.bank_opening
        # Only if the bank_id matches the branch's default bank
        if branch and branch.bank_id == bank_id:
            opening_balance = Decimal(branch.bank_opening 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, "bank_id": bank_id}

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

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

        # Contra
        # cash_to_bank = Bank In = Credit
        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)
        # bank_to_cash = Bank Out = Debit
        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)

        # Collection (Bank In)
        coll = tm_collection_table.objects.filter(**common_filt, **bf("date")).aggregate(total=Sum('receipt'))['total'] or 0
        opening_balance += Decimal(coll)

        # Vouchers
        v_qs = voucher_table.objects.filter(**common_filt, payment_mode='bank', **bf("date"))
        for v in v_qs:
            if v.voucher_type in ['receipt', 'cash_receipt']:
                opening_balance += Decimal(v.receivable_amount or 0)
            elif v.voucher_type in ['payment', 'cash_payment']:
                opening_balance -= Decimal(v.payable_amount or 0)
        
        # Expenses (Bank Out)
        exp = expense_table.objects.filter(**common_filt, **bf("date")).aggregate(total=Sum('bank'))['total'] or 0
        opening_balance -= Decimal(exp)
    
    return {
        "opening_balance": opening_balance,
    }


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


def get_bank_transactions(branch_id, financial_year, from_date, to_date, bank_id):
    """Collect all bank 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 {}
    
    # --------------------------------------------------
    # 0.1 EXPENSES (Bank Out - Debit)
    # --------------------------------------------------
    expenses = expense_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        bank__gt=0,
        bank_id=bank_id,
        **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": "",
            "type": "Expense",
            "particulars": f"Expense - <span class='text-danger'>{expense_name}</span>",
            "credit": Decimal("0.00"),
            "debit": Decimal(exp.bank or 0),
            "balance": Decimal("0.00"),
            "employee": exp.created_by or ""
        })


    # --------------------------------------------------
    # 1️⃣ SALES - Bank Payments (Bank In - Credit)
    # --------------------------------------------------
    bank_sales = transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type="bank",
        bank_id=bank_id,
        **get_dt_filter("date")
    )

    for sale in bank_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 - Bank 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 ""
            })

    # --------------------------------------------------
    # 2️⃣ SALES RETURN - Bank Refunds (Bank Out - Debit)
    # --------------------------------------------------
    bank_returns = return_transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type="bank",
        bank_id=bank_id,
        **get_dt_filter("date")
    )

    for ret in bank_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 - Bank 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 ""
            })



    # --------------------------------------------------
    # 3️⃣ CONTRA SALES (Bank transactions)
    # --------------------------------------------------
    # cash_to_bank: Cash going to bank = Bank IN = Credit
    # bank_to_cash: Cash coming from bank = Bank OUT = Debit
    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 bank
        bank_id=bank_id,  # Filter by specific bank
        **get_dt_filter("date")
    )

    for contra in contra_sales:
        # Determine credit/debit based on payment type
        if contra.payment_type == "cash_to_bank":
            # Cash to bank = Bank receiving = Bank IN = Credit
            credit_amt = Decimal(contra.amount or 0)
            debit_amt = Decimal("0.00")
            particulars = f"Contra: Cash to Bank ({contra.contra_no})"
        else:  # bank_to_cash
            # Bank to cash = Bank giving = Bank OUT = Debit
            credit_amt = Decimal("0.00")
            debit_amt = Decimal(contra.amount or 0)
            particulars = f"Contra: Bank to Cash ({contra.contra_no})"
        
        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 ""
        })

    # --------------------------------------------------
    # 4️⃣ COLLECTION (Bank transactions)
    # --------------------------------------------------
    collections = tm_collection_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        bank_id=bank_id,
        **get_dt_filter("date")
    )

    for coll in collections:
        rows.append({
            "date": coll.date,
            "time": format_hr_m(coll.time or time(0, 0)),
            "receipt": coll.collection_no or "",
            "type": "Collection",
            "particulars": f"Collection - {coll.collection_no}",
            "credit": Decimal(coll.receipt or 0),
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": coll.created_by or ""
        })

    # --------------------------------------------------
    # 5️⃣ VOUCHER (Bank transactions)
    # --------------------------------------------------
    vouchers = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_mode="bank",
        bank_id=bank_id,
        **get_dt_filter("date")
    )

    for vouch in vouchers:
        if vouch.voucher_type in ['receipt', 'cash_receipt']:
            is_receipt = True
        elif vouch.voucher_type in ['payment', 'cash_payment']:
            is_receipt = False
        else:
            continue # Skip unknown voucher types or handle as needed
        
        # Use receivable_amount for receipts (bank in) and payable_amount for payments (bank 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")
    }