# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt


import copy
from collections import defaultdict

import frappe
from frappe import _
from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import cint, flt, get_datetime
from pypika import Order
from pypika.analytics import RowNumber

from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import get_stock_balance_for
from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.utils import (
	is_reposting_item_valuation_in_progress,
	update_included_uom_in_report,
)


def execute(filters=None):
	is_reposting_item_valuation_in_progress()
	include_uom = filters.get("include_uom")
	columns = get_columns(filters)
	items = get_items(filters)
	sl_entries = get_stock_ledger_entries(filters, items)
	item_details = get_item_details(items, sl_entries, include_uom)

	inv_dimension_key = []
	inv_dimension_wise_value = get_inv_dimension_wise_value(filters)
	if inv_dimension_wise_value:
		for key in inv_dimension_wise_value:
			value = inv_dimension_wise_value[key]
			if isinstance(value, list):
				inv_dimension_key.extend(value)
			else:
				inv_dimension_key.append(value)

	if filters.get("batch_no"):
		opening_row = get_opening_balance_from_batch(filters, columns, sl_entries)
	elif inv_dimension_wise_value:
		opening_row = get_opening_balance_for_inv_dimension(filters, inv_dimension_wise_value)
	else:
		opening_row = get_opening_balance(filters, columns, sl_entries, inv_dimension_wise_value)

	precision = cint(frappe.db.get_single_value("System Settings", "float_precision"))
	bundle_details = {}

	if filters.get("segregate_serial_batch_bundle"):
		bundle_details = get_serial_batch_bundle_details(sl_entries, filters)

	data = []
	conversion_factors = []
	opening_rows = opening_row if isinstance(opening_row, list) else ([opening_row] if opening_row else [])
	for row in opening_rows:
		data.append(row)
		conversion_factors.append(0)

	actual_qty = stock_value = 0
	if opening_rows:
		actual_qty = opening_rows[0].get("qty_after_transaction", 0)
		stock_value = opening_rows[0].get("stock_value", 0)

	available_serial_nos = {}

	batch_balance_dict = frappe._dict({})
	if actual_qty and filters.get("batch_no"):
		batch_balance_dict[filters.batch_no] = [actual_qty, stock_value]

	inv_dimension_wise_dict = frappe._dict({})
	set_opening_row_for_inv_dimension(
		inv_dimension_wise_dict, filters, inv_dimension_key=inv_dimension_key, opening_row=opening_row
	)

	item_wh_wise_prev_sle = {}
	for sle in sl_entries:
		item_detail = item_details[sle.item_code]

		sle.update(item_detail)
		if bundle_info := bundle_details.get(sle.serial_and_batch_bundle):
			data.extend(get_segregated_bundle_entries(sle, bundle_info, batch_balance_dict, filters))
			continue

		if inv_dimension_key:
			set_balance_value_for_inv_dimesion(inv_dimension_key, inv_dimension_wise_dict, sle)

		if filters.get("batch_no"):
			actual_qty += flt(sle.actual_qty, precision)
			stock_value += sle.stock_value_difference
			if sle.batch_no:
				if not batch_balance_dict.get(sle.batch_no):
					batch_balance_dict[sle.batch_no] = [0, 0]

				batch_balance_dict[sle.batch_no][0] += sle.actual_qty
				batch_balance_dict[sle.batch_no][1] += stock_value

			if filters.get("segregate_serial_batch_bundle"):
				actual_qty = batch_balance_dict[sle.batch_no][0]

			if sle.voucher_type == "Stock Reconciliation" and not sle.actual_qty:
				actual_qty = sle.qty_after_transaction
				stock_value = sle.stock_value

			sle.update({"qty_after_transaction": actual_qty, "stock_value": stock_value})

		sle.update({"in_qty": max(sle.actual_qty, 0), "out_qty": min(sle.actual_qty, 0)})

		if sle.serial_no:
			update_available_serial_nos(available_serial_nos, sle)

		if sle.actual_qty < 0:
			sle["in_out_rate"] = flt(sle.stock_value_difference / sle.actual_qty, precision)
			sle["incoming_rate"] = 0

		elif sle.voucher_type == "Stock Reconciliation" and sle.actual_qty < 0:
			sle["in_out_rate"] = sle.valuation_rate

		if (
			sle.voucher_type == "Stock Reconciliation"
			and not sle.in_qty
			and not sle.out_qty
			and not sle.actual_qty
		):
			if prev_sle := item_wh_wise_prev_sle.get((sle.item_code, sle.warehouse)):
				bal_qty = prev_sle.get("qty_after_transaction", 0)
				qty = sle.qty_after_transaction - bal_qty
				if qty > 0:
					sle.in_qty = qty
				elif qty < 0:
					sle.out_qty = qty

		item_wh_wise_prev_sle[(sle.item_code, sle.warehouse)] = sle
		data.append(sle)

		if include_uom:
			conversion_factors.append(item_detail.conversion_factor)

	update_included_uom_in_report(columns, data, include_uom, conversion_factors)
	return columns, data


