CREATE MATERIALIZED VIEW alpheya_agent.tasks_mv AS
SELECT
t.task_id,
t.name,
t.description,
t.due_date,
t.status,
t.priority,
t.assignee_id,
t.created_by,
t.updated_by,
t.created_at,
t.updated_at,
t.is_automated,
t.automation_type,
t.resource_account_id,
t.resource_client_id,
t.resource_portfolio_id,
t.resource_party_id,
t.resource_draft_account_id,
t.resource_referral_id,
CASE
WHEN NOT t.resource_client_id IS NULL
THEN 'CLIENT'
WHEN NOT t.resource_portfolio_id IS NULL
THEN 'PORTFOLIO'
WHEN NOT t.resource_account_id IS NULL
THEN 'ACCOUNT'
WHEN NOT t.resource_referral_id IS NULL
THEN 'REFERRAL'
WHEN NOT t.resource_draft_account_id IS NULL
THEN 'DRAFT_ACCOUNT'
WHEN NOT t.resource_party_id IS NULL
THEN 'PARTY'
ELSE NULL
END AS resource_type,
COALESCE(
t.resource_client_id,
t.resource_portfolio_id,
t.resource_account_id,
t.resource_referral_id,
t.resource_draft_account_id,
t.resource_party_id
) AS resource_id
FROM crm_service.tasks_dm AS t
WHERE
t.disabled_at IS NULL