<?php
/**
 * Holds the database update functions for making sure the tables are up to
 * date and have all the columns that are needed.
 *
 * @package rsvp_pro_plugin
 * @since 1.0.0
 */

/**
 * Database updating script.
 *
 * @param  object $wpdb The WPDB object
 *
 * @since  1.0.0
 */
function rsvp_pro_update_database( $wpdb ) {
	$installed_ver = get_option( 'rsvp_pro_db_version' );
	$table         = $wpdb->prefix . 'rsvpAttendees';

	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = 'CREATE TABLE ' . $table . " (
  		    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  		    `firstName` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  		    `lastName` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
      		`rsvpDate` DATE NULL ,
      		`rsvpStatus` ENUM( 'Yes', 'No', 'NoResponse' ) NOT NULL DEFAULT 'NoResponse',
      		`note` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
      		`kidsMeal` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N',
      		`additionalAttendee` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N',
      		`veggieMeal` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N',
      		`personalGreeting` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
        	`email` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
  		  );";
		$wpdb->query( $sql );
	}
	$table = $wpdb->prefix . 'rsvpAssociatedAttendees';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = 'CREATE TABLE ' . $table . ' (
    		`attendeeID` INT NOT NULL ,
    		`associatedAttendeeID` INT NOT NULL
    		);';
		$wpdb->query( $sql );
		$sql = 'ALTER TABLE `' . $table . '` ADD INDEX ( `attendeeID` ) ';
		$wpdb->query( $sql );
		$sql = 'ALTER TABLE `' . $table . '` ADD INDEX ( `associatedAttendeeID` )';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'personalGreeting'" ) != 'personalGreeting' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `personalGreeting` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = " CREATE TABLE $table (
      		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
      		`question` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
      		`questionTypeID` INT NOT NULL,
      		`sortOrder` INT NOT NULL DEFAULT '99',
      		`permissionLevel` ENUM( 'public', 'private' ) NOT NULL DEFAULT 'public',
        	`grouping` VARCHAR(30) NOT NULL,
        	`rsvpEventID` INT NOT NULL
    		);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'sortOrder'" ) != 'sortOrder' ) {
		$sql = 'ALTER TABLE ' . $table . " ADD `sortOrder` INT NOT NULL DEFAULT '99';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProQuestionTypes';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = " CREATE TABLE $table (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    		`questionType` VARCHAR( 100 ) NOT NULL ,
    		`friendlyName` VARCHAR(100) NOT NULL
    		);";
		$wpdb->query( $sql );

		$wpdb->insert(
			$table,
			array(
				'questionType' => 'shortAnswer',
				'friendlyName' => 'Short Answer',
			),
			array(
				'%s',
				'%s',
			)
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'multipleChoice',
				'friendlyName' => 'Multiple Choice',
			),
			array( '%s', '%s' )
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'longAnswer',
				'friendlyName' => 'Long Answer',
			),
			array(
				'%s',
				'%s',
			)
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'dropdown',
				'friendlyName' => 'Drop Down',
			),
			array(
				'%s',
				'%s',
			)
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'radio',
				'friendlyName' => 'Radio',
			),
			array( '%s', '%s' )
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'datepicker',
				'friendlyName' => 'Calendar Datepicker',
			),
			array( '%s', '%s' )
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'file',
				'friendlyName' => 'File',
			),
			array( '%s', '%s' )
		);
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'numeric',
				'friendlyName' => 'Only Numbers',
			),
			array( '%s', '%s' )
		);
	} elseif ( (int) $installed_ver < 6 ) {
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'radio',
				'friendlyName' => 'Radio',
			),
			array( '%s', '%s' )
		);
	} elseif ( (int) $installed_ver < 50 ) {
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'datepicker',
				'friendlyName' => 'Calendar Datepicker',
			),
			array( '%s', '%s' )
		);
	} elseif ( (int) $installed_ver < 51 ) {
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'file',
				'friendlyName' => 'File',
			),
			array( '%s', '%s' )
		);
	} elseif ( (int) $installed_ver < 55 ) {
		$wpdb->insert(
			$table,
			array(
				'questionType' => 'numeric',
				'friendlyName' => 'Only Numbers',
			),
			array( '%s', '%s' )
		);
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestionAnswers';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = "CREATE TABLE $table (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    		`questionID` INT NOT NULL,
    		`answer` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
    		);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeAnswers';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = "CREATE TABLE $table (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    		`questionID` INT NOT NULL,
    		`answer` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    		`attendeeID` INT NOT NULL
    		);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestionAttendees';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = "CREATE TABLE $table (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    		`questionID` INT NOT NULL ,
    		`attendeeID` INT NOT NULL
    		);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'permissionLevel'" ) != 'permissionLevel' ) {
		$sql = "ALTER TABLE `$table` ADD `permissionLevel` ENUM( 'public', 'private' ) NOT NULL DEFAULT 'public';";
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 9 ) {
		rsvp_pro_install_passcode_field();
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( (int) $installed_ver < 10 ) {
		// create the rsvpEvents table
		$table = $wpdb->prefix . 'rsvpEvents';
		if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
			$sql = "CREATE TABLE `$table` (
  	          `id` int(11) NOT NULL AUTO_INCREMENT,
  	          `eventName` mediumtext NOT NULL,
  	          `open_date` DATE NOT NULL ,
  	          `close_date` DATE NOT NULL ,
  	          `options` TEXT NOT NULL ,
  	          PRIMARY KEY (`id`)
  	        );";
			$wpdb->query( $sql );
		}

		// Add in the association between rsvpEvents and attendees
		$table = $wpdb->prefix . 'rsvpAttendees';
		if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'rsvpEventID'" ) != 'rsvpEventID' ) {
			$sql = "ALTER TABLE `$table` ADD `rsvpEventID` INT NOT NULL ";
			$wpdb->query( $sql );
		}

		$table = $wpdb->prefix . 'rsvpProCustomQuestions';
		if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'rsvpEventID'" ) != 'rsvpEventID' ) {
			$sql = "ALTER TABLE `$table` ADD `rsvpEventID` INT NOT NULL ";
			$wpdb->query( $sql );
		}
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'email'" ) != 'email' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `email` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'grouping'" ) != 'grouping' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `grouping` VARCHAR(30) NOT NULL ;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'numGuests'" ) != 'numGuests' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `numGuests` INT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'parentEventID'" ) != 'parentEventID' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `parentEventID` INT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = 'CREATE TABLE ' . $table . " (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    		`rsvpDate` DATE NULL ,
    		`rsvpStatus` ENUM( 'Yes', 'No', 'NoResponse' ) NOT NULL DEFAULT 'NoResponse',
    		`kidsMeal` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N',
    		`veggieMeal` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N',
        	`rsvpEventID` INT NOT NULL,
        	`rsvpAttendeeID` INT NOT NULL
    		);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'required'" ) != 'required' ) {
		$sql = 'ALTER TABLE ' . $table . " ADD `required` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'suffix'" ) != 'suffix' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `suffix` VARCHAR(10) NULL;';
		$wpdb->query( $sql );
	}
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'salutation'" ) != 'salutation' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `salutation` VARCHAR(10) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEventAttendees';
	if ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) {
		$sql = 'CREATE TABLE ' . $table . ' (
    		`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `rsvpEventID` INT NOT NULL,
        `rsvpAttendeeID` INT NOT NULL
    		);';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'event_access'" ) != 'event_access' ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `event_access` VARCHAR(25) NULL;';
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 23 ) {
		$table = $wpdb->prefix . 'rsvpAttendeeAnswers';
		$sql   = 'ALTER TABLE ' . $table . " ADD INDEX `attendee_question` (`questionID`, `attendeeID`) COMMENT '';";
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpAttendees';

		$sql = 'ALTER TABLE ' . $table . " ADD INDEX `rsvpEventId` (`rsvpEventID`) COMMENT '';";
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 24 ) {
		$table = $wpdb->prefix . 'rsvpAttendees';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 25 ) {
		$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestionAnswers';
	if ( ( (int) $installed_ver < 26 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'defaultAnswer'" ) != 'defaultAnswer' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `defaultAnswer` VARCHAR(1) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 27 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'nicknames'" ) != 'nicknames' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `nicknames` VARCHAR(250) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 28 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'primaryAttendee'" ) != 'primaryAttendee' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `primaryAttendee` VARCHAR(1) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 29 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'eventStartDate'" ) != 'eventStartDate' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `eventStartDate` DATETIME NULL,
        ADD `eventEndDate` DATETIME NULL,
        ADD `eventLocation` VARCHAR(350) NULL,
        ADD `eventDescription` mediumtext;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 30 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'repeatStartDate'" ) != 'repeatStartDate' ) ) {
		$sql = "ALTER TABLE `$table` ADD `repeatStartDate` DATE NULL,
              ADD `repeatEndDate` DATE NULL,
              ADD `repeatFrequencyType` ENUM('day','week','month','year') NULL,
              ADD `repeatFrequency` INT NULL,
              ADD `eventLength` INT NULL,
              ADD `eventLengthType` ENUM('day','week','month','year') NULL,
              ADD `currentRepeatEndDate` DATE NULL;";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEventReminders';
	if ( ( (int) $installed_ver < 31 ) || ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) ) {
		$sql = 'CREATE TABLE ' . $table . " (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `rsvpEventID` INT NOT NULL,
        `name` VARCHAR(100) NOT NULL,
        `timeType` ENUM('day', 'week','month', 'static') NULL,
        `sendOn` DATE NULL,
        `period` INT NULL,
        `rsvpStatuses` VARCHAR(100) NULL,
        `messageBody` TEXT,
        `messageSubject` MEDIUMTEXT
        );";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 32 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'open_date_time'" ), 'open_date_time' ) != 0 ) ) {
		$sql = "ALTER TABLE `$table` ADD `open_date_timezone` VARCHAR(50) NULL,
                ADD `open_date_time` VARCHAR(25) NULL,
                ADD `close_date_timezone` VARCHAR(50) NULL,
                ADD `close_date_time` VARCHAR(25) NULL,
                ADD `attendee_list_sent_on` DATETIME NULL;";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 33 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'send_attendee_list'" ), 'send_attendee_list' ) != 0 ) ) {
		$sql = "ALTER TABLE `$table` ADD `send_attendee_list` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';";
		$wpdb->query( $sql );
	}

	$table         = $wpdb->prefix . 'rsvpAttendees';
	$sql           = "SELECT character_maximum_length FROM information_schema.columns
			WHERE  table_schema = '" . DB_NAME . "'
       		AND table_name = '" . $table . "'
       		AND column_name = 'suffix' ";
	$suffix_length = $wpdb->get_var( $sql );
	if ( ( (int) $installed_ver < 34 ) || ( $suffix_length < 150 ) ) {
		$sql = "ALTER TABLE `$table` CHANGE `suffix` `suffix` VARCHAR(150);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 35 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'checked_in'" ), 'checked_in' ) != 0 ) ) {
		$sql = "ALTER TABLE `$table` ADD `checked_in` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';";
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
		if ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'checked_in'" ), 'checked_in' ) != 0 ) {
			$sql = "ALTER TABLE `$table` ADD `checked_in` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';";
			$wpdb->query( $sql );
		}
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'only_show_on_status'" ), 'only_show_on_status' ) != 0 ) {
		$sql = "ALTER TABLE `$table` ADD `only_show_on_status` VARCHAR(30);";
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 37 ) {
		$table = $wpdb->prefix . 'rsvpAttendees';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist','Maybe') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist','Maybe') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 38 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'group_id'" ), 'group_id' ) != 0 ) ) {
		$sql = "ALTER TABLE `$table` ADD `group_id` VARCHAR(100);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( ( (int) $installed_ver < 39 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'ask_only_associated'" ) != 'ask_only_associated' ) ) {
		$sql = 'ALTER TABLE ' . $table . " ADD `ask_only_associated` ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeAnswers';

	if ( $installed_ver && ( (int) $installed_ver < 40 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD INDEX `attendee_answers` (`answer`(1024), `questionID`, `attendeeID`) ';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeesChangelog';
	if ( ( (int) $installed_ver < 41 ) || ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) ) {
		$sql = 'CREATE TABLE ' . $table . ' (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `attendeeID` INT NOT NULL,
        `changes` TEXT,
        `change_date` DATE NULL
      );';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 42 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'plus_one'" ), 'plus_one' ) != 0 ) ) {
		$sql = "ALTER TABLE `$table` ADD `plus_one` VARCHAR(1);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
	if ( ( (int) $installed_ver < 43 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'plus_one'" ), 'plus_one' ) != 0 ) ) {
		$sql = $sql = "ALTER TABLE `$table` ADD `plus_one` VARCHAR(1);";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpMessageLog';
	if ( ( (int) $installed_ver < 44 ) || ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) ) {
		$sql = 'CREATE TABLE ' . $table . ' (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `rsvpEventID` INT NOT NULL,
        `from_email` VARCHAR(200),
        `from_name` VARCHAR(250),
        `rsvp_status` VARCHAR(20),
        `read_receipt` VARCHAR(1),
        `subject` TEXT,
        `message` TEXT,
        `send_date` DATE NULL
      );';
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpMessageLogAttendees';
		$sql   = 'CREATE TABLE ' . $table . ' (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `message_id` INT NOT NULL,
        `attendeeID` INT NOT NULL
      );';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( ( (int) $installed_ver < 45 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'required_text'" ), 'required_text' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `required_text` VARCHAR(300);';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 46 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'attendee_columns'" ), 'attendee_columns' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `attendee_columns` MEDIUMTEXT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpEvents';
	if ( ( (int) $installed_ver < 47 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'eventstartdatetimezone'" ), 'eventstartdatetimezone' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `eventStartDateTimezone` VARCHAR(150) NULL,
		ADD `eventEndDateTimezone` VARCHAR(150) NULL;';
		$wpdb->query( $sql );
	}

	if ( (int) $installed_ver < 48 ) {
		$table = $wpdb->prefix . 'rsvpAttendees';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist','Maybe', 'PlusOne') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';

		$sql = 'ALTER TABLE ' . $table . " CHANGE `rsvpStatus` `rsvpStatus` ENUM('Yes','No','NoResponse','Waitlist','Maybe', 'PlusOne') NOT NULL DEFAULT 'NoResponse';";
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 49 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'token'" ), 'token' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `token` VARCHAR(75) NULL;';
		$wpdb->query( $sql );

		$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
		$sql   = 'ALTER TABLE ' . $table . ' ADD `token` VARCHAR(75) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 53 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'amount'" ) != 'amount' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `amount` DECIMAL NULL,
		ADD `payment_status` VARCHAR(50) NULL,
		ADD `transaction_id` VARCHAR(150) NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
	if ( ( (int) $installed_ver < 53 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'amount'" ) != 'amount' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `amount` DECIMAL NULL,
		ADD `payment_status` VARCHAR(50) NULL,
		ADD `transaction_id` VARCHAR(150) NULL;';
		$wpdb->query( $sql );
	}

	$table    = $wpdb->prefix . 'rsvpAttendees';
	$col_info = $wpdb->get_row( 'SHOW FULL COLUMNS FROM ' . $table . ' WHERE Field="note" AND Collation<>"utf8mb4_unicode_520_ci"' );
	if ( ( ( int ) $installed_ver < 54 ) || ( $col_info !== null ) ) {
		$sql = 'ALTER TABLE ' . $table . ' MODIFY note text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeSubEvents';
	if ( ( ( int ) $installed_ver < 56 ) || ( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'numGuests'" ) != 'numGuests' ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `numGuests` INT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpAttendeeEventCosts';
	if ( ( (int) $installed_ver < 57 ) || ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) ) {
		$sql = 'CREATE TABLE ' . $table . ' (
        	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        	`rsvpEventID` INT NOT NULL,
        	`attendeeID` INT NOT NULL ,
        	`amount` NUMERIC(15,4)
      	);';
		$wpdb->query( $sql );
	}

	$table    = $wpdb->prefix . 'rsvpAttendees';
	$col_info = $wpdb->get_row( 'SHOW FULL COLUMNS FROM ' . $table . ' WHERE Field="rsvpDate" AND type = "date"' );
	if ( ( (int) $installed_ver < 58 ) || ( null !== $col_info ) ) {
		$sql = 'ALTER TABLE ' . $table . ' CHANGE `rsvpDate` `rsvpDate` DATETIME NULL DEFAULT NULL;';
		$wpdb->query( $sql );
	}

	$table    = $wpdb->prefix . 'rsvpAttendeeSubEvents';
	$col_info = $wpdb->get_row( 'SHOW FULL COLUMNS FROM ' . $table . ' WHERE Field="rsvpDate" AND type = "date"' );
	if ( ( (int) $installed_ver < 58 ) || ( null !== $col_info ) ) {
		$sql = 'ALTER TABLE ' . $table . ' CHANGE `rsvpDate` `rsvpDate` DATETIME NULL DEFAULT NULL;';
		$wpdb->query( $sql );
	}

	$table    = $wpdb->prefix . 'rsvpAttendeesChangelog';
	$col_info = $wpdb->get_row( 'SHOW FULL COLUMNS FROM ' . $table . ' WHERE Field="change_date" AND type = "date"' );
	if ( ( (int) $installed_ver < 58 ) || ( null !== $col_info ) ) {
		$sql = 'ALTER TABLE ' . $table . ' CHANGE `change_date` `change_date` DATETIME NULL DEFAULT NULL;';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpMessageLog';
	$col_info = $wpdb->get_row( 'SHOW FULL COLUMNS FROM ' . $table . ' WHERE Field="send_date" AND type = "date"' );
	if ( ( (int) $installed_ver < 59 ) || ( strcasecmp( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ), $table ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' CHANGE `send_date` `send_date` DATETIME NULL DEFAULT NULL;';
		$wpdb->query( $sql );
	}

	$table    = $wpdb->prefix . 'rsvpAttendees';
	if ( ( (int) $installed_ver < 60 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'alternate_passcode'" ), 'alternate_passcode' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `alternate_passcode` VARCHAR(50) NOT NULL DEFAULT \'\';';
		$wpdb->query( $sql );
	}

	$table = $wpdb->prefix . 'rsvpProCustomQuestions';
	if ( ( (int) $installed_ver < 61 ) || ( strcasecmp( $wpdb->get_var( "SHOW COLUMNS FROM `$table` LIKE 'other_question'" ), 'other_question' ) != 0 ) ) {
		$sql = 'ALTER TABLE ' . $table . ' ADD `other_question` VARCHAR(1) DEFAULT \'N\',
		ADD `other_question_text` VARCHAR(300);';
		$wpdb->query( $sql );
	}

	update_option( 'rsvp_pro_db_version', RSVP_PRO_DB_VERSION );
}

function rsvp_pro_install_passcode_field() {
	global $wpdb;
	$table = PRO_ATTENDEES_TABLE;
	$sql   = "SHOW COLUMNS FROM `$table` LIKE 'passcode'";
	if ( ! $wpdb->get_results( $sql ) ) {
		$sql = "ALTER TABLE `$table` ADD `passcode` VARCHAR(50) NOT NULL DEFAULT '';";
		$wpdb->query( $sql );
	}
}
