--
-- 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'),
	posttime timestamp	not null,
	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),
	number_in_pr bigint	not null,
	who bigint		not null references users (id),
	parent_id bigint	null references messages (id),
	posttime timestamp	not null,
	mimetype text		not null,
	body text		not null,

	-- we don't keep these directly, they go into an admin log entry
	-- XXX: we may need to keep the external message-id
	--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;

CREATE SEQUENCE next_attachid;

-- for patches and mime-attachments
CREATE TABLE attachments (
	id bigint		primary key default nextval('next_attachid'),
	number_in_pr bigint	not null,
	msgid bigint		not null references messages (id),
	mimetype text		not null,
	body text		not null
)
WITHOUT OIDS;

-- Create an index for number_in_pr both to enforce uniqueness and
-- to enable lookup.
CREATE UNIQUE INDEX ON messages (pr, number_in_pr);
-- XXX: does this work?
CREATE UNIQUE INDEX ON
  SELECT messages.pr, attachments.number_in_pr
  FROM messages, attachments
  WHERE messages.id = attachments.msgid
;