-- Create missing categories (category_id 42 & 43)
INSERT INTO was_event_category(category_name) 
VALUES
  ('Outsourcing'),
  ('Creating Digital Products'),
  ('TikTok'),
  ('Freelancing'),
  ('CPA Marketing');

-- Update was_event to set all drafts date time to 2022-01-01 17:00:00
UPDATE was_event
JOIN wap_r_resource ON wap_r_resource.resource_id = was_event.event_type_id
SET was_event.event_start_date = UNIX_TIMESTAMP('2022-01-01 17:00:00')
WHERE wap_r_resource.published = 0 AND wap_r_resource.disabled = 0 AND was_event.event_status != 'DISABLED';

-- Update was_event.event_start_date as per xlsx list
UPDATE was_event
SET event_start_date = CASE WHEN was_event.event_id = 638 THEN UNIX_TIMESTAMP('2020-09-01 17:00:00')
                            WHEN was_event.event_id = 640 THEN UNIX_TIMESTAMP('2020-09-01 17:00:00')
                            WHEN was_event.event_id = 639 THEN UNIX_TIMESTAMP('2020-09-08 17:00:00')
                            WHEN was_event.event_id = 641 THEN UNIX_TIMESTAMP('2020-09-15 17:00:00')
                            WHEN was_event.event_id = 642 THEN UNIX_TIMESTAMP('2020-09-22 17:00:00')
                            WHEN was_event.event_id = 643 THEN UNIX_TIMESTAMP('2020-09-29 17:00:00')
                            WHEN was_event.event_id = 645 THEN UNIX_TIMESTAMP('2020-12-29 17:00:00')
                            WHEN was_event.event_id = 646 THEN UNIX_TIMESTAMP('2020-12-29 17:00:00')
                            WHEN was_event.event_id = 647 THEN UNIX_TIMESTAMP('2021-01-05 17:00:00')
                            WHEN was_event.event_id = 648 THEN UNIX_TIMESTAMP('2021-01-12 17:00:00')
                            WHEN was_event.event_id = 649 THEN UNIX_TIMESTAMP('2021-01-19 17:00:00')
                            WHEN was_event.event_id = 650 THEN UNIX_TIMESTAMP('2021-01-26 17:00:00')
                            WHEN was_event.event_id = 657 THEN UNIX_TIMESTAMP('2020-07-10 0:00:00')
                            WHEN was_event.event_id = 658 THEN UNIX_TIMESTAMP('2020-11-03 17:00:00')
                            WHEN was_event.event_id = 659 THEN UNIX_TIMESTAMP('2020-11-10 17:00:00')
                            WHEN was_event.event_id = 660 THEN UNIX_TIMESTAMP('2020-11-17 17:00:00')
                            WHEN was_event.event_id = 661 THEN UNIX_TIMESTAMP('2020-11-24 17:00:00')
                            WHEN was_event.event_id = 662 THEN UNIX_TIMESTAMP('2020-12-01 17:00:00')
                            WHEN was_event.event_id = 670 THEN UNIX_TIMESTAMP('2020-07-10 3:00:00')
                            WHEN was_event.event_id = 671 THEN UNIX_TIMESTAMP('2020-10-06 17:00:00')
                            WHEN was_event.event_id = 672 THEN UNIX_TIMESTAMP('2020-10-13 17:00:00')
                            WHEN was_event.event_id = 673 THEN UNIX_TIMESTAMP('2020-10-20 17:00:00')
                            WHEN was_event.event_id = 674 THEN UNIX_TIMESTAMP('2020-10-27 17:00:00')
                            WHEN was_event.event_id = 696 THEN UNIX_TIMESTAMP('2020-11-26 17:00:00')
                            WHEN was_event.event_id = 699 THEN UNIX_TIMESTAMP('2020-12-03 17:00:00')
                            WHEN was_event.event_id = 697 THEN UNIX_TIMESTAMP('2020-12-10 17:00:00')
                            WHEN was_event.event_id = 700 THEN UNIX_TIMESTAMP('2020-10-22 17:00:00')
                            WHEN was_event.event_id = 691 THEN UNIX_TIMESTAMP('2020-12-17 17:00:00')
                            WHEN was_event.event_id = 677 THEN UNIX_TIMESTAMP('2020-07-16 0:00:00')
                            WHEN was_event.event_id = 678 THEN UNIX_TIMESTAMP('2020-10-01 17:00:00')
                            WHEN was_event.event_id = 679 THEN UNIX_TIMESTAMP('2020-10-08 17:00:00')
                            WHEN was_event.event_id = 680 THEN UNIX_TIMESTAMP('2020-10-15 17:00:00')
                            WHEN was_event.event_id = 590 THEN UNIX_TIMESTAMP('2020-06-30 5:00:00')
                            WHEN was_event.event_id = 595 THEN UNIX_TIMESTAMP('2020-10-05 17:00:00')
                            WHEN was_event.event_id = 591 THEN UNIX_TIMESTAMP('2020-10-12 17:00:00')
                            WHEN was_event.event_id = 593 THEN UNIX_TIMESTAMP('2020-10-19 17:00:00')
                            WHEN was_event.event_id = 592 THEN UNIX_TIMESTAMP('2020-10-26 17:00:00')
                            WHEN was_event.event_id = 594 THEN UNIX_TIMESTAMP('2020-11-02 17:00:00')
                            WHEN was_event.event_id = 596 THEN UNIX_TIMESTAMP('2020-06-30 18:00:00')
                            WHEN was_event.event_id = 597 THEN UNIX_TIMESTAMP('2020-12-08 17:00:00')
                            WHEN was_event.event_id = 598 THEN UNIX_TIMESTAMP('2020-12-15 17:00:00')
                            WHEN was_event.event_id = 599 THEN UNIX_TIMESTAMP('2020-12-22 17:00:00')
                            WHEN was_event.event_id = 561 THEN UNIX_TIMESTAMP('2020-06-17 23:00:00')
                            WHEN was_event.event_id = 562 THEN UNIX_TIMESTAMP('2020-11-09 17:00:00')
                            WHEN was_event.event_id = 563 THEN UNIX_TIMESTAMP('2020-11-16 17:00:00')
                            WHEN was_event.event_id = 564 THEN UNIX_TIMESTAMP('2020-11-23 17:00:00')
                            WHEN was_event.event_id = 565 THEN UNIX_TIMESTAMP('2020-11-30 17:00:00')
                            WHEN was_event.event_id = 566 THEN UNIX_TIMESTAMP('2020-12-07 17:00:00')
                            WHEN was_event.event_id = 567 THEN UNIX_TIMESTAMP('2020-06-18 0:00:00')
                            WHEN was_event.event_id = 568 THEN UNIX_TIMESTAMP('2020-09-07 17:00:00')
                            WHEN was_event.event_id = 569 THEN UNIX_TIMESTAMP('2020-09-14 17:00:00')
                            WHEN was_event.event_id = 570 THEN UNIX_TIMESTAMP('2020-09-21 17:00:00')
                            WHEN was_event.event_id = 571 THEN UNIX_TIMESTAMP('2020-09-28 17:00:00')
                            WHEN was_event.event_id = 582 THEN UNIX_TIMESTAMP('2020-06-18 0:00:00')
                            WHEN was_event.event_id = 583 THEN UNIX_TIMESTAMP('2020-12-29 17:00:00')
                            WHEN was_event.event_id = 584 THEN UNIX_TIMESTAMP('2021-01-04 17:00:00')
                            WHEN was_event.event_id = 585 THEN UNIX_TIMESTAMP('2021-01-11 17:00:00')
                            WHEN was_event.event_id = 586 THEN UNIX_TIMESTAMP('2021-01-18 17:00:00')
                            WHEN was_event.event_id = 512 THEN UNIX_TIMESTAMP('2020-06-17 2:00:00')
                            WHEN was_event.event_id = 513 THEN UNIX_TIMESTAMP('2020-09-03 17:00:00')
                            WHEN was_event.event_id = 514 THEN UNIX_TIMESTAMP('2020-09-10 17:00:00')
                            WHEN was_event.event_id = 515 THEN UNIX_TIMESTAMP('2020-09-17 17:00:00')
                            WHEN was_event.event_id = 516 THEN UNIX_TIMESTAMP('2020-09-24 17:00:00')
                            WHEN was_event.event_id = 543 THEN UNIX_TIMESTAMP('2020-06-14 20:00:00')
                            WHEN was_event.event_id = 544 THEN UNIX_TIMESTAMP('2020-10-29 17:00:00')
                            WHEN was_event.event_id = 545 THEN UNIX_TIMESTAMP('2020-11-05 17:00:00')
                            WHEN was_event.event_id = 546 THEN UNIX_TIMESTAMP('2020-11-12 17:00:00')
                            WHEN was_event.event_id = 547 THEN UNIX_TIMESTAMP('2020-11-17 17:00:00')
                            WHEN was_event.event_id = 538 THEN UNIX_TIMESTAMP('2020-12-07 17:00:00')
                            WHEN was_event.event_id = 537 THEN UNIX_TIMESTAMP('2020-12-07 17:00:00')
                            WHEN was_event.event_id = 539 THEN UNIX_TIMESTAMP('2021-12-14 17:00:00')
                            WHEN was_event.event_id = 540 THEN UNIX_TIMESTAMP('2020-12-21 17:00:00')
                            WHEN was_event.event_id = 541 THEN UNIX_TIMESTAMP('2020-12-28 17:00:00')
                            WHEN was_event.event_id = 527 THEN UNIX_TIMESTAMP('2020-06-16 1:00:00')
                            WHEN was_event.event_id = 528 THEN UNIX_TIMESTAMP('2020-10-14 17:00:00')
                            WHEN was_event.event_id = 529 THEN UNIX_TIMESTAMP('2020-10-21 17:00:00')
                            WHEN was_event.event_id = 530 THEN UNIX_TIMESTAMP('2020-10-28 17:00:00')
                            WHEN was_event.event_id = 531 THEN UNIX_TIMESTAMP('2020-11-04 17:00:00')
                            WHEN was_event.event_id = 624 THEN UNIX_TIMESTAMP('2020-07-07 22:00:00')
                            WHEN was_event.event_id = 625 THEN UNIX_TIMESTAMP('2020-11-18 17:00:00')
                            WHEN was_event.event_id = 626 THEN UNIX_TIMESTAMP('2020-11-25 17:00:00')
                            WHEN was_event.event_id = 627 THEN UNIX_TIMESTAMP('2020-12-02 17:00:00')
                            WHEN was_event.event_id = 628 THEN UNIX_TIMESTAMP('2020-12-09 17:00:00')
                            WHEN was_event.event_id = 629 THEN UNIX_TIMESTAMP('2020-12-16 17:00:00')
                            WHEN was_event.event_id = 631 THEN UNIX_TIMESTAMP('2020-12-23 17:00:00')
                            WHEN was_event.event_id = 630 THEN UNIX_TIMESTAMP('2020-12-30 17:00:00')
                            WHEN was_event.event_id = 632 THEN UNIX_TIMESTAMP('2021-01-05 17:00:00')
                            WHEN was_event.event_id = 617 THEN UNIX_TIMESTAMP('2020-07-22 3:00:00')
                            WHEN was_event.event_id = 618 THEN UNIX_TIMESTAMP('2020-09-02 17:00:00')
                            WHEN was_event.event_id = 622 THEN UNIX_TIMESTAMP('2020-09-09 17:00:00')
                            WHEN was_event.event_id = 623 THEN UNIX_TIMESTAMP('2020-09-16 17:00:00')
                            WHEN was_event.event_id = 621 THEN UNIX_TIMESTAMP('2020-09-23 17:00:00')
                            WHEN was_event.event_id = 619 THEN UNIX_TIMESTAMP('2020-09-30 17:00:00')
                            WHEN was_event.event_id = 620 THEN UNIX_TIMESTAMP('2020-10-07 17:00:00')
                        END
