CREATE TABLE `AccessList` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `lastAccess` datetime NOT NULL,
  `created` datetime NOT NULL,
  `banTime` datetime DEFAULT NULL,
  `retries` int(10) unsigned NOT NULL DEFAULT '0',
  `captchaReload` int(10) unsigned DEFAULT '0',
  `hardBan` tinyint(3) unsigned DEFAULT '0',
  `emailSent` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_UNIQUE` (`ip`)
) ENGINE=MEMORY AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Alle Zugriffe werden mitgeloggt für Botabwehr';

CREATE TABLE `Captcha` (
  `captchaId` int(11) NOT NULL,
  `origCaptchaFileName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `currCaptchaFileName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lastCaptchaFileName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `currQuestionFileName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lastQuestionFileName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `origQuestionFileName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`captchaId`),
  UNIQUE KEY `origCaptchaUrl` (`origCaptchaFileName`),
  UNIQUE KEY `currCaptchaUrl` (`currCaptchaFileName`),
  UNIQUE KEY `currQuestionUrl` (`currQuestionFileName`),
  UNIQUE KEY `currCaptchaFileName` (`currCaptchaFileName`),
  UNIQUE KEY `currQuestionFileName` (`currQuestionFileName`),
  UNIQUE KEY `origCaptchaFileName` (`origCaptchaFileName`),
  UNIQUE KEY `lastCaptchaUrl` (`lastCaptchaFileName`),
  UNIQUE KEY `lastQuestionUrl` (`lastQuestionFileName`),
  UNIQUE KEY `lastCaptchaFileName` (`lastCaptchaFileName`),
  UNIQUE KEY `lastQuestionFileName` (`lastQuestionFileName`),
  KEY `captchaIndex` (`captchaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Mapping + ZufallsNamen für Captcha/Fragebilder';

CREATE TABLE IF NOT EXISTS `cronJobStatistic` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end` timestamp NULL DEFAULT NULL,
  `status` enum('ENGAGED','DISENGAGED','FAILED') COLLATE utf8_unicode_ci NOT NULL,
  `message` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
