<?php
namespace JExtstore\Component\JRealtimeAnalytics\Administrator\Model;
/** 
 * @package JREALTIMEANALYTICS::SERVERSTATS::administrator::components::com_jrealtimeanalytics
 * @subpackage models
 * @author Joomla! Extensions Store
 * @copyright (C) 2013 - Joomla! Extensions Store
 * @license GNU/GPLv2 http://www.gnu.org/licenses/gpl-2.0.html 
 */
defined ( '_JEXEC' ) or die ( 'Restricted access' );
use Joomla\CMS\MVC\Factory\MVCFactoryInterface;
use Joomla\CMS\HTML\HTMLHelper;
use Joomla\CMS\Language\Text;
use Joomla\CMS\Uri\Uri;
use Joomla\String\StringHelper;
use JExtstore\Component\JRealtimeAnalytics\Administrator\Framework\Http;
use JExtstore\Component\JRealtimeAnalytics\Administrator\Framework\Http\Transport\Socket;
use JExtstore\Component\JRealtimeAnalytics\Administrator\Framework\Http\Transport\Curl;
use JExtstore\Component\JRealtimeAnalytics\Administrator\Framework\Model as JRealtimeModel;
use JExtstore\Component\JRealtimeAnalytics\Administrator\Framework\Exception as JRealtimeException;

/**
 * Server stats model responsibilities contract
 *
 * @package JREALTIMEANALYTICS::SERVERSTATS::administrator::components::com_jrealtimeanalytics
 * @subpackage models
 * @since 1.0
 */
interface IServerstatsModel {
	/**
	 * Dependency injection setter del graph instance object generator
	 *
	 * @param Object& $graphInstance
	 * @access public
	 * @return void
	 */
	public function setGraphRenderer(&$graphInstance);
	
	/**
	 * Get geolocation translations from DB
	 *
	 * @access public
	 * @return array[] &
	 */
	public function &getGeoTranslations();

	/**
	 * Main fetch data method already populated
	 *
	 * @access public
	 * @return Object[]
	 */
	public function fetchData(); 
	
	/**
	 * Secondary get data method to obtain only counters data used for example for the module view
	 *
	 * @access public
	 * @return mixed Array of data if successful or an exception on errors
	 */
	public function getDataCounters();
	
	/**
	 * Load details entity
	 *
	 * @access public
	 * @param string $identifier
	 * @param string $detailType
	 * @return Object[]
	 */
	public function loadStatsEntity($identifier, $detailType);
}

/**
 * Main global data stats model <<testable_behavior>>
 * 
 * @package JREALTIMEANALYTICS::SERVERSTATS::administrator::components::com_jrealtimeanalytics
 * @subpackage models
 * @since 1.0
 */
class ServerstatsModel extends JRealtimeModel implements IServerstatsModel {
	/**
	 * Data inizio periodo statistiche
	 *
	 * @access private
	 * @var string
	 */
	private $intervalFrom;
	
	/**
	 * Data termine periodo statistiche
	 *
	 * @access private
	 * @var string
	 */
	private $intervalTo;
	
	/**
	 * Format of the stats filter, date or datetime
	 *
	 * @access private
	 * @var string
	 */
	private $dateFilterFormat;
	
	/**
	 * Data structure container
	 * 
	 * @access private
	 * @var array 
	 */
	private $data;
	
	/**
	 * Component configuration object
	 *
	 * @access private
	 * @var Object
	 */
	private $cParams;
	
	/**
	 * Graph generator object
	 * 
	 * @access private
	 * @var Object
	 */
	private $graphGenerator;
	
	/**
	 * Query snippet WHERE in cui tutto viene filtrato in base al periodo
	 *
	 * @access private
	 * @var string
	 */
	private $whereQuery;
	
	/**
	 * Maps the requested reports to the user choice
	 *
	 * @access private
	 * @var array
	 */
	private $mappingReports;

	/** 
	 * Numero di visite GROUPED BY visitedpage
	 * 
	 * @access private
	 * @return array
	 */
	private function visitsPerPage() {
		$query = "SELECT COUNT(*) AS numvisits, MAX(". $this->dbInstance->quoteName('visit_timestamp') . "), " . $this->dbInstance->quoteName('visitedpage') .  
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('visitedpage') .
				 "\n ORDER BY numvisits DESC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		return $results;
	}

	/**
	 * Numero di pagine visitate complessivamente nel periodo
	 * 
	 * @access private
	 * @return int 
	 */
	private function totalVisitedPages() {
		$query = "SELECT COUNT(*)" .  
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery;
		$this->dbInstance->setQuery($query);
		$result = (int) $this->dbInstance->loadResult();
		
		return $result;
	}

