<?php 
/**
* @version		$Id: messages.helper.php 2587 2024-08-13 15:46:55Z IOS $
* @package		Elxis
* @subpackage	Helpers
* @copyright	Copyright (c) 2006-2024 Elxis CMS (https://www.elxis.org). All rights reserved.
* @license		Elxis Public License ( https://www.elxis.org/elxis-public-license.html )
* @author		Elxis Team ( https://www.elxis.org )
* @description 	Elxis CMS is free software. Read the license for copyright notices and details
*/

defined('_ELXIS_') or die ('Direct access to this location is not allowed');


class elxisMessagesHelper {


	private $total_threads = 0;


	/***************/
	/* CONSTRUCTOR */
	/***************/
	public function __construct() {
	}
	

	/******************/
	/* COUNT MESSAGES */
	/******************/
	public function countMessages($options=array()) {
		$db = eFactory::getDB();

		if (!is_array($options)) { $options = array(); }

		$binds = $this->bindOptions($options);

		$sql = "SELECT COUNT(".$db->quoteId('id').") FROM ".$db->quoteId('#__messages');
		if ($binds) {
			foreach ($binds as $k => $bind) {
				if ($k == 0) {
					$sql .= ' WHERE '.$db->quoteId($bind[0]).' '.$bind[1].' :xval'.$k."\n";
				} else {
					$sql .= ' AND '.$db->quoteId($bind[0]).' '.$bind[1].' :xval'.$k."\n";
				}
			}
		}
		$stmt = $db->prepare($sql);
		if ($binds) {
			foreach ($binds as $k => $bind) {
				$stmt->bindParam(':xval'.$k, $bind[2], $bind[3]);
			}
		}
		$stmt->execute();
		$num = (int)$stmt->fetchResult();

		return $num;
	}


	/****************/
	/* GET MESSAGES */
	/****************/
	public function getMessages($options=array()) {
		$db = eFactory::getDB();

		if (!is_array($options)) { $options = array(); }

		$binds = $this->bindOptions($options);

		$orderby = isset($options['orderby']) ? trim($options['orderby']) : 'created';
		$orderdir = isset($options['orderdir']) ? strtoupper($options['orderdir']) : 'DESC';
		$limit = isset($options['limit']) ? (int)$options['limit'] : 0;
		$limitstart = isset($options['limitstart']) ? (int)$options['limitstart'] : 0;
		$userdata = isset($options['userdata']) ? (int)$options['userdata'] : 0;
		if ($orderby == '') { $orderby = 'created'; }
		if ($orderdir != 'ASC') { $orderdir = 'DESC'; }

		$sql = "SELECT * FROM ".$db->quoteId('#__messages');
		if ($binds) {
			foreach ($binds as $k => $bind) {
				if ($k == 0) {
					$sql .= ' WHERE '.$db->quoteId($bind[0]).' '.$bind[1].' :xval'.$k."\n";
				} else {
					$sql .= ' AND '.$db->quoteId($bind[0]).' '.$bind[1].' :xval'.$k."\n";
				}
			}
		}
		$sql .= ' ORDER BY '.$db->quoteId($orderby).' '.$orderdir;
		if ($limit > 0) {
			$stmt = $db->prepareLimit($sql, $limitstart, $limit);
		} else {
			$stmt = $db->prepare($sql);
		}
		if ($binds) {
			foreach ($binds as $k => $bind) {
				$stmt->bindParam(':xval'.$k, $bind[2], $bind[3]);
			}
		}

		$stmt->execute();
		$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
		if (!$rows) { return false; }

		$convertlinks = isset($options['convertlinks']) ? (int)$options['convertlinks'] : 1;
		if ($convertlinks) {
			$elxis = eFactory::getElxis();

			foreach ($rows as $i => $row) {
				//convert URLs to links
				$rows[$i]->message = preg_replace("~[[:alpha:]]+://[^<>[:space:]]+[[:alnum:]/]~", "<a href=\"\\0\" target=\"_blank\">\\0</a>", $row->message);

				//convert Elxis URIs to links
				preg_match_all('/(#elink:[a-z0-9\:\.\_\-\/]+)/i', $row->message, $matches, PREG_PATTERN_ORDER);
				if ($matches) {
					foreach ($matches[0] as $k => $match) {
						$elxis_uri = preg_replace('/^(\#elink\:)/i', '', $match);
						$url = $elxis->makeURL($elxis_uri);
						$parts = preg_split('@\/@', $url, -1, PREG_SPLIT_NO_EMPTY);
						$c = count($parts) - 1;
						$href = '<a href="'.$url.'" target="_blank">'.$parts[$c].'</a>';
						$rows[$i]->message = str_replace($matches[0][$k], $href, $rows[$i]->message);
					}
				}
			}
		}

		$userdata = isset($options['userdata']) ? (int)$options['userdata'] : 0;

		if ($userdata != 1) { return $rows; }

		$uids = array();
		foreach ($rows as $row) {
			if ($row->fromid > 0) { $uids[] = $row->fromid; }
			if ($row->toid > 0) { $uids[] = $row->toid; }
		}
		if (!$uids) { return $rows; }
		$uids = array_unique($uids);

		$sql = "SELECT ".$db->quoteId('uid').", ".$db->quoteId('uname').", ".$db->quoteId('avatar').", ".$db->quoteId('email')
		."\n FROM ".$db->quoteId('#__users');
		if (count($uids) == 1) {
			$sql .= "\n WHERE ".$db->quoteId('uid').' = '.$uids[0];
		} else {
			$sql .= "\n WHERE ".$db->quoteId('uid')." IN (".implode(', ', $uids).")";
		}
		$stmt = $db->prepare($sql);
		$stmt->execute();
		$users = $stmt->fetchAllAssoc('uid', PDO::FETCH_ASSOC);
		if (!$users) { $users = array(); }

		foreach ($rows as $i => $row) {
			$toid = $row->toid;
			$fromid = $row->fromid;
			$rows[$i]->to_uname = '';
			$rows[$i]->to_avatar = '';
			$rows[$i]->to_email = '';
			$rows[$i]->from_uname = '';
			$rows[$i]->from_avatar = '';
			$rows[$i]->from_email = '';
			if ($toid > 0) {
				if (isset($users[$toid])) {
					$rows[$i]->to_uname = $users[$toid]['uname'];
					$rows[$i]->to_avatar = $users[$toid]['avatar'];
					$rows[$i]->to_email = $users[$toid]['email'];
				}
			}
			if ($fromid > 0) {
				if (isset($users[$fromid])) {
					$rows[$i]->from_uname = $users[$fromid]['uname'];
					$rows[$i]->from_avatar = $users[$fromid]['avatar'];
					$rows[$i]->from_email = $users[$fromid]['email'];
				}
			}
		}

		return $rows;
	}


