<?php
class KanbanDashboardOp extends Core
{

    private function getPostData($key, $filter = FILTER_SANITIZE_SPECIAL_CHARS)
    {
        return filter_input(INPUT_POST, $key, $filter);
        return $order;
    }

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

    function addItems()
    {

        $process_id = $this->getPostData('processId', FILTER_VALIDATE_INT);
        $step_id = $this->getPostData('stepId', FILTER_VALIDATE_INT);
        $group_id = $this->getPostData('groupId', FILTER_VALIDATE_INT);
        $group = $this->getPostData('group', FILTER_DEFAULT);
        $insert_last = $this->getPostData('insertLast', FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
        $items = $this->getPostData('items', FILTER_DEFAULT);

        if (!$process_id || !$step_id || !$items) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $sql = "SELECT * FROM kanban_dashboard WHERE process_id = :process_id AND id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':process_id' => $process_id,
            ':id' => $step_id
        ]);

        if ($stmt->rowCount() === 0) {
            return ['type' => 'Error', 'result' => 'No records found'];
        }

        $order = 0;

        if ($group)
            $group_id = $this->createGroup(json_decode($group, true), $step_id);

        if ($insert_last)
            $order = $this->getLastOrder($step_id);

        $sql = "INSERT INTO kanban_item(step_id, name, type, data, group_id, visible, `order`) 
            VALUES(:step_id, :name, :type, :data, :group_id, :visible, :order)";
        $stmt = parent::$conn->prepare($sql);
        $items = json_decode($items, true);
        foreach ($items as $index => $item) {
            $stmt->execute([
                ':step_id' => $step_id,
                ':name' => $item['name'],
                ':type' => $item['type'],
                ':data' => json_encode($item['data']),
                ':group_id' => $group_id,
                ':visible' => $group_id ? 0 : 1,
                ':order' => $order + $index
            ]);
        }

        $lastInsertId = parent::$conn->lastInsertId();

        return [
            'type' => 'OK',
            'result' => $this->getById($lastInsertId)
        ];
    }


    function moveToGroup()
    {

        $id = $this->getPostData('id', FILTER_VALIDATE_INT);
        $group_id = $this->getPostData('groupId', FILTER_VALIDATE_INT);

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

        $group = $this->getById($group_id);
        $sql = "UPDATE kanban_item SET visible = 0, step_id = :step_id, group_id = :group_id, 
            order_group = 999, `order` = 999 WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':step_id' => $group['step_id'],
            ':group_id' => $group_id,
            ':id' => $id
        ]);

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

    private function createGroup($group, $step_id)
    {

        $sql = "INSERT INTO kanban_item(step_id, name, type, visible) VALUES(:step_id, :name, :type, :visible)";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':step_id' => $step_id,
            ':name' => $group['name'],
            ':type' => 'group',
            ':visible' => 1
        ]);

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

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

        return $result;
    }

    private function getLastOrder($step_id)
    {

        $sql = "SELECT `order` FROM kanban_item WHERE step_id = :step_id ORDER BY `order` DESC LIMIT 1";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':step_id' => $step_id]);
        $order = (int) $stmt->fetchColumn() + 1;

        return $order;
    }

    function moveStep()
    {

        $orders = $this->getPostData('orders', FILTER_DEFAULT);

        if (!$orders) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $orders = json_decode($orders, true);
        $sql = "UPDATE kanban_dashboard SET `order` = :order WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);

        foreach ($orders as $order) {
            $id = $order['id'];
            $order = $order['order'];

            $stmt->execute([
                ':id' => $id,
                ':order' => $order
            ]);
        }

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

    function addToDefaultProcessStep()
    {

        $user_ids = $this->getPostData('userIds');
        $process_id = $this->getPostData('processId', FILTER_VALIDATE_INT);

        if (!$user_ids || !$process_id) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $sql = "SELECT id FROM kanban_dashboard WHERE process_id = :process_id AND `default` = 1";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':process_id' => $process_id
        ]);

        if ($stmt->rowCount() === 0) {
            $sql = "INSERT INTO kanban_dashboard(process_id, name, `order`, `default`) 
                VALUES(:process_id, 'Candidatos selecionados', -1, 1)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([':process_id' => $process_id]);
            $kanban_id = parent::$conn->lastInsertId();
        } else {
            $kanban_id = $stmt->fetchColumn();
        }

        $user_ids = json_decode($user_ids);
        
        foreach ($user_ids as $user_id) {

            if($this->verifyAlreadyExistsItem($user_id, $process_id))
                break;            
            
            $sql = "SELECT * FROM leads WHERE id = :id";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([':id' => $user_id]);
            $lead = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$lead) {
                return ['type' => 'ERROR', 'message' => 'Lead not found'];
            }

            $sql = "INSERT INTO kanban_item(step_id, name, `order`, data) 
                VALUES(:step_id, :name, 0, :data)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':step_id' => $kanban_id,
                ':name' => strtoupper($lead['firstname']),
                ':data' => json_encode($lead),
            ]);
        }

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

    private function verifyAlreadyExistsItem($user_id, $process_id) {

        $sql = "SELECT A.* FROM kanban_item A 
            LEFT JOIN kanban_dashboard B ON A.step_id = B.id 
            WHERE process_id = :process_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':process_id' => $process_id]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach($results as $index=>$item) {
            
            $data = json_decode($item['data'], true);            
            if($data['id'] == $user_id) {
                return true;
            }
        }

        return false;

    }

    function migrateOrCreateCards() {

        $process_id = $_GET['processId'];
        
        $sql = "SELECT * FROM process_list_lead WHERE kanban_step_id IS NOT NULL AND process_list = :process_list";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([ ':process_list' => $process_id ]);

        if($stmt->rowCount() > 0) {
            return ['type' => 'OK'];
        }        

        $sql = "SELECT K.* FROM kanban_item AS K 
            LEFT JOIN kanban_dashboard AS KD ON KD.id = K.step_id
            WHERE KD.process_id = :process_id";

        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':process_id' => $process_id]);
        $cards = $stmt->fetchAll();

        if($cards && sizeof($cards)) {

            $sql_update_lead = "UPDATE process_list_lead SET kanban_step_id = :step_id, kanban_item_order = :order  WHERE lead = :lead_id";

            foreach($cards as $index=>$card) {

                $lead = json_decode($card['data'], true);
                $lead_id = $lead['id'];
                // file_put_contents("LEAD.{$index}.txt", print_r($lead, true));

                $stmt_update_lead = parent::$conn->prepare($sql_update_lead);
                $stmt_update_lead->execute([
                    ':lead_id' => $lead_id,
                    ':step_id' => $card['step_id'],
                    ':order' => $card['order']
                ]);

            }

        } else {

            $sql = "INSERT INTO kanban_dashboard(process_id, name, `order`, `default`) 
                VALUES(:process_id, 'Candidatos selecionados', -1, 1)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([':process_id' => $process_id]);
            $kanban_id = parent::$conn->lastInsertId();

            $sql_update_lead = "UPDATE process_list_lead SET kanban_step_id = :step_id WHERE process_list = :process_list";
            $stmt_update_lead = parent::$conn->prepare($sql_update_lead);
            $stmt_update_lead->execute([                
                ':step_id' => $card['step_id'],
                ':process_list' => $process_id
            ]);
            
        }

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

    }
}
