---

with movements as (
    select
        a.account_type_id,
        t.value as credit_value,
        cast(0 as numeric) as debit_value
    from back_office.account a
    join back_office.transaction t
        on t.credit_account_id = a.id
    join back_office.operation o
        on o.id = t.operation_id
    where a.owner_sharer_id = 14280820
        and o.approved is not null
        and o.deleted is null
        and t.effective_date < cast('2026-10-20' as date)

    union all

    select
        a.account_type_id,
        cast(0 as numeric),
        t.value
    from back_office.account a
    join back_office.transaction t
        on t.debit_account_id = a.id
    join back_office.operation o
        on o.id = t.operation_id
    where a.owner_sharer_id = 14280820
        and o.approved is not null
        and o.deleted is null
        and t.effective_date < cast('2026-10-20' as date)
)
select
    atg.mnemonics as account_group,
    at.mnemonics as account_type,
    sum(m.credit_value) as credit_total,
    sum(m.debit_value) as debit_total,
    sum(m.credit_value) - sum(m.debit_value) as difference
from movements m
join back_office.account_type at
    on at.id = m.account_type_id
left join back_office.account_type_group atg
    on atg.id = at.account_type_group_id
group by atg.mnemonics, at.mnemonics
order by atg.mnemonics, at.mnemonics;