----------------------------------------------------------------------
-- CCM SQLite Schema                                                --
--                                                                  --
----------------------------------------------------------------------

SELECT 'Creating Peeps Tables...';

---------------------------------
-- base contact manager tables --
---------------------------------
SELECT '* Creating Contact Manager Tables...';

--
-- countries table
--
SELECT '  o Creating cm_countries.';
CREATE TABLE cm_countries ( 
  id          INTEGER NOT NULL PRIMARY KEY, 
  abbr        VARCHAR(5) UNIQUE NOT NULL DEFAULT '', 
  name        VARCHAR(50) NOT NULL DEFAULT ''
);

-- populate countries
BEGIN TRANSACTION;
  INSERT INTO cm_countries(abbr,name) VALUES ('','');
  INSERT INTO cm_countries(abbr,name) VALUES ('US', 'United States of America');
  INSERT INTO cm_countries(abbr,name) VALUES ('UK', 'United Kingdom (Britain)');
END TRANSACTION;


--
-- states table
--
SELECT '  o Creating cm_states.';
CREATE TABLE cm_states ( 
  id          INTEGER NOT NULL PRIMARY KEY, 
  abbr        VARCHAR(5) UNIQUE NOT NULL DEFAULT '', 
  name        VARCHAR(50) UNIQUE NOT NULL DEFAULT ''
);

-- populate states
BEGIN TRANSACTION;
  INSERT INTO cm_states(abbr,name) VALUES ('', '');
  INSERT INTO cm_states(abbr,name) VALUES ('CA', 'California');
  INSERT INTO cm_states(abbr,name) VALUES ('OR', 'Oregon');
  INSERT INTO cm_states(abbr,name) VALUES ('TN', 'Tennessee');
  INSERT INTO cm_states(abbr,name) VALUES ('VA', 'Virginia');
END TRANSACTION;


--
-- labels
-- TODO: this should be pre-populated
--
SELECT '  o Creating cm_labels.';
CREATE TABLE cm_labels (
  id          INTEGER NOT NULL PRIMARY KEY,
  label       VARCHAR(100) UNIQUE NOT NULL DEFAULT ''
);

-- populate labels
BEGIN TRANSACTION;
  INSERT INTO cm_labels(label) VALUES ('');
  INSERT INTO cm_labels(label) VALUES ('home');
  INSERT INTO cm_labels(label) VALUES ('work');
  INSERT INTO cm_labels(label) VALUES ('cell');
  INSERT INTO cm_labels(label) VALUES ('workcell');
  INSERT INTO cm_labels(label) VALUES ('personal');
  INSERT INTO cm_labels(label) VALUES ('tech');
END TRANSACTION;


--
-- instant messenger services
--
SELECT '  o Creating cm_im_servicess.';
CREATE TABLE cm_im_services ( 
  id          INTEGER NOT NULL PRIMARY KEY, 

  abbr        VARCHAR(5) UNIQUE NOT NULL DEFAULT '', 
  name        VARCHAR(50) UNIQUE NOT NULL DEFAULT '',
  url         TEXT UNIQUE NOT NULL DEFAULT ''
);

-- im svs insert constraint trigger
CREATE TRIGGER tri_im_services BEFORE INSERT ON cm_im_services
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check format constraint
    WHEN NEW.url NOT LIKE '%_://_%'
    THEN RAISE(ABORT, 'insert on ct_im_services violates url format constraint')
  END;
END;

-- im svs update constraint trigger
CREATE TRIGGER tru_im_services BEFORE UPDATE ON cm_im_services
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check format constraint
    WHEN NEW.url NOT LIKE '%_://_%'
    THEN RAISE(ABORT, 'update on ct_im_services violates url format constraint')
  END;
END;

