-- @tag: email_journal
-- @description: Journal für verschickte E-Mails
-- @depends: release_3_3_0

-- Note: sender_id may be NULL to indicate a mail sent by the system
-- without a user being logged in – e.g. by the task server.
CREATE TABLE email_journal (
  id              SERIAL    NOT NULL,
  sender_id       INTEGER,
  "from"          TEXT      NOT NULL,
  recipients      TEXT      NOT NULL,
  sent_on         TIMESTAMP NOT NULL DEFAULT now(),
  subject         TEXT      NOT NULL,
  body            TEXT      NOT NULL,
  headers         TEXT      NOT NULL,
  status          TEXT      NOT NULL,
  extended_status TEXT      NOT NULL,
  itime           TIMESTAMP NOT NULL DEFAULT now(),
  mtime           TIMESTAMP NOT NULL DEFAULT now(),

  PRIMARY KEY (id),
  FOREIGN KEY (sender_id) REFERENCES employee (id),
  CONSTRAINT valid_status CHECK (status IN ('ok', 'failed'))
);

CREATE TABLE email_journal_attachments (
  id               SERIAL    NOT NULL,
  position         INTEGER   NOT NULL,
  email_journal_id INTEGER   NOT NULL,
  name             TEXT      NOT NULL,
  mime_type        TEXT      NOT NULL,
  content          BYTEA     NOT NULL,
  itime            TIMESTAMP NOT NULL DEFAULT now(),
  mtime            TIMESTAMP NOT NULL DEFAULT now(),

  PRIMARY KEY (id),
  FOREIGN KEY (email_journal_id) REFERENCES email_journal (id) ON DELETE CASCADE
);

CREATE TRIGGER mtime_email_journal             BEFORE UPDATE ON email_journal             FOR EACH ROW EXECUTE PROCEDURE set_mtime();
CREATE TRIGGER mtime_email_journal_attachments BEFORE UPDATE ON email_journal_attachments FOR EACH ROW EXECUTE PROCEDURE set_mtime();