import frappe
from frappe import _
from frappe.model.document import Document
from decimal import Decimal, ROUND_HALF_UP
import json

# Константа для tolerance округления
ROUNDING_TOLERANCE = Decimal('0.01')

class VATallocation(Document):
    def validate(self):
        # Check if document with same year and month already exists
        self.validate_unique_year_month()
        # Recalculate all totals from links
        #self.recalculate_totals_from_links()
        # Validate all allocations
        self.validate_allocations()
    
    def validate_unique_year_month(self):
        """
        Ensure only one VAT allocation document exists per year and month
        """
        if not self.year or not self.month:
            return
        
        existing = frappe.db.exists({
            'doctype': 'VAT allocation',
            'year': self.year,
            'month': self.month,
            'name': ['!=', self.name]
        })
        
        if existing:
            frappe.throw(_(f"VAT allocation for {self.month} {self.year} already exists: {existing}"))
    
    def recalculate_totals_from_links(self):
        """
        Recalculate all allocated/unallocated amounts from allocation links
        """
        # Reset all allocated amounts
        for pe_row in self.payment_entry_table:
            pe_row.vat_free_allocated = 0
            pe_row.vat_allocated_allocated = 0
            pe_row.allocated_amount = 0
        
        for si_row in self.sales_invoice_table:
            si_row.allocated_amount = 0
        
        for si_row in self.sales_invoice_previous_table:
            si_row.allocated_amount = 0
        
        # Calculate from links
        for link in self.allocation_links:
            allocated = Decimal(str(link.allocated_amount or 0))
            if allocated <= 0:
                continue
            
            # Update payment entry row
            for pe_row in self.payment_entry_table:
                if pe_row.name == link.payment_entry_row:
                    current_allocated = Decimal(str(pe_row.allocated_amount or 0))
                    pe_row.allocated_amount = float(current_allocated + allocated)
                    
                    if link.allocation_type == 'vat_free':
                        current_vat_free = Decimal(str(pe_row.vat_free_allocated or 0))
                        pe_row.vat_free_allocated = float(current_vat_free + allocated)
                    else:
                        current_vat_allocated = Decimal(str(pe_row.vat_allocated_allocated or 0))
                        pe_row.vat_allocated_allocated = float(current_vat_allocated + allocated)
                    break
            
            # Update sales invoice row - check both tables
            found = False
            for si_row in self.sales_invoice_table:
                if si_row.name == link.sales_invoice_row:
                    current_allocated = Decimal(str(si_row.allocated_amount or 0))
                    si_row.allocated_amount = float(current_allocated + allocated)
                    found = True
                    break
            
            if not found:
                for si_row in self.sales_invoice_previous_table:
                    if si_row.name == link.sales_invoice_row:
                        current_allocated = Decimal(str(si_row.allocated_amount or 0))
                        si_row.allocated_amount = float(current_allocated + allocated)
                        break
        
        # Calculate unallocated amounts
        for pe_row in self.payment_entry_table:
            total = Decimal(str(pe_row.payment_entry_amount or 0))
            allocated = Decimal(str(pe_row.allocated_amount or 0))
            pe_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
        
        for si_row in self.sales_invoice_table:
            total = Decimal(str(si_row.amount or 0))
            allocated = Decimal(str(si_row.allocated_amount or 0))
            si_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
        
        for si_row in self.sales_invoice_previous_table:
            total = Decimal(str(si_row.amount or 0))
            allocated = Decimal(str(si_row.allocated_amount or 0))
            si_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
    
    def validate_allocations(self):
        """
        Validate that all allocations are mathematically correct
        """
        # Validate payment entries
        for pe_row in self.payment_entry_table:
            vat_free_total = Decimal(str(pe_row.vat_free_amount or 0))
            vat_free_spent = Decimal(str(pe_row.vat_free_allocated or 0))
            vat_allocated_total = Decimal(str(pe_row.vat_allocated_amount or 0))
            vat_allocated_spent = Decimal(str(pe_row.vat_allocated_allocated or 0))
            
            # Check non-negative
            if vat_free_spent < 0 or vat_allocated_spent < 0:
                frappe.throw(_(f"Spent amounts cannot be negative for payment entry {pe_row.payment_entry}"))
            
            # Check not exceeding totals (with tolerance)
            if vat_free_spent > vat_free_total + ROUNDING_TOLERANCE:
                frappe.throw(_(f"VAT Free spent exceeds total for payment entry {pe_row.payment_entry}"))
            
            if vat_allocated_spent > vat_allocated_total + ROUNDING_TOLERANCE:
                frappe.throw(_(f"VAT Allocated spent exceeds total for payment entry {pe_row.payment_entry}"))
        
        # Validate sales invoice items - both tables
        for si_row in self.sales_invoice_table:
            amount = Decimal(str(si_row.amount or 0))
            allocated = Decimal(str(si_row.allocated_amount or 0))
            unallocated = Decimal(str(si_row.unallocated_amount or 0))
            
            # Check non-negative
            if allocated < 0 or unallocated < -ROUNDING_TOLERANCE:
                frappe.throw(_(f"Allocated amounts cannot be negative for {si_row.item_tax_template}"))
            
            # Check sum equals total (with tolerance)
            total_check = allocated + unallocated
            if abs(total_check - amount) > ROUNDING_TOLERANCE:
                frappe.throw(_(f"Allocated + Unallocated != Amount for {si_row.item_tax_template}"))
        
        for si_row in self.sales_invoice_previous_table:
            amount = Decimal(str(si_row.amount or 0))
            allocated = Decimal(str(si_row.allocated_amount or 0))
            unallocated = Decimal(str(si_row.unallocated_amount or 0))
            
            # Check non-negative
            if allocated < 0 or unallocated < -ROUNDING_TOLERANCE:
                frappe.throw(_(f"Allocated amounts cannot be negative for previous month {si_row.item_tax_template}"))
            
            # Check sum equals total (with tolerance)
            total_check = allocated + unallocated
            if abs(total_check - amount) > ROUNDING_TOLERANCE:
                frappe.throw(_(f"Allocated + Unallocated != Amount for previous month {si_row.item_tax_template}"))

