<?php

namespace Plainwork\Database;

use Plainwork\Database\DatabaseAdapterInterface;
use Plainwork\Database\Query;

class Database
{
    public $self;
    protected DatabaseAdapterInterface $adapter;
    protected string $prefix = '';
    private bool $debug = false;
    private array $queries = [];

    public function getPrefix(): string
    {
        return $this->prefix;
    }

    public function setPrefix(string $prefix): self
    {
        $this->prefix = $prefix;

        return $this;
    }

    public function setAdapter(DatabaseAdapterInterface $adapter): self
    {
        $this->adapter = $adapter;

        return $this;
    }

    public function dropTable(string $table)
    {
        $table = $this->tableName($table);
        $sql = 'DROP TABLE IF EXISTS ' . $table;

        return $this->query($sql);
    }

    public function createTable(string $table, array $fields)
    {
        $table = $this->tableName($table);
        if (!$fields) {
            throw new \InvalidArgumentException('Need fields to CREATE TABLE');
        }

        $args = [];
        $sql = '';
        $sql .= 'CREATE TABLE IF NOT EXISTS ' . $table;

    // fields
        $primaryKeys = $uniques = [];
        $sql_ = [];
        foreach ($fields as $col => $detail) {
            $detail += [
                'type' => 'INTEGER',
                'null' => true,
                'autoIncrement' => false,
                'default' => null,
                'primaryKey' => false,
                'unique' => false,
            ];

            $detailString = [];
            $detailString[] = $detail['type'];
            $detailString[] = $detail['null'] ? 'NULL' : 'NOT NULL';
            if ($detail['autoIncrement']) {
                $detailString[] = 'AUTO_INCREMENT';
            }
            if (null !== $detail['default']) {
                $detailString[] = 'DEFAULT ' . $this->placeholder($detail['default']);
                $args[] = $detail['default'];
            }
            if ($detail['primaryKey']) {
                $primaryKeys[] = $col;
            }
            if ($detail['unique']) {
                $uniques[] = $col;
            }
            $detailString = join(' ', $detailString);

            $sql_[] = $col . ' ' . $detailString;
        }

    // primary keys
        if ($primaryKeys) {
            $constraintName = join('_', $primaryKeys) . '_key';
            $constraintValue = join(', ', $primaryKeys);
            $sql_[] = 'CONSTRAINT ' . $constraintName . ' PRIMARY KEY (' . $constraintValue . ')';
        }

    // uniques
        foreach ($uniques as $col) {
            $sql_[] = 'CONSTRAINT ' . $col . '_unique' . ' UNIQUE (' . $col . ')';
        }

        $sql .= ' (' . join(', ', $sql_) . ')';

        return $this->execute($sql, $args);
    }

    public function dropColumn(string $table, string $col)
    {
        $table = $this->tableName($table);
        $sql = 'ALTER TABLE ' . $table . ' DROP COLUMN ' . $col;

        return $this->query($sql);
    }

    public function addColumn(string $table, string $col, array $detail)
    {
        $table = $this->tableName($table);
        $args = [];
        $sql = '';
        $sql .= 'ALTER TABLE ' . $table;

        $detail += [
            'type' => 'INTEGER',
            'null' => true,
            'autoIncrement' => false,
            'default' => null,
            'primaryKey' => false,
            'unique' => false,
        ];

        $detailString = [];
        $detailString[] = $detail['type'];
        $detailString[] = $detail['null'] ? 'NULL' : 'NOT NULL';
        if ($detail['autoIncrement']) {
            $detailString[] = 'AUTO_INCREMENT';
        }
        if (null !== $detail['default']) {
            $detailString[] = 'DEFAULT ' . $this->placeholder($detail['default']);
            $args[] = $detail['default'];
        }
        $detailString = join(' ', $detailString);
        $sql .= ' ADD COLUMN ' . $col . ' ' . $detailString;

        $this->execute($sql, $args);

        if ($detail['unique']) {
            $sql = 'ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $col . '_unique' . ' UNIQUE (' . $col . ')';
            $this->query($sql);
        }
    }

    public function addUnique(string $table, ...$cols)
    {
        $sql = 'ALTER TABLE ' . $table;
        $sql .= ' ADD CONSTRAINT ' . join('_', $cols) . '_unique' . ' UNIQUE (' . join(', ', $cols) . ')';
        $this->query($sql);
    }

