<?php 
/*!
 * Jetpack CRM
 * https://jetpackcrm.com
 * V1.2.5
 *
 * Copyright 2020 Automattic
 *
 * Date: 09/01/2017
 */

defined( 'ZEROBSCRM_PATH' ) || exit( 0 );

	#1 - Client Portal Welcome Email
	#2 - Quote Accepted Email
	#3 - Email Invoice 
	#4 - New Quote 
	#5 - Task Notification (leave for now)...
	#6 - Client Portal Password Reset

	// Define globals for EMAIL TEMPLATES
	define( 'ZBSEMAIL_CLIENTPORTALWELCOME', 1 );
	define( 'ZBSEMAIL_QUOTEACCEPTED', 2 );
	define( 'ZBSEMAIL_EMAILINVOICE', 3 );
	define( 'ZBSEMAIL_NEWQUOTE', 4 );
	define( 'ZBSEMAIL_TASK_NOTIFICATION', 5 );
	define( 'ZBSEMAIL_CLIENTPORTALPWREST', 6 );
	define( 'ZBSEMAIL_STATEMENT', 7 );

/* ======================================================
  Email tracking functions
   ====================================================== */

#} This tracks emails being opened by loading a 1px by 1px gif
add_action('init','zeroBSCRM_mailTracking_track');
function zeroBSCRM_mailTracking_track(){
	
	if (isset($_GET['zbspostman']) && !empty($_GET['zbspostman'])){

		$potentialHash = sanitize_text_field( $_GET['zbspostman'] );

		if (!empty($potentialHash)){

			// attempt to get history row for pixel
			$row = zeroBSCRM_mailTracking_getEmailFromHash($potentialHash);

			// found one?
			if (is_array($row)){

				// make new opened count
				$openedCount = 1; if (isset($row['zbsmail_opened'])) { $openedCount = (int)$row['zbsmail_opened']; $openedCount++; }
				$firstOpen 	 = 0; if (isset($row['zbsmail_firstopened'])) { $firstOpen = (int)$row['zbsmail_firstopened'];}
				
				
				// log it
				zeroBSCRM_mailTracking_logOpen($potentialHash,-1,$openedCount, $firstOpen);


			}

			header( 'Content-Type: image/gif' );

			// Full path to the image on the server it is in /i/blank.gif
			$localBlankGif = ZEROBSCRM_PATH . 'i/blank.gif';

			// Now actually output the image requested (intentionally disregarding if the database was affected)
			header( 'Pragma: public' );
			header( 'Expires: 0' );
			header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
			header( 'Cache-Control: private',false );
			header( 'Content-Disposition: attachment; filename="blank.gif"' );
			header( 'Content-Transfer-Encoding: binary' );			
			readfile( $localBlankGif );

			exit( 0 );
		}
	
	}



}


// Adds open tracking pixel to a html email
/* As per our brain-melting convo of 10/05/18: https://epicplugins.slack.com/archives/G075SD73P/p1525956136000217

	$messageHTML = html
	$who = either WPID of user sending, 
					OR 
					-10 = sys email to customer,
					-11 quote proposal accepted sent to admin,
					-12 you have a new quote sent to contact,
					-13 task notification sent to owner of task
					-14 you have a new invoice sent to customer,
					-15 you have a statement sent to customer,
					-16 you have a new invoice sent to company,
					-17 you have a new quote sent to company
	$user =  CONTACT ID associated OR if to team member, their WPID
	$email = target email
	$item = TEMPLATE_ID
	$subject = subject
	

// THIS WAS decommissioned by WH in favour of hashed pixels :)
function zeroBSCRM_mailTracking_addPixel($messageHTML='', $who = -1, $user=-1, $email='', $item=-1, $subject=''){


	//this surely needs a hash which is decoded via PHP side..
	$tracker = site_url() . '?zbs-email-tracker=1&zbs-who=' . urlencode( $who ) . '&zbs-user=' . urlencode( $user ) . '&email-zbs=' . sanitize_email($email) . '&zbs-item=' . urlencode($item) . '&zbs-subj=' . urlencode($subject);
	$trackingImg = '<img alt="" src="'.$tracker.'" width="1" height="1" border="0" />';

	//Add the tracker to the message.

		// first we try and jam into </body>, if none, (THATS WEIRD), we just append...
		if (strpos($messageHTML,'</body') > 0){

			// prepend </body with our img
			$messageHTML = str_replace('</body',$trackingImg.'</body',$messageHTML);

		} else {
			
			// just append, though that's funky... no body?
			$messageHTML .= $trackingImg;
		}
	
	return $messageHTML;
}*/
function zeroBSCRM_mailTracking_addPixel($messageHTML='', $hash=''){

	// NOTE if an empty hash is passed, this'll still return body, but without any pixel
	if (empty($hash)) return $messageHTML;


	//this surely needs a hash which is decoded via PHP side..
	$tracker = site_url() . '?zbspostman=' . urlencode($hash);
	$trackingImg = '<img alt="" src="'.$tracker.'" width="1" height="1" border="0" />';

	//Add the tracker to the message.

		// first we try and jam into </body>, if none, (THATS WEIRD), we just append...
		if (strpos($messageHTML,'</body') > 0){

			// prepend </body with our img
			$messageHTML = str_replace('</body',$trackingImg.'</body',$messageHTML);

		} else {
			
			// just append, though that's funky... no body?
			$messageHTML .= $trackingImg;
		}
	
	return $messageHTML;
}