@frappe.whitelist()
def validate_all_allocations(vat_allocation_name):
    """
    Manual validation function that can be called from UI
    """
    try:
        doc = frappe.get_doc('VAT allocation', vat_allocation_name)
        doc.validate_allocations()
        return {
            'success': True,
            'message': _('All allocations are valid')
        }
    except Exception as e:
        return {
            'success': False,
            'message': str(e)
        }

@frappe.whitelist()
def get_payment_entries(year, month, customers=None, min_amount=None, max_amount=None):
    """
    Get Payment Entries for specified month and year with optional filters
    customers can be a JSON string of customer list
    """
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        frappe.throw(_("Invalid month"))
    
    # Build WHERE conditions
    conditions = """
        pe.docstatus = 1
        AND YEAR(pe.posting_date) = %(year)s
        AND MONTH(pe.posting_date) = %(month)s
        AND pe.payment_type = 'Receive'
        AND pe.party_type = 'Customer'
    """
    
    params = {
        'year': year,
        'month': month_number
    }
    
    # Add optional filters
    if customers:
        # Parse customers JSON string if it's a string
        if isinstance(customers, str):
            customers = json.loads(customers)
        
        if customers and len(customers) > 0:
            customer_list = [c.get('customer') for c in customers if c.get('customer')]
            if customer_list:
                conditions += " AND pe.party IN %(customers)s"
                params['customers'] = customer_list
    
    if min_amount:
        conditions += " AND pe.paid_amount >= %(min_amount)s"
        params['min_amount'] = min_amount
    
    if max_amount:
        conditions += " AND pe.paid_amount <= %(max_amount)s"
        params['max_amount'] = max_amount
    
    # SQL query to get Payment Entries
    payment_entries = frappe.db.sql(f"""
        SELECT 
            pe.name as payment_entry,
            pe.party as customer,
            pe.paid_amount as payment_entry_amount,
            pe.posting_date
        FROM 
            `tabPayment Entry` pe
        WHERE 
            {conditions}
        ORDER BY 
            pe.posting_date
    """, params, as_dict=1)
    
    return payment_entries

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def get_journal_entry_query(doctype, txt, searchfield, start, page_len, filters):
    """
    Custom query for filtering Journal Entries
    """
    customer = filters.get('customer')
    year = filters.get('year')
    month = filters.get('month')
    
    if not all([customer, year, month]):
        return []
    
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        return []
    
    # SQL query to get matching Journal Entries
    # txt is already sanitized by @frappe.validate_and_sanitize_search_inputs decorator
    return frappe.db.sql("""
        SELECT DISTINCT
            je.name, je.posting_date, je.total_debit
        FROM 
            `tabJournal Entry` je
        WHERE 
            je.docstatus = 1
            AND YEAR(je.posting_date) = %(year)s
            AND MONTH(je.posting_date) = %(month)s
            AND je.name LIKE %(txt)s
            AND EXISTS (
                SELECT 1 
                FROM `tabJournal Entry Account` jea_credit
                WHERE jea_credit.parent = je.name
                    AND jea_credit.account LIKE '211%%'
                    AND jea_credit.credit_in_account_currency > 0
                    AND jea_credit.party = %(customer)s
            )
            AND EXISTS (
                SELECT 1
                FROM `tabJournal Entry Account` jea_debit
                WHERE jea_debit.parent = je.name
                    AND jea_debit.account LIKE '226%%'
                    AND jea_debit.debit_in_account_currency > 0
            )
        ORDER BY 
            je.posting_date DESC
        LIMIT %(start)s, %(page_len)s
    """, {
        'year': year,
        'month': month_number,
        'customer': customer,
        'txt': f"%{txt}%",
        'start': start,
        'page_len': page_len
    })

