<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Category_m extends MY_Model
{

	protected $_table_name = 'categories';
	protected $_primary_key = 'id_categories';
	protected $_order_by = 'id_categories';

	public $rules = array(
		array(
			'field'   => 'category_name',
			'label'   => 'Nama Kategori Produk (id)',
			'rules'   => 'trim|required',
			/* 'rules'   => 'trim|required|callback__cek_existing_category_title' */
		),
		array(
			'field'   => 'category_name_en',
			'label'   => 'Nama Kategori Produk (en)',
			'rules'   => 'trim|required',
			/* 'rules'   => 'trim|required|callback__cek_existing_category_title' */
		),
		array(
			'field'   => 'status',
			'label'   => 'Status',
			'rules'   => 'trim|required'
		),
		array(
			'field'   => 'priority',
			'label'   => 'Urutan',
			'rules'   => 'trim|required|numeric'
		),
		array(
			'field'   => 'meta_description',
			'label'   => 'Deskripsi Meta',
			'rules'   => 'trim'
		),
		array(
			'field'   => 'meta_title',
			'label'   => 'Judul Browser',
			'rules'   => 'trim'
		),
	);


	function __construct()
	{
		parent::__construct();
	}

	public function get_all_categories()
	{
		$this->db->select('*');
		$this->db->from('categories');

		$query = $this->db->get();
		return $query->result();
	}

	public function get_category_by_id($category_id)
	{
		$this->db->select('*');
		$this->db->from('categories');
		$this->db->where('id_categories', $category_id);

		$query = $this->db->get();
		return $query->row();
	}


	public function get_all_parent_categories($limit, $start)
	{
		$this->db->select('id_categories, category, category_en, parent, priority, status');
		$this->db->from('categories');
		$this->db->where('parent', NULL);
		$this->db->order_by('priority', 'ASC');
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}

	//function to display new category, where all fields are empty
	public function get_new()
	{
		$categories = new stdClass();
		$categories->category = '';
		$categories->category_en = '';
		$categories->parent = '';
		$categories->status = '';
		$categories->priority = '';
		$categories->meta_description = '';
		$categories->meta_title = '';
		$categories->banner_link = '';
		$categories->description = '';
		$categories->description_en = '';
		return $categories;
	}

	//get all categories 
	function get_categories()
	{
		$this->db->select('*');
		$this->db->from('categories');
		$this->db->order_by('id_categories asc');
		$query = $this->db->get();
		return $query->result();
	}

	//function add new category
	function add_category($data)
	{
		$this->db->insert('categories', $data);
		$user_id = $this->session->userdata('admin')['id'];
		$activity = 'User menambah kategori(' . $data['category'] . ')';

		log_activity($user_id, $activity);
		return $this->db->insert_id();
	}

	//function edit category
	function edit_category($id, $data)
	{
		$this->db->where('id_categories', $id);
		$this->db->update('categories', $data);
		$user_id = $this->session->userdata('admin')['id'];
		$activity = 'User mengedit varian(' . $data['category'] . ')';

		log_activity($user_id, $activity);
	}

	//function count all record for category
	public function record_count()
	{
		return $this->db->select('id_categories')->from('categories')->where('parent', NULL)->get()->num_rows();
	}

	//function count if existing record exist
	public function count_exist($id)
	{
		$this->db->select('*');
		$this->db->from('categories');
		$this->db->where('id_categories', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}

	//FOR FRONT END REGISTRATION PAGE

	//get all categories to display at front registration page, with status display 1 only
	function get_categories_display_on()
	{
		$this->db->select('*');
		$this->db->from('categories');
		$this->db->where('status', '1');
		$this->db->where('id_categories !=', '0');
		$this->db->order_by('id_categories asc');
		$query = $this->db->get();
		return $query->result();
	}

	//cek if brand title already exist
	public function cek_existing_category_title($str, $category_id)
	{

		$this->db->select('id_categories');
		$this->db->from('categories');
		$this->db->where('category', $str);

		if ($category_id != NULL) {
			$this->db->where('id_categories !=', $category_id);
		}

		$query = $this->db->get();
		return $query->num_rows();
	}

	public function get_parent_categories()
	{

		$this->db->select('*')->from('categories')->where('parent', NULL);
		$query = $this->db->get();
		$parent_categories = $query->result();
		return  $parent_categories;
	}

	public function all_categories()
	{
		// Ambil parent categories dulu
		$this->db->select('id_categories, category, parent, alias, priority');
		$this->db->from('categories');
		$this->db->where('status', '1');
		$this->db->where('(parent IS NULL OR parent = "")');
		$this->db->order_by('priority', 'ASC');
		$parents = $this->db->get()->result();

		$structured_categories = [];

		foreach ($parents as $parent) {
			// Tambahkan parent ke array dengan parent_alias kosong
			$parent->parent_alias = null;
			$parent->is_child = false;
			$structured_categories[] = $parent;

			// Ambil child categories untuk parent ini
			$this->db->select('id_categories, category, parent, alias, priority');
			$this->db->from('categories');
			$this->db->where('status', '1');
			$this->db->where('parent', $parent->id_categories);
			$this->db->order_by('priority', 'ASC');
			$children = $this->db->get()->result();

			// Tambahkan children ke array dengan parent_alias
			foreach ($children as $child) {
				$child->parent_alias = $parent->alias; // Tambahkan alias parent
				$child->is_child = true;
				$structured_categories[] = $child;
			}
		}

		return $structured_categories;
	}

	public function count_categories($status = NULL)
	{
		$this->db->from('categories');
		if (!is_null($status)) {
			$this->db->where('status', (string) $status);
		}

		return $this->db->count_all_results();
	}

	public function fetch_parent_categories()
	{
		$this->db->select('*');
		$this->db->from('categories');
		$this->db->where('parent', null);
		$this->db->order_by('priority', 'ASC');
		$query = $this->db->get();
		return $query->result();
	}
	public function fetch_category_by_id($id_category)
	{
		$this->db->select('*')
			->from('categories')
			->where('id_categories', $id_category);

		return $this->db->get()->row();
	}
	public function search_categories($query)
	{
		$this->db->select('c.id_categories, c.category, c.category_en, c.alias, c.image, COUNT(DISTINCT p.id_products) as total_product');
		$this->db->from('categories c');
		$this->db->join('category_product cp', 'c.id_categories = cp.id_category', 'left');
		$this->db->join('products p', 'cp.id_product = p.id_products AND p.product_status = "1"', 'left');

		if ($query) {
			// Pecah query jadi kata-kata
			$keywords = array_filter(explode(' ', trim($query)));
			$fulltext_keywords = [];
			$like_keywords = [];

			// Pisahin kata yang mungkin angka atau pendek
			foreach ($keywords as $keyword) {
				if (is_numeric($keyword) || strpos($keyword, ',') !== false || strlen($keyword) < 4) {
					// Angka, desimal (00,1), atau kata pendek masuk ke LIKE
					$like_keywords[] = $keyword;
				} else {
					// Kata normal masuk ke FULLTEXT
					$fulltext_keywords[] = $keyword;
				}
			}

			$this->db->group_start();

			// FULLTEXT untuk kata-kata panjang
			if (!empty($fulltext_keywords)) {
				$fulltext_query = '+' . implode(' +', $fulltext_keywords);
				$this->db->where("MATCH(c.category, c.category_en) AGAINST('$fulltext_query' IN BOOLEAN MODE)", NULL, FALSE);
			}

			// LIKE untuk angka atau kata pendek
			if (!empty($like_keywords)) {
				$this->db->group_start();
				foreach ($like_keywords as $keyword) {
					$this->db->like('c.category', $keyword);
					$this->db->or_like('c.category_en', $keyword);
				}
				$this->db->group_end();
			}

			$this->db->group_end();
		}

		$this->db->where('c.status', '1');
		$this->db->group_by('c.id_categories, c.category, c.category_en, c.alias, c.image');
		$this->db->order_by('c.priority', 'ASC');
		$this->db->limit(5);

		// Urutkan berdasarkan relevansi FULLTEXT (jika ada)
		if (!empty($fulltext_keywords)) {
			$fulltext_query = '+' . implode(' +', $fulltext_keywords);
			$this->db->order_by("MATCH(c.category, c.category_en) AGAINST('$fulltext_query' IN BOOLEAN MODE) DESC");
		}

		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_popular_categories($limit = 4, $days = 30)
	{
		$date_filter = date('Y-m-d H:i:s', strtotime("-{$days} days"));

		// Step 1: Gunakan LIKE yang lebih sederhana daripada REGEX
		$sql = "
        SELECT 
            TRIM(SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(link_url, '?', 1), -- Remove query params
                    '/', -1  -- Get last segment
                ), '#', 1    -- Remove anchors
            )) as category_slug,
            COUNT(*) as click_count
        FROM link_tracks 
        WHERE link_url LIKE '%/shop/category/%' 
            AND is_robot = 0
            AND click_date >= ?
            AND LENGTH(TRIM(SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(link_url, '?', 1), 
                    '/', -1
                ), '#', 1
            ))) > 0
        GROUP BY category_slug
        HAVING click_count > 0
        ORDER BY click_count DESC
        LIMIT ?
    ";

		$category_clicks = $this->db->query($sql, array($date_filter, $limit * 2))->result_array();

		if (empty($category_clicks)) {
			return $this->get_fallback_categories($limit);
		}

		$category_slugs = array_column($category_clicks, 'category_slug');

		if (empty($category_slugs)) {
			return $this->get_fallback_categories($limit);
		}

		// Step 2: Single query dengan JOIN yang lebih efisien
		$placeholders = str_repeat('?,', count($category_slugs) - 1) . '?';

		$sql2 = "
			SELECT 
				c.category as name,
				c.alias_en as slug,
				c.image,
				COALESCE(cp_count.product_count, 0) as product_count
			FROM categories c
			LEFT JOIN (
				SELECT 
					id_category,
					COUNT(id_product) as product_count
				FROM category_product 
				WHERE id_category IS NOT NULL
				GROUP BY id_category
			) cp_count ON c.id_categories = cp_count.id_category
			WHERE c.status = '1' 
				AND c.alias_en IN ({$placeholders})
				AND c.image IS NOT NULL 
				AND c.image != ''
			ORDER BY FIELD(c.alias_en, {$placeholders})
			LIMIT ?
		";

		$params = array_merge($category_slugs, $category_slugs, array($limit));
		$result = $this->db->query($sql2, $params)->result_array();

		return $this->format_categories_result($result);
	}

	/**
	 * Alternative method with better URL parsing
	 * @param int $limit Number of categories to return
	 * @param int $days Number of days to consider for popularity (default: 30)
	 * @return array Popular categories with product count
	 */
	public function get_popular_categories_by_date($limit = 4, $days = 30)
	{
		$date_filter = date('Y-m-d H:i:s', strtotime("-{$days} days"));

		// Step 1: Get popular category slugs from recent clicks
		$sql = "
            SELECT 
                category_slug,
                click_count
            FROM (
                SELECT 
                    CASE 
                        WHEN link_url LIKE '%/shop/category/%/%' AND link_url NOT LIKE '%/shop/category/%/%/%' THEN 
                            SUBSTRING_INDEX(SUBSTRING_INDEX(link_url, '/', -1), '?', 1)
                        WHEN link_url LIKE '%/shop/category/%' THEN 
                            SUBSTRING_INDEX(SUBSTRING_INDEX(link_url, '/', -1), '?', 1)
                        ELSE NULL
                    END as category_slug,
                    COUNT(*) as click_count
                FROM link_tracks 
                WHERE link_url LIKE '%/shop/category/%' 
                    AND is_robot = 0
                    AND click_date >= ?
                GROUP BY category_slug
                HAVING category_slug IS NOT NULL AND category_slug != ''
            ) as category_stats
            ORDER BY click_count DESC
            LIMIT ?
        ";

		$category_clicks = $this->db->query($sql, array($date_filter, $limit * 2))->result_array();

		if (empty($category_clicks)) {
			return $this->get_fallback_categories($limit);
		}

		$category_slugs = array();
		foreach ($category_clicks as $click) {
			$category_slugs[] = $click['category_slug'];
		}

		// Step 2: Get category details with product counts
		$sql2 = "
            SELECT 
                c.category as name,
                c.alias_en as slug,
                c.image,
                COALESCE(pc.product_count, 0) as product_count
            FROM categories c
            LEFT JOIN (
                SELECT 
                    id_category,
                    COUNT(DISTINCT id_product) as product_count
                FROM category_product 
                GROUP BY id_category
            ) pc ON c.id_categories = pc.id_category
            WHERE c.status = '1' 
                AND c.alias_en IN (" . implode(',', array_fill(0, count($category_slugs), '?')) . ")
                AND c.image IS NOT NULL 
                AND c.image != ''
            ORDER BY FIELD(c.alias_en, " . implode(',', array_fill(0, count($category_slugs), '?')) . ")
            LIMIT ?
        ";

		$params = array_merge($category_slugs, $category_slugs, array($limit));
		$result = $this->db->query($sql2, $params)->result_array();

		// Format the result
		$popular_categories = array();
		foreach ($result as $row) {
			$popular_categories[] = array(
				'name' => $row['name'],
				'slug' => $row['slug'],
				'image' => $row['image'],
				'product_count' => (int)$row['product_count']
			);
		}

		return $popular_categories;
	}

	/**
	 * Get fallback categories when no tracking data available
	 * @param int $limit Number of categories to return
	 * @return array Categories ordered by product count
	 */
	private function get_fallback_categories($limit = 4)
	{
		$sql = "
			SELECT 
				c.category as name,
				c.alias_en as slug,
				c.image,
				COALESCE(cp_count.product_count, 0) as product_count
			FROM categories c
			LEFT JOIN (
				SELECT id_category, COUNT(id_product) as product_count
				FROM category_product 
				WHERE id_category IS NOT NULL
				GROUP BY id_category
			) cp_count ON c.id_categories = cp_count.id_category
			WHERE c.status = '1'
				AND c.image IS NOT NULL
				AND c.image != ''
				AND c.parent IS NULL
			ORDER BY cp_count.product_count DESC, c.priority ASC
			LIMIT ?
		";

		$result = $this->db->query($sql, array($limit))->result_array();

		return $this->format_categories_result($result);
	}

	private function format_categories_result($result)
	{
		$categories = array();
		foreach ($result as $row) {
			$categories[] = array(
				'name' => $row['name'],
				'slug' => $row['slug'],
				'image' => $row['image'],
				'product_count' => (int)$row['product_count']
			);
		}
		return $categories;
	}

	/**
	 * Get popular categories for footer data with improved error handling
	 * @param int $limit Number of categories to return
	 * @return array Formatted for footer usage
	 */
	public function get_footer_popular_categories($limit = 4)
	{
		// Try to get popular categories from tracking data
		$popular_categories = $this->get_popular_categories_by_date($limit, 30);

		// If no results, use fallback
		if (empty($popular_categories)) {
			$popular_categories = $this->get_fallback_categories($limit);
		}

		// Ensure we have at least some categories
		if (empty($popular_categories)) {
			// Last resort: get any active categories
			$this->db->select('category as name, alias_en as slug, image, 0 as product_count');
			$this->db->from('categories');
			$this->db->where('status', '1');
			$this->db->where('image IS NOT NULL');
			$this->db->where('image !=', '');
			$this->db->limit($limit);

			$result = $this->db->get()->result_array();

			foreach ($result as $row) {
				$popular_categories[] = array(
					'name' => $row['name'],
					'slug' => $row['slug'],
					'image' => $row['image'],
					'product_count' => 0
				);
			}
		}

		return $popular_categories;
	}
}