CREATE MATERIALIZED VIEW alpheya_agent.book_of_business_party_clients_mv AS
WITH party_lifecycle AS (
SELECT
party_id,
base_currency_code,
onboarding_date,
segment_id,
status_label_id,
customer_relationship_status,
updated_at
FROM (
SELECT
cr.party_id,
lp.base_currency_code,
lp.onboarding_date,
lp.segment_id,
cr.status_label_id,
cr.status AS customer_relationship_status,
GREATEST(COALESCE(cr.updated_at, cr.created_at), COALESCE(lp.updated_at, lp.created_at)) AS updated_at,
ROW_NUMBER() OVER (
PARTITION BY cr.party_id
ORDER BY CASE cr.status WHEN 'ACTIVE' THEN 3 WHEN 'SUSPENDED' THEN 2 ELSE 1 END DESC, cr.updated_at DESC NULLS LAST, cr.created_at DESC NULLS LAST, cr.id DESC
) AS lifecycle_rank
FROM party.customer_relationships AS cr
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = cr.id AND lp.disabled_at IS NULL
WHERE
cr.type = 'CUSTOMER'
AND cr.disabled_at IS NULL
AND (
cr.status IN ('SUSPENDED', 'DRAFT')
OR (
cr.status = 'ACTIVE'
AND cr.effective_from <= CURRENT_TIMESTAMP
AND COALESCE(cr.effective_to, CAST('9999-12-31' AS DATE)) > CURRENT_TIMESTAMP
)
)
) AS ranked_lifecycle
WHERE
lifecycle_rank = 1
)
SELECT
p.id AS party_id,
p.display_name,
pi.first_name,
pi.last_name,
pl.base_currency_code,
pl.customer_relationship_status,
pl.segment_id AS segment_label_id,
pl.status_label_id,
pl.onboarding_date,
GREATEST(pl.updated_at, COALESCE(p.updated_at, p.created_at)) AS updated_at
FROM party.parties AS p
JOIN party_lifecycle AS pl
ON pl.party_id = p.id
LEFT JOIN party.party_individual AS pi
ON pi.party_id = p.id
WHERE
p.disabled_at IS NULL