awo-hamburg-intranet/module/tasks/repo/TaskRuleRepository.php

<?php
declare(strict_types=1);

class TaskRuleRepository
{
    public function replaceTaskRules(int $taskId, array $dto, int $actorId): void
    {
        $this->replaceMethods($taskId, $dto['methods'] ?? []);
        $this->replaceTargetRules($taskId, $dto['target_rules'] ?? []);
        $this->replaceTargetOverrides($taskId, $dto['target_overrides'] ?? [], $actorId);
        $this->replaceReminderPolicy($taskId, $dto['reminder_policy'] ?? []);
        $this->replaceResponsibles($taskId, $dto['responsible_contact_ids'] ?? []);
    }

    public function getTaskRuleBundle(int $taskId): array
    {
        return [
            'methods' => $this->getMethodsByTask($taskId, true),
            'target_rules' => $this->getTargetRulesByTask($taskId),
            'target_overrides' => $this->getTargetOverridesByTask($taskId),
            'reminder_policy' => $this->getReminderPolicyByTask($taskId),
            'responsible_contact_ids' => $this->getResponsibleContactIds($taskId),
        ];
    }

    public function findMethodById(int $methodId): ?array
    {
        return TaskCertDb::fetchOne('SELECT * FROM task_method WHERE id = ? AND active = 1', 'i', [$methodId]);
    }

    public function getMethodsByTask(int $taskId, bool $includeInactive = false): array
    {
        $sql = 'SELECT * FROM task_method WHERE task_id = ?';
        if (!$includeInactive) {
            $sql .= ' AND active = 1';
        }
        $sql .= ' ORDER BY sort_order ASC, id ASC';

        $methods = TaskCertDb::fetchAll($sql, 'i', [$taskId]);

        foreach ($methods as &$method) {
            $methodId = (int) $method['id'];
            $method['settings'] = $this->decodeJson($method['settings_json'] ?? null);
            $method['knowledge_posts'] = $this->getKnowledgePostsForMethod($methodId);
            $method['form_fields'] = $this->getFormFieldsForMethod($methodId);
            $method['quiz_questions'] = $this->getQuizQuestionsWithOptions($methodId);
            unset($method['settings_json']);
        }

        return $methods;
    }

    public function getTargetRulesByTask(int $taskId): array
    {
        return TaskCertDb::fetchAll(
            'SELECT * FROM task_target_rule WHERE task_id = ? ORDER BY id ASC',
            'i',
            [$taskId]
        );
    }

    public function getTargetOverridesByTask(int $taskId): array
    {
        return TaskCertDb::fetchAll(
            'SELECT * FROM task_target_override WHERE task_id = ? ORDER BY id ASC',
            'i',
            [$taskId]
        );
    }

    public function getReminderPolicyByTask(int $taskId): ?array
    {
        return TaskCertDb::fetchOne(
            'SELECT * FROM task_reminder_policy WHERE task_id = ? LIMIT 1',
            'i',
            [$taskId]
        );
    }

