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

class Expense_model extends CI_Model {

	//Datatable start
	var $table = 'db_expense as a';
	var $column_order = array('a.id','a.expense_date','b.category_name','a.reference_no','a.expense_for','a.expense_amt','a.account_id','a.note','a.created_by','a.store_id','a.expense_currency_id','a.base_currency_amount'); //set column field database for datatable orderable
	var $column_search = array('a.id','a.expense_date','b.category_name','a.reference_no','a.expense_for','a.expense_amt','a.account_id','a.note','a.created_by','a.store_id'); //set column field database for datatable searchable 
	var $order = array('a.id' => 'desc'); // default order 

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

	private function _get_datatables_query()
	{
		
		$this->db->select('a.*, b.category_name');
		$this->db->from($this->table);
		$this->db->join('db_expense_category as b','b.id=a.category_id','left');
		
		//if not admin
		//if(!is_admin()){
			$this->db->where("a.store_id",get_current_store_id());
		//}
	     if(!is_admin()){
	      	if($this->session->userdata('role_id')!='2'){
	      		if(!permissions('show_all_users_expenses')){
	      			$this->db->where("upper(a.created_by)",strtoupper($this->session->userdata('inv_username')));
	      		}
	      	}
	      }
	     
		$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();
	}

	//Save Cutomers
	public function verify_and_save(){
		//Filtering XSS and html escape from user inputs 
		extract($this->security->xss_clean(html_escape(array_merge($this->data,$_POST))));
		
		// Get currency information
		$expense_currency_id = isset($expense_currency_id) ? $expense_currency_id : get_base_currency_id();
		$base_currency = get_base_currency();
		$exchange_rate = 1.0;
		$base_currency_amount = $expense_amt;
		
		// Handle currency conversion
		if ($expense_currency_id != $base_currency->id) {
			$exchange_rate = get_exchange_rate($expense_currency_id, $base_currency->id);
			$base_currency_amount = convert_currency($expense_amt, $expense_currency_id, $base_currency->id);
		}
		
		// Validate account balance (account is now required)
		$account_balance = get_account_balance($account_id);
		
		// Get account currency
		$account_currency = $this->db->select('currency_id')->where('id', $account_id)->get('ac_accounts')->row();
		$account_currency_id = $account_currency ? $account_currency->currency_id : get_base_currency_id();
		
		// Convert expense amount to account currency for comparison
		$expense_amount_in_account_currency = $expense_amt;
		if ($expense_currency_id != $account_currency_id) {
			$expense_amount_in_account_currency = convert_currency($expense_amt, $expense_currency_id, $account_currency_id);
		}
		
		if ($account_balance < $expense_amount_in_account_currency) {
			$account_currency_info = $this->db->select('symbol, currency_code')->where('id', $account_currency_id)->get('db_currency')->row();
			$expense_currency_info = $this->db->select('symbol, currency_code')->where('id', $expense_currency_id)->get('db_currency')->row();
			
			return "Insufficient account balance. Available: " . format_currency_amount($account_balance, $account_currency_id) . 
				   ", Required: " . format_currency_amount($expense_amount_in_account_currency, $account_currency_id) . 
				   " (from " . format_currency_amount($expense_amt, $expense_currency_id) . ")";
		}
		
		$expense_code = get_init_code('expense');
		
		// Auto-generate reference number if not provided
		if (empty($reference_no)) {
			$reference_no = 'EXP-' . date('Ymd') . '-' . str_pad(get_count_id('db_expense'), 4, '0', STR_PAD_LEFT);
		}
		
		$info = array(  
						'count_id' 					=> get_count_id('db_expense'), 
	    				'expense_code' 				=> $expense_code,
	    				'category_id' 				=> $category_id,
	    				'expense_for' 				=> $expense_for,
	    				'expense_amt' 				=> $expense_amt,
	    				'reference_no' 				=> $reference_no,
	    				'note' 				=> $note,
	    				'expense_date' 				=> system_fromatted_date($expense_date),
	    				'expense_currency_id' 		=> $expense_currency_id,
	    				'exchange_rate' 			=> $exchange_rate,
	    				'base_currency_amount' 		=> $base_currency_amount,
	    				/*System Info*/
	    				'created_date' 				=> $CUR_DATE,
	    				'created_time' 				=> $CUR_TIME,
	    				'created_by' 				=> $CUR_USERNAME,
	    				'system_ip' 				=> $SYSTEM_IP,
	    				'system_name' 				=> $SYSTEM_NAME,
	    				'status' 					=> 1,
	    				'payment_type' 				=> $payment_type,
	    				'account_id' 		=> $account_id,
	    			);
		
		$info['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();	

		$q1 = $this->db->insert('db_expense', $info);
		$reference_table_id = $this->db->insert_id();
		//Set the payment to specified account (account is now required)
		//ACCOUNT INSERT - Store amounts in account currency and base currency separately
		$payment_code=get_init_code('expense_payment');
		
		// Calculate amounts in account currency
		$expense_amount_in_account_currency = $expense_amt;
		if ($expense_currency_id != $account_currency_id) {
			$expense_amount_in_account_currency = convert_currency($expense_amt, $expense_currency_id, $account_currency_id);
		}
		
		$insert_bit = insert_account_transaction(array(
													'transaction_type'  	=> 'EXPENSE PAYMENT',
													'reference_table_id'  	=> $reference_table_id,
													'debit_account_id'  	=> $account_id,
													'credit_account_id'  	=> null,
													'debit_amt'  			=> $expense_amount_in_account_currency,  // Store in account currency
													'credit_amt'  			=> 0,
													'process'  				=> 'SAVE',
													'note'  				=> $note . ($expense_currency_id != $base_currency->id ? ' (Converted from ' . format_currency_amount($expense_amt, $expense_currency_id) . ')' : ''),
													'transaction_date'  	=> $CUR_DATE,
													'payment_code'  		=> $payment_code,
													'customer_id'  			=> null,
													'supplier_id'  			=> null,
													// Add currency information
													'currency_id'  			=> $account_currency_id,
													'transaction_currency_id' => $expense_currency_id,
													'exchange_rate'  		=> $exchange_rate,
													'base_currency_debit_amt' => $base_currency_amount,  // Store base currency amount
													'base_currency_credit_amt' => 0,
											));
		if(!$insert_bit){
			return "failed";
		}
		//end

		if ($q1){
			    $this->session->set_flashdata('success', 'Success!! Record Added Successfully!');
		        return "success";
		}
		else{
		        return "failed";
		}
		
	}

	//Get expenses_details
	public function get_details($id,$data){
		//Validate This expenses already exist or not
		$query=$this->db->query("select * from db_expense where upper(id)=upper('$id')");
		if($query->num_rows()==0){
			show_404();exit;
		}
		else{
			$query=$query->row();
			$data['q_id']=$query->id;
			$data['expense_code']=$query->expense_code;			
			$data['expense_date']=show_date($query->expense_date);
			$data['category_id']=$query->category_id;
			$data['reference_no']=$query->reference_no;
			$data['expense_for']=$query->expense_for;
			$data['expense_amt']=store_number_format($query->expense_amt,0);
			$data['note']=$query->note;
			$data['store_id']=$query->store_id;
			$data['payment_type']=$query->payment_type;
			$data['account_id']=$query->account_id;
			$data['expense_currency_id']=isset($query->expense_currency_id) ? $query->expense_currency_id : get_base_currency_id();
			return $data;
		}
	}
	public function update_expense(){
		//Filtering XSS and html escape from user inputs 
		extract($this->security->xss_clean(html_escape(array_merge($this->data,$_POST))));
		
		// Get currency information
		$expense_currency_id = isset($expense_currency_id) ? $expense_currency_id : get_base_currency_id();
		$base_currency = get_base_currency();
		$exchange_rate = 1.0;
		$base_currency_amount = $expense_amt;
		
		// Handle currency conversion
		if ($expense_currency_id != $base_currency->id) {
			$exchange_rate = get_exchange_rate($expense_currency_id, $base_currency->id);
			$base_currency_amount = convert_currency($expense_amt, $expense_currency_id, $base_currency->id);
		}
		
		// Validate account balance (account is now required)
		$account_balance = get_account_balance($account_id);
		
		// Get account currency
		$account_currency = $this->db->select('currency_id')->where('id', $account_id)->get('ac_accounts')->row();
		$account_currency_id = $account_currency ? $account_currency->currency_id : get_base_currency_id();
		
		// Convert expense amount to account currency for comparison
		$expense_amount_in_account_currency = $expense_amt;
		if ($expense_currency_id != $account_currency_id) {
			$expense_amount_in_account_currency = convert_currency($expense_amt, $expense_currency_id, $account_currency_id);
		}
		
		if ($account_balance < $expense_amount_in_account_currency) {
			$account_currency_info = $this->db->select('symbol, currency_code')->where('id', $account_currency_id)->get('db_currency')->row();
			$expense_currency_info = $this->db->select('symbol, currency_code')->where('id', $expense_currency_id)->get('db_currency')->row();
			
			return "Insufficient account balance. Available: " . format_currency_amount($account_balance, $account_currency_id) . 
				   ", Required: " . format_currency_amount($expense_amount_in_account_currency, $account_currency_id) . 
				   " (from " . format_currency_amount($expense_amt, $expense_currency_id) . ")";
		}
		
		// Auto-generate reference number if not provided
		if (empty($reference_no)) {
			$reference_no = 'EXP-' . date('Ymd') . '-' . str_pad(get_count_id('db_expense'), 4, '0', STR_PAD_LEFT);
		}
		
		$info = array(
	    				'category_id' 			=> $category_id,
	    				'expense_for' 				=> $expense_for,
	    				'expense_amt' 				=> $expense_amt,
	    				'reference_no' 				=> $reference_no,
	    				'note' 				=> $note,
	    				'expense_date' 				=> system_fromatted_date($expense_date),
	    				'expense_currency_id' 		=> $expense_currency_id,
	    				'exchange_rate' 			=> $exchange_rate,
	    				'base_currency_amount' 		=> $base_currency_amount,
	    				'payment_type' 				=> $payment_type,
	    				'account_id' 		=> $account_id,
	    			);
		
		$info['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();	

		$q1 = $this->db->where('id',$q_id)->update('db_expense', $info);

		//Set the payment to specified account (account is now required)
		//ACCOUNT INSERT - Use base currency amount for accounting
		$insert_bit = insert_account_transaction(array(
													'transaction_type'  	=> 'EXPENSE PAYMENT',
													'reference_table_id'  	=> $q_id,
													'debit_account_id'  	=> $account_id,
													'credit_account_id'  	=> null,
													'debit_amt'  			=> $base_currency_amount,
													'credit_amt'  			=> 0,
													'process'  				=> 'UPDATE',
													'note'  				=> $note . ($expense_currency_id != $base_currency->id ? ' (Converted from ' . format_currency_amount($expense_amt, $expense_currency_id) . ')' : ''),
													'transaction_date'  	=> $CUR_DATE,
													'payment_code'  		=> '',
													'customer_id'  			=> null,
													'supplier_id'  			=> null,
											));
		if(!$insert_bit){
			return "failed";
		}
		//end

		if ($q1){
				$this->session->set_flashdata('success', 'Success!! Record Updated Successfully!');
		        return "success";
		}
		else{
		        return "failed";
		}
		
	}
	public function update_status($id,$status){
		if (set_status_of_table($id,$status,'db_expense')){
            echo "success";
        }
        else{
            echo "failed";
        }
	}
	
	public function check_table_data($table_name,$field,$value){
		return $this->db->query("select count(*) as tot_count from db_expense where $field='$value'")->row()->tot_count;
	}
	
	public function delete_expenses_from_table($ids){
		$this->db->trans_begin();
		
		// Log the deletion attempt
		log_message('debug', 'Starting expense deletion for IDs: ' . $ids);
		
		// Get affected accounts before deleting transactions
		$reset_accounts = $this->db->select("debit_account_id,credit_account_id")
									->where("ref_expense_id in ($ids)")
									->group_by("debit_account_id,credit_account_id")
									->get("ac_transactions");

		log_message('debug', 'Found ' . $reset_accounts->num_rows() . ' affected accounts for expenses');

		// Delete related transactions first (to avoid foreign key constraint issues)
		$this->db->where("ref_expense_id in ($ids)");
		$delete_transactions = $this->db->delete("ac_transactions");
		log_message('debug', 'Deleted expense transactions: ' . ($delete_transactions ? 'success' : 'failed'));
		if (!$delete_transactions){
			log_message('error', 'Failed to delete expense transactions: ' . $this->db->last_query());
			$this->db->trans_rollback();
			return "failed";
		}

		// Now delete the expense records
		$this->db->where("id in ($ids)");
		//if not admin
		if(!is_admin()){
			$this->db->where("store_id",get_current_store_id());
		}

		$query1=$this->db->delete("db_expense");
		log_message('debug', 'Deleted expenses: ' . ($query1 ? 'success' : 'failed'));
		if(!$query1){
			log_message('error', 'Failed to delete expenses: ' . $this->db->last_query());
			log_message('error', 'Database error: ' . json_encode($this->db->error()));
			$this->db->trans_rollback();
            return "failed";
        }

		// Update account balances
        if($reset_accounts->num_rows()>0){
        	foreach ($reset_accounts->result() as $res1) {
        		// Only update debit account balance if debit_account_id is not null
        		if($res1->debit_account_id) {
        			log_message('debug', 'Updating balance for debit account: ' . $res1->debit_account_id);
        			if(!update_account_balance($res1->debit_account_id)){
						log_message('error', 'Failed to update debit account balance: ' . $res1->debit_account_id);
						$this->db->trans_rollback();
						return 'failed';
					}
				}

				// Only update credit account balance if credit_account_id is not null
				if($res1->credit_account_id) {
					log_message('debug', 'Updating balance for credit account: ' . $res1->credit_account_id);
					if(!update_account_balance($res1->credit_account_id)){
						log_message('error', 'Failed to update credit account balance: ' . $res1->credit_account_id);
						$this->db->trans_rollback();
						return 'failed';
					}
				}
        	}
        }

		$this->db->trans_commit();
		log_message('debug', 'Expense deletion completed successfully');
		return "success";
	}
}