@frappe.whitelist()
def get_sales_invoice_items(year, month, customers=None, min_amount=None, max_amount=None):
    """
    Get Sales Invoice items for specified month and year, grouped by customer, item_tax_template, and tax_article
    Includes all items, even those without tax_article
    customers can be a JSON string of customer list
    """
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        frappe.throw(_("Invalid month"))
    
    # Build WHERE conditions
    conditions = """
        si.docstatus = 1
        AND YEAR(si.posting_date) = %(year)s
        AND MONTH(si.posting_date) = %(month)s
    """
    
    params = {
        'year': year,
        'month': month_number
    }
    
    # Add optional customer filter
    if customers:
        # Parse customers JSON string if it's a string
        if isinstance(customers, str):
            customers = json.loads(customers)
        
        if customers and len(customers) > 0:
            customer_list = [c.get('customer') for c in customers if c.get('customer')]
            if customer_list:
                conditions += " AND si.customer IN %(customers)s"
                params['customers'] = customer_list
    
    # SQL query with HAVING clause for amount filters after grouping
    having_conditions = []
    
    if min_amount:
        having_conditions.append("SUM(sii.amount) >= %(min_amount)s")
        params['min_amount'] = min_amount
    
    if max_amount:
        having_conditions.append("SUM(sii.amount) <= %(max_amount)s")
        params['max_amount'] = max_amount
    
    having_clause = ""
    if having_conditions:
        having_clause = " HAVING " + " AND ".join(having_conditions)
    
    # SQL query to get Sales Invoice items grouped by customer, item_tax_template, and tax_article
    invoice_items = frappe.db.sql(f"""
        SELECT 
            si.customer,
            sii.item_tax_template,
            sii.tax_article,
            SUM(sii.amount) as amount,
            SUM(sii.net_amount) as net_amount,
            SUM(sii.qty) as total_qty
        FROM 
            `tabSales Invoice` si
        INNER JOIN 
            `tabSales Invoice Item` sii ON si.name = sii.parent
        WHERE 
            {conditions}
        GROUP BY 
            si.customer, sii.item_tax_template, sii.tax_article
        {having_clause}
        ORDER BY 
            si.customer, sii.item_tax_template, sii.tax_article
    """, params, as_dict=1)
    
    return invoice_items

@frappe.whitelist()
def get_sales_invoice_items_previous_months(year, month, customers=None, min_amount=None, max_amount=None):
    """
    Get Sales Invoice items from previous VAT Allocation documents where unallocated_amount > 0
    Grouped by customer, item_tax_template, and tax_article
    """
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        frappe.throw(_("Invalid month"))
    
    # Parse customers filter
    customer_list = None
    if customers:
        if isinstance(customers, str):
            customers = json.loads(customers)
        if customers and len(customers) > 0:
            customer_list = [c.get('customer') for c in customers if c.get('customer')]
    
    # Find all previous VAT Allocation documents
    # We need to find documents where (year < current_year) OR (year == current_year AND month < current_month)
    vat_allocations = frappe.db.sql("""
        SELECT name, year, month
        FROM `tabVAT allocation`
        WHERE docstatus = 1
        AND (
            year < %(year)s
            OR (year = %(year)s AND month < %(month)s)
        )
        ORDER BY year DESC, month DESC
    """, {
        'year': year,
        'month': month_number
    }, as_dict=1)
    
    if not vat_allocations:
        return []
    
    # Collect all items with unallocated_amount > 0 from previous VAT Allocations
    all_items = []
    
    for vat_alloc in vat_allocations:
        # Get sales invoice items from this VAT Allocation
        items = frappe.db.sql("""
            SELECT 
                customer,
                item_tax_template,
                tax_article,
                unallocated_amount
            FROM `tabVAT allocation sales invoice`
            WHERE parent = %(parent)s
            AND unallocated_amount > 0
        """, {
            'parent': vat_alloc.name
        }, as_dict=1)
        
        all_items.extend(items)
    
    # Group by customer, item_tax_template, tax_article and sum unallocated amounts
    grouped_items = {}
    
    for item in all_items:
        # Apply customer filter
        if customer_list and item.customer not in customer_list:
            continue
        
        key = (item.customer, item.item_tax_template or '', item.tax_article or '')
        
        if key not in grouped_items:
            grouped_items[key] = {
                'customer': item.customer,
                'item_tax_template': item.item_tax_template,
                'tax_article': item.tax_article,
                'amount': Decimal('0')
            }
        
        grouped_items[key]['amount'] += Decimal(str(item.unallocated_amount))
    
    # Convert to list and apply amount filters
    result = []
    for key, item in grouped_items.items():
        amount = float(item['amount'])
        
        # Apply min/max amount filters
        if min_amount and amount < float(min_amount):
            continue
        if max_amount and amount > float(max_amount):
            continue
        
        result.append({
            'customer': item['customer'],
            'item_tax_template': item['item_tax_template'],
            'tax_article': item['tax_article'],
            'amount': amount,
            'unallocated_amount': amount,  # Initially all is unallocated
            'allocated_amount': 0
        })
    
    # Sort by customer, item_tax_template, tax_article
    result.sort(key=lambda x: (x['customer'], x['item_tax_template'] or '', x['tax_article'] or ''))
    
    return result

