drop schema if exists nntp cascade;
create schema nntp;

set search_path to nntp,public;

-- TODO could probably just do a nested replace in straight sql
create or replace function wildmat_to_re(wm text) returns text as $$
declare
	re text;
begin
	re := regexp_replace(wm, E'\\.', E'\\.','g'); -- first string will be
	-- interpreted as a regex, second as just a string
	re := regexp_replace(re, E'\\?', E'.','g');
	re := regexp_replace(re, E'\\*', E'.*','g');
	return re;
end;
$$ language 'plpgsql';

create or replace function wildmat_regex(wm text) returns text as $$
declare
	skip_init_neg boolean;
	negated boolean;
	repat text;
	sql	text;
	pat	text;
begin
	sql := '';
	skip_init_neg := true;

	for pat in select p from regexp_split_to_table(wm, E',') as p loop
	if skip_init_neg and pat ~ E'^!' then continue; end if;
		skip_init_neg := false;
		negated := (pat ~ E'^!');
		pat := regexp_replace(pat, E'^!', '');
		repat := wildmat_to_re(pat);
		if not negated then
			sql := sql || '|' || repat;
		else
			sql := regexp_replace(sql, E'^\\|', '');
			sql := '(^(?!' || repat || ')(' || sql || ')$)';
		end if;
	end loop;

	sql := regexp_replace(sql, E'^\\|', '');
	if not negated then
		sql := '^(' || sql || ')$';
	end if;

	return sql;
end;
$$ language 'plpgsql';

-- TODO put the rest of the mandatory overview fields here?
create table articles (
	id	text primary key,
	header	text not null,
	body	text, -- not null?
	peer	text,
	received	timestamp with time zone default CURRENT_TIMESTAMP,
	newsgroups	text, -- TODO array?
	expires	timestamp with time zone,
	"date"	timestamp with time zone,
	-- hdr headers
	lines	integer, -- actual lines, see the overview table for Lines hdr
	bytes	integer,
	control	boolean	not null default 'f'
);
-- check id like '<%' and id like '%>'

create table moderated_posts (
	id	text primary key,
	article	text,
	received	timestamp with time zone default CURRENT_TIMESTAMP
);

create or replace function multiline(content text) returns text as $$
select regexp_replace(
	regexp_replace(
		regexp_replace($1, E'^\\.', E'..', 'gn'), -- dot stuff
	       	E'\\r?\\n', E'\r\n', 'g'), -- lines separated by \r\n
	E'(\\r\\n)?$', E'\r\n\.\r\n'); -- terminated by \r\n.\r\n
$$ language 'sql';

create or replace function demultiline(content text) returns text as $$
select regexp_replace(
	regexp_replace(
		regexp_replace($1,
	E'\\r\\n\\.\\r\\n$', E'\r\n'), -- terminated by \r\n.\r\n
	       	E'\\r\\n', E'\n', 'g'), -- lines separated by \r\n
		       	E'^\\.\\.', E'.', 'gn'); -- undot stuff
$$ language 'sql';

create table overview (
	article	text references articles on delete cascade
	deferrable initially deferred,
	header	text, -- or metadata, but it has a unique name
	value	text,
	unique(article,header)
);

create table header_order (
	header	text primary key,
	ord	integer unique not null
);

insert into header_order (values
('Subject',1),
('From',2),
('Date',3),
('Message-ID',4),
('References',5),
(':bytes',6),
(':lines',7),
('Xref', 8)
);

create view articleover as
select id, array_to_string(array_agg(regexp_replace(value,E'\t',' ', 'g')),E'\t') as overview from (
select
A.id, ORD.header,
case when ord.ord <= 7 then coalesce(O.value,'') else coalesce(ORD.header || ': ' || nullif(O.value, ''), '') end as value
from articles A cross join header_order ord
left join overview O on O.article = A.id and O.header = ORD.header
order by ord.ord
) as foo
group by id
;

-- TODO trigger to make an insert an update if the unique constraint
-- would be violated

-- From
-- Date
-- Newsgroups
-- Subject
-- Message-ID
-- Path

-- Optional
-- Reply-To
-- Sender
-- Followup-To
-- Expires
-- References
-- Control
-- Distribution
-- Organization
-- Keywords
-- Summary
-- Approved
-- Lines
-- Xref

create table newsgroups (
	newsgroup	text primary key,
	posting	char(1) not null default 'y',
	moderator	text default '%s@moderators.isc.org',
	description	text,
	high	integer default 0,
	low	integer default 1,
	created	timestamp default CURRENT_TIMESTAMP,
	creator	text,
	active	boolean not null default true,
	local	boolean not null default false
);

create or replace function newsgroups_update_trig() returns trigger as $$
begin
	if TG_OP = 'UPDATE' and NEW.active = false then
	       if NEW.active != OLD.active then
		insert into log (message) values ('rmgroup ' || NEW.newsgroup);
	end if;
	end if;
	if TG_OP = 'INSERT' and NEW.active = true then
		insert into log (message) values ('newgroup ' || NEW.newsgroup);
	end if;
	return NEW;
