<?php

/**
 * Created by PhpStorm.
 * User: filip
 * Date: 5/13/16
 * Time: 10:30 AM.
 */

namespace QueryTest;

use Doctrine\DBAL\Query\QueryBuilder;
use Query\Operator;

class QueryBuilderTest extends \DatabaseTestCase
{
    public function testGlobalFactoryFunction()
    {
        $this->assertInstanceOf('Query\QueryBuilder', sqlQueryBuilder());
        $this->assertNotSame(sqlQueryBuilder(), sqlQueryBuilder());
    }

    /**
     * @depends testGlobalFactoryFunction
     */
    public function testWhereUsesAnd()
    {
        $specStub = function ($field, $value) {
            return function (QueryBuilder $qb) use ($field, $value) {
                return $qb->expr()->eq($field, $value);
            };
        };

        $rowCount = sqlQueryBuilder()->select('*')->from('users')->where(
            $specStub('city', "'Vrchlabí'"),
            $specStub('figure', "'Y'")
        )->execute()->rowCount();

        $this->assertSame(2, $rowCount);
    }

    public function testOrderBySql()
    {
        $qb = sqlQueryBuilder();
        $qb->orderBySql('expression < 0 Asc');
        $this->assertSame(['expression < 0 Asc'], $qb->getQueryPart('orderBy'));
    }

    public function testMultiDirectValuesSql()
    {
        $qb = sqlQueryBuilder()
                ->insert('languages')
                ->multiDirectValues(
                    [
                        'id' => 've',
                        'name' => 'veverky',
                        'locale' => 've_VE',
                        'translate' => 0,
                    ]
                )
                ->multiDirectValues(
                    [
                        'id' => 'li',
                        'name' => 'lišky',
                        'locale' => 'li_LI',
                        'translate' => 0,
                    ])
                ->multiDirectValues(
                    [
                        'id' => 'ko',
                        'name' => 'kočky',
                        'locale' => 'ko_KO',
                        'translate' => 0,
                    ]);
        $this->assertSame('INSERT INTO languages (`id`,`name`,`locale`,`translate`) VALUES (:multi0_id, :multi0_name, :multi0_locale, :multi0_translate),(:multi1_id, :multi1_name, :multi1_locale, :multi1_translate),(:multi2_id, :multi2_name, :multi2_locale, :multi2_translate) ', $qb->getSQL());
    }

    public function testMultiValuesSql()
    {
        $qb = sqlQueryBuilder()
                ->insert('languages')
                ->multiValues(
                    [
                        [
                            'id' => 've',
                            'name' => 'veverky',
                            'locale' => 've_VE',
                            'translate' => 0,
                        ],
                        [
                            'id' => 'li',
                            'name' => 'lišky',
                            'locale' => 'li_LI',
                            'translate' => 0,
                        ],
                        [
                            'id' => 'ko',
                            'name' => 'kočky',
                            'locale' => 'ko_KO',
                            'translate' => 0,
                        ],
                    ]
                );
        $this->assertSame('INSERT INTO languages (id,name,locale,translate) VALUES (ve, veverky, ve_VE, 0),(li, lišky, li_LI, 0),(ko, kočky, ko_KO, 0) ', $qb->getSQL());
    }

    public function testDirectValues(): void
    {
        $params = [
            'foo' => 'fooFoo',
            'bar' => 10,
            'baz' => '1970-01-01 00:00:00',
        ];
        $queryBuilder = sqlQueryBuilder()
            ->insert('users')
            ->directValues($params);

        $this->assertSame('INSERT INTO users (`foo`,`bar`,`baz`) VALUES (:foo, :bar, :baz) ', $queryBuilder->getSQL());
        $this->assertSame($params, $queryBuilder->getParameters());
        $this->assertSame(
            <<<EOT
        INSERT INTO users (`foo`, `bar`, `baz`)
        VALUES
          (
            'fooFoo', 10, '1970-01-01 00:00:00'
          )
        EOT,
            $queryBuilder->getRunnableSQL()
        );
    }

    public function testForUpdate(): void
    {
        $queryBuilder = sqlQueryBuilder()
            ->select('count(*)', 'foo', 'baz')
            ->where('a', 'b.c')
            ->forUpdate();

        $this->assertSame('SELECT count(*), foo, baz WHERE (a) AND (b.c) FOR UPDATE', $queryBuilder->getSQL());
    }

    public function testCalcRows(): void
    {
        $queryBuilder = sqlQueryBuilder()
            ->select('*', 'foo')
            ->from('t')
            ->where('id >= :id')
                ->setParameter('id', 1)
            ->addCalcRows();

        $this->assertSame('SELECT SQL_CALC_FOUND_ROWS *, foo FROM t WHERE id >= :id', $queryBuilder->getSQL());
        $this->assertSame(['id' => 1], $queryBuilder->getParameters());
    }

