-- SWH DB schema upgrade
-- from_version: 26
-- to_version: 27
-- description: Change organizations to the entity schema

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

ALTER TABLE list_history
	DROP CONSTRAINT list_history_organization_fkey;

ALTER TABLE occurrence_history
	DROP CONSTRAINT occurrence_history_origin_branch_revision_authority_validi_excl;

ALTER TABLE occurrence_history
	DROP CONSTRAINT occurrence_history_authority_fkey;

DROP TABLE project_history;

DROP TABLE project;

CREATE SEQUENCE entity_history_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

create type entity_type as enum (
  'organization',
  'group_of_entities',
  'hosting',
  'group_of_persons',
  'person',
  'project'
);

CREATE TABLE entity (
	uuid uuid NOT NULL,
	parent uuid,
	name text NOT NULL,
	type entity_type NOT NULL,
	description text,
	homepage text,
	active boolean NOT NULL,
	generated boolean NOT NULL,
	lister uuid,
	lister_metadata jsonb,
	doap jsonb,
	last_seen timestamp with time zone,
	last_id bigint
);

CREATE TABLE entity_equivalence (
	entity1 uuid NOT NULL,
	entity2 uuid NOT NULL
);

CREATE TABLE entity_history (
	id bigint DEFAULT nextval('entity_history_id_seq'::regclass) NOT NULL,
	uuid uuid,
	parent uuid,
	name text NOT NULL,
	type entity_type NOT NULL,
	description text,
	homepage text,
	active boolean NOT NULL,
	generated boolean NOT NULL,
	lister uuid,
	lister_metadata jsonb,
	doap jsonb,
	validity timestamp with time zone[]
);

CREATE TABLE listable_entity (
	uuid uuid NOT NULL,
	enabled boolean DEFAULT true NOT NULL,
	list_engine text,
	list_url text,
	list_params json,
	latest_list timestamp with time zone
);

ALTER TABLE list_history
	DROP COLUMN organization,
	ADD COLUMN entity uuid;

ALTER TABLE origin
	ADD COLUMN lister uuid,
	ADD COLUMN project uuid;

ALTER SEQUENCE entity_history_id_seq
	OWNED BY entity_history.id;

CREATE OR REPLACE FUNCTION swh_stat_counters() RETURNS SETOF counter
    LANGUAGE sql STABLE
    AS $$
    select relname::text as label, reltuples::bigint as value
    from pg_class
    where oid in (
        'public.content'::regclass,
        'public.directory'::regclass,
        'public.directory_entry_dir'::regclass,
        'public.directory_entry_file'::regclass,
        'public.directory_entry_rev'::regclass,
        'public.occurrence'::regclass,
        'public.occurrence_history'::regclass,
        'public.origin'::regclass,
        'public.person'::regclass,
        'public.entity'::regclass,
        'public.entity_history'::regclass,
        'public.release'::regclass,
        'public.revision'::regclass,
        'public.revision_history'::regclass,
        'public.skipped_content'::regclass
    );
$$;

CREATE OR REPLACE FUNCTION swh_update_entity_from_entity_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    with all_entities as (
      select uuid, parent, name, type, description, homepage, active,
             generated, lister, lister_metadata, doap, last_seen, last_id
      from (
          select row_number() over (partition by uuid order by unnest(validity) desc) as row,
	         id as last_id, uuid, parent, name, type, description, homepage, active,
		 generated, lister, lister_metadata, doap,
	         unnest(validity) as last_seen
          from entity_history
      ) as latest_entities
      where latest_entities.row = 1
    ),
    updated_uuids as (
      update entity set
        parent = all_entities.parent,
        name = all_entities.name,
	type = all_entities.type,
	description = all_entities.description,
	homepage = all_entities.homepage,
	active = all_entities.active,
	generated = all_entities.generated,
	lister = all_entities.lister,
	lister_metadata = all_entities.lister_metadata,
	doap = all_entities.doap,
	last_seen = all_entities.last_seen,
        last_id = all_entities.last_id
      from all_entities
      where entity.uuid = all_entities.uuid
      returning entity.uuid
    )
    insert into entity
    (select * from all_entities
     where uuid not in (select uuid from updated_uuids));
    return null;
end
$$;

ALTER TABLE entity
	ADD CONSTRAINT entity_pkey PRIMARY KEY (uuid);

ALTER TABLE entity_equivalence
	ADD CONSTRAINT entity_equivalence_pkey PRIMARY KEY (entity1, entity2);

ALTER TABLE entity_history
	ADD CONSTRAINT entity_history_pkey PRIMARY KEY (id);

ALTER TABLE listable_entity
	ADD CONSTRAINT listable_entity_pkey PRIMARY KEY (uuid);

ALTER TABLE entity
	ADD CONSTRAINT entity_last_id_fkey FOREIGN KEY (last_id) REFERENCES entity_history(id);

ALTER TABLE entity
	ADD CONSTRAINT entity_lister_fkey FOREIGN KEY (lister) REFERENCES listable_entity(uuid);

ALTER TABLE entity
	ADD CONSTRAINT entity_parent_fkey FOREIGN KEY (parent) REFERENCES entity(uuid) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE entity_equivalence
	ADD CONSTRAINT order_entities CHECK ((entity1 < entity2));

