--
-- PR data.
--
-- PRs is the primary table of bug info, with one row per problem
-- report.
--

CREATE SEQUENCE next_PR;

CREATE TABLE PRs (
	id bigint		primary key default nextval('next_PR'),

	-- basic description
	synopsis text		not null,
	confidential boolean	not null,

	-- states
	state text		not null references states (name),
	locked boolean		not null,	-- deny modifications

	-- intended constraint:
	-- select * from PRs, states where PRs.state = states.name
	--    and states.closed = false and PRs.locked = true
	-- should always return empty.
	-- (no PR should be locked unless it is closed)

	-- Timeouts cause bugs to automatically change state in the
	-- future.  This is intended to be used for e.g. "feedback
	-- timeout".
	--
	-- States that should have a timeout installed are tagged
	-- accordingly in the states table. When changing the state of
	-- a PR, if the new state expects a timeout the PR should be
	-- given a non-null timeout; otherwise, the timeout field
	-- should be nulled.
	timeout_date timestamp	null,
	timeout_state text	null references states (name),
	check ((timeout_date is null) = (timeout_state is null)),

	-- intended constraint:
	-- select * from PRs, states where PRs.state = states.name
	--    and states.timeout <> PRs.timeout_date is not null
	-- should always return empty.
	-- (All PRs in timeout states should have timeouts, and those
	-- not in timeout states should not.)

	-- fixed-size history
	arrival_schemaversion int  not null,
	arrival_date timestamp	not null,
	modified_date timestamp	not null,
	closed_date timestamp	,

	-- original submitter
	originator bigint	references users (id),

	-- original submission
	-- we don't keep this as such - these items go into an
	-- entry in the admin log instead, and the submitter is
	-- automatically subscribed to the bug.
	-- "Submitted by joe@schmoe, Message-Id <3@schmoe>, Subject: foo"
	--from_address text	not null,
	--mail_subject text	not null,
	--mail_msgid text	not null,

	-- contents
	release text		,
	environment text

	-- these appear as a message
	--description text	,
	--how_to_repeat text	,
	--fix text		,
	--unformatted text	

)
WITHOUT OIDS;

-- Only the admin can delete PRs.
GRANT SELECT, INSERT, UPDATE, DELETE ON PRs TO swallowtail_admin;
GRANT SELECT, INSERT, UPDATE ON PRs TO swallowtail_writer;
GRANT SELECT ON PRs TO swallowtail_reader;
GRANT SELECT ON PRs TO swallowtail_public;