<?php

namespace Velis\Db;

use ArrayObject;
use PDO;
use PDOStatement;
use stdClass;
use Velis\App;
use Velis\Arrays;
use Velis\Config\Ini as IniConfig;
use Velis\Lang;

/**
 * PostgreSQL database adapter
 * @author Olek Procki <olo@velis.pl>
 */
class Postgres extends Db
{
    // default timeouts for postgres in milliseconds
    public const DEFAULT_POSTGRES_TIMEOUT = 0;
    public const POSTGRES_QUICK_REPORT_TIMEOUT = 180000;

    /**
     * Singleton instance
     * @var Db
     */
    protected static $_instance;

    protected bool $_connected = false;

    protected bool $_timeZoneSet = false;

    /**
     * {@inheritDoc}
     */
    public function connect(array &$profilerInfo = null): self
    {
        $this->_currentHost = App::$config->db->host;
        $innerCall = false;

        if ($this->_debug && $profilerInfo === null) {
            $profilerInfo = [];
            $profilerInfo['query']     = 'CONNECT';
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['server']    = $this->_currentHost;
        } else {
            $innerCall = true;
        }

        if (!$this->_connectionParams) {
            $dbParams = App::$config->db;

            if (file_exists(CONFIG_PATH . 'database.ini')) {
                $serverConfig = new IniConfig(CONFIG_PATH . 'database.ini', 'database');
                if ($serverConfig->db) {
                    $dbParams = $serverConfig->db;
                }
            }

            $this->_connectionParams = $dbParams;
        }

        $pdoParams = [];
        if ($this->_connectionParams->persistent) {
            $pdoParams[PDO::ATTR_PERSISTENT] = true;
        }

        $this->_pdo = new PDO(
            "pgsql:host=" . $this->_connectionParams->host
            . ";port="    . $this->_connectionParams->port
            . ";dbname="  . $this->_connectionParams->database
            . ($this->_connectionParams->sslmode ? "" : ";sslmode=disable"),
            $this->_connectionParams->user,
            $this->_connectionParams->password,
            $pdoParams
        );

        $this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $language = Lang::getLanguage();
        if (App::$config->db->datestyle && ($dateStyle = App::$config->db->datestyle->{$language})) {
            $this->_pdo->exec('SET DateStyle TO ' . $dateStyle);
        }

        // set default timeout for postgres
        $this->_pdo->exec("SET statement_timeout TO " . self::DEFAULT_POSTGRES_TIMEOUT);

        if ($this->shouldLogToProfiler()) {
            $profilerInfo['info'] = "Connected to "
                                  . $this->_connectionParams->database
                                  . "@" . $this->_connectionParams->host;
            if (!$innerCall) {
                $this->_profiler->addRow($profilerInfo);
            }
        }

        $this->_connected = true;

        $this->setDefaultTimezone();

        return $this;
    }


    /**
     * Sets timezone by logged user
     */
    private function setDefaultTimezone(): void
    {
        if (
            !App::isConsole()
            && App::$config->hasTimezoneSupport()
            && App::$user
            && App::$user->isLogged()
            && App::$user->getTimezone()
        ) {
            $this->_pdo->exec("SET timezone='" . App::$user->getTimezone() . "';");
            $this->_timeZoneSet = true;
        }
    }

    public function setCustomTimezone(string $timezone): void
    {
        if (App::$config->hasTimezoneSupport()) {
            $this->_pdo->exec("SET timezone='" . $timezone . "';");
            $this->_timeZoneSet = true;
        }
    }

    /**
     * Disconnects from database
     * @return $this
     */
    public function disconnect(): self
    {
        $this->_pdo = null;
        $this->_connected = false;

        return $this;
    }

    public function isConnected(): bool
    {
        return $this->_connected;
    }

    /**
     * Reconnect if no connection
     */
    public function checkConnection(array &$profilerInfo = null): void
    {
        parent::checkConnection($profilerInfo);

        if (!$this->_timeZoneSet) {
            $this->setDefaultTimezone();
        }
    }


