DELETE FROM pg_largeobject;

DROP TABLE mailid CASCADE;
DROP TABLE main CASCADE;
DROP TABLE mailto CASCADE;
DROP TABLE mailcc CASCADE;
DROP TABLE attachment CASCADE;


CREATE TABLE mailid(
messageid text PRIMARY KEY
);

CREATE TABLE main(
messageid text,
from_email text,
subject text,
mailbody oid,
CONSTRAINT fk_main FOREIGN KEY (messageid) REFERENCES mailid,
CONSTRAINT pk_main PRIMARY KEY (messageid,from_email)
);

CREATE TABLE mailto(
messageid text,
mailto text,
CONSTRAINT fk_mailto FOREIGN KEY (messageid) REFERENCES mailid,
CONSTRAINT pk_mailto PRIMARY KEY (messageid,mailto)
);

CREATE TABLE mailcc(
messageid text,
mailcc text,
CONSTRAINT fk_mailcc FOREIGN KEY (messageid) REFERENCES mailid,
CONSTRAINT pk_mailcc PRIMARY KEY (messageid,mailcc)
);

CREATE TABLE attachment(
messageid text,
description text,
mime_type text,
attachment oid,
CONSTRAINT fk_attachment FOREIGN KEY (messageid) REFERENCES mailid,
CONSTRAINT pk_attachment PRIMARY KEY (messageid,description)
);