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

import frappe
from frappe import _
from frappe.utils import add_days, flt, formatdate


def execute(filters=None):
	filters.day_before_from_date = add_days(filters.from_date, -1)
	columns, data = get_columns(filters), get_data(filters)
	return columns, data


def get_data(filters):
	"""Получаем данные по активам с tax exempt статьями года создания компании"""
	
	# Список всех tax exempt статей
	tax_exempt_articles = get_tax_exempt_articles_list()
	
	# Получаем активы с этими статьями (только год создания компании)
	assets_data = get_establishment_year_tax_exempt_assets(filters, tax_exempt_articles)
	
	if not assets_data:
		return []
	
	# Получаем расчеты балансов для этих активов из стандартного отчета
	asset_balances = get_asset_balances_from_standard_report(filters, [asset['name'] for asset in assets_data])
	
	# Формируем итоговые данные
	data = []
	
	for asset in assets_data:
		row = frappe._dict()
		
		# Основные данные актива
		row.asset = asset['name']
		row.purchase_date = asset['purchase_date'] 
		row.tax_exempt_tax_article = asset['tax_exempt_tax_article']
		row.insured_value = flt(asset.get('insured_value', 0))
		
		# Находим баланс для этого актива
		asset_balance = next((bal for bal in asset_balances if bal['asset'] == asset['name']), {})

		# Берем значения из стандартного отчета
		value_as_on_from_date = flt(asset_balance.get('value_as_on_from_date', 0))
		net_asset_value_as_on_to_date = flt(asset_balance.get('net_asset_value_as_on_to_date', 0))
		
		# Инициализируем все колонки нулями
		for article in tax_exempt_articles:
			article_key = get_field_key(article)
			row[f"{article_key}_value_from"] = 0
			row[f"{article_key}_value_to"] = 0  
			row[f"{article_key}_insured"] = 0
		
		# Заполняем колонки для соответствующей статьи
		if asset['tax_exempt_tax_article']:
			article_key = get_field_key(asset['tax_exempt_tax_article'])
			row[f"{article_key}_value_from"] = value_as_on_from_date
			row[f"{article_key}_value_to"] = net_asset_value_as_on_to_date
			row[f"{article_key}_insured"] = row.insured_value
		
		data.append(row)
	
	return data


def get_tax_exempt_articles_list():
	"""Возвращает список всех tax exempt статей"""
	return [
		"Vergi Məcəlləsinin 199.1-ci maddəsinə əsasən, azaldılmalı əmlak vergisinin məbləği",
		"Vergi Məcəlləsinin 199.4.1-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.4.2-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.4.3-cü maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.4.4-cü maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.4.1-1-ci madacdəsinə əsasən",
		"Vergi Məcəlləsinin 199.5-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.7-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.8-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.9-cu maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.11-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.14-cü maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.15-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.16-cı maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.17-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.18-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.19-cu maddəsinə əsasən",
		"Vergi Məcəlləsinin 199.20-ci maddəsinə əsasən",
		"Vergi Məcəlləsinin 227.1-ci maddəsinə əsasən",
		"Qanunla təsdiq olunmuş hasilatın pay bölgüsü haqqında, əsas boru kəməri haqqında və digər bu qəbildən olan sazişlərdə və ya qanunlarda, o cümlədən neft və qaz haqqında, ixrac məqsədli neft-qaz fəaliyyəti üzrə",
		"Xüsusi iqtisadi zonalar haqqında qanunlarda nəzərdə tutulan əmlaklar üzrə",
		"Azərbaycan Respublikasının tərəfdar çıxdığı beynəlxalq müqavilələr üzrə"
	]


