# Copyright (c) 2024, Company and contributors
# For license information, please see license.txt

import frappe
from frappe import _
from datetime import datetime
from dateutil.relativedelta import relativedelta

def execute(filters=None):
	"""Main function for report execution"""
	columns = get_columns()
	data = get_data(filters)
	
	return columns, data

def get_columns():
	"""Get report columns definition with 16 new columns for tax templates"""
	columns = [
		{
			"label": _("Customer"),
			"fieldname": "customer",
			"fieldtype": "Link",
			"options": "Customer",
			"width": 150
		},
		{
			"label": _("Customer Name"),
			"fieldname": "customer_name",
			"fieldtype": "Data",
			"width": 200
		},
		{
			"label": _("Opening Balance"),
			"fieldname": "opening_balance",
			"fieldtype": "Currency",
			"width": 140
		},
		{
			"label": _("New Invoices (Increase)"),
			"fieldname": "new_invoices",
			"fieldtype": "Currency",
			"width": 140
		},
		{
			"label": _("Payments (Decrease)"),
			"fieldname": "payments",
			"fieldtype": "Currency",
			"width": 140
		},
		{
			"label": _("Closing Balance"),
			"fieldname": "closing_balance",
			"fieldtype": "Currency",
			"width": 140
		}
	]
	
	# Add 16 columns for 4 tax templates
	tax_templates = [
		{"name": "ƏDV 18%", "prefix": "edv_18"},
		{"name": "ƏDV daxil 18%", "prefix": "edv_daxil_18"},
		{"name": "ƏDV 0%", "prefix": "edv_0"},
		{"name": "ƏDV-dən azadolma", "prefix": "edv_azadolma"}
	]
	
	for template in tax_templates:
		columns.extend([
			{
				"label": _(f"{template['name']} Opening"),
				"fieldname": f"{template['prefix']}_opening",
				"fieldtype": "Currency",
				"width": 130
			},
			{
				"label": _(f"{template['name']} +"),
				"fieldname": f"{template['prefix']}_plus",
				"fieldtype": "Currency",
				"width": 130
			},
			{
				"label": _(f"{template['name']} -"),
				"fieldname": f"{template['prefix']}_minus",
				"fieldtype": "Currency",
				"width": 130
			},
			{
				"label": _(f"{template['name']} Closing"),
				"fieldname": f"{template['prefix']}_closing",
				"fieldtype": "Currency",
				"width": 130
			}
		])
	
	return columns

def get_data(filters):
	"""Get report data from VAT Allocation documents"""
	conditions = get_conditions(filters)
	
	# Get current period VAT Allocation
	from_date = datetime.strptime(filters.get('from_date'), '%Y-%m-%d')
	to_date = datetime.strptime(filters.get('to_date'), '%Y-%m-%d')
	
	current_year = from_date.year
	current_month = from_date.strftime('%B')  # Full month name
	
	# Get previous month for opening balance
	previous_date = from_date - relativedelta(months=1)
	previous_year = previous_date.year
	previous_month = previous_date.strftime('%B')
	
	# Get opening balance (before from_date) - original ledger-based calculation
	opening_balance_data = frappe.db.sql(f"""
		SELECT 
			ple.party as customer,
			SUM(ple.amount) as opening_balance
		FROM 
			`tabPayment Ledger Entry` ple
		LEFT JOIN 
			`tabAccount` acc ON acc.name = ple.account
		WHERE 
			ple.delinked = 0
			AND ple.posting_date < %(from_date)s
			AND acc.account_type = 'Receivable'
			AND ple.company = %(company)s
			{conditions}
		GROUP BY 
			ple.party
		HAVING 
			ABS(opening_balance) >= 0.01
	""", filters, as_dict=1)
	
	# Get movements during the period - original ledger-based calculation
	period_data = frappe.db.sql(f"""
		SELECT 
			ple.party as customer,
			SUM(CASE WHEN ple.amount > 0 THEN ple.amount ELSE 0 END) as new_invoices,
			SUM(CASE WHEN ple.amount < 0 THEN ABS(ple.amount) ELSE 0 END) as payments
		FROM 
			`tabPayment Ledger Entry` ple
		LEFT JOIN 
			`tabAccount` acc ON acc.name = ple.account
		WHERE 
			ple.delinked = 0
			AND ple.posting_date >= %(from_date)s
			AND ple.posting_date <= %(to_date)s
			AND acc.account_type = 'Receivable'
			AND ple.company = %(company)s
			{conditions}
		GROUP BY 
			ple.party
	""", filters, as_dict=1)
	
	# Get closing balance (up to to_date) - original ledger-based calculation
	closing_balance_data = frappe.db.sql(f"""
		SELECT 
			ple.party as customer,
			SUM(ple.amount) as closing_balance
		FROM 
			`tabPayment Ledger Entry` ple
		LEFT JOIN 
			`tabAccount` acc ON acc.name = ple.account
		WHERE 
			ple.delinked = 0
			AND ple.posting_date <= %(to_date)s
			AND acc.account_type = 'Receivable'
			AND ple.company = %(company)s
			{conditions}
		GROUP BY 
			ple.party
		HAVING 
			ABS(closing_balance) >= 0.01
	""", filters, as_dict=1)
	
	# Get VAT Allocation data for tax templates
	vat_current = get_vat_allocation_data(current_year, current_month, filters.get('company'))
	vat_previous = get_vat_allocation_data(previous_year, previous_month, filters.get('company'))
	
	# Get Sales Invoice data directly for current month (for "+" column)
	si_current = get_sales_invoice_data(current_year, current_month, filters.get('company'))
	
	# Combine all data
	result = combine_data(opening_balance_data, period_data, closing_balance_data, 
	                      vat_current, vat_previous, si_current)
	
	# Get customer names and other details
	add_customer_details(result)
	
	return result

