<?php

namespace Tests\AnyDataset\Store;

use ByJG\AnyDataset\Db\DbCached;
use ByJG\AnyDataset\Db\Factory;
use ByJG\AnyDataset\Db\Helpers\DbSqliteFunctions;
use ByJG\Util\Uri;
use PHPUnit\Framework\TestCase;

class PdoSqliteTest extends TestCase
{
    /** @var \ByJG\AnyDataset\Db\DbDriverInterface */
    protected $dbDriver;

    public function setUp(): void
    {
        $this->dbDriver = Factory::getDbRelationalInstance('sqlite:///tmp/test.db');

        $this->dbDriver->execute(
            "create table users (\n            id integer primary key  autoincrement, \n            name varchar(45), \n            createdate datetime);"
        );
        $this->dbDriver->execute("insert into users (name, createdate) values ('John Doe', '2017-01-02')");
        $this->dbDriver->execute("insert into users (name, createdate) values ('Jane Doe', '2017-01-04')");
        $this->dbDriver->execute("insert into users (name, createdate) values ('JG', '1974-01-26')");


        $this->dbDriver->execute(
            "create table info (\n            id integer primary key  autoincrement,\n            iduser INTEGER,\n            property varchar(45));"
        );
        $this->dbDriver->execute("insert into info (iduser, property) values (1, 'xxx')");
        $this->dbDriver->execute("insert into info (iduser, property) values (1, 'ggg')");
        $this->dbDriver->execute("insert into info (iduser, property) values (3, 'bbb')");
    }

    public function tearDown(): void
    {
        unlink('/tmp/test.db');
    }

    public function testGetIterator()
    {
        $iterator = $this->dbDriver->getIterator('select * from info');
        $expected =
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx'],
                [ 'id'=> 2, 'iduser' => 1, 'property' => 'ggg'],
                [ 'id'=> 3, 'iduser' => 3, 'property' => 'bbb'],
            ];

        // To Array
        $this->assertEquals(
            $expected,
            $iterator->toArray()
        );

        // While
        $iterator = $this->dbDriver->getIterator('select * from info');
        $i = 0;
        while ($iterator->hasNext()) {
            $row = $iterator->moveNext();
            $this->assertEquals($expected[$i++], $row->toArray());
        }

