DELETE FROM CONFIGURATION
WHERE [SECTION]='HRPortal' AND [KEY]='TransactionFormat';

DELETE FROM CONFIGURATION
WHERE [SECTION]='Logging' AND [KEY]='PathName';

DELETE FROM CONFIGURATION
WHERE [SECTION]='Core' AND [KEY]='LauncherFullName';

DELETE FROM CONFIGURATION
WHERE [SECTION]='Core' AND [KEY]='TempPath';

DELETE FROM CONFIGURATION
WHERE [SECTION]='Core' AND [KEY]='KillMeAfterWait';

DELETE FROM CONFIGURATION
WHERE [SECTION]='Printer' AND [KEY]='PdfPrintPath';

DELETE FROM CONFIGURATION
WHERE [SECTION]='ZGate' AND [KEY]='MasterUrl' AND [VALUE]='http://';

DELETE FROM CONFIGURATION
WHERE [SECTION]='HRPortal' AND [KEY]='MasterUrl' AND [VALUE]='http://';

DELETE FROM CONFIGURATION
WHERE [SECTION]='EDENRED' AND [KEY]='Url';

DELETE FROM CONFIGURATION
WHERE [SECTION]='TransactionsManager' AND [KEY]='PushMessagesPersistence';

UPDATE CONFIGURATION SET [VALUE]='LeftEntryRightExit' where [SECTION]='TandA' and [KEY]='RegistrationMode' and [VALUE]='Bidirectional';
UPDATE CONFIGURATION SET [VALUE]='LeftExitRightEntry' where [SECTION]='TandA' and [KEY]='RegistrationMode' and [VALUE]='Reverse';

DELETE FROM CONFIGURATION
WHERE [SECTION]='System' AND [KEY]='ProxyAddress' AND [VALUE]='';

-- drop old table
DROP TABLE IF EXISTS MAINTENANCE_PASSWORDS;

-- update datetime format (ISO-8601 standard)
UPDATE TRANSACTIONS
SET TSTRANSACTION = 
	substr(TSTRANSACTION,  1, 4) || '-' || 
	substr(TSTRANSACTION,  5, 2) || '-' || 
	substr(TSTRANSACTION,  7, 2) || ' ' || 
	substr(TSTRANSACTION,  9, 2) || ':' || 
	substr(TSTRANSACTION, 11, 2) || ':' || 
	substr(TSTRANSACTION, 13, 2)
WHERE NOT (TSTRANSACTION LIKE '____-__-%');

UPDATE GTL_TRANSACTIONS
SET TSTRANSACTION = 
	substr(TSTRANSACTION,  1, 4) || '-' || 
	substr(TSTRANSACTION,  5, 2) || '-' || 
	substr(TSTRANSACTION,  7, 2) || ' ' || 
	substr(TSTRANSACTION,  9, 2) || ':' || 
	substr(TSTRANSACTION, 11, 2) || ':' || 
	substr(TSTRANSACTION, 13, 2)
WHERE NOT (TSTRANSACTION LIKE '____-__-%');

UPDATE EMPLOYEES
SET DTSTARTVL = 
	substr(DTSTARTVL,  1, 4) || '-' || 
	substr(DTSTARTVL,  5, 2) || '-' || 
	substr(DTSTARTVL,  7, 2) || ' ' || 
	substr(DTSTARTVL,  9, 2) || ':' || 
	substr(DTSTARTVL, 11, 2) || ':' || 
	substr(DTSTARTVL, 13, 2),
	DTENDVL = 
	substr(DTENDVL,  1, 4) || '-' || 
	substr(DTENDVL,  5, 2) || '-' || 
	substr(DTENDVL,  7, 2) || ' ' || 
	substr(DTENDVL,  9, 2) || ':' || 
	substr(DTENDVL, 11, 2) || ':' || 
	substr(DTENDVL, 13, 2)
WHERE NOT (DTSTARTVL LIKE '____-__-%') AND NOT (DTENDVL LIKE '____-__-%');

UPDATE GTL_ENTITY_ATTRIBUTIONS
SET DATE_REF = 
	substr(DATE_REF,  1, 4) || '-' || 
	substr(DATE_REF,  5, 2) || '-' || 
	substr(DATE_REF,  7, 2) || ' ' || 
	substr(DATE_REF,  9, 2) || ':' || 
	substr(DATE_REF, 11, 2) || ':' || 
	substr(DATE_REF, 13, 2)
WHERE NOT (DATE_REF LIKE '____-__-%');

UPDATE PUSHMESSAGES
SET RECEIVEDTS = 
	substr(RECEIVEDTS,  1, 4) || '-' || 
	substr(RECEIVEDTS,  5, 2) || '-' || 
	substr(RECEIVEDTS,  7, 2) || ' ' || 
	substr(RECEIVEDTS,  9, 2) || ':' || 
	substr(RECEIVEDTS, 11, 2) || ':' || 
	substr(RECEIVEDTS, 13, 2)
WHERE NOT (RECEIVEDTS LIKE '____-__-%');

UPDATE VERSIONS
SET UPDATE_TS = 
	substr(UPDATE_TS,  1, 4) || '-' || 
	substr(UPDATE_TS,  5, 2) || '-' || 
	substr(UPDATE_TS,  7, 2) || ' ' || 
	substr(UPDATE_TS,  9, 2) || ':' || 
	substr(UPDATE_TS, 11, 2) || ':' || 
	substr(UPDATE_TS, 13, 2)
WHERE NOT (UPDATE_TS LIKE '____-__-%');

-- Adding new TRANSACTION columns
ALTER TABLE TRANSACTIONS ADD COLUMN TYPE VARCHAR(1) DEFAULT 'P' NULL;
ALTER TABLE TRANSACTIONS ADD COLUMN TSSENT DATETIME NULL;

-- Adding new GTL_TRANSACTIONS columns
ALTER TABLE GTL_TRANSACTIONS ADD COLUMN TSSENT DATETIME NULL;

-- Adding new STATIC_CONFIG_VALUES columns
ALTER TABLE STATIC_CONFIG_VALUES ADD COLUMN DEFAULT_VALUE VARCHAR(200) NULL;
ALTER TABLE STATIC_CONFIG_VALUES ADD COLUMN IS_EXPORTABLE BOOLEAN NULL;
