====== Non-trigger Functions ======
These functions are called by the UI or manitou-mdx

===== trash_msg =====
<code sql>
CREATE OR REPLACE FUNCTION trash_msg(in_mail_id integer, in_op integer) RETURNS
integer AS $$
DECLARE
new_status int;
BEGIN
  UPDATE mail SET status=status|16,operator=in_op WHERE mail_id=in_mail_id;
  SELECT INTO new_status status FROM mail WHERE mail_id=in_mail_id;
  RETURN new_status;
END;
$$ LANGUAGE 'plpgsql';
</code>

===== trash_msg_set =====
<code sql>
CREATE OR REPLACE FUNCTION trash_msg_set(in_array_mail_id int[], in_op int) RETU
RNS int AS $$
DECLARE
cnt int;
BEGIN
  UPDATE mail SET status=status|16, operator=in_op WHERE mail_id=any(in_array_mail_id);
  GET DIAGNOSTICS cnt=ROW_COUNT;
  return cnt;
END;
$$ LANGUAGE 'plpgsql';
</code>

===== untrash_msg =====
<code sql>
CREATE OR REPLACE FUNCTION untrash_msg(in_mail_id int, in_op int) RETURNS int AS
 $$
DECLARE
new_status int;
BEGIN
  UPDATE mail SET status=status&(~16),operator=in_op WHERE mail_id=in_mail_id;
  SELECT INTO new_status status FROM mail WHERE mail_id=in_mail_id;
  RETURN new_status;
END;
$$ LANGUAGE 'plpgsql';
</code>

===== delete_msg =====
<code sql>
CREATE OR REPLACE FUNCTION delete_msg(integer) RETURNS integer AS $$
DECLARE
        id ALIAS FOR $1;
        attch RECORD;
        cnt integer;
        o oid;
BEGIN
  DELETE FROM notes WHERE mail_id=id;
  DELETE FROM mail_addresses WHERE mail_id=id;
  DELETE FROM header WHERE mail_id=id;
  DELETE FROM body WHERE mail_id=id;
  DELETE FROM mail_tags WHERE mail_id=id;

  FOR attch IN SELECT a.attachment_id,c.content,c.fingerprint
    FROM attachments a, attachment_contents c WHERE a.mail_id=id AND c.attachment_id=a.attachment_id
  LOOP
    cnt=0;
    IF attch.fingerprint IS NOT NULL THEN
      -- check if that content is shared with another message's attachment
      SELECT count(*) INTO cnt FROM attachment_contents WHERE fingerprint=attch.fingerprint 
      AND attachment_id!=attch.attachment_id;
    END IF;
    IF (cnt=0) THEN
      PERFORM lo_unlink(attch.content);
    END IF;
    DELETE FROM attachment_contents WHERE attachment_id=attch.attachment_id;
  END LOOP;

  DELETE FROM attachments WHERE mail_id=id;
  UPDATE mail SET in_reply_to=NULL WHERE in_reply_to=id;

  SELECT mail_text INTO o FROM raw_mail WHERE mail_id=id;
  IF FOUND THEN
     PERFORM lo_unlink(o);
     DELETE FROM raw_mail WHERE mail_id=id;
  END IF;

  DELETE FROM mail WHERE mail_id=id;
  IF (FOUND) THEN
          RETURN 1;
  ELSE
          RETURN 0;
  END IF;
END;
$$ LANGUAGE 'plpgsql';
</code>

===== delete_msg_set =====
<code sql>
CREATE OR REPLACE FUNCTION delete_msg_set(in_array_mail_id int[]) RETURNS int AS
 $$
DECLARE
 cnt int;
BEGIN
 cnt:=0;
 FOR idx IN array_lower(in_array_mail_id,1)..array_upper(in_array_mail_id,1) LOOP
   cnt:=cnt + delete_msg(in_array_mail_id[idx]);
 END LOOP;
 RETURN cnt;
END;
$$ LANGUAGE 'plpgsql';
</code>

====== Trigger Functions ======
The purpose of these functions is to maintain the contents of the ''mail_status'' table, which caches the status of current messages (current=not yet processed)

===== insert_mail =====
<code sql>
CREATE OR REPLACE FUNCTION insert_mail() RETURNS TRIGGER AS $$
BEGIN
        IF NEW.status&(256+32+16)=0 THEN
          -- The message is not yet sent, archived, or trashed
          INSERT INTO mail_status(mail_id,status) VALUES(new.mail_id,new.status);
        END IF;
        RETURN new;
END;
$$ LANGUAGE 'plpgsql';
</code>

===== update_mail =====
<code sql>
CREATE OR REPLACE FUNCTION update_mail() RETURNS TRIGGER AS $$
DECLARE
 rc int4;
BEGIN
   IF new.status!=old.status THEN
        IF NEW.status&(256+32+16)=0 THEN
          -- The message is not yet sent, archived, or trashed
          UPDATE mail_status
            SET status = new.status
           WHERE mail_id = new.mail_id;
          GET DIAGNOSTICS rc = ROW_COUNT;
          if rc=0 THEN
            INSERT INTO mail_status(mail_id,status) VALUES(new.mail_id,new.status);
          END IF;
        ELSE
          -- The mail has been "processed"
          DELETE FROM mail_status
           WHERE mail_id = new.mail_id;
        END IF;
   END IF;
   RETURN new;
END;
$$ LANGUAGE 'plpgsql';
</code>


===== delete_mail =====
<code sql>
CREATE OR REPLACE FUNCTION delete_mail() RETURNS TRIGGER AS $$
BEGIN
        DELETE FROM mail_status WHERE mail_id=OLD.mail_id;
        RETURN old;
END;
$$ LANGUAGE 'plpgsql';
</code>

====== Trigger declarations ======
<code sql>
CREATE TRIGGER update_mail AFTER UPDATE ON mail
 FOR EACH ROW EXECUTE PROCEDURE update_mail();

CREATE TRIGGER insert_mail AFTER INSERT ON mail
 FOR EACH ROW EXECUTE PROCEDURE insert_mail();

CREATE TRIGGER delete_mail AFTER DELETE ON mail
 FOR EACH ROW EXECUTE PROCEDURE delete_mail();
</code>