# One-time updates;
# ALTER TABLE mm_Event ADD COLUMN eventId VARCHAR (12);
# ALTER TABLE mm_Event ADD UNIQUE KEY ( `eventId` );

DROP FUNCTION IF EXISTS generateAlias;

DELIMITER  |

CREATE FUNCTION generateAlias( str VARCHAR(255) ) RETURNS VARCHAR(255)
DETERMINISTIC
  BEGIN
    DECLARE i, LENGTH SMALLINT DEFAULT 1;
    DECLARE isSpace SMALLINT DEFAULT 0;
    DECLARE alias VARCHAR(255) DEFAULT '';
    DECLARE currentCharacter VARCHAR(1);
    SET LENGTH = CHAR_LENGTH( str );
    REPEAT
      BEGIN
        SET currentCharacter = MID( str, i, 1 );
        IF currentCharacter NOT REGEXP '[[:alnum:]]' THEN
          IF currentCharacter != '&' THEN
            SET isSpace = 1;
          ELSE
            SET isSpace = 0;
            SET alias = CONCAT(alias,'-and');
          END IF;
        ELSE
          IF isSpace = 1 THEN
            SET isSpace = 0;
            SET alias = CONCAT(alias,'-');
          END IF;
          SET alias = CONCAT(alias,currentCharacter);
        END IF;
        SET i = i + 1;
      END;
    UNTIL i > LENGTH END REPEAT;
    RETURN alias;
  END |
DELIMITER ;

# Create temporary tables for import and load data from *.csv files

