--
-- 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'),
	msgid bigint		not null references messages (id),
	number_in_msg bigint	not null,
	mimetype text		not null,
	body text		not null
)
WITHOUT OIDS;

-- Create indexes for number_in_pr and number_in_msg both to enforce
-- uniqueness and to enable lookup.
CREATE UNIQUE INDEX ON messages (pr, number_in_pr);
CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg);

-- The raw mail queue is append-only except to admins.
GRANT SELECT, INSERT, UPDATE, DELETE ON rawmail TO swallowtail_admin;
GRANT INSERT ON rawmail TO swallowtail_writer;

-- Messages and attachments are generically accessible, but we don't
-- allow ordinary writers to modify them. (XXX: is that silly?)
GRANT SELECT, INSERT, UPDATE, DELETE ON messages TO swallowtail_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO swallowtail_admin;
GRANT SELECT, INSERT ON messages TO swallowtail_writer;
GRANT SELECT, INSERT ON attachments TO swallowtail_writer;
GRANT SELECT ON messages, attachments TO swallowtail_reader;
GRANT SELECT ON messages, attachments TO swallowtail_public;