with
    int_xero__bank_transactions as (
        select * from {{ ref("int_xero__bank_transactions") }}
    ),
    stg_xero__accounts as (select * from {{ ref("stg_xero__accounts") }})

select
    bt.id_bank_transaction,
    j.id_line_item,
    j.line_description,
    j.id_account,
    j.account_code,
    a.account_name,
    j.item_code,
    j.quantity::numeric,
    j.unit_amount::numeric,
    bt.transaction_sign * j.line_amount_local_curr::numeric as line_amount_local_curr,
    (
        bt.transaction_sign
        * j.line_amount_local_curr::numeric
        * bt.exchange_rate_to_gbp
    )::numeric(18, 4) as line_amount_in_gbp,
    (
        case
            when bt.line_amount_tax_inclusiveness = 'Inclusive'
            then j.line_amount_local_curr::numeric - j.tax_amount_local_curr::numeric
            when bt.line_amount_tax_inclusiveness = 'Exclusive'
            then j.line_amount_local_curr::numeric
            when bt.line_amount_tax_inclusiveness = 'NoTax'
            then j.line_amount_local_curr::numeric
            else null
        end
    )
    * bt.transaction_sign as line_amount_wo_taxes_local_curr,
    (
        case
            when bt.line_amount_tax_inclusiveness = 'Inclusive'
            then
                (
                    (
                        j.line_amount_local_curr::numeric
                        - j.tax_amount_local_curr::numeric
                    )
                    * bt.exchange_rate_to_gbp
                )::numeric(18, 4)
            when bt.line_amount_tax_inclusiveness = 'Exclusive'
            then
                (j.line_amount_local_curr::numeric * bt.exchange_rate_to_gbp)::numeric(
                    18, 4
                )
            when bt.line_amount_tax_inclusiveness = 'NoTax'
            then
                (j.line_amount_local_curr::numeric * bt.exchange_rate_to_gbp)::numeric(
                    18, 4
                )
            else null
        end
    )
    * bt.transaction_sign as line_amount_wo_taxes_in_gbp,
    bt.transaction_sign * j.tax_amount_local_curr::numeric as tax_amount_local_curr,
    (
        bt.transaction_sign * j.tax_amount_local_curr::numeric * bt.exchange_rate_to_gbp
    )::numeric(18, 4) as tax_amount_in_gbp,
    j.tax_type,
    bt.transaction_currency_iso_4217
from int_xero__bank_transactions bt
cross join
    lateral(
        select
            (jsonb_array_elements(bt.line_items) ->> 'LineItemID') as id_line_item,
            (jsonb_array_elements(bt.line_items) ->> 'AccountID') as id_account,

            (jsonb_array_elements(bt.line_items) ->> 'Quantity') as quantity,
            (jsonb_array_elements(bt.line_items) ->> 'UnitAmount') as unit_amount,
            (
                jsonb_array_elements(bt.line_items) ->> 'LineAmount'
            ) as line_amount_local_curr,
            (
                jsonb_array_elements(bt.line_items) ->> 'TaxAmount'
            ) as tax_amount_local_curr,
            (jsonb_array_elements(bt.line_items) ->> 'TaxType') as tax_type,
            (jsonb_array_elements(bt.line_items) ->> 'Description') as line_description,

            (jsonb_array_elements(bt.line_items) ->> 'AccountCode') as account_code,
            (jsonb_array_elements(bt.line_items) ->> 'ItemCode') as item_code
    ) j
left join stg_xero__accounts a on j.account_code = a.account_code