	/**
	 * Array contenente il numero di pagine, last visit date, browser, os,  GROUP BY session_id_person
	 * 
	 * @access private
	 * @return array 
	 */
	private function totalVisitedPagesPerUser() {
		// Init empty vars
		$selectReferral = null;
		$selectReferReferral = null;
		$leftJoinReferral = null;
		
		$selectEmail = null;
		$selectUsrEmail = null;
		$leftJoinEmail = null;
		
		if($this->cParams->get('show_usergroup', 0)) {
			// Parametric referral JOIN
			if($this->cParams->get('show_referral', 0)) {
				$selectReferral = "," . $this->dbInstance->quoteName('referral');
				$selectReferReferral = 'refer.referral,';
				$leftJoinReferral = "\n LEFT JOIN" .
									"\n (SELECT " .  $this->dbInstance->quoteName('session_id_person') . ", " . $this->dbInstance->quoteName('referral') .
									"\n FROM " .  $this->dbInstance->quoteName('#__realtimeanalytics_referral') . 
									"\n WHERE " .  $this->dbInstance->quoteName('record_date') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
			  						"\n AND " .  $this->dbInstance->quoteName('record_date') . " <= " . $this->dbInstance->quote( $this->intervalTo ) . 
			  						"\n GROUP BY " .  $this->dbInstance->quoteName('session_id_person') . ") refer" .
				 					"\n ON sess.session_id_person = refer.session_id_person";
			}
			
			// Parametric referral JOIN
			if($this->cParams->get('show_email_address', 0)) {
				$selectEmail = "," . $this->dbInstance->quoteName('email');
				$selectUsrEmail = "usr.email,";
				$leftJoinEmail = "\n LEFT JOIN" . $this->dbInstance->quoteName('#__users') . " AS " . $this->dbInstance->quoteName('usr') .
								 "\n ON sess.user_id_person = usr.id";
			}
			
			$query = "SELECT COUNT(*), " . $this->dbInstance->quoteName('customer_name') . "," .
					 "\n MAX(". $this->dbInstance->quoteName('visit_timestamp') . ")," .
					 $this->dbInstance->quoteName('browser') . "," .
					 $this->dbInstance->quoteName('os') . "," .
					 $this->dbInstance->quoteName('session_id_person') . "," .
					 $this->dbInstance->quoteName('ip') . "," .
					 "\n SUM(" . $this->dbInstance->quoteName('impulse') . ")," .
					 $this->dbInstance->quoteName('geolocation') . "," .
					 $this->dbInstance->quoteName('device') . "," .
					 $this->dbInstance->quoteName('usergroup') .
					 $selectReferral .
					 $selectEmail .
					 "\n FROM ( SELECT sess.*, $selectReferReferral $selectUsrEmail userg.title AS usergroup FROM #__realtimeanalytics_serverstats AS sess" .
					 $leftJoinReferral .
					 $leftJoinEmail .
					 "\n LEFT JOIN #__users AS users" .
					 "\n ON sess.user_id_person = users.id" .
					 "\n LEFT JOIN (SELECT user_id, MAX(group_id) AS group_id FROM #__user_usergroup_map GROUP BY user_id) map" .
					 "\n ON map.user_id = users.id" .
					 "\n LEFT JOIN #__usergroups AS userg" .
					 "\n ON map.group_id = userg.id" .
					 $this->whereQuery . " ORDER BY sess.visit_timestamp DESC) AS INTABLE" .
					 $this->whereQuery .
					 "\n GROUP BY " . $this->dbInstance->quoteName('session_id_person') .
					 "\n ORDER BY " . $this->dbInstance->quoteName('customer_name');
		} else {
			// Parametric referral JOIN
			if($this->cParams->get('show_referral', 0)) {
				$selectReferral = "," . $this->dbInstance->quoteName('referral');
				$selectReferReferral = ', refer.referral';
				$leftJoinReferral = "\n LEFT JOIN" .
									"\n (SELECT " .  $this->dbInstance->quoteName('session_id_person') . ", " . $this->dbInstance->quoteName('referral') .
									"\n FROM " .  $this->dbInstance->quoteName('#__realtimeanalytics_referral') . 
									"\n WHERE " .  $this->dbInstance->quoteName('record_date') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
			  						"\n AND " .  $this->dbInstance->quoteName('record_date') . " <= " . $this->dbInstance->quote( $this->intervalTo ) . 
			  						"\n GROUP BY " .  $this->dbInstance->quoteName('session_id_person') . ") refer" .
				 					"\n ON sess.session_id_person = refer.session_id_person";
			}
			
			// Parametric referral JOIN
			if($this->cParams->get('show_email_address', 0)) {
				$selectEmail =  "," . $this->dbInstance->quoteName('email');
				$selectUsrEmail = ", usr.email";
				$leftJoinEmail = "\n LEFT JOIN" . $this->dbInstance->quoteName('#__users') . " AS " . $this->dbInstance->quoteName('usr') .
								 "\n ON sess.user_id_person = usr.id";
			}
			
			$query = "SELECT COUNT(*), " . $this->dbInstance->quoteName('customer_name') . "," .
					 "\n MAX(". $this->dbInstance->quoteName('visit_timestamp') . ")," .
					 $this->dbInstance->quoteName('browser') . "," .
					 $this->dbInstance->quoteName('os') . "," .
					 $this->dbInstance->quoteName('session_id_person') . "," .
					 $this->dbInstance->quoteName('ip') . "," .
					 "\n SUM(" . $this->dbInstance->quoteName('impulse') . ")," .
					 $this->dbInstance->quoteName('geolocation') . "," .
					 $this->dbInstance->quoteName('device') .
					 $selectReferral .
					 $selectEmail .
					 "\n FROM ( SELECT sess.* $selectReferReferral $selectUsrEmail FROM #__realtimeanalytics_serverstats AS sess" .
					 $leftJoinReferral .
					 $leftJoinEmail .
					 $this->whereQuery . " ORDER BY sess.visit_timestamp DESC) AS INTABLE" .
					 $this->whereQuery .
					 "\n GROUP BY " . $this->dbInstance->quoteName('session_id_person') .
					 "\n ORDER BY " . $this->dbInstance->quoteName('customer_name');
		}
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		return $results;
	}
	
	/**
	 * Array contenente il numero di pagine, last visit date, browser, os,  GROUP BY session_id_person
	 *
	 * @access private
	 * @return array
	 */
	private function totalVisitedPagesPerIPAddress() {
		$query = "SELECT COUNT(*), " .
				 "\n MAX(". $this->dbInstance->quoteName('visit_timestamp') . ")," .
				 $this->dbInstance->quoteName('ip') . "," .
				 "\n SUM(" . $this->dbInstance->quoteName('impulse') . ")," .
				 $this->dbInstance->quoteName('geolocation') .
				 "\n FROM ( SELECT * FROM #__realtimeanalytics_serverstats " . $this->whereQuery . " ORDER BY " . $this->dbInstance->quoteName('visit_timestamp') . " DESC) AS INTABLE" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('ip') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('ip');
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}
	
