\prompt 'domain name: ' domain
\prompt 'bind to socket: ' bind
\qecho 'by default, parsav tracks rights on its own. you can override this later by replacing the rights table with a view, but you''ll then need to set appropriate rules on the view to allow administrators to modify rights from the web UI, or set the rights-readonly flag in the config table to true. for now, enter the name of an actor who will be granted full rights when she logs in.'
\prompt 'admin actor: ' admin
\qecho 'you will need to create an authentication view mapping your user database to something parsav can understand; see auth.sql for an example. enter the name of the view to use.'
\prompt 'auth view: ' auth

begin;

drop table if exists parsav_config;
create table if not exists parsav_config (
	key text primary key,
	value text
);

insert into parsav_config (key,value) values
	('bind',:'bind'),
	('domain',:'domain'),
	('auth-source',:'auth'),
	('administrator',:'admin');

-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
drop table if exists parsav_servers cascade;
create table parsav_servers (
	id bigint primary key default (1+random()*(2^63-1))::bigint,
	domain text not null,
	key bytea
);
drop table if exists parsav_actors cascade;
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
	bio text,
	rank smallint not null default 0,
	quota integer not null default 1000,
	key bytea, -- private if localactor; public if remote
	
	unique (handle,origin)
);

drop table if exists parsav_rights cascade;
create table parsav_rights (
	key text,
	actor bigint references parsav_actors(id)
		on delete cascade,
	allow boolean,

	primary key (key,actor)
);

insert into parsav_actors (handle,rank,quota) values (:'admin',1,0);
insert into parsav_rights (actor,key,allow)
	select (select id from parsav_actors where handle=:'admin'), a.column1, a.column2 from (values
		('ban',true),
		('config',true),
		('censor',true),
		('suspend',true),
		('rebrand',true)
	) as a;

drop table if exists parsav_posts cascade;
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,
	body text,
	posted timestamp not null,
	discovered timestamp not null,
	scope smallint not null,
	convo bigint, parent bigint,
	circles bigint[], mentions bigint[]
);

drop table if exists parsav_conversations cascade;
create table parsav_conversations (
	id bigint primary key default (1+random()*(2^63-1))::bigint,
	uri text not null,
	discovered timestamp not null,
	head bigint references parsav_posts(id)
);

drop table if exists parsav_rels cascade;
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. follower
	kind smallint, -- e.g. follow, block, mute

	primary key (relator, relatee, kind)
);

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

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