@frappe.whitelist()
def get_allocation_data(vat_allocation_name, payment_entry_row_name, customer):
    """
    Get data needed for allocation dialog with existing allocations
    """
    # Validate customer is active
    customer_doc = frappe.get_cached_value('Customer', customer, ['disabled'], as_dict=True)
    if customer_doc and customer_doc.get('disabled'):
        frappe.throw(_("Customer {0} is disabled").format(customer))
    
    # Get the payment entry row
    payment_row = frappe.db.get_value(
        'VAT allocation payment entry',
        payment_entry_row_name,
        [
            'vat_free_amount', 
            'vat_allocated_amount', 
            'vat_free_allocated',
            'vat_allocated_allocated',
            'allocated_amount', 
            'unallocated_amount'
        ],
        as_dict=1
    )
    
    if not payment_row:
        frappe.throw(_("Payment entry row not found"))
    
    # Calculate available amounts using Decimal for precision
    vat_free_amount = Decimal(str(payment_row.vat_free_amount or 0))
    vat_free_allocated = Decimal(str(payment_row.vat_free_allocated or 0))
    vat_allocated_amount = Decimal(str(payment_row.vat_allocated_amount or 0))
    vat_allocated_allocated = Decimal(str(payment_row.vat_allocated_allocated or 0))
    
    vat_free_available = float(vat_free_amount - vat_free_allocated)
    vat_allocated_available = float(vat_allocated_amount - vat_allocated_allocated)
    
    # Get sales invoice items for this customer from the same VAT allocation document
    doc = frappe.get_doc('VAT allocation', vat_allocation_name)
    
    # Get existing allocations from this payment row
    existing_allocations = {}
    for link in doc.allocation_links:
        if link.payment_entry_row == payment_entry_row_name:
            existing_allocations[link.sales_invoice_row] = {
                'amount': link.allocated_amount,
                'type': link.allocation_type
            }
    
    # VAT Free templates (ƏDV 0% and ƏDV-dən azadolma)
    vat_free_items = []
    # VAT templates (ƏDV 18% and ƏDV daxil 18%)
    vat_items = []
    
    vat_free_items_previous = []
    vat_items_previous = []
    
    # Current month items
    for item in doc.sales_invoice_table:
        if item.customer == customer:
            # Get existing allocation for this item
            existing_alloc = existing_allocations.get(item.name, {'amount': 0, 'type': None})
            
            item_data = {
                'name': item.name,
                'item_tax_template': item.item_tax_template or '',
                'tax_article': item.tax_article or '',
                'amount': item.amount,
                'unallocated_amount': item.unallocated_amount,
                'allocated_amount': item.allocated_amount or 0,
                'current_allocation': existing_alloc['amount']  # Allocation from THIS payment row
            }
            
            template = (item.item_tax_template or '').lower()
            
            # Check if it's VAT free template
            if 'ədv 0%' in template or 'ədv-dən azadolma' in template:
                vat_free_items.append(item_data)
            # Check if it's VAT 18% template
            elif 'ədv 18%' in template or 'ədv daxil 18%' in template:
                vat_items.append(item_data)
    
    # Previous months items
    for item in doc.sales_invoice_previous_table:
        if item.customer == customer:
            # Get existing allocation for this item
            existing_alloc = existing_allocations.get(item.name, {'amount': 0, 'type': None})
            
            item_data = {
                'name': item.name,
                'item_tax_template': item.item_tax_template or '',
                'tax_article': item.tax_article or '',
                'amount': item.amount,
                'unallocated_amount': item.unallocated_amount,
                'allocated_amount': item.allocated_amount or 0,
                'current_allocation': existing_alloc['amount']  # Allocation from THIS payment row
            }
            
            template = (item.item_tax_template or '').lower()
            
            # Check if it's VAT free template
            if 'ədv 0%' in template or 'ədv-dən azadolma' in template:
                vat_free_items_previous.append(item_data)
            # Check if it's VAT 18% template
            elif 'ədv 18%' in template or 'ədv daxil 18%' in template:
                vat_items_previous.append(item_data)
    
    return {
        'payment_row': payment_row,
        'vat_free_available': vat_free_available,
        'vat_allocated_available': vat_allocated_available,
        'vat_free_items': vat_free_items,
        'vat_items': vat_items,
        'vat_free_items_previous': vat_free_items_previous,
        'vat_items_previous': vat_items_previous
    }

