---

BEGIN READ ONLY;

SET LOCAL statement_timeout = '180s';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
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 AS MATERIALIZED (
    SELECT
        s.id,
        death_info.death_date AS individual_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
      )
),
reserve_accounts AS MATERIALIZED (
    SELECT array_agg(a.id) AS ids
    FROM back_office.account a
    JOIN back_office.account_type at
        ON at.id = a.account_type_id
    WHERE at.mnemonics IN ('Р ОПС', 'Р ОПС ИД')
),
reserve_movements AS NOT MATERIALIZED (
    SELECT
        t.debit_account_id AS account_id,
        t.value AS signed_value
    FROM reserve_accounts r
    JOIN back_office.transaction t
        ON t.credit_account_id = ANY(r.ids)

    UNION ALL

    SELECT
        t.credit_account_id AS account_id,
        -t.value AS signed_value
    FROM reserve_accounts r
    JOIN back_office.transaction t
        ON t.debit_account_id = ANY(r.ids)
),
balances AS (
    SELECT
        a.owner_sharer_id AS sharer_id,
        SUM(m.signed_value) AS balance,

        SUM(CASE WHEN at.mnemonics IN (
            'ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС'
        ) THEN m.signed_value ELSE 0 END) AS ops,

        SUM(CASE WHEN at.mnemonics IN (
            'ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД'
        ) THEN m.signed_value ELSE 0 END) AS ops_id,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г'
        ) THEN m.signed_value ELSE 0 END) AS dps_g,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД'
        ) THEN m.signed_value ELSE 0 END) AS dps_g_id,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л'
        ) THEN m.signed_value ELSE 0 END) AS dps_l,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД'
        ) THEN m.signed_value ELSE 0 END) AS dps_l_id,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК'
        ) THEN m.signed_value ELSE 0 END) AS dps_mk,

        SUM(CASE WHEN at.mnemonics IN (
            'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД'
        ) THEN m.signed_value ELSE 0 END) AS dps_mk_id

    FROM reserve_movements m
    JOIN back_office.account a
        ON a.id = m.account_id
    JOIN back_office.account_type at
        ON at.id = a.account_type_id
    JOIN ops_sharer s
        ON s.id = a.owner_sharer_id
    WHERE at.mnemonics IN (
        'ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
        'ОПС ИД', 'ОПС ФР', 'Р ПП ОПС ИД',
        'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
        'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
        'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
        'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
        'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
        'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД'
    )
    GROUP BY a.owner_sharer_id
    HAVING SUM(m.signed_value) <> 0
)
SELECT
    i.insurance_number,
    s.number AS sharer_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 AS total,
    s.id AS sharer_id
FROM balances 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;

ROLLBACK;