---

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_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_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_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
)

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 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;