CREATE TABLE `cb_account_log` (
  `Log_ID` int(9) NOT NULL AUTO_INCREMENT,
  `Id` bigint(20) NOT NULL,
  `Username` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `LastLoginIp` varchar(255) DEFAULT NULL,
  `LastLoginDate` datetime DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `ActivationCode` varchar(10) DEFAULT NULL,
  `ChangePwdCode` varchar(10) DEFAULT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `EditedBy` bigint(20),
  `EditDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Log_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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;

CREATE TABLE `cb_adminaccount_log` (
  `Log_ID` int(9) NOT NULL AUTO_INCREMENT,
  `Id` bigint(20) NOT NULL,
  `Username` varchar(45) NOT NULL,
  `Password` varchar(45) NOT NULL,
  `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_account_log` 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);
ALTER TABLE `cb_adminaccount_log` ADD COLUMN `Checksum` varchar(32);

DROP TRIGGER IF EXISTS `Account_Log`;
DELIMITER //
CREATE TRIGGER `Account_Log` AFTER INSERT ON `cb_account`
 FOR EACH ROW
  BEGIN
	INSERT INTO cb_account_log(`Id`,`Username`,`Password`,`LastLoginIp`,`LastLoginDate`,`Email`,  `ActivationCode`,`ChangePwdCode`,`Enabled`,`Checksum`)
	VALUES (NEW.`Id`,NEW.`Username`,NEW.`Password`,NEW.`LastLoginIp`,NEW.`LastLoginDate`,NEW.`Email`,  NEW.`ActivationCode`, NEW.`ChangePwdCode`,NEW.`Enabled`,NEW.`Checksum`);
END
// DELIMITER ;

DROP TRIGGER IF EXISTS `Account_Log_Update`;
DELIMITER //
CREATE TRIGGER `Account_Log_Update` BEFORE UPDATE ON `cb_account`
 FOR EACH ROW
  BEGIN
  	IF NEW.`Password` <> OLD.`Password` OR NEW.`Enabled` <> OLD.`Enabled`
  	THEN
 		INSERT INTO cb_account_log(`Id`,`Username`,`Password`,`LastLoginIp`,`LastLoginDate`,`Email`,  `ActivationCode`,`ChangePwdCode`,`Enabled`,`Checksum`)
		VALUES (NEW.`Id`,NEW.`Username`,NEW.`Password`,NEW.`LastLoginIp`,NEW.`LastLoginDate`,NEW.`Email`,  NEW.`ActivationCode`, NEW.`ChangePwdCode`,NEW.`Enabled`,NEW.`Checksum`);
	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));
	IF @key= 'EditedBy'
	THEN
		UPDATE cb_account_log SET EditedBy=@newEditedBy WHERE Id=NEW.IdAccount and EditedBy is null;
	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);