<?php

/**
 * Class to test Db2 SQL Engine
 * Tests use static setUpBeforeClass and tearDownAfterClass because setUp and tearDown
 * have problems recreating the test library
 */
class DB2iSeriesSQLEngineTest extends PHPUnit_Framework_TestCase
{
    protected static $dbUsername = "USERNAME";
    protected static $dbPassword = "PASSWORD";

    protected static $conn;
    protected static $toolkitObject;
    protected static $testLib = 'DOCTRINE';
    protected static $testTable = 'TESTTBL';
    protected static $testData = [
        ['COD' => 'A', 'DES' => 'DESCA'],
        ['COD' => 'B', 'DES' => 'DESCB'],
        ['COD' => 'C', 'DES' => 'DESCC'],
        ['COD' => 'D', 'DES' => 'DESCD'],
    ];

    public static function setUpBeforeClass()
    {
        parent::setUpBeforeClass();

        static::initConnection();
        static::initToolkitObject();

        static::createTestDb();
        static::createTestTable();
        static::initTestTableData();
    }

    public static function tearDownAfterClass()
    {
        parent::tearDownAfterClass();
        static::deleteTestDb();
    }

    protected static function initConnection()
    {
        $options = [
            'autocommit' => 1, //DB2_AUTOCOMMIT_ON',
            'i5_naming' => 1,  //DB2_I5_NAMING_ON
            'db2_attr_case' => 0, //DB2_CASE_NATURAL
            'i5_date_fmt' => 1, //DB2_I5_FMT_ISO
            //'i5_lib' => 'QGPL',
            'i5_libl' => 'QGPL',
            'i5_commit' => 2 //2=READ_COMMITTED 5=DB2_I5_TXN_SERIALIZABLE
        ];

        static::$conn = db2_connect('*LOCAL', static::$dbUsername, static::$dbPassword, $options);
    }

    protected static function execute($sql, $params = [])
    {
        //echo $sql;
        $stmt = @db2_prepare(static::$conn, $sql);
        if (!$stmt) {
            throw new \Exception(db2_stmt_errormsg());
        }

        if (empty($params)) {
            $res = db2_exec(static::$conn, $sql);
        } else {
            $res = db2_execute($stmt, $params);
        }

        return $res;
    }

    protected static function fetchAll($stmt)
    {
        $resultSet = [];
        while ($row = db2_fetch_assoc($stmt)) {
            $resultSet[] = $row;
        }
        return $resultSet;
    }

    protected static function initToolkitObject()
    {
        //Toolkit CCSID test
        include_once 'iToolkitService.php';

        static::$toolkitObject = ToolkitService::getInstance(static::$conn, DB2_I5_NAMING_ON);
        static::$toolkitObject->setToolkitServiceParams(array(
            'stateless' => true
        ));
    }

    protected static function call($command)
    {
        static::$toolkitObject->CLCommand($command);
    }

    protected static function createTestDb()
    {
        static::deleteTestDb();
        static::execute(sprintf('CREATE COLLECTION %s', static::$testLib));
    }

