CREATE TABLE folders (
	folder_id INTEGER PRIMARY KEY,
	parent_id INTEGER,
	change_number INTEGER UNIQUE NOT NULL,
	is_search INTEGER DEFAULT 0,
	search_flags INTEGER DEFAULT NULL,
	search_criteria BLOB DEFAULT NULL,
	cur_eid INTEGER NOT NULL,
	max_eid INTEGER NOT NULL,
	FOREIGN KEY (parent_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX search_index ON folders(is_search);

CREATE TABLE messages (
	message_id INTEGER PRIMARY KEY,
	parent_fid INTEGER,
	parent_attid INTEGER,
	is_associated INTEGER,
	change_number INTEGER UNIQUE NOT NULL,
	read_cn INTEGER UNIQUE DEFAULT NULL,
	read_state INTEGER DEFAULT 0,
	message_size INTEGER NOT NULL,
	group_id INTEGER DEFAULT NULL,
	timer_id INTEGER DEFAULT NULL,
	mid_string TEXT DEFAULT NULL,
	FOREIGN KEY (parent_fid)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (parent_attid)
		REFERENCES attachments (attachment_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX pid_messages_index ON messages(parent_fid);

CREATE INDEX attid_messages_index ON messages(parent_attid);

CREATE INDEX assoc_index ON messages(is_associated);
		
CREATE INDEX parent_assoc_index ON messages(parent_fid, is_associated);

CREATE INDEX parent_read_assoc_index ON messages(parent_fid, read_state, is_associated);

CREATE TABLE receive_table (
	class TEXT COLLATE NOCASE UNIQUE NOT NULL,
	folder_id INTEGER NOT NULL,
	modified_time INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_receive_index ON receive_table(folder_id);

CREATE TABLE search_scopes (
	folder_id INTEGER NOT NULL,
	included_fid INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (included_fid)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_scope_index ON search_scopes(folder_id);

CREATE INDEX included_scope_index ON search_scopes(included_fid);

CREATE TABLE search_result (
	folder_id INTEGER NOT NULL,
	message_id INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_result_index ON search_result(folder_id);

CREATE INDEX mid_result_index ON search_result(message_id);

CREATE UNIQUE INDEX search_message_index ON search_result(folder_id, message_id);
