<?php

use Journal3\Utils\Arr;

class ModelJournal3Filter extends Model {
    private $redis_conn = null;
    private static $search_results_cache = [];

// OLMASI GEREKEN
private function getRedis() {
        if ($this->redis_conn !== null) return $this->redis_conn;
        
        if (class_exists('Redis')) {
            try {
                $this->redis_conn = new Redis();
                
                // Config dosyasındaki tanımları kullanıyoruz
                $host = defined('REDIS_HOSTNAME') ? REDIS_HOSTNAME : '127.0.0.1';
                $port = defined('REDIS_PORT') ? REDIS_PORT : 6379;
                $pass = defined('REDIS_PASSWORD') ? REDIS_PASSWORD : '';

                $this->redis_conn->connect($host, $port);
                
                // Eğer bir şifre tanımlanmışsa Auth yap
                if ($pass) {
                    $this->redis_conn->auth($pass);
                }
                
                return $this->redis_conn;
            } catch (Exception $e) { 
                return null; 
            }
        }
        return null;
    }

    // --- URL OLUŞTURUCU ---
    public function getFilterUrl() {
        switch ($this->journal3_request->get('route')) {
            case 'product/catalog': return $this->journal3_url->link('product/catalog', '', true);
            case 'product/category': return $this->journal3_url->link('product/category', 'path=' . $this->journal3_request->get('path'), true);
            case 'product/search':
                $url = '';
                if (!empty($this->request->get['search'])) $url .= '&search=' . urlencode(html_entity_decode($this->request->get['search'], ENT_QUOTES, 'UTF-8'));
                return $this->journal3_url->link('product/search', $url, true);
            default: return null;
        }
    }

