<?php

namespace Leantime\Domain\Tickets\Repositories;

use Carbon\CarbonImmutable;
use Illuminate\Contracts\Container\BindingResolutionException;
use Illuminate\Support\Facades\Cache;
use Leantime\Core\Db\Db as DbCore;
use Leantime\Core\Events\DispatchesEvents as EventhelperCore;
use Leantime\Core\Language as LanguageCore;
use Leantime\Domain\Users\Services\Users;
use PDO;

class Tickets
{
    use EventhelperCore;

    public ?object $result = null;

    public ?object $tickets = null;

    private DbCore $db;

    public array $statusClasses = ['3' => 'label-info', '1' => 'label-important', '4' => 'label-warning', '2' => 'label-warning', '0' => 'label-success', '-1' => 'label-default'];

    public array $statusListSeed = [
        3 => [
            'name' => 'status.new',
            'class' => 'label-info',
            'statusType' => 'NEW',
            'kanbanCol' => true,
            'sortKey' => 1,
        ],
        1 => [
            'name' => 'status.blocked',
            'class' => 'label-important',
            'statusType' => 'INPROGRESS',
            'kanbanCol' => true,
            'sortKey' => 2,
        ],
        4 => [
            'name' => 'status.in_progress',
            'class' => 'label-warning',
            'statusType' => 'INPROGRESS',
            'kanbanCol' => true,
            'sortKey' => 3,
        ],
        2 => [
            'name' => 'status.waiting_for_approval',
            'class' => 'label-warning',
            'statusType' => 'INPROGRESS',
            'kanbanCol' => true,
            'sortKey' => 4,
        ],
        0 => [
            'name' => 'status.done',
            'class' => 'label-success',
            'statusType' => 'DONE',
            'kanbanCol' => true,
            'sortKey' => 5,
        ],
        -1 => [
            'name' => 'status.archived',
            'class' => 'label-default',
            'statusType' => 'DONE',
            'kanbanCol' => false,
            'sortKey' => 6,
        ],
    ];

    public array $priority = ['1' => 'Critical', '2' => 'High', '3' => 'Medium', '4' => 'Low', '5' => 'Lowest'];

    public array $efforts = ['0.5' => '< 2min', '1' => 'XS', '2' => 'S', '3' => 'M', '5' => 'L', '8' => 'XL', '13' => 'XXL'];

    public array $type = ['task', 'subtask', 'story', 'bug'];

    public array $typeIcons = ['story' => 'fa-book', 'task' => 'fa-check-square', 'subtask' => 'fa-diagram-successor', 'bug' => 'fa-bug'];

    /**
     * @var bool
     */
    private int|bool $page = 0;

    /**
     * @var bool
     */
    public int|bool $rowsPerPage = 10;

    private string $limitSelect = '';

    public string $numPages = '';

    public string $sortBy = 'date';

    private LanguageCore $language;

    /**
     * __construct - get db connection
     *
     * @return void
     */
    public function __construct(DbCore $db, LanguageCore $language)
    {
        $this->db = $db;
        $this->language = $language;
    }

    /**
     * @return array|array[]
     */
    public function getStateLabels($projectId = null): array
    {
        if (Cache::has('projectsettings.'.$projectId.'.ticketlabels')) {
            return Cache::get('projectsettings.'.$projectId.'.ticketlabels');
        }

        if ($projectId == null) {
            $projectId = session('currentProject');
        }

        $sql = 'SELECT
						value
				FROM zp_settings WHERE `key` = :key
				LIMIT 1';

        $stmn = $this->db->database->prepare($sql);
        $stmn->bindvalue(':key', 'projectsettings.'.$projectId.'.ticketlabels', PDO::PARAM_STR);

        $stmn->execute();
        $values = $stmn->fetch();
        $stmn->closeCursor();

        $labels = [];

        $statusList = $this->statusListSeed;

        // Override the state values that are in the db
        if ($values !== false) {

            $statusList = [];

            // Archive is required and protected.
            // Adding the original version back in case folks removed it
            $statusList[-1] = $this->statusListSeed[-1];

            foreach (unserialize($values['value']) as $key => $status) {
                if (is_int($key)) {
                    // Backwards Compatibility with existing labels in db
                    // Prior to 2.1.9 labels were stored as <<statuskey>>:<<labelString>>
                    // Afterwards labelString was replaced with an array to include all different status attributes needed for custom status types
                    if (! is_array($status)) {
                        $statusList[$key] = $this->statusListSeed[$key];

                        if (is_array($statusList[$key]) && isset($statusList[$key]['name']) && $key !== -1) {
                            $statusList[$key]['name'] = $status;
                        }
                    } else {
                        $statusList[$key] = $status;
                    }
                }
            }
        } else {
            // If the values are not coming from the db, we need to translate the label strings
            foreach ($statusList as &$status) {
                $status['name'] = $this->language->__($status['name']);
            }
        }

        // Sort by order number
        uasort($statusList, function ($a, $b) {
            return $a['sortKey'] <=> $b['sortKey'];
        });

        Cache::put('projectsettings.'.$projectId.'.ticketlabels', $statusList, 3600);

        return $statusList;
    }

    public function getStatusList(): mixed
    {
        return $this->statusListSeed;
    }

    /**
     * @return string[]
     */
    public function getStatusListGroupedByType($projectId): array
    {

        // Ignoring status type NONE by design
        $statusByType = [
            'DONE' => [],
            'INPROGRESS' => [],
            'NEW' => [],
        ];
        $states = $this->getStateLabels($projectId);

        foreach ($states as $key => $value) {
            $statusByType[$value['statusType']][] = $key;
        }

        $doneQuery = 'IN('.implode(',', $statusByType['DONE']).')';
        $inProgressQuery = 'IN('.implode(',', $statusByType['INPROGRESS']).')';
        $newQuery = 'IN('.implode(',', $statusByType['NEW']).')';
        $openTodos = 'IN('.implode(',', array_merge($statusByType['NEW'], $statusByType['INPROGRESS'])).')';

        if ($doneQuery == 'IN()') {
            $doneQuery = 'IN(FALSE)';
        }
        if ($inProgressQuery == 'IN()') {
            $inProgressQuery = 'IN(FALSE)';
        }
        if ($newQuery == 'IN()') {
            $newQuery = 'IN(FALSE)';
        }
        if ($openTodos == 'IN()') {
            $openTodos = 'IN(FALSE)';
        }

        $statusByTypeQuery = [
            'DONE' => $doneQuery,
            'INPROGRESS' => $inProgressQuery,
            'NEW' => $newQuery,
            'ALLOPEN' => $openTodos,
        ];

        return $statusByTypeQuery;
    }

    public function getStatusIdByName($statusLabel, $projectId): int|false
    {
        $statusList = $this->getStateLabels($projectId);

        foreach ($statusList as $key => $status) {
            if ($status['name'] == $statusLabel) {
                return $key;
            }
        }

        return false;
    }

