-- these views are not part of the schema proper, but rather are uploaded
-- into postgres' memory by parsav at the beginning of a connection. they
-- are not visible to other clients and politely disappear once the
-- connection terminates, allowing us to simultaneously avoid versioning
-- headaches, limit the amount of data we need to send to the server, and
-- reduce the compilation time of our prepared queries.

create or replace temp view parsavpg_post_react_counts as (
	with counts as (
		select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
			inner join parsav_posts as p on p.id = a.subject
		group by a.kind, p.id
	)

	select p.id as post,
		coalesce((select counts.ct from counts where counts.subject = p.id
			and counts.kind = 'like'),0)::integer as likes,
		coalesce((select counts.ct from counts where counts.subject = p.id
			and counts.kind = 'rt'  ),0)::integer as rts
	from parsav_posts as p
);

create type pg_temp.parsavpg_intern_notice as (
	kind	smallint,
	"when"	bigint,
	who		bigint,
	what	bigint,
	reply	bigint,
	reaction text
);

create type pg_temp.parsavpg_intern_actor as (
	id			bigint,
	nym			text,
	handle		text,
	origin		bigint,
	bio			text,
	avataruri	text,
	rank		smallint,
	quota		integer,
	key			bytea,
	epithet		text,
	knownsince	bigint,
	xid			text,
	invites		integer
);

create or replace function
pg_temp.parsavpg_translate_actor(parsav_actors)
returns pg_temp.parsavpg_intern_actor as $$
	select
		($1).id,        ($1).nym,  ($1).handle, ($1).origin, ($1).bio,
		($1).avataruri, ($1).rank, ($1).quota,  ($1).key,    ($1).epithet,
		($1).knownsince::bigint,
		coalesce(($1).handle || '@' ||
				(select domain from parsav_servers as s where s.id = ($1).origin),
			'@' || ($1).handle) as xid,
		($1).invites
$$ language sql;

--drop type if exists pg_temp.parsavpg_intern_post;
create type pg_temp.parsavpg_intern_post as (
	-- order is crucially important, and must match the order used
	-- in row_to_actor. names don't matter
	localpost	bool,
	id			bigint,
	author		bigint,
	subject		text,
	acl			text,
	body		text,
	posted		bigint,
	discovered	bigint,
	edited		bigint,
	parent		bigint,
	convoheaduri text,
	chgcount	integer,
	accent		smallint,
	rtdby		bigint, -- note that these must be 0 if the record
	rtid		bigint, -- in question does not represent an RT!
	n_likes		integer,
	n_rts		integer
);

create or replace function
pg_temp.parsavpg_translate_post(parsav_posts,bigint,bigint)
returns pg_temp.parsavpg_intern_post as $$
	select a.origin is null,
		($1).id,     ($1).author,
		($1).subject,($1).acl,         ($1).body,
		($1).posted, ($1).discovered,  ($1).edited,
		($1).parent, ($1).convoheaduri,($1).chgcount,
		coalesce(c.value, -1)::smallint,
		$2 as rtdby, $3 as rtid,
		re.likes, re.rts
	from parsav_actors as a 
		left join parsav_actor_conf_ints as c
		          on c.key = 'ui-accent' and
		             c.uid = a.id
		left join pg_temp.parsavpg_post_react_counts as re
		          on re.post = ($1).id
	where a.id = ($1).author
$$ language sql;

create or replace temp view parsavpg_known_content as (
	with posts as (
		select p as orig,
			null::bigint as promoter,
			null::bigint as promotion,
			coalesce(p.posted,p.discovered) as promotime
		from parsav_posts as p
	),

	rts as (
		select p as orig,
			a.actor as promoter,
			a.id    as promotion,
			a.time as  promotime
		from parsav_acts as a
			inner join parsav_posts as p on a.subject = p.id
		where a.kind = 'rt'
	),

	content as (select * from posts union select * from rts)

	select pg_temp.parsavpg_translate_post(cn.orig,
			coalesce(cn.promoter,0), coalesce(cn.promotion,0)
		) as post,
		cn.promotime::bigint as tltime,
		coalesce(cn.promoter, (cn.orig).author) as promoter
	from content as cn
	order by cn.promotime desc
);

--create temp view parsavpg_post_threads as (
--
--);

create temp view parsavpg_notices as (
 -- TODO add mentions
	with ntimes as (
		select uid, value as when from parsav_actor_conf_ints where key = 'notice-clear-time'
	), acts as (
		select row(
				kmap.kind::smallint,
				a.time,
				a.actor,
				a.subject,
				null::bigint,
				null::text
			)::pg_temp.parsavpg_intern_notice as notice,
			p.author as rcpt
		from parsav_acts as a
			inner join parsav_posts as p on a.subject = p.id
			inner join (values
				('rt',    <notice:rt>   ),
				('like',  <notice:like> ),
				('react', <notice:react>)
			) as kmap(kstr,kind) on kmap.kstr = a.kind
			left  join ntimes as nt on nt.uid = p.author
		where a.time >= coalesce(nt.when,0)
	), replies as (
		select row(
				<notice:reply>::smallint,
				coalesce(p.posted,p.discovered),
				p.author,
				p.parent,
				p.id,
				null::text
			)::pg_temp.parsavpg_intern_notice as notice,
			par.author as rcpt
		from parsav_posts as p
			inner join parsav_posts as par on p.parent = par.id
			left  join ntimes as nt on nt.uid = p.author
		where p.discovered >= coalesce(nt.when,0)
	), allnotices as (select * from acts union select * from replies)

	table allnotices order by (notice).when desc
);

