<?php

namespace Plainwork\Database;

class Query
{
    private $db;
    private array $parts;

    private string $deleteFrom = '';
    private array $update = [];
    private array $insert = [];
    private array $insertMany = [];
    private array $order = [];
    private array $groupBy = [];
    private int $limit = 0;
    private int $offset = 0;
    private array $where = [];
    private string $aliasFor;

    public function reset()
    {
        $this->order = $this->groupBy = $this->where = [];
        $this->limit = $this->offset = 0;
        $this->parts = [
            'select' => [],
            'from' => [],
            'join' => [],
        ];
    }

    public final function __construct($db = null)
    {
        $this->db = $db;
        $this->reset();
    }

    private function appendPart(string $partType, array $part): self
    {
        $this->parts[$partType][] = $part;
        $this->aliasFor = $partType;

        return $this;
    }

    public function update(string $table): self
    {
        $this->update[$table] = [];
        $this->aliasFor = 'update';

        return $this;
    }

    public function insertInto(string $table): self
    {
        $this->insert[$table] = [];
        $this->aliasFor = 'insert';

        return $this;
    }

    public function insertManyInto(string $table): self
    {
        $this->insertMany[$table] = [];
        $this->aliasFor = 'insertMany';

        return $this;
    }

    public function valueDateTime(string $col, \DateTimeInterface $v): self
    {
        return $this->value($col, $v);
    }

    public function valueNull(string $col): self
    {
        return $this->value($col, null);
    }

    public function valueInt(string $col, int $v): self
    {
        return $this->value($col, $v);
    }

    public function valueFloat(string $col, float $v): self
    {
        return $this->value($col, $v);
    }

    public function valueBool(string $col, bool $v): self
    {
        return $this->value($col, $v ? 1 : 0);
    }

    public function valueString(string $col, string $v): self
    {
        return $this->value($col, $v);
    }

    private function value(string $col, $v): self
    {
        if ('insert' === $this->aliasFor) {
            $table = current(array_keys($this->insert));
            $this->insert[$table][$col] = $v;
        } elseif ('update' === $this->aliasFor) {
            $table = current(array_keys($this->update));
            $this->update[$table][$col] = $v;
        } elseif ('insertMany' === $this->aliasFor) {
            $table = current(array_keys($this->insertMany));
            if (!isset($this->insertMany[$table][$col])) {
                $this->insertMany[$table][$col] = [];
            }
            $this->insertMany[$table][$col][] = $v;
        }

        return $this;
    }

    public function insertMany(string $tableName, array $arrayOfKeyValue): self
    {
        $this->insertMany[$tableName] = $arrayOfKeyValue;

        return $this;
    }

    public function limit(int $v): self
    {
        $this->limit = $v;

        return $this;
    }

    public function offset(int $v): self
    {
        $this->offset = $v;

        return $this;
    }

    public function deleteFrom(string $tableName): self
    {
        $this->deleteFrom = $tableName;

        return $this;
    }

    public function selectExists(): self
    {
        return $this->select('1');
    }

    public function select(string $colName): self
    {
        return $this->appendPart('select', ['name' => $colName]);
    }

    public function from(string $tableName): self
    {
        return $this->appendPart('from', ['name' => $tableName]);
    }

    public function join(string $tableName): self
    {
        return $this->appendPart('join', ['name' => $tableName]);
    }

    public function leftJoin(string $tableName): self
    {
        return $this->appendPart('join', ['name' => $tableName, 'type' => 'LEFT']);
    }

    public function orderAsc(string $col): self
    {
        $this->order[] = ['name' => $col, 'how' => 'ASC'];

        return $this;
    }

    public function orderDesc(string $col): self
    {
        $this->order[] = ['name' => $col, 'how' => 'DESC'];

        return $this;
    }

    public function groupBy(string $col): self
    {
        $this->groupBy[] = $col;

        return $this;
    }

    public function as(string $alias): self
    {
        $k = $this->aliasFor;
        $i = count($this->parts[$k]) - 1;
        $this->parts[$k][$i]['as'] = $alias;

        return $this;
    }

