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

class Candidates extends Core
{
    use Filter;

    function list()
    {

        $limit = intval($_GET['limit']);
        $page = intval($_GET['page']);
        $offset = $limit * $page;
        $searchs = isset($_GET['searchs']) ? $_GET['searchs'] : null;
        $keyword = $_GET['keyword'] ?? null;
        $dates_interval = $_GET['datesInterval'] ?? null;        
        $joins = "";
        $where = "WHERE account_type != 'company' AND (COALESCE(TRIM(firstname), '') != '' AND COALESCE(TRIM(email), '') != '')";
        $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";
        
        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) {
                            if ($keyword)
                                $all_keywords[] = '@' . $keyword;
                            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;
                }
            }
        }

        if ($keyword) {
            $keyword = addslashes($keyword);
            $keywords = explode(' ', $keyword);

            if (sizeof($keywords) > 1)
                list($firstname, $lastname) = $keywords;
            else
                $lastname = $firstname = $keywords[0];

            $where .= " AND ((
                    firstname LIKE '%$firstname%' OR lastname LIKE '%$lastname%' OR gender = '$keyword' 
                    OR state LIKE '%$keyword%' OR city LIKE '%$keyword%' OR district LIKE '%$keyword%')";

            
            $leads_id = $this->_getProfessionalExperiencesByKeyword($keyword);
            if (sizeof($leads_id)) {
                $in_str = implode(", ", $leads_id);
                $where .= " OR leads.id IN ($in_str)";
            }
            
            $where .= ")";
        }

        if ($dates_interval) {
            list($date_start, $date_end) = explode(':', $dates_interval);
            $where .= " AND (date_added BETWEEN '$date_start' AND '$date_end')";
        }

        $sql = "SELECT $columns FROM leads LEFT JOIN leads_curriculo ON leads_curriculo.lead_id = leads.id $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);
        
        return [
            'type' => 'OK',
            'data' => $results
        ];
    }

    function total()
    {

        $searchs = isset($_GET['searchs']) ? $_GET['searchs'] : null;
        $dates_interval = $_GET['datesInterval'] ?? null;
        $only_validated = $_GET['onlyValidated'] ?? null;
        $where = "WHERE account_type != 'company' AND (COALESCE(TRIM(firstname), '') != '' AND COALESCE(TRIM(email), '') != '')";
        $keyword = $_GET['keyword'] ?? null;
        $process_id = $_GET['processId'] ?? null;
        
        
        if ($searchs) {
            $searchs = json_decode($searchs, true);
            
            if ($searchs !== null && sizeof($searchs) > 0) {
                $additional_condition = $this->applyFilter($searchs);
                if (!empty($additional_condition)) {
                    $where .= "AND " . $additional_condition . " ";
                }
            }
        }

        if ($keyword) {
            $keyword = addslashes($keyword);
            $keywords = explode(' ', $keyword);

            if (sizeof($keywords) > 1)
                list($firstname, $lastname) = $keywords;
            else
                $lastname = $firstname = $keywords[0];

            $where .= " AND ((
                    firstname LIKE '%$firstname%' OR lastname LIKE '%$lastname%' OR gender = '$keyword' 
                    OR state LIKE '%$keyword%' OR city LIKE '%$keyword%' OR district LIKE '%$keyword%')";

            /** */
            $leads_id = $this->_getProfessionalExperiencesByKeyword($keyword);

            if (sizeof($leads_id)) {

                $in_str = implode(", ", $leads_id);
                $where .= " OR leads.id IN ($in_str) ";
            }
            /** */
            $where .= ")";
        }

        if ($dates_interval) {
            list($date_start, $date_end) = explode(':', $dates_interval);
            $where .= " AND date_added BETWEEN '$date_start' AND '$date_end'";
        }

        $sql = "SELECT count(leads.id) as records FROM leads LEFT JOIN leads_curriculo ON leads_curriculo.lead_id = leads.id $where";
        
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $user_id = $_GET['userId'] ?? null;
        if($user_id && $process_id) {
            $sql = "SELECT * FROM process_list WHERE id = :process_list";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([':process_list' => $process_id]);
            $process = $stmt->fetch(PDO::FETCH_ASSOC);
            $url = "/recruiter/process";
            
            $this->logFilter($user_id, $process_id, $url, $process['name'], $searchs);
        }
        
        return [
            'records' => $result['records'],
            'params' => $searchs
        ];
        
    }

    private function _getProfessionalExperiencesByKeyword($keyword)
    {

        $keyword = $this->unaccent($keyword);
        
        $sql = "SELECT lead_id FROM professional_experiences
            WHERE 
                description COLLATE utf8_general_ci LIKE '%$keyword%' OR
                organization COLLATE utf8_general_ci LIKE '%$keyword%' OR
                office COLLATE utf8_general_ci LIKE '%$keyword%' OR
                sinonimo COLLATE utf8_general_ci LIKE '%$keyword%';
        ";        
        
        $stmt = parent::$conn->query($sql);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return array_map(function ($item) {
            return $item['lead_id'];
        }, $results);
    }

    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);
    }

    private function _getColumns($searchs)
    {

        $filters = array(
            'jobDescription' => function ($term) {

                $term = strtolower($term);
                $sql = "SELECT sum( (LENGTH(description) - LENGTH(REPLACE(LOWER(description), '$term', ''))) / LENGTH('$term') ) FROM professional_experiences WHERE professional_experiences.lead_id = leads.id";

                return "COALESCE(($sql), 0) AS freq_term";
            }
        );
        $columns = [];

        foreach ($searchs as $field => $value) {
            if (!empty($value)) {
                if (isset($filters[$field])) {
                    $columns[] = $filters[$field]($value);
                }
            }
        }

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

    function get(){
    
        $id = $_GET['id'];
        $process_list = $_GET['processListId'] ?? null;

        $condition = $process_list
            ? "(SELECT status FROM process_list_lead AS PLL WHERE PLL.lead = leads.id AND PLL.process_list = :process_list AND PLL.status = 'pending' LIMIT 1) AS candidateStatus"
            : "NULL AS candidateStatus";

        $sql = "SELECT leads.*,
                (select professional_name FROM professional_experiences as P WHERE P.lead_id = leads.id ORDER BY final_date DESC LIMIT 1) as desired_job_title2,
                 process_list_lead.report,
                $condition FROM leads
                LEFT JOIN process_list_lead ON process_list_lead.lead = leads.id AND process_list_lead.process_list = :process_list
                WHERE leads.id = :id";
        
        $stmt = parent::$conn->prepare($sql);
        //if ($process_list)
        $stmt->execute([':process_list' => $process_list, ':id' => $id]);
        /*else
            $stmt->execute([':process_list' => $process_list, ':id' => $id]);*/

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result;
    }

    function updateVoucher()
    {

        $lead = $_POST['user'];
        $voucher = $_POST['code'];

        $sql = "SELECT A1.id FROM companies AS A1 
                LEFT JOIN process_list AS A2 ON A2.company = A1.id
                LEFT JOIN process_list_lead_actions AS A3 ON A3.process_list = A2.id
                LEFT JOIN leads AS A4 ON A4.id = A3.lead
                WHERE A1.voucher = ? AND A4.id = ?
            ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$voucher, $lead]);

        if (!$stmt->fetch(PDO::FETCH_ASSOC))
            return ['type' => 'FAIL'];

        $sql = "UPDATE leads SET voucher = ? WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$voucher, $lead]);

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

    function statuspay()
    {

        $email = $_GET['email'];

        if (!empty($email)) {

            $sql = "SELECT signature_status FROM `leads` WHERE email = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$email]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$result || $result['signature_status'] !== 'ACTIVE')
                return ['type' => 'FAIL'];

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

    function updateTerms()
    {

        $email = $_POST['email'];
        $code = $_POST['code'];
        $price = $_POST['price'];
        $acceptTerm = $_POST['acceptTerm'];
        $acceptContract = $_POST['acceptContract'];

        if (!empty($email)) {

            $sql = "UPDATE leads SET plan_signature = ? , accept_term = ? , acceptcontract = ? WHERE email = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$code, $acceptTerm, $acceptContract, $email]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

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

    function report()
    {
        $candidates = $this->list();

        foreach ($candidates as $index => $candidate) {
            $sql = "SELECT sinonimo FROM professional_experiences WHERE lead_id = :id";
            $stmt = parent::$conn->prepare($sql);
            $stmt->bindParam(':id', $candidate['id']);
            $stmt->execute();
            $processes_experiences = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $descriptions = array_map(function ($pe) {
                return $pe['sinonimo'];
            }, $processes_experiences);

            $candidates[$index]['experiences'] = implode('|', $descriptions);
        }

        return $candidates;
    }

    function getCandidatesBy()
    {

        $filter_value = $_GET['filterValue'];
        $filter_type = $_GET['filterType'];
        $keywords = $_GET['keywords'] ?? null;
        $where = " account_type != 'company'";

        switch ($filter_type) {
            case 'cpf':
                $where .= " AND cpf = ?";
                break;
            case 'phone':
                $where .= " AND phone = ?";
                break;
            case 'email':
                $where .= " AND email = ?";
                break;
        };

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

            if (strlen(($joins)))
                $sql = "SELECT *, $new_columns FROM leads $joins WHERE $where";
        } else
            $sql = "SELECT * FROM leads WHERE $where";
        
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$filter_value]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function logFilter($created_by, $insert_id, $url, $name, $searchs) {

        $title = "Filtro feito no processo seletivo \"$name\"";
        $title .= " com os seguintes filtros: ";

        if (isset($searchs['gender']) && !empty($searchs['gender']))
            $title .= "\nGênero: " . $searchs['gender'];
        if (isset($searchs['city']) && !empty($searchs['city']))
            $title .= "\nCidade: " . $searchs['city'];
        if (isset($searchs['district']) && !empty($searchs['district']))
            $title .= "\nBairro: " . $searchs['district'];
        if (isset($searchs['stateInitials']) && !empty($searchs['stateInitials']))
            $title .= "\nEstado: " . $searchs['stateInitials'];
        if (isset($searchs['educationLevel']) && !empty($searchs['educationLevel']))
            $title .= "\nNivel educacional: " . $searchs['educationLevel'];
        if (isset($searchs['ageStart']) && $searchs['ageStart'] !== '' && $searchs['ageStart'] !== null)
            $title .= "\nIdade inicial: " . $searchs['ageStart'];
        if (isset($searchs['ageEnd']) && $searchs['ageEnd'] !== '' && $searchs['ageEnd'] !== null)
            $title .= "\nIdade final: " . $searchs['ageEnd'];
        if (isset($searchs['jobDescription']) && !empty($searchs['jobDescription']))
            $title .= "\nDescrição do cargo: " . (is_array($searchs['jobDescription']) ? join(", ", $searchs['jobDescription']) : $searchs['jobDescription']);
        
        $url = "/recruiter/process";
        LogEvent::log($created_by, $insert_id, $url, $title, LogEvent::USE_FILTER);

    }

    function saveViewLog() {

        $user_id = $_POST['userId'] ?? null;
        $process_id = $_POST['processId'] ?? null;
        $lead_id = $_POST['candidateId'] ?? null;
        
        $sql = "SELECT * FROM process_list WHERE id = :process_list";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':process_list' => $process_id]);
        $result_process = $stmt->fetch(PDO::FETCH_ASSOC);

        $sql = "SELECT * FROM leads WHERE id = :lead";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':lead' => $lead_id]);
        $result_lead = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$result_process || !$result_lead) {
            return ['type' => 'FAIL', 'message' => 'Processo ou candidato não encontrado'];
        }

        $candidate_name = strtoupper($result_lead['firstname'] . ' ' . $result_lead['lastname']);                
        $title = "O candidato $candidate_name foi analisado no processo seletivo {$result_process['name']}";
        $url = "/recruiter/process";        
        
        LogEvent::log($user_id, $result_process['id'], $url, $title, LogEvent::VIEW_CANDIDATES);

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

    }

    function getCandidatesByPhone() {

        $phone = $_GET['phone'];
        $sql = "SELECT * FROM leads WHERE phone = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$phone]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

}