-- populate im services
BEGIN TRANSACTION;
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('','','http://example.com/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('AIM','AOL Instant Messenger', 'http://aim.com/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('ICQ','ICQ', 'http://icq.com/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('MSN','MSN Messenger', 'http://msn.com/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('YHM','Yahoo Messenger', 'http://im.yahoo.com/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('JBR','Jabber', 'http://jabber.org/');
  INSERT INTO cm_im_services(abbr,name,url) VALUES ('IRC','Internet Relay Chat', 'http://irc.org/');
END TRANSACTION;


--
-- contact relationship types
-- (eg wife, sibling, etc)
--
SELECT '  o Creating cm_rels.';
CREATE TABLE cm_rels (
  id          INTEGER NOT NULL PRIMARY KEY,
  rel         VARCHAR(50) UNIQUE NOT NULL DEFAULT ''
);

-- populate rels
BEGIN TRANSACTION;
  INSERT INTO cm_rels(rel) VALUES ('');
  INSERT INTO cm_rels(rel) VALUES ('friend');
  INSERT INTO cm_rels(rel) VALUES ('family');
  INSERT INTO cm_rels(rel) VALUES ('parent');
  INSERT INTO cm_rels(rel) VALUES ('child');
  INSERT INTO cm_rels(rel) VALUES ('sibling');
  INSERT INTO cm_rels(rel) VALUES ('partner');
END TRANSACTION;

--
-- contacts table
--
SELECT '  o Creating cm_contacts.';
CREATE TABLE cm_contacts (
  id          INTEGER NOT NULL PRIMARY KEY,

  last_name   VARCHAR(50) NOT NULL DEFAULT '',
  first_name  VARCHAR(50) NOT NULL DEFAULT '',
  mi          VARCHAR(5) NOT NULL DEFAULT '',

  birthday    VARCHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00',

  ts_created  VARCHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00',
  ts_updated  VARCHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00'
);

-- contact insert constraint trigger
CREATE TRIGGER tri_cts BEFORE INSERT ON cm_contacts
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check format constraint
    WHEN (NEW.ts_created IS NOT NULL) AND (NEW.ts_created NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'insert on cm_contacts violates ts_created format constraint')
    WHEN (NEW.ts_updated IS NOT NULL) AND (NEW.ts_updated NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'insert on cm_contacts violates ts_updated format constraint')
    WHEN (NEW.birthday IS NOT NULL) AND (NEW.birthday NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'insert on cm_contacts violates birthday format constraint')
  END;
END;

-- contact update constraint trigger
CREATE TRIGGER tru_cts BEFORE UPDATE ON cm_contacts
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check format constraint
    WHEN (NEW.ts_created IS NOT NULL) AND (NEW.ts_created NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'update on cm_contacts violates ts_created format constraint')
    WHEN (NEW.ts_updated IS NOT NULL) AND (NEW.ts_updated NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'update on cm_contacts violates ts_updated format constraint')
    WHEN (NEW.birthday IS NOT NULL) AND (NEW.birthday NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
    THEN RAISE(ABORT, 'update on cm_contacts violates birthday format constraint')
  END;
END;

-- populate contacts
BEGIN TRANSACTION;
  INSERT INTO cm_contacts(last_name,first_name,mi,ts_created) VALUES ('Carney', 'Robert', '?',DATETIME('NOW'));
  INSERT INTO cm_contacts(last_name,first_name,mi,ts_created) VALUES ('Duncan', 'Paul', 'M',DATETIME('NOW'));
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Duncan', 'Erin', 'M');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Duncan', 'Richard', 'R');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Duncan', 'Robynn', 'W');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Duncan', 'Sara', 'E');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Gilbert', 'Tom', 'R');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Lowe', 'Richard', 'M');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Perry', 'Alonzo', 'E');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Rooke', 'Steven', 'P');
  INSERT INTO cm_contacts(last_name,first_name,mi) VALUES ('Rooke', 'Susan', 'E');
END TRANSACTION;


--------------------------
-- basic contact tables --
--------------------------
SELECT '* Creating Contact Tables.';

