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

import frappe
from frappe.model.document import Document
from frappe.utils import flt, add_days
import re
import json


class Propertytaxreturn(Document):
	pass


@frappe.whitelist()
def populate_property_tax_tables(company, year, from_date, to_date, calculation_mode, existing_elave_1, existing_elave_2):
	"""
	Main method to populate Property Tax Return child tables from Asset data

	Args:
		company: Company name
		year: Tax year (integer)
		from_date: Start date (YYYY-MM-DD)
		to_date: End date (YYYY-MM-DD)
		calculation_mode: 'tam_il' or 'il_erzinde'
		existing_elave_1: Existing Əlavə 1 rows (JSON array)
		existing_elave_2: Existing Əlavə 2 rows (JSON array)

	Returns:
		Dictionary with elave_1_data and elave_2_data mappings
	"""
	try:
		# Parse JSON if needed
		if isinstance(existing_elave_1, str):
			existing_elave_1 = json.loads(existing_elave_1)
		if isinstance(existing_elave_2, str):
			existing_elave_2 = json.loads(existing_elave_2)

		# Get aggregated asset data
		taxable_aggregated = get_taxable_assets_aggregated(company, from_date, to_date)
		tax_exempt_aggregated = get_tax_exempt_assets_aggregated(company, from_date, to_date)

		# Map to rows with proper field names based on calculation_mode
		# Use fuzzy matching for Əlavə 2 (Taxable Assets)
		elave_2_data = {}
		for category, data in taxable_aggregated.items():
			# Find matching row by partial match (e.g., "Mənzillər" matches "1.5 Mənzillər")
			matching_key = find_matching_category(category, existing_elave_2)

			if matching_key:
				if calculation_mode == 'tam_il':
					elave_2_data[matching_key] = {
						'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyəri': data['value_as_on_from_date'],
						'hesabatilinsonunaəsasvəsaitlərinqalıqdəyəri': data['net_asset_value_as_on_to_date'],
						'hesabatilierzindeuçotaalınan': 0,
						'artıqqiymətəəsasvəsaitlərindəyəri505': data['insurance_value']
					}
				else:  # il_erzinde
					# Бизнес-правило: Для компаний, созданных в течение года (İl Ərzində),
					# если нет новых покупок (value_of_new_purchase < 0.01), то все поля = 0
					if flt(data.get('value_of_new_purchase', 0)) >= 0.01:
						elave_2_data[matching_key] = {
							'hesabatilierzindeuçotaalınan': data['value_of_new_purchase'],
							'hesabatilinsonunaəsasvəsaitlərinqalıqdəyəri': data['net_asset_value_as_on_to_date'],
							'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyəri': 0,
							'artıqqiymətəəsasvəsaitlərindəyəri505': data['insurance_value']
						}
					else:
						# Нет новых покупок - очистить все поля
						elave_2_data[matching_key] = {
							'hesabatilierzindeuçotaalınan': 0,
							'hesabatilinsonunaəsasvəsaitlərinqalıqdəyəri': 0,
							'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyəri': 0,
							'artıqqiymətəəsasvəsaitlərindəyəri505': 0
						}

		# Map to rows with fuzzy matching for Əlavə 1 (Tax Exempt Assets)
		elave_1_data = {}
		for article_number, data in tax_exempt_aggregated.items():
			# Find matching row by article number in "Azadolma səbəbi"
			matching_key = find_matching_azadolma_sebeb(article_number, existing_elave_1)

			if matching_key:
				if calculation_mode == 'tam_il':
					elave_1_data[matching_key] = {
						'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyərimanatla5012': data['value_as_on_from_date'],
						'ilinsonunaəsasvəsaitlərinqalıqdəyəri': data['net_asset_value_as_on_to_date'],
						'hesabatiliərzindəəsasvəsaitlərinqalıqdəyəri': 0,
						'artıqqiymətəəsasvəsaitlərindəyəri': data['insurance_value']
					}
				else:  # il_erzinde
					# Бизнес-правило: Для компаний, созданных в течение года (İl Ərzində),
					# если нет новых покупок (value_of_new_purchase < 0.01), то все поля = 0
					if flt(data.get('value_of_new_purchase', 0)) >= 0.01:
						elave_1_data[matching_key] = {
							'hesabatiliərzindəəsasvəsaitlərinqalıqdəyəri': data['value_of_new_purchase'],
							'ilinsonunaəsasvəsaitlərinqalıqdəyəri': data['net_asset_value_as_on_to_date'],
							'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyərimanatla5012': 0,
							'artıqqiymətəəsasvəsaitlərindəyəri': data['insurance_value']
						}
					else:
						# Нет новых покупок - очистить все поля
						elave_1_data[matching_key] = {
							'hesabatiliərzindəəsasvəsaitlərinqalıqdəyəri': 0,
							'ilinsonunaəsasvəsaitlərinqalıqdəyəri': 0,
							'hesabatilininəvvəlinəəsasvəsaitlərinqalıqdəyərimanatla5012': 0,
							'artıqqiymətəəsasvəsaitlərindəyəri': 0
						}

		return {
			'success': True,
			'elave_1_data': elave_1_data,
			'elave_2_data': elave_2_data
		}

	except Exception as e:
		frappe.log_error(f"Error in populate_property_tax_tables: {str(e)}", "Property Tax Return")
		return {
			'success': False,
			'error': str(e)
		}


