---

WITH report_data AS (
    SELECT
        oad.id AS decision_id,
        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,

        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

    WHERE :isShow
      AND oad.accepted IS NOT NULL
),

report_result AS (
    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(
            coalesce((
                SELECT sum(t.value)
                FROM back_office.operation o

                CROSS JOIN LATERAL (
                    SELECT
                        tr.credit_account_id,
                        tr.value
                    FROM back_office.transaction tr
                    WHERE tr.operation_id = o.id
                    OFFSET 0
                ) t

                JOIN back_office.account ca
                    ON ca.id = t.credit_account_id

                WHERE o.ops_assignee_decision_id = r.decision_id
                  AND o.operation_type_id = (
                      SELECT id
                      FROM back_office.operation_type
                      WHERE code = 'CHARGE_FROM_RESERVE'
                  )
                  AND ca.account_type_id = (
                      SELECT id
                      FROM back_office.account_type
                      WHERE mnemonics = 'НВ ПП ОПС'
                  )
                  AND o.approved IS NOT NULL
                  AND o.deleted IS NULL
                  AND o.effective_date <= current_date
            ), 0)

            - coalesce((
                SELECT sum(pr.value)
                FROM back_office.ops_payment_register_decision pr
                WHERE pr.ops_assignee_decision_id = r.decision_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
                  )
            ), 0)

            + coalesce((
                SELECT sum(ret.value)
                FROM back_office.ops_payment_return ret
                JOIN back_office.ops_payment_register_decision pr
                    ON pr.id = ret.ops_payment_register_decision_id
                WHERE pr.ops_assignee_decision_id = r.decision_id
                  AND EXISTS (
                      SELECT 1
                      FROM back_office.operation o
                      WHERE o.ops_payment_return_id = ret.id
                        AND o.operation_type_id = (
                            SELECT id
                            FROM back_office.operation_type
                            WHERE code = 'RETURN'
                        )
                        AND o.approved IS NOT NULL
                        AND o.deleted IS NULL
                        AND o.effective_date <= current_date
                  )
            ), 0),

            '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
)

SELECT
    r.rn AS rn,
    r.snils AS snils,
    r.contract_number AS contract_number,
    r.contract_date 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,
    r.date AS date,
    r.payment_value AS payment_value,
    r.payment_val AS payment_val,
    r.balance AS balance,
    r.balance_rs AS balance_rs,
    r.rops AS rops,
    r.reserved AS reserved,
    r.total AS total,
    r.fio_a AS fio_a
FROM report_result r