<?php
/**
 *----------------------------------------------------------------------------
 * iCagenda     Events Management Extension for Joomla!
 *----------------------------------------------------------------------------
 * @version     4.0.1 2026-01-19
 *
 * @package     iCagenda.Admin
 * @subpackage  src.Model
 * @link        https://www.joomlic.com
 *
 * @author      Cyril Reze
 * @copyright   (c) 2012-2026 Cyril Reze / JoomliC. All rights reserved.
 * @license     GNU General Public License version 3 or later; see LICENSE.txt
 *
 * @since       1.0
 *----------------------------------------------------------------------------
 */

namespace WebiC\Component\iCagenda\Administrator\Model;

use Joomla\CMS\MVC\Model\ListModel;
use Joomla\Database\ParameterType;

// phpcs:disable PSR1.Files.SideEffects
\defined('_JEXEC') or die;
// phpcs:enable PSR1.Files.SideEffects

/**
 * iCagenda Component Dashboard Model.
 */
class IcagendaModel extends ListModel
{
	/**
	 * Build an SQL query to load Top 10 Categories stats.
	 *
	 * @return  JDatabaseQuery
	 */
	public function getCategoryStats()
	{
		// Get database object
		$db = $this->getDatabase();

		$query = $db->createQuery()
			->select(
				[
					$db->qn('e.catid', 'catid'),
					'SUM(' . $db->qn('e.hits') . ') AS hits',
				]
			)
			->select(
				[
					$db->qn('c.id', 'cat_id'),
					$db->qn('c.title', 'cat_title'),
					$db->qn('c.title', 'stats_label'),
					$db->qn('c.color', 'cat_color'),
				]
			)
			->from($db->qn('#__icagenda_events', 'e'))
			->join('LEFT', $db->qn('#__icagenda_category', 'c'), $db->qn('c.id') . ' = ' . $db->qn('e.catid'))
			->group($db->qn('c.id'))
			->order($db->qn('hits') . ' DESC');

		$db->setQuery($query, 0, 10);

		$list = $db->loadObjectList();

		return $list;
	}

	/**
	 * Build an SQL query to return total of event hits.
	 *
	 * @return  JDatabaseQuery
	 */
	public function getEventHitsTotal()
	{
		// Get database object
		$db = $this->getDatabase();

		$query = $db->createQuery()
			->select('SUM(' . $db->qn('e.hits') . ') AS hits')
			->from($db->qn('#__icagenda_events', 'e'));

		$db->setQuery($query);

		$list = $db->loadResult();

		return $list;
	}

	/**
	 * Build an SQL query to load Top 10 Events stats.
	 *
	 * @return  JDatabaseQuery
	 */
	public function getEventStats()
	{
		// Get database object
		$db = $this->getDatabase();

		$query = $db->createQuery()
			->select(
				[
					$db->qn('e.title', 'stats_label'),
					$db->qn('e.hits', 'hits'),
				]
			)
			->select(
				[
					$db->qn('c.id', 'cat_id'),
					$db->qn('c.title', 'cat_title'),
					$db->qn('c.color', 'cat_color'),
				]
			)
			->from($db->qn('#__icagenda_events', 'e'))
			->join('LEFT', $db->qn('#__icagenda_category', 'c'), $db->qn('c.id') . ' = ' . $db->qn('e.catid'))
			->order($db->qn('hits') . ' DESC');

		$db->setQuery($query, 0, 10);

		$list = $db->loadObjectList();

		return $list;
	}

	/**
	 * Build an SQL query to load iCagenda System.
	 *
	 * @return  JDatabaseQuery
	 *
	 * @since   4.0.0
	 */
	public function getIcagendaSystem()
	{
		// Get database object
		$db = $this->getDatabase();

		$query = $db->createQuery()
			->select(
				[
					$db->qn('version', 'icv'),
					$db->qn('releasedate', 'icd'),
					$db->qn('params', 'icp'),
				]
			)
			->from($db->qn('#__icagenda'))
			->where($db->qn('id') . ' = ' . $db->q((int) ICAGENDA_MAJOR));

		$db->setQuery($query);
		$result = $db->loadObject();

		return $result;
	}

	/**
	 * Build an SQL query to load iCagenda System Params.
	 *
	 * @return  JDatabaseQuery
	 *
	 * @since   4.0.0
	 */
	public function getIcagendaSystemParams()
	{
		// Get database object
		$db = $this->getDatabase();

		$query = $db->createQuery()
			->select($db->qn('params'))
			->from($db->qn('#__icagenda'))
			->where($db->qn('id') . ' = ' . $db->q((int) ICAGENDA_MAJOR));

		$db->setQuery($query);
		$result = $db->loadResult();

		return $result;
	}

