<?php 
/**
 * Common Library for Sphinx single index query
 */
require_once 'Sphinx.php';
class sphinxLib extends sphinx
{
    const SPHINX_INDEX = "amazon_feed_new,connexity_feed";
    const AMAZON_SPHINX_INDEX = "amazon_feed_new";
    const CONNEXITY_SPHINX_INDEX = "connexity_feed";
    const PAGE_OFFSET = 0;
    const PAGE_LIMIT = 20;
    
    private $_faceted = ["prices", "brand", "category","availability","min_price","max_price"];
    private $_match_faceted = ["brand", "category","master_category"];
    private $_faceted_map = array('prices' => 'prices', 'brand' => 'brand', 'category' => 'category','availability'=>'availability');
    private $_faceted_expr = array(
        "price" => "FACET INTERVAL(price,0,10,100,1000,10000,100000) as prices ORDER BY price DESC",
        "brand[]" => "FACET brand ORDER BY COUNT(*) DESC LIMIT 20",
        "category[]" => "FACET category,category_display as cat ORDER BY count(*) DESC LIMIT 20",
        "availability" => "FACET availability ORDER BY count(*) DESC LIMIT 2"
    );
    
    public function __construct($config=array()) {
        parent::__construct($config);
    }

    /**
     * Sorting condition
     * @param type $sort
     * @return string
     */
    private function _sortCondition($sort) {
        $condition = " ";
        if($sort == "price"){
            $condition = " ORDER BY price asc";
        }elseif($sort == "price-desc") {
            $condition = " ORDER BY price desc";
        }elseif($sort == "random") {
            $condition = " ORDER BY rand()";
        }
        else{
            $condition = " ORDER BY final_score ASC ";
        }
        return $condition;
    }
    
    private function _createWhere($param) {        
        $filters = [];
        $minStr = "";
        foreach ($this->_faceted as $f) {
            if(in_array($f, $this->_match_faceted))
                continue;
            if (isset($param[$f]) && !empty($param[$f])) {
                switch ($f) {
                    case 'prices':
                        $start = ($param[$f]==1) ? 0 : (pow(10,$param[$f]-1));
                        $filters[] = "price BETWEEN " . $start . ' AND ' . (pow(10,$param[$f]));
                        break;
                    case 'min_price':
                        $minStr = "price BETWEEN " . $param[$f] . ' AND ';
                        break;
                    case 'max_price':
                        if(!empty($minStr))
                            $filters[] = $minStr.$param[$f];
                        break;
                    default:
                        $filters[] = "$f = " . $this->getQuote($param[$f]);
                }
            }
        }
        return $filters;
    }
    /**
     * create where match
     * @param type $param
     * @return type
     */
    private function _createMatchWhere($param) {
        $filters = [];
        foreach ($this->_match_faceted as $f) {
            if (isset($param[$f]) && !empty($param[$f])) {
                $filters[] = " @$f(\"$param[$f]\") ";
            }
        }
        return $filters;
    }
    
