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

import frappe
from frappe import _
from frappe.query_builder.functions import Sum
from frappe.utils import flt

def execute(filters=None):
	columns = get_columns()
	data = get_data(filters)
	return columns, data

def get_columns():
	item_groups = get_predefined_item_groups()
	sales_groups = get_sales_item_groups()
	
	columns = [
		{
			"label": _("Date"),
			"fieldname": "date",
			"fieldtype": "Date",
			"width": 100,
		}
	]
	
	# Add a column for each item group
	for group in item_groups:
		columns.append({
			"label": _(group),
			"fieldname": get_field_name(group),
			"fieldtype": "Float",
			"width": 120,
		})
	
	# Add sales columns for first 3 groups
	for group in sales_groups:
		columns.append({
			"label": _(group + " (Sales)"),
			"fieldname": get_field_name(group + "_sales"),
			"fieldtype": "Currency",
			"width": 130,
		})
	
	return columns

def get_predefined_item_groups():
	"""Return the hardcoded list of item groups"""
	return [
		"Xam neft - ton",
		"Təbii qaz - m³",
		"Filiz faydalı qazıntıları: bütün növ metallar - ton",
		"Mişar daşları - m³",
		"Üzlük daşları - m³",
		"Seolit - ton",
		"Barit - ton",
		"Yüngül doldurucular üçün gillər - m³",
		"Vulkan külü və pemza - m³",
		"Kvars qumları - ton",
		"Sement xammalı - ton",
		"Duz - ton",
		"Qiymətli və yarımqiymətli bəzək daşları - karat / qram",
		"Yodlu bromlu sular - m³",
		"Mineral sular - m³",
		"Bentonit gillər - ton",
		"Kərpic-kirəmit gilləri - m³",
		"Tikinti qumları - m³",
		"Çınqıl xammalı - m³",
		"Gips, gəc - ton"
	]

def get_sales_item_groups():
	"""Return the first 3 item groups for sales columns"""
	return [
		"Xam neft - ton",
		"Təbii qaz - m³",
		"Filiz faydalı qazıntıları: bütün növ metallar - ton"
	]

def get_field_name(group_name):
	"""Convert item group name to valid field name"""
	return group_name.replace(" ", "_").replace("-", "_").replace(":", "_").replace("/", "_").replace("³", "3").replace("ə", "e").replace("ü", "u").replace("ö", "o").replace("ı", "i").replace("ç", "c").replace("ş", "s").replace("ğ", "g").replace("(", "").replace(")", "")

def get_stock_data(filters):
	"""Get stock ledger data"""
	item_groups = get_predefined_item_groups()
	
	# Get stock ledger entries grouped by posting_date and item_group
	sle_table = frappe.qb.DocType("Stock Ledger Entry")
	item_table = frappe.qb.DocType("Item")
	item_group_table = frappe.qb.DocType("Item Group")
	
	query = (
		frappe.qb.from_(sle_table)
		.inner_join(item_table)
		.on(sle_table.item_code == item_table.name)
		.inner_join(item_group_table)
		.on(item_table.item_group == item_group_table.name)
		.select(
			sle_table.posting_date,
			item_group_table.name.as_("item_group"),
			Sum(sle_table.actual_qty).as_("total_qty")
		)
		.where(
			(item_group_table.name.isin(item_groups))
			& (sle_table.docstatus < 2)
			& (sle_table.is_cancelled == 0)
			& (sle_table.actual_qty > 0)  # Only positive quantities (IN)
		)
		.groupby(sle_table.posting_date, item_group_table.name)
		.orderby(sle_table.posting_date)
	)
	
	# Add date filter if provided
	if filters and filters.get("from_date"):
		query = query.where(sle_table.posting_date >= filters.from_date)
	if filters and filters.get("to_date"):
		query = query.where(sle_table.posting_date <= filters.to_date)
	
	return query.run(as_dict=True)

def get_sales_data(filters):
	"""Get sales invoice data for first 3 item groups"""
	sales_groups = get_sales_item_groups()
	
	# Get sales invoice data
	si_table = frappe.qb.DocType("Sales Invoice")
	si_item_table = frappe.qb.DocType("Sales Invoice Item")
	item_table = frappe.qb.DocType("Item")
	item_group_table = frappe.qb.DocType("Item Group")
	
	query = (
		frappe.qb.from_(si_table)
		.inner_join(si_item_table)
		.on(si_table.name == si_item_table.parent)
		.inner_join(item_table)
		.on(si_item_table.item_code == item_table.name)
		.inner_join(item_group_table)
		.on(item_table.item_group == item_group_table.name)
		.select(
			si_table.posting_date,
			item_group_table.name.as_("item_group"),
			Sum(si_item_table.amount).as_("total_amount")
		)
		.where(
			(item_group_table.name.isin(sales_groups))
			& (si_table.docstatus == 1)  # Only submitted invoices
			& (si_table.selling_price_list == "Topdan Satış")
		)
		.groupby(si_table.posting_date, item_group_table.name)
		.orderby(si_table.posting_date)
	)
	
	# Add date filter if provided
	if filters and filters.get("from_date"):
		query = query.where(si_table.posting_date >= filters.from_date)
	if filters and filters.get("to_date"):
		query = query.where(si_table.posting_date <= filters.to_date)
	
	return query.run(as_dict=True)

def get_data(filters):
	item_groups = get_predefined_item_groups()
	sales_groups = get_sales_item_groups()
	
	# Get stock data
	stock_raw_data = get_stock_data(filters)
	
	# Get sales data
	sales_raw_data = get_sales_data(filters)
	
	# Group stock data by date
	date_data = {}
	for row in stock_raw_data:
		date = row.posting_date
		if date not in date_data:
			date_data[date] = {}
		
		field_name = get_field_name(row.item_group)
		date_data[date][field_name] = flt(row.total_qty)
	
	# Group sales data by date
	for row in sales_raw_data:
		date = row.posting_date
		if date not in date_data:
			date_data[date] = {}
		
		field_name = get_field_name(row.item_group + "_sales")
		date_data[date][field_name] = flt(row.total_amount)
	
	# Convert to list format for the report
	result = []
	for date in sorted(date_data.keys()):
		row = {"date": date}
		
		# Add data for each stock item group column
		for group in item_groups:
			field_name = get_field_name(group)
			row[field_name] = date_data[date].get(field_name, 0)
		
		# Add data for each sales item group column
		for group in sales_groups:
			field_name = get_field_name(group + "_sales")
			row[field_name] = date_data[date].get(field_name, 0)
		
		# Only add row if it has any non-zero values (stock or sales)
		has_stock_data = any(row[get_field_name(group)] > 0 for group in item_groups)
		has_sales_data = any(row[get_field_name(group + "_sales")] > 0 for group in sales_groups)
		
		if has_stock_data or has_sales_data:
			result.append(row)
	
	return result