<?php
require_once ("../MysqliDb.php");
error_reporting(E_ALL);

function pretty_print($array) {
  echo '<pre>';
  print_r($array);
  echo '</pre>';
}

$prefix = 't_';
$db = new Mysqlidb('localhost', 'root', '', 'testdb');
if(!$db) die("Database error");

$mysqli = new mysqli ('localhost', 'root', '', 'testdb');
$db = new Mysqlidb($mysqli);

$db = new Mysqlidb(Array (
                'host' => 'localhost',
                'username' => 'root',
                'password' => '',
                'db' => 'testdb',
                'prefix' => $prefix,
                'charset' => null));
if(!$db) die("Database error");

$db->setTrace(true);

$tables = Array (
    'users' => Array (
        'login' => 'char(10) not null',
        'active' => 'bool default 0',
        'customerId' => 'int(10) not null',
        'firstName' => 'char(10) not null',
        'lastName' => 'char(10)',
        'password' => 'text not null',
        'createdAt' => 'datetime',
        'updatedAt' => 'datetime',
        'expires' => 'datetime',
        'loginCount' => 'int(10) default 0',
        'unique key' => 'login (login)'
    ),
    'products' => Array (
        'customerId' => 'int(10) not null',
        'userId' => 'int(10) not null',
        'productName' => 'char(50)'
    )
);
$data = Array (
    'users' => Array (
        Array ('login' => 'user1',
               'customerId' => 10,
               'firstName' => 'John',
               'lastName' => 'Doe',
               'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
               'createdAt' => $db->now(),
               'updatedAt' => $db->now(),
               'expires' => $db->now('+1Y'),
               'loginCount' => $db->inc()
        ),
        Array ('login' => 'user2',
               'customerId' => 10,
               'firstName' => 'Mike',
               'lastName' => NULL,
               'password' => $db->func('SHA1(?)',Array ("secretpassword2+salt")),
               'createdAt' => $db->now(),
               'updatedAt' => $db->now(),
               'expires' => $db->now('+1Y'),
               'loginCount' => $db->inc(2)
        ),
        Array ('login' => 'user3',
               'active' => true,
               'customerId' => 11,
               'firstName' => 'Pete',
               'lastName' => 'D',
               'password' => $db->func('SHA1(?)',Array ("secretpassword2+salt")),
               'createdAt' => $db->now(),
               'updatedAt' => $db->now(),
               'expires' => $db->now('+1Y'),
               'loginCount' => $db->inc(3)
        )
    ),
    'products' => Array (
        Array ('customerId' => 1,
               'userId' => 1,
               'productName' => 'product1',
        ),
        Array ('customerId' => 1,
               'userId' => 1,
               'productName' => 'product2',
        ),
        Array ('customerId' => 1,
               'userId' => 1,
               'productName' => 'product3',
        ),
        Array ('customerId' => 1,
               'userId' => 2,
               'productName' => 'product4',
        ),
        Array ('customerId' => 1,
               'userId' => 2,
               'productName' => 'product5',
        ),

    )
);

function createTable ($name, $data) {
    global $db;
    //$q = "CREATE TABLE $name (id INT(9) UNSIGNED PRIMARY KEY NOT NULL";
    $db->rawQuery("DROP TABLE IF EXISTS $name");
    $q = "CREATE TABLE $name (id INT(9) UNSIGNED PRIMARY KEY AUTO_INCREMENT";
    foreach ($data as $k => $v) {
        $q .= ", $k $v";
    }
    $q .= ")";
    $db->rawQuery($q);
}

// rawQuery test
foreach ($tables as $name => $fields) {
    $db->rawQuery("DROP TABLE ".$prefix.$name);
    createTable ($prefix.$name, $fields);
}

if (!$db->ping()) {
    echo "db is not up";
    exit;
}

// insert test with autoincrement
foreach ($data as $name => $datas) {
    foreach ($datas as $d) {
        $id = $db->insert($name, $d);
        if ($id)
            $d['id'] = $id;
        else {
            echo "failed to insert: ".$db->getLastQuery() ."\n". $db->getLastError();
            exit;
        }
    }
}

// bad insert test
$badUser = Array ('login' => null,
               'customerId' => 10,
               'firstName' => 'John',
               'lastName' => 'Doe',
               'password' => 'test',
               'createdAt' => $db->now(),
               'updatedAt' => $db->now(),
               'expires' => $db->now('+1Y'),
               'loginCount' => $db->inc()
        );
$id = $db->insert ("users", $badUser);
if ($id) {
    echo "bad insert test failed";
    exit;
}

