<?php declare(strict_types = 1);

namespace idoit\Module\SyneticsFlows\Model;

use DateTime;
use idoit\Model\Dao\Base;
use idoit\Module\SyneticsFlows\Automation\Automation;
use idoit\Module\SyneticsFlows\Automation\Execution;
use idoit\Module\SyneticsFlows\Controller\SearchParams;
use idoit\Module\SyneticsFlows\Serialization\Serializer;
use isys_format_json;

class AutomationDao extends Base
{
    public const AUTOMATION_FIELDS = [
        'id' => 'id',
        'status' => 'status',
        'title' => 'name',
        'description' => 'description',
        'automation_trigger' => 'trigger',
        'automation_condition' => 'condition',
        'automation_actions' => 'actions',
        'created_by' => 'createdBy'
    ];

    public const SORTING_MAP = [
        'id' => 'id',
        'name' => 'title',
        'description' => 'description',
        'status' => 'status',
    ];

    public function getData($condition = null, ?SearchParams $params = null): array
    {
        $result = [];
        $mapping = self::AUTOMATION_FIELDS;
        array_walk($mapping, function (&$item, $key) {
            $item = 'automation.' . $key . ' AS ' . $this->convert_sql_text($item);
        });
        $query = 'SELECT ' . implode(', ', $mapping)
            . ", JSON_OBJECT(
               'id', e.id,
               'flowId', e.isys_flow_execution__isys_flow_automation__id,
               'time', e.time,
               'flowTitle', automation.title,
               'started', e.started,
               'status', e.status,
               'finished', e.finished,
               'execution', e.execution_execution,
               'result', e.execution_result
            ) as execution"
            . ' FROM isys_flow_automation automation'
            . ' LEFT JOIN
                    (SELECT execution.*
                     FROM (
                          SELECT max(e.id) as id, e.isys_flow_execution__isys_flow_automation__id
                          FROM isys_flow_execution e
                          GROUP BY e.isys_flow_execution__isys_flow_automation__id
                          ) last_execution
                     LEFT JOIN isys_flow_execution execution ON execution.id = last_execution.id
                     ) e
                ON e.isys_flow_execution__isys_flow_automation__id = automation.id'
            . ' WHERE TRUE ';
        if ($condition) {
            $query .= $condition;
        }
        if ($params) {
            $sortId = $params->getSort()?->getId() ?? null;
            if ($sortId && isset(self::SORTING_MAP[$sortId])) {
                $direction = $params->getSort()?->isDesc() ? 'desc' : 'asc';
                $query .= ' ORDER BY ' . self::SORTING_MAP[$sortId] . ' ' . $direction;
            }
            $query .= " LIMIT {$params->getPerPage()} OFFSET {$params->getOffset()}";
        }
        $daoResult = $this->retrieve($query . ';');
        while ($row = $daoResult->get_row()) {
            $data = [];
            foreach (self::AUTOMATION_FIELDS as $field) {
                $data[$field] = match ($field) {
                    'trigger', 'condition', 'actions' => json_decode($row[$field], true),
                    default => $row[$field],
                };
            }

            /** @var Automation $automation */
            $automation = Serializer::fromJson(Automation::class, $data);
            $execution = json_decode($row['execution'], true);
            if (!empty($execution['id'])) {
                $automation->setExecution(Serializer::fromJson(Execution::class, $execution));
            }
            $result[] = $automation;
        }

        return $result;
    }

    public function getCount($condition = null): int
    {
        $daoResult = $this->retrieve('SELECT count(1) as count FROM isys_flow_automation WHERE TRUE ' . ($condition ?: '') . ';');
        $result = $daoResult->get_row_value('count');
        if ($result === null) {
            return 0;
        }
        return intval($result);
    }

    public function get(string $id): ?Automation
    {
        return $this->getData(' AND automation.id = ' . $this->convert_sql_id($id))[0] ?? null;
    }

    /**
     * @param array $ids
     *
     * @return array
     */
    public function find(array $ids): array
    {
        if (!count($ids)) {
            return [];
        }
        $ids = array_map(fn(int|string $id) => $this->convert_sql_int($id), $ids);

        return $this->getData("AND automation.id IN ('" . implode("','", $ids) . "')");
    }

    public function save(Automation $automation, int $userId, DateTime $dateTime): ?string
    {
        $formattedTime = $dateTime->format(DateTime::ATOM);
        $params = [
            'updated = ' . $this->convert_sql_text($formattedTime),
            'updated_by = ' . $this->convert_sql_text($userId),
        ];

        $current = $this->get($automation->getId());
        if ($current) {
            $sql = 'UPDATE isys_flow_automation SET %s WHERE id = ' . $this->convert_sql_id($automation->getId()) . ';';
        } else {
            $sql = 'INSERT INTO isys_flow_automation SET %s;';
            $params[] = 'created = ' . $this->convert_sql_text($formattedTime);
            $params[] = 'created_by = ' . $this->convert_sql_text($userId);
        }
        $data = $automation->jsonSerialize();

        foreach (self::AUTOMATION_FIELDS as $dbField => $field) {
            switch ($field) {
                case 'name':
                case 'status':
                case 'description':
                    $params[] = $dbField . ' = ' . $this->convert_sql_text($data[$field]);
                    break;
                case 'trigger':
                case 'condition':
                case 'actions':
                    $params[] = $dbField . ' = ' . $this->convert_sql_text(isys_format_json::encode($data[$field]));
                    break;
            }
        }

        if (!empty($params) && $this->update(str_replace('%s', implode(',', $params), $sql)) && $this->apply_update()) {
            return strval($this->get_last_insert_id());
        }

        return $automation->getId();
    }

    public function remove(string $id): bool
    {
        return $this->update('DELETE FROM isys_flow_automation WHERE id = ' . $this->convert_sql_text($id))
            && $this->apply_update();
    }
}