def get_conditions(filters):
	"""Build WHERE conditions based on filters"""
	conditions = ""
	
	if filters.get("customer_group"):
		conditions += " AND ple.party IN (SELECT name FROM `tabCustomer` WHERE customer_group = %(customer_group)s)"
	
	return conditions

def get_vat_allocation_data(year, month, company):
	"""
	Get VAT allocation data for specified year and month
	Returns dict with customer -> tax_template -> {unallocated, allocated}
	"""
	# Find VAT Allocation document
	vat_name = f"VAT-{year}-{month}"
	
	if not frappe.db.exists("VAT allocation", vat_name):
		return {}
	
	# Get sales invoice table data from VAT allocation
	sales_data = frappe.db.sql("""
		SELECT 
			customer,
			item_tax_template,
			SUM(unallocated_amount) as total_unallocated,
			SUM(allocated_amount) as total_allocated
		FROM 
			`tabVAT allocation sales invoice`
		WHERE 
			parent = %(vat_name)s
		GROUP BY 
			customer, item_tax_template
	""", {"vat_name": vat_name}, as_dict=1)
	
	# Organize data by customer and tax template
	result = {}
	for row in sales_data:
		customer = row['customer']
		template = row['item_tax_template']
		
		if customer not in result:
			result[customer] = {}
		
		result[customer][template] = {
			'unallocated': row['total_unallocated'] or 0.0,
			'allocated': row['total_allocated'] or 0.0
		}
	
	return result

def get_sales_invoice_data(year, month, company):
	"""
	Get Sales Invoice data directly for the specified month
	Returns dict with customer -> tax_template -> amount
	"""
	# 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 Sales Invoice items directly
	invoice_data = frappe.db.sql("""
		SELECT 
			si.customer,
			sii.item_tax_template,
			SUM(sii.amount) as total_amount
		FROM 
			`tabSales Invoice` si
		INNER JOIN 
			`tabSales Invoice Item` sii ON si.name = sii.parent
		WHERE 
			si.docstatus = 1
			AND YEAR(si.posting_date) = %(year)s
			AND MONTH(si.posting_date) = %(month)s
			AND si.company = %(company)s
		GROUP BY 
			si.customer, sii.item_tax_template
	""", {"year": year, "month": month_number, "company": company}, as_dict=1)
	
	# Organize data by customer and tax template
	result = {}
	for row in invoice_data:
		customer = row['customer']
		template = row['item_tax_template']
		
		if customer not in result:
			result[customer] = {}
		
		result[customer][template] = row['total_amount'] or 0.0
	
	return result