    /**
     * getAll - get all Tickets, depending on userrole
     *
     * @throws BindingResolutionException
     */
    public function getAll(int $limit = 9999): false|array
    {

        $id = session('userdata.id');

        $values = $this->getUsersTickets($id, $limit);

        return $values;
    }

    /**
     * @throws BindingResolutionException
     */
    public function getUsersTickets($id, $limit): false|array
    {
        $users = app()->make(Users::class);
        $user = $users->getUser($id);

        $sql = "SELECT
						ticket.id,
						ticket.headline,
						ticket.type,
						ticket.description,
						ticket.date,
						ticket.dateToFinish,
						ticket.projectId,
						ticket.priority,
						ticket.status,
						project.name as projectName,
						client.name as clientName,
						client.name as clientName,
						t1.id AS authorId,
						t1.firstname AS authorFirstname,
						t1.lastname AS authorLastname,
						t2.id AS editorId,
						t2.firstname AS editorFirstname,
						t2.lastname AS editorLastname
				FROM
				zp_tickets AS ticket
				LEFT JOIN zp_projects as project ON ticket.projectId = project.id
				LEFT JOIN zp_clients as client ON project.clientId = client.id
				LEFT JOIN zp_user AS t1 ON ticket.userId = t1.id
				LEFT JOIN zp_user AS t2 ON ticket.editorId = t2.id
				WHERE (
				    ticket.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :id)
                    OR project.psettings = 'all'
                    OR (project.psettings = 'clients' AND project.clientId = :clientId)
                )
                AND ticket.type <> 'milestone'
				ORDER BY ticket.id DESC";

        if ($limit > -1) {
            $sql .= ' LIMIT :limit';
        }

        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $stmn->bindValue(':clientId', $user['clientId'] ?? '', PDO::PARAM_STR);
        if ($limit > -1) {
            $stmn->bindValue(':limit', $limit, PDO::PARAM_INT);
        }
        $stmn->execute();
        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    /**
     * getAllBySearchCriteria - get Tickets by search criteria array
     *
     * @param  null  $limit
     */
    public function getAllBySearchCriteria(array $searchCriteria, string $sort = 'standard', $limit = null, $includeCounts = true, $offset = null): bool|array
    {
        $query = "
                SELECT
                     zp_tickets.id,
                zp_tickets.headline,
                zp_tickets.description,
                zp_tickets.date,
                zp_tickets.sprint,
                zp_sprints.name as sprintName,
                zp_tickets.storypoints,
                zp_tickets.sortindex,
                zp_tickets.dateToFinish,
                zp_tickets.projectId,
                zp_tickets.priority,
                IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
                zp_tickets.status,
                zp_tickets.tags,
                zp_tickets.editorId,
                zp_tickets.dependingTicketId,
                zp_tickets.milestoneid,
                zp_tickets.planHours,
                zp_tickets.editFrom,
                zp_tickets.editTo,
                zp_tickets.hourRemaining,
                COALESCE(ROUND(timesheet_agg.total_hours, 2), 0) AS bookedHours,
                zp_projects.name AS projectName,
                zp_clients.name AS clientName,
                zp_clients.id AS clientId,
                t1.id AS authorId,
                t1.lastname AS authorLastname,
                t1.firstname AS authorFirstname,
                t1.profileId AS authorProfileId,
                t2.firstname AS editorFirstname,
                t2.lastname AS editorLastname,
                t2.profileId AS editorProfileId,
                milestone.headline AS milestoneHeadline,
                IF((milestone.tags IS NULL OR milestone.tags = ''), 'var(--grey)', milestone.tags) AS milestoneColor,";

        if ($includeCounts) {
            $query .= '
                    COALESCE(comment_agg.comment_count, 0) AS commentCount,
                    COALESCE(file_agg.file_count, 0) AS fileCount,
                    COALESCE(subtask_agg.subtask_count, 0) AS subtaskCount,
                           ';
        } else {
            $query .= '
                        0 AS commentCount,
                        0 AS fileCount,
                        0 AS subtaskCount,
                    ';
        }

        $query .= "
                    parent.headline AS parentHeadline
                FROM
                    zp_tickets
                LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
                LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
                LEFT JOIN zp_user AS t1 ON zp_tickets.userId = t1.id
                LEFT JOIN zp_user AS t2 ON zp_tickets.editorId = t2.id
                LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
                LEFT JOIN zp_sprints ON zp_tickets.sprint = zp_sprints.id
                LEFT JOIN zp_tickets AS milestone ON zp_tickets.milestoneid = milestone.id AND zp_tickets.milestoneid > 0 AND milestone.type = 'milestone'
                LEFT JOIN zp_tickets AS parent ON zp_tickets.dependingTicketId = parent.id";

        $query .= '
            LEFT JOIN (
                SELECT ticketId, SUM(hours) as total_hours
                FROM zp_timesheets
                GROUP BY ticketId
            ) AS timesheet_agg ON zp_tickets.id = timesheet_agg.ticketId';

        if ($includeCounts) {
            $query .= "
            LEFT JOIN (
                SELECT moduleId, COUNT(*) as comment_count
                FROM zp_comment
                WHERE module = 'ticket'
                GROUP BY moduleId
            ) AS comment_agg ON zp_tickets.id = comment_agg.moduleId
            LEFT JOIN (
                SELECT moduleId, COUNT(*) as file_count
                FROM zp_file
                WHERE module = 'ticket'
                GROUP BY moduleId
            ) AS file_agg ON zp_tickets.id = file_agg.moduleId
            LEFT JOIN (
                SELECT dependingTicketId, COUNT(*) as subtask_count
                FROM zp_tickets
                WHERE dependingTicketId > 0
                GROUP BY dependingTicketId
            ) AS subtask_agg ON zp_tickets.id = subtask_agg.dependingTicketId";
        }

        $query .= "
            LEFT JOIN zp_relationuserproject AS rup ON zp_tickets.projectId = rup.projectId AND rup.userId = :userId
            WHERE (
                rup.projectId IS NOT NULL
                OR zp_projects.psettings = 'all'
                OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
                OR (requestor.role >= 40)
            )
        ";

        if (isset($searchCriteria['dateFrom']) && $searchCriteria['dateFrom'] != '') {
            $query .= ' AND zp_tickets.date > :dateFrom';
        }

        if (isset($searchCriteria['dateTo']) && $searchCriteria['dateTo'] != '') {
            $query .= ' AND zp_tickets.date < :dateTo';
        }

        if (isset($searchCriteria['excludeType']) && $searchCriteria['excludeType'] != '') {
            $query .= ' AND zp_tickets.type <> :excludeType';
        }

        // Pulling tasks is currrently locked to the currentProject (which is tied to the user session)
        if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
            $query .= ' AND zp_tickets.projectId = :projectId';
        }

