<?php
include_once __dir__ .  '/../core/Filter.php';
include_once __dir__ . '/../core/LogEvent.php';

class ProcessListLeadSuggestion extends Core {

    use Filter;

    function createSuggestion() {

        $limit = 20;
        $page = 0;
        $offset = $limit * $page;
        $process_id = $_POST['processId'];

        $removed_leads = $this->getRemovedSuggestion($process_id);
        $removed_ids = [];
        
        foreach ($removed_leads as $lead) {
            $removed_ids[] = $lead['lead_id'];
        }

        $candidates = $this->getCandidates($process_id);        
        $ids = $candidates ? array_column($candidates, 'lead') : [];        
        $removed_ids = array_merge($removed_ids, $ids);
        
        if (sizeof($removed_ids) > 0) {
            $removed_ids_str = implode(',', $removed_ids);
            $where_removed = " AND leads.id NOT IN ($removed_ids_str) ";
        } else {
            $where_removed = "";
        }

        $searchs = isset($_POST['searchs']) ? $_POST['searchs'] : null;
        $where = "WHERE account_type != 'company' AND COALESCE(TRIM(firstname), '') != '' AND COALESCE(TRIM(email), '') != '' $where_removed ";
        $columns = 'leads.id, leads.firstname, leads.lastname, leads.gender, leads.city, leads.district, leads.state, leads.email, leads.address1, leads.address2, leads.phone';
        $order_by = "ORDER BY firstname ASC";
        $joins = "";
        
        if ($searchs) {
            $searchs = json_decode($searchs, true);
            
            if ($searchs !== null) {
                if (sizeof($searchs) > 0) {

                    $filterss = $this->applyFilter($searchs);
                    if (!empty($filterss)) {
                        $where .= "AND " . $filterss;
                    }

                    $new_order_by = $this->_getOrderBy($searchs);

                    if (isset($searchs['jobDescription'])) {

                        $all_keywords = (array) $searchs['jobDescription'];

                        if ($all_keywords)
                            list($joins, $new_columns) = $this->getScoreJoin($all_keywords);                        

                        if (isset($new_columns)) {
                            $columns .= ',' . $new_columns;
                            $order_by = ' ORDER BY score DESC';
                        }
                    }

                    if (strlen($new_order_by))
                        $order_by = ' ORDER BY ' . $new_order_by;
                }
            }
        }

        $sql = "SELECT $columns FROM leads $joins $where $order_by LIMIT :offset, :limit";
        
        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $sql = "INSERT IGNORE INTO process_list_lead_suggestion(process_id, lead_id, `data`) VALUES(:process_id, :lead_id, :json_data)";
        
        foreach($results as $index=>$lead) {
            $json = json_encode($lead);
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                'process_id' => $process_id,
                'lead_id' => $lead['id'],
                'json_data' => $json
            ]);
        }

        return ['type' => 'OK', 'total' => sizeof($results)];

    }

    private function getRemovedSuggestion($process_id) {
        $sql = "SELECT lead_id FROM process_list_lead_suggestion WHERE removedAt IS NOT NULL AND process_id = :process_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute(['process_id' => $process_id]);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function _getOrderBy($searchs)
    {

        $filters = array(
            'score' => function ($dir) use ($searchs) {
                if (sizeof($searchs['jobDescription']) > 0) {
                    $sql = "score $dir";
                    return $sql;
                }
            },
            'district' => function ($dir) {
                $sql = "district $dir";
                return $sql;
            },
            'city' => function ($dir) {
                $sql = "city $dir";
                return $sql;
            },
            'state' => function ($dir) {
                $sql = "state $dir";
                return $sql;
            },
            'gender' => function ($dir) {
                $sql = "gender $dir";
                return $sql;
            },
            'firstname' => function ($dir) {
                $sql = "firstname $dir";
                return $sql;
            }
        );

        $orders = [];
        $order_by = isset($searchs['orderBy']) ? $searchs['orderBy'] : [];

        foreach ($order_by as $field => $value) {
            if (isset($filters[$field]))
                if (!empty($value)) {
                    $order_str = $filters[$field]($value);

                    if ($order_str)
                        $orders[] = $order_str;
                }
        }

        return implode(', ', $orders);
    }

    function getSuggestion() {

        $process_id = $_GET['processId'];
        $user_id = isset($_GET['userId']) ? $_GET['userId'] : null;
        $sql = "SELECT * FROM process_list_lead_suggestion WHERE process_id = :process_id AND removedAt IS NULL";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute(['process_id' => $process_id]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if($user_id) {

            $url = '';
            $title = 'Viu os candidatos sugeridos!';
            LogEvent::log($user_id, $process_id, $url, $title, LogEvent::VIEW_SUGGESTION_CANDIDATES);

        }

        return ['data' => $results];
        
    }

    function deleteSuggestion() {

        $process_id = $_GET['processId'];
        $id = $_GET['id'];
        
        $sql = "UPDATE process_list_lead_suggestion SET removedAt = NOW() WHERE process_id = :process_id AND id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            'process_id' => $process_id,
            'id' => $id
        ]);
        return ['type' => 'OK'];

    }

    function clearSuggestions() {        

        $process_id = $_POST['processId'];
        $sql = "DELETE FROM process_list_lead_suggestion WHERE process_id = :process_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            'process_id' => $process_id
        ]);
        return ['type' => 'OK'];

    }

    private function getCandidates($process_id) {

        $sql = "SELECT lead FROM process_list_lead WHERE process_list = :process_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute(['process_id' => $process_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }

}