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

import frappe
from frappe import _
from frappe.utils import flt, getdate


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

	return columns, data


def get_columns(filters=None):
	"""Get report columns definition (Cədvəl 18(1) — ƏDV - Kənd təsərrüfatı)."""
	return [
		{
			"label": _("Period"),
			"fieldname": "period",
			"fieldtype": "Data",
			"width": 200
		},
		{
			# Hesabat dövrünün əvvəlinə satılmamış malların (alış qiyməti ilə) məbləği
			"label": _("Opening balance (purchase price)"),
			"fieldname": "opening_balance",
			"fieldtype": "Currency",
			"width": 200
		},
		{
			# Hesabat dövrü ərzində alınmış malların (alış qiyməti ilə) məbləği
			"label": _("Purchased in period (purchase price)"),
			"fieldname": "purchased",
			"fieldtype": "Currency",
			"width": 200
		},
		{
			# Dövr ərzində satılmış malların (satış qiyməti ilə) məbləği
			"label": _("Sold in period (sale price)"),
			"fieldname": "sold",
			"fieldtype": "Currency",
			"width": 200
		},
		{
			# Dövr ərzində satılmış malların (alış qiyməti ilə) məbləği
			"label": _("Sold in period (purchase price)"),
			"fieldname": "sold_purchase_price",
			"fieldtype": "Currency",
			"width": 220
		},
		{
			# Dövrün sonuna malların (alış qiyməti ilə) qalıq məbləği
			"label": _("Closing balance (purchase price)"),
			"fieldname": "closing_balance",
			"fieldtype": "Currency",
			"width": 200
		},
		{
			# Cari hesabat dövründə satılmış mallara görə ticarət əlavəsi
			"label": _("Trade markup on goods sold"),
			"fieldname": "trade_markup",
			"fieldtype": "Currency",
			"width": 220
		},
		{
			# Cari hesabat dövründə satılmış mallara görə ticarət əlavəsinin ƏDV məbləği
			"label": _("VAT on trade markup"),
			"fieldname": "trade_markup_vat",
			"fieldtype": "Currency",
			"width": 200
		}
	]


def _company_cond(company, params, alias):
	if company:
		params["company"] = company
		return f" AND {alias}.company = %(company)s"
	return ""


def _stock_value_as_of(company, date):
	"""Total stock value (purchase price) of agricultural items as of a date.

	Sums the running stock_value of the latest Stock Ledger Entry per
	item+warehouse on or before the given date.
	"""
	if not date:
		return 0.0

	params = {"date": date}
	company_cond = _company_cond(company, params, "sle")

	result = frappe.db.sql(f"""
		SELECT COALESCE(SUM(t.stock_value), 0)
		FROM (
			SELECT
				sle.stock_value,
				ROW_NUMBER() OVER (
					PARTITION BY sle.item_code, sle.warehouse
					ORDER BY sle.posting_date DESC, sle.posting_time DESC, sle.creation DESC
				) AS rn
			FROM `tabStock Ledger Entry` sle
			INNER JOIN `tabItem` it
				ON it.name = sle.item_code AND it.agricultural_goods = 1
			WHERE sle.is_cancelled = 0
				AND sle.posting_date <= %(date)s
				{company_cond}
		) t
		WHERE t.rn = 1
	""", params)

	return flt(result[0][0]) if result else 0.0


def _movements(company, from_date, to_date):
	"""Incoming (purchased) and outgoing (COGS) stock value within the period."""
	params = {}
	conditions = "sle.is_cancelled = 0 AND it.agricultural_goods = 1"
	conditions += _company_cond(company, params, "sle")

	if from_date:
		conditions += " AND sle.posting_date >= %(from_date)s"
		params["from_date"] = from_date
	if to_date:
		conditions += " AND sle.posting_date <= %(to_date)s"
		params["to_date"] = to_date

	# Direction is decided by the value change, not actual_qty: opening-stock
	# reconciliations post with actual_qty = 0 but a non-zero value.
	result = frappe.db.sql(f"""
		SELECT
			COALESCE(SUM(CASE WHEN sle.stock_value_difference > 0 THEN sle.stock_value_difference ELSE 0 END), 0) AS purchased,
			COALESCE(SUM(CASE WHEN sle.stock_value_difference < 0 THEN -sle.stock_value_difference ELSE 0 END), 0) AS cogs
		FROM `tabStock Ledger Entry` sle
		INNER JOIN `tabItem` it ON it.name = sle.item_code
		WHERE {conditions}
	""", params, as_dict=1)

	row = result[0] if result else {}
	return flt(row.get("purchased")), flt(row.get("cogs"))


def _sold_sale_price(company, from_date, to_date):
	"""Net sale value of agricultural items that actually moved stock.

	Only Sales Invoice item rows that produced an outgoing Stock Ledger entry
	are counted, so the figure stays consistent with COGS / closing: a sale
	that did not update stock contributes 0 (just like it contributes no COGS).
	"""
	params = {}
	conditions = "si.docstatus = 1 AND it.agricultural_goods = 1"
	conditions += _company_cond(company, params, "si")

	if from_date:
		conditions += " AND si.posting_date >= %(from_date)s"
		params["from_date"] = from_date
	if to_date:
		conditions += " AND si.posting_date <= %(to_date)s"
		params["to_date"] = to_date

	result = frappe.db.sql(f"""
		SELECT COALESCE(SUM(sii.net_amount), 0)
		FROM `tabSales Invoice Item` sii
		INNER JOIN `tabSales Invoice` si ON si.name = sii.parent
		INNER JOIN `tabItem` it ON it.name = sii.item_code
		WHERE {conditions}
			AND EXISTS (
				SELECT 1 FROM `tabStock Ledger Entry` sle
				WHERE sle.voucher_type = 'Sales Invoice'
					AND sle.voucher_no = si.name
					AND sle.item_code = sii.item_code
					AND sle.is_cancelled = 0
					AND sle.stock_value_difference < 0
			)
	""", params)

	return flt(result[0][0]) if result else 0.0


def get_data(filters=None):
	"""Single row for the selected period, sourced from the Stock Ledger.

	Opening/closing balances and cost of goods sold come from ERPNext's Stock
	Ledger (which already applies the company's valuation method and includes
	opening stock entered via Stock Reconciliation). Sale price comes from
	Sales Invoices. All amounts are net of VAT.
	"""
	filters = filters or {}

	company = filters.get("company")
	from_date = getdate(filters["from_date"]) if filters.get("from_date") else None
	to_date = getdate(filters["to_date"]) if filters.get("to_date") else None

	opening_balance = _stock_value_as_of(company, _day_before(from_date)) if from_date else 0.0
	purchased, cogs = _movements(company, from_date, to_date)
	sold = _sold_sale_price(company, from_date, to_date)

	if to_date:
		closing_balance = _stock_value_as_of(company, to_date)
	else:
		closing_balance = opening_balance + purchased - cogs

	# Cari hesabat dövründə satılmış mallara görə ticarət əlavəsi = sale - cost.
	trade_markup = sold - cogs
	trade_markup_vat = trade_markup * 0.18

	if from_date and to_date:
		period = f"{from_date} — {to_date}"
	elif to_date:
		period = f"... — {to_date}"
	else:
		period = _("All time")

	return [{
		"period": period,
		"opening_balance": opening_balance,
		"purchased": purchased,
		"sold": sold,
		"sold_purchase_price": cogs,
		"closing_balance": closing_balance,
		"trade_markup": trade_markup,
		"trade_markup_vat": trade_markup_vat
	}]


def _day_before(d):
	from datetime import timedelta
	return d - timedelta(days=1)