def combine_data(opening_data, period_data, closing_data, vat_current, vat_previous, si_current):
	"""Combine all datasets into final result"""
	result_dict = {}
	
	# Tax template mapping
	template_map = {
		"ƏDV 18%": "edv_18",
		"ƏDV daxil 18%": "edv_daxil_18",
		"ƏDV 0%": "edv_0",
		"ƏDV-dən azadolma": "edv_azadolma"
	}
	
	# Add opening balances (original logic)
	for row in opening_data:
		customer = row['customer']
		result_dict[customer] = {
			'customer': customer,
			'opening_balance': row['opening_balance'] or 0.0,
			'payments': 0.0,
			'new_invoices': 0.0,
			'closing_balance': 0.0
		}
		# Initialize tax template fields
		for prefix in template_map.values():
			result_dict[customer][f'{prefix}_opening'] = 0.0
			result_dict[customer][f'{prefix}_plus'] = 0.0
			result_dict[customer][f'{prefix}_minus'] = 0.0
			result_dict[customer][f'{prefix}_closing'] = 0.0
	
	# Add period movements (original logic)
	for row in period_data:
		customer = row['customer']
		if customer not in result_dict:
			result_dict[customer] = {
				'customer': customer,
				'opening_balance': 0.0,
				'payments': 0.0,
				'new_invoices': 0.0,
				'closing_balance': 0.0
			}
			# Initialize tax template fields
			for prefix in template_map.values():
				result_dict[customer][f'{prefix}_opening'] = 0.0
				result_dict[customer][f'{prefix}_plus'] = 0.0
				result_dict[customer][f'{prefix}_minus'] = 0.0
				result_dict[customer][f'{prefix}_closing'] = 0.0
		
		result_dict[customer]['payments'] = row['payments'] or 0.0
		result_dict[customer]['new_invoices'] = row['new_invoices'] or 0.0
	
	# Add closing balances (original logic)
	for row in closing_data:
		customer = row['customer']
		if customer not in result_dict:
			result_dict[customer] = {
				'customer': customer,
				'opening_balance': 0.0,
				'payments': 0.0,
				'new_invoices': 0.0,
				'closing_balance': 0.0
			}
			# Initialize tax template fields
			for prefix in template_map.values():
				result_dict[customer][f'{prefix}_opening'] = 0.0
				result_dict[customer][f'{prefix}_plus'] = 0.0
				result_dict[customer][f'{prefix}_minus'] = 0.0
				result_dict[customer][f'{prefix}_closing'] = 0.0
		
		result_dict[customer]['closing_balance'] = row['closing_balance'] or 0.0
	
	# Get all customers from all sources
	all_customers = set(result_dict.keys())
	all_customers.update(vat_current.keys())
	all_customers.update(vat_previous.keys())
	all_customers.update(si_current.keys())
	
	# Add VAT allocation data for tax templates
	for customer in all_customers:
		# Ensure customer exists in result_dict
		if customer not in result_dict:
			result_dict[customer] = {
				'customer': customer,
				'opening_balance': 0.0,
				'payments': 0.0,
				'new_invoices': 0.0,
				'closing_balance': 0.0
			}
			# Initialize tax template fields
			for prefix in template_map.values():
				result_dict[customer][f'{prefix}_opening'] = 0.0
				result_dict[customer][f'{prefix}_plus'] = 0.0
				result_dict[customer][f'{prefix}_minus'] = 0.0
				result_dict[customer][f'{prefix}_closing'] = 0.0
		
		# Get data for this customer
		customer_current = vat_current.get(customer, {})
		customer_previous = vat_previous.get(customer, {})
		customer_si = si_current.get(customer, {})
		
		for template_name, prefix in template_map.items():
			# Previous month data (for opening balance)
			prev_data = customer_previous.get(template_name, {'unallocated': 0.0, 'allocated': 0.0})
			opening = prev_data['unallocated']  # Closing of previous month = unallocated amount
			
			# Current month data
			curr_data = customer_current.get(template_name, {'unallocated': 0.0, 'allocated': 0.0})
			minus = curr_data['allocated']   # Payments (allocated amount)
			
			# Sales Invoice data for "+" (new invoices)
			plus = customer_si.get(template_name, 0.0)
			
			# Calculate closing
			closing = opening + plus - minus
			
			result_dict[customer][f'{prefix}_opening'] = opening
			result_dict[customer][f'{prefix}_plus'] = plus
			result_dict[customer][f'{prefix}_minus'] = minus
			result_dict[customer][f'{prefix}_closing'] = closing
	
	# Convert to list and filter out zero balances
	result = []
	for customer, data in result_dict.items():
		# Check if there's any activity
		has_activity = (
			abs(data['opening_balance']) >= 0.01 or 
			abs(data['closing_balance']) >= 0.01 or
			abs(data['new_invoices']) >= 0.01 or
			abs(data['payments']) >= 0.01
		)
		
		# Also check tax template activity
		for prefix in template_map.values():
			if (abs(data[f'{prefix}_opening']) >= 0.01 or 
			    abs(data[f'{prefix}_closing']) >= 0.01 or
			    abs(data[f'{prefix}_plus']) >= 0.01 or
			    abs(data[f'{prefix}_minus']) >= 0.01):
				has_activity = True
				break
		
		if has_activity:
			result.append(data)
	
	return sorted(result, key=lambda x: x['customer'])

def add_customer_details(data):
	"""Add customer names and other details"""
	if not data:
		return
	
	customer_names = {}
	customers = [row['customer'] for row in data]
	
	if customers:
		customer_details = frappe.db.sql("""
			SELECT name, customer_name
			FROM `tabCustomer`
			WHERE name IN ({})
		""".format(', '.join(['%s'] * len(customers))), customers, as_dict=1)
		
		for customer in customer_details:
			customer_names[customer['name']] = customer['customer_name']
	
	# Update data with customer names
	for row in data:
		row['customer_name'] = customer_names.get(row['customer'], row['customer'])