// Saves presence of tracking pixel image
/* As per our brain-melting convo of 10/05/18: https://epicplugins.slack.com/archives/G075SD73P/p1525956136000217
.. WH rewrote the db table to make more simple sense (naming) so now:

	$emailTypeID = type of email, e.g. mailTemplate ID 
					OR -999 for direct email from UI
	$targetObjID = contactID or wp user id if sent to admin
	$senderWPID = either WPID of user sending, 
					OR 
					-10 = sys email to customer,
					-11 quote proposal accepted sent to admin,
					-12 you have a new quote sent to contact,
					-13 task notification sent to owner of task
					-14 you have a new invoice sent to customer,
					-15 you have a statement sent to customer,
					-16 you have a new invoice sent to company,
					-17 you have a new quote sent to company
	$senderEmailAddress = sent from email
	$associatedObjID = associated obj id, e.g. Invoice ID, 
					OR -999 for direct email from UI (yes MS used this twice in this case)
	$emailSubject = the email subject

*/
function zeroBSCRM_mailTracking_logEmail($emailTypeID=-1, $targetObjID=-1, $senderWPID = 0, $senderEmailAddress='', $associatedObjID=-1, $emailSubject='',$returnHash = true, $emailContent='', $thread = -1, $email_to = '', $in_or_out = 'sent', $mailDeliveryMethod = ''){
	//track the template ID into the history, along with the contact ID (or admin ID)..
	//$who = 0 customer, 1 CRM team...
	global $wpdb, $ZBSCRM_t;

	// MAKE A HASH - this isn't particularly legitimate, #torethink
	 // phpcs:ignore WordPress.WP.AlternativeFunctions.rand_rand
	$hash = sha1(time().$emailTypeID.$associatedObjID.$senderEmailAddress.$emailSubject.$senderWPID.rand(0,99999).$targetObjID);

	if($thread == -1){
		//then we are making a new thread. Otherwise, it will be passed via the function / other send boxes
		$sql = "SELECT MAX(zbsmail_sender_thread) as max_thread FROM " . $ZBSCRM_t['system_mail_hist'];
		$max_thread = (int)$wpdb->get_var($sql);
		$max_thread++;
		$thread = $max_thread;
	}

	$email_data = array( 
		'zbs_site' => -1,
		'zbs_team' => -1,
		'zbs_owner' => -1,
		'zbsmail_type' => $emailTypeID, // prev zbs_emailID
		'zbsmail_sent' => -1,
		'zbsmail_assoc_objid' => $associatedObjID, // prev zbsmail_assoc_objid
		'zbsmail_sender_email' => $senderEmailAddress, // prev zbsmail_sender_email
		'zbsmail_subject' => $emailSubject,
		// phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		'zbsmail_content'                 => $emailContent, // THIS IS FREE of formatting e.g. not put through a specific format (wp_kses) - do that a level up/when passing content
		'zbsmail_sender_wpid' => $senderWPID,   // prev zbsmail_sender_wpid
		'zbsmail_target_objid' => $targetObjID,  // prev zbsmail_target_objid
		'zbsmail_hash' => $hash,

		'zbsmail_status' => $in_or_out,
		'zbsmail_starred' => 0,

		'zbsmail_sender_thread' => $thread,
		'zbsmail_receiver_email' => $email_to,

		// maildelivery method
		'zbsmail_sender_maildelivery_key' => $mailDeliveryMethod,


		'zbsmail_lastopened' => 0,
		'zbsmail_lastclicked' => 0,
		'zbsmail_created' => time()
	);

	if ($wpdb->insert( 
		$ZBSCRM_t['system_mail_hist'], 
		$email_data, 
		array( 
		'%d',    //zbs_site
		'%d',    //zbs_team
		'%d',    //zbs_owner
		'%d',    //zbs_emailID
		'%d',    //sent
		'%d',    //item
		'%s',    //email
		'%s',    //subject
		'%s',	 //body
		'%d',    //who
		'%d',    //userID
		'%s',    //hash

		'%s',    //status (inbox, sent, draft, scheduled)
		'%d',    //0 = not starred, 1 = starred

		'%d', 	//the thread we are sending
		'%s',   //sent to which email

		'%s',   //mail delivery method key

		'%d',	 //last opened
		'%d',    //lastclicked
		'%d',    //created
		) 
	) > 0){
		// inserted, let's move on
		
		// return id?
		if (!$returnHash) $newSysTemplateHist = $wpdb->insert_id;

		// otherwise return hash (used in tracking pixel)
		return $hash;

	} else {
		return false;
	}
}

