CREATE DEFINER=CURRENT_USER PROCEDURE `profit_loss_report`(IN `start_date_filter` DATE, IN `end_date_filter` DATE)
BEGIN
SELECT 
    label AS date,
    Format(SUM(income), 0) AS income,
    Format(SUM(expense), 2) AS expense,
    format((SUM(income) - SUM(expense)), 2) AS revenue
FROM (
    
    
    SELECT 
        DATE(scm_po_payments.created_at) AS label,
        0 AS income,
        SUM(scm_po_payments.payment_amount) AS expense
    FROM scm_po_payments
    WHERE 
    date(scm_po_payments.created_at) between start_date_filter and end_date_filter
    GROUP BY DATE(scm_po_payments.created_at)

    UNION ALL

    SELECT 
        DATE(expenses.date) AS label,
        0 AS income,
        SUM(expenses.amount) AS expense
    FROM expenses
    WHERE 
        date(expenses.date) between start_date_filter and end_date_filter
    GROUP BY DATE(expenses.date)

    UNION ALL

    
    SELECT 
        DATE(payments.created_at) AS label,
        SUM(payments.payment) AS income,
        0 AS expense
    FROM payments
    WHERE 
       payments.status <> 'Refund' and
        date(payments.created_at) between start_date_filter and end_date_filter
    
    GROUP BY DATE(payments.created_at)

    UNION ALL

    SELECT 
        DATE(phar_pos.created_at) AS label,
        SUM(phar_pos_payments.amount) AS income,
        0 AS expense
    FROM phar_pos
    JOIN phar_pos_payments 
        ON phar_pos.id = phar_pos_payments.pos_id
    WHERE 
        date(phar_pos.created_at)  between start_date_filter and end_date_filter AND IFNULL(phar_pos.is_deleted, 0) = 0
    GROUP BY DATE(phar_pos.created_at)

) AS daily_data

GROUP BY label
ORDER BY label;
END
