# Copyright (c) 2025, Jey Soft and contributors
# For license information, please see license.txt

from frappe.model.document import Document
import frappe
from frappe.model.document import Document

class TaxArticleItemsReport(Document):
	pass

@frappe.whitelist()
def get_items_by_tax_article(tax_article, from_date=None, to_date=None, company=None):
    """
    Get all Sales Invoice Items that have the specified tax_article
    """
    
    conditions = ["sii.tax_article = %s"]
    values = [tax_article]
    
    # Add date filters
    if from_date:
        conditions.append("si.posting_date >= %s")
        values.append(from_date)
        
    if to_date:
        conditions.append("si.posting_date <= %s")
        values.append(to_date)
        
    # Add company filter
    if company:
        conditions.append("si.company = %s")
        values.append(company)
    
    # Add docstatus condition (only submitted invoices)
    conditions.append("si.docstatus = 1")
    
    where_clause = " AND ".join(conditions)
    
    query = f"""
        SELECT 
            sii.parent,
            si.posting_date,
            si.customer,
            si.customer_name,
            sii.item_code,
            sii.item_name,
            sii.qty,
            sii.rate,
            sii.amount,
            sii.tax_article,
            si.company,
            si.currency
        FROM 
            `tabSales Invoice Item` sii
        INNER JOIN 
            `tabSales Invoice` si ON sii.parent = si.name
        WHERE 
            {where_clause}
        ORDER BY 
            si.posting_date DESC, sii.parent, sii.idx
    """
    
    try:
        results = frappe.db.sql(query, values, as_dict=True)
        return results
    except Exception as e:
        frappe.log_error(f"Error in get_items_by_tax_article: {str(e)}")
        frappe.throw(f"Error retrieving data: {str(e)}")

@frappe.whitelist()
def get_tax_article_summary(tax_article, from_date=None, to_date=None, company=None):
    """
    Get summary statistics for a tax article
    """
    
    conditions = ["sii.tax_article = %s"]
    values = [tax_article]
    
    if from_date:
        conditions.append("si.posting_date >= %s")
        values.append(from_date)
        
    if to_date:
        conditions.append("si.posting_date <= %s")
        values.append(to_date)
        
    if company:
        conditions.append("si.company = %s")
        values.append(company)
    
    conditions.append("si.docstatus = 1")
    
    where_clause = " AND ".join(conditions)
    
    query = f"""
        SELECT 
            COUNT(DISTINCT sii.parent) as total_invoices,
            COUNT(sii.name) as total_items,
            SUM(sii.qty) as total_qty,
            SUM(sii.amount) as total_amount,
            AVG(sii.rate) as avg_rate
        FROM 
            `tabSales Invoice Item` sii
        INNER JOIN 
            `tabSales Invoice` si ON sii.parent = si.name
        WHERE 
            {where_clause}
    """
    
    try:
        result = frappe.db.sql(query, values, as_dict=True)
        return result[0] if result else {}
    except Exception as e:
        frappe.log_error(f"Error in get_tax_article_summary: {str(e)}")
        return {}