CREATE MATERIALIZED VIEW search.active_identifier_edges_mv AS
SELECT
a.account_id AS target_entity_id,
'account' AS target_entity_type,
a.account_id AS owner_entity_id,
'account' AS owner_entity_type
FROM olap.accounts_dm AS a
WHERE
a.disabled_at IS NULL
UNION ALL
SELECT
c.id,
'client',
c.id,
'client'
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL
UNION ALL
SELECT
p.portfolio_id,
'portfolio',
p.portfolio_id,
'portfolio'
FROM olap.portfolios_dm AS p
WHERE
p.disabled_at IS NULL
UNION ALL
SELECT
atc.account_id,
'account',
atc.client_id,
'client'
FROM olap.accounts_to_clients_dm AS atc
JOIN olap.accounts_dm AS a
ON a.account_id = atc.account_id AND a.disabled_at IS NULL
JOIN olap.clients_dm AS c
ON c.id = atc.client_id AND c.closing_date IS NULL
WHERE
atc.disabled_at IS NULL AND atc.effective_end_date IS NULL
UNION ALL
SELECT
atp.account_id,
'account',
atp.portfolio_id,
'portfolio'
FROM olap.account_to_portfolios_dm AS atp
JOIN olap.accounts_dm AS a
ON a.account_id = atp.account_id AND a.disabled_at IS NULL
JOIN olap.portfolios_dm AS p
ON p.portfolio_id = atp.portfolio_id AND p.disabled_at IS NULL
WHERE
atp.disabled_at IS NULL AND atp.effective_end_date IS NULL
UNION ALL
SELECT
atc.client_id,
'client',
atc.account_id,
'account'
FROM olap.accounts_to_clients_dm AS atc
JOIN olap.clients_dm AS c
ON c.id = atc.client_id AND c.closing_date IS NULL
JOIN olap.accounts_dm AS a
ON a.account_id = atc.account_id AND a.disabled_at IS NULL
WHERE
atc.disabled_at IS NULL AND atc.effective_end_date IS NULL
UNION ALL
SELECT
cp.client_id,
'client',
cp.portfolio_id,
'portfolio'
FROM olap.clients_portfolios_dm AS cp
JOIN olap.clients_dm AS c
ON c.id = cp.client_id AND c.closing_date IS NULL
JOIN olap.portfolios_dm AS p
ON p.portfolio_id = cp.portfolio_id AND p.disabled_at IS NULL
WHERE
cp.disabled_at IS NULL AND cp.effective_end_date IS NULL
UNION ALL
SELECT
cp.portfolio_id,
'portfolio',
cp.client_id,
'client'
FROM olap.clients_portfolios_dm AS cp
JOIN olap.portfolios_dm AS p
ON p.portfolio_id = cp.portfolio_id AND p.disabled_at IS NULL
JOIN olap.clients_dm AS c
ON c.id = cp.client_id AND c.closing_date IS NULL
WHERE
cp.disabled_at IS NULL AND cp.effective_end_date IS NULL
UNION ALL
SELECT
atp.portfolio_id,
'portfolio',
atp.account_id,
'account'
FROM olap.account_to_portfolios_dm AS atp
JOIN olap.portfolios_dm AS p
ON p.portfolio_id = atp.portfolio_id AND p.disabled_at IS NULL
JOIN olap.accounts_dm AS a
ON a.account_id = atp.account_id AND a.disabled_at IS NULL
WHERE
atp.disabled_at IS NULL AND atp.effective_end_date IS NULL
UNION ALL
SELECT
phe.party_id,
'party',
phe.entity_id,
phe.entity_type
FROM search.party_holder_edges_mv AS phe