CREATE EXTENSION hstore;

CREATE TABLE lists (

    id                VARCHAR(64) PRIMARY KEY,

    language          VARCHAR(32) NOT NULL,

    msg_first         TIMESTAMP,
    msg_last          TIMESTAMP,
    msg_count         INT NOT NULL DEFAULT 0,

    CONSTRAINT date_check CHECK (msg_last >= msg_first),
    CONSTRAINT count_check CHECK (msg_count >= 0)

);

CREATE TABLE messages (

    id                SERIAL PRIMARY KEY,
    parent_id         INT REFERENCES messages(id),
    thread_id         INT,
    level             INT,
    hash_id           VARCHAR(32) NOT NULL UNIQUE,

    list              VARCHAR(32) NOT NULL REFERENCES lists(id),
    message_id        VARCHAR(200),
    in_reply_to       TEXT[],
    refs              TEXT[],
    sent              TIMESTAMP,

    subject           TEXT,
    author            TEXT,

    body_plain        TEXT,

    body_tsvector     tsvector,
    subject_tsvector  tsvector,

    headers           HSTORE,
    raw_message       TEXT
);

CREATE TABLE message_replies (

    id INT REFERENCES messages(id),
    parent_id INT REFERENCES messages(id),

    PRIMARY KEY (id, parent_id)

);

CREATE OR REPLACE FUNCTION message_update_tsvector() RETURNS trigger AS $$
BEGIN

    IF TG_OP = 'INSERT' THEN

        NEW.body_tsvector := to_tsvector('english', strip_replies(NEW.body_plain));
        NEW.subject_tsvector := to_tsvector('english', NEW.subject);

    ELSIF TG_OP = 'UPDATE' THEN

        IF NEW.body_plain IS DISTINCT FROM OLD.body_plain THEN
            NEW.body_tsvector := to_tsvector('english', strip_replies(NEW.body_plain));
        END IF;

        IF NEW.subject IS DISTINCT FROM OLD.subject THEN
            NEW.subject_tsvector := to_tsvector('english', NEW.subject);
        END IF;

    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

-- TODO detect sudden changes in subject, which usually mean someone started
-- a new thread by 'reply-to' an existing message
CREATE OR REPLACE FUNCTION message_update_thread() RETURNS trigger AS $$
DECLARE
    v_min_tid INT;
    v_max_tid INT;
    v_level   INT;
    v_id      INT;
    v_subject TEXT;
    v_len     INT;
BEGIN

    NEW.hash_id := md5(NEW.raw_message);

    IF (NEW.in_reply_to IS NOT NULL) THEN

        SELECT MIN(id), MIN(thread_id), MAX(thread_id), MIN(level) INTO v_id, v_min_tid, v_max_tid, v_level
          FROM messages WHERE list = NEW.list AND message_id = ANY(NEW.in_reply_to);

        IF v_min_tid IS NOT NULL THEN
            NEW.parent_id := v_id;
            NEW.thread_id := v_min_tid;
            NEW.level     := v_level+1;
        END IF;

        IF v_min_tid IS DISTINCT FROM v_max_tid THEN
            RAISE WARNING 'min = %, max = %', v_min_tid, v_max_tid;
        END IF;

    END IF;

    IF (NEW.thread_id IS NULL) AND (NEW.refs IS NOT NULL) THEN

        SELECT MIN(id), MIN(thread_id), MAX(thread_id), MIN(level) INTO v_id, v_min_tid, v_max_tid, v_level
          FROM messages WHERE list = NEW.list AND message_id = ANY(NEW.refs);

        IF v_min_tid IS NOT NULL THEN
            NEW.parent_id := v_id;
            NEW.thread_id := v_min_tid;
            NEW.level     := v_level+1;
        END IF;

        IF v_min_tid IS DISTINCT FROM v_max_tid THEN
            RAISE WARNING 'min = %, max = %', v_min_tid, v_max_tid;
        END IF;

    END IF;

    IF (NEW.thread_id IS NULL) AND (NEW.subject IS NOT NULL) AND (NEW.subject ~* '[Rr][Ee]:.*') THEN

        v_subject := NEW.subject;
        v_len := length(v_subject);

        LOOP
            v_subject := trim(regexp_replace(v_subject, '^[Rr][Ee]:\s?', ''));
            EXIT WHEN length(v_subject) = v_len;
            v_len := length(v_subject);
        END LOOP;

        SELECT MAX(id) INTO v_max_tid FROM messages
         WHERE subject = v_subject AND list = NEW.list AND NOT (subject ~* '[Rr][Ee]:.*');

        IF v_max_tid IS NOT NULL THEN

            SELECT level INTO v_level FROM messages WHERE id = v_max_tid;

            NEW.thread_id := v_max_tid;
            NEW.level     := v_level + 1;
        END IF;

    END IF;

    IF (NEW.thread_id IS NULL) THEN
        NEW.thread_id := NEW.id;
        NEW.level     := 0;
    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION message_fill_replies() RETURNS trigger AS $$
