---

WITH sample AS MATERIALIZED (
    SELECT
        o.id AS operation_id,
        o.operation_date,
        et.id AS eps_transaction_id,
        et.eps_id,
        et.oper_date AS eps_oper_date,
        et.pens_schema_dt,
        et.pens_schema_ct,
        et.strategy_id AS eps_strategy_id
    FROM back_office.eps_transaction et
    JOIN back_office.eps_transaction_source src
        ON src.id = et.eps_transaction_source_id
    JOIN back_office.operation o
        ON o.id = src.operation_id
    JOIN ourpension.eps e
        ON e.id = et.eps_id
    WHERE e.service_type = 'NPO'
      AND o.operation_date >= CAST('2026-08-01' AS date)
      AND o.operation_date < CAST('2026-09-01' AS date)
    LIMIT 5
)
SELECT
    sm.operation_id,
    sm.operation_date,
    sm.eps_transaction_id,
    sm.eps_oper_date,

    ed.id AS eps_detail_id,
    ed.account_dt,
    ed.account_ct,

    ed.pension_scheme_id AS setting_scheme_id,
    sm.pens_schema_dt AS eps_debit_scheme_id,
    sm.pens_schema_ct AS eps_credit_scheme_id,

    COALESCE(
        ed.pension_scheme_id,
        sm.pens_schema_dt
    ) AS report_debit_scheme_id,

    COALESCE(
        ed.pension_scheme_id,
        sm.pens_schema_ct
    ) AS report_credit_scheme_id,

    sm.eps_strategy_id,
    tr.transaction_strategy_ids,
    tr.transaction_count

FROM sample sm
JOIN ourpension.eps_detail ed
    ON ed.eps_id = sm.eps_id

LEFT JOIN LATERAL (
    SELECT
        COUNT(*) AS transaction_count,
        ARRAY_AGG(DISTINCT te.strategy_id)
            FILTER (
                WHERE te.strategy_id IS NOT NULL
            ) AS transaction_strategy_ids
    FROM back_office.transaction t
    LEFT JOIN back_office.transaction_extended te
        ON te.transaction_id = t.id
    WHERE t.operation_id = sm.operation_id
) tr ON true;