    public function on(string $col, string $operator, string $col2): self
    {
        $i = count($this->parts['join']) - 1;
        $this->parts['join'][$i]['on'] = [$col, $operator, $col2];

        return $this;
    }

    public function whereColumn(string $col1, string $operator, string $col2): self
    {
        $this->where[] = ['col1' => $col1, 'operator' => $operator, 'col2' => $col2];

        return $this;
    }

    public function whereDateTime($col, string $operator, \DateTimeInterface $v): self
    {
        return $this->where($col, $operator, $v);
    }

    public function whereNull($col): self
    {
        return $this->where($col, '=', null);
    }

    public function whereNotNull($col): self
    {
        return $this->where($col, '<>', null);
    }

    public function whereInt($col, string $operator, $v): self
    {
        if (null === $v && '=' === $operator) {
            return $this->whereNull($col);
        }
        if (null === $v && '<>' === $operator) {
            return $this->whereNotNull($col);
        }

        $v = is_array($v) ? array_map('intval', $v) : (int) $v;

        return $this->where($col, $operator, $v);
    }

    public function whereString($col, string $operator, $v): self
    {
        if (null === $v && '=' === $operator) {
            return $this->whereNull($col);
        }
        if (null === $v && '<>' === $operator) {
            return $this->whereNotNull($col);
        }
        $v = is_array($v) ? array_map('strval', $v) : (string) $v;

        return $this->where($col, $operator, $v);
    }

    public function whereFloat($col, string $operator, $v): self
    {
        if (null === $v && '=' === $operator) {
            return $this->whereNull($col);
        }
        if (null === $v && '<>' === $operator) {
            return $this->whereNotNull($col);
        }
        $v = is_array($v) ? array_map('floatval', $v) : (float) $v;

        return $this->where($col, $operator, $v);
    }

    private function where($col, string $operator, $v): self
    {
        $this->where[] = ['col' => $col, 'operator' => $operator, 'v' => $v];

        return $this;
    }

    // public function where($col, string $operator, $v): self
    // {
        // if (is_array($col)) {
            // $this->startOr();
            // foreach ($col as $col_) {
                // $this->where($col_, $operator, $v);
            // }
            // $this->end();
        // } else {
            // $this->where[] = ['col' => $col, 'operator' => $operator, 'v' => $v];
        // }

        // return $this;
    // }

    public function startOr(): self
    {
        $this->where[] = 'OR';

        return $this;
    }

    public function startAnd(): self
    {
        $this->where[] = 'AND';

        return $this;
    }

    public function end(): self
    {
        $this->where[] = 'END';

        return $this;
    }

