import frappe
from frappe import _

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

def get_columns():
    """Defines the columns for the report."""
    return [
        {
            "label": _("Sales Invoice"),
            "fieldname": "sales_invoice",
            "fieldtype": "Link",
            "options": "Sales Invoice",
            "width": 200
        },
        {
            "label": _("Posting Date"),
            "fieldname": "posting_date",
            "fieldtype": "Date",
            "width": 120
        },
        {
            "label": _("Grand Total"),
            "fieldname": "grand_total",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        },
        {
            "label": _("Amount up to 500"),
            "fieldname": "amount_up_to_500",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 180
        },
        {
            "label": _("Amount over 500"),
            "fieldname": "amount_over_500",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 180
        },
        {
            "label": _("Currency"),
            "fieldname": "currency",
            "fieldtype": "Link",
            "options": "Currency",
            "hidden": 1
        }
    ]

def get_data(filters):
    """Fetches and processes the report data."""
    conditions = get_conditions(filters)
    
    # The item groups to filter by
    target_item_groups = ["İdman və mərc oyunları", "Lotoreya oyunları"]

    # Fetching the data using Frappe's Query Builder
    si = frappe.qb.DocType("Sales Invoice")
    sii = frappe.qb.DocType("Sales Invoice Item")
    item = frappe.qb.DocType("Item")

    query = (
        frappe.qb.from_(si)
        .inner_join(sii)
        .on(si.name == sii.parent)
        .inner_join(item)
        .on(sii.item_code == item.name)
        .select(
            si.name.as_("sales_invoice"),
            si.posting_date,
            si.grand_total,
            si.currency,
            si.won,
            sii.amount
        )
        .where(
            (si.docstatus == 1) &
            (item.item_group.isin(target_item_groups)) &
            (si.company == filters.get("company")) &
            (si.posting_date.between(filters.get("from_date"), filters.get("to_date")))
        )
    )
    
    # Execute the query
    invoice_items = query.run(as_dict=True)

    # Process the data to aggregate amounts per invoice
    processed_data = {}
    for item in invoice_items:
        doc_name = item.sales_invoice
        if doc_name not in processed_data:
            processed_data[doc_name] = {
                "sales_invoice": doc_name,
                "posting_date": item.posting_date,
                "grand_total": item.grand_total,
                "currency": item.currency,
                "amount_up_to_500": 0,
                "amount_over_500": 0,
            }

        # Distribute the item amount into the correct bucket ONLY if won = 1
        # Grand total already contains all amounts (won + lost)
        if item.won == 1:
            if item.amount <= 500:
                processed_data[doc_name]["amount_up_to_500"] += item.amount
            else:
                processed_data[doc_name]["amount_over_500"] += item.amount
            
    # Return the processed data as a list of dictionaries
    return list(processed_data.values())

def get_conditions(filters):
    """Builds SQL conditions from filters."""
    conditions = ""
    if filters.get("company"):
        conditions += f" AND si.company = '{filters.get('company')}'"
    if filters.get("from_date"):
        conditions += f" AND si.posting_date >= '{filters.get('from_date')}'"
    if filters.get("to_date"):
        conditions += f" AND si.posting_date <= '{filters.get('to_date')}'"
    return conditions

