<?php

class QueryMakerTest extends \PHPUnit_Framework_TestCase
{
    public function testSelectQuery()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $criteria = [
            [
                'logicalOperator' => '',
                'column'          => 'parentId',
                'value'           => 456,
                'type'            => \PDO::PARAM_INT,
            ],
            [
                'column' => 'status',
                'value'  => 'active',
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], 'name ASC', 5, 10);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE parentId = :parentId1 AND status = :status2 AND archivedAt IS NULL ORDER BY name ASC LIMIT :start, :limit;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column' => 'parentId',
                'value'  => 50,
                'type'   => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], 'name ASC', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE parentId = :parentId1 ORDER BY name ASC;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'testColumn1',
                'operator' => 'NOT LIKE',
                'value'    => 'test value',
                'type'     => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], 'name ASC', 0, 10);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE testColumn1 NOT LIKE :testColumn11 ORDER BY name ASC LIMIT :limit;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column' => 'id',
                'value'  => 50,
                'type'   => \PDO::PARAM_INT,
            ],
            [
                'logicalOperator' => 'OR',
                'column'          => 'id',
                'value'           => 37,
                'type'            => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE id = :id1 OR id = :id2;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
            [
                'logicalOperator' => 'OR',
                'column'          => 'live',
                'value'           => 1,
                'type'            => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE archivedAt IS NULL OR live = :live2;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key' => 'live',
                ],
            ],
            [
                'logicalOperator' => 'OR',
                'column'          => 'live',
                'value'           => 1,
                'type'            => \PDO::PARAM_INT,
                'nested'          => [
                    'key' => 'live',
                ],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE (archivedAt IS NULL OR live = :live2);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key' => 'live1',
                ],
            ],
            [
                'column' => 'live',
                'value'  => 1,
                'type'   => \PDO::PARAM_INT,
                'nested' => [
                    'key'    => 'live2',
                    'before' => 'AND',
                ],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE (archivedAt IS NULL) AND (live = :live2);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
            [
                'column' => 'live',
                'value'  => 1,
                'type'   => \PDO::PARAM_INT,
                'nested' => [
                    'key'    => 'live2',
                    'before' => 'AND',
                ],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE archivedAt IS NULL AND (live = :live2);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key'   => 'live2',
                    'after' => 'AND',
                ],
            ],
            [
                'column' => 'live',
                'value'  => 1,
                'type'   => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE (archivedAt IS NULL) AND live = :live2;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key' => 'live2',
                ],
            ],
            [
                'column'   => 'archivedAtTest',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key' => 'live2',
                ],
            ],
            [
                'column' => 'live',
                'value'  => 1,
                'type'   => \PDO::PARAM_INT,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE (archivedAt IS NULL AND archivedAtTest IS NULL) AND live = :live3;';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column' => 'live',
                'value'  => 1,
                'type'   => \PDO::PARAM_INT,
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key'    => 'live2',
                    'before' => 'OR',
                ],
            ],
            [
                'column'   => 'archivedAtTest',
                'operator' => 'IS NULL',
                'nested'   => [
                    'key' => 'live2',
                ],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE live = :live1 OR (archivedAt IS NULL AND archivedAtTest IS NULL);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => 2,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE id IN (:id1);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => [2, 3, 24],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE id IN (:id10,:id11,:id12);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => [2, 3, 24],
            ],
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => 2,
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE id IN (:id10,:id11,:id12) AND id IN (:id2);';

        $this->assertEquals($expectedQuery, $query);

        $criteria = [
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => 2,
            ],
            [
                'column'   => 'id',
                'operator' => 'IN',
                'value'    => [2, 3, 24],
            ],
        ];

        $query = $queryMaker->selectQuery($criteria, [], '', 0, 0);
        $expectedQuery = 'SELECT * FROM `testTable` AS `testTable` WHERE id IN (:id1) AND id IN (:id20,:id21,:id22);';

        $this->assertEquals($expectedQuery, $query);

        $tables = [
            'cj_states' => [
                'name' => 'cj_states',
            ],
            'cj_cities' => [
                'name' => 'cj_cities',
                'on'   => [
                    '`cj_states`.`id`',
                    '`cj_cities`.`stateId`',
                ],
            ],
            'cj_countries' => [
                'name' => 'cj_countries',
                'on'   => [
                    '`cj_countries`.`code`',
                    '`cj_states`.`countryCode`',
                ],
            ],
        ];

        $queryMaker = new \CodeJetter\core\database\QueryMaker($tables);
        $query = $queryMaker->selectQuery([], '*');
        $expectedQuery = 'SELECT * FROM `cj_states` AS `cj_states` JOIN `cj_cities` AS `cj_cities` ON `cj_states`.`id` = `cj_cities`.`stateId` JOIN `cj_countries` AS `cj_countries` ON `cj_countries`.`code` = `cj_states`.`countryCode`;';

        $this->assertEquals($expectedQuery, $query);

        $tables = [
            'cj_states' => [
                'name' => 'cj_states',
            ],
            'cj_cities' => [
                'name' => 'cj_cities',
                'on'   => [
                    '`cj_states`.`id`',
                    '`cj_cities`.`stateId`',
                ],
            ],
            'cj_countries' => [
                'name' => 'cj_countries',
                'on'   => [
                    '`cj_countries`.`code`',
                    '`cj_states`.`countryCode`',
                ],
            ],
        ];

        $fromColumns = [
            'cj_states.name',
            'cj_countries.name',
        ];

        $queryMaker = new \CodeJetter\core\database\QueryMaker($tables);
        $query = $queryMaker->selectQuery([], $fromColumns);
        $expectedQuery = 'SELECT cj_states.name, cj_countries.name FROM `cj_states` AS `cj_states` JOIN `cj_cities` AS `cj_cities` ON `cj_states`.`id` = `cj_cities`.`stateId` JOIN `cj_countries` AS `cj_countries` ON `cj_countries`.`code` = `cj_states`.`countryCode`;';

        $this->assertEquals($expectedQuery, $query);

        $tables = [
            'cj_states' => [
                'name' => 'cj_states',
            ],
            'cj_cities' => [
                'name' => 'cj_cities',
                'on'   => [
                    'cj_states.id',
                    'cj_cities.stateId',
                ],
            ],
            'cj_countries' => [
                'name' => 'cj_countries',
                'on'   => [
                    'cj_countries.code',
                    'cj_states.countryCode',
                ],
            ],
        ];

        $fromColumns = [
            'cj_states.name',
            'cj_countries.name',
        ];

        $criteria = [
            [
                'column' => 'cj_states.status',
                'value'  => 'active',
            ],
            [
                'column'   => 'cj_states.archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $queryMaker = new \CodeJetter\core\database\QueryMaker($tables);
        $query = $queryMaker->selectQuery($criteria, $fromColumns, 'cj_states.status ASC', 0, 10);
        $expectedQuery = 'SELECT cj_states.name, cj_countries.name FROM `cj_states` AS `cj_states` JOIN `cj_cities` AS `cj_cities` ON cj_states.id = cj_cities.stateId JOIN `cj_countries` AS `cj_countries` ON cj_countries.code = cj_states.countryCode WHERE cj_states.status = :cj_states_status1 AND cj_states.archivedAt IS NULL ORDER BY cj_states.status ASC LIMIT :limit;';

        $this->assertEquals($expectedQuery, $query);
    }

    public function testUpdateQueryWithException()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $criteria = [
            [
                'logicalOperator' => '',
                'column'          => 'parentId',
                'value'           => 456,
                'type'            => \PDO::PARAM_INT,
            ],
            [
                'column' => 'status',
                'value'  => 'active',
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $fieldsValues = [

        ];

        $this->setExpectedException('Exception', 'fieldsValues cannot be empty in updateQuery function');

        $queryMaker->updateQuery($criteria, $fieldsValues, 0, 0);
    }

    public function testUpdateQuery()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $criteria = [
            [
                'logicalOperator' => '',
                'column'          => 'parentId',
                'value'           => 456,
                'type'            => \PDO::PARAM_INT,
            ],
            [
                'column' => 'status',
                'value'  => 'active',
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $fieldsValues = [
            [
                'column' => 'name',
                'value'  => 'dummyName',
            ],
            [
                'column' => 'email',
                'value'  => 'dummyValue',
            ],
            [
                'column' => 'phone',
                'value'  => 'dummyPhone',
            ],
        ];

        $query = $queryMaker->updateQuery($criteria, $fieldsValues, 0, 0);
        $expectedQuery = 'UPDATE testTable SET name = :name, email = :email, phone = :phone WHERE parentId = :parentId1 AND status = :status2 AND archivedAt IS NULL;';

        $this->assertEquals($expectedQuery, $query);
    }

    public function testInsertQuery()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $fieldsValues = [
            [
                'column' => 'name',
                'value'  => 'dummyName',
            ],
            [
                'column' => 'email',
                'value'  => 'dummyValue',
            ],
            [
                'column' => 'phone',
                'value'  => 'dummyPhone',
            ],
        ];

        $query = $queryMaker->insertQuery($fieldsValues);
        $expectedQuery = 'INSERT INTO testTable (name,email,phone) VALUES (:name,:email,:phone);';

        $this->assertEquals($expectedQuery, $query);
    }

    public function testDeleteQuery()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $criteria = [
            [
                'logicalOperator' => '',
                'column'          => 'parentId',
                'value'           => 456,
                'type'            => \PDO::PARAM_INT,
            ],
            [
                'column' => 'status',
                'value'  => 'active',
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $query = $queryMaker->deleteQuery($criteria, 0, 0);
        $expectedQuery = 'DELETE FROM testTable WHERE parentId = :parentId1 AND status = :status2 AND archivedAt IS NULL;';

        $this->assertEquals($expectedQuery, $query);
    }

    public function testCountQuery()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');

        $criteria = [
            [
                'logicalOperator' => '',
                'column'          => 'parentId',
                'value'           => 456,
                'type'            => \PDO::PARAM_INT,
            ],
            [
                'column' => 'status',
                'value'  => 'active',
            ],
            [
                'column'   => 'archivedAt',
                'operator' => 'IS NULL',
            ],
        ];

        $query = $queryMaker->countQuery($criteria);
        $expectedQuery = 'SELECT COUNT(*) FROM `testTable` AS `testTable` WHERE parentId = :parentId1 AND status = :status2 AND archivedAt IS NULL;';

        $this->assertEquals($expectedQuery, $query);
    }

    public function testGetTable()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');
        $this->assertEquals(['name' => 'testTable'], $queryMaker->getTable());

        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');
        $this->assertEquals(['name' => 'testTable'], $queryMaker->getTable('testTable'));

        $queryMaker = new \CodeJetter\core\database\QueryMaker([
            'testTable' => [
                'name' => 'testTableName',
            ],
            'testTable1' => [
                'name' => 'testTableName',
            ],
        ]);

        $this->assertEquals(['name' => 'testTableName'], $queryMaker->getTable());

        $queryMaker = new \CodeJetter\core\database\QueryMaker([
            'testTable' => [
                'name' => 'testTableName',
            ],
            'testTable1' => [
                'name' => 'testTableName',
            ],
        ]);

        $this->assertEquals(['name' => 'testTableName'], $queryMaker->getTable('testTable'));
    }

    public function testGetTables()
    {
        $queryMaker = new \CodeJetter\core\database\QueryMaker('testTable');
        $this->assertEquals(['testTable' => ['name' => 'testTable']], $queryMaker->getTables());

        $queryMaker = new \CodeJetter\core\database\QueryMaker([
            'testTable' => [
                'name' => 'testTableName',
            ],
        ]);

        $this->assertEquals(['testTable' => ['name' => 'testTableName']], $queryMaker->getTables());
    }
}
