# 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": _("Sales Invoice"),
			"fieldname": "sales_invoice",
			"fieldtype": "Link",
			"options": "Sales Invoice",
			"width": 140
		},
		{
			"label": _("Date"),
			"fieldname": "posting_date",
			"fieldtype": "Date",
			"width": 100
		},
		{
			"label": _("Customer"),
			"fieldname": "customer",
			"fieldtype": "Link",
			"options": "Customer",
			"width": 150
		},
		{
			"label": _("Customer Name"),
			"fieldname": "customer_name",
			"fieldtype": "Data",
			"width": 150
		},
		{
			"label": _("Net Amount (Without VAT)"),
			"fieldname": "net_amount",
			"fieldtype": "Currency",
			"width": 150
		},
		{
			"label": _("VAT Amount"),
			"fieldname": "vat_amount",
			"fieldtype": "Currency",
			"width": 120
		},
		{
			"label": _("Grand Total"),
			"fieldname": "grand_total",
			"fieldtype": "Currency",
			"width": 120
		}
	]

def get_data(filters):
	"""Get report data"""
	conditions = get_conditions(filters)
	
	data = frappe.db.sql(f"""
		SELECT 
			si.name as sales_invoice,
			si.posting_date,
			si.customer,
			si.customer_name,
			si.total as net_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,
			si.grand_total
		FROM 
			`tabSales Invoice` si
		LEFT JOIN 
			`tabSales Taxes and Charges` stc ON stc.parent = si.name
		LEFT JOIN
			`tabAccount` acc ON acc.name = stc.account_head
		WHERE 
			si.docstatus = 1
			{conditions}
		GROUP BY 
			si.name, si.posting_date, si.customer, si.customer_name, si.total, si.grand_total
		ORDER BY 
			si.posting_date DESC, si.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 si.posting_date >= %(from_date)s"
	
	if filters.get("to_date"):
		conditions += " AND si.posting_date <= %(to_date)s"
	
	return conditions