# Copyright (c) 2025, Jey Soft and contributors
# For license information, please see license.txt

import frappe
from frappe import _
from frappe.utils import flt


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


def get_columns():
	"""Define 13 columns: 1 ID + 12 data columns"""
	columns = [
		{
			"label": _("VAT Allocation"),
			"fieldname": "vat_allocation",
			"fieldtype": "Link",
			"options": "VAT allocation",
			"width": 150
		}
	]

	# Column Group 1: Ayın əvvəlinə (начало месяца)
	columns.extend([
		{"label": _("Ayın əvvəlinə 18%"), "fieldname": "prev_month_18", "fieldtype": "Currency", "width": 130},
		{"label": _("Ayın əvvəlinə 0%"), "fieldname": "prev_month_0", "fieldtype": "Currency", "width": 130},
		{"label": _("Ayın əvvəlinə Azad"), "fieldname": "prev_month_azad", "fieldtype": "Currency", "width": 130}
	])

	# Column Group 2: Yaranan borc (возникший долг)
	columns.extend([
		{"label": _("Yaranan borc 18%"), "fieldname": "unallocated_18", "fieldtype": "Currency", "width": 130},
		{"label": _("Yaranan borc 0%"), "fieldname": "unallocated_0", "fieldtype": "Currency", "width": 130},
		{"label": _("Yaranan borc Azad"), "fieldname": "unallocated_azad", "fieldtype": "Currency", "width": 130}
	])

	# Column Group 3: Silənən borc (погашенный долг)
	columns.extend([
		{"label": _("Silənən borc 18%"), "fieldname": "allocated_18", "fieldtype": "Currency", "width": 130},
		{"label": _("Silənən borc 0%"), "fieldname": "allocated_0", "fieldtype": "Currency", "width": 130},
		{"label": _("Silənən borc Azad"), "fieldname": "allocated_azad", "fieldtype": "Currency", "width": 130}
	])

	# Column Group 4: Ayın sonuna (конец месяца)
	columns.extend([
		{"label": _("Ayın sonuna 18%"), "fieldname": "balance_18", "fieldtype": "Currency", "width": 130},
		{"label": _("Ayın sonuna 0%"), "fieldname": "balance_0", "fieldtype": "Currency", "width": 130},
		{"label": _("Ayın sonuna Azad"), "fieldname": "balance_azad", "fieldtype": "Currency", "width": 130}
	])

	return columns


def get_empty_row(vat_name=""):
	"""Return an empty row with zero values (needed for Formula Editor)"""
	return {
		'vat_allocation': vat_name or "No Data",
		'prev_month_18': 0.0,
		'prev_month_0': 0.0,
		'prev_month_azad': 0.0,
		'unallocated_18': 0.0,
		'unallocated_0': 0.0,
		'unallocated_azad': 0.0,
		'allocated_18': 0.0,
		'allocated_0': 0.0,
		'allocated_azad': 0.0,
		'balance_18': 0.0,
		'balance_0': 0.0,
		'balance_azad': 0.0
	}


def get_data(filters):
	"""Get VAT allocation documents and calculate values"""

	# Return empty row if filters are not provided (needed for Formula Editor)
	if not filters:
		return [get_empty_row()]

	# Validate filters - return empty row if incomplete
	if not filters.get('company'):
		return [get_empty_row()]

	# Support both direct year/month filters AND from_date/to_date (for Formula Editor)
	year = filters.get('year')
	month = filters.get('month')

	# If year/month not provided, try to extract from from_date or to_date
	if not year or not month:
		date_to_use = filters.get('to_date') or filters.get('from_date')
		if date_to_use:
			from frappe.utils import getdate
			date_obj = getdate(date_to_use)
			year = date_obj.year
			# Convert month number to month name
			month_names = ['January', 'February', 'March', 'April', 'May', 'June',
			               'July', 'August', 'September', 'October', 'November', 'December']
			month = month_names[date_obj.month - 1]
		else:
			return [get_empty_row()]

	# Find VAT allocation document (max one result due to autoname format)
	vat_name = f"VAT-{year}-{month}"

	if not frappe.db.exists("VAT allocation", vat_name):
		return [get_empty_row(vat_name)]

	# Check if submitted
	docstatus = frappe.db.get_value("VAT allocation", vat_name, "docstatus")
	if docstatus != 1:
		return [get_empty_row(vat_name)]

	row = calculate_row_data(vat_name)
	return [row] if row else [get_empty_row(vat_name)]


def calculate_row_data(vat_allocation_name):
	"""Calculate all values for one VAT allocation document"""

	# Get data from sales_invoice_previous_table
	prev_month_data = frappe.db.sql("""
		SELECT
			item_tax_template,
			SUM(amount) as total_amount,
			SUM(allocated_amount) as total_allocated
		FROM `tabVAT allocation sales invoice`
		WHERE parent = %(parent)s
			AND parentfield = 'sales_invoice_previous_table'
		GROUP BY item_tax_template
	""", {"parent": vat_allocation_name}, as_dict=1)

	# Get data from sales_invoice_table
	current_month_data = frappe.db.sql("""
		SELECT
			item_tax_template,
			SUM(unallocated_amount) as total_unallocated
		FROM `tabVAT allocation sales invoice`
		WHERE parent = %(parent)s
			AND parentfield = 'sales_invoice_table'
		GROUP BY item_tax_template
	""", {"parent": vat_allocation_name}, as_dict=1)

	# Debug: log the raw data
	frappe.logger().debug(f"VAT Allocation: {vat_allocation_name}")
	frappe.logger().debug(f"Previous month data: {prev_month_data}")
	frappe.logger().debug(f"Current month data: {current_month_data}")

	# Initialize row
	row = {
		'vat_allocation': vat_allocation_name,
		'prev_month_18': 0.0,
		'prev_month_0': 0.0,
		'prev_month_azad': 0.0,
		'unallocated_18': 0.0,
		'unallocated_0': 0.0,
		'unallocated_azad': 0.0,
		'allocated_18': 0.0,
		'allocated_0': 0.0,
		'allocated_azad': 0.0
	}

	# Process previous month data
	for item in prev_month_data:
		template = (item.item_tax_template or '').strip().lower()
		amount = flt(item.total_amount)
		allocated = flt(item.total_allocated)

		# Match templates (case-insensitive)
		if 'ədv 18%' in template or 'ədv daxil 18%' in template:
			row['prev_month_18'] += amount
			row['allocated_18'] += allocated
		elif 'ədv 0%' in template:
			row['prev_month_0'] += amount
			row['allocated_0'] += allocated
		elif 'azadolma' in template:
			row['prev_month_azad'] += amount
			row['allocated_azad'] += allocated

	# Process current month data
	for item in current_month_data:
		template = (item.item_tax_template or '').strip().lower()
		unallocated = flt(item.total_unallocated)

		# Match templates (case-insensitive)
		if 'ədv 18%' in template or 'ədv daxil 18%' in template:
			row['unallocated_18'] += unallocated
		elif 'ədv 0%' in template:
			row['unallocated_0'] += unallocated
		elif 'azadolma' in template:
			row['unallocated_azad'] += unallocated

	# Calculate balances
	row['balance_18'] = (row['prev_month_18'] + row['unallocated_18']) - row['allocated_18']
	row['balance_0'] = (row['prev_month_0'] + row['unallocated_0']) - row['allocated_0']
	row['balance_azad'] = (row['prev_month_azad'] + row['unallocated_azad']) - row['allocated_azad']

	return row