def get_field_key(tax_article):
	"""Создает ключ поля на основе названия tax article"""
	if not tax_article:
		return "unknown"
	
	# Извлекаем номер статьи для создания короткого ключа
	if "199.1-ci" in tax_article and "azaldılmalı" in tax_article:
		return "article_199_1_reduced"
	elif "199.4.1-ci" in tax_article:
		return "article_199_4_1"
	elif "199.4.2-ci" in tax_article:
		return "article_199_4_2"
	elif "199.4.3-cü" in tax_article:
		return "article_199_4_3"
	elif "199.4.4-cü" in tax_article:
		return "article_199_4_4"
	elif "199.4.1-1-ci" in tax_article:
		return "article_199_4_1_1"
	elif "199.5-ci" in tax_article:
		return "article_199_5"
	elif "199.7-ci" in tax_article:
		return "article_199_7"
	elif "199.8-ci" in tax_article:
		return "article_199_8"
	elif "199.9-cu" in tax_article:
		return "article_199_9"
	elif "199.11-ci" in tax_article:
		return "article_199_11"
	elif "199.14-cü" in tax_article:
		return "article_199_14"
	elif "199.15-ci" in tax_article:
		return "article_199_15"
	elif "199.16-cı" in tax_article:
		return "article_199_16"
	elif "199.17-ci" in tax_article:
		return "article_199_17"
	elif "199.18-ci" in tax_article:
		return "article_199_18"
	elif "199.19-cu" in tax_article:
		return "article_199_19"
	elif "199.20-ci" in tax_article:
		return "article_199_20"
	elif "227.1-ci" in tax_article:
		return "article_227_1"
	elif "pay bölgüsü" in tax_article:
		return "article_profit_sharing"
	elif "iqtisadi zonalar" in tax_article:
		return "article_economic_zones"
	elif "beynəlxalq müqavilələr" in tax_article:
		return "article_international"
	else:
		return "article_other"


def check_erpnext_version():
	"""Проверяет версию ERPNext и определяет, какое поле использовать для стоимости актива"""
	# Проверяем наличие колонки net_purchase_amount
	columns = frappe.db.get_table_columns("Asset")
	has_net_purchase_amount = "net_purchase_amount" in columns
	has_capitalization = "Asset Capitalization" in frappe.get_all("DocType", pluck="name")

	return {
		"purchase_amount_field": "net_purchase_amount" if has_net_purchase_amount else "gross_purchase_amount",
		"has_capitalization": has_capitalization
	}


def get_establishment_year_tax_exempt_assets(filters, tax_exempt_articles):
	"""Получает активы с tax exempt статьями ТОЛЬКО года создания компании"""

	version_info = check_erpnext_version()

	conditions = []
	values = []

	# Фильтр по компании
	if filters.get("company"):
		conditions.append("a.company = %s")
		values.append(filters.get("company"))
	
	# Фильтр по датам
	if filters.get("from_date"):
		conditions.append("a.purchase_date <= %s")
		values.append(filters.get("to_date"))
	
	# Фильтр по tax exempt статьям
	article_placeholders = ", ".join(["%s"] * len(tax_exempt_articles))
	conditions.append(f"a.tax_exempt_tax_article IN ({article_placeholders})")
	values.extend(tax_exempt_articles)
	
	# Только подтвержденные активы
	conditions.append("a.docstatus = 1")
	
	# ТОЛЬКО активы года создания компании
	conditions.append("YEAR(a.purchase_date) = YEAR(c.date_of_establishment)")
	
	where_clause = ""
	if conditions:
		where_clause = "WHERE " + " AND ".join(conditions)

	# Исключаем капитализированные активы только если есть поддержка
	if version_info["has_capitalization"]:
		exclude_capitalized = """
			AND NOT EXISTS(
				SELECT 1 FROM `tabAsset Capitalization Asset Item` acai
				JOIN `tabAsset Capitalization` ac ON acai.parent = ac.name
				WHERE acai.asset = a.name
				AND ac.posting_date < %s
				AND ac.docstatus = 1
			)
		"""
		where_clause += exclude_capitalized
		values.append(filters.get("from_date"))
	
	query = f"""
		SELECT 
			a.name,
			a.purchase_date,
			a.tax_exempt_tax_article,
			a.insured_value
		FROM 
			`tabAsset` a
		JOIN 
			`tabCompany` c ON a.company = c.name
		{where_clause}
		ORDER BY 
			a.purchase_date DESC, a.name
	"""
	
	return frappe.db.sql(query, values, as_dict=True)


