CREATE PROCEDURAL LANGUAGE plpgsql;

-- general array sorting functions
-- sorts array
CREATE FUNCTION sort(original anyarray) RETURNS anyarray
	LANGUAGE sql IMMUTABLE STRICT
	AS $_$
select array_agg(item) as result from (select unnest($1) as item order by item) a;
$_$;

-- sorts array and removes duplicates
CREATE FUNCTION usort(original anyarray) RETURNS anyarray
	LANGUAGE sql IMMUTABLE STRICT
	AS $_$
select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
$_$;

-- this function adds tag to domain
CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
	LANGUAGE plpgsql STRICT
	AS $$
declare
	my_site text[];

	-- note that tags should be sorted to eliminate permutations
	select tag from urls natural join tag where id_site = my_site_id into my_tag;
	if not found then
		-- no records found - creating new tag
		my_tag := array[new_tag];
	else
		-- joining tags

		select usort(my_tag || array[new_tag]) into my_tag;

		-- deleting old site specification
		delete from urls where id_site = my_site_id;
	end if;

	-- selecting new tag id or adding tag to the table
	select id_tag from tag where my_tag = tag into my_tag_id;
	if not found then
		insert into tag (tag) values(my_tag);
		select id_tag from tag where my_tag = tag into my_tag_id;
	end if;

	-- adding new site specification
	insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;
$$;

-- this function adds tag to site by site id
CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
	LANGUAGE plpgsql STRICT
	AS $$
declare
	-- maybe check should be added to make sure supplied site id really exists
	my_tag text[];
	my_tag_id smallint;
begin
	-- selecting tags site already have and adding new tag to them
	-- note that tags should be sorted to eliminate permutations
	select tag from urls natural join tag where id_site = my_site_id into my_tag;
	if not found then
		-- no records found - creating new tag
		my_tag := array[new_tag];
	else
		-- joining tags

		select usort(my_tag || array[new_tag]) into my_tag;

		-- deleting old site specification
		delete from urls where id_site = my_site_id;
	end if;

	-- selecting new tag id or adding tag to the table
	select id_tag from tag where my_tag = tag into my_tag_id;
	if not found then
		insert into tag (tag) values(my_tag);
		select id_tag from tag where my_tag = tag into my_tag_id;
	end if;

	-- adding new site specification
	insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;
$$;

-- transforms domain into ordered array for indexing
CREATE FUNCTION tripdomain(url text) RETURNS text[]
	LANGUAGE plpgsql IMMUTABLE STRICT
	AS $_$
declare
	result text[];

	id_site serial,
	site text[] NOT NULL
);

ALTER TABLE ONLY site
	ADD CONSTRAINT site_id PRIMARY KEY (id_site);

CREATE UNIQUE INDEX site_s ON site (usort(site));

CREATE INDEX site_g ON site USING gin (site);

-- table to hold tag combinations
CREATE TABLE tag (
	id_tag serial,
	tag text[] NOT NULL
);

ALTER TABLE ONLY tag
	ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);

CREATE UNIQUE INDEX tag_s ON tag (usort(tag));

CREATE INDEX tag_g ON tag USING gin (tag);

-- table to hold tag - site links
CREATE TABLE urls (
	date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
	id_site smallint NOT NULL,
	id_tag smallint NOT NULL,
	regex text