    protected static function createTestTable()
    {
        $createStmt = sprintf("
            CREATE TABLE %s/%s (
                /*ID INTEGER GENERATED BY DEFAULT AS IDENTITY,*/
                COD VARCHAR(2) NOT NULL WITH DEFAULT '',
                DES NVARCHAR (20)
            )
        ", static::$testLib, static::$testTable);

        static::execute($createStmt);
    }

    protected static function initTestTableData()
    {
        $insertStmt = sprintf("
            INSERT INTO %s/%s (COD, DES) VALUES (?, ?)
        ", static::$testLib, static::$testTable);

        foreach (static::$testData as $row) {
            static::execute($insertStmt, array_values($row));
        }
    }

    protected static function deleteTestDb()
    {
        /* Disconnect Journal from library */
        static::call(sprintf("ENDJRNPF FILE(*ALL) JRN(%s/QSQJRN)", static::$testLib));
        /* Delete Journal */
        static::call(sprintf("DLTJRN JRN(%s/QSQJRN)", static::$testLib));
        /* Delete Journal Receiver */
        static::call(sprintf("DLTJRNRCV JRNRCV(%s/QSQJRN*) DLTOPT(*IGNINQMSG)", static::$testLib));

        /* Base Libl */
//        static::call(sprintf("CHGLIBL LIBL(QGPL)"));

        /* Delete library */
        static::call(sprintf("DLTLIB LIB(%s)", static::$testLib));
    }

    /**
     * @requires OS AIX
     */
    public function testOrderBy()
    {
        $expectedData = array_reverse(static::$testData);

        $testStmt = sprintf("
            SELECT
                *
            FROM %s/%s
            ORDER BY COD DESC
        ", static::$testLib, static::$testTable);

        $stmt = $this->execute($testStmt);

        $data = $this->fetchAll($stmt);

        $this->assertEquals($expectedData, $data);
    }

    /**
     * This is a false test because nested Order By do not work as expected on IBMi
     * @requires OS AIX
     */
    public function testNestedOrderByIsNotWorkingOnIbmi()
    {
        $expectedData = static::$testData;// array_reverse(static::$testData);

        $testStmt = sprintf("
             SELECT * FROM (
                SELECT
                    *
                FROM %s/%s
                ORDER BY COD DESC
            ) t1
        ", static::$testLib, static::$testTable);

        $stmt = $this->execute($testStmt);
        $data = $this->fetchAll($stmt);

        $this->assertEquals($expectedData, $data);
    }

    /**
     * This is a false test because nested Order By do not work as expected on IBMi
     * @requires OS AIX
     */
    public function testNestedOrderByAlternativeUsingOverStatement()
    {
        $expectedData = array_reverse(static::$testData);

        $testStmt = sprintf("
             SELECT t1.* FROM (
                SELECT %s.*, ROW_NUMBER() OVER(ORDER BY COD DESC) as ROWNUM
                FROM %s/%s
                ORDER BY COD DESC
            ) t1
        ", static::$testTable, static::$testLib, static::$testTable);

        $stmt = $this->execute($testStmt);
        $data = $this->fetchAll($stmt);

        //remove ROWNUM
        $data = array_map(function ($el) {
            return ['COD' => $el['COD'], 'DES' => $el['DES']];
        }, $data);

        $this->assertEquals($expectedData, $data);
    }

    /**
     * This is a false test because LIMIT OFFSET statement is not implemented until 7.1 TR11 and IBM i 7.2 TR3
     * @requires OS AIX
     * @expectedException \Exception
     */
    public function testLimitOffsetIsNotWorkingOnCurrentIbmiVersion()
    {

        $testStmt = sprintf("
          SELECT * FROM %s/%s LIMIT 2 OFFSET 2
        ", static::$testLib, static::$testTable);

        $this->execute($testStmt);
    }

    /**
     * @requires OS AIX
     */
    public function testLimitOffsetAlternativeUsingRownum()
    {

        $limit = 2;
        $offset = 2;

        $expectedData = array_splice(static::$testData, $limit, $offset);

        $testStmt = sprintf("
            SELECT t1.* FROM (
                SELECT %s.*, ROW_NUMBER() OVER(ORDER BY COD ASC) as ROWNUM
                FROM %s/%s
                ORDER BY COD ASC
            ) t1 WHERE ROWNUM BETWEEN %d AND %d
        ", static::$testTable, static::$testLib, static::$testTable, $offset + 1, $offset + $limit);

        $stmt = $this->execute($testStmt);
        $data = $this->fetchAll($stmt);

        //remove ROWNUM
        $data = array_map(function ($el) {
            return ['COD' => $el['COD'], 'DES' => $el['DES']];
        }, $data);

        $this->assertEquals($expectedData, $data);
    }
}