def set_opening_row_for_inv_dimension(
	inv_dimension_wise_dict, filters, inv_dimension_key=None, opening_row=None
):
	if (
		not inv_dimension_key
		or not opening_row
		or not filters.get("item_code")
		or not filters.get("warehouse")
	):
		return

	if len(filters.get("item_code")) > 1 or len(filters.get("warehouse")) > 1:
		return

	if inv_dimension_key and opening_row and filters.get("item_code") and filters.get("warehouse"):
		new_key = copy.deepcopy(inv_dimension_key)
		new_key.extend([filters.item_code[0], filters.warehouse[0]])

		opening_key = tuple(new_key)
		inv_dimension_wise_dict[opening_key] = {
			"qty_after_transaction": flt(opening_row.get("qty_after_transaction")),
			"dimension_stock_value": flt(opening_row.get("stock_value")),
		}


def set_balance_value_for_inv_dimesion(inv_dimension_key, inv_dimension_wise_dict, sle):
	new_key = copy.deepcopy(inv_dimension_key)
	new_key.extend([sle.item_code, sle.warehouse])
	new_key = tuple(new_key)

	if new_key not in inv_dimension_wise_dict:
		inv_dimension_wise_dict[new_key] = {"qty_after_transaction": 0, "dimension_stock_value": 0}

	inv_dimesion_value = inv_dimension_wise_dict[new_key]
	inv_dimesion_value["qty_after_transaction"] += sle.actual_qty
	inv_dimesion_value["dimension_stock_value"] += sle.stock_value_difference
	sle.update(
		{
			"qty_after_transaction": inv_dimesion_value["qty_after_transaction"],
			"stock_value": inv_dimesion_value["dimension_stock_value"],
		}
	)


def get_segregated_bundle_entries(sle, bundle_details, batch_balance_dict, filters):
	segregated_entries = []
	qty_before_transaction = sle.qty_after_transaction - sle.actual_qty
	stock_value_before_transaction = sle.stock_value - sle.stock_value_difference

	for row in bundle_details:
		new_sle = copy.deepcopy(sle)
		new_sle.update(row)
		new_sle.update(
			{
				"in_out_rate": flt(new_sle.stock_value_difference / row.qty) if row.qty < 0 else 0,
				"in_qty": row.qty if row.qty > 0 else 0,
				"out_qty": row.qty if row.qty < 0 else 0,
				"qty_after_transaction": qty_before_transaction + row.qty,
				"stock_value": stock_value_before_transaction + new_sle.stock_value_difference,
				"incoming_rate": row.incoming_rate if row.qty > 0 else 0,
			}
		)

		if filters.get("batch_no") and row.batch_no:
			if not batch_balance_dict.get(row.batch_no):
				batch_balance_dict[row.batch_no] = [0, 0]

			batch_balance_dict[row.batch_no][0] += row.qty
			batch_balance_dict[row.batch_no][1] += row.stock_value_difference

			new_sle.update(
				{
					"qty_after_transaction": batch_balance_dict[row.batch_no][0],
					"stock_value": batch_balance_dict[row.batch_no][1],
				}
			)

		qty_before_transaction += row.qty
		stock_value_before_transaction += new_sle.stock_value_difference

		new_sle.valuation_rate = (
			stock_value_before_transaction / qty_before_transaction if qty_before_transaction else 0
		)

		segregated_entries.append(new_sle)

	return segregated_entries