def get_taxable_assets_aggregated(company, from_date, to_date):
	"""
	Get aggregated data for taxable assets grouped by taxable_asset_type

	Returns:
		Dictionary: {
			'taxable_asset_type_value': {
				'value_as_on_from_date': float,
				'value_of_new_purchase': float,
				'net_asset_value_as_on_to_date': float
			}
		}
	"""
	# Get all assets with taxable_asset_type populated
	assets_with_taxable_type = frappe.db.sql("""
		SELECT
			name,
			taxable_asset_type
		FROM
			`tabAsset`
		WHERE
			docstatus = 1
			AND company = %(company)s
			AND taxable_asset_type IS NOT NULL
			AND taxable_asset_type != ''
			AND purchase_date <= %(to_date)s
	""", {
		'company': company,
		'to_date': to_date
	}, as_dict=True)

	if not assets_with_taxable_type:
		return {}

	# Get asset balances
	asset_names = [asset['name'] for asset in assets_with_taxable_type]
	asset_balances = get_asset_balances_using_standard_logic(company, from_date, to_date, asset_names)

	# Aggregate by taxable_asset_type
	aggregated = {}

	for asset in assets_with_taxable_type:
		asset_type = asset['taxable_asset_type']

		# Find balance for this asset
		balance = next(
			(b for b in asset_balances if b['asset'] == asset['name']),
			{}
		)

		# Initialize if not exists
		if asset_type not in aggregated:
			aggregated[asset_type] = {
				'value_as_on_from_date': 0.0,
				'value_of_new_purchase': 0.0,
				'net_asset_value_as_on_to_date': 0.0,
				'insurance_value': 0.0
			}

		# Accumulate values
		aggregated[asset_type]['value_as_on_from_date'] += flt(balance.get('value_as_on_from_date', 0))
		aggregated[asset_type]['value_of_new_purchase'] += flt(balance.get('value_of_new_purchase', 0))
		aggregated[asset_type]['net_asset_value_as_on_to_date'] += flt(balance.get('net_asset_value_as_on_to_date', 0))
		aggregated[asset_type]['insurance_value'] += flt(balance.get('insurance_value', 0))

	return aggregated


def get_tax_exempt_assets_aggregated(company, from_date, to_date):
	"""
	Get aggregated data for tax exempt assets grouped by tax article

	Returns:
		Dictionary: {
			'tax_article_number': {  # e.g., '199.1', '207.3'
				'value_as_on_from_date': float,
				'value_of_new_purchase': float,
				'net_asset_value_as_on_to_date': float
			}
		}
	"""
	# Get all assets with industrial_tax_article or tax_exempt_tax_article
	# Exclude agricultural_tax_article as per requirements
	assets_with_tax_articles = frappe.db.sql("""
		SELECT
			a.name,
			a.industrial_tax_article,
			a.tax_exempt_tax_article,
			COALESCE(NULLIF(a.industrial_tax_article, ''), a.tax_exempt_tax_article) as tax_article
		FROM
			`tabAsset` a
		WHERE
			a.docstatus = 1
			AND a.company = %(company)s
			AND (
				(a.industrial_tax_article IS NOT NULL AND a.industrial_tax_article != '')
				OR (a.tax_exempt_tax_article IS NOT NULL AND a.tax_exempt_tax_article != '')
			)
			AND a.purchase_date <= %(to_date)s
	""", {
		'company': company,
		'to_date': to_date
	}, as_dict=True)

	if not assets_with_tax_articles:
		return {}

	# Get asset balances
	asset_names = [asset['name'] for asset in assets_with_tax_articles]
	asset_balances = get_asset_balances_using_standard_logic(company, from_date, to_date, asset_names)

	# Aggregate by tax article number
	aggregated = {}

	for asset in assets_with_tax_articles:
		# Extract article number from Tax Article name
		tax_article_full = asset['tax_article']
		article_number = extract_article_number(tax_article_full)

		if not article_number:
			continue

		# Find balance for this asset
		balance = next(
			(b for b in asset_balances if b['asset'] == asset['name']),
			{}
		)

		# Initialize if not exists
		if article_number not in aggregated:
			aggregated[article_number] = {
				'value_as_on_from_date': 0.0,
				'value_of_new_purchase': 0.0,
				'net_asset_value_as_on_to_date': 0.0,
				'insurance_value': 0.0
			}

		# Accumulate values
		aggregated[article_number]['value_as_on_from_date'] += flt(balance.get('value_as_on_from_date', 0))
		aggregated[article_number]['value_of_new_purchase'] += flt(balance.get('value_of_new_purchase', 0))
		aggregated[article_number]['net_asset_value_as_on_to_date'] += flt(balance.get('net_asset_value_as_on_to_date', 0))
		aggregated[article_number]['insurance_value'] += flt(balance.get('insurance_value', 0))

	return aggregated


