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

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


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


def get_data(filters):
	"""Получаем данные по Sales Invoice для игорного бизнеса"""
	
	# Получаем Sales Invoice с учетом фильтров
	sales_invoices = get_sales_invoices(filters)
	
	if not sales_invoices:
		return []
	
	# Получаем информацию о компаниях для определения типа игры
	companies_activity = get_companies_main_activity()
	
	# Формируем итоговые данные
	data = []
	
	for invoice in sales_invoices:
		row = frappe._dict()
		
		# Основные данные
		row.sales_invoice = invoice['name']
		row.posting_date = invoice['posting_date']
		row.company = invoice['company']
		row.grand_total = flt(invoice['grand_total'])
		
		# Определяем тип игры по main_activity компании
		company_activity = companies_activity.get(invoice['company'], '')
		game_type = determine_game_type(company_activity)
		
		# Инициализируем все колонки нулями
		row.sports_organizer = 0
		row.sports_seller = 0
		row.lottery_organizer = 0
		row.lottery_seller = 0
		
		# Распределяем сумму в зависимости от типа игры и роли
		if game_type == 'sports':
			if invoice.get('organizer'):
				row.sports_organizer = row.grand_total
			elif invoice.get('seller'):
				row.sports_seller = row.grand_total
		elif game_type == 'lottery':
			if invoice.get('organizer'):
				row.lottery_organizer = row.grand_total
			elif invoice.get('seller'):
				row.lottery_seller = row.grand_total
		
		data.append(row)
	
	return data


def get_sales_invoices(filters):
	"""Получает Sales Invoice с учетом фильтров"""
	
	conditions = []
	values = []
	
	# Фильтр по компании
	if filters.get("company"):
		conditions.append("si.company = %s")
		values.append(filters.get("company"))
	
	# Фильтр по датам
	if filters.get("from_date"):
		conditions.append("si.posting_date >= %s")
		values.append(filters.get("from_date"))
	
	if filters.get("to_date"):
		conditions.append("si.posting_date <= %s")
		values.append(filters.get("to_date"))
	
	# Только подтвержденные документы
	conditions.append("si.docstatus = 1")
	
	# Только документы с отмеченными галочками organizer или seller
	conditions.append("(si.organizer = 1 OR si.seller = 1)")
	
	where_clause = ""
	if conditions:
		where_clause = "WHERE " + " AND ".join(conditions)
	
	query = f"""
		SELECT 
			si.name,
			si.posting_date,
			si.company,
			si.grand_total,
			si.organizer,
			si.seller
		FROM 
			`tabSales Invoice` si
		{where_clause}
		ORDER BY 
			si.posting_date DESC, si.name
	"""
	
	return frappe.db.sql(query, values, as_dict=True)


def get_companies_main_activity():
	"""Получает main_activity для всех компаний"""
	
	companies = frappe.db.sql("""
		SELECT name, main_activity
		FROM `tabCompany`
		WHERE main_activity IS NOT NULL
	""", as_dict=True)
	
	return {company['name']: company['main_activity'] for company in companies}


def determine_game_type(main_activity):
	"""Определяет тип игры на основе main_activity"""
	
	if not main_activity:
		return 'unknown'
	
	# Коды для спортивных игр
	sports_codes = [
		'92000',
		'9200003',
		'9200004',
		'9200002',
		'9200005'
	]
	
	# Код для лотерейных игр
	lottery_codes = [
		'9200001'
	]
	
	# Проверяем на соответствие кодам
	for code in sports_codes:
		if code in main_activity:
			return 'sports'
	
	for code in lottery_codes:
		if code in main_activity:
			return 'lottery'
	
	return 'unknown'


def get_columns():
	"""Создает колонки отчета"""
	return [
		{
			"label": "Sales Invoice",
			"fieldname": "sales_invoice",
			"fieldtype": "Link",
			"options": "Sales Invoice",
			"width": 150
		},
		{
			"label": "Tarix",
			"fieldname": "posting_date",
			"fieldtype": "Date",
			"width": 100
		},
		{
			"label": "İdman oyunları - Təşkilatçı",
			"fieldname": "sports_organizer",
			"fieldtype": "Currency",
			"width": 160
		},
		{
			"label": "İdman oyunları - Satıcı",
			"fieldname": "sports_seller",
			"fieldtype": "Currency",
			"width": 160
		},
		{
			"label": "Lotereya oyunları - Təşkilatçı",
			"fieldname": "lottery_organizer",
			"fieldtype": "Currency",
			"width": 170
		},
		{
			"label": "Lotereya oyunları - Satıcı",
			"fieldname": "lottery_seller",
			"fieldtype": "Currency",
			"width": 170
		}
	]