<?php

namespace Velis\Test\Db;

use PHPUnit\Framework\TestCase;
use User\User;
use Velis\Db\NullValue;
use Velis\Db\QueryBuilder;
use Velis\Db\QueryBuilder\Enums\JoinExpressionType;

class QueryBuilderTest extends TestCase
{
    private QueryBuilder $query;

    public function setUp(): void
    {
        parent::setUp();
        $db = $this->createMock(\Velis\Db\Db::class);
        $this->query = new QueryBuilder($db);
    }

    // public function from(Closure|QueryBuilderInterface|string $table, ?string $as = null): self;
    public function testFromClosure()
    {
        $this->query->from(function ($query) {
            $query->from('users');
        });

        $this->assertEqualsIgnoringCase('select * from (select * from users)', $this->query->toSql());
    }

    public function testFromQueryBuilderInterface()
    {
        $db = $this->createMock(\Velis\Db\Db::class);
        $query = new QueryBuilder($db);
        $query2 = new QueryBuilder($db);
        $query2->from('users');
        $query->from($query2);

        $this->assertEqualsIgnoringCase('select * from (select * from users)', $query->toSql());
    }

    public function testFromTable()
    {
        $this->query->from('users');

        $this->assertEqualsIgnoringCase('select * from users', $this->query->toSql());
    }

    public function testFromTableWithAlias()
    {
        $this->query->from('users', 'u');

        $this->assertEqualsIgnoringCase('select * from users as u', $this->query->toSql());
    }

    // public function select(?array $columns = ['*']): self;
    public function testSelect()
    {
        $this->query
            ->from('acl.users')
            ->select(['id', 'name']);

        $this->assertEqualsIgnoringCase('select id, name from acl.users', $this->query->toSql());
    }

    public function testSelectShouldIgnoreNull()
    {
        $this->query
            ->from('acl.users')
            ->select(null);

        $this->assertEqualsIgnoringCase('select * from acl.users', $this->query->toSql());
    }

    // public function where(Closure|string|array $column, mixed $operator = null, mixed $value = null, string $boolean = 'and'): self;
    public function testWhereWith2Params()
    {
        $this->query
            ->from('acl.users')
            ->where('id', 1);

        $this->assertEqualsIgnoringCase('select * from acl.users where id = :id', $this->query->toSql());
        $this->assertEquals(['id' => 1], $this->query->getParams());
    }

    public function testWhereWith3Params()
    {
        $this->query
            ->from('acl.users')
            ->where('id', '>=', 1);

        $this->assertEqualsIgnoringCase('select * from acl.users where id >= :id', $this->query->toSql());
        $this->assertEquals(['id' => 1], $this->query->getParams());
    }

    public function testWhereWithArray()
    {
        $this->query
            ->from('acl.users')
            ->where(['id' => 1, 'name' => 'John']);

        $this->assertEqualsIgnoringCase('select * from acl.users where id = :id and name = :name', $this->query->toSql());
        $this->assertEquals(['id' => 1, 'name' => 'John'], $this->query->getParams());
    }

    public function testWhereWithNull()
    {
        $this->query
            ->from('acl.users')
            ->where('user_id', null)
            ->where('date_added', new NullValue());

        $this->assertEqualsIgnoringCase('select * from acl.users where user_id IS NULL AND date_added IS NULL', $this->query->toSql());
    }

    public function testWhereWithClosure()
    {
        $this->query
            ->from('acl.users')
            ->where(function ($query) {
                $query->where('id', 1);
                $query->orWhere('name', 'John');
            });

        $this->assertEqualsIgnoringCase('select * from acl.users where (id = :id or name = :name)', $this->query->toSql());
        $this->assertEquals(['id' => 1, 'name' => 'John'], $this->query->getParams());
    }

    public function testWhereWithValueAsArray()
    {
        $this->query
            ->from('acl.users')
            ->where('id', [1, 2, 3])
            ->where('name', 'ILIKE', '%John%')
            ->where('age', '>', 18)
            ->where('role', 'IN', ['admin', 'user']);

        $this->assertEqualsIgnoringCase('select * from acl.users where id in (:id_0, :id_1, :id_2) and name ILIKE :name and age > :age and role in (:role_0, :role_1)', $this->query->toSql());
        $this->assertEquals(['id_0' => 1, 'id_1' => 2, 'id_2' => 3, 'name' => '%John%', 'age' => 18, 'role_0' => 'admin', 'role_1' => 'user'], $this->query->getParams());
    }

    public function testWhereRaw()
    {
        $this->query
            ->from('acl.users')
            ->whereRaw('user_id = :user_id', ['user_id' => 1]);

        $this->assertEqualsIgnoringCase('select * from acl.users where user_id = :user_id', $this->query->toSql());
        $this->assertEquals(['user_id' => 1], $this->query->getParams());
    }

    public function testWhereWithEmptyArray()
    {
        $this->query
            ->from('acl.users')
            ->where([]);

        $this->assertEqualsIgnoringCase('select * from acl.users', $this->query->toSql());
        $this->assertEquals([], $this->query->getParams());
    }

    public function testWhereWithEmptyArrayAsValue()
    {
        $this->query
            ->from('acl.users')
            ->where('id', []);

        $this->assertEqualsIgnoringCase('select * from acl.users', $this->query->toSql());
        $this->assertEquals([], $this->query->getParams());
    }

