---

WITH target_decisions AS (
    SELECT id, number
    FROM back_office.ops_assignee_decision
    WHERE id IN (439205, 444915)
),
amounts AS (
    SELECT
        d.id AS decision_id,
        d.number AS decision_number,
        coalesce(ch.amount, 0) AS charged,
        coalesce(pay.amount, 0) AS paid,
        coalesce(ret.amount, 0) AS returned
    FROM target_decisions d

    LEFT JOIN LATERAL (
        SELECT sum(t.value) AS amount
        FROM back_office.operation o
        JOIN back_office.transaction t
            ON t.operation_id = o.id
        JOIN back_office.account ca
            ON ca.id = t.credit_account_id
        WHERE o.ops_assignee_decision_id = d.id
          AND o.operation_type_id = 4 -- CHARGE_FROM_RESERVE
          AND ca.account_type_id = 62 -- НВ ПП ОПС
          AND o.approved IS NOT NULL
          AND o.deleted IS NULL
          AND o.effective_date <= current_date
    ) ch ON true

    LEFT JOIN LATERAL (
        SELECT sum(pr.value) AS amount
        FROM back_office.ops_payment_register_decision pr
        WHERE pr.ops_assignee_decision_id = d.id
          AND EXISTS (
              SELECT 1
              FROM back_office.operation o
              JOIN back_office.operation_type ot
                  ON ot.id = o.operation_type_id
              WHERE o.ops_payment_register_id =
                    pr.ops_payment_register_id
                AND ot.code IN (
                    'PAYMENT_PP',
                    'ADDITIONAL_PAYMENT_PP'
                )
                AND o.approved IS NOT NULL
                AND o.deleted IS NULL
                AND o.effective_date <= current_date
          )
    ) pay ON true

    LEFT JOIN LATERAL (
        SELECT sum(r.value) AS amount
        FROM back_office.ops_payment_return r
        JOIN back_office.ops_payment_register_decision pr
            ON pr.id = r.ops_payment_register_decision_id
        WHERE pr.ops_assignee_decision_id = d.id
          AND EXISTS (
              SELECT 1
              FROM back_office.operation o
              WHERE o.ops_payment_return_id = r.id
                AND o.operation_type_id = 17 -- RETURN
                AND o.approved IS NOT NULL
                AND o.deleted IS NULL
                AND o.effective_date <= current_date
          )
    ) ret ON true
)
SELECT
    decision_id,
    decision_number,
    charged,
    paid,
    returned,
    charged - paid + returned AS reserved_test
FROM amounts
ORDER BY decision_id;