with node_group_using_launch_template_image_id as (
  select
    g.arn as node_group_arn,
    v.image_id as image_id
  from
    aws_eks_node_group as g
    left join aws_ec2_launch_template_version as v on v.launch_template_id = g.launch_template ->> 'Id'
    and v.version_number = (g.launch_template ->> 'Version') :: int
  where
    g.launch_template is not null
),
ami_architecture as (
  select
    node_group_arn,
    architecture,
    case
      when s.platform_details = 'Linux/UNIX' then 'linux'
      else platform_details
    end as platform
  from
    node_group_using_launch_template_image_id as i
    left join aws_ec2_ami_shared as s on s.image_id = i.image_id
  where
    architecture is not null
  union
  select
    node_group_arn,
    architecture,
    case
      when a.platform_details = 'Linux/UNIX' then 'linux'
      else platform_details
    end as platform
  from
    node_group_using_launch_template_image_id as i
    left join aws_ec2_ami as a on a.image_id = i.image_id
  where
    architecture is not null
)
select
  concat(
    g.nodegroup_name,
    ' [',
    g.region,
    '/',
    g.account_id,
    ']'
  ) as title,
  g.cluster_name,
  g.nodegroup_name,
  g.region,
  g.sp_connection_name as conn
from
  aws_eks_node_group as g
  left join ami_architecture as a on a.node_group_arn = g.arn
where
  ami_type = 'CUSTOM%'
  and a.architecture <> 'arm_64'
  and a.platform = 'linux';