CREATE TABLE IF NOT EXISTS `mn_DEventDocStatus` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(32) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `sorter` (`sorter`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT IGNORE INTO `mn_DEventDocStatus` (`id`, `title`, `sorter`) VALUES
(1, 'Applied For', 0),
(2, 'Approved', 1);

ALTER TABLE `mn_Event` CHANGE `doc_number` `doc_number` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `mn_EventDocumentofCommerce` CHANGE `doc_number` `doc_number` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `mn_Event` ADD INDEX ( `doc_number` );

ALTER TABLE `mn_EventDocumentofCommerce` ADD `status_id` INT UNSIGNED NOT NULL AFTER `ce_hours` ;
ALTER TABLE `mn_EventDocumentofCommerce` ADD INDEX ( `status_id` ) ;

UPDATE `mn_EventDocumentofCommerce` SET `renewal_date` = NULL WHERE `renewal_date` = '0000-00-00';

ALTER TABLE `mn_EventFormReq` ADD `certificate` VARCHAR( 64 ) NOT NULL AFTER `attended` ;

UPDATE `mn_zz_HTMLBlock` AS `b`
JOIN `mn_zz_HTMLBlockContent` AS `bc` ON `bc`.`id_parent` = `b`.`id`
SET `bc`.`content` =  '<p>Please note: The CE Calculator only displays MNCAR courses that you have earned in the current licensing period.To verify your total CE credits, please call the Department Of Commerce at 651-296-6319.<br /><br />CE Certificates in PDF format are available for download for MNCAR courses occurring on or after 01/05/2012.</p>'
WHERE `name` = 'account/CEoverview/my-account-ce-history.html';

-- remove not numeric re licence numbers
UPDATE `mn_Member`
SET `re_license_number` = ''
WHERE `re_license_number` NOT REGEXP '[0-9]+' AND `re_license_number`  != '';

-- SELECT `re_license_number`
-- FROM `mn_Member`
-- WHERE `re_license_number` != ''
--   AND `re_license_number` NOT REGEXP '[0-9]+';

UPDATE `mn_Member`
SET `re_license_number` = ''
WHERE
  `re_license_number` != ''
  AND `re_license_number` NOT REGEXP '[0-9]+';


CREATE TABLE IF NOT EXISTS `mn_EventCertificateSendQueue` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_event` int(11) NOT NULL default '0',
  `id_member` int(11) NOT NULL default '0',
  `status` enum('new','processing','failed','sent') NOT NULL default 'new',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_event` (`id_event`,`id_member`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;