<?php

class Dashboard_model extends CI_Model
{

	//Datatable start
		var $table = 'db_items a';
		var $column_order = array(
									'a.item_code',
									'a.item_name',
									'b.category_name',
									'c.brand_name',
									'a.stock'
									); //set column field database for datatable orderable
		var $column_search = array( 
									'a.item_code',
									'a.item_name',
									'b.category_name',
									'c.brand_name',
									'a.stock'
									); //set column field database for datatable searchable 
		var $order = array('a.id' => 'desc'); // default order 

	
		
		private function _get_datatables_query()
		{	
			$this->db->select($this->column_order);
			$this->db->from($this->table);
			$this->db->where('a.store_id',get_current_store_id());
	        $this->db->where('(a.stock<=a.alert_qty or a.stock is null)');
	        $this->db->where('a.service_bit',0);
	        
	        $this->db->where('a.status=1');
	        $this->db->join('db_category b','b.id=a.category_id','left');
	        $this->db->join('db_brands c','c.id=a.brand_id','left');

	     

			//	echo $this->db->get_compiled_select();exit();
			$i = 0;
		
			foreach ($this->column_search as $item) // loop column 
			{
				if($_POST['search']['value']) // if datatable send POST for search
				{
					
					if($i===0) // first loop
					{
						$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
						$this->db->like($item, $_POST['search']['value']);
					}
					else
					{
						$this->db->or_like($item, $_POST['search']['value']);
					}

					if(count($this->column_search) - 1 == $i) //last loop
						$this->db->group_end(); //close bracket
				}
				$i++;
			}
			
			if(isset($_POST['order'])) // here order processing
			{
				$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
			} 
			else if(isset($this->order))
			{
				$order = $this->order;
				$this->db->order_by(key($order), $order[key($order)]);
			}
		}

		function get_datatables()
		{
			$this->_get_datatables_query();
			if($_POST['length'] != -1)
			$this->db->limit($_POST['length'], $_POST['start']);
			$query = $this->db->get();
			return $query->result();
		}

		function count_filtered()
		{
			$this->_get_datatables_query();
			$query = $this->db->get();
			return $query->num_rows();
		}

		public function count_all()
		{
			$this->db->where("store_id",get_current_store_id());
			$this->db->from($this->table);
			return $this->db->count_all_results();
		}
		//Datatable end

	public function get_subscription_chart()
	{
		$sub_chart = array();
		for ($i=6; $i >= 0; $i--) {
			//Date
            $sub_chart['date'][$i] = date("Y-m-d",strtotime("-".$i." months"));
            $sub_chart['sub_year'][$i] = date("Y",strtotime($sub_chart['date'][$i]));
            $sub_chart['sub_month'][$i] = date("M",strtotime($sub_chart['date'][$i]));

            $this->db->select("count(*) as tot_subscribes");
            $this->db->from("db_subscription");
            $this->db->where("month(subscription_date)",date("m",strtotime($sub_chart['date'][$i])));
            $q3=$this->db->get();
            $sub_chart['tot_subscribes'][$i] = $q3->row()->tot_subscribes;
        }
        return $sub_chart;
	}

