<?php

namespace Leantime\Domain\Comments\Repositories;

use Leantime\Core\Db\Db as DbCore;
use PDO;

class Comments
{
    private DbCore $db;

    public function __construct(DbCore $db)
    {
        $this->db = $db;
    }

    public function getComments($module, $moduleId, int $parent = 0, string $orderByState = '0'): false|array
    {
        $orderBy = 'DESC';

        if ($orderByState == 1) {
            $orderBy = 'ASC';
        }

        $sql = "SELECT
					comment.id,
					comment.text,
					comment.date,
					DATE_FORMAT(comment.date, '%Y,%m,%e') AS timelineDate,
					comment.moduleId,
					comment.userId,
					comment.commentParent,
                    comment.status,
					user.firstname,
					user.lastname,
					user.profileId,
					user.modified AS userModified
				FROM zp_comment as comment
					INNER JOIN zp_user as user ON comment.userId = user.id
				WHERE moduleId = :moduleId AND module = :module";
        if ($parent >= 0) {
            $sql .= ' AND commentParent = :parent';
        }
        $sql .= ' ORDER BY comment.date '.$orderBy;

        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':module', $module, PDO::PARAM_STR);
        $stmn->bindValue(':moduleId', $moduleId, PDO::PARAM_INT);
        if ($parent >= 0) {
            $stmn->bindvalue(':parent', $parent, PDO::PARAM_INT);
        }
        $stmn->execute();
        $values = $stmn->fetchAll();
        $stmn->closeCursor();

        return $values;
    }

    /**
     * @return int|mixed
     */
    /**
     * @return int|mixed
     */
    public function countComments($module = null, $moduleId = null): mixed
    {

        $sql = 'SELECT count(id) as count
				FROM zp_comment as comment';

        if ($module != null || $moduleId != null) {
            $sql .= ' WHERE ';
            if ($module != null) {
                $sql .= 'module = :module AND ';
            }

            if ($moduleId != null) {
                $sql .= 'moduleId = :moduleId AND ';
            }

            $sql .= '1=1';
        }

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

        if ($module != null) {
            $stmn->bindValue(':module', $module, PDO::PARAM_STR);
        }

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

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

        return $values['count'] ?? 0;
    }

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

        $sql = 'SELECT
					comment.id,
					comment.text,
					comment.date,
					comment.moduleId,
					comment.userId,
					comment.commentParent,
					user.firstname,
					user.lastname,
					user.profileId,
					user.modified AS userModified
				FROM zp_comment as comment
				INNER JOIN zp_user as user ON comment.userId = user.id
				WHERE commentParent = :id';

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

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

        return $values;
    }

    public function getComment($id): void
    {

        $sql = 'SELECT
					comment.id, comment.text, comment.date, comment.moduleId, comment.userId, comment.commentParent, comment.status,
					user.firstname, user.lastname
				FROM zp_comment as comment
				INNER JOIN zp_user as user ON comment.userId = user.id
				WHERE comment.id=:id';

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

        $stmn->execute();
        $stmn->closeCursor();
    }

    public function addComment($values, $module): false|string
    {

        $sql = 'INSERT INTO zp_comment (
			text, userId, date, moduleId, module, commentParent, status
		) VALUES (:text, :userId, :date, :moduleId, :module, :commentParent, :status)';

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

        $stmn->bindValue(':moduleId', $values['moduleId'], PDO::PARAM_INT);
        $stmn->bindValue(':userId', $values['userId'], PDO::PARAM_INT);
        $stmn->bindValue(':commentParent', $values['commentParent'], PDO::PARAM_INT);
        $stmn->bindValue(':text', $values['text'], PDO::PARAM_STR);
        $stmn->bindValue(':module', $module, PDO::PARAM_STR);
        $stmn->bindValue(':date', $values['date'], PDO::PARAM_STR);
        $stmn->bindValue(':status', $values['status'] ?? '', PDO::PARAM_STR);

        $result = $stmn->execute();

        $insertId = $this->db->database->lastInsertId();

        $stmn->closeCursor();

        if ($result) {
            return $insertId;
        } else {
            return false;
        }
    }

    public function deleteComment($id): bool
    {

        $sql = 'DELETE FROM zp_comment WHERE id = :id';
        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':id', $id, PDO::PARAM_INT);

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

        return $result;
    }

    public function editComment($text, $id): bool
    {
        $sql = 'UPDATE zp_comment SET text = :text WHERE id = :id';
        $stmn = $this->db->database->prepare($sql);
        $stmn->bindValue(':id', $id, PDO::PARAM_INT);
        $stmn->bindValue(':text', $text, PDO::PARAM_STR);

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

        return $result;
    }

    public function getAllAccountComments(?int $projectId, ?int $moduleId): array|false
    {
        $sql = "SELECT comment.id,
                comment.module,
                comment.text,
                comment.date,
                comment.moduleId,
                comment.userId,
                comment.commentParent,
                comment.status,
                zp_projects.id AS projectId
            FROM zp_comment as comment
                LEFT JOIN zp_tickets ON comment.moduleId = zp_tickets.id
                LEFT JOIN zp_canvas_items ON comment.moduleId = zp_tickets.id
                LEFT JOIN zp_canvas ON zp_canvas.id = zp_canvas_items.canvasId
                LEFT JOIN zp_projects ON zp_canvas.projectId = zp_projects.id OR zp_tickets.projectId = zp_projects.id
            WHERE

                (zp_projects.id 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 (:requesterRole = 'admin' OR :requesterRole = 'manager')) ";

        if (isset($projectId) && $projectId > 0) {
            $sql .= ' AND (zp_projects.id = :projectId)';
        }

        if (isset($moduleId) && $moduleId > 0) {
            $sql .= ' AND ( comment.moduleId = :moduleId)';
        }

        $sql .= ' GROUP BY comment.id';

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

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

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

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

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

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

        return $values;
    }
}