CREATE TABLE contact (
	id integer NOT NULL,
	family_name varchar(1024) NOT NULL DEFAULT '',
	given_name varchar(1024) NOT NULL DEFAULT '',
	additional_name varchar(1024) NOT NULL DEFAULT '',
	detail text NOT NULL DEFAULT '',
	create_time bigint NOT NULL DEFAULT (strftime('%s', 'now')),
	modify_time bigint NOT NULL DEFAULT (strftime('%s', 'now')),
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT contact_pk PRIMARY KEY (id)
);

CREATE TABLE contact_mail (
	contact_id integer NOT NULL,
	mail_address varchar(1024) NOT NULL,
	is_primary boolean NOT NULL DEFAULT FALSE,
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT contact_mail_uk UNIQUE (contact_id, mail_address),
	CONSTRAINT contact_mail_id_fk FOREIGN KEY (contact_id)
		REFERENCES contact (id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE contact_search (
	contact_id integer NOT NULL,
	search text NOT NULL,
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT contact_search_uk UNIQUE (contact_id, search),
	CONSTRAINT contact_search_id_fk FOREIGN KEY (contact_id)
		REFERENCES contact (id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE contact_photo (
	contact_id integer NOT NULL,
	photo BLOB NOT NULL,
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT contact_photo_uk UNIQUE (contact_id),
	CONSTRAINT contact_search_id_fk FOREIGN KEY (contact_id)
		REFERENCES contact (id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE contact_group (
	id integer NOT NULL,
	name varchar(1024) NOT NULL DEFAULT '',
	background_color varchar(6) NOT NULL DEFAULT '',
	text_color varchar(6) NOT NULL DEFAULT '',
	create_time bigint NOT NULL DEFAULT (strftime('%s', 'now')),
	modify_time bigint NOT NULL DEFAULT (strftime('%s', 'now')),
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT group_pk PRIMARY KEY (id)
);

CREATE TABLE group_mapper (
	group_id integer NOT NULL,
	contact_id integer NOT NULL,
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT group_mapper_uk UNIQUE (group_id, contact_id),
	CONSTRAINT group_mapper_group_id_fk FOREIGN KEY (group_id)
		REFERENCES contact_group (id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT group_mapper_contact_id_fk FOREIGN KEY (contact_id)
		REFERENCES contact (id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE config (
	key varchar(256) NOT NULL,
	value text NOT NULL,
	updated integer NOT NULL DEFAULT 0,
	CONSTRAINT config_key_pk PRIMARY KEY (key)
);

INSERT INTO contact_group (id, name, background_color, text_color) VALUES(-1, 'starred', 'ffffff', 'ffffff');
INSERT INTO contact_group (id, name, background_color, text_color) VALUES(-2, 'sender', 'ffffff', 'ffffff');

INSERT INTO config (key, value) VALUES('version', '1');