---

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
)

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