        // Foreach
        $iterator = $this->dbDriver->getIterator('select * from info');
        $i = 0;
        foreach ($iterator as $row) {
            $this->assertEquals($expected[$i++], $row->toArray());
        }
    }

    public function testGetIteratorFilter()
    {
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 1]);
        $expected =
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx'],
                [ 'id'=> 2, 'iduser' => 1, 'property' => 'ggg'],
            ];

        // To Array
        $this->assertEquals(
            $expected,
            $iterator->toArray()
        );

        // While
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 1]);
        $i = 0;
        while ($iterator->hasNext()) {
            $row = $iterator->moveNext();
            $this->assertEquals($expected[$i++], $row->toArray());
        }

        // Foreach
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 1]);
        $i = 0;
        foreach ($iterator as $row) {
            $this->assertEquals($expected[$i++], $row->toArray());
        }
    }

    public function testGetIteratorNotFound()
    {
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 5]);

        // To Array
        $this->assertEquals(
            [],
            $iterator->toArray()
        );

        // While
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 5]);
        $this->assertFalse($iterator->hasNext());

        // Foreach
        $iterator = $this->dbDriver->getIterator('select * from info where iduser = :id', ['id' => 5]);
        $i = 0;
        foreach ($iterator as $row) {
            $i++;
        }
        $this->assertEquals(0, $i);
    }

    public function testGetScalar()
    {
        $count1 = $this->dbDriver->getScalar('select count(*) from info');
        $this->assertEquals(3, $count1);

        $count2 = $this->dbDriver->getScalar('select count(*) from info where iduser = :id', ['id' => 1]);
        $this->assertEquals(2, $count2);

        $count3 = $this->dbDriver->getScalar('select count(*) from info where iduser = :id', ['id' => 5]);
        $this->assertEquals(0, $count3);
    }

    public function testExecute()
    {
        $this->dbDriver->execute("insert into users (name, createdate) values ('Another', '2017-05-11')");
        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another']);

        $this->assertEquals(
            [
                ['id' => 4, 'name' => 'Another', 'createdate' => '2017-05-11'],
            ],
            $iterator->toArray()
        );
    }

    public function testExecuteAndGetId()
    {
        $newId = $this->dbDriver->executeAndGetId("insert into users (name, createdate) values ('Another', '2017-05-11')");

        $this->assertEquals(4, $newId);
        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another']);

        $this->assertEquals(
            [
                ['id' => 4, 'name' => 'Another', 'createdate' => '2017-05-11'],
            ],
            $iterator->toArray()
        );
    }

    public function testGetDbHelper()
    {
        $helper = $this->dbDriver->getDbHelper();
        $this->assertInstanceOf(DbSqliteFunctions::class, $helper);
    }

    public function testTransaction()
    {
        $this->dbDriver->beginTransaction();
        $newId = $this->dbDriver->executeAndGetId("insert into users (name, createdate) values ('Another', '2017-05-11')");
        $this->assertEquals(4, $newId);
        $this->dbDriver->commitTransaction();

        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another']);

        $this->assertEquals(
            [
                ['id' => 4, 'name' => 'Another', 'createdate' => '2017-05-11'],
            ],
            $iterator->toArray()
        );
    }

    public function testTransaction2()
    {
        $this->dbDriver->beginTransaction();
        $newId = $this->dbDriver->executeAndGetId("insert into users (name, createdate) values ('Another', '2017-05-11')");
        $this->assertEquals(4, $newId);
        $this->dbDriver->rollbackTransaction();

        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another']);
        $this->assertFalse($iterator->hasNext());
    }

    public function testTransactionTwoContext()
    {
        // Context 1
        $this->dbDriver->beginTransaction();
        $newId = $this->dbDriver->executeAndGetId("insert into users (name, createdate) values ('Another', '2017-05-11')");
        $this->assertEquals(4, $newId);
        $this->dbDriver->rollbackTransaction();

        // Context 2
        $context2 = Factory::getDbRelationalInstance('sqlite:///tmp/test.db');
        $context2->beginTransaction();
        $newId = $context2->executeAndGetId("insert into users (name, createdate) values ('Another2', '2017-04-11')");
        $this->assertEquals(4, $newId);
        $context2->commitTransaction();

        // Check values
        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another']);
        $this->assertFalse($iterator->hasNext());

        $iterator = $this->dbDriver->getIterator('select * from users where name = [[name]]', ['name' => 'Another2']);
        $this->assertEquals(
            [
                ['id' => 4, 'name' => 'Another2', 'createdate' => '2017-04-11'],
            ],
            $iterator->toArray()
        );
    }

    public function testGetDbConnection()
    {
        $connection = $this->dbDriver->getDbConnection();
        $this->assertInstanceOf(\PDO::class, $connection);
    }

    public function testGetUri()
    {
        $uri = $this->dbDriver->getUri();
        $this->assertInstanceOf(Uri::class, $uri);
        $this->assertEquals('sqlite:///tmp/test.db', $uri->__toString());
    }

    public function testisSupportMultRowset()
    {
        $this->assertFalse($this->dbDriver->isSupportMultRowset());
    }

    public function testCachedResults()
    {
        $dbCached = new DbCached($this->dbDriver, \ByJG\Cache\Factory::createArrayPool(), 600);

        // Get the first from Db and then cache it;
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 1]);
        $this->assertEquals(
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx', "__id" => 0, "__key" => 0],
            ],
            $iterator->toArray()
        );

        // Remove it from DB (Still in cache) - Execute don't use cache
        $dbCached->execute("delete from users where name = [[name]]", ['name' => 'Another2']);

        // Try get from cache
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 1]);
        $this->assertEquals(
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx', "__id" => 0, "__key" => 0],
            ],
            $iterator->toArray()
        );
    }

    public function testCachedResultsNotFound()
    {
        $dbCached = new DbCached($this->dbDriver, \ByJG\Cache\Factory::createArrayPool(), 600);

        // Get the first from Db and then cache it;
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 4]);
        $this->assertEquals(
            [],
            $iterator->toArray()
        );
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 1]);
        $this->assertEquals(
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx', "__id" => 0, "__key" => 0],
            ],
            $iterator->toArray()
        );

        // Remove it from DB (Still in cache)
        $this->dbDriver->execute("insert into users (name, createdate) values ('John Doe 2', '2018-01-02')");

        // Try get from cache
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 1]);
        $this->assertEquals(
            [
                [ 'id'=> 1, 'iduser' => 1, 'property' => 'xxx', "__id" => 0, "__key" => 0],
            ],
            $iterator->toArray()
        );
        $iterator = $dbCached->getIterator('select * from info where id = :id', ['id' => 4]);
        $this->assertEquals(
            [],
            $iterator->toArray()
        );
    }
}
__halt_compiler();----SIGNATURE:----h9XL75ZVcHzjqrHWCPKkKXg9AimyCvxhdW/1dDoEcZHyjLDUp/5lTA+80GGACHrnglzLculPlT0uZgB1ESSw91XkLn7CP4Ip4omr4CfK0IothB47yNhf38pmgxzLWrnHiNSyXTr9dlGq6PlOPSUJevTgW30Qrd0eW1MuZovL57x7ngT8BLxKR3NBCn2TasePLpZiCzAfwYE9hE5Z0sEhDZXzp/2ioe7SaA1QjwA2AZYVur6y//ELAER/QRxBksiJ0k5JjmVJfbhwoCQmcmMZa6rfCtd+w94HqBe9AF927iPvTYKUSRAV0VT1YPQl5wpf64LDqalkP4WFFT1L8Wu79kwlm2LkHbRZ/dWRAUto/19h1yHgV/1Yw0r5auZwr5OuJrez7rUbyplamZixYn5d4fjx4tmNN89w4ORUHeYe4EPbWvFa45kQp0IzEiUc5Tsyxzo8tl0uMXwxnTCXyBVoBhmn7Zf8sKZHMhBE5BJMOvnm6MWqYQ+lrAB04fa0s8teRrB4yG2J2QLm2MFufOTFjhZzJ3UWkePRQAxbSaHd2c9Yeq3oTpAvkphi6iXYHrCJnNxFPC0apk8RQtllmTeRbOzupMton8tWDxeRaToU88iChhmpSsHVZDaWoJ12XYtT8Y0l5xigIVyF/sIkIzXGdjluplAb8yM3K6bsL0aoHgM=----ATTACHMENT:----OTYzMzAzNTcyNTQ3OTY3NCA3MDg2OTMwNjY2ODEwMDkwIDc4MTYwMjk0MTEzMTUyMDg=