--
-- Users.
--
-- (That is, Swallowtail-level users.)
--
-- Rather than trying to have different kinds of users (as in the last
-- rev of this stuff) I think we will just have user permissions.
-- Otherwise we end up in various kinds of trouble if users change
-- type. This means we cannot use foreign key constraints to e.g.
-- ensure that non-developers are not responsible for bugs; however,
-- I guess we can cope.
--
-- The permissions are:
--    mailto		we can mail to the user's e-mail address on file
--    oldresponsible	allowed to be responsible for closed PRs
--    responsible	allowed to be responsible for PRs
--    editpr		can do edit-pr things
--    admin		can do destructive things
--
-- The following types of users should have permissions as follows:
--    peon              none or mailto
--    submitter         mailto
--    role              mailto|oldresponsible|responsible
--    developer         mailto|oldresponsible|responsible|editpr
--    administrator     mailto|oldresponsible|responsible|editpr|admin
--    retired devel     mailto|oldresponsible
--    deceased devel           oldresponsible
--
-- However, this may vary; e.g. users whose mail address bounces might
-- have their mailto permission revoked.
--
-- The "webpassword" is for use logging in over unsecured HTTP to do
-- things submitters can do. It is assumed that developers (and
-- administrators, who should all be developers) authenticate some
-- other way.
-- 

CREATE SEQUENCE next_user;

CREATE TABLE users (
	id bigint		primary key default nextval('next_user'),
	username text		,
	realname text		,

	-- permissions --
	mailto boolean		not null,
	oldresponsible boolean	not null,
	responsible boolean	not null,
	editpr boolean		not null,
	admin boolean		not null

	-- responsible implies oldresponsible
	check (oldresponsible OR NOT responsible)
)
WITHOUT OIDS;

CREATE TABLE mailaddresses (
	id bigint		not null references users (id),
	selected boolean	not null,
	email text		not null,
	organization text	,
	webpassword text	,
	lastheardfrom timestamp
)
WITHOUT OIDS;

CREATE VIEW usermail AS
   SELECT users.id AS id, username, realname, email, organization,
          mailto, oldresponsible, responsible, editpr, admin
   FROM users, mailaddresses
   WHERE users.id = mailaddresses.id AND mailaddresses.selected
;

-- Intended constraint:
-- (oldresponsible OR responsible OR editpr OR admin)
-- implies username not null.

-- Admins need to be able to adjust user data freely.
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO swallowtail_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON mailaddresses TO swallowtail_admin;
GRANT SELECT ON usermail TO swallowtail_admin;

-- Writers need to see the permission bits.
GRANT SELECT ON users TO swallowtail_writer;

-- Readers only need to be able to print.
-- XXX: deny public access to emails?
GRANT SELECT ON usermail TO swallowtail_reader;
GRANT SELECT ON usermail TO swallowtail_public;

-- The user operations interface needs to manage email addresses.
GRANT SELECT ON users TO swallowtail_uops;
GRANT UPDATE (realname) ON users TO swallowtail_uops;
GRANT SELECT, INSERT, UPDATE, DELETE on mailaddresses to swallowtail_uops;