	/*****************************/
	/* BIND OPTIONS TO SQL QUERY */
	/*****************************/
	private function bindOptions($options) {
		$binds = array();

		if (!is_array($options) || (count($options) == 0)) { return $binds; }

		foreach ($options as $k => $v) {
			switch ($k) {
				case 'id':
				case 'fromid':
				case 'toid':
				case 'read':
				case 'replyto':
					$v = (int)$v;
					if ($v > -1) { $binds[] = array($k, '=', $v, PDO::PARAM_INT); }
				break;
				case 'fromname':
				case 'toname':
				case 'msgtype':
					$v = eUTF::trim($v);
					if ($v != '') { $binds[] = array($k, '=', $v, PDO::PARAM_STR); }
				break;
				case 'message':
				case 'created':
					$v = eUTF::trim($v);
					if ($v != '') {
						$v2 = '%'.$v.'%';
						$binds[] = array($k, 'LIKE', $v2, PDO::PARAM_STR); 
					}
				break;
				default: break;
			}
		}

		return $binds;
	}


	/************************/
	/* GET MESSAGES THREADS */
	/************************/
	public function getThreads($uid, $limitstart=0, $limit=10, $convertlinks=false) {
		$db = eFactory::getDB();

		$this->total_threads = 0;

		$sql = "SELECT * FROM ".$db->quoteId('#__messages')." WHERE ".$db->quoteId('fromid')." = :xuid OR ".$db->quoteId('toid')." = :xuid"
		."\n ORDER BY ".$db->quoteId('created')." DESC";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':xuid', $uid, PDO::PARAM_INT);
		$stmt->execute();
		$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
		if (!$rows) { return array(); }

