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

import frappe
from frappe import _

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"""
	return [
		{
			"label": _("Payment Entry"),
			"fieldname": "payment_entry",
			"fieldtype": "Link",
			"options": "Payment Entry",
			"width": 140
		},
		{
			"label": _("Date"),
			"fieldname": "posting_date",
			"fieldtype": "Date",
			"width": 100
		},
		{
			"label": _("Payment Type"),
			"fieldname": "payment_type",
			"fieldtype": "Data",
			"width": 120
		},
		{
			"label": _("Party Type"),
			"fieldname": "party_type",
			"fieldtype": "Data",
			"width": 100
		},
		{
			"label": _("Party"),
			"fieldname": "party",
			"fieldtype": "Dynamic Link",
			"options": "party_type",
			"width": 150
		},
		{
			"label": _("Party Name"),
			"fieldname": "party_name",
			"fieldtype": "Data",
			"width": 150
		},
		{
			"label": _("Paid Amount"),
			"fieldname": "paid_amount",
			"fieldtype": "Currency",
			"width": 120
		},
		{
			"label": _("Received Amount"),
			"fieldname": "received_amount",
			"fieldtype": "Currency",
			"width": 120
		},
		{
			"label": _("VAT Amount"),
			"fieldname": "vat_amount",
			"fieldtype": "Currency",
			"width": 120
		},
		{
			"label": _("Grand Total"),
			"fieldname": "grand_total",
			"fieldtype": "Currency",
			"width": 120
		},
		{
			"label": _("Mode of Payment"),
			"fieldname": "mode_of_payment",
			"fieldtype": "Link",
			"options": "Mode of Payment",
			"width": 130
		},
		{
			"label": _("Reference No"),
			"fieldname": "reference_no",
			"fieldtype": "Data",
			"width": 120
		},
		{
			"label": _("Reference Date"),
			"fieldname": "reference_date",
			"fieldtype": "Date",
			"width": 100
		}
	]

def get_data(filters):
	"""Get report data"""
	conditions = get_conditions(filters)
	
	data = frappe.db.sql(f"""
		SELECT 
			pe.name as payment_entry,
			pe.posting_date,
			pe.payment_type,
			pe.party_type,
			pe.party,
			pe.party_name,
			pe.paid_amount - COALESCE(SUM(CASE 
				WHEN stc.account_head = '521.3 - ƏDV' OR acc.account_number = '521.3' 
				THEN stc.tax_amount 
				ELSE 0 
			END), 0) as paid_amount,
			pe.received_amount,
			COALESCE(SUM(CASE 
				WHEN stc.account_head = '521.3 - ƏDV' OR acc.account_number = '521.3' 
				THEN stc.tax_amount 
				ELSE 0 
			END), 0) as vat_amount,
			pe.paid_amount as grand_total,
			pe.mode_of_payment,
			pe.reference_no,
			pe.reference_date
		FROM 
			`tabPayment Entry` pe
		LEFT JOIN 
			`tabPayment Entry Reference` per ON per.parent = pe.name AND per.reference_doctype = 'Sales Invoice'
		LEFT JOIN 
			`tabSales Taxes and Charges` stc ON stc.parent = per.reference_name
		LEFT JOIN
			`tabAccount` acc ON acc.name = stc.account_head
		WHERE 
			pe.docstatus = 1
			AND pe.payment_type = 'Receive'
			{conditions}
		GROUP BY 
			pe.name, pe.posting_date, pe.payment_type, pe.party_type, pe.party, pe.party_name, 
			pe.paid_amount, pe.received_amount, pe.mode_of_payment, pe.reference_no, pe.reference_date
		ORDER BY 
			pe.posting_date DESC, pe.name
	""", filters, as_dict=1)
	
	return data

def get_conditions(filters):
	"""Build WHERE conditions based on filters"""
	conditions = ""
	
	if filters.get("from_date"):
		conditions += " AND pe.posting_date >= %(from_date)s"
	
	if filters.get("to_date"):
		conditions += " AND pe.posting_date <= %(to_date)s"
	
	return conditions