def get_serial_batch_bundle_details(sl_entries, filters=None):
	bundle_details = []
	for sle in sl_entries:
		if sle.serial_and_batch_bundle:
			bundle_details.append(sle.serial_and_batch_bundle)

	if not bundle_details:
		return frappe._dict({})

	query_filers = {"parent": ("in", bundle_details)}
	if filters.get("batch_no"):
		query_filers["batch_no"] = filters.batch_no

	_bundle_details = frappe._dict({})
	batch_entries = frappe.get_all(
		"Serial and Batch Entry",
		filters=query_filers,
		fields=["parent", "qty", "incoming_rate", "stock_value_difference", "batch_no", "serial_no"],
		order_by="parent, idx",
	)
	for entry in batch_entries:
		_bundle_details.setdefault(entry.parent, []).append(entry)

	return _bundle_details


def update_available_serial_nos(available_serial_nos, sle):
	serial_nos = get_serial_nos(sle.serial_no)
	key = (sle.item_code, sle.warehouse)
	if key not in available_serial_nos:
		stock_balance = get_stock_balance_for(
			sle.item_code, sle.warehouse, sle.posting_date, sle.posting_time
		)
		serials = get_serial_nos(stock_balance["serial_nos"]) if stock_balance["serial_nos"] else []
		available_serial_nos.setdefault(key, serials)

	existing_serial_no = available_serial_nos[key]
	for sn in serial_nos:
		if sle.actual_qty > 0:
			if sn in existing_serial_no:
				existing_serial_no.remove(sn)
			else:
				existing_serial_no.append(sn)
		else:
			if sn in existing_serial_no:
				existing_serial_no.remove(sn)
			else:
				existing_serial_no.append(sn)

	sle.balance_serial_no = "\n".join(existing_serial_no)


