-- general database structure
-- table to hold all rules
CREATE TABLE rules (
	netmask cidr NOT NULL,
	redirect_url text DEFAULT 'about::blank'::text NOT NULL,
	from_weekday smallint DEFAULT 0 NOT NULL,
	to_weekday smallint DEFAULT 6 NOT NULL,
	from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
	to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
	id_tag smallint NOT NULL
);

ALTER TABLE ONLY rules
	ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

-- table to hold site arrays
CREATE TABLE site (
	id_site serial,
	site text[] NOT NULL
);

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

CREATE UNIQUE INDEX site_u 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_pkey PRIMARY KEY (id_tag);

CREATE UNIQUE INDEX tag_u 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 integer NOT NULL,
	id_tag smallint NOT NULL,
	regexp text
);

CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);

CREATE INDEX urls_id_tag ON urls USING btree (id_tag);

ALTER TABLE ONLY urls
	ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE ONLY urls
	ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

-- rule to join all tables into one to simplify access
-- automaticall uses current day and time data
CREATE VIEW site_rule AS
SELECT a.redirect_url, a.netmask, b.site, b.regexp
FROM ((
	SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
	FROM rules NATURAL JOIN tag
	WHERE ('now'::text)::time without time zone >= rules.from_time
		AND ('now'::text)::time without time zone <= rules.to_time
		AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
		AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
) a JOIN (
	SELECT site.site, tag.tag AS url_tag, regexp
	FROM urls NATURAL JOIN tag NATURAL JOIN site
) b ON (b.url_tag && a.rule_tag));

CREATE PROCEDURAL LANGUAGE plpgsql;

-- general array sorting and domain processing functions
-- sorts array
CREATE or replace 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 or replace 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;
$_$;




































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

		pair := string_to_array(site[x], ':');
		splitted[0 - pair[2]::integer] := pair[1];
		x := x - 1;
	end loop;
	return array_to_string(splitted, '.');
end;
$_$;

-- functions that works with tables
-- this functions returns id of site
create or replace function get_site(my_site text[]) returns integer
	language plpgsql strict
	as $$
declare
	site_id integer;
begin
	select id_site from site where my_site = site into site_id;
	if not found then
		insert into site (site) values (my_site);
		select id_site from site where my_site = site into site_id;
	end if;
	return site_id;
end;
$$;

create or replace function get_site(domain text) returns integer
	language sql immutable strict
	as $$
select get_site(tripdomain($1)) as result;
$$;

-- this function inserts or updates record with tags to site by site id with regexp
CREATE or replace FUNCTION mark(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
	LANGUAGE plpgsql STRICT
	AS $$
declare
	-- maybe check should be added to make sure supplied site id really exists
	my_tag text[];
begin
	-- selecting tags site already have and adding new tag to them
	-- note that tags should be sorted to eliminate permutations
	select coalesce(tag, '{}'::text[]) from urls natural left join tag
		where id_site = my_id_site and regexp = my_regexp into my_tag;
	if not found then
		-- no records found - creating new tag
		insert into urls (id_site, id_tag, regexp) values (my_id_site, my_id_tag, my_regexp);
	else
		-- joining tags
		select usort(my_tag || tag) from tag where id_tag = my_id_tag into my_tag;
		-- updating existing record
		update urls set id_tag = get_tag(my_tag)
			where id_site = my_id_site and regexp = my_regexp;
	end if;
	return my_id_site;
end;
$$;

-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
	LANGUAGE sql immutable STRICT
	AS $$
select mark($1, get_tag(array[$2]), NULL) as result;
$$;

-- this function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
	LANGUAGE sql immutable STRICT
	AS $$
select mark(get_site($1), get_tag(array[$2]), NULL) as result;
$$;

-- this function sets tags for site without regexp
CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
	LANGUAGE sql STRICT
	AS $$
delete from urls where $1 = id_site and regexp is NULL;
insert into urls (id_site, id_tag) values ($1, $2);
select $1;
$$;

-- this function sets tags for site/regexp pair
CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
	LANGUAGE sql STRICT
	AS $$
delete from urls where $1 = id_site and $3 = regexp;
insert into urls (id_site, id_tag, regexp) values ($1, $2, $3);
select $1;
$$;

-- this function stores new data for site/regexp pair
create or replace function set(domain text, tags text, regexp text) returns integer
	language sql immutable strict
	as $$
select set(get_site($1), get_tag($2::text[]), $3);
$$;

-- this function stores new data for site/regexp pair
create or replace function set(domain text, tags text) returns integer
	language sql immutable strict
	as $$
select set(get_site($1), get_tag($2::text[]));
$$;

-- this function returns id of tag array
create or replace function get_tag(my_tag text[]) returns integer
	language plpgsql strict
	as $$
declare
	tag_id integer;
begin
	select id_tag from tag where usort(my_tag) = tag into tag_id;
	if not found then
		insert into tag (tag) values (usort(my_tag));
		select id_tag from tag where usort(my_tag) = tag into tag_id;
	end if;
	return tag_id;
end;
$$;