<?php declare(strict_types = 1);

namespace idoit\Module\SyneticsFlows\Model;

use idoit\Component\Helper\ArrayHelper;
use idoit\Model\Dao\Base;
use idoit\Module\SyneticsFlows\Controller\SearchParams;
use idoit\Module\SyneticsFlows\Filter\CiObjectFilter;
use idoit\Module\SyneticsFlows\Model\Dto\CiObject;
use isys_application;
use isys_auth_cmdb_object_types;
use isys_auth_cmdb_objects;

class CiObjectDao extends Base
{
    public const SORTING_MAP = [
        'id' => 'id',
        'title' => 'title',
        'type' => 'type',
        'typeId' => 'type_id',
    ];

    private const HIDDEN_TYPES = ['C__OBJTYPE__RELATION'];

    private ?array $objectTypeTranslationMap = null;


    private function getObjectTypeMap(): array
    {
        if ($this->objectTypeTranslationMap === null) {
            $language = isys_application::instance()->container->get('language');
            $query = "SELECT isys_obj_type__title FROM isys_obj_type";
            $result = $this->retrieve($query);
            $this->objectTypeTranslationMap = [];
            while ($row = $result->get_row()) {
                $title = $row['isys_obj_type__title'];
                $this->objectTypeTranslationMap[$title] = $language->get($title);
            }
        }

        return $this->objectTypeTranslationMap;
    }

    private function prepareObjectFilter(?CiObjectFilter $filter): ?string
    {
        $conditions = [];
        if (!empty($filter->getTypes())) {
            $types = array_map(function ($typeId) {
                $id = $typeId;
                if (defined($id)) {
                    $id = constant($typeId);
                }
                    return $this->convert_sql_int($id);

            }, $filter->getTypes());
            $typesString = implode(', ', $types);
            $conditions[] = "isys_obj_type__id IN ($typesString) ";
        }

        if (!empty($filter->getIds())) {
            $ids = array_map(fn (int $id) => $this->convert_sql_int($id), $filter->getIds());
            $idsString = implode(', ', $ids);
            $conditions[] = "isys_obj__id IN ($idsString)";
        }

        if (!empty($filter->getTitle())) {
            $conditions[] = "isys_obj__title LIKE '%{$filter->getTitle()}%' ";
        }

        if (!empty($filter->getStatuses())) {
            $statuses = array_map(fn (string $id) => $this->convert_sql_int($id), $filter->getStatuses());
            $statusesString = implode(', ', $statuses);
            $conditions[] = "isys_obj__status IN ($statusesString)";
        }

        if (empty($conditions)) {
            return null;
        }
        return implode(' AND ', $conditions);
    }

    private function prepareObjectFilterGroup(?CiObjectFilter $filter): ?string
    {
        $conditions = [];

        if (!empty($filter->getTitle())) {
            $conditions[] = " isys_obj__title LIKE '%{$filter->getTitle()}%' ";
        }
        if (empty($conditions)) {
            return null;
        }
        return implode(' AND ', $conditions);
    }

    private function prepareSelectObjects(?string $condition = null): string
    {
        $authObjectCondition = isys_auth_cmdb_objects::instance()->get_allowed_objects_condition();
        $allowedObjTypes = isys_auth_cmdb_object_types::instance()->get_allowed_objecttypes();
        $authObjectTypeCondition = '';

        if (!empty($allowedObjTypes)) {
            $allowedObjTypes = array_map('intval', $allowedObjTypes);
            $authObjectTypeCondition = ' AND isys_obj_type__id IN (' . implode(', ', $allowedObjTypes) . ')';
        }

        $objTypes = $this->getObjectTypeMap();
        $objTypeCases = array_map(fn(string $title, string $label): string =>
        "WHEN isys_obj_type__title = {$this->convert_sql_text($title)} THEN {$this->convert_sql_text($label)}",
            array_keys($objTypes),
            $objTypes,
        );
        $caseSelection = sprintf("CASE %s END as type", implode(' ', $objTypeCases));

        $extraCondition = $condition ?? '1 = 1';

        $hiddenTypesCondition = sprintf("AND isys_obj_type__const NOT IN ('%s')", implode("','", self::HIDDEN_TYPES));

        return "SELECT isys_obj__id as id, isys_obj__title as title, isys_obj__sysid as sysid,
                isys_obj_type__id as type_id, {$caseSelection}, isys_obj__status as status
            FROM isys_obj
            INNER JOIN isys_obj_type ON isys_obj__isys_obj_type__id = isys_obj_type__id
            WHERE {$extraCondition} {$authObjectCondition} {$authObjectTypeCondition} {$hiddenTypesCondition}";
    }

