CREATE MATERIALIZED VIEW adib_rm.settlement_orders_mv AS
WITH latest_settlement AS (
SELECT
order_id,
state,
expected_settlement_date,
settled_at,
amount,
failure_reason
FROM (
SELECT
order_id,
state,
expected_settlement_date,
settled_at,
amount,
failure_reason,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY settled_at DESC NULLS LAST, created_at DESC, id DESC
) AS rn
FROM order_service.order_settlements
WHERE
NOT order_id IS NULL
) AS ranked
WHERE
rn = 1
), orders_current AS (
SELECT
id,
client_order_id,
security_account_id,
asset_id,
side,
cost_estimate,
filled_quantity,
created_at
FROM (
SELECT
id,
client_order_id,
security_account_id,
asset_id,
side,
cost_estimate,
filled_quantity,
created_at,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
FROM order_service.orders
) AS ranked_orders
WHERE
rn = 1
), latest_route AS (
SELECT
order_id,
external_order_id
FROM (
SELECT
order_id,
external_order_id,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC, id DESC) AS rn
FROM order_service.order_routes
) AS ranked_routes
WHERE
rn = 1
)
SELECT
ord.id AS order_id,
ord.client_order_id,
r.external_order_id,
ord.security_account_id,
ord.asset_id,
ord.side AS order_side,
COALESCE(s.state, 'SETTLEMENT_STATE_UNSPECIFIED') AS settlement_state,
ord.created_at,
s.expected_settlement_date,
s.settled_at,
s.failure_reason,
ord.filled_quantity,
CAST((
ord.cost_estimate -> 'estimatedNet' -> 'amount' ->> 'value'
) AS DECIMAL) AS est_net_amount,
ord.cost_estimate -> 'estimatedNet' -> 'currencyCode' ->> 'value' AS est_net_currency,
CAST((
s.amount -> 'amount' ->> 'value'
) AS DECIMAL) AS settled_amount
FROM orders_current AS ord
LEFT JOIN latest_settlement AS s
ON s.order_id = ord.id
LEFT JOIN latest_route AS r
ON r.order_id = ord.id