    /**
     * Get search data from given params
     * @param type $params array
        $params["only_amazon"] = 0/1 if it 1 then show only products from amazon feed
        $params["design_gpt"] = 0/1 if it 1 then savoid facet query
        $params["keyword"] = keyword for which against you wanna search
        $params["master_category"] = master category for which against you wanna search
        $params["category"] = catgory for which against you wanna search
        $params["brand"] = Brand for which against you wanna search
        $params["prices"] = Price Number (0-5) which indicates price range 1=>0 - 9,2=>10 -99,3=>100 - 999,4=>1000 - 9999,5=>10000-99999 and so on 
        $params["availability"] = catgory for which against you wanna search
        $params["page"] = page for which you wanna search, by default there is 20 product on 1 page
        $params["sort"] = Sort type like price ace and desc
        $params["debugSql"] = For debug sql statement
        $params["debugSqlResponse"] = For debug sql statement response
        $params["debugMatchSqlCond"] = For debug sql statement for matching words 
        $params["debugFinalResponse"] = For debug Final response send by function
     * @return type
     */
    public function getSearchData($params) {
        $index = self ::SPHINX_INDEX;

        if (isset($params['only_amazon']) && $params['only_amazon'] == 1) {
            $index = self::AMAZON_SPHINX_INDEX;
        } elseif (isset($params['only_connexity']) && $params['only_connexity'] == 1) {
            $index = self::CONNEXITY_SPHINX_INDEX;
        }

        $whereCond = $this->_createWhere($params);
        $offset = (isset($params['limit'])) ? $params['limit'] : self::PAGE_LIMIT;
        $start = (empty($params['page']) || $params['page'] == 1) ? self::PAGE_OFFSET : (($params['page']-1) * $offset);
        $sortCondition = $this->_sortCondition($params['sort']);
        $matchWhere = $this->_createMatchWhere($params);
        if(!empty($matchWhere)){
            if(empty($params['keyword'])){
                $match = $this->getQuote(implode(" ",$matchWhere));
            }else{
                $match = $this->getQuote($params['keyword'].implode(" ",$matchWhere));
            }
        }else{
            $match = (empty($params['keyword'])) ? "" : $this->getQuote($params['keyword']);
        }
        if(isset($params['design_gpt']) && $params['design_gpt']==1){
            $facet_string = '';
        }else{
            $facet_string = implode(' ', $this->_faceted_expr);
        }
        
        $condition = "";
        if(!empty($match)){
            $condition = " WHERE MATCH($match) ";
        }
        if(count($whereCond) > 0){
           $w_cond =   (empty($condition)) ? ' WHERE ' . implode(' AND ', $whereCond) : ' AND ' . implode(' AND ', $whereCond);
           $condition .= $w_cond;
        }
        
        // ADDED CUTOFF OPTION
        $cutoff_condition = "";
        if(!empty($params['cutoff'])){
            $cutoff_condition = ",cutoff=".$params['cutoff'];
        }

        $sql = "SELECT *,IF(feed_name='connexity', 1000, 0) AS final_score FROM ".$index.$condition.$sortCondition." LIMIT $start,$offset OPTION max_query_time=2000,max_matches=9999,ranker=sph04,field_weights=(category=100,merchant=90,title=50,description=1)".$cutoff_condition." " . $facet_string;
        
        if(isset($params['design_gpt']) && $params['design_gpt']==1){
            $response = $this->getArray($sql);
            return ["result"=>$response];
        }else{
            $response = $this->getDataWithFacet($sql, $this->_faceted);
        }
        
        if(empty($response['result']) && $response['metaInfo']['total_found'] <=0){
            $meta = $response['metaData'];
            $words = []; 
            foreach ($meta as $m) {
                if (preg_match('/keyword\[\d+]/', $m['Variable_name'])) {
                    preg_match('/\d+/', $m['Variable_name'], $key);
                    $key = $key[0];
                    $words[$key]['keyword'] = $m['Value'];
                }
                if (preg_match('/docs\[\d+]/', $m['Variable_name'])) {
                    preg_match('/\d+/', $m['Variable_name'], $key);
                    $key = $key[0];
                    $words[$key]['docs'] = $m['Value'];
                }
            }
            $didyoumean = false;
            $didyoumeanquery = [];
            foreach ($words as $i => $keyword) {
                if ($keyword['docs'] == 0) {
                    $indexes = $index;
                    $rows = $this->getArray("CALL SUGGEST('{$keyword['keyword']}','$indexes')");
                    if (is_array($rows) && count($rows) > 0) {
                        $keywords[$i]['keyword'] = $rows[0]['suggest'];
                        $didyoumeanquery[] = $rows[0]['suggest'];
                        $didyoumean = true;
                        break;
                    }
                }
            }
            $oldKeyword = $params['keyword'];
            $params['keyword'] = (isset($didyoumeanquery[0])) ? $didyoumeanquery[0] : $oldKeyword;
            $whereCond = $this->_createWhere($params);
            $offset = self::PAGE_LIMIT;
            $start = (empty($params['page']) || $params['page'] == 1) ? self::PAGE_OFFSET : (($params['page']-1) * self::PAGE_LIMIT);
            $sortCondition = $this->_sortCondition($params['sort']);
            $matchWhere = $this->_createMatchWhere($params);
            if(!empty($matchWhere)){
                if(empty($params['keyword'])){
                    $match = $this->getQuote(implode(" ",$matchWhere));
                }else{
                    $match = $this->getQuote($params['keyword'].implode(" ",$matchWhere));
                }
            }else{
                $match = (empty($params['keyword'])) ? "" : $this->getQuote($params['keyword']);
            }
            $facet_string = implode(' ', $this->_faceted_expr);
            $condition = "";
            if(!empty($match)){
                $condition = " WHERE MATCH($match) ";
            }
            if(count($whereCond) > 0){
               $w_cond =   (empty($condition)) ? ' WHERE ' . implode(' AND ', $whereCond) : ' AND ' . implode(' AND ', $whereCond);
               $condition .= $w_cond;
            }

            // ADDED CUTOFF OPTION
            $cutoff_condition = "";
            if(!empty($params['cutoff'])){
                $cutoff_condition = ",cutoff=".$params['cutoff'];
            }

            $sql = "SELECT *,IF(feed_name='connexity', 1000, 0) AS final_score FROM ".$index.$condition.$sortCondition." LIMIT $start,$offset OPTION  max_query_time=2000,max_matches=9999,ranker=sph04,field_weights=(category=100,merchant=90,title=50,description=1)".$cutoff_condition." " . $facet_string;
            
            $response = $this->getDataWithFacet($sql, $this->_faceted);
            $response['actual_result']=0;
            $response['old_keyword']=$oldKeyword;
            $response['new_keyword']=$params['keyword'];
        }
        
        if(!empty($response['result']) && empty($response['metaInfo'])){
            $response['metaInfo']['total_found'] = empty($response['facet']['availability'][0][1]) ? 20 : $response['facet']['availability'][0][1];
        }
        return $response;
    }
    
