
CREATE TABLE `was_hub_affiliate_category` (
  `affiliate_category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) unsigned NOT NULL,
  `category_name` varchar(64) NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_category_id`),
  KEY `idx_business_id` (`business_id`)
) ENGINE=InnoDB COMMENT='stores Hub Affiliate categories';

CREATE TRIGGER `was_hub_affiliate_category_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_category`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


CREATE TABLE `was_hub_affiliate_program` (
  `affiliate_program_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `affiliate_category_id` int(11) unsigned NOT NULL,
  `program_name` varchar(64) NOT NULL,
  `program_domain` varchar(128) NOT NULL,
  `program_commission_rate` varchar(64) NOT NULL,
  `program_avg_commission_estimate` varchar(64) NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_program_id`),
  KEY `idx_affiliate_category_id` (`affiliate_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='stores Hub Affiliate programs';

CREATE TRIGGER `was_hub_affiliate_program_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_program`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


CREATE TABLE `was_hub_affiliate_program_link` (
  `affiliate_program_link_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `affiliate_program_id` int(11) unsigned NOT NULL,
  `program_link_url` varchar(256) NOT NULL,
  `program_link_name` varchar(64) NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_program_link_id`),
  KEY `idx_affiliate_program_id` (`affiliate_program_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='stores Hub Affiliate program links';

CREATE TRIGGER `was_hub_affiliate_program_link_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_program_link`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


CREATE TABLE `was_hub_affiliate_program_favorite` (
  `affiliate_program_favorite_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `affiliate_program_id` int(11) unsigned NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_program_favorite_id`),
  KEY `idx_affiliate_program_id` (`affiliate_program_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='stores Favorite Affiliate programs';

CREATE TRIGGER `was_hub_affiliate_program_favorite_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_program_favorite`
FOR EACH ROW SET NEW.insert_timestamp = NOW();


CREATE TABLE `was_hub_affiliate_program_network` (
  `affiliate_program_network_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `network_name` varchar(128) NOT NULL,
  `network_url_name` varchar(128) NOT NULL,
  `network_url` varchar(128) NOT NULL,
  `priority` tinyint(2) unsigned NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_program_network_id`),
  KEY `idx_priority` (`priority`)
) ENGINE=InnoDB COMMENT='stores Hub Affiliate program network';

CREATE TRIGGER `was_hub_affiliate_program_network_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_program_network`
FOR EACH ROW SET NEW.insert_timestamp = NOW();

INSERT INTO `was_hub_affiliate_program_network` (`network_name`, `network_url_name`, `network_url`, `priority`)
VALUES
  ('Awin', 'awin', 'awin.com', '1'),
  ('ShareASale', 'shareasale', 'shareasale.com', '2'),
  ('Impact', 'impact', 'impact.com', '3'),
  ('CJ', 'cj', 'cj.com', '4'),
  ('Rakuten', 'rakuten', 'rakuten.com', '5'),
  ('Clickbank', 'clickbank', 'clickbank.com', '6'),
  ('Digistore24', 'digistore24', 'digistore24.com', '7');


CREATE TABLE `was_hub_affiliate_program_affiliate_url` (
  `affiliate_program_affiliate_url_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `program_domain` varchar(128) NOT NULL,
  `program_affiliate_url` text NOT NULL,
  `affiliate_url_type` varchar(128) NOT NULL,
  `created_date` int(11) unsigned NOT NULL,
  `insert_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`affiliate_program_affiliate_url_id`),
  KEY `idx_program_domain` (`program_domain`)
) ENGINE=InnoDB COMMENT='stores Hub Affiliate program urls';

CREATE TRIGGER `was_hub_affiliate_program_affiliate_url_insert_timestamp` BEFORE INSERT ON `was_hub_affiliate_program_affiliate_url`
FOR EACH ROW SET NEW.insert_timestamp = NOW();