    /**
     * {@inheritDoc}
     */
    public function currval(string $sequence): int
    {
        return $this->getOne("SELECT currval('" . $sequence . "') AS last_value");
    }

    /**
     * {@inheritDoc}
     */
    public function setval(string $sequence, int $value): int
    {
        return $this->getOne("SELECT setval('" . $sequence . "', '$value')");
    }

    /**
     * {@inheritDoc}
     */
    public function nextval(string $sequence): int
    {
        return $this->getOne("SELECT nextval('" . $sequence . "') AS next_value");
    }

    /**
     * {@inheritDoc}
     */
    public function startTrans(&$profilerInfo = null): bool
    {
        if ($this->_debug) {
            $profilerInfo = [];
            $profilerInfo['query']     = 'BEGIN TRANSACTION';
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['server']    = $this->_currentHost;
        }

        $this->checkConnection($profilerInfo);

        return parent::startTrans($profilerInfo);
    }


    /**
     * Returns stored function arguments list
     *
     * @param string $function
     * @param string $schema
     *
     * @return array
     */
    public function getFunctionArgs($function, $schema = 'public')
    {
        $arguments = App::$cache["pgsql_" . $schema . "_" . $function . "_args"];
        if ($arguments === null) {
            if ($schema != 'public') {
                $this->execDML("SET search_path = 'public','$schema'");
            }

            $params = [
               'function' => $function,
               'schema' => $schema,
            ];

            $query = "SELECT * FROM public.function_args(:function, :schema)";
            $arguments = $this->getAll($query, $params, 'argname');

            App::$cache["pgsql_" . $schema . "_" . $function . "_args"] = $arguments;
        }

        return $arguments;
    }


    /**
     * Explodes record (as result of stored function) to array
     *
     * @param \stdClass|string|null $record
     * @param array $keys
     *
     * @return array
     */
    public function recordToArray($record, $keys): array
    {
        $result = [];

        if (null === $record) {
            return $result;
        }

        if (is_object($record) && $record instanceof stdClass) {
            $values = get_object_vars($record);
        } else {
            $values = str_getcsv(trim($record, '()'));
        }

        for ($i = 0; $i < count($keys); $i++) {
            $result[rtrim($keys[$i], '_')] = trim($values[$i] ?? '', '"');
        }

        return $result;
    }


    /**
     * Converts array to database record type
     * @param array $array
     * @param array $keys
     *
     * @return string
     */
    public function arrayToRecord($array, $keys = null)
    {
        if (!$keys) {
            $keys = array_keys($array);
        }

        $preparedValue = '(';
        foreach ($keys as $field) {
            $preparedValue .= '"' . str_replace('"', '\"', $array[$field]) . '",';
        }
        $preparedValue = trim($preparedValue, ',');
        $preparedValue .= ")";

        return $preparedValue;
    }


    /**
     * Executes stored function
     *
     * @param string $functionName
     * @param array|ArrayObject $bindParams
     * @return mixed
     */
    public function execFunction($functionName, &$bindParams = null)
    {
        if ($this->_debug) {
            $profilerInfo = [];
            $profilerInfo['caller']    = $this->_getCaller();
            $profilerInfo['func']      = __FUNCTION__;
            $profilerInfo['startTime'] = $this->getCurrentLifeTime();
            $profilerInfo['params']    = $bindParams;
            $profilerInfo['server']    = $this->_currentHost;
        }

        $this->checkConnection($profilerInfo);


        if (strpos($functionName, '.')) {
            list ($schema, $function) = explode('.', $functionName);
        } else {
            $function = $functionName;
            $schema   = 'public';
        }

        $arguments = $this->getFunctionArgs($function, $schema);
        $sql = "SELECT $functionName(";
        $params = [];
        $outParams = [];
        $i = 0;
        $resultType = null;

        foreach ($arguments as $argname => $arg) {
            if ($argname != 'RETURN VALUE') {
                // IN & INOUT parameter
                if ($arg['direction'] != 'o') {
                    // INOUT parameter
                    if ($arg['direction'] == 'b') {
                        $outParams[] = $argname;
                    }

                    if (!isset($bindParams[rtrim($argname, '_')]) || $bindParams[rtrim($argname, '_')] === '') {
                        $params[$argname] = null;
                    } else {
                        $params[$argname] = $bindParams[rtrim($argname, '_')];
                    }

                    $sql .= ":$argname";
                    if ($i < count($arguments) - 1) {
                        $sql .= ',';
                    }

                // OUT parameters
                } else {
                    $outParams[] = $argname;
                }
            } else {
                $resultType = $arg['datatype'];
            }

            $i++;
        }
        $sql = trim($sql, ',');
        $sql .= ')';

        $stmt = count($params) ? $this->_exec($sql, $params) : $this->_query($sql);
        $result = Arrays::getFirst($stmt->fetch(PDO::FETCH_ASSOC));

        if ($resultType == 'record') {
            $result = $this->recordToArray($result, $outParams);
        }

        if ($this->shouldLogToProfiler()) {
            $profilerInfo['query'] = $sql;
            $this->_profiler->addRow($profilerInfo);
        }

        return $result;
    }