	public function get_pie_chart($value='')
	{
		// Optimized query using proper JOIN syntax
		$this->db->select("COALESCE(SUM(b.sales_qty),0) AS sales_qty, a.item_name");
		$this->db->from("db_items AS a");
		$this->db->join("db_salesitems AS b", "a.id = b.item_id", "inner");
		$this->db->join("db_sales AS c", "b.sales_id = c.id", "inner");
		$this->db->where("c.store_id", get_current_store_id());
		$this->db->where("c.sales_status", "Final");
		/*if(!is_admin() && !is_store_admin()){
			$this->db->where("c.created_by", $this->session->userdata('inv_username'));  
		}*/
		$this->db->group_by("a.id", "a.item_name");
		$this->db->having("sales_qty >", 0);
		$this->db->order_by("sales_qty", "ASC");
		$this->db->limit(10);

		$q3 = $this->db->get();
		$pie_chart = array();
		$i = 0;
		if($q3->num_rows() > 0){
			foreach($q3->result() as $res3){
				++$i;
				$pie_chart['tranding_item'][$i]['name'] = $res3->item_name;
				$pie_chart['tranding_item'][$i]['sales_qty'] = $res3->sales_qty;
			}
		}
		$pie_chart['tranding_item']['tot_rec'] = $i;
		return $pie_chart;
	}
	public function get_bar_chart(){
		$bar_chart = array();
		$store_id = get_current_store_id();
		$user_filter = (!is_admin() && !is_store_admin()) ? $this->session->userdata('inv_username') : null;
		
		// Build date array
		for ($i=6; $i >= 0; $i--) { 
			$bar_chart['date'][$i] = date("Y-m-d", strtotime("-".$i." months"));
			$bar_chart['month'][$i] = date("M", strtotime($bar_chart['date'][$i])).",".date("Y", strtotime($bar_chart['date'][$i]));
			$bar_chart['purchase'][$i] = 0;
			$bar_chart['sales'][$i] = 0;
			$bar_chart['expense'][$i] = 0;
		}
		
		// Optimized: Single query for all purchases (7 months in one query)
		$start_date = date("Y-m-01", strtotime("-6 months"));
		$end_date = date("Y-m-t");
		$this->db->select("DATE_FORMAT(purchase_date, '%Y-%m') AS month_key, COALESCE(SUM(grand_total),0) AS pur_total");
		$this->db->from("db_purchase");
		$this->db->where("store_id", $store_id);
		$this->db->where("purchase_status", "Received");
		$this->db->where("purchase_date >=", $start_date);
		$this->db->where("purchase_date <=", $end_date);
		if($user_filter){
			$this->db->where("created_by", $user_filter);
		}
		$this->db->group_by("month_key");
		$purchase_results = $this->db->get()->result();
		$purchase_data = array();
		foreach($purchase_results as $row){
			$purchase_data[$row->month_key] = $row->pur_total;
		}
		
		// Optimized: Single query for all sales (7 months in one query)
		$this->db->select("DATE_FORMAT(sales_date, '%Y-%m') AS month_key, COALESCE(SUM(grand_total),0) AS sal_total");
		$this->db->from("db_sales");
		$this->db->where("store_id", $store_id);
		$this->db->where("sales_status", "Final");
		$this->db->where("sales_date >=", $start_date);
		$this->db->where("sales_date <=", $end_date);
		if($user_filter){
			$this->db->where("created_by", $user_filter);
		}
		$this->db->group_by("month_key");
		$sales_results = $this->db->get()->result();
		$sales_data = array();
		foreach($sales_results as $row){
			$sales_data[$row->month_key] = $row->sal_total;
		}
		
		// Optimized: Single query for all expenses (7 months in one query)
		$this->db->select("DATE_FORMAT(expense_date, '%Y-%m') AS month_key, COALESCE(SUM(CASE WHEN base_currency_amount IS NOT NULL AND base_currency_amount > 0 THEN base_currency_amount ELSE expense_amt END),0) AS expense_amt");
		$this->db->from("db_expense");
		$this->db->where("store_id", $store_id);
		$this->db->where("expense_date >=", $start_date);
		$this->db->where("expense_date <=", $end_date);
		if($user_filter){
			$this->db->where("created_by", $user_filter);
		}
		$this->db->group_by("month_key");
		$expense_results = $this->db->get()->result();
		$expense_data = array();
		foreach($expense_results as $row){
			$expense_data[$row->month_key] = $row->expense_amt;
		}
		
		// Map results back to array indices
		for ($i=6; $i >= 0; $i--) {
			$month_key = date("Y-m", strtotime($bar_chart['date'][$i]));
			$bar_chart['purchase'][$i] = isset($purchase_data[$month_key]) ? $purchase_data[$month_key] : 0;
			$bar_chart['sales'][$i] = isset($sales_data[$month_key]) ? $sales_data[$month_key] : 0;
			$bar_chart['expense'][$i] = isset($expense_data[$month_key]) ? $expense_data[$month_key] : 0;
		}
		
		return $bar_chart;
	}
	public function get_by_date($table_date)
	{
		$dates = $this->input->post('dates');
		
		if($dates=='Today'){
      		$this->db->where("$table_date",date("Y-m-d"));
      	}
      	elseif($dates=='Yesterday'){
      		$this->db->where("$table_date",date("Y-m-d", strtotime("-1 day")));
      	}
      	elseif($dates=='This Week'){
      		$this->db->where("$table_date >= DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY)");
      		$this->db->where("$table_date < DATE_ADD(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY)");
      	}
      	elseif($dates=='Last Week'){
      		$this->db->where("$table_date >= DATE_SUB(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY)");
      		$this->db->where("$table_date < DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY)");
      	}
      	elseif($dates=='This Month'){
      		$this->db->where("$table_date >= DATE_FORMAT(NOW(), '%Y-%m-01')");
      		$this->db->where("$table_date < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)");
      	}
      	elseif($dates=='Last Month'){
      		$this->db->where("$table_date >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)");
      		$this->db->where("$table_date < DATE_FORMAT(NOW(), '%Y-%m-01')");
      	}
      	elseif($dates=='This Year'){
      		$this->db->where("$table_date >= DATE_FORMAT(NOW(), '%Y-01-01')");
      		$this->db->where("$table_date < DATE_ADD(DATE_FORMAT(NOW(), '%Y-01-01'), INTERVAL 1 YEAR)");
      	}
      	elseif($dates=='Last Year'){
      		$this->db->where("$table_date >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-01-01'), INTERVAL 1 YEAR)");
      		$this->db->where("$table_date < DATE_FORMAT(NOW(), '%Y-01-01')");
      	}
      	elseif($dates=='Weekly'){
      		$this->db->where("$table_date > DATE_SUB(NOW(), INTERVAL 1 WEEK)");
      	}
      	elseif($dates=='Monthly'){
      		$this->db->where("$table_date > DATE_SUB(NOW(), INTERVAL 1 MONTH)");
      	}
      	elseif($dates=='Yearly'){
      		$this->db->where("$table_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)");
      	}
      	elseif($dates=='Custom'){
      		$from_date = $this->input->post('from_date');
      		$to_date = $this->input->post('to_date');
      		if(!empty($from_date) && !empty($to_date)){
      			$this->db->where("$table_date >=", $from_date);
      			$this->db->where("$table_date <=", $to_date);
      		}
      	}
      	// For 'All' or any other value, no date filter is applied
	}
	public function breadboard_values()
	{	
		$dates = $this->input->post('dates');
		//$store_id=$this->input->post('store_id');
		$CI =& get_instance();
		$info=array();
		
		// Initialize base currency at the beginning
		$base_currency = get_base_currency();
		if (!$base_currency) {
			$base_currency = (object)['id' => 1, 'currency_code' => 'USD', 'currency_name' => 'US Dollar', 'symbol' => '$'];
		}

		///Find total suppliers
		/*if(store_module() && is_admin()){if(!empty($store_id)){ 
					$this->db->where("store_id",$store_id);}
				}else{ */
					$this->db->where("store_id",get_current_store_id());	
			/*}*/
		$this->db->select("coalesce(count(*),0) as tot_sup");
		$this->db->from("db_suppliers");
		$this->db->where("status=1");
		
		$tot_sup=$this->db->get()->row()->tot_sup;	
		$info['tot_sup']=$tot_sup;

		///Find total Products
		/*if(store_module() && is_admin()){if(!empty($store_id)){ 
					$this->db->where("store_id",$store_id);}
				}else{ */
					$this->db->where("store_id",get_current_store_id());	
			/*}*/
		$this->db->select("coalesce(count(*),0) as tot_pro");
		$this->db->from("db_items");
		$this->db->where("status=1");
		$tot_pro=$this->db->get()->row()->tot_pro;	
		$info['tot_pro']=$tot_pro;

		//Total Customers
		/*if(store_module() && is_admin()){if(!empty($store_id)){ 
					$this->db->where("store_id",$store_id);}
				}else{ */
					$this->db->where("store_id",get_current_store_id());	
			/*}*/
		$this->db->select("coalesce(count(*),0) as tot_cust");
		$this->db->from("db_customers");
		$this->db->where("status=1");
		$tot_cust=$this->db->get()->row()->tot_cust;	
		$info['tot_cust']=$tot_cust;

		//Total Purchases Active
		$this->db->reset_query(); // Reset query builder
		$this->db->where("store_id",get_current_store_id());	
		// Removed user filter - all authorized users can see all store purchases
     	$this->get_by_date('purchase_date');//DATES FUNCTION
		$this->db->select("coalesce(count(*),0) as tot_pur");
		$this->db->from("db_purchase");
		$this->db->where("purchase_status='Received'");
		
		$tot_pur=$this->db->get()->row()->tot_pur;	
		$info['tot_pur']=$tot_pur;

  		//Total SAles Active
		$this->db->reset_query(); // Reset query builder
		$this->db->where("store_id",get_current_store_id());	
		// Removed user filter - all authorized users can see all store sales
		$this->get_by_date('sales_date');//DATES FUNCTION
		$this->db->select("coalesce(count(*),0) as tot_sal");
		$this->db->from("db_sales");
		$this->db->where("`sales_status`= 'Final'");
		$tot_sal=$this->db->get()->row()->tot_sal;
		$info['tot_sal']=$tot_sal;


		//Total Sales return amount - Convert to base currency
		$this->db->reset_query();
					$this->db->where("store_id",get_current_store_id());	
		// Removed user filter - all authorized users can see all store sales
		$this->get_by_date('return_date');//DATES FUNCTION
		
		// Get all sales returns with their currency information for conversion
		$this->db->select("grand_total, currency_id, exchange_rate, base_currency_total, customer_id");
		$this->db->from("db_salesreturn");
		$all_sales_returns = $this->db->get()->result();
		
		$tot_sal_ret_grand_total_base = 0;
		foreach ($all_sales_returns as $sales_return) {
			if (!empty($sales_return->currency_id) && $sales_return->currency_id != $base_currency->id) {
				// Convert to base currency using stored exchange rate
				// Use the convert_currency helper function which handles the conversion correctly
				$sales_return_total_base = convert_currency($sales_return->grand_total, $sales_return->currency_id, $base_currency->id);
			} elseif (empty($sales_return->currency_id) && !empty($sales_return->customer_id)) {
				// For old records, try to get customer's trading currency
				$customer_currency = get_customer_trading_currency($sales_return->customer_id);
				if ($customer_currency && $customer_currency->id != $base_currency->id) {
					$sales_return_total_base = convert_currency($sales_return->grand_total, $customer_currency->id, $base_currency->id);
				} else {
					$sales_return_total_base = $sales_return->grand_total;
				}
			} else {
				// Already in base currency
				$sales_return_total_base = $sales_return->grand_total;
			}
			
			$tot_sal_ret_grand_total_base += $sales_return_total_base;
		}
		
		$info['tot_sal_ret_grand_total']=$CI->currency(kmb($tot_sal_ret_grand_total_base));

		//Total SAles amount - Convert to base currency
		$this->db->reset_query();
					$this->db->where("store_id",get_current_store_id());	
		// Removed user filter - all authorized users can see all store sales
		$this->get_by_date('sales_date');//DATES FUNCTION
		
		// Get all sales with their currency information for conversion
		$this->db->select("grand_total, transaction_currency_id, exchange_rate, base_currency_total, customer_id");
		$this->db->from("db_sales");
		$this->db->where("`sales_status`= 'Final'");
		$all_sales = $this->db->get()->result();
		
		// Initialize base currency if not already set
		if (!isset($base_currency) || !$base_currency) {
			$base_currency = get_base_currency();
			if (!$base_currency) {
				$base_currency = (object)['id' => 1, 'currency_code' => 'USD', 'currency_name' => 'US Dollar', 'symbol' => '$'];
			}
		}
		
		$tot_sal_grand_total_base = 0;
		foreach ($all_sales as $sale) {
			if (!empty($sale->transaction_currency_id) && $sale->transaction_currency_id != $base_currency->id) {
				// Convert to base currency using stored exchange rate
				// Use the convert_currency helper function which handles the conversion correctly
				$sale_total_base = convert_currency($sale->grand_total, $sale->transaction_currency_id, $base_currency->id);
			} elseif (empty($sale->transaction_currency_id) && !empty($sale->customer_id)) {
				// For old records, try to get customer's trading currency
				$customer_currency = get_customer_trading_currency($sale->customer_id);
				if ($customer_currency && $customer_currency->id != $base_currency->id) {
					$sale_total_base = convert_currency($sale->grand_total, $customer_currency->id, $base_currency->id);
				} else {
					$sale_total_base = $sale->grand_total;
				}
			} else {
				// Already in base currency
				$sale_total_base = $sale->grand_total;
			}
			
			$tot_sal_grand_total_base += $sale_total_base;
		}
		
		$info['tot_sal_grand_total']=$CI->currency(kmb($tot_sal_grand_total_base-$tot_sal_ret_grand_total_base));

		


		//Total expense amount - use base currency amount for proper conversion
		/*if(store_module() && is_admin()){if(!empty($store_id)){ 
					$this->db->where("store_id",$store_id);}
				}else{ */
					$this->db->where("store_id",get_current_store_id());	
			/*}*/
		if(!is_admin() && !is_store_admin()){
			$this->db->where("created_by",$this->session->userdata('inv_username'));	
		}
		$this->get_by_date('expense_date');//DATES FUNCTION
		
		// Use base_currency_amount if available, otherwise use expense_amt (for backward compatibility)
		$this->db->select("COALESCE(sum(CASE WHEN base_currency_amount IS NOT NULL AND base_currency_amount > 0 THEN base_currency_amount ELSE expense_amt END),0) AS tot_exp");
		$this->db->from("db_expense");
		$tot_exp=$this->db->get()->row()->tot_exp;
		$info['tot_exp']=$CI->currency(kmb($tot_exp,2));

		//Total SAles Due
		$this->db->reset_query();
					$this->db->where("store_id",get_current_store_id());	
		if(!is_admin() && !is_store_admin()){
			$this->db->where("created_by",$this->session->userdata('inv_username'));	
		}
		$this->get_by_date('sales_date');//DATES FUNCTION
		
		// Get all sales with their currency information (date filter already applied)
		$this->db->select("grand_total, paid_amount, transaction_currency_id, exchange_rate, base_currency_total, customer_id");
		$this->db->from("db_sales");
		$this->db->where("`sales_status`= 'Final'");
		$all_sales = $this->db->get()->result();
		
		// Initialize base currency if not already set
		if (!isset($base_currency) || !$base_currency) {
			$base_currency = get_base_currency();
			if (!$base_currency) {
				$base_currency = (object)['id' => 1, 'currency_code' => 'USD', 'currency_name' => 'US Dollar', 'symbol' => '$'];
			}
		}
		
		$sales_due_base = 0;
		
		// Convert each sale to base currency
		foreach ($all_sales as $sale) {
			$sale_due = $sale->grand_total - $sale->paid_amount;
			
			if (!empty($sale->transaction_currency_id) && $sale->transaction_currency_id != $base_currency->id) {
				// Convert to base currency using exchange rate
				// Use the convert_currency helper function which handles the conversion correctly
				$sale_due_base_amount = convert_currency($sale_due, $sale->transaction_currency_id, $base_currency->id);
			} elseif (empty($sale->transaction_currency_id) && !empty($sale->customer_id)) {
				// For old records, try to get customer's trading currency
				$customer_currency = get_customer_trading_currency($sale->customer_id);
				if ($customer_currency && $customer_currency->id != $base_currency->id) {
					$sale_due_base_amount = convert_currency($sale_due, $customer_currency->id, $base_currency->id);
					} else {
					$sale_due_base_amount = $sale_due;
				}
			} else {
				// Already in base currency
				$sale_due_base_amount = $sale_due;
			}
			
			$sales_due_base += $sale_due_base_amount;
		}
		
		$info['sales_due']=$CI->currency(kmb($sales_due_base));

		//Total Purchase  Due
		$this->db->reset_query();
					$this->db->where("store_id",get_current_store_id());	
		if(!is_admin() && !is_store_admin()){
			$this->db->where("created_by",$this->session->userdata('inv_username'));	
		}
		$this->get_by_date('purchase_date');//DATES FUNCTION
		
		// Get all purchases with their currency information (date filter already applied)
		$this->db->select("grand_total, paid_amount, transaction_currency_id, exchange_rate, base_currency_total, supplier_id");
		$this->db->from("db_purchase");
		$this->db->where("`purchase_status`= 'Received'");
		$all_purchases = $this->db->get()->result();
		
		// Initialize base currency if not already set
		if (!isset($base_currency) || !$base_currency) {
			$base_currency = get_base_currency();
			if (!$base_currency) {
				$base_currency = (object)['id' => 1, 'currency_code' => 'USD', 'currency_name' => 'US Dollar', 'symbol' => '$'];
			}
		}
		$purchase_due_base = 0;
		
		// Convert each purchase to base currency
		foreach ($all_purchases as $purchase) {
			$purchase_due = $purchase->grand_total - $purchase->paid_amount;
			
			if (!empty($purchase->transaction_currency_id) && $purchase->transaction_currency_id != $base_currency->id) {
				// Convert to base currency using exchange rate
				// Use the convert_currency helper function which handles the conversion correctly
				$purchase_due_base_amount = convert_currency($purchase_due, $purchase->transaction_currency_id, $base_currency->id);
			} elseif (empty($purchase->transaction_currency_id) && !empty($purchase->supplier_id)) {
				// For old records, try to get supplier's trading currency
				$supplier_currency = get_supplier_trading_currency($purchase->supplier_id);
				if ($supplier_currency && $supplier_currency->id != $base_currency->id) {
					$purchase_due_base_amount = convert_currency($purchase_due, $supplier_currency->id, $base_currency->id);
				} else {
					$purchase_due_base_amount = $purchase_due;
				}
			} else {
				// Already in base currency
				$purchase_due_base_amount = $purchase_due;
			}
			
			$purchase_due_base += $purchase_due_base_amount;
		}
		
		$info['purchase_due']=$CI->currency(kmb($purchase_due_base));

		//Total Purchase Return amount - Convert to base currency
		$this->db->reset_query();
		$this->db->where("store_id",get_current_store_id());	
		if(!is_admin() && !is_store_admin()){
			$this->db->where("created_by",$this->session->userdata('inv_username'));	
		}
		$this->get_by_date('return_date');//DATES FUNCTION
		
		// Get all purchase returns with their currency information for conversion
		$this->db->select("grand_total, currency_id, exchange_rate, base_currency_total, supplier_id");
		$this->db->from("db_purchasereturn");
		$all_purchase_returns = $this->db->get()->result();
		
		$tot_pur_ret_grand_total_base = 0;
		foreach ($all_purchase_returns as $purchase_return) {
			if (!empty($purchase_return->currency_id) && $purchase_return->currency_id != $base_currency->id) {
				// Convert to base currency using stored exchange rate
				// Use the convert_currency helper function which handles the conversion correctly
				$purchase_return_total_base = convert_currency($purchase_return->grand_total, $purchase_return->currency_id, $base_currency->id);
			} elseif (empty($purchase_return->currency_id) && !empty($purchase_return->supplier_id)) {
				// For old records, try to get supplier's trading currency
				$supplier_currency = get_supplier_trading_currency($purchase_return->supplier_id);
				if ($supplier_currency && $supplier_currency->id != $base_currency->id) {
					$purchase_return_total_base = convert_currency($purchase_return->grand_total, $supplier_currency->id, $base_currency->id);
				} else {
					$purchase_return_total_base = $purchase_return->grand_total;
				}
			} else {
				// Already in base currency
				$purchase_return_total_base = $purchase_return->grand_total;
			}
			
			$tot_pur_ret_grand_total_base += $purchase_return_total_base;
		}
		
		$info['tot_pur_ret_grand_total']=$CI->currency(kmb($tot_pur_ret_grand_total_base));

		return $info;
	}
	
