<?php
require_once ('config.php');

$pdo=new PDO ('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);

function add_event ($info)
{
	global $pdo;
	$st=$pdo->prepare ('insert into `events` (`title`,`location`,`start_date`,`end_date`,`source`,`added_by`,`description`) values (?,?,?,?,?,?,?)');
	$ok=$st->execute ([
		$info['title'],
		$info['location'],
		$info['start_date'],
		$info['end_date'],
		$info['source'],
		$info['added_by'],
		$info['description']
	]);

	if (!$ok){
		$err=$st->errorInfo();
		throw new Exception ('Error ('.$err[0].', '.$err[1].'): '.$err[2]);
	}

	$id=$pdo->lastInsertId();

	$st=$pdo->prepare ('insert into `event_keyword` (`event`,`keyword`) values (?,?)');
	foreach ($info['keywords'] as $keyword_id)
		$st->execute ([$id,$keyword_id]);

	return $id;
}

function get_events ($keywords=[])
{
	global $pdo;

	if (count ($keywords) > 0){
		$keywords=$pdo->query (
			'select `id` from `keywords` ' .
			'where `keyword` in ('.implode (',',array_map ([$pdo,'quote'],$keywords)).')'
		);
		$keywords=array_map ('array_shift',$keywords->fetchAll (PDO::FETCH_NUM));
	}

	$sql='select `events`.*,`sources`.`source` as `source_name` ' .
		'from `events` inner join `sources` on `events`.`source`=`sources`.`id` ';
	if (count ($keywords) > 0){
		$sql.='where exists (select * from `event_keyword` ' .
			'where `event`=`events`.`id` and `keyword` in ('.implode (',',$keywords).')) ';
	}
	$sql.='order by `start_date` asc,`end_date` asc,`title` asc';

	$st=$pdo->prepare ($sql);
	$st->execute();
	$events=$st->fetchAll (PDO::FETCH_ASSOC);

	$get_keywords_st=$pdo->prepare (
		'select `keywords`.`keyword` ' .
		'from `event_keyword` inner join `keywords` on `keywords`.`id`=`event_keyword`.`keyword` ' .
		'where `event_keyword`.`event`=? ' .
		'order by `keywords`.`keyword` asc'
	);
	foreach ($events as $i => $event){
		$get_keywords_st->execute ([$event['id']]);
		$keywords=$get_keywords_st->fetchAll (PDO::FETCH_NUM);
		$events[$i]['keywords']=array_map ('array_shift',$keywords);
	}

	return $events;
}

function get_keyword ($keyword)
{
	global $pdo;
	$st=$pdo->prepare ('select `id` from `keywords` where `keyword`=?');
	$st->execute ([$keyword]);

	if ($st->rowCount() > 0){
		$row=$st->fetch();
		return $row['id'];
	}

	return null;
}

function get_keywords ()
{
	global $pdo;
	$keywords=$pdo->query (
		'select `keywords`.`keyword`, count(`event_keyword`.`event`) as `count` ' .
		'from `event_keyword` inner join `keywords` on `event_keyword`.`keyword`=`keywords`.`id` ' .
		'group by `event_keyword`.`keyword` ' .
		'order by `keywords`.`keyword` asc'
	);
	return $keywords->fetchAll (PDO::FETCH_ASSOC);
}

function get_or_add_keyword ($keyword)
{
	global $pdo;
	$id=get_keyword ($keyword);

	if (is_null ($id)){
		$st=$pdo->prepare ('insert into `keywords` (`keyword`) values (?)');
		$st->execute ([$keyword]);
		$id=$pdo->lastInsertId();
	}

	return $id;
}

function get_source ($source)
{
	global $pdo;
	$st=$pdo->prepare ('select `id` from `sources` where `source`=?');
	$st->execute ([$source]);

	if ($st->rowCount() > 0){
		$row=$st->fetch();
		return $row['id'];
	}

	return null;
}

function get_or_add_source ($source)
{
	global $pdo;
	$id=get_source ($source);

	if (is_null ($id)){
		$st=$pdo->prepare ('insert into `sources` (`source`) values (?)');
		$st->execute ([$source]);
		$id=$pdo->lastInsertId();
	}

	return $id;
}

function get_user_by_email ($email)
{
	global $pdo;
	$st=$pdo->prepare ('select * from `users` where `email`=?');
	$st->execute ([$email]);

	if ($st->rowCount() > 0)
		return $st->fetch (PDO::FETCH_ASSOC);
	else
		throw new Exception ('no user with email '.$email);
}