    public function getRelatedData($keyword) {
        $responseData = [];
        $sphinixIndex = self::SPHINX_INDEX;
        $keyword = preg_replace("/[^0-9a-zA-Z ]/","", $keyword);
        $keyword = preg_replace("/\s+/"," ", $keyword);
        $keywords = explode(" ",$keyword);
        $keywords = array_chunk($keywords,3)[0];        
        foreach ($keywords as $key => $word) {
            $sql = "CALL SUGGEST('$word','$sphinixIndex', 9 as limit)";
            $response = $this->getArray($sql);
            if(!empty($response)){
                foreach ($response as $data) {
                    if($data['distance']>0 && $data['docs'] > 10){
                        $responseData[] = $data['suggest'];
                        if(count($responseData)>7)
                            break 2;
                    }
                }
            }
        }
        return $responseData;
    }
    /**
     * Get product details by its sku id
     * @param type $id sku id of product
     * @return boolean false OR array
     */
    public function getDetail($id) {
        $data = $this->getRow("select * from ".self::SPHINX_INDEX." WHERE MATCH('@sku(\"$id\")')");
        if(!empty($data)){
            return $data;
        }else{
            return false;
        }
    }
    public function getCategories() {
        $data = $this->getArray("select category from ".self::AMAZON_SPHINX_INDEX." group by category limit 100");
        if(!empty($data)){
            return $data;
        }else{
            return false;
        }
    }
    /**
     * get products for design GPT
     */
    public function getProducts($keywords, $param = []){
        $gender_condition = '';
        $result = [];
        $index = self ::SPHINX_INDEX;
        if(!is_array($keywords)){
            $keywords = array($keywords);
        }
		if(!empty($param) && $param['gender']) {
			if($param['gender'] == 'women') {
				$gender_quote = $this->getQuote('FEMALE');
				$gender_condition = " AND (feature.gender = $gender_quote OR feature.gender = 'UNISEX')";
			} else if($param['gender'] == 'men') {
				$gender_quote = $this->getQuote('MALE');
				$gender_condition = " AND (feature.gender = $gender_quote OR feature.gender = 'UNISEX')";
			} else if($param['gender'] == 'kids') {
				$gender_quote = $this->getQuote('KIDS');
				$gender_condition = " AND feature.age_group=$gender_quote ";
			}
		}
        $offset = 1;
        $start = 0;
        foreach($keywords as $keyword){
            $match = $this->getQuote($keyword);
            $condition = " WHERE MATCH($match) ".$gender_condition;
            $sql = "SELECT * FROM ".$index.$condition." LIMIT $start,$offset OPTION max_query_time=2000,max_matches=9999,ranker=bm25,field_weights=(category=100,merchant=90,title=50,description=1)";
            $response = $this->getRow($sql);
            if(!empty($response)){
                $result[] = $response;
            }
        }   
        return ["result"=>$result];
    }

