CREATE TABLE mgw_companies (
  id INTEGER NOT NULL default '0',
  name1 CHARACTER VARYING(200) default NULL,
  name2 CHARACTER VARYING(150) default NULL,
  street CHARACTER VARYING(200) default NULL,
  additional CHARACTER VARYING(150) default NULL,
  zip CHARACTER VARYING(12) default NULL,
  city CHARACTER VARYING(200) default NULL,
  state CHARACTER VARYING(50) default NULL,
  countrycode CHARACTER VARYING(5) default NULL,
  telephone CHARACTER VARYING(100) default NULL,
  fax CHARACTER VARYING(100) default NULL,
  url CHARACTER VARYING(150) default NULL,
  email CHARACTER VARYING(100) default NULL,
  note TEXT,
  groupid INTEGER NOT NULL default '0',
  ispublic INTEGER default NULL,
  owner INTEGER default NULL,
  lu_user INTEGER default NULL,
  lu_date TIMESTAMP NOT NULL,
  PRIMARY KEY  (id)
);

CREATE TABLE mgw_contacts (
  id INTEGER NOT NULL default '0',
  title CHARACTER VARYING(30) default NULL,
  honorific_prefix CHARACTER VARYING(100) default NULL,
  name_prefix CHARACTER VARYING(100) default NULL,
  firstname CHARACTER VARYING(100) default NULL,
  name_add CHARACTER VARYING(100) default NULL,
  nickname CHARACTER VARYING(100) default NULL,
  lastname CHARACTER VARYING(100) default NULL,
  name_suffix CHARACTER VARYING(100) default NULL,
  priv_street CHARACTER VARYING(150) default NULL,
  priv_additional CHARACTER VARYING(100) default NULL,
  priv_zip CHARACTER VARYING(12) default NULL,
  priv_city CHARACTER VARYING(150) default NULL,
  priv_state CHARACTER VARYING(50) default NULL,
  priv_countrycode CHARACTER VARYING(5) default NULL,
  priv_region CHARACTER VARYING(100) default NULL,
  priv_telefon CHARACTER VARYING(50) default NULL,
  priv_fax CHARACTER VARYING(50) default NULL,
  email CHARACTER VARYING(150) default NULL,
  email2 CHARACTER VARYING(150) default NULL,
  email3 CHARACTER VARYING(150) default NULL,
  tel_company CHARACTER VARYING(50) default NULL,
  tel_mobile CHARACTER VARYING(50) default NULL,
  url CHARACTER VARYING(150) default NULL,
  function CHARACTER VARYING(100) default NULL,
  birthday DATE default NULL,
  note TEXT,
  publicKey TEXT default NULL,
  keyType CHARACTER VARYING(150) default NULL,
  agentid INTEGER default NULL,
  companyid INTEGER default NULL,
  groupid INTEGER NOT NULL default '0',
  ispublic INTEGER default NULL,
  owner INTEGER default NULL,
  lu_user INTEGER default NULL,
  lu_date TIMESTAMP NOT NULL,
  PRIMARY KEY  (id)
);

CREATE TABLE mgw_contacts_groups (
  id INTEGER NOT NULL default '0',
  name_key CHARACTER VARYING(100) NOT NULL default '',
  description CHARACTER VARYING(255) NOT NULL default '',
  lu_date TIMESTAMP NOT NULL,
  lu_user INTEGER NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE (name_key)
);

INSERT INTO mgw_contacts_groups VALUES (1, 'Customer', 'Customer', '2002-01-01', 1);
INSERT INTO mgw_contacts_groups VALUES (2, 'Supplier', 'Supplier', '2002-01-01', 1);
INSERT INTO mgw_contacts_groups VALUES (3, 'Partner', 'Partner', '2002-01-01', 1);

CREATE TABLE mgw_contacts_history (
  contactid INTEGER NOT NULL default '0',
  contact_company INTEGER NOT NULL default 0,
  contact_date TIMESTAMP NOT NULL,
  contact_person TEXT,
  contact_todo TEXT,
  contact_comment TEXT,
  contact_userid INTEGER default NULL,
  lu_user INTEGER default NULL,
  lu_date TIMESTAMP NOT NULL,
  PRIMARY KEY  (contactid,contact_date)
);

INSERT INTO mgw_configtags VALUES ('quicksearchmethod','contact','WHat fields to look for when quick searching',1,1,'str',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('contacts_per_page','contact','How many contacts to display on one page',0,0,'int',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('birthdayremindoffset','contact','Days before or after birthday to remind (-1 = one day before, +1 one day after, 0 = same day ...)',0,0,'str',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('birthdayremindmethod','contact','How the reminder should remind',0,0,'str',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('birthdayremind_on_off','contact','remind on / off',0,0,'int',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('favourite_countries','contact','list of top 5 favourite countries',0,1,'str',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('show_company_id','contact','1 if company id is to be shown, 0 if not',0,0,'int',1,'2002-01-01');
INSERT INTO mgw_configtags VALUES ('use_webmail','contact','1 if webmail2 is to be used for mail links, 0 if not',0,0,'int',1,'2002-01-01');

INSERT INTO mgw_config VALUES (0,'quicksearchmethod',1,'mgw_contacts.firstname',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'quicksearchmethod',2,'mgw_contacts.lastname',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'quicksearchmethod',3,'mgw_companies.name1',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'contacts_per_page',1,'',10,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'birthdayremindoffset',1,'-1',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'birthdayremindmethod',1,'EMAIL',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'birthdayremind_on_off',1,'',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'favourite_countries',1,'DEU',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'favourite_countries',2,'FRA',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'favourite_countries',3,'CZE',0,'',1,'2002-01-01');
INSERT INTO mgw_config VALUES (0,'favourite_countries',4,'GBR',0,'',1,'2002-01-01');