	/**
	 * Update iCagenda System Params
	 *
	 * Update Database
	 *
	 * @since   4.0.0
	 */
	public function updateSystemParams($params_array)
	{
		// Read the existing component value(s)
		$db = $this->getDatabase();

		$major_version = ICAGENDA_MAJOR;

		$query = $db->createQuery()
			->select($db->qn('params'))
			->from($db->qn('#__icagenda'))
			->where($db->qn('id') . ' = :ICAGENDA_MAJOR')
			->bind(':ICAGENDA_MAJOR', $major_version, ParameterType::INTEGER);

		$db->setQuery($query);
		$result = $db->loadResult();
		$params = json_decode($result, true);

		// Add the new variable(s) to the existing one(s)
		foreach ($params_array as $name => $value) {
			$params[(string) $name] = $value;
		}

		// Store the combined new and existing values back as a JSON string
		$paramsString = json_encode($params);

		$query = $db->createQuery()
			->update($db->qn('#__icagenda'))
			->set($db->qn('params') . ' = ' . $db->q($paramsString))
			->where($db->qn('id') . ' = :ICAGENDA_MAJOR')
			->bind(':ICAGENDA_MAJOR', $major_version, ParameterType::INTEGER);

		$db->setQuery($query);
		$db->execute();
	}

	/**
	 * Build an SQL query to load Top 10 Events stats.
	 *
	 * @return  JDatabaseQuery
	 */
	public function getDeprecatedExtensions($elements)
	{
		if (!\is_array($elements) && \count($elements) < 1) {
			return [];
		}

		$exts = [];

		// Get database object
		$db = $this->getDatabase();

		foreach ($elements as $k => $element) {
			$query = $db->createQuery()
				->select(
					[
						$db->qn('e.extension_id', 'extid'),
						$db->qn('e.type', 'type'),
						$db->qn('e.element', 'element'),
						$db->qn('e.folder', 'folder'),
						$db->qn('e.enabled', 'enabled'),
					]
				);

			if ($element[0] === 'module') {
				$query->select(
					[
						$db->qn('m.id', 'moduleid'),
						$db->qn('m.position', 'position'),
						$db->qn('m.module', 'module'),
						$db->qn('m.title', 'title'),
						$db->qn('m.published', 'published'),
					]
				);
			}

			$query->from($db->qn('#__extensions', 'e'));

			if ($element[0] === 'module') {
				$query->join('LEFT', $db->qn('#__modules', 'm'), $db->qn('m.module') . ' = ' . $db->qn('e.element'));
			}

			$query->where($db->qn('e.element') . ' = ' . $db->q($element[1]));
			$query->where($db->qn('e.enabled') . ' = 1');

			if ($element[0] === 'plugin') {
				$query->where($db->qn('e.folder') . ' = ' . $db->q($element[2]));
			}

			$db->setQuery($query);

			$list = $db->loadObjectList();

			foreach ($list as $ext) {
				$nameExt = $ext->type;

				if ($ext->folder) {
					$nameExt.= '_' .$ext->folder;
				}

				$nameExt.= '_' .$ext->element;

				$extID = ($ext->type === 'module') ? $ext->moduleid : $ext->extid;
				$exts[$nameExt]['extension_id'] = $ext->extid;
				$exts[$nameExt][$extID] = $ext;
			}
		}

		return $exts;
	}

	/**
	 * Get the package update site ID.
	 *
	 * @return  mixed
	 */
	public function getUpdateSiteID($element)
	{
		$db = $this->getDatabase();

		// Get pkg_icagenda extension id
		$query = $db->createQuery()
			->select('extension_id')
			->from($db->qn('#__extensions'))
			->where($db->qn('element') . ' = ' . $db->q($element));
		$db->setQuery($query);

		$eid = $db->loadResult();

		if ($eid) {
			$query = $db->createQuery()
				->select('update_site_id')
				->from($db->qn('#__update_sites_extensions'))
				->where($db->qn('extension_id') . ' = ' . (int) $eid);
			$db->setQuery($query);

			$update_site_id = $db->loadResult();

			if ($update_site_id) {
				return $update_site_id;
			}
		}

		return false;
	}
}