	// Enhanced stock alert data for dashboard
	public function get_enhanced_stock_alert_data() {
		$this->load->helper('multi_currency_helper');
		
		// Get base currency info
		$store_id = get_current_store_id();
		$base_currency = get_base_currency($store_id);
		$base_currency_symbol = $base_currency ? $base_currency->symbol : '$';
		$base_currency_position = $base_currency ? $base_currency->currency_position : 'before';
		
		// Use the exact same query as the reports
		$this->db->select("a.id as item_id, a.item_code, a.item_name, a.sales_price, a.purchase_price, COALESCE(a.alert_qty, 0) as minimum_stock_level, a.store_id, a.stock as current_stock");
		$this->db->select("d.category_name, c.brand_name");
		$this->db->from("db_items as a");
		$this->db->join("db_brands as c", "c.id = a.brand_id", "left");
		$this->db->join("db_category as d", "d.id = a.category_id", "left");
		$this->db->where("a.service_bit", 0);
		$this->db->where("a.status", 1);
		$this->db->where("a.item_group !=", "Variants");
		$this->db->where("a.store_id", $store_id);
		$this->db->where("COALESCE(a.alert_qty, 0) >", 0);
		$this->db->where("(a.stock <= a.alert_qty OR a.stock IS NULL)");
		
		$query = $this->db->get();
		
		// Debug logging
		log_message('debug', 'Dashboard Stock Alert Query: ' . $this->db->last_query());
		log_message('debug', 'Dashboard Stock Alert - Store ID: ' . $store_id);
		log_message('debug', 'Dashboard Stock Alert - Query returned ' . $query->num_rows() . ' rows');
		
		$out_of_stock_count = 0;
		$critical_stock_count = 0;
		$low_stock_count = 0;
		$total_risk_value = 0;
		$stock_alert_items = array();
		
		if($query->num_rows() > 0) {
			foreach($query->result() as $row) {
				$current_stock = $row->current_stock ?: 0;
				$minimum_stock_level = $row->minimum_stock_level ?: 0;
				
				// Calculate stock value
				$stock_value = $current_stock * $row->sales_price;
				$total_risk_value += $stock_value;
				
				// Determine stock status (same logic as reports)
				$stock_status = '';
				$status_class = '';
				if($current_stock == 0) {
					$stock_status = $this->lang->line('out_of_stock');
					$status_class = 'danger';
					$out_of_stock_count++;
				} elseif($current_stock < ($minimum_stock_level * 0.5)) {
					$stock_status = $this->lang->line('critical_stock');
					$status_class = 'danger';
					$critical_stock_count++;
				} elseif($current_stock < $minimum_stock_level) {
					$stock_status = $this->lang->line('low_stock');
					$status_class = 'warning';
					$low_stock_count++;
				}
				
				// Calculate reorder quantity (same as reports)
				$suggested_reorder = $minimum_stock_level * 2;
				$reorder_needed = max(0, $suggested_reorder - $current_stock);
				
				$stock_alert_items[] = array(
					'item_id' => $row->item_id,
					'item_code' => $row->item_code,
					'item_name' => $row->item_name,
					'category_name' => $row->category_name ?: 'N/A',
					'brand_name' => $row->brand_name ?: 'N/A',
					'current_stock' => $current_stock,
					'alert_qty' => $minimum_stock_level,
					'stock_status' => $stock_status,
					'status_class' => $status_class,
					'stock_value' => $stock_value,
					'reorder_needed' => $reorder_needed,
					'sales_price' => $row->sales_price
				);
			}
		}
		
		return array(
			'out_of_stock_count' => $out_of_stock_count,
			'critical_stock_count' => $critical_stock_count,
			'low_stock_count' => $low_stock_count,
			'total_risk_value' => $this->format_currency($total_risk_value, $base_currency_symbol, $base_currency_position),
			'stock_alert_items' => $stock_alert_items
		);
	}
	
	// Helper method to format currency
	private function format_currency($amount, $symbol, $position) {
		if($position == 'before') {
			return $symbol . number_format($amount, 2);
		} else {
			return number_format($amount, 2) . ' ' . $symbol;
		}
	}
}