-- Fixes for date/time validations (also fixed in 5.5.13 LTS)
UPDATE `redcap_validation_types` SET `regex_js` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4}))$/', `regex_php` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4}))$/' WHERE `validation_name` = 'date_dmy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4}))$/', `regex_php` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4}))$/' WHERE `validation_name` = 'date_mdy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31))))$/', `regex_php` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31))))$/' WHERE `validation_name` = 'date_ymd';
UPDATE `redcap_validation_types` SET `regex_js` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4})) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/', `regex_php` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4})) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/' WHERE `validation_name` = 'datetime_dmy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4})) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/', `regex_php` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4})) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/' WHERE `validation_name` = 'datetime_mdy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31)))) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/', `regex_php` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31)))) (\\d|[0-1]\\d|[2][0-3]):[0-5]\\d$/' WHERE `validation_name` = 'datetime_ymd';
UPDATE `redcap_validation_types` SET `regex_js` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4})) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/', `regex_php` = '/^((29([-\\/.]?)02\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1\\d|2[0-8])([-\\/.]?)(0[1-9]|1[012]))|((29|30)([-\\/.]?)(0[13-9]|1[012]))|(31([-\\/.]?)(0[13578]|1[02])))(\\11|\\15|\\18)\\d{4})) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/' WHERE `validation_name` = 'datetime_seconds_dmy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4})) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/', `regex_php` = '/^((02([-\\/.]?)29\\3(\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00)))|((((0[1-9]|1[012])([-\\/.]?)(0[1-9]|1\\d|2[0-8]))|((0[13-9]|1[012])([-\\/.]?)(29|30))|((0[13578]|1[02])([-\\/.]?)31))(\\11|\\15|\\19)\\d{4})) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/' WHERE `validation_name` = 'datetime_seconds_mdy';
UPDATE `redcap_validation_types` SET `regex_js` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31)))) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/', `regex_php` = '/^(((\\d{2}([13579][26]|[2468][048]|04|08)|(1600|2[048]00))([-\\/.]?)02(\\6)29)|(\\d{4}([-\\/.]?)((0[1-9]|1[012])(\\9)(0[1-9]|1\\d|2[0-8])|((0[13-9]|1[012])(\\9)(29|30))|((0[13578]|1[02])(\\9)31)))) (\\d|[0-1]\\d|[2][0-3])(:[0-5]\\d){2}$/' WHERE `validation_name` = 'datetime_seconds_ymd';
-- Add DDP setting
insert into redcap_config values ('realtime_webservice_stop_fetch_inactivity_days', '7');
-- Add index
ALTER TABLE  `redcap_projects` ADD INDEX (  `last_logged_event` );
-- Modify DDP table
ALTER TABLE  `redcap_ddp_records` CHANGE  `item_count`  `item_count` INT( 10 ) NULL DEFAULT NULL
	COMMENT  'New item count (as of last viewing)';
-- Reset values in DDP table
update redcap_ddp_records set item_count = null;