-- TRUNCATE service.service_static_url RESTART IDENTITY;

WITH
all_divesite AS (
  SELECT
    divesite.id,
    divesite.country_id,
    divesite.name,
    COALESCE(divesite.region_id, 0) as region_id
  FROM ds.ds_divesite divesite
),
excluded_divesite AS (
  SELECT
    t.name,
    t.country_id,
    max(t.region_id) as region_id
  FROM all_divesite t

  GROUP BY t.name, t.country_id
),
select_divesite AS (
  SELECT
    ad.*
  FROM excluded_divesite ed
    JOIN all_divesite ad ON ad.region_id = ed.region_id
      AND ad.country_id = ed.country_id
      AND ad.name = ed.name
),
divesite_url AS (
  SELECT
    t.human_url,
    'divesite/view?id=' || t.id as default_url
  FROM (
    SELECT
      human_url,
      max(id) as id
    FROM (
      SELECT
          'discover/' || lower(replace(country.name, ' ', '-')) || '/'  || lower(replace(divesite.name, ' ', '-')) as human_url,
          divesite.id
        FROM select_divesite divesite
        JOIN dict.dict_country country ON country.id = divesite.country_id
    ) t
    GROUP BY human_url
  ) t
)

SELECT * FROM divesite_url