def get_asset_balances_from_standard_report(filters, asset_names):
	"""Получает балансы активов используя логику стандартного отчета Asset Depreciations and Balances"""

	if not asset_names:
		return []

	version_info = check_erpnext_version()

	# Получаем детали активов
	asset_details = get_asset_details_for_tax_exempt(filters, asset_names, version_info)

	# Получаем депрециацию активов
	asset_depreciation = get_asset_depreciation_for_tax_exempt(filters, asset_names)

	# Объединяем данные
	result = []

	for asset_detail in asset_details:
		asset_name = asset_detail.get('name')

		# Находим депреciацию для актива
		depreciation = next(
			(dep for dep in asset_depreciation if dep['asset'] == asset_name),
			{}
		)

		# Рассчитываем значения по логике стандартного отчета
		value_as_on_from_date = flt(asset_detail.get('cost_as_on_from_date', 0))

		value_as_on_to_date = (
			value_as_on_from_date +
			flt(asset_detail.get('cost_of_new_purchase', 0)) -
			flt(asset_detail.get('cost_of_sold_asset', 0)) -
			flt(asset_detail.get('cost_of_scrapped_asset', 0))
		)

		accumulated_depreciation_as_on_from_date = flt(depreciation.get('accumulated_depreciation_as_on_from_date', 0))

		accumulated_depreciation_as_on_to_date = (
			accumulated_depreciation_as_on_from_date +
			flt(depreciation.get('depreciation_amount_during_the_period', 0)) -
			flt(depreciation.get('depreciation_eliminated_during_the_period', 0))
		)

		net_asset_value_as_on_to_date = value_as_on_to_date - accumulated_depreciation_as_on_to_date

		result.append({
			'asset': asset_name,
			'value_as_on_from_date': value_as_on_from_date,
			'net_asset_value_as_on_to_date': net_asset_value_as_on_to_date
		})

	return result


def get_asset_details_for_tax_exempt(filters, asset_names, version_info):
	"""Получает детали активов (совместимо со старой и новой версией)"""

	asset_placeholders = ", ".join(["%s"] * len(asset_names))
	purchase_amount_field = version_info["purchase_amount_field"]

	# Базовый запрос без капитализации
	capitalization_check = ""
	if version_info["has_capitalization"]:
		capitalization_check = """
			and not exists(
				select 1 from `tabAsset Capitalization Asset Item` acai
				join `tabAsset Capitalization` ac on acai.parent=ac.name
				where acai.asset = a.name
				and ac.posting_date < %s
				and ac.docstatus=1
			)
		"""

	query = f"""
		SELECT a.name,
			   ifnull(sum(case when a.purchase_date < %s then
							   case when ifnull(a.disposal_date, 0) = 0 or a.disposal_date >= %s then
									a.{purchase_amount_field}
							   else
									0
							   end
						   else
								0
						   end), 0) as cost_as_on_from_date,
			   ifnull(sum(case when a.purchase_date >= %s then
			   						a.{purchase_amount_field}
			   				   else
			   				   		0
			   				   end), 0) as cost_of_new_purchase,
			   ifnull(sum(case when ifnull(a.disposal_date, 0) != 0
			   						and a.disposal_date >= %s
			   						and a.disposal_date <= %s then
							   case when a.status = "Sold" then
							   		a.{purchase_amount_field}
							   else
							   		0
							   end
						   else
								0
						   end), 0) as cost_of_sold_asset,
			   ifnull(sum(case when ifnull(a.disposal_date, 0) != 0
			   						and a.disposal_date >= %s
			   						and a.disposal_date <= %s then
							   case when a.status = "Scrapped" then
							   		a.{purchase_amount_field}
							   else
							   		0
							   end
						   else
								0
						   end), 0) as cost_of_scrapped_asset
		from `tabAsset` a
		where a.docstatus=1 and a.company=%s and a.purchase_date <= %s
		and a.name IN ({asset_placeholders})
		{capitalization_check}
		group by a.name
	"""

	values = [
		filters.get("from_date"),  # cost_as_on_from_date condition 1
		filters.get("from_date"),  # cost_as_on_from_date condition 2
		filters.get("from_date"),  # cost_of_new_purchase
		filters.get("from_date"),  # cost_of_sold_asset condition 1
		filters.get("to_date"),    # cost_of_sold_asset condition 2
		filters.get("from_date"),  # cost_of_scrapped_asset condition 1
		filters.get("to_date"),    # cost_of_scrapped_asset condition 2
		filters.get("company"),    # company filter
		filters.get("to_date"),    # purchase_date filter
		*asset_names,              # asset names for IN clause
	]

	# Добавляем значение для капитализации если нужно
	if version_info["has_capitalization"]:
		values.append(filters.get("from_date"))

	return frappe.db.sql(query, values, as_dict=True)


