---

WITH report_data AS (
    SELECT
        row_number() OVER (ORDER BY oad.id) AS rn,
        im.id AS individual_id,
        im.insurance_number AS snils,
        c.id AS contract_id,
        c.number AS contract_number,
        c.date AS contract_date,
        f.name_in_pfr,

        concat(
            im.last_name, ' ',
            im.first_name, ' ',
            im.middle_name
        ) AS fio_m,

        concat(
            ip.last_name, ' ',
            ip.first_name, ' ',
            ip.middle_name
        ) AS fio_p,

        oad.number AS number,
        oad.date AS date,
        coalesce(oadp.payment_value, 0) AS payment_value,
        coalesce(b.balance, 0) AS balance,
        coalesce(bm.balance, 0) AS balance_rs,
        coalesce(dr.ops, 0) + coalesce(dr.ops_id, 0) AS rops,

        coalesce(ch.amount, 0)
            - coalesce(pay.amount, 0)
            + coalesce(ret.amount, 0) AS reserved,

        concat(
            a.last_name, ' ',
            substr(a.first_name, 1, 1), '. ',
            substr(a.middle_name, 1, 1) || '.'
        ) AS fio_a

    FROM back_office.ops_assignee_decision oad

    JOIN ourpension.ops_assignee_payment oap
        ON oap.id = oad.ops_assignee_payment_id

    JOIN ourpension.sharer sh
        ON sh.id = oap.sharer_id

    JOIN ourpension.contract c
        ON c.id = sh.contract_id

    LEFT JOIN ourpension.fund f
        ON f.id = c.fund_id

    JOIN ourpension.individual im
        ON im.id = sh.individual_id

    JOIN ourpension.individual ip
        ON ip.id = oap.assignee_id

    LEFT JOIN back_office.ops_assignee_decision_payment oadp
        ON oadp.ops_assignee_decision_id = oad.id

    JOIN ourpension.agent a
        ON a.id = oad.agent_id

    LEFT JOIN report.dia_rops dr
        ON dr.dia_register_id = :dia_register_id
       AND dr.insurance_number = im.insurance_number

    LEFT JOIN LATERAL (
        SELECT
            sum(b.total_credit) - sum(b.total_debit) AS balance
        FROM back_office.account acc
        JOIN back_office.balance b
            ON b.account_id = acc.id
        WHERE current_date BETWEEN b.start_date AND b.end_date
          AND acc.owner_sharer_id = sh.id
    ) b ON true

    LEFT JOIN LATERAL (
        SELECT
            sum(b.total_credit) - sum(b.total_debit) AS balance
        FROM back_office.account acc
        JOIN back_office.account_type act
            ON act.id = acc.account_type_id
        JOIN back_office.account_type_group g
            ON g.id = act.account_type_group_id
        JOIN back_office.balance b
            ON b.account_id = acc.id
        WHERE current_date BETWEEN b.start_date AND b.end_date
          AND g.mnemonics = 'Р(С)'
          AND acc.owner_sharer_id = sh.id
    ) bm ON true

    -- Начисления по решению
    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 = oad.id
          AND o.operation_type_id = 4
          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 = oad.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 = oad.id
          AND EXISTS (
              SELECT 1
              FROM back_office.operation o
              WHERE o.ops_payment_return_id = r.id
                AND o.operation_type_id = 17
                AND o.approved IS NOT NULL
                AND o.deleted IS NULL
                AND o.effective_date <= current_date
          )
    ) ret ON true

    WHERE :isShow
      AND oad.accepted IS NOT NULL
      -- Временное ограничение для проверки
      AND oad.id IN (6880, 439205, 444915)
)
SELECT
    r.rn AS rn,

    concat(
        '<a target="_blank" href="view?viewCode=Client_list&id=',
        r.individual_id, '">',
        r.snils, '</a>'
    ) AS snils,

    concat(
        '<a target="_blank" href="view?viewCode=7HPZX8vF&id=',
        r.contract_id, '">',
        r.contract_number, '</a>'
    ) AS contract_number,

    to_char(r.contract_date, 'DD.MM.YYYY') AS contract_date,
    r.name_in_pfr AS name_in_pfr,
    r.fio_m AS fio_m,
    r.fio_p AS fio_p,
    r.number AS number,
    to_char(r.date, 'DD.MM.YYYY') AS date,

    trim(to_char(
        r.payment_value,
        '99 999 999 999 999 990D99'
    )) AS payment_value,

    trim(to_char(
        r.payment_value,
        '99 999 999 999 999 990D99'
    )) AS payment_val,

    trim(to_char(
        r.balance,
        '99 999 999 999 999 990D99'
    )) AS balance,

    trim(to_char(
        r.balance_rs,
        '99 999 999 999 999 990D99'
    )) AS balance_rs,

    trim(to_char(
        r.rops,
        '99 999 999 999 999 990D99'
    )) AS rops,

    trim(to_char(
        r.reserved,
        '99 999 999 999 999 990D99'
    )) AS reserved,

    trim(to_char(
        r.balance + r.balance_rs + r.rops - r.payment_value,
        '99 999 999 999 999 990D99'
    )) AS total,

    r.fio_a AS fio_a

FROM report_data r