<?php

declare(strict_types=1);

namespace Zeedhi\Framework\DBAL\Driver\OCI8;

use Doctrine\DBAL\Driver\Result as ResultInterface;
use Doctrine\DBAL\Driver\FetchUtils;
use Doctrine\DBAL\Driver\OCI8\Exception\Error;

use function oci_cancel;
use function oci_error;
use function oci_fetch_all;
use function oci_fetch_array;
use function oci_num_fields;
use function oci_num_rows;
use function oci_field_name;
use function oci_field_type;
use function oci_field_precision;
use function oci_field_scale;

use const OCI_ASSOC;
use const OCI_FETCHSTATEMENT_BY_COLUMN;
use const OCI_FETCHSTATEMENT_BY_ROW;
use const OCI_NUM;
use const OCI_RETURN_LOBS;
use const OCI_RETURN_NULLS;

final class OCI8Result implements ResultInterface
{
    /** @var resource */
    private $statement;

    private bool $metaLoaded = false;
    private array $fieldTypes = [];
    private array $fieldIdByName = [];

    private string $decimalSeparator = '.';
    private string $thousandsSeparator = ',';
    private bool $setSeparator = true;

    public function __construct($statement)
    {
        $this->statement = $statement;
    }

    /* ==========================================================
     * METADATA
     * ========================================================== */

    private function loadMeta(): void
    {
        if ($this->metaLoaded) {
            return;
        }

        $count = oci_num_fields($this->statement);

        for ($i = 1; $i <= $count; $i++) {
            $name = oci_field_name($this->statement, $i);

            $this->fieldIdByName[$name] = $i;
            $this->fieldTypes[$i] = $this->resolveOracleType($i);
        }

        $this->metaLoaded = true;
    }

    private function resolveOracleType(int $i): string
    {
        $type = oci_field_type($this->statement, $i);

        if ($type === 'NUMBER') {
            $precision = oci_field_precision($this->statement, $i);
            $scale     = oci_field_scale($this->statement, $i);

            if ($scale === 0) {
                return 'int';
            }

            return 'float';
        }

//        if ($type === 'DATE' || $type === 'TIMESTAMP') {
//            return 'datetime';
//        }

        if ($type === 'BLOB') {
            return 'blob';
        }

        return 'string';
    }

    /* ==========================================================
     * NORMALIZAÇÃO
     * ========================================================== */

    private function normalizeValue(mixed $value, string $type): mixed
    {
        if ($value === null) {
            return null;
        }

        return match ($type) {
            'int'      => (int) $value,
            'float'    => (float) $this->normalizeFloatString($value),
            'datetime' => $this->parseOracleDate($value),
            default    => $value,
        };
    }

    protected function parseOracleDate(string $value): \DateTime
    {
        foreach ([
                     'd/m/Y H:i:s',
                     'd/m/Y',
                     'Y-m-d H:i:s',
                     'Y-m-d',
                 ] as $format) {
            $dt = \DateTime::createFromFormat($format, $value);
            if ($dt !== false) {
                return $dt;
            }
        }

        return new \DateTime($value);
    }

    private function normalizeFloatString(string $value): string
    {
        $this->loadNumericSeparators();

        return str_replace(
            $this->decimalSeparator,
            '.',
            str_replace($this->thousandsSeparator, '', $value)
        );
    }

    private function loadNumericSeparators(): void
    {
        if (! $this->setSeparator) {
            return;
        }

        try {
            $sessionInit = \Zeedhi\Framework\DependencyInjection\InstanceManager::getInstance()
                ->getService('crudSessionInit');

            $vars = $sessionInit->getDefaultSessionVars();
            $chars = $vars['NLS_NUMERIC_CHARACTERS'];

            $this->decimalSeparator   = $chars[0];
            $this->thousandsSeparator = $chars[1];
        } catch (\Throwable $e) {
            $this->decimalSeparator = '.';
            $this->thousandsSeparator = ',';
        }

        $this->setSeparator = false;
    }

    /* ==========================================================
     * FETCH API (compatível Doctrine)
     * ========================================================== */

    public function fetchNumeric()
    {
        return $this->fetch(OCI_NUM);
    }

    public function fetchAssociative()
    {
        return $this->fetch(OCI_ASSOC);
    }

    public function fetchOne()
    {
        return FetchUtils::fetchOne($this);
    }

    public function fetchAllNumeric(): array
    {
        return $this->fetchAll(OCI_NUM, OCI_FETCHSTATEMENT_BY_ROW);
    }

    public function fetchAllAssociative(): array
    {
        return $this->fetchAll(OCI_ASSOC, OCI_FETCHSTATEMENT_BY_ROW);
    }

    public function fetchFirstColumn(): array
    {
        return $this->fetchAll(OCI_NUM, OCI_FETCHSTATEMENT_BY_COLUMN)[0] ?? [];
    }

    public function rowCount(): int
    {
        return oci_num_rows($this->statement) ?: 0;
    }

    public function columnCount(): int
    {
        return oci_num_fields($this->statement) ?: 0;
    }

    public function free(): void
    {
        oci_cancel($this->statement);
    }

    /* ==========================================================
     * CORE FETCH
     * ========================================================== */

    private function fetch(int $mode)
    {
        $row = oci_fetch_array(
            $this->statement,
            $mode | OCI_RETURN_NULLS | OCI_RETURN_LOBS
        );

        if ($row === false) {
            if (oci_error($this->statement) !== false) {
                throw Error::new($this->statement);
            }

            return false;
        }

        $this->loadMeta();

        foreach ($row as $key => $value) {
            $fieldId = is_string($key)
                ? $this->fieldIdByName[$key]
                : $key + 1;

            $row[$key] = $this->normalizeValue(
                $value,
                $fieldId !== null
                    ? ($this->fieldTypes[$fieldId] ?? "")
                    : ""
            );
        }

        return $row;
    }

    private function fetchAll(int $mode, int $fetchStructure): array
    {
        oci_fetch_all(
            $this->statement,
            $rows,
            0,
            -1,
            $mode | OCI_RETURN_NULLS | $fetchStructure | OCI_RETURN_LOBS
        );

        $this->loadMeta();

        foreach ($rows as &$row) {
            foreach ($row as $key => $value) {
                $fieldId = is_string($key)
                    ? $this->fieldIdByName[$key]
                    : $key + 1;

                $row[$key] = $this->normalizeValue(
                    $value,
                    $fieldId !== null
                        ? ($this->fieldTypes[$fieldId] ?? "")
                        : ""
                );
            }
        }

        return $rows;
    }
}
