---

SELECT
    c.id AS contract_id,
    c.number AS contract_number,
    c.service_type,
    c.date AS contract_date,
    cph.pens_product_id AS product_id,
    pp.name AS product_name
FROM ourpension.contract c
LEFT JOIN LATERAL (
    SELECT h.pens_product_id
    FROM ourpension.contract_product_history h
    WHERE h.contract_id = c.id
      AND CURRENT_TIMESTAMP BETWEEN h.begin_date
          AND COALESCE(h.end_date, 'infinity')
    ORDER BY h.begin_date DESC, h.id DESC
    LIMIT 1
) cph ON TRUE
LEFT JOIN prod_line.pens_product pp
    ON pp.id = cph.pens_product_id
WHERE c.number ILIKE '%00000778'
ORDER BY c.id DESC;