START TRANSACTION;


CREATE TABLE `cb_account` (
  `Id` bigint(20) NOT NULL,
  `Username` varchar(45) NOT NULL,
  `Password` varchar(45) 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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_account_platform` (
  `IdAccount` bigint(20) NOT NULL,
  `PlatformKey` varchar(45) NOT NULL,
  `PlatformId` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_achievement` (
  `Id` bigint(20) NOT NULL,
  `Title` varchar(100) CHARACTER SET latin1 NOT NULL,
  `Description` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `UniqueRecords` tinyint(1) NOT NULL DEFAULT '0',
  `Position` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_achievement_user` (
  `Id` bigint(20) NOT NULL,
  `IdAchievement` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Progress` smallint(6) NOT NULL,
  `LastUpdated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_adminaccount` (
  `Id` bigint(20) NOT NULL,
  `Username` varchar(45) NOT NULL,
  `Password` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_customdata` (
  `IdAccount` bigint(20) NOT NULL,
  `DataKey` varchar(45) NOT NULL,
  `DataValue` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_friend` (
  `IdAccount` bigint(20) NOT NULL,
  `IdFriend` bigint(20) NOT NULL,
  `State` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_inventory` (
  `Id` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `Quantity` int(11) NOT NULL,
  `CustomData` text CHARACTER SET latin1
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_issue` (
  `Id` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Description` longtext NOT NULL,
  `UrlLog` varchar(255) DEFAULT NULL,
  `UrlScreenshot` varchar(255) DEFAULT NULL,
  `IssueDate` datetime NOT NULL,
  `IssueIp` varchar(255) DEFAULT NULL,
  `IssueState` tinyint(1) NOT NULL DEFAULT '0',
  `CustomData` longtext,
  `UpdateDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_leaderboard` (
  `Id` bigint(20) NOT NULL,
  `Title` varchar(100) NOT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `UniqueRecords` tinyint(1) NOT NULL DEFAULT '0',
  `ValueType` tinyint(1) NOT NULL DEFAULT '0',
  `OrderType` tinyint(1) NOT NULL DEFAULT '0',
  `AllowAnonymous` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_leaderboard_user` (
  `Id` bigint(20) NOT NULL,
  `IdLeaderboard` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `ValueInt` int(11) NOT NULL DEFAULT '0',
  `ValueFloat` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `LastUpdated` datetime DEFAULT NULL,
  `Username` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_mail` (
  `Id` bigint(20) NOT NULL,
  `SendDate` datetime NOT NULL,
  `ReadDate` datetime DEFAULT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `IdGroup` bigint(20) NOT NULL,
  `IdSender` bigint(20) NOT NULL,
  `IsPublic` tinyint(1) NOT NULL,
  `Subject` varchar(255) NOT NULL,
  `Message` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_match` (
  `Id` bigint(20) NOT NULL,
  `IdTournament` bigint(20) NOT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `DateCreation` datetime NOT NULL,
  `DateExpire` datetime DEFAULT NULL,
  `Rounds` int(2) NOT NULL,
  `Finished` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_match_account` (
  `Id` bigint(20) NOT NULL,
  `IdMatch` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `CustomData` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_match_customdata` (
  `IdMatch` bigint(20) NOT NULL,
  `DataKey` varchar(45) NOT NULL,
  `DataValue` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_match_round` (
  `Id` bigint(20) NOT NULL,
  `IdMatchAccount` bigint(20) NOT NULL,
  `Score` decimal(20,5) NOT NULL DEFAULT '0.00000',
  `DateScore` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_news` (
  `Id` bigint(20) NOT NULL,
  `IdAdminAccount` bigint(20) NOT NULL,
  `PublishDate` datetime NOT NULL,
  `Subject` varchar(255) NOT NULL,
  `Message` longtext NOT NULL,
  `Url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_newsletter` (
  `Id` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Subject` varchar(255) NOT NULL,
  `Body` longtext NOT NULL,
  `IsHtml` tinyint(1) NOT NULL,
  `DateCreation` datetime NOT NULL,
  `DateSent` datetime DEFAULT NULL,
  `Status` tinyint(1) DEFAULT NULL,
  `LogMessage` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_newsletterlog` (
  `Id` bigint(20) NOT NULL,
  `IdNewsletter` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Sent` tinyint(1) NOT NULL,
  `DateCreation` datetime NOT NULL,
  `Message` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_serversettings` (
  `DataKey` varchar(45) NOT NULL,
  `DataValue` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_session` (
  `Id` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `GUID` varchar(45) NOT NULL,
  `LastActionDate` datetime NOT NULL,
  `LastActionIP` varchar(45) NOT NULL,
  `SignatureTimestamp` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_tournament` (
  `Id` bigint(20) NOT NULL,
  `IdOwner` bigint(20) NOT NULL,
  `DateCreation` datetime DEFAULT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `Finished` tinyint(1) NOT NULL,
  `DateFinished` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_tournament_customdata` (
  `IdTournament` bigint(20) NOT NULL,
  `DataKey` varchar(45) NOT NULL,
  `DataValue` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_userfile` (
  `Id` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `Url` varchar(255) CHARACTER SET latin1 NOT NULL,
  `ShareType` tinyint(1) NOT NULL,
  `Likes` int(11) NOT NULL,
  `Views` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_userfilesactivity` (
  `Id` bigint(20) NOT NULL,
  `IdFile` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL,
  `Likes` tinyint(1) NOT NULL,
  `Views` int(11) NOT NULL,
  `LastActivity` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_usergroup` (
  `Id` bigint(20) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `IdOwner` bigint(20) NOT NULL,
  `Public` tinyint(1) NOT NULL,
  `CustomData` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cb_usergroupaccount` (
  `IdGroup` bigint(20) NOT NULL,
  `IdAccount` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `cb_account`
  ADD PRIMARY KEY (`Id`),
  ADD UNIQUE KEY `AccountUsername` (`Username`),
  ADD KEY `AccountPassword` (`Password`);

ALTER TABLE `cb_account_platform`
  ADD PRIMARY KEY (`IdAccount`,`PlatformId`,`PlatformKey`);

ALTER TABLE `cb_achievement`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_achievement_user`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `AchievementUser` (`IdAchievement`),
  ADD KEY `UserAchievement` (`IdAccount`);

ALTER TABLE `cb_adminaccount`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `AdminAccountUsername` (`Username`),
  ADD KEY `AdminAccountPassword` (`Password`);

ALTER TABLE `cb_customdata`
  ADD PRIMARY KEY (`IdAccount`,`DataKey`);

ALTER TABLE `cb_friend`
  ADD PRIMARY KEY (`IdAccount`,`IdFriend`);

ALTER TABLE `cb_inventory`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `InventoryAccount` (`IdAccount`);

ALTER TABLE `cb_issue`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `IssueAccount` (`IdAccount`,`IssueDate`),
  ADD KEY `IssueState` (`IssueDate`,`IssueState`);

ALTER TABLE `cb_leaderboard`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_leaderboard_user`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `LeaderboardScoreValue` (`IdLeaderboard`,`ValueInt`,`ValueFloat`) USING BTREE,
  ADD KEY `LeaderboardAccount` (`IdAccount`);

ALTER TABLE `cb_mail`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `MailDate` (`SendDate`,`IdAccount`,`IdSender`);

ALTER TABLE `cb_match`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `IDX_Match_Finished` (`Finished`,`DateCreation`,`DateExpire`),
  ADD KEY `IDX_Match_Tournament` (`IdTournament`) USING BTREE;

ALTER TABLE `cb_match_account`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `FK_Account_User_idx` (`IdAccount`),
  ADD KEY `FK_Account_Match_idx` (`IdMatch`);

ALTER TABLE `cb_match_customdata`
  ADD PRIMARY KEY (`IdMatch`,`DataKey`);

ALTER TABLE `cb_match_round`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `FK_Round_Match_idx` (`IdMatchAccount`);

ALTER TABLE `cb_news`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_newsletter`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_newsletterlog`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_serversettings`
  ADD PRIMARY KEY (`DataKey`),
  ADD UNIQUE KEY `DataKey_UNIQUE` (`DataKey`);

ALTER TABLE `cb_session`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `UserSession` (`IdAccount`,`GUID`,`LastActionDate`);

ALTER TABLE `cb_tournament`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `IDX_Tournament_Finished` (`Finished`,`DateCreation`,`DateFinished`);

ALTER TABLE `cb_tournament_customdata`
  ADD PRIMARY KEY (`IdTournament`,`DataKey`);

ALTER TABLE `cb_userfile`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `UserFileAccount` (`IdAccount`);

ALTER TABLE `cb_userfilesactivity`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `cb_usergroup`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `UserGroupOwner` (`IdOwner`,`Public`);

ALTER TABLE `cb_usergroupaccount`
  ADD PRIMARY KEY (`IdGroup`,`IdAccount`);


ALTER TABLE `cb_account`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_achievement`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_achievement_user`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_adminaccount`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_inventory`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_issue`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_leaderboard`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_leaderboard_user`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_mail`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_match`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_match_account`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_match_round`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_news`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_newsletter`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_newsletterlog`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_session`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_tournament`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_userfile`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_userfilesactivity`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `cb_usergroup`
  MODIFY `Id` bigint(20) NOT NULL AUTO_INCREMENT;

COMMIT;