/**

 * Contacts model database schema (file version v8).

 *

 *

 */

-- CREATE SEQUENCE contacts_id_sequence INITIAL_VALUE=0 INCREMENT=1;

CREATE table contacts

	(

	cm_id					INTEGER NOT NULL, -- see (1): DEFAULT NEXT VALUE OF contacts_id_sequence

	cm_type					INTEGER NOT NULL,

	cm_preftemplaterefid	INTEGER NULL,

	cm_uidstring			VARCHAR(244) NULL,

	cm_last_modified		TIMESTAMP NULL,

	cm_contactcreationdate	TIMESTAMP NULL,

	cm_attributes			INTEGER NOT NULL, -- should be UNSIGNED

	cm_replicationcount		INTEGER NOT NULL, -- should be UNSIGNED

	cm_header				LONGBLOB NULL, -- BLOB

	cm_textblob				LONGBLOB NULL, -- BLOB

	cm_searchabletext		LONGTEXT NULL, -- CLOB

	PRIMARY KEY (cm_id), 

	FOREIGN KEY (cm_preftemplaterefid) REFERENCES contacts(cm_id) 

		ON DELETE SET NULL 

		ON UPDATE CASCADE

	);

CREATE table identitytable

	(

	parent_cmid			INTEGER NOT NULL,

	cm_firstname		VARCHAR(255) NULL,

	cm_lastname			VARCHAR(255) NULL,

	cm_companyname		VARCHAR(255) NULL,

	cm_type				INTEGER NULL,

	cm_attributes		INTEGER NULL, -- should be UNSIGNED

	cm_hintfield		BIT(8) NULL, -- binary(1)

	cm_exthintfield		BIT(16) NULL,-- binary(2)

	cm_firstnmprn		VARCHAR(255) NULL,

	cm_lastnmprn		VARCHAR(255) NULL,

	cm_companynmprn		VARCHAR(255) NULL,

	PRIMARY KEY (parent_cmid),

	FOREIGN KEY (parent_cmid) REFERENCES contacts(cm_id)

	);

-- CREATE SEQUENCE emailtable_fieldid_sequence INITIAL_VALUE=0 INCREMENT=1;

CREATE table emailtable

	(

	emailparent_cmid	INTEGER NOT NULL,

	email_fieldid		INTEGER NOT NULL, -- see (2) DEFAULT NEXT VALUE OF emailtable_fieldid_sequence

	emailaddress		VARCHAR(255) NULL,

	PRIMARY KEY (email_fieldid, emailparent_cmid),

	FOREIGN KEY (emailparent_cmid) REFERENCES contacts(cm_id)

	);

CREATE table phone

	(

	cm_id						INTEGER NOT NULL,

	cm_phonematching			INTEGER NOT NULL,

	cm_extendedphonematching	INTEGER NOT NULL,

	PRIMARY KEY (cm_phonematching, cm_id),

	FOREIGN KEY (cm_id) REFERENCES contacts(cm_id)

	);

CREATE table groups

	(

	cm_id		INTEGER NOT NULL,

	cm_members	INTEGER NOT NULL, -- this should really be cm_member, but it is called cm_members in the current database

	PRIMARY KEY (cm_members, cm_id),

	FOREIGN KEY (cm_id) REFERENCES contacts(cm_id),

	FOREIGN KEY (cm_members) REFERENCES contacts(cm_id)

	);

CREATE table groups2

	(

	cm_id			INTEGER NOT NULL,

	cm_groupmembers	LONGBLOB NULL, -- BLOB

	PRIMARY KEY (cm_id),

	FOREIGN KEY (cm_id) REFERENCES contacts(cm_id)

	);

CREATE table sync

	(

	cm_id			INTEGER NOT NULL,	-- why is this UNSIGNED in ?

	cm_lastsyncdate	TIMESTAMP NULL,

	PRIMARY KEY (cm_id),

	FOREIGN KEY (cm_id) REFERENCES contacts(cm_id) -- does this reference contacts?

	);

CREATE table preferences

	(

	cm_preffileid				SMALLINT NULL,

	cm_preftemplateid			INTEGER NOT NULL,

	cm_prefowncardid			INTEGER NULL,

	cm_prefcardtemplateprefid	INTEGER NULL,

	cm_preffilever				INTEGER NOT NULL,

	cm_prefcardtemplateid		LONGBLOB NULL, -- BLOB

	cm_prefgroupidlist			LONGBLOB NULL, -- BLOB

	cm_creationdate				TIMESTAMP NULL,

	cm_machineuid				BIGINT NULL,

	cm_prefsortorder			LONGBLOB NULL, -- BLOB

	FOREIGN KEY (cm_preftemplateid) REFERENCES contacts(cm_id),

	FOREIGN KEY (cm_prefowncardid) REFERENCES contacts(cm_id),

	FOREIGN KEY (cm_prefcardtemplateprefid) REFERENCES contacts(cm_id)

	);