CREATE TABLE IF NOT EXISTS `db_calls` (
  `UserId` int(11) NOT NULL,
  `FromToWho` varchar(50) COLLATE utf8_bin NOT NULL,
  `RecordedCall` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_categories` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(50) COLLATE utf8_bin NOT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_categoriesblocks` (
  `UserId` int(11) NOT NULL,
  `CategoryId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_locations` (
  `UserId` int(11) NOT NULL,
  `Location` varchar(250) COLLATE utf8_bin NOT NULL,
  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_messages` (
  `UserId` int(11) NOT NULL,
  `FromWho` varchar(50) COLLATE utf8_bin NOT NULL,
  `Message` varchar(255) COLLATE utf8_bin NOT NULL,
  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_models` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(50) COLLATE utf8_bin NOT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_pushnotifications` (
  `Title` varchar(50) COLLATE utf8_bin NOT NULL,
  `Message` varchar(256) COLLATE utf8_bin NOT NULL,
  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_questions` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `CategoryId` int(11) NOT NULL,
  `ModelId` int(11) NOT NULL,
  `TechnologyId` int(11) NOT NULL,
  `DifficultyLevel` tinyint(4) NOT NULL,
  `QuestionText` varchar(250) COLLATE utf8_bin NOT NULL,
  `Option1` varchar(50) COLLATE utf8_bin NOT NULL,
  `Option2` varchar(50) COLLATE utf8_bin NOT NULL,
  `Option3` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `Option4` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `RightOption` tinyint(4) NOT NULL,
  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DateModified` timestamp NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_reports` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `QuestionId` int(11) NOT NULL,
  `Attempts` int(11) NOT NULL,
  `Time` int(11) NOT NULL,
  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_sessions` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `SessionAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SessionEnded` timestamp NULL DEFAULT NULL,
  `IpAddress` varchar(50) COLLATE utf8_bin NOT NULL,
  `DeviceInfo` varchar(250) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_technologies` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(50) COLLATE utf8_bin NOT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `db_users` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ApiKey` varchar(500) COLLATE utf8_bin NOT NULL,
  `Email` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `PasswordHash` varchar(500) COLLATE utf8_bin NOT NULL,
  `Username` varchar(50) COLLATE utf8_bin NOT NULL,
  `AccessLevel` tinyint(4) NOT NULL,
  `LastOnlineTimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `TrackLocation` tinyint(1) NOT NULL DEFAULT '0',
  `SaveMessages` tinyint(1) NOT NULL DEFAULT '0',
  `LogCalls` tinyint(1) NOT NULL DEFAULT '0',
  `MaxDifficultyLevel` tinyint(4) NOT NULL DEFAULT '5',
  `QuestionCount` int(11) NOT NULL DEFAULT '0',
  `Score` int(11) NOT NULL DEFAULT '0',
  `AverageTime` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `db_users` (`Id`, `ApiKey`, `Email`, `PasswordHash`, `Username`, `AccessLevel`, `LastOnlineTimestamp`, `TrackLocation`, `SaveMessages`, `LogCalls`, `MaxDifficultyLevel`, `QuestionCount`, `Score`, `AverageTime`) VALUES
(1, '8cb28cc276e98ddfbbea68457770c2db', NULL, '$2a$10$2e402a9c2169e42071b99u.AYf/ON.HBn4C.pF2NDqpfvzJlWBt.y', 'Administrator', 0, '2017-04-09 09:16:51', 1, 1, 1, 5, 5, 2, 3);
