<?php

class Tour extends DatabaseTable
{
    protected $_is_admin;
    protected $_is_planned;

    public function __construct($id, $column=false)
    {
	parent::__construct('tour', $id, $column);
    }

    public function isAdmin()
    {
	if (is_null($this->_is_admin)) {
	    $sql = sprintf("SELECT admin FROM tour_member WHERE tour_id = %d AND member_id = %d",
			   $this->id, $_SESSION['userid']);
	    $this->_is_admin = $this->db->fetchValue($sql);
	}

	return $this->_is_admin;
    }

    public function isPlanned()
    {
	if (is_null($this->_is_planned)) {
	    $sql = sprintf("SELECT key = 'plan' FROM tour_status WHERE id = %d", $this->data->tour_status_id);
	    $this->_is_planned = $this->db->fetchValue($sql);
	}

	return $this->_is_planned;
    }

    public function getBaseData()
    {
	$sql = <<<EOS
	    SELECT
	        tour.name,
	        year,
	        duration,
	        information,
	        start_date,
	        extract(day from start_date) ||'.'|| extract(month from start_date) ||'.'|| extract(year from start_date) AS start_datum,
	        tour_status.key AS status_key,
	        tour_status.name AS status
	    FROM tour
	    JOIN tour_status ON tour_status_id = tour_status.id
	    LEFT JOIN tour_date ON tour_date_id = tour_date.id
	    WHERE tour.id = {$this->id}
EOS;

	return $this->db->fetchObject($sql);
    }

    public function toggleDateStatus($tour_date_id, $sys_user_id)
    {
	$date = new Tour_Date($tour_date_id);

	$sql =<<<EOS
	    SELECT tour_date_member.id,tour_date_status_id
	    FROM tour_date
	    JOIN tour_date_member ON tour_date_id = tour_date.id
	    JOIN tour_member ON tour_member_id = tour_member.id
	    WHERE tour_date.id = %d AND tour_member.member_id = %d
EOS;
	$sql = sprintf($sql, $tour_date_id, $sys_user_id);

	$row = $this->db->fetchObject($sql);

	if ($row === false) {
	    $sql =<<<EOS
		SELECT tour_member.id
		FROM tour_date
		JOIN tour ON tour_id = tour.id
		JOIN tour_member using(tour_id)
		WHERE tour_date.id = %d AND member_id = %d
EOS;
	    $sql = sprintf($sql, $tour_date_id, $sys_user_id);
	    $member_id = $this->db->fetchValue($sql);

	    $sql = "SELECT id FROM tour_date_status ORDER BY priority OFFSET 1 LIMIT 1";
	    $status_id = $this->db->fetchValue($sql);

	    $this->db->insertInto('tour_date_member', ['tour_date_id' => $tour_date_id,
						       'tour_member_id' => $member_id,
						       'tour_date_status_id' => $status_id]);
	    $status = new Tour_Date_Status($status_id);
	    if ($sys_user_id == $_SESSION['userid'])
		Tour_Log::add($this->id(), sprintf("Verfügbarkeit am %s: %s",
						   $date->germanDate(), $status->get('name')));
	    else {
		$user = new Sys_User($sys_user_id);
		Tour_Log::add($this->id(), sprintf("Verfügbarkeit von %s am %s: %s",
						   $user->get('name'),
							 $date->germanDate(), $status->get('name')));
	    }
	} else {
	    $sql = sprintf("SELECT id FROM tour_date_status WHERE id > %d ORDER BY priority LIMIT 1",
			   $row->tour_date_status_id);
	    $status_id = $this->db->fetchValue($sql);

	    if ($status_id === false) {
		$sql = "SELECT id FROM tour_date_status ORDER BY priority LIMIT 1";
		$status_id = $this->db->fetchValue($sql);
	    }

	    $this->db->update('tour_date_member', ['tour_date_status_id' => $status_id], 'id = ' . $row->id);

	    $status = new Tour_Date_Status($status_id);
	    $status_name = $status->get('name');
	    if (!strlen($status_name))
		$status_name = 'n/a';

	    if ($sys_user_id == $_SESSION['userid']) {
		Tour_Log::addRewrite($this->id(),
				     sprintf("Verfügbarkeit am %s: ", $date->germanDate()),
				     $status_name);
	    } else {
		$user = new Sys_User($sys_user_id);
		Tour_Log::addRewrite($this->id(),
				     sprintf("Verfügbarkeit von %s am %s: ", $user->get('name'), $date->germanDate()),
				     $status_name);
	    }
	}
    }

    public function inviteMember($sys_user_id)
    {
	if (!$this->isAdmin())
	    throw new Exception("Keine Berechtigung zum Einladen");

	$user = new Sys_User($sys_user_id);

	if (!$user->id())
	    throw new Exception("User $sys_user_id not found");

	$this->db->insertInto('tour_member',
	    ['tour_id' => $this->id(),
	     'member_id' => $sys_user_id]);

	$myself = new Sys_User($_SESSION['userid']);
	$request = Application::get()->getRequest();
	$url = sprintf('%s://%s%s', $request->getScheme(), $request->getHttpHost(), Application::get()->getBaseURL());
	$body = Template::render('tour/invitemail',
				 ['inviter' => $myself->get('name'),
				  'tour_name' => $this->get('name'),
				  'tour_year' => $this->get('year'),
				  'tour_duration' => $this->get('duration'),
				  'name' => $user->get('name'),
				  'email' => $user->get('email'),
				  'url' => $url,
				  ]);
	$mail = new Mail();
	$mail->env_from(MAIL_FROM);
	$mail->set('From', mb_encode_mimeheader(utf8_decode(sprintf("%s <%s>", MAIL_FROM_NAME, MAIL_FROM)),'latin1'));
	$mail->set('To', $user->get('email'));
	$mail->set('Subject', mb_encode_mimeheader(utf8_decode(sprintf("Einladung zur Tour %s", $this->get('name'))),'latin1'));

	$mail->send($body);
    }

