with recursive ordered as (
  select
    product_id,
    sort_order,
    row_number() over (order by sort_order, updated_at desc, product_id) as row_index
  from agent_product_details
  where status <> 'deleted'
),
normalized as (
  select
    row_index,
    product_id,
    sort_order as normalized_sort_order
  from ordered
  where row_index = 1
  union all
  select
    ordered.row_index,
    ordered.product_id,
    greatest(ordered.sort_order, normalized.normalized_sort_order + 1) as normalized_sort_order
  from normalized
  join ordered on ordered.row_index = normalized.row_index + 1
)
update agent_product_details details
set sort_order = normalized.normalized_sort_order,
    updated_at = now()
from normalized
where details.product_id = normalized.product_id
  and details.sort_order <> normalized.normalized_sort_order;

with recursive ordered as (
  select
    product_id,
    sort_order,
    row_number() over (order by sort_order, updated_at desc, product_id) as row_index
  from course_product_details
  where status <> 'deleted'
),
normalized as (
  select
    row_index,
    product_id,
    sort_order as normalized_sort_order
  from ordered
  where row_index = 1
  union all
  select
    ordered.row_index,
    ordered.product_id,
    greatest(ordered.sort_order, normalized.normalized_sort_order + 1) as normalized_sort_order
  from normalized
  join ordered on ordered.row_index = normalized.row_index + 1
)
update course_product_details details
set sort_order = normalized.normalized_sort_order,
    updated_at = now()
from normalized
where details.product_id = normalized.product_id
  and details.sort_order <> normalized.normalized_sort_order;

with recursive ordered as (
  select
    product_id,
    sort_order,
    row_number() over (order by sort_order, updated_at desc, product_id) as row_index
  from data_product_details
  where status <> 'deleted'
),
normalized as (
  select
    row_index,
    product_id,
    sort_order as normalized_sort_order
  from ordered
  where row_index = 1
  union all
  select
    ordered.row_index,
    ordered.product_id,
    greatest(ordered.sort_order, normalized.normalized_sort_order + 1) as normalized_sort_order
  from normalized
  join ordered on ordered.row_index = normalized.row_index + 1
)
update data_product_details details
set sort_order = normalized.normalized_sort_order,
    updated_at = now()
from normalized
where details.product_id = normalized.product_id
  and details.sort_order <> normalized.normalized_sort_order;