def round_decimal(value, precision=2):
    """
    Round decimal value to specified precision
    """
    if isinstance(value, (int, float)):
        value = Decimal(str(value))
    quantize_value = Decimal('0.1') ** precision
    return float(value.quantize(quantize_value, rounding=ROUND_HALF_UP))

@frappe.whitelist()
def save_allocation(vat_allocation_name, payment_entry_row_name, allocations):
    """
    Save allocation data using links table
    allocations is a JSON string with format:
    [
        {'name': 'si_row_name', 'allocated': amount, 'type': 'vat_free' or 'vat_allocated'},
        ...
    ]
    """
    try:
        # Parse allocations
        if isinstance(allocations, str):
            allocations = json.loads(allocations)
        
        # Validate input
        if not isinstance(allocations, list):
            frappe.throw(_("Invalid allocations data"))
        
        # Get document
        doc = frappe.get_doc('VAT allocation', vat_allocation_name)
        
        # Check if document is submitted or cancelled
        if doc.docstatus == 1:
            frappe.throw(_("Cannot modify submitted document"))
        if doc.docstatus == 2:
            frappe.throw(_("Cannot modify cancelled document"))
        
        # Find payment entry row
        payment_row = None
        for pe_row in doc.payment_entry_table:
            if pe_row.name == payment_entry_row_name:
                payment_row = pe_row
                break
        
        if not payment_row:
            frappe.throw(_("Payment entry row not found"))
        
        # Calculate what we're trying to allocate
        total_vat_free_new = Decimal('0')
        total_vat_allocated_new = Decimal('0')
        
        for allocation in allocations:
            allocated_amount = Decimal(str(allocation['allocated']))
            if allocated_amount < 0:
                frappe.throw(_("Allocated amount cannot be negative"))
                
            if allocation['type'] == 'vat_free':
                total_vat_free_new += allocated_amount
            else:
                total_vat_allocated_new += allocated_amount
        
        # Check available amounts
        vat_free_amount = Decimal(str(payment_row.vat_free_amount or 0))
        vat_allocated_amount = Decimal(str(payment_row.vat_allocated_amount or 0))
        
        # Validate totals with tolerance
        if total_vat_free_new > vat_free_amount + ROUNDING_TOLERANCE:
            frappe.throw(_(
                "Total VAT Free allocation ({0}) exceeds total amount ({1})"
            ).format(total_vat_free_new, vat_free_amount))
        
        if total_vat_allocated_new > vat_allocated_amount + ROUNDING_TOLERANCE:
            frappe.throw(_(
                "Total VAT Allocated ({0}) exceeds total amount ({1})"
            ).format(total_vat_allocated_new, vat_allocated_amount))
        
        # Validate each SI item - check both tables
        si_items_map = {item.name: item for item in doc.sales_invoice_table}
        si_items_map.update({item.name: item for item in doc.sales_invoice_previous_table})
        
        for allocation in allocations:
            si_row_name = allocation['name']
            allocated_amount = Decimal(str(allocation['allocated']))
            
            item = si_items_map.get(si_row_name)
            if not item:
                frappe.throw(_(f"Sales invoice item {si_row_name} not found"))
            
            # Calculate how much is already allocated to this SI from OTHER payment rows
            other_allocations = Decimal('0')
            for link in doc.allocation_links:
                if link.sales_invoice_row == si_row_name and link.payment_entry_row != payment_entry_row_name:
                    other_allocations += Decimal(str(link.allocated_amount or 0))
            
            # Check if total (new + other) exceeds SI amount
            total_for_si = allocated_amount + other_allocations
            si_amount = Decimal(str(item.amount))
            
            if total_for_si > si_amount + ROUNDING_TOLERANCE:
                frappe.throw(_(
                    "Cannot allocate {0} to {1}. Total allocation would be {2}, but amount is only {3}."
                ).format(allocated_amount, item.item_tax_template, total_for_si, si_amount))
        
        # Remove existing links for this payment row
        links_to_remove = []
        for link in doc.allocation_links:
            if link.payment_entry_row == payment_entry_row_name:
                links_to_remove.append(link)
        
        for link in links_to_remove:
            doc.allocation_links.remove(link)
        
        # Create new links
        for allocation in allocations:
            allocated_amount = Decimal(str(allocation['allocated']))
            
            # Skip if amount is 0
            if allocated_amount <= 0:
                continue
            
            link_row = doc.append('allocation_links', {})
            link_row.payment_entry_row = payment_entry_row_name
            link_row.sales_invoice_row = allocation['name']
            link_row.allocated_amount = float(allocated_amount)
            link_row.allocation_type = allocation['type']
        
        # Save document (this will trigger recalculate_totals_from_links in validate)
        doc.save()
        
        # Create audit log
        create_allocation_log(
            vat_allocation_name=vat_allocation_name,
            payment_entry_row_name=payment_entry_row_name,
            allocations=allocations,
            total_vat_free=float(total_vat_free_new),
            total_vat_allocated=float(total_vat_allocated_new)
        )
        
        frappe.db.commit()
        
        return {
            'success': True,
            'message': _('Allocation saved successfully'),
            'vat_free_spent': round_decimal(total_vat_free_new),
            'vat_allocated_spent': round_decimal(total_vat_allocated_new),
            'total_allocated': round_decimal(total_vat_free_new + total_vat_allocated_new)
        }
        
    except Exception as e:
        frappe.db.rollback()
        frappe.log_error(f"Allocation Save Error: {str(e)}", "VAT Allocation Error")
        return {
            'success': False,
            'message': str(e)
        }

