-- SWH DB schema upgrade
-- from_version: 81
-- to_version: 82
-- description: refactor cache_content_revision

insert into dbversion(version, release, description)
      values(82, now(), 'Work In Progress');

drop function swh_cache_content_revision_add(revision_id sha1_git);
drop function swh_content_find_provenance(content_id sha1_git);

DROP TABLE cache_content_revision;

create table cache_content_revision (
    content         sha1_git not null,
    blacklisted     boolean default false,
    revision_paths  bytea[][]
);

CREATE TABLE cache_content_revision_processed (
	revision sha1_git NOT NULL
);

CREATE OR REPLACE FUNCTION swh_cache_content_revision_add(revision_id sha1_git) RETURNS void
    LANGUAGE plpgsql
    AS $$
declare
  rev sha1_git;
begin
    select revision
        from cache_content_revision_processed
        where revision=revision_id
        into rev;

    if rev is NULL then

      insert into cache_content_revision_processed (revision) VALUES (revision_id);

      insert into cache_content_revision
          select sha1_git as content, false as blacklisted, array_agg(ARRAY[revision_id::bytea, name::bytea]) as revision_paths
          from swh_directory_walk((select directory from revision where id=revision_id))
          where type='file'
          group by sha1_git
      on conflict (content) do update
          set revision_paths = cache_content_revision.revision_paths || EXCLUDED.revision_paths
          where cache_content_revision.blacklisted = false;
      return;

    else
      return;
    end if;
end
$$;

COMMENT ON FUNCTION swh_cache_content_revision_add(revision_id sha1_git) IS 'Cache the specified revision directory contents into cache_content_revision';

CREATE OR REPLACE FUNCTION swh_content_find_provenance(content_id sha1_git) RETURNS SETOF content_provenance
    LANGUAGE sql
    AS $$
    with subscripted_paths as (
        select content, revision_paths, generate_subscripts(revision_paths, 1) as s
        from cache_content_revision
        where content = content_id
    ),
    cleaned_up_contents as (
        select content, revision_paths[s][1]::sha1_git as revision, revision_paths[s][2]::unix_path as path
        from subscripted_paths
    )
    select cuc.content, cuc.revision, cro.origin, cro.visit, cuc.path
    from cleaned_up_contents cuc
    inner join cache_revision_origin cro using(revision)
$$;

COMMENT ON FUNCTION swh_content_find_provenance(content_id sha1_git) IS 'Given a content, provide provenance information on it';

ALTER TABLE cache_content_revision
	ADD CONSTRAINT cache_content_revision_pkey PRIMARY KEY (content);

ALTER TABLE cache_content_revision
	ADD CONSTRAINT cache_content_revision_content_fkey FOREIGN KEY (content) REFERENCES content(sha1_git);

ALTER TABLE cache_content_revision_processed
	ADD CONSTRAINT cache_content_revision_processed_pkey PRIMARY KEY (revision);

ALTER TABLE cache_content_revision_processed
	ADD CONSTRAINT cache_content_revision_processed_revision_fkey FOREIGN KEY (revision) REFERENCES revision(id);