    /**
     * Checks if function exists
     *
     * @param string $functionName
     * @return bool
     */
    public function functionExists($functionName)
    {
        if (strpos($functionName, '.')) {
            list ($schema, $function) = explode('.', $functionName);
        } else {
            $function = $functionName;
            $schema   = 'public';
        }

        return 0 != $this->getOne('
            SELECT COUNT(*) FROM pg_catalog.pg_proc p
            JOIN pg_catalog.pg_namespace n ON (p.pronamespace = n.oid)
            WHERE p.proname = :function AND n.nspname=:schema
        ', [
            'function' => $function,
            'schema' => $schema,
        ]);
    }


    /**
     * Checks if table exists
     *
     * @param string $functionName
     * @return bool
     */
    public function tableExists($tableName)
    {
        if (strpos($tableName, '.')) {
            list ($schema, $table) = explode('.', $tableName);
        } else {
            $table  = $tableName;
            $schema = 'public';
        }

        return 0 != $this->cacheGetOne(
            'SELECT COUNT(*) FROM pg_tables WHERE schemaname=:schema AND tablename=:table',
            [
                'table' => $table,
                'schema' => $schema,
            ]
        );
    }

    /**
     * {@inheritDoc}
     */
    public function getColumns(string $dataSource): array
    {
        if (strpos($dataSource, ' ')) {
            $dataSource = substr($dataSource, 0, strpos($dataSource, ' '));
        }
        $cacheName = str_replace('.', '_', $dataSource) . '_columns';

        $columns = $this->_objectsColumns[$dataSource] ?? null;
        if (!$columns) {
            if (null === ($columns = App::$cache[$cacheName])) {
                if (strpos($dataSource, '.')) {
                    [$schema, $table] = explode('.', $dataSource);
                } else {
                    $table  = $dataSource;
                    $schema = 'public';
                }
                $columns = $this->getAll(
                    "SELECT c.table_schema,
                            c.table_name,
                            c.column_name,
                            c.ordinal_position,
                            c.is_nullable,
                            c.data_type,
                            c.character_maximum_length,
                            c.udt_schema,
                            c.udt_name,
                            (SELECT array_to_string(array_agg(attname ORDER BY attnum), ',')
                               FROM pg_catalog.pg_class pc
                               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pc.relnamespace
                               LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = pc.oid
                               WHERE n.nspname = c.udt_schema
                                AND pc.relname  = c.udt_name
                                AND pc.relkind = 'c'
                            ) AS type_fields,
                            (SELECT COUNT(typname) > 0
                                FROM pg_catalog.pg_type t
                                WHERE typtype = 'e'
                                    AND c.udt_name = t.typname
                            ) AS is_enum
                    FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.table_schema=:schema AND c.table_name=:table",
                    ['schema' => $schema, 'table' => $table],
                    'column_name'
                );

                foreach ($columns as $key => $col) {
                    if ($col['type_fields']) {
                        $columns[$key]['type_fields'] = explode(',', $col['type_fields']);
                    }
                }

                App::$cache[$cacheName] = $columns;
            }
            $this->_objectsColumns[$dataSource] = $columns;
        }

        return $columns;
    }


    /**
     * Transforms query parameters into SQL where statement
     *
     * @param array|ArrayObject $params
     * @return string
     */
    public function conditions(&$params)
    {
        if ($params instanceof ArrayObject) {
            $class = get_class($params);
            $bindParams = new $class();
        } else {
            $bindParams = [];
        }

        $sql = '';

        foreach ($params as $field => $values) {
            $fieldBindName = preg_replace('/[^[:alnum:]]/', '_', $field);

            if (is_array($values) && count($values) === 1) {
                // same as scalar value
                $values = array_pop($values);

                if (!$values && $values !== 0 && $values !== '0' && $values !== null && !($values instanceof NullValue)) {
                    continue;
                }
            }

            // scalar value
            if (!is_array($values) && !($values instanceof ArrayObject)) {
                if ($values === null || $values instanceof NullValue) {
                    $sql .= " AND {$field} IS NULL ";
                } elseif ($values instanceof AnyValue) {
                    $sql .= " AND {$field} IS NOT NULL ";
                } elseif ($values instanceof ExpressionInterface) {
                    $values->setFieldName($field);
                    $sql .= " AND ({$values->getSql()}) ";
                    $values->bindParams($bindParams);
                } elseif (strpos($values, '%') !== false || strpos($values, '_') !== false) {
                    $sql .= " AND {$field} ILIKE :{$fieldBindName}";
                    $bindParams[$fieldBindName] = $values;
                } else {
                    $sql .= " AND {$field} = :{$fieldBindName}";
                    $bindParams[$fieldBindName] = $values;
                }
            } else {
                // array of values
                $values = Arrays::filterKeepZeros($values);
                $nullsAllowed = false;

                if (count($values)) {
                    $queryParams = [];
                    $sql .= " AND ({$field} IN( ";

                    foreach ($values as $key => $oneValue) {
                        if ($oneValue instanceof NullValue) {
                            $nullsAllowed = true;
                        } else {
                            $queryParams[] = ":{$fieldBindName}_{$key}";
                            $bindParams["{$fieldBindName}_{$key}"] = $oneValue;
                        }
                    }

                    $sql .= implode(', ', $queryParams) . ')';
                    if ($nullsAllowed) {
                        $sql .= " OR {$field} IS NULL";
                    }
                    $sql .= ')';
                }
            }
        }

        $params = $bindParams;
        return $sql;
    }


    /**
     * Returns primary key fields
     *
     * @param string $table
     * @param bool $simple
     * @return array
     * @throws \Velis\Exception
     */
    public function getPrimaryKeyFields($table, $simple = false)
    {
        $cacheKey = str_replace('.', '_', $table)  . '_pkey_fields';

        $fields = App::$cache[$cacheKey];
        if ($fields === null) {
            $sql = "SELECT
                        pg_attribute.attname as field,
                        format_type(pg_attribute.atttypid, pg_attribute.atttypmod) as type
                    FROM pg_index, pg_class, pg_attribute
                    WHERE
                        pg_class.oid = :table_name::regclass AND
                        indrelid = pg_class.oid AND
                        pg_attribute.attrelid = pg_class.oid AND
                        pg_attribute.attnum = any(pg_index.indkey)
                        AND indisprimary";

            $fields = $this->getAll($sql, array('table_name' => $table));
            App::$cache[$cacheKey] = $fields;
        }

        if ($simple) {
            return Arrays::getColumn($fields, 'field');
        } else {
            return $fields;
        }
    }

    /**
     * {@inheritdoc}
     */
    protected function _extractMetadata($stmt)
    {
        parent::_extractMetadata($stmt);

        foreach ($this->metadata as &$columnMetadata) {
            if ('numeric' != $columnMetadata['native_type']) {
                $columnMetadata['scale'] = null;
            } elseif (-1 == $columnMetadata['precision']) {
                $columnMetadata['scale'] = null;
            } else {
                $columnMetadata['scale'] = ($columnMetadata['precision'] - 4) & 65535;
            }
        }
    }
}
