# WEBDB upgrade proposed for 2011/11
# bug#976
# https://bugs.cacert.org/view.php?id=976


# add table CCA
# maybe do method as an enum
CREATE TABLE  `cacert`.`agreements` (
  `id` int(11) NOT NULL auto_increment,
  `memid` int(11) NOT NULL default '0',
  `method` varchar(50) NOT NULL default '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `deleted` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `memid` (`memid`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

# Vendor table for API (eg. Drupal Find Assurer)
# defered as of https://wiki.cacert.org/Software/Assessment/20111004-S-A-MiniTOP

# add a description field to all cert tables (not to use in cert,
# but to use on display overview of certs)
# eg view domain certs
#  https://secure.cacert.org/account.php?id=12
# view client certs
#  https://secure.cacert.org/account.php?id=5

alter TABLE `cacert`.`domaincerts` ADD COLUMN `description` varchar(50) NOT NULL default '';
alter TABLE `cacert`.`emailcerts` ADD COLUMN `description` varchar(50) NOT NULL default '';
alter TABLE `cacert`.`gpg` ADD COLUMN `description` varchar(50) NOT NULL default '';
alter TABLE `cacert`.`orgdomaincerts` ADD COLUMN `description` varchar(50) NOT NULL default '';
alter TABLE `cacert`.`orgemailcerts` ADD COLUMN `description` varchar(50) NOT NULL default '';


# 	TTP
# see bugs
# https://bugs.cacert.org/view.php?id=855 [^]
# https://bugs.cacert.org/view.php?id=863 [^]
# https://bugs.cacert.org/view.php?id=864 [^]
# https://bugs.cacert.org/view.php?id=888 [^]
# 1. notary assurance method to modify
#    a. keep "Trusted Third Parties" (old program)
#    b. add "TOPUP"
#    c. add column notary.deleted                see above
# notaty table summarize:
# new method: TOPUP
# new field deleted-when
# new field addtlinfos avail
#   eg entry revoked -> addtl. info
#      pojam -> addtl. info

# update table notary add deletedwhen
alter TABLE `cacert`.`notary` ADD COLUMN `deleted` datetime NOT NULL default '0000-00-00 00:00:00';
alter table `cacert`.`notary` change `method` `method` enum('Face to Face Meeting','Trusted Third Parties','Thawte Points Transfer','Administrative Increase','CT Magazine - Germany','Temporary Increase','Unknown','TOPUP') NOT NULL default 'Face to Face Meeting';
alter TABLE `cacert`.`notary` ADD COLUMN `addtlinfo` int(1) NOT NULL default '0';

# users table summarize:
# new field deleted-when    exist deleted
# new field addtlinfos avail
#   eg if user is deleted -> addtl. info
#   addtl. infos like chat, im handle and more
#   geo coordinates or whatever else
alter TABLE `cacert`.`users` ADD COLUMN `addtlinfo` int(1) NOT NULL default '0';

# addtl addon infos/descriptions table for all other tables
# eg feature request to add CHAT + IM handle in users record
#    https://bugs.cacert.org/view.php?id=791 
# open format
# eg userid, type, format, value
#    id, userid, function/type, (format), value
# (idea by dirk)

CREATE TABLE  `cacert`.`addtlinfos` (
  `id` int(11) NOT NULL auto_increment,
  `memid` int(11) NOT NULL default '0',
  `method` varchar(50) NOT NULL default '',
  `addtlinfoval` varchar(50) NOT NULL default '',  
  `deleted` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `memid` (`memid`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

# country specific table
# eg country, POJAM age, TTP allowed, OrgAssurer for country XYZ
# defered as of https://wiki.cacert.org/Software/Assessment/20111004-S-A-MiniTOP

# 0000956: Request to add new information to organsiation entries in Org section
# summary:
# add field from
# add field when
# add field deleted-when
# potencial tables: org, orginfo
# Description:
# The OA team needs to know for an organisation who entered an organisation to the WebDB and when was it entered.
# Propsoal: two new fields in the OrgInfo table to have these fields sortable.
# see http://wiki.cacert.org/OrganisationAssurance/Team/Meetings/2011-06-30

alter TABLE `cacert`.`orginfo` ADD COLUMN `creator_id` int(11) NOT NULL default '0';
alter TABLE `cacert`.`orginfo` ADD COLUMN `created` datetime NOT NULL default '0000-00-00 00:00:00';
alter TABLE `cacert`.`orginfo` ADD COLUMN `deleted` datetime NOT NULL default '0000-00-00 00:00:00';

# ???? table with list of OrgAdmins 
# alter TABLE `cacert`.`org` ADD COLUMN `creator_id` int(11) NOT NULL default '0';
# alter TABLE `cacert`.`org` ADD COLUMN `created` datetime NOT NULL default '0000-00-00 00:00:00';
# alter TABLE `cacert`.`org` ADD COLUMN `deleted` datetime NOT NULL default '0000-00-00 00:00:00';

# eot