end;
$$ language 'plpgsql';

create trigger newsgroups_maintenance_log_trigger after update or insert on newsgroups
for each row execute procedure newsgroups_update_trig();

create table expiration (
	wildmat	text primary key,
	wildmatre text,
	moderated	boolean, -- true = mod only, false = not mod, null=any
	crossposts	boolean not null default false, -- also expire all crossposts
	retention	interval not null default '30 days', -- how long to keep from receipt
	max	interval not null default '90 days',
	min	interval not null default '7 days'
);

create table xpost (
	article	text references articles on delete cascade
		deferrable initially deferred,
		-- deferrable lets us do the xposts first on new
		-- article insertion
	newsgroup	text references newsgroups on delete cascade,
	number	integer,
	unique(newsgroup,number)
);
-- the following index makes calculating average cross-posting much
-- faster.
create index xpost_article_index on xpost(article);

create view xref as
select article, array_to_string(array_agg(newsgroup||':'||number),' ') as xref
from xpost group by article
;

create or replace function headers(article text)
returns table (header text, value text)  as $$
	select
	substring(h from E'^([^:]+):'),
        substring(h from E'(?n)^[^:]+: (.+?)$')
        from
        regexp_split_to_table(
		regexp_replace($1, E'\r?\n$', ''),
	E'\r?\n(?!\\s)') as h
	;
$$ language 'sql' immutable strict;

create or replace function strip_header(headers text, strip text)
returns text as $$
select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n'
from (
        select * from headers($1)
        where upper(header) != upper($2)
) as base
;$$ language 'sql';

create or replace function line_count(content text) returns integer as $$
select octet_length(regexp_replace(regexp_replace($1,E'\\n$', ''), E'[^\\n]', '','g'))+1;
$$ language 'sql';

create or replace function header_value(headers text, want text)
returns text as $$
select value from headers($1) where lower(header) = lower($2);
$$ language 'sql';

create or replace function header_add(headers text, h text, v text) returns text as $$
select $1 || $2 || ': ' || $3 || E'\r\n';
$$ language 'sql';

create or replace function header_replace(headers text, hdr text, content text)
returns text as $$
	select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n' from (
	select
		case when lower(header) = lower($2) then $2 else header end,
		case when lower(header) = lower($2) then $3 else value end
        	from headers($1)
	) as base
	;
$$ language 'sql';

create or replace function rfc_date(d text) returns timestamptz as $$
declare
	noct text;
	simple text;
	match	text[];
begin
	simple := E'(\\d\\d?)\\s+(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\\s+(\\d\\d(?:\\d\\d)?)\\s+(\\d\\d?:\\d\\d(?::\\d\\d)?)\\s+([+-]\\d\\d\\d\\d|\\w{3})';
	if d ~ simple then
		match := regexp_matches(d, simple);
		begin
			-- was getting +2400 as time zone
		return array_to_string(match, ' ')::timestamptz;
		exception when others then
			raise NOTICE 'date parse error: % %', SQLSTATE, SQLERRM;
			return null;
		end;
	end if;
	raise NOTICE 'date regex mismatch %', d;

	return null;
end;
$$ language 'plpgsql';

create or replace function article_post() returns trigger as $$
declare
	id text;
	ng text;
	xrefhdr text;
	host text;
	xrefcur text;
begin
	xrefhdr := header_value(NEW.header, 'Path');
	host := substring(xrefhdr from E'^\\s*([^!]+)');

	id := header_value(NEW.header, 'Message-ID');
	ng := header_value(NEW.header, 'Newsgroups');
	xrefcur := header_value(NEW.header, 'Xref');

	-- path
	-- subject
	-- date

	insert into xpost (article, newsgroup)
	select
	id, GROUPS.newsgroup from
	regexp_split_to_table(ng, E'\\s*,\\s*') as G
	inner join newsgroups GROUPS on GROUPS.newsgroup = btrim(G)
	;

	xrefhdr := (select xref from xref where article = id);

	if xrefcur is not null then
		NEW.header :=
	       	header_replace(NEW.header, 'Xref', host || ' ' ||xrefhdr);
	else
		NEW.header :=
	       	header_add(NEW.header, 'Xref', host || ' ' ||xrefhdr);
	end if;

	if NEW.header is null then
		raise exception 'null header H';
	end if;

	NEW.newsgroups = ng;
	-- Two for the logical crlf between the header and body
	-- There will be three more for the .\r\n at the end
	-- TODO need to decide on how to store header and body
	NEW.bytes = octet_length(NEW.header) + octet_length(NEW.body) + 2;
	NEW.lines = line_count(NEW.body);

	if NEW.header is null then
		raise exception 'null header a';
	end if;

	begin
		-- TODO improve this regular expression
		NEW.expires = regexp_replace(header_value(NEW.header, 'Expires'),E'\\([^\\)]*\\)', ' ')::timestamptz;
	exception
		when OTHERS then NEW.expires = NULL;
	end;

	-- TODO if date is null?
	begin
		-- TODO improve this regular expression
		NEW.date = regexp_replace(header_value(NEW.header, 'Date'),E'\\([^\\)]*\\)', ' ')::timestamptz;
	exception
		when OTHERS then NEW.date = NULL;
	end;

	if NEW.header is null then
		raise exception 'null header b';
	end if;

	-- Overview headers
	-- TODO just do all headers?  what about dups?
	insert into overview (article, header, value)
	select NEW.id, HO.header, H.value
	from headers(NEW.header) H
	inner join
	header_order HO on upper(HO.header) = upper(H.header)
	union
	values (NEW.id, ':bytes', NEW.bytes::text),
	(NEW.id, ':lines', NEW.lines::text)
	;
	-- qw(Subject From Date Message-ID References :bytes :lines Xref);

	return NEW;
