-- Contracts with an MtM strategy, pricing_component details, and latest valuation snapshot
SELECT
    'Purchase'                          AS side,
    pp.number                           AS contract,
    pl.quantity                         AS contracted_qty,
    ms.name                             AS strategy_name,
    msc.name                            AS scenario_name,
    msc.valuation_date,
    msc.use_last_price,
    cur.symbol                          AS strategy_currency,
    -- MtM component (pricing_component linked via strategy)
    pc_mtm.price_source_type            AS component_price_source,
    pc_mtm.ratio                        AS component_ratio_pct,
    ft.name                             AS component_fix_type,
    pi.price_desc                       AS component_curve,
    pc_cal.name                         AS component_calendar,
    pc_mtm.auto                         AS component_auto,
    pc_mtm.fallback                     AS component_fallback,
    -- Latest valuation (physical price row)
    vv.date                             AS last_valuation_date,
    vv.price                            AS last_valuation_price,
    vv.quantity                         AS last_valuation_qty,
    vv.amount                           AS last_valuation_amount,
    vv.mtm                              AS portfolio_mtm
FROM purchase_strategy ps
JOIN purchase_line     pl       ON pl.id        = ps.line
JOIN purchase_purchase pp       ON pp.id        = pl.purchase
JOIN mtm_strategy      ms       ON ms.id        = ps.strategy
JOIN mtm_scenario      msc      ON msc.id       = ms.scenario
JOIN currency_currency cur      ON cur.id       = ms.currency
LEFT JOIN pricing_component pc_mtm  ON pc_mtm.strategy = ms.id
LEFT JOIN price_fixtype     ft      ON ft.id            = pc_mtm.fix_type
LEFT JOIN price_price       pi      ON pi.id            = pc_mtm.price_index
LEFT JOIN price_calendar    pc_cal  ON pc_cal.id        = pc_mtm.calendar
LEFT JOIN LATERAL (
    SELECT date, price, quantity, amount, mtm
    FROM valuation_valuation
    WHERE line = pl.id
      AND type = 'pur. priced'
      AND reference = 'Purchase/Physic'
    ORDER BY date DESC
    LIMIT 1
) vv ON TRUE

UNION ALL

SELECT
    'Sale',
    ss.number,
    sl.quantity,
    ms.name,
    msc.name,
    msc.valuation_date,
    msc.use_last_price,
    cur.symbol,
    pc_mtm.price_source_type,
    pc_mtm.ratio,
    ft.name,
    pi.price_desc,
    pc_cal.name,
    pc_mtm.auto,
    pc_mtm.fallback,
    vv.date,
    vv.price,
    vv.quantity,
    vv.amount,
    vv.mtm
FROM sale_strategy     sa
JOIN sale_line         sl       ON sl.id        = sa.sale_line
JOIN sale_sale         ss       ON ss.id        = sl.sale
JOIN mtm_strategy      ms       ON ms.id        = sa.strategy
JOIN mtm_scenario      msc      ON msc.id       = ms.scenario
JOIN currency_currency cur      ON cur.id       = ms.currency
LEFT JOIN pricing_component pc_mtm  ON pc_mtm.strategy = ms.id
LEFT JOIN price_fixtype     ft      ON ft.id            = pc_mtm.fix_type
LEFT JOIN price_price       pi      ON pi.id            = pc_mtm.price_index
LEFT JOIN price_calendar    pc_cal  ON pc_cal.id        = pc_mtm.calendar
LEFT JOIN LATERAL (
    SELECT date, price, quantity, amount, mtm
    FROM valuation_valuation
    WHERE line = sl.id
      AND type = 'sale priced'
      AND reference = 'Sale/Physic'
    ORDER BY date DESC
    LIMIT 1
) vv ON TRUE

ORDER BY side, contract;


select * from price_price