---

with target_accounts as (
    select id
    from back_office.account
    where owner_sharer_id = 14275376
),
target_transactions as materialized (
    select
        t.id,
        t.operation_id,
        t.effective_date,
        t.value,
        t.debit_account_id,
        t.credit_account_id
    from target_accounts a
    join back_office.transaction t
        on t.debit_account_id = a.id
    where t.effective_date in (
        date '2020-03-30',
        date '2023-05-31'
    )

    union

    select
        t.id,
        t.operation_id,
        t.effective_date,
        t.value,
        t.debit_account_id,
        t.credit_account_id
    from target_accounts a
    join back_office.transaction t
        on t.credit_account_id = a.id
    where t.effective_date in (
        date '2020-03-30',
        date '2023-05-31'
    )
)
select
    t.id as transaction_id,
    o.id as operation_id,
    t.effective_date as transaction_date,
    o.effective_date as operation_date,
    t.value,
    ot.code as operation_code,
    ot.short_name as operation_name,
    o.approved,
    o.deleted,

    dat.mnemonics as debit_account_type,
    datg.mnemonics as debit_account_group,
    da.owner_sharer_id as debit_sharer_id,

    cat.mnemonics as credit_account_type,
    catg.mnemonics as credit_account_group,
    ca.owner_sharer_id as credit_sharer_id

from target_transactions t
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id

left join back_office.account da on da.id = t.debit_account_id
left join back_office.account_type dat on dat.id = da.account_type_id
left join back_office.account_type_group datg
    on datg.id = dat.account_type_group_id

left join back_office.account ca on ca.id = t.credit_account_id
left join back_office.account_type cat on cat.id = ca.account_type_id
left join back_office.account_type_group catg
    on catg.id = cat.account_type_group_id

order by t.effective_date, o.id, t.id;