import json

import jingrow
from jingrow import _

from crm.fcrm.pagetype.crm_dashboard.crm_dashboard import create_default_manager_dashboard
from crm.utils import sales_user_only


@jingrow.whitelist()
def reset_to_default():
	jingrow.only_for("System Manager")
	create_default_manager_dashboard(force=True)


@jingrow.whitelist()
@sales_user_only
def get_dashboard(from_date="", to_date="", user=""):
	"""
	Get the dashboard data for the CRM dashboard.
	"""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	roles = jingrow.get_roles(jingrow.session.user)
	is_sales_user = "Sales User" in roles and "Sales Manager" not in roles and "System Manager" not in roles
	if is_sales_user and not user:
		user = jingrow.session.user

	dashboard = jingrow.db.exists("CRM Dashboard", "Manager Dashboard")

	layout = []

	if not dashboard:
		layout = json.loads(create_default_manager_dashboard())
		jingrow.db.commit()
	else:
		layout = json.loads(jingrow.db.get_value("CRM Dashboard", "Manager Dashboard", "layout") or "[]")

	for l in layout:
		method_name = f"get_{l['name']}"
		if hasattr(jingrow.get_attr("crm.api.dashboard"), method_name):
			method = getattr(jingrow.get_attr("crm.api.dashboard"), method_name)
			l["data"] = method(from_date, to_date, user)
		else:
			l["data"] = None

	return layout


@jingrow.whitelist()
@sales_user_only
def get_chart(name, type, from_date="", to_date="", user=""):
	"""
	Get number chart data for the dashboard.
	"""
	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	roles = jingrow.get_roles(jingrow.session.user)
	is_sales_user = "Sales User" in roles and "Sales Manager" not in roles and "System Manager" not in roles
	if is_sales_user and not user:
		user = jingrow.session.user

	method_name = f"get_{name}"
	if hasattr(jingrow.get_attr("crm.api.dashboard"), method_name):
		method = getattr(jingrow.get_attr("crm.api.dashboard"), method_name)
		return method(from_date, to_date, user)
	else:
		return {"error": _("Invalid chart name")}


def get_total_leads(from_date, to_date, user=""):
	"""
	Get lead count for the dashboard.
	"""
	conds = ""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	if user:
		conds += f" AND lead_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
            COUNT(CASE
                WHEN creation >= %(from_date)s AND creation < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
                {conds}
                THEN name
                ELSE NULL
            END) as current_month_leads,

            COUNT(CASE
                WHEN creation >= %(prev_from_date)s AND creation < %(from_date)s
                {conds}
                THEN name
                ELSE NULL
            END) as prev_month_leads
		FROM `tabCRM Lead`
    """,
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_leads = result[0].current_month_leads or 0
	prev_month_leads = result[0].prev_month_leads or 0

	delta_in_percentage = (
		(current_month_leads - prev_month_leads) / prev_month_leads * 100 if prev_month_leads else 0
	)

	return {
		"title": _("Total leads"),
		"tooltip": _("Total number of leads"),
		"value": current_month_leads,
		"delta": delta_in_percentage,
		"deltaSuffix": "%",
	}


def get_ongoing_deals(from_date, to_date, user=""):
	"""
	Get ongoing deal count for the dashboard, and also calculate average deal value for ongoing deals.
	"""
	conds = ""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			COUNT(CASE
				WHEN d.creation >= %(from_date)s AND d.creation < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
					AND s.type NOT IN ('Won', 'Lost')
					{conds}
				THEN d.name
				ELSE NULL
			END) as current_month_deals,

			COUNT(CASE
				WHEN d.creation >= %(prev_from_date)s AND d.creation < %(from_date)s
					AND s.type NOT IN ('Won', 'Lost')
					{conds}
				THEN d.name
				ELSE NULL
			END) as prev_month_deals
		FROM `tabCRM Deal` d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
	""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_deals = result[0].current_month_deals or 0
	prev_month_deals = result[0].prev_month_deals or 0

	delta_in_percentage = (
		(current_month_deals - prev_month_deals) / prev_month_deals * 100 if prev_month_deals else 0
	)

	return {
		"title": _("Ongoing deals"),
		"tooltip": _("Total number of non won/lost deals"),
		"value": current_month_deals,
		"delta": delta_in_percentage,
		"deltaSuffix": "%",
	}


def get_average_ongoing_deal_value(from_date, to_date, user=""):
	"""
	Get ongoing deal count for the dashboard, and also calculate average deal value for ongoing deals.
	"""
	conds = ""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			AVG(CASE
				WHEN d.creation >= %(from_date)s AND d.creation < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
					AND s.type NOT IN ('Won', 'Lost')
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as current_month_avg_value,

			AVG(CASE
				WHEN d.creation >= %(prev_from_date)s AND d.creation < %(from_date)s
					AND s.type NOT IN ('Won', 'Lost')
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as prev_month_avg_value
		FROM `tabCRM Deal` d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
    """,
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_avg_value = result[0].current_month_avg_value or 0
	prev_month_avg_value = result[0].prev_month_avg_value or 0

	avg_value_delta = current_month_avg_value - prev_month_avg_value if prev_month_avg_value else 0

	return {
		"title": _("Avg. ongoing deal value"),
		"tooltip": _("Average deal value of non won/lost deals"),
		"value": current_month_avg_value,
		"delta": avg_value_delta,
		"prefix": get_base_currency_symbol(),
	}


