<?php
class KanbanItem extends Core
{

    function list()
    {
        $step_id = filter_input(INPUT_GET, 'stepId', FILTER_VALIDATE_INT);
        $group_id = filter_input(INPUT_GET, 'groupId', FILTER_VALIDATE_INT);
        $process_id = filter_input(INPUT_GET, 'processId', FILTER_VALIDATE_INT);

        if ($step_id === null || $step_id === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid or missing step ID'];
        }

        if ($group_id !== null && $group_id === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid group ID'];
        }

        if ($process_id !== null && $process_id === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid process ID'];
        }

        $sql = "SELECT A2.*, 
                A1.status AS candidateStatus, 
                A1.id AS id2,
                A1.score,
                A1.keywords,                    
                (SELECT sum(points) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead GROUP BY A4.lead) AS points,
                (SELECT count(status) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND status IN('evaluated') LIMIT 1) AS status,
                (SELECT count(*) FROM process_list_test_response AS A5 WHERE lead = A1.lead AND A5.process_list = :process_list) as responses_total,
                (SELECT count(*) FROM process_list_test_response AS A6 WHERE lead = A1.lead AND A6.process_list = :process_list AND A6.approved = 1) as responses_approved_total,
                (SELECT A4.accept FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview,
                (SELECT A4.status FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview_status,
                (SELECT count(*) FROM fit_cultural AS A8 WHERE A8.email_fit = A2.email) as responses_fit,
                (SELECT count(A4.view) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND view = '0') AS accept_view_interview,
                (SELECT count(A9.view) FROM process_list_test AS A9 WHERE process_list = :process_list AND A9.lead = A1.lead AND view = '0') AS accept_view_test,
                IFNULL((SELECT COALESCE(SUM(points), 0) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead GROUP BY A4.lead), 0) + IFNULL(A1.score, 0) AS total_score
            FROM process_list_lead as A1
            LEFT JOIN leads as A2 ON A1.lead = A2.id                
            WHERE A1.kanban_step_id = :step_id AND A1.status NOT IN ('pre-approval', 'disabled')
            ORDER BY kanban_item_order
        ";

        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':step_id' => $step_id,
            ':process_list' => $process_id
        ]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function listByGroup($group_id, $step_id)
    {
        $sql = "SELECT data FROM kanban_item WHERE group_id = :group_id AND step_id = :step_id ORDER BY `order`";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':group_id' => $group_id,
            ':step_id' => $step_id
        ]);
        $result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

        return $result;
    }

    function save()
    {
        $data = filter_input_array(INPUT_POST, [
            'stepId' => FILTER_VALIDATE_INT,
            'name' => FILTER_SANITIZE_SPECIAL_CHARS,
            'order' => FILTER_VALIDATE_INT,
            'data' => FILTER_DEFAULT,
            'type' => FILTER_SANITIZE_SPECIAL_CHARS,
            'visible' => FILTER_VALIDATE_BOOLEAN
        ]);
        
        if ($data['stepId'] === false || $data['name'] === false || $data['type'] === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $step_id = $data['stepId'];
        $name = $data['name'];
        $order = $data['order'] ?? 1;
        $json = isset($data['data']) ? json_encode($data['data']) : null;
        $type = $data['type'];
        $visible = $data['visible'] ?? true;

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

        return [
            'type' => 'OK',
            'id' => parent::$conn->lastInsertId()
        ];
    }

    function update()
    {
        $id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
        $data = filter_input_array(INPUT_POST, [
            'stepId' => FILTER_VALIDATE_INT,
            'name' => FILTER_SANITIZE_SPECIAL_CHARS,
            'order' => FILTER_VALIDATE_INT,
            'type' => FILTER_SANITIZE_SPECIAL_CHARS,
            'visible' => FILTER_VALIDATE_BOOLEAN
        ]);

        if ($id === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid ID'];
        }

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

        $sql = "UPDATE kanban_item SET step_id = :step_id, name = :name, `order` = :order, type = :type, visible = :visible WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':step_id' => $data['stepId'],
            ':name' => $data['name'],
            ':order' => $data['order'],
            ':type' => $data['type'],
            ':visible' => $data['visible'],
            ':id' => $id
        ]);

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

