---

WITH cte_pens_product AS MATERIALIZED
(
    SELECT
        products.id,
        products."name",
        products.pens_scheme_accum_stage_id AS scheme_id
    FROM ourpension.contract_product_history history
        INNER JOIN prod_line.pens_product products
            ON products.id = history.pens_product_id
    WHERE history.contract_id = 13405120
      AND CURRENT_TIMESTAMP BETWEEN
          history.begin_date
          AND COALESCE(history.end_date, 'infinity')
    LIMIT 1
),
cte_param_pl AS MATERIALIZED
(
    SELECT
        pp2.id AS param_id,
        pp2.brief,
        pppp.value_decimal
    FROM cte_pens_product cpp
        INNER JOIN prod_line.pens_product pens_p
            ON pens_p.id = cpp.id
        INNER JOIN prod_line.pens_product_param_pl pppp
            ON pppp.pens_product_id = pens_p.id
        INNER JOIN prod_line.param_pl pp2
            ON pp2.id = pppp.param_pl_id
)
SELECT
    cpp.id AS product_id,

    (
        SELECT param.value_decimal
        FROM cte_param_pl param
        WHERE param.brief = 'МГДНакПериодС'
    ) AS mgd_accumulation_period,

    (
        SELECT param.value_decimal
        FROM cte_param_pl param
        WHERE param.brief = 'МГДВыплПериодС'
    ) AS mgd_payment_period

FROM cte_pens_product cpp;