<?php


class RSVP_PRO_Admin_Helper {
	/**
	 * Holds the class object.
	 *
	 * @since 4.4.8
	 *
	 * @var object
	 */
	public static $instance;


	/**
	 * RSVP_PRO_Admin_Helper constructor.
	 *
	 * @since 4.4.8
	 */
	function __construct(){
		add_action( 'admin_action_delete-rsvp-pro-attendee', array( $this, 'delete_attendee' ) );
		add_action( 'wp_ajax_update-rsvp-pro-questions-menu-order', array( $this, 'update_questions_order' ) );
		add_action( 'admin_action_delete-rsvp-pro-question', array( $this, 'delete_question' ) );
		add_action( 'admin_init', array( $this, 'bulk_check_in_attendees' ) );
		add_action( 'admin_init', array( $this, 'bulk_delete_attendees' ) );
		add_action( 'admin_init', array( $this, 'bulk_delete_events' ) );
		add_filter( 'rsvp_show_upsells', '__return_false' );
	}

	/**
	 * Returns the singleton instance of the class.
	 *
	 * @return object The RSVP_Helper object.
	 * @since 4.4.8
	 */
	public static function get_instance(){

		if ( !isset( self::$instance ) && !( self::$instance instanceof RSVP_PRO_Admin_Helper ) ){
			self::$instance = new RSVP_PRO_Admin_Helper();
		}

		return self::$instance;

	}

	/**
	 * Get parent events
	 *
	 * @return array|object|null
	 * @since 4.4.8
	 */
	public function get_parent_events(){

		global $wpdb;
		$sql = 'SELECT id, eventName, open_date, close_date FROM ' . PRO_EVENT_TABLE .
		       ' WHERE (parentEventID IS NULL OR parentEventID = 0) AND id != ' . RSVP_PRO_GLOBAL_ATTENDEE_EVENT_ID;

		return $wpdb->get_results( $sql );
	}

	/**
	 * Get all events
	 *
	 * @return array|object|null
	 * @since 4.4.8
	 */
	public function get_all_events(){

		global $wpdb;
		$sql = 'SELECT id, eventName,parentEventID, open_date, close_date FROM ' . PRO_EVENT_TABLE .
		       ' WHERE id != ' . RSVP_PRO_GLOBAL_ATTENDEE_EVENT_ID;

		return $wpdb->get_results( $sql );
	}

	/**
	 * Get Event by ID
	 *
	 * @param false $id
	 *
	 * @return string
	 * @since 4.4.8
	 */
	public function get_event( $id = false ){
		global $wpdb;

		if ( $id ){
			$sql = 'SELECT eventName, open_date, close_date, options, parentEventID, event_access, eventStartDate, eventEndDate, eventLocation, eventDescription,repeatStartDate, repeatEndDate, repeatFrequencyType, eventLength, eventLengthType, currentRepeatEndDate, open_date_timezone, open_date_time, close_date_timezone, close_date_time, attendee_list_sent_on, send_attendee_list, attendee_columns, eventStartDateTimezone, eventEndDateTimezone FROM ' . PRO_EVENT_TABLE . ' WHERE id = %d';
			return $wpdb->get_results( $wpdb->prepare( $sql,  $id ) );
		}

		return esc_html__( 'No ID has been given', 'rsvp-pro-plugin' );
	}

	/**
	 * Get Event meta by event ID
	 *
	 * @param false $id
	 *
	 * @return string
	 * @since 4.4.8
	 */
	public function get_event_meta( $id = false, $meta = false ){
		global $wpdb;

		if ( $id ){

			if ( $meta ){
				$sql    = 'SELECT ' . $meta . ' FROM ' . PRO_EVENT_TABLE . ' WHERE id = %d';
				$result = $wpdb->get_results( $wpdb->prepare( $sql, $id  ) );

				if ( $result && !empty( $result ) ){
					return $result;
				}

				return esc_html__( 'No results', 'rsvp-pro-plugin' );
			}

			return esc_html__( 'No meta has been given', 'rsvp-pro-plugin' );

		}

		return esc_html__( 'No ID has been given', 'rsvp-pro-plugin' );
	}

