SELECT CASE WHEN DATEDIFF(CURDATE(), inv.due_date) BETWEEN 0 AND 30 THEN '0-30 Hari' WHEN DATEDIFF(CURDATE(), inv.due_date) BETWEEN 31 AND 60 THEN '31-60 Hari' WHEN DATEDIFF(CURDATE(), inv.due_date) > 60 THEN '> 60 Hari' ELSE 'Belum Jatuh Tempo' END AS aging_bucket, SUM(ii.item_value) AS total_tunggakan, COUNT(DISTINCT inv.id) AS jumlah_invoice FROM shieldtech_finance.invoices inv JOIN shieldtech_finance.invoice_items ii ON inv.id = ii.invoice_id GROUP BY aging_bucket ORDER BY MIN(DATEDIFF(CURDATE(), inv.due_date));

SELECT ii.item_label, SUM(ii.item_value) AS total_revenue FROM shieldtech_finance.invoices inv JOIN shieldtech_finance.invoice_items ii ON inv.id = ii.invoice_id WHERE inv.status_id != 0 AND YEAR(inv.updated_at) = YEAR(CURDATE()) AND MONTH(inv.updated_at) = MONTH(CURDATE()) GROUP BY ii.item_label ORDER BY total_revenue DESC;

SELECT CASE WHEN DATEDIFF(inv.due_date, CURDATE()) BETWEEN 0 AND 30 THEN '0-30 Hari ke Depan' WHEN DATEDIFF(inv.due_date, CURDATE()) BETWEEN 31 AND 60 THEN '31-60 Hari ke Depan' WHEN DATEDIFF(inv.due_date, CURDATE()) BETWEEN 61 AND 90 THEN '61-90 Hari ke Depan' END AS forecast_bucket, SUM(ii.item_value) AS projected_income FROM shieldtech_finance.invoices inv JOIN shieldtech_finance.invoice_items ii ON inv.id = ii.invoice_id WHERE inv.due_date >= CURDATE() GROUP BY forecast_bucket ORDER BY MIN(DATEDIFF(inv.due_date, CURDATE()));