CREATE SEQUENCE seq_email_id;
CREATE TABLE email (
    email_id      int8 NOT NULL DEFAULT nextval('seq_email_id'),
    email_address text NOT NULL
);
ALTER SEQUENCE seq_email_id OWNED BY email.email_id;
ALTER TABLE email ADD CONSTRAINT p_email PRIMARY KEY (email_id);
ALTER TABLE email ADD CONSTRAINT u_email UNIQUE (email_address);

CREATE SEQUENCE seq_message_id;
CREATE TABLE message (
    message_id      int8 NOT NULL DEFAULT nextval('seq_message_id'),
    queue_id        int8 NOT NULL,
    rfc_822_code    text,
    sender_ip       inet,
    sender_email_id int8,
    rsyslog_ip      inet NOT NULL DEFAULT inet_client_addr(),
    is_complete     boolean NOT NULL DEFAULT false
)
WITH (fillfactor=50);
ALTER SEQUENCE seq_message_id OWNED BY message.message_id;

CREATE TABLE recipient(
    message_id         int8,
    recipient_email_id int8
);

CREATE TYPE sendstatus
    AS ENUM ('deferred', 'bounced', 'expired', 'rejected', 'sent');
CREATE TABLE attempt (
    message_id         int8 NOT NULL,
    recipient_email_id int8 NOT NULL,
    attempt_no         int2,
    tstamp             timestamptz NOT NULL,
    server_response    text NOT NULL,
    status             sendstatus NOT NULL
);

CREATE OR REPLACE FUNCTION hex2int8(text) RETURNS bigint IMMUTABLE STRICT AS $hex2int8$
    SELECT ('x'||$1)::bit(48)::bigint;
$hex2int8$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION get_email_id(text) RETURNS int8 AS $get_email_id$
DECLARE
    vemail_address text;
    vemail_id      int8;

BEGIN
    vemail_address := split_part(split_part($1, '>,', 1), '<', 2);
    IF vemail_address = '' THEN
        vemail_address := '<>';
    END IF;
    SELECT email_id INTO vemail_id FROM email 
        WHERE email_address = vemail_address;
    IF vemail_id IS NULL THEN
        vemail_id := nextval('seq_email_id');
        INSERT INTO email(email_id, email_address) 
            VALUES (vemail_id, vemail_address);
    END IF;
    RETURN vemail_id;
END;
$get_email_id$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION attempt_t() RETURNS trigger AS $attempts_t$
BEGIN
    SELECT count(*) + 1 INTO NEW.attempt_no FROM attempt WHERE message_id = NEW.message_id;
    NEW.status = split_part(NEW.server_response, ' ', 1);
    NEW.server_response = regexp_replace(NEW.server_response, '^[^ ]* \((.*)\)$', '\1');
    RETURN NEW;
END;
$attempts_t$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION parsemsg_t() RETURNS trigger AS $parsemsg_t$
DECLARE
    vqueue_id           int8;
    vmessage_id         int8;
    vip_address         inet;
    vrecipient_email_id int8;
    message_body        text;
    filter_re           text DEFAULT '^ ([0-9A-F]+): (.*)$';

BEGIN
    IF NOT NEW.vrawmessage ~ filter_re THEN
        RETURN NULL;
    END IF;

    vqueue_id := hex2int8(regexp_replace(NEW.vrawmessage, filter_re, '\1'));
    message_body := regexp_replace(NEW.vrawmessage, filter_re, '\2');

    SELECT message_id INTO vmessage_id FROM message
        WHERE queue_id = vqueue_id AND is_complete = false;
    IF vmessage_id IS NULL THEN
        vmessage_id := nextval('seq_message_id');
        INSERT INTO message(message_id, queue_id)
            VALUES (vmessage_id, vqueue_id);
    END IF;

    IF message_body = 'removed' THEN
        UPDATE message SET is_complete = true WHERE message_id = vmessage_id;
        RETURN NULL;
    END IF;

    IF message_body ~ '^client=' THEN
        UPDATE message SET sender_ip = rtrim(split_part(message_body, '[', 2), ']')::inet
            WHERE message_id = vmessage_id;
    ELSIF message_body ~ '^message-id=' THEN
        UPDATE message
            SET rfc_822_code = rtrim(split_part(message_body, '<', 2), '>')
            WHERE message_id = vmessage_id;

    ELSIF message_body ~ '^from=' THEN
        UPDATE message SET sender_email_id = get_email_id(message_body)
            WHERE message_id = vmessage_id;

    ELSIF message_body ~ '^to=' THEN
        vrecipient_email_id := get_email_id(message_body);
        INSERT INTO recipient(message_id, recipient_email_id)
        SELECT vmessage_id, vrecipient_email_id
         WHERE NOT EXISTS (
            SELECT 1 FROM recipient WHERE message_id=vmessage_id
               AND recipient_email_id=vrecipient_email_id);
        INSERT INTO attempt(message_id, recipient_email_id, tstamp, server_response)
        SELECT vmessage_id, vrecipient_email_id, NEW.vnow, split_part(fld,'=',2)
          FROM regexp_split_to_table(NEW.vrawmessage, ', ') AS t(fld)
         WHERE split_part(fld,'=',1)='status';
    END IF;

    RETURN NULL;
END;
$parsemsg_t$ LANGUAGE plpgsql;

CREATE TRIGGER t_attempt BEFORE INSERT ON attempt FOR EACH ROW EXECUTE PROCEDURE attempt_t();
CREATE TRIGGER t_parselog INSTEAD OF INSERT ON unparsed FOR EACH ROW EXECUTE PROCEDURE parsemsg_t();