def extract_article_number(tax_article_name):
	"""
	Extract article number from Tax Article name

	Examples:
		"Vergi Məcəlləsinin 199.1-ci maddəsinə əsasən" → "199.1"
		"Vergi Məcəlləsinin 207.3-cü maddəsinə əsasən" → "207.3"
		"Vergi Məcəlləsinin 199.4.1-ci maddəsinə əsasən" → "199.4.1"

	Returns:
		String: Article number or None if not found
	"""
	if not tax_article_name:
		return None

	# Pattern to match article numbers like 199.1, 207.3, 199.4.1-1, 227.1
	pattern = r'(\d+(?:\.\d+)*(?:-\d+)?)'
	match = re.search(pattern, tax_article_name)

	if match:
		return match.group(1)

	return None


def find_matching_azadolma_sebeb(article_number, existing_rows):
	"""
	Find the "Azadolma səbəbi" value that contains the given article number

	Args:
		article_number: String like "199.1" or "207.3"
		existing_rows: List of existing Əlavə 1 rows

	Returns:
		The matching "Azadolma səbəbi" value or None
	"""
	if not article_number:
		return None

	for row in existing_rows:
		azadolma_sebeb = row.get('azadolmasəbəbi', '')
		if article_number in azadolma_sebeb:
			return azadolma_sebeb

	return None


def find_matching_category(asset_category, existing_rows):
	"""
	Find matching category in Əlavə 2 rows using partial match

	Examples:
		Asset has: "Mənzillər"
		Row has: "1.5 Mənzillər"
		Result: Match found

		Asset has: "Qeyri-yaşayış binaları (sahələri)"
		Row has: "1.2 Qeyri-yaşayış binaları (sahələri)"
		Result: Match found

	Args:
		asset_category: Category from Asset's taxable_asset_type field
		existing_rows: List of existing Əlavə 2 rows

	Returns:
		The matching "Vergiyə cəlb olunan əmlakların kateqoriyası" value or None
	"""
	if not asset_category:
		return None

	for row in existing_rows:
		row_category = row.get('vergiyəcəlbolunanəmlaklarınkateqoriyası', '')

		# Check if asset_category is contained in row_category (e.g., "Mənzillər" in "1.5 Mənzillər")
		if asset_category in row_category:
			return row_category

		# Also check reverse - if row_category is contained in asset_category
		# (in case asset has full name with prefix)
		if row_category in asset_category:
			return row_category

	return None


