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

<?php
declare(strict_types=1);

class TaskDefinitionRepository
{
    public function create(array $data): int
    {
        $sql = 'INSERT INTO task_definition (
            title, description, task_category_id, frequency_type, recurrence_basis,
            recurrence_unit, recurrence_interval, start_date, deadline_rule_type,
            deadline_offset_days, deadline_weekday, deadline_monthday,
            fulfillment_mode, certificate_enabled, certificate_title, certificate_validity_mode, certificate_validity_days,
            show_cycle_period, active, created_by, modified_by
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

        return TaskCertDb::insert($sql, 'ssisssissiiisissiiiii', [
            $data['title'],
            $data['description'],
            $data['task_category_id'],
            $data['frequency_type'],
            $data['recurrence_basis'],
            $data['recurrence_unit'],
            $data['recurrence_interval'],
            $data['start_date'],
            $data['deadline_rule_type'],
            $data['deadline_offset_days'],
            $data['deadline_weekday'],
            $data['deadline_monthday'],
            $data['fulfillment_mode'],
            $data['certificate_enabled'],
            $data['certificate_title'],
            $data['certificate_validity_mode'],
            $data['certificate_validity_days'],
            $data['show_cycle_period'],
            $data['active'],
            $data['created_by'],
            $data['modified_by'],
        ]);
    }

    public function update(int $taskId, array $data): void
    {
        $sql = 'UPDATE task_definition SET
            title = ?,
            description = ?,
            task_category_id = ?,
            frequency_type = ?,
            recurrence_basis = ?,
            recurrence_unit = ?,
            recurrence_interval = ?,
            start_date = ?,
            deadline_rule_type = ?,
            deadline_offset_days = ?,
            deadline_weekday = ?,
            deadline_monthday = ?,
            fulfillment_mode = ?,
            certificate_enabled = ?,
            certificate_title = ?,
            certificate_validity_mode = ?,
            certificate_validity_days = ?,
            show_cycle_period = ?,
            active = ?,
            modified_by = ?
            WHERE id = ?';

        TaskCertDb::execute($sql, 'ssisssissiiisissiiiii', [
            $data['title'],
            $data['description'],
            $data['task_category_id'],
            $data['frequency_type'],
            $data['recurrence_basis'],
            $data['recurrence_unit'],
            $data['recurrence_interval'],
            $data['start_date'],
            $data['deadline_rule_type'],
            $data['deadline_offset_days'],
            $data['deadline_weekday'],
            $data['deadline_monthday'],
            $data['fulfillment_mode'],
            $data['certificate_enabled'],
            $data['certificate_title'],
            $data['certificate_validity_mode'],
            $data['certificate_validity_days'],
            $data['show_cycle_period'],
            $data['active'],
            $data['modified_by'],
            $taskId,
        ]);
    }

    public function softDelete(int $taskId, int $modifiedBy): void
    {
        TaskCertDb::execute(
            'UPDATE task_definition SET active = 0, modified_by = ? WHERE id = ?',
            'ii',
            [$modifiedBy, $taskId]
        );
    }

    public function hardDelete(int $taskId): int
    {
        return TaskCertDb::execute(
            'DELETE FROM task_definition WHERE id = ?',
            'i',
            [$taskId]
        );
    }

    public function getHardDeleteImpact(int $taskId): array
    {
        $assignmentCount = (int) TaskCertDb::scalar(
            'SELECT COUNT(*)
             FROM task_assignment
             WHERE task_id = ?',
            'i',
            [$taskId]
        );

        $submissionCount = (int) TaskCertDb::scalar(
            'SELECT COUNT(*)
             FROM task_submission s
             INNER JOIN task_assignment a ON a.id = s.assignment_id
             WHERE a.task_id = ?',
            'i',
            [$taskId]
        );

        $certificateCount = (int) TaskCertDb::scalar(
            'SELECT COUNT(*)
             FROM task_certificate
             WHERE task_id = ?',
            'i',
            [$taskId]
        );

        $methodCount = (int) TaskCertDb::scalar(
            'SELECT COUNT(*)
             FROM task_method
             WHERE task_id = ?',
            'i',
            [$taskId]
        );

        $escalationEventCount = (int) TaskCertDb::scalar(
            'SELECT COUNT(*)
             FROM task_escalation_event e
             INNER JOIN task_assignment a ON a.id = e.assignment_id
             WHERE a.task_id = ?',
            'i',
            [$taskId]
        );

        return [
            'assignments' => $assignmentCount,
            'submissions' => $submissionCount,
            'certificates' => $certificateCount,
            'methods' => $methodCount,
            'escalation_events' => $escalationEventCount,
        ];
    }

    public function findById(int $taskId): ?array
    {
        return TaskCertDb::fetchOne('SELECT * FROM task_definition WHERE id = ?', 'i', [$taskId]);
    }

    public function listForAdmin(string $searchQuery = '', int $categoryIdFilter = 0): array
    {
        $searchQuery = trim($searchQuery);
        $categoryIdFilter = max(0, $categoryIdFilter);

        $sql = 'SELECT
            d.*,
            MAX(tc.name) AS task_category_name,
            COUNT(a.id) AS assignment_count,
            SUM(CASE WHEN a.status IN ("open", "in_progress", "approval_pending", "overdue") THEN 1 ELSE 0 END) AS open_count,
            SUM(CASE WHEN a.status = "completed" THEN 1 ELSE 0 END) AS completed_count
        FROM task_definition d
        LEFT JOIN task_category tc ON tc.id = d.task_category_id
        LEFT JOIN task_assignment a ON a.task_id = d.id';

        $conditions = [];
        $types = '';
        $params = [];

        if ($categoryIdFilter > 0) {
            $conditions[] = 'd.task_category_id = ?';
            $types .= 'i';
            $params[] = $categoryIdFilter;
        }

        if ($searchQuery !== '') {
            $searchLike = '%' . $searchQuery . '%';
            $conditions[] = '(
                d.title LIKE ?
                OR d.description LIKE ?
                OR COALESCE(tc.name, "") LIKE ?
                OR CAST(d.id AS CHAR) LIKE ?
            )';
            $types .= 'ssss';
            $params[] = $searchLike;
            $params[] = $searchLike;
            $params[] = $searchLike;
            $params[] = $searchLike;
        }

        if ($conditions !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $conditions);
        }

        $sql .= ' GROUP BY d.id
        ORDER BY d.active DESC, d.created_at DESC';

        return TaskCertDb::fetchAll($sql, $types, $params);
    }

    public function listForResponsible(int $contactId): array
    {
        if ($contactId <= 0) {
            return [];
        }

        $sql = 'SELECT
            d.*,
            MAX(tc.name) AS task_category_name,
            COUNT(a.id) AS assignment_count,
            SUM(CASE WHEN a.status IN ("open", "in_progress", "approval_pending", "overdue") THEN 1 ELSE 0 END) AS open_count,
            SUM(CASE WHEN a.status = "completed" THEN 1 ELSE 0 END) AS completed_count
        FROM task_definition d
        INNER JOIN task_responsible tr ON tr.task_id = d.id AND tr.main_contact_id = ?
        LEFT JOIN task_category tc ON tc.id = d.task_category_id
        LEFT JOIN task_assignment a ON a.task_id = d.id
        GROUP BY d.id
        ORDER BY d.active DESC, d.created_at DESC';

        return TaskCertDb::fetchAll($sql, 'i', [$contactId]);
    }

    public function listActive(): array
    {
        return TaskCertDb::fetchAll(
            'SELECT * FROM task_definition WHERE active = 1 ORDER BY id DESC'
        );
    }
}