<?php
use PHPUnit\Framework\TestCase;

require_once dirname(__FILE__) . "/../initialise.php";
require_once dirname(__FILE__) . "/../../db/IDB.php";
require_once dirname(__FILE__) . "/../../db/MySQL.php";
require_once dirname(__FILE__) . "/../../db/QueryBuilder.php";
require_once dirname(__FILE__) . "/../Debug.php";

final class QueryBuilderTest extends TestCase
{
	private $db;
	
	/**
	 * @before
	 */
	public function configure() {
		$this->db = new MySQL(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB, new Debug());
	}
	
	public function testSimpleSingleField() {
		$queryBuilder = new QueryBuilder($this->db, "test");
		$queryBuilder->addField("a");
		$queryBuilder->addField("b", "c");
		
		$this->assertEquals("SELECT `a`, `b` AS `c` FROM `test`", $queryBuilder->toQuery());
	}
	
	/**
	 * From UpdateBodiesRundaily.php
	 */
	public function testSimpleFieldArray() {
		$queryBuilder = new QueryBuilder($this->db, "ab_bodies");
		$queryBuilder->addField(array("bodycode", "bodyname", "email", "bodystatus", "bodycategory", "bodycategoryorder", "memberno", "bodynameascii", "netcomcode", "countrycode"));
		
		$this->assertEquals("SELECT `bodycode`, `bodyname`, `email`, `bodystatus`, `bodycategory`, `bodycategoryorder`, `memberno`, `bodynameascii`, `netcomcode`, `countrycode` FROM `ab_bodies`", $queryBuilder->toQuery());
	}
	
	/**
	 * From StatisticsRundaily.php
	 */
	public function testWithMinMaxAndLeft() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField(new DBMethodMin(new DBMethodLeft("created", 10)), "first");
		$queryBuilder->addField(new DBMethodMax(new DBMethodLeft("created", 10)), "last");
		