def get_columns(filters):
	columns = [
		{"label": _("Date"), "fieldname": "date", "fieldtype": "Datetime", "width": 150},
		{
			"label": _("Item"),
			"fieldname": "item_code",
			"fieldtype": "Link",
			"options": "Item",
			"width": 100,
		},
		{"label": _("Item Name"), "fieldname": "item_name", "width": 100},
		{
			"label": _("Stock UOM"),
			"fieldname": "stock_uom",
			"fieldtype": "Link",
			"options": "UOM",
			"width": 90,
		},
	]

	for dimension in get_inventory_dimensions():
		columns.append(
			{
				"label": _(dimension.doctype),
				"fieldname": dimension.fieldname,
				"fieldtype": "Link",
				"options": dimension.doctype,
				"width": 110,
			}
		)

	columns.extend(
		[
			{
				"label": _("In Qty"),
				"fieldname": "in_qty",
				"fieldtype": "Float",
				"width": 80,
				"convertible": "qty",
			},
			{
				"label": _("Out Qty"),
				"fieldname": "out_qty",
				"fieldtype": "Float",
				"width": 80,
				"convertible": "qty",
			},
			{
				"label": _("Balance Qty"),
				"fieldname": "qty_after_transaction",
				"fieldtype": "Float",
				"width": 100,
				"convertible": "qty",
			},
			{
				"label": _("Warehouse"),
				"fieldname": "warehouse",
				"fieldtype": "Link",
				"options": "Warehouse",
				"width": 150,
			},
			{
				"label": _("Item Group"),
				"fieldname": "item_group",
				"fieldtype": "Link",
				"options": "Item Group",
				"width": 100,
			},
			{
				"label": _("Brand"),
				"fieldname": "brand",
				"fieldtype": "Link",
				"options": "Brand",
				"width": 100,
			},
			{"label": _("Description"), "fieldname": "description", "width": 200},
			{
				"label": _("Incoming Rate"),
				"fieldname": "incoming_rate",
				"fieldtype": "Currency",
				"width": 110,
				"options": "Company:company:default_currency",
				"convertible": "rate",
			},
			{
				"label": _("Avg Rate (Balance Stock)"),
				"fieldname": "valuation_rate",
				"fieldtype": filters.valuation_field_type,
				"width": 180,
				"options": "Company:company:default_currency"
				if filters.valuation_field_type == "Currency"
				else None,
				"convertible": "rate",
			},
			{
				"label": _("Outgoing Rate"),
				"fieldname": "in_out_rate",
				"fieldtype": filters.valuation_field_type,
				"width": 140,
				"options": "Company:company:default_currency"
				if filters.valuation_field_type == "Currency"
				else None,
				"convertible": "rate",
			},
			{
				"label": _("Balance Value"),
				"fieldname": "stock_value",
				"fieldtype": "Currency",
				"width": 110,
				"options": "Company:company:default_currency",
			},
			{
				"label": _("Value Change"),
				"fieldname": "stock_value_difference",
				"fieldtype": "Currency",
				"width": 110,
				"options": "Company:company:default_currency",
			},
			{"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 110},
			{
				"label": _("Voucher #"),
				"fieldname": "voucher_no",
				"fieldtype": "Dynamic Link",
				"options": "voucher_type",
				"width": 100,
			},
			{
				"label": _("Serial and Batch Bundle"),
				"fieldname": "serial_and_batch_bundle",
				"fieldtype": "Link",
				"options": "Serial and Batch Bundle",
				"width": 150,
				"hidden": not filters.get("segregate_serial_batch_bundle"),
			},
			{
				"label": _("Batch"),
				"fieldname": "batch_no",
				"fieldtype": "Link",
				"options": "Batch",
				"width": 100,
				"hidden": not filters.get("segregate_serial_batch_bundle"),
			},
			{
				"label": _("Serial No"),
				"fieldname": "serial_no",
				"fieldtype": "Link",
				"options": "Serial No",
				"width": 100,
				"hidden": not filters.get("segregate_serial_batch_bundle"),
			},
			{
				"label": _("Project"),
				"fieldname": "project",
				"fieldtype": "Link",
				"options": "Project",
				"width": 100,
			},
			{
				"label": _("Company"),
				"fieldname": "company",
				"fieldtype": "Link",
				"options": "Company",
				"width": 110,
			},
		]
	)

	return columns


def get_stock_ledger_entries(filters, items):
	from_date = get_datetime(filters.from_date + " 00:00:00")
	to_date = get_datetime(filters.to_date + " 23:59:59")

	sle = frappe.qb.DocType("Stock Ledger Entry")
	query = (
		frappe.qb.from_(sle)
		.select(
			sle.item_code,
			sle.posting_datetime.as_("date"),
			sle.warehouse,
			sle.posting_date,
			sle.posting_time,
			sle.actual_qty,
			sle.incoming_rate,
			sle.valuation_rate,
			sle.company,
			sle.voucher_type,
			sle.qty_after_transaction,
			sle.stock_value_difference,
			sle.serial_and_batch_bundle,
			sle.voucher_no,
			sle.stock_value,
			sle.batch_no,
			sle.serial_no,
			sle.project,
		)
		.where((sle.docstatus < 2) & (sle.is_cancelled == 0) & (sle.posting_datetime[from_date:to_date]))
		.orderby(sle.posting_datetime)
		.orderby(sle.creation)
	)

	inventory_dimension_fields = get_inventory_dimension_fields()
	if inventory_dimension_fields:
		for fieldname in inventory_dimension_fields:
			query = query.select(fieldname)
			if fieldname in filters and filters.get(fieldname):
				query = query.where(sle[fieldname].isin(filters.get(fieldname)))

	if items:
		query = query.where(sle.item_code.isin(items))

	for field in ["voucher_no", "project", "company"]:
		if filters.get(field) and field not in inventory_dimension_fields:
			query = query.where(sle[field] == filters.get(field))

	if filters.get("batch_no"):
		bundles = get_serial_and_batch_bundles(filters)

		if bundles:
			query = query.where(
				(sle.serial_and_batch_bundle.isin(bundles)) | (sle.batch_no == filters.batch_no)
			)
		else:
			query = query.where(sle.batch_no == filters.batch_no)

	query = apply_warehouse_filter(query, sle, filters)

	return query.run(as_dict=True)


def get_serial_and_batch_bundles(filters):
	SBB = frappe.qb.DocType("Serial and Batch Bundle")
	SBE = frappe.qb.DocType("Serial and Batch Entry")

	query = (
		frappe.qb.from_(SBE)
		.inner_join(SBB)
		.on(SBE.parent == SBB.name)
		.select(SBE.parent)
		.where(
			(SBB.docstatus == 1)
			& (SBB.has_batch_no == 1)
			& (SBB.voucher_no.notnull())
			& (SBE.batch_no == filters.batch_no)
		)
	)

	return query.run(pluck=SBE.parent)


def get_inventory_dimension_fields():
	return [dimension.fieldname for dimension in get_inventory_dimensions()]


def get_items(filters):
	item = frappe.qb.DocType("Item")
	query = frappe.qb.from_(item).select(item.name)
	conditions = []

	if item_codes := filters.get("item_code"):
		conditions.append(item.name.isin(item_codes))

	else:
		if brand := filters.get("brand"):
			conditions.append(item.brand == brand)

		if filters.get("item_group") and (
			condition := get_item_group_condition(filters.get("item_group"), item)
		):
			conditions.append(condition)

	items = []
	if conditions:
		for condition in conditions:
			query = query.where(condition)

		items = [r[0] for r in query.run()]

	return items


def get_item_details(items, sl_entries, include_uom):
	item_details = {}
	if not items:
		items = list(set(d.item_code for d in sl_entries))

	if not items:
		return item_details

	item = frappe.qb.DocType("Item")
	query = (
		frappe.qb.from_(item)
		.select(item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom)
		.where(item.name.isin(items))
	)

	if include_uom:
		ucd = frappe.qb.DocType("UOM Conversion Detail")
		query = (
			query.left_join(ucd)
			.on((ucd.parent == item.name) & (ucd.uom == include_uom))
			.select(ucd.conversion_factor)
		)

	res = query.run(as_dict=True)

	for item in res:
		item_details.setdefault(item.name, item)

	return item_details


# TODO: THIS IS NOT USED
def get_sle_conditions(filters):
	conditions = []
	if filters.get("warehouse"):
		warehouse_condition = get_warehouse_condition(filters.get("warehouse"))
		if warehouse_condition:
			conditions.append(warehouse_condition)
	if filters.get("voucher_no"):
		conditions.append("voucher_no=%(voucher_no)s")
	if filters.get("batch_no"):
		conditions.append("batch_no=%(batch_no)s")
	if filters.get("project"):
		conditions.append("project=%(project)s")

	for dimension in get_inventory_dimensions():
		if filters.get(dimension.fieldname):
			conditions.append(f"{dimension.fieldname} in %({dimension.fieldname})s")

	return "and {}".format(" and ".join(conditions)) if conditions else ""


def get_opening_balance_from_batch(filters, columns, sl_entries):
	query_filters = {
		"batch_no": filters.batch_no,
		"docstatus": 1,
		"is_cancelled": 0,
		"posting_date": ("<", filters.from_date),
		"company": filters.company,
	}

	for fields in ["item_code", "warehouse"]:
		if value := filters.get(fields):
			query_filters[fields] = ("in", value)

	opening_data = frappe.get_all(
		"Stock Ledger Entry",
		fields=[
			{"SUM": "actual_qty", "as": "qty_after_transaction"},
			{"SUM": "stock_value_difference", "as": "stock_value"},
		],
		filters=query_filters,
	)[0]

	for field in ["qty_after_transaction", "stock_value", "valuation_rate"]:
		if opening_data.get(field) is None:
			opening_data[field] = 0.0

	table = frappe.qb.DocType("Stock Ledger Entry")
	sabb_table = frappe.qb.DocType("Serial and Batch Entry")
	query = (
		frappe.qb.from_(table)
		.inner_join(sabb_table)
		.on(table.serial_and_batch_bundle == sabb_table.parent)
		.select(
			Sum(sabb_table.qty).as_("qty"),
			Sum(sabb_table.stock_value_difference).as_("stock_value"),
		)
		.where(
			(sabb_table.batch_no == filters.batch_no)
			& (sabb_table.docstatus == 1)
			& (table.posting_date < filters.from_date)
			& (table.is_cancelled == 0)
		)
	)

	for field in ["item_code", "warehouse", "company"]:
		value = filters.get(field)

		if not value:
			continue

		if isinstance(value, list | tuple):
			query = query.where(table[field].isin(value))

		else:
			query = query.where(table[field] == value)

	bundle_data = query.run(as_dict=True)

	if bundle_data:
		opening_data.qty_after_transaction += flt(bundle_data[0].qty)
		opening_data.stock_value += flt(bundle_data[0].stock_value)
		if opening_data.qty_after_transaction:
			opening_data.valuation_rate = flt(opening_data.stock_value) / flt(
				opening_data.qty_after_transaction
			)

	return {
		"item_code": _("'Opening'"),
		"qty_after_transaction": opening_data.qty_after_transaction,
		"valuation_rate": opening_data.valuation_rate,
		"stock_value": opening_data.stock_value,
	}


def get_opening_balance(filters, columns, sl_entries, inv_dimension_wise_value=None):
	if not (filters.item_code and filters.warehouse and filters.from_date):
		return

	item_codes = filters.item_code
	if isinstance(item_codes, str):
		item_codes = [item_codes]

	warehouses = get_matching_warehouses(filters.warehouse)
	if not warehouses:
		return

	sle_doctype = frappe.qb.DocType("Stock Ledger Entry")
	sr_doctype = frappe.qb.DocType("Stock Reconciliation")

	opening_reco_query = (
		frappe.qb.from_(sle_doctype)
		.inner_join(sr_doctype)
		.on(sle_doctype.voucher_no == sr_doctype.name)
		.select(sle_doctype.voucher_no)
		.where(sle_doctype.docstatus < 2)
		.where(sle_doctype.is_cancelled == 0)
		.where(sle_doctype.item_code.isin(item_codes))
		.where(sle_doctype.warehouse.isin(warehouses))
		.where(sle_doctype.voucher_type == "Stock Reconciliation")
		.where(sle_doctype.posting_date == filters.from_date)
		.where(sr_doctype.purpose == "Opening Stock")
	)

	opening_reco_vouchers = set(opening_reco_query.run(pluck=True))

	if opening_reco_vouchers:
		sl_entries[:] = [sle for sle in sl_entries if sle.get("voucher_no") not in opening_reco_vouchers]

	sle_cond = (sle_doctype.posting_date < filters.from_date) | (
		(sle_doctype.posting_date == filters.from_date) & (sle_doctype.posting_time == "00:00:00")
	)
	if opening_reco_vouchers:
		sle_cond = sle_cond | (
			(sle_doctype.posting_date == filters.from_date)
			& (sle_doctype.voucher_no.isin(list(opening_reco_vouchers)))
		)

	subq = (
		frappe.qb.from_(sle_doctype)
		.select(
			sle_doctype.qty_after_transaction,
			sle_doctype.stock_value,
			RowNumber()
			.over(sle_doctype.item_code, sle_doctype.warehouse)
			.orderby(sle_doctype.posting_datetime, sle_doctype.creation, sle_doctype.name, order=Order.desc)
			.as_("rn"),
		)
		.where(sle_doctype.docstatus < 2)
		.where(sle_doctype.is_cancelled == 0)
		.where(sle_doctype.item_code.isin(item_codes))
		.where(sle_doctype.warehouse.isin(warehouses))
		.where(sle_cond)
	)

	for field in ["voucher_no", "project", "company"]:
		if filters.get(field):
			subq = subq.where(sle_doctype[field] == filters.get(field))

	inventory_dimension_fields = get_inventory_dimension_fields()
	if inventory_dimension_fields:
		for fieldname in inventory_dimension_fields:
			if filters.get(fieldname):
				subq = subq.where(sle_doctype[fieldname].isin(filters.get(fieldname)))

	query = (
		frappe.qb.from_(subq)
		.select(
			IfNull(Sum(subq.qty_after_transaction), 0.0).as_("total_qty"),
			IfNull(Sum(subq.stock_value), 0.0).as_("total_stock_value"),
		)
		.where(subq.rn == 1)
	)

	res = query.run(as_dict=True)

	total_qty = flt(res[0].total_qty) if res else 0.0
	total_stock_value = flt(res[0].total_stock_value) if res else 0.0
	valuation_rate = flt(total_stock_value / total_qty) if total_qty else 0.0

	return {
		"item_code": _("'Opening'"),
		"qty_after_transaction": total_qty,
		"valuation_rate": valuation_rate,
		"stock_value": total_stock_value,
	}


def get_matching_warehouses(warehouses):
	if not warehouses:
		return []

	if isinstance(warehouses, str):
		warehouses = [warehouses]

	warehouse_details = frappe.get_all(
		"Warehouse",
		filters={"name": ("in", warehouses)},
		fields=["lft", "rgt"],
	)

	if not warehouse_details:
		return warehouses

	wh = frappe.qb.DocType("Warehouse")
	cond = None
	for d in warehouse_details:
		c = (wh.lft >= d.lft) & (wh.rgt <= d.rgt)
		cond = c if cond is None else (cond | c)

	matching = (frappe.qb.from_(wh).select(wh.name).where(cond)).run(pluck=True)

	return matching if matching else warehouses


def get_warehouse_condition(warehouses):
	if not warehouses:
		return ""

	if isinstance(warehouses, str):
		warehouses = [warehouses]

	warehouse_range = frappe.get_all(
		"Warehouse",
		filters={
			"name": ("in", warehouses),
		},
		fields=["lft", "rgt"],
		as_list=True,
	)

	if not warehouse_range:
		return ""

	alias = "wh"
	conditions = []
	for lft, rgt in warehouse_range:
		conditions.append(f"({alias}.lft >= {lft} and {alias}.rgt <= {rgt})")

	conditions = " or ".join(conditions)

	return f" exists (select name from `tabWarehouse` {alias} \
		where ({conditions}) and warehouse = {alias}.name)"


def get_item_group_condition(item_group, item_table=None):
	item_group_details = frappe.db.get_value("Item Group", item_group, ["lft", "rgt"], as_dict=1)
	if item_group_details:
		if item_table:
			ig = frappe.qb.DocType("Item Group")
			return item_table.item_group.isin(
				frappe.qb.from_(ig)
				.select(ig.name)
				.where(
					(ig.lft >= item_group_details.lft)
					& (ig.rgt <= item_group_details.rgt)
					& (item_table.item_group == ig.name)
				)
			)
		else:
			return f"item.item_group in (select ig.name from `tabItem Group` ig \
				where ig.lft >= {item_group_details.lft} and ig.rgt <= {item_group_details.rgt} and item.item_group = ig.name)"


def get_opening_balance_for_inv_dimension(filters, inv_dimension_wise_value):
	if not filters.item_code or not filters.warehouse or not filters.from_date:
		return

	item_codes = filters.get("item_code")
	if isinstance(item_codes, str):
		item_codes = [item_codes]

	warehouses = filters.get("warehouse")
	if isinstance(warehouses, str):
		warehouses = [warehouses]

	if len(item_codes) > 1 or len(warehouses) > 1:
		return

	sl_doctype = frappe.qb.DocType("Stock Ledger Entry")

	query = (
		frappe.qb.from_(sl_doctype)
		.select(
			sl_doctype.item_code,
			sl_doctype.warehouse,
			Sum(sl_doctype.actual_qty).as_("qty_after_transaction"),
			Sum(sl_doctype.stock_value_difference).as_("stock_value"),
		)
		.where(
			(sl_doctype.posting_date < filters.from_date)
			& (sl_doctype.docstatus < 2)
			& (sl_doctype.is_cancelled == 0)
		)
	)

	if item_codes:
		query = query.where(sl_doctype.item_code.isin(item_codes))

	if warehouses:
		query = query.where(sl_doctype.warehouse.isin(warehouses))

	for key, value in inv_dimension_wise_value.items():
		if isinstance(value, list | tuple):
			query = query.where(sl_doctype[key].isin(value))
		else:
			query = query.where(sl_doctype[key] == value)

	opening_data = query.run(as_dict=True)

	if opening_data:
		return frappe._dict(
			{
				"item_code": _("'Opening'"),
				"qty_after_transaction": opening_data[0].qty_after_transaction,
				"stock_value": opening_data[0].stock_value,
				"valuation_rate": flt(opening_data[0].stock_value)
				/ flt(opening_data[0].qty_after_transaction)
				if opening_data[0].qty_after_transaction
				else 0,
			}
		)

	return frappe._dict({})


def get_inv_dimension_wise_value(filters) -> list:
	inv_dimension_key = frappe._dict({})
	for dimension in get_inventory_dimensions():
		if dimension.fieldname in filters and filters.get(dimension.fieldname):
			inv_dimension_key[dimension.fieldname] = filters.get(dimension.fieldname)

	if filters.get("project") and not frappe.get_all(
		"Inventory Dimension", filters={"reference_document": "Project"}
	):
		inv_dimension_key["project"] = filters.get("project")

	return inv_dimension_key