// insert without autoincrement
$q = "create table {$prefix}test (id int(10), name varchar(10));";
$db->rawQuery($q);
$id = $db->insert ("test", Array ("id" => 1, "name" => "testname"));
if (!$id) {
    echo "insert without autoincrement failed";
    exit;
}
$db->get("test");
if ($db->count != 1) {
    echo "insert without autoincrement failed -- wrong insert count";
    exit;
}

$q = "drop table {$prefix}test;";
$db->rawQuery($q);


$db->orderBy("`id`","asc");
$users = $db->get("users");
if ($db->count != 3) {
    echo "Invalid total insert count";
    exit;
}

// insert with on duplicate key update
$user = Array ('login' => 'user3',
       'active' => true,
       'customerId' => 11,
       'firstName' => 'Pete',
       'lastName' => 'D',
       'password' => $db->func('SHA1(?)',Array ("secretpassword2+salt")),
       'createdAt' => $db->now(),
       'updatedAt' => $db->now(),
       'expires' => $db->now('+1Y'),
       'loginCount' => $db->inc(3)
       );
$updateColumns = Array ("updatedAt");
$insertLastId = "id";
sleep(1);
$db->onDuplicate($updateColumns, "id");
$db->insert("users", $user);
$nUser = $db->where('login','user3')->get('users');
if ($db->count != 1) {
    echo "onDuplicate update failed. ";
    exit;
}
if ($nUser[0]['createdAt'] == $nUser[0]['updatedAt']) {
    echo "onDuplicate2 update failed. ";
    exit;
}

// order by field
$db->orderBy("login","asc", Array ("user3","user2","user1"));
$login = $db->getValue ("users", "login");
if ($login != "user3") {
    echo "order by field test failed";
    exit;
}

$db->where ("active", true);
$users = $db->get("users");
if ($db->count != 1) {
    echo "Invalid total insert count with boolean";
    exit;
}

$db->where ("active", false);
$db->update("users", Array ("active" => $db->not()));
if ($db->count != 2) {
    echo "Invalid update count with not()";
    exit;
}

$db->where ("active", true);
$users = $db->get("users");
if ($db->count != 3) {
    echo "Invalid total insert count with boolean";
    exit;
}

$db->where ("active", true);
$users = $db->get("users", 2);
if ($db->count != 2) {
    echo "Invalid total insert count with boolean";
    exit;
}

// TODO
//$db->where("createdAt", Array (">" => $db->interval("-1h")));
//$users = $db->get("users");
//print_r ($users);

$db->where("firstname", Array ('LIKE' => '%John%'));
$users = $db->get("users");
if ($db->count != 1) {
    echo "Invalid insert count in LIKE: ".$db->count;
    print_r ($users);
    echo $db->getLastQuery();
    exit;
}

$db->groupBy("customerId");
$cnt = $db->get ("users", null, "customerId, count(id) as cnt");
if ($db->count != 2) {
    echo "Invalid records count with group by";
}


$upData = Array (
    'expires' => $db->now("+5M","expires"),
    'loginCount' => $db->inc()
);
$db->where ("id", 1);
$cnt = $db->update("users", $upData);
if ($db->count != 1) {
    echo "Invalid update count with functions";
    exit;
}


$db->where ("id", 1);
$r = $db->getOne("users");
if ($db->count != 1) {
    echo "Invalid users count on getOne()";
    exit;
}
if ($r['password'] != '546f98b24edfdc3b9bbe0d241bd8b29783f71b32') {
    echo "Invalid password were set".
    exit;
}

$db->where ("id", Array('1','2','3'), 'IN');
$db->get("users");
if ($db->count != 3) {
    echo "Invalid users count on where() with in ";
    exit;
}

$db->where ("id", Array('2','3'), 'between');
$db->get("users");
if ($db->count != 2) {
    echo "Invalid users count on where() with between";
    exit;
}
///
$db->where ("id", 2);
$db->orWhere ("customerId", 11);
$r = $db->get("users");
if ($db->count != 2) {
    echo "Invalid users count on orWhere()";
    exit;
}
///
$db->where ("lastName", NULL, '<=>');
$r = $db->get("users");
if ($db->count != 1) {
    echo "Invalid users count on null where()";
    exit;
}
///
$db->join("users u", "p.userId=u.id", "LEFT");
$db->where("u.login",'user2');
$db->orderBy("CONCAT(u.login, u.firstName)");
$products = $db->get ("products p", null, "u.login, p.productName");
if ($db->count != 2) {
    echo "Invalid products count on join ()";
    exit;
}
///
$db->join("users u", "p.userId=u.id", "LEFT");
$db->joinWhere('t_users u', 'u.id', 'non existant value');
$products = $db->get ("products p", null, "u.login, p.productName");
if ($db->count != 5) {
  echo 'Invalid product count on joinWhere';
  exit;
}
foreach($products as $product) {
  if ($product['login']) {
    echo 'Invalid login result on joinWhere';
    exit;
  }
}
///
$db->join("users u", "p.userId=u.id", "LEFT");
$db->joinOrWhere('t_users u', 'u.id', 'non existant value');
$products = $db->get ("products p", null, "u.login, p.productName");
if ($db->count != 5) {
  echo 'Invalid product count on joinOrWhere';
  exit;
}
foreach($products as $product) {
  if (!$product['login']) {
    echo 'Invalid login result on joinWhere';
    exit;
  }
}
///
$db->where("id = ? or id = ?", Array(1,2));
$res = $db->get ("users");
if ($db->count != 2) {
    echo "Invalid users count on select with multiple params";
    exit;
}