    public function testOnDuplicateKeyUpdate(): void
    {
        $queryBuilder = sqlQueryBuilder()
            ->insert('foo')
            ->values(['username' => 'jarin'])
            ->onDuplicateKeyUpdate(['foo', 'bar']);

        $this->assertSame('INSERT INTO foo (username) VALUES (jarin)  ON DUPLICATE KEY UPDATE foo=VALUES(foo), bar=VALUES(bar) ', $queryBuilder->getSQL());
    }

    public function testOnDuplicateKeyUpdateException(): void
    {
        $this->expectException(\InvalidArgumentException::class);
        $this->expectExceptionMessage('Call of "onDuplicateKeyUpdate" is allowed only with insert query');
        sqlQueryBuilder()
            ->select('foo')
            ->values(['username' => 'jarin'])
            ->onDuplicateKeyUpdate(['foo', 'bar'])
            ->getSQL();
    }

    public function testSendMaster(): void
    {
        $queryBuilder = sqlQueryBuilder()
            ->select('foo')
            ->from('t')
            ->sendToMaster();

        $this->assertSame('SELECT foo FROM t -- maxscale route to master', $queryBuilder->getSQL());
    }

    public function testAddQueryBuilderParameters(): void
    {
        $queryBuilderForParameters = sqlQueryBuilder()
            ->setParameter('foo', 1)
            ->setParameter('bar', 'baz');

        $queryBuilder = sqlQueryBuilder()
            ->select('*')
            ->from('t')
            ->where('foo != :foo')
            ->orWhere('bar = :bar')
            ->addQueryBuilderParameters($queryBuilderForParameters);

        $this->assertSame('SELECT * FROM t WHERE (foo != :foo) OR (bar = :bar)', $queryBuilder->getSQL());
        $this->assertSame(
            <<<EOT
            SELECT
              *
            FROM
              t
            WHERE
              (foo != 1)
              OR (bar = 'baz')
            EOT,
            $queryBuilder->getRunnableSQL()
        );

        $this->assertSame($queryBuilderForParameters->getParameters(), $queryBuilder->getParameters());
    }

    public function testSetForceIndexForJoin(): void
    {
        $queryBuilder = sqlQueryBuilder()
            ->select('id')
            ->from('t')
            ->setForceIndexForJoin('a', 'a_table');

        // ignore when using non-existing join alias
        $this->assertSame('SELECT id FROM t', $queryBuilder->getSQL());

        $queryBuilder = sqlQueryBuilder()
            ->select('id', 'MAX(max_1, max_2) as j')
            ->from('t', 'a')
            ->leftJoin('a', 'products', 'p', 'p.id = a.id_product')
            ->setForceIndexForJoin('j', 'pricelist_product')
            ->setForceIndexForJoin('a', 'blbost');

        // ignore when using force index for from clause alias or select clause alias
        $this->assertSame('SELECT id, MAX(max_1, max_2) as j FROM t a LEFT JOIN products p ON p.id = a.id_product', $queryBuilder->getSQL());

        $queryBuilder = sqlQueryBuilder()
            ->select('id', 'MAX(max_1, max_2) as j')
            ->from('t', 'a')
            ->leftJoin('a', 'products', 'p', 'p.id = a.id_product')
            ->setForceIndexForJoin('p', 'PRIMARY');

        $this->assertSame('SELECT id, MAX(max_1, max_2) as j FROM t a LEFT JOIN products p FORCE INDEX (PRIMARY) ON p.id = a.id_product', $queryBuilder->getSQL());
    }

    public function testLimitDeleteThrowsMultitable01()
    {
        $qb = sqlQueryBuilder()->delete('orders', 'o')
            ->setMaxResults(100);

        $this->expectException(\Doctrine\DBAL\Exception::class);
        $qb->execute();
    }

    public function testLimitDeleteThrowsMultitable02()
    {
        $qb = sqlQueryBuilder()->delete('orders')
            ->join('orders', 'orders_history', 'oh', 'oh.id_order = orders.id')
            ->setMaxResults(100);

        $this->expectException(\Doctrine\DBAL\Exception::class);
        $qb->execute();
    }

