<?php

trait DatabaseCommunication
{
    public function insertSQL($table, $SQLFields, $except = [], $types = [])
    {
        wpj_debug(['insertSQL', 'table' => $table, 'SQLFields' => $SQLFields, 'except' => $except, 'types' => $types]);
        $conn = $this->getDbalConnection();

        return $conn->insert(getTableName($table), $this->escapeFields($this->excludeFields($SQLFields, $except)), $types);
    }

    public function updateSQL($table, $SQLFields, $idArray, $except = [], $types = [])
    {
        wpj_debug(['updateSQL', 'table' => $table, 'SQLFields' => $SQLFields, 'idArray' => $idArray, 'except' => $except, 'types' => $types]);
        $conn = $this->getDbalConnection();

        return $conn->update(getTableName($table), $this->escapeFields($this->excludeFields($SQLFields, $except)), $idArray, $types);
    }

    public function deleteSQL($table, $idArray)
    {
        wpj_debug(['deleteSQL', 'table' => $table, 'idArray' => $idArray]);
        $conn = $this->getDbalConnection();

        return $conn->delete(getTableName($table), $idArray);
    }

    public function replaceSQL($table, $SQLFields, $except = [], $types = [])
    {
        wpj_debug(['replaceSQL', 'table' => $table, 'SQLFields' => $SQLFields, 'except' => $except, 'types' => $types]);
        $conn = $this->getDbalConnection();
        $values = $this->escapeFields($this->excludeFields($SQLFields, $except));

        return $conn->executeUpdate("REPLACE INTO {$table}".$this->createInsert($values), $values, $types);
    }

    public function selectSQL($table, $idArray, $fields = null)
    {
        wpj_debug(['selectSQL', 'table' => $table, 'idArray' => $idArray, 'fields' => $fields]);
        if ($fields) {
            if (is_string(array_keys($fields)[0])) {
                $fields = join(',', array_map(function ($alias, $value) {
                    return "{$value} as {$alias}";
                }, array_keys($fields), $fields));
            } else {
                $fields = join(',', $fields);
            }
        } else {
            $fields = '*';
        }

        $conn = $this->getDbalConnection();
        $sql = "SELECT {$fields} FROM ".getTableName($table).' WHERE '.$this->createWhere($idArray);

        return $conn->executeQuery($sql, $idArray);
    }

    /**
     * @return Doctrine\DBAL\Query\QueryBuilder
     */
    public function getQueryBuilder()
    {
        return sqlQueryBuilder();
    }

    public function getConnection()
    {
        return $this->getDbalConnection();
    }

    /**
     * @return Doctrine\DBAL\Connection
     */
    public function getDbalConnection()
    {
        $conn = sqlGetConnection();

        static $initialized = false;
        if (!$initialized) {
            $platform = $conn->getDatabasePlatform();
            $platform->registerDoctrineTypeMapping('enum', 'string');
            $platform->registerDoctrineTypeMapping('point', 'string');

            $initialized = true;
        }

        return $conn;
    }

    public function escapeFields($fields)
    {
        return array_combine(
            array_map(function ($field) {
                switch ($field) {
                    case 'interval':
                    case 'number':
                    case 'set':
                        return "`{$field}`";

                    default:
                        return $field;
                }
            }, array_keys($fields)),
            array_values($fields));
    }

    public function filterFields($values, $fields)
    {
        return array_intersect_key($values, array_flip($fields));
    }

    public function excludeFields($values, $fields)
    {
        return array_diff_key($values, array_flip($fields));
    }

    public function createWhere($values)
    {
        $ret = [];

        foreach ($values as $field => $value) {
            $param = $field;
            if (($pos = strpos($param, '.')) !== false) {
                $param = substr($param, $pos + 1);
            }

            if ($value === null) {
                $ret[] = "{$field} IS NULL";
            } else {
                $ret[] = "{$field} = :{$param}";
            }
        }

        if (empty($ret)) {
            $ret[] = '1=1';
        }

        return join(' AND ', $ret);
    }

    public function createUpdate($values)
    {
        return join(', ', array_map(function ($field) {
            return "{$field} = :{$field}";
        }, array_keys($values)));
    }

    public function createInsert($values)
    {
        $fields = array_keys($this->escapeFields($values));

        return '('.join(', ', $fields).') VALUES ('.join(', ', array_map(function ($x) {
            return ':'.$x;
        }, $fields)).')';
    }

    public function fetchObject($table, $ID)
    {
        $SQL = sqlQuery('SELECT * FROM '.getTableName($table)." WHERE id='{$ID}' ");
        if (sqlNumRows($SQL) == 1) {
            return sqlFetchAssoc($SQL);
        }
    }

    public function preparePrice(&$price)
    {
        if (($price === '') || ($price === null)) {
            return null;
        }

        return $price = floatval(preg_replace('/[^\d\.-]/', '', str_replace(',', '.', $price)));
    }

    /**
     * @param $price array [value, with_vat, vat]
     *
     * @return Decimal
     */
    public function prepareVatPrice($price)
    {
        if ($price['value'] === '') {
            return null;
        }

        $dbcfg = Settings::getDefault();
        $priceWithVat = $price['with_vat'] ?? ($dbcfg['prod_prefer_price_vat'] == 'Y' || $dbcfg['prod_prefer_price_vat'] == 'F');
        $vat = $price['vat'] ?? getAdminVat()['value'];
        $price = toDecimal($this->preparePrice($price['value']));
        if ($priceWithVat) {
            $price = $price->removeVat($vat);
        }

        return $price;
    }

    public function prepareInt(&$number, $default = 0)
    {
        if (!is_numeric($number)) {
            $number = $default;
        }

        return $number = intval(str_replace([',', ' '], ['.', ''], $number));
    }

    public function prepareBoolean(&$bool)
    {
        return $bool = ($bool == 'Y' || $bool == '1') ? 1 : 0;
    }

    public function prepareYesNo(&$bool)
    {
        $this->prepareBoolean($bool);

        return $bool ? 'Y' : 'N';
    }

    public function prepareNull(&$value)
    {
        $value = trim($value);

        return $value = $value == '' ? null : $value;
    }

    public function prepareDate($date)
    {
        if (empty($date)) {
            return '';
        }

        if (!$date instanceof DateTime) {
            $date = DateTime::createFromFormat(Settings::getDateFormat(), $date);
            if ($date == false) {
                $date = new DateTime();
            }
        }

        return $date->format('Y-m-d');
    }

    public function prepareDateTime($datetime)
    {
        if (empty($datetime)) {
            return '';
        }

        if (!$datetime instanceof DateTimeInterface) {
            $format = Settings::getDateFormat().' '.Settings::getTimeFormat();

            $datetime = DateTime::createFromFormat($format, $datetime);
            if ($datetime == false) {
                $datetime = new DateTime();
            }
        }

        return $datetime->format('Y-m-d H:i:s');
    }

    /**
     * @param Doctrine\DBAL\DBALException $e
     */
    public function translateException($e)
    {
        $exception = $e->getPrevious();
        switch (intval($exception->errorInfo[1])) {
            case 1062:
                // Message: Duplicate entry '17-ZE523' for key 'id_supplier_2'
                preg_match("/Duplicate entry '([^']+)' for key '([^']+)'/", $exception->errorInfo[2], $matches);
                $ErrStr = "Duplicitni kombinace {$matches[1]}";

                return $ErrStr;

            default:
                return $e->getMessage();
        }
    }
}