    public function build(): array
    {
        if ($this->insert) {
            if (count($this->insert) > 1){
                throw new \Exception('Just one table for insert is allowed');
            }
            if (!current($this->insert)) {
                throw new \Exception('Values missing for insert');
            }
        }

        if ($this->update) {
            if (count($this->update) > 1){
                throw new \Exception('Just one table for update is allowed');
            }
            if (!current($this->update)) {
                throw new \Exception('Values missing for update');
            }
        }

        $ret = [];
        $vars = [];

// _print_r($this->parts);

        if ($this->deleteFrom) {
            list($i_, $vars_) = $this->parseIdentifier($this->deleteFrom);
            $ret[] = 'DELETE FROM ' . $i_;
            $vars = array_merge($vars, $vars_);
        } elseif ($this->update) {
            foreach ($this->update as $tableName => $keyValue) {
                list($i_, $vars_) = $this->parseIdentifier($tableName);
                $ret[] = 'UPDATE ' . $i_;
                $vars = array_merge($vars, $vars_);

                $r = [];
                foreach ($keyValue as $name => $value) {
                    list($i_, $vars_) = $this->parseIdentifier($name);
                    $r_ = $i_ . ' = ';
                    $vars = array_merge($vars, $vars_);

                    $value = $this->prepareValue($value);

                    if (null === $value) {
                        $r_ .= 'NULL';
                    } else {
                        $r_ .= $this->placeholder($value);
                        $vars[] = $value;
                    }
                    $r[] = $r_;
                }
                $ret[] = ' SET ' . join(', ', $r);
            }
        } elseif ($this->insert) {
            foreach ($this->insert as $tableName => $keyValue) {
                list($i_, $vars_) = $this->parseIdentifier($tableName);
                $ret[] = 'INSERT INTO ' . $i_;
                $vars = array_merge($vars, $vars_);

            // don't need nulls
                $keyValue = array_filter($keyValue, fn($e) => (null !== $e));

                $r1 = $r2 = [];
                foreach ($keyValue as $name => $value) {
                    $r1[] = '%i';
                    $vars[] = $name;
                }
                foreach ($keyValue as $name => $value) {
                    $value = $this->prepareValue($value);
                    if (null === $value) {
                        $r2[] = 'NULL';
                    } else {
                        $r2[] = $this->placeholder($value);
                        $vars[] = $value;
                    }
                }
                $ret[] = ' (' . join(', ', $r1) . ') VALUES (' . join(', ', $r2) . ')';
            }
        } elseif ($this->insertMany) {
            foreach ($this->insertMany as $tableName => $keyValueArrays) {
                list($i_, $vars_) = $this->parseIdentifier($tableName);
                $ret[] = 'INSERT INTO ' . $i_;
                $vars = array_merge($vars, $vars_);

                $r1 = $r2 = [];

                $names = array_keys($keyValueArrays);

                foreach ($names as $name) {
                    $r1[] = '%i';
                    $vars[] = $name;
                }

                $count = count(current($keyValueArrays));
                for ($j = 0; $j < $count; $j++) {
                    $r3 = [];
                    foreach ($names as $name) {
                        $value = $keyValueArrays[$name][$j];
                        $value = $this->prepareValue($value);

                        if (null === $value) {
                            $r3[] = 'NULL';
                        } else {
                            $r3[] = $this->placeholder($value);
                            $vars[] = $value;
                        }
                    }
                    $r2[] = '(' . join(', ', $r3) . ')';
                }

                $ret[] = ' (' . join(', ', $r1) . ') VALUES ' . join(', ', $r2) . '';
            }
        } elseif ($this->parts['select']) {
            $ret[] = 'SELECT ';
        }

    // columns
        $r = [];
        foreach ($this->parts['select'] as $e) {
            $r_ = '';
            $col = $e['name'];
            $modifier = $func = null;

            if ('1' === $col){ // SELECT 1
                $r_ = $col;
            } else {
            // has function like COUNT(), MAX() etc
                $pos1 = strpos($col, '(');
                if (false !== $pos1) {
                    $pos2 = strpos($col, ')');
                    $func = substr($col, 0, $pos1);
                    $col = substr($col, $pos1 + 1, $pos2 - $pos1 - 1);
                }

            // contains modifier like DISTINCT
                $pos = strpos($col, ' ');
                if (false !== $pos) {
                    list($modifier, $col) = explode(' ', $col);
                }

                list($i_, $vars_) = $this->parseIdentifier($col);
                $r_ = $i_;
                $vars = array_merge($vars, $vars_);
            }

            if (null !== $modifier) {
                $r_ = $modifier . ' ' . $r_;
            }
            if (null !== $func) {
                $r_ = $func . '(' . $r_ . ')';
            }
            if (isset($e['as'])) {
                list($i_, $vars_) = $this->parseIdentifier($e['as']);
                $r_ .= ' AS ' . $i_;
                $vars = array_merge($vars, $vars_);
            }

            $r[] = $r_;
        }
        $ret[] = join(', ', $r);

    // from
        if ($this->parts['from']) {
            $ret[] = ' FROM ';
            $r = [];
            foreach ($this->parts['from'] as $e) {
                if (isset($e['as'])) {
                    list($i1_, $vars1_) = $this->parseIdentifier($e['name']);
                    list($i2_, $vars2_) = $this->parseIdentifier($e['as']);
                    $r[] = $i1_ . ' AS ' . $i2_;
                    $vars = array_merge($vars, $vars1_);
                    $vars = array_merge($vars, $vars2_);
                } else {
                    list($i_, $vars_) = $this->parseIdentifier($e['name']);
                    $r[] = $i_;
                    $vars = array_merge($vars, $vars_);
                }
            }
            $ret[] = join(', ', $r);
        }

    // joins
        if ($this->parts['join']) {
            $sql_ = [];
            foreach ($this->parts['join'] as $join) {
                $table = $join['name'];

                $sql2 = '';
                if (isset($join['type'])) {
                    $sql2 .= $join['type'] . ' ';
                }

                list($i_, $vars_) = $this->parseIdentifier($table);
                $sql2 .= 'JOIN ' . $i_;
                $vars = array_merge($vars, $vars_);

                if (isset($join['as'])) {
                    list($i_, $vars_) = $this->parseIdentifier($join['as']);
                    $sql2 .= ' AS ' . $i_;
                    $vars = array_merge($vars, $vars_);
                }

                if (isset($join['on'])) {
                    [$col1, $op, $col2] = $join['on'];

                    list($i1_, $vars1_) = $this->parseIdentifier($col1);
                    list($i2_, $vars2_) = $this->parseIdentifier($col2);
                    $sql2 .= ' ON ' . $i1_ . ' ' . $op . ' ' . $i2_;
                    $vars = array_merge($vars, $vars1_);
                    $vars = array_merge($vars, $vars2_);
                }

                $sql_[] = $sql2;
            }
            $ret[] = ' ' . join(' ', $sql_);
        }

    // where
        if ($this->where) {
            [$sql_, $vars_] = $this->buildWhere();
            $vars = array_merge($vars, $vars_);
            $ret[] = ' WHERE ' . $sql_;
        }

    // order
        if ($this->order) {
            $sql_ = [];
            foreach ($this->order as $e) {
                list($i_, $vars_) = $this->parseIdentifier($e['name']);
                $how = ('DESC' === $e['how']) ? 'DESC' : 'ASC';
                $sql_[] = $i_ . ' ' . $how;
                $vars = array_merge($vars, $vars_);
            }
            $ret[] = ' ORDER BY ' . join(', ', $sql_);
        }

    // group
        if ($this->groupBy) {
            $sql_ = [];
            foreach ($this->groupBy as $e) {
                list($i_, $vars_) = $this->parseIdentifier($e);
                $sql_[] = $i_;
                $vars = array_merge($vars, $vars_);
            }
            $ret[] = ' GROUP BY ' . join(', ', $sql_);
        }

        if ($this->limit && $this->offset) {
            $ret[] = ' LIMIT %d, %d';
            $vars[] = $this->offset;
            $vars[] = $this->limit;
        } elseif ($this->limit) {
            $ret[] = ' LIMIT %d';
            $vars[] = $this->limit;
        }

        $ret = join('', $ret);
        $ret = [$ret, $vars];

        $this->reset();

        return $ret;
    }