--
-- contact addresses
--
SELECT '  o Creating ct_addrs.';
CREATE TABLE ct_addrs ( 
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL 
              CONSTRAINT fk_addrs_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL
              CONSTRAINT fk_addrs_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  street      VARCHAR(50) NOT NULL DEFAULT '', 
  city        VARCHAR(50) NOT NULL DEFAULT '', 
  appt_no     VARCHAR(10) NOT NULL DEFAULT '', 
  zip_code    VARCHAR(10) NOT NULL DEFAULT '', 
  state_id    INTEGER NOT NULL
              CONSTRAINT fk_addrs_state REFERENCES cm_states(id) 
                         ON DELETE CASCADE,
  country_id  INTEGER NOT NULL
              CONSTRAINT fk_addrs_country REFERENCES cm_countries(id) 
                         ON DELETE CASCADE,

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- addr contact index
CREATE INDEX in_addrs_ct ON ct_addrs(ct_id);

-- addr insert constraint trigger
CREATE TRIGGER tri_addrs BEFORE INSERT ON ct_addrs
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_addrs violates foreign key constraint fk_addrs_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_addrs violates foreign key constraint fk_addrs_label')
    WHEN ((SELECT id FROM cm_states WHERE id = NEW.state_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_addrs violates foreign key constraint fk_addrs_state')
    WHEN ((SELECT id FROM cm_countries WHERE id = NEW.country_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_addrs violates foreign key constraint fk_addrs_country')

    -- check format constraint
    WHEN NEW.zip_code GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'insert on ct_addrs violates zip_code format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_addrs WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND street = NEW.street AND city = NEW.city AND appt_no = NEW.appt_no AND zip_code = NEW.zip_code AND state_id = NEW.state_id AND country_id = NEW.country_id) > 0)
    THEN RAISE(ABORT, 'insert on ct_addrs violates unique constraint')
  END;
END;

-- addr update constraint trigger
CREATE TRIGGER tru_addrs BEFORE UPDATE ON ct_addrs
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_addrs violates foreign key constraint fk_addrs_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_addrs violates foreign key constraint fk_addrs_label')
    WHEN ((SELECT id FROM cm_states WHERE id = NEW.state_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_addrs violates foreign key constraint fk_addrs_state')
    WHEN ((SELECT id FROM cm_countries WHERE id = NEW.country_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_addrs violates foreign key constraint fk_addrs_country')

    -- check format constraint
    WHEN NEW.zip_code GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'update on ct_addrs violates zip_code format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_addrs WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND street = NEW.street AND city = NEW.city AND appt_no = NEW.appt_no AND zip_code = NEW.zip_code AND state_id = NEW.state_id AND country_id = NEW.country_id) > 0)
    THEN RAISE(ABORT, 'update on ct_addrs violates unique constraint')
  END;
END;

-- contact delete cascade trigger for addrs
CREATE TRIGGER trd_ct_addrs BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_addrs WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for addrs
CREATE TRIGGER trd_label_addrs BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_addrs SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- state delete cascade trigger for addrs
CREATE TRIGGER trd_state_addrs BEFORE DELETE ON cm_states
FOR EACH ROW BEGIN
  UPDATE ct_addrs SET state_id = (SELECT id FROM cm_states WHERE abbr = '') WHERE state_id = OLD.id;
END;

-- country delete cascade trigger for addrs
CREATE TRIGGER trd_country_addrs BEFORE DELETE ON cm_countries
FOR EACH ROW BEGIN
  UPDATE ct_addrs SET country_id = (SELECT id FROM cm_countries WHERE abbr = '') WHERE country_id = OLD.id;
END;

-- populate addrs
BEGIN TRANSACTION;
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'home'),'3444 Gallows Rd','Annandale','22003',(SELECT id FROM cm_states WHERE abbr = 'VA'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'work'),'3150 Fairview Park Drive','Falls Church','22042',(SELECT id FROM cm_states WHERE abbr = 'VA'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Richard'),(SELECT id FROM cm_labels WHERE label = 'home'),'3495 Elysium St','Eugene','97405',(SELECT id FROM cm_states WHERE abbr = 'OR'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Robynn'),(SELECT id FROM cm_labels WHERE label = 'home'),'3495 Elysium St','Eugene','97405',(SELECT id FROM cm_states WHERE abbr = 'OR'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Carney' AND first_name = 'Robert'),(SELECT id FROM cm_labels WHERE label = 'home'),'3495 Elysium St','Eugene','97405',(SELECT id FROM cm_states WHERE abbr = 'OR'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'home'),'3444 Gallows Rd','Annandale','22003',(SELECT id FROM cm_states WHERE abbr = 'VA'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
  INSERT INTO ct_addrs(ct_id,label_id,street,city,zip_code,state_id,country_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'work'),'3150 Fairview Park Drive','Falls Church','22042',(SELECT id FROM cm_states WHERE abbr = 'VA'),(SELECT id FROM cm_countries WHERE abbr = 'US'));
END TRANSACTION;


--
-- contact phone numbers
--
SELECT '  o Creating ct_phones.';
CREATE TABLE ct_phones (
  id          INTEGER NOT NULL PRIMARY KEY,
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_phones_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL
              CONSTRAINT fk_addrs_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  phone       VARCHAR(50) NOT NULL DEFAULT '',
  ext         VARCHAR(50) NOT NULL DEFAULT '',

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- phone contact index
CREATE INDEX in_phones_ct ON ct_phones(ct_id);

-- phone insert constraint trigger
CREATE TRIGGER tri_phones BEFORE INSERT ON ct_phones
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_phones violates foreign key constraint fk_phones_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_phones violates foreign key constraint fk_phones_label')

    -- check format constraint
    WHEN NEW.phone GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'insert on ct_phones violates phone format constraint')
    WHEN NEW.ext GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'insert on ct_phones violates ext format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_phones WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND ext = NEW.ext AND phone = NEW.phone) > 0)
    THEN RAISE(ABORT, 'insert on ct_phones violates unique constraint')
  END;
END;

-- phone update constraint trigger
CREATE TRIGGER tru_phones BEFORE UPDATE ON ct_phones
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_phones violates foreign key constraint fk_phones_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_phones violates foreign key constraint fk_phones_label')

    -- check format constraint
    WHEN NEW.phone GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'update on ct_phones violates phone format constraint')
    WHEN NEW.ext GLOB '*[A-Za-z]*'
    THEN RAISE(ABORT, 'update on ct_phones violates ext format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_phones WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND ext = NEW.ext AND phone = NEW.phone) > 0)
    THEN RAISE(ABORT, 'update on ct_phones violates unique constraint')
  END;
END;

-- contact delete cascade trigger for phones
CREATE TRIGGER trd_ct_phones BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_phones WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for phones
CREATE TRIGGER trd_label_phones BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_phones SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- populate phones
BEGIN TRANSACTION;
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'home'),'703-573-5507');
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'work'),'703-610-1833');
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'home'),'703-573-5507');
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'cell'),'703-628-9423');
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'workcell'),'571-238-9114');
  INSERT INTO ct_phones(ct_id,label_id,phone) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),(SELECT id FROM cm_labels WHERE label = 'work'),'703-610-1560');
