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 card_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":
            # Get all card machines (finance_table filtered by f_type='cards')
            cards = selectList(finance_table, {"f_type": "card"}, order_by="name")
            return render(
                request,
                "card_ledger.html",
                {
                    "cards": cards,
                },
            )
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


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


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

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

    # Card ID is required
    if not card_id or card_id == "":
        return JsonResponse({
            "data": [],
            "closing_balance": "0.00"
        })

    card_id = int(card_id)

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

    # Get opening balance
    res = get_opening_card_balance(branch_id, financial_year, from_date, card_id)
    running_balance = res["opening_balance"]
    receivable = res.get("receivable", Decimal("0.00"))
    payable = res.get("payable", Decimal("0.00"))
    
    # -----------------------
    # Initial Row: Opening Balance
    # -----------------------
    if running_balance != 0 or receivable != 0 or payable != 0:
        all_rows.append({
            "date": from_date if from_date else financial_year['start_date'],
            "time": "00:00",
            "receipt": "",
            "type": "Opening Balance",
            "particulars": "Opening Balance (Receivable - Payable)",
            "credit": payable,      # Liability/Payable count as Credit
            "debit": receivable,    # Asset/Receivable count as Debit
            "balance": running_balance,
            "employee": ""
        })

    # Collect all transactions
    res = get_card_transactions(branch_id, financial_year, from_date, to_date, card_id)
    all_rows.extend(res["rows"])

    # Sort by datetime
    for row in all_rows:
        if "sort_datetime" not in row:
            d_str = row["date"] if row["date"] else "1970-01-01"
            t_str = row["time"] if row["time"] else "00:00"
            row["sort_datetime"] = datetime.strptime(f"{d_str} {t_str}", "%Y-%m-%d %H:%M")

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

    # Format ledger with running balance
    formatted = []
    current_balance = running_balance

    for idx, row in enumerate(all_rows, start=1):
        if row["type"] != "Opening Balance":
            debit = Decimal(row["debit"])
            credit = Decimal(row["credit"])
            current_balance += (debit - credit)
            row["balance"] = current_balance
        
        row["id"] = idx
        row["date"] = row["sort_datetime"].strftime("%Y-%m-%d %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}"
        
        bal_val = Decimal(row["balance"])
        dr_cr = "DR" if bal_val >= 0 else "CR"
        row["balance"] = f"{abs(bal_val):,.2f} {dr_cr}"

        row.pop("sort_datetime", None)

        formatted.append(row)

    dr_cr_total = "DR" if current_balance >= 0 else "CR"
    closing_balance = f"{abs(current_balance):.2f} {dr_cr_total}"

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


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


def get_opening_card_balance(branch_id, financial_year, from_date, card_id):
    """Calculate opening card balance"""
    # Filter by f_type='cards' ensures we look at card machines
    card = select_row(finance_table, {"id": card_id, "f_type": "card"})
    opening_balance = Decimal("0.00")
    receivable = Decimal("0.00")
    payable = Decimal("0.00")
    
    if card:
        receivable = Decimal(card.receivable or 0)
        payable = Decimal(card.payable or 0)
        opening_balance = receivable - payable

    return {
        "opening_balance": opening_balance,
        "receivable": receivable,
        "payable": payable
    }


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


def get_card_transactions(branch_id, financial_year, from_date, to_date, card_id):
    """Collect all card transactions"""
    rows = []
    
    date_filter = {}
    if from_date and to_date:
        date_filter["date__range"] = [from_date, to_date]
    
    # --------------------------------------------------
    # 1️⃣ SALES (Debit - Receivable Increases)
    # --------------------------------------------------
    # Sales where card_id is set
    sales = transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        card_id=card_id,
        **date_filter
    )

    for sale in sales:
        sales_order = select_row(sales_order_table, {'id': sale.tm_sales_id})
        inv_no = sales_order.inv_no if sales_order else ""
        
        rows.append({
            "date": sale.date,
            "time": format_hr_m(sale.time or time(0, 0)),
            "receipt": inv_no,
            "type": "Sales",
            "particulars": f"Card Sales - {inv_no}",
            "credit": Decimal("0.00"),
            "debit": Decimal(sale.amount or 0), # Amount to Receive (Debit)
            "balance": Decimal("0.00"),
            "employee": sale.created_by or ""
        })

    # --------------------------------------------------
    # 2️⃣ SALES RETURN (Credit - Receivable Decreases)
    # --------------------------------------------------
    returns = return_transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        card_id=card_id, 
        **date_filter
    )

    for ret in returns:
        try:
            # Check sales_return_table first as per project context
            return_order = select_row(sales_return_table, {'id': ret.tm_return_id})
            if not return_order:
                 return_order = select_row(sales_return_table, {'id': ret.tm_return_id})

            ret_no = return_order.sr_no if return_order and hasattr(return_order, 'sr_no') else (return_order.return_no if return_order else "")
            
        except:
            ret_no = ""

        rows.append({
            "date": ret.date,
            "time": format_hr_m(ret.time or time(0, 0)),
            "receipt": ret_no,
            "type": "Sales Return",
            "particulars": f"Card Return - {ret_no}",
            "credit": Decimal(ret.amount or 0), # Refund (Credit)
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": ret.created_by or ""
        })

    # --------------------------------------------------
    # 3️⃣ COLLECTIONS (Credit - Money In from Settlement)
    # --------------------------------------------------
    # Logic: When money settles from Card machine to Bank, it's a 'Collection'?
    # Or is it a Voucher? Usually settlements are vouchers (Bank Receipt).
    # But checking collection table just in case it's used for card settlements.
    
    # Assuming collection not used for card settlements usually (that would be bank receipt voucher)
    # But code structure allows it if future needed.
    # Checking for now if tm_collection_table has card_id? No, it has bank_id and finance_id.
    # Finance ID field is often used for Card ID in some systems if unified.
    # However, commonly card_id is separate.
    # Let's check models. 
    # tm_collection_table has finance_id (Line 65) but no card_id explicitly unless finance_id used.
    # voucher_table has card_id (Line 15).
    
    # So skip tm_collection logic unless finance_id is overloaded. 
    # Given user request "from finance_table filter f_type=cards", implies cards ARE finances.
    # So card_id passed here IS a finance_id from finance_table.
    # So we CAN query tm_collection_table using finance_id=card_id.
    
    collections = tm_collection_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        finance_id=card_id, # Using card_id as finance_id
        **date_filter
    )

    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"Settlement? - {coll.collection_no}",
            "credit": Decimal(coll.total_amount or 0), 
            "debit": Decimal("0.00"),
            "balance": Decimal("0.00"),
            "employee": coll.created_by or ""
        })

    # --------------------------------------------------
    # 4️⃣ VOUCHERS
    # --------------------------------------------------
    vouchers = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        card_id=card_id,
        **date_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:
            # Settlement Received -> Credit Receivable
            amount = Decimal(vouch.receivable_amount or 0)
            credit = amount
            v_type = "Receipt"
        elif is_payment:
            # Charges/Fees Paid -> Debit Receivable ?
            amount = Decimal(vouch.payable_amount or 0)
            debit = amount
            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}",
                "credit": credit,
                "debit": debit,
                "balance": Decimal("0.00"),
                "employee": vouch.created_by or ""
            })

    return {
        "rows": rows,
    }