		$threads = array();
		//first pass, create threads
		foreach ($rows as $k => $row) {
			if ($row->replyto == 0) {
				$id = (int)$row->id;
				if (($row->fromid == $uid) && ($row->delbyfrom == 1)) {//thread deleted by original sender
					unset($rows[$k]);
					continue;
				}
				if (($row->toid == $uid) && ($row->delbyto == 1)) {//thread deleted by original recipient
					unset($rows[$k]);
					continue;
				}
				$threads[$id] = $row;
				unset($rows[$k]);
			}
		}

		//second pass, get latest message on each thread
		if ($rows) {
			foreach ($rows as $row) {
				$id = (int)$row->replyto;
				if ($id == 0) { continue; } //just in case
				if (!isset($threads[$id])) { continue; }
				if ($row->created > $threads[$id]->created) { $threads[$id] = $row; }
			}
		}

		if (!$threads) { return array(); }

		$this->total_threads = count($threads);

		//re-order threads based on latest reply date
		if ($this->total_threads > 1) {
			uasort($threads, array($this, 'orderThreadsByDate'));
		}

		if ($limit > 0) {
			$newthreads = array();
			$i = 0;
			$end = $limitstart + $limit;
			foreach($threads as $id => $thread) {
				if ($i < $limitstart) { continue; }
				if ($i >= $end) { break; }
				$newthreads[$id] = $thread;
				$i++;
			}
			$threads = $newthreads;
			unset($newthreads);

			if (!$threads) { return array(); }
		}

		$uids = array();
		foreach ($threads as $thread) {
			if ($thread->fromid > 0) { $uids[] = $thread->fromid; }
			if ($thread->toid > 0) { $uids[] = $thread->toid; }
		}

		$users = array();
		if ($uids) {
			$uids = array_unique($uids);
			$sql = "SELECT DISTINCT u.uid, u.uname, u.avatar,u.email, s.last_activity FROM ".$db->quoteId('#__users')." u"
			."\n LEFT JOIN ".$db->quoteId('#__session')." s ON s.uid = u.uid";
			if (count($uids) == 1) {
				$sql .= "\n WHERE u.uid = ".$uids[0];
			} else {
				$sql .= "\n WHERE u.uid IN (".implode(', ', $uids).")";
			}
			$stmt = $db->prepare($sql);
			$stmt->execute();
			$users = $stmt->fetchAllAssoc('uid', PDO::FETCH_ASSOC);
			if (!$users) { $users = array(); }
		}

		foreach ($threads as $id => $thread) {
			$toid = $thread->toid;
			$fromid = $thread->fromid;
			$threads[$id]->from_uname = '';
			$threads[$id]->from_avatar = '';
			$threads[$id]->from_email = '';
			$threads[$id]->from_last_activity = 0;
			$threads[$id]->to_uname = '';
			$threads[$id]->to_avatar = '';
			$threads[$id]->to_email = '';
			$threads[$id]->to_last_activity = 0;
			if ($fromid > 0) {
				if (isset($users[$fromid])) {
					$threads[$id]->from_uname = $users[$fromid]['uname'];
					$threads[$id]->from_avatar = $users[$fromid]['avatar'];
					$threads[$id]->from_email = $users[$fromid]['email'];
					$threads[$id]->from_last_activity = (int)$users[$fromid]['last_activity'];
				}
			}
			if ($toid > 0) {
				if (isset($users[$toid])) {
					$threads[$id]->to_uname = $users[$toid]['uname'];
					$threads[$id]->to_avatar = $users[$toid]['avatar'];
					$threads[$id]->to_email = $users[$toid]['email'];
					$threads[$id]->to_last_activity = (int)$users[$toid]['last_activity'];
				}
			}

		}

		if ($convertlinks) {
			$elxis = eFactory::getElxis();

			foreach ($threads as $id => $thread) {
				//convert URLs to links
				$threads[$id]->message = preg_replace("~[[:alpha:]]+://[^<>[:space:]]+[[:alnum:]/]~", "<a href=\"\\0\" target=\"_blank\">\\0</a>", $thread->message);

				//convert Elxis URIs to links
				preg_match_all('/(#elink:[a-z0-9\:\.\_\-\/]+)/i', $thread->message, $matches, PREG_PATTERN_ORDER);
				if ($matches) {
					foreach ($matches[0] as $k => $match) {
						$elxis_uri = preg_replace('/^(\#elink\:)/i', '', $match);
						$url = $elxis->makeURL($elxis_uri);
						$parts = preg_split('@\/@', $url, -1, PREG_SPLIT_NO_EMPTY);
						$c = count($parts) - 1;
						$href = '<a href="'.$url.'" target="_blank">'.$parts[$c].'</a>';
						$threads[$id]->message = str_replace($matches[0][$k], $href, $threads[$id]->message);
					}
				}
			}
		}