		$this->assertEquals("SELECT MIN(LEFT(`created`, 10)) AS `first`, MAX(LEFT(`created`, 10)) AS `last` FROM `applications`", $queryBuilder->toQuery());
	}
	public function testWithLeft() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField(new DBMethodLeft("created", 10), "applicationday");
		$queryBuilder->addField(new DBMethodCount("id"), "number");
		$queryBuilder->addGroup(new DBMethodLeft("created", 10));
		$queryBuilder->addOrderAsc("applicationday");
		
		$this->assertEquals("SELECT LEFT(`created`, 10) AS `applicationday`, COUNT(`id`) AS `number` FROM `applications` GROUP BY LEFT(`created`, 10) ORDER BY `applicationday` ASC", $queryBuilder->toQuery());
	}
	public function testWithInnerJoin() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField(new DBMethodCount("ab_bodies.bodycode"), "count");
		$queryBuilder->addInnerJoin("ab_bodies", "bodycode", "applications.bodycode");
		$queryBuilder->addWhereEquals("ab_bodies.bodystatus", "A");
		$queryBuilder->addWhereIn("accepted", array("A", "B"));
		$queryBuilder->addGroup("ab_bodies.bodycode");
		
		$this->assertEquals("SELECT COUNT(`ab_bodies`.`bodycode`) AS `count` FROM `applications` INNER JOIN `ab_bodies` ON `ab_bodies`.`bodycode` = `applications`.`bodycode` WHERE `ab_bodies`.`bodystatus` = 'A' AND `accepted` IN ('A', 'B') GROUP BY `ab_bodies`.`bodycode`", $queryBuilder->toQuery());
	}
	public function testGroupWithFunction() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField(new DBMethodLeft("created", 10), "applicationday");
		$queryBuilder->addField(new DBMethodCount("id"), "number");
		$queryBuilder->addGroup(new DBMethodLeft("created", 10));
		$queryBuilder->addOrderAsc("applicationday");
		
		$this->assertEquals("SELECT LEFT(`created`, 10) AS `applicationday`, COUNT(`id`) AS `number` FROM `applications` GROUP BY LEFT(`created`, 10) ORDER BY `applicationday` ASC", $queryBuilder->toQuery());
	}
	
	/**
	 * From ContactPwdRundaily.php
	 */
	public function testWithLeftJoinGroupAndOrder() {
		$queryBuilder = new QueryBuilder($this->db, "ab_bodies");
		$queryBuilder->addField(array("ab_bodies.bodycode", "ab_bodies.bodyname", "ab_bodies.email", "ab_bodies.bodycategory"));
		$queryBuilder->addField(new DBMethodCount("applications.id"), "count");
		$queryBuilder->addLeftJoin("applications", "bodycode", "ab_bodies.bodycode");
		$queryBuilder->addWhereGreaterEquals("bodycategoryorder", 0);
		$queryBuilder->addWhereIsNull("password");
		$queryBuilder->addWhereIsNotNull("ab_bodies.email");
		$queryBuilder->addGroup("bodycode");
		$queryBuilder->addGroup("bodyname");
		$queryBuilder->addGroup("email");
		$queryBuilder->addGroup("bodycategory");
		$queryBuilder->addOrderAsc("bodycategoryorder");
		$queryBuilder->addOrderAsc("bodynameascii");
		
		$this->assertEquals("SELECT `ab_bodies`.`bodycode`, `ab_bodies`.`bodyname`, `ab_bodies`.`email`, `ab_bodies`.`bodycategory`, COUNT(`applications`.`id`) AS `count` FROM `ab_bodies` LEFT JOIN `applications` ON `applications`.`bodycode` = `ab_bodies`.`bodycode` WHERE `bodycategoryorder` >= 0 AND `password` IS NULL AND `ab_bodies`.`email` IS NOT NULL GROUP BY `bodycode`, `bodyname`, `email`, `bodycategory` ORDER BY `bodycategoryorder` ASC, `bodynameascii` ASC", $queryBuilder->toQuery());
	}
	
	/**
	 * From MemListRundaily.php
	 */
	public function testMultipleJoinCriteria() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField("applications.id", "id");
		$queryBuilder->addField("memberlistoncurrent");
		$queryBuilder->addField("memberlist.id", "curmemlistid");
		$queryBuilder->addLeftJoin("memberlist", array(new DBMethodLower("memberlist.givenname"), new DBMethodLower("memberlist.surname"), new DBMethodLower("memberlist.bodycode")), array(new DBMethodLower("applications.firstname"), new DBMethodLower("applications.lastname"), new DBMethodLower("applications.bodycode")));
		$queryBuilder->addInnerJoin("ab_bodies", "bodycode", "applications.bodycode");
		$queryBuilder->addWhereEquals("memberlistoncurrent", "-");
		$queryBuilder->addWhereGreater("ab_bodies.memberclaimed", 0);
		
		$this->assertEquals("SELECT `applications`.`id` AS `id`, `memberlistoncurrent`, `memberlist`.`id` AS `curmemlistid` FROM `applications` LEFT JOIN `memberlist` ON LOWER(`memberlist`.`givenname`) = LOWER(`applications`.`firstname`) AND LOWER(`memberlist`.`surname`) = LOWER(`applications`.`lastname`) AND LOWER(`memberlist`.`bodycode`) = LOWER(`applications`.`bodycode`) INNER JOIN `ab_bodies` ON `ab_bodies`.`bodycode` = `applications`.`bodycode` WHERE `memberlistoncurrent` = '-' AND `ab_bodies`.`memberclaimed` > 0", $queryBuilder->toQuery());
	}
		
	/**
	 * From index.php
	 */
	public function testWhereInOrderAndLimit() {
		$queryBuilder = new QueryBuilder($this->db, "info");
		$queryBuilder->addField(array("id", "subject", "access", "dateadd", "datemodify"));
		$queryBuilder->addWhereIn("access", array("public", "aegee"));
		$queryBuilder->addOrderDesc("dateadd");
		$queryBuilder->setLimit(3);
		
		$this->assertEquals("SELECT `id`, `subject`, `access`, `dateadd`, `datemodify` FROM `info` WHERE `access` IN ('public', 'aegee') ORDER BY `dateadd` DESC LIMIT 3", $queryBuilder->toQuery());
	}
	
	/**
	 * From Exports/Visa.php
	 */
	public function testLeftJoinAs() {
		$queryBuilder = new QueryBuilder($this->db, "applications");
		$queryBuilder->addField("applications.id", "applicationid");
		$queryBuilder->addField("nationality.name", "nationality");
		$queryBuilder->addField("ab_bodies.bodyname", "body");
		$queryBuilder->addField("country.name", "country");
		$queryBuilder->addLeftJoin("countrycodes", "code", "applications.nationality", "nationality");
		$queryBuilder->addLeftJoin("countrycodes", "code", "applications.countrycode", "country");
		$queryBuilder->addLeftJoin("ab_bodies", "bodycode", "applications.bodycode");
		$queryBuilder->addWhereIn("accepted", array("yes", "pending", "confirmed"));
		$queryBuilder->addWhereEquals("visa", "yes");
		$queryBuilder->addOrderAsc("nationality");
		$queryBuilder->addOrderAsc("firstname");
		$queryBuilder->addOrderAsc("lastname");

		$this->assertEquals("SELECT `applications`.`id` AS `applicationid`, `nationality`.`name` AS `nationality`, `ab_bodies`.`bodyname` AS `body`, `country`.`name` AS `country` FROM `applications` LEFT JOIN `countrycodes` `nationality` ON `nationality`.`code` = `applications`.`nationality` LEFT JOIN `countrycodes` `country` ON `country`.`code` = `applications`.`countrycode` LEFT JOIN `ab_bodies` ON `ab_bodies`.`bodycode` = `applications`.`bodycode` WHERE `accepted` IN ('yes', 'pending', 'confirmed') AND `visa` = 'yes' ORDER BY `nationality` ASC, `firstname` ASC, `lastname` ASC", $queryBuilder->toQuery());
	}
	
	public function testWhereOr() {
		$queryBuilder = new QueryBuilder($this->db, "table");
		$queryBuilder->addField("field1");
		$queryBuilder->addField("field2");
		$queryBuilder->addWhere(new DBWhereOr(new DBWhereEquals("field1", "value1"), new DBWhereEquals("field2", "value2")));
		$queryBuilder->addWhere(new DBWhereIsNull("field3"));
		
		$this->assertEquals("SELECT `field1`, `field2` FROM `table` WHERE (`field1` = 'value1' OR `field2` = 'value2') AND `field3` IS NULL", $queryBuilder->toQuery());
	}
	
	public function testSum() {
		$queryBuilder = new QueryBuilder($this->db, "table");
		$queryBuilder->addField(new DBMethodSum("field1"), "sum");
		$queryBuilder->addGroup("field2");
		
		$this->assertEquals("SELECT SUM(`field1`) AS `sum` FROM `table` GROUP BY `field2`", $queryBuilder->toQuery());
	}
}
?>