---

WITH target_account AS MATERIALIZED (
    SELECT DISTINCT t.credit_account_id AS account_id
    FROM back_office.transaction t
    WHERE t.operation_id = 84319207
      AND t.id = 557461760
),
movements AS (
    SELECT
        t.operation_id,
        t.id AS transaction_id,
        t.debit_account_id,
        t.credit_account_id,
        t.value,
        t.value AS reserve_change
    FROM target_account a
    JOIN back_office.transaction t
      ON t.credit_account_id = a.account_id

    UNION ALL

    SELECT
        t.operation_id,
        t.id AS transaction_id,
        t.debit_account_id,
        t.credit_account_id,
        t.value,
        -t.value AS reserve_change
    FROM target_account a
    JOIN back_office.transaction t
      ON t.debit_account_id = a.account_id
)
SELECT
    o.id AS operation_id,
    ot.code AS operation_type_code,
    o.ops_assignee_decision_id AS decision_id,
    o.effective_date,
    dat.mnemonics AS debit_account_type,
    cat.mnemonics AS credit_account_type,
    m.value,
    m.reserve_change
FROM movements m
JOIN back_office.operation o
  ON o.id = m.operation_id
LEFT JOIN back_office.operation_type ot
  ON ot.id = o.operation_type_id
LEFT JOIN back_office.account da
  ON da.id = m.debit_account_id
LEFT JOIN back_office.account_type dat
  ON dat.id = da.account_type_id
LEFT JOIN back_office.account ca
  ON ca.id = m.credit_account_id
LEFT JOIN back_office.account_type cat
  ON cat.id = ca.account_type_id
WHERE o.approved IS NOT NULL
  AND o.deleted IS NULL
ORDER BY o.effective_date, o.id, m.transaction_id;