CREATE TABLE `was_event_revenue_stats_day` (
  `event_revenue_stats_day_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
  `stats_day` date NOT NULL,
  `billing` enum('Monthly','Yearly') 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 (`event_revenue_stats_day_id`),
  KEY `event_id` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `was_event_share_revenue_stats_day` (
  `event_share_revenue_stats_day_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `data1` varchar(50) DEFAULT NULL,
  `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
  `billing` enum('Monthly','Yearly') NOT NULL,
  `stats_day` date 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 (`event_share_revenue_stats_day_id`),
  KEY `event_id` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `was_event_stats_hour` (
 `event_stats_hour_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `event_id` int(11) unsigned NOT NULL,
 `stats_day` date NOT NULL DEFAULT '0000-00-00',
 `stats_hour` int(11) unsigned NOT NULL,
 `signup_count` int(11) unsigned NOT NULL DEFAULT '0',
 `upgrade_count` int(11) unsigned NOT NULL DEFAULT '0',
 `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
 `unique_view_count` int(11) unsigned NOT NULL DEFAULT '0',
 `unique_mobile_count` int(11) unsigned NOT NULL DEFAULT '0',
 `unique_desktop_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_view_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_desktop_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_mobile_count` int(11) unsigned NOT NULL DEFAULT '0',
 `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 (`event_stats_hour_id`),
 KEY `idx_event_stats_hour` (`event_id`,`stats_day`,`stats_hour`)
) ENGINE=InnoDB COMMENT='Store hourly class stats data';