def get_asset_balances_using_standard_logic(company, from_date, to_date, asset_names):
	"""
	Calculate asset balances using ERPNext's Asset Depreciations and Balances report logic

	Returns:
		List of dictionaries with asset balances
	"""
	if not asset_names:
		return []

	# Check ERPNext version for field compatibility
	version_info = check_erpnext_version()
	purchase_amount_field = version_info['purchase_amount_field']
	has_capitalization = version_info['has_capitalization']

	# Get asset details (gross values)
	asset_details = get_asset_cost_details(
		company, from_date, to_date, asset_names,
		purchase_amount_field, has_capitalization
	)

	# Get depreciation details
	asset_depreciation = get_asset_depreciation_details(
		company, from_date, to_date, asset_names
	)

	# Get value adjustments
	asset_adjustments = get_asset_value_adjustments(
		company, from_date, to_date, asset_names
	)

	# Combine and calculate net values
	result = []

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

		# Find depreciation
		depreciation = next(
			(dep for dep in asset_depreciation if dep['asset'] == asset_name),
			{}
		)

		# Find adjustments
		adjustments = asset_adjustments.get(asset_name, {
			'adjustment_before_from_date': 0.0,
			'adjustment_till_to_date': 0.0
		})

		# Calculate values following ERPNext standard report logic
		value_as_on_from_date = flt(asset_detail.get('cost_as_on_from_date', 0))
		value_as_on_from_date += adjustments['adjustment_before_from_date']

		value_of_new_purchase = flt(asset_detail.get('cost_of_new_purchase', 0))

		adjustment_during_period = (
			adjustments['adjustment_till_to_date'] -
			adjustments['adjustment_before_from_date']
		)

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

		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)) -
			flt(depreciation.get('depreciation_eliminated_via_reversal', 0))
		)

		net_asset_value_as_on_from_date = (
			value_as_on_from_date - accumulated_depreciation_as_on_from_date
		)

		net_asset_value_as_on_to_date = (
			value_as_on_to_date - accumulated_depreciation_as_on_to_date
		)

		# Calculate insurance value (Insured value - Gross Purchase Amount)
		# Version-compat: ERPNext v15 renamed Asset.gross_purchase_amount -> net_purchase_amount
		# (patch v15_0/rename_gross_purchase_amount_to_net_purchase_amount). Use the same field
		# the rest of this cost logic uses (purchase_amount_field), with explicit fallback.
		_asset_cols = frappe.db.get_table_columns('Asset')
		if 'gross_purchase_amount' in _asset_cols:
			_gross_field = 'gross_purchase_amount'
		elif 'net_purchase_amount' in _asset_cols:
			_gross_field = 'net_purchase_amount'
		else:
			_gross_field = 'purchase_amount'
		insured_value = flt(frappe.db.get_value('Asset', asset_name, 'insured_value') or 0)
		gross_purchase_amount = flt(frappe.db.get_value('Asset', asset_name, _gross_field) or 0)
		# Only calculate difference if insured_value is actually set (> 0)
		insurance_difference = (insured_value - gross_purchase_amount) if insured_value > 0 else 0

		balance_result = {
			'asset': asset_name,
			'value_as_on_from_date': value_as_on_from_date,
			'value_of_new_purchase': value_of_new_purchase,
			'net_asset_value_as_on_from_date': net_asset_value_as_on_from_date,
			'net_asset_value_as_on_to_date': net_asset_value_as_on_to_date,
			'insurance_value': insurance_difference
		}
		result.append(balance_result)

	return result


