---

WITH 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

    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'

      AND 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

    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.date BETWEEN :start_date AND :end_date

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

      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;