CREATE SINK alpheya_experience_bff.party_clients_sink AS
SELECT
pc.party_id,
pc.party_type,
pc.base_currency_code,
pc.created_at,
pc.onboarding_date,
pc.closing_date,
pc.customer_relationship_status,
COALESCE(ref.reference_identifiers, CAST('[]' AS JSONB)) AS reference_identifiers,
pc.account_group_type,
pc.display_name,
pc.local_display_name,
pc.preferred_name,
pc.prefix,
pc.suffix,
pc.first_name,
pc.last_name,
pc.status_label_id,
pc.status_name_en,
pc.status_name_ar,
pc.status_color,
pc.currency_code,
pc.currency_name_en,
pc.currency_name_ar,
pc.currency_symbol,
pc.portfolio_count,
pc.contact_count,
pc.task_active_count,
pc.task_high_priority_count,
pc.market_value,
pc.fair_value,
pc.relationship_value,
pc.fair_relationship_value,
pc.market_value_system_currency,
pc.fair_value_system_currency,
pc.relationship_value_system_currency,
pc.fair_relationship_value_system_currency,
pc.aum_value,
pc.fair_aum_value,
pc.aum_value_system_currency,
pc.fair_aum_value_system_currency,
pc.total_fees,
pc.transactional_fees,
pc.non_transactional_fees,
pc.total_fees_system_currency,
pc.transactional_fees_system_currency,
pc.non_transactional_fees_system_currency
FROM alpheya_experience_bff.book_of_business_party_clients_mv AS pc
LEFT JOIN alpheya_experience_bff.party_reference_identifiers_mv AS ref
ON pc.party_id = ref.party_id
WITH (
connector='postgres',
host='psql-nonprod-uae-derived.postgres.database.azure.com',
port='6432',
user='migration_user',
password='***',
database='ai-hub-int-alpheya-exp-bff',
table='party_clients',
schema='advisor_portal_bff',
type='upsert',
force_compaction='true',
primary_key='party_id,account_group_type'
)