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 exchange_ledger(request):
    if "user_id" in request.session:
        user_type = request.session.get("user_type")
        branch_id = request.session.get("branch_id")
        branch = branch_table.objects.filter(status=1)
        if user_type == "stores":
            # Get all finance companies for dropdown
            return render(
                request,
                "exchange_ledger.html",
                {"branch": branch})
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")


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


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

    # Note: Exchange ledger uses current branch only as per request
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')
    branch_id = request.POST.get('branch_id')

    # Filter Sales Orders (tm_sales) based on date range
    query = Q( status=1)
    query &= ~Q(sales_status='rejected')
    if branch_id:
        query &= Q(branch_id=branch_id)
    if from_date and to_date:
        query &= Q(inv_date__range=[from_date, to_date])
    
    sales_orders = sales_order_table.objects.filter(query).order_by('inv_date', 'time')
    
    all_rows = []
    current_balance = Decimal("0.00")

    for master in sales_orders:
        # Get its exchange transaction(s)
        exchange_txs = transaction_table.objects.filter(
            tm_sales_id=master.id,
            payment_type='exchange',
            status=1,
            is_active=1,
            branch_id=branch_id
        ).exclude(exchange_status='rejected') # Exclude rejected exchange transactions

        for tx in exchange_txs:
            # 1. Sale Row (Debit)
            # This increases what we are "waiting for" (receivable)
            all_rows.append({
                "date": master.inv_date.strftime("%Y-%m-%d"),
                "time": master.time.strftime("%H:%M") if master.time else "00:00",
                "receipt": master.inv_no,
                "type": "Exchange Sale",
                "particulars": f"Sale - {master.inv_no} ({master.customer_name})",
                "credit": Decimal("0.00"),
                "debit": Decimal(tx.amount or 0),
                "employee": master.employee_id,
                "sort_datetime": datetime.combine(master.inv_date, master.time or time(0,0))
            })

            # 2. Collection Rows (Credit)
            # Find collections for this specific exchange transaction
            collections = tx_collection_table.objects.filter(
                transaction_id=tx.id,
                status=1,
                is_active=1
            )
            for coll in collections:
                coll_master = tm_collection_table.objects.filter(id=coll.tm_collection_id).first()
                if not coll_master:
                    continue
                
                all_rows.append({
                    "date": coll_master.date.strftime("%Y-%m-%d"),
                    "time": coll_master.time.strftime("%H:%M") if coll_master.time else "00:00",
                    "receipt": coll_master.collection_no,
                    "type": "Exchange Collection",
                    "particulars": f"Collection - {coll_master.collection_no}",
                    "credit": Decimal(coll.amount or 0),
                    "debit": Decimal("0.00"),
                    "employee": coll_master.employee_id,
                    "sort_datetime": datetime.combine(coll_master.date, coll_master.time or time(0,0))
                })

    # Sort all rows by sort_datetime
    all_rows.sort(key=lambda x: x["sort_datetime"])

    # Format output and calculate running balance
    formatted = []
    running_balance = Decimal("0.00")
    
    for idx, row in enumerate(all_rows, start=1):
        debit = row["debit"]
        credit = row["credit"]
        running_balance += (debit - credit)
        
        row["id"] = idx
        # Display as combined date/time
        row["date"] = row["sort_datetime"].strftime("%d-%m-%Y %H:%M")
        row["employee"] = getItemNameById(employee_table, row["employee"]) if row["employee"] else ""
        row["credit"] = format_amount(credit)
        row["debit"] = format_amount(debit)
        
        dr_cr = "DR" if running_balance >= 0 else "CR"
        row["balance"] = f"{abs(running_balance):,.2f} {dr_cr}"
        
        row.pop("sort_datetime")
        formatted.append(row)

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

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