<?php

class Search 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 list()
    {

        $search_term = $_GET['searchTerm'] ?? null;
        $page_index = $_GET['pageIndex'] ?? 0;
        $page_size = $_GET['pageSize'] ?? 10;
        $lead_id = $_GET['userId'];
        $offset = intval($page_index) * intval($page_size);
        list($score_join, $score_columns) = $this->_getScoreJoin($lead_id);

        $terms = explode(" ", $search_term);
        $query = "SELECT process_list.*, $score_columns FROM process_list $score_join WHERE status IN ('read', 'new') AND (";
        $conditions = [];

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

            $quoted_term = parent::$conn->quote("%$term%");
            $conditions[] = "(sinonimo LIKE $quoted_term
                    OR name LIKE $quoted_term
                    OR requirements LIKE $quoted_term
                    OR activities LIKE $quoted_term
                    OR benefits LIKE $quoted_term)";
        }

        $query .= join("AND", $conditions);
        $query .= ") ORDER BY score DESC, created_at DESC LIMIT $offset, $page_size";

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

    private function _getScoreJoin($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);
        $join = [];
        $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"
        ];
    }

    function listTotal()
    {

        $search_term = $_GET['searchTerm'] ?? null;

        $terms = explode(" ", $search_term);
        $query = "SELECT count(*) as total FROM process_list WHERE (";
        $conditions = [];

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

            $quoted_term = parent::$conn->quote("%$term%");
            $conditions[] = "(sinonimo LIKE $quoted_term
                    OR name LIKE $quoted_term
                    OR requirements LIKE $quoted_term
                    OR activities LIKE $quoted_term
                    OR benefits LIKE $quoted_term)";
        }

        $query .= join("AND", $conditions);
        $query .= ")";

        $stmt = parent::$conn->query($query);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return ['type' => 'OK', 'data' => $result];
    }
}
