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

class Statistic extends Admin_Controller
{

	function __construct()
	{
		parent::__construct();
		$this->load->model('statistic_m');
		$this->load->model('log_m');
		$this->load->model('Product_m');
		$this->load->model('customer_m');
	}

	//this is to list all homepage
	public function index()
	{

		//load view
		$this->data['subview'] = 'admin/statistic/index';
		$this->load->view('admin/templates/header', $this->data);
		$this->load->view('admin/_layout_main', $this->data);
		$this->load->view('admin/templates/footer', $this->data);
	}

	// Sales Statistics
	public function sales_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Penjualan | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/sales_statistics');
		$this->load->view('admin_new/layouts/footer');
	}

	public function get_sales_data()
	{
		$date_filter = $this->input->get('date_filter', 'last7days');
		$start_date = $this->input->get('start_date');
		$end_date = $this->input->get('end_date');

		// Tentukan rentang tanggal berdasarkan filter
		switch ($date_filter) {
			case 'today':
				$start_date = date('Y-m-d');
				$end_date = date('Y-m-d');
				break;
			case 'yesterday':
				$start_date = date('Y-m-d', strtotime('-1 day'));
				$end_date = date('Y-m-d', strtotime('-1 day'));
				break;
			case 'last7days':
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
			case 'last30days':
				$start_date = date('Y-m-d', strtotime('-29 days'));
				$end_date = date('Y-m-d');
				break;
			case 'thisMonth':
				$start_date = date('Y-m-01');
				$end_date = date('Y-m-t');
				break;
			case 'thisYear':
				$start_date = date('Y-01-01');
				$end_date = date('Y-12-31');
				break;
			case 'custom':
				if ($start_date && $end_date) {
					$start_date = date('Y-m-d', strtotime($start_date));
					$end_date = date('Y-m-d', strtotime($end_date));
				}
				break;
			default:
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
		}

		// Hitung tanggal tahun lalu untuk perbandingan
		$previous_start_date = date('Y-m-d', strtotime('-1 year', strtotime($start_date)));
		$previous_end_date = date('Y-m-d', strtotime('-1 year', strtotime($end_date)));

		// Order type filters
		$order_type_filters = [
			'isWebsiteOrder' => $this->input->get('isWebsiteOrder'),
			'isRetailerOrder' => $this->input->get('isRetailerOrder'),
			'isTokopediaOrder' => $this->input->get('isTokopediaOrder'),
			'isAllOrder' => $this->input->get('isAllOrder'),
		];

		// Payment method filters
		$payment_filters = [
			'isBCATransfer' => $this->input->get('isBCATransfer'),
			'isMandiriTransfer' => $this->input->get('isMandiriTransfer'),
			'isPaypal' => $this->input->get('isPaypal'),
			'isDoku' => $this->input->get('isDoku')
		];

		// Shipping method filters
		$shipping_filters = [
			'isRegularShipping' => $this->input->get('isRegularShipping'),
			'isTwoHourShipping' => $this->input->get('isTwoHourShipping'),
			'isOneDayShipping' => $this->input->get('isOneDayShipping'),
			'isNextDayShipping' => $this->input->get('isNextDayShipping')
		];

		// Insurance filters
		$insurance_filters = [
			'usingInsurance' => $this->input->get('usingInsurance'),
			'notUsingInsurance' => $this->input->get('notUsingInsurance')
		];

		// Search parameter
		$search = $this->input->get('search');

		// Sort parameter
		$sort = $this->input->get('sort', '');

		// Get current period sales data with all filters
		$current_sales_data = $this->statistic_m->get_sales_data(
			$start_date,
			$end_date,
			$payment_filters,
			$shipping_filters,
			$insurance_filters,
			$order_type_filters,
			$search,
			$sort
		);

		// Get previous year sales data with same filters
		$previous_sales_data = $this->statistic_m->get_sales_data(
			$previous_start_date,
			$previous_end_date,
			$payment_filters,
			$shipping_filters,
			$insurance_filters,
			$order_type_filters,
			$search,
			$sort
		);

		// Get daily data for charts
		$chart_data = $this->statistic_m->get_comparison_chart_data(
			$start_date,
			$end_date,
			$previous_start_date,
			$previous_end_date,
			$payment_filters,
			$shipping_filters,
			$insurance_filters,
			$order_type_filters
		);

		// Calculate summary statistics
		$current_total = array_sum(array_column($current_sales_data['sales'], 'grand_total_amount'));
		$previous_total = array_sum(array_column($previous_sales_data['sales'], 'grand_total_amount'));
		$growth_percentage = ($previous_total > 0) ? (($current_total - $previous_total) / $previous_total * 100) : 100;

		$this->output
			->set_content_type('application/json')
			->set_output(json_encode([
				'current_sales' => $current_sales_data['sales'],
				'previous_sales' => $previous_sales_data['sales'],
				'start_date' => $start_date,
				'end_date' => $end_date,
				'previous_start_date' => $previous_start_date,
				'previous_end_date' => $previous_end_date,
				'current_total' => $current_total,
				'previous_total' => $previous_total,
				'growth_percentage' => round($growth_percentage, 2),
				'chart_data' => $chart_data
			]));
	}

	// Visit Statistics
	public function visit_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Kunjungan | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/visit_statistics');
		$this->load->view('admin_new/layouts/footer');
	}

	public function get_visit_data()
	{
		// Default filter: last 7 days
		$date_filter = $this->input->get('date_filter', 'last7days');
		$start_date = $this->input->get('start_date');
		$end_date = $this->input->get('end_date');

		// Tentukan rentang tanggal berdasarkan filter
		switch ($date_filter) {
			case 'today':
				$start_date = date('Y-m-d');
				$end_date = date('Y-m-d');
				break;
			case 'yesterday':
				$start_date = date('Y-m-d', strtotime('-1 day'));
				$end_date = date('Y-m-d', strtotime('-1 day'));
				break;
			case 'last7days':
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
			case 'last30days':
				$start_date = date('Y-m-d', strtotime('-29 days'));
				$end_date = date('Y-m-d');
				break;
			case 'thisMonth':
				$start_date = date('Y-m-01');
				$end_date = date('Y-m-t');
				break;
			case 'thisYear':
				$start_date = date('Y-01-01');
				$end_date = date('Y-12-31');
				break;
			case 'custom':
				if ($start_date && $end_date) {

					$startDate = date('Y-m-d', strtotime($start_date));
					$endDate = date('Y-m-d', strtotime($end_date));
					if ($startDate && $endDate) {
						$this->db->where('DATE(click_date) >=', $start_date);
						$this->db->where('DATE(click_date) <=', $end_date);
					}
				}
				break;
		}

		// Ambil data kunjungan
		$visits_data = $this->statistic_m->get_visits_by_date_range($start_date, $end_date);
		$top_traffic_sources = $this->statistic_m->get_top_traffic_sources($start_date, $end_date);
		$top_internal_traffic_sources = $this->statistic_m->get_internal_traffic_sources($start_date, $end_date);
		$engagement_metrics = $this->statistic_m->get_engagement_metrics($start_date, $end_date);
		$peak_hours = $this->statistic_m->get_peak_hours($start_date, $end_date, 5);
		$keywords = $this->statistic_m->get_top_search_keywords($start_date, $end_date, 15);
		$device_performance = $this->statistic_m->get_device_performance($start_date, $end_date);

		$hours_labels = [];
		$hours_clicks = [];
		$hours_unique_visitors = [];

		foreach ($peak_hours as $hour) {
			$hours_labels[] = $hour['formatted_hour'];
			$hours_clicks[] = (int)$hour['total_clicks'];
			$hours_unique_visitors[] = (int)$hour['unique_visitors'];
		}

		$keywords_labels = [];
		$keywords_searches = [];
		$keywords_unique_searchers = [];
		foreach ($keywords as $keyword) {
			$keywords_labels[] = $keyword['search_query'];
			$keywords_searches[] = (int)$keyword['total_searches'];
			$keywords_unique_searchers[] = (int)$keyword['unique_searchers'];
		}

		$device_labels = [];
		$device_visits = [];
		$device_unique_visitors = [];
		$device_percentages = [];
		$device_avg_time = [];
		$device_bounce_rates = [];

		foreach ($device_performance as $device) {
			$device_labels[] = $device['device_type'];
			$device_visits[] = (int)$device['total_visits'];
			$device_unique_visitors[] = (int)$device['unique_visitors'];
			$device_percentages[] = (float)$device['percentage'];
			$device_avg_time[] = $device['avg_time_formatted'];
			$device_bounce_rates[] = (float)$device['bounce_rate_percent'];
		}

		// Response JSON
		$this->output
			->set_content_type('application/json')
			->set_output(json_encode([
				'labels' => $visits_data['labels'],
				'visits' => $visits_data['visits'],
				'prev_visits' => $visits_data['prev_visits'],
				'total_visits' => $visits_data['total_visits'],
				'prev_total_visits' => $visits_data['prev_total_visits'],
				'unique_visits' => $visits_data['unique_visits'],
				'prev_unique_visits' => $visits_data['prev_unique_visits'],
				'start_date' => $start_date,
				'end_date' => $end_date,
				'top_traffic_sources' => $top_traffic_sources,
				'top_internal_traffic_sources' => $top_internal_traffic_sources,
				'average_time_seconds' => $engagement_metrics['average_time_seconds'],
				'average_time_formatted' => $engagement_metrics['average_time_formatted'],
				'bounce_rate' => $engagement_metrics['bounce_rate'],

				'hours_labels' => $hours_labels,
				'hours_clicks' => $hours_clicks,
				'hours_unique_visitors' => $hours_unique_visitors,

				'keywords_labels' => $keywords_labels,
				'keywords_searches' => $keywords_searches,
				'keywords_unique_searchers' => $keywords_unique_searchers,

				'device_labels' => $device_labels,
				'device_visits' => $device_visits,
				'device_unique_visitors' => $device_unique_visitors,
				'device_percentages' => $device_percentages,
				'device_avg_time' => $device_avg_time,
				'device_bounce_rates' => $device_bounce_rates,
			]));
	}

	// Customer Statistics
	public function customer_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Pelanggan | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/customer_statistics');
		$this->load->view('admin_new/layouts/footer');
	}

	/**
	 * Main endpoint untuk customer statistics
	 */
	public function get_customer_data()
	{
		try {
			// Get filter parameters
			$date_filter = $this->input->get('date_filter') ?: 'last7days';
			$start_date = $this->input->get('start_date');
			$end_date = $this->input->get('end_date');
			$group_by = $this->input->get('group_by') ?: 'day'; // day, week, month

			// Get date range
			$date_range = $this->customer_m->get_date_range($date_filter, $start_date, $end_date);
			$current_start = $date_range['start'];
			$current_end = $date_range['end'];

			// Get previous period for comparison
			$prev_period = $this->customer_m->get_previous_period($current_start, $current_end);

			// 1. OVERVIEW METRICS
			$new_customers_current = $this->customer_m->get_new_customers($current_start, $current_end);
			$new_customers_prev = $this->customer_m->get_new_customers($prev_period['start'], $prev_period['end']);

			// Calculate growth rate
			$growth_rate = 0;
			if ($new_customers_prev > 0) {
				$growth_rate = (($new_customers_current - $new_customers_prev) / $new_customers_prev) * 100;
			} elseif ($new_customers_current > 0) {
				$growth_rate = 100;
			}

			// Total customers by type
			$total_by_type = $this->customer_m->get_total_customers_by_type();

			// 2. DEMOGRAPHICS
			$gender_breakdown = $this->customer_m->get_gender_breakdown($current_start, $current_end);
			$age_breakdown = $this->customer_m->get_age_breakdown($current_start, $current_end);
			$location_breakdown = $this->customer_m->get_location_breakdown($current_start, $current_end);

			// 3. REGISTRATION TREND
			$registration_trend = $this->customer_m->get_registration_trend($current_start, $current_end, $group_by);

			// 4. SEGMENTATION
			$source_breakdown = $this->customer_m->get_source_breakdown($current_start, $current_end);
			$oauth_breakdown = $this->customer_m->get_oauth_breakdown($current_start, $current_end);
			$newsletter_stats = $this->customer_m->get_newsletter_stats($current_start, $current_end);
			$dropshipper_stats = $this->customer_m->get_dropshipper_stats($current_start, $current_end);

			// 5. AFFILIATE & REWARDS
			$affiliate_stats = $this->customer_m->get_affiliate_stats();
			$point_rewards = $this->customer_m->get_point_rewards_stats();

			// 6. ADDRESS STATISTICS
			$address_stats = $this->customer_m->get_address_stats();

			// 7. DATA QUALITY
			$data_quality = $this->customer_m->get_data_quality_metrics($current_start, $current_end);

			// Prepare response
			$response = [
				'success' => true,
				'meta' => [
					'date_filter' => $date_filter,
					'start_date' => $current_start,
					'end_date' => $current_end,
					'previous_period' => $prev_period,
					'generated_at' => date('Y-m-d H:i:s')
				],
				'data' => [
					// Overview KPIs
					'overview' => [
						'total_customers' => array_sum($total_by_type),
						'total_by_type' => $total_by_type,
						'new_customers' => [
							'current_period' => $new_customers_current,
							'previous_period' => $new_customers_prev,
							'growth_rate' => round($growth_rate, 2),
							'growth_percentage' => round($growth_rate, 2) . '%'
						]
					],

					// Demographics
					'demographics' => [
						'gender' => $gender_breakdown,
						'age' => $age_breakdown,
						'location' => $location_breakdown
					],

					// Trends
					'trends' => [
						'registration' => $registration_trend
					],

					// Segmentation
					'segmentation' => [
						'source' => $source_breakdown,
						'oauth_provider' => $oauth_breakdown,
						'newsletter' => $newsletter_stats,
						'dropshipper' => $dropshipper_stats
					],

					// Engagement
					'engagement' => [
						'affiliate' => $affiliate_stats,
						'point_rewards' => $point_rewards
					],

					// Address Statistics
					'address' => $address_stats,

					// Data Quality
					'data_quality' => $data_quality
				]
			];

			$this->output
				->set_content_type('application/json')
				->set_status_header(200)
				->set_output(json_encode($response, JSON_PRETTY_PRINT));
		} catch (Exception $e) {
			$this->output
				->set_content_type('application/json')
				->set_status_header(500)
				->set_output(json_encode([
					'success' => false,
					'message' => 'Internal server error',
					'error' => $e->getMessage()
				]));
		}
	}

	// Product Statistics
	public function product_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Produk | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/product_statistics');
		$this->load->view('admin_new/layouts/footer', $data);
	}

	public function get_product_data()
	{
		// Default filter: last 7 days
		$date_filter = $this->input->get('date_filter', 'last7days');
		$start_date = $this->input->get('start_date');
		$end_date = $this->input->get('end_date');

		$searchTerm = $this->input->get('search', true);

		// Tentukan rentang tanggal berdasarkan filter
		switch ($date_filter) {
			case 'today':
				$start_date = date('Y-m-d');
				$end_date = date('Y-m-d');
				break;
			case 'yesterday':
				$start_date = date('Y-m-d', strtotime('-1 day'));
				$end_date = date('Y-m-d', strtotime('-1 day'));
				break;
			case 'last7days':
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
			case 'last30days':
				$start_date = date('Y-m-d', strtotime('-29 days'));
				$end_date = date('Y-m-d');
				break;
			case 'thisMonth':
				$start_date = date('Y-m-01');
				$end_date = date('Y-m-t');
				break;
			case 'thisYear':
				$start_date = date('Y-01-01');
				$end_date = date('Y-12-31');
				break;
			case 'custom':
				if ($start_date && $end_date) {
					$start_date = date('Y-m-d', strtotime($start_date));
					$end_date = date('Y-m-d', strtotime($end_date));
				}
				break;
		}

		// Query untuk mendapatkan data produk dengan metrik
		$this->db->select('
			p.id_products, 
			p.title, 
			p.alias,
			pi.image,
			b.brand as brand_title,
			COALESCE(view_counts.view_count, 0) as view_count,
			COALESCE(sold_counts.total_sold, 0) as sold_count,
			COALESCE(wishlist_counts.total_wishlist, 0) as wishlist_count,
			COALESCE(cart_counts.total_cart, 0) as cart_count
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('brands b', 'p.brand_id = b.id_brands', 'left');
		$this->db->join('product_images pi', 'pi.product_details_id = pd.id AND pi.priority = 1 AND pi.status = 1', 'left');

		// Subquery untuk jumlah dilihat (dari link_tracks)
		$this->db->join('(
			SELECT 
				SUBSTRING_INDEX(lt.link_url, "/product/", -1) as product_alias,
				COUNT(*) as view_count
			FROM 
				link_tracks lt
			WHERE 
				lt.link_url LIKE "%/product/%" AND
				DATE(lt.click_date) >= "' . $start_date . '" AND 
				DATE(lt.click_date) <= "' . $end_date . '"
			GROUP BY 
				product_alias
		) view_counts', 'p.alias = view_counts.product_alias', 'left');

		// Subquery untuk jumlah terjual (dari orders_detail)
		$this->db->join('(
			SELECT 
				od.product_id,
				COUNT(od.product_id) as total_sold
			FROM 
				orders_detail od
			JOIN 
				orders o ON od.orders_id = o.id_orders
			WHERE 
				o.payment_status = 5 AND
				DATE(o.order_date) >= "' . $start_date . '" AND 
				DATE(o.order_date) <= "' . $end_date . '"
			GROUP BY 
				od.product_id
		) sold_counts', 'p.id_products = sold_counts.product_id', 'left');

		// Subquery untuk jumlah wishlist
		$this->db->join('(
			SELECT 
				product_id,
				COUNT(*) as total_wishlist
			FROM 
				wishlists
			WHERE 
				DATE(created_at) >= "' . $start_date . '" AND 
				DATE(created_at) <= "' . $end_date . '"
			GROUP BY 
				product_id
		) wishlist_counts', 'p.id_products = wishlist_counts.product_id', 'left');

		// Subquery untuk jumlah cart
		$this->db->join('(
			SELECT 
				product_id,
				COUNT(*) as total_cart
			FROM 
				cart_history
			WHERE 
				DATE(created_at) >= "' . $start_date . '" AND 
				DATE(created_at) <= "' . $end_date . '"
			GROUP BY 
				product_id
		) cart_counts', 'p.id_products = cart_counts.product_id', 'left');

		if (!empty($searchTerm)) {
			$this->db->group_start();
			$this->db->like('p.title', $searchTerm);
			$this->db->or_like('b.brand', $searchTerm);
			$this->db->group_end();
		}
		$this->db->where('p.product_status', '1'); // Only active products
		$this->db->where('p.deleted_at', null);
		$this->db->group_by('p.id_products');
		$this->db->order_by('view_count', 'DESC');

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

		// Ambil total produk
		$this->db->where('product_status', '1');
		$total_products = $this->db->count_all_results('products');

		// Ambil top 5 kategori berdasarkan penjualan
		$this->db->select('
			c.category as category_name,
			COUNT(od.product_id) as total_sold
		');
		$this->db->from('orders_detail od');
		$this->db->join('orders o', 'od.orders_id = o.id_orders', 'left');
		$this->db->join('category_product cp', 'od.product_id = cp.id_product', 'left');
		$this->db->join('categories c', 'cp.id_category = c.id_categories', 'left');
		$this->db->where('o.payment_status', 5);
		$this->db->where('DATE(o.order_date) >=', $start_date);
		$this->db->where('DATE(o.order_date) <=', $end_date);
		$this->db->group_by('c.id_categories');
		$this->db->order_by('total_sold', 'DESC');
		$this->db->limit(5);

		$top_categories_query = $this->db->get();
		$top_categories = $top_categories_query->result_array();

		// Ambil produk dengan sold terbanyak (paling laris)
		$this->db->select('p.title as product_name');
		$this->db->from('products p');
		$this->db->join('(
			SELECT 
				od.product_id,
				COUNT(od.product_id) as total_sold
			FROM 
				orders_detail od
			JOIN 
				orders o ON od.orders_id = o.id_orders
			WHERE 
				o.payment_status = 5 AND
				DATE(o.order_date) >= "' . $start_date . '" AND 
				DATE(o.order_date) <= "' . $end_date . '"
			GROUP BY 
				od.product_id
		) sold_counts', 'p.id_products = sold_counts.product_id', 'inner');
		$this->db->where('p.product_status', '1');
		$this->db->where('p.deleted_at', null);
		$this->db->order_by('sold_counts.total_sold', 'DESC');
		$this->db->limit(1);
		$best_selling_query = $this->db->get();
		$best_selling_product = $best_selling_query->row_array();

		// Ambil produk dengan view terbanyak
		$this->db->select('p.title as product_name');
		$this->db->from('products p');
		$this->db->join('(
			SELECT 
				SUBSTRING_INDEX(lt.link_url, "/product/", -1) as product_alias,
				COUNT(*) as view_count
			FROM 
				link_tracks lt
			WHERE 
				lt.link_url LIKE "%/product/%" AND
				DATE(lt.click_date) >= "' . $start_date . '" AND 
				DATE(lt.click_date) <= "' . $end_date . '"
			GROUP BY 
				product_alias
		) view_counts', 'p.alias = view_counts.product_alias', 'inner');
		$this->db->where('p.product_status', '1');
		$this->db->where('p.deleted_at', null);
		$this->db->order_by('view_counts.view_count', 'DESC');
		$this->db->limit(1);
		$most_viewed_query = $this->db->get();
		$most_viewed_product = $most_viewed_query->row_array();

		// Ambil produk dengan sold paling sedikit (kurang diminati)
		$this->db->select('p.title as product_name');
		$this->db->from('products p');
		$this->db->join('(
			SELECT 
				od.product_id,
				COUNT(od.product_id) as total_sold
			FROM 
				orders_detail od
			JOIN 
				orders o ON od.orders_id = o.id_orders
			WHERE 
				o.payment_status = 5 AND
				DATE(o.order_date) >= "' . $start_date . '" AND 
				DATE(o.order_date) <= "' . $end_date . '"
			GROUP BY 
				od.product_id
		) sold_counts', 'p.id_products = sold_counts.product_id', 'inner');
		$this->db->where('p.product_status', '1');
		$this->db->where('p.deleted_at', null);
		$this->db->order_by('sold_counts.total_sold', 'ASC');
		$this->db->limit(1);
		$least_selling_query = $this->db->get();
		$least_selling_product = $least_selling_query->row_array();

		// Ambil produk yang rusak
		$this->db->select('COUNT(*) as total_defected, p.title as product_name');
		$this->db->from('claim_garansi cg');
		$this->db->join('orders_detail od', 'cg.no_order_detail_id = od.id_orders_detail');
		$this->db->join('products p', 'od.product_id = p.id_products');
		$this->db->group_by('p.title');
		$defected_product_query = $this->db->get();
		$defected_product = $defected_product_query->result_array();

		// Ambil semua ID produk yang sudah pernah dipesan (sepanjang waktu, tidak terbatas rentang tanggal)
		$this->db->select('DISTINCT(od.product_id)');
		$this->db->from('orders_detail od');
		$this->db->join('orders o', 'od.orders_id = o.id_orders');
		$this->db->where_in('o.payment_status', [5, 8]); // Hanya transaksi yang sudah dibayar
		// Hilangkan filter tanggal di sini

		$ordered_query = $this->db->get();
		$ordered_products = [];

		// Kumpulkan ID ke array
		if ($ordered_query->num_rows() > 0) {
			foreach ($ordered_query->result_array() as $row) {
				$ordered_products[] = $row['product_id'];
			}
		}

		// Log untuk debugging
		log_message('debug', 'All time ordered products count: ' . count($ordered_products));

		// Query untuk produk yang tidak pernah dipesan sama sekali
		$this->db->select('p.id_products, p.title as product_name, b.brand as brand_title, p.alias, pi.image, pd.sku');
		$this->db->from('products p');
		$this->db->join('brands b', 'p.brand_id = b.id_brands', 'left');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('product_images pi', 'pi.product_details_id = pd.id AND pi.priority = 1 AND pi.status = 1', 'left');
		$this->db->where('p.product_status', '1'); // Hanya produk aktif
		$this->db->where('p.deleted_at', null);

		// Filter produk yang NULL sebelum digunakan dalam where_not_in
		$ordered_products = array_filter($ordered_products, function ($value) {
			return $value !== NULL;
		});

		if (!empty($ordered_products)) {
			$this->db->where_not_in('p.id_products', $ordered_products);
		}

		$this->db->order_by('p.title', 'ASC');
		$never_ordered_query = $this->db->get();
		log_message('debug', 'Never ordered all time SQL: ' . $this->db->last_query());

		$never_ordered_products = $never_ordered_query->result_array();
		$never_ordered_count = count($never_ordered_products);

		$this->output
			->set_content_type('application/json')
			->set_output(json_encode([
				'start_date' => $start_date,
				'end_date' => $end_date,
				'totals' => [
					'products' => $total_products,
					'never_ordered' => $never_ordered_count
				],
				'top_categories' => $top_categories,
				'best_selling_product' => $best_selling_product ? $best_selling_product['product_name'] : null,
				'most_viewed_product' => $most_viewed_product ? $most_viewed_product['product_name'] : null,
				'least_selling_product' => $least_selling_product ? $least_selling_product['product_name'] : null,
				'never_ordered_products' => $never_ordered_products,
				'product_breakdown' => $product_breakdown,
				'defected_product' => $defected_product
			]));
	}

	public function stock_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Stok | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/stock_statistics');
		$this->load->view('admin_new/layouts/footer', $data);
	}

	public function get_stock_data()
	{
		// Default filter: last 7 days
		$date_filter = $this->input->get('date_filter', true) ?: 'last7days';
		$start_date = $this->input->get('start_date', true);
		$end_date = $this->input->get('end_date', true);
		$searchTerm = $this->input->get('search', true);
		$page = max(1, (int)$this->input->get('page', true) ?: 1);
		$limit = max(1, (int)$this->input->get('limit', true) ?: 250);
		$offset = ($page - 1) * $limit;

		// Set date range based on filter
		switch ($date_filter) {
			case 'today':
				$start_date = date('Y-m-d');
				$end_date = date('Y-m-d');
				break;
			case 'yesterday':
				$start_date = date('Y-m-d', strtotime('-1 day'));
				$end_date = date('Y-m-d', strtotime('-1 day'));
				break;
			case 'last7days':
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
			case 'last30days':
				$start_date = date('Y-m-d', strtotime('-29 days'));
				$end_date = date('Y-m-d');
				break;
			case 'thisMonth':
				$start_date = date('Y-m-01');
				$end_date = date('Y-m-t');
				break;
			case 'thisYear':
				$start_date = date('Y-01-01');
				$end_date = date('Y-12-31');
				break;
			case 'custom':
				if ($start_date && $end_date) {
					$start_date = date('Y-m-d', strtotime($start_date));
					$end_date = date('Y-m-d', strtotime($end_date));
				} else {
					// Default to last 7 days if custom dates are invalid
					$start_date = date('Y-m-d', strtotime('-6 days'));
					$end_date = date('Y-m-d');
				}
				break;
			default:
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
		}

		// Validasi tanggal
		if (strtotime($start_date) > strtotime($end_date)) {
			$this->output
				->set_status_header(400)
				->set_content_type('application/json')
				->set_output(json_encode(['error' => 'Tanggal mulai tidak boleh melebihi tanggal akhir']));
			return;
		}

		try {
			// Get stock statistics for all products
			$result = $this->get_stock_statistics($start_date, $end_date, $searchTerm);

			// Calculate totals
			$total_products = count($result);
			$total_opening_stock = 0;
			$total_incoming_stock = 0;
			$total_outgoing_stock = 0;
			$total_closing_stock = 0;

			$top_moving_products = [];
			$most_stocked_products = [];

			if (!empty($result)) {
				foreach ($result as $item) {
					$total_opening_stock += $item['opening_stock'];
					$total_incoming_stock += $item['incoming_stock'];
					$total_outgoing_stock += $item['outgoing_stock'];
					$total_closing_stock += $item['closing_stock'];
				}

				// Get top movers (products with highest outgoing stock)
				$top_moving_products = $result;
				usort($top_moving_products, function ($a, $b) {
					return $b['outgoing_stock'] <=> $a['outgoing_stock'];
				});
				$top_moving_products = array_slice($top_moving_products, 0, 5);

				// Get most stocked products (products with highest closing stock)
				$most_stocked_products = $result;
				usort($most_stocked_products, function ($a, $b) {
					return $b['closing_stock'] <=> $a['closing_stock'];
				});
				$most_stocked_products = array_slice($most_stocked_products, 0, 5);
			}

			// Apply pagination to main result
			$paginated_result = array_slice($result, $offset, $limit);

			$this->output
				->set_content_type('application/json')
				->set_output(json_encode([
					'start_date' => $start_date,
					'end_date' => $end_date,
					'summary' => [
						'total_products' => $total_products,
						'total_opening_stock' => $total_opening_stock,
						'total_incoming_stock' => $total_incoming_stock,
						'total_outgoing_stock' => $total_outgoing_stock,
						'total_closing_stock' => $total_closing_stock
					],
					'top_moving_products' => $top_moving_products,
					'most_stocked_products' => $most_stocked_products,
					'stock_data' => $paginated_result,
					'pagination' => [
						'page' => (int)$page,
						'limit' => (int)$limit,
						'total_records' => $total_products,
					]
				]));
		} catch (Exception $e) {
			$this->output
				->set_status_header(500)
				->set_content_type('application/json')
				->set_output(json_encode(['error' => 'Terjadi kesalahan dalam memproses data: ' . $e->getMessage()]));
		}
	}

	private function get_stock_statistics($start_date, $end_date, $searchTerm = null)
	{
		// Konversi format tanggal untuk query
		$start_datetime = date('Y-m-d 00:00:00', strtotime($start_date));
		$end_datetime = date('Y-m-d 23:59:59', strtotime($end_date));

		// Query untuk mendapatkan semua produk yang relevan
		$this->db->select('s.id as stock_id, s.stock as current_stock, p.id_products as product_id, pd.id as product_detail_id, p.title as product_name, pd.sku, b.brand');
		$this->db->from('stock s');
		$this->db->join('products p', 'p.id_products = s.id_product', 'left');
		$this->db->join('brands b', 'b.id_brands = p.brand_id', 'left');
		$this->db->join('product_details pd', 'pd.id = s.id_product_detail', 'left');

		// Tambahkan filter pencarian jika ada
		if (!empty($searchTerm)) {
			$this->db->group_start();
			$this->db->like('p.title', $searchTerm);
			$this->db->or_like('pd.sku', $searchTerm);
			$this->db->group_end();
		}

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

		// Daftar tabel pergerakan stok yang akan diakumulasikan
		$stock_tables = [
			'stock_movement',
			'stock_movement_keep',
			'stock_movement_reject',
			'stock_movement_sample'
		];

		foreach ($products as $product) {
			$opening_stock = 0;
			$incoming_stock = 0;
			$outgoing_stock = 0;

			foreach ($stock_tables as $table) {
				// Cari stok awal dari setiap tabel (pergerakan terakhir sebelum start_date)
				$this->db->select('total');
				$this->db->from($table);
				$this->db->where('stock_id', $product['stock_id']);
				$this->db->where('datetime <', $start_datetime);
				$this->db->order_by('datetime', 'DESC');
				$this->db->limit(1);
				$query = $this->db->get();

				$table_opening_stock = 0;
				if ($query->num_rows() > 0) {
					$table_opening_stock = $query->row()->total;
				} else {
					// Jika tidak ada riwayat, untuk tabel pertama (stock_movement) kita gunakan stok saat ini dan hitung mundur
					// Untuk tabel lainnya, kita asumsikan 0 karena tidak ada data historis
					if ($table === 'stock_movement') {
						$table_opening_stock = $product['current_stock'];

						// Hitung total pergerakan stok dalam periode dari semua tabel
						$total_in_all_tables = 0;
						$total_out_all_tables = 0;

						foreach ($stock_tables as $inner_table) {
							$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as total_in');
							$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as total_out');
							$this->db->from($inner_table);
							$this->db->where('stock_id', $product['stock_id']);
							$this->db->where('datetime >=', $start_datetime);
							$this->db->where('datetime <=', $end_datetime);
							$total_movement = $this->db->get()->row();

							$total_in_all_tables += ($total_movement->total_in ?? 0);
							$total_out_all_tables += ($total_movement->total_out ?? 0);
						}

						// Hitung mundur untuk mendapatkan stok awal
						$table_opening_stock = $table_opening_stock - $total_in_all_tables + $total_out_all_tables;
					}
				}

				$opening_stock += $table_opening_stock;

				// Hitung stok masuk dan keluar dalam periode untuk tabel ini
				$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as incoming_stock');
				$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as outgoing_stock');
				$this->db->from($table);
				$this->db->where('stock_id', $product['stock_id']);
				$this->db->where('datetime >=', $start_datetime);
				$this->db->where('datetime <=', $end_datetime);
				$stock_movement = $this->db->get()->row();

				$incoming_stock += ($stock_movement->incoming_stock ?? 0);
				$outgoing_stock += ($stock_movement->outgoing_stock ?? 0);
			}

			// Hitung stok akhir
			$closing_stock = $opening_stock + $incoming_stock - $outgoing_stock;

			// Tambahkan ke hasil jika ada pergerakan atau stok tidak nol
			if ($opening_stock > 0 || $incoming_stock > 0 || $outgoing_stock > 0 || $closing_stock > 0) {
				$result[] = [
					'product_id' => $product['product_id'],
					'product_detail_id' => $product['product_detail_id'],
					'product_name' => $product['product_name'],
					'sku' => $product['sku'] ?? '-',
					'brand' => $product['brand'] ?? '-',
					'opening_stock' => (int)$opening_stock,
					'incoming_stock' => (int)$incoming_stock,
					'outgoing_stock' => (int)$outgoing_stock,
					'closing_stock' => (int)$closing_stock
				];
			}
		}

		return $result;
	}

	public function get_product_stock_movement()
	{
		$product_detail_id = $this->input->get('product_detail_id', true);
		$date_filter = $this->input->get('date_filter', true) ?: 'last7days';
		$start_date = $this->input->get('start_date', true);
		$end_date = $this->input->get('end_date', true);

		if (!$product_detail_id) {
			$this->output
				->set_status_header(400)
				->set_content_type('application/json')
				->set_output(json_encode(['error' => 'Product detail ID diperlukan']));
			return;
		}

		// Set date range based on filter (exactly like in get_stock_data method)
		switch ($date_filter) {
			case 'today':
				$start_date = date('Y-m-d');
				$end_date = date('Y-m-d');
				break;
			case 'yesterday':
				$start_date = date('Y-m-d', strtotime('-1 day'));
				$end_date = date('Y-m-d', strtotime('-1 day'));
				break;
			case 'last7days':
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
			case 'last30days':
				$start_date = date('Y-m-d', strtotime('-29 days'));
				$end_date = date('Y-m-d');
				break;
			case 'thisMonth':
				$start_date = date('Y-m-01');
				$end_date = date('Y-m-t');
				break;
			case 'thisYear':
				$start_date = date('Y-01-01');
				$end_date = date('Y-12-31');
				break;
			case 'custom':
				if ($start_date && $end_date) {
					$start_date = date('Y-m-d', strtotime($start_date));
					$end_date = date('Y-m-d', strtotime($end_date));
				} else {
					// Default to last 7 days if custom dates are invalid
					$start_date = date('Y-m-d', strtotime('-6 days'));
					$end_date = date('Y-m-d');
				}
				break;
			default:
				$start_date = date('Y-m-d', strtotime('-6 days'));
				$end_date = date('Y-m-d');
				break;
		}

		// Validasi tanggal
		if (strtotime($start_date) > strtotime($end_date)) {
			$this->output
				->set_status_header(400)
				->set_content_type('application/json')
				->set_output(json_encode(['error' => 'Tanggal mulai tidak boleh melebihi tanggal akhir']));
			return;
		}

		try {
			$movement_detail = $this->get_stock_movement_detail($product_detail_id, $start_date, $end_date);

			$this->output
				->set_content_type('application/json')
				->set_output(json_encode([
					'start_date' => $start_date,
					'end_date' => $end_date,
					'product_info' => $movement_detail['product_info'],
					'movements' => $movement_detail['movements']
				]));
		} catch (Exception $e) {
			$this->output
				->set_status_header(500)
				->set_content_type('application/json')
				->set_output(json_encode(['error' => 'Terjadi kesalahan dalam memproses data: ' . $e->getMessage()]));
		}
	}

	public function get_stock_movement_detail($product_detail_id, $start_date, $end_date)
	{
		// Konversi format tanggal untuk query
		$start_datetime = date('Y-m-d 00:00:00', strtotime($start_date));
		$end_datetime = date('Y-m-d 23:59:59', strtotime($end_date));

		// Dapatkan stock_id berdasarkan product_detail_id
		$this->db->select('id as stock_id');
		$this->db->from('stock');
		$this->db->where('id_product_detail', $product_detail_id);
		$stock_query = $this->db->get();

		if ($stock_query->num_rows() == 0) {
			return [
				'product_info' => null,
				'movements' => [],
				'summary' => [
					'opening_stock' => 0,
					'incoming_stock' => 0,
					'outgoing_stock' => 0,
					'closing_stock' => 0
				]
			];
		}

		$stock_id = $stock_query->row()->stock_id;

		// Dapatkan info produk
		$this->db->select('p.id_products as product_id, p.title as product_name, pd.sku, b.brand');
		$this->db->from('product_details pd');
		$this->db->join('products p', 'p.id_products = pd.product_id', 'left');
		$this->db->join('brands b', 'b.id_brands = p.brand_id', 'left');
		$this->db->where('pd.id', $product_detail_id);
		$product_info = $this->db->get()->row_array();

		// Daftar tabel pergerakan stok
		$stock_tables = [
			'stock_movement' => 'Total',
			'stock_movement_keep' => 'Keep',
			'stock_movement_reject' => 'Reject',
			'stock_movement_sample' => 'Sample'
		];

		$movements = [];
		$total_opening_stock = 0;
		$total_incoming_stock = 0;
		$total_outgoing_stock = 0;

		// Untuk setiap jenis stok, hitung stok awal
		foreach ($stock_tables as $table => $stock_type) {
			// Cari stok awal (pergerakan terakhir sebelum start_date)
			$this->db->select('total');
			$this->db->from($table);
			$this->db->where('stock_id', $stock_id);
			$this->db->where('datetime <', $start_datetime);
			$this->db->order_by('datetime', 'asc');
			$this->db->limit(1);
			$query = $this->db->get();

			$opening_stock = 0;
			if ($query->num_rows() > 0) {
				$opening_stock = $query->row()->total;
			} else {
				// Jika tidak ada riwayat, kita tidak bisa menghitung stok awal dengan pasti
				if ($table === 'stock_movement') {
					// Ambil stok saat ini
					$this->db->select('stock');
					$this->db->from('stock');
					$this->db->where('id', $stock_id);
					$current_stock = $this->db->get()->row()->stock ?? 0;

					// Hitung total pergerakan stok dalam periode dari semua tabel
					$total_in_all_tables = 0;
					$total_out_all_tables = 0;

					foreach ($stock_tables as $inner_table => $inner_type) {
						$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as total_in');
						$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as total_out');
						$this->db->from($inner_table);
						$this->db->where('stock_id', $stock_id);
						$this->db->where('datetime >=', $start_datetime);
						$this->db->where('datetime <=', $end_datetime);
						$total_movement = $this->db->get()->row();

						$total_in_all_tables += ($total_movement->total_in ?? 0);
						$total_out_all_tables += ($total_movement->total_out ?? 0);
					}

					// Hitung mundur untuk mendapatkan stok awal
					$opening_stock = $current_stock - $total_in_all_tables + $total_out_all_tables;
				}
			}

			$total_opening_stock += $opening_stock;

			// Hitung stok masuk dan keluar dalam periode
			$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as incoming');
			$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as outgoing');
			$this->db->from($table);
			$this->db->where('stock_id', $stock_id);
			$this->db->where('datetime >=', $start_datetime);
			$this->db->where('datetime <=', $end_datetime);
			$stock_movement = $this->db->get()->row();

			$incoming = $stock_movement->incoming ?? 0;
			$outgoing = $stock_movement->outgoing ?? 0;

			$total_incoming_stock += $incoming;
			$total_outgoing_stock += $outgoing;

			// Tambahkan summary per jenis stok ke informasi produk
			$product_info[$stock_type . '_summary'] = [
				'opening_stock' => (int)$opening_stock,
				'incoming_stock' => (int)$incoming,
				'outgoing_stock' => (int)$outgoing,
				'closing_stock' => (int)($opening_stock + $incoming - $outgoing)
			];

			$running_balance = $opening_stock;

			// Dapatkan semua pergerakan stok dari tabel ini
			$this->db->select("'{$stock_type}' as stock_type, datetime, type, stock_change, total, remark");
			$this->db->from($table);
			$this->db->where('stock_id', $stock_id);
			$this->db->where('datetime >=', $start_datetime);
			$this->db->where('datetime <=', $end_datetime);
			$this->db->order_by('datetime', 'ASC');

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

			foreach ($table_movements as &$movement) {
				// Add beginning balance before this movement
				$movement['beginning_balance'] = $running_balance;

				// Update running balance
				if ($movement['type'] == '+') {
					$running_balance += $movement['stock_change'];
				} else {
					$running_balance -= $movement['stock_change'];
				}

				// Also store ending balance after this movement
				$movement['ending_balance'] = $running_balance;
			}
			// harus nambahin reference_id, dan reference type

			$movements = array_merge($movements, $table_movements);
		}

		// Hitung stok akhir total
		$total_closing_stock = $total_opening_stock + $total_incoming_stock - $total_outgoing_stock;

		// Urutkan semua pergerakan berdasarkan tanggal
		usort($movements, function ($a, $b) {
			return strtotime($b['datetime']) - strtotime($a['datetime']);
		});

		return [
			'product_info' => $product_info,
			'movements' => $movements,
			'summary' => [
				'opening_stock' => (int)$total_opening_stock,
				'incoming_stock' => (int)$total_incoming_stock,
				'outgoing_stock' => (int)$total_outgoing_stock,
				'closing_stock' => (int)$total_closing_stock
			]
		];
	}

	public function banner_statistics()
	{
		$data['userdata'] = $this->session->userdata();
		$data['title'] = 'Statistik Banner | Laciasmara';

		$this->load->view('admin_new/layouts/header', $data);
		$this->load->view('admin_new/statistics/banner_statistics');
		$this->load->view('admin_new/layouts/footer', $data);
	}
	public function get_banners()
	{
		$dateFilter = $this->input->get('date_filter', true);
		$startDate = $this->input->get('start_date', true);
		$endDate = $this->input->get('end_date', true);

		// Build main query
		$this->db->select('
        cb.carousel_banner_id,
			cb.cta_text,
			cb.cta_button_link,
			cb.is_active,
			cb.created_at,
			COUNT(DISTINCT cbi.id) AS jumlah_tayang
		');
		$this->db->from('carousel_banners cb');
		$this->db->join('carousel_banner_impressions cbi', 'cb.carousel_banner_id = cbi.banner_id', 'left');

		// Filter berdasarkan tanggal
		if ($dateFilter) {
			switch ($dateFilter) {
				case 'today':
					$this->db->where('DATE(cbi.timestamp) = CURDATE()');
					break;
				case 'yesterday':
					$this->db->where('DATE(cbi.timestamp) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
					break;
				case 'last7days':
					$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
					break;
				case 'last30days':
					$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
					break;
				case 'thisMonth':
					$this->db->where('MONTH(cbi.timestamp) = MONTH(CURDATE()) AND YEAR(cbi.timestamp) = YEAR(CURDATE())');
					break;
				case 'thisYear':
					$this->db->where('YEAR(cbi.timestamp) = YEAR(CURDATE())');
					break;
				case 'custom':
					if ($startDate && $endDate) {
						$startDate = date('Y-m-d', strtotime($startDate));
						$endDate = date('Y-m-d', strtotime($endDate));
						if ($startDate && $endDate) {
							$this->db->where('DATE(cbi.timestamp) >=', $startDate);
							$this->db->where('DATE(cbi.timestamp) <=', $endDate);
						}
					}
					break;
			}
		} else {
			// Default: data 7 hari terakhir jika tidak ada filter
			$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
		}



		// Group by banner
		$this->db->group_by('cb.carousel_banner_id');
		// Execute query
		$query = $this->db->get();
		$all_banners = $query->result();

		// Post-processing data
		foreach ($all_banners as $banner) {
			// Format status
			if ($banner->is_active == 1) {
				$banner->status_text = 'Active';
				$banner->status_class = 'bg-green-100 text-green-700 px-2 py-1 rounded';
			} else {
				$banner->status_text = 'Inactive';
				$banner->status_class = 'bg-red-100 text-red-700 px-2 py-1 rounded';
			}

			// Format created_at
			$banner->created_at_formatted = date('d M Y H:i', strtotime($banner->created_at));
			$banner->jumlah_klik = $this->count_banner_clicks($banner->cta_button_link, $dateFilter, $startDate, $endDate);
			$banner->ctr = ($banner->jumlah_tayang > 0) ?
				round(($banner->jumlah_klik / $banner->jumlah_tayang) * 100, 2) : 0;

			$banner->ctr_formatted = $banner->ctr . '%';
		}

		// Return JSON response
		echo json_encode($all_banners);
	}

	/**
	 * Hitung jumlah klik untuk banner berdasarkan parameter UTM dalam link
	 * 
	 * @param string $cta_button_link Link dari banner dengan parameter UTM
	 * @param string $dateFilter Filter tanggal
	 * @param string $startDate Tanggal mulai untuk filter custom
	 * @param string $endDate Tanggal akhir untuk filter custom
	 * @return int Jumlah klik
	 */
	private function count_banner_clicks($cta_button_link, $dateFilter, $startDate = null, $endDate = null)
	{
		if (empty($cta_button_link)) {
			return 0;
		}

		// Parse URL untuk mendapatkan base URL dan parameter UTM
		$parsed_url = parse_url($cta_button_link);
		$base_url = isset($parsed_url['scheme']) ? $parsed_url['scheme'] . '://' . $parsed_url['host'] . $parsed_url['path'] : '';

		// Parse query parameters untuk mendapatkan UTM values
		$utm_source = null;
		$utm_medium = null;
		$utm_campaign = null;

		if (isset($parsed_url['query'])) {
			parse_str($parsed_url['query'], $query_params);

			// Ambil parameter UTM yang diperlukan
			if (isset($query_params['utm_source'])) {
				$utm_source = $query_params['utm_source'];
			}
			if (isset($query_params['utm_medium'])) {
				$utm_medium = $query_params['utm_medium'];
			}
			if (isset($query_params['utm_campaign'])) {
				$utm_campaign = $query_params['utm_campaign'];
			}
		}

		// Buat query untuk menghitung klik
		$this->db->select('COUNT(id) as total_clicks');
		$this->db->from('link_tracks');
		$this->db->where('link_url', $base_url);

		// Tambahkan kondisi untuk parameter UTM jika ada
		if ($utm_source) {
			$this->db->where('utm_source', $utm_source);
		}
		if ($utm_medium) {
			$this->db->where('utm_medium', $utm_medium);
		}
		if ($utm_campaign) {
			$this->db->where('utm_campaign', $utm_campaign);
		}

		// Tambahkan filter tanggal
		if ($dateFilter) {
			switch ($dateFilter) {
				case 'today':
					$this->db->where('DATE(click_date) = CURDATE()');
					break;
				case 'yesterday':
					$this->db->where('DATE(click_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
					break;
				case 'last7days':
					$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
					break;
				case 'last30days':
					$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
					break;
				case 'thisMonth':
					$this->db->where('MONTH(click_date) = MONTH(CURDATE()) AND YEAR(click_date) = YEAR(CURDATE())');
					break;
				case 'thisYear':
					$this->db->where('YEAR(click_date) = YEAR(CURDATE())');
					break;
				case 'custom':
					if ($startDate && $endDate) {
						$startDate = date('Y-m-d', strtotime($startDate));
						$endDate = date('Y-m-d', strtotime($endDate));
						if ($startDate && $endDate) {
							$this->db->where('DATE(click_date) >=', $startDate);
							$this->db->where('DATE(click_date) <=', $endDate);
						}
					}
					break;
			}
		} else {
			// Default: data 7 hari terakhir jika tidak ada filter
			$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
		}

		$result = $this->db->get()->row();
		return $result ? (int)$result->total_clicks : 0;
	}
}