    function delete()
    {
        $id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
        if ($id === false) {
            return ['type' => 'ERROR', 'message' => 'Invalid ID'];
        }

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

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

    function reorderCards()
    {

        $orders = filter_input(INPUT_POST, 'orders', FILTER_DEFAULT);
        $step_id = filter_input(INPUT_POST, 'stepId', FILTER_VALIDATE_INT);        
        
        if ($orders === null || $step_id === null) {
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];
        }

        $sql = "UPDATE process_list_lead SET kanban_item_order = :order, kanban_step_id = :step_id WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $orders = json_decode($orders, true);
        
        foreach ($orders as $order) {
            $stmt->execute([
                ':id' => $order['id'],
                ':order' => $order['order'],
                ':step_id' => $step_id
            ]);
        }

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

    // function reorderCards()
    // {

    //     $orders = filter_input(INPUT_POST, 'orders', FILTER_DEFAULT);
    //     $step_id = filter_input(INPUT_POST, 'stepId', FILTER_VALIDATE_INT);
    //     $group_id = filter_input(INPUT_POST, 'groupId', FILTER_VALIDATE_INT);
    //     $remove_from_group = filter_input(INPUT_POST, 'removeFromGroup', FILTER_VALIDATE_BOOLEAN);

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

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

    //     foreach ($orders as $order) {
    //         $stmt->execute([
    //             ':id' => $order['id'],
    //             ':order' => $order['order'],
    //             ':step_id' => $step_id
    //         ]);

    //         if ($remove_from_group)
    //             $this->removeFromGroup($order['id']);
    //     }

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

    function removeFromGroup($id)
    {
        $sql = "UPDATE kanban_item SET group_id = NULL, visible = 1 WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id]);
    }

    function listGroup()
    {

        $process_id = filter_input(INPUT_GET, 'processId', FILTER_VALIDATE_INT);

        if ($process_id === null)
            return ['type' => 'ERROR', 'message' => 'Invalid input data'];

        $sql = "SELECT A.*, B.name as step_name FROM kanban_item A 
            LEFT JOIN kanban_dashboard as B ON B.id = A.step_id
            WHERE A.type = 'group' AND process_id = :process_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':process_id' => $process_id
        ]);

        return [
            'type' => 'OK',
            'result' => $stmt->fetchAll(PDO::FETCH_ASSOC)
        ];
    }

    // private function get_lead($process_id, $id)
    // {

        
    //     $sql = "SELECT A2.*, 
    //             A1.status AS candidateStatus, 
    //             A1.id AS id2,
    //             A1.score,
    //             A1.keywords,                    
    //             (SELECT sum(points) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead GROUP BY A4.lead) AS points,
    //             (SELECT count(status) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND status IN('evaluated') LIMIT 1) AS status,
    //             (SELECT count(*) FROM process_list_test_response AS A5 WHERE lead = A1.lead AND A5.process_list = :process_list) as responses_total,
    //             (SELECT count(*) FROM process_list_test_response AS A6 WHERE lead = A1.lead AND A6.process_list = :process_list AND A6.approved = 1) as responses_approved_total,
    //             (SELECT A4.accept FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview,
    //             (SELECT A4.status FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview_status,
    //             (SELECT count(*) FROM fit_cultural AS A8 WHERE A8.email_fit = A2.email) as responses_fit,
    //             (SELECT count(A4.view) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND view = '0') AS accept_view_interview,
    //             (SELECT count(A9.view) FROM process_list_test AS A9 WHERE process_list = :process_list AND A9.lead = A1.lead AND view = '0') AS accept_view_test,
    //             IFNULL((SELECT COALESCE(SUM(points), 0) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead GROUP BY A4.lead), 0) + IFNULL(A1.score, 0) AS total_score
    //         FROM process_list_lead as A1
    //         LEFT JOIN leads as A2 ON A1.lead = A2.id                
    //         WHERE A1.process_list = :process_list AND A2.id = :id AND A1.status NOT IN ('pre-approval', 'disabled')
    //     ";

    //     $stmt = parent::$conn->prepare($sql);
    //     $stmt->execute([
    //         ':id' => $id,
    //         ':process_list' => $process_id
    //     ]);
    //     $lead = $stmt->fetch(PDO::FETCH_ASSOC);
        
    //     return $lead;
    // }
}