    // --- FİLTRE VERİLERİNİ URL'DEN OKU ---
    public function getFilterData() {
        $separator = $this->journal3->get('filterUrlValuesSeparator') ?: ',';
        $filter_data = ['is_filter' => true];
        
        if (isset($this->request->get['threed'])) {
            $filter_data['threed'] = (int)$this->request->get['threed'];
        }
		
		
		// *** KAUTEK: date_added filtresi URL'den oku ***
		if (isset($this->request->get['fda']) && $this->request->get['fda'] !== '') {
			$allowed_values = ['1w', '1m', '3m', '6m', '1y'];
			if (in_array($this->request->get['fda'], $allowed_values)) {
				$filter_data['date_added_range'] = $this->request->get['fda'];
			}
		}		
        
        foreach ($this->request->get as $k => $v) {
            if (empty($k) || $k[0] !== 'f') continue;
            switch ($k) {
                case 'fc': $filter_data['categories'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_cat':   $filter_data['vehicle']['v_cat'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_tb':    $filter_data['vehicle']['wTb'] = explode($separator, $v); break;
                case 'fv_marka': $filter_data['vehicle']['wmarka_id'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_model': $filter_data['vehicle']['wmodel1_id'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_yil':   $filter_data['vehicle']['yil'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_yakit': $filter_data['vehicle']['wyakit_id'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_kasa':  $filter_data['vehicle']['wkasat_id'] = array_map('intval', explode($separator, $v)); break;
                case 'fv_motor': $filter_data['vehicle']['wmotor2_id'] = array_map('intval', explode($separator, $v)); break;
                default:
                    if (substr($k, 0, 2) === 'fa') $filter_data['attributes'][(int)substr($k, 2)] = explode($separator, $v);
                    if (substr($k, 0, 2) === 'ff') $filter_data['filters'][(int)substr($k, 2)] = explode($separator, $v);
            }
        }
        $route = $this->journal3_request->get('route');
        if ($route === 'product/category') {
            $path = explode('_', (string)$this->journal3_request->get('path', ''));
            $filter_data['filter_category_id'] = (int)array_pop($path);
        } elseif ($route === 'product/search') {
            $filter_data['filter_name'] = $this->journal3_request->get('search', '');
            $filter_data['filter_description'] = $this->journal3_request->get('description', '');
            $filter_data['filter_category_id'] = $this->journal3_request->get('category_id', '');
            $filter_data['filter_sub_category'] = $this->journal3_request->get('sub_category', '');
        }
        // Varsayılan sıralamayı 'random' yaptık
        $filter_data['sort'] = !empty($this->request->get['sort']) ? $this->request->get['sort'] : 'p.date_added';
        $filter_data['order'] = !empty($this->request->get['order']) ? $this->request->get['order'] : 'DESC';
        $page = !empty($this->request->get['page']) ? max(1, (int)$this->request->get['page']) : 1;
        $limit = !empty($this->request->get['limit']) ? max(1, (int)$this->request->get['limit']) : max(1, (int)$this->config->get('theme_journal3_product_limit'));
        $filter_data['start'] = ($page - 1) * $limit;
        $filter_data['limit'] = $limit;
        return $filter_data;
    }

    public function getFilterParams($filter_data) {
        $separator = $this->journal3->get('filterUrlValuesSeparator') ?: ',';
        $result = [];
        foreach ($filter_data as $k => $v) {
            switch ($k) {
                case 'threed': 
                    if ($v) $result['threed'] = $v; 
                    break;
				case 'date_added_range':
					if ($v) $result['fda'] = $v;
					break;	
                case 'categories': $result['fc'] = implode($separator, $v); break;
                case 'vehicle':
                    foreach ($v as $vk => $vv) {
                        $key = 'fv_';
                        $key .= strtolower(str_replace(['w', 'v_', '_id'], '', $vk));
                        if ($vk == 'wmodel1_id') $key = 'fv_model';
                        if ($vk == 'wkasat_id')  $key = 'fv_kasa';
                        if ($vk == 'wmotor2_id') $key = 'fv_motor';
                        $result[$key] = implode($separator, (array)$vv);
                    }
                    break;
                case 'attributes': foreach ($v as $kk => $vv) $result['fa' . $kk] = implode($separator, $vv); break;
                case 'filters': foreach ($v as $kk => $vv) $result['ff' . $kk] = implode($separator, $vv); break;
            }
        }
        return $result ? http_build_query($result) : null;
    }

public function getCustomCategoriesTree($data) {
        $redis = $this->getRedis();
        $cache_key = 'j3:cats_tree_ctx:v30:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($data) . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);

        $sql = "SELECT c.category_id as id, c.parent_id, cd.name as value, COUNT(DISTINCT p.product_id) as total FROM " . DB_PREFIX . "category c JOIN " . DB_PREFIX . "category_description cd ON c.category_id = cd.category_id JOIN " . DB_PREFIX . "product_to_category p2c ON c.category_id = p2c.category_id JOIN " . DB_PREFIX . "product p ON p2c.product_id = p.product_id ";
        
        if (!empty($data['filter_category_id'])) {
            $sql .= " JOIN " . DB_PREFIX . "product_to_category p2c_context ON (p.product_id = p2c_context.product_id AND p2c_context.category_id = '" . (int)$data['filter_category_id'] . "') ";
        }

        $temp = $data; 
        unset($temp['vehicle']['v_cat']); 
        $sql .= $this->addFilters($temp, 'category', null, 'p', 'vm_cat', 'p2v_cat');
        
        $sql .= " AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c.status = '1' GROUP BY c.category_id HAVING total > 0 ORDER BY c.sort_order, cd.name ASC";
        
        $rows = $this->db->query($sql)->rows;
        $tree = [];
        $references = [];
        
        foreach ($rows as $row) {
            $id = $row['id'];
            $references[$id] = $row;
            $references[$id]['items'] = [];
        }
        
        foreach ($references as $id => &$node) {
            $parent_id = $node['parent_id'];
            if ($parent_id == 0 || !isset($references[$parent_id])) {
                $tree[$id] = &$node;
            } else {
                $references[$parent_id]['items'][] = &$node;
            }
        }
        
        if ($redis) $redis->set($cache_key, json_encode($tree));
        return $tree;
    }

    public function getVehicleValues($data, $column) {
        $redis = $this->getRedis(); 
        $cache_key = 'j3:veh_val:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($data) . $column . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);
        
        $map = ['wmarka_id' => ['table' => 'lrv_vehicle_wmarka', 'name' => 'wmarka_name', 'id' => 'wmarka_id'], 'wmodel1_id' => ['table' => 'lrv_vehicle_wmodel1', 'name' => 'wmodel_name', 'id' => 'wmodel1_id'], 'wyakit_id' => ['table' => 'lrv_vehicle_wyakit', 'name' => 'wyakit_name', 'id' => 'wyakit_id'], 'wkasat_id' => ['table' => 'lrv_vehicle_wkasat', 'name' => 'wkasat_name', 'id' => 'wkasat_id'], 'wmotor2_id' => ['table' => 'lrv_vehicle_wmotor2', 'name' => 'wmotor2_name', 'id' => 'wmotor2_id']];
        if ($column === 'wTb') { $sql = "SELECT DISTINCT vm_vv.`wTb` as id, vm_vv.`wTb` as value, COUNT(DISTINCT p.product_id) as total FROM lrv_vehicle_master vm_vv "; } else { $m = $map[$column]; $sql = "SELECT DISTINCT lt.{$m['id']} as id, lt.{$m['name']} as value, COUNT(DISTINCT p.product_id) as total FROM {$m['table']} lt JOIN lrv_vehicle_master vm_vv ON lt.{$m['id']} = vm_vv.{$m['id']} "; }
        $sql .= " JOIN lrv_product_to_vehicle p2v_vv ON vm_vv.master_id = p2v_vv.master_id JOIN " . DB_PREFIX . "product p ON p2v_vv.product_id = p.product_id ";
        $temp = $data;
        if ($column === 'wTb') { unset($temp['vehicle']['wTb']); } else { $hierarchy = ['wmarka_id', 'wmodel1_id', 'yil', 'wyakit_id', 'wkasat_id', 'wmotor2_id']; $idx = array_search($column, $hierarchy); if ($idx !== false) for ($i = $idx; $i < count($hierarchy); $i++) unset($temp['vehicle'][$hierarchy[$i]]); }
        $sql .= $this->addFilters($temp, 'vehicle_values', null, 'p', 'vm_vv', 'p2v_vv');
        $sql .= ($column === 'wTb') ? " GROUP BY vm_vv.`wTb` " : " GROUP BY lt.{$m['id']} ";
        $sql .= " HAVING total > 0 ORDER BY value ASC";
        $rows = $this->db->query($sql)->rows;
        
        if ($redis) $redis->set($cache_key, json_encode($rows)); 
        return $rows;
    }

    public function getVehicleYears($data) {
        $redis = $this->getRedis();
        $cache_key = 'j3:veh_yrs:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($data));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);

        $sql = "SELECT MIN(vm_y.yil_bas) as min_y, MAX(IFNULL(vm_y.yil_bit, YEAR(NOW()))) as max_y FROM lrv_vehicle_master vm_y JOIN lrv_product_to_vehicle p2v_y ON vm_y.master_id = p2v_y.master_id JOIN " . DB_PREFIX . "product p_y ON p2v_y.product_id = p_y.product_id ";
        $temp = $data; unset($temp['vehicle']['yil']);
        $sql .= $this->addFilters($temp, 'vehicle_values', null, 'p_y', 'vm_y', 'p2v_y');
        $row = $this->db->query($sql)->row;
        
        $result = ['min' => (int)($row['min_y'] ?: 1990), 'max' => (int)($row['max_y'] ?: date('Y'))];
        if ($redis) $redis->set($cache_key, json_encode($result));
        return $result;
    }

    public function getVehicleYearCounts($data, $years_list) {
        $redis = $this->getRedis(); 
        $cache_key = 'j3:yr_cnt:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($data) . json_encode($years_list) . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);
        
        $counts = array_fill_keys($years_list, 0);
        $sql = "SELECT p_yc.product_id, vm_yc.yil_bas, IFNULL(vm_yc.yil_bit, YEAR(NOW())) as yil_bit FROM lrv_vehicle_master vm_yc JOIN lrv_product_to_vehicle p2v_yc ON vm_yc.master_id = p2v_yc.master_id JOIN " . DB_PREFIX . "product p_yc ON p2v_yc.product_id = p_yc.product_id ";
        $temp = $data; unset($temp['vehicle']['yil']); $sql .= $this->addFilters($temp, 'vehicle_values', null, 'p_yc', 'vm_yc', 'p2v_yc');
        $rows = $this->db->query($sql)->rows;
        $temp_counts = [];
        foreach ($rows as $row) { $p_id = (int)$row['product_id']; $bas = (int)$row['yil_bas']; $bit = (int)$row['yil_bit']; foreach ($years_list as $year) { if ($year >= $bas && $year <= $bit) { $temp_counts[$year][$p_id] = 1; } } }
        foreach ($years_list as $year) if (isset($temp_counts[$year])) $counts[$year] = count($temp_counts[$year]);
        
        if ($redis) $redis->set($cache_key, json_encode($counts)); 
        return $counts;
    }

    public function hasFilterData($filter_data, $key, $value = null) {
        $values = Arr::get($filter_data, $key);
        if ($value === null) return $values !== null;
        return is_array($values) ? in_array($value, $values) : $values === $value;
    }

    public function getAttributes($filter_data) {
        $redis = $this->getRedis(); 
        $cache_key = 'j3:attr:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data) . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);
        
        $attribute_table = $this->journal3->get('filterAttributeValuesSeparator') ? 'journal3_product_attribute' : 'product_attribute';
        $sql = "SELECT MAX(a.attribute_id) attribute_id, MAX(ad.name) attribute_name, MAX(pa.text) value, COUNT(DISTINCT p.product_id) total FROM `" . DB_PREFIX . "product` p JOIN `" . DB_PREFIX . $attribute_table . "` pa ON (p.product_id = pa.product_id) JOIN `" . DB_PREFIX . "attribute` a ON (a.attribute_id = pa.attribute_id) JOIN `" . DB_PREFIX . "attribute_description` ad ON (ad.attribute_id = ad.attribute_id) ";
        $sql .= $this->addFilters($filter_data, 'attribute') . " AND pa.language_id = '" . (int)$this->journal3_opencart->language_id . "' AND ad.language_id = '" . (int)$this->journal3_opencart->language_id . "' GROUP BY lower(pa.text), a.attribute_id HAVING total > 0";
        $query = $this->db->query($sql); $results = [];
        foreach ($query->rows as $row) { $results[$row['attribute_id']]['attribute_name'] = $row['attribute_name']; $results[$row['attribute_id']]['attribute_id'] = $row['attribute_id']; $results[$row['attribute_id']]['values'][$row['value']] = ['id' => $row['value'], 'value' => $row['value'], 'total' => $row['total']]; }
        
        if ($redis) $redis->set($cache_key, json_encode($results)); 
        return $results;
    }

    public function getFilters($filter_data, $opts = []) {
        $redis = $this->getRedis(); 
        $cache_key = 'j3:filter_opts:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data) . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);
        
        $sql = "SELECT f.filter_id id, fd.name filter_name, fg.filter_group_id filter_group_id, fgd.name filter_group_name, COUNT(*) total, f.sort_order as sort_order FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_filter` pf ON (p.product_id = pf.product_id) LEFT JOIN `" . DB_PREFIX . "filter` f ON (f.filter_id = pf.filter_id) LEFT JOIN `" . DB_PREFIX . "filter_description` fd ON (fd.filter_id = pf.filter_id) LEFT JOIN `" . DB_PREFIX . "filter_group` fg ON (fg.filter_group_id = fd.filter_group_id) LEFT JOIN `" . DB_PREFIX . "filter_group_description` fgd ON (fd.filter_group_id = fgd.filter_group_id) ";
        if (($filter_category_id = (int)Arr::get($filter_data, 'filter_category_id')) && Arr::get($opts, 'filtersCategoryCheck')) $sql .= " LEFT JOIN `" . DB_PREFIX . "category_filter` cf ON (f.filter_id = cf.filter_id) ";
        $sql .= $this->addFilters($filter_data, 'filter');
        if (($filter_category_id = (int)Arr::get($filter_data, 'filter_category_id')) && Arr::get($opts, 'filtersCategoryCheck')) $sql .= " AND cf.category_id = '" . (int)$filter_category_id . "' ";
        $sql .= " AND fd.language_id = '" . (int)$this->journal3_opencart->language_id . "' AND fgd.language_id = '" . (int)$this->journal3_opencart->language_id . "' GROUP BY pf.filter_id HAVING COUNT(*) > 0 ORDER BY fd.name ";
        $query = $this->db->query($sql); $results = array();
        foreach ($query->rows as $row) { if (!isset($results[$row['filter_group_id']])) $results[$row['filter_group_id']] = ['filter_group_id' => $row['filter_group_id'], 'filter_group_name' => $row['filter_group_name'], 'values' => []]; $results[$row['filter_group_id']]['values'][$row['id']] = ['id' => $row['id'], 'value' => $row['filter_name'], 'total' => $row['total'], 'sort_order' => $row['sort_order']]; }
        
        if ($redis) $redis->set($cache_key, json_encode($results)); 
        return $results;
    }

public function getProducts($filter_data) {
        $redis = $this->getRedis();
        $cache_key = 'j3:prods:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data));
        
        if ($redis && $redis->exists($cache_key)) {
            return json_decode($redis->get($cache_key), true);
        } 
        
        if (isset($filter_data['sort']) && $filter_data['sort'] == 'sales') $filter_data['sort'] = 'p.date_added';
        if (isset($filter_data['sort']) && $filter_data['sort'] === 'rating') { $filter_data['sort'] = 'p.date_added'; $filter_data['order'] = 'DESC'; }

        $sql = "SELECT p.product_id FROM `" . DB_PREFIX . "product` p ";
        $sql .= $this->addFilters($filter_data, null, null, 'p', 'vm_p', 'p2v_p');

		$sort = isset($filter_data['sort']) ? $filter_data['sort'] : '';
        if (strpos($sort, 'date_') === 0 || $sort === 'all') { $sql .= " ORDER BY p.date_added DESC, p.product_id DESC "; } 
        else {
            if ($filter_data['sort'] === 'random') { $sql .= " ORDER BY RAND()"; } 
            else {
                if ($filter_data['sort'] === 'pd.name' || $filter_data['sort'] === 'p.model') { $sql .= " ORDER BY LCASE(" . $filter_data['sort'] . ")"; } 
                else { $sql .= " ORDER BY " . $filter_data['sort']; }
                $sql .= ($filter_data['order'] === 'DESC') ? " DESC" : " ASC";
                $sql .= ", p.product_id DESC";
            }
        }

        if (isset($filter_data['start']) || isset($filter_data['limit'])) {
            if ($filter_data['start'] < 0) $filter_data['start'] = 0;
            if ($filter_data['limit'] < 1) $filter_data['limit'] = 20;
            $sql .= " LIMIT " . (int)$filter_data['start'] . "," . (int)$filter_data['limit'];
        }

$rows = $this->db->query($sql)->rows;
        if ($redis) $redis->set($cache_key, json_encode($rows));

        // Tekli arama (pipe yok) VE hiç sonuç yoksa → başarısız aramaya logla
        if (!empty($filter_data['filter_name'])
            && strpos($filter_data['filter_name'], '|') === false
            && empty($rows)) {
            $kw = trim($filter_data['filter_name']);
            if ($kw !== '') {
                $this->logFailedSearch($kw, false);
            }
        }

        return $rows;
    }

public function getTotalProducts($filter_data) {
        $redis = $this->getRedis(); 
        $cache_key = 'j3:total:v29:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data));
        if ($redis && $redis->exists($cache_key)) return (int)$redis->get($cache_key);
        
        $sql = "SELECT COUNT(p.product_id) as total FROM `" . DB_PREFIX . "product` p ";
        $sql .= $this->addFilters($filter_data, null, null, 'p', 'vm_t', 'p2v_t');
        
        $query = $this->db->query($sql);
        $total = (int)$query->row['total'];
        if ($redis) $redis->set($cache_key, $total); return $total;
    }

// =============================================================================
// DEĞİŞİKLİK 1: getFoundProductIds - bulk aramada status = 1 filtresi eklendi
// =============================================================================
private function getFoundProductIds($search_string) {
    $search_string = trim($search_string);
    $search_string = str_replace(array(',', "\n", "\r"), '|', $search_string);
    $cache_key_static = md5($search_string);
    
    if (isset(self::$search_results_cache[$cache_key_static])) {
        return self::$search_results_cache[$cache_key_static];
    }

    $redis = $this->getRedis();
    // Cache key v33 - status filtresi eklendiği için eski cache geçersiz
    $cache_key_redis = 'j3:search_ids:v33:' . (int)$this->config->get('config_language_id') . ':' . $cache_key_static;
    if ($redis && $redis->exists($cache_key_redis)) {
        $ids = json_decode($redis->get($cache_key_redis), true);
        self::$search_results_cache[$cache_key_static] = $ids;
        return $ids;
    }

    $is_bulk = (strpos($search_string, '|') !== false);
    $ids = [];

    if ($is_bulk) {
        $raw_keywords = explode('|', $search_string);
        $keywords = array_filter(array_map('trim', $raw_keywords));
        
        if (empty($keywords)) {
            $this->cacheResults($redis, $cache_key_redis, $cache_key_static, []);
            return [];
        }

        $raw_list = [];
        $search_list = [];
        
        foreach ($keywords as $k) {
            $raw_list[] = $this->db->escape($k);
            for ($z = 1; $z <= 4; $z++) {
                $raw_list[] = $this->db->escape(str_repeat('0', $z) . $k);
            }

            $clean = preg_replace('/[^a-zA-Z0-9]/', '', $k);
            if (!empty($clean)) {
                $search_list[] = $this->db->escape($clean);
                for ($z = 1; $z <= 4; $z++) {
                    $search_list[] = $this->db->escape(str_repeat('0', $z) . $clean);
                }
            }
        }

        $union_parts = [];
        
        // --- STATUS = 1 FİLTRESİ EKLENDİ ---
        if (!empty($raw_list)) {
            $in_sql = "'" . implode("','", $raw_list) . "'";
            $union_parts[] = "SELECT product_id FROM " . DB_PREFIX . "product WHERE status = 1 AND model IN ($in_sql)";
            $union_parts[] = "SELECT po.product_id FROM " . DB_PREFIX . "product_oem po INNER JOIN " . DB_PREFIX . "product p ON p.product_id = po.product_id AND p.status = 1 WHERE po.oem_code IN ($in_sql)";
        }
        
        if (!empty($search_list)) {
            $in_sql_search = "'" . implode("','", $search_list) . "'";
            $union_parts[] = "SELECT product_id FROM " . DB_PREFIX . "product WHERE status = 1 AND model_search IN ($in_sql_search)";
            $union_parts[] = "SELECT po.product_id FROM " . DB_PREFIX . "product_oem po INNER JOIN " . DB_PREFIX . "product p ON p.product_id = po.product_id AND p.status = 1 WHERE po.oem_search IN ($in_sql_search)";
        }
        // ------------------------------------
        
        if (!empty($union_parts)) {
            $sql = "SELECT DISTINCT product_id FROM (" . implode(" UNION ALL ", $union_parts) . ") AS all_results";
            $result = $this->db->query($sql);
            foreach ($result->rows as $r) {
                $ids[$r['product_id']] = (int)$r['product_id'];
            }
        }
        
    } else {
        $global_clean = preg_replace('/[^a-zA-Z0-9]/', '', $search_string);
        $search_string_clean = preg_replace('/\s\s+/', ' ', $search_string);
        $words = array_filter(explode(' ', $search_string_clean));
        
        if (empty($words) && empty($global_clean)) {
            $this->cacheResults($redis, $cache_key_redis, $cache_key_static, []);
            return [];
        }

        $where_conditions = [];
        $lang_id = (int)$this->journal3_opencart->language_id;

        $word_sql_parts = [];
        foreach ($words as $word) {
            $word = trim($word);
            if (empty($word)) continue;

            $raw_word = $this->db->escape($word);
            $clean_word = preg_replace('/[^a-zA-Z0-9]/', '', $word);
            
            $word_conditions = [
                "pd.name LIKE '%" . $raw_word . "%'",
                "CONCAT(',', pd.tag, ',') LIKE '%," . $raw_word . ",%'",
                "p.model LIKE '" . $raw_word . "%'",
                "po.oem_code LIKE '" . $raw_word . "%'"
            ];

            for ($z = 1; $z <= 3; $z++) {
                $zeros = str_repeat('0', $z);
                $word_conditions[] = "p.model LIKE '" . $zeros . $raw_word . "%'";
                $word_conditions[] = "po.oem_code LIKE '" . $zeros . $raw_word . "%'";
            }

            if (!empty($clean_word) && strlen($clean_word) > 2) {
                $clean_escaped = $this->db->escape($clean_word);
                $word_conditions[] = "p.model_search LIKE '%" . $clean_escaped . "%'";
                $word_conditions[] = "po.oem_search LIKE '%" . $clean_escaped . "%'";
            }

            $word_sql_parts[] = '(' . implode(' OR ', $word_conditions) . ')';
        }

        if (!empty($word_sql_parts)) {
            $where_conditions[] = '(' . implode(' AND ', $word_sql_parts) . ')';
        }

        $global_clean_sql = '';
        if (!empty($global_clean) && strlen($global_clean) > 2) {
            $gc = $this->db->escape($global_clean);
            
            $clean_conditions = [
                "p.model_search LIKE '%" . $gc . "%'",
                "po.oem_search LIKE '%" . $gc . "%'",
                "p.model LIKE '%" . $gc . "%'", 
                "po.oem_code LIKE '%" . $gc . "%'"
            ];
            
            for ($z = 1; $z <= 2; $z++) {
                $zeros = str_repeat('0', $z);
                $clean_conditions[] = "p.model_search LIKE '" . $zeros . $gc . "%'";
                $clean_conditions[] = "po.oem_search LIKE '" . $zeros . $gc . "%'";
            }

            $global_clean_sql = '(' . implode(' OR ', $clean_conditions) . ')';
        }

        $final_where_sql = '';
        
        if (!empty($where_conditions) && !empty($global_clean_sql)) {
            $final_where_sql = "(" . implode(' AND ', $where_conditions) . ") OR " . $global_clean_sql;
        } elseif (!empty($where_conditions)) {
            $final_where_sql = implode(' AND ', $where_conditions);
        } elseif (!empty($global_clean_sql)) {
            $final_where_sql = $global_clean_sql;
        }

        if (!empty($final_where_sql)) {
            $sql = "
                SELECT DISTINCT p.product_id 
                FROM " . DB_PREFIX . "product p 
                LEFT JOIN " . DB_PREFIX . "product_description pd 
                    ON (p.product_id = pd.product_id AND pd.language_id = '" . $lang_id . "')
                LEFT JOIN " . DB_PREFIX . "product_oem po 
                    ON (p.product_id = po.product_id)
                WHERE p.status = 1
                AND (" . $final_where_sql . ")
            ";
            
            $result = $this->db->query($sql);
            foreach ($result->rows as $r) {
                $ids[$r['product_id']] = (int)$r['product_id'];
            }
        }
    }

    $result_ids = array_values($ids);
    $this->cacheResults($redis, $cache_key_redis, $cache_key_static, $result_ids);
    return $result_ids;
}

private function cacheResults($redis, $cache_key_redis, $cache_key_static, $result_ids) {
    self::$search_results_cache[$cache_key_static] = $result_ids;
    if ($redis) {
        $redis->setex($cache_key_redis, 86400, json_encode($result_ids));
    }
}

// =============================================================================
// DEĞİŞİKLİK 2: handleMissingKeywordsCached - status = 1 filtresi eklendi
// Pasif ürünler artık "bulunamadı" olarak raporlanıyor
// =============================================================================
public function handleMissingKeywordsCached($search_string) {
    $redis = $this->getRedis();
    // Cache key v33 - status filtresi eklendiği için eski cache geçersiz
    $cache_key = 'j3:report:v33:' . (int)$this->config->get('config_language_id') . ':' . md5($search_string);

    $is_bulk = (strpos($search_string, '|') !== false);
    $final_data = [];

    if ($redis && $redis->exists($cache_key)) {
        $final_data = json_decode($redis->get($cache_key), true);
    } else {
        $raw_keywords = explode('|', $search_string);
        $keywords = array_filter(array_map('trim', $raw_keywords));
        
        if (empty($keywords)) return [];
        
        $report_data = [];
        $export_data = [];
        
        $all_raw_list = [];
        $all_search_list = [];
        $keyword_map = [];
        
        foreach ($keywords as $k) {
            $variants = [$this->db->escape($k)];
            for ($z = 1; $z <= 4; $z++) {
                $variants[] = $this->db->escape(str_repeat('0', $z) . $k);
            }
            
            $clean = preg_replace('/[^a-zA-Z0-9]/', '', $k);
            if (!empty($clean)) {
                $search_variants = [$this->db->escape($clean)];
                for ($z = 1; $z <= 4; $z++) {
                    $search_variants[] = $this->db->escape(str_repeat('0', $z) . $clean);
                }
                $keyword_map[$k] = ['raw' => $variants, 'search' => $search_variants];
                $all_raw_list = array_merge($all_raw_list, $variants);
                $all_search_list = array_merge($all_search_list, $search_variants);
            } else {
                $keyword_map[$k] = ['raw' => $variants, 'search' => []];
                $all_raw_list = array_merge($all_raw_list, $variants);
            }
        }
        
        $found_models = [];
        if (!empty($all_raw_list) || !empty($all_search_list)) {
            $union_parts = [];
            
            // --- STATUS = 1 FİLTRESİ EKLENDİ ---
            if (!empty($all_raw_list)) {
                $in_sql = "'" . implode("','", $all_raw_list) . "'";
                $union_parts[] = "SELECT DISTINCT model, model as search_term FROM " . DB_PREFIX . "product WHERE status = 1 AND model IN ($in_sql)";
                $union_parts[] = "SELECT DISTINCT p.model, po.oem_code as search_term FROM " . DB_PREFIX . "product p JOIN " . DB_PREFIX . "product_oem po ON p.product_id = po.product_id WHERE p.status = 1 AND po.oem_code IN ($in_sql)";
            }
            
            if (!empty($all_search_list)) {
                $in_sql_search = "'" . implode("','", $all_search_list) . "'";
                $union_parts[] = "SELECT DISTINCT model, model_search as search_term FROM " . DB_PREFIX . "product WHERE status = 1 AND model_search IN ($in_sql_search)";
                $union_parts[] = "SELECT DISTINCT p.model, po.oem_search as search_term FROM " . DB_PREFIX . "product p JOIN " . DB_PREFIX . "product_oem po ON p.product_id = po.product_id WHERE p.status = 1 AND po.oem_search IN ($in_sql_search)";
            }
            // ------------------------------------
            
            if (!empty($union_parts)) {
                $sql = "SELECT model, search_term FROM (" . implode(" UNION ALL ", $union_parts) . ") AS all_matches";
                $result = $this->db->query($sql);
                
                foreach ($result->rows as $row) {
                    $term = strtolower($row['search_term']);
                    if (!isset($found_models[$term])) {
                        $found_models[$term] = [];
                    }
                    $found_models[$term][] = $row['model'];
                }
            }
        }
        
        foreach ($keywords as $k) {
            $found = false;
            $matched_models = [];
            
            if (isset($keyword_map[$k])) {
                foreach ($keyword_map[$k]['raw'] as $variant) {
                    $key = strtolower($variant);
                    if (isset($found_models[$key])) {
                        $found = true;
                        $matched_models = array_merge($matched_models, $found_models[$key]);
                    }
                }
                
                foreach ($keyword_map[$k]['search'] as $variant) {
                    $key = strtolower($variant);
                    if (isset($found_models[$key])) {
                        $found = true;
                        $matched_models = array_merge($matched_models, $found_models[$key]);
                    }
                }
            }
            
            $matched_models = array_unique($matched_models);
            
            if ($found) {
                $report_data[$k] = 'found';
                $export_data[$k] = $matched_models;
            } else {
                $report_data[$k] = 'missing';
                $export_data[$k] = [];
            }
        }
        
        $final_data = [
            'total'         => count($report_data),
            'missing_count' => count(array_filter($report_data, function($v){return $v=='missing';})),
            'items'         => $report_data,
            'matches'       => $export_data
        ];
        
        if ($redis) {
            $redis->setex($cache_key, 3600, json_encode($final_data));
        }
    }
    
    if (isset($final_data['items']) && is_array($final_data['items'])) {
        foreach ($final_data['items'] as $keyword => $status) {
            if ($status === 'missing') {
                $this->logFailedSearch($keyword, $is_bulk);
            }
        }
    }

    return $final_data;
}


public function getThreedTotal($filter_data) {
        $redis = $this->getRedis();
        $cache_key = 'j3:total_360:v30:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data));
        if ($redis && $redis->exists($cache_key)) return (int)$redis->get($cache_key);

        $temp_data = $filter_data;
        unset($temp_data['threed']); 

        $sql = "SELECT COUNT(p.product_id) as total FROM `" . DB_PREFIX . "product` p ";
        $sql .= $this->addFilters($temp_data, null, null, 'p', 'vm_t', 'p2v_t');
        $sql .= " AND p.threed = '1'";

        $query = $this->db->query($sql);
        $total = (int)$query->row['total'];
        if ($redis) $redis->set($cache_key, $total);
        return $total;
    }

public function getDateAddedCounts($filter_data) {
        $redis = $this->getRedis();
        $cache_key = 'j3:da_cnt:v30:' . (int)$this->config->get('config_language_id') . ':' . md5(json_encode($filter_data) . (int)$this->config->get('config_store_id'));
        if ($redis && $redis->exists($cache_key)) return json_decode($redis->get($cache_key), true);

        $temp_data = $filter_data;
        unset($temp_data['date_added_range']);

        $ranges = [
            '1w'  => 'DATE_SUB(NOW(), INTERVAL 1 WEEK)',
            '1m'  => 'DATE_SUB(NOW(), INTERVAL 1 MONTH)',
            '3m'  => 'DATE_SUB(NOW(), INTERVAL 3 MONTH)',
            '6m'  => 'DATE_SUB(NOW(), INTERVAL 6 MONTH)',
            '1y'  => 'DATE_SUB(NOW(), INTERVAL 1 YEAR)',
        ];

        $counts = [];
        foreach ($ranges as $key => $date_expr) {
            $sql = "SELECT COUNT(p.product_id) as total FROM `" . DB_PREFIX . "product` p ";
            $sql .= $this->addFilters($temp_data, null, null, 'p', 'vm_da', 'p2v_da');
            $sql .= " AND p.date_added >= " . $date_expr;
            $row = $this->db->query($sql)->row;
            $counts[$key] = (int)$row['total'];
        }

        if ($redis) $redis->setex($cache_key, 3600, json_encode($counts));
        return $counts;
    }

// Whitelist prefixleri - şirket içi IP'ler loglanmaz
private function isWhitelistedIp($ip) {
    $whitelist_prefixes = [
        '84.17.', '192.68.', '10.34.',
        '127.', '10.', '192.168.',
        '172.16.', '172.17.', '172.18.', '172.19.',
        '172.20.', '172.21.', '172.22.', '172.23.',
        '172.24.', '172.25.', '172.26.', '172.27.',
        '172.28.', '172.29.', '172.30.', '172.31.',
        '::1'
    ];
    foreach ($whitelist_prefixes as $prefix) {
        if (strpos($ip, $prefix) === 0) return true;
    }
    return false;
}

public function logFailedSearch($keyword, $is_bulk = false) {
    if (utf8_strlen($keyword) < 3) return;

    // Şirket içi IP'lerden gelen aramalar loglanmaz
    $ip = isset($this->request->server['REMOTE_ADDR']) ? $this->request->server['REMOTE_ADDR'] : '';
    if ($this->isWhitelistedIp($ip)) return;

    $keyword = $this->db->escape(utf8_strtolower($keyword));
    $lang_full = isset($this->session->data['language']) ? $this->session->data['language'] : $this->config->get('config_language');
    $lang_code = strtoupper(substr($lang_full, 0, 2));

    if (isset($this->session->data['search_spam_check'][$lang_code][$keyword])) return;
    $this->session->data['search_spam_check'][$lang_code][$keyword] = true;
    
    $this->db->query("INSERT INTO `lrv_search_failed_log` SET keyword = '" . $keyword . "', language_code = '" . $this->db->escape($lang_code) . "', is_bulk = '" . (int)$is_bulk . "', hit = '1', date_added = NOW(), date_updated = NOW() ON DUPLICATE KEY UPDATE hit = hit + 1, is_bulk = '" . (int)$is_bulk . "', date_updated = NOW()");
}


private function addFilters($filter_data, $query_type = null, $force_year = null, $p = 'p', $vm = 'vm', $p2v = 'p2v') {
    $sql = "";

    $sql .= " JOIN `" . DB_PREFIX . "product_description` pd ON ($p.product_id = pd.product_id) ";

    $sql .= " WHERE $p.status = '1' ";
    $sql .= " AND $p.date_available <= NOW() ";
    $sql .= " AND pd.language_id = '" . (int)$this->journal3_opencart->language_id . "'";

    if ($query_type !== 'category' && (!empty($filter_data['categories']) || !empty($filter_data['filter_category_id']))) { 
        
        $cat_ids = [];
        if (!empty($filter_data['categories'])) {
            $cat_ids = array_map('intval', $filter_data['categories']);
        } elseif (!empty($filter_data['filter_category_id'])) {
            $cat_ids = [(int)$filter_data['filter_category_id']];
        }

        if (!empty($cat_ids)) {
            $in_cats = implode(",", $cat_ids);
            if (Arr::get($filter_data, 'filter_sub_category')) {
                $sql .= " AND EXISTS (SELECT 1 FROM `" . DB_PREFIX . "product_to_category` p2c_opt JOIN `" . DB_PREFIX . "category_path` cp_opt ON (cp_opt.category_id = p2c_opt.category_id) WHERE p2c_opt.product_id = $p.product_id AND cp_opt.path_id IN ($in_cats))";
            } else {
                $sql .= " AND EXISTS (SELECT 1 FROM `" . DB_PREFIX . "product_to_category` p2c_opt WHERE p2c_opt.product_id = $p.product_id AND p2c_opt.category_id IN ($in_cats))";
            }
        }
    }
    
    if (!empty($filter_data['vehicle']['v_cat'])) { 
        $in_vcat = implode(",", array_map('intval', $filter_data['vehicle']['v_cat']));
        $sql .= " AND EXISTS (SELECT 1 FROM `" . DB_PREFIX . "product_to_category` custom_p2c WHERE custom_p2c.product_id = $p.product_id AND custom_p2c.category_id IN ($in_vcat))"; 
    }

    $has_vehicle_filter = false;
    if (!empty($filter_data['vehicle'])) { 
        $temp_veh = $filter_data['vehicle']; 
        unset($temp_veh['v_cat']); 
        if (!empty($temp_veh)) { 
            $has_vehicle_filter = true; 
        } 
    }

    if ($has_vehicle_filter || $force_year) {
        if ($query_type === 'vehicle_values') {
            if ($force_year) {
                $sql .= " AND (" . (int)$force_year . " BETWEEN $vm.yil_bas AND IFNULL($vm.yil_bit, YEAR(NOW())))";
            }
            if ($has_vehicle_filter) {
                $v = $filter_data['vehicle'];
                
                if (!empty($v['wTb'])) {
                    $escaped_wtb = [];
                    foreach ($v['wTb'] as $wtb) { $escaped_wtb[] = "'" . $this->db->escape($wtb) . "'"; }
                    $sql .= " AND $vm.`wTb` IN (" . implode(",", $escaped_wtb) . ")"; 
                }
                
                if (!empty($v['wmarka_id'])) $sql .= " AND $vm.wmarka_id IN (" . implode(",", array_map('intval', $v['wmarka_id'])) . ")"; 
                if (!empty($v['wmodel1_id'])) $sql .= " AND $vm.wmodel1_id IN (" . implode(",", array_map('intval', $v['wmodel1_id'])) . ")"; 
                
                if (!$force_year && !empty($v['yil'])) { 
                    $y_sql = []; 
                    foreach ($v['yil'] as $y) {
                        $y_sql[] = "(" . (int)$y . " BETWEEN $vm.yil_bas AND IFNULL($vm.yil_bit, YEAR(NOW())))"; 
                    }
                    $sql .= " AND (" . implode(" OR ", $y_sql) . ")"; 
                } 
                
                if (!empty($v['wyakit_id'])) $sql .= " AND $vm.wyakit_id IN (" . implode(",", array_map('intval', $v['wyakit_id'])) . ")"; 
                if (!empty($v['wkasat_id'])) $sql .= " AND $vm.wkasat_id IN (" . implode(",", array_map('intval', $v['wkasat_id'])) . ")"; 
                if (!empty($v['wmotor2_id'])) $sql .= " AND $vm.wmotor2_id IN (" . implode(",", array_map('intval', $v['wmotor2_id'])) . ")"; 
            }
        } else {
            $sql .= " AND EXISTS (";
            $sql .= " SELECT 1 FROM lrv_product_to_vehicle $p2v ";
            $sql .= " JOIN lrv_vehicle_master $vm ON ($p2v.master_id = $vm.master_id) ";
            $sql .= " WHERE $p2v.product_id = $p.product_id ";

            if ($force_year) {
                $sql .= " AND (" . (int)$force_year . " BETWEEN $vm.yil_bas AND IFNULL($vm.yil_bit, YEAR(NOW())))";
            }

            if ($has_vehicle_filter) {
                $v = $filter_data['vehicle'];
                
                if (!empty($v['wTb'])) {
                    $escaped_wtb = [];
                    foreach ($v['wTb'] as $wtb) { $escaped_wtb[] = "'" . $this->db->escape($wtb) . "'"; }
                    $sql .= " AND $vm.`wTb` IN (" . implode(",", $escaped_wtb) . ")"; 
                }

                if (!empty($v['wmarka_id'])) $sql .= " AND $vm.wmarka_id IN (" . implode(",", array_map('intval', $v['wmarka_id'])) . ")"; 
                if (!empty($v['wmodel1_id'])) $sql .= " AND $vm.wmodel1_id IN (" . implode(",", array_map('intval', $v['wmodel1_id'])) . ")"; 
                
                if (!$force_year && !empty($v['yil'])) { 
                    $y_sql = []; 
                    foreach ($v['yil'] as $y) {
                        $y_sql[] = "(" . (int)$y . " BETWEEN $vm.yil_bas AND IFNULL($vm.yil_bit, YEAR(NOW())))"; 
                    }
                    $sql .= " AND (" . implode(" OR ", $y_sql) . ")"; 
                } 
                
                if (!empty($v['wyakit_id'])) $sql .= " AND $vm.wyakit_id IN (" . implode(",", array_map('intval', $v['wyakit_id'])) . ")"; 
                if (!empty($v['wkasat_id'])) $sql .= " AND $vm.wkasat_id IN (" . implode(",", array_map('intval', $v['wkasat_id'])) . ")"; 
                if (!empty($v['wmotor2_id'])) $sql .= " AND $vm.wmotor2_id IN (" . implode(",", array_map('intval', $v['wmotor2_id'])) . ")"; 
            }
            $sql .= " )";
        }
    }

    if (Arr::get($filter_data, 'filter_name')) {
        $found_ids = $this->getFoundProductIds($filter_data['filter_name']);
        if (!empty($found_ids)) { 
            $sql .= " AND $p.product_id IN (" . implode(',', $found_ids) . ")"; 
        } else { 
            $sql .= " AND 1=0";
        }
    }

    if ($query_type !== 'attribute' && Arr::get($filter_data, 'attributes')) { 
        $attribute_table = $this->journal3->get('filterAttributeValuesSeparator') ? 'journal3_product_attribute' : 'product_attribute'; 
        foreach ($filter_data['attributes'] as $attribute_id => $attribute_values) { 
            $temp = array(); 
            foreach ($attribute_values as $key => $value) {
                $temp[] = "TRIM(pai.text) = '" . $this->db->escape($value) . "'"; 
            }
            $sql .= " AND EXISTS (SELECT 1 FROM `" . DB_PREFIX . $attribute_table . "` pai WHERE $p.product_id = pai.product_id AND pai.attribute_id = " . (int)$attribute_id . " AND (" . implode(' OR ', $temp) . "))"; 
        } 
    }

    if ($query_type !== 'filter' && Arr::get($filter_data, 'filters')) { 
        foreach ($filter_data['filters'] as $filters) { 
            $sql .= " AND EXISTS (SELECT 1 FROM `" . DB_PREFIX . "product_filter` pfi WHERE $p.product_id = pfi.product_id AND pfi.filter_id IN (" . implode(', ', array_map('intval', $filters)) . "))"; 
        } 
    }

    if (!empty($filter_data['threed']) && $filter_data['threed'] == 1) { 
        $sql .= " AND $p.threed = '1'"; 
    }

    if (!empty($filter_data['date_added_range'])) {
        $range_map = [
            '1w'  => 'DATE_SUB(NOW(), INTERVAL 1 WEEK)',
            '1m'  => 'DATE_SUB(NOW(), INTERVAL 1 MONTH)',
            '3m'  => 'DATE_SUB(NOW(), INTERVAL 3 MONTH)',
            '6m'  => 'DATE_SUB(NOW(), INTERVAL 6 MONTH)',
            '1y'  => 'DATE_SUB(NOW(), INTERVAL 1 YEAR)',
        ];
        if (isset($range_map[$filter_data['date_added_range']])) {
            $sql .= " AND $p.date_added >= " . $range_map[$filter_data['date_added_range']];
        }
    }

    return $sql;
}

}
class_alias('ModelJournal3Filter', '\Opencart\Catalog\Model\Journal3\Filter');