CREATE TABLE `was_event_share_stats_hour` (
 `event_share_stats_hour_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `event_id` int(11) unsigned NOT NULL,
 `data1` varchar(50) DEFAULT NULL,
 `stats_day` date NOT NULL DEFAULT '0000-00-00',
 `stats_hour` int(11) unsigned NOT NULL,
 `signup_count` int(11) unsigned NOT NULL DEFAULT '0',
 `upgrade_count` int(11) unsigned NOT NULL DEFAULT '0',
 `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
 `unique_view_count` int(11) unsigned NOT NULL DEFAULT '0',
 `unique_mobile_count` int(11) unsigned NOT NULL DEFAULT '0',
 `unique_desktop_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_view_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_desktop_count` int(11) unsigned NOT NULL DEFAULT '0',
 `raw_mobile_count` int(11) unsigned NOT NULL DEFAULT '0',
 `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 (`event_share_stats_hour_id`),
 KEY `idx_event_share_stats_hour` (`event_id`,`stats_day`,`stats_hour`)
) ENGINE=InnoDB COMMENT='Store hourly class share stats data';


CREATE TABLE `was_event_revenue_stats_hour` (
  `event_revenue_stats_hour_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
  `stats_day` date NOT NULL DEFAULT '0000-00-00',
  `stats_hour` int(11) unsigned NOT NULL,
  `billing` enum('Monthly','Yearly') 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 (`event_revenue_stats_hour_id`),
  KEY `idx_event_stats_revenue_hour` (`event_id`,`stats_day`,`stats_hour`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `was_event_share_revenue_stats_hour` (
  `event_share_revenue_stats_hour_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `data1` varchar(50) DEFAULT NULL,
  `revenue` decimal(9,2) NOT NULL DEFAULT '0.00',
  `billing` enum('Monthly','Yearly') NOT NULL,
  `stats_day` date NOT NULL DEFAULT '0000-00-00',
  `stats_hour` 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 (`event_share_revenue_stats_hour_id`),
  KEY `idx_event_share_stats_revenue_hour` (`event_id`,`stats_day`,`stats_hour`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `wap_r_resource` ADD `status_disabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `disabled`;
ALTER TABLE was_user_skill ADD COLUMN `skill_order` int(11) DEFAULT 0 AFTER skill;
ALTER TABLE `was_user_group` ADD COLUMN `membership_level` enum('STARTER', 'PREMIUM', 'PPLUS') DEFAULT 'STARTER' AFTER `name`;

CREATE TABLE `wax_admin_setting` (
  `admin_setting_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `admin_mode` tinyint(1) NOT NULL DEFAULT 1,
  `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 (`admin_setting_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;

INSERT INTO wax_admin_setting (user_id, admin_mode)
VALUES (1, 1)
, (37, 1)
, (10550, 1)
, (19441, 1);

INSERT INTO `was_event_category` (`category_id`, `category_name`, `insert_timestamp`, `update_timestamp`) VALUES
(NULL, 'Social Media Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'SEO', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Website Development', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'WordPress', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Local Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Amazon', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Hot Seat', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Research', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Keywords', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Niches', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Email Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Conversions', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Youtube', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Bing Ads', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Google Adwords', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Yahoo / Bing SEO', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Google Adsense', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Analytics', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'WA Affiliate Program', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Affiliate Programs', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Traffic', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Facebook', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Instagram', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Pinterest', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Case Study', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Facebook Ads', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Content Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Copywriting', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Branding', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Affiliate Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'PPC', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Design / UX', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Google Search Console', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Google SEO', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'E-Commerce', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Dropshipping', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Graphic Design', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Domain / Website Flipping', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Video Marketing', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'Mindset & Motivation', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP),
(NULL, 'AMA (Ask Me Anything)', '0000-00-00 00:00:00.000000', CURRENT_TIMESTAMP);


INSERT INTO `was_event_category_relation` (`event_id`, `category_id`) VALUES
('472', '1'),
('472', '24'),
('471', '1'),
('471', '24'),
('470', '1'),
('470', '24'),
('469', '1'),
('469', '24'),
('468', '25'),
('467', '25'),
('466', '8'),
('466', '9'),
('466', '25'),
('465', '3'),
('465', '4'),
('465', '25'),
('464', '28'),
('463', '2'),
('463', '3'),
('463', '30'),
('461', '20'),
('461', '30'),
('460', '2'),
('460', '30'),
('460', '31'),
('459', '30'),
('458', '7'),
('457', '12'),
('456', '12'),
('455', '27'),
('455', '28'),
('454', '27'),
('454', '28'),
('453', '5'),
('452', '2'),
('452', '5'),
('451', '5'),
('450', '5'),
('449', '1'),
('449', '23'),
('448', '1'),
('448', '23'),
('447', '1'),
('447', '23'),
('447', '29'),
('446', '1'),
('446', '23'),
('445', '28'),
('444', '27'),
('444', '32'),
('443', '28'),
('443', '29'),
('442', '3'),
('441', '7'),
('440', '4'),
('439', '18'),
('438', '3'),
('438', '4'),
('438', '32'),
('437', '4'),
('436', '2'),
('436', '8'),
('435', '2'),
('435', '8'),
('434', '2'),
('434', '8'),
('434', '9'),
('433', '2'),
('433', '8'),
('432', '1'),
('432', '21'),
('431', '2'),
('431', '14'),
('431', '16'),
('430', '11'),
('429', '32'),
('428', '7'),
('427', '13'),
('426', '13'),
('425', '13'),
('424', '13'),
('423', '2'),
('422', '2'),
('421', '2'),
('421', '9'),
('421', '33'),
('419', '33'),
('418', '12'),
('418', '18'),
('417', '12'),
('417', '18'),
('416', '18'),
('415', '16'),
('414', '30'),
('413', '9'),
('412', '3'),
('411', '29'),
('403', '28'),
('402', '2'),
('402', '28'),
('401', '7'),
('408', '31'),
('408', '35'),
('407', '35'),
('407', '36'),
('406', '35'),
('405', '35'),
('404', '35'),
('400', '32'),
('400', '37'),
('399', '37'),
('398', '29'),
('397', '38'),
('396', '4'),
('396', '32'),
('395', '4'),
('395', '32'),
('394', '4'),
('394', '32'),
('393', '4'),
('393', '32'),
('392', '11'),
('392', '30'),
('391', '2'),
('391', '11'),
('391', '31'),
('390', '11'),
('389', '11'),
('388', '11'),
('387', '37'),
('386', '30'),
('385', '29'),
('384', '7'),
('383', '8'),
('382', '8'),
('382', '10'),
('381', '8'),
('381', '20'),
('380', '8'),
('380', '9'),
('379', '8'),
('379', '10'),
('378', '13'),
('378', '31'),
('377', '13'),
('376', '13'),
('375', '2'),
('375', '13'),
('374', '2'),
('374', '27'),
('373', '2'),
('372', '7'),
('371', '32'),
('370', '10'),
('369', '11'),
('368', '11'),
('367', '11'),
('366', '11'),
('365', '7'),
('364', '27'),
('364', '28'),
('363', '41'),
('362', '33'),
('352', '30'),
('351', '4'),
('351', '35'),
('350', '4'),
('350', '35'),
('349', '30'),
('348', '4'),
('347', '13'),
('346', '12'),
('346', '18'),
('345', '7'),
('344', '39'),
('343', '32'),
('343', '37'),
('339', '20'),
('339', '30'),
('338', '6'),
('338', '20'),
('338', '30'),
('337', '2'),
('336', '28'),
('335', '41'),
('334', '9'),
('334', '28'),
('333', '3'),
('332', '2'),
('331', '7'),
('330', '2'),
('330', '27'),
('330', '28'),
('329', '2'),
('329', '3'),
('329', '4'),
('328', '8'),
('327', '13'),
('326', '2'),
('325', '12'),
('324', '8'),
('324', '10'),
('323', '7'),
('322', '10'),
('322', '25'),
('321', '10'),
('321', '25'),
('320', '10'),
('320', '25'),
('319', '10'),
('319', '25'),
('318', '8'),
('318', '33'),
('317', '8'),
('317', '33'),
('316', '40'),
('315', '2'),
('315', '13'),
('314', '2'),
('314', '13'),
('313', '8'),
('313', '9'),
('312', '2'),
('311', '7'),
('310', '19'),
('309', '11'),
('304', '30'),
('300', '7'),
('299', '1'),
('298', '10'),
('292', '26'),
('292', '31'),
('291', '26'),
('291', '31'),
('290', '26'),
('290', '31'),
('289', '26'),
('289', '31'),
('288', '26'),
('288', '31'),
('287', '4'),
('286', '7'),
('285', '2'),
('284', '2'),
('284', '5'),
('283', '12'),
('282', '1'),
('281', '2'),
('281', '34'),
('280', '2'),
('280', '4'),
('279', '29'),
('278', '27'),
('277', '26'),
('277', '31'),
('276', '26'),
('276', '31'),
('275', '41'),
('272', '29'),
('271', '1'),
('264', '7'),
('263', '28'),
('261', '12'),
('261', '28'),
('259', '10'),
('258', '40'),
('257', '41'),
('255', '7'),
('254', '37'),
('252', '2'),
('251', '28'),
('250', '7'),
('249', '9'),
('248', '41'),
('247', '32'),
('246', '12'),
('245', '2'),
('245', '5'),
('244', '2'),
('242', '28'),
('241', '27'),
('240', '10'),
('239', '10'),
('238', '10'),
('237', '10'),
('236', '10');

UPDATE wap_r_resource r
SET r.disabled = 0, r.status_disabled = 0
WHERE r.resource_id IN (3043, 3044, 3045, 3046, 3047, 3048, 3049, 3050, 3051, 3052, 3053, 3054, 3074, 3076, 3078, 3093, 3097, 3115, 3116, 3117, 3135, 3136, 3137, 3138, 3139, 3140, 3141, 3142, 3143, 3144, 3145, 3146, 3147, 3148, 3149, 3150, 3151, 3152, 3153, 3154, 3155, 3156, 3157, 3158, 3159, 3160, 3161, 3162, 3163, 3164, 3166, 3167, 3168, 3170, 3171, 3172, 3173, 3174, 3175, 3176, 3177, 3178, 3179, 3180, 3181, 3183, 3185, 3186, 3187, 3188, 3189, 3190, 3191, 3192, 3193, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 3202, 3203, 3205, 3225, 3240, 3241, 3242, 3244, 3245, 3260, 3264, 3265, 3266, 3267, 3268, 3269, 3270, 3271, 3272, 3274, 3273, 3275, 3276, 3277, 3278, 3279, 3280, 3281, 3282, 3283, 3284, 3286, 3287, 3288, 3289, 3290, 3291, 3292, 3293, 3294, 3295, 3296, 3297, 3298, 3299, 3300, 3301, 3302, 3303, 3304, 3305, 3306, 3307, 3308, 3309, 3310, 3311, 3312, 3313, 3314, 3315, 3316, 3342, 3387, 3395, 3397, 3401, 3402, 3403, 3404, 3405, 3408, 3412, 3413, 3438, 3439, 3440, 3441, 3442, 3446, 3447, 3448, 3451, 3452, 3453, 3613, 3614, 3615, 3646, 3684, 3689, 3691, 3692, 3693, 3694, 3695, 4076);

ALTER TABLE `wap_r_resource` ADD `rating_avg` DECIMAL(3,1) NOT NULL DEFAULT '00.0' AFTER `last_comment_date`;
ALTER TABLE `wap_r_resource` ADD `rating_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `rating_avg`;

ALTER TABLE `was_event` ADD `participant_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `est_duration`;

ALTER TABLE `was_event_category` ADD `event_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `category_name`;


UPDATE wap_r_resource as r1
JOIN (
  SELECT wr.type_id, wr.type, COUNT(wr.review_id) as rating_count, AVG(wr.rating) as avg_rating
  FROM wa_review wr
  GROUP BY wr.type_id
) x ON x.type_id = r1.resource_id AND x.type = 'RESOURCE'
SET r1.rating_avg = x.avg_rating, r1.rating_count = x.rating_count

UPDATE was_event as e
JOIN (
  SELECT ep.event_id, COUNT(DISTINCT(ep.user_id)) as total_count
  FROM was_event_participant ep
  GROUP BY ep.event_id
) x ON x.event_id = e.event_id
SET e.participant_count = x.total_count;


UPDATE was_event_category as c
JOIN (
  SELECT ecr.category_id, COUNT(DISTINCT(ecr.event_id)) as total_count
  FROM was_event_category_relation ecr
  GROUP BY ecr.category_id
) x ON x.category_id = c.category_id
SET c.event_count = x.total_count;

UPDATE wap_r_resource as r1,
(
  SELECT r.resource_id, v.wistia_thumbnail
  FROM wap_r_resource r
  JOIN was_wistia_video v ON v.wistia_video_id = r.wistia_video_id
  WHERE r.type_id = 5
  AND r.video_start_image = ''
) as r2
SET r1.video_start_image = r2.wistia_thumbnail
WHERE r1.resource_id = r2.resource_id;



CREATE TRIGGER `was_event_revenue_stats_day_insert_timestamp` BEFORE INSERT ON `was_event_revenue_stats_day` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_event_share_revenue_stats_day_insert_timestamp` BEFORE INSERT ON `was_event_share_revenue_stats_day` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_event_stats_hour_insert_timestamp` BEFORE INSERT ON `was_event_stats_hour` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_event_share_stats_hour_insert_timestamp` BEFORE INSERT ON `was_event_share_stats_hour` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_event_revenue_stats_hour_insert_timestamp` BEFORE INSERT ON `was_event_revenue_stats_hour` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `was_event_share_revenue_stats_hour_insert_timestamp` BEFORE INSERT ON `was_event_share_revenue_stats_hour` FOR EACH ROW SET NEW.insert_timestamp = NOW();
CREATE TRIGGER `wax_admin_setting_insert_timestamp` BEFORE INSERT ON `wax_admin_setting` FOR EACH ROW SET NEW.insert_timestamp = NOW();
