---

WITH report_sharers AS (

    /* Старая ветка отчета */
    SELECT opaa.sharer_id
    FROM ourpension.ops_pension_assignment_application opaa
    WHERE opaa.ops_payment_type_code = '01'
      AND opaa.appeal_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'

    UNION

    /* Новая ветка отчета */
    SELECT opaa.sharer_id
    FROM back_office.ops_payment_decision opd
    JOIN ourpension.ops_pension_assignment_application opaa
        ON opaa.id = opd.ops_pension_assignment_application_id
    WHERE opd.decision_payment_type_code IN ('01', '16')
      AND opd.accepted IS NOT NULL
      AND opd.date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
      AND opd.rejected IS NULL
      AND opd.canceled IS NULL
),

ev_operations AS (
    SELECT o.id
    FROM back_office.operation o
    WHERE o.operation_type_id IN (35, 37, 112, 173)
      AND o.effective_date >= DATE '2025-01-01'
      AND o.effective_date < DATE '2025-02-01'
),

payment_sharers AS (

    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
)

SELECT
    count(*) AS report_sharers,

    count(*) FILTER (
        WHERE ps.sharer_id IS NOT NULL
    ) AS with_payment_in_january,

    count(*) FILTER (
        WHERE ps.sharer_id IS NULL
    ) AS without_payment_in_january

FROM report_sharers rs
LEFT JOIN payment_sharers ps
    ON ps.sharer_id = rs.sharer_id;