/*
SQLyog Ultimate
MySQL - 10.2.7-MariaDB : Database - bugTest
*********************************************************************
*/



CREATE DATABASE /*!32312 IF NOT EXISTS*/`bugTest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `bugTest`;

/*Table structure for table `fieldListing` */


CREATE TABLE `fieldListing` (
  `fieldId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `fieldName` varchar(16) NOT NULL,
  `productId` tinyint(3) unsigned NOT NULL,
  `websiteId` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `label` varchar(32) DEFAULT NULL,
  `type` enum('inputText','inputTel','inputNumber','inputEmail','inputCheckbox','inputRadio','inputSubmit','select','textArea','button','raw') DEFAULT NULL,
  `classes` varchar(256) DEFAULT NULL,
  `style` varchar(512) DEFAULT NULL,
  `extraInformation` text DEFAULT NULL,
  `orderToDisplay` tinyint(3) unsigned DEFAULT NULL,
  `required` enum('Yes','No') NOT NULL,
  `validationRoutine` varchar(32) NOT NULL,
  `validationParams` text DEFAULT NULL,
  PRIMARY KEY (`fieldId`),
  UNIQUE KEY `fieldNameProductIdWebsiteIdUnique` (`fieldName`,`productId`,`websiteId`),
  KEY `fieldListingLinkToProductProducts` (`productId`),
  CONSTRAINT `fieldListingLinkToProductProducts` FOREIGN KEY (`productId`) REFERENCES `products` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fieldListingExtraInformationValidJSONOrNull` CHECK (`extraInformation` is null or json_valid(`extraInformation`))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `fieldListing` */

insert  into `fieldListing`(`fieldId`,`fieldName`,`productId`,`websiteId`,`label`,`type`,`classes`,`style`,`extraInformation`,`orderToDisplay`,`required`,`validationRoutine`,`validationParams`) values 
(1,'age',10,1,'Customer Age','inputNumber','input',NULL,NULL,1,'Yes','validateNumber','[50,80]'),
(2,'_headers',0,0,'System Headers',NULL,NULL,NULL,NULL,NULL,'Yes','validateHeaders',NULL);

/*Table structure for table `products` */


CREATE TABLE `products` (
  `productId` tinyint(3) unsigned NOT NULL,
  `productName` varchar(31) NOT NULL,
  `productActive` enum('Yes','No') NOT NULL DEFAULT 'No',
  PRIMARY KEY (`productId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `products` */

insert  into `products`(`productId`,`productName`,`productActive`) values 
(0,'SYSTEM','No'),
(1,'Funeral Leads (Legacy)','No'),
(2,'Debt','Yes'),
(3,'Life Insurance','Yes'),
(4,'Contractor Guardian','No'),
(5,'Private Medical Insurance','Yes'),
(6,'Equity Release','No'),
(7,'Packaged Bank Accounts','Yes'),
(8,'Flight Delays','Yes'),
(9,'Solar','Yes'),
(10,'Funeral Distribution','Yes'),
(11,'Wills','Yes'),
(12,'Mortgages','Yes'),
(13,'Cars','No'),
(14,'Loans','No');

/*Table structure for table `supplierCampaigns` */


