
CREATE TABLE `Campaign` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `type` VARCHAR(50) NULL DEFAULT NULL,
  `code` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `Campaign_code` (`code`)
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
;

CREATE TABLE `CampaignVisit` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaignId` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `campaignId`),
  INDEX `timestamp` (`timestamp`),
  INDEX `FK1_campaignVisits_campaign` (`campaignId`),
  CONSTRAINT `FK1_campaignVisits_campaign` FOREIGN KEY (`campaignId`) REFERENCES `Campaign` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
;

CREATE TABLE `CampaignVisitHasProduct` (
  `campaignVisitId` INT(10) UNSIGNED NOT NULL,
  `campaignId` INT(10) UNSIGNED NOT NULL,
  `productId` INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`campaignVisitId`, `campaignId`, `productId`, `productVariantId`),
  INDEX `productId_productVariantId` (`productId`, `productVariantId`),
  INDEX `campaignVisitId_campaignId` (`campaignVisitId`, `campaignId`),
  CONSTRAINT `FK1_toCampaignVisit` FOREIGN KEY (`campaignVisitId`, `campaignId`) REFERENCES `CampaignVisit` (`id`, `campaignId`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK2_toProduct` FOREIGN KEY (`productId`, `productVariantId`) REFERENCES `Product` (`id`, `productVariantId`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
;

CREATE TABLE `CampaignVisitHasOrder` (
  `campaignVisitId` INT(10) UNSIGNED NOT NULL,
  `campaignId` INT(10) UNSIGNED NOT NULL,
  `orderId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`campaignVisitId`, `campaignId`, `orderId`),
  INDEX `campaignVisitId_campaignId` (`campaignVisitId`, `campaignId`),
  INDEX `orderId` (`orderId`),
  CONSTRAINT `FK1_toVisit` FOREIGN KEY (`campaignVisitId`, `campaignId`) REFERENCES `CampaignVisit` (`id`, `campaignId`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK1_visit_to_order` FOREIGN KEY (`orderId`) REFERENCES `Order` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  ENGINE=InnoDB
;

ALTER TABLE `ActivityLog`
  ADD COLUMN `hasCampaignData` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `campaignData`;

UPDATE ActivityLog set hasCampaignData = if(campaignData is null, 0, 1);

ALTER TABLE `ActivityLog`
  DROP COLUMN `campaignData`,
  ADD INDEX `hasCampaignData` (`hasCampaignData`);