---

SELECT
    opaa.id,
    opaa.number,
    opaa.appeal_date,
    opaa.registration,
    opaa.accepted,

    -- Тип заявления / выплаты
    opaa.ops_payment_type_code,
    opaa.decision_payment_type_code,
    dpt.code AS found_decision_payment_type,

    -- Банковские реквизиты
    opaa.bank_detail_id,
    opaa.bank_account_id,

    aibd.id AS found_bank_detail_id,
    aibd.personal_account,
    aibd.bic,
    aibd.accepted AS bank_detail_accepted,

    -- Адрес
    opaa.address_id AS application_address_id,
    i.registration_address_id AS individual_registration_address_id,
    a.id AS found_registration_address_id,

    -- Решения
    (
        SELECT count(*)
        FROM back_office.ops_payment_decision opd
        WHERE opd.ops_pension_assignment_application_id = opaa.id
    ) AS decision_count,

    (
        SELECT max(opd.accepted)
        FROM back_office.ops_payment_decision opd
        WHERE opd.ops_pension_assignment_application_id = opaa.id
    ) AS decision_accepted,

    -- Диагностика именно текущего отчета
    CASE
        WHEN s.id IS NULL
            THEN 'НЕ ПОПАДЕТ: нет sharer'
        WHEN c.id IS NULL
            THEN 'НЕ ПОПАДЕТ: нет contract'
        WHEN i.id IS NULL
            THEN 'НЕ ПОПАДЕТ: нет individual'
        WHEN a.id IS NULL
            THEN 'НЕ ПОПАДЕТ: нет registration_address'
        WHEN dpt.code IS NULL
            THEN 'НЕ ПОПАДЕТ: decision_payment_type_code не найден'
        WHEN aibd.id IS NULL
            THEN 'НЕ ПОПАДЕТ: bank_detail_id отсутствует/не найден'
        WHEN aibd.accepted IS NULL
            THEN 'НЕ ПОПАДЕТ: aibd.accepted IS NULL'
        ELSE
            'ПО УСЛОВИЯМ ОТЧЕТА ДОЛЖНО ПОПАДАТЬ'
    END AS report_diagnostic

FROM ourpension.ops_pension_assignment_application opaa

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

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

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

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

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

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

WHERE opaa.ops_payment_type_code = '01'
  AND opaa.appeal_date BETWEEN :start_date AND :end_date

ORDER BY opaa.appeal_date DESC, opaa.id DESC;