-- this upgrade will be executed only if mailbox table is not exist
-- for reparing dropped mailbox table

-- delete message-related records to avoid messages having wrong id_mailbox
DELETE FROM attachment;
DELETE FROM many_message_has_many_recipient;
DELETE FROM many_message_has_many_reference;
DELETE FROM many_thread_has_many_label;
DELETE FROM message;
DELETE FROM notified_message;
DELETE FROM reference;
DELETE FROM thread;

-- delete mailbox-related records
DELETE FROM mailbox_permission;
DELETE FROM config WHERE key='archive_mailbox';
DELETE FROM config WHERE key='priority_mailbox';

-- create mailbox, should be exactly the same schema as mailbox_tmp in 10.sql
CREATE TABLE mailbox(
	id integer NOT NULL,
	is_subscribed boolean NOT NULL,
	uid_validity bigint NOT NULL,
	highest_mod_seq varchar(32) NOT NULL,
	path varchar(256) NOT NULL,
	deleted integer NOT NULL DEFAULT 0,
	is_notification_enabled boolean NOT NULL DEFAULT 0,
	is_own boolean NOT NULL DEFAULT 1,
	my_permission text NOT NULL DEFAULT '',
	owner text NOT NULL DEFAULT '',
	CONSTRAINT mailbox_id_pk PRIMARY KEY (id),
	CONSTRAINT mailbox_uid_validity_owner_uk UNIQUE (uid_validity, owner),
	CONSTRAINT mailbox_path_uk UNIQUE (path)
);

-- insert default mailbox record
INSERT INTO mailbox(id, is_subscribed, uid_validity, highest_mod_seq, path) VALUES(0, 0, 0, 0, '');

UPDATE config SET value = '11' WHERE key = 'version';