    /**
     * @param CiObjectFilter|null $objectFilter
     * @param SearchParams|null $params
     *
     * @return array
     */
    public function getData(?CiObjectFilter $objectFilter = null, ?SearchParams $params = null): array
    {
        $query = $this->prepareSelectObjects($this->prepareObjectFilter($objectFilter));
        $limit = $params?->getPerPage() ?? 100;
        $page = $params?->getPage() ?? 0;
        $sort = $params?->getSort();
        $offset = $page * $limit;
        if ($sort && isset(self::SORTING_MAP[$sort->getId()])) {
            $direction = $sort->isDesc() ? 'DESC' : 'ASC';
            $query .= " ORDER BY {$sort->getId()} $direction";
        }
        $query .= " LIMIT $limit OFFSET $offset";

        $result = $this->retrieve($query);

        $objects = [];
        while ($row = $result->get_row()) {
            $objects[] = new CiObject(
                $row['id'],
                $row['title'],
                $row['type'],
                $row['type_id'],
                $row['status'],
                $row['sysid'] ?? null
            );
        }
        return $objects;
    }

    /**
     * @param string $id
     *
     * @return CiObject|null
     */
    public function get(string $id): ?CiObject
    {
        $query = $this->prepareSelectObjects('isys_obj__id = ' . $this->convert_sql_text($id));
        $result = $this->retrieve($query)->get_row();
        if (!$result) {
            return null;
        }
        return new CiObject(
            $result['id'],
            $result['title'],
            $result['type'],
            $result['type_id'],
            $result['status'],
            $result['sysid'] ?? null
        );
    }

    /**
     * @param CiObjectFilter|null $objectFilter
     *
     * @return int
     */
    public function getCount(?CiObjectFilter $objectFilter = null): int
    {
        $select = $this->prepareSelectObjects($this->prepareObjectFilter($objectFilter));
        $result = $this->retrieve("SELECT COUNT(1) as count from ($select) cnt");
        return (int)$result->get_row_value('count');
    }

    /**
     * @param string $field
     * @param CiObjectFilter|null $objectFilter
     *
     * @return array|null
     * @throws \isys_exception_database
     */

    public function getGroupsAll(string $field, ?CiObjectFilter $objectFilter = null): ?array
    {
        if (!isset(self::SORTING_MAP[$field])) {
            return null;
        }
        $groupField = self::SORTING_MAP[$field];
        $select = $this->prepareSelectObjects($this->prepareObjectFilterGroup($objectFilter));
        $result = $this->retrieve("SELECT object.{$groupField}, object.type, object.type_id, count(1) as count from ($select) object group by object.{$groupField}, object.type, object.type_id");

        $groups = [];
        $totalCount = 0;
        while ($row = $result->get_row()) {
            $count = (int)$row['count'];
            $totalCount += $count;
            $groups[$row[$groupField]] = [
                'count' => $count,
                'type' => $row['type'],
                'typeId' => $row['type_id'],
            ];
        }

        return [
            'results' => $groups,
            'count' => $totalCount,
        ];
    }

    /**
     * @param string $field
     * @param CiObjectFilter|null $objectFilter
     *
     * @return array|null
     * @throws \isys_exception_database
     */
    public function getGroups(string $field, ?CiObjectFilter $objectFilter = null): ?array
    {
        if (!isset(self::SORTING_MAP[$field])) {
            return null;
        }
        $groupField = self::SORTING_MAP[$field];
        $select = $this->prepareSelectObjects($this->prepareObjectFilter($objectFilter));
        $result = $this->retrieve("SELECT object.{$groupField}, object.type, object.type_id, count(1) as count from ($select) object group by object.{$groupField}, object.type, object.type_id");

        $groups = [];
        $totalCount = 0;
        while ($row = $result->get_row()) {
            $count = (int)$row['count'];
            $totalCount += $count;
            $groups[$row[$groupField]] = [
                'count' =>$count,
                'type' => $row['type'],
                'typeId' => $row['type_id'],
            ];
        }
        return [
            'results' => $groups,
            'count' => $totalCount,
        ];
    }

    /**
     * @param string $condition
     *
     * @return array
     */
    public function getObjectsIdsByCondition(string $condition): array
    {
        $query = 'SELECT isys_obj__id as id FROM isys_obj WHERE TRUE';
        if ($condition) {
            $query .= " AND $condition";
        }

        $result = $this->retrieve($query);
        $ids = [];

        while ($row = $result->get_row()) {
            $ids[] = (int) $row['id'];
        }

        return $ids;
    }
}
