

CREATE TABLE `cb_account_deleted` (
  `Log_ID` int(9) NOT NULL AUTO_INCREMENT,
  `Id` bigint(20) NOT NULL,
  `DeletedBy` bigint(20),
  `DeleteDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Log_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_customdata_log` (
  `Log_ID` int(9) NOT NULL AUTO_INCREMENT,
  `IdAccount` bigint(20) NOT NULL,
  `DataKey` varchar(45) NOT NULL,
  `DataValue` text,
  `EditedBy` bigint(20),
  `EditDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Log_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `cb_account` ADD COLUMN `Checksum` varchar(32);

ALTER TABLE `cb_customdata` ADD COLUMN `Checksum` varchar(32);
ALTER TABLE `cb_customdata_log` ADD COLUMN `Checksum` varchar(32);

ALTER TABLE `cb_adminaccount` ADD COLUMN `Checksum` varchar(32);

DROP TRIGGER IF EXISTS `Account_Log`;
END
// DELIMITER ;

DROP TRIGGER IF EXISTS `Account_Log_Update`;
	END IF;
END
// DELIMITER ;

DROP TRIGGER IF EXISTS `CustomData_Log`;
DELIMITER //
CREATE TRIGGER `CustomData_Log` BEFORE INSERT ON `cb_customdata`
 FOR EACH ROW
  BEGIN
  	SET @key=NEW.DataKey, @prevValue = (SELECT DataValue FROM cb_customdata WHERE IdAccount=NEW.IdAccount and DataKey=NEW.DataKey), @newEditedBy = (CONVERT(AES_DECRYPT(FROM_BASE64((SELECT DataValue FROM cb_customdata WHERE IdAccount=NEW.IdAccount and DataKey='EditedBy')), '[CYPHER]') USING utf8));
	ELSEIF @prevValue is null OR @prevValue <> NEW.DataValue
	THEN
    	INSERT INTO cb_customdata_log(`IdAccount`,`DataKey`,`DataValue`,`EditedBy`,`EditDate`,`Checksum`)
		VALUES(
            NEW.IdAccount,
            NEW.DataKey,
            NEW.DataValue,
            @newEditedBy,
            CURRENT_TIMESTAMP,
            NEW.`Checksum`);
	END IF;
END
// DELIMITER ;


ALTER TABLE `cb_achievement` ADD COLUMN `Checksum` varchar(32);
ALTER TABLE `cb_achievement_user` ADD COLUMN `Checksum` varchar(32);

ALTER TABLE `cb_leaderboard` ADD COLUMN `Checksum` varchar(32);
ALTER TABLE `cb_leaderboard_user` ADD COLUMN `Checksum` varchar(32);