def check_erpnext_version():
	"""Check ERPNext version for field compatibility"""
	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_asset_cost_details(company, from_date, to_date, asset_names, purchase_amount_field, has_capitalization):
	"""Get asset cost details (mimics ERPNext standard report)"""

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

	capitalization_check = ""
	if 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
			)
		"""
	else:
		# В версии 15 просто исключаем активы где есть запись в Asset Capitalization Asset Item
		capitalization_check = " AND NOT EXISTS(SELECT name FROM `tabAsset Capitalization Asset Item` WHERE asset = a.name)"

	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,

			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 = 'Capitalized' THEN a.{purchase_amount_field} ELSE 0 END
					ELSE 0
				END
			), 0) as cost_of_capitalized_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 = [
		from_date,  # cost_as_on_from_date condition 1
		from_date,  # cost_as_on_from_date condition 2
		from_date,  # cost_of_new_purchase
		from_date,  # cost_of_sold_asset condition 1
		to_date,    # cost_of_sold_asset condition 2
		from_date,  # cost_of_scrapped_asset condition 1
		to_date,    # cost_of_scrapped_asset condition 2
		from_date,  # cost_of_capitalized_asset condition 1
		to_date,    # cost_of_capitalized_asset condition 2
		company,    # company filter
		to_date,    # purchase_date filter
		*asset_names  # asset names for IN clause
	]

	if has_capitalization:
		values.append(from_date)

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


def get_asset_depreciation_details(company, from_date, to_date, asset_names):
	"""Get asset depreciation details (mimics ERPNext standard report)"""

	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,
			SUM(results.depreciation_eliminated_via_reversal) as depreciation_eliminated_via_reversal
		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 gle.posting_date <= %s
							AND IFNULL(a.disposal_date, 0) = 0
						THEN gle.credit
						ELSE 0
					END
				), 0) as depreciation_eliminated_via_reversal,

				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.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
						THEN 0
						ELSE a.opening_accumulated_depreciation
					END
				), 0) as accumulated_depreciation_as_on_from_date,
				0 as depreciation_eliminated_via_reversal,
				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 = [
		from_date,  # accumulated_depreciation_as_on_from_date condition 1
		from_date,  # accumulated_depreciation_as_on_from_date condition 2
		to_date,    # depreciation_eliminated_via_reversal
		from_date,  # depreciation_eliminated_during_the_period condition 1
		to_date,    # depreciation_eliminated_during_the_period condition 2
		from_date,  # depreciation_amount_during_the_period condition 1
		to_date,    # depreciation_amount_during_the_period condition 2
		company,    # aca.company_name
		company,    # company.name
		company,    # a.company
		to_date,    # a.purchase_date
		*asset_names,  # asset names for IN clause (first SELECT)
		from_date,  # disposal_date condition (second SELECT)
		from_date,  # disposal_date condition (second SELECT)
		to_date,    # disposal_date condition (second SELECT)
		company,    # a.company (second SELECT)
		to_date,    # a.purchase_date (second SELECT)
		*asset_names   # asset names for IN clause (second SELECT)
	]

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


def get_asset_value_adjustments(company, from_date, to_date, asset_names):
	"""Get asset value adjustments (mimics ERPNext standard report)"""

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

	query = f"""
		SELECT
			a.name AS asset,
			IFNULL(
				SUM(
					CASE
						WHEN gle.posting_date < %s
							AND (a.disposal_date IS NULL OR a.disposal_date >= %s)
						THEN gle.debit - gle.credit
						ELSE 0
					END
				),
			0) AS value_adjustment_before_from_date,
			IFNULL(
				SUM(
					CASE
						WHEN gle.posting_date <= %s
							AND (a.disposal_date IS NULL OR a.disposal_date >= %s)
						THEN gle.debit - gle.credit
						ELSE 0
					END
				),
			0) AS value_adjustment_till_to_date
		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
		WHERE
			gle.is_cancelled = 0
			AND a.docstatus = 1
			AND a.company = %s
			AND a.purchase_date <= %s
			AND gle.account = aca.fixed_asset_account
			AND a.name IN ({asset_placeholders})
		GROUP BY a.name
	"""

	values = [
		from_date,  # value_adjustment_before_from_date condition 1
		from_date,  # value_adjustment_before_from_date condition 2
		to_date,    # value_adjustment_till_to_date condition 1
		to_date,    # value_adjustment_till_to_date condition 2
		company,    # aca.company_name
		company,    # a.company
		to_date,    # a.purchase_date
		*asset_names   # asset names for IN clause
	]

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

	# Convert to dictionary
	adjustment_map = {}
	for r in result:
		adjustment_map[r['asset']] = {
			'adjustment_before_from_date': flt(r.get('value_adjustment_before_from_date', 0)),
			'adjustment_till_to_date': flt(r.get('value_adjustment_till_to_date', 0))
		}

	return adjustment_map


@frappe.whitelist()
def calculate_months_for_vergi_hesab_4cu_1(company, year):
	"""
	Находит самый ранний Asset по available_for_use_date в указанном году
	и возвращает количество месяцев от этого месяца до конца года (включительно)

	Args:
		company: Название компании
		year: Год для поиска (integer)

	Returns:
		Integer: Количество месяцев (1-12) или 0 если нет Assets
	"""
	try:
		# Найти самый ранний Asset в году
		# docstatus = 1 означает статус "Submitted" (не Draft и не Cancelled)
		earliest_asset = frappe.db.sql("""
			SELECT MIN(available_for_use_date) as earliest_date
			FROM `tabAsset`
			WHERE company = %(company)s
			  AND docstatus = 1
			  AND available_for_use_date IS NOT NULL
			  AND YEAR(available_for_use_date) = %(year)s
		""", {
			'company': company,
			'year': year
		}, as_dict=True)

		if not earliest_asset or not earliest_asset[0].get('earliest_date'):
			return 0

		earliest_date = earliest_asset[0]['earliest_date']

		# Получить номер месяца (1-12)
		from datetime import datetime
		if isinstance(earliest_date, str):
			earliest_date = datetime.strptime(earliest_date, '%Y-%m-%d')

		month = earliest_date.month

		# Вычислить количество месяцев от этого месяца до конца года (не включая этот месяц)
		# Январь (1) → 12-1=11 месяцев, Март (3) → 12-3=9 месяцев, Сентябрь (9) → 12-9=3 месяца
		months_count = 12 - month

		return months_count

	except Exception as e:
		frappe.log_error(f"Error in calculate_months_for_vergi_hesab_4cu_1: {str(e)}", "Property Tax Return")
		return 0