END TRANSACTION;


--
-- contact aliases
-- (eg "Bob Smith" might be "bob", and "Bobby Jones" might be "bobby")
--
SELECT '  o Creating ct_aliases.';
CREATE TABLE ct_aliases (  
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_aliases_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,

  alias       VARCHAR(20) NOT NULL, 

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- alias contact index
CREATE INDEX in_aliases_ct ON ct_aliases(ct_id);

-- alias insert constraint trigger
CREATE TRIGGER tri_aliases BEFORE INSERT ON ct_aliases
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_aliases violates foreign key constraint fk_aliases_ct')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_aliases WHERE ct_id = NEW.ct_id AND alias = NEW.alias) > 0)
    THEN RAISE(ABORT, 'insert on ct_aliases violates unique constraint')
  END;
END;

-- alias update constraint trigger
CREATE TRIGGER tru_aliases BEFORE UPDATE ON ct_aliases
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_aliases violates foreign key constraint fk_aliases_ct')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_aliases WHERE ct_id = NEW.ct_id AND alias = NEW.alias) > 0)
    THEN RAISE(ABORT, 'update on ct_aliases violates unique constraint')
  END;
END;

-- contact delete cascade trigger for aliases
CREATE TRIGGER trd_ct_aliases BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_aliases WHERE ct_id = OLD.id;
END;

-- populate aliases
BEGIN TRANSACTION;
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Perry' AND first_name = 'Alonzo'),'alonzo');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),'me');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),'pabs');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'),'richlowe');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Gilbert' AND first_name = 'Tom'),'giblet');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Gilbert' AND first_name = 'Tom'),'tom');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Richard'),'dad');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Rooke' AND first_name = 'Susan'),'mom');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Robynn'),'robynn');
  INSERT INTO ct_aliases(ct_id,alias) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Rooke' AND first_name = 'Susan'),'steve');