	/**
	 * Get PRO associations
	 *
	 * @param false $attendee_id
	 *
	 * @return array|false|object|null
	 * @since 4.4.8
	 */
	public function get_associated_attendees( $attendee_id = false ){
		global $wpdb;
		if ( $attendee_id ){
			$sql = "SELECT CONCAT_WS(' ', firstName, lastName) AS name FROM " . PRO_ATTENDEES_TABLE . ' a
    			JOIN ' . PRO_EVENT_TABLE . ' e ON e.id = a.rsvpEventID
    			WHERE a.id IN (SELECT attendeeID FROM ' . PRO_ASSOCIATED_ATTENDEES_TABLE . ' WHERE associatedAttendeeID = %d)
    			OR a.id in (SELECT associatedAttendeeID FROM ' . PRO_ASSOCIATED_ATTENDEES_TABLE . " WHERE attendeeID = %d)
    			AND ((IFNULL(e.event_access, '" . RSVP_PRO_OPEN_EVENT_ACCESS . "') != '" . RSVP_PRO_PRIVATE_EVENT_ACCESS . "') OR (a.id IN (SELECT rsvpAttendeeID FROM " . PRO_EVENT_ATTENDEE_TABLE . ' WHERE rsvpEventID = e.id)))';

			return $wpdb->get_results( $wpdb->prepare( $sql, $attendee_id, $attendee_id ) );
		}

		return false;
	}

	/**
	 * Function to delete an attendee from the database.
	 *
	 * @param int $attendee_id The attendee ID we wish to delete.
	 *                         Relocated from wp-rsvp.php file and updated
	 */
	public function delete_attendee( $attendee_id = false ){

		if ( $attendee_id ){
			global $wpdb;

			if ( is_numeric( $attendee_id ) && ( $attendee_id > 0 ) ){
				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_ASSOCIATED_ATTENDEES_TABLE . ' WHERE attendeeID = %d OR associatedAttendeeID = %d',
						$attendee_id,
						$attendee_id
					)
				);
				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_ATTENDEES_TABLE . ' WHERE id = %d',
						$attendee_id
					)
				);

				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_ATTENDEE_SUB_EVENTS_TABLE . ' WHERE rsvpAttendeeID = %d',
						$attendee_id
					)
				);

				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_MESSAGE_LOG_ATTENDEES . ' WHERE attendeeID = %d',
						$attendee_id
					)
				);
			}
		} else {

			check_admin_referer( 'delete-rsvp-pro-attendee_' . $_REQUEST['id'] );
			if ( isset( $_REQUEST['action'] ) && 'delete-rsvp-pro-attendee' == $_REQUEST['action'] && isset( $_REQUEST['id'] ) ){

				$attendee_id = absint( $_REQUEST['id'] );
				global $wpdb;

				if ( $attendee_id > 0 ){
					$wpdb->query(
						$wpdb->prepare(
							'DELETE FROM ' . PRO_ASSOCIATED_ATTENDEES_TABLE . ' WHERE attendeeID = %d OR associatedAttendeeID = %d',
							$attendee_id,
							$attendee_id
						)
					);
					$wpdb->query(
						$wpdb->prepare(
							'DELETE FROM ' . PRO_ATTENDEES_TABLE . ' WHERE id = %d',
							$attendee_id
						)
					);

					$wpdb->query(
						$wpdb->prepare(
							'DELETE FROM ' . PRO_ATTENDEE_SUB_EVENTS_TABLE . ' WHERE rsvpAttendeeID = %d',
							$attendee_id
						)
					);

					$wpdb->query(
						$wpdb->prepare(
							'DELETE FROM ' . PRO_MESSAGE_LOG_ATTENDEES . ' WHERE attendeeID = %d',
							$attendee_id
						)
					);
				}
			}
			wp_redirect( wp_get_referer() );
			exit;
		}
	}

	/**
	 * Handles the data erasing for a given email address.
	 *
	 * @param string  $email_address The email address we want to delete from the attendees table
	 * @param integer $page          The page we are on
	 *
	 * @return array                  An array containing how many attendees were deleted
	 * @since 4.4.8
	 *        Relocated from wp-rsvp.php file
	 */
	public function data_eraser_handler( $email_address, $page = 1 ){
		global $wpdb;
		$num_deleted = 0;
		$sql         = 'SELECT id FROM ' . PRO_ATTENDEES_TABLE . ' WHERE email = %s';
		$attendees   = $wpdb->get_results( $wpdb->prepare( $sql, $email_address ) );

		foreach ( $attendees as $a ){
			$this->delete_attendee( $a->id );
			$num_deleted++;
		}

		return array(
			'items_removed'  => $num_deleted,
			'items_retained' => false, // We never retain items
			'messages'       => array( rsvp_pro_get_admin_string( 'data_eraser_successful' ) ),
			'done'           => true,
		);
	}

	/**
	 * Retrieves and packages up the exporter information for the new WordPress compliance functionality
	 *
	 * @param string  $email_address The email address we need to export the information for
	 * @param integer $page          The current page
	 *
	 * @return array                  Containing the information and if everything is done being exported
	 *                                Relocated from wp-rsvp.php file
	 */
	public function data_exporter_handler( $email_address, $page = 1 ){
		global $wpdb;

		$export_items = array();
		$sql          = 'SELECT a.id, e.eventName, a.firstName, a.lastName, a.rsvpDate, 
      a.rsvpStatus, a.note, a.additionalAttendee, a.personalGreeting, a.numGuests, 
      a.suffix, a.salutation, a.nicknames, a.rsvpEventID
    FROM ' . PRO_ATTENDEES_TABLE . ' a 
    JOIN ' . PRO_EVENT_TABLE . ' e ON e.id = a.rsvpEventID 
    WHERE email = %s';
		$attendees    = $wpdb->get_results( $wpdb->prepare( $sql, $email_address ) );
		foreach ( $attendees as $a ){
			// Print out the main event information
			$event_name                         = stripslashes( $a->eventName );
			$export_items['firstName']          = stripslashes( $a->firstName );
			$export_items['lastName']           = stripslashes( $a->lastName );
			$export_items['rsvpDate']           = $a->rsvpDate;
			$export_items['rsvpStatus']         = stripslashes( $a->rsvpStatus );
			$export_items['note']               = stripslashes( $a->note );
			$export_items['additionalAttendee'] = stripslashes( $a->additionalAttendee );
			$export_items['personalGreeting']   = stripslashes( $a->personalGreeting );
			$export_items['numGuests']          = stripslashes( $a->numGuests );
			$export_items['suffix']             = stripslashes( $a->suffix );
			$export_items['salutation']         = stripslashes( $a->salutation );
			$export_items['nicknames']          = stripslashes( $a->nicknames );

			// Print out the custom question information for the main event
			$export_items = rsvp_pro_data_exporter_custom_questions( $a->id, $event_name, $a->rsvpEventID, $export_items );

			// Get the sub-event RSVP information
			$sql        = 'SELECT e.eventName, a.rsvpEventID, a.rsvpAttendeeID, a.rsvpDate, a.rsvpStatus 
        FROM ' . PRO_ATTENDEE_SUB_EVENTS_TABLE . ' a 
        JOIN ' . PRO_EVENT_TABLE . ' e ON e.id = a.rsvpEventID 
        WHERE a.rsvpAttendeeID = %d';
			$sub_events = $wpdb->get_results( $wpdb->prepare( $sql, $a->id ) );
			foreach ( $sub_events as $se ){
				$sub_event_name                                    = stripslashes( $se->eventName );
				$export_items[ $sub_event_name . ' - rsvpDate' ]   = $se->rsvpDate;
				$export_items[ $sub_event_name . ' - rsvpStatus' ] = stripslashes( $se->rsvpStatus );

				// Get the custom question information for each sub-event
				$export_items = rsvp_pro_data_exporter_custom_questions(
					$a->id,
					$sub_event_name,
					$se->rsvpEventID,
					$export_items
				);
			}
		}

		return array(
			'data' => $export_items,
			'done' => true,
		);
	}


	/**
	 * Populates the attendee tables with tokens if they are missing.
	 *
	 * @since 4.4.8
	 */
	public function populate_attendee_tokens(){
		global $wpdb;
		$query     = 'SELECT id FROM ' . PRO_ATTENDEES_TABLE . ' WHERE token = \'\' OR token IS NULL LIMIT 500';
		$attendees = $wpdb->get_results( $query );

		foreach ( $attendees as $a ){
			$wpdb->update(
				PRO_ATTENDEES_TABLE,
				array(
					'token' => rsvp_pro_generate_attendee_token(),
				),
				array(
					'id' => $a->id,
				),
				array( '%s' ),
				array( '%d' )
			);
		}

		$query        = 'SELECT id FROM ' . PRO_ATTENDEE_SUB_EVENTS_TABLE . ' WHERE token = \'\' OR token IS NULL LIMIT 500';
		$subattendees = $wpdb->get_results( $query );
		foreach ( $subattendees as $sa ){
			$wpdb->update(
				PRO_ATTENDEE_SUB_EVENTS_TABLE,
				array(
					'token' => rsvp_pro_generate_attendee_token(),
				),
				array(
					'id' => $sa->id,
				),
				array( '%s' ),
				array( '%d' )
			);
		}
	}

	/**
	 * Update questions order
	 *
	 * @return false
	 * @since 4.4.8
	 */
	public function update_questions_order(){

		global $wpdb;

		parse_str( $_POST['order'], $data );

		if ( !is_array( $data ) )
			return false;

		$id_arr = array();
		foreach ( $data as $key => $values ){
			foreach ( $values as $position => $id ){
				$id_arr[] = $id;
			}
		}

		foreach ( $id_arr as $key => $id ){
			$wpdb->update( PRO_QUESTIONS_TABLE, array( 'sortOrder' => $key ), array( 'id' => intval( $id ) ) );
		}

		die();
	}

	/**
	 * Delete Question
	 *
	 * @param bool $question_id
	 * @param bool $event_id
	 *
	 * @since 4.4.8
	 */
	public function delete_question( $question_id = false, $event_id = false ){

		if ( !$question_id || !$event_id ){

			check_admin_referer( 'delete-rsvp-pro-question_' . $_REQUEST['id'] );

			if ( isset( $_REQUEST['action'] ) && 'delete-rsvp-pro-question' == $_REQUEST['action'] && isset( $_REQUEST['id'] ) && isset( $_REQUEST['eventID'] ) ){

				global $wpdb;
				$question_id = absint( $_REQUEST['id'] );
				$event_id    = absint( $_REQUEST['eventID'] );
				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_QUESTIONS_TABLE . ' WHERE id = %d AND rsvpEventID = %d',
						$question_id,
						$event_id
					)
				);

				wp_redirect( wp_get_referer() );
				exit;
			}
		} else {

			global $wpdb;

			if ( is_numeric( $question_id ) && ( $question_id > 0 ) && is_numeric( $event_id ) && ( $event_id > 0 ) ){
				$wpdb->query(
					$wpdb->prepare(
						'DELETE FROM ' . PRO_QUESTIONS_TABLE . ' WHERE id = %d AND rsvpEventID = %d',
						$question_id,
						$event_id
					)
				);
			}
		}
	}

	/**
	 * Get Questions
	 *
	 * @param false $eventID
	 *
	 * @return array|false|object|null
	 * @since 4.4.8
	 */
	public function get_custom_questions( $eventID = false ){

		if ( $eventID ){
			global $wpdb;
			$sql = 'SELECT pro_questions.id, pro_questions.question, pro_questions.questionTypeID, pro_questions.sortOrder, pro_questions.permissionLevel, pro_questions.grouping, pro_questions.rsvpEventID, pro_questions.required, pro_questions.only_show_on_status, pro_questions.ask_only_associated, pro_questions.required_text, pro_questions.other_question, pro_questions.other_question_text FROM ' . PRO_QUESTIONS_TABLE . ' pro_questions WHERE pro_questions.rsvpEventID = %d ORDER BY sortOrder ASC';

			return $wpdb->get_results( $wpdb->prepare( $sql, $eventID ) );
		}

		return false;
	}

	/**
	 * Get Question
	 *
	 * @param false $eventID
	 * @param bool  $question_id
	 *
	 * @return array|false|object|null
	 * @since 4.4.8
	 */
	public function get_custom_question( $eventID = false, $question_id = false ){

		if ( $eventID && $question_id ){
			global $wpdb;
			$sql = 'SELECT pro_questions.question, pro_questions.questionTypeID, pro_questions.sortOrder, pro_questions.permissionLevel, pro_questions.grouping, pro_questions.rsvpEventID, pro_questions.required, pro_questions.only_show_on_status, pro_questions.ask_only_associated, pro_questions.required_text, pro_questions.other_question, pro_questions.other_question_text FROM ' . PRO_QUESTIONS_TABLE . ' pro_questions WHERE pro_questions.rsvpEventID = %d AND pro_questions.id = %d';

			return $wpdb->get_results( $wpdb->prepare( $sql, $eventID, $question_id ) );
		}

		return false;
	}

	/**
	 * Get Answer
	 *
	 * @param bool $question_id
	 * @param bool $attendee_id
	 *
	 * @return array|false|object|null
	 * @since 4.4.8
	 */
	public function get_custom_answer( $question_id = false, $attendee_id = false ){

		if ( $attendee_id && $question_id ){
			global $wpdb;
			$sql = 'SELECT id, answer FROM ' . PRO_ATTENDEE_ANSWERS . ' WHERE questionID = %d AND attendeeID = %d';

			return $wpdb->get_results( $wpdb->prepare( $sql, $question_id, $attendee_id ) );
		}

		return false;
	}

	/**
	 * Get attendees list columns
	 *
	 * @param false $event_ID
	 *
	 * @return array|false|object|null
	 * @since 4.4.8
	 */
	public function get_event_attendee_columns( $event_ID = false ){
		if ( !$event_ID ){
			return false;
		}

		global $wpdb;

		$sql = 'SELECT attendee_columns FROM ' . PRO_EVENT_TABLE . ' WHERE id = %d';

		return $wpdb->get_results( $wpdb->prepare( $sql, $event_ID ) );
	}

	/**
	 * Bulk delete attendees
	 *
	 * @param array $attendees
	 *
	 * @return string|void
	 * @since 4.4.8
	 */
	public function bulk_delete_attendees( $attendees = array() ){

		if ( !empty( $attendees ) ){

			foreach ( $attendees as $attendee ){

				if ( is_numeric( $attendee ) && ( $attendee > 0 ) ){
					$this->delete_attendee( $attendee );
				}
			}
		} else if ( ( count( $_GET ) > 0 ) && isset( $_GET['rsvp-pro-bulk-action'] ) && ( $_GET['rsvp-pro-bulk-action'] == 'delete' ) && ( is_array( $_GET['attendee'] ) && ( count( $_GET['attendee'] ) > 0 ) ) ){
			// security check!
			if ( isset( $_GET['_wpnonce'] ) && !empty( $_GET['_wpnonce'] ) ){

				$nonce  = $_GET['_wpnonce'];
				$action = 'rsvp-bulk-attendees';
				if ( !wp_verify_nonce( $nonce, $action ) )
					wp_die( 'Nope! Security check failed!' );

			}

			foreach ( $_GET['attendee'] as $attendee ){

				if ( is_numeric( $attendee ) && ( $attendee > 0 ) ){
					$this->delete_attendee( $attendee );
				}
			}
		} else {
			return __( 'No attendees found', 'rsvp-pro-plugin' );
		}
	}

	/**
	 * Bulk delete attendees
	 *
	 * @param array $events
	 *
	 * @return string|void
	 * @since 4.4.8
	 */
	public function bulk_delete_events( $events = array() ){

		if ( !empty( $events ) ){

			foreach ( $events as $event ){

				if ( is_numeric( $event ) && ( $event > 0 ) ){
					$this->delete_attendee( $event );
				}
			}
		} else if ( ( count( $_GET ) > 0 ) && isset( $_GET['rsvp-pro-bulk-action'] ) && ( $_GET['rsvp-pro-bulk-action'] == 'delete_events' ) && ( is_array( $_GET['events'] ) && ( count( $_GET['events'] ) > 0 ) ) ){
			// security check!
			if ( isset( $_GET['_wpnonce'] ) && !empty( $_GET['_wpnonce'] ) ){

				$nonce  = $_GET['_wpnonce'];
				$action = 'rsvp-bulk-events';
				if ( !wp_verify_nonce( $nonce, $action ) )
					wp_die( 'Nope! Security check failed!' );

			}

			foreach ( $_GET['events'] as $event ){

				if ( is_numeric( $event ) && ( $event > 0 ) ){
					$this->delete_event_only( $event );
				}
			}
		} else if ( ( count( $_GET ) > 0 ) && isset( $_GET['rsvp-pro-bulk-action'] ) && ( $_GET['rsvp-pro-bulk-action'] == 'delete_events_and_attendees' ) && ( is_array( $_GET['events'] ) && ( count( $_GET['events'] ) > 0 ) ) ){

			if ( isset( $_GET['_wpnonce'] ) && !empty( $_GET['_wpnonce'] ) ){

				$nonce  = $_GET['_wpnonce'];
				$action = 'rsvp-bulk-events';
				if ( !wp_verify_nonce( $nonce, $action ) )
					wp_die( 'Nope! Security check failed!' );

			}

			foreach ( $_GET['events'] as $event ){

				if ( is_numeric( $event ) && ( $event > 0 ) ){
					rsvp_pro_delete_event( $event );
				}
			}

		} else {
			return __( 'No attendees found', 'rsvp-pro-plugin' );
		}
	}

	/**
	 * Delete event only and not event related data
	 *
	 * @param false $event_id
	 *
	 * @since 4.4.8
	 */
	public function delete_event_only( $event_id = false ){

		if ( $event_id && is_numeric( $event_id ) && ( $event_id > 0 ) ){
			global $wpdb;
			$wpdb->query( $wpdb->prepare( 'DELETE FROM ' . PRO_EVENT_TABLE . ' WHERE id = %d', $event_id ) );
			$wpdb->query( $wpdb->prepare( 'UPDATE ' . PRO_ATTENDEES_TABLE . ' SET rsvpEventID = %d WHERE rsvpEventID = %d',RSVP_PRO_GLOBAL_ATTENDEE_EVENT_ID, $event_id ) );
		}
	}

	/**
	 * Bulk check in attendees
	 *
	 * @param array $attendees
	 * @param bool  $eventID
	 *
	 * @return string|void
	 * @since 4.4.8
	 */
	public function bulk_check_in_attendees( $attendees = array(), $eventID = false ){

		if ( !empty( $attendees ) && $eventID ){
			if ( 0 < $eventID ){
				foreach ( $attendees as $attendee ){
					if ( is_numeric( $attendee ) && ( $attendee > 0 ) ){
						rsvp_pro_handle_attendee_check_in( $eventID, $attendee, 'Y' );
					}
				}
			} else {
				return __( 'Event id error', 'rsvp-pro-plugin' );
			}
		} else if ( isset( $_GET['rsvp-pro-bulk-action'] ) && ( $_GET['rsvp-pro-bulk-action'] == 'check_in' ) &&
		            is_array( $_GET['attendee'] ) && ( count( $_GET['attendee'] ) > 0 ) ){

			if ( isset( $_GET['_wpnonce'] ) && !empty( $_GET['_wpnonce'] ) ){

				$nonce  = $_GET['_wpnonce'];
				$action = 'rsvp-bulk-attendees';
				if ( !wp_verify_nonce( $nonce, $action ) )
					wp_die( 'Nope! Security check failed!' );

			}
			$eventID = $_GET['eventID'];

			if ( 0 < $eventID ){
				foreach ( $_GET['attendee'] as $attendee ){
					if ( is_numeric( $attendee ) && ( $attendee > 0 ) ){
						rsvp_pro_handle_attendee_check_in( $eventID, $attendee, 'Y' );
					}
				}
			}
		} else {
			return __( 'No attendees found', 'rsvp-pro-plugin' );
		}
	}

	
	/**
	 * Set propper timezones
	 */
	public static function set_propper_date( $date ) {

		if ( preg_match( '/^UTC[+-]/', $date ) ) {
			return str_replace('.', '', preg_replace( '/UTC?/', '', $date ) ) ;
		} 
		
		return $date;
	}
}

RSVP_PRO_Admin_Helper::get_instance();