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

class Vacancy extends Core
{

    private  $stopwords = [
        "a",
        "à",
        "ao",
        "aos",
        "às",
        "as",
        "até",
        "com",
        "como",
        "da",
        "das",
        "de",
        "dei",
        "do",
        "dos",
        "e",
        "em",
        "entre",
        "era",
        "eram",
        "é",
        "foi",
        "fui",
        "há",
        "já",
        "lá",
        "mas",
        "me",
        "meu",
        "minha",
        "na",
        "nas",
        "não",
        "nem",
        "no",
        "nos",
        "nós",
        "o",
        "os",
        "ou",
        "para",
        "pela",
        "pelas",
        "pelo",
        "pelos",
        "por",
        "qual",
        "quais",
        "que",
        "quem",
        "se",
        "seja",
        "sem",
        "seu",
        "sua",
        "são",
        "só",
        "sobre",
        "sou",
        "sua",
        "suas",
        "também",
        "tem",
        "têm",
        "um",
        "uma",
        "umas",
        "uns",
        "vou"
    ];

    function getSimilars()
    {

        $limit = 10;
        $id = $_GET['id'];
        $page_index = $_GET['pageIndex'];
        $all_vacancies = [];
        $offset = intval($page_index) * $limit;
        $sql = "SELECT sinonimo FROM professional_experiences WHERE lead_id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id]);

        list($score_join, $score_columns) = $this->getScoreJoinByUserId($id);

        $sql = "SELECT process_list.*, $score_columns FROM process_list $score_join
            WHERE LOWER(sinonimo) = LOWER(:text) AND process_list.status NOT IN('finished', 'archived') ORDER BY score DESC
            LIMIT $offset, $limit";

        while ($pe = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $stmt2 = parent::$conn->prepare($sql);
            $stmt2->execute([':text' => $pe['sinonimo']]);
            $vacancies = $stmt2->fetchAll(PDO::FETCH_ASSOC);
            $all_vacancies = array_merge($all_vacancies, $vacancies);
        }

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

    private function getScoreJoinByUserId($user_id)
    {

        $sql = "SELECT desired_job_title FROM leads WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$user_id]);
        $lead = $stmt->fetch(PDO::FETCH_ASSOC);
        $desired_job = $lead['desired_job_title'];
        $terms = explode(" ", $desired_job);
        $columns = [];

        foreach ($terms as $index => $term) {
            if (in_array($term, $this->stopwords))
                continue;

            $alias_column = "score_term_{$index}";
            $alias_join = "score_{$index}";

            $joins[] = "
                    LEFT JOIN (
                        SELECT 
                            id,
                            SUM(
                                COALESCE((LENGTH(sinonimo) - LENGTH(REPLACE(LOWER(sinonimo), '$term', ''))) / LENGTH('$term'), 0) +
                                COALESCE((LENGTH(activities) - LENGTH(REPLACE(LOWER(activities), '$term', ''))) / LENGTH('$term'), 0)
                            ) AS $alias_column
                        FROM process_list 
                        GROUP BY id
                    ) AS $alias_join ON {$alias_join}.id = process_list.id
                ";

            $columns[] = "COALESCE({$alias_join}.{$alias_column}, 0)";
        }

        return [
            implode(" ", $joins),
            implode(" + ", $columns) . " AS score"
        ];
    }
}
