WITH FilteredSales AS (
    -- Get only the sales orders matching the date filter
    SELECT 
        so.customer,
        so.transaction_date,
        so.name AS sales_order_no,
        soi.item_code,
        soi.qty,
        IFNULL(soi.delivered_qty, 0) AS delivered_qty,
        (soi.qty - IFNULL(soi.delivered_qty, 0)) AS pending_qty
    FROM `tabSales Order` so
    JOIN `tabSales Order Item` soi ON so.name = soi.parent
    WHERE 
        so.company = 'Cotton Craft Pvt Ltd'
        AND so.docstatus != 2  
        AND (soi.qty - IFNULL(soi.delivered_qty, 0)) > 0  
        AND (%(from_date)s IS NULL OR so.transaction_date >= %(from_date)s)
        AND (%(to_date)s IS NULL OR so.transaction_date <= %(to_date)s)
),
CustomersWithOrders AS (
    -- Get distinct customers who have sales orders in the filtered dataset
    SELECT DISTINCT customer FROM FilteredSales
)

SELECT 
    `Customer Name`, 
    `Sales Order Date`, 
    `Sales Order No`, 
    `Item Code`, 
    FORMAT(`Qty`, 2) AS `Qty`, 
    FORMAT(`Delivered Qty`, 2) AS `Delivered Qty`, 
    FORMAT(`Pending Qty`, 2) AS `Pending Qty`
FROM (
    -- Insert exactly one blank row per customer in the filtered dataset
    SELECT 
        '' AS `Customer Name`, 
        NULL AS `Sales Order Date`, 
        NULL AS `Sales Order No`, 
        NULL AS `Item Code`,
        NULL AS `Qty`, 
        NULL AS `Delivered Qty`, 
        NULL AS `Pending Qty`,
        -1 AS sort_order, 
        c.customer AS `Group Identifier`
    FROM CustomersWithOrders c

    UNION ALL

    -- Main sales order data
    SELECT 
        f.customer AS `Customer Name`, 
        f.transaction_date AS `Sales Order Date`, 
        f.sales_order_no AS `Sales Order No`, 
        f.item_code AS `Item Code`,
        f.qty AS `Qty`, 
        f.delivered_qty AS `Delivered Qty`, 
        f.pending_qty AS `Pending Qty`,
        0 AS sort_order,
        f.customer AS `Group Identifier`
    FROM FilteredSales f
) grouped_data
ORDER BY 
    `Group Identifier`, sort_order, `Sales Order Date`, `Sales Order No`, `Item Code`;