    private function buildWhere(): array
    {
        $sql = $vars = [];

        $currentlyGrouped = $sqlGroup = [];
        foreach ($this->where as $w) {
            if ('OR' === $w) {
                $currentlyGrouped[] = 'OR';
                $sqlGroup[] = [];
            } elseif ('AND' === $w) {
                $currentlyGrouped[] = 'AND';
                $sqlGroup[] = [];
            } elseif ('END' === $w) {
                $sql_ = '(' . join(' ' . array_pop($currentlyGrouped) . ' ', array_pop($sqlGroup)) . ')';
                if ($sqlGroup) {
                    $sqlGroup[count($sqlGroup) - 1][] = $sql_;
                } else {
                    $sql[] = $sql_;
                }
            } elseif (is_array($w)) {
            // just simple compare
                $operator = $w['operator'];

                if (isset($w['col1'])) {
                    $col1 = $w['col1'];
                    $col2 = $w['col2'];

                    list($i_, $vars_) = $this->parseIdentifier($col1);
                    $sql1_ = $i_;
                    $vars = array_merge($vars, $vars_);

                    list($i_, $vars_) = $this->parseIdentifier($col2);
                    $sql2_ = $i_;
                    $vars = array_merge($vars, $vars_);

                    $sql_ = $sql1_ . ' ' . $operator . ' ' . $sql2_;
                } else {
                    $col = $w['col'];
                    $v = $w['v'];
                    
                    if (is_array($v)) {
                        if (1 === count($v)) {
                            $v = current($v);
                        } else {
                            if ('=' === $operator) {
                                $operator = 'IN';
                                $v = array_unique($v);
                            } elseif ('<>' === $operator) {
                                $operator = 'NOT IN';
                                $v = array_unique($v);
                            }
                        }
                    }

                    $this->checkOperator($operator, $v);

                    $sql_ = '';

                    list($i_, $vars_) = $this->parseIdentifier($col);
                    $sql_ = $i_;
                    $vars = array_merge($vars, $vars_);

                    $v = $this->prepareValue($v);

                // subquery?
                    if (is_object($v) ){
                        if ('=' === $operator) {
                            $operator = 'IN';
                        }
                        $sql_ .= ' ' . $operator . ' (' . $this->prepareSelectQuery($v) . ')';
                    } else {
                        if ((null === $v) && ('=' === $operator)) {
                            $sql_ .= ' IS NULL';
                        } elseif ((null === $v) && ('<>' === $operator)) {
                            $sql_ .= ' IS NOT NULL';
                        } else {
                            $sql_ .= ' ' . $operator . ' ';
                            if (is_array($v)) {
                                $sql_ .= '(' . join(', ', array_map(fn($e) => $this->placeholder($e), $v)) . ')';
                                $vars = array_merge($vars, $v);
                            } else {
                                $sql_ .= $this->placeholder($v);
                                $vars[] = $v;
                            }
                        }
                    }
                }

                if ($currentlyGrouped) {
                    $sqlGroup[count($sqlGroup) - 1][] = $sql_;
                } else {
                    $sql[] = $sql_;
                }

            } else {
                $sql[] = $w;
            }
        }

        $sql = join(' AND ', $sql);

        return [$sql, $vars];
    }

