---

WITH ev_operations AS (

    /* ============================================================
       ФАКТИЧЕСКИЕ ОПЕРАЦИИ ЕВ В ВЫБРАННОМ ПЕРИОДЕ

       35  - Единовременная выплата
       37  - Дополнительная выплата ЕВ
       112 - Единовременная выплата
       173 - Дополнительная единовременная выплата
       ============================================================ */

    SELECT o.id
    FROM back_office.operation o
    WHERE o.operation_type_id IN (35, 37, 112, 173)
      AND o.effective_date >= CAST(:start_date AS date)
      AND o.effective_date < CAST(:end_date AS date) + INTERVAL '1 day'
),

ev_payment_sharers AS (

    /* ============================================================
       Определяем sharer, по счетам которых была фактическая
       операция ЕВ в выбранном периоде
       ============================================================ */

    /* Дебет */
    SELECT da.owner_sharer_id AS sharer_id

    FROM ev_operations eo

    JOIN back_office.transaction t
        ON t.operation_id = eo.id

    JOIN back_office.account da
        ON da.id = t.debit_account_id

    LEFT JOIN back_office.account ca
        ON ca.id = t.credit_account_id

    WHERE da.owner_sharer_id IS NOT NULL
      AND da.owner_sharer_id <> COALESCE(ca.owner_sharer_id, -1)


    UNION


    /* Кредит */
    SELECT ca.owner_sharer_id AS sharer_id

    FROM ev_operations eo

    JOIN back_office.transaction t
        ON t.operation_id = eo.id

    JOIN back_office.account ca
        ON ca.id = t.credit_account_id

    WHERE ca.owner_sharer_id IS NOT NULL
),

ev AS (

    /* ============================================================
       1. СТАРАЯ ЛОГИКА
       Исторические заявления, где ЕВ = ops_payment_type_code '01'
       ============================================================ */

    SELECT
        opaa.id,
        opaa.appeal_date,

        CASE
            WHEN opaa.accepted IS NOT NULL
                THEN 'Да'
            ELSE 'Нет'
        END AS accepted,

        opaa.number AS reg_numb,

        dpt.short_name,

        upper(
            i.last_name || ' ' ||
            i.first_name || ' ' ||
            i.middle_name
        ) AS fio_zl,

        i.insurance_number AS i_zl,

        c.number AS contract_numb,

        pt.name AS payment_way,

        a.address_text,

        aibd.personal_account,
        aibd.bic,

        br.bank_name,
        br.corraccount,

        br.place || ' ' || br.address AS bank_addres,

        aibd.accepted AS br_date

    FROM ourpension.ops_pension_assignment_application opaa

    /* В отчет попадут только те sharer,
       по которым была фактическая ЕВ в выбранном периоде */
    JOIN ev_payment_sharers eps
        ON eps.sharer_id = opaa.sharer_id

    LEFT JOIN ourpension.payment_type pt
        ON opaa.payment_way = pt.code

    JOIN ourpension.sharer s
        ON s.id = opaa.sharer_id

    JOIN ourpension.contract c
        ON c.id = s.contract_id

    JOIN ourpension.individual i
        ON i.id = c.individual_id

    JOIN ourpension.address a
        ON i.registration_address_id = a.id

    JOIN ourpension.decision_payment_type dpt
        ON opaa.decision_payment_type_code = dpt.code

    LEFT JOIN ourpension.application_individual_bank_detail aibd
        ON opaa.bank_detail_id = aibd.id

    LEFT JOIN ourpension.bankru br
        ON aibd.bic = br.bic

    WHERE opaa.ops_payment_type_code = '01'

      /*
       ВАЖНО:
       старого условия
       opaa.appeal_date BETWEEN :start_date AND :end_date
       здесь больше НЕТ.

       Период определяется фактической выплатой.
       */

      AND aibd.accepted = COALESCE(:br_date, aibd.accepted)


    UNION ALL


    /* ============================================================
       2. НОВАЯ ЛОГИКА
       Современные ЕВ / ЕВО определяем по РЕШЕНИЮ
       ============================================================ */

    SELECT
        opaa.id,
        opaa.appeal_date,

        CASE
            WHEN opaa.accepted IS NOT NULL
                THEN 'Да'
            ELSE 'Нет'
        END AS accepted,

        opaa.number AS reg_numb,

        dpt.short_name,

        upper(
            i.last_name || ' ' ||
            i.first_name || ' ' ||
            i.middle_name
        ) AS fio_zl,

        i.insurance_number AS i_zl,

        c.number AS contract_numb,

        pt.name AS payment_way,

        a.address_text,

        aibd.personal_account,
        aibd.bic,

        br.bank_name,
        br.corraccount,

        br.place || ' ' || br.address AS bank_addres,

        aibd.accepted AS br_date

    FROM back_office.ops_payment_decision opd

    JOIN ourpension.ops_pension_assignment_application opaa
        ON opaa.id = opd.ops_pension_assignment_application_id

    /* В отчет попадут только те sharer,
       по которым была фактическая ЕВ в выбранном периоде */
    JOIN ev_payment_sharers eps
        ON eps.sharer_id = opaa.sharer_id

    LEFT JOIN ourpension.payment_type pt
        ON opaa.payment_way = pt.code

    JOIN ourpension.sharer s
        ON s.id = opaa.sharer_id

    JOIN ourpension.contract c
        ON c.id = s.contract_id

    JOIN ourpension.individual i
        ON i.id = c.individual_id

    JOIN ourpension.address a
        ON i.registration_address_id = a.id

    /* Для современной модели тип выплаты берем из решения */
    JOIN ourpension.decision_payment_type dpt
        ON opd.decision_payment_type_code = dpt.code

    LEFT JOIN ourpension.application_individual_bank_detail aibd
        ON opaa.bank_detail_id = aibd.id

    LEFT JOIN ourpension.bankru br
        ON aibd.bic = br.bic

    WHERE opd.decision_payment_type_code IN ('01', '16')

      /* Только принятые решения */
      AND opd.accepted IS NOT NULL

      /* Не берем отмененные / удаленные решения */
      AND opd.rejected IS NULL
      AND opd.canceled IS NULL

      /*
       ВАЖНО:
       старого условия
       opd.date BETWEEN :start_date AND :end_date
       здесь больше НЕТ.

       Период определяется фактической выплатой.
       */

      AND aibd.accepted = COALESCE(:br_date, aibd.accepted)
)

SELECT
    ev.id AS id,
    to_char(ev.appeal_date, 'DD.MM.YYYY') AS appeal_date,
    ev.accepted AS accepted,
    ev.reg_numb AS reg_numb,
    ev.short_name AS short_name,
    ev.fio_zl AS fio_zl,
    ev.i_zl AS i_zl,
    ev.contract_numb AS contract_numb,
    ev.payment_way AS payment_way,
    ev.address_text AS address_text,
    ev.personal_account AS personal_account,
    ev.bic AS bic,
    ev.bank_name AS bank_name,
    ev.corraccount AS corraccount,
    ev.bank_addres AS bank_addres,
    to_char(ev.br_date, 'DD.MM.YYYY') AS br_date

FROM ev;