CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_period_series_mv AS
SELECT
user_id,
period_kind,
period_start,
CAST('RELATIONSHIP_VALUE' AS VARCHAR) AS metric,
CAST(relationship_value AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_balance_series_mv AS advisor_kpi_period_balance_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('NET_ASSETS' AS VARCHAR) AS metric,
CAST(net_assets AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_balance_series_mv AS advisor_kpi_period_balance_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('GAIN_LOSS' AS VARCHAR) AS metric,
CAST(gain_loss AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_pnl_series_mv AS advisor_kpi_period_pnl_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('FEE' AS VARCHAR) AS metric,
CAST(fee AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_fee_series_mv AS advisor_kpi_period_fee_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('PORTFOLIO_COUNT' AS VARCHAR) AS metric,
CAST(portfolios_added AS DECIMAL) AS value,
CAST(NULL AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_user_bucket_adds_mv AS advisor_kpi_user_bucket_adds_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('CLIENT_COUNT' AS VARCHAR) AS metric,
CAST(clients_added AS DECIMAL) AS value,
CAST(NULL AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_user_bucket_adds_mv AS advisor_kpi_user_bucket_adds_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('TOTAL_AUM' AS VARCHAR) AS metric,
CAST(aum AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_account_value_series_mv AS advisor_kpi_period_account_value_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('TOTAL_DEPOSITS' AS VARCHAR) AS metric,
CAST(deposits AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_account_value_series_mv AS advisor_kpi_period_account_value_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('TOTAL_FINANCING' AS VARCHAR) AS metric,
CAST(financing AS DECIMAL) AS value,
CAST('SAR' AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_period_account_value_series_mv AS advisor_kpi_period_account_value_series_mv_next
UNION ALL
SELECT
user_id,
period_kind,
period_start,
CAST('ACCOUNT_COUNT' AS VARCHAR) AS metric,
CAST(accounts_added AS DECIMAL) AS value,
CAST(NULL AS VARCHAR) AS currency_code
FROM alinma_bff.advisor_kpi_user_bucket_adds_mv AS advisor_kpi_user_bucket_adds_mv_next