DECLARE
    v_min_id INT;
    v_max_id INT;
BEGIN

    INSERT INTO message_replies SELECT NEW.id, id
     FROM messages WHERE list = NEW.list AND message_id = ANY(NEW.in_reply_to);

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_vectors BEFORE INSERT OR UPDATE ON messages
FOR EACH ROW EXECUTE PROCEDURE message_update_tsvector();

CREATE TRIGGER update_thread BEFORE INSERT ON messages
FOR EACH ROW EXECUTE PROCEDURE message_update_thread();

CREATE TRIGGER fill_replies AFTER INSERT ON messages
FOR EACH ROW EXECUTE PROCEDURE message_fill_replies();

CREATE INDEX message_body_idx on messages using gin(body_tsvector);
CREATE INDEX message_subject_idx on messages using gin(subject_tsvector);
CREATE INDEX message_send_idx on messages(sent);
CREATE INDEX message_id_idx on messages(message_id);
CREATE INDEX message_subj_idx on messages(subject) WHERE NOT (subject ~* '[Rr][Ee]:.*');
CREATE INDEX message_thread_idx on messages(thread_id);


-- rewrap e-mail body to given line length
CREATE OR REPLACE FUNCTION rewrap(p_text TEXT, p_len INT) RETURNS text AS $$
DECLARE

    v_lines TEXT[];
    v_words TEXT[];

    v_line   TEXT;
    v_word   TEXT;
    v_result TEXT;

    line_idx INT;
    word_idx INT;
    v_len    INT;

BEGIN

    -- handle tabs (replace with 4 spaces)
    p_text := regexp_replace(p_text, '\t', '    ', 'g');

    v_lines := regexp_split_to_array(p_text, '\n');
    v_result := '';

    IF v_lines IS NULL THEN
        RETURN NULL;
    END IF;

    FOR line_idx IN array_lower(v_lines, 1) .. array_upper(v_lines, 1) LOOP

        v_line := v_lines[line_idx];

        IF length(v_line) < p_len THEN
            v_result := v_result || trim(trailing ' ' from v_line) || E'\n';
            v_line := '';
        ELSE
            
            v_words := regexp_split_to_array(v_line, ' ');
            v_line := '';

            FOR word_idx IN array_lower(v_words, 1) .. array_upper(v_words, 1) LOOP

                v_word := v_words[word_idx];

                IF length(v_line) + length(v_word) + 1 <= p_len THEN
                    v_line := v_line || v_word || ' ';
                ELSIF length(v_word) > p_len THEN

                    v_len  := p_len - length(v_line);

                    WHILE length(v_word) > 0 LOOP
                        v_line := v_line || substr(v_word, 0, v_len);
                        v_result := v_result || trim(trailing ' ' from v_line) || E'\n';
                        v_line  := '';
                        v_word := substr(v_word, v_len);
                    END LOOP;
                
                ELSE

                    v_result := v_result || trim(trailing ' ' from v_line) || E'\n';
                    v_line := v_words[word_idx] || ' ';

                    -- FIXME handle case when the line is a quotation (prepend >)

                END IF;

            END LOOP;

        END IF;

        IF length(v_line) > 0 THEN
            v_result := v_result || trim(trailing ' ' from v_line) || E'\n';
        END IF;

    END LOOP;

    RETURN v_result;

END;
$$ LANGUAGE plpgsql;

-- extracts e-mail address from the "From:" header
CREATE OR REPLACE FUNCTION getmail(p_text TEXT) RETURNS text AS $$
DECLARE
    v_email TEXT;
BEGIN

    IF p_text LIKE '%<%>%' THEN
        v_email := SUBSTRING(p_text,'<(.*)>');
    ELSE
        v_email := p_text;
    END IF;

    RETURN lower(trim(v_email));

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION strip_replies(p_text TEXT) RETURNS text AS $$
DECLARE
    v_result TEXT;
    v_lines  TEXT[];
    v_line   TEXT;
    line_idx INT;
BEGIN

    v_lines := regexp_split_to_array(p_text, '\n');
    v_result := '';

    IF v_lines IS NULL THEN
        RETURN NULL;
    END IF;

    FOR line_idx IN array_lower(v_lines, 1) .. array_upper(v_lines, 1) LOOP

        v_line := v_lines[line_idx];

        IF NOT v_line ~* '^>[\s>]*' THEN
            v_result := v_result || v_line || E'\n';
        END IF;

    END LOOP;

    RETURN v_result;

END;
$$ LANGUAGE plpgsql;

INSERT INTO lists (id, language) VALUES ('pgsql-general', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-hackers', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-novice', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-sql', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-bugs', 'english');
INSERT INTO lists (id, language) VALUES ('pgadmin-hackers', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-jdbc', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-odbc', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-patches', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-advocacy', 'english');
INSERT INTO lists (id, language) VALUES ('pgsql-announce', 'english');