END TRANSACTION;

--
-- contact relationships
--
SELECT '  o Creating ct_rels.';
CREATE TABLE ct_rels (
  id          INTEGER NOT NULL PRIMARY KEY,
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_rels_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  rel_id      INTEGER NOT NULL
              CONSTRAINT fk_rels_rel REFERENCES cm_rels(id) 
                         ON DELETE CASCADE,

  rel_ct_id   INTEGER NOT NULL
              CONSTRAINT fk_rels_rel_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- relationship contact indices
CREATE INDEX in_rels_ct ON ct_rels(ct_id);
CREATE INDEX in_rels_rel_ct ON ct_rels(rel_ct_id);

-- relationship insert constraint trigger
CREATE TRIGGER tri_rels BEFORE INSERT ON ct_rels
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_rels violates foreign key constraint fk_rels_ct')
    WHEN ((SELECT id FROM cm_rels WHERE id = NEW.rel_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_rels violates foreign key constraint fk_rels_rel')
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.rel_ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_rels violates foreign key constraint fk_rels_rels_ct')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_rels WHERE ct_id = NEW.ct_id AND rel_id = NEW.rel_id AND rel_ct_id = NEW.rel_ct_id) > 0)
    THEN RAISE(ABORT, 'insert on ct_rels violates unique constraint')
  END;
END;

-- relationship update constraint trigger
CREATE TRIGGER tru_rels BEFORE UPDATE ON ct_rels
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_rels violates foreign key constraint fk_rels_rel_ct')
    WHEN ((SELECT id FROM cm_rels WHERE id = NEW.rel_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_rels violates foreign key constraint fk_rels_rel')
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.rel_ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_rels violates foreign key constraint fk_rels_rel_ct')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_rels WHERE ct_id = NEW.ct_id AND rel_id = NEW.rel_id AND rel_ct_id = NEW.rel_ct_id) > 0)
    THEN RAISE(ABORT, 'update on ct_rels violates unique constraint')
  END;
END;

-- contact delete cascade trigger for contact relationships
CREATE TRIGGER trd_ct_rels BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_rels WHERE ct_id = OLD.id OR ct_rel_id = OLD.id;
END;

-- relationship delete cascade trigger for contact relationships
CREATE TRIGGER trd_rel_rels BEFORE DELETE ON cm_rels
FOR EACH ROW BEGIN
  UPDATE ct_rels SET rel_id = (SELECT id FROM cm_rels WHERE rel = '') WHERE rel_id = OLD.id;
END;

-- auto relationship triggers
-- (are these a good idea?)
CREATE TRIGGER tri_auto_rel AFTER INSERT ON ct_rels
FOR EACH ROW BEGIN
  INSERT OR IGNORE INTO ct_rels(ct_id,rel_ct_id,rel_id) VALUES (NEW.rel_ct_id, NEW.ct_id, (SELECT id FROM cm_rels WHERE rel = (SELECT CASE 
    WHEN ((SELECT rel FROM cm_rels WHERE id = NEW.rel_id) = 'parent') 
    THEN 'child'
    WHEN ((SELECT rel FROM cm_rels WHERE id = NEW.rel_id) = 'child') 
    THEN 'parent'
    ELSE (SELECT rel FROM cm_rels WHERE id = NEW.rel_id)
  END)));
END;

-- populate relationships
BEGIN TRANSACTION;
  INSERT INTO ct_rels(ct_id,rel_ct_id,rel_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Richard'), (SELECT id FROM cm_rels WHERE rel = 'parent'));
  INSERT INTO ct_rels(ct_id,rel_ct_id,rel_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Robynn'), (SELECT id FROM cm_rels WHERE rel = 'parent'));
  INSERT INTO ct_rels(ct_id,rel_ct_id,rel_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_contacts WHERE last_name = 'Rooke' AND first_name = 'Susan'), (SELECT id FROM cm_rels WHERE rel = 'parent'));
  INSERT INTO ct_rels(ct_id,rel_ct_id,rel_id) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'), (SELECT id FROM cm_rels WHERE rel = 'friend'));
END TRANSACTION;


---------------------------
-- techie contact tables --
---------------------------

--
-- contact nicknames (instant messenger, IRC, etc)
--
SELECT '  o Creating ct_nicks.';
CREATE TABLE ct_nicks ( 
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_nicks_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL DEFAULT 1
              CONSTRAINT fk_nicks_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  svs_id      INTEGER NOT NULL
              CONSTRAINT fk_nicks_svs REFERENCES cm_im_services(id) 
                         ON DELETE CASCADE,

  nick        VARCHAR(50) NOT NULL, 

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- nick contact index
CREATE INDEX in_nicks_ct ON ct_nicks(ct_id);

-- nick insert constraint trigger
CREATE TRIGGER tri_nicks BEFORE INSERT ON ct_nicks
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_nicks violates foreign key constraint fk_nicks_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_nicks violates foreign key constraint fk_nicks_label')
    WHEN ((SELECT id FROM cm_im_services WHERE id = NEW.svs_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_nicks violates foreign key constraint fk_nicks_svs')

    -- check format constraint
    -- TODO: im_service column with nickname constraints

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_nicks WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND svs_id = NEW.svs_id AND nick = NEW.nick) > 0)
    THEN RAISE(ABORT, 'insert on ct_nicks violates unique constraint')
  END;
END;

-- nick update constraint trigger
CREATE TRIGGER tru_nicks BEFORE UPDATE ON ct_nicks
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_nicks violates foreign key constraint fk_nicks_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_nicks violates foreign key constraint fk_nicks_label')
    WHEN ((SELECT id FROM cm_im_services WHERE id = NEW.svs_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_nicks violates foreign key constraint fk_nicks_svs')

    -- check format constraint
    -- TODO: im_service column with nickname constraints

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_nicks WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND svs_id = NEW.svs_id AND nick = NEW.nick) > 0)
    THEN RAISE(ABORT, 'update on ct_nicks violates unique constraint')
  END;
END;

-- contact delete cascade trigger for nicks
CREATE TRIGGER trd_ct_nicks BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_nicks WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for nicks
CREATE TRIGGER trd_label_nicks BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_nicks SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- im service delete cascade trigger for nicks
CREATE TRIGGER trd_svs_nicks BEFORE DELETE ON cm_im_services
FOR EACH ROW BEGIN
  UPDATE ct_nicks SET svs_id = (SELECT id FROM cm_services WHERE svs = '') WHERE svs_id = OLD.id;
END;

-- populate nicks
BEGIN TRANSACTION;
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_im_services WHERE abbr = 'AIM'),'pablotronic');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_im_services WHERE abbr = 'ICQ'),'23423423');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'), (SELECT id FROM cm_im_services WHERE abbr = 'IRC'),'pabs');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'), (SELECT id FROM cm_im_services WHERE abbr = 'AIM'),'richloo');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'), (SELECT id FROM cm_im_services WHERE abbr = 'ICQ'),'richlowe');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'), (SELECT id FROM cm_im_services WHERE abbr = 'IRC'),'richlowe');
  INSERT INTO ct_nicks(ct_id,svs_id,nick) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Gilbert' AND first_name = 'Tom'), (SELECT id FROM cm_im_services WHERE abbr = 'IRC'),'giblet');