def create_allocation_log(vat_allocation_name, payment_entry_row_name, allocations, 
                         total_vat_free, total_vat_allocated):
    """
    Create audit log for allocation
    """
    try:
        log_doc = frappe.get_doc({
            'doctype': 'Comment',
            'comment_type': 'Info',
            'reference_doctype': 'VAT allocation',
            'reference_name': vat_allocation_name,
            'content': f"""
                <b>Allocation Updated</b><br>
                Payment Row: {payment_entry_row_name}<br>
                VAT Free: {total_vat_free}<br>
                VAT 18%: {total_vat_allocated}<br>
                Total: {total_vat_free + total_vat_allocated}<br>
                Items: {len([a for a in allocations if a['allocated'] > 0])}<br>
                User: {frappe.session.user}<br>
                Time: {frappe.utils.now_datetime()}
            """
        })
        log_doc.insert(ignore_permissions=True)
    except Exception as e:
        frappe.log_error(f"Failed to create allocation log: {str(e)}", "Allocation Log Error")

@frappe.whitelist()
def calculate_auto_allocation(vat_free_available, vat_allocated_available, vat_free_items, vat_items):
    """
    Calculate smart auto allocation with improved rounding
    """
    if isinstance(vat_free_items, str):
        vat_free_items = json.loads(vat_free_items)
    if isinstance(vat_items, str):
        vat_items = json.loads(vat_items)
    
    vat_free_available = Decimal(str(vat_free_available))
    vat_allocated_available = Decimal(str(vat_allocated_available))
    
    allocations = []
    
    # Allocate VAT free amount proportionally to VAT free items
    if vat_free_available > 0 and len(vat_free_items) > 0:
        # Calculate available for each item (total unallocated minus current allocation from this PE)
        items_with_available = []
        for item in vat_free_items:
            available = Decimal(str(item['unallocated_amount'])) + Decimal(str(item.get('current_allocation', 0)))
            if available > 0:
                items_with_available.append({
                    'name': item['name'],
                    'available': available
                })
        
        if items_with_available:
            total_available = sum(item['available'] for item in items_with_available)
            remaining = vat_free_available
            
            for i, item in enumerate(items_with_available):
                if i == len(items_with_available) - 1:
                    # Last item gets the remainder
                    allocated = min(remaining, item['available'])
                else:
                    # Proportional allocation
                    proportion = item['available'] / total_available
                    allocated = min(vat_free_available * proportion, item['available'])
                    allocated = allocated.quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
                
                if allocated > 0:
                    allocations.append({
                        'name': item['name'],
                        'allocated': float(allocated),
                        'type': 'vat_free'
                    })
                    remaining -= allocated
    
    # Allocate VAT amount proportionally to VAT items
    if vat_allocated_available > 0 and len(vat_items) > 0:
        # Calculate available for each item
        items_with_available = []
        for item in vat_items:
            available = Decimal(str(item['unallocated_amount'])) + Decimal(str(item.get('current_allocation', 0)))
            if available > 0:
                items_with_available.append({
                    'name': item['name'],
                    'available': available
                })
        
        if items_with_available:
            total_available = sum(item['available'] for item in items_with_available)
            remaining = vat_allocated_available
            
            for i, item in enumerate(items_with_available):
                if i == len(items_with_available) - 1:
                    # Last item gets the remainder
                    allocated = min(remaining, item['available'])
                else:
                    # Proportional allocation
                    proportion = item['available'] / total_available
                    allocated = min(vat_allocated_available * proportion, item['available'])
                    allocated = allocated.quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
                
                if allocated > 0:
                    allocations.append({
                        'name': item['name'],
                        'allocated': float(allocated),
                        'type': 'vat_allocated'
                    })
                    remaining -= allocated
    
    return allocations

