<?php

class KanbanCrmDashboard extends Core
{
    private function getPostData($key, $filter = FILTER_SANITIZE_SPECIAL_CHARS)
    {
        return filter_input(INPUT_POST, $key, $filter);
    }

    private function getGetData($key, $filter = FILTER_SANITIZE_SPECIAL_CHARS)
    {
        return filter_input(INPUT_GET, $key, $filter);
    }

    function store()
    {        
        $name = $this->getPostData('name');
        $color = $this->getPostData('color');
        $order = $this->getPostData('order', FILTER_VALIDATE_INT);

        if (!$name) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }
        
        if (is_null($order)) {
            $sql = "SELECT `order` FROM kanban_crm ORDER BY `order` DESC LIMIT 1";
            $stmt = parent::$conn->query($sql);
        }

        $sql = "INSERT INTO kanban_crm (name, color, `order`) VALUES (:name, :color, :order)";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':color' => $color,
            ':order' => $order
        ]);

        $lastInsertId = parent::$conn->lastInsertId();
        return $this->getById($lastInsertId);
    }

    function update()
    {
        $id = $this->getPostData('id', FILTER_VALIDATE_INT);        
        $name = $this->getPostData('name');
        $color = $this->getPostData('color');
        $order = $this->getPostData('order', FILTER_VALIDATE_INT);
        $archived = $this->getPostData('archived', FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);

        if (!$id || !$name || $order === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $sql = "UPDATE kanban_crm SET name = :name, color = :color, `order` = :order, archived = :archived WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':color' => $color,
            ':order' => $order,
            ':archived' => $archived,
            ':id' => $id
        ]);

        return $this->getById($id);
    }

    function list()
    {

        $sql = "SELECT * FROM kanban_crm ORDER BY `order`";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return ['type' => 'OK', 'result' => $result];
    }

    function delete()
    {
        $id = $this->getGetData('id', FILTER_VALIDATE_INT);
        if (!$id) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $sql = "DELETE FROM kanban_crm WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id]);

        return ['type' => 'OK'];
    }

    private function getById($id)
    {
        $sql = "SELECT * FROM kanban_crm WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result ? ['type' => 'OK', 'result' => $result] : ['type' => 'ERROR', 'message' => 'Record not found'];
    }

    function listAllItems() {

        $sql = "SELECT KI.id, KI.data, KD.name, KD.order FROM kanban_item AS KI
            LEFT JOIN kanban_crm AS KD ON KD.id = KI.step_id
            WHERE KI.type = 'item' ORDER BY KD.order ASC";
        
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $sql = "SELECT count(*) AS total, id, `name` FROM kanban_crm GROUP BY id LIMIT 10";
        $stmt = parent::$conn->query($sql);
        $result_total = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return [
            'type' => 'OK', 
            'data' =>  [
                'steps' => $result_total,
                'items' => $result
            ]
        ];

    }

}