END TRANSACTION;


--
-- contact urls (home pages, business, live journal, RSS, etc)
--
SELECT '  o Creating ct_urls.';
CREATE TABLE ct_urls (  
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_urls_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL DEFAULT 1
              CONSTRAINT fk_urls_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  url TEXT    NOT NULL, 

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- url contact index
CREATE INDEX in_urls_ct ON ct_urls(ct_id);

-- url insert constraint trigger
CREATE TRIGGER tri_urls BEFORE INSERT ON ct_urls
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_urls violates foreign key constraint fk_urls_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_urls violates foreign key constraint fk_urls_label')

    -- check format constraint
    WHEN NEW.url NOT LIKE '%_://_%'
    THEN RAISE(ABORT, 'insert on ct_urls violates url format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_urls WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND url = NEW.url) > 0)
    THEN RAISE(ABORT, 'insert on ct_urls violates unique constraint')
  END;
END;

-- url update constraint trigger
CREATE TRIGGER tru_urls BEFORE UPDATE ON ct_urls
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_urls violates foreign key constraint fk_urls_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_urls violates foreign key constraint fk_urls_label')

    -- check format constraint
    WHEN NEW.url NOT LIKE '%_://_%'
    THEN RAISE(ABORT, 'update on ct_urls violates url format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_urls WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND url = NEW.url) > 0)
    THEN RAISE(ABORT, 'update on ct_urls violates unique constraint')
  END;
END;

-- contact delete cascade trigger for urls
CREATE TRIGGER trd_ct_urls BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_urls WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for urls
CREATE TRIGGER trd_label_urls BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_urls SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- populate ct_urls
BEGIN TRANSACTION;
  INSERT INTO ct_urls(ct_id,label_id,url) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'tech'), 'http://pablotron.org/');
  INSERT INTO ct_urls(ct_id,label_id,url) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'personal'), 'http://paulduncan.org/');
  INSERT INTO ct_urls(ct_id,url) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'),'http://richlowe.net/');
  INSERT INTO ct_urls(ct_id,url) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Gilbert' AND first_name = 'Tom'),'http://linuxbrit.co.uk/');