WHERE was_event.event_id IN (638, 640, 639, 641, 642, 643, 645, 646, 647, 648, 649, 650, 657, 658, 659, 660, 661, 662, 670, 671, 672, 673, 674, 696, 699, 697, 700, 691, 677, 678, 679, 680, 590, 595, 591, 593, 592, 594, 596, 597, 598, 599, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 582, 583, 584, 585, 586, 512, 513, 514, 515, 516, 543, 544, 545, 546, 547, 538, 537, 539, 540, 541, 527, 528, 529, 530, 531, 624, 625, 626, 627, 628, 629, 631, 630, 632, 617, 618, 622, 623, 621, 619, 620);

-- Clear event category relation for provided events (was_event_category_relation)
DELETE from was_event_category_relation WHERE event_id IN (638, 640, 639, 641, 642, 643, 645, 646, 647, 648, 649, 650, 657, 658, 659, 660, 661, 662, 670, 671, 672, 673, 674, 696, 699, 697, 700, 691, 677, 678, 679, 680, 590, 595, 591, 593, 592, 594, 596, 597, 598, 599, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 582, 583, 584, 585, 586, 512, 513, 514, 515, 516, 543, 544, 545, 546, 547, 538, 537, 539, 540, 541, 527, 528, 529, 530, 531, 624, 625, 626, 627, 628, 629, 631, 630, 632, 617, 618, 622, 623, 621, 619, 620);