DROP TABLE IF EXISTS `importEventSingleAdditional`;
CREATE TABLE IF NOT EXISTS `importEventSingleAdditional` (
  `eventId` varchar(12) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(10) DEFAULT NULL,
  `subType` varchar(20) DEFAULT NULL,
  `startDate` varchar(10) DEFAULT NULL,
  `endDate` varchar(10) DEFAULT NULL,
  `startTime` varchar(8) DEFAULT NULL,
  `endTime` varchar(8) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `memberPrice` decimal(5,2) DEFAULT NULL,
  `nonMemberPrice` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/eventsSingleDay.csv'
#LOAD DATA INFILE '/home/roo/public_html/mmua.org/trunk/db/eventsSingleDay.csv'
  INTO TABLE importEventSingleAdditional
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;
#
# DROP TABLE IF EXISTS `importEventMultiple`;
# CREATE TABLE IF NOT EXISTS `importEventMultiple` (
#   `eventId` varchar(12) DEFAULT NULL,
#   `title` varchar(72) DEFAULT NULL,
#   `type` varchar(10) DEFAULT NULL,
#   `subType` varchar(20) DEFAULT NULL,
#   `startDate` varchar(10) DEFAULT NULL,
#   `endDate` varchar(10) DEFAULT NULL,
#   `startTime1` varchar(8) DEFAULT NULL,
#   `endTime1` varchar(8) DEFAULT NULL,
#   `startTime2` varchar(8) DEFAULT NULL,
#   `endTime2` varchar(8) DEFAULT NULL,
#   `startTime3` varchar(8) DEFAULT NULL,
#   `endTime3` varchar(8) DEFAULT NULL,
#   `startTime4` varchar(8) DEFAULT NULL,
#   `endTime4` varchar(8) DEFAULT NULL,
#   `location` varchar(100) DEFAULT NULL,
#   `memberPrice` decimal(5,2) DEFAULT NULL,
#   `nonMemberPrice` decimal(6,2) DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# LOAD DATA INFILE '/home/mmua/import/eventsMultipleDays.csv'
# #LOAD DATA INFILE '/home/roo/public_html/mmua.org/trunk/db/eventsMultipleDays.csv'
#   INTO TABLE importEventMultiple
#   FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
#   LINES TERMINATED BY '\n' STARTING BY ''
#   IGNORE 1 LINES;
#
DROP TABLE IF EXISTS `importEventAttendees`;
CREATE TABLE IF NOT EXISTS `importEventAttendees` (
  `eventId` varchar(12) DEFAULT NULL,
  `individualId` varchar(12) DEFAULT NULL,
  `attendeeStatus` varchar(9) DEFAULT NULL,
  `certificate` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/eventsAttendees.csv'
#LOAD DATA INFILE '/home/roo/public_html/mmua.org/trunk/db/eventsAttendees.csv'
  INTO TABLE importEventAttendees
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;
#
ALTER TABLE `importEventAttendees` ADD INDEX(`individualId`);
ALTER TABLE `importEventAttendees` ADD INDEX(`eventId`);
#
DELETE FROM mm_Event WHERE eventId IN (SELECT eventId FROM importEventSingleAdditional);
#
# #should be calculated manually
# ALTER TABLE `mm_Event` auto_increment = 105;
#
INSERT INTO mm_Event (type, subtype, alias, durationType, title, startDate, endDate, registrationStart,
  registrationEnd, location, memberPrice, nonmemberPrice, enabled, added, changed, eventId)
SELECT LOWER(type), est.id, CONCAT(generateAlias(ies.title), eventId), 'singleDay', ies.title,
  STR_TO_DATE(startDate, '%m/%d/%Y'), STR_TO_DATE(endDate, '%m/%d/%Y'),
  STR_TO_DATE(startDate, '%m/%d/%Y') - INTERVAL 1 DAY, STR_TO_DATE(startDate, '%m/%d/%Y') - INTERVAL 1 HOUR,
  location, memberPrice, nonMemberPrice, 'y', NOW(),
  NOW(), eventId
FROM importEventSingleAdditional ies
  LEFT JOIN mm_EventSubtype est ON ies.subType = est.title;
#
INSERT INTO mm_EventTime (eventId, date, startTime, endTime)
SELECT e.id, e.startDate, IF(startTime = '', '13:00:00', STR_TO_DATE(startTime, "%l:%i %p")),
  IF(endTime = '', '14:00:00', STR_TO_DATE(endTime, "%l:%i %p"))
  FROM importEventSingleAdditional ies JOIN mm_Event e ON ies.eventId = e.eventId;
#
# INSERT INTO mm_Event (type, subtype, alias, durationType, title, startDate, endDate, registrationStart,
#                       registrationEnd, location, memberPrice, nonmemberPrice, enabled, added, changed, eventId)
#   SELECT LOWER(type), est.id, CONCAT(generateAlias(iem.title), eventId), 'multipleDays', iem.title,
#     STR_TO_DATE(startDate, '%m/%d/%Y'), STR_TO_DATE(endDate, '%m/%d/%Y'),
#     STR_TO_DATE(startDate, '%m/%d/%Y') - INTERVAL 1 DAY, STR_TO_DATE(startDate, '%m/%d/%Y') - INTERVAL 1 HOUR,
#     location, memberPrice, nonMemberPrice, 'y', NOW(),
#     NOW(), eventId
#   FROM importEventMultiple iem
#     LEFT JOIN mm_EventSubtype est ON iem.subType = est.title;;
#
# INSERT INTO mm_EventTime (eventId, date, startTime, endTime)
#   SELECT e.id, e.startDate, IF(startTime1 = '', '13:00:00', STR_TO_DATE(startTime1, "%l:%i %p")),
#     IF(endTime1 = '', '14:00:00', STR_TO_DATE(endTime2, "%l:%i %p"))
#   FROM importEventMultiple iem JOIN mm_Event e ON iem.eventId = e.eventId;
#
# INSERT INTO mm_EventTime (eventId, date, startTime, endTime)
#   SELECT e.id, e.startDate + INTERVAL 1 DAY, IF(startTime2 = '', '13:00:00', STR_TO_DATE(startTime2, "%l:%i %p")),
#     IF(endTime2 = '', '14:00:00', STR_TO_DATE(endTime2, "%l:%i %p"))
#   FROM importEventMultiple iem JOIN mm_Event e ON iem.eventId = e.eventId
#   WHERE e.startDate + INTERVAL 1 DAY  <= e.endDate;
#
# INSERT INTO mm_EventTime (eventId, date, startTime, endTime)
#   SELECT e.id, e.startDate + INTERVAL 2 DAY, IF(startTime3 = '', '13:00:00', STR_TO_DATE(startTime3, "%l:%i %p")),
#     IF(endTime3 = '', '14:00:00', STR_TO_DATE(endTime3, "%l:%i %p"))
#   FROM importEventMultiple iem JOIN mm_Event e ON iem.eventId = e.eventId
#   WHERE e.startDate + INTERVAL 2 DAY <= e.endDate;
#
# INSERT INTO mm_EventTime (eventId, date, startTime, endTime)
#   SELECT e.id, e.startDate + INTERVAL 3 DAY, IF(startTime4 = '', '13:00:00', STR_TO_DATE(startTime4, "%l:%i %p")),
#     IF(endTime4 = '', '14:00:00', STR_TO_DATE(endTime4, "%l:%i %p"))
#   FROM importEventMultiple iem JOIN mm_Event e ON iem.eventId = e.eventId
#   WHERE e.startDate + INTERVAL 3 DAY <= e.endDate;
#
SELECT iea.individualId, ii.individualId
  FROM importEventAttendees iea
  LEFT JOIN importIndividual ii ON ii.individualId = iea.individualId
  LEFT JOIN mm_User u ON u.individualId = iea.individualId
  WHERE u.individualId IS NULL;

CREATE TEMPORARY TABLE tEventAttendee
    SELECT e.id, 'member' type, u.id userId, 'admin' createdBy, 'Imported' createdByName, 'y' bought,
      firstName, lastName, nickname, u.email, jobTitle, c.name companyName, supervisorName, e.registrationStart,
      eas.id statusId, NOW() added, NOW() changed
    FROM importEventAttendees iea
      JOIN mm_Event e ON iea.eventId = e.eventId
      JOIN mm_User u ON u.individualId = iea.individualId
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN mm_EventAttendeeStatus eas ON eas.title = iea.attendeeStatus;
#
# # find duplicate firstName-lastName-event records
CREATE TEMPORARY TABLE tEventAttendeeResult
  SELECT id, firstName, lastName, COUNT(*) _total, MIN(userId) AS userId FROM tEventAttendee
  GROUP BY id, firstName, lastName
  HAVING _total > 1;

DELETE ea FROM tEventAttendee ea
  JOIN tEventAttendeeResult ear ON ea.id = ear.id AND ea.firstName = ear.firstName AND ea.lastName = ear.lastName AND
    ea.userId != ear.userId;

INSERT INTO mm_EventAttendee (eventId, type, userId, createdBy, createdByName, bought, firstName, lastName, nickname,
    email, jobTitle, company, supervisorName, registrationDate, status, added, changed)
  SELECT id, type, userId, createdBy, createdByName, bought, firstName, lastName, nickname,
    email, jobTitle, companyName, supervisorName, registrationStart, statusId, added, changed FROM tEventAttendee;