--
-- subscription lists
-- 
-- Users subscribe to PRs to be notified when the PR is modified.
--
-- If "batch" is set, notices and copies of messages are sent out
-- in batches.
--
-- If "reporter" is set, the user is also nagged when the PR is in
-- feedback. If "responsible" is set, the user is nagged when the PR
-- is not in feedback.
--
-- The original submitter is automatically subscribed as a reporter.

CREATE TABLE subscriptions (
	pr bigint		not null references PRs (id),
	user bigint		not null references users (id),
	batch boolean		not null,
	reporter boolean	not null,
	responsible boolean	not null
)
WITHOUT OIDS;

-- Intended constraints:
--
--    SELECT id, responsible FROM PRs, subscriptions, users
--    WHERE PRs.id = subscriptions.id
--      AND subscriptions.user = users.id
--      AND PRs.state <> 'closed'
--      AND subscriptions.responsible
--      AND NOT users.responsible
--    ;
--
-- and
--
--    SELECT id, responsible FROM PRs, subscriptions, users
--    WHERE PRs.id = subscriptions.id
--      AND subscriptions.user = users.id
--      AND PRs.state = 'closed'
--      AND subscriptions.responsible
--      AND NOT users.oldresponsible
--    ;
--
-- should always return nothing.