    private function replaceMethods(int $taskId, array $methods): void
    {
        $existingRows = TaskCertDb::fetchAll(
            'SELECT id, method_type FROM task_method WHERE task_id = ?',
            'i',
            [$taskId]
        );
        $existingByType = [];
        foreach ($existingRows as $row) {
            $type = (string) ($row['method_type'] ?? '');
            $id = (int) ($row['id'] ?? 0);
            if ($type !== '' && $id > 0) {
                $existingByType[$type] = $id;
            }
        }

        $sort = 0;
        $seenTypes = [];
        foreach ($methods as $method) {
            $methodType = (string) ($method['method_type'] ?? '');
            if (!TaskCertConstants::isValidMethodType($methodType)) {
                continue;
            }

            $isRequired = (int) ($method['is_required'] ?? 1);
            $sortOrder = (int) ($method['sort_order'] ?? $sort);
            $settingsJson = json_encode($method['settings'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            $active = (int) ($method['active'] ?? 1);
            $seenTypes[$methodType] = true;

            if (isset($existingByType[$methodType])) {
                $methodId = (int) $existingByType[$methodType];
                TaskCertDb::execute(
                    'UPDATE task_method
                     SET is_required = ?, sort_order = ?, settings_json = ?, active = ?
                     WHERE id = ?',
                    'iisii',
                    [$isRequired, $sortOrder, $settingsJson, $active, $methodId]
                );
            } else {
                $methodId = TaskCertDb::insert(
                    'INSERT INTO task_method (task_id, method_type, is_required, sort_order, settings_json, active)
                     VALUES (?, ?, ?, ?, ?, ?)',
                    'isiisi',
                    [$taskId, $methodType, $isRequired, $sortOrder, $settingsJson, $active]
                );
            }

            $this->clearMethodChildren($methodId);

            if ($methodType === 'knowledge_read') {
                $this->insertKnowledgePosts($methodId, $method['knowledge_posts'] ?? []);
            }

            if ($methodType === 'form_submit') {
                $this->insertFormFields($methodId, $method['form_fields'] ?? []);
            }

            if ($methodType === 'quiz') {
                $this->insertQuizQuestions($methodId, $method['quiz_questions'] ?? []);
            }

            $sort++;
        }

        foreach ($existingByType as $methodType => $methodId) {
            if (isset($seenTypes[$methodType])) {
                continue;
            }

            TaskCertDb::execute(
                'UPDATE task_method
                 SET active = 0, sort_order = ?
                 WHERE id = ?',
                'ii',
                [$sort, (int) $methodId]
            );
            $sort++;
        }
    }

    private function clearMethodChildren(int $methodId): void
    {
        TaskCertDb::execute('DELETE FROM task_method_knowledge_post WHERE method_id = ?', 'i', [$methodId]);
        TaskCertDb::execute('DELETE FROM task_form_field WHERE method_id = ?', 'i', [$methodId]);

        $questionRows = TaskCertDb::fetchAll(
            'SELECT id FROM task_quiz_question WHERE method_id = ?',
            'i',
            [$methodId]
        );
        $questionIds = [];
        foreach ($questionRows as $row) {
            $questionId = (int) ($row['id'] ?? 0);
            if ($questionId > 0) {
                $questionIds[] = $questionId;
            }
        }

        if ($questionIds !== []) {
            $placeholders = implode(',', array_fill(0, count($questionIds), '?'));
            TaskCertDb::execute(
                'DELETE FROM task_quiz_option WHERE question_id IN (' . $placeholders . ')',
                str_repeat('i', count($questionIds)),
                $questionIds
            );
        }

        TaskCertDb::execute('DELETE FROM task_quiz_question WHERE method_id = ?', 'i', [$methodId]);
    }

    private function replaceTargetRules(int $taskId, array $targetRules): void
    {
        TaskCertDb::execute('DELETE FROM task_target_rule WHERE task_id = ?', 'i', [$taskId]);

        foreach ($targetRules as $rule) {
            TaskCertDb::insert(
                'INSERT INTO task_target_rule (task_id, main_mandant_id, main_department_id, main_role_id, active)
                 VALUES (?, ?, ?, ?, ?)',
                'iiiii',
                [
                    $taskId,
                    (int) ($rule['main_mandant_id'] ?? 0) ?: null,
                    (int) ($rule['main_department_id'] ?? 0) ?: null,
                    (int) ($rule['main_role_id'] ?? 0) ?: null,
                    (int) ($rule['active'] ?? 1),
                ]
            );
        }
    }

    private function replaceTargetOverrides(int $taskId, array $overrides, int $actorId): void
    {
        TaskCertDb::execute('DELETE FROM task_target_override WHERE task_id = ?', 'i', [$taskId]);

        foreach ($overrides as $override) {
            $type = (string) ($override['override_type'] ?? '');
            $contactId = (int) ($override['main_contact_id'] ?? 0);

            if (!in_array($type, ['include', 'exclude'], true) || $contactId <= 0) {
                continue;
            }

            TaskCertDb::insert(
                'INSERT INTO task_target_override (task_id, main_contact_id, override_type, reason, created_by)
                 VALUES (?, ?, ?, ?, ?)',
                'iissi',
                [
                    $taskId,
                    $contactId,
                    $type,
                    (string) ($override['reason'] ?? ''),
                    $actorId,
                ]
            );
        }
    }

    private function replaceReminderPolicy(int $taskId, array $policy): void
    {
        TaskCertDb::execute('DELETE FROM task_reminder_policy WHERE task_id = ?', 'i', [$taskId]);

        TaskCertDb::insert(
            'INSERT INTO task_reminder_policy (task_id, base_mode, first_escalation_days, step_days, max_level, grace_days, active)
             VALUES (?, ?, ?, ?, ?, ?, ?)',
            'isiiiii',
            [
                $taskId,
                'due_date',
                (int) ($policy['first_escalation_days'] ?? 0),
                max(1, (int) ($policy['step_days'] ?? 7)),
                max(1, (int) ($policy['max_level'] ?? 3)),
                max(0, (int) ($policy['grace_days'] ?? 7)),
                (int) ($policy['active'] ?? 1),
            ]
        );
    }

    private function insertKnowledgePosts(int $methodId, array $knowledgePosts): void
    {
        $sortOrder = 0;
        foreach ($knowledgePosts as $item) {
            $postId = is_array($item) ? (int) ($item['knowledge_post_id'] ?? 0) : (int) $item;
            if ($postId <= 0) {
                continue;
            }

            $required = is_array($item) ? (int) ($item['required'] ?? 1) : 1;
            $sort = is_array($item) ? (int) ($item['sort_order'] ?? $sortOrder) : $sortOrder;

            TaskCertDb::insert(
                'INSERT INTO task_method_knowledge_post (method_id, knowledge_post_id, required, sort_order)
                 VALUES (?, ?, ?, ?)',
                'iiii',
                [$methodId, $postId, $required, $sort]
            );

            $sortOrder++;
        }
    }

    private function insertFormFields(int $methodId, array $fields): void
    {
        $sortOrder = 0;
        foreach ($fields as $field) {
            if (!is_array($field)) {
                continue;
            }

            $fieldKey = trim((string) ($field['field_key'] ?? ''));
            $label = trim((string) ($field['label'] ?? ''));
            $fieldType = (string) ($field['field_type'] ?? 'text');

            if ($fieldKey === '' || $label === '') {
                continue;
            }

            if (!TaskCertConstants::isValidFieldType($fieldType)) {
                $fieldType = 'text';
            }

            $prefillColumn = ContactPrefillColumns::sanitize((string) ($field['prefill_column'] ?? ''));

            TaskCertDb::insert(
                'INSERT INTO task_form_field (method_id, field_key, label, field_type, is_required, options_json, placeholder, prefill_column, sort_order, active)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
                'isssisssii',
                [
                    $methodId,
                    $fieldKey,
                    $label,
                    $fieldType,
                    (int) ($field['is_required'] ?? 0),
                    json_encode($field['options'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    (string) ($field['placeholder'] ?? ''),
                    $prefillColumn,
                    (int) ($field['sort_order'] ?? $sortOrder),
                    (int) ($field['active'] ?? 1),
                ]
            );

            $sortOrder++;
        }
    }

    private function insertQuizQuestions(int $methodId, array $questions): void
    {
        $questionSort = 0;
        foreach ($questions as $question) {
            if (!is_array($question)) {
                continue;
            }

            $questionText = trim((string) ($question['question_text'] ?? ''));
            if ($questionText === '') {
                continue;
            }

            $questionId = TaskCertDb::insert(
                'INSERT INTO task_quiz_question (method_id, question_text, sort_order, active)
                 VALUES (?, ?, ?, ?)',
                'isii',
                [
                    $methodId,
                    $questionText,
                    (int) ($question['sort_order'] ?? $questionSort),
                    (int) ($question['active'] ?? 1),
                ]
            );

            $options = is_array($question['options'] ?? null) ? $question['options'] : [];
            $optionSort = 0;
            foreach ($options as $option) {
                if (!is_array($option)) {
                    continue;
                }

                $optionText = trim((string) ($option['option_text'] ?? ''));
                if ($optionText === '') {
                    continue;
                }

                TaskCertDb::insert(
                    'INSERT INTO task_quiz_option (question_id, option_text, is_correct, sort_order)
                     VALUES (?, ?, ?, ?)',
                    'isii',
                    [
                        $questionId,
                        $optionText,
                        (int) ($option['is_correct'] ?? 0),
                        (int) ($option['sort_order'] ?? $optionSort),
                    ]
                );

                $optionSort++;
            }

            $questionSort++;
        }
    }

    private function getKnowledgePostsForMethod(int $methodId): array
    {
        return TaskCertDb::fetchAll(
            'SELECT * FROM task_method_knowledge_post WHERE method_id = ? ORDER BY sort_order ASC, id ASC',
            'i',
            [$methodId]
        );
    }

    private function getFormFieldsForMethod(int $methodId): array
    {
        $rows = TaskCertDb::fetchAll(
            'SELECT * FROM task_form_field WHERE method_id = ? ORDER BY sort_order ASC, id ASC',
            'i',
            [$methodId]
        );

        foreach ($rows as &$row) {
            $row['options'] = $this->decodeJson($row['options_json'] ?? null);
            unset($row['options_json']);
        }

        return $rows;
    }

    private function getQuizQuestionsWithOptions(int $methodId): array
    {
        $questions = TaskCertDb::fetchAll(
            'SELECT * FROM task_quiz_question WHERE method_id = ? ORDER BY sort_order ASC, id ASC',
            'i',
            [$methodId]
        );

        foreach ($questions as &$question) {
            $question['options'] = TaskCertDb::fetchAll(
                'SELECT * FROM task_quiz_option WHERE question_id = ? ORDER BY sort_order ASC, id ASC',
                'i',
                [(int) $question['id']]
            );
        }

        return $questions;
    }

    private function decodeJson(?string $json): array
    {
        if ($json === null || trim($json) === '') {
            return [];
        }

        $decoded = json_decode($json, true);
        return is_array($decoded) ? $decoded : [];
    }

    public function getResponsibleContactIds(int $taskId): array
    {
        $rows = TaskCertDb::fetchAll(
            'SELECT main_contact_id FROM task_responsible WHERE task_id = ? ORDER BY id ASC',
            'i',
            [$taskId]
        );

        return array_map(static fn(array $row): int => (int) $row['main_contact_id'], $rows);
    }

    public function getResponsiblesWithContactData(int $taskId): array
    {
        return TaskCertDb::fetchAll(
            'SELECT r.main_contact_id, c.name, c.email
             FROM task_responsible r
             INNER JOIN main_contact c ON c.id = r.main_contact_id
             WHERE r.task_id = ?
             ORDER BY c.name ASC',
            'i',
            [$taskId]
        );
    }

    private function replaceResponsibles(int $taskId, array $contactIds): void
    {
        TaskCertDb::execute('DELETE FROM task_responsible WHERE task_id = ?', 'i', [$taskId]);

        $contactIds = array_values(array_unique(array_filter(array_map('intval', $contactIds), static fn(int $id): bool => $id > 0)));

        foreach ($contactIds as $contactId) {
            TaskCertDb::insert(
                'INSERT INTO task_responsible (task_id, main_contact_id) VALUES (?, ?)',
                'ii',
                [$taskId, $contactId]
            );
        }
    }
}