Healthcare

CREATE SOURCE claims_submitted WITH (
  connector = 'kafka',
  topic = 'claims.submitted',
  properties.bootstrap.server = 'broker:9092'
) FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW eligibility_check AS
SELECT
  c.claim_id,
  c.member_id,
  c.provider_npi,
  c.cpt_code,
  c.diagnosis_icd,
  c.billed_amount,
  c.service_date,
  c.ts,
  CASE
    WHEN e.member_id IS NULL THEN 'MEMBER_NOT_FOUND'
    WHEN c.service_date < e.coverage_start OR c.service_date > e.coverage_end THEN 'NOT_COVERED_DATE'
    WHEN pb.cpt_code IS NULL THEN 'PROCEDURE_NOT_COVERED'
    ELSE 'ELIGIBLE'
  END AS eligibility_status,
  e.plan_id,
  pb.requires_prior_auth
FROM claims_submitted c
LEFT JOIN member_enrollment e
  ON c.member_id = e.member_id
  AND c.service_date BETWEEN e.coverage_start AND e.coverage_end
LEFT JOIN plan_benefits pb
  ON e.plan_id = pb.plan_id
  AND c.cpt_code = pb.cpt_code;