<?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();
	}

	//pagination included
	// function get_all_categories($limit, $start)
	// {
	// 	$this->db->select('*');
	// 	$this->db->from('categories');
	// 	$this->db->where('id_categories !=', '0');
	// 	$this->db->order_by('parent', 'ASC');
	// 	$this->db->order_by('priority', 'ASC');
	// 	$this->db->limit($limit, $start);
	// 	$query = $this->db->get();
	// 	return $query->result();
	// }
	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()
	{
		$this->db->select('id_categories, category, parent');
		$this->db->from('categories');
		$this->db->where('status', '1'); // Hanya ambil kategori aktif
		$this->db->order_by('priority', 'ASC');
		$query = $this->db->get();
		return $query->result();
	}

	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');
		$this->db->where('c.status', '1');
		$this->db->group_start();
		$this->db->like('c.category', $query);
		$this->db->or_like('c.category_en', $query);
		$this->db->group_end();
		$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);
		$query = $this->db->get();

		return $query->result_array();
	}

	public function get_popular_categories($limit = 4)
	{
		// Step 1: Get category clicks from link_tracks
		$this->db->select("
            CASE 
                WHEN link_url REGEXP 'shop/category/[^/]+/[^/?]+' AND link_url REGEXP 'shop/category/[^/]+/[^/]+/[^/?]+' THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(link_url, '?', 1), '#', ''), '/', -1), '/', 1)
                ELSE 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(link_url, '?', 1), '#', ''), '/', -1), '/', 1)
            END as category_slug,
            COUNT(*) as click_count
        ");
		$this->db->from('link_tracks');
		$this->db->where('link_url LIKE', '%/shop/category/%');
		$this->db->where('is_robot', 0);
		$this->db->group_by('category_slug');
		$this->db->having('category_slug IS NOT NULL');
		$this->db->having('category_slug != ""');
		$this->db->order_by('click_count', 'DESC');
		$this->db->limit($limit * 3); // Get more to filter later

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

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

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

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

		// Step 2: Get category details with product counts
		$this->db->select('
            c.category as name,
            c.alias_en as slug,
            c.image,
            COALESCE(pc.product_count, 0) as product_count
        ');
		$this->db->from('categories c');

		// Left join for product counts
		$this->db->join('(
            SELECT 
                id_category,
                COUNT(DISTINCT id_product) as product_count
            FROM category_product 
            GROUP BY id_category
        ) pc', 'c.id_categories = pc.id_category', 'left');

		$this->db->where('c.status', '1');
		$this->db->where_in('c.alias_en', $category_slugs);
		$this->db->where('c.image IS NOT NULL');
		$this->db->where('c.image !=', '');

		// Order by click popularity
		$click_order = array();
		foreach ($category_clicks as $index => $click) {
			$click_order[$click['category_slug']] = $index;
		}

		$this->db->order_by('pc.product_count', 'DESC');
		$this->db->limit($limit);

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

		// Step 3: Sort by original click order and format result
		$popular_categories = array();
		$found_slugs = array();

		// First, add categories in click order
		foreach ($category_clicks as $click) {
			foreach ($result as $row) {
				if ($row['slug'] == $click['category_slug'] && !in_array($row['slug'], $found_slugs)) {
					$popular_categories[] = array(
						'name' => $row['name'],
						'slug' => $row['slug'],
						'image' => $row['image'],
						'product_count' => (int)$row['product_count']
					);
					$found_slugs[] = $row['slug'];
					break;
				}
			}
			if (count($popular_categories) >= $limit) {
				break;
			}
		}

		// Fill remaining slots if needed
		if (count($popular_categories) < $limit) {
			foreach ($result as $row) {
				if (!in_array($row['slug'], $found_slugs)) {
					$popular_categories[] = array(
						'name' => $row['name'],
						'slug' => $row['slug'],
						'image' => $row['image'],
						'product_count' => (int)$row['product_count']
					);
					if (count($popular_categories) >= $limit) {
						break;
					}
				}
			}
		}

		return $popular_categories;
	}

	/**
	 * 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)
	{
		$this->db->select('
            c.category as name,
            c.alias_en as slug,
            c.image,
            COALESCE(pc.product_count, 0) as product_count
        ');
		$this->db->from('categories c');

		// Left join for product counts using subquery
		$this->db->join('(
            SELECT 
                id_category,
                COUNT(DISTINCT id_product) as product_count
            FROM category_product 
            GROUP BY id_category
        ) pc', 'c.id_categories = pc.id_category', 'left');

		$this->db->where('c.status', '1');
		$this->db->where('c.image IS NOT NULL');
		$this->db->where('c.image !=', '');
		$this->db->where('c.parent IS NULL'); // Only parent categories
		$this->db->order_by('pc.product_count', 'DESC');
		$this->db->order_by('c.priority', 'ASC');
		$this->db->limit($limit);

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

		$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;
	}
}