// This function updates line to say 'SENT'
// it just updates the 'sent' flag :)
function zeroBSCRM_mailTracking_logSend($hash='',$ID=-1){

	global $wpdb, $ZBSCRM_t;

	// build where
	$whereArr = array(); $whereTypeArr = array();
	if (!empty($hash)) {
		$whereArr['zbsmail_hash'] = $hash;
		$whereTypeArr[] = '%s';
	}
	if (!empty($ID) && $ID > 0) {
		$whereArr['ID'] = $ID;
		$whereTypeArr[] = '%d';
	}

	if (count($whereArr) > 0){

		  if ($wpdb->update($ZBSCRM_t['system_mail_hist'],
		  array( 
		    'zbsmail_sent' => 1,
		  ), 
		  $whereArr,
		  $whereTypeArr,
		  array(
		    '%d'
		    )
		  ) !== false){

		  	return true;

		  }

	}

	return false;
}


// This function updates line to say 'opened' ++increments opened and updates zbsmail_lastopened	
function zeroBSCRM_mailTracking_logOpen($hash='', $ID=-1, $newOpenedCount = 1, $firstOpen = 0){

	global $wpdb, $ZBSCRM_t;



	// build where
	$whereArr = array(); $whereTypeArr = array();
	if (!empty($hash)) {
		$whereArr['zbsmail_hash'] = $hash;
		$whereTypeArr[] = '%s';
	}
	if (!empty($ID) && $ID > 0) {
		$whereArr['ID'] = $ID;
		$whereTypeArr[] = '%d';
	}

	#} so we only record the first opened the once.
	$data = array( 
		'zbsmail_opened' => $newOpenedCount,
		'zbsmail_lastopened' => time()
	);

	$data_format = 		  array(
		'%d',
		'%d'
	);




	if($firstOpen == 0){
		$data['zbsmail_firstopened'] = time();
		$data_format[] = '%d';
	}

	if (count($whereArr) > 0){


	/*

	$wpdb->update( 
	'table', 
	array( 
		'column1' => 'value1',	// string
		'column2' => 'value2'	// integer (number) 
	), 
	array( 'ID' => 1 ), 
	array( 
		'%s',	// value1
		'%d'	// value2
	), 
	array( '%d' ) 
);

	*/

	//this had a bug in the whereArr and data_format were the wrong way around (to check in core branch)
		$rows_updated = $wpdb->update($ZBSCRM_t['system_mail_hist'],
		  $data, 
		  $whereArr,
		  $data_format,
		  $whereTypeArr
		);

		if($rows_updated){
			return true;
		}

	}

	return false;
}








