<?php

namespace idoit\Module\SyneticsJdisc\Model;

use Idoit\Dto\Serialization\Serializer;
use idoit\Module\SyneticsJdisc\Controller\Table\SearchParams;
use idoit\Module\SyneticsJdisc\Model\Dto\DiscoveryServer;
use isys_helper_crypt;
use isys_jdisc_dao;
use isys_module_dao;

class JDiscServerDao extends AbstractDao
{
    public const SERVER_FIELDS = [
        'isys_jdisc_db__id' => 'id',
        'isys_jdisc_db__title' => 'name',
        'isys_jdisc_db__host' => 'host',
        'isys_jdisc_db__port' => 'port',
        'isys_jdisc_db__database' => 'database',
        'isys_jdisc_db__username' => 'username',
        'isys_jdisc_db__password' => 'password',
        'isys_jdisc_db__tls_certificate' => 'tls_certificate',
        'isys_jdisc_db__version_check' => 'version_check',
        'isys_jdisc_db__default_server' => 'default_server',
        'isys_jdisc_db__discovery_username' => 'discovery_username',
        'isys_jdisc_db__discovery_password' => 'discovery_password',
        'isys_jdisc_db__discovery_port' => 'discovery_port',
        'isys_jdisc_db__discovery_protocol' => 'discovery_protocol',
        'isys_jdisc_db__discovery_tls_certificate' => 'discovery_tls_certificate',
        'isys_jdisc_db__discovery_timeout' => 'discovery_timeout',
        'isys_jdisc_db__discovery_import_retries' => 'discovery_import_retries',
    ];

    public const SORTING_MAP = [
        'id'       => 'isys_jdisc_db__id',
        'name'     => 'isys_jdisc_db__title',
        'host'     => 'isys_jdisc_db__host',
        'database' => 'isys_jdisc_db__database',
    ];

    /**
     * @param string|null       $condition
     * @param SearchParams|null $searchParams
     *
     * @return DiscoveryServer[]
     * @throws \isys_exception_database
     */
    public function getData(?string $condition = null, ?SearchParams $searchParams = null): array
    {
        $result = [];
        $mapping = self::SERVER_FIELDS;
        array_walk($mapping, function (&$item, $key) {
            $item = $key . ' AS ' . $this->convert_sql_text($item);
        });

        $query = 'SELECT ' . implode(',', $mapping) . ' FROM isys_jdisc_db WHERE TRUE ';
        if (null !== $condition) {
            $query .= ' AND ' . $condition;
        }

        if ($searchParams) {
            if ($searchParams->getSearchTerm()) {
                $searchTerm = $this->convert_sql_text('%' . $searchParams->getSearchTerm() . '%');
                $query .= " AND (isys_jdisc_db__title LIKE {$searchTerm} OR isys_jdisc_db__host LIKE {$searchTerm} OR isys_jdisc_db__database LIKE {$searchTerm})";
            }

            $sortId = $searchParams->getSort()?->getId() ?? null;
            if ($sortId && isset(self::SORTING_MAP[$sortId])) {
                $direction = $searchParams->getSort()?->isDesc() ? 'desc' : 'asc';
                $query .= ' ORDER BY ' . self::SORTING_MAP[$sortId] . ' ' . $direction;
            }
            $query .= " LIMIT {$searchParams->getPerPage()} OFFSET {$searchParams->getOffset()}";
        }

        $properties = $this->getProperties();
        $daoResult  = $this->retrieve($query . ';');
        while ($row = $daoResult->get_row()) {
            $data = [];
            foreach (self::SERVER_FIELDS as $field) {
                if (
                    !empty($properties[$field][C__PROPERTY__DATA]['crypt'])
                    && $properties[$field][C__PROPERTY__DATA]['crypt'] === true
                ) {
                    $row[$field] = isys_helper_crypt::decrypt($row[$field]);
                }
                $data[$field] = $row[$field];
            }
            $result[] = Serializer::fromJson(DiscoveryServer::class, $data);
        }

        return $result;
    }

    /**
     * @param string|null $condition
     *
     * @return int
     * @throws \isys_exception_database
     */
    public function getCount(?string $condition = null): int
    {
        $daoResult = $this->retrieve('SELECT count(1) as count FROM isys_jdisc_db WHERE TRUE ' . ($condition ?: '') . ';');
        $result = $daoResult->get_row_value('count');
        if ($result === null) {
            return 0;
        }
        return intval($result);
    }

