<?php
/**
 * Copyright (c) 2004-2015, EMESA BV
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are PROHIBITED without prior written permission from
 * the author. This product may NOT be used anywhere and on any computer
 * except the server platform of EMESA BV. If you received this code
 * accidentally and without intent to use it, please report this
 * incident to the author by email.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 */
/**
 * ${DESCRIPTION}
 *
 * @author Victor Bolshov <victor.bolshov@emesa.nl>
 * @phpcs
 */


namespace tinyorm\test;

use tinyorm\Bind;
use tinyorm\Select;

class SelectTest extends BaseTestCase
{
    const ROWCOUNT = 10;
    const DENOMINATOR = 3;
    const ZEROS = 4;
    const ONES = 3;
    const TWOS = 3;
    const THREES = 3;
    protected function setUp()
    {
        parent::setUp();
        $this->connection->exec("ALTER TABLE test AUTO_INCREMENT = 1");
        $this->connection->exec("ALTER TABLE test2 AUTO_INCREMENT = 1");
        for ($i = 0; $i < self::ROWCOUNT; $i++) {
            $c_int = $i % self::DENOMINATOR;
            $c_varchar = "varchar " . $c_int;
            $c_unique = "unique " . $i;
            $this->connection->exec("INSERT INTO test (c_varchar, c_int, c_unique)
              VALUES ('$c_varchar', $c_int, '$c_unique')");
            $this->connection->exec("INSERT INTO test2 (c_varchar, c_int, c_unique)
              VALUES ('$c_varchar', $c_int, '$c_unique')");
        }
    }

    function testBasicSelect()
    {
        $rows = $this->createSelect("test")->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ROWCOUNT, count($rows));
    }

    function testBasicCount()
    {
        $rowCount = $this->createSelect("test")->count();
        $this->assertEquals(self::ROWCOUNT, $rowCount);
    }

    function testWhereCondition()
    {
        $select = $this->createSelect("test")->where("c_int = ?", 0);
        $this->assertEquals(self::ZEROS, $select->count());
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ZEROS, count($rows));
    }

    function testWhereConditionWithBind()
    {
        $select = $this->createSelect("test")->where("c_int = ?", Bind::int(0));
        $this->assertEquals(self::ZEROS, $select->count());
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ZEROS, count($rows));
    }

    function testLimit()
    {
        $select = $this->createSelect("test")->limit(1);
        $this->assertEquals(self::ROWCOUNT, $select->count());
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(1, count($rows));
    }

    function testOrderBy()
    {
        $select = $this->createSelect("test")->orderBy("c_unique");
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        for ($i = 0; $i < self::ROWCOUNT; $i++) {
            $this->assertEquals("unique $i", $rows[$i]["c_unique"]);
        }
    }

    function testOffset()
    {
        $offset = 5;
        $select = $this->createSelect("test")->orderBy("c_unique")->limit(10)->offset($offset);
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        for ($i = 0; $i < self::ROWCOUNT - $offset; $i++) {
            $val = "unique " . ($i + $offset);
            $this->assertEquals($val, $rows[$i]["c_unique"]);
        }
    }

    function testGroupBy()
    {
        $select = $this->createSelect("test")->groupBy("c_int");
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::DENOMINATOR, count($rows));
    }

    function testCountWithExpression()
    {
        $select = $this->createSelect("test")->groupBy("c_int");
        $this->assertEquals(self::DENOMINATOR, $select->count("DISTINCT c_int"));
    }

    function testHaving()
    {
        $select = $this->createSelect("test")->groupBy("c_int")->having("c_int = 0");
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(1, count($rows));
    }

    function testJoin()
    {
        $select = $this->createSelect("test")->join("JOIN test2 USING (c_unique)");
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ROWCOUNT, count($rows));
    }

    function testInClause()
    {
        $select = $this->createSelect("test")->where("c_int IN (?)", [0, 1]);
        $expectedRowCount = self::ZEROS + self::ONES;
        $this->assertEquals($expectedRowCount, $select->count());
    }

    function testGroupByWithParameters()
    {
        $select = $this->createSelect("test")->groupBy("c_int = ?", 0);
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(2, count($rows));
    }

    function testHavingWithParameters()
    {
        $select = $this->createSelect("test")->groupBy("c_int")->having("c_int = ?", 0);
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(1, count($rows));
    }

    function testJoinWithParameters()
    {
        $select = $this->createSelect("test")->join("JOIN test2 ON (test.id = test2.id AND test2.c_int = ?)", "", 0);
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ZEROS, count($rows));
    }

    function testColsWithParameters()
    {
        $addOn = "ADDON";
        $select = (new Select("test", "test.*, ? AS add_on", $addOn))->setConnection($this->connection);
        $rows = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ROWCOUNT, count($rows));
        $this->assertEquals($addOn, $rows[0]["add_on"]);
    }

    function testQueryId()
    {
        $select = $this->createSelect("test")->setId("TEST_ID");
        $str = $select->__toString();
        $this->assertTrue(substr_count($str, "TEST_ID") == 1);
        $stmt = $select->execute();
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $this->assertEquals(self::ROWCOUNT, count($rows));

        $stmt = $select->getCountStatement();
        $sql = $stmt->queryString;
        $this->assertTrue(substr_count($sql, "TEST_ID") == 1);
        $this->assertEquals(self::ROWCOUNT, $stmt->fetchColumn());
    }

    /**
     * @param $table
     * @return Select
     */
    protected function createSelect($table)
    {
        return (new Select($table))->setConnection($this->connection);
    }
}