ALTER TABLE entity_equivalence
	ADD CONSTRAINT entity_equivalence_entity1_fkey FOREIGN KEY (entity1) REFERENCES entity(uuid);

ALTER TABLE entity_equivalence
	ADD CONSTRAINT entity_equivalence_entity2_fkey FOREIGN KEY (entity2) REFERENCES entity(uuid);

ALTER TABLE list_history
	ADD CONSTRAINT list_history_entity_fkey FOREIGN KEY (entity) REFERENCES listable_entity(uuid);

ALTER TABLE listable_entity
	ADD CONSTRAINT listable_entity_uuid_fkey FOREIGN KEY (uuid) REFERENCES entity(uuid);

ALTER TABLE origin
	ADD CONSTRAINT origin_lister_fkey FOREIGN KEY (lister) REFERENCES listable_entity(uuid);

ALTER TABLE origin
	ADD CONSTRAINT origin_project_fkey FOREIGN KEY (project) REFERENCES entity(uuid);

CREATE INDEX entity_name_idx ON entity USING btree (name);

CREATE INDEX entity_history_name_idx ON entity_history USING btree (name);

CREATE INDEX entity_history_uuid_idx ON entity_history USING btree (uuid);

CREATE TRIGGER update_entity
	AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON entity_history
	FOR EACH STATEMENT
	EXECUTE PROCEDURE swh_update_entity_from_entity_history();

insert into entity_history
  (uuid, parent, name, type, description, homepage, active, generated, validity)
values
  ('5f4d4c51-498a-4e28-88b3-b3e4e8396cba', NULL, 'softwareheritage',
   'organization', 'Software Heritage',
   'http://www.softwareheritage.org/', true, false, ARRAY[now()]),
  ('6577984d-64c8-4fab-b3ea-3cf63ebb8589', NULL, 'gnu', 'organization',
   'GNU is not UNIX', 'https://gnu.org/', true, false, ARRAY[now()]),
  ('7c33636b-8f11-4bda-89d9-ba8b76a42cec', '6577984d-64c8-4fab-b3ea-3cf63ebb8589',
   'GNU Hosting', 'group_of_entities',
   'GNU Hosting facilities', NULL, true, false, ARRAY[now()]),
  ('4706c92a-8173-45d9-93d7-06523f249398', '6577984d-64c8-4fab-b3ea-3cf63ebb8589',
   'GNU rsync mirror', 'hosting',
   'GNU rsync mirror', 'rsync://mirror.gnu.org/', true, false, ARRAY[now()]),
  ('5cb20137-c052-4097-b7e9-e1020172c48e', '6577984d-64c8-4fab-b3ea-3cf63ebb8589',
   'GNU Projects', 'group_of_entities',
   'GNU Projects', 'https://gnu.org/software/', true, false, ARRAY[now()]),
  ('4bfb38f6-f8cd-4bc2-b256-5db689bb8da4', NULL, 'GitHub', 'organization',
   'GitHub', 'https://github.org/', true, false, ARRAY[now()]),
  ('aee991a0-f8d7-4295-a201-d1ce2efc9fb2', '4bfb38f6-f8cd-4bc2-b256-5db689bb8da4',
   'GitHub Hosting', 'group_of_entities',
   'GitHub Hosting facilities', 'https://github.org/', true, false, ARRAY[now()]),
  ('34bd6b1b-463f-43e5-a697-785107f598e4', 'aee991a0-f8d7-4295-a201-d1ce2efc9fb2',
   'GitHub git hosting', 'hosting',
   'GitHub git hosting', 'https://github.org/', true, false, ARRAY[now()]),
  ('e8c3fc2e-a932-4fd7-8f8e-c40645eb35a7', 'aee991a0-f8d7-4295-a201-d1ce2efc9fb2',
   'GitHub asset hosting', 'hosting',
   'GitHub asset hosting', 'https://github.org/', true, false, ARRAY[now()]),
  ('9f7b34d9-aa98-44d4-8907-b332c1036bc3', '4bfb38f6-f8cd-4bc2-b256-5db689bb8da4',
   'GitHub Organizations', 'group_of_entities',
   'GitHub Organizations', 'https://github.org/', true, false, ARRAY[now()]),
  ('ad6df473-c1d2-4f40-bc58-2b091d4a750e', '4bfb38f6-f8cd-4bc2-b256-5db689bb8da4',
   'GitHub Users', 'group_of_entities',
   'GitHub Users', 'https://github.org/', true, false, ARRAY[now()]);

ALTER TABLE occurrence_history
	ADD COLUMN authority_new uuid;

update occurrence_history
  set authority_new = auth.authority_new
  from (
    select uuid as authority_new, id as authority
    from organization
    left join entity
    on organization.name = entity.name
  ) auth
  where occurrence_history.authority = auth.authority;

alter table occurrence_history drop column authority;
alter table occurrence_history rename column authority_new to authority;

DROP TABLE organization;

alter table occurrence_history alter column authority set not null;

ALTER TABLE occurrence_history
	ADD CONSTRAINT occurrence_history_origin_branch_revision_authority_validi_excl EXCLUDE USING gist (origin WITH =, branch WITH =, revision WITH =, ((authority)::text) WITH =, validity WITH &&);

ALTER TABLE occurrence_history
	ADD CONSTRAINT occurrence_history_authority_fkey FOREIGN KEY (authority) REFERENCES entity(uuid);