///
$db->where("id = 1 or id = 2");
$res = $db->get ("users");
if ($db->count != 2) {
    echo "Invalid users count on select with multiple params";
    exit;
}
///
$usersQ = $db->subQuery();
$usersQ->where ("login", "user2");
$usersQ->getOne ("users", "id");

$db->where ("userId", $usersQ);
$cnt = $db->getValue ("products", "count(id)");
if ($cnt != 2) {
    echo "Invalid select result with subquery";
    exit;
}
///
$dbi_sub = $db->subQuery();
$dbi_sub->where ('active', 1);
$dbi_sub->get ('users', null, 'id');

$db->where ('id', $dbi_sub, 'IN');

$cnt = $db->copy();
$c = $cnt->getValue ('users', "COUNT(id)");
if ($c != 3) {
    echo "copy with subquery count failed";
    exit;
}
unset ($cnt);

$users = $db->get('users');
if (count($users) != 3) {
    echo "copy with subquery data count failed";
    exit;
}
///
$usersQ = $db->subQuery ("u");
$usersQ->where ("active", 1);
$usersQ->get("users");

$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get ("products p", null, "u.login, p.productName");
if ($products[2]['login'] != 'user1' || $products[2]['productName'] != 'product3') {
    echo "invalid join with subquery";
    exit;
}
if ($db->count != 5) {
    echo "invalid join with subquery count";
    exit;
}

$db->withTotalCount()->get('users', 1);
if ($db->totalCount != 3) {
    echo "error in totalCount";
    exit;
}

$result = $db->map ('id')->ArrayBuilder()->getOne ('users', 'id,login');
if (key ($result) != 1 && $result[1] != 'user1') {
    echo 'map string=string failed';
    exit;
}
$result = $db->map ('id')->ArrayBuilder()->getOne ('users', 'id,login,createdAt');
if (key ($result) != 1 && !is_array ($result[1])) {
    echo 'map string=array failed';
    exit;
}
$result = $db->map ('id')->ObjectBuilder()->getOne ('users', 'id,login');
if (key ($result) != 1 && $result[1] != 'user1') {
    echo 'map object string=string failed';
    exit;
}
$result = $db->map ('id')->ObjectBuilder()->getOne ('users', 'id,login,createdAt');
if (key ($result) != 1 && !is_object ($result[1])) {
    echo 'map string=object failed';
    exit;
}

$expectedIDs = [
    'users' => [5, 6, 7],
    'products' => [6,7,8,9,10],
];

// multi-insert test with autoincrement
foreach ($data as $name => $datas) {

    // remove previous entries to ensure avoiding PRIMARY-KEY collisions here
    $db->delete($name);

    // actual insertion test
    $ids = $db->insertMulti($name, $datas);

    // check results
    if(!$ids) {
        echo "failed to multi-insert: ".$db->getLastQuery() ."\n". $db->getLastError();
        exit;
    } elseif($ids !== $expectedIDs[$name]) {
        pretty_print($ids);
        echo "multi-insert succeeded, but unexpected id's: ".$db->getLastQuery() ."\n". $db->getLastError();
        exit;
    }
}

// skip last user here, since it has other keys than the others
unset($data['users'][2]);

// multi-insert test with autoincrement and overriding column-names
foreach ($data as $name => $datas) {

    // remove previous entries to ensure avoiding PRIMARY-KEY collisions here
    $db->delete($name);

    // actual insertion test
    if(!$db->insertMulti($name, $datas, array_keys($datas[0]))) {
        echo "failed to multi-insert: ".$db->getLastQuery() ."\n". $db->getLastError();
        exit;
    }
}

///
//TODO: insert test
$db->delete("users");
$db->get("users");
if ($db->count != 0) {
    echo "Invalid users count after delete";
    exit;
}
$db->delete("products");

//print_r($db->rawQuery("CALL simpleproc(?)",Array("test")));

echo '<pre>';
pretty_print($db->trace);
echo '</pre>';
echo "All done\n";
echo "Memory usage: ".memory_get_peak_usage()."\n";

?>