END TRANSACTION;


--
-- contact email addresses
--
SELECT '  o Creating ct_emails.';
CREATE TABLE ct_emails (  
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_emails_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL DEFAULT 1
              CONSTRAINT fk_emails_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  email       VARCHAR(255) NOT NULL, 

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- email contact id index
CREATE INDEX in_emails_ct ON ct_emails(ct_id);

-- email insert constraint trigger
CREATE TRIGGER tri_emails BEFORE INSERT ON ct_emails
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_emails violates foreign key constraint fk_emails_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_emails violates foreign key constraint fk_emails_label')

    -- check format constraint
    WHEN NEW.email NOT LIKE '%@%.%'
    THEN RAISE(ABORT, 'insert on ct_emails violates email format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_emails WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND email = NEW.email) > 0)
    THEN RAISE(ABORT, 'insert on ct_emails violates unique constraint')
  END;
END;

-- email update constraint trigger
CREATE TRIGGER tru_emails BEFORE UPDATE ON ct_emails
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_emails violates foreign key constraint fk_emails_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_emails violates foreign key constraint fk_emails_label')

    -- check format constraint
    WHEN NEW.email NOT LIKE '%@%.%'
    THEN RAISE(ABORT, 'update on ct_emails violates email format constraint')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_emails WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND email = NEW.email) > 0)
    THEN RAISE(ABORT, 'update on ct_emails violates unique constraint')
  END;
END;

-- contact delete cascade trigger for emails
CREATE TRIGGER trd_ct_emails BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_emails WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for emails
CREATE TRIGGER trd_label_emails BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_emails SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- populate ct_emails
BEGIN TRANSACTION;
  INSERT INTO ct_emails(ct_id,label_id,email) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'tech'), 'pabs@pablotron.org');
  INSERT INTO ct_emails(ct_id,label_id,email) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Duncan' AND first_name = 'Paul'),(SELECT id FROM cm_labels WHERE label = 'personal'), 'paul@paulduncan.org');
  INSERT INTO ct_emails(ct_id,email) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Lowe' AND first_name = 'Richard'),'richlowe@richlowe.net');
  INSERT INTO ct_emails(ct_id,email) VALUES ((SELECT id FROM cm_contacts WHERE last_name = 'Gilbert' AND first_name = 'Tom'),'tom@linuxbrit.co.uk');
