CREATE TABLE folders (
	folder_id INTEGER PRIMARY KEY,
	parent_id INTEGER,
	change_number INTEGER UNIQUE NOT NULL,
	is_deleted INTEGER DEFAULT 0,
	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 folder_delete_index ON folders(parent_id, is_deleted);

CREATE TABLE messages (
	message_id INTEGER PRIMARY KEY,
	parent_fid INTEGER,
	parent_attid INTEGER,
	is_deleted INTEGER DEFAULT 0,
	is_associated INTEGER,
	change_number INTEGER UNIQUE NOT NULL,
	message_size INTEGER NOT NULL,
	group_id INTEGER 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_delete_index ON messages(parent_fid, is_associated, is_deleted);

CREATE TABLE read_states (
	message_id INTEGER NOT NULL,
	username TEXT COLLATE NOCASE NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_states_index ON read_states(message_id);

CREATE UNIQUE INDEX state_username_index ON read_states(message_id, username);

CREATE TABLE read_cns (
	message_id INTEGER NOT NULL,
	username TEXT COLLATE NOCASE NOT NULL,
	read_cn INTEGER UNIQUE NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_readcn_index ON read_cns(message_id);

CREATE UNIQUE INDEX readcn_username_index ON read_cns(message_id, username);

CREATE TABLE replca_mapping (
	replid INTEGER PRIMARY KEY AUTOINCREMENT,
	replguid TEXT COLLATE NOCASE UNIQUE NOT NULL);
	