RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW opportunity.account_bond_maturity_breaches_mv AS
WITH bond_holdings AS (
  SELECT
    b.asset_id,
    b.maturity_date,
    h.account_id,
    'GET_ACCOUNT_DAYS_TO_BOND_ASSET_MATURITY_DATE' AS activity_name
  FROM asset_service.bond_assets_dm AS b
  JOIN insights.holding_values_latest_mv AS h
    ON h.asset_id = b.asset_id AND h.type = 'ASSET'
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = h.account_id
)
SELECT
  c.opportunity_id,
  b.account_id AS resource_id,
  c.as_of_date AS fact_date,
  b.asset_id,
  b.maturity_date,
  ytm.yield_to_maturity,
  (
    b.maturity_date - c.as_of_date
  ) AS days_to_maturity,
  COALESCE(iss.name_en, '') AS issuer_name_en,
  COALESCE(iss.name_ar, iss.name_en, '') AS issuer_name_ar
FROM bond_holdings AS b
JOIN opportunity.opportunity_conditions_mv AS c
  ON c.activity_name = b.activity_name
LEFT JOIN insights.asset_latest_yield_to_maturity_mv AS ytm
  ON ytm.asset_id = b.asset_id
LEFT JOIN asset_service.assets_dm AS a
  ON a.id = b.asset_id
LEFT JOIN asset_service.issuers_dm AS iss
  ON iss.id = a.issuer_id
WHERE
  NOT c.as_of_date IS NULL
  AND b.maturity_date > c.as_of_date
  AND CASE c.op
    WHEN 'GTE'
    THEN (
      b.maturity_date - c.as_of_date
    ) >= c.threshold
    WHEN 'GT'
    THEN (
      b.maturity_date - c.as_of_date
    ) > c.threshold
    WHEN 'LTE'
    THEN (
      b.maturity_date - c.as_of_date
    ) <= c.threshold
    WHEN 'LT'
    THEN (
      b.maturity_date - c.as_of_date
    ) < c.threshold
    WHEN 'EQ'
    THEN (
      b.maturity_date - c.as_of_date
    ) = c.threshold
    ELSE FALSE
  END