def get_asset_depreciation_for_tax_exempt(filters, asset_names):
	"""Получает депреciацию активов (совместимо со старой и новой версией)"""

	asset_placeholders = ", ".join(["%s"] * len(asset_names))

	query = f"""
		SELECT results.name as asset,
			   sum(results.accumulated_depreciation_as_on_from_date) as accumulated_depreciation_as_on_from_date,
			   sum(results.depreciation_eliminated_during_the_period) as depreciation_eliminated_during_the_period,
			   sum(results.depreciation_amount_during_the_period) as depreciation_amount_during_the_period
		from (SELECT a.name as name,
				   ifnull(sum(case when gle.posting_date < %s and (ifnull(a.disposal_date, 0) = 0 or a.disposal_date >= %s) then
								   gle.debit
							  else
								   0
							  end), 0) as accumulated_depreciation_as_on_from_date,
				   ifnull(sum(case when ifnull(a.disposal_date, 0) != 0 and a.disposal_date >= %s
										and a.disposal_date <= %s and gle.posting_date <= a.disposal_date then
								   gle.debit
							  else
								   0
							  end), 0) as depreciation_eliminated_during_the_period,
				   ifnull(sum(case when gle.posting_date >= %s and gle.posting_date <= %s
										and (ifnull(a.disposal_date, 0) = 0 or gle.posting_date <= a.disposal_date) then
								   gle.debit
							  else
								   0
							  end), 0) as depreciation_amount_during_the_period
			from `tabGL Entry` gle
			join `tabAsset` a on
				gle.against_voucher = a.name
			join `tabAsset Category Account` aca on
				aca.parent = a.asset_category and aca.company_name = %s
			join `tabCompany` company on
				company.name = %s
			where
				a.docstatus=1
				and a.company=%s
				and a.purchase_date <= %s
				and gle.debit != 0
				and gle.is_cancelled = 0
				and gle.account = ifnull(aca.depreciation_expense_account, company.depreciation_expense_account)
				and a.name IN ({asset_placeholders})
			group by a.name
			union
			SELECT a.name as name,
				   ifnull(sum(case when ifnull(a.disposal_date, 0) != 0 and (a.disposal_date < %s or a.disposal_date > %s) then
									0
							   else
									a.opening_accumulated_depreciation
							   end), 0) as accumulated_depreciation_as_on_from_date,
				   ifnull(sum(case when a.disposal_date >= %s and a.disposal_date <= %s then
								   a.opening_accumulated_depreciation
							  else
								   0
							  end), 0) as depreciation_eliminated_during_the_period,
				   0 as depreciation_amount_during_the_period
			from `tabAsset` a
			where a.docstatus=1 and a.company=%s and a.purchase_date <= %s
			and a.name IN ({asset_placeholders})
			group by a.name) as results
		group by results.name
	"""

	values = [
		filters.get("from_date"),  # accumulated_depreciation_as_on_from_date condition 1
		filters.get("from_date"),  # accumulated_depreciation_as_on_from_date condition 2
		filters.get("from_date"),  # depreciation_eliminated_during_the_period condition 1
		filters.get("to_date"),    # depreciation_eliminated_during_the_period condition 2
		filters.get("from_date"),  # depreciation_amount_during_the_period condition 1
		filters.get("to_date"),    # depreciation_amount_during_the_period condition 2
		filters.get("company"),    # aca.company_name
		filters.get("company"),    # company.name
		filters.get("company"),    # a.company
		filters.get("to_date"),    # a.purchase_date
		*asset_names,              # asset names for IN clause (first union)
		filters.get("from_date"),  # disposal_date condition (second union)
		filters.get("to_date"),    # disposal_date condition (second union)
		filters.get("from_date"),  # disposal_date condition (second union)
		filters.get("to_date"),    # disposal_date condition (second union)
		filters.get("company"),    # a.company (second union)
		filters.get("to_date"),    # a.purchase_date (second union)
		*asset_names               # asset names for IN clause (second union)
	]

	return frappe.db.sql(query, values, as_dict=True)