end;
$$ language 'plpgsql';

create trigger article_post_trigger before insert on articles
for each row execute procedure article_post();

create or replace function xpost_trigger() returns trigger as $$
declare
	hiwater	integer;
begin
	update newsgroups set high = high + 1
       	where newsgroup = NEW.newsgroup returning high into hiwater;

	NEW.number := hiwater;
	return NEW;
end;
$$ language 'plpgsql';

create trigger highwater before insert on xpost
	for each row execute procedure xpost_trigger();

create or replace function highwater_trigger() returns trigger as $$
begin
	update newsgroups set high = NEW.number where newsgroup = NEW.newsgroup;
	return NEW;
end;
$$ language plpgsql;

-- TODO this function breaks when the last article is deleted
-- need to look up rfc to determine proper behavior
create or replace function lowwater_trigger() returns trigger as $$
begin
	update newsgroups set low = (select coalesce(min(number),0) from xpost where newsgroup = OLD.newsgroup) where newsgroup = OLD.newsgroup;
	return OLD;
end;
$$ language plpgsql;

--create trigger highwater after insert on xpost
--	for each row execute procedure highwater_trigger();
create trigger lowwater after delete on xpost
	for each row execute procedure lowwater_trigger();

-- who do i forward articles to
create table feeds (
	name	text primary key,
	host	text,
	port	integer default 119,
	enabled	boolean not null default true,
	stream	boolean not null default false, -- use CHECK/TAKETHIS
	groups	text default '*', -- wildmat
	wildmat	text default '*',
	wildmatre	text default '.', -- parsed wildmat
	noxposts	text,
	noxpostsre	text,
	distribution	text,
	distributionre	text,
	-- TODO feed moderated/unmoderated
       	-- see http://www.faqs.org/docs/linux_network/x18341.html
	maxsize	integer, -- maximum article size to feed
	localonly	boolean default false,
	path	text, -- regular expression default host?
	frequency	interval default '1 hour'::interval, -- int secs?
	feedtime	timestamp with time zone
);
-- todo check(port >= 0 and port <= 65535)

create table sessions (
	peer	text,
	addr	inet,
	port	integer,
	incoming	boolean not null default true,
	connected	timestamp with time zone,
	closed	timestamp with time zone default clock_timestamp(),
	received	integer, -- 239, 235
	refused		integer, -- 435
	rejected	integer, -- 439, 437
	postponed	integer -- 436
);
-- offered == all those together
create view session_summary as select peer, addr, port, connected, sum(received) as received, sum(refused) as refused, sum(rejected) as rejected, sum(postponed) as postponed, coalesce(max(closed), clock_timestamp()) - connected as duration from sessions group by peer, addr, port, connected;


create table feedq (
	peer	text references feeds on delete cascade,
	article	text references articles on delete cascade,
	response	integer
);

create function feedto(newsgroups text, host text) returns boolean as $$
begin
	return (select
	       	true =
	       	any (select regexp_split_to_table(newsgroups, E',')
		       	~ F.wildmatre)
		and
	       	not true = 
	       	any (select regexp_split_to_table(newsgroups, E',')
		       	~ F.noxpostsre)
	       	from feeds F
	       	where F.host = host
	)
	;
end;
$$ language 'plpgsql';

create or replace function feedout() returns trigger as $$
begin
	insert into feedq
	select coalesce(F.host, F.name), NEW.id
	from feeds F
	where true = any (select regexp_split_to_table(NEW.newsgroups, E',')
		       	~ F.wildmatre)
	-- TODO add in noxposts
	and coalesce(F.host,F.name) != NEW.peer
	and F.enabled
	;
	if FOUND then
		NOTIFY feedq;
	end if;
	return NEW;
end;
$$ language 'plpgsql';

create trigger feedpeer_trigger after insert on articles
for each row execute procedure feedout();

create table log (
	ts	timestamp with time zone default CURRENT_TIMESTAMP,
	client	inet,
	port	integer,
	pid	integer,
	priority	integer,
	facility	text,
	message	text
);