        if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
            $editorIdIn = DbCore::arrayToPdoBindingString('users', count(explode(',', $searchCriteria['users'])));
            $query .= ' AND zp_tickets.editorId IN('.$editorIdIn.')';
        }

        if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
            $milestoneIn = DbCore::arrayToPdoBindingString('milestone', count(explode(',', $searchCriteria['milestone'])));
            $query .= ' AND zp_tickets.milestoneid IN('.$milestoneIn.')';
        }

        if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
            $query .= ' ';
        } elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
            $statusArray = explode(',', $searchCriteria['status']);

            if (array_search('not_done', $statusArray) !== false) {
                // Project Id needs to be set to search for not_done due to custom done states across projects
                if ($searchCriteria['currentProject'] != '') {
                    $statusLabels = $this->getStateLabels($searchCriteria['currentProject']);

                    $statusList = [];
                    foreach ($statusLabels as $key => $status) {
                        if ($status['statusType'] !== 'DONE') {
                            $statusList[] = $key;
                        }
                    }

                    $query .= ' AND zp_tickets.status IN('.implode(',', $statusList).')';
                }
            } else {
                $statusIn = DbCore::arrayToPdoBindingString(
                    'status',
                    count(explode(',', $searchCriteria['status']))
                );
                $query .= ' AND zp_tickets.status IN('.$statusIn.')';
            }
        } else {
            $query .= ' AND zp_tickets.status <> -1';
        }

        if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
            $typeIn = DbCore::arrayToPdoBindingString('type', count(explode(',', strtolower($searchCriteria['type']))));
            $query .= ' AND LOWER(zp_tickets.type) IN('.$typeIn.')';
        }

        if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
            $priorityIn = DbCore::arrayToPdoBindingString('priority', count(explode(',', strtolower($searchCriteria['priority']))));
            $query .= ' AND LOWER(zp_tickets.priority) IN('.$priorityIn.')';
        }

        if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
            $query .= ' AND (FIND_IN_SET(:termStandard, zp_tickets.tags) OR zp_tickets.headline LIKE :termWild OR zp_tickets.description LIKE :termWild OR zp_tickets.id LIKE :termWild)';
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
            $sprintIn = DbCore::arrayToPdoBindingString('sprint', count(explode(',', $searchCriteria['sprint'])));
            $query .= ' AND zp_tickets.sprint IN('.$sprintIn.')';
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] == 'backlog') {
            $query .= " AND (zp_tickets.sprint IS NULL OR zp_tickets.sprint = '' OR zp_tickets.sprint = -1)";
        }

        $query .= ' GROUP BY zp_tickets.id ';

        if ($sort == 'standard') {
            $query .= ' ORDER BY zp_tickets.sortindex ASC, zp_tickets.id DESC';
        } elseif ($sort == 'kanbansort') {
            $query .= ' ORDER BY zp_tickets.kanbanSortIndex ASC, zp_tickets.id DESC';
        } elseif ($sort == 'duedate') {
            $query .= " ORDER BY (zp_tickets.dateToFinish = '0000-00-00 00:00:00'), zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC";
        } elseif ($sort == 'priority') {
            $query .= ' ORDER BY zp_tickets.priority ASC, zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
        } elseif ($sort == 'date') {
            $query .= ' ORDER BY zp_tickets.date DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
        }

        if ($limit !== null && $limit > 0) {
            if ($offset !== null && $offset > 0) {
                $query .= ' LIMIT :offset, :limit';
            } else {
                $query .= ' LIMIT :limit';
            }
        }

        $stmn = $this->db->database->prepare($query);

        if (isset($searchCriteria['dateFrom']) && $searchCriteria['dateFrom'] != '') {
            $stmn->bindValue(':dateFrom', $searchCriteria['dateFrom'], PDO::PARAM_STR);
        }

        if (isset($searchCriteria['dateTo']) && $searchCriteria['dateTo'] != '') {
            $stmn->bindValue(':dateTo', $searchCriteria['dateTo'], PDO::PARAM_STR);
        }

        if (isset($searchCriteria['excludeType']) && $searchCriteria['excludeType'] != '') {
            $stmn->bindValue(':excludeType', $searchCriteria['excludeType'], PDO::PARAM_STR);
        }

        // NOTE: This should not be removed as it is used for authorization
        if (isset($searchCriteria['currentUser'])) {
            $stmn->bindValue(':userId', $searchCriteria['currentUser'], PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
        }

        // Current client is only used for authorization as it represents the current client Id assigned to a user.
        // Do not attempt to filter tickets using this value.
        if (isset($searchCriteria['currentClient'])) {
            $stmn->bindValue(':clientId', $searchCriteria['currentClient'], PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
        }

        if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
            $stmn->bindValue(':projectId', $searchCriteria['currentProject'], PDO::PARAM_INT);
        }

        if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
            foreach (explode(',', $searchCriteria['milestone']) as $key => $milestone) {
                $stmn->bindValue(':milestone'.$key, $milestone, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
            foreach (explode(',', $searchCriteria['type']) as $key => $type) {
                $stmn->bindValue(':type'.$key, $type, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
            foreach (explode(',', $searchCriteria['priority']) as $key => $priority) {
                $stmn->bindValue(':priority'.$key, $priority, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
            foreach (explode(',', $searchCriteria['users']) as $key => $user) {
                $stmn->bindValue(':users'.$key, $user, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['status']) && $searchCriteria['status'] != 'all') {
            $statusArray = explode(',', $searchCriteria['status']);
            if ($searchCriteria['status'] != '' && array_search('not_done', $statusArray) === false) {
                foreach (explode(',', $searchCriteria['status']) as $key => $status) {
                    $stmn->bindValue(':status'.$key, $status, PDO::PARAM_STR);
                }
            }
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
            foreach (explode(',', $searchCriteria['sprint']) as $key => $sprint) {
                $stmn->bindValue(':sprint'.$key, $sprint, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
            $termWild = '%'.$searchCriteria['term'].'%';
            $stmn->bindValue(':termWild', $termWild, PDO::PARAM_STR);
            $stmn->bindValue(':termStandard', $searchCriteria['term'], PDO::PARAM_STR);
        }

        if ($limit !== null && $limit > 0) {
            $stmn->bindValue(':limit', $limit, PDO::PARAM_INT);
            if ($offset !== null && $offset > 0) {
                $stmn->bindValue(':offset', $offset, PDO::PARAM_INT);
            }
        }

        if (session()->exists('userdata')) {
            $stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
        }

        $stmn->execute();

        $values = $stmn->fetchAll(PDO::FETCH_ASSOC);
        $stmn->closeCursor();

        return $values;
    }

    public function simpleTicketQuery(?int $userId, ?int $projectId, array $types = []): array|false
    {

        $query = <<<'SQL'
                SELECT
                    zp_tickets.id,
                    zp_tickets.headline,
                    zp_tickets.description,
                    zp_tickets.date,
                    zp_tickets.sprint,
                    zp_tickets.storypoints,
                    zp_tickets.sortindex,
                    zp_tickets.dateToFinish,
                    zp_tickets.projectId,
                    zp_tickets.priority,
                    IF(zp_tickets.type <> "", zp_tickets.type, "task") AS type,
                    zp_tickets.status,
                    zp_tickets.tags,
                    zp_tickets.userId,
                    zp_tickets.editorId,
                    zp_tickets.dependingTicketId,
                    zp_tickets.milestoneid,
                    milestones.headline AS milestoneHeadline,
                    zp_tickets.planHours,
                    zp_tickets.editFrom,
                    zp_tickets.editTo,
                    zp_tickets.hourRemaining,
                    zp_projects.name AS projectName,
                    zp_projects.details AS projectDescription
                FROM
                    zp_tickets
                    LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
                    LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
                    LEFT JOIN zp_tickets AS milestones ON zp_tickets.milestoneid = milestones.id
                      WHERE (
                        zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :requestorId)
                        OR zp_projects.psettings = 'all'
                        OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
                        OR (requestor.role >= 40)
                    )
            SQL;

        // Pulling tasks is currrently locked to the currentProject (which is tied to the user session)
        if (isset($projectId) && $projectId > 0) {
            $query .= ' AND zp_tickets.projectId = :projectId';
        }

        if (isset($userId) && $userId > 0) {
            $query .= ' AND zp_tickets.editorId = :userId';
        }

        if (count($types) > 0) {
            $typeIn = DbCore::arrayToPdoBindingString('types', count($types));
            $query .= ' AND zp_tickets.type IN('.$typeIn.')';
        }

        $query .= ' ORDER BY zp_tickets.dateToFinish DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';

        $stmn = $this->db->database->prepare($query);

        if (isset($projectId) && $projectId > 0) {
            $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        }

        if (isset($userId) && $userId > 0) {
            $stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
        }

        if (count($types) > 0) {
            foreach ($types as $key => $type) {
                $stmn->bindValue(':types'.$key, $type, PDO::PARAM_STR);
            }
        }

        $stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);

        // Current client is only used for authorization as it represents the current client Id assigned to a user.
        // Do not attempt to filter tickets using this value.
        if (session()->exists('userdata')) {
            $stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
        }

        $stmn->execute();

        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    public function getScheduledTasks(CarbonImmutable $dateFrom, CarbonImmutable $dateTo, ?int $userId = null)
    {

        $query = <<<'SQL'
                SELECT
                    zp_tickets.id,
                    zp_tickets.headline,
                    zp_tickets.description,
                    zp_tickets.date,
                    zp_tickets.sprint,
                    zp_tickets.storypoints,
                    zp_tickets.sortindex,
                    zp_tickets.dateToFinish,
                    zp_tickets.projectId,
                    zp_tickets.priority,
                    IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
                    zp_tickets.status,
                    zp_tickets.tags,
                    zp_tickets.editorId,
                    zp_tickets.dependingTicketId,
                    zp_tickets.milestoneid,
                    zp_tickets.planHours,
                    zp_tickets.editFrom,
                    zp_tickets.editTo,
                    zp_tickets.hourRemaining
                FROM
                    zp_tickets
                LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
                LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
                WHERE
                    (
                        zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :userId)
                        OR zp_projects.psettings = 'all'
                        OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
                        OR (requestor.role >= 40)
                    )
                    AND zp_tickets.type <> 'milestone'
            SQL;

        if (isset($userId)) {
            $query .= ' AND zp_tickets.editorId = :userId';
        }

        $query .= ' AND ((zp_tickets.editFrom BETWEEN :dateFrom AND :dateTo) OR (zp_tickets.editTo BETWEEN :dateFrom AND :dateTo))';

        $stmn = $this->db->database->prepare($query);

        if (isset($userId)) {
            $stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
        }

        $stmn->bindValue(':dateFrom', $dateFrom->formatDateTimeForDb(), PDO::PARAM_STR);

        $stmn->bindValue(':dateTo', $dateTo->formatDateTimeForDb(), PDO::PARAM_STR);

        if (session()->exists('userdata')) {
            $stmn->bindValue(':requestorId', session('userdata.id'), PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':requestorId', -1, PDO::PARAM_INT);
        }

        $stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);

        $stmn->execute();
        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    public function getAllByProjectId($projectId): false|array
    {

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						zp_tickets.hourRemaining,
						zp_tickets.acceptanceCriteria,
						zp_tickets.userId,
						zp_tickets.editorId,
						zp_tickets.planHours,
						zp_tickets.tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid,
						zp_projects.name AS projectName,
						zp_clients.name AS clientName,
						zp_user.firstname AS userFirstname,
						zp_user.lastname AS userLastname,
						t3.firstname AS editorFirstname,
						t3.lastname AS editorLastname
					FROM
				        zp_tickets
				    LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
					LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
					LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
					LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
					WHERE
						zp_tickets.projectId = :projectId";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);

        $stmn->execute();
        $values = $stmn->fetchAll(PDO::FETCH_CLASS, '\Leantime\Domain\Tickets\Models\Tickets');
        $stmn->closeCursor();

        return $values;
    }

    public function getTags($projectId): false|array
    {

        $query = "SELECT
						zp_tickets.tags
					FROM
						zp_tickets LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
					WHERE
						zp_tickets.projectId = :projectId AND zp_tickets.type <> 'milestone'";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);

        $stmn->execute();
        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    /**
     * getTicket - get a specific Ticket depending on the role
     */
    public function getTicket($id): \Leantime\Domain\Tickets\Models\Tickets|bool
    {

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						zp_tickets.hourRemaining,
						zp_tickets.acceptanceCriteria,
						zp_tickets.userId,
						zp_tickets.editorId,
						zp_tickets.planHours,
						zp_tickets.tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid,
						milestones.headline AS milestoneHeadline,
						zp_projects.name AS projectName,
						zp_projects.details AS projectDescription,
						zp_clients.name AS clientName,
						zp_user.firstname AS userFirstname,
						zp_user.lastname AS userLastname,
						t3.firstname AS editorFirstname,
						t3.lastname AS editorLastname,
						parent.headline AS parentHeadline
					FROM
						zp_tickets
					LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
					LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
					LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
					LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
					LEFT JOIN zp_tickets AS parent on zp_tickets.dependingTicketId = parent.id
					LEFT JOIN zp_tickets AS milestones on zp_tickets.milestoneid = milestones.id

					WHERE
						zp_tickets.id = :ticketId
					LIMIT 1";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':ticketId', $id, PDO::PARAM_INT);

        $stmn->execute();
        $values = $stmn->fetchObject('\Leantime\Domain\Tickets\Models\Tickets');
        $stmn->closeCursor();

        return $values;
    }

    public function getAllSubtasks($id): false|array
    {

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
						DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						IFNULL(zp_tickets.hourRemaining, 0) AS hourRemaining,
						zp_tickets.acceptanceCriteria,
						zp_tickets.userId,
						zp_tickets.editorId,
						IFNULL(zp_tickets.planHours, 0) AS planHours,
						zp_tickets.tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid,
						zp_projects.name AS projectName,
						zp_clients.name AS clientName,
						zp_user.firstname AS userFirstname,
						zp_user.lastname AS userLastname,
						t3.firstname AS editorFirstname,
						t3.lastname AS editorLastname
					FROM
						zp_tickets
					LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
					LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
					LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
					LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
					WHERE
						zp_tickets.dependingTicketId = :ticketId
					ORDER BY zp_tickets.date DESC";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':ticketId', $id, PDO::PARAM_INT);

        $stmn->execute();
        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    public function getAllPossibleParents(\Leantime\Domain\Tickets\Models\Tickets $ticket, $projectId): false|array
    {

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
						DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						IFNULL(zp_tickets.hourRemaining, 0) AS hourRemaining,
						zp_tickets.acceptanceCriteria,
						zp_tickets.userId,
						zp_tickets.editorId,
						IFNULL(zp_tickets.planHours, 0) AS planHours,
						zp_tickets.tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid,
						zp_projects.name AS projectName,
						zp_clients.name AS clientName,
						zp_user.firstname AS userFirstname,
						zp_user.lastname AS userLastname,
						t3.firstname AS editorFirstname,
						t3.lastname AS editorLastname
					FROM
						zp_tickets
					LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
					LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
					LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
					LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
					WHERE
						zp_tickets.id <> :ticketId
					    AND zp_tickets.type <> 'milestone'
					    AND (zp_tickets.dependingTicketId <> :ticketId OR zp_tickets.dependingTicketId IS NULL)
                    ";

        if ($projectId !== 0) {
            $query .= ' AND zp_tickets.projectId = :projectId';
        }

        $query .= ' ORDER BY zp_tickets.date DESC';

        $stmn = $this->db->database->prepare($query);

        $stmn->bindValue(':ticketId', $ticket->id ?? 0, PDO::PARAM_INT);
        $stmn->bindValue(':dependingId', $ticket->dependingTicketId ?? null, PDO::PARAM_INT);

        if ($projectId !== 0) {
            $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        }

        $stmn->execute();
        $values = $stmn->fetchAll(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
        $stmn->closeCursor();

        return $values;
    }

    /**
     * Gets all tasks grouped around milestones for timeline views
     */
    public function getAllMilestones(array $searchCriteria, string $sort = 'standard'): false|array
    {

        $statusGroups = $this->getStatusListGroupedByType($searchCriteria['currentProject'] ?? session('currentProject'));

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
						DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						zp_tickets.hourRemaining,
						zp_tickets.acceptanceCriteria,
						depMilestone.headline AS milestoneHeadline,
						IF((depMilestone.tags IS NULL OR depMilestone.tags = ''), 'var(--grey)', depMilestone.tags) AS milestoneColor,
						zp_tickets.userId,
						zp_tickets.editorId,
						zp_tickets.planHours,
						IF((zp_tickets.tags IS NULL OR zp_tickets.tags = ''), 'var(--grey)', zp_tickets.tags) AS tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.sortIndex,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid,
						zp_projects.name AS projectName,
						zp_clients.name AS clientName,
						zp_user.firstname AS userFirstname,
						zp_user.lastname AS userLastname,
						t3.firstname AS editorFirstname,
						t3.lastname AS editorLastname,
						t3.profileId AS editorProfileId
					FROM
						zp_tickets
						LEFT JOIN zp_projects ON zp_tickets.projectId = zp_projects.id
						LEFT JOIN zp_tickets AS depMilestone ON zp_tickets.milestoneid = depMilestone.id
						LEFT JOIN zp_clients ON zp_projects.clientId = zp_clients.id
						LEFT JOIN zp_user ON zp_tickets.userId = zp_user.id
						LEFT JOIN zp_user AS t3 ON zp_tickets.editorId = t3.id
					    LEFT JOIN zp_user AS requestor ON requestor.id = :requestorId
						WHERE (zp_projects.state <> -1 OR zp_projects.state IS NULL)
						AND (
                        zp_tickets.projectId IN (SELECT projectId FROM zp_relationuserproject WHERE zp_relationuserproject.userId = :userId)
                        OR zp_projects.psettings = 'all'
                        OR (zp_projects.psettings = 'clients' AND zp_projects.clientId = :clientId)
                        OR (requestor.role >= 40)
                    )
                    ";

        if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
            $query .= ' AND zp_tickets.projectId = :projectId';
        }

        if (isset($searchCriteria['clients']) && $searchCriteria['clients'] != 0 && $searchCriteria['clients'] != '' && $searchCriteria['clients'] != '') {
            $clientIdIn = DbCore::arrayToPdoBindingString('clients', count(explode(',', $searchCriteria['clients'])));
            $query .= ' AND zp_projects.clientId IN('.$clientIdIn.')';
        }

        if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
            $editorIdIn = DbCore::arrayToPdoBindingString('users', count(explode(',', $searchCriteria['users'])));
            $query .= ' AND zp_tickets.editorId IN('.$editorIdIn.')';
        }

        if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
            $milestoneIn = DbCore::arrayToPdoBindingString('milestone', count(explode(',', $searchCriteria['milestone'])));
            $query .= ' AND zp_tickets.milestoneid IN('.$milestoneIn.')';
        }

        if (isset($searchCriteria['status']) && $searchCriteria['status'] == 'all') {
            $query .= ' ';
        } elseif (isset($searchCriteria['status']) && $searchCriteria['status'] != '') {
            $statusArray = explode(',', $searchCriteria['status']);

            if (array_search('not_done', $statusArray) !== false) {
                // Project Id needs to be set to search for not_done due to custom done states across projects
                if ($searchCriteria['currentProject'] != '') {
                    $statusLabels = $this->getStateLabels($searchCriteria['currentProject']);

                    $statusList = [];
                    foreach ($statusLabels as $key => $status) {
                        if ($status['statusType'] !== 'DONE') {
                            $statusList[] = $key;
                        }
                    }

                    $query .= ' AND zp_tickets.status IN('.implode(',', $statusList).')';
                }
            } else {
                $statusIn = DbCore::arrayToPdoBindingString(
                    'status',
                    count(explode(',', $searchCriteria['status']))
                );
                $query .= ' AND zp_tickets.status IN('.$statusIn.')';
            }
        } else {
            $query .= ' AND zp_tickets.status <> -1';
        }

        if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
            $typeIn = DbCore::arrayToPdoBindingString('type', count(explode(',', strtolower($searchCriteria['type']))));
            $query .= ' AND LOWER(zp_tickets.type) IN('.$typeIn.')';
        }

        if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
            $priorityIn = DbCore::arrayToPdoBindingString('priority', count(explode(',', strtolower($searchCriteria['priority']))));
            $query .= ' AND LOWER(zp_tickets.priority) IN('.$priorityIn.')';
        }

        if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
            $query .= ' AND (FIND_IN_SET(:termStandard, zp_tickets.tags) OR zp_tickets.headline LIKE :termWild OR zp_tickets.description LIKE :termWild OR zp_tickets.id LIKE :termWild)';
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
            $sprintIn = DbCore::arrayToPdoBindingString('sprint', count(explode(',', $searchCriteria['sprint'])));
            $query .= ' AND (zp_tickets.sprint IN('.$sprintIn.") OR zp_tickets.type = 'milestone')";
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] == 'backlog') {
            $query .= " AND (zp_tickets.sprint IS NULL OR zp_tickets.sprint = '' OR zp_tickets.sprint = -1 OR zp_tickets.type = 'milestone')";
        }

        $query .= '	GROUP BY
						zp_tickets.id';

        if ($sort == 'standard') {
            $query .= ' ORDER BY zp_tickets.sortindex ASC, zp_tickets.editFrom ASC, zp_tickets.id DESC';
        } elseif ($sort == 'kanbansort') {
            $query .= ' ORDER BY zp_tickets.kanbanSortIndex ASC, zp_tickets.id DESC';
        } elseif ($sort == 'duedate') {
            $query .= " ORDER BY (zp_tickets.dateToFinish = '0000-00-00 00:00:00'), zp_tickets.dateToFinish ASC, zp_tickets.sortindex ASC, zp_tickets.id DESC";
        } elseif ($sort == 'date') {
            $query .= ' ORDER BY zp_tickets.date DESC, zp_tickets.sortindex ASC, zp_tickets.id DESC';
        }

        $stmn = $this->db->database->prepare($query);

        // NOTE: This should not be removed as it is used for authorization
        if (isset($searchCriteria['currentUser'])) {
            $stmn->bindValue(':userId', $searchCriteria['currentUser'], PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':userId', session('userdata.id') ?? '-1', PDO::PARAM_INT);
        }

        $stmn->bindValue(':requestorId', session('userdata.id') ?? '-1', PDO::PARAM_INT);

        if (isset($searchCriteria['currentProject']) && $searchCriteria['currentProject'] != '') {
            $stmn->bindValue(':projectId', $searchCriteria['currentProject'], PDO::PARAM_INT);
        }

        if (isset($searchCriteria['users']) && $searchCriteria['users'] != '') {
            foreach (explode(',', $searchCriteria['users']) as $key => $user) {
                $stmn->bindValue(':users'.$key, $user, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['clients']) && $searchCriteria['clients'] != 0 && $searchCriteria['clients'] != '' && $searchCriteria['clients'] != '') {
            foreach (explode(',', $searchCriteria['clients']) as $key => $client) {
                $stmn->bindValue(':clients'.$key, $client, PDO::PARAM_STR);
            }
        }

        // Current client is only used for authorization as it represents the current client Id assigned to a user.
        // Do not attempt to filter tickets using this value.
        if (isset($searchCriteria['currentClient'])) {
            $stmn->bindValue(':clientId', $searchCriteria['currentClient'], PDO::PARAM_INT);
        } else {
            $stmn->bindValue(':clientId', session('userdata.clientId') ?? '-1', PDO::PARAM_INT);
        }

        if (isset($searchCriteria['milestone']) && $searchCriteria['milestone'] != '') {
            foreach (explode(',', $searchCriteria['milestone']) as $key => $milestone) {
                $stmn->bindValue(':milestone'.$key, $milestone, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['status']) && $searchCriteria['status'] != 'all') {
            $statusArray = explode(',', $searchCriteria['status']);
            if ($searchCriteria['status'] != '' && array_search('not_done', $statusArray) === false) {
                foreach (explode(',', $searchCriteria['status']) as $key => $status) {
                    $stmn->bindValue(':status'.$key, $status, PDO::PARAM_STR);
                }
            }
        }

        if (isset($searchCriteria['type']) && $searchCriteria['type'] != '') {
            foreach (explode(',', $searchCriteria['type']) as $key => $type) {
                $stmn->bindValue(':type'.$key, $type, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['priority']) && $searchCriteria['priority'] != '') {
            foreach (explode(',', $searchCriteria['priority']) as $key => $priority) {
                $stmn->bindValue(':priority'.$key, $priority, PDO::PARAM_STR);
            }
        }

        if (isset($searchCriteria['term']) && $searchCriteria['term'] != '') {
            $termWild = '%'.$searchCriteria['term'].'%';
            $stmn->bindValue(':termWild', $termWild, PDO::PARAM_STR);
            $stmn->bindValue(':termStandard', $searchCriteria['term'], PDO::PARAM_STR);
        }

        if (isset($searchCriteria['sprint']) && $searchCriteria['sprint'] > 0 && $searchCriteria['sprint'] != 'all') {
            foreach (explode(',', $searchCriteria['sprint']) as $key => $sprint) {
                $stmn->bindValue(':sprint'.$key, $sprint, PDO::PARAM_STR);
            }
        }

        $stmn->execute();
        $values = $stmn->fetchAll(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
        $stmn->closeCursor();

        return $values;
    }

    /**
     * getType - get the Type from the type array
     */
    public function getType(): array
    {
        return $this->type;
    }

    /**
     * getPriority - get the priority from the priority array
     */
    public function getPriority($priority): string
    {

        if ($priority !== null && $priority !== '') {
            return $this->priority[$priority];
        } else {
            return $this->priority[1];
        }
    }

    public function getFirstTicket($projectId): mixed
    {

        $query = "SELECT
						zp_tickets.id,
						zp_tickets.headline,
						IF(zp_tickets.type <> '', zp_tickets.type, 'task') AS type,
						zp_tickets.description,
						zp_tickets.date,
						DATE_FORMAT(zp_tickets.date, '%Y,%m,%e') AS timelineDate,
						DATE_FORMAT(zp_tickets.dateToFinish, '%Y,%m,%e') AS timelineDateToFinish,
						zp_tickets.dateToFinish,
						zp_tickets.projectId,
						zp_tickets.priority,
						zp_tickets.status,
						zp_tickets.sprint,
						zp_tickets.storypoints,
						zp_tickets.hourRemaining,
						zp_tickets.acceptanceCriteria,
						zp_tickets.userId,
						zp_tickets.editorId,
						zp_tickets.planHours,
						zp_tickets.tags,
						zp_tickets.url,
						zp_tickets.editFrom,
						zp_tickets.editTo,
						zp_tickets.dependingTicketId,
						zp_tickets.milestoneid

					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
                    ORDER BY
					    zp_tickets.date ASC
					LIMIT 1";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);

        $stmn->execute();
        $stmn->setFetchMode(PDO::FETCH_CLASS, 'Leantime\Domain\Tickets\Models\Tickets');
        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values;
    }

    public function getNumberOfAllTickets($projectId = null): mixed
    {

        $query = "SELECT
						COUNT(zp_tickets.id) AS allTickets
					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone'";

        if (! is_null($projectId)) {
            $query .= 'AND zp_tickets.projectId = :projectId ';
        }

        $stmn = $this->db->database->prepare($query);

        if (! is_null($projectId)) {
            $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        }

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['allTickets'];
    }

    public function getNumberOfMilestones($projectId = null): mixed
    {

        $query = "SELECT
						COUNT(zp_tickets.id) AS allTickets
					FROM
						zp_tickets
					WHERE
						zp_tickets.type = 'milestone' ";

        if (! is_null($projectId)) {
            $query .= 'AND zp_tickets.projectId = :projectId ';
        }

        $stmn = $this->db->database->prepare($query);

        if (! is_null($projectId)) {
            $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        }

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['allTickets'];
    }

    public function getNumberOfClosedTickets($projectId): mixed
    {

        $statusGroups = $this->getStatusListGroupedByType($projectId);

        $query = "SELECT
						COUNT(zp_tickets.id) AS allTickets
					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
						AND zp_tickets.status ".$statusGroups['DONE'].'
                    ORDER BY
					    zp_tickets.date ASC
					LIMIT 1';

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['allTickets'];
    }

    public function getEffortOfClosedTickets($projectId, $averageStorySize): mixed
    {

        $statusGroups = $this->getStatusListGroupedByType($projectId);

        $query = "SELECT
						SUM(CASE when zp_tickets.storypoints <> '' then zp_tickets.storypoints else :avgStorySize end) AS allEffort
					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
						AND zp_tickets.status ".$statusGroups['DONE'].'
                    ORDER BY
					    zp_tickets.date ASC
					LIMIT 1';

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        $stmn->bindValue(':avgStorySize', $averageStorySize, PDO::PARAM_INT);

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['allEffort'];
    }

    public function getEffortOfAllTickets($projectId, $averageStorySize): mixed
    {

        $query = "SELECT
						SUM(CASE when zp_tickets.storypoints <> '' then zp_tickets.storypoints else :avgStorySize end) AS allEffort
					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone' AND zp_tickets.projectId = :projectId
                    ORDER BY
					    zp_tickets.date ASC
					LIMIT 1";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);
        $stmn->bindValue(':avgStorySize', $averageStorySize, PDO::PARAM_INT);

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['allEffort'];
    }

    public function getAverageTodoSize($projectId): mixed
    {
        $query = "SELECT
						AVG(zp_tickets.storypoints) as avgSize
					FROM
						zp_tickets
					WHERE
						zp_tickets.type <> 'milestone' AND
						(zp_tickets.storypoints <> '' AND zp_tickets.storypoints IS NOT NULL) AND zp_tickets.projectId = :projectId
                    ORDER BY
					    zp_tickets.date ASC
					LIMIT 1";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':projectId', $projectId, PDO::PARAM_INT);

        $stmn->execute();

        $values = $stmn->fetch();
        $stmn->closeCursor();

        return $values['avgSize'];
    }

    /**
     * addTicket - add a Ticket with postback test
     */
    public function addTicket(array $values): bool|int
    {

        $query = 'INSERT INTO zp_tickets (
						headline,
						type,
						description,
						date,
						dateToFinish,
						projectId,
						status,
						userId,
						tags,
						sprint,
						storypoints,
						priority,
						hourRemaining,
						planHours,
						acceptanceCriteria,
						editFrom,
						editTo,
						editorId,
						dependingTicketId,
                        milestoneid,
						sortindex,
						kanbanSortindex
				) VALUES (
						:headline,
						:type,
						:description,
						:date,
						:dateToFinish,
						:projectId,
						:status,
						:userId,
						:tags,
						:sprint,
						:storypoints,
						:priority,
						:hourRemaining,
						:planHours,
						:acceptanceCriteria,
						:editFrom,
						:editTo,
						:editorId,
						:dependingTicketId,
				         :milestoneid,
						:sortIndex,
						0
				)';

        $stmn = $this->db->database->prepare($query);

        $stmn->bindValue(':headline', $values['headline'], PDO::PARAM_STR);
        $stmn->bindValue(':type', $values['type'], PDO::PARAM_STR);
        $stmn->bindValue(':description', $values['description'], PDO::PARAM_STR);
        $stmn->bindValue(':date', $values['date'], PDO::PARAM_STR);
        $stmn->bindValue(':dateToFinish', $values['dateToFinish'], PDO::PARAM_STR);
        $stmn->bindValue(':projectId', $values['projectId'], PDO::PARAM_STR);
        $stmn->bindValue(':status', $values['status'], PDO::PARAM_STR);
        $stmn->bindValue(':userId', $values['userId'], PDO::PARAM_STR);
        $stmn->bindValue(':tags', $values['tags'], PDO::PARAM_STR);

        $stmn->bindValue(':sprint', $values['sprint'], PDO::PARAM_STR);
        $stmn->bindValue(':storypoints', $values['storypoints'], PDO::PARAM_STR);
        $stmn->bindValue(':priority', $values['priority'], PDO::PARAM_STR);
        $stmn->bindValue(':hourRemaining', $values['hourRemaining'], PDO::PARAM_STR);
        $stmn->bindValue(':planHours', $values['planHours'], PDO::PARAM_STR);
        $stmn->bindValue(':acceptanceCriteria', $values['acceptanceCriteria'], PDO::PARAM_STR);

        $stmn->bindValue(':editFrom', $values['editFrom'], PDO::PARAM_STR);
        $stmn->bindValue(':editTo', $values['editTo'], PDO::PARAM_STR);
        $stmn->bindValue(':sortIndex', $values['sortIndex'] ?? '', PDO::PARAM_STR);
        $stmn->bindValue(':editorId', $values['editorId'], PDO::PARAM_STR);

        $depending = $values['dependingTicketId'] ?? '';

        $stmn->bindValue(':dependingTicketId', $depending, PDO::PARAM_STR);

        $milestoneId = $values['milestoneid'] ?? '';

        $stmn->bindValue(':milestoneid', $milestoneId, PDO::PARAM_STR);

        $stmn->execute();

        $stmn->closeCursor();

        if ($this->db->database->lastInsertId() !== false) {
            return intval($this->db->database->lastInsertId());
        }

        return false;
    }

    public function patchTicket($id, array $params): bool
    {

        $this->addTicketChange(session('userdata.id'), $id, $params);

        $sql = 'UPDATE zp_tickets SET ';

        foreach ($params as $key => $value) {
            $sql .= ''.DbCore::sanitizeToColumnString($key).'=:'.DbCore::sanitizeToColumnString($key).', ';
            // send status update event
            if ($key == 'status') {
                static::dispatch_event('ticketStatusUpdate', ['ticketId' => $id, 'status' => $value, 'action' => 'ticketStatusUpdate']);
            }
        }

        $sql .= 'id=:id WHERE id=:id LIMIT 1';

        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);

        foreach ($params as $key => $value) {
            $stmn->bindValue(':'.DbCore::sanitizeToColumnString($key), $value, PDO::PARAM_STR);
        }

        $return = $stmn->execute();
        $stmn->closeCursor();

        return $return;
    }

    /**
     * updateTicket - Update Ticketinformation
     */
    public function updateTicket(array $values, $id): bool
    {

        $this->addTicketChange(session('userdata.id'), $id, $values);

        $query = 'UPDATE zp_tickets
			SET
				headline = :headline,
				type = :type,
				description=:description,
				projectId=:projectId,
				status = :status,
                date = :date,
				dateToFinish = :dateToFinish,
				sprint = :sprint,
				storypoints = :storypoints,
				priority = :priority,
				hourRemaining = :hourRemaining,
				planHours = :planHours,
				tags = :tags,
				editorId = :editorId,
				editFrom = :editFrom,
				editTo = :editTo,
				acceptanceCriteria = :acceptanceCriteria,
				dependingTicketId = :dependingTicketId,
				milestoneid = :milestoneid
			WHERE id = :id LIMIT 1';

        $stmn = $this->db->database->prepare($query);

        $stmn->bindValue(':headline', $values['headline'], PDO::PARAM_STR);
        $stmn->bindValue(':type', $values['type'], PDO::PARAM_STR);
        $stmn->bindValue(':description', $values['description'], PDO::PARAM_STR);
        $stmn->bindValue(':projectId', $values['projectId'], PDO::PARAM_STR);
        $stmn->bindValue(':status', $values['status'], PDO::PARAM_STR);
        $stmn->bindValue(':date', $values['date'], PDO::PARAM_STR);
        $stmn->bindValue(':dateToFinish', $values['dateToFinish'], PDO::PARAM_STR);
        $stmn->bindValue(':sprint', $values['sprint'], PDO::PARAM_STR);
        $stmn->bindValue(':storypoints', $values['storypoints'], PDO::PARAM_STR);
        $stmn->bindValue(':priority', $values['priority'], PDO::PARAM_STR);
        $stmn->bindValue(':hourRemaining', $values['hourRemaining'], PDO::PARAM_STR);
        $stmn->bindValue(':acceptanceCriteria', $values['acceptanceCriteria'], PDO::PARAM_STR);
        $stmn->bindValue(':planHours', $values['planHours'], PDO::PARAM_STR);
        $stmn->bindValue(':tags', $values['tags'], PDO::PARAM_STR);
        $stmn->bindValue(':editorId', $values['editorId'], PDO::PARAM_STR);
        $stmn->bindValue(':editFrom', $values['editFrom'], PDO::PARAM_STR);
        $stmn->bindValue(':editTo', $values['editTo'], PDO::PARAM_STR);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $stmn->bindValue(':dependingTicketId', $values['dependingTicketId'], PDO::PARAM_STR);
        $stmn->bindValue(':milestoneid', $values['milestoneid'], PDO::PARAM_STR);

        $result = $stmn->execute();

        $stmn->closeCursor();

        return $result;
    }

    public function updateTicketStatus($ticketId, $status, int $ticketSorting = -1, $handler = null): bool
    {

        $this->addTicketChange(session('userdata.id'), $ticketId, ['status' => $status]);

        if ($ticketSorting > -1) {
            $query = 'UPDATE zp_tickets
					SET
						kanbanSortIndex = :sortIndex,
						status = :status
					WHERE id = :ticketId
					LIMIT 1';

            $stmn = $this->db->database->prepare($query);
            $stmn->bindValue(':status', $status, PDO::PARAM_INT);
            $stmn->bindValue(':sortIndex', $ticketSorting, PDO::PARAM_INT);
            $stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
        } else {
            $query = 'UPDATE zp_tickets
					SET
						status = :status
					WHERE id = :ticketId
					LIMIT 1';

            $stmn = $this->db->database->prepare($query);
            $stmn->bindValue(':status', $status, PDO::PARAM_INT);
            $stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
        }

        static::dispatch_event('ticketStatusUpdate', ['ticketId' => $ticketId, 'status' => $status, 'action' => 'ticketStatusUpdate', 'handler' => $handler]);

        return $stmn->execute();

        $stmn->closeCursor();
    }

    public function addTicketChange($userId, $ticketId, $values): void
    {
        if (empty($ticketId)) {
            return;
        }

        $fields = [
            'headline' => 'headline',
            'type' => 'type',
            'description' => 'description',
            'project' => 'projectId',
            'priority' => 'priority',
            'deadline' => 'dateToFinish',
            'editors' => 'editorId',
            'fromDate' => 'editFrom',
            'toDate' => 'editTo',
            'staging' => 'staging',
            'production' => 'production',
            'planHours' => 'planHours',
            'status' => 'status',
        ];

        $changedFields = [];

        $sql = 'SELECT * FROM zp_tickets WHERE id=:ticketId LIMIT 1';

        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);

        $stmn->execute();
        $oldValues = $stmn->fetch();
        $stmn->closeCursor();

        // compare table
        foreach ($fields as $enum => $dbTable) {
            if (
                isset($values[$dbTable]) === true &&
                isset($oldValues[$dbTable]) === true &&

                ($oldValues[$dbTable] != $values[$dbTable]) &&
                ($values[$dbTable] != '')
            ) {
                $changedFields[$enum] = $values[$dbTable];
            }
        }

        $sql = 'INSERT INTO zp_tickethistory (
					userId, ticketId, changeType, changeValue, dateModified
				) VALUES (
					:userId, :ticketId, :changeType, :changeValue, :date
				)';

        $stmn = $this->db->database->prepare($sql);

        foreach ($changedFields as $field => $value) {
            $stmn->bindValue(':userId', $userId, PDO::PARAM_INT);
            $stmn->bindValue(':ticketId', $ticketId, PDO::PARAM_INT);
            $stmn->bindValue(':changeType', $field, PDO::PARAM_STR);
            $stmn->bindValue(':changeValue', $value, PDO::PARAM_STR);
            $stmn->bindValue(':date', date('Y-m-d H:i:s'), PDO::PARAM_STR);

            $stmn->execute();
        }

        $stmn->closeCursor();
    }

    /**
     * delTicket - delete a Ticket and all dependencies
     */
    public function delticket($id): bool
    {

        $query = 'DELETE FROM zp_tickets WHERE id = :id';

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $result = $stmn->execute();
        $stmn->closeCursor();

        return $result;
    }

    /**
     * @return true
     */
    /**
     * @return true
     */
    /**
     * @return true
     */
    public function delMilestone($id): bool
    {

        $query = "UPDATE zp_tickets
                SET
                    milestoneid = ''
                WHERE milestoneid = :id";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $stmn->execute();

        $query = "UPDATE zp_canvas_items
                SET
                    milestoneid = ''
                WHERE milestoneid = :id";

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $stmn->execute();

        $query = 'DELETE FROM zp_tickets WHERE id = :id';

        $stmn = $this->db->database->prepare($query);
        $stmn->bindValue(':id', $id, PDO::PARAM_STR);
        $stmn->execute();

        return true;
    }
}
