---

EXPLAIN (VERBOSE, COSTS)
WITH latest_death_info AS MATERIALIZED (
    SELECT DISTINCT ON (a.individual_id)
        a.individual_id,
        a.death_date
    FROM ourpension.application_death_info a
    WHERE a.accepted IS NOT NULL
      AND a.canceled IS NULL
      AND a.rejected IS NULL
    ORDER BY
        a.individual_id,
        a.version DESC
),
ops_sharer(id, individual_death_date) AS MATERIALIZED (
    SELECT
        s.id,
        death_info.death_date
    FROM latest_death_info death_info
    JOIN report.dia_param p
        ON death_info.death_date <= p.report_date
    JOIN ourpension.sharer s
        ON s.individual_id = death_info.individual_id
    JOIN ourpension.contract c
        ON c.id = s.contract_id
    WHERE c.service_type = 'OPS'
      AND NOT EXISTS (
          SELECT 1
          FROM back_office.ops_transfer_details d
          JOIN back_office.ops_transfer_register tr
              ON tr.id = d.ops_transfer_register_id
          JOIN back_office.operation o
              ON o.ops_transfer_register_id = tr.id
          WHERE d.sharer_id = s.id
            AND o.approved IS NOT NULL
            AND o.effective_date <= p.report_date
      )
),
t_balance_all AS (
    SELECT
        da.owner_sharer_id AS sharer_id,
        SUM(t.value) AS balance,
        SUM(CASE WHEN dat.mnemonics IN
            ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС')
            THEN t.value ELSE 0 END) AS ops,
        SUM(CASE WHEN dat.mnemonics IN
            ('ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД')
            THEN t.value ELSE 0 END) AS ops_id,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г')
            THEN t.value ELSE 0 END) AS dps_g,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД')
            THEN t.value ELSE 0 END) AS dps_g_id,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л')
            THEN t.value ELSE 0 END) AS dps_l,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД')
            THEN t.value ELSE 0 END) AS dps_l_id,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК')
            THEN t.value ELSE 0 END) AS dps_mk,
        SUM(CASE WHEN dat.mnemonics IN
            ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')
            THEN t.value ELSE 0 END) AS dps_mk_id
    FROM ops_sharer s
    JOIN back_office.account da
        ON da.owner_sharer_id = s.id
    JOIN back_office.account_type dat
        ON dat.id = da.account_type_id
    JOIN back_office.transaction t
        ON t.debit_account_id = da.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 dat.mnemonics IN (
        'ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
        'ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД',
        'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
        'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
        'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
        'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
        'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
        'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД'
    )
      AND cat.mnemonics IN ('Р ОПС', 'Р ОПС ИД')
    GROUP BY da.owner_sharer_id

    UNION ALL

    SELECT
        ca.owner_sharer_id AS sharer_id,
        -SUM(t.value) AS balance,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС')
            THEN t.value ELSE 0 END) AS ops,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД')
            THEN t.value ELSE 0 END) AS ops_id,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г')
            THEN t.value ELSE 0 END) AS dps_g,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД')
            THEN t.value ELSE 0 END) AS dps_g_id,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л')
            THEN t.value ELSE 0 END) AS dps_l,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД')
            THEN t.value ELSE 0 END) AS dps_l_id,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК')
            THEN t.value ELSE 0 END) AS dps_mk,
        -SUM(CASE WHEN cat.mnemonics IN
            ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')
            THEN t.value ELSE 0 END) AS dps_mk_id
    FROM ops_sharer s
    JOIN back_office.account ca
        ON ca.owner_sharer_id = s.id
    JOIN back_office.account_type cat
        ON cat.id = ca.account_type_id
    JOIN back_office.transaction t
        ON t.credit_account_id = ca.id
    JOIN back_office.account da
        ON da.id = t.debit_account_id
    JOIN back_office.account_type dat
        ON dat.id = da.account_type_id
    WHERE cat.mnemonics IN (
        'ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
        'ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД',
        'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
        'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
        'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
        'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
        'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
        'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД'
    )
      AND dat.mnemonics IN ('Р ОПС', 'Р ОПС ИД')
    GROUP BY ca.owner_sharer_id
)
INSERT INTO report.dia_rops (
    id,
    dia_register_id,
    insurance_number,
    sharer_number,
    death_date,
    ops,
    ops_id,
    dps_l,
    dps_l_id,
    dps_g,
    dps_g_id,
    dps_mk,
    dps_mk_id,
    total,
    sharer_id
)
SELECT
    nextval('report.dia_rops_id_sequence'),
    currval('report.dia_register_id_sequence'),
    i.insurance_number,
    s.number,
    death_info.death_date,
    b.ops,
    b.ops_id,
    b.dps_l,
    b.dps_l_id,
    b.dps_g,
    b.dps_g_id,
    b.dps_mk,
    b.dps_mk_id,
    b.balance,
    s.id
FROM (
    SELECT
        sharer_id,
        SUM(ops) AS ops,
        SUM(ops_id) AS ops_id,
        SUM(dps_g) AS dps_g,
        SUM(dps_g_id) AS dps_g_id,
        SUM(dps_l) AS dps_l,
        SUM(dps_l_id) AS dps_l_id,
        SUM(dps_mk) AS dps_mk,
        SUM(dps_mk_id) AS dps_mk_id,
        SUM(balance) AS balance
    FROM t_balance_all
    GROUP BY sharer_id
) b
JOIN ourpension.sharer s
    ON s.id = b.sharer_id
JOIN ourpension.individual i
    ON i.id = s.individual_id
JOIN LATERAL (
    SELECT a.death_date
    FROM ourpension.application_death_info a
    WHERE a.individual_id = s.individual_id
      AND a.accepted IS NOT NULL
      AND a.canceled IS NULL
      AND a.rejected IS NULL
    ORDER BY a.version DESC
    LIMIT 1
) death_info ON true
WHERE b.balance <> 0;