		return $threads;
	}


	/**************/
	/* GET THREAD */
	/**************/
	public function getThread($threadid) {
		$elxis = eFactory::getElxis();
		$db = eFactory::getDB();

		$sql = "SELECT * FROM ".$db->quoteId('#__messages')." WHERE ".$db->quoteId('id')." = :xid OR ".$db->quoteId('replyto')." = :xid"
		."\n ORDER BY ".$db->quoteId('created')." ASC";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':xid', $threadid, PDO::PARAM_INT);
		$stmt->execute();
		$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
		if (!$rows) { return array(); }

		$uids = array();
		foreach ($rows as $row) {
			if ($row->fromid > 0) { $uids[] = $row->fromid; }
			if ($row->toid > 0) { $uids[] = $row->toid; }
		}

		$users = array();
		if ($uids) {
			$uids = array_unique($uids);
			$sql = "SELECT DISTINCT u.uid, u.uname, u.avatar,u.email, s.last_activity FROM ".$db->quoteId('#__users')." u"
			."\n LEFT JOIN ".$db->quoteId('#__session')." s ON s.uid = u.uid";
			if (count($uids) == 1) {
				$sql .= "\n WHERE u.uid = ".$uids[0];
			} else {
				$sql .= "\n WHERE u.uid IN (".implode(', ', $uids).")";
			}
			$stmt = $db->prepare($sql);
			$stmt->execute();
			$users = $stmt->fetchAllAssoc('uid', PDO::FETCH_ASSOC);
			if (!$users) { $users = array(); }
		}

		foreach ($rows as $k => $row) {
			$toid = $row->toid;
			$fromid = $row->fromid;
			$rows[$k]->from_uname = '';
			$rows[$k]->from_avatar = '';
			$rows[$k]->from_email = '';
			$rows[$k]->from_last_activity = 0;
			$rows[$k]->to_uname = '';
			$rows[$k]->to_avatar = '';
			$rows[$k]->to_email = '';
			$rows[$k]->to_last_activity = 0;
			if ($fromid > 0) {
				if (isset($users[$fromid])) {
					$rows[$k]->from_uname = $users[$fromid]['uname'];
					$rows[$k]->from_avatar = $users[$fromid]['avatar'];
					$rows[$k]->from_email = $users[$fromid]['email'];
					$rows[$k]->from_last_activity = (int)$users[$fromid]['last_activity'];
				}
			}
			if ($toid > 0) {
				if (isset($users[$toid])) {
					$rows[$k]->to_uname = $users[$toid]['uname'];
					$rows[$k]->to_avatar = $users[$toid]['avatar'];
					$rows[$k]->to_email = $users[$toid]['email'];
					$rows[$k]->to_last_activity = (int)$users[$toid]['last_activity'];
				}
			}

			//convert URLs to links
			$rows[$k]->message = preg_replace("~[[:alpha:]]+://[^<>[:space:]]+[[:alnum:]/]~", "<a href=\"\\0\" target=\"_blank\">\\0</a>", $row->message);
			//convert Elxis URIs to links
			preg_match_all('/(#elink:[a-z0-9\:\.\_\-\/]+)/i', $row->message, $matches, PREG_PATTERN_ORDER);
			if ($matches) {
				foreach ($matches[0] as $k => $match) {
					$elxis_uri = preg_replace('/^(\#elink\:)/i', '', $match);
					$url = $elxis->makeURL($elxis_uri);
					$parts = preg_split('@\/@', $url, -1, PREG_SPLIT_NO_EMPTY);
					$c = count($parts) - 1;
					$href = '<a href="'.$url.'" target="_blank">'.$parts[$c].'</a>';
					$rows[$k]->message = str_replace($matches[0][$k], $href, $rows[$k]->message);
				}
			}
		}

		return $rows;
	}


	/***********************/
	/* NEWER THREADS FIRST */
	/***********************/
	private function orderThreadsByDate($a, $b) {
		if ($a->created == $b->created) { return 0; }
		return ($a->created < $b->created) ? 1 : -1;
	}


	/*******************************************************/
	/* GET TOTAL THREADS AS COUNTED IN METHOD "getThreads" */
	/*******************************************************/
	public function getTotalThreads() {
		return $this->total_threads;
	}


	/*******************************/
	/* COUNT USER'S TOTAL MESSAGES */
	/*******************************/
	public function getTotalMessages($uid) {
		$db = eFactory::getDB();

		$sql = "SELECT COUNT(".$db->quoteId('id').") FROM ".$db->quoteId('#__messages')
		."\n WHERE (".$db->quoteId('fromid')." = :xuid AND ".$db->quoteId('delbyfrom')." = 0) OR (".$db->quoteId('toid')." = :xuid AND ".$db->quoteId('delbyto')." = 0)";
		$stmt = $db->prepareLimit($sql, 0, 1);
		$stmt->bindParam(':xuid', $uid, PDO::PARAM_INT);
		$stmt->execute();
		$num = (int)$stmt->fetchResult();

		return $num;
	}


	/**************************/
	/* DELETE MESSAGES THREAD */
	/**************************/
	public function deleteThread($threadid, $is_sender) {
		$db = eFactory::getDB();

		$threadid = (int)$threadid;
		if ($threadid < 1) { return; }

		if ($is_sender == 1) {
			$sql = "UPDATE ".$db->quoteId('#__messages')." SET ".$db->quoteId('delbyfrom')." = 1"
			."\n WHERE (".$db->quoteId('id')." = :xid AND ".$db->quoteId('replyto')." = 0) OR (".$db->quoteId('replyto')." = :xid)";
		} else if ($is_sender == 0) {
			$sql = "UPDATE ".$db->quoteId('#__messages')." SET ".$db->quoteId('read')." = 1, ".$db->quoteId('delbyto')." = 1"
			."\n WHERE (".$db->quoteId('id')." = :xid AND ".$db->quoteId('replyto')." = 0) OR (".$db->quoteId('replyto')." = :xid)";
		} else {
			return;
		}
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':xid', $threadid, PDO::PARAM_INT);
		$stmt->execute();

		$this->cleanUP();
	}


	/****************************/
	/* CLEANUP DELETED MESSAGES */
	/****************************/
	public function cleanUP() {
		$db = eFactory::getDB();

		$sql = "DELETE FROM ".$db->quoteId('#__messages')." WHERE ".$db->quoteId('delbyfrom')." = 1 AND ".$db->quoteId('delbyto')." = 1";
		$stmt = $db->prepare($sql);
		$stmt->execute();

		$sql = "DELETE FROM ".$db->quoteId('#__messages')." WHERE ".$db->quoteId('delbyto')." = 1 AND ".$db->quoteId('fromid')." = 0";
		$stmt = $db->prepare($sql);
		$stmt->execute();

		$sql = "DELETE FROM ".$db->quoteId('#__messages')." WHERE ".$db->quoteId('delbyfrom')." = 1 AND ".$db->quoteId('toid')." = 0";
		$stmt = $db->prepare($sql);
		$stmt->execute();
	}


	/************************/
	/* COUNT USER'S THREADS */
	/************************/
	public function countTotalThreads($uid) {
		$db = eFactory::getDB();

		$sql = "SELECT COUNT(id) FROM ".$db->quoteId('#__messages')
		."\n WHERE (".$db->quoteId('fromid')." = :xuid OR ".$db->quoteId('toid')." = :xuid) AND ".$db->quoteId('replyto')." = 0"; 
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':xuid', $uid, PDO::PARAM_INT);
		$stmt->execute();
		$total_threads = (int)$stmt->fetchResult();
		$total_threads = $stmt->fetchResult();
		return $total_threads;
	}


	/*********************************/
	/* MARK THREAD AS READ OR UNREAD */
	/*********************************/
	public function markThreadRead($threadid, $uid, $read=1) {
		$db = eFactory::getDB();

		$read = (int)$read;
		$sql = "UPDATE ".$db->quoteId('#__messages')." SET ".$db->quoteId('read')." = :xread"
		."\n WHERE (".$db->quoteId('id')." = :xid OR ".$db->quoteId('replyto')." = :xid) AND ".$db->quoteId('toid')." = :xuid";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':xread', $read, PDO::PARAM_INT);
		$stmt->bindParam(':xid', $threadid, PDO::PARAM_INT);
		$stmt->bindParam(':xuid', $uid, PDO::PARAM_INT);
		$stmt->execute();
	}

}

?>