CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_portfolio_fee_mv AS
WITH recent_fees AS (
SELECT
portfolio_id,
dim_transaction_date,
net_flow_system_currency
FROM alinma_bff.report_flows_daily_mv AS report_flows_daily_mv_next
WHERE
transaction_type = 'FEE'
AND account_group_type = 'all'
AND CAST(dim_transaction_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '400 DAYS'
), fee_weekly AS (
SELECT
portfolio_id,
SUM(COALESCE(net_flow_system_currency, 0)) AS fee
FROM recent_fees
WHERE
CAST(dim_transaction_date AS TIMESTAMPTZ) >= DATE_TRUNC('WEEK', CURRENT_TIMESTAMP)
GROUP BY
portfolio_id
), fee_monthly AS (
SELECT
portfolio_id,
SUM(COALESCE(net_flow_system_currency, 0)) AS fee
FROM recent_fees
WHERE
CAST(dim_transaction_date AS TIMESTAMPTZ) >= DATE_TRUNC('MONTH', CURRENT_TIMESTAMP)
GROUP BY
portfolio_id
), fee_quarterly AS (
SELECT
portfolio_id,
SUM(COALESCE(net_flow_system_currency, 0)) AS fee
FROM recent_fees
WHERE
CAST(dim_transaction_date AS TIMESTAMPTZ) >= DATE_TRUNC('QUARTER', CURRENT_TIMESTAMP)
GROUP BY
portfolio_id
), fee_yearly AS (
SELECT
portfolio_id,
SUM(COALESCE(net_flow_system_currency, 0)) AS fee
FROM recent_fees
WHERE
CAST(dim_transaction_date AS TIMESTAMPTZ) >= DATE_TRUNC('YEAR', CURRENT_TIMESTAMP)
GROUP BY
portfolio_id
), combined AS (
SELECT
portfolio_id,
fee AS fee_weekly,
CAST(0 AS DECIMAL) AS fee_monthly,
CAST(0 AS DECIMAL) AS fee_quarterly,
CAST(0 AS DECIMAL) AS fee_yearly
FROM fee_weekly
UNION ALL
SELECT
portfolio_id,
CAST(0 AS DECIMAL),
fee,
CAST(0 AS DECIMAL),
CAST(0 AS DECIMAL)
FROM fee_monthly
UNION ALL
SELECT
portfolio_id,
CAST(0 AS DECIMAL),
CAST(0 AS DECIMAL),
fee,
CAST(0 AS DECIMAL)
FROM fee_quarterly
UNION ALL
SELECT
portfolio_id,
CAST(0 AS DECIMAL),
CAST(0 AS DECIMAL),
CAST(0 AS DECIMAL),
fee
FROM fee_yearly
)
SELECT
portfolio_id,
SUM(fee_weekly) AS fee_weekly,
SUM(fee_monthly) AS fee_monthly,
SUM(fee_quarterly) AS fee_quarterly,
SUM(fee_yearly) AS fee_yearly
FROM combined
GROUP BY
portfolio_id