def get_columns(filters):
	"""Создает колонки отчета"""
	columns = []
	
	# Основные колонки
	columns.extend([
		{
			"label": "Əmlak",
			"fieldname": "asset",
			"fieldtype": "Link",
			"options": "Asset",
			"width": 150
		},
		{
			"label": "Tarix",
			"fieldname": "purchase_date",
			"fieldtype": "Date",
			"width": 100
		}
	])
	
	# Создаем колонки для каждого tax article
	tax_exempt_articles = get_tax_exempt_articles_list()
	
	for article in tax_exempt_articles:
		article_key = get_field_key(article)
		
		# Сокращенное название для заголовка колонки
		short_name = get_short_article_name(article)
		
		columns.extend([
			{
				"label": f"{short_name} - " + "Qiymət " + formatdate(filters.day_before_from_date) + " əsasən",
				"fieldname": f"{article_key}_value_from",
				"fieldtype": "Currency",
				"width": 150
			},
			{
				"label": f"{short_name} - " + "Qiymət " + formatdate(filters.to_date) + " əsasən",
				"fieldname": f"{article_key}_value_to", 
				"fieldtype": "Currency",
				"width": 150
			},
			{
				"label": f"{short_name} - " + "Sığorta dəyəri",
				"fieldname": f"{article_key}_insured",
				"fieldtype": "Currency", 
				"width": 120
			}
		])
	
	return columns


def get_short_article_name(tax_article):
	"""Создает короткое название для заголовка колонки"""
	if "199.1-ci" in tax_article and "azaldılmalı" in tax_article:
		return "199.1 (Azaldılmalı)"
	elif "199.4.1-1-ci" in tax_article:
		return "199.4.1-1"
	elif "199.4.1-ci" in tax_article:
		return "199.4.1"
	elif "199.4.2-ci" in tax_article:
		return "199.4.2"
	elif "199.4.3-cü" in tax_article:
		return "199.4.3"
	elif "199.4.4-cü" in tax_article:
		return "199.4.4"
	elif "199.5-ci" in tax_article:
		return "199.5"
	elif "199.7-ci" in tax_article:
		return "199.7"
	elif "199.8-ci" in tax_article:
		return "199.8"
	elif "199.9-cu" in tax_article:
		return "199.9"
	elif "199.11-ci" in tax_article:
		return "199.11"
	elif "199.14-cü" in tax_article:
		return "199.14"
	elif "199.15-ci" in tax_article:
		return "199.15"
	elif "199.16-cı" in tax_article:
		return "199.16"
	elif "199.17-ci" in tax_article:
		return "199.17"
	elif "199.18-ci" in tax_article:
		return "199.18"
	elif "199.19-cu" in tax_article:
		return "199.19"
	elif "199.20-ci" in tax_article:
		return "199.20"
	elif "227.1-ci" in tax_article:
		return "227.1"
	elif "pay bölgüsü" in tax_article:
		return "Pay Bölgüsü"
	elif "iqtisadi zonalar" in tax_article:
		return "İqtisadi Zonalar"
	elif "beynəlxalq müqavilələr" in tax_article:
		return "Beynəlxalq"
	else:
		return "Digər"