<?php

namespace Velis\Db;

use ArrayObject;
use Velis\App;
use Velis\Arrays;

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

        $sql = "";

        foreach ($params as $field => $values) {
            $separator = "";

            $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 !== null && !($values instanceof NullValue)) {
                    continue;
                }
            }

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

                if (count($values)) {
                    $sql .= " AND (" . $field . " IN( ";

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

                            $separator = ", ";
                        }
                    }

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

        $params = $bindParams;

        return $sql;
    }

    /**
     * Translate query with named parameters into unnamed parameters compatible with ODBC driver
     *
     * @param array<string|int, mixed> $params
     */
    protected function translateParams(string &$sql, array &$params): void
    {
        $unnamedParams = [];
        $matchedParams = [];

        if (preg_match_all('/:([a-z_0-9]+)/i', $sql, $matches)) {
            foreach ($matches[0] as $key => $match) {
                if (array_key_exists($matches[1][$key], $params)) {
                    $unnamedParams[] = $params[$matches[1][$key]];
                    $matchedParams[] = $match;
                }
            }
        }
        $matchedParams = array_reverse($matchedParams);
        $sql = str_replace($matchedParams, '?', $sql);
        $params = $unnamedParams;
    }

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

        if (!($columns = $this->_objectsColumns[$dataSource])) {
            if (null === ($columns = App::$cache[$cacheName])) {
                if (strpos($dataSource, '.')) {
                    list ($schema, $table) = explode('.', $dataSource);
                } else {
                    $table = $dataSource;
                    $schema = App::$config->snowflake->schema;
                }

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

                $result = $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
                    FROM INFORMATION_SCHEMA.COLUMNS c
                    WHERE
                        c.table_schema = :schema
                        AND c.table_name = :table
                ', $params, 'COLUMN_NAME');

                foreach ($result as $key => $row) {
                    $columns[$key] = array_change_key_case($row, CASE_LOWER);
                }

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

        return $columns ?? [];
    }

    /**
     * Return available databases
     * @return array<array<string, mixed>>
     */
    public function getDatabases(): array
    {
        return $this->getAll("SELECT * FROM INFORMATION_SCHEMA.DATABASES");
    }
}