function zeroBSCRM_get_email_history($page=0, $limit=50, $userID = -1, $type='', $email=-1, $thread=false, $threadID = -1, $star=false, $justCust = true){
	global $wpdb, $ZBSCRM_t;

	$order = 'DESC';
	$extra_sql = "";
	if($userID > 0){
		$extra_sql = $wpdb->prepare(" WHERE zbsmail_target_objid = %d",  $userID);
	}
	if($type != ''){
		#} this is our inboxes condition here for zbsmail_target_objid > 0
		$extra_sql = $wpdb->prepare(" WHERE zbsmail_status = %s AND zbsmail_target_objid >= 0 AND zbsmail_sender_wpid >= 0",  $type);
	}
	if($email > 0){
		$extra_sql = $wpdb->prepare(" WHERE ID = %d",  $email);
	}
	if($thread){
		$extra_sql .= " GROUP BY zbsmail_sender_thread";
	}
	if($threadID >= 0){
		$extra_sql = $wpdb->prepare(" WHERE zbsmail_sender_thread = %d",  $threadID);
	}
	if($threadID >= 0 && $userID > 0){
		$extra_sql = $wpdb->prepare(" WHERE zbsmail_target_objid = %d AND zbsmail_sender_thread = %d", $userID, $threadID);	
		$order = 'ASC';
	}
	if($star && $thread){
		$extra_sql = " WHERE zbsmail_starred = 1 GROUP BY zbsmail_sender_thread";
	}
	$limit = (int)$limit; $page = (int)$page;
	$sql = "SELECT * FROM " . $ZBSCRM_t['system_mail_hist'] . $extra_sql . " ORDER BY zbsmail_created ".$order." LIMIT $limit OFFSET $page";
	

	$r = $wpdb->get_results($sql);



	return $r;
}

function zeroBSCRM_mailDelivery_getTemplateStats($ID){
	//get our stats...
	global $wpdb, $ZBSCRM_t;
	$sql = $wpdb->prepare("SELECT count(ID) as zbs_sent, count(zbsmail_opened) as zbs_opened, count(zbsmail_clicked) as zbs_clicked, sum(zbsmail_opened) as total_opens, sum(zbsmail_clicked) as total_clicks FROM " . $ZBSCRM_t['system_mail_hist'] . " WHERE zbsmail_type = %d", $ID);
	$r = $wpdb->get_results($sql);

	if(is_array($r) && $r[0]->zbs_sent > 0){
		$sent = $r[0]->zbs_sent;
		$open = round(100*($r[0]->zbs_opened / $r[0]->zbs_sent),0);
		$click = round(100*($r[0]->zbs_clicked / $r[0]->zbs_sent),0);
		esc_html_e($sent . " SENT, ".$open."% OPENED", 'zero-bs-crm');
	}else{
		esc_html_e("0 SENT, 0% OPENED", 'zero-bs-crm');		
	}
}

function zeroBSCRM_get_email_status($ID){
	//gets whether an email is active or not (i.e. client portal)
	global $wpdb, $ZBSCRM_t;
	$sql = $wpdb->prepare("SELECT zbsmail_active FROM " . $ZBSCRM_t['system_mail_templates'] . " WHERE zbsmail_id = %d", $ID);
	$r = $wpdb->get_results($sql);

	if ( isset( $r[0] ) && $r[0]->zbsmail_active == 1 ) { // phpcs:ignore Universal.Operators.StrictComparisons.LooseEqual -- strict comparison breaks functionality.
		return true;
	}else{
		return false;
	}
}

//gets an email line based on a passed hash
function zeroBSCRM_mailTracking_getEmailFromHash($hash=''){
	
	global $wpdb, $ZBSCRM_t;
	$sql = $wpdb->prepare("SELECT * FROM " . $ZBSCRM_t['system_mail_hist'] . " WHERE zbsmail_hash = %s", $hash);
	$r = $wpdb->get_row($sql,'ARRAY_A');

	if (is_array($r)) return $r;

	return false;

}


/* ======================================================
  / Email tracking functions
   ====================================================== */
