<?php
require_once("/opt/msa/lib/php/include/dbPDO.php");

require_once("redis.php");

class offers extends RedisLib
{
	const REDIS_KEY_PREFIX = 'cp_';
	const BRANDS_KEY_NAME = 'brands';
	const COUPONS_KEY_NAME = 'brand_coupons';
	const COUPON_DATA = 'coupons_data';

	private $_crawler_offers;
	private $_crawler_offer_brands;
	public $_couponsData;
	protected $redis;

	function __construct()
	{
		$this->stats_db = CommonPDOConnector::connect('Stats');
		$this->admin_db = CommonPDOConnector::connect('ConnectToAdmin');
		$this->cms_db = CommonPDOConnector::connect('ConnectToCMS');

		$this->_crawler_offers     = 'CrawlerCoupons';
		$this->_crawler_offer_brands     = 'CrawlerBrands';
		$this->_couponsData = null;

		try {
			$this->redis = new RedisLib();
		} catch (Exception $e) {
			print_r($e);
			return array();
		}
	}

	/**
	 * Function to Fetch Total number of offers
	 */
	public function getMerchantWiseOffersCount($params)
	{
		$term = $params["term"];
		$cms_offers = [];
		/** Fetch from rep_coupon */
		$rep_coupon_count = $this->cms_db->query("SELECT store, 
		image_url, 
		ifnull( rep_coupon.merchant_redirect_link, 
		ifnull( rep_coupon.coupon_redirect_link, store ) ) as cashback_link, 
		COUNT( rep_coupon.store ) as total_count, 
		0 as cashback_value
		FROM (rep_coupon) 
		WHERE 
		`image_url` <>'' 
		AND type IN ('code') 
		AND ( store like ('{$term}%') OR merchant_home_page like ('{$term}%') ) 
		AND `rep_coupon`.`end_date` >= now() 
		AND ( primary_location LIKE('%**%') or primary_location LIKE('%multi country%') ) 
		GROUP BY rep_coupon.store ORDER BY total_count desc LIMIT 10
		");
		if (!$rep_coupon_count) {
			$cms_offers = $rep_coupon_count->fetchAll(PDO::FETCH_ASSOC);
		}

		/** Fetch from couponcrawler */
		$query = "SELECT ifnull( merchant_domain, brand_name ) as store, brand_image as image_url, 
		ifnull( CrawlerBrands.redirect_link, 
		ifnull( $this->_crawler_offers.redirect_link, brand_url ) ) as cashback_link,
		COUNT($this->_crawler_offers.coupon) as total_count,
		cashback_value
		FROM " . $this->_crawler_offers . " LEFT JOIN " . $this->_crawler_offer_brands . " ON CrawlerCoupons.brand_id = CrawlerBrands.id 
		WHERE `brand_image` <>'' 
		AND ( LOWER(brand_name) like ('{$term}%') OR LOWER(brand_url) like ('{$term}%') OR LOWER(merchant_domain) like ('{$term}%') OR LOWER(brand_url) like ('www.{$term}%') OR LOWER(merchant_domain) like ('www.{$term}%') ) 
		AND ( $this->_crawler_offers.`start_date` <= now() AND ( $this->_crawler_offers.`validthru` >= curdate() or ( $this->_crawler_offers.`validthru` is null 
		AND  $this->_crawler_offers.modified >= now() - interval 1 day))) 
		AND `type` IN ('coupon','deal')
		GROUP BY store ORDER BY total_count desc LIMIT 10";

		$crawler_coupon = $this->stats_db->query($query);

		if (!empty($crawler_coupon)) {
			$crawlers_offers = $crawler_coupon->fetchAll(PDO::FETCH_ASSOC);
		}

		$crawlers_offers = !empty($crawlers_offers) ? $crawlers_offers : [];
		$offers =  array_merge($crawlers_offers, $cms_offers);

		return $crawlers_offers;
	}

	/**
	 * Function to fetch all offers
	 */

	public function getAllOffers($params)
	{
		if (!empty($params)) {
			if (!empty($params['term'])) {
				$term = $params['term'];
			} else {
				return [];
			}
			$response = $this->redis->get_hash_data(SELF::REDIS_KEY_PREFIX . SELF::COUPON_DATA, $term);
			if (!empty($response)) {
				$this->_couponsData = json_decode($response, true);
			} else {
				$query = "SELECT $this->_crawler_offers.id, title, description, coupon as code,
			$this->_crawler_offers.brand_id,
			ifnull( CrawlerBrands.redirect_link, 
			ifnull( $this->_crawler_offers.redirect_link, brand_url ) ) as cashback_link, 
			brand_name as store, 
			merchant_domain as merchant_home_page, 
			brand_image as image_url, 
			$this->_crawler_offers.created as created_on, 
			$this->_crawler_offers.modified as updated_on, 
			'crawler' as data_source, 
			$this->_crawler_offers.validthru as end_date, 
			$this->_crawler_offers.type 
			FROM $this->_crawler_offers LEFT JOIN CrawlerBrands ON $this->_crawler_offers.brand_id = CrawlerBrands.id 
			WHERE `brand_image` <>'' AND ( LOWER(brand_name) like ('{$term}%') OR LOWER(brand_url) like ('{$term}%') OR LOWER(merchant_domain) like ('{$term}%') OR LOWER(brand_url) like ('www.{$term}%') OR LOWER(merchant_domain) like ('www.{$term}%') ) 
			AND ( $this->_crawler_offers.`start_date` <= now() AND ( $this->_crawler_offers.`validthru` >= curdate() or ( $this->_crawler_offers.`validthru` is null and $this->_crawler_offers.modified >= now() - interval 1 day))) 
			";

				if (!empty($params['type'])) {
					$query .= " AND ($this->_crawler_offers.type = '" . $params['type'] . "')";
				} else {
					$query .= " AND ($this->_crawler_offers.type = 'deal' OR $this->_crawler_offers.type = 'coupon')";
				}

				if (!empty($params['order_by'])) {
					$query .= " ORDER BY {$params['order_by']}  desc";
				} else {
					$query .= " ORDER BY $this->_crawler_offers.created  desc";
				}

				if (!empty($params['start']) && !empty($params['limit'])) {
					$query .= " limit " . $params['start'] . "," . $params['limit'];
				}

				$crawler_coupon_list = $this->stats_db->query($query);
				if (!empty($crawler_coupon_list)) {
					$crawlers_offers = $crawler_coupon_list->fetchAll(PDO::FETCH_ASSOC);
				}
				$this->_couponsData = !empty($crawlers_offers) ? $crawlers_offers : [];
			}
			$this->setRedisData();
			return $this->_couponsData;
		}
	}

	/** Get Coupon Categories */
	public function getCategories()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "categories");
		if (empty($data)) {
			$sql = "select id, category_name from Items_Category WHERE status = 1";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "categories", $data);
		}
		return $data;
	}

	/** Get Popular Stores*/
	public function getPopularStores()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "popular_stores");
		if (empty($data)) {

			$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, brand_image as image_url, count(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND ( CrawlerBrands.redirect_link LIKE '%cad.php%' OR CrawlerBrands.is_premium = 1 ) AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND CrawlerBrands.redirect_link IS NOT NULL GROUP BY store ORDER BY count(CrawlerCoupons.id) DESC ";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "popular_stores", $data);
		}
		return $data;
	}

	/** Get Top Stores */

	public function getTopStores()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "top_stores");
		if (empty($data)) {
			$sql = "SELECT DISTINCT(CrawlerBrands.id), LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, ifnull( brand_name, merchant_domain ) as merchant FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id 
			WHERE 
			CrawlerBrands.brand_image <> ''
			AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) 
			AND CrawlerBrands.is_active = 1 
			AND CrawlerCoupons.description <> '' 
			AND CrawlerCoupons.type IS NOT NULL LIMIT 44";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "top_stores", $data);
		}
		return $data;
	}

	/** Get latest Offers */

	public function getLatestOffers()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "latest_offers");
		if (empty($data)) {
			$sql = "SELECT DISTINCT(LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) )) as store, CrawlerBrands.id, ifnull( brand_name, merchant_domain ) as merchant, brand_image as image_url, title, description, CrawlerCoupons.type, CrawlerCoupons.validthru FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id where 
			CrawlerBrands.brand_image <> ''
			AND CrawlerCoupons.validthru BETWEEN NOW() AND (NOW() + INTERVAL 30 DAY) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND brand_image <> '' AND CrawlerCoupons.type = 'coupon' AND CrawlerBrands.redirect_link IS NOT NULL ORDER BY CrawlerCoupons.validthru ASC LIMIT 0, 50";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$result = [];
			foreach ($data as $row) {
				$result[$row['store']] = $row;
			}
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "latest_offers", $result);
		}
		return $data;
	}

	/**Get Popular Categories */
	public function getPopularCategories()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "popular_categories");
		if (empty($data)) {
			$categories = $this->getRedisKeyData(self::REDIS_KEY_PREFIX . "categories");
			if (empty($categories)) {
				$categories = $this->getCategories();
			}
			unset($categories[12]);
			$result = [];
			foreach ($categories as $category) {
				$category_id = $category['id'];
				$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, CrawlerBrands.category_id, brand_name, brand_image as image_url FROM CrawlerBrands JOIN Items_Category ON Items_Category.id = CrawlerBrands.category_id JOIN CrawlerCoupons on CrawlerCoupons.brand_id = CrawlerBrands.id where category_id = '$category_id' AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerCoupons.description <> '' AND brand_image <> '' AND CrawlerBrands.is_active = 1 AND CrawlerBrands.redirect_link IS NOT NULL GROUP BY CrawlerBrands.id LIMIT 10 ";
				$query = $this->stats_db->query($sql);
				$data = $query->fetchAll(PDO::FETCH_ASSOC);
				foreach ($data as $row) {
					$result[$category['category_name']][] = $row;
				}
			}
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "popular_categories", $result);
		}
		return $data;
	}

	/** Get Top savings offers */
	public function getTopSavings()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "top_savings");
		if (empty($data)) {
			$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, brand_name as merchant, brand_image as image_url, type, title, description, validthru FROM CrawlerBrands JOIN CrawlerCoupons on CrawlerCoupons.brand_id = CrawlerBrands.id WHERE brand_image <> '' AND CrawlerCoupons.type = 'deal' AND CrawlerCoupons.validthru BETWEEN NOW() AND (NOW() + INTERVAL 1 MONTH) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND CrawlerBrands.redirect_link IS NOT NULL limit 8";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "top_savings", $data);
		}
		return $data;
	}

	/** Get top 20 Deals */
	public function getTop20Deals()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "top20_deals");
		if (empty($data)) {
			$sql = "SELECT ifnull(CrawlerBrands.brand_name, CrawlerBrands.merchant_domain) as merchant, ifnull(CrawlerBrands.redirect_link, CrawlerCoupons.brand_url) as redirect_link, CrawlerBrands.brand_image as image_url, CrawlerCoupons.title, CrawlerCoupons.description, CrawlerCoupons.description, CrawlerCoupons.type, CrawlerCoupons.coupon as code, CrawlerCoupons.validthru FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.redirect_link LIKE '%cad.php%' AND CrawlerCoupons.type IS NOT NULL LIMIT 20 ";
			$query = $this->stats_db->query($sql);
			$response = $query->fetchAll(PDO::FETCH_ASSOC);
			foreach ($response as $row) {
				$result[$row['type']][] = $row;
			}

			$data['coupons'] = $data['deals'] = [];
			if (!empty($result)) {
				$data['coupons'] = isset($result['coupon']) && !empty($result['coupon']) ? $result['coupon'] : [];
				$data['deals'] = isset($result['deal']) && !empty($result['deal']) ? $result['deal'] : [];
			}

			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "top20_deals", $data);
		}
		return $data;
	}

	/** Get Latest Offers */
	public function getAllLatestOffers()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "all_latest_offers");
		if (empty($data)) {
			$sql = "SELECT ifnull(CrawlerBrands.brand_name, CrawlerBrands.merchant_domain) as merchant, ifnull(CrawlerBrands.redirect_link, CrawlerCoupons.brand_url) as redirect_link, CrawlerBrands.brand_image as image_url, CrawlerCoupons.title, CrawlerCoupons.description, CrawlerCoupons.description, CrawlerCoupons.type, CrawlerCoupons.coupon as code, CrawlerCoupons.validthru FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerCoupons.type IS NOT NULL ORDER BY CrawlerCoupons.validthru ASC LIMIT 0, 100";
			$query = $this->stats_db->query($sql);
			$response = $query->fetchAll(PDO::FETCH_ASSOC);
			foreach ($response as $row) {
				$result[$row['type']][] = $row;
			}

			$data['coupons'] = $data['deals'] = [];
			if (!empty($result)) {
				$data['coupons'] = isset($result['coupon']) && !empty($result['coupon']) ? $result['coupon'] : [];
				$data['deals'] = isset($result['deal']) && !empty($result['deal']) ? $result['deal'] : [];
				$data['merchant'] = (isset($result['coupon'][0]['merchant']) && !empty($result['coupon'][0]['merchant'])) ? $result['coupon'][0]['merchant'] : $result['deal'][0]['merchant'];
				$data['image_url'] = (isset($result['coupon'][0]['image_url']) && !empty($result['coupon'][0]['image_url'])) ? $result['coupon'][0]['image_url'] : (isset($result['deal'][0]['image_url']) && !empty($result['deal'][0]['image_url']) ? $result['deal'][0]['image_url'] : '');
			}

			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "all_latest_offers", $data);
		}
		return $data;
	}

	/** Get top saving offers */
	public function getAllTopSavingOffers()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "all_top_savings");
		if (empty($data)) {
			$sql = "SELECT ifnull(CrawlerBrands.brand_name, CrawlerBrands.merchant_domain) as merchant, ifnull(CrawlerBrands.redirect_link, CrawlerCoupons.brand_url) as redirect_link, CrawlerBrands.brand_image as image_url, CrawlerCoupons.title, CrawlerCoupons.description, CrawlerCoupons.description, CrawlerCoupons.type, CrawlerCoupons.coupon as code, CrawlerCoupons.validthru FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerCoupons.type = 'deal' ORDER BY CrawlerCoupons.validthru ASC LIMIT 0, 100";
			$query = $this->stats_db->query($sql);
			$response = $query->fetchAll(PDO::FETCH_ASSOC);
			foreach ($response as $row) {
				$result[$row['type']][] = $row;
			}

			$data['coupons'] = $data['deals'] = [];
			if (!empty($result)) {
				$data['coupons'] = isset($result['coupon']) && !empty($result['coupon']) ? $result['coupon'] : [];
				$data['deals'] = isset($result['deal']) && !empty($result['deal']) ? $result['deal'] : [];
				$data['merchant'] = (isset($result['coupon'][0]['merchant']) && !empty($result['coupon'][0]['merchant'])) ? $result['coupon'][0]['merchant'] : $result['deal'][0]['merchant'];
				$data['image_url'] = (isset($result['coupon'][0]['image_url']) && !empty($result['coupon'][0]['image_url'])) ? $result['coupon'][0]['image_url'] : (isset($result['deal'][0]['image_url']) && !empty($result['deal'][0]['image_url']) ? $result['deal'][0]['image_url'] : '');
			}

			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "all_top_savings", $data);
		}
		return $data;
	}

	/** Get Best Coupons */

	public function getAllBestCoupons()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "all_best_coupons");
		if (empty($data)) {
			$sql = "SELECT ifnull(CrawlerBrands.brand_name, CrawlerBrands.merchant_domain) as merchant, ifnull(CrawlerBrands.redirect_link, CrawlerCoupons.brand_url) as redirect_link, CrawlerBrands.brand_image as image_url, CrawlerCoupons.title, CrawlerCoupons.description, CrawlerCoupons.description, CrawlerCoupons.type, CrawlerCoupons.coupon as code, CrawlerCoupons.validthru FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) 
			AND CrawlerCoupons.type = 'coupon' ORDER BY CrawlerCoupons.validthru ASC LIMIT 0, 100";
			$query = $this->stats_db->query($sql);
			$response = $query->fetchAll(PDO::FETCH_ASSOC);
			foreach ($response as $row) {
				$result[$row['type']][] = $row;
			}

			$data['coupons'] = $data['deals'] = [];
			if (!empty($result)) {
				$data['coupons'] = isset($result['coupon']) && !empty($result['coupon']) ? $result['coupon'] : [];
				$data['deals'] = isset($result['deal']) && !empty($result['deal']) ? $result['deal'] : [];
				$data['merchant'] = (isset($result['coupon'][0]['merchant']) && !empty($result['coupon'][0]['merchant'])) ? $result['coupon'][0]['merchant'] : $result['deal'][0]['merchant'];
				$data['image_url'] = (isset($result['coupon'][0]['image_url']) && !empty($result['coupon'][0]['image_url'])) ? $result['coupon'][0]['image_url'] : (isset($result['deal'][0]['image_url']) && !empty($result['deal'][0]['image_url']) ? $result['deal'][0]['image_url'] : '');
			}

			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "all_best_coupons", $data);
		}
		return $data;
	}

	/** Get Featured Stores */

	public function getFeaturedStores()
	{
		$data = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "featured_stores");
		if (empty($data)) {
			$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, CrawlerBrands.brand_name, COUNT(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons on CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.brand_name <> '' AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' GROUP BY CrawlerBrands.id ORDER BY COUNT(CrawlerCoupons.id) DESC LIMIT 40";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisKeyData(self::REDIS_KEY_PREFIX . "featured_stores", $data);
		}
		return $data;
	}

	/** Get All Stores */
	public function getAllStores()
	{
		$keys = [];
		foreach (range('a', 'z') as $letter) {
			$keys[] = $letter;
		}
		$keys[] = '0-9';

		foreach ($keys as $key) {
			$response = $this->redis->get_hash_data(self::REDIS_KEY_PREFIX . "alphabetical_stores", $key);
			if (empty($response)) {
				$brand_name_condition = "AND ( CrawlerBrands.merchant_domain LIKE '$key%' OR CrawlerBrands.brand_name LIKE '$key%' )";
				if ($key == '0-9') {
					$brand_name_condition = "AND ( CrawlerBrands.merchant_domain REGEXP '^[0-9]' OR CrawlerBrands.brand_name REGEXP '^[0-9]' )";
				}
				$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, count(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
				CrawlerBrands.brand_image <> ''
				AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' $brand_name_condition GROUP BY store";
				$query = $this->stats_db->query($sql);
				$response = $query->fetchAll(PDO::FETCH_ASSOC);
				$this->redis->setRedisHashKeyData(self::REDIS_KEY_PREFIX . "alphabetical_stores", $key, $response);
			}
			$data[$key] = $response;
		}
		return $data;
	}

	/** Get categories stores */

	public function getCategoryStores()
	{
		$keys = $data = [];
		foreach (range('a', 'z') as $letter) {
			$keys[] = $letter;
		}
		$keys[] = '0-9';
		$categories = $this->redis->getRedisKeyData(self::REDIS_KEY_PREFIX . "categories");
		if (empty($categories)) {
			$categories = $this->getCategories();
		}
		foreach ($categories as $category) {
			$category_id = $category['id'];
			foreach ($keys as $key) {
				$response = $this->redis->get_hash_data(self::REDIS_KEY_PREFIX . "alphabetical_category_stores_$category_id", $key);
				if (empty($response)) {
					$brand_name_condition = "AND ( CrawlerBrands.merchant_domain LIKE '$key%' OR CrawlerBrands.brand_name LIKE '$key%' )";
					if ($key == '0-9') {
						$brand_name_condition = "AND ( CrawlerBrands.merchant_domain REGEXP '^[0-9]' OR CrawlerBrands.brand_name REGEXP '^[0-9]' )";
					}
					$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, count(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
					CrawlerBrands.brand_image <> ''
					AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' $brand_name_condition AND CrawlerBrands.category_id = $category_id GROUP BY store";
					$query = $this->stats_db->query($sql);
					$response = $query->fetchAll(PDO::FETCH_ASSOC);
					$this->redis->setRedisHashKeyData(self::REDIS_KEY_PREFIX . "alphabetical_category_stores_$category_id", $key, $response);
				}
				$data[$category_id][$key] = $response;
			}
		}
		return $data;
	}
	/** Get Host wise redirect links */
	public function getHostwiseRedirectLinks()
	{
		$result = [];
		$sql = "SELECT * FROM hostwise_redirect_links";
		$query = $this->stats_db->query($sql);
		$response = $query->fetchAll(PDO::FETCH_ASSOC);
		foreach ($response as $row) {
			$hash = self::REDIS_KEY_PREFIX . "redirect_links_" . $row['host_id'];
			$key = $row['brand_id'];
			$data = $row['redirect_url'];
			$this->redis->setRedisHashKeyData($hash, $key, $data);
			$result[$key] = $data;
		}
		return $response;
	}

	public function getAlphabeticalStores($alphabet)
	{
		$data = $this->redis->get_hash_data(SELF::REDIS_KEY_PREFIX . "alphabetical_stores", $alphabet);
		if (empty($data)) {
			$brand_name_condition = "AND ( CrawlerBrands.merchant_domain LIKE '$alphabet%' OR CrawlerBrands.brand_name LIKE '$alphabet%' )";
			if ($alphabet == '0-9') {
				$brand_name_condition = "AND ( CrawlerBrands.merchant_domain REGEXP '^[0-9]' OR CrawlerBrands.brand_name REGEXP '^[0-9]' )";
			}
			$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, count(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' $brand_name_condition GROUP BY store";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisHashKeyData(self::REDIS_KEY_PREFIX . "alphabetical_stores", $alphabet, $data);
		}
		return $data;
	}

	public function getAlphabeticalCategoryStores($alphabet, $category_id)
	{
		$data = $this->redis->get_hash_data(SELF::REDIS_KEY_PREFIX . "alphabetical_category_stores_$category_id", $alphabet);
		if (empty($data)) {
			$brand_name_condition = "AND ( CrawlerBrands.merchant_domain LIKE '$alphabet%' OR CrawlerBrands.brand_name LIKE '$alphabet%' )";
			if ($alphabet == '0-9') {
				$brand_name_condition = "AND ( CrawlerBrands.merchant_domain REGEXP '^[0-9]' OR CrawlerBrands.brand_name REGEXP '^[0-9]' )";
			}
			$sql = "SELECT CrawlerBrands.id, LOWER( ifnull(CrawlerBrands.merchant_domain, CrawlerBrands.brand_name) ) as store, count(CrawlerCoupons.id) as coupon_count FROM CrawlerBrands JOIN CrawlerCoupons ON CrawlerCoupons.brand_id = CrawlerBrands.id WHERE 
			CrawlerBrands.brand_image <> ''
			AND ( CrawlerCoupons.start_date <= NOW() AND ( CrawlerCoupons.validthru >= curdate() OR ( CrawlerCoupons.validthru IS NULL AND CrawlerCoupons.modified >= NOW() - interval 1 day ) ) ) AND CrawlerBrands.is_active = 1 AND CrawlerCoupons.description <> '' $brand_name_condition AND CrawlerBrands.category_id = $category_id GROUP BY store";
			$query = $this->stats_db->query($sql);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$this->redis->setRedisHashKeyData(self::REDIS_KEY_PREFIX . "alphabetical_category_stores_$category_id", $alphabet, $data);
		}
		return $data;
	}

	public function getSearchBrands($term)
	{
		return  $this->redis->searchInRedis($term);
	}
}