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

import frappe
from frappe import _

# Cədvəl 23 — "ƏDV - Sertifikat".
#
# One row per (item certificate). Certificates are stored on the Sales Invoice
# parent in the `custom_item_certificates` table (doctype "Sales Invoice
# Certificate"), each row linked to its item row by `parent_row` ==
# `Sales Invoice Item.custom_si_row_key`. If one item carries 3 certificates we
# emit 3 rows, each with the item's FULL amount — the value is intentionally not
# split across certificates.


def execute(filters=None):
    filters = filters or {}
    return get_columns(), get_data(filters)


def get_columns():
    return [
        {
            "fieldname": "sales_invoice",
            "label": _("Sales Invoice"),
            "fieldtype": "Link",
            "options": "Sales Invoice",
            "width": 160,
        },
        {
            "fieldname": "posting_date",
            "label": _("Date"),
            "fieldtype": "Date",
            "width": 100,
        },
        {
            "fieldname": "cert_no",
            "label": _("Sertifikatın seriya / nömrəsi"),
            "fieldtype": "Data",
            "width": 200,
        },
        {
            "fieldname": "voen",
            "label": _("Sertifikat təqdim olunan şəxsin Vöeni"),
            "fieldtype": "Data",
            "width": 170,
        },
        {
            "fieldname": "taxpayer_full_name",
            "label": _("Sertifikat təqdim olunan şəxsin adı"),
            "fieldtype": "Data",
            "width": 280,
        },
        {
            "fieldname": "value",
            "label": _("Sertifikat altında təqdim olunan malların (işlərin, xidmətlərin) dəyəri"),
            "fieldtype": "Currency",
            "width": 230,
        },
    ]


def get_data(filters):
    conditions = ""
    if filters.get("company"):
        conditions += " AND si.company = %(company)s"
    if filters.get("from_date"):
        conditions += " AND si.posting_date >= %(from_date)s"
    if filters.get("to_date"):
        conditions += " AND si.posting_date <= %(to_date)s"

    return frappe.db.sql(
        """
        SELECT
            sic.parent              AS sales_invoice,
            si.posting_date         AS posting_date,
            epc.cert_no             AS cert_no,
            epc.voen                AS voen,
            epc.taxpayer_full_name  AS taxpayer_full_name,
            sii.amount              AS value
        FROM `tabSales Invoice Certificate` sic
        INNER JOIN `tabSales Invoice` si
            ON si.name = sic.parent AND si.docstatus = 1
        INNER JOIN `tabSales Invoice Item` sii
            ON sii.parent = sic.parent
           AND sii.custom_si_row_key = sic.parent_row
        LEFT JOIN `tabE-Taxes Presented Certificate` epc
            ON epc.name = sic.certificate
        WHERE sic.parenttype = 'Sales Invoice'
          AND sic.certificate IS NOT NULL AND sic.certificate != ''
          {conditions}
        ORDER BY si.posting_date DESC, sic.parent, epc.cert_no
        """.format(conditions=conditions),
        filters,
        as_dict=True,
    )
