create table parsav_config (
	key   text primary key,
	value text
);

insert into parsav_config (key,value) values ('schema-version','1'),
	('credential-store','managed');
--	('bind',:'bind'),
--	('domain',:'domain'),
--	('instance-name',:'inst'),
--	('policy-security',:'secmode'),
--	('policy-self-register',:'regpol'),
--	('master',:'admin'),

-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
create table parsav_servers (
	id     bigint primary key default (1+random()*(2^63-1))::bigint,
	domain text not null unique,
	key    bytea,
	knownsince bigint,
	parsav boolean -- whether to use parsav protocol extensions
);

create table parsav_actors (
	id        bigint primary key default (1+random()*(2^63-1))::bigint,
	nym       text,
	handle    text not null, -- nym [@handle@origin] 
	origin    bigint references parsav_servers(id)
		on delete cascade, -- null origin = local actor
	knownsince bigint not null,
	bio       text,
	avatarid  bigint, -- artifact id, null if remote
	avataruri text, -- null if local
	rank      smallint not null default 0,
	quota     integer not null default 1000,
	invites   integer not null default 0,
	key       bytea, -- private if localactor; public if remote
	epithet   text,
	authtime  bigint not null, -- cookies earlier than this timepoint will not be accepted
	
	unique (handle,origin)
);

create table parsav_rights (
	key text,
	actor bigint references parsav_actors(id)
		on delete cascade,
	allow boolean not null,
	scope bigint, -- for future expansion

	primary key (key,actor)
);
create index on parsav_rights (actor);

create table parsav_posts (
	id         bigint primary key default (1+random()*(2^63-1))::bigint,
	author     bigint references parsav_actors(id) on delete cascade,
	subject    text,
	acl        text not null default 'all', -- just store the script raw 🤷
	body       text,
	posted     bigint not null,
	discovered bigint not null,
	chgcount   integer not null default 0,
	edited     bigint,
	parent     bigint not null default 0, -- if post: part of conversation; if chatroom: top-level post
	circles    bigint[], -- TODO at edit or creation, iterate through each circle
	mentions   bigint[], -- a user has, check if it can see her post, and if so add
	artifacts  bigint[],

	convoheaduri text
	-- only used for tracking foreign conversations and tying them to post heads;
	-- local conversations are tracked directly and mapped to URIs based on the
	-- head's ID. null if native tweet or not the first tweet in convo
);
create index on parsav_posts (author);
create index on parsav_posts (parent);

create table parsav_rels (
	relator bigint references parsav_actors(id)
		on delete cascade, -- e.g. follower
	relatee bigint references parsav_actors(id)
		on delete cascade, -- e.g. followed
	kind    smallint, -- e.g. follow, block, mute

	primary key (relator, relatee, kind)
);

create table parsav_acts (
	id      bigint primary key default (1+random()*(2^63-1))::bigint,
	kind    text not null, -- like, rt, react, so on
	time    bigint not null,
	actor   bigint references parsav_actors(id) on delete cascade,
	subject bigint, -- may be post or act, depending on kind
	body	text -- emoji, if react
);
create index on parsav_acts (subject);
create index on parsav_acts (actor);
create index on parsav_acts (time);

create table parsav_log (
	-- accesses are tracked for security & sending delete acts
	id    bigint primary key default (1+random()*(2^63-1))::bigint,
	time  bigint not null,
	actor bigint references parsav_actors(id)
		on delete cascade,
	post  bigint not null
);

create table parsav_artifacts (
	id          bigint primary key default (1+random()*(2^63-1))::bigint,
	birth       bigint not null,
	content     bytea, -- if null, this is a "ban record" preventing content matching the hash from being re-uploaded
	hash		bytea unique not null, -- sha256 hash of content
	-- it would be cool to use a computed column for this, but i don't want
	-- to lock people into PG12 or drag in the pgcrypto extension just for this
	mime        text -- null if unknown, will be reported as x-octet-stream
);
create index on parsav_artifacts (mime);

create table parsav_artifact_claims (
	birth bigint not null,
	uid bigint references parsav_actors(id) on delete cascade,
	rid bigint references parsav_artifacts(id) on delete cascade,
	description text,
	folder text,

	unique (uid,rid)
);
create index on parsav_artifact_claims (uid);
create index on parsav_artifact_claims (uid,folder);

create table parsav_circles (
	id          bigint primary key default (1+random()*(2^63-1))::bigint,
	owner       bigint not null references parsav_actors(id) on delete cascade,
	name        text not null,
	members     bigint[] not null default array[]::bigint[],

	unique (owner,name)
);
create index on parsav_circles (owner);

create table parsav_rooms (
	id          bigint primary key default (1+random()*(2^63-1))::bigint,
	origin		bigint references parsav_servers(id) on delete cascade,
	name		text not null,
	description text not null,
	policy      smallint not null
);

create table parsav_room_members (
	room   bigint not null references parsav_rooms(id) on delete cascade,
	member bigint not null references parsav_actors(id) on delete cascade,
	rank   smallint not null default 0,
	admin  boolean not null default false, -- non-admins with rank can only moderate + invite
	title  text, -- admin-granted title like reddit flair
	vouchedby bigint references parsav_actors(id) on delete set null
);
create index on parsav_room_members (member);
create index on parsav_room_members (room);

create table parsav_invites (
	id          bigint primary key default (1+random()*(2^63-1))::bigint,
	-- when a user is created from an invite, the invite is deleted and the invite
	-- ID becomes the user ID. privileges granted on the invite ID during the invite
	-- process are thus inherited by the user
	issuer bigint references parsav_actors(id) on delete set null,
	handle text, -- admin can lock invite to specific handle
	rank   smallint not null default 0,
	quota  integer not null  default 1000
);

create table parsav_sanctions (
	id     bigint primary key default (1+random()*(2^63-1))::bigint,
	issuer bigint references parsav_actors(id) on delete set null,
	scope  bigint, -- can be null or room for local actions
	nature smallint not null, -- silence, suspend, disemvowel, censor, noreply, etc
	victim bigint not null, -- can be user, room, or post
	expire bigint, -- auto-expires if set
	review bigint,  -- brings up for review at given time if set
	reason text, -- visible to victim if set
	context text, -- admin-only note
	appeal text -- null if no appeal lodged
);
create index on parsav_sanctions (victim,scope);
create index on parsav_sanctions (issuer);

create table parsav_actor_conf_strs (
	uid bigint not null references parsav_actors(id) on delete cascade,
	key text not null, value text not null, unique (uid,key)
);
create table parsav_actor_conf_ints (
	uid bigint not null references parsav_actors(id) on delete cascade,
	key text not null, value bigint not null, unique (uid,key)
);

-- create a temporary managed auth table; we can delete this later
-- if it ends up being replaced with a view
%include pgsql-auth.sql%