def get_won_deals(from_date, to_date, user=""):
	"""
	Get won deal count for the dashboard, and also calculate average deal value for won deals.
	"""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	conds = ""

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			COUNT(CASE
				WHEN d.closed_date >= %(from_date)s AND d.closed_date < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
					AND s.type = 'Won'
					{conds}
				THEN d.name
				ELSE NULL
			END) as current_month_deals,

			COUNT(CASE
				WHEN d.closed_date >= %(prev_from_date)s AND d.closed_date < %(from_date)s
					AND s.type = 'Won'
					{conds}
				THEN d.name
				ELSE NULL
			END) as prev_month_deals
		FROM `tabCRM Deal` d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_deals = result[0].current_month_deals or 0
	prev_month_deals = result[0].prev_month_deals or 0

	delta_in_percentage = (
		(current_month_deals - prev_month_deals) / prev_month_deals * 100 if prev_month_deals else 0
	)

	return {
		"title": _("Won deals"),
		"tooltip": _("Total number of won deals based on its closure date"),
		"value": current_month_deals,
		"delta": delta_in_percentage,
		"deltaSuffix": "%",
	}


def get_average_won_deal_value(from_date, to_date, user=""):
	"""
	Get won deal count for the dashboard, and also calculate average deal value for won deals.
	"""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	conds = ""

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			AVG(CASE
				WHEN d.closed_date >= %(from_date)s AND d.closed_date < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
					AND s.type = 'Won'
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as current_month_avg_value,

			AVG(CASE
				WHEN d.closed_date >= %(prev_from_date)s AND d.closed_date < %(from_date)s
					AND s.type = 'Won'
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as prev_month_avg_value
		FROM `tabCRM Deal` d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_avg_value = result[0].current_month_avg_value or 0
	prev_month_avg_value = result[0].prev_month_avg_value or 0

	avg_value_delta = current_month_avg_value - prev_month_avg_value if prev_month_avg_value else 0

	return {
		"title": _("Avg. won deal value"),
		"tooltip": _("Average deal value of won deals"),
		"value": current_month_avg_value,
		"delta": avg_value_delta,
		"prefix": get_base_currency_symbol(),
	}


def get_average_deal_value(from_date, to_date, user=""):
	"""
	Get average deal value for the dashboard.
	"""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	conds = ""

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			AVG(CASE
				WHEN d.creation >= %(from_date)s AND d.creation < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
					AND s.type != 'Lost'
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as current_month_avg,

			AVG(CASE
				WHEN d.creation >= %(prev_from_date)s AND d.creation < %(from_date)s
					AND s.type != 'Lost'
					{conds}
				THEN d.deal_value * IFNULL(d.exchange_rate, 1)
				ELSE NULL
			END) as prev_month_avg
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": jingrow.utils.add_days(from_date, -diff),
		},
		as_dict=1,
	)

	current_month_avg = result[0].current_month_avg or 0
	prev_month_avg = result[0].prev_month_avg or 0

	delta = current_month_avg - prev_month_avg if prev_month_avg else 0

	return {
		"title": _("Avg. deal value"),
		"tooltip": _("Average deal value of ongoing & won deals"),
		"value": current_month_avg,
		"prefix": get_base_currency_symbol(),
		"delta": delta,
		"deltaSuffix": "%",
	}


def get_average_time_to_close_a_lead(from_date, to_date, user=""):
	"""
	Get average time to close deals for the dashboard.
	"""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	conds = ""

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	prev_from_date = jingrow.utils.add_days(from_date, -diff)
	prev_to_date = from_date

	result = jingrow.db.sql(
		f"""
		SELECT
			AVG(CASE WHEN d.closed_date >= %(from_date)s AND d.closed_date < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
				THEN TIMESTAMPDIFF(DAY, COALESCE(l.creation, d.creation), d.closed_date) END) as current_avg_lead,
			AVG(CASE WHEN d.closed_date >= %(prev_from_date)s AND d.closed_date < %(prev_to_date)s
				THEN TIMESTAMPDIFF(DAY, COALESCE(l.creation, d.creation), d.closed_date) END) as prev_avg_lead
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		LEFT JOIN `tabCRM Lead` l ON d.lead = l.name
		WHERE d.closed_date IS NOT NULL AND s.type = 'Won'
			{conds}
		""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": prev_from_date,
			"prev_to_date": prev_to_date,
		},
		as_dict=1,
	)

	current_avg_lead = result[0].current_avg_lead or 0
	prev_avg_lead = result[0].prev_avg_lead or 0
	delta_lead = current_avg_lead - prev_avg_lead if prev_avg_lead else 0

	return {
		"title": _("Avg. time to close a lead"),
		"tooltip": _("Average time taken from lead creation to deal closure"),
		"value": current_avg_lead,
		"suffix": " days",
		"delta": delta_lead,
		"deltaSuffix": " days",
		"negativeIsBetter": True,
	}


def get_average_time_to_close_a_deal(from_date, to_date, user=""):
	"""
	Get average time to close deals for the dashboard.
	"""

	diff = jingrow.utils.date_diff(to_date, from_date)
	if diff == 0:
		diff = 1

	conds = ""

	if user:
		conds += f" AND d.deal_owner = '{user}'"

	prev_from_date = jingrow.utils.add_days(from_date, -diff)
	prev_to_date = from_date

	result = jingrow.db.sql(
		f"""
		SELECT
			AVG(CASE WHEN d.closed_date >= %(from_date)s AND d.closed_date < DATE_ADD(%(to_date)s, INTERVAL 1 DAY)
				THEN TIMESTAMPDIFF(DAY, d.creation, d.closed_date) END) as current_avg_deal,
			AVG(CASE WHEN d.closed_date >= %(prev_from_date)s AND d.closed_date < %(prev_to_date)s
				THEN TIMESTAMPDIFF(DAY, d.creation, d.closed_date) END) as prev_avg_deal
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		LEFT JOIN `tabCRM Lead` l ON d.lead = l.name
		WHERE d.closed_date IS NOT NULL AND s.type = 'Won'
			{conds}
		""",
		{
			"from_date": from_date,
			"to_date": to_date,
			"prev_from_date": prev_from_date,
			"prev_to_date": prev_to_date,
		},
		as_dict=1,
	)

	current_avg_deal = result[0].current_avg_deal or 0
	prev_avg_deal = result[0].prev_avg_deal or 0
	delta_deal = current_avg_deal - prev_avg_deal if prev_avg_deal else 0

	return {
		"title": _("Avg. time to close a deal"),
		"tooltip": _("Average time taken from deal creation to deal closure"),
		"value": current_avg_deal,
		"suffix": " days",
		"delta": delta_deal,
		"deltaSuffix": " days",
		"negativeIsBetter": True,
	}


def get_sales_trend(from_date="", to_date="", user=""):
	"""
	Get sales trend data for the dashboard.
	[
		{ date: new Date('2024-05-01'), leads: 45, deals: 23, won_deals: 12 },
		{ date: new Date('2024-05-02'), leads: 50, deals: 30, won_deals: 15 },
		...
	]
	"""

	lead_conds = ""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		lead_conds += f" AND lead_owner = '{user}'"
		deal_conds += f" AND deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			DATE_FORMAT(date, '%%Y-%%m-%%d') AS date,
			SUM(leads) AS leads,
			SUM(deals) AS deals,
			SUM(won_deals) AS won_deals
		FROM (
			SELECT
				DATE(creation) AS date,
				COUNT(*) AS leads,
				0 AS deals,
				0 AS won_deals
			FROM `tabCRM Lead`
			WHERE DATE(creation) BETWEEN %(from)s AND %(to)s
			{lead_conds}
			GROUP BY DATE(creation)

			UNION ALL

			SELECT
				DATE(d.creation) AS date,
				0 AS leads,
				COUNT(*) AS deals,
				SUM(CASE WHEN s.type = 'Won' THEN 1 ELSE 0 END) AS won_deals
			FROM `tabCRM Deal` d
			JOIN `tabCRM Deal Status` s ON d.status = s.name
			WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s
			{deal_conds}
			GROUP BY DATE(d.creation)
		) AS daily
		GROUP BY date
		ORDER BY date
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	sales_trend = [
		{
			"date": jingrow.utils.get_datetime(row.date).strftime("%Y-%m-%d"),
			"leads": row.leads or 0,
			"deals": row.deals or 0,
			"won_deals": row.won_deals or 0,
		}
		for row in result
	]

	return {
		"data": sales_trend,
		"title": _("Sales trend"),
		"subtitle": _("Daily performance of leads, deals, and wins"),
		"xAxis": {
			"title": _("Date"),
			"key": "date",
			"type": "time",
			"timeGrain": "day",
		},
		"yAxis": {
			"title": _("Count"),
		},
		"series": [
			{"name": "leads", "type": "line", "showDataPoints": True},
			{"name": "deals", "type": "line", "showDataPoints": True},
			{"name": "won_deals", "type": "line", "showDataPoints": True},
		],
	}


def get_forecasted_revenue(from_date="", to_date="", user=""):
	"""
	Get forecasted revenue for the dashboard.
	[
		{ date: new Date('2024-05-01'), forecasted: 1200000, actual: 980000 },
		{ date: new Date('2024-06-01'), forecasted: 1350000, actual: 1120000 },
		{ date: new Date('2024-07-01'), forecasted: 1600000, actual: "" },
		{ date: new Date('2024-08-01'), forecasted: 1500000, actual: "" },
		...
	]
	"""
	deal_conds = ""

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			DATE_FORMAT(d.expected_closure_date, '%Y-%m')                        AS month,
			SUM(
				CASE
					WHEN s.type = 'Lost' THEN d.expected_deal_value * IFNULL(d.exchange_rate, 1)
					ELSE d.expected_deal_value * IFNULL(d.probability, 0) / 100 * IFNULL(d.exchange_rate, 1)  -- forecasted
				END
			)                                                       AS forecasted,
			SUM(
				CASE
					WHEN s.type = 'Won' THEN d.deal_value * IFNULL(d.exchange_rate, 1)            -- actual
					ELSE 0
				END
			)                                                       AS actual
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		WHERE d.expected_closure_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
		{deal_conds}
		GROUP BY DATE_FORMAT(d.expected_closure_date, '%Y-%m')
		ORDER BY month
		""",
		as_dict=True,
	)

	for row in result:
		row["month"] = jingrow.utils.get_datetime(row["month"]).strftime("%Y-%m-01")
		row["forecasted"] = row["forecasted"] or ""
		row["actual"] = row["actual"] or ""

	return {
		"data": result or [],
		"title": _("Forecasted revenue"),
		"subtitle": _("Projected vs actual revenue based on deal probability"),
		"xAxis": {
			"title": _("Month"),
			"key": "month",
			"type": "time",
			"timeGrain": "month",
		},
		"yAxis": {
			"title": _("Revenue") + f" ({get_base_currency_symbol()})",
		},
		"series": [
			{"name": "forecasted", "type": "line", "showDataPoints": True},
			{"name": "actual", "type": "line", "showDataPoints": True},
		],
	}


def get_funnel_conversion(from_date="", to_date="", user=""):
	"""
	Get funnel conversion data for the dashboard.
	[
		{ stage: 'Leads', count: 120 },
		{ stage: 'Qualification', count: 100 },
		{ stage: 'Negotiation', count: 80 },
		{ stage: 'Ready to Close', count: 60 },
		{ stage: 'Won', count: 30 },
		...
	]
	"""
	lead_conds = ""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		lead_conds += f" AND lead_owner = '{user}'"
		deal_conds += f" AND deal_owner = '{user}'"

	result = []

	# Get total leads
	total_leads = jingrow.db.sql(
		f"""
			SELECT COUNT(*) AS count
			FROM `tabCRM Lead`
			WHERE DATE(creation) BETWEEN %(from)s AND %(to)s
			{lead_conds}
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)
	total_leads_count = total_leads[0].count if total_leads else 0

	result.append({"stage": "Leads", "count": total_leads_count})

	result += get_deal_status_change_counts(from_date, to_date, deal_conds)

	return {
		"data": result or [],
		"title": _("Funnel conversion"),
		"subtitle": _("Lead to deal conversion pipeline"),
		"xAxis": {
			"title": _("Stage"),
			"key": "stage",
			"type": "category",
		},
		"yAxis": {
			"title": _("Count"),
		},
		"swapXY": True,
		"series": [
			{
				"name": "count",
				"type": "bar",
				"echartOptions": {
					"colorBy": "data",
				},
			},
		],
	}


def get_deals_by_stage_axis(from_date="", to_date="", user=""):
	"""
	Get deal data by stage for the dashboard.
	[
		{ stage: 'Prospecting', count: 120 },
		{ stage: 'Negotiation', count: 45 },
		...
	]
	"""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			d.status AS stage,
			COUNT(*) AS count,
			s.type AS status_type
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s AND s.type NOT IN ('Lost')
		{deal_conds}
		GROUP BY d.status
		ORDER BY count DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Deals by ongoing & won stage"),
		"xAxis": {
			"title": _("Stage"),
			"key": "stage",
			"type": "category",
		},
		"yAxis": {"title": _("Count")},
		"series": [
			{"name": "count", "type": "bar"},
		],
	}


def get_deals_by_stage_donut(from_date="", to_date="", user=""):
	"""
	Get deal data by stage for the dashboard.
	[
		{ stage: 'Prospecting', count: 120 },
		{ stage: 'Negotiation', count: 45 },
		...
	]
	"""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			d.status AS stage,
			COUNT(*) AS count,
			s.type AS status_type
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s
		{deal_conds}
		GROUP BY d.status
		ORDER BY count DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Deals by stage"),
		"subtitle": _("Current pipeline distribution"),
		"categoryColumn": "stage",
		"valueColumn": "count",
	}


def get_lost_deal_reasons(from_date="", to_date="", user=""):
	"""
	Get lost deal reasons for the dashboard.
	[
		{ reason: 'Price too high', count: 20 },
		{ reason: 'Competitor won', count: 15 },
		...
	]
	"""

	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			d.lost_reason AS reason,
			COUNT(*) AS count
		FROM `tabCRM Deal` AS d
		JOIN `tabCRM Deal Status` s ON d.status = s.name
		WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s AND s.type = 'Lost'
		{deal_conds}
		GROUP BY d.lost_reason
		HAVING reason IS NOT NULL AND reason != ''
		ORDER BY count DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Lost deal reasons"),
		"subtitle": _("Common reasons for losing deals"),
		"xAxis": {
			"title": _("Reason"),
			"key": "reason",
			"type": "category",
		},
		"yAxis": {
			"title": _("Count"),
		},
		"series": [
			{"name": "count", "type": "bar"},
		],
	}


def get_leads_by_source(from_date="", to_date="", user=""):
	"""
	Get lead data by source for the dashboard.
	[
		{ source: 'Website', count: 120 },
		{ source: 'Referral', count: 45 },
		...
	]
	"""
	lead_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		lead_conds += f" AND lead_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			IFNULL(source, 'Empty') AS source,
			COUNT(*) AS count
		FROM `tabCRM Lead`
		WHERE DATE(creation) BETWEEN %(from)s AND %(to)s
		{lead_conds}
		GROUP BY source
		ORDER BY count DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Leads by source"),
		"subtitle": _("Lead generation channel analysis"),
		"categoryColumn": "source",
		"valueColumn": "count",
	}


def get_deals_by_source(from_date="", to_date="", user=""):
	"""
	Get deal data by source for the dashboard.
	[
		{ source: 'Website', count: 120 },
		{ source: 'Referral', count: 45 },
		...
	]
	"""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			IFNULL(source, 'Empty') AS source,
			COUNT(*) AS count
		FROM `tabCRM Deal`
		WHERE DATE(creation) BETWEEN %(from)s AND %(to)s
		{deal_conds}
		GROUP BY source
		ORDER BY count DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Deals by source"),
		"subtitle": _("Deal generation channel analysis"),
		"categoryColumn": "source",
		"valueColumn": "count",
	}


def get_deals_by_territory(from_date="", to_date="", user=""):
	"""
	Get deal data by territory for the dashboard.
	[
		{ territory: 'North America', deals: 45, value: 2300000 },
		{ territory: 'Europe', deals: 30, value: 1500000 },
		...
	]
	"""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			IFNULL(d.territory, 'Empty') AS territory,
			COUNT(*) AS deals,
			SUM(COALESCE(d.deal_value, 0) * IFNULL(d.exchange_rate, 1)) AS value
		FROM `tabCRM Deal` AS d
		WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s
		{deal_conds}
		GROUP BY d.territory
		ORDER BY value DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Deals by territory"),
		"subtitle": _("Geographic distribution of deals and revenue"),
		"xAxis": {
			"title": _("Territory"),
			"key": "territory",
			"type": "category",
		},
		"yAxis": {
			"title": _("Number of deals"),
		},
		"y2Axis": {
			"title": _("Deal value") + f" ({get_base_currency_symbol()})",
		},
		"series": [
			{"name": "deals", "type": "bar"},
			{"name": "value", "type": "line", "showDataPoints": True, "axis": "y2"},
		],
	}


def get_deals_by_salesperson(from_date="", to_date="", user=""):
	"""
	Get deal data by salesperson for the dashboard.
	[
		{ salesperson: 'John Smith', deals: 45, value: 2300000 },
		{ salesperson: 'Jane Doe', deals: 30, value: 1500000 },
		...
	]
	"""
	deal_conds = ""

	if not from_date or not to_date:
		from_date = jingrow.utils.get_first_day(from_date or jingrow.utils.nowdate())
		to_date = jingrow.utils.get_last_day(to_date or jingrow.utils.nowdate())

	if user:
		deal_conds += f" AND d.deal_owner = '{user}'"

	result = jingrow.db.sql(
		f"""
		SELECT
			IFNULL(u.full_name, d.deal_owner) AS salesperson,
			COUNT(*)                           AS deals,
			SUM(COALESCE(d.deal_value, 0) * IFNULL(d.exchange_rate, 1)) AS value
		FROM `tabCRM Deal` AS d
		LEFT JOIN `tabUser` AS u ON u.name = d.deal_owner
		WHERE DATE(d.creation) BETWEEN %(from)s AND %(to)s
		{deal_conds}
		GROUP BY d.deal_owner
		ORDER BY value DESC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)

	return {
		"data": result or [],
		"title": _("Deals by salesperson"),
		"subtitle": _("Number of deals and total value per salesperson"),
		"xAxis": {
			"title": _("Salesperson"),
			"key": "salesperson",
			"type": "category",
		},
		"yAxis": {
			"title": _("Number of deals"),
		},
		"y2Axis": {
			"title": _("Deal value") + f" ({get_base_currency_symbol()})",
		},
		"series": [
			{"name": "deals", "type": "bar"},
			{"name": "value", "type": "line", "showDataPoints": True, "axis": "y2"},
		],
	}


def get_base_currency_symbol():
	"""
	Get the base currency symbol from the system settings.
	"""
	base_currency = jingrow.db.get_single_value("FCRM Settings", "currency") or "USD"
	return jingrow.db.get_value("Currency", base_currency, "symbol") or ""


def get_deal_status_change_counts(from_date, to_date, deal_conds=""):
	"""
	Get count of each status change (to) for each deal, excluding deals with current status type 'Lost'.
	Order results by status position.
	Returns:
	[
	  {"status": "Qualification", "count": 120},
	  {"status": "Negotiation", "count": 85},
	  ...
	]
	"""
	result = jingrow.db.sql(
		f"""
		SELECT
			scl.to AS stage,
			COUNT(*) AS count
		FROM
			`tabCRM Status Change Log` scl
		JOIN
			`tabCRM Deal` d ON scl.parent = d.name
		JOIN
			`tabCRM Deal Status` s ON d.status = s.name
		JOIN
			`tabCRM Deal Status` st ON scl.to = st.name
		WHERE
			scl.to IS NOT NULL
			AND scl.to != ''
			AND s.type != 'Lost'
			AND DATE(d.creation) BETWEEN %(from)s AND %(to)s
			{deal_conds}
		GROUP BY
			scl.to, st.position
		ORDER BY
			st.position ASC
		""",
		{"from": from_date, "to": to_date},
		as_dict=True,
	)
	return result or []