    /**
     * Get a simple list of products for a keyword ordered by relevance.
     * - Scoring: 70% sph_weight, 5% (title/description), 25% exact-phrase-in-title.
     *
     * Params:
     *   - keyword (string)         : required
     *   - limit (int)              : results count (default PAGE_LIMIT)
     *
     * Return:
     *   ['result'=>[ rows... ]]
     */
    public function getProductsByRelevance($params = [])
    {
        $keyword = isset($params['keyword']) ? trim($params['keyword']) : '';
        if ($keyword === '') {
            return ['result' => [], 'error' => 'Empty keyword'];
        }

        $index = (isset($params['only_amazon']) && $params['only_amazon'] == 1) ? self::AMAZON_SPHINX_INDEX : self::SPHINX_INDEX;
        $limit = isset($params['limit']) ? max(1, (int)$params['limit']) : self::PAGE_LIMIT;
        $page = isset($params['page']) && (int)$params['page'] > 0 ? (int)$params['page'] : 1;
        $start = ($page - 1) * $limit;

        $match = $this->getQuote($keyword);

        $sql = "SELECT *, WEIGHT() AS sph_weight FROM {$index} WHERE MATCH({$match}) LIMIT {$start},{$limit} OPTION max_query_time=500,max_matches=99,ranker=bm25,field_weights=(category=100,merchant=90,title=50,description=1)";

        $rows = $this->getArray($sql);

        if (empty($rows)) {
            return ['result' => [], 'sql' => $sql];
        }

        // compute max weight for normalization
        $maxWeight = 0.0;
        foreach ($rows as $r) {
            $w = isset($r['sph_weight']) ? (float)$r['sph_weight'] : 0.0;
            if ($w > $maxWeight) $maxWeight = $w;
        }
        if ($maxWeight <= 0) $maxWeight = 1.0;

        // prepare keyword tokens (unique lowercase)
        $keyword_clean = preg_replace("/[^0-9a-zA-Z ]+/", " ", $keyword);
        $keyword_clean = preg_replace("/\s+/", " ", trim($keyword_clean));
        $kw_tokens = array_values(array_filter(array_unique(array_map('strtolower', explode(' ', $keyword_clean)))));
        $total_kw_tokens = count($kw_tokens);
        if ($total_kw_tokens == 0) $total_kw_tokens = 1;

        // Lowercase keyword for phrase check
        $keyword_lc = strtolower($keyword);

        $scored = [];
        foreach ($rows as $r) {
            $w = isset($r['sph_weight']) ? (float)$r['sph_weight'] : 0.0;
            // Weight contribution -> up to 70 points
            $weight_score = ($w / $maxWeight) * 70.0;

            // Token overlap checks in title_string or description_string -> up to 5 points
            $title = isset($r['title_string']) ? strtolower($r['title_string']) : (isset($r['title']) ? strtolower($r['title']) : '');
            $description = isset($r['description_string']) ? strtolower($r['description_string']) : (isset($r['description']) ? strtolower($r['description']) : '');

            $matched = 0;
            foreach ($kw_tokens as $tk) {
                if ($tk === '') continue;
                if (stripos($title, $tk) !== false || stripos($description, $tk) !== false) {
                    $matched++;
                }
            }
            $token_ratio = min(1.0, $matched / $total_kw_tokens);
            $token_score = $token_ratio * 5.0;

            // Exact phrase bonus: if full keyword appears in title -> +25 points
            $phrase_bonus = 0.0;
            if ($keyword_lc !== '' && stripos($title, $keyword_lc) !== false) {
                $phrase_bonus = 25.0;
            }

            $relevance = round($weight_score + $token_score + $phrase_bonus, 2);
            if ($relevance > 100) $relevance = 100.00;
            if ($relevance < 0) $relevance = 0.00;

            $r['sph_weight'] = $w;
            $r['relevance_score'] = $relevance;
            $r['_matched_tokens'] = $matched;
            $r['_token_ratio'] = round($token_ratio, 3);
            $r['_phrase_bonus'] = $phrase_bonus;

            $scored[] = $r;
        }

        // Sort by computed relevance_score DESC; tie-break by sph_weight DESC
        usort($scored, function ($a, $b) {
            if ($b['relevance_score'] == $a['relevance_score']) {
                return ($b['sph_weight'] <=> $a['sph_weight']);
            }
            return ($b['relevance_score'] <=> $a['relevance_score']);
        });

        return ['result' => $scored, 'sql' => $sql];
    }
}