def recalculate_totals_from_links(self):
    """
    Recalculate all allocated/unallocated amounts from allocation links
    """
    # Reset all allocated amounts
    for pe_row in self.payment_entry_table:
        pe_row.vat_free_allocated = 0
        pe_row.vat_allocated_allocated = 0
        pe_row.allocated_amount = 0
    
    for si_row in self.sales_invoice_table:
        si_row.allocated_amount = 0
    
    for si_row in self.sales_invoice_previous_table:
        si_row.allocated_amount = 0
    
    # Calculate from links
    for link in self.allocation_links:
        allocated = Decimal(str(link.allocated_amount or 0))
        if allocated <= 0:
            continue
        
        # Update payment entry row
        for pe_row in self.payment_entry_table:
            if pe_row.name == link.payment_entry_row:
                current_allocated = Decimal(str(pe_row.allocated_amount or 0))
                pe_row.allocated_amount = float(current_allocated + allocated)
                
                if link.allocation_type == 'vat_free':
                    current_vat_free = Decimal(str(pe_row.vat_free_allocated or 0))
                    pe_row.vat_free_allocated = float(current_vat_free + allocated)
                else:
                    current_vat_allocated = Decimal(str(pe_row.vat_allocated_allocated or 0))
                    pe_row.vat_allocated_allocated = float(current_vat_allocated + allocated)
                break
        
        # Update sales invoice row - check both tables
        found = False
        for si_row in self.sales_invoice_table:
            if si_row.name == link.sales_invoice_row:
                current_allocated = Decimal(str(si_row.allocated_amount or 0))
                si_row.allocated_amount = float(current_allocated + allocated)
                found = True
                break
        
        if not found:
            for si_row in self.sales_invoice_previous_table:
                if si_row.name == link.sales_invoice_row:
                    current_allocated = Decimal(str(si_row.allocated_amount or 0))
                    si_row.allocated_amount = float(current_allocated + allocated)
                    break
    
    # Calculate unallocated amounts
    for pe_row in self.payment_entry_table:
        total = Decimal(str(pe_row.payment_entry_amount or 0))
        allocated = Decimal(str(pe_row.allocated_amount or 0))
        pe_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
    
    for si_row in self.sales_invoice_table:
        total = Decimal(str(si_row.amount or 0))
        allocated = Decimal(str(si_row.allocated_amount or 0))
        si_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
    
    for si_row in self.sales_invoice_previous_table:
        total = Decimal(str(si_row.amount or 0))
        allocated = Decimal(str(si_row.allocated_amount or 0))
        si_row.unallocated_amount = float(max(Decimal('0'), total - allocated))
    
    # Calculate totals by tax template
    self.calculate_tax_template_totals()

def calculate_tax_template_totals(self):
    """
    Calculate totals by tax template from sales invoice table
    """
    # Reset totals
    self.total_edv_daxil_18 = 0
    self.total_edv_azadolma = 0
    self.total_edv_0 = 0
    self.total_edv_18 = 0
    self.total_allocated_sum = 0
    
    # Calculate totals - only from current month table
    for si_row in self.sales_invoice_table:
        allocated = Decimal(str(si_row.allocated_amount or 0))
        if allocated <= 0:
            continue
        
        template = (si_row.item_tax_template or '').strip()
        
        # Match by template name
        if template == 'ƏDV daxil 18%':
            self.total_edv_daxil_18 = float(Decimal(str(self.total_edv_daxil_18 or 0)) + allocated)
        elif template == 'ƏDV-dən azadolma':
            self.total_edv_azadolma = float(Decimal(str(self.total_edv_azadolma or 0)) + allocated)
        elif template == 'ƏDV 0%':
            self.total_edv_0 = float(Decimal(str(self.total_edv_0 or 0)) + allocated)
        elif template == 'ƏDV 18%':
            self.total_edv_18 = float(Decimal(str(self.total_edv_18 or 0)) + allocated)
        
        # Add to total sum
        self.total_allocated_sum = float(Decimal(str(self.total_allocated_sum or 0)) + allocated)

