CREATE MATERIALIZED VIEW insights.settled_position_valuations_mv AS
WITH latest_position AS (
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
settled_quantity,
settled_value
FROM (
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
settled_quantity,
settled_value,
ROW_NUMBER() OVER (
PARTITION BY account_id, asset_id, currency_code
ORDER BY dim_settlement_date DESC
) AS rn
FROM insights.settled_position_series_mv AS settled_position_series_mv_next
WHERE
dim_settlement_date <= CURRENT_TIMESTAMP
) AS ranked
WHERE
rn = 1
)
SELECT
p.account_id,
p.asset_id,
p.currency_code,
p.dim_settlement_date AS last_settlement_date,
p.settled_quantity,
p.settled_value,
px.price,
px.price_date,
p.settled_quantity * px.price AS market_value,
p.settled_quantity * px.price * COALESCE(fx.rate, CASE WHEN p.currency_code = 'AED' THEN 1 ELSE NULL END) AS market_value_system_currency,
cb.average_cost_per_unit,
cb.average_cost_per_unit_system_currency,
cb.purchased_quantity AS baseline_purchased_quantity,
cb.dim_value_date AS cost_basis_date,
cb.m_is_stub AS cost_basis_is_stub,
p.settled_quantity * cb.average_cost_per_unit AS total_cost,
cb.total_cost_system_currency,
cb.cost_fx_provenance
FROM latest_position AS p
LEFT JOIN insights.settled_cost_basis_latest_mv AS cb
ON cb.account_id = p.account_id AND cb.asset_id = p.asset_id
LEFT JOIN insights.asset_latest_price_mv AS px
ON px.asset_id = p.asset_id
LEFT JOIN insights.fx_rates_snapshot_mv AS fx
ON fx.source_currency_code = p.currency_code AND fx.target_currency_code = 'AED'