CREATE TABLE `ApplicationLog` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `source` VARCHAR(255) NOT NULL,
  `severity` VARCHAR(45) NOT NULL,
  `title` VARCHAR(45) NULL DEFAULT NULL,
  `message` TINYTEXT NULL,
  `context` TEXT NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sid` VARCHAR(64) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `source` (`source`),
  INDEX `severity` (`severity`),
  INDEX `creationDate` (`creationDate`)
)
  COLLATE='utf8_general_ci'
  ENGINE=MyISAM
  AUTO_INCREMENT=10256603
;

CREATE TABLE `ActivityLog` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `sid` VARCHAR(64) NOT NULL,
  `userId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `routeName` VARCHAR(30) NULL DEFAULT NULL,
  `referral` VARCHAR(200) NULL DEFAULT NULL,
  `requestedResource` VARCHAR(500) NULL DEFAULT NULL,
  `method` CHAR(3) NULL DEFAULT NULL,
  `actionArgs` VARCHAR(300) NULL DEFAULT NULL,
  `vars` VARCHAR(300) NULL DEFAULT NULL,
  `hasCampaignData` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `ip` VARCHAR(15) NULL DEFAULT NULL,
  `userAgent` VARCHAR(255) NULL DEFAULT NULL,
  `queryCount` TINYINT(4) NULL DEFAULT '0',
  `responseTime` VARCHAR(9) NULL DEFAULT NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `routeName` (`routeName`),
  INDEX `creationDate` (`creationDate`),
  INDEX `vars` (`vars`),
  INDEX `hasCampaignData` (`hasCampaignData`)
)
  COLLATE='utf8_general_ci'
  ENGINE=MyISAM
  AUTO_INCREMENT=5653006
;

CREATE TABLE `JobLog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `jobId` INT(11) NOT NULL,
  `jobExecutionId` INT(11) NOT NULL,
  `severity` VARCHAR(10) NOT NULL DEFAULT 'REPORT',
  `subject` VARCHAR(45) NOT NULL,
  `content` VARCHAR(255) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `context` TEXT NULL,
  PRIMARY KEY (`id`, `jobId`, `jobExecutionId`),
  INDEX `jobId_idx` (`jobId`, `jobExecutionId`),
  INDEX `severity` (`severity`)
)
  COLLATE='utf8_general_ci'
  ENGINE=MyISAM
  AUTO_INCREMENT=15637526
;

CREATE TABLE `QueryStatistics` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queryString` VARCHAR(455) NULL DEFAULT '0',
  `parameters` TINYTEXT NULL,
  `microtime` FLOAT UNSIGNED NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)
  COLLATE='utf8_general_ci'
  ENGINE=MyISAM
  AUTO_INCREMENT=979305
;

CREATE TABLE `ProductStatistics` (
  `productId` INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `shopId` INT(10) UNSIGNED NOT NULL,
  `qty` INT(10) NOT NULL,
  `isVisible` TINYINT(3) UNSIGNED NOT NULL,
  `hasDetails` TINYINT(3) UNSIGNED NOT NULL,
  `hasPhotos` TINYINT(3) UNSIGNED NOT NULL,
  `hasPrices` TINYINT(3) UNSIGNED NOT NULL,
  `isOnSale` TINYINT(3) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  PRIMARY KEY (`productId`, `productVariantId`, `shopId`, `date`),
  INDEX `productId_productVariantId_shopId` (`productId`, `productVariantId`, `shopId`),
  CONSTRAINT `fk_ProductStatistics_shopHasProduct` FOREIGN KEY (`productId`, `productVariantId`, `shopId`) REFERENCES `ShopHasProduct` (`productId`, `productVariantId`, `shopId`)
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
;

