create or replace table day06_data as
    select row_number() over () as line, column0
  from read_csv('inputs/06/input.txt', header=false, delim='\n');
create or replace table day06_test as
    select row_number() over () as line, column0
  from read_csv('inputs/06/test.txt', header=false, delim='\n');


create or replace table day06_input as
  with raw as (
    select line as y,
    array_filter(split(column0, ' '), lambda x: x != '') as s
    from day06_data
  ), splitted as (
    select x, y, elem
    from raw,
    unnest(s) with ordinality as u(elem, x)
  ), rotated as (
     select array_agg(elem order by y) as rot
     from splitted
     group by x
  )
  select rot[-1] as op, list_apply(rot[:-2], lambda x: x::int) as num
  from rotated;

select sum(
  case when op = '+' then list_sum(num)
       when op = '*' then list_product(num)
       else 0
  end
)::long as part1
from day06_input;

create or replace table day06_input2 as
  with rotated as (
    select
      x,
      array_agg(elem order by line) rot
    from day06_data,
      unnest(split(column0, '')) with ordinality as u(elem, x)
    group by x
    order by x
  ), cleaned as (
    select
      x,
      any_value(rot[-1]) as op,
      reverse(string_agg(n, '')) as num
    from rotated, unnest(array_filter(rot[:-2], lambda x: x != ' ')) as u(n)
    group by x
  ), with_breaks as (
    select
      x, op, num::int as num,
      case when lag(x) over (order by x) = x - 1 then 0 else 1 end as is_break
    from cleaned
  ), with_groups as (
    select
      x, op, num,
      sum(is_break) over (order by x rows unbounded preceding) as group_id
    from with_breaks
  ), grouped as (
    select
      group_id,
      max(op) as op,
      array_agg(num) as nums
    from with_groups
    group by group_id
  )
  select *
  from grouped;

select sum(
  case when op = '+' then list_sum(nums)
       when op = '*' then list_product(nums)
       else 0
  end
)::long as part2
from day06_input2;