-- Insert into was_event_category_relation
INSERT INTO `was_event_category_relation` (`event_id`, `category_id`) VALUES
('638', 27),
('638', 28),
('640', 27),
('640', 28),
('639', 27),
('639', 28),
('641', 2),
('641', 27),
('641', 28),
('641', 16),
('641', 34),
('642', 27),
('642', 28),
('642', 12),
('643', 27),
('643', 28),
('645', 2),
('645', 16),
('645', 34),
('646', 2),
('646', 16),
('646', 34),
('647', 2),
('647', 34),
('648', 2),
('648', 16),
('648', 27),
('648', 34),
('649', 2),
('649', 16),
('649', 34),
('650', 2),
('650', 16),
('650', 34),
('657', 40),
('658', 40),
('659', 40),
('660', 27),
('660', 28),
('660', 40),
('661', 40),
('662', 40),
('670', 2),
('670', 16),
('670', 34),
('671', 2),
('671', 16),
('671', 34),
('672', 2),
('672', 16),
('672', 34),
('673', 2),
('673', 16),
('673', 34),
('674', 2),
('674', 16),
('674', 34),
('696', 27),
('696', 28),
('699', 27),
('699', 21),
('699', 12),
('697', 40),
('700', 40),
('691', 13),
('691', 39),
('677', 12),
('678', 5),
('678', 12),
('679', 27),
('679', 28),
('679', 45),
('680', 2),
('680', 16),
('680', 34),
('590', 10),
('595', 3),
('595', 10),
('591', 10),
('593', 10),
('592', 10),
('594', 10),
('596', 14),
('596', 31),
('597', 14),
('597', 31),
('598', 3),
('598', 14),
('598', 31),
('599', 14),
('599', 31),
('561', 27),
('561', 28),
('562', 27),
('562', 28),
('562', 30),
('563', 27),
('563', 28),
('563', 30),
('564', 27),
('564', 28),
('564', 30),
('565', 27),
('565', 28),
('565', 30),
('566', 27),
('566', 28),
('567', 8),
('567', 9),
('567', 27),
('567', 28),
('568', 8),
('568', 9),
('569', 1),
('569', 8),
('569', 9),
('570', 27),
('570', 28),
('571', 8),
('571', 27),
('571', 28),
('582', 32),
('582', 37),
('583', 32),
('583', 37),
('584', 32),
('584', 37),
('585', 32),
('585', 37),
('586', 32),
('586', 37),
('512', 42),
('513', 42),
('514', 42),
('515', 42),
('516', 42),
('543', 29),
('543', 35),
('543', 40),
('544', 29),
('544', 40),
('544', 34),
('544', 35),
('545', 29),
('545', 35),
('546', 29),
('546', 35),
('546', 12),
('547', 29),
('547', 35),
('547', 12),
('538', 19),
('538', 20),
('537', 19),
('537', 20),
('539', 19),
('539', 20),
('540', 19),
('540', 20),
('541', 19),
('541', 20),
('527', 1),
('527', 39),
('527', 44),
('528', 1),
('528', 39),
('528', 44),
('529', 1),
('529', 39),
('529', 44),
('530', 1),
('530', 39),
('530', 44),
('531', 1),
('531', 39),
('531', 44),
('624', 13),
('624', 39),
('625', 13),
('625', 39),
('625', 12),
('626', 13),
('626', 39),
('626', 3),
('627', 13),
('627', 39),
('628', 13),
('628', 39),
('628', 29),
('628', 1),
('629', 13),
('629', 39),
('631', 13),
('631', 39),
('631', 30),
('630', 13),
('630', 39),
('630', 43),
('632', 13),
('632', 39),
('617', 13),
('617', 39),
('618', 13),
('618', 39),
('622', 13),
('622', 39),
('623', 13),
('623', 39),
('621', 13),
('621', 39),
('619', 13),
('619', 39),
('620', 13),
('620', 39);

-- Update counter for events categories
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;
