ALTER TABLE ActivityLog
  ADD COLUMN ip VARCHAR(15) NULL
  AFTER vars,
  ADD COLUMN routeName VARCHAR(30) NULL
  AFTER userId,
  ADD COLUMN userAgent VARCHAR(255) NULL
  AFTER ip,
  CHANGE COLUMN `id` `id` BIGINT NOT NULL AUTO_INCREMENT
  FIRST;

UPDATE ActivityLog al, UserSession us
SET al.ip = us.ip
WHERE al.sid = us.sid;

ALTER TABLE UserSession
  DROP COLUMN ip,
  DROP COLUMN request,
  DROP COLUMN userBrowserId;

ALTER TABLE `ActivityLog`
  ADD COLUMN `campaignData` VARCHAR(45) NULL DEFAULT NULL
  AFTER `vars`,
  ADD INDEX `campaignData` (`campaignData`);


DROP TABLE Campaign;
CREATE TABLE `Campaign` (
  `id`             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`           VARCHAR(50)      NOT NULL,
  `type`           VARCHAR(50)      NULL     DEFAULT NULL,
  `descriptor`     VARCHAR(50)      NULL     DEFAULT NULL,
  `descriptorType` VARCHAR(50)      NULL     DEFAULT NULL,
  `pageView`       INT(11)          NOT NULL DEFAULT '0',
  `conversion`     SMALLINT(6)      NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name`)
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB
  AUTO_INCREMENT = 6;


ALTER TABLE `Campaign`
  CHANGE COLUMN `descriptor` `code` VARCHAR(50) NOT NULL AFTER `type`,
  DROP COLUMN `descriptorType`;

INSERT INTO Campaign (name,descriptor) VALUES ('Organic','');
ALTER TABLE `Campaign`
  DROP INDEX `name`,
  ADD UNIQUE INDEX `Campaign_code` (`code`);

DROP TABLE ProductStatistics;

CREATE TABLE `ProductStatistics` (
  `productId`        INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `campaignId`       INT(10) UNSIGNED NOT NULL,
  `pageView`         SMALLINT(6)      NOT NULL DEFAULT '0',
  `conversion`       TINYINT(4)       NOT NULL DEFAULT '0',
  `creationDate`     TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastUpdate`       TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`productId`, `productVariantId`, `campaignId`),
  INDEX `productId_productVariantId` (`productId`, `productVariantId`),
  INDEX `campaignId` (`campaignId`),
  CONSTRAINT `productStatistics_campaign_id` FOREIGN KEY (`campaignId`) REFERENCES `Campaign` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
  CONSTRAINT `productStatistics_product` FOREIGN KEY (`productId`, `productVariantId`) REFERENCES `Product` (`id`, `productVariantId`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

DROP TABLE ProductStatisticsDetail;
CREATE TABLE `ProductStatisticsDetail` (
  `id`               INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `productId`        INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `campaignId`       INT(10) UNSIGNED NOT NULL,
  `timestamp`        TIMESTAMP        NULL     DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `productId_productVariantId` (`productId`, `productVariantId`),
  INDEX `campaignId` (`campaignId`),
  INDEX `timestamp` (`timestamp`),
  CONSTRAINT `productStatisticsDetail_campaign` FOREIGN KEY (`campaignId`) REFERENCES `Campaign` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
  CONSTRAINT `productStatisticsDetail_product` FOREIGN KEY (`productId`, `productVariantId`) REFERENCES `Product` (`id`, `productVariantId`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

DROP TABLE ProductStatisticsDetailHasOrderLine;
CREATE TABLE `ProductStatisticsDetailHasOrderLine` (
  `productStatisticsDetailId` INT(10) UNSIGNED NOT NULL,
  `orderLineId`               INT(10) UNSIGNED NOT NULL,
  `orderId`                   INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`productStatisticsDetailId`, `orderLineId`, `orderId`),
  INDEX `productStatisticsDetailId` (`productStatisticsDetailId`),
  INDEX `orderLineId_orderId` (`orderLineId`, `orderId`),
  CONSTRAINT `productStatisticsDetailHasOrder_orderLine` FOREIGN KEY (`orderLineId`, `orderId`) REFERENCES `OrderLine` (`id`, `orderId`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
  CONSTRAINT `productStatisticsDetailHasOrder_productStatistic` FOREIGN KEY (`productStatisticsDetailId`) REFERENCES `ProductStatisticsDetail` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COMMENT = 'Link to Conversion'
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;


CREATE OR REPLACE VIEW vProductStatisticsCampaing AS
    SELECT
    concat(`psd`.`productId`, '-', `psd`.`productVariantId`) AS `code`,
    `psd`.`productId`                                        AS `productId`,
    `psd`.`productVariantId`                                 AS `productVariantId`,
    `psd`.`campaignId`                                       AS `campaignId`,
    group_concat(DISTINCT `s`.`name` SEPARATOR ',')          AS `shops`,
    `pse`.`name`                                             AS `season`,
    `c`.`name`                                               AS `campaign`,
    count(`psd`.`id`)                                        AS `pageView`,
    count(distinct `psdhol`.`productStatisticsDetailId`)     AS `conversions`,
    min(`psd`.`timestamp`)                                   AS `first`,
    max(`psd`.`timestamp`)                                   AS `last`,
    `ps`.`creationDate`                                      AS `firstest`,
    `pb`.`name`                                              AS `brand`
  FROM (((((((((`Product` `p`
    JOIN `ProductBrand` `pb`)
    JOIN `ProductSeason` `pse`)
    JOIN `ProductHasProductCategory` `phpc`)
    JOIN `ProductCategory` `pc`)
    JOIN `ShopHasProduct` `shp`)
    JOIN `Shop` `s`)
    JOIN `ProductStatistics` `ps`)
    JOIN `ProductStatisticsDetail` `psd`)
    JOIN `Campaign` `c`)
    LEFT JOIN ProductStatisticsDetailHasOrderLine psdhol ON psd.id = psdhol.productStatisticsDetailId
    LEFT JOIN OrderLine ol ON psdhol.orderLineId = ol.id AND psdhol.orderId = ol.orderId
  WHERE ((`p`.`productBrandId` = `pb`.`id`) AND (`p`.`id` = `phpc`.`productId`) AND
         (`p`.`productVariantId` = `phpc`.`productVariantId`) AND (`phpc`.`productCategoryId` = `pc`.`id`) AND
         (`p`.`productSeasonId` = `pse`.`id`) AND (`p`.`id` = `shp`.`productId`) AND
         (`p`.`productVariantId` = `shp`.`productVariantId`) AND (`s`.`id` = `shp`.`shopId`) AND
         (`p`.`id` = `psd`.`productId`) AND (`p`.`productVariantId` = `psd`.`productVariantId`) AND
         (`p`.`id` = `ps`.`productId`) AND (`p`.`productVariantId` = `ps`.`productVariantId`) AND
         (`ps`.`campaignId` = `psd`.`campaignId`) AND (`psd`.`campaignId` = `c`.`id`))
  GROUP BY `psd`.`productId`, `psd`.`productVariantId`, `psd`.`campaignId`;

