CREATE TABLE configurations (
	config_id INTEGER PRIMARY KEY,
	config_value NONE NOT NULL);

CREATE TABLE folders (
	folder_id INTEGER PRIMARY KEY,
	parent_fid INTEGER NOT NULL,
	commit_max INTEGER NOT NULL,
	name TEXT NOT NULL UNIQUE,
	uidnext INTEGER DEFAULT 0,
	unsub INTEGER DEFAULT 0,
	sort_field INTEGER DEFAULT 0);

CREATE INDEX parent_fid_index ON folders(parent_fid);

CREATE TABLE messages (
	message_id INTEGER PRIMARY KEY,
	folder_id INTEGER NOT NULL,
	mid_string TEXT NOT NULL UNIQUE,
	idx INTEGER DEFAULT NULL,
	mod_time INTEGER DEFAULT 0,
	uid INTEGER NOT NULL,
	unsent INTEGER DEFAULT 0,
	recent INTEGER DEFAULT 1,
	read INTEGER DEFAULT 0,
	flagged INTEGER DEFAULT 0,
	replied INTEGER DEFAULT 0,
	forwarded INTEGER DEFAULT 0,
	deleted INTEGER DEFAULT 0,
	subject TEXT NOT NULL,
	sender TEXT NOT NULL,
	rcpt TEXT NOT NULL,
	size INTEGER NOT NULL,
	ext TEXT DEFAULT NULL,
	received INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX folder_id_index ON messages(folder_id);

CREATE INDEX fid_idx_index ON messages(folder_id, idx);

CREATE INDEX fid_recent_index ON messages(folder_id, recent);

CREATE INDEX fid_read_index ON messages(folder_id, read);

CREATE INDEX fid_received_index ON messages(folder_id, received);

CREATE INDEX fid_uid_index ON messages(folder_id, uid);

CREATE INDEX fid_flagged_index ON messages(folder_id, flagged);

CREATE INDEX fid_subject_index ON messages(folder_id, subject);

CREATE INDEX fid_from_index ON messages(folder_id, sender);

CREATE INDEX fid_rcpt_index ON messages(folder_id, rcpt);

CREATE INDEX fid_size_index ON messages(folder_id, size);

CREATE TABLE mapping (
	message_id INTEGER PRIMARY KEY,
	mid_string TEXT NOT NULL,
	flag_string TEXT);
