CREATE MATERIALIZED VIEW adib_rm.cash_holds_mv AS
WITH latest_settlement AS (
SELECT
order_id,
id,
state,
expected_settlement_date,
settled_at
FROM (
SELECT
order_id,
id,
state,
expected_settlement_date,
settled_at,
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,
execution_state,
executed_at
FROM (
SELECT
id,
execution_state,
executed_at,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
FROM order_service.orders
) AS ranked_orders
WHERE
rn = 1
)
SELECT
h.id AS cash_hold_id,
h.order_id,
h.provider_hold_id,
h.funding_account_id,
h.hold_amount,
h.float_used,
h.currency_code,
h.status,
h.created_at,
h.updated_at,
ord.execution_state AS order_execution_state,
ord.executed_at AS order_executed_at,
s.id AS settlement_id,
s.state AS settlement_state,
s.expected_settlement_date AS settlement_expected_date,
s.settled_at AS settlement_settled_at
FROM order_service.order_cash_holds AS h
LEFT JOIN orders_current AS ord
ON ord.id = h.order_id
LEFT JOIN latest_settlement AS s
ON s.order_id = h.order_id