CREATE MATERIALIZED VIEW alinma_bff.party_advisor_kpis_mv AS
SELECT
cnt.user_id,
cnt.clients_count,
cnt.portfolios_count,
cnt.accounts_count,
COALESCE(val.total_financing, 0) AS total_financing,
COALESCE(val.total_relationship_value, 0) AS total_relationship_value,
COALESCE(val.total_net_assets, 0) AS total_net_assets,
COALESCE(val.total_deposits, 0) AS total_deposits,
COALESCE(val.total_aum, 0) AS total_aum,
period.trv_growth_weekly,
period.trv_growth_monthly,
period.trv_growth_quarterly,
period.trv_growth_yearly,
period.net_assets_growth_weekly,
period.net_assets_growth_monthly,
period.net_assets_growth_quarterly,
period.net_assets_growth_yearly,
COALESCE(adds.portfolios_added_weekly, 0) AS portfolios_added_weekly,
COALESCE(adds.portfolios_added_monthly, 0) AS portfolios_added_monthly,
COALESCE(adds.portfolios_added_quarterly, 0) AS portfolios_added_quarterly,
COALESCE(adds.portfolios_added_yearly, 0) AS portfolios_added_yearly,
COALESCE(adds.clients_added_weekly, 0) AS clients_added_weekly,
COALESCE(adds.clients_added_monthly, 0) AS clients_added_monthly,
COALESCE(adds.clients_added_quarterly, 0) AS clients_added_quarterly,
COALESCE(adds.clients_added_yearly, 0) AS clients_added_yearly,
period.fee_weekly,
period.fee_monthly,
period.fee_quarterly,
period.fee_yearly,
period.gain_loss_weekly,
period.gain_loss_monthly,
period.gain_loss_quarterly,
period.gain_loss_yearly,
CAST('SAR' AS VARCHAR) AS currency_code,
COALESCE(val.updated_at, CAST('1970-01-01 00:00:00 UTC' AS TIMESTAMPTZ)) AS updated_at
FROM alinma_bff.party_advisor_kpi_counts_mv AS cnt
LEFT JOIN alinma_bff.party_advisor_kpi_values_mv AS val
ON val.user_id = cnt.user_id
LEFT JOIN alinma_bff.party_advisor_kpi_period_adds_mv AS adds
ON adds.user_id = cnt.user_id
LEFT JOIN alinma_bff.party_advisor_kpi_period_values_mv AS period
ON period.user_id = cnt.user_id