-- SWH DB schema upgrade
-- from_version: 74
-- to_version: 75
-- description: Add completion information to origin_visit

INSERT INTO dbversion(version, release, description)
      VALUES(75, now(), 'Work In Progress');

CREATE TYPE origin_visit_status AS ENUM (
  'ongoing',
  'full',
  'partial'
);

COMMENT ON TYPE origin_visit_status IS 'Possible visit status';

ALTER TABLE origin_visit
            ADD COLUMN status origin_visit_status;

-- Already visited origins are considered full
UPDATE origin_visit SET status = 'full';

-- provide a status for visits is mandatory
ALTER TABLE origin_visit
            ALTER COLUMN status SET NOT NULL;

comment on column origin_visit.origin is 'Visited origin';
comment on column origin_visit.visit is 'The numbered visit occurrence for that origin';
comment on column origin_visit.date is 'Visit date for that origin';
comment on column origin_visit.status is 'Visit status for that origin';

-- add a new origin_visit for origin origin_id at date.
--
-- Returns the new visit id.
create or replace function swh_origin_visit_add(origin_id bigint, date timestamptz)
  returns bigint
  language sql
as $$
  with last_known_visit as (
    select coalesce(max(visit), 0) as visit
    from origin_visit
    where origin = origin_id
  )
  insert into origin_visit (origin, date, visit, status)
  values (origin_id, date, (select visit from last_known_visit) + 1, 'ongoing')
  returning visit;
$$;

create or replace function swh_mktemp_occurrence_history()
    returns void
    language sql
as $$
    create temporary table tmp_occurrence_history(
        like occurrence_history including defaults,
        visit bigint not null
    ) on commit drop;
    alter table tmp_occurrence_history
      drop column visits,
      drop column object_id;
$$;


create or replace function swh_occurrence_history_add()
    returns void
    language plpgsql
as $$
declare
  origin_id origin.id%type;
begin
  -- Create or update occurrence_history
  with occurrence_history_id_visit as (
    select tmp_occurrence_history.*, object_id, visits from tmp_occurrence_history
    left join occurrence_history using(origin, branch, target, target_type)
  ),
  occurrences_to_update as (
    select object_id, visit from occurrence_history_id_visit where object_id is not null
  ),
  update_occurrences as (
    update occurrence_history
    set visits = array(select unnest(occurrence_history.visits) as e
                        union
                       select occurrences_to_update.visit as e
                       order by e)
    from occurrences_to_update
    where occurrence_history.object_id = occurrences_to_update.object_id
  )
  insert into occurrence_history (origin, branch, target, target_type, visits)
    select origin, branch, target, target_type, ARRAY[visit]
      from occurrence_history_id_visit
      where object_id is null;

  -- update occurrence
  for origin_id in
    select distinct origin from tmp_occurrence_history
  loop
    perform swh_occurrence_update_for_origin(origin_id);
  end loop;
  return;
end
$$;