Query: wellarchitected_workload_risks_by_milestone

with milestone_info as (
  select
    workload_id,
    milestone_number,
    milestone_name,
    recorded_at
  from
    aws_wellarchitected_milestone
  where
    workload_id = $1

  union

  select
    $1 as workload_id,
    0 as milestone_number, -- Lens review API returns latest milestone as 0
    'latest' as milestone_name,
    current_timestamp as recorded_at
), lens_review as (
  select
    *
  from
    aws_wellarchitected_lens_review
  where
    milestone_number in (select milestone_number from aws_wellarchitected_milestone where workload_id = $1 union select 0 as milestone_number) -- Latest milestone is returned as 0
    and workload_id = (select workload_id from aws_wellarchitected_workload where workload_id = $1)
), risk_data as (
  select
    milestone_number,
    sum((risk_counts ->> 'HIGH')::int) as high_risks,
    sum((risk_counts ->> 'MEDIUM')::int) as medium_risks,
    sum((risk_counts ->> 'NONE')::int) as no_improvements_risks,
    sum((risk_counts ->> 'NOT_APPLICABLE')::int) as not_applicable_risks
    --sum((risk_counts ->> 'UNANSWERED')::int) as unanswered_risks
  from
    lens_review as r
  where
    r.lens_arn = any(string_to_array($2, ','))
  group by
    r.milestone_number
  order by
    r.milestone_number
) select
  m.milestone_name,
  r.high_risks,
  r.medium_risks,
  r.no_improvements_risks,
  r.not_applicable_risks
from
  risk_data r
  left join milestone_info m
    on r.milestone_number = m.milestone_number
order by
  m.recorded_at;