    public function testWhereWithCompositeType()
    {
        $this->query
            ->from('acl.user_tab')
            ->where('(name).pl', 'ILIKE', '%John%');

        $this->assertEqualsIgnoringCase("select * from acl.user_tab where (name).pl ILIKE :_name__pl", $this->query->toSql());
        $this->assertEquals(['_name__pl' => '%John%'], $this->query->getParams());
    }

    public function testWhereWithCollidingIds()
    {
        $this->query
            ->from('acl.user_tab')
            ->where('user_id', '<', 15)
            ->whereRaw('user_id > :user_id', ['user_id' => 10]);

        $this->assertEqualsIgnoringCase("select * from acl.user_tab where user_id < :user_id AND user_id > :user_id_1", $this->query->toSql());
        $this->assertEquals(['user_id' => 15, 'user_id_1' => 10], $this->query->getParams());
    }

    // public function orWhere(Closure|string|array $column, mixed $operator = null, mixed $value = null): self;
    public function testOrWhere()
    {
        $this->query
            ->from('acl.users')
            ->where('id', 1)
            ->orWhere('name', 'John')
            ->orWhere('age', '>', 18);

        $this->assertEqualsIgnoringCase('select * from acl.users where id = :id or name = :name or age > :age', $this->query->toSql());
        $this->assertEquals(['id' => 1, 'name' => 'John', 'age' => 18], $this->query->getParams());
    }

    // public function join(string $table, string $first, ?string $operator = null, ?string $second = null, string $type = 'inner'): self;
    public function testJoin()
    {
        $this->query
            ->from('acl.users')
            ->join('acl.roles', 'users.role_id', 'roles.id');

        $this->assertEqualsIgnoringCase('select * from acl.users inner join acl.roles on users.role_id = roles.id', $this->query->toSql());
    }

    public function testJoinWith2Params()
    {
        $this->query
            ->from('acl.users')
            ->join('acl.roles', 'role_id');

        $this->assertEqualsIgnoringCase('select * from acl.users inner join acl.roles using (role_id)', $this->query->toSql());
    }

    public function testJoinWith4Params()
    {
        $this->query
            ->from('acl.users')
            ->join('acl.roles', 'users.role_id', '<=', 'roles.id', JoinExpressionType::LEFT);

        $this->assertEqualsIgnoringCase('select * from acl.users left join acl.roles on users.role_id <= roles.id', $this->query->toSql());
    }

    // public function limit(int $value): self;
    public function testLimit()
    {
        $this->query
            ->from('acl.users')
            ->limit(10);

        $this->assertEqualsIgnoringCase('select * from acl.users limit 10', $this->query->toSql());
    }

    // public function offset(int $value): self;
    public function testOffset()
    {
        $this->query
            ->from('acl.users')
            ->offset(10);

        $this->assertEqualsIgnoringCase('select * from acl.users offset 10', $this->query->toSql());
    }

    public function testGet()
    {
        $db = $this->createMock(\Velis\Db\Db::class);
        $db->expects($this->once())
            ->method('getAll')
            ->with('SELECT * FROM acl.user_tab WHERE user_id = :user_id', ['user_id' => 1])
            ->willReturn([]);

        $query = new QueryBuilder($db);
        $query
            ->from('acl.user_tab')
            ->where('user_id', 1);

        $this->assertEquals([], $query->get());
    }

    public function testGetMapped()
    {
        $db = $this->createMock(\Velis\Db\Db::class);
        $db->expects($this->once())
            ->method('getAll')
            ->with('SELECT * FROM acl.user_tab WHERE user_id = :user_id', ['user_id' => 1])
            ->willReturn([
                ['user_id' => 123, 'name' => 'Velis']
            ]);

        $query = new QueryBuilder($db);
        $query
            ->setMapperClass(User::class)
            ->from('acl.user_tab')
            ->where('user_id', 1);

        $result = $query->get();
        $this->assertCount(1, $result);
        $this->assertArrayHasKey(123, $result);
        $this->assertInstanceOf(User::class, $result[123]);
        $this->assertEquals('Velis', $result[123]->name);
    }

    /**
     * Complex query using QueryBuilder
     *
     * SELECT * FROM acl.user_tab AS ut
     * INNER JOIN acl.role_tab rt ON ut.role_id = rt.role_id
     * WHERE (
     *  user_id = :user_id
     *  OR role_id IN (:role_id_0, :role_id_1, :role_id_2)
     *  OR (name = :name AND age > :age)
     * )
     */
    public function testComplexQuery()
    {
        $this->query
            ->from('acl.user_tab', 'ut')
            ->join('acl.role_tab rt', 'ut.role_id', 'rt.role_id')
            ->where(function ($query) {
                $query
                    ->where('user_id', 1)
                    ->orWhere('role_id', [1, 2, 3])
                    ->orWhere(function ($query2) {
                        $query2
                            ->where('name', 'John')
                            ->where('age', '>', 18);
                    });
            });

        $this->assertEquals('SELECT * FROM acl.user_tab AS ut INNER JOIN acl.role_tab rt ON ut.role_id = rt.role_id WHERE (user_id = :user_id OR role_id IN (:role_id_0, :role_id_1, :role_id_2) OR (name = :name AND age > :age))', $this->query->toSql());
    }
}
