create or replace table day05_data as
  select
    if('-' in column0, SPLIT(column0, '-')[1]::long, NULL) as range_start,
    if('-' in column0, SPLIT(column0, '-')[2]::long, NULL) as range_end,
    if('-' not in column0, column0::long, NULL) as product_id
  from read_csv(
       'inputs/05/input.txt',
    header = false)
  where (range_start is not null and range_end is not null) or product_id is not null
;

create or replace table day05_test as
  select
    if('-' in column0, SPLIT(column0, '-')[1]::long, NULL) as range_start,
    if('-' in column0, SPLIT(column0, '-')[2]::long + 1, NULL) as range_end,
    if('-' not in column0, column0::long, NULL) as product_id
  from read_csv(
       'inputs/05/test.txt',
    header = false)
  where (range_start is not null and range_end is not null) or product_id is not null
;

create or replace table day05_ranges as
  select row_number() over (order by range_start) as range_id, range_start, range_end
  from day05_data
  where product_id is null;
create or replace table day05_products as
  select product_id
  from day05_data
  where product_id is not null;

select count(distinct product_id) as part1
from day05_products
inner join day05_ranges
  on product_id between range_start and range_end
;

create or replace table day05_cleaned_ranges as
with recursive seq(step, range_id, range_start, range_end) as (
  select 0 as step,
    range_id,
    range_start,
    range_end
  from day05_ranges

  union all (

  with res as (
    select distinct
      s1.step + 1 as step,
      list_min([s1.range_start::long, coalesce(s2.range_start, s1.range_start)]) as range_start,
      list_max([s1.range_end, coalesce(s2.range_end, s1.range_end)]) as range_end,
      s2.step is not null as changed_smth
    from seq s1
    left join seq s2
    on s1.range_id != s2.range_id and
    (s1.range_start between s2.range_start and s2.range_end
      or s1.range_end between s2.range_start and s2.range_end)
    order by range_start
  )
  select
    step,
    row_number() over () as range_id,
    range_start,
    range_end
  from res
  where (select count(1) filter (where changed_smth is true) from res) > 0
  and step < 20
  group by step, range_start, range_end
))
select *
from seq;
;

select *
from day05_cleaned_ranges
order by step, range_start;

select sum(range_end - range_start) as part2
from day05_cleaned_ranges
where step = (select max(step) from day05_cleaned_ranges);