    public function getDates()
    {
	$sql = <<<EOS
	    SELECT
	        tour_date.id,
	        start_date,
	        extract(day from start_date) ||'.'|| extract(month from start_date) ||'.'|| extract(year from start_date) AS start_datum
	    FROM tour_date
	    WHERE tour_date.tour_id = {$this->id()}
	    ORDER BY start_date
EOS;

	$list = $this->db->fetchObjectList($sql);

	foreach ($list as &$row) {
	    $dt = new DateTime($row->start_date);
	    $row->year = $dt->format('Y');
	    $row->start_short = $dt->format('d.m.');
	    $dt->add(new DateInterval('P'.$this->get('duration').'D'));
	    $row->end_short = $dt->format('d.m.');
	}

	return $list;
    }

    public function getMembers($admin=null)
    {
	if (!is_null($admin))
	    $cond_admin = ' AND tour_member.admin = ' . ($admin ? 'true' : 'false');
	$sql = <<<EOS
	    SELECT
	        tour_member.id,
	        sys_user.name,
	        sys_user.email,
	        sys_user.mobile,
	        sys_user.single_room,
	        tour_member.comment,
	        tour_member.admin
	    FROM sys_user
	    JOIN tour_member ON member_id = sys_user.id
	    WHERE tour_id = {$this->id}{$cond_admin}
	    ORDER BY name
EOS;

	$list = $this->db->fetchObjectList($sql);

	return $list;
    }

    public function getAvailability($user_id)
    {
	$list = $this->getDates();

	if (!count($list)) return [];

	$ids = [];
	foreach ($list as &$row)
	    $ids[] = $row->id;
	$ids = join(',', $ids);

	$sql = <<<EOS
	    SELECT
	      tour_date_id,
	      tour_date_status.key AS status_key,
	      tour_date_status.name AS status_text
	    FROM tour_date_member
	    JOIN tour_member ON tour_member_id = tour_member.id AND member_id = {$user_id}
	    JOIN tour_date_status ON tour_date_status_id = tour_date_status.id
	    WHERE tour_date_id IN ({$ids})
EOS;

	foreach ($this->db->fetchObjectList($sql) as $item) {
	    foreach ($list as &$row) {
		if ($row->id == $item->tour_date_id) {
		    $row->status_key = $item->status_key;
		    $row->status_text = $item->status_text;
		    break;
		}
	    }
	}

	return $list;
    }

    public function getAvailabilityMatrix()
    {
	$list = $this->getDates();

	$ids = [];
	foreach ($list as &$row)
	    $ids[] = $row->id;
	$ids = join(',', $ids);

	$sql = <<<EOS
	    SELECT
	      tour_date_id,
	      member_id,
	      sys_user.name,
	      tour_date_status.key AS status_key,
	      tour_date_status.name AS status_text
	    FROM tour_date_member
	    JOIN tour_member ON tour_member_id = tour_member.id
	    JOIN sys_user ON sys_user.id = member_id
	    JOIN tour_date_status ON tour_date_status_id = tour_date_status.id
	    WHERE tour_date_id IN ({$ids})
EOS;

	$userlist = [];
	foreach ($this->db->fetchObjectList($sql) as $item) {
	    foreach ($list as &$row) {
		if ($row->id == $item->tour_date_id) {
		    if (!is_array($row->avail))
			$row->avail = [];

		    if (!in_array($item->member_id, $userlist)) {
			$nick = '';
			foreach (explode(' ', $item->name) as $word)
			    $nick .= substr($word,0,1);

			$user = new stdClass();
			$user->nick = $nick;
			$user->name = $item->name;
			$userlist[$item->member_id] = $user;
		    }

		    $av = new stdClass();
		    $av->member_id = $item->member_id;
		    $av->nick = $userlist[$item->member_id]->nick;
		    $av->status_key = $item->status_key;
		    $av->status_text = $item->status_text;

		    $row->avail[$item->member_id] = $av;
		    break;
		}
	    }
	}

	return ['user' => $userlist, 'list' => $list];
    }

    public function getNotes($all=false)
    {
	if ($all !== true)
	    $condition = ' AND tour_note_seen.id IS NULL';
	$sql = <<<EOS
	    SELECT
	        tour_note.id,
	        tour_note.note,
	        tour_note.sys_user_id = {$_SESSION['userid']} AS own,
	        sys_user.name,
	        sys_user.sys_edit,
	        extract(day from sys_user.sys_edit) ||'.'|| extract(month from sys_user.sys_edit) ||'.'|| extract(year from sys_user.sys_edit) AS datum
	    FROM tour_note
	    JOIN sys_user ON tour_note.sys_user_id = sys_user.id
	    LEFT JOIN tour_note_seen ON tour_note_id = tour_note.id
	    WHERE tour_id = {$this->id} AND deleted = false{$condition}
	    ORDER BY tour_note.sys_edit DESC
EOS;

	$list = $this->db->fetchObjectList($sql);

	if (count($list)) {
	    foreach ($list as &$row) {
		$row->note_html = Wiki::renderHTML($row->note);
		if ($all !== true)
		    $this->db->insertInto('tour_note_seen', ['tour_note_id' => $row->id]);
	    }
	}

	return $list;
    }
}