CREATE TABLE `supplierCampaigns` (
  `campaignId` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `groupId` tinyint(3) unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  `active` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  `trafficType` varchar(32) NOT NULL DEFAULT 'UNKNOWN',
  `websiteCode` char(16) DEFAULT NULL,
  `webserviceTestApiKey` char(15) DEFAULT NULL,
  `webserviceLiveApiKey` char(32) DEFAULT NULL,
  `defaultProduct` tinyint(3) unsigned DEFAULT NULL,
  `defaultWebsite` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`campaignId`),
  KEY `supplierCampaignsToGroupsGroupIdLink` (`groupId`),
  CONSTRAINT `supplierCampaignsToGroupsGroupIdLink` FOREIGN KEY (`groupId`) REFERENCES `supplierGroups` (`groupId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `supplierCampaigns` */

insert  into `supplierCampaigns`(`campaignId`,`groupId`,`name`,`active`,`trafficType`,`websiteCode`,`webserviceTestApiKey`,`webserviceLiveApiKey`,`defaultProduct`,`defaultWebsite`) values 
(0,0,'SYSTEM_USE_ONLY','No','SYSTEM',NULL,NULL,NULL,NULL,NULL),
(1,0,'Reposted Leads','Yes','SYSTEM',NULL,'test_rjOdnYjj','DPDJkVmTfn2CJ3KdNJr6w0R1KRy7rfWP',NULL,NULL),
(2,0,'Website Direct','Yes','SYSTEM','ZERO','test_TXuBf7v5','CF7fhjysp94IHWA8IRmfF1HTbwU0uj6G',NULL,NULL),
(3,1,'Test Campaign','Yes','TEST','TEST',NULL,NULL,NULL,NULL);

/*Table structure for table `supplierCriteria` */


CREATE TABLE `supplierCriteria` (
  `groupId` tinyint(3) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to an entry',
  `campaignId` smallint(5) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to a group',
  `productId` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Set to 0 to apply to all products',
  `fieldId` mediumint(8) unsigned NOT NULL,
  `validationRoutine` varchar(32) NOT NULL,
  `validationParams` text DEFAULT NULL,
  PRIMARY KEY (`groupId`,`campaignId`,`productId`,`fieldId`),
  KEY `supplierCriteriaLinkToCampaignsCampaignId` (`campaignId`),
  KEY `supplierCriteriaLinkToProductsProductId` (`productId`),
  CONSTRAINT `supplierCriteriaLinkToCampaignsCampaignId` FOREIGN KEY (`campaignId`) REFERENCES `supplierCampaigns` (`campaignId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierCriteriaLinkToGroupsGroupId` FOREIGN KEY (`groupId`) REFERENCES `supplierGroups` (`groupId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierCriteriaLinkToProductsProductId` FOREIGN KEY (`productId`) REFERENCES `products` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierInformationLinkToOnlyACampaignOrGroup` CHECK (`groupId` = 0 xor `campaignId` = 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `supplierCriteria` */

/*Table structure for table `supplierGroups` */


CREATE TABLE `supplierGroups` (
  `groupId` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `active` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  PRIMARY KEY (`groupId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*Data for the table `supplierGroups` */

insert  into `supplierGroups`(`groupId`,`name`,`active`) values 
(0,'SYSTEM','Yes'),
(1,'Internal Campaigns','Yes');

/*Table structure for table `supplierInformation` */


CREATE TABLE `supplierInformation` (
  `groupId` tinyint(3) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to an entry',
  `campaignId` smallint(5) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to a group',
  `productId` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Set to 0 to apply to all products',
  `cpl` decimal(6,2) NOT NULL,
  `dedupeDays` smallint(6) NOT NULL,
  `supplierType` enum('Internal','External','System') NOT NULL,
  PRIMARY KEY (`groupId`,`campaignId`,`productId`),
  KEY `supplierInformationLinkToCampaignsCampaignId` (`campaignId`),
  KEY `supplierInformationLinkToProductsProductsId` (`productId`),
  CONSTRAINT `supplierInformationLinkToCampaignsCampaignId` FOREIGN KEY (`campaignId`) REFERENCES `supplierCampaigns` (`campaignId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierInformationLinkToGroupsGroupId` FOREIGN KEY (`groupId`) REFERENCES `supplierGroups` (`groupId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierInformationLinkToProductsProductsId` FOREIGN KEY (`productId`) REFERENCES `products` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierInformationLinkToOnlyACampaignOrGroup` CHECK (`groupId` = 0 xor `campaignId` = 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `supplierInformation` */

insert  into `supplierInformation`(`groupId`,`campaignId`,`productId`,`cpl`,`dedupeDays`,`supplierType`) values 
(0,1,0,0.00,0,'System'),
(0,2,0,0.00,14,'System'),
(1,0,0,5.00,14,'Internal');

/*Table structure for table `supplierSendingTimes` */


CREATE TABLE `supplierSendingTimes` (
  `groupId` tinyint(3) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to an entry',
  `campaignId` smallint(5) unsigned NOT NULL COMMENT 'Set to 0 if the settings apply to a group',
  `productId` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Set to 0 to apply to all products',
  `jsonRuleString` text NOT NULL,
  `currentStartTime` time GENERATED ALWAYS AS (coalesce(cast(json_value(json_extract(`jsonRuleString`,concat('$.singleDates."',curdate(),'"')),'$.start') as time),cast(json_value(json_extract(`jsonRuleString`,concat('$.singleDay."',dayname(curdate()),'"')),'$.start') as time),cast(json_value(json_extract(`jsonRuleString`,'$.default'),'$.start') as time))) VIRTUAL,
  `currentEndTime` time GENERATED ALWAYS AS (coalesce(cast(json_value(json_extract(`jsonRuleString`,concat('$.singleDates."',curdate(),'"')),'$.end') as time),cast(json_value(json_extract(`jsonRuleString`,concat('$.singleDay."',dayname(curdate()),'"')),'$.end') as time),cast(json_value(json_extract(`jsonRuleString`,'$.default'),'$.end') as time))) VIRTUAL,
  PRIMARY KEY (`groupId`,`campaignId`,`productId`),
  KEY `supplierSendingTimesLinkToSupplierCampaignsCampignId` (`campaignId`),
  KEY `supplierSendingTimesLinkToSupplierProductsProductId` (`productId`),
  CONSTRAINT `supplierSendingTimesLinkToSupplierCampaignsCampignId` FOREIGN KEY (`campaignId`) REFERENCES `supplierCampaigns` (`campaignId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierSendingTimesLinkToSupplierGroupsGroupId` FOREIGN KEY (`groupId`) REFERENCES `supplierGroups` (`groupId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplierSendingTimesLinkToSupplierProductsProductId` FOREIGN KEY (`productId`) REFERENCES `products` (`productId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `checkJsonStringIsValidJSON` CHECK (json_valid(`jsonRuleString`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `supplierSendingTimes` */

insert  into `supplierSendingTimes`(`groupId`,`campaignId`,`productId`,`jsonRuleString`) values 
(0,1,0,'{\"default\":{\"start\":\"00:00:00\",\"end\":\"23:59:50\"}}'),
(0,2,0,'{\"default\":{\"start\":\"00:00:00\",\"end\":\"23:59:59\"}}'),
(1,0,0,'{\"singleDay\":{\"Sunday\":{\"start\":\"00:00:00\",\"end\":\"00:00:00\"},\"Monday\":{\"start\":\"10:00:00\",\"end\":\"20:00:00\"},\"Tuesday\":{\"start\":\"10:00:00\",\"end\":\"20:00:00\"},\"Wednesday\":{\"start\":\"10:00:00\",\"end\":\"20:00:00\"},\"Thursday\":{\"start\":\"10:00:00\",\"end\":\"20:00:00\"},\"Friday\":{\"start\":\"10:00:00\",\"end\":\"20:00:00\"},\"Saturday\":{\"start\":\"00:00:00\",\"end\":\"00:00:00\"}}}');

/* Trigger structure for table `supplierCampaigns` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `supplierCampaignsBeforeInsertCheckActiveStatus` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'%' */ /*!50003 TRIGGER `supplierCampaignsBeforeInsertCheckActiveStatus` BEFORE INSERT ON `supplierCampaigns` FOR EACH ROW BEGIN
	IF NEW.`websiteCode` IS NULL AND (NEW.`webserviceTestApiKey` IS NULL OR NEW.`webserviceLiveApiKey` IS NULL) THEN
		SET NEW.`active` = "No";
	END IF;
    END */$$


DELIMITER ;

/* Trigger structure for table `supplierCampaigns` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `supplierCampaignsBeforeUpdateCheckActiveStatus` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'%' */ /*!50003 TRIGGER `supplierCampaignsBeforeUpdateCheckActiveStatus` BEFORE UPDATE ON `supplierCampaigns` FOR EACH ROW BEGIN
	IF NEW.`websiteCode` IS NULL AND (NEW.`webserviceTestApiKey` IS NULL OR NEW.`webserviceLiveApiKey` IS NULL) THEN
		SET NEW.`active` = "No";
	END IF;
    END */$$


DELIMITER ;

/* Function  structure for function  `workOutStartOrEndTimeFromJSON` */

/*!50003 DROP FUNCTION IF EXISTS `workOutStartOrEndTimeFromJSON` */;
DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` FUNCTION `workOutStartOrEndTimeFromJSON`(`jsonString` TEXT, `startTimeOrEndTime` ENUM("StartTime", "EndTime")) RETURNS time
    DETERMINISTIC
BEGIN
	DECLARE currentTry TIME;
	DECLARE timeField VARCHAR(5);

	IF NOT JSON_VALID(`jsonString`) THEN
		RETURN NULL;
	END IF;
	CASE
		WHEN `startTimeOrEndTime` = "StartTime" THEN
			SET `timeField` = "start";
		WHEN `startTimeOrEndTime` = "EndTime" THEN
			SET `timeField` = "end";
		ELSE
			RETURN NULL;
	END CASE;
		
	
	# First Check for the exact date
	SELECT TIME(JSON_VALUE(JSON_EXTRACT(`jsonString`, CONCAT("$.singleDates.\"", CURRENT_DATE, "\"")), CONCAT("$.", `timeField`))) INTO `currentTry`;
	IF `currentTry` IS NOT NULL THEN
		RETURN `currentTry`;
	END IF;
	# Then check for a date in the date range
	/* To Implement
	IF `currentTry` IS NOT NULL THEN
		RETURN `currentTry`;
	END IF;*/
	# Then check for a particular day
	SELECT TIME(JSON_VALUE(JSON_EXTRACT(`jsonString`, CONCAT("$.singleDay.\"", DAYNAME(CURRENT_DATE), "\"")), CONCAT("$.", `timeField`))) INTO `currentTry`;
	IF `currentTry` IS NOT NULL THEN
		RETURN `currentTry`;
	END IF;
	# Then check for a day range
	/* To Implement
	IF `currentTry` IS NOT NULL THEN
		RETURN `currentTry`;
	END IF;*/
	# Then check for a default
	SELECT TIME(JSON_VALUE(JSON_EXTRACT(`jsonString`, "$.default"), CONCAT("$.", `timeField`))) INTO `currentTry`;
	IF `currentTry` IS NOT NULL THEN
		RETURN `currentTry`;
	END IF;
	
	RETURN NULL;
    END */$$
DELIMITER ;

