-- add max_arrival_time to message, which will be used to speed up thread list speed
ALTER TABLE message add column max_arrival_time bigint NOT NULL DEFAULT -1;;
CREATE INDEX max_arrival_time_idx ON message(max_arrival_time);;


-- when a new message is inserted, update messages related to the new thread_id
CREATE TRIGGER max_arrival_time_insert_message_trigger after INSERT ON message
BEGIN
	UPDATE message SET max_arrival_time =
	ifnull((SELECT MAX(arrival_time) FROM message WHERE id_thread=new.id_thread AND id_mailbox NOT IN (-3, -5, -6)), -1)
	WHERE id_thread = new.id_thread;
END;;

-- when a message is moved or arrival_time is modified, update messages related to the new thread_id
CREATE TRIGGER max_arrival_time_update_message_trigger after UPDATE OF id_mailbox, arrival_time ON message
BEGIN
	UPDATE message SET max_arrival_time =
	ifnull((SELECT MAX(arrival_time) FROM message WHERE id_thread=new.id_thread AND id_mailbox NOT IN (-3, -5, -6)), -1)
	WHERE id_thread = new.id_thread;
END;;

-- when a message is deleted, update messages related to the old thread_id
CREATE TRIGGER max_arrival_time_delete_message_trigger after DELETE ON message
BEGIN
	UPDATE message SET max_arrival_time =
	ifnull( (SELECT MAX(arrival_time) FROM message WHERE id_thread=old.id_thread AND id_mailbox NOT IN (-3, -5, -6)), -1)
	WHERE id_thread = old.id_thread;
END;;

-- create tmp table for updating max_arrival_time for all existing threads
CREATE TEMP table tmp_thread_max_arrival_time AS
SELECT id_thread AS id, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox NOT IN (-3, -5, -6) GROUP BY id_thread;;
CREATE INDEX temp.id_idx ON tmp_thread_max_arrival_time(id);;
CREATE INDEX temp.max_arrival_time_idx ON tmp_thread_max_arrival_time(max_arrival_time);;

-- updating max_arrival_time for all existing threads
UPDATE message SET max_arrival_time =
ifnull((SELECT max_arrival_time FROM tmp_thread_max_arrival_time WHERE tmp_thread_max_arrival_time.id = id_thread), -1)
WHERE id_thread IN (SELECT id FROM thread);;

-- done
UPDATE config SET value = '8' WHERE key = 'version';;