    public function addIndex(string $table, string $col)
    {
        $table = $this->tableName($table);
        list($sql, $args) = $this->adapter->addIndexSql($table, [$col]);

        return $this->execute($sql, $args);
    }

    private function placeholder($v): string
    {
        if (is_int($v)) {
            $ret = '%d';
        } else {
            $ret = '%s';
        }

        return $ret;
    }

    private function prepareValue($v)
    {
        if ($v instanceof \DateTimeInterface) {
        // explicitely convert to UTC
            $offset = (int) $v->format('Z');
            if (0 !== $offset) {
                $v = $v->setTimezone(new \DateTimeZone('UTC'));
            }
            $v = $v->format('Y-m-d H:i:s');
        } elseif (is_bool($v)) {
            $v = $v ? 1 : 0;
        }

        return $v;
    }

/*
* %d (integer)
* %f (float)
* %s (string)
* %i (identifier, e.g. table/field names)
*/
    public function execute(string $sqlTemplate, array $vars)
    {
        $sql = $this->adapter->prepareQuery($sqlTemplate, $vars);

        return $this->query($sql);
    }

    private function query(string $sql)
    {
    /* replace prefix */
        if (false !== strpos($sql, '__')) {
            $sql = str_replace('__', $this->getPrefix(), $sql);
        }

        if ($this->debug) {
            $this->queries[] = [$sql, -microtime(true)];
        }

        $ret = $this->adapter->query($sql);

        if ($this->debug) {
            $this->queries[count($this->queries)-1][1] += microtime(true);
        }

        return $ret;
    }

    public function debug(bool $v): self
    {
        $this->debug = $v;

        return $this;
    }

    public function getQueries(): array
    {
        return $this->queries;
    }

    private function tableName(string $tbl)
    {
        // if (strlen($this->prefix) > 0) {
            // if ('__' !== substr($tbl, 0, 2)) {
                // $tbl = '__' . $tbl;
            // }
        // }

        return $tbl;
    }

    public function insert(string $table, array $keyValue)
    {
        $keyValue = array_map([$this, 'prepareValue'], $keyValue);

        return $this->q()->insert($table, $keyValue)->execute();
    }

    public function insertMany(string $table, array $arrayOfFields)
    {
        if (!$arrayOfFields) {
            return;
        }

    // split by 100
        $sqls = [];

        $count = count($arrayOfFields);
        $step = 100;

        $rex = 0;
        while ($rex < $count) {
            $thisArrayOfFields = array_slice($arrayOfFields, $rex, $step);
            foreach (array_keys($thisArrayOfFields) as $i) {
                $thisArrayOfFields[$i] = array_map([$this, 'prepareValue'], $thisArrayOfFields[$i]);
            }

            $sqls[] = $this->q()
                ->insertMany($table, $thisArrayOfFields)
                ->build()
                ;

            $rex += $step;
        }

        foreach ($sqls as list($sql, $args)) {
            $this->execute($sql, $args);
        }
    }

/* fluent proxy methods for Query builder */
    public final function q(): Query
    {
        return new Query($this->self);
    }

    public function select(string $colName): Query
    {
        return $this->q()->select($colName);
    }

    public function deleteFrom(string $tableName): Query
    {
        return $this->q()->deleteFrom($tableName);
    }

    public function from(string $tableName): Query
    {
        return $this->q()->from($tableName);
    }

    public final function whereInt($propName, string $compare, $v): Query
    {
        return $this->q()->whereInt($propName, $compare, $v);
    }

    public final function whereString($propName, string $compare, $v): Query
    {
        return $this->q()->whereString($propName, $compare, $v);
    }

    public final function whereFloat($propName, string $compare, $v): Query
    {
        return $this->q()->whereFloat($propName, $compare, $v);
    }

    public final function limit(int $v): Query
    {
        return $this->q()->limit($v);
    }

    public final function offset(int $v): Query
    {
        return $this->q()->offset($v);
    }

    public final function orderAsc(string $propName): Query
    {
        return $this->q()->orderAsc($propName);
    }

    public final function orderDesc(string $propName): Query
    {
        return $this->q()->orderDesc($propName);
    }

    public function update(string $tableName, array $keyValue): Query
    {
        return $this->q()->update($tableName, $keyValue);
    }
}
