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

CREATE TABLE allocated_eids (
	range_begin INTEGER NOT NULL,
	range_end INTEGER NOT NULL,
	allocate_time INTEGER NOT NULL,
	is_system INTEGER DEFAULT NULL);

CREATE INDEX time_index ON allocated_eids(allocate_time);

CREATE TABLE named_properties (
	propid INTEGER PRIMARY KEY AUTOINCREMENT,
	name_string TEXT COLLATE NOCASE NOT NULL);

CREATE TABLE store_properties (
	proptag INTEGER UNIQUE NOT NULL,
	propval NONE NOT NULL);

CREATE TABLE folder_properties (
	folder_id INTEGER NOT NULL,
	proptag INTEGER NOT NULL,
	propval NONE NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_properties_index ON folder_properties(folder_id);
		
CREATE UNIQUE INDEX folder_property_index ON folder_properties(folder_id, proptag);

CREATE TABLE permissions (
	member_id INTEGER PRIMARY KEY AUTOINCREMENT,
	folder_id INTEGER NOT NULL,
	username TEXT COLLATE NOCASE NOT NULL,
	permission INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_permissions_index ON permissions(folder_id);

CREATE UNIQUE INDEX folder_username_index ON permissions(folder_id, username);

CREATE TABLE rules (
	rule_id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT COLLATE NOCASE,
	provider TEXT COLLATE NOCASE NOT NULL,
	sequence INTEGER NOT NULL,
	state INTEGER NOT NULL,
	level INTEGER,
	user_flags INTEGER,
	provider_data BLOB,
	condition BLOB NOT NULL,
	actions BLOB NOT NULL,
	folder_id INTEGER NOT NULL,
	FOREIGN KEY (folder_id)
		REFERENCES folders (folder_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX fid_rules_index on rules(folder_id);

CREATE TABLE message_properties (
	message_id INTEGER NOT NULL,
	proptag INTEGER NOT NULL,
	propval NONE NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_properties_index ON message_properties(message_id);
		
CREATE UNIQUE INDEX message_property_index ON message_properties(message_id, proptag);

CREATE INDEX proptag_propval_index ON message_properties(proptag, propval);

CREATE TABLE message_changes (
	message_id INTEGER NOT NULL,
	change_number INTEGER NOT NULL,
	indices BLOB NOT NULL,
	proptags BLOB NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_changes_index ON message_changes(message_id);

CREATE TABLE recipients (
	recipient_id INTEGER PRIMARY KEY AUTOINCREMENT,
	message_id INTEGER NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_recipients_index ON recipients(message_id);
		
CREATE TABLE recipients_properties (
	recipient_id INTEGER NOT NULL,
	proptag INTEGER NOT NULL,
	propval NONE NOT NULL,
	FOREIGN KEY (recipient_id)
		REFERENCES recipients (recipient_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX rid_properties_index ON recipients_properties(recipient_id);

CREATE UNIQUE INDEX recipient_property_index ON recipients_properties(recipient_id, proptag);

CREATE TABLE attachments (
	attachment_id INTEGER PRIMARY KEY AUTOINCREMENT,
	message_id INTEGER NOT NULL,
	FOREIGN KEY (message_id)
		REFERENCES messages (message_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX mid_attachments_index ON attachments(message_id);

CREATE TABLE attachment_properties (
	attachment_id INTEGER NOT NULL,
	proptag INTEGER NOT NULL,
	propval NONE NOT NULL,
	FOREIGN KEY (attachment_id)
		REFERENCES attachments (attachment_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE);

CREATE INDEX attid_properties_index ON attachment_properties(attachment_id);

CREATE UNIQUE INDEX attachment_property_index ON attachment_properties(attachment_id, proptag);
