Query: wellarchitected_workload_milestone_risk_table

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
  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
  case
    when m.milestone_number = 0 then null -- Instead of showing milestone number as 0 for latest, show null to avoid confusion
    else m.milestone_number
  end as "Milestone Number",
  case
    when m.milestone_number = 0 then 'latest'
    else m.milestone_name
  end as "Milestone Name",
  r.high_risks as "High",
  r.medium_risks as "Medium",
  r.no_improvements_risks as "No Improvements",
  r.not_applicable_risks as "Not Applicable",
  to_char(m.recorded_at, 'YYYY-MM-DD HH24:MI') as "Date Saved"
from
  risk_data r
  left join milestone_info m on r.milestone_number = m.milestone_number
order by
  m.recorded_at desc;