---

EXPLAIN (VERBOSE, COSTS)
WITH ops_sharer(id, individual_death_date) AS MATERIALIZED (
    SELECT
        s.id,
        death_info.death_date
    FROM ourpension.sharer s
    JOIN ourpension.contract c
        ON s.contract_id = c.id
    JOIN report.dia_param p
        ON true
    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
    LEFT JOIN LATERAL (
        SELECT d.sharer_id
        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
        LIMIT 1
    ) transfer ON true
    WHERE c.service_type = 'OPS'
      AND death_info.death_date <= p.report_date
      AND transfer.sharer_id IS NULL
),
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 t.credit_account_id = ca.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 t.debit_account_id = da.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 s.individual_id = i.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;