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

import frappe
from frappe import _


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


def get_columns():
    return [
        {
            "label": _("Purchase Invoice"),
            "fieldname": "invoice",
            "fieldtype": "Link",
            "options": "Purchase Invoice",
            "width": 150
        },
        {
            "label": _("Date"),
            "fieldname": "posting_date",
            "fieldtype": "Date",
            "width": 100
        },
        {
            "label": _("Supplier"),
            "fieldname": "supplier",
            "fieldtype": "Link",
            "options": "Supplier",
            "width": 150
        },
        {
            "label": _("Aİ-92"),
            "fieldname": "ai_92",
            "fieldtype": "Float",
            "width": 80
        },
        {
            "label": _("Aİ-95"),
            "fieldname": "ai_95",
            "fieldtype": "Float",
            "width": 80
        },
        {
            "label": _("Aİ-80"),
            "fieldname": "ai_80",
            "fieldtype": "Float",
            "width": 80
        },
        {
            "label": _("Dizel yanacağı"),
            "fieldname": "dizel",
            "fieldtype": "Float",
            "width": 100
        },
        {
            "label": _("Maye qaz"),
            "fieldname": "maye_qaz",
            "fieldtype": "Float",
            "width": 80
        },
        {
            "label": _("Total Qty"),
            "fieldname": "total_qty",
            "fieldtype": "Float",
            "width": 100
        }
    ]


def get_data(filters):
    # Хардкод фильтр на топливные продукты
    fuel_items = [
        "Avtomobil benzini (Aİ-92)",
        "Avtomobil benzini (Aİ-95)", 
        "Avtomobil benzini (Aİ-80)",
        "Dizel yanacağı",
        "Maye qaz"
    ]
    
    conditions = []
    values = []
    
    # Фильтр по датам
    if filters.get("from_date"):
        conditions.append("pi.posting_date >= %s")
        values.append(filters.get("from_date"))
        
    if filters.get("to_date"):
        conditions.append("pi.posting_date <= %s")
        values.append(filters.get("to_date"))
    
    # Хардкод фильтр по товарам
    item_condition = "pii.item_name IN ({})".format(", ".join(["%s"] * len(fuel_items)))
    conditions.append(item_condition)
    values.extend(fuel_items)
    
    # Только подтвержденные инвойсы
    conditions.append("pi.docstatus = 1")
    
    where_clause = ""
    if conditions:
        where_clause = "WHERE " + " AND ".join(conditions)
    
    # Получаем все данные
    query = """
        SELECT 
            pi.name as invoice,
            pi.posting_date,
            pi.supplier,
            pii.item_name,
            pii.qty
        FROM 
            `tabPurchase Invoice Item` pii
        INNER JOIN 
            `tabPurchase Invoice` pi ON pii.parent = pi.name
        {where_clause}
        ORDER BY 
            pi.posting_date DESC, pi.name
    """.format(where_clause=where_clause)
    
    raw_data = frappe.db.sql(query, values, as_dict=True)
    
    # Группируем данные по документам
    invoice_data = {}
    
    for row in raw_data:
        invoice = row['invoice']
        
        if invoice not in invoice_data:
            invoice_data[invoice] = {
                'invoice': invoice,
                'posting_date': row['posting_date'],
                'supplier': row['supplier'],
                'ai_92': 0,
                'ai_95': 0,
                'ai_80': 0,
                'dizel': 0,
                'maye_qaz': 0,
                'total_qty': 0
            }
        
        # Распределяем количество по колонкам
        item_name = row['item_name']
        qty = row['qty'] or 0
        
        if 'Aİ-92' in item_name:
            invoice_data[invoice]['ai_92'] += qty
        elif 'Aİ-95' in item_name:
            invoice_data[invoice]['ai_95'] += qty
        elif 'Aİ-80' in item_name:
            invoice_data[invoice]['ai_80'] += qty
        elif 'Dizel' in item_name:
            invoice_data[invoice]['dizel'] += qty
        elif 'Maye qaz' in item_name:
            invoice_data[invoice]['maye_qaz'] += qty
        
        invoice_data[invoice]['total_qty'] += qty
    
    # Конвертируем в список
    data = list(invoice_data.values())
    
    return data


def get_filters():
    return [
        {
            "fieldname": "from_date",
            "label": _("From Date"),
            "fieldtype": "Date",
            "default": frappe.utils.add_months(frappe.utils.today(), -1),
            "reqd": 1
        },
        {
            "fieldname": "to_date", 
            "label": _("To Date"),
            "fieldtype": "Date",
            "default": frappe.utils.today(),
            "reqd": 1
        }
    ]