<?php

namespace ByJG\AnyDataset\Db\Helpers;

use ByJG\AnyDataset\Db\DbDriverInterface;
use ByJG\AnyDataset\Db\IsolationLevelEnum;

class DbPgsqlFunctions extends DbBaseFunctions
{
    public function __construct()
    {
        $this->deliFieldLeft = '"';
        $this->deliFieldRight = '"';
        $this->deliTableLeft = '"';
        $this->deliTableRight = '"';
    }

    public function concat(string $str1, ?string $str2 = null): string
    {
        return implode(' || ', func_get_args());
    }

    /**
     * Given a SQL returns it with the proper LIMIT or equivalent method included
     * @param string $sql
     * @param int $start
     * @param int $qty
     * @return string
     */
    public function limit(string $sql, int $start, int $qty = 50): string
    {
        if (stripos($sql, ' LIMIT ') === false) {
            $sql = $sql . " LIMIT x OFFSET y";
        }

        return preg_replace(
            '~(\s[Ll][Ii][Mm][Ii][Tt])\s.*?\s([Oo][Ff][Ff][Ss][Ee][Tt])\s.*~',
            '$1 ' . $qty . ' $2 ' . $start,
            $sql
        );
    }

    /**
     * Given a SQL returns it with the proper TOP or equivalent method included
     * @param string $sql
     * @param int $qty
     * @return string
     */
    public function top(string $sql, int $qty): string
    {
        return $this->limit($sql, 0, $qty);
    }

    /**
     * Return if the database provider have a top or similar function
     * @return bool
     */
    public function hasTop(): bool
    {
        return true;
    }

    /**
     * Return if the database provider have a limit function
     * @return bool
     */
    public function hasLimit(): bool
    {
        return true;
    }

    /**
     * Format date column in sql string given an input format that understands Y M D
     *
     * @param string $format
     * @param string|null $column
     * @return string
     * @example $db->getDbFunctions()->SQLDate("d/m/Y H:i", "dtcriacao")
     */
    public function sqlDate(string $format, ?string $column = null): string
    {
        if (is_null($column)) {
            $column = 'current_timestamp';
        }

        $pattern = [
            'Y' => "YYYY",
            'y' => "YY",
            'M' => "Mon",
            'm' => "MM",
            'Q' => "Q",
            'q' => "Q",
            'D' => "DD",
            'd' => "DD",
            'h' => "HH",
            'H' => "HH24",
            'i' => "MI",
            's' => "SS",
            'a' => "AM",
            'A' => "AM",
        ];

        return sprintf(
            "TO_CHAR(%s,'%s')",
            $column,
            implode('', $this->prepareSqlDate($format, $pattern, ''))
        );
    }

    /**
     * @param DbDriverInterface $dbdataset
     * @param string $sql
     * @param array|null $param
     * @return mixed
     */
    public function executeAndGetInsertedId(DbDriverInterface $dbdataset, string $sql, ?array $param = null): mixed
    {
        parent::executeAndGetInsertedId($dbdataset, $sql, $param);
        return $dbdataset->getScalar('select lastval()');
    }

    public function hasForUpdate(): bool
    {
        return true;
    }

    public function getTableMetadata(DbDriverInterface $dbdataset, string $tableName): array
    {
        $tableName = strtolower($tableName);
        $sql = "select column_name, data_type || '(' || coalesce(cast(character_maximum_length as varchar), cast(numeric_precision_radix as varchar) || ',' || numeric_scale) || ')' as type, column_default, is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name = '$tableName' ";
        return $this->getTableMetadataFromSql($dbdataset, $sql);
    }

    protected function parseColumnMetadata($metadata)
    {
        $return = [];

        foreach ($metadata as $key => $value) {
            $return[strtolower($value['column_name'])] = [
                    'name' => $value['column_name'],
                    'dbType' => strtolower($value['type']),
                    'required' => $value['is_nullable'] == 'NO',
                    'default' => $value['column_default'],
                ] + $this->parseTypeMetadata(strtolower($value['type']));
        }

        return $return;
    }

    public function getIsolationLevelCommand(?IsolationLevelEnum $isolationLevel = null): string
    {
        return match ($isolationLevel) {
            IsolationLevelEnum::READ_UNCOMMITTED => "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED",
            IsolationLevelEnum::READ_COMMITTED => "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED",
            IsolationLevelEnum::REPEATABLE_READ => "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ",
            IsolationLevelEnum::SERIALIZABLE => "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE",
            default => "",
        };
    }

    public function getJoinTablesUpdate(array $tables): array
    {
        $joinTables = [];
        $join = "FROM";
        foreach ($tables as $table) {
            $joinTables[] = " $join " . $this->deliTableLeft . $table['table'] . $this->deliTableRight . " ON " . $table['condition'];
            $join = " INNER JOIN ";
        }

        return [
            "position" => "after_set",
            "sql" => implode(' ', $joinTables)
        ];
    }
}__halt_compiler();----SIGNATURE:----oUcjrZZCfPSuAgJJsMmbL9tDscOSO5AaL3idjrBiFh8IHR/rpYHYQXkS4/jq2wd0nNZ67ipxVcHQA3A5GXk1oaOeWykEwWBdjQ79lUoaqt1o+r07gItPxrP7f7Jk0VL66Ec0x1vY90Jg9y/k3+pPIA/sz7sRqZehVJfIxM4g8FKIVW5+22VAHI02TLSRmfBJ5oOkrZGFlTNOgDZ0fx8sHEeMw8DrSVDOPg+0eqQKcmPwRMcE7Ykb2i/jf2N2m10f+OPliXJaMKzbaoz2WXgHgN2b3KwcACn+ir0TQD5MfEBJYT8hk0TA6aU0qAAHNIm5NBwAFbTaqw83pppNjvkGgOfq0Hb4tP1yzHlkXsaAj2o3EKUnxTPGbVDCu2H51lez3yC9YQWbhh3Vv7q6R2yIYiE4Rlpmfr1xd+xUUdY1LkDllZVFVavz8IFVoSVyff1NyIwHaXtZnT6vVaCS9K4EOHGI6XIZqYksOiSFGzX1mTK4Uhd9MRFek7DwnR/a0CrSgBzv5SVwB2+3CjakAon7LDgyqhH5VZEbnias3ULAVJq+xHx6WzSCEQGcX0/F/pRtWneiilttmG/9tEFm4wfOnzRdY85UhbbB9tHu+dlTNJgNWqlphpGFp5/vJ8QAc/Zm3mgd8fNymA3Xqg4r89HBadKwzXvdMNZ3A2vF1mXI1jQ=----ATTACHMENT:----MjQ5NDIzNDE4NDAwMjc4NCA4MzExNTM2ODI2NzA1MiA3NDE2OTY0MDMwODc0MTE0