---

WITH target_decisions AS MATERIALIZED (
    SELECT d.id
    FROM back_office.ops_assignee_decision d
    WHERE :isShow
      AND d.accepted IS NOT NULL
),

-- Начисления по каждому решению
charged AS (
    SELECT
        o.ops_assignee_decision_id AS decision_id,
        sum(t.value) AS amount
    FROM target_decisions d
    JOIN back_office.operation o
        ON o.ops_assignee_decision_id = d.id
    JOIN back_office.operation_type ot
        ON ot.id = o.operation_type_id
    JOIN back_office.transaction t
        ON t.operation_id = o.id
    JOIN back_office.account ca
        ON ca.id = t.credit_account_id
    JOIN back_office.account_type cat
        ON cat.id = ca.account_type_id
    WHERE ot.code = 'CHARGE_FROM_RESERVE'
      AND cat.mnemonics = 'НВ ПП ОПС'
      AND o.approved IS NOT NULL
      AND o.deleted IS NULL
      AND o.effective_date <= current_date
    GROUP BY o.ops_assignee_decision_id
),

-- Реестры с подтверждённой операцией выплаты
paid_registers AS MATERIALIZED (
    SELECT DISTINCT
        o.ops_payment_register_id AS register_id
    FROM back_office.operation o
    JOIN back_office.operation_type ot
        ON ot.id = o.operation_type_id
    WHERE ot.code IN (
        'PAYMENT_PP',
        'ADDITIONAL_PAYMENT_PP'
    )
      AND o.ops_payment_register_id IS NOT NULL
      AND o.approved IS NOT NULL
      AND o.deleted IS NULL
      AND o.effective_date <= current_date
),

-- Выплаты по каждому решению
paid AS (
    SELECT
        pr.ops_assignee_decision_id AS decision_id,
        sum(pr.value) AS amount
    FROM target_decisions d
    JOIN back_office.ops_payment_register_decision pr
        ON pr.ops_assignee_decision_id = d.id
    JOIN paid_registers p
        ON p.register_id = pr.ops_payment_register_id
    GROUP BY pr.ops_assignee_decision_id
),

-- Документы с подтверждённой операцией возврата
confirmed_returns AS MATERIALIZED (
    SELECT DISTINCT
        o.ops_payment_return_id AS return_id
    FROM back_office.operation o
    JOIN back_office.operation_type ot
        ON ot.id = o.operation_type_id
    WHERE ot.code = 'RETURN'
      AND o.ops_payment_return_id IS NOT NULL
      AND o.approved IS NOT NULL
      AND o.deleted IS NULL
      AND o.effective_date <= current_date
),

-- Возвраты по каждому решению
returned AS (
    SELECT
        pr.ops_assignee_decision_id AS decision_id,
        sum(r.value) AS amount
    FROM target_decisions d
    JOIN back_office.ops_payment_register_decision pr
        ON pr.ops_assignee_decision_id = d.id
    JOIN back_office.ops_payment_return r
        ON r.ops_payment_register_decision_id = pr.id
    JOIN confirmed_returns cr
        ON cr.return_id = r.id
    GROUP BY pr.ops_assignee_decision_id
),

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 target_decisions td

    JOIN back_office.ops_assignee_decision oad
        ON oad.id = td.id

    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 charged ch
        ON ch.decision_id = oad.id

    LEFT JOIN paid pay
        ON pay.decision_id = oad.id

    LEFT JOIN returned ret
        ON ret.decision_id = oad.id

    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
)

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