@frappe.whitelist()
def get_payment_entries_previous_months(year, month, customers=None, min_amount=None, max_amount=None):
    """
    Get Payment Entries from previous VAT Allocation documents where unallocated_amount > 0
    Returns the most recent state of each Payment Entry
    """
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        frappe.throw(_("Invalid month"))
    
    # Parse customers filter
    customer_list = None
    if customers:
        if isinstance(customers, str):
            customers = json.loads(customers)
        if customers and len(customers) > 0:
            customer_list = [c.get('customer') for c in customers if c.get('customer')]
    
    # Find all previous VAT Allocation documents
    vat_allocations = frappe.db.sql("""
        SELECT name, year, month
        FROM `tabVAT allocation`
        WHERE docstatus = 1
        AND (
            year < %(year)s
            OR (year = %(year)s AND month < %(month)s)
        )
        ORDER BY year DESC, month DESC
    """, {
        'year': year,
        'month': month_number
    }, as_dict=1)
    
    if not vat_allocations:
        return []
    
    # Collect all payment entries with unallocated_amount > 0
    # Keep only the most recent entry for each payment_entry
    payment_entries_map = {}
    
    for vat_alloc in vat_allocations:
        # Get payment entries from this VAT Allocation
        entries = frappe.db.sql("""
            SELECT 
                payment_entry,
                customer,
                payment_entry_amount,
                vat_journal_entry,
                vat_amount,
                vat_allocated_amount,
                vat_free_amount,
                vat_allocated_allocated,
                vat_free_allocated,
                unallocated_amount
            FROM `tabVAT allocation payment entry`
            WHERE parent = %(parent)s
            AND unallocated_amount > 0
        """, {
            'parent': vat_alloc.name
        }, as_dict=1)
        
        for entry in entries:
            # Apply customer filter
            if customer_list and entry.customer not in customer_list:
                continue
            
            # Apply min/max amount filters to payment_entry_amount
            if min_amount and (entry.payment_entry_amount or 0) < float(min_amount):
                continue
            if max_amount and (entry.payment_entry_amount or 0) > float(max_amount):
                continue
            
            # Keep only the most recent (first encountered due to ORDER BY DESC)
            if entry.payment_entry not in payment_entries_map:
                # Calculate remaining amounts
                vat_allocated_remaining = Decimal(str(entry.vat_allocated_amount or 0)) - Decimal(str(entry.vat_allocated_allocated or 0))
                vat_free_remaining = Decimal(str(entry.vat_free_amount or 0)) - Decimal(str(entry.vat_free_allocated or 0))
                
                payment_entries_map[entry.payment_entry] = {
                    'payment_entry': entry.payment_entry,
                    'customer': entry.customer,
                    'payment_entry_amount': entry.payment_entry_amount,
                    'vat_journal_entry': entry.vat_journal_entry,
                    'vat_amount': entry.vat_amount,
                    'vat_allocated_amount': float(vat_allocated_remaining),
                    'vat_free_amount': float(vat_free_remaining),
                    'unallocated_amount': 0,  # Will be calculated
                    'allocated_amount': 0,
                    'vat_allocated_allocated': 0,
                    'vat_free_allocated': 0
                }
    
    # Convert to list
    result = list(payment_entries_map.values())
    
    # Sort by payment_entry
    result.sort(key=lambda x: x['payment_entry'])
    
    return result

@frappe.whitelist()
def get_payment_entries_with_previous(year, month, customers=None, min_amount=None, max_amount=None):
    """
    Get Payment Entries - both current month and previous months with unallocated amounts
    """
    # Get current month payment entries
    current_entries = get_payment_entries(year, month, customers, min_amount, max_amount)
    
    # Get previous months payment entries
    previous_entries = get_payment_entries_previous_months(year, month, customers, min_amount, max_amount)
    
    # Create a set of payment_entry names from current month
    current_pe_names = {entry['payment_entry'] for entry in current_entries}
    
    # Filter out previous entries that are already in current month
    filtered_previous = [entry for entry in previous_entries if entry['payment_entry'] not in current_pe_names]
    
    # Combine and return
    return current_entries + filtered_previous

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def get_payment_entry_query(doctype, txt, searchfield, start, page_len, filters):
    """
    Custom query for filtering Payment Entries - includes both current month and those with remaining balance
    """
    year = filters.get('year')
    month = filters.get('month')
    
    if not all([year, month]):
        return []
    
    # Convert month name to number
    months = {
        "January": 1, "February": 2, "March": 3, "April": 4,
        "May": 5, "June": 6, "July": 7, "August": 8,
        "September": 9, "October": 10, "November": 11, "December": 12
    }
    
    month_number = months.get(month)
    if not month_number:
        return []
    
    # Get Payment Entries from current month OR from previous VAT Allocations with balance
    return frappe.db.sql("""
        SELECT DISTINCT pe.name, pe.posting_date, pe.paid_amount
        FROM `tabPayment Entry` pe
        WHERE pe.docstatus = 1
        AND pe.payment_type = 'Receive'
        AND pe.party_type = 'Customer'
        AND pe.name LIKE %(txt)s
        AND (
            (YEAR(pe.posting_date) = %(year)s AND MONTH(pe.posting_date) = %(month)s)
            OR 
            EXISTS (
                SELECT 1 
                FROM `tabVAT allocation payment entry` vape
                INNER JOIN `tabVAT allocation` va ON va.name = vape.parent
                WHERE vape.payment_entry = pe.name
                AND va.docstatus = 1
                AND vape.unallocated_amount > 0
                AND (
                    va.year < %(year)s
                    OR (va.year = %(year)s AND va.month < %(month)s)
                )
            )
        )
        ORDER BY pe.posting_date DESC
        LIMIT %(start)s, %(page_len)s
    """, {
        'year': year,
        'month': month_number,
        'txt': f"%{txt}%",
        'start': start,
        'page_len': page_len
    })        