	/**
	 * Numero di utenti totali DISTINCT per il periodo
	 *
	 * @access private
	 * @return int
	 */
	private function totalVisitors() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))" .  
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery;
		$this->dbInstance->setQuery($query);
		$result = (int)$this->dbInstance->loadResult();
		
		return $result;
	}
	
	/**
	 * Numero di utenti totali DISTINCT per il periodo e singola pagina
	 *
	 * @access private
	 * @param string $currentPage
	 * @return int
	 */
	private function totalVisitorsByPage($currentPage) {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))" .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n AND " . $this->dbInstance->quoteName('visitedpage') . " = " . $this->dbInstance->quote($currentPage);
		$this->dbInstance->setQuery($query);
		$result = (int)$this->dbInstance->loadResult();
				
		return $result;
	}
	
	/**
	 * Numero di visitatori unici totali IP DISTINCT per il periodo
	 *
	 * @access private
	 * @return int
	 */
	private function totalUniqueVisitors() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('ip') . "))" .
				"\n FROM  #__realtimeanalytics_serverstats" .
				$this->whereQuery;
		$this->dbInstance->setQuery($query);
		$result = (int)$this->dbInstance->loadResult();
	
		return $result;
	}
	
	/**
	 * Calcolo del bounce rate, rapporto tra utenti totali e utenti che hanno visitato una sola pagina
	 *
	 * @access private
	 * @param int $totalVisitors
	 * @return string
	 */
	private function bounceRate($totalVisitors) {
		// Avoid division by zero
		if(!$totalVisitors) {
			return 0;
		}
		
		$query = "SELECT COUNT(*) AS numusers FROM (" .
				  	"\n SELECT COUNT(*) AS " . $this->dbInstance->quoteName('pages') . "," . 
				  	$this->dbInstance->quoteName('impulse') .
				  	"\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') . 
				  	$this->whereQuery .
				  	"\n GROUP BY " . $this->dbInstance->quoteName('session_id_person') .
				 "\n ) AS " . $this->dbInstance->quoteName('innertable') . 
				 "\n WHERE " . $this->dbInstance->quoteName('pages') . " = 1" .
				 "\n AND " . $this->dbInstance->quoteName('impulse') . " <= 5";
		$this->dbInstance->setQuery($query);
		$onepageVisitorsCount = (int)$this->dbInstance->loadResult();
	
		$bounceRate = round (($onepageVisitorsCount / $totalVisitors ) * 100, 2);
	
		return $bounceRate . '%';
	}
	
	/**
	 * Durata media della visita per il singolo utente
	 *
	 * @access private
	 * @return int in secondi
	 */
	private function mediumVisitTime() {
		$query = "SELECT SUM(". $this->dbInstance->quoteName('impulse') . ")".
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('session_id_person');
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		$mediumVisitTime = 0;
		
		if(count($results)) {
			// Ciclo di calcolo per determinare il tempo medio di visita
			$totalVisitedTime = 0;
			$daemonRefresh = $this->cParams->get('daemonrefresh', 2);
			foreach ($results as $result) {
				$totalVisitedTime += $result[0] * $daemonRefresh;
			}
			
			// Media di visita
			$totalVisitors = count($results);
			$mediumVisitTime = (int)$totalVisitedTime / $totalVisitors; 
			$mediumVisitTime = gmdate('H:i:s', (int)$mediumVisitTime);
		}
		
		return $mediumVisitTime;
	}
	
	/**
	 * Numero medio di pagine viste per il singolo utente
	 *
	 * @access private
	 * @return int
	 */
	private function mediumVisitedPagesPerSingleUser() {
		$query = "SELECT COUNT(" . $this->dbInstance->quoteName('visitedpage') . ")," . 
				 "\n COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))" .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery;
		$this->dbInstance->setQuery($query);
		$result = $this->dbInstance->loadRow();
		
		// Calcolo pagine medie viste per utente
		$mediumVisistedPage = 0;
		if($result[0] && $result[1]) {
			$mediumVisistedPage = sprintf('%.2f', $result[0] / $result[1]);
		}
		
		return $mediumVisistedPage;
	}

	/**
	 * Array contenente il numero di utenti GROUP BY geolocation
	 * 
	 * @access private
	 * @return array 
	 */
	private function numUsersGeoGrouped() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . ")) AS numusers," .  
				 $this->dbInstance->quoteName('geolocation') .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('geolocation') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('numusers') . " DESC," .
				 				  $this->dbInstance->quoteName('geolocation') . " ASC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		return $results;
	}

	/**
	 * Array contenente il numero di utenti GROUP BY browser
	 * 
	 * @access private
	 * @return array 
	 */
	private function numUsersBrowserGrouped() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))  AS numusers," .     
				 $this->dbInstance->quoteName('browser') .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('browser') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('numusers') . " DESC," .
				 				  $this->dbInstance->quoteName('browser') . " ASC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		return $results;
	}

	/**
	 * Array contenente il numero di utenti GROUP BY os
	 * 
	 * @access private
	 * @return array 
	 */
	private function numUsersOSGrouped() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))  AS numusers," .    
				 $this->dbInstance->quoteName('os') .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('os') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('numusers') . " DESC," .
				 				  $this->dbInstance->quoteName('os') . " ASC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
		
		return $results;
	}
	
	/**
	 * Array contenente il numero di utenti GROUP BY device family
	 *
	 * @access private
	 * @return array
	 */
	private function numUsersDeviceGrouped() {
		$query = "SELECT COUNT(DISTINCT(" . $this->dbInstance->quoteName('session_id_person') . "))  AS numusers," .
				 $this->dbInstance->quoteName('device') .
				 "\n FROM  #__realtimeanalytics_serverstats" .
				 $this->whereQuery .
				 "\n AND NOT ISNULL(" . $this->dbInstance->quoteName('device') . ") AND " . $this->dbInstance->quoteName('device') . ' != ""' .
				 "\n GROUP BY " . $this->dbInstance->quoteName('device') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('numusers') . " DESC," .
				 $this->dbInstance->quoteName('device') . " ASC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}
	
	/**
	 * Get records count for referral traffic
	 *
	 * @access private
	 * @return array
	 */
	private function numReferral() {
		$whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('record_date') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
					  "\n AND " .  $this->dbInstance->quoteName('record_date') . " <= " . $this->dbInstance->quote( $this->intervalTo );
		
		$query = "SELECT " . 
				 $this->dbInstance->quoteName('referral') . ", " .
				 $this->dbInstance->quoteName('ip') . ", " .
				 "\n COUNT(DISTINCT " . $this->dbInstance->quoteName('ip') . ") AS " . $this->dbInstance->quoteName('ipcounter') . ", " .
				 "\n COUNT(*) AS " . $this->dbInstance->quoteName('counter') . ", " .
				 "\n (SELECT COUNT(*) FROM #__realtimeanalytics_referral " . $whereQuery . ") AS " .  $this->dbInstance->quoteName('totalcounter') .
				 "\n FROM  #__realtimeanalytics_referral" .
				 $whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('referral') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('counter') . " DESC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}
	
	/**
	 * Get records for searches keywords phrase
	 *
	 * @access private
	 * @return array
	 */
	private function searchedPhrase() {
		$whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('record_date') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
					  "\n AND " .  $this->dbInstance->quoteName('record_date') . " <= " . $this->dbInstance->quote( $this->intervalTo );
		
		$query = "SELECT " . $this->dbInstance->quoteName('phrase') . ", " .
				 "\n COUNT(*) AS " . $this->dbInstance->quoteName('counter') . ", " .
				 "\n (SELECT COUNT(*) FROM #__realtimeanalytics_searches " . $whereQuery . ") AS " .  $this->dbInstance->quoteName('totalcounter') .
				 "\n FROM #__realtimeanalytics_searches" .
				 $whereQuery .
				 "\n GROUP BY " . $this->dbInstance->quoteName('phrase') .
				 "\n ORDER BY " . $this->dbInstance->quoteName('counter') . " DESC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}
	
	/**
	 * Classifica delle pagine pił abbandonate, AKA le ultime
	 * pagine viste dagli utenti dalla pił ultima visitata
	 * Leave off page - Necessita di una COUNT GROUPED BY session_id_customer
	 * delle pagine con MAX visit_timestamp
	 *
	 * @access private
	 * @return array
	 */
	private function hitLeaveOffPages() { 
		$query = "SELECT COUNT(" . $this->dbInstance->quoteName('visitedpage') . ") AS mostleaved," . 
				$this->dbInstance->quoteName('visitedpage') .
				"\n FROM  #__realtimeanalytics_serverstats" .
				$this->whereQuery .
				"\n AND " . $this->dbInstance->quoteName('visit_timestamp') . " IN (" .
				"\n SELECT MAX(" . $this->dbInstance->quoteName('visit_timestamp') . ")" .
				"\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') .
				$this->whereQuery .
				"\n	GROUP BY " . $this->dbInstance->quoteName('session_id_person') . ")" . 
				"\n GROUP BY " . $this->dbInstance->quoteName('visitedpage') .
				"\n ORDER BY " . $this->dbInstance->quoteName('mostleaved') . " DESC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}

	/**
	 * Classifica delle landing page, AKA le prime
	 * pagine viste dagli utenti in arrivo 
	 *
	 * @access private
	 * @return array
	 */
	private function hitLandingPages() {
		$query = "SELECT COUNT(" . $this->dbInstance->quoteName('visitedpage') . ") AS mostlanding," .
				$this->dbInstance->quoteName('visitedpage') .
				"\n FROM  #__realtimeanalytics_serverstats" .
				$this->whereQuery .
				"\n AND " . $this->dbInstance->quoteName('visit_timestamp') . " IN (" .
				"\n SELECT MIN(" . $this->dbInstance->quoteName('visit_timestamp') . ")" .
				"\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') .
				$this->whereQuery .
				"\n	GROUP BY " . $this->dbInstance->quoteName('session_id_person') . ")" .
				"\n GROUP BY " . $this->dbInstance->quoteName('visitedpage') .
				"\n ORDER BY " . $this->dbInstance->quoteName('mostlanding') . " DESC";
		$this->dbInstance->setQuery($query);
		$results = $this->dbInstance->loadRowList();
	
		return $results;
	}

	/**
	 * Esplica la generazione delle graph images successivamente richiamate
	 * e visualizzate dalla view templates preposta entro ogni stats box
	 * 
	 * @access private
	 * @return boolean
	 */
	private function graphRender() {   
		$this->graphGenerator->buildBars($this->data);
		$this->graphGenerator->buildPies($this->data, $this->getGeoTranslations());
	}

	/**
	 * Format top rows summary.
	 *
	 * @access private
	 * @param array $rows
	 * @param int $limit
	 * @param int $labelIndex
	 * @param int $valueIndex
	 * @return string
	 */
	private function formatTopRows(array $rows, int $limit, int $labelIndex, int $valueIndex): string {
		if(empty($rows)) {
			return '-';
		}
		
		$summary = array();
		$count = 0;
		foreach($rows as $row) {
			if(!is_array($row) || !isset($row[$labelIndex], $row[$valueIndex])) {
				continue;
			}
			$summary[] = trim((string)$row[$labelIndex]) . ' (' . (float)$row[$valueIndex] . ')';
			$count++;
			if($count >= $limit) {
				break;
			}
		}
		
		return $summary ? implode(', ', $summary) : '-';
	}
	
	/**
	 * Sanitize report content before sending to AI provider.
	 *
	 * @access private
	 * @param string $reportContent
	 * @return string
	 */
	private function isAIErrorResponse($text) {
		$text = trim((string)$text);
		if($text === '') {
			return true;
		}
		return stripos($text, 'AI analysis error:') === 0 || stripos($text, 'API key is missing') !== false;
	}
	
	private function sanitizeAIContent($reportContent) {
		$plainContent = strip_tags((string)$reportContent);
		$plainContent = html_entity_decode($plainContent, ENT_QUOTES | ENT_HTML5, 'UTF-8');
		$plainContent = preg_replace('/\s+/', ' ', $plainContent);
		$plainContent = trim($plainContent);
		if(strlen($plainContent) > 10000) {
			$plainContent = substr($plainContent, 0, 10000);
		}
		return $plainContent;
	}
	
	/**
	 * Build the AI prompt.
	 *
	 * @access private
	 * @param string $sanitizedContent
	 * @param string $periodLabel
	 * @param string $channel
	 * @return string
	 */
	private function buildAIAnalysisPrompt($sanitizedContent, $periodLabel, $channel) {
		$siteName = Uri::root();
		$channelTitle = $channel === 'email' ? 'email report' : 'native analytics dashboard';
		$customTemplate = trim((string)$this->cParams->get('ai_analysis_prompt', ''));
		
		if($customTemplate !== '') {
			if(strpos($customTemplate, '{dataset}') === false) {
				$customTemplate .= "\n\nDataset:\n{dataset}";
			}
			
			return strtr($customTemplate, array(
					'{site_name}' => $siteName,
					'{period_label}' => $periodLabel,
					'{channel_title}' => $channelTitle,
					'{dataset}' => $sanitizedContent
			));
		}
		
		return "You are a senior web analytics consultant. Analyze this $channelTitle data for period \"$periodLabel\" on website \"$siteName\".\n\n" .
		"Output in concise natural language with these sections:\n" .
		"1) Executive summary\n" .
		"2) Key insights (max 5 bullets)\n" .
		"3) Critical issues/anomalies\n" .
		"4) Actionable recommendations (max 5 actions)\n" .
		"5) Suggested KPI focus for next period\n\n" .
		"Dataset:\n" . $sanitizedContent;
	}
	
	/**
	 * Execute AI analysis against selected provider.
	 *
	 * @access private
	 * @param string $prompt
	 * @return string
	 *
	 * @throws \RuntimeException
	 */
	private function requestAIAnalysis($prompt) {
		$provider = trim((string)$this->cParams->get('ai_provider', 'gemini'));
		return $provider === 'openai' ? $this->requestOpenAIAnalysis($prompt) : $this->requestGeminiAnalysis($prompt);
	}
	
	/**
	 * Request analysis to OpenAI Chat Completions API.
	 *
	 * @access private
	 * @param string $prompt
	 * @return string
	 */
	private function requestOpenAIAnalysis($prompt) {
		$apiKey = trim((string)$this->cParams->get('ai_openai_api_key', ''));
		if(!$apiKey) {
			throw new \RuntimeException(Text::_('COM_JREALTIME_AI_OPENAI_MISSING_APIKEY'));
		}
		
		$model = trim((string)$this->cParams->get('ai_openai_model', 'gpt-4o-mini'));
		$maxTokens = max(1200, (int)$this->cParams->get('ai_max_tokens', 2000));
		$temperature = (float)$this->cParams->get('ai_temperature', 0.4);
		$isNewModel = preg_match('/^gpt-(4\.1|5)/i', $model) === 1;
		$tokenKeyName = $isNewModel ? 'max_completion_tokens' : 'max_tokens';
		
		$httpTransport = $this->cParams->get('pagespeed_http_transport', 'curl') == 'socket' ? new Socket() : new Curl();
		$connectionAdapter = new Http($httpTransport, $this->cParams);
		$headers = array(
				'Content-Type' => 'application/json',
				'Authorization' => 'Bearer ' . $apiKey
		);
		$postData = array(
				'model' => $model,
				'messages' => array(
						array('role' => 'system', 'content' => 'You are an analytics strategist producing accurate and actionable analyses.'),
						array('role' => 'user', 'content' => $prompt)
				),
				'temperature' => $temperature
		);
		$postData[$tokenKeyName] = $maxTokens;
		
		$httpResponse = $connectionAdapter->post('https://api.openai.com/v1/chat/completions', json_encode($postData), $headers);
		if($httpResponse->code != 200 || !$httpResponse->body) {
			$errorMessage = Text::sprintf('COM_JREALTIME_AI_PROVIDER_HTTP_ERROR', (int)$httpResponse->code);
			if($httpResponse->body) {
				$errorData = json_decode($httpResponse->body, true);
				if(isset($errorData['error']['message'])) {
					$errorMessage = $errorData['error']['message'];
				}
			}
			throw new \RuntimeException($errorMessage);
		}
		
		$responseData = json_decode($httpResponse->body, true);
		if(!is_array($responseData) || empty($responseData['choices'][0]['message']['content'])) {
			throw new \RuntimeException(Text::_('COM_JREALTIME_AI_INVALID_RESPONSE'));
		}
		
		return trim((string)$responseData['choices'][0]['message']['content']);
	}

	/**
	 * Request analysis to Google Gemini API.
	 *
	 * @access private
	 * @param string $prompt
	 * @return string
	 */
	private function requestGeminiAnalysis($prompt) {
		$apiKey = trim((string)$this->cParams->get('ai_gemini_api_key', ''));
		if(!$apiKey) {
			throw new \RuntimeException(Text::_('COM_JREALTIME_AI_GEMINI_MISSING_APIKEY'));
		}
		
		$model = trim((string)$this->cParams->get('ai_gemini_model', 'gemini-2.5-flash'));
		$maxTokens = max(1200, (int)$this->cParams->get('ai_max_tokens', 2000));
		$temperature = (float)$this->cParams->get('ai_temperature', 0.4);
		$apiVersion = StringHelper::strpos($model, '-preview') !== false ? 'v1beta' : 'v1';
		$apiUrl = "https://generativelanguage.googleapis.com/$apiVersion/models/$model:generateContent?key=$apiKey";
		
		$httpTransport = $this->cParams->get('pagespeed_http_transport', 'curl') == 'socket' ? new Socket() : new Curl();
		$connectionAdapter = new Http($httpTransport, $this->cParams);
		$headers = array('Content-Type' => 'application/json');
		$postData = array(
				'contents' => array(
						array(
								'parts' => array(
										array('text' => $prompt)
								)
						)
				),
				'generationConfig' => array(
						'temperature' => $temperature,
						'maxOutputTokens' => $maxTokens
				)
		);
		
		$httpResponse = $connectionAdapter->post($apiUrl, json_encode($postData), $headers);
		if($httpResponse->code != 200 || !$httpResponse->body) {
			$errorMessage = Text::sprintf('COM_JREALTIME_AI_PROVIDER_HTTP_ERROR', (int)$httpResponse->code);
			if($httpResponse->body) {
				$errorData = json_decode($httpResponse->body, true);
				if(isset($errorData['error']['message'])) {
					$errorMessage = $errorData['error']['message'];
				}
			}
			throw new \RuntimeException($errorMessage);
		}
		
		$responseData = json_decode($httpResponse->body, true);
		if(!is_array($responseData) || empty($responseData['candidates'][0]['content']['parts'])) {
			throw new \RuntimeException(Text::_('COM_JREALTIME_AI_INVALID_RESPONSE'));
		}
		
		$textChunks = array();
		foreach((array)$responseData['candidates'][0]['content']['parts'] as $part) {
			if(is_array($part) && isset($part['text']) && trim((string)$part['text']) !== '') {
				$textChunks[] = trim((string)$part['text']);
			}
		}
		
		$analysisText = trim(implode("\n", $textChunks));
		if($analysisText === '') {
			throw new \RuntimeException(Text::_('COM_JREALTIME_AI_INVALID_RESPONSE'));
		}

		$finishReason = strtoupper((string)($responseData['candidates'][0]['finishReason'] ?? ''));
		if($finishReason === 'MAX_TOKENS') {
			$retryPostData = $postData;
			$retryPostData['generationConfig']['maxOutputTokens'] = min(8192, max($maxTokens + 800, (int)round($maxTokens * 1.5)));
			$retryHttpResponse = $connectionAdapter->post($apiUrl, json_encode($retryPostData), $headers);
			if($retryHttpResponse->code == 200 && $retryHttpResponse->body) {
				$retryResponseData = json_decode($retryHttpResponse->body, true);
				if(is_array($retryResponseData) && !empty($retryResponseData['candidates'][0]['content']['parts'])) {
					$retryTextChunks = array();
					foreach((array)$retryResponseData['candidates'][0]['content']['parts'] as $retryPart) {
						if(is_array($retryPart) && isset($retryPart['text']) && trim((string)$retryPart['text']) !== '') {
							$retryTextChunks[] = trim((string)$retryPart['text']);
						}
					}
					
					$retryAnalysisText = trim(implode("\n", $retryTextChunks));
					if($retryAnalysisText !== '') {
						$analysisText = $retryAnalysisText;
					}
				}
			}
		}

		return $analysisText;
	}
	
	/**
	 * Esplica il garbage collector delle immagini generate per i grafici
	 * contestualmente all'utente ad esempio 62_graphbar1.png, dwe6tf67wetfwewefew_pie1.png ecc
	 * 
	 * @access protected
	 * @return array Un array dei file immagini cancellati
	 */
	protected function imagesGarbage() {
		// Deleting all files in a directory older than latency = 24h
		$directory = JPATH_COMPONENT_ADMINISTRATOR . '/cache';
	    $filenames = array();
	    $latencyTime = time() - ($this->cParams->get ( 'maxlifetime_file' )*24*60*60);
	    $iterator = new \DirectoryIterator($directory);
	    foreach ($iterator as $fileinfo) {
	    	$fileName = $fileinfo->getFilename();
	        if ($fileinfo->isFile() && $fileName != 'index.html') {
	            $filenames[] = array($fileinfo->getMTime(), $fileName);
	        }
	    }
 		
	    $deletedFiles = array();
	    if(sizeof($filenames) > 1) {
	        foreach ($filenames as $fileElem) {
	            if($fileElem[0] < $latencyTime){ 
	                if(unlink($directory."/".$fileElem[1])) {
	                	$deletedFiles[] = $fileElem[1];
	                } 
	            } 
	        }
	    } 
	    return $deletedFiles;
	}
	
	/**
	 * Dependency injection setter del graph instance object generator
	 *
	 * @param Object& $graphInstance <<mockable_di>>
	 * @access public
	 * @return void
	 */
	public function setGraphRenderer(&$graphInstance) {
		$this->graphGenerator = $graphInstance;
	}
	
	/**
	 * Get geolocation translations from DB
	 * 
	 * @access public
	 * @return array[] &
	 */
	public function &getGeoTranslations() {
		static $resultTranslations;
		
		if($resultTranslations) {
			return $resultTranslations;
		}
		$query = "SELECT" .
				$this->dbInstance->quoteName('iso1_code') . "," .
				$this->dbInstance->quoteName('name') . 
				"\n FROM  #__realtimeanalytics_countries_map";
		
		try {
			$this->dbInstance->setQuery($query);
			$resultTranslations = $this->dbInstance->loadAssocList('iso1_code');
		} catch (JRealtimeException $e) {
			$this->app->enqueueMessage($e->getMessage(), $e->getExceptionLevel());
			$result = array();
		} catch (\Exception $e) {
			$jRealtimeException = new JRealtimeException($e->getMessage(), 'error');
			$this->app->enqueueMessage($jRealtimeException->getMessage(), $jRealtimeException->getExceptionLevel());
			$result = array();
		}
		
		return $resultTranslations;
	}
	
	/**
	 * Return select lists used as filter for editEntity
	 *
	 * @access public
	 * @param Object $record
	 * @return array
	 */
	public function getLists($record = null): array {
		$lists = [];
		
		// Supported graph theme
		$themes = [];
		$themes[] = HTMLHelper::_('select.option', 'Universal', 'Universal');
		$themes[] = HTMLHelper::_('select.option', 'Aqua', 'Aqua');
		$themes[] = HTMLHelper::_('select.option', 'Orange', 'Orange');
		$themes[] = HTMLHelper::_('select.option', 'Pastel', 'Pastel');
		$themes[] = HTMLHelper::_('select.option', 'Rose', 'Rose');
		$themes[] = HTMLHelper::_('select.option', 'Softy', 'Softy');
		$themes[] = HTMLHelper::_('select.option', 'Vivid', 'Vivid');
		
		$lists ['graphTheme'] = HTMLHelper::_ ( 'select.genericlist', $themes, 'graphtheme', 'onchange="Joomla.submitform();"', 'value', 'text', $this->getState('graphTheme', 'Universal'));
		
		return $lists;
	}
	
	/**
	 * Main get data method
	 *
	 * @access public
	 * @return Object[]
	 */
	public function getData(): array {
		// Manage stats report building match as the first operation
		$statsReport = $this->getState ( 'statsReport', 'full' );
		if(array_key_exists($statsReport, $this->mappingReports)) {
			foreach ($this->mappingReports[$statsReport] as $excludedStats) {
				$this->cParams->set($excludedStats, false);
			}
		}
		
		// Images graph garbage collector start
		$randomNumber = rand(0, 100);
		if(0 < $randomNumber && $randomNumber <= (int) $this->cParams->get ( 'probability' )){
			$this->imagesGarbage();
		}

		// Store period nelle class properties
		$this->intervalFrom = $this->getState('fromPeriod');
		$this->intervalTo = $this->getState('toPeriod');
		
		// Switch the build of the WHERE query based on date or datetime
		if ($this->dateFilterFormat == 'date') {
			$this->whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('visitdate') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
								"\n AND " .  $this->dbInstance->quoteName('visitdate') . " <= " . $this->dbInstance->quote( $this->intervalTo );
		} elseif ($this->dateFilterFormat == 'datetime') {
			$this->whereQuery = "\n WHERE " . $this->dbInstance->quoteName ( 'visit_timestamp' ) . " >= " . $this->dbInstance->quote ( strtotime ( $this->intervalFrom . " UTC") ) .
								"\n AND " . $this->dbInstance->quoteName ( 'visit_timestamp' ) . " <= " . $this->dbInstance->quote ( strtotime ( $this->intervalTo . " UTC") );
		}
		
		// Calculation dei dati da presentare all'utente
		try {
			if($this->cParams->get('visitsbypage_stats', true)) {
				$this->data[] = $this->visitsPerPage();
			} else {
				$this->data[] = array();
			}
			
		  	$this->data[] = $this->totalVisitedPages();
		  	
		  	if($this->cParams->get('visitsbyuser_stats', true)) {
		  		$this->data[] = $this->totalVisitedPagesPerUser();  
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	$this->data[] = $totalRangeVisitors = $this->totalVisitors();
		  	$this->data[] = $this->mediumVisitTime();
		  	$this->data[] = $this->mediumVisitedPagesPerSingleUser();
		  	
		  	if($this->cParams->get('geolocation_stats', true)) {
				$serverStatsGeoUsers = $this->numUsersGeoGrouped();
				$clientStatsGeoUsers = array();
				foreach ($serverStatsGeoUsers as $data) {
					$clientStatsGeoUsers[strtolower($data[1])] = $data[0];
				}
				$this->data[] = array('serverside'=>$serverStatsGeoUsers, 'clientside'=>$clientStatsGeoUsers);
			} else {
				$this->data[] = array('serverside'=>array(), 'clientside'=>array());
			}
		  	
		  	if($this->cParams->get('browser_stats', true)) {
		  		$this->data[] = $this->numUsersBrowserGrouped();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
			if($this->cParams->get('os_stats', true)) {
		  		$this->data[] = $this->numUsersOSGrouped();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	if($this->cParams->get('leaveoff_stats', true)) {
			  	$this->data[] = $this->hitLeaveOffPages();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	if($this->cParams->get('landing_stats', true)) {
			  	$this->data[] = $this->hitLandingPages();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	if($this->cParams->get('referral_stats', true)) {
		  		$this->data[] = $this->numReferral();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	if($this->cParams->get('searchkeys_stats', true)) {
		  		$this->data[] = $this->searchedPhrase();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	if($this->cParams->get('visitsbyip_stats', true)) {
		  		$this->data[] = $this->totalVisitedPagesPerIPAddress();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
		  	// Bounce rate calculation
		  	$this->data[] = $this->bounceRate($totalRangeVisitors);

		  	// Total unique visitors
		  	$this->data[] = $this->totalUniqueVisitors();
		  	
		  	if($this->cParams->get('device_stats', true)) {
		  		$this->data[] = $this->numUsersDeviceGrouped();
		  	} else {
		  		$this->data[] = array();
		  	}
		  	
			// Generazione nuove immagini grafici su filesystem richiamabili dalla view in base all'id utente
			$this->graphRender();
		} catch (JRealtimeException $e) {
			$this->app->enqueueMessage($e->getMessage(), $e->getExceptionLevel());
			$this->data = array();
		} catch (\Exception $e) {
			$jRealtimeException = new JRealtimeException($e->getMessage(), 'error');
			$this->app->enqueueMessage($jRealtimeException->getMessage(), $jRealtimeException->getExceptionLevel());
			$this->data = array();
		}
		
		return $this->data;
	}
	
	/**
	 * Secondary get data method to obtain only counters data used for example for the module view
	 *
	 * @access public
	 * @return mixed Array of data if successful or an exception message string on errors
	 */
	public function getDataCounters() {
		// Store period nelle class properties
		$this->intervalFrom = $this->getState('fromPeriod');
		$this->intervalTo = $this->getState('toPeriod');
		$this->whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('visitdate') . " >= " . $this->dbInstance->quote($this->intervalFrom) .
							"\n AND " .  $this->dbInstance->quoteName('visitdate') . " <= " . $this->dbInstance->quote( $this->intervalTo );

		// Calculation dei dati da presentare all'utente
		try {
			$this->data['total_visited_pages'] = $this->totalVisitedPages();
			$this->data['total_visitors'] = $this->totalVisitors();
			$this->data['medium_visit_time'] = $this->mediumVisitTime();
			$this->data['medium_page_per_user'] = $this->mediumVisitedPagesPerSingleUser();

			// Check if even the data for the visual geo map are needed
			if($this->cParams->get('visualmap_stats', 0)) {
				$serverStatsGeoUsers = $this->numUsersGeoGrouped();
				$clientStatsGeoUsers = array();
				foreach ($serverStatsGeoUsers as $data) {
					$clientStatsGeoUsers[strtolower($data[1])] = $data[0];
				}
				$this->data['geomap_data'] = $clientStatsGeoUsers;
			}
			
			// Generic visitor counter without time limit
			if($this->cParams->get('visitors_counter', 0)) {
				// Optional counter visitors by page
				if($this->cParams->get('visitors_counter_bypage', 0)) {
					$currentPage = Uri::current();
					$this->data['visitors_counter'] = $this->totalVisitorsByPage($currentPage);
				} else {
					$this->whereQuery = null;
					$this->data['visitors_counter'] = $this->totalVisitors();
				}
			}
		} catch (JRealtimeException $e) {
			return $e->getMessage();
		} catch (\Exception $e) {
			$jRealtimeException = new JRealtimeException($e->getMessage(), 'error');
			return $jRealtimeException->getMessage();
		}

		return $this->data;
	}
	
	/**
	 * Load details entity
	 *
	 * @access public
	 * @param string $identifier
	 * @param string $detailType
	 * @return Object[]
	 */
	public function loadStatsEntity($identifier, $detailType) {
		// Init query
		$query = null;
		
		// Switch load data in base al tipo di detail richiesto
		switch ($detailType){
			case 'user':
			case 'flow':
				$selectJoin = null;
				$leftJoin = null;
				// Where fixed query construction
				if ($this->dateFilterFormat == 'date') {
					$this->whereQuery = "\n WHERE srvstats.visitdate >= " . $this->dbInstance->quote($this->getState('fromPeriod')) .
										"\n AND srvstats.visitdate <= " . $this->dbInstance->quote($this->getState('toPeriod'));
				} elseif ($this->dateFilterFormat == 'datetime') {
					$this->whereQuery = "\n WHERE srvstats.visit_timestamp >= " . $this->dbInstance->quote ( strtotime ( $this->getState('fromPeriod') . " UTC") ) .
										"\n AND srvstats.visit_timestamp <= " . $this->dbInstance->quote ( strtotime ( $this->getState('toPeriod') . " UTC") );
				}
				
				if($this->cParams->get('xtd_singleuser_stats', 0)) {
					$selectJoin = ",\n srvstats.ip, srvstats.geolocation, srvstats.browser, srvstats.os, srvstats.device, usrs.email";
					$leftJoin = "\n LEFT JOIN #__users AS usrs ON srvstats.user_id_person = usrs.id";
				}
				$query = "SELECT srvstats.visitedpage," . 
						 "\n srvstats.session_id_person," .
						 "\n srvstats.customer_name," .
						 "\n srvstats.visit_timestamp," .
						 "\n srvstats.impulse".  
						 $selectJoin .
						 "\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') . " AS srvstats" .
						 $leftJoin .
						 $this->whereQuery . 
						 "\n AND srvstats.session_id_person = " . $this->dbInstance->quote($identifier) .
						 "\n ORDER BY srvstats.visit_timestamp DESC";
				break;
				
			case 'page':
				$selectJoin = null;
				// Where fixed query construction
				if ($this->dateFilterFormat == 'date') {
					$this->whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('visitdate') . " >= " . $this->dbInstance->quote($this->getState('fromPeriod')) .
										"\n AND " .  $this->dbInstance->quoteName('visitdate') . " <= " . $this->dbInstance->quote($this->getState('toPeriod'));
				} elseif ($this->dateFilterFormat == 'datetime') {
					$this->whereQuery = "\n WHERE " .  $this->dbInstance->quoteName('visit_timestamp') . " >= " . $this->dbInstance->quote( strtotime ( $this->getState('fromPeriod') . " UTC") ) .
										"\n AND " .  $this->dbInstance->quoteName('visit_timestamp') . " <= " . $this->dbInstance->quote( strtotime ( $this->getState('toPeriod') . " UTC") );
				}
				
				if($this->cParams->get('xtd_singleuser_stats', 0)) {
					$selectJoin = ",\n srvstats.ip, srvstats.geolocation, srvstats.browser, srvstats.os, srvstats.device";
				}
				$query = "SELECT " . $this->dbInstance->quoteName('customer_name') . "," . 
						 "\n ". $this->dbInstance->quoteName('visitedpage') . "," .
						 "\n ". $this->dbInstance->quoteName('visit_timestamp') . "," .
						 "\n " . $this->dbInstance->quote($identifier) . " AS " . $this->dbInstance->quoteName('pageurl') . "," .
						 "\n ". $this->dbInstance->quoteName('impulse') .
						 $selectJoin .
						 "\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') . " AS srvstats" .
						 $this->whereQuery . 
						 "\n AND " . $this->dbInstance->quoteName('visitedpage') . " = " . $this->dbInstance->quote($identifier) .
						 "\n ORDER BY " . 
						 $this->dbInstance->quoteName('visit_timestamp') . " DESC";
				break;
				
			case 'ip':
				$selectJoin = null;
				// Where fixed query construction
				if ($this->dateFilterFormat == 'date') {
					$this->whereQuery = "\n WHERE srvstats.visitdate >= " . $this->dbInstance->quote($this->getState('fromPeriod')) .
										"\n AND srvstats.visitdate <= " . $this->dbInstance->quote($this->getState('toPeriod'));
				} elseif ($this->dateFilterFormat == 'datetime') {
					$this->whereQuery = "\n WHERE srvstats.visit_timestamp >= " . $this->dbInstance->quote( strtotime ( $this->getState('fromPeriod') . " UTC") ) .
										"\n AND srvstats.visit_timestamp <= " . $this->dbInstance->quote( strtotime ( $this->getState('toPeriod') . " UTC") );
				}
				$selectJoin = ",\n srvstats.customer_name, srvstats.geolocation, srvstats.browser, srvstats.os, srvstats.device";
				$query = "SELECT srvstats.visitedpage," .
						 "\n srvstats.session_id_person," .
						 "\n srvstats.customer_name," .
						 "\n srvstats.visit_timestamp," .
						 "\n srvstats.impulse".
						 $selectJoin .
						 "\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') . " AS srvstats" .
						 $this->whereQuery .
						 "\n AND srvstats.ip = " . $this->dbInstance->quote($identifier) .
						 "\n ORDER BY srvstats.visit_timestamp DESC";
				break;

			case 'referral':
				$selectJoin = null;
				// Where fixed query construction
				$this->whereQuery = "\n WHERE refer.record_date >= " . $this->dbInstance->quote($this->getState('fromPeriod')) .
									"\n AND refer.record_date <= " . $this->dbInstance->quote($this->getState('toPeriod'));
				$query = "SELECT refer.record_date," .
						 "\n refer.ip," .
						 "\n svrstats.geolocation" .
						 "\n FROM " . $this->dbInstance->quoteName('#__realtimeanalytics_referral') . " AS refer" .
						 "\n LEFT JOIN " . $this->dbInstance->quoteName('#__realtimeanalytics_serverstats') . " AS svrstats" .
						 "\n ON refer.ip = svrstats.ip" .
						 $this->whereQuery .
						 "\n AND refer.referral = " . $this->dbInstance->quote($identifier) .
						 "\n GROUP BY refer.ip" .
						 "\n ORDER BY refer.record_date DESC";
				break;
		}
		
		try {
			$this->dbInstance->setQuery($query);
			$results = $this->dbInstance->loadObjectList();
			
			// Set timezone if required
			if($this->cParams->get('offset_type', 'joomla') == 'joomla') {
				$jConfig = $this->app->getConfig();
				date_default_timezone_set($jConfig->get('offset', 'UTC'));
				// Fix for the new Date by logger date_default_timezone_set to UTC, reset always timezone
				if(property_exists('\Joomla\CMS\Date\Date', 'stz')) {
					$reflection = new \ReflectionProperty('\Joomla\CMS\Date\Date', 'stz');
					if (\PHP_VERSION_ID < 80100) {
						$reflection->setAccessible(true);
					}
					$reflection->setValue(null, null);
				}
			}
		} catch (JRealtimeException $e) {
			$this->setException($e);
			return false;
		} catch (\Exception $e) {
			$jrealtimeException = new JRealtimeException($e->getMessage(), 'error');
			$this->setException($jrealtimeException);
			return false;
		}
		
		return $results;
	}
	
	/**
	 * Delete DB cache for all stats tables
	 *
	 * @access public
	 * @return bool
	 */
	public function deleteEntity($ids): bool {
		// Check if it's a partial or full delete
		$whereServerStats = null;
		$whereStats = null;
		if($this->getState('task') == 'deletePeriodEntity') {
			$startPeriod = $this->getState('fromPeriod', date('Y-m-d'));
			$toPeriod = $this->getState('toPeriod', date('Y-m-d'));
			$whereServerStats = "\n WHERE " . $this->dbInstance->quoteName('visitdate') . " >= " . $this->dbInstance->quote($startPeriod) .
								"\n AND " . $this->dbInstance->quoteName('visitdate') . " <= " . $this->dbInstance->quote($toPeriod);
			
			$whereStats = "\n WHERE " . $this->dbInstance->quoteName('record_date') . " >= " . $this->dbInstance->quote($startPeriod) .
						  "\n AND " . $this->dbInstance->quoteName('record_date') . " <= " . $this->dbInstance->quote($toPeriod);
		}
		
		try {
			$query = "DELETE FROM #__realtimeanalytics_serverstats" . $whereServerStats;
			$this->dbInstance->setQuery($query);
			$this->dbInstance->execute();
			
			$query = "DELETE FROM #__realtimeanalytics_referral" . $whereStats;
			$this->dbInstance->setQuery($query);
			$this->dbInstance->execute();
			
			$query = "DELETE FROM #__realtimeanalytics_searches" . $whereStats;
			$this->dbInstance->setQuery($query);
			$this->dbInstance->execute();
		} catch (JRealtimeException $e) {
			$this->setException($e);
			return false;
		} catch (\Exception $e) {
			$jRealtimeException = new JRealtimeException($e->getMessage(), 'error');
			$this->setException($jRealtimeException);
			return false;
		}
		return true;
	}
	
	/**
	 * Main fetch data method already populated
	 *
	 * @access public
	 * @return Object[]
	 */
	public function fetchData() {
		// Force data get if not already done
		if(!count($this->data)) {
			$this->getData();
		}

		return $this->data;
	}
	
	/**
	 * Build a compact native dashboard summary from server stats data.
	 *
	 * @access public
	 * @param array $statsData
	 * @return string
	 */
	public function buildAINativeReportContent(array $statsData): string {
		$lines = array();
		$lines[] = 'Total visited pages: ' . (int)($statsData[1] ?? 0);
		$lines[] = 'Total visitors: ' . (int)($statsData[3] ?? 0);
		$lines[] = 'Unique visitors: ' . (int)($statsData[15] ?? 0);
		$lines[] = 'Average visit time: ' . (float)($statsData[4] ?? 0);
		$lines[] = 'Pages per user: ' . (float)($statsData[5] ?? 0);
		$lines[] = 'Bounce rate: ' . (float)($statsData[14] ?? 0) . '%';
		$lines[] = 'Top countries: ' . $this->formatTopRows($statsData[6]['serverside'] ?? array(), 5, 1, 0);
		$lines[] = 'Top browsers: ' . $this->formatTopRows($statsData[7] ?? array(), 5, 1, 0);
		$lines[] = 'Top operating systems: ' . $this->formatTopRows($statsData[8] ?? array(), 5, 1, 0);
		$lines[] = 'Top devices: ' . $this->formatTopRows($statsData[16] ?? array(), 5, 1, 0);
		$lines[] = 'Top landing pages: ' . $this->formatTopRows($statsData[10] ?? array(), 5, 1, 0);
		$lines[] = 'Top leave pages: ' . $this->formatTopRows($statsData[9] ?? array(), 5, 1, 0);
		$lines[] = 'Top referrals: ' . $this->formatTopRows($statsData[11] ?? array(), 5, 0, 3);
		$lines[] = 'Top searches: ' . $this->formatTopRows($statsData[12] ?? array(), 5, 0, 1);
		return implode("\n", $lines);
	}
	
	/**
	 * Generate AI analysis from native serverstats summary content.
	 *
	 * @access public
	 * @param string $reportContent
	 * @param string $periodLabel
	 * @param string $channel
	 * @return string
	 */
	public function generateAIAnalysis($reportContent, $periodLabel = 'custom', $channel = 'dashboard') {
		if(!(int)$this->cParams->get('ai_analysis_enabled', 0)) {
			return '';
		}
		
		$sanitizedContent = $this->sanitizeAIContent($reportContent);
		if(!$sanitizedContent) {
			return Text::_('COM_JREALTIME_AI_NO_CONTENT');
		}
		
		try {
			$provider = trim((string)$this->cParams->get('ai_provider', 'gemini'));
			if($channel === 'periodic') {
				if($provider === 'openai' && !trim((string)$this->cParams->get('ai_openai_api_key', ''))) {
					return '';
				}
				if($provider !== 'openai' && !trim((string)$this->cParams->get('ai_gemini_api_key', ''))) {
					return '';
				}
			}
			$prompt = $this->buildAIAnalysisPrompt($sanitizedContent, $periodLabel, $channel);
			return $this->requestAIAnalysis($prompt);
		} catch (\Exception $e) {
			return $channel === 'periodic' ? '' : Text::sprintf('COM_JREALTIME_AI_ERROR', $e->getMessage());
		}
	}

	/**
	 * Class Constructor
	 *
	 * @access public
	 * @return Object&
	 */
	public function __construct($config = array(), ?MVCFactoryInterface $factory = null) {
		parent::__construct ( $config, $factory );
		
		$this->data = array ();
		$this->cParams = $this->getComponentParams ();
		
		$this->dateFilterFormat = $this->cParams->get('stats_calendar_type', 'date');
		
		// Map the reports to show based on task requested
		$this->mappingReports = array (
				'full' => array (),
				'landingleave' => array (
						'visitsbyip_stats',
						'visitsbypage_stats',
						'visitsbyuser_stats',
						'referral_stats',
						'searchkeys_stats'
				),
				'visitsbypage' => array (
						'visitsbyip_stats',
						'visitsbyuser_stats',
						'leaveoff_stats',
						'landing_stats',
						'referral_stats',
						'searchkeys_stats'
				),
				'visitsbyuser' => array (
						'visitsbyip_stats',
						'visitsbypage_stats',
						'leaveoff_stats',
						'landing_stats',
						'referral_stats',
						'searchkeys_stats'
				),
				'visitsbyip' => array (
						'visitsbypage_stats',
						'visitsbyuser_stats',
						'leaveoff_stats',
						'landing_stats',
						'referral_stats',
						'searchkeys_stats'
				),
				'referralkeys' => array (
						'visitsbypage_stats',
						'visitsbyuser_stats',
						'visitsbyip_stats',
						'leaveoff_stats',
						'landing_stats'
				)
		);
	}
}