WITH cte AS (
  SELECT
    ansm.nom_collectivite,
    ansm.noms_communes_associees,
    groupements.echelon_geo,
    CASE
      WHEN groupements.echelon_geo = 'commune'
      THEN groupements.insee_geo
      ELSE NULL
    END AS insee_com,
    CASE
      WHEN groupements.echelon_geo = 'commune'
      THEN commune.insee_dep
      WHEN groupements.echelon_geo = 'epci'
      THEN c2.insee_dep
      WHEN groupements.echelon_geo = 'departement'
      THEN groupements.insee_geo
      ELSE NULL
    END AS insee_dep,
    CASE
      WHEN groupements.echelon_geo = 'commune'
      THEN commune.insee_reg
      WHEN groupements.echelon_geo = 'epci'
      THEN c2.insee_reg
      WHEN groupements.echelon_geo = 'departement'
      THEN departement.insee_reg
      WHEN groupements.echelon_geo = 'region'
      THEN groupements.insee_geo
      ELSE NULL
    END AS insee_reg
  FROM ansm
  LEFT JOIN groupements_2024 AS groupements
    ON ansm.siren_collectivite = groupements.siren_groupement
    AND CASE
      WHEN groupements.siren_groupement IN ('217500016', '219711231', '219711272')
      THEN groupements.echelon_geo = 'commune'
      WHEN groupements.siren_groupement IN ('200052678', '200055507')
      THEN groupements.echelon_geo = 'departement'
      ELSE TRUE
    END
  LEFT JOIN commune
    ON groupements.echelon_geo = 'commune' AND commune.insee_com = groupements.insee_geo
  LEFT JOIN departement
    ON groupements.echelon_geo = 'departement'
    AND departement.insee_dep = groupements.insee_geo
  LEFT JOIN groupements AS g2
    ON groupements.echelon_geo = 'epci' AND g2.siren_groupement = groupements.cheflieu
  LEFT JOIN commune AS c2
    ON groupements.echelon_geo = 'epci' AND c2.insee_com = g2.insee_geo
)
SELECT
  nom_collectivite,
  ARRAY_TO_STRING(noms_communes_associees, CAST(', ' AS TEXT)) AS noms_communes_associees
FROM cte
WHERE
  NOT noms_communes_associees IS NULL
ORDER BY
  nom_collectivite