CREATE MATERIALIZED VIEW search.items_mv
WITH (
backfill_order=FIXED(olap.labels_dm -> olap.clients_dm, olap.labels_dm -> olap.accounts_dm, olap.labels_dm -> olap.portfolios_dm, olap.product_types_dm -> olap.accounts_dm, olap.service_types_dm -> olap.portfolios_dm)
) AS
SELECT
entity_id,
entity_type,
ARRAY_AGG(DISTINCT val) FILTER(WHERE
NOT val IS NULL AND val <> '' AND TRIM(val) <> '') AS search_term_en,
ARRAY_AGG(DISTINCT ar_val) FILTER(WHERE
NOT ar_val IS NULL AND ar_val <> '' AND TRIM(ar_val) <> '') AS search_term_ar,
ARRAY_AGG(DISTINCT filter_val) FILTER(WHERE
NOT filter_val IS NULL) AS filters
FROM (
SELECT
a.account_id AS entity_id,
'account' AS entity_type,
u.val,
CAST(NULL AS VARCHAR) AS ar_val,
CAST(NULL AS VARCHAR) AS filter_val
FROM (
SELECT
account_id,
name AS dec_name,
number AS dec_number
FROM olap.accounts_dm
WHERE
disabled_at IS NULL
) AS a
CROSS JOIN UNNEST(ARRAY[
a.dec_name,
REPLACE(a.dec_name, '-', ''),
a.dec_number,
REPLACE(a.dec_number, '-', '')
]) AS u(val)
UNION ALL
SELECT
c.account_id,
'account',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
atc.account_id,
c.display_name AS dec_display_name,
c.local_display_name AS dec_local_display_name,
c.preferred_name AS dec_preferred_name,
c.customer_identification_file AS dec_cif
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
WHERE
atc.disabled_at IS NULL
) AS c
CROSS JOIN UNNEST(ARRAY[
c.dec_display_name,
REPLACE(c.dec_display_name, '-', ''),
c.dec_local_display_name,
REPLACE(c.dec_local_display_name, '-', ''),
c.dec_preferred_name,
REPLACE(c.dec_preferred_name, '-', ''),
c.dec_cif,
REPLACE(c.dec_cif, '-', '')
]) AS u(val)
UNION ALL
SELECT
cc.account_id,
'account',
u.val,
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR)
FROM (
SELECT
atc.account_id,
cc.value AS dec_value
FROM olap.accounts_to_clients_dm AS atc
JOIN olap.clients_contacts_dm AS cc
ON cc.client_id = atc.client_id AND cc.disabled_at IS NULL
WHERE
atc.disabled_at IS NULL
) AS cc
CROSS JOIN UNNEST(ARRAY[cc.dec_value, REPLACE(cc.dec_value, '-', '')]) AS u(val)
UNION ALL
SELECT
c.id,
'client',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
id,
display_name AS dec_display_name,
local_display_name AS dec_local_display_name,
preferred_name AS dec_preferred_name,
customer_identification_file AS dec_cif
FROM olap.clients_dm
WHERE
closing_date IS NULL
) AS c
CROSS JOIN UNNEST(ARRAY[
c.dec_display_name,
REPLACE(c.dec_display_name, '-', ''),
c.dec_local_display_name,
REPLACE(c.dec_local_display_name, '-', ''),
c.dec_preferred_name,
REPLACE(c.dec_preferred_name, '-', ''),
c.dec_cif,
REPLACE(c.dec_cif, '-', '')
]) AS u(val)
UNION ALL
SELECT
cc.client_id,
'client',
u.val,
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR)
FROM (
SELECT
client_id,
value AS dec_value
FROM olap.clients_contacts_dm
WHERE
disabled_at IS NULL
) AS cc
CROSS JOIN UNNEST(ARRAY[cc.dec_value, REPLACE(cc.dec_value, '-', '')]) AS u(val)
UNION ALL
SELECT
a.client_id,
'client',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
atc.client_id,
a.name AS dec_name,
a.number AS dec_number
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
WHERE
atc.disabled_at IS NULL
) AS a
CROSS JOIN UNNEST(ARRAY[
a.dec_name,
REPLACE(a.dec_name, '-', ''),
a.dec_number,
REPLACE(a.dec_number, '-', '')
]) AS u(val)
UNION ALL
SELECT
p.client_id,
'client',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
cp.client_id,
p.name AS dec_name,
p.number AS dec_number
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
WHERE
cp.disabled_at IS NULL
) AS p
CROSS JOIN UNNEST(ARRAY[
p.dec_name,
REPLACE(p.dec_name, '-', ''),
p.dec_number,
REPLACE(p.dec_number, '-', '')
]) AS u(val)
UNION ALL
SELECT
pi.entity_id,
pi.entity_type,
pi.val,
pi.ar_val,
pi.filter_val
FROM search.party_items_mv AS pi
UNION ALL
SELECT
p.portfolio_id,
'portfolio',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
portfolio_id,
name AS dec_name,
number AS dec_number
FROM olap.portfolios_dm
WHERE
disabled_at IS NULL
) AS p
CROSS JOIN UNNEST(ARRAY[
p.dec_name,
REPLACE(p.dec_name, '-', ''),
p.dec_number,
REPLACE(p.dec_number, '-', '')
]) AS u(val)
UNION ALL
SELECT
a.portfolio_id,
'portfolio',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
atp.portfolio_id,
a.name AS dec_name,
a.number AS dec_number
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
WHERE
atp.disabled_at IS NULL
) AS a
CROSS JOIN UNNEST(ARRAY[
a.dec_name,
REPLACE(a.dec_name, '-', ''),
a.dec_number,
REPLACE(a.dec_number, '-', '')
]) AS u(val)
UNION ALL
SELECT
c.portfolio_id,
'portfolio',
u.val,
u.val,
CAST(NULL AS VARCHAR)
FROM (
SELECT
cp.portfolio_id,
c.display_name AS dec_display_name,
c.local_display_name AS dec_local_display_name,
c.preferred_name AS dec_preferred_name,
c.customer_identification_file AS dec_cif
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
WHERE
cp.disabled_at IS NULL
) AS c
CROSS JOIN UNNEST(ARRAY[
c.dec_display_name,
REPLACE(c.dec_display_name, '-', ''),
c.dec_local_display_name,
REPLACE(c.dec_local_display_name, '-', ''),
c.dec_preferred_name,
REPLACE(c.dec_preferred_name, '-', ''),
c.dec_cif,
REPLACE(c.dec_cif, '-', '')
]) AS u(val)
UNION ALL
SELECT
cc.portfolio_id,
'portfolio',
u.val,
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR)
FROM (
SELECT
cp.portfolio_id,
cc.value AS dec_value
FROM olap.clients_portfolios_dm AS cp
JOIN olap.clients_contacts_dm AS cc
ON cc.client_id = cp.client_id AND cc.disabled_at IS NULL
WHERE
cp.disabled_at IS NULL
) AS cc
CROSS JOIN UNNEST(ARRAY[cc.dec_value, REPLACE(cc.dec_value, '-', '')]) AS u(val)
UNION ALL
SELECT
a.id,
'asset',
u.val,
CAST(NULL AS VARCHAR),
LOWER(a.type)
FROM asset_service.assets_dm AS a
CROSS JOIN UNNEST(ARRAY[
a.name_en,
REPLACE(a.name_en, '-', ''),
a.ticker,
REPLACE(a.ticker, '-', ''),
a.isin,
REPLACE(a.isin, '-', ''),
a.ric,
REPLACE(a.ric, '-', ''),
a.cusip,
REPLACE(a.cusip, '-', ''),
a.sedol,
REPLACE(a.sedol, '-', ''),
a.figi,
REPLACE(a.figi, '-', '')
]) AS u(val)
WHERE
a.disabled_at IS NULL
UNION ALL
SELECT
a.id,
'asset',
CAST(NULL AS VARCHAR),
a.name_ar,
LOWER(a.type)
FROM asset_service.assets_dm AS a
WHERE
a.disabled_at IS NULL
UNION ALL
SELECT
c.id,
'client',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'client_type:' || LOWER(CAST(c.type AS TEXT))
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL
UNION ALL
SELECT
c.id,
'client',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'status:' || LOWER(sl.name_en)
FROM olap.clients_dm AS c
JOIN olap.labels_dm AS sl
ON c.status_label_id = sl.label_id
WHERE
c.closing_date IS NULL
UNION ALL
SELECT
et.entity_id,
'client',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'team:' || et.team_id
FROM olap.entity_to_teams_dm AS et
JOIN olap.clients_dm AS c
ON c.id = et.entity_id AND c.closing_date IS NULL
WHERE
et.entity_type = 'CLIENT'
AND et.disabled_at IS NULL
AND et.effective_end_date IS NULL
UNION ALL
SELECT
a.account_id,
'account',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'account_type:' || LOWER(CAST(pt.type AS TEXT))
FROM olap.accounts_dm AS a
JOIN olap.product_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS pt
ON a.product_type_id = pt.product_type_id
WHERE
a.disabled_at IS NULL
UNION ALL
SELECT
a.account_id,
'account',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'product:' || LOWER(TRIM(pt.name_en))
FROM olap.accounts_dm AS a
JOIN olap.product_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS pt
ON a.product_type_id = pt.product_type_id
WHERE
a.disabled_at IS NULL AND NOT pt.name_en IS NULL AND TRIM(pt.name_en) <> ''
UNION ALL
SELECT
a.account_id,
'account',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'status:' || LOWER(sl.name_en)
FROM olap.accounts_dm AS a
JOIN olap.labels_dm AS sl
ON a.status_label_id = sl.label_id
WHERE
a.disabled_at IS NULL
UNION ALL
SELECT
atc.account_id,
'account',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'team:' || et.team_id
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.entity_to_teams_dm AS et
ON et.entity_id = atc.client_id
AND et.entity_type = 'CLIENT'
AND et.disabled_at IS NULL
AND et.effective_end_date IS NULL
WHERE
atc.disabled_at IS NULL AND atc.effective_end_date IS NULL
UNION ALL
SELECT
p.portfolio_id,
'portfolio',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'portfolio_type:' || LOWER(CAST(st.type AS TEXT))
FROM olap.portfolios_dm AS p
JOIN olap.service_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS st
ON p.service_type_id = st.service_type_id
WHERE
p.disabled_at IS NULL
UNION ALL
SELECT
p.portfolio_id,
'portfolio',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'status:' || LOWER(sl.name_en)
FROM olap.portfolios_dm AS p
JOIN olap.labels_dm AS sl
ON p.status_label_id = sl.label_id
WHERE
p.disabled_at IS NULL
UNION ALL
SELECT
et.entity_id,
'portfolio',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'team:' || et.team_id
FROM olap.entity_to_teams_dm AS et
JOIN olap.portfolios_dm AS p
ON p.portfolio_id = et.entity_id AND p.disabled_at IS NULL
WHERE
et.entity_type = 'PORTFOLIO'
AND et.disabled_at IS NULL
AND et.effective_end_date IS NULL
UNION ALL
SELECT
cp.portfolio_id,
'portfolio',
CAST(NULL AS VARCHAR),
CAST(NULL AS VARCHAR),
'team:' || et.team_id
FROM olap.clients_portfolios_dm AS cp
JOIN olap.entity_to_teams_dm AS et
ON et.entity_id = cp.client_id
AND et.entity_type = 'CLIENT'
AND et.disabled_at IS NULL
AND et.effective_end_date IS NULL
WHERE
cp.disabled_at IS NULL AND cp.effective_end_date IS NULL
UNION ALL
SELECT
e.target_entity_id,
e.target_entity_type,
t.val,
t.ar_val,
CAST(NULL AS VARCHAR)
FROM search.active_identifier_edges_mv AS e
JOIN search.olap_reference_identifier_terms_mv AS t
ON t.owner_entity_id = e.owner_entity_id
AND t.owner_entity_type = e.owner_entity_type
WHERE
e.target_entity_type IN ('account', 'client', 'portfolio')
UNION ALL
SELECT
e.target_entity_id,
e.target_entity_type,
t.val,
t.ar_val,
CAST(NULL AS VARCHAR)
FROM search.party_identifier_edges_mv AS e
JOIN search.party_reference_identifier_terms_mv AS t
ON t.owner_entity_id = e.owner_entity_id
AND t.owner_entity_type = e.owner_entity_type
) AS sub
GROUP BY
entity_id,
entity_type