--
-- Messages.
--

CREATE SEQUENCE next_rawmsgid;

-- all incoming mail in original form, for reference
-- (should be pruned periodically)
CREATE TABLE rawmail (
	id bigint		primary key default nextval('next_rawmsgid'),
	data text		not null
)
WITHOUT OIDS;

CREATE SEQUENCE next_msgid;

-- comments
CREATE TABLE messages (
	id bigint		primary key default nextval('next_msgid'),
	pr bigint		not null references prs (id),
	who bigint		not null references users (id),
	parent_id bigint	null references messages (id),
	posttime timestamp	not null,
	contenttype text	not null,
	body text		not null,

	-- we don't keep these directly, they go into an admin log entry
	--from_address text	not null,
	--mail_subject text	not null,
	--message_id text	not null,
	rawid bigint		null references rawmail (id)

	check (parent_id != id)
)
WITHOUT OIDS;

-- for patches and mime-attachments
-- if msgid is null, attachment came with original PR
CREATE TABLE attachments (
	pr bigint		not null references PRs (id),
	msgid bigint		null references messages (id),
	mimetype text		not null,
	body text		not null
)
WITHOUT OIDS;

-- intended constraint:
--   SELECT messages.pr, attachments.pr
--   FROM messages, attachments
--   WHERE messages.id = attachments.msgid AND messages.pr <> attachments.pr
-- should always be empty.
-- (XXX this is gross.)