    public function testLimitDelete()
    {
        $qb = sqlQueryBuilder()->delete('orders')
            ->setMaxResults(1000);

        $qb->execute(); // Should not throw
        $this->assertSame('DELETE FROM orders LIMIT 1000', $qb->getSQL());

        $qb = sqlQueryBuilder()->delete('orders_history')
            ->andWhere(Operator::exists(
                sqlQueryBuilder()->select('*')
                    ->from('orders', 'o')
                    ->where('o.id = id_order')
                    ->andWhere('o.date_updated < (NOW() - INTERVAL 5 YEAR)')
            ))
            ->andWhere('custom_data IS NOT NULL')
            ->andWhere("JSON_EXISTS(custom_data, '$.email_type')")
            ->andWhere("JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE'")
            ->orderBy('date', 'DESC')
            ->addOrderBy('id', 'ASC')
            ->setMaxResults(1000);

        $qb->execute(); // Should not throw
        $this->assertSame(
            "DELETE FROM orders_history WHERE (EXISTS (SELECT * FROM orders o WHERE (o.id = id_order) AND (o.date_updated < (NOW() - INTERVAL 5 YEAR)))) AND (custom_data IS NOT NULL) AND (JSON_EXISTS(custom_data, '$.email_type')) AND (JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE') ORDER BY date DESC, id ASC LIMIT 1000",
            $qb->getSQL(),
        );

        // Test normal delete still functional
        $qb = sqlQueryBuilder()->delete()
            ->from('orders_history', 'oh')
            ->andWhere(Operator::exists(
                sqlQueryBuilder()->select('*')
                    ->from('orders', 'o')
                    ->where('o.id = id_order')
                    ->andWhere('o.date_updated < (NOW() - INTERVAL 5 YEAR)')
            ))
            ->andWhere('custom_data IS NOT NULL')
            ->andWhere("JSON_EXISTS(custom_data, '$.email_type')")
            ->andWhere("JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE'");

        $qb->execute(); // Should not throw
        $this->assertSame(
            "DELETE oh FROM orders_history oh WHERE (EXISTS (SELECT * FROM orders o WHERE (o.id = id_order) AND (o.date_updated < (NOW() - INTERVAL 5 YEAR)))) AND (custom_data IS NOT NULL) AND (JSON_EXISTS(custom_data, '$.email_type')) AND (JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE')",
            $qb->getSQL(),
        );

        $qb = sqlQueryBuilder()->delete('orders_history', 'oh')
            ->andWhere(Operator::exists(
                sqlQueryBuilder()->select('*')
                    ->from('orders', 'o')
                    ->where('o.id = id_order')
                    ->andWhere('o.date_updated < (NOW() - INTERVAL 5 YEAR)')
            ))
            ->andWhere('custom_data IS NOT NULL')
            ->andWhere("JSON_EXISTS(custom_data, '$.email_type')")
            ->andWhere("JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE'");

        $qb->execute(); // Should not throw
        $this->assertSame(
            "DELETE oh FROM orders_history oh WHERE (EXISTS (SELECT * FROM orders o WHERE (o.id = id_order) AND (o.date_updated < (NOW() - INTERVAL 5 YEAR)))) AND (custom_data IS NOT NULL) AND (JSON_EXISTS(custom_data, '$.email_type')) AND (JSON_EXTRACT(custom_data, '$.email_type') <> 'ORDER_CREATE')",
            $qb->getSQL(),
        );
    }

    public function testLimitUpdate()
    {
        $qb = sqlQueryBuilder()
            ->update('orders_history', 'oh')
            ->set('oh.comment', 'NULL')
            ->orderBy('oh.date')
            ->setMaxResults(10000);

        $qb->execute();
        $this->assertSame('UPDATE orders_history oh SET oh.comment = NULL ORDER BY oh.date ASC LIMIT 10000', $qb->getSQL());

        $qb = sqlQueryBuilder()
            ->update('orders_history', 'oh')
            ->set('oh.comment', 'NULL');

        $qb->execute();
        $this->assertSame('UPDATE orders_history oh SET oh.comment = NULL', $qb->getSQL());

        $qb = sqlQueryBuilder()
            ->update('orders_history', 'oh')
            ->join('oh', 'orders', 'o', 'oh.id_order = o.id')
            ->set('oh.comment', 'NULL');

        $qb->execute();
        $this->assertSame('UPDATE orders_history oh INNER JOIN orders o ON oh.id_order = o.id SET oh.comment = NULL', $qb->getSQL());

        $qb = sqlQueryBuilder()
            ->update('orders_history', 'oh')
            ->join('oh', 'orders', 'o', 'oh.id_order = o.id')
            ->set('oh.comment', 'NULL')
            ->orderBy('oh.date', 'DESC')
            ->addOrderBy('o.date_updated', 'ASC')
            ->setMaxResults(10000);

        // https://mariadb.com/kb/en/update/#description
        // "Until MariaDB 10.3.2, for the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions.
        //  In this case, ORDER BY and LIMIT cannot be used. This restriction was lifted in MariaDB 10.3.2 and both clauses can be used with multiple-table updates."
        $qb->execute(); // Should not throw
        $this->assertSame('UPDATE orders_history oh INNER JOIN orders o ON oh.id_order = o.id SET oh.comment = NULL ORDER BY oh.date DESC, o.date_updated ASC LIMIT 10000', $qb->getSQL());
    }
}