    private function parseIdentifier(string $v): array
    {
        $placeholders = $vars = [];

        if (false !== strpos($v, '.')) {
            $vs = explode('.', $v);
        } else {
            $vs = [$v];
        }

        foreach ($vs as $v) {
            if (in_array($v, ['*'])) {
                $placeholders[] = '*';
            } else {
                if (!preg_match('/^[a-zA-Z0-9_\.]+$/', $v)) {
                    throw new \InvalidArgumentException('Only alphanumerics allowed for identifiers');
                }
                $placeholders[] = '%i';
                $vars[] = $v;
            }
        }

        return [join('.', $placeholders), $vars];
    }

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

        return $ret;
    }

    private function checkOperator(string $operator, $v)
    {
        $allowed = ['=', '<>', '>', '<', '>=', '<=', 'IN', 'NOT IN', 'EXISTS', 'NOT EXISTS', 'LIKE'];
        if (!in_array($operator, $allowed)) {
            throw new \InvalidArgumentException('Operator no allowed: ' . $operator);
        }

        $allowedForArray = ['IN', 'NOT IN'];
    // subquery?
        if (is_object($v)) {
        } elseif (is_array($v)) {
            if (!in_array($operator, $allowedForArray)) {
                throw new \InvalidArgumentException('Operator no allowed for array: ' . $operator);
            }
        } else {
            if (!in_array($operator, array_diff($allowed, $allowedForArray))) {
                throw new \InvalidArgumentException('Operator no allowed for non array: ' . $operator);
            }
        }
    }

    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;
    }

/* proxy methods for database */
    public final function execute()
    {
        if (!$this->db) {
            throw new \Exception('db is not set for query builder');
        }

        [$sqlTemplate, $vars] = $this->build();

        return $this->db->execute($sqlTemplate, $vars);
    }
}