END TRANSACTION;


--------------------------
-- other contact tables --
--------------------------

/*
--
-- contact flags
--
SELECT '  o Creating ct_flags.';
CREATE TABLE ct_flags (
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_flags_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL
              CONSTRAINT fk_flags_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  flag        BOOLEAN NOT NULL,

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- flag contact id index
CREATE INDEX in_flags_ct ON ct_flags(ct_id);

-- flag insert constraint trigger
CREATE TRIGGER tri_flags BEFORE INSERT ON ct_flags
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_flags violates foreign key constraint fk_flags_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_flags violates foreign key constraint fk_flags_label')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_flags WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND flag = NEW.flag) > 0)
    THEN RAISE(ABORT, 'insert on ct_flags violates unique constraint')
  END;
END;

-- flag update constraint trigger
CREATE TRIGGER tru_flags BEFORE UPDATE ON ct_flags
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_flags violates foreign key constraint fk_flags_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_flags violates foreign key constraint fk_flags_label')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_flags WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND flag = NEW.flag) > 0)
    THEN RAISE(ABORT, 'update on ct_flags violates unique constraint')
  END;
END;

-- contact delete cascade trigger for flags
CREATE TRIGGER trd_ct_flags BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_flags WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for flags
CREATE TRIGGER trd_label_flags BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_flags SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- populate ct_flags
*/

--
-- contact misc stuff
--
SELECT '  o Creating ct_misc.';
CREATE TABLE ct_misc (
  id          INTEGER NOT NULL PRIMARY KEY, 
  ct_id       INTEGER NOT NULL
              CONSTRAINT fk_misc_ct REFERENCES cm_contacts(id) 
                         ON DELETE CASCADE,
  label_id    INTEGER NOT NULL
              CONSTRAINT fk_misc_label REFERENCES cm_labels(id) 
                         ON DELETE CASCADE,

  other       BLOB NOT NULL DEFAULT '',

  FOREIGN KEY (ct_id) REFERENCES cm_contacts ON DELETE CASCADE
);

-- misc contact id index
CREATE INDEX in_misc_ct ON ct_misc(ct_id);

-- mics insert constraint trigger
CREATE TRIGGER tri_misc BEFORE INSERT ON ct_misc
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_misc violates foreign key constraint fk_misc_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'insert on ct_misc violates foreign key constraint fk_misc_label')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_misc WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND misc = NEW.misc) > 0)
    THEN RAISE(ABORT, 'insert on ct_misc violates unique constraint')
  END;
END;

-- mics update constraint trigger
CREATE TRIGGER tru_misc BEFORE UPDATE ON ct_misc
FOR EACH ROW BEGIN 
  SELECT CASE
    -- check foreign key constraint
    WHEN ((SELECT id FROM cm_contacts WHERE id = NEW.ct_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_misc violates foreign key constraint fk_misc_ct')
    WHEN ((SELECT id FROM cm_labels WHERE id = NEW.label_id) IS NULL)
    THEN RAISE(ABORT, 'update on ct_misc violates foreign key constraint fk_misc_label')

    -- check unique constraint
    WHEN ((SELECT COUNT(*) FROM ct_misc WHERE ct_id = NEW.ct_id AND label_id = NEW.label_id AND misc = NEW.misc) > 0)
    THEN RAISE(ABORT, 'update on ct_misc violates unique constraint')
  END;
END;

-- contact delete cascade trigger for misc
CREATE TRIGGER trd_ct_misc BEFORE DELETE ON cm_contacts
FOR EACH ROW BEGIN
  DELETE FROM ct_misc WHERE ct_id = OLD.id;
END;

-- label delete cascade trigger for misc
CREATE TRIGGER trd_label_misc BEFORE DELETE ON cm_labels
FOR EACH ROW BEGIN
  UPDATE ct_misc SET label_id = (SELECT id FROM cm_labels WHERE label = '') WHERE label_id = OLD.id;
END;

-- populate ct_misc

-- if you can read this, you don't need glasses
SELECT 'Done.';