CREATE MATERIALIZED VIEW alinma_bff.notes_mv
WITH (
backfill_order=FIXED(olap.clients_dm -> crm_service.notes_dm, olap.portfolios_dm -> crm_service.notes_dm, olap.accounts_dm -> crm_service.notes_dm)
) AS
WITH note_tags_agg AS (
SELECT
note_id,
ARRAY_AGG(tag_id) AS tag_ids
FROM crm_service.note_tags_dm
GROUP BY
note_id
)
SELECT
n.id,
n.content ->> 'text' AS content_text,
n.content ->> 'format' AS content_format,
n.created_at,
n.updated_at,
NOT n.archived_at IS NULL AS is_archived,
n.archived_at,
n.resource_client_id AS client_id,
n.resource_portfolio_id AS portfolio_id,
n.resource_account_id AS account_id,
n.resource_draft_account_id AS draft_account_id,
n.resource_party_id AS party_id,
CASE
WHEN NOT n.resource_client_id IS NULL
THEN 'CLIENT'
WHEN NOT n.resource_portfolio_id IS NULL
THEN 'PORTFOLIO'
WHEN NOT n.resource_account_id IS NULL
THEN 'ACCOUNT'
WHEN NOT n.resource_draft_account_id IS NULL
THEN 'DRAFT_ACCOUNT'
WHEN NOT n.resource_party_id IS NULL
THEN 'PARTY'
ELSE NULL
END AS entity_type,
COALESCE(
n.resource_client_id,
n.resource_portfolio_id,
n.resource_account_id,
n.resource_draft_account_id,
n.resource_party_id
) AS entity_id,
COALESCE(nt.tag_ids, CAST(ARRAY[] AS VARCHAR[])) AS tag_ids,
n.created_by AS created_by_id,
n.updated_by AS updated_by_id,
c.display_name AS client_display_name,
c.local_display_name AS client_local_display_name,
c.preferred_name AS client_preferred_name,
c.customer_identification_file AS client_customer_identification_file,
p.number AS portfolio_number,
COALESCE(po.owners, CAST('[]' AS JSONB)) AS portfolio_owners,
a.name AS account_name,
a.number AS account_number,
COALESCE(ao.owners, CAST('[]' AS JSONB)) AS account_owners
FROM crm_service.notes_dm AS n
LEFT JOIN note_tags_agg AS nt
ON nt.note_id = n.id
LEFT JOIN olap.clients_dm FOR SYSTEM_TIME AS OF PROCTIME() AS c
ON c.id = n.resource_client_id
LEFT JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
ON p.portfolio_id = n.resource_portfolio_id
LEFT JOIN alinma_bff.portfolio_owners_mv AS po
ON po.portfolio_id = n.resource_portfolio_id
LEFT JOIN olap.accounts_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
ON a.account_id = n.resource_account_id
LEFT JOIN alinma_bff.account_owners_mv AS ao
ON ao.account_id = n.resource_account_id