SELECT 
t.id
, a.id AS account_id
, a.name AS account_name
, aty.type  
, t.amount
, tj.description AS tj_descr
, tj.date
, c.name AS category_name
FROM firefly.transactions AS t
INNER JOIN firefly.transaction_journals AS tj
ON t.transaction_journal_id = tj.id
LEFT JOIN firefly.category_transaction_journal as ctj
ON tj.id = ctj.transaction_journal_id
LEFT JOIN firefly.categories AS c
ON c.id = ctj.category_id
LEFT JOIN firefly.accounts AS a
ON t.account_id = a.id
LEFT JOIN firefly.account_types AS aty
ON a.account_type_id = aty.id 
WHERE TRUE
AND tj.DATE BETWEEN '2025-11-28' AND '2025-11-28'
AND a.id = 1
;


/*
Total Expanses of the month
*/
SELECT 
SUM(t.amount)
FROM firefly.transactions AS t
INNER JOIN firefly.transaction_journals AS tj
ON t.transaction_journal_id = tj.id
LEFT JOIN firefly.category_transaction_journal as ctj
ON tj.id = ctj.transaction_journal_id
LEFT JOIN firefly.categories AS c
ON c.id = ctj.category_id
LEFT JOIN firefly.accounts AS a
ON t.account_id = a.id
LEFT JOIN firefly.account_types AS aty
ON a.account_type_id = aty.id 
WHERE TRUE
AND tj.DATE BETWEEN ${__from:date:iso} AND ${__to:date:iso}
AND a.id = 1
;



/*
Barchart with Budget
*/
SELECT 
c.name AS category_name
, 500 AS budget
, ABS(SUM(t.amount)) AS current_status
FROM firefly.transactions AS t
INNER JOIN firefly.transaction_journals AS tj
ON t.transaction_journal_id = tj.id
LEFT JOIN firefly.category_transaction_journal as ctj
ON tj.id = ctj.transaction_journal_id
LEFT JOIN firefly.categories AS c
ON c.id = ctj.category_id
LEFT JOIN firefly.accounts AS a
ON t.account_id = a.id
LEFT JOIN firefly.account_types AS aty
ON a.account_type_id = aty.id 
WHERE TRUE
AND tj.DATE BETWEEN "${__from:date:iso}" AND "${__to:date:iso}"
AND a.id = 1
GROUP BY 1,2
ORDER BY 3 ASC
LIMIT 10
;


/*
Barchart with Budget difference stacked
*/
WITH calc_budget AS (
SELECT 
c.name AS category_name
, ABS(SUM(t.amount)) AS current_status
, CASE 
    WHEN c.name = 'Lebensmittel'              THEN ${budget_lebensmittel}      
    WHEN c.name = 'Unterhaltung: Ausgehen'    THEN ${budget_unterhaltung_ausgehen}              
    WHEN c.name = 'Sonstiges'                 THEN ${budget_sonstiges}  
    WHEN c.name = 'Online Einkäufe'           THEN ${budget_online_einkaeufe}          
    WHEN c.name = 'Wohnen: Möbel'             THEN ${budget_wohnen_moebel}      
    WHEN c.name = 'Drogerie'                  THEN ${budget_drogerie}  
    ELSE 0
  END AS budget
FROM firefly.transactions AS t
INNER JOIN firefly.transaction_journals AS tj
ON t.transaction_journal_id = tj.id
LEFT JOIN firefly.category_transaction_journal as ctj
ON tj.id = ctj.transaction_journal_id
LEFT JOIN firefly.categories AS c
ON c.id = ctj.category_id
LEFT JOIN firefly.accounts AS a
ON t.account_id = a.id
LEFT JOIN firefly.account_types AS aty
ON a.account_type_id = aty.id 
WHERE TRUE
AND tj.DATE BETWEEN "${__from:date:iso}" AND "${__to:date:iso}"
AND a.id = 1
AND c.name != "Monatliche Einzahlung"
GROUP BY 1,3
ORDER BY 2 DESC
)
SELECT 
  category_name
, current_status
, CASE 
    WHEN budget - current_status > 0 THEN budget - current_status
    ELSE 0
  END AS rest_budget
FROM calc_budget
WHERE TRUE
    AND budget > 0
;