    /**
     * @param array|int|null $id
     *
     * @return \isys_component_dao_result
     * @throws \isys_exception_database
     */
    public function getServerList(mixed $id = null, ?array $conditions = [])
    {
        $query = "SELECT isys_jdisc_db__id, isys_jdisc_db__title, isys_jdisc_db__host, isys_jdisc_db__port, isys_jdisc_db__database,
			isys_jdisc_db__username, isys_jdisc_db__password, isys_jdisc_db__version_check,
			isys_jdisc_db__discovery_username, isys_jdisc_db__discovery_password, isys_jdisc_db__discovery_port,
			isys_jdisc_db__discovery_protocol, isys_jdisc_db__default_server, isys_jdisc_db__discovery_timeout,
			isys_jdisc_db__discovery_import_retries FROM isys_jdisc_db WHERE TRUE";

        $query .= match (gettype($id)) {
            'int' => " AND isys_jdisc_db__id = {$this->convert_sql_id($id)}",
            'array' => " AND isys_jdisc_db__id {$this->prepare_in_condition($id)}",
            default => ''
        };

        if (!empty($conditions)) {
            $query .= ' AND ' . implode(' AND ', $conditions);
        }

        return $this->retrieve($query . ';');
    }

    public function getProperties(): array
    {
        return $this->getJdiscDao()->get_properties(isys_jdisc_dao::C__CONFIGURATION);
    }

    public function delete(array $ids)
    {
        $ids = array_filter($ids);
        if (empty($ids)) {
            return;
        }

        $ids = array_map([$this, 'convert_sql_int'], $ids);
        $query = 'DELETE FROM isys_jdisc_db WHERE isys_jdisc_db__id IN (' . implode(',', $ids) . ')';

        return $this->update($query) && $this->apply_update();
    }

    /**
     * @param int|null $id
     * @param array    $data
     *
     * @return bool
     * @throws \isys_exception_dao
     * @throws \isys_exception_general
     */
    public function save(?int $id, array $data)
    {
        // Profile:
        $properties = $this->getProperties();
        $saveData = [];

        foreach ($properties as $propertyKey => $propertyInfo) {
            if ($propertyKey === 'id' && ((isset($data['id']) && $data['id'] < 1) || !isset($data[$propertyKey]['id']))) {
                continue;
            }

            // Save property only if create and save are provided:
            if (array_key_exists(C__PROPERTY__PROVIDES, $propertyInfo)) {
                if ((isys_module_dao::C__PROPERTY__PROVIDES__CREATE & $propertyInfo[C__PROPERTY__PROVIDES]) ||
                    (isys_module_dao::C__PROPERTY__PROVIDES__SAVE & $propertyInfo[C__PROPERTY__PROVIDES])) {
                    $saveData[$propertyKey] = $data[$propertyKey];
                }
            }
        }

        if ($id !== null) {
            $saveData['id'] = $id;
        }

        return (bool)$this->getJdiscDao()->save(isys_jdisc_dao::C__CONFIGURATION, $saveData);

    }

    /**
     * @param int $id
     *
     * @return mixed|null
     */
    public function getConfiguration(int $id)
    {
        $server = end($this->getJdiscDao()->get_configuration(null, ['id' => $id]));
        if ($server === false) {
            return null;
        }
        return $server;
    }

    /**
     * @param int $id
     *
     * @return bool
     */
    public function isConnected(int $id)
    {
        return $this->getJdiscDao()->is_connected($id);
    }

    /**
     * @return array
     * @throws \idoit\Exception\JsonException
     */
    public function prepareData()
    {
        $data = $this->getJdiscDao()->transformDataByProperties($this->getProperties(), $_POST);
        return $data;
    }

    /**
     * @param int $serverId
     *
     * @return bool
     */
    public function isDiscoverySettingsActive(int $serverId): bool
    {
        return $this->getJdiscModule()->web_service_active($serverId) || $this->getJdiscModule()->isGraphqlAvailable($serverId);
    }

    /**
     * @param string|null $searchTerm
     * @param array|null  $ids
     * @return array
     */
    public function getFiles(?string $searchTerm = null, ?array $ids = null): array
    {
        $files = [];
        $subQuery = '';
        $recordStatusNormal = $this->convert_sql_int(C__RECORD_STATUS__NORMAL);

        if ($searchTerm !== null) {
            $subQuery .= " AND fileObject.isys_obj__title LIKE {$this->convert_sql_text('%' . $searchTerm . '%')}";
        }

        if ($ids !== null && count($ids) > 0) {
            $subQuery .= ' AND fileObject.isys_obj__id IN (' . implode(',', array_map([$this, 'convert_sql_int'], $ids)) . ')';
        }

        $query = <<<SQL
            SELECT
                fileObject.isys_obj__id,
                fileObject.isys_obj__title
            FROM isys_cats_file_list
            INNER JOIN isys_file_version AS version ON version.isys_file_version__id = isys_cats_file_list__isys_file_version__id
            INNER JOIN isys_obj AS fileObject ON isys_cats_file_list__isys_obj__id = fileObject.isys_obj__id
            WHERE
                isys_cats_file_list__status = {$recordStatusNormal}
                AND fileObject.isys_obj__status = {$recordStatusNormal}
                {$subQuery}
            GROUP BY isys_cats_file_list__id
            ORDER BY fileObject.isys_obj__title ASC
            LIMIT 100
        SQL;

        $result = $this->retrieve($query);
        while ($row = $result->get_row()) {
            $files[] = [
                'id'    => (int)$row['isys_obj__id'],
                'title' => $row['isys_obj__title'],
            ];
        }
        return $files;
    }
}
