<?php

class AVForums_Competitions_Model_Entry extends XenForo_Model
{
	const FETCH_USER = 0x01;
	const FETCH_COMPETITION = 0x02;

	public function getEntryById($entryId)
	{
		return $this->_getDb()->fetchRow('
			SELECT *
			FROM xf_avforums_competitions_entry
			WHERE entry_id = ?
		', $entryId);
	}

	public function getEntries(array $conditions = array(), array $fetchOptions = array(), $key = 'entry_id')
	{
		$whereClause = $this->prepareEntryConditions($conditions, $fetchOptions);

		$joinOptions = $this->prepareEntryFetchOptions($fetchOptions);
		$limitOptions = $this->prepareLimitFetchOptions($fetchOptions);
		$sqlClauses = $this->prepareEntryFetchOptions($fetchOptions);

		return $this->fetchAllKeyed($this->limitQueryResults('
			SELECT entry.*
				' . $joinOptions['selectFields'] . '
			FROM xf_avforums_competitions_entry AS entry
				' . $joinOptions['joinTables'] . '
				WHERE ' . $whereClause . '
				' . $sqlClauses['orderClause'] . '
			', $limitOptions['limit'], $limitOptions['offset']
		), $key);
	}

	public function countEntries($competitionId = '')
	{
		$whereClause = '';
		if (is_int($competitionId))
		{
			$whereClause = 'WHERE competition_id = ' . $this->_getDb()->quote($competitionId);
		}

		return $this->_getDb()->fetchOne('
			SELECT COUNT(*)
			FROM xf_avforums_competitions_entry
			' . $whereClause . '
		');
	}

	public function countCorrectEntriesForCompetition($competitionId)
	{
		return $this->_getDb()->fetchOne('
			SELECT COUNT(*)
			FROM xf_avforums_competitions_entry
			WHERE competition_id = ? AND answer_state = \'correct\'
		', $competitionId);
	}

	public function countOptInsForCompetition($competitionId)
	{
		return $this->_getDb()->fetchOne('
			SELECT COUNT(*)
			FROM xf_avforums_competitions_entry
			WHERE competition_id = ? AND opt_in = 1
		', $competitionId);
	}

	public function getOptInsByCompetitionId($competitionId)
	{
		return $this->fetchAllKeyed('
			SELECT entry.entry_id, entry.competition_id, entry.user_id,
				IF(user.username IS NULL, entry.username, user.username) AS username, user.email
			FROM xf_avforums_competitions_entry AS entry
			INNER JOIN xf_user AS user ON
				(entry.user_id = user.user_id)
			WHERE entry.competition_id = ? AND entry.opt_in = 1
		', 'entry_id', $competitionId);
	}

	public function getWinnersByCompetitionId($competitionId)
	{
		return $this->fetchAllKeyed('
			SELECT entry.entry_id, entry.competition_id, entry.user_id,
				IF(user.username IS NULL, entry.username, user.username) AS username, user.email,
				entry.entry_state
			FROM xf_avforums_competitions_entry AS entry
			INNER JOIN xf_user AS user ON
				(entry.user_id = user.user_id)
			WHERE entry.competition_id = ? AND (entry.entry_state = \'winner\' OR entry.entry_state = \'runner_up\')
		', 'entry_id', $competitionId);
	}

	public function prepareEntryFetchOptions(array $fetchOptions)
	{
		$selectFields = '';
		$joinTables = '';
		$orderBy = '';

		if (!empty($fetchOptions['join']))
		{
			if ($fetchOptions['join'] & self::FETCH_USER)
			{
				$selectFields .= ',
					user.*, user_profile.*, IF(user.username IS NULL, entry.username, user.username) AS username';
				$joinTables .= '
					LEFT JOIN xf_user AS user ON
						(user.user_id = entry.user_id)
					LEFT JOIN xf_user_profile AS user_profile ON
						(user_profile.user_id = entry.user_id)';
			}

			if ($fetchOptions['join'] & self::FETCH_COMPETITION)
			{
				$selectFields .= ',
					competition.*';
				$joinTables .= '
					LEFT JOIN xf_avforums_competitions AS competition ON
						(entry.competition_id = competition.competition_id)';
			}
		}

		if (!empty($fetchOptions['order']))
		{
			$orderBySecondary = '';

			switch ($fetchOptions['order'])
			{
				case 'rand':
					$orderBy = 'RAND()';
					break;

				default:
					$orderBy = 'entry.entry_date';
					break;
			}
			if (!isset($fetchOptions['orderDirection']) || $fetchOptions['orderDirection'] == 'desc')
			{
				$orderBy .= ' DESC';
			}
			else
			{
				$orderBy .= ' ASC';
			}

			$orderBy .= $orderBySecondary;
		}

		return array(
			'selectFields' => $selectFields,
			'joinTables'   => $joinTables,
			'orderClause' => ($orderBy ? "ORDER BY $orderBy" : '')
		);
	}

	public function prepareEntryConditions(array $conditions, array &$fetchOptions)
	{
		$db = $this->_getDb();

		$sqlConditions = array();

		if (!empty($conditions['user_id']))
		{
			if (is_array($conditions['user_id']))
			{
				$sqlConditions[] = 'entry.user_id IN (' . $db->quote($conditions['user_id']) . ')';
			}
			else
			{
				$sqlConditions[] = 'entry.user_id = ' . $db->quote($conditions['user_id']);
			}
		}

		if (!empty($conditions['competition_id']))
		{
			if (is_array($conditions['competition_id']))
			{
				$sqlConditions[] = 'entry.competition_id IN (' . $db->quote($conditions['competition_id']) . ')';
			}
			else
			{
				$sqlConditions[] = 'entry.competition_id = ' . $db->quote($conditions['competition_id']);
			}
		}

		if (!empty($conditions['answer_state']))
		{
			if (is_array($conditions['answer_state']))
			{
				$sqlConditions[] = 'entry.answer_state IN (' . $db->quote($conditions['answer_state']) . ')';
			}
			else
			{
				$sqlConditions[] = 'entry.answer_state = ' . $db->quote($conditions['answer_state']);
			}
		}

		if (!empty($conditions['user_state']))
		{
			if (is_array($conditions['user_state']))
			{
				$sqlConditions[] = 'user.user_state IN (' . $db->quote($conditions['user_state']) . ')';
			}
			else
			{
				$sqlConditions[] = 'user.user_state = ' . $db->quote($conditions['user_state']);
			}
		}

		if (!empty($conditions['entry_state']))
		{
			if (is_array($conditions['entry_state']))
			{
				$sqlConditions[] = 'entry.entry_state IN (' . $db->quote($conditions['entry_state']) . ')';
			}
			else
			{
				$sqlConditions[] = 'entry.entry_state = ' . $db->quote($conditions['entry_state']);
			}
		}

		return $this->getConditionsForClause($sqlConditions);
	}
}