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

class Exchange_rates_model extends CI_Model {
    
    var $table = 'db_exchange_rates';
    var $column_order = array(null, 'fc.currency_name', 'tc.currency_name', 'er.exchange_rate', 'er.effective_date', 'er.rate_type', 'er.status');
    var $column_search = array('fc.currency_name', 'tc.currency_name', 'er.exchange_rate', 'er.effective_date');
    var $order = array('er.effective_date' => 'desc');
    
    public function __construct() {
        parent::__construct();
        $this->create_table_if_not_exists();
    }
    
    /**
     * Create exchange rates table if it doesn't exist
     */
    private function create_table_if_not_exists() {
        if (!$this->db->table_exists($this->table)) {
            $sql = "CREATE TABLE IF NOT EXISTS `db_exchange_rates` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `from_currency_id` int(11) NOT NULL,
              `to_currency_id` int(11) NOT NULL,
              `exchange_rate` decimal(15,6) NOT NULL,
              `rate_type` enum('manual','api') NOT NULL DEFAULT 'manual',
              `effective_date` date NOT NULL,
              `expiry_date` date DEFAULT NULL,
              `status` tinyint(1) NOT NULL DEFAULT 1,
              `created_by` varchar(50) DEFAULT NULL,
              `created_date` datetime DEFAULT NULL,
              `updated_by` varchar(50) DEFAULT NULL,
              `updated_date` datetime DEFAULT NULL,
              `system_ip` varchar(50) DEFAULT NULL,
              `system_name` varchar(255) DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `from_currency_id` (`from_currency_id`),
              KEY `to_currency_id` (`to_currency_id`),
              KEY `effective_date` (`effective_date`),
              KEY `rate_type` (`rate_type`),
              KEY `status` (`status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
            
            $this->db->query($sql);
        } else {
            // Check if rate_type column exists, if not add it
            if (!$this->db->field_exists('rate_type', $this->table)) {
                $this->db->query("ALTER TABLE `db_exchange_rates` ADD COLUMN `rate_type` enum('manual','api') NOT NULL DEFAULT 'manual' AFTER `exchange_rate`");
            }
        }
    }
    
    private function _get_datatables_query() {
        $this->db->select('er.*, 
                          fc.currency_name as from_currency_name, 
                          fc.currency_code as from_currency_code,
                          tc.currency_name as to_currency_name, 
                          tc.currency_code as to_currency_code');
        $this->db->from($this->table . ' er');
        $this->db->join('db_currency fc', 'fc.id = er.from_currency_id', 'left');
        $this->db->join('db_currency tc', 'tc.id = er.to_currency_id', 'left');
        // Removed rate_type filter to show both manual and API rates
        
        $i = 0;
        
        foreach ($this->column_search as $item) {
            if ($_POST['search']['value']) {
                if ($i === 0) {
                    $this->db->group_start();
                    $this->db->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                
                if (count($this->column_search) - 1 == $i) {
                    $this->db->group_end();
                }
            }
            $i++;
        }
        
        if (isset($_POST['order'])) {
            $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->from($this->table);
        return $this->db->count_all_results();
    }
    
    public function save() {
        extract($this->security->xss_clean(html_escape(array_merge($this->data, $_POST))));
        
        // Validate that from and to currencies are different
        if ($from_currency_id == $to_currency_id) {
            return "From and To currencies cannot be the same!";
        }
        
        // Validate and format the effective date
        if (empty($effective_date)) {
            return "Effective date is required!";
        }
        
        // Convert date to proper format if needed
        $effective_date = date('Y-m-d', strtotime($effective_date));
        if ($effective_date == '1970-01-01') {
            return "Invalid effective date format!";
        }
        
        // Check if rate already exists for this date
        $existing = $this->db->where('from_currency_id', $from_currency_id)
                            ->where('to_currency_id', $to_currency_id)
                            ->where('effective_date', $effective_date)
                            ->get($this->table);
        
        if ($existing->num_rows() > 0) {
            return "Exchange rate for this currency pair and date already exists!";
        }
        
        // Format expiry date if provided
        $expiry_date_formatted = null;
        if (!empty($expiry_date)) {
            $expiry_date_formatted = date('Y-m-d', strtotime($expiry_date));
            if ($expiry_date_formatted == '1970-01-01') {
                $expiry_date_formatted = null; // Invalid date, set to null
            }
        }
        
        $data = array(
            'from_currency_id' => $from_currency_id,
            'to_currency_id' => $to_currency_id,
            'exchange_rate' => $exchange_rate,
            'rate_type' => 'manual',
            'effective_date' => $effective_date,
            'expiry_date' => $expiry_date_formatted,
            'status' => 1,
            'created_by' => $this->session->userdata('inv_userid') ?: 'system',
            'created_date' => date('Y-m-d H:i:s'),
            'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
            'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
        );
        
        if ($this->db->insert($this->table, $data)) {
            $this->session->set_flashdata('success', 'Exchange rate added successfully!');
            return "success";
        } else {
            return "Failed to add exchange rate!";
        }
    }
    
    public function save_bidirectional() {
        extract($this->security->xss_clean(html_escape(array_merge($this->data, $_POST))));
        
        // Validate that from and to currencies are different
        if ($from_currency_id == $to_currency_id) {
            return "From and To currencies cannot be the same!";
        }
        
        // Validate and format the effective date
        if (empty($effective_date)) {
            return "Effective date is required!";
        }
        
        // Convert date to proper format if needed
        $effective_date = date('Y-m-d', strtotime($effective_date));
        if ($effective_date == '1970-01-01') {
            return "Invalid effective date format!";
        }
        
        // Format expiry date if provided
        $expiry_date_formatted = null;
        if (!empty($expiry_date)) {
            $expiry_date_formatted = date('Y-m-d', strtotime($expiry_date));
            if ($expiry_date_formatted == '1970-01-01') {
                $expiry_date_formatted = null; // Invalid date, set to null
            }
        }
        
        $this->db->trans_start();
        
        try {
            // Save A -> B direction
            $existing_ab = $this->db->where('from_currency_id', $from_currency_id)
                                  ->where('to_currency_id', $to_currency_id)
                                  ->where('effective_date', $effective_date)
                                  ->get($this->table);
            
            if ($existing_ab->num_rows() == 0) {
                $data_ab = array(
                    'from_currency_id' => $from_currency_id,
                    'to_currency_id' => $to_currency_id,
                    'exchange_rate' => $exchange_rate,
                    'rate_type' => 'manual',
                    'effective_date' => $effective_date,
                    'expiry_date' => $expiry_date_formatted,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if (!$this->db->insert($this->table, $data_ab)) {
                    throw new Exception("Failed to insert A->B exchange rate");
                }
            }
            
            // Save B -> A direction (inverse rate)
            $existing_ba = $this->db->where('from_currency_id', $to_currency_id)
                                  ->where('to_currency_id', $from_currency_id)
                                  ->where('effective_date', $effective_date)
                                  ->get($this->table);
            
            if ($existing_ba->num_rows() == 0) {
                $inverse_rate = 1 / $exchange_rate; // Calculate inverse rate
                
                $data_ba = array(
                    'from_currency_id' => $to_currency_id,
                    'to_currency_id' => $from_currency_id,
                    'exchange_rate' => $inverse_rate,
                    'rate_type' => 'manual',
                    'effective_date' => $effective_date,
                    'expiry_date' => $expiry_date_formatted,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if (!$this->db->insert($this->table, $data_ba)) {
                    throw new Exception("Failed to insert B->A exchange rate");
                }
            }
            
            $this->db->trans_complete();
            
            if ($this->db->trans_status() === FALSE) {
                throw new Exception("Transaction failed");
            }
            
            $this->session->set_flashdata('success', 'Bidirectional exchange rates added successfully!');
            return "success";
            
        } catch (Exception $e) {
            $this->db->trans_rollback();
            return "Failed to add bidirectional exchange rates: " . $e->getMessage();
        }
    }
    
    public function get_by_id($id) {
        $this->db->select('er.*, 
                          fc.currency_name as from_currency_name, 
                          fc.currency_code as from_currency_code,
                          tc.currency_name as to_currency_name, 
                          tc.currency_code as to_currency_code');
        $this->db->from($this->table . ' er');
        $this->db->join('db_currency fc', 'fc.id = er.from_currency_id', 'left');
        $this->db->join('db_currency tc', 'tc.id = er.to_currency_id', 'left');
        $this->db->where('er.id', $id);
        
        $query = $this->db->get();
        return $query->row();
    }
    
    public function update() {
        extract($this->security->xss_clean(html_escape(array_merge($this->data, $_POST))));
        
        // Validate that from and to currencies are different
        if ($from_currency_id == $to_currency_id) {
            return "From and To currencies cannot be the same!";
        }
        
        // Validate and format the effective date
        if (empty($effective_date)) {
            return "Effective date is required!";
        }
        
        // Convert date to proper format if needed
        $effective_date = date('Y-m-d', strtotime($effective_date));
        if ($effective_date == '1970-01-01') {
            return "Invalid effective date format!";
        }
        
        // Check if rate already exists for this date (excluding current record)
        $existing = $this->db->where('from_currency_id', $from_currency_id)
                            ->where('to_currency_id', $to_currency_id)
                            ->where('effective_date', $effective_date)
                            ->where('id !=', $id)
                            ->get($this->table);
        
        if ($existing->num_rows() > 0) {
            return "Exchange rate for this currency pair and date already exists!";
        }
        
        // Format expiry date if provided
        $expiry_date_formatted = null;
        if (!empty($expiry_date)) {
            $expiry_date_formatted = date('Y-m-d', strtotime($expiry_date));
            if ($expiry_date_formatted == '1970-01-01') {
                $expiry_date_formatted = null; // Invalid date, set to null
            }
        }
        
        $data = array(
            'from_currency_id' => $from_currency_id,
            'to_currency_id' => $to_currency_id,
            'exchange_rate' => $exchange_rate,
            'effective_date' => $effective_date,
            'expiry_date' => $expiry_date_formatted,
            'updated_by' => get_current_user_id(),
            'updated_date' => date('Y-m-d H:i:s')
        );
        
        $this->db->where('id', $id);
        
        if ($this->db->update($this->table, $data)) {
            $this->session->set_flashdata('success', 'Exchange rate updated successfully!');
            return "success";
        } else {
            return "Failed to update exchange rate!";
        }
    }
    
    public function delete($id) {
        // Check if the rate is used in any transactions
        if ($this->is_rate_used_in_transactions($id)) {
            return "Cannot delete exchange rate! This rate is being used in transactions and cannot be deleted to maintain data integrity.";
        }
        
        // Proceed with deletion
        $this->db->where('id', $id);
        if ($this->db->delete($this->table)) {
            $this->session->set_flashdata('success', 'Exchange rate deleted successfully!');
            return "success";
        } else {
            return "Failed to delete exchange rate!";
        }
    }
    
    public function delete_all() {
        // Exchange rates cannot be deleted once added to maintain data integrity
        return "Cannot delete exchange rates! Exchange rates cannot be deleted once added to maintain data integrity and historical accuracy.";
    }
    
    public function is_rate_used_in_transactions($rate_id) {
        // Get the exchange rate details
        $rate = $this->db->where('id', $rate_id)->get($this->table)->row();
        if (!$rate) {
            return false; // Rate doesn't exist
        }
        
        $total_count = 0;
        
        try {
            // Count sales payments using this currency (not exact exchange rate match)
            if ($this->db->table_exists('db_salespayments')) {
                $this->db->where('payment_currency_id', $rate->from_currency_id);
                $sales_payments_count = $this->db->count_all_results('db_salespayments');
                $total_count += $sales_payments_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count purchase payments using this currency
            if ($this->db->table_exists('db_purchasepayments')) {
                $this->db->where('payment_currency_id', $rate->from_currency_id);
                $purchase_payments_count = $this->db->count_all_results('db_purchasepayments');
                $total_count += $purchase_payments_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count account transactions using this currency
            if ($this->db->table_exists('ac_transactions')) {
                $this->db->where('currency_id', $rate->from_currency_id);
                $account_transactions_count = $this->db->count_all_results('ac_transactions');
                $total_count += $account_transactions_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count money transfers using this currency
            if ($this->db->table_exists('ac_moneytransfer')) {
                $this->db->where('transfer_currency_id', $rate->from_currency_id);
                $money_transfers_count = $this->db->count_all_results('ac_moneytransfer');
                $total_count += $money_transfers_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count money deposits using this currency
            if ($this->db->table_exists('ac_moneydeposits')) {
                $this->db->where('deposit_currency_id', $rate->from_currency_id);
                $money_deposits_count = $this->db->count_all_results('ac_moneydeposits');
                $total_count += $money_deposits_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        // Also check sales and purchases tables directly
        try {
            if ($this->db->table_exists('db_sales')) {
                // Check if currency_id column exists, if not skip
                if ($this->db->field_exists('currency_id', 'db_sales')) {
                    $this->db->where('currency_id', $rate->from_currency_id);
                    $sales_count = $this->db->count_all_results('db_sales');
                    $total_count += $sales_count;
                }
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            if ($this->db->table_exists('db_purchase')) {
                // Check if currency_id column exists, if not skip
                if ($this->db->field_exists('currency_id', 'db_purchase')) {
                    $this->db->where('currency_id', $rate->from_currency_id);
                    $purchase_count = $this->db->count_all_results('db_purchase');
                    $total_count += $purchase_count;
                }
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        // Return true if any transactions are using this currency
        return $total_count > 0;
    }
    
    public function get_transaction_count_for_rate($rate_id) {
        // Get the exchange rate details
        $rate = $this->db->where('id', $rate_id)->get($this->table)->row();
        if (!$rate) {
            return 0;
        }
        
        $total_count = 0;
        
        try {
            // Count sales payments using this currency (not exact exchange rate match)
            if ($this->db->table_exists('db_salespayments')) {
                $this->db->where('payment_currency_id', $rate->from_currency_id);
                $sales_payments_count = $this->db->count_all_results('db_salespayments');
                $total_count += $sales_payments_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count purchase payments using this currency
            if ($this->db->table_exists('db_purchasepayments')) {
                $this->db->where('payment_currency_id', $rate->from_currency_id);
                $purchase_payments_count = $this->db->count_all_results('db_purchasepayments');
                $total_count += $purchase_payments_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count account transactions using this currency
            if ($this->db->table_exists('ac_transactions')) {
                $this->db->where('currency_id', $rate->from_currency_id);
                $account_transactions_count = $this->db->count_all_results('ac_transactions');
                $total_count += $account_transactions_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            // Count money transfers using this currency
            if ($this->db->table_exists('db_moneytransfer')) {
                $this->db->where('from_currency_id', $rate->from_currency_id);
                $money_transfers_count = $this->db->count_all_results('db_moneytransfer');
                $total_count += $money_transfers_count;
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        // Also check sales and purchases tables directly
        try {
            if ($this->db->table_exists('db_sales')) {
                // Check if currency_id column exists, if not skip
                if ($this->db->field_exists('currency_id', 'db_sales')) {
                    $this->db->where('currency_id', $rate->from_currency_id);
                    $sales_count = $this->db->count_all_results('db_sales');
                    $total_count += $sales_count;
                }
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        try {
            if ($this->db->table_exists('db_purchase')) {
                // Check if currency_id column exists, if not skip
                if ($this->db->field_exists('currency_id', 'db_purchase')) {
                    $this->db->where('currency_id', $rate->from_currency_id);
                    $purchase_count = $this->db->count_all_results('db_purchase');
                    $total_count += $purchase_count;
                }
            }
        } catch (Exception $e) {
            // Table doesn't exist or other error, skip
        }
        
        return $total_count;
    }
    
    /**
     * Fetch single exchange rate from external API
     * @param int $from_currency_id - From currency ID
     * @param int $to_currency_id - To currency ID
     * @return array
     */
    public function fetch_single_rate_from_api($from_currency_id, $to_currency_id) {
        // Get currency codes
        $from_currency = $this->db->where('id', $from_currency_id)->get('db_currency')->row();
        $to_currency = $this->db->where('id', $to_currency_id)->get('db_currency')->row();
        
        if (!$from_currency || !$to_currency) {
            return array(
                'success' => false,
                'message' => 'Currency not found in database'
            );
        }
        
        // If same currency, return 1.0
        if ($from_currency_id == $to_currency_id) {
            return array(
                'success' => true,
                'rate' => 1.0,
                'from_currency_code' => $from_currency->currency_code,
                'to_currency_code' => $to_currency->currency_code,
                'from_currency_name' => $from_currency->currency_name,
                'to_currency_name' => $to_currency->currency_name,
                'message' => 'Same currency - rate is 1.0'
            );
        }
        
        // Try multiple FREE APIs as fallback (no API key required)
        $apis = array(
            "https://api.exchangerate.host/latest?base={$from_currency->currency_code}&symbols={$to_currency->currency_code}",
            "https://cdn.jsdelivr.net/gh/fawazahmed0/currency-api@1/latest/currencies/" . strtolower($from_currency->currency_code) . "/" . strtolower($to_currency->currency_code) . ".json",
            "https://api.fxratesapi.com/latest?base={$from_currency->currency_code}&symbols={$to_currency->currency_code}"
        );
        
        // Check internet connectivity first
        if (!$this->check_internet_connection()) {
            return array(
                'success' => false,
                'message' => 'Internet connection required to fetch rates via API. Please check your internet connection and try again.'
            );
        }
        
        $last_error = '';
        
        foreach ($apis as $api_url) {
            // Initialize cURL
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $api_url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_TIMEOUT, 10); // 10 second timeout
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5); // 5 second connection timeout
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_USERAGENT, 'NCS Billing Book/1.0');
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
            
            $response = curl_exec($ch);
            $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $curl_error = curl_error($ch);
            curl_close($ch);
            
            if ($curl_error) {
                $last_error = 'Failed to connect to exchange rate API: ' . $curl_error;
                continue;
            }
            
            if ($http_code !== 200) {
                $last_error = 'Exchange rate API returned error code: ' . $http_code;
                continue;
            }
            
            $data = json_decode($response, true);
            
            if (!$data) {
                $last_error = 'Invalid JSON response from exchange rate API';
                continue;
            }
            
            // Handle different API response formats
            $rate_value = null;
            $date = null;
            
            if (isset($data['rates']) && isset($data['rates'][$to_currency->currency_code])) {
                // exchangerate.host / fxratesapi.com format
                $rate_value = $data['rates'][$to_currency->currency_code];
                $date = isset($data['date']) ? $data['date'] : date('Y-m-d');
            } elseif (isset($data[strtolower($to_currency->currency_code)])) {
                // fawazahmed0 currency-api format (direct currency pair)
                $rate_value = $data[strtolower($to_currency->currency_code)];
                $date = isset($data['date']) ? $data['date'] : date('Y-m-d');
            } elseif (isset($data['data']) && isset($data['data'][$to_currency->currency_code])) {
                // currencyapi.com format
                $rate_value = $data['data'][$to_currency->currency_code]['value'];
                $date = isset($data['meta']['last_updated_at']) ? date('Y-m-d', strtotime($data['meta']['last_updated_at'])) : date('Y-m-d');
            } elseif (isset($data['result']) && isset($data['result'][$to_currency->currency_code])) {
                // Another exchangerate.host format
                $rate_value = $data['result'][$to_currency->currency_code];
                $date = isset($data['date']) ? $data['date'] : date('Y-m-d');
            }
            
            if ($rate_value === null) {
                $last_error = "Exchange rate for {$to_currency->currency_code} not available in API response";
                continue;
            }
            
            // Success! Return the rate
            return array(
                'success' => true,
                'rate' => $rate_value,
                'from_currency_code' => $from_currency->currency_code,
                'to_currency_code' => $to_currency->currency_code,
                'from_currency_name' => $from_currency->currency_name,
                'to_currency_name' => $to_currency->currency_name,
                'date' => $date,
                'message' => 'Rate fetched successfully from API'
            );
        }
        
        // All APIs failed
        return array(
            'success' => false,
            'message' => 'All exchange rate APIs failed. Last error: ' . $last_error
        );
    }

    /**
     * Fetch exchange rates from external API
     * @param string $from_currency_code - Base currency code (e.g., 'USD')
     * @return array
     */
    public function fetch_rates_from_api($from_currency_code = 'USD') {
        // Try multiple FREE APIs as fallback (no API key required)
        $apis = array(
            "https://api.exchangerate.host/latest?base={$from_currency_code}",
            "https://api.fxratesapi.com/latest?base={$from_currency_code}"
        );
        
        // Check internet connectivity first
        if (!$this->check_internet_connection()) {
            return array(
                'success' => false,
                'message' => 'Internet connection required to fetch rates via API. Please check your internet connection and try again.',
                'rates' => array()
            );
        }
        
        $last_error = '';
        
        foreach ($apis as $api_url) {
            // Initialize cURL
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $api_url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_TIMEOUT, 10); // 10 second timeout
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5); // 5 second connection timeout
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_USERAGENT, 'NCS Billing Book/1.0');
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
            
            $response = curl_exec($ch);
            $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $curl_error = curl_error($ch);
            curl_close($ch);
            
            if ($curl_error) {
                $last_error = 'Failed to connect to exchange rate API: ' . $curl_error;
                continue;
            }
            
            if ($http_code !== 200) {
                $last_error = 'Exchange rate API returned error code: ' . $http_code;
                continue;
            }
            
            $data = json_decode($response, true);
            
            if (!$data) {
                $last_error = 'Invalid JSON response from exchange rate API';
                continue;
            }
            
            // Handle different API response formats
            $rates = null;
            $date = null;
            $base = null;
            
            if (isset($data['rates'])) {
                // fxratesapi.com format
                $rates = $data['rates'];
                $date = isset($data['date']) ? $data['date'] : date('Y-m-d');
                $base = isset($data['base']) ? $data['base'] : $from_currency_code;
            } elseif (isset($data['result'])) {
                // exchangerate.host format
                $rates = $data['result'];
                $date = isset($data['date']) ? $data['date'] : date('Y-m-d');
                $base = isset($data['base']) ? $data['base'] : $from_currency_code;
            }
            
            if (!$rates) {
                $last_error = 'No rates found in API response';
                continue;
            }
            
            // Success! Return the rates
            return array(
                'success' => true,
                'message' => 'Rates fetched successfully from API',
                'base_currency' => $base,
                'date' => $date,
                'rates' => $rates
            );
        }
        
        // All APIs failed
        return array(
            'success' => false,
            'message' => 'All exchange rate APIs failed. Last error: ' . $last_error,
            'rates' => array()
        );
    }
    
    /**
     * Check internet connectivity
     * @return bool
     */
    public function check_internet_connection() {
        // Try to connect to a reliable service
        $connected = @fsockopen("8.8.8.8", 53, $errno, $errstr, 5);
        if ($connected) {
            fclose($connected);
            return true;
        }
        
        // Fallback: try to connect to the API directly
        $connected = @fsockopen("api.fxratesapi.com", 443, $errno, $errstr, 5);
        if ($connected) {
            fclose($connected);
            return true;
        }
        
        // Another fallback
        $connected = @fsockopen("api.exchangerate.host", 443, $errno, $errstr, 5);
        if ($connected) {
            fclose($connected);
            return true;
        }
        
        return false;
    }
    
    /**
     * Save API rates to database
     * @param array $api_rates - Rates from API
     * @param string $base_currency_code - Base currency code
     * @param string $date - Date for the rates
     * @return array
     */
    public function save_api_rates($api_rates, $base_currency_code, $date) {
        $base_currency = $this->db->where('currency_code', $base_currency_code)->get('db_currency')->row();
        if (!$base_currency) {
            return array(
                'success' => false,
                'message' => 'Base currency not found in database'
            );
        }
        
        $saved_count = 0;
        $skipped_count = 0;
        $error_count = 0;
        
        foreach ($api_rates as $currency_code => $rate) {
            // Skip if same as base currency
            if ($currency_code === $base_currency_code) {
                continue;
            }
            
            // Get target currency
            $target_currency = $this->db->where('currency_code', $currency_code)->get('db_currency')->row();
            if (!$target_currency) {
                $skipped_count++;
                continue;
            }
            
            // Check if rate already exists for this date
            $existing = $this->db->where('from_currency_id', $base_currency->id)
                                ->where('to_currency_id', $target_currency->id)
                                ->where('effective_date', $date)
                                ->where('rate_type', 'api')
                                ->get($this->table);
            
            if ($existing->num_rows() > 0) {
                // Update existing rate
                $this->db->where('from_currency_id', $base_currency->id)
                        ->where('to_currency_id', $target_currency->id)
                        ->where('effective_date', $date)
                        ->where('rate_type', 'api')
                        ->update($this->table, array(
                            'exchange_rate' => $rate,
                            'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                            'updated_date' => date('Y-m-d H:i:s')
                        ));
                $saved_count++;
            } else {
                // Insert new rate
                $rate_data = array(
                    'from_currency_id' => $base_currency->id,
                    'to_currency_id' => $target_currency->id,
                    'exchange_rate' => $rate,
                    'rate_type' => 'api',
                    'effective_date' => $date,
                    'expiry_date' => null,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if ($this->db->insert($this->table, $rate_data)) {
                    $saved_count++;
                } else {
                    $error_count++;
                }
            }
        }
        
        return array(
            'success' => true,
            'message' => "API rates processed: {$saved_count} saved, {$skipped_count} skipped (currency not found), {$error_count} errors",
            'saved_count' => $saved_count,
            'skipped_count' => $skipped_count,
            'error_count' => $error_count
        );
    }
    
    /**
     * Save single API rate to database
     * @param int $from_currency_id - From currency ID
     * @param int $to_currency_id - To currency ID
     * @param float $rate - Exchange rate
     * @param string $date - Effective date
     * @param string $expiry_date - Expiry date (optional)
     * @return array
     */
    public function save_single_api_rate($from_currency_id, $to_currency_id, $rate, $date, $expiry_date = null) {
        // Check if table exists first
        if (!$this->db->table_exists($this->table)) {
            return array(
                'success' => false,
                'message' => 'Exchange rates table does not exist! Please create the table first.'
            );
        }
        
        // Validate that from and to currencies are different
        if ($from_currency_id == $to_currency_id) {
            return array(
                'success' => false,
                'message' => 'From and To currencies cannot be the same!'
            );
        }
        
        // Validate and format the effective date
        if (empty($date)) {
            return array(
                'success' => false,
                'message' => 'Effective date is required!'
            );
        }
        
        // Convert date to proper format if needed
        $effective_date = date('Y-m-d', strtotime($date));
        if ($effective_date == '1970-01-01') {
            return array(
                'success' => false,
                'message' => 'Invalid effective date format!'
            );
        }
        
        // Format expiry date if provided
        $expiry_date_formatted = null;
        if (!empty($expiry_date)) {
            $expiry_date_formatted = date('Y-m-d', strtotime($expiry_date));
            if ($expiry_date_formatted == '1970-01-01') {
                $expiry_date_formatted = null; // Invalid date, set to null
            }
        }
        
        // Check if rate already exists for this date and type
        $existing = $this->db->where('from_currency_id', $from_currency_id)
                            ->where('to_currency_id', $to_currency_id)
                            ->where('effective_date', $effective_date)
                            ->where('rate_type', 'api')
                            ->get($this->table);
        
        $rate_data = array(
            'from_currency_id' => $from_currency_id,
            'to_currency_id' => $to_currency_id,
            'exchange_rate' => $rate,
            'rate_type' => 'api',
            'effective_date' => $effective_date,
            'expiry_date' => $expiry_date_formatted,
            'status' => 1,
            'created_by' => $this->session->userdata('inv_userid') ?: 'system',
            'created_date' => date('Y-m-d H:i:s'),
            'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
            'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
        );
        
        if ($existing->num_rows() > 0) {
            // Update existing rate
            $this->db->where('from_currency_id', $from_currency_id)
                    ->where('to_currency_id', $to_currency_id)
                    ->where('effective_date', $effective_date)
                    ->where('rate_type', 'api')
                    ->update($this->table, array(
                        'exchange_rate' => $rate,
                        'expiry_date' => $expiry_date_formatted,
                        'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                        'updated_date' => date('Y-m-d H:i:s')
                    ));
            
            return array(
                'success' => true,
                'message' => 'API exchange rate updated successfully!'
            );
        } else {
            // Insert new rate
            // Debug: Log the data being inserted
            log_message('debug', 'Attempting to insert exchange rate: ' . json_encode($rate_data));
            
            if ($this->db->insert($this->table, $rate_data)) {
                $insert_id = $this->db->insert_id();
                log_message('debug', 'Exchange rate inserted successfully with ID: ' . $insert_id);
                return array(
                    'success' => true,
                    'message' => 'API exchange rate added successfully!'
                );
            } else {
                // Log database error for debugging
                $error = $this->db->error();
                log_message('error', 'Database insert failed: ' . json_encode($error));
                log_message('error', 'Insert data was: ' . json_encode($rate_data));
                return array(
                    'success' => false,
                    'message' => 'Failed to add API exchange rate! Database error: ' . $error['message']
                );
            }
        }
    }
    
    public function get_active_rates($date = null) {
        if ($date === null) {
            $date = date('Y-m-d');
        }
        
        $this->db->select('er.*, 
                          fc.currency_name as from_currency_name, 
                          fc.currency_code as from_currency_code,
                          tc.currency_name as to_currency_name, 
                          tc.currency_code as to_currency_code');
        $this->db->from($this->table . ' er');
        $this->db->join('db_currency fc', 'fc.id = er.from_currency_id', 'left');
        $this->db->join('db_currency tc', 'tc.id = er.to_currency_id', 'left');
        $this->db->where('er.status', 1);
        $this->db->where('er.rate_type', 'manual');
        $this->db->where('er.effective_date <=', $date);
        $this->db->where('(er.expiry_date IS NULL OR er.expiry_date >=)', $date);
        $this->db->order_by('er.effective_date', 'DESC');
        
        $query = $this->db->get();
        return $query->result();
    }
    
    /**
     * Save single API rate bidirectionally
     */
    public function save_single_api_rate_bidirectional($from_currency_id, $to_currency_id, $rate, $date, $expiry_date = null) {
        // Check if table exists first
        if (!$this->db->table_exists($this->table)) {
            return array(
                'success' => false,
                'message' => 'Exchange rates table does not exist! Please create the table first.'
            );
        }
        
        // Validate that from and to currencies are different
        if ($from_currency_id == $to_currency_id) {
            return array(
                'success' => false,
                'message' => 'From and To currencies cannot be the same!'
            );
        }
        
        // Validate and format the effective date
        if (empty($date)) {
            return array(
                'success' => false,
                'message' => 'Effective date is required!'
            );
        }
        
        // Convert date to proper format if needed
        $effective_date = date('Y-m-d', strtotime($date));
        if ($effective_date == '1970-01-01') {
            return array(
                'success' => false,
                'message' => 'Invalid effective date format!'
            );
        }
        
        // Format expiry date if provided
        $expiry_date_formatted = null;
        if (!empty($expiry_date)) {
            $expiry_date_formatted = date('Y-m-d', strtotime($expiry_date));
            if ($expiry_date_formatted == '1970-01-01') {
                $expiry_date_formatted = null; // Invalid date, set to null
            }
        }
        
        $this->db->trans_start();
        
        try {
            // Save A -> B direction
            $existing_ab = $this->db->where('from_currency_id', $from_currency_id)
                                  ->where('to_currency_id', $to_currency_id)
                                  ->where('effective_date', $effective_date)
                                  ->where('rate_type', 'api')
                                  ->get($this->table);
            
            if ($existing_ab->num_rows() == 0) {
                $data_ab = array(
                    'from_currency_id' => $from_currency_id,
                    'to_currency_id' => $to_currency_id,
                    'exchange_rate' => $rate,
                    'rate_type' => 'api',
                    'effective_date' => $effective_date,
                    'expiry_date' => $expiry_date_formatted,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if (!$this->db->insert($this->table, $data_ab)) {
                    throw new Exception("Failed to insert A->B API exchange rate");
                }
            } else {
                // Update existing rate
                $this->db->where('from_currency_id', $from_currency_id)
                        ->where('to_currency_id', $to_currency_id)
                        ->where('effective_date', $effective_date)
                        ->where('rate_type', 'api')
                        ->update($this->table, array(
                            'exchange_rate' => $rate,
                            'expiry_date' => $expiry_date_formatted,
                            'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                            'updated_date' => date('Y-m-d H:i:s')
                        ));
            }
            
            // Save B -> A direction (inverse rate)
            $existing_ba = $this->db->where('from_currency_id', $to_currency_id)
                                  ->where('to_currency_id', $from_currency_id)
                                  ->where('effective_date', $effective_date)
                                  ->where('rate_type', 'api')
                                  ->get($this->table);
            
            if ($existing_ba->num_rows() == 0) {
                $inverse_rate = 1 / $rate; // Calculate inverse rate
                
                $data_ba = array(
                    'from_currency_id' => $to_currency_id,
                    'to_currency_id' => $from_currency_id,
                    'exchange_rate' => $inverse_rate,
                    'rate_type' => 'api',
                    'effective_date' => $effective_date,
                    'expiry_date' => $expiry_date_formatted,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if (!$this->db->insert($this->table, $data_ba)) {
                    throw new Exception("Failed to insert B->A API exchange rate");
                }
            } else {
                // Update existing inverse rate
                $inverse_rate = 1 / $rate;
                $this->db->where('from_currency_id', $to_currency_id)
                        ->where('to_currency_id', $from_currency_id)
                        ->where('effective_date', $effective_date)
                        ->where('rate_type', 'api')
                        ->update($this->table, array(
                            'exchange_rate' => $inverse_rate,
                            'expiry_date' => $expiry_date_formatted,
                            'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                            'updated_date' => date('Y-m-d H:i:s')
                        ));
            }
            
            $this->db->trans_complete();
            
            if ($this->db->trans_status() === FALSE) {
                throw new Exception("Transaction failed");
            }
            
            return array(
                'success' => true,
                'message' => 'Bidirectional API exchange rates saved successfully!'
            );
            
        } catch (Exception $e) {
            $this->db->trans_rollback();
            return array(
                'success' => false,
                'message' => 'Failed to add bidirectional API exchange rates: ' . $e->getMessage()
            );
        }
    }
    
    /**
     * Fetch all bidirectional exchange rates for all currency pairs
     */
    public function fetch_all_bidirectional_rates($date) {
        // Get all currencies
        $currencies = $this->db->get('db_currency')->result();
        
        if (empty($currencies)) {
            return array(
                'success' => false,
                'message' => 'No currencies found in database',
                'rates' => array(),
                'statistics' => array(
                    'total_currencies' => 0,
                    'success_count' => 0,
                    'error_count' => 0,
                    'skipped_count' => 0
                ),
                'errors' => array()
            );
        }
        
        $rates = array();
        $errors = array();
        $success_count = 0;
        $error_count = 0;
        $skipped_count = 0;
        
        foreach ($currencies as $from_currency) {
            foreach ($currencies as $to_currency) {
                if ($from_currency->id == $to_currency->id) {
                    $skipped_count++;
                    continue; // Skip same currency
                }
                
                $result = $this->fetch_single_rate_from_api($from_currency->id, $to_currency->id);
                
                if ($result['success']) {
                    $rates[] = array(
                        'from_currency_id' => $from_currency->id,
                        'to_currency_id' => $to_currency->id,
                        'rate' => $result['rate'],
                        'from_currency_code' => $result['from_currency_code'],
                        'to_currency_code' => $result['to_currency_code'],
                        'from_currency_name' => $result['from_currency_name'],
                        'to_currency_name' => $result['to_currency_name'],
                        'date' => $result['date']
                    );
                    $success_count++;
                } else {
                    $errors[] = "Failed to fetch rate for {$from_currency->currency_code} -> {$to_currency->currency_code}: " . $result['message'];
                    $error_count++;
                }
            }
        }
        
        return array(
            'success' => true,
            'message' => "Fetched {$success_count} exchange rates successfully",
            'rates' => $rates,
            'statistics' => array(
                'total_currencies' => count($currencies),
                'success_count' => $success_count,
                'error_count' => $error_count,
                'skipped_count' => $skipped_count
            ),
            'errors' => $errors
        );
    }
    
    /**
     * Save all bidirectional rates to database
     */
    public function save_all_bidirectional_rates($rates, $date) {
        $saved_count = 0;
        $skipped_count = 0;
        $error_count = 0;
        $errors = array();
        
        $this->db->trans_start();
        
        try {
            foreach ($rates as $rate_data) {
                // Save A -> B direction
                $existing_ab = $this->db->where('from_currency_id', $rate_data['from_currency_id'])
                                      ->where('to_currency_id', $rate_data['to_currency_id'])
                                      ->where('effective_date', $date)
                                      ->where('rate_type', 'api')
                                      ->get($this->table);
                
                $data_ab = array(
                    'from_currency_id' => $rate_data['from_currency_id'],
                    'to_currency_id' => $rate_data['to_currency_id'],
                    'exchange_rate' => $rate_data['rate'],
                    'rate_type' => 'api',
                    'effective_date' => $date,
                    'expiry_date' => null,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if ($existing_ab->num_rows() > 0) {
                    // Update existing rate
                    $this->db->where('from_currency_id', $rate_data['from_currency_id'])
                            ->where('to_currency_id', $rate_data['to_currency_id'])
                            ->where('effective_date', $date)
                            ->where('rate_type', 'api')
                            ->update($this->table, array(
                                'exchange_rate' => $rate_data['rate'],
                                'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                                'updated_date' => date('Y-m-d H:i:s')
                            ));
                } else {
                    // Insert new rate
                    if (!$this->db->insert($this->table, $data_ab)) {
                        $errors[] = "Failed to insert A->B rate for {$rate_data['from_currency_code']} -> {$rate_data['to_currency_code']}";
                        $error_count++;
                        continue;
                    }
                }
                
                // Save B -> A direction (inverse rate)
                $existing_ba = $this->db->where('from_currency_id', $rate_data['to_currency_id'])
                                      ->where('to_currency_id', $rate_data['from_currency_id'])
                                      ->where('effective_date', $date)
                                      ->where('rate_type', 'api')
                                      ->get($this->table);
                
                $inverse_rate = 1 / $rate_data['rate'];
                $data_ba = array(
                    'from_currency_id' => $rate_data['to_currency_id'],
                    'to_currency_id' => $rate_data['from_currency_id'],
                    'exchange_rate' => $inverse_rate,
                    'rate_type' => 'api',
                    'effective_date' => $date,
                    'expiry_date' => null,
                    'status' => 1,
                    'created_by' => $this->session->userdata('inv_userid') ?: 'system',
                    'created_date' => date('Y-m-d H:i:s'),
                    'system_ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                    'system_name' => $_SERVER['HTTP_USER_AGENT'] ?? ''
                );
                
                if ($existing_ba->num_rows() > 0) {
                    // Update existing inverse rate
                    $this->db->where('from_currency_id', $rate_data['to_currency_id'])
                            ->where('to_currency_id', $rate_data['from_currency_id'])
                            ->where('effective_date', $date)
                            ->where('rate_type', 'api')
                            ->update($this->table, array(
                                'exchange_rate' => $inverse_rate,
                                'updated_by' => $this->session->userdata('inv_userid') ?: 'system',
                                'updated_date' => date('Y-m-d H:i:s')
                            ));
                } else {
                    // Insert new inverse rate
                    if (!$this->db->insert($this->table, $data_ba)) {
                        $errors[] = "Failed to insert B->A rate for {$rate_data['to_currency_code']} -> {$rate_data['from_currency_code']}";
                        $error_count++;
                        continue;
                    }
                }
                
                $saved_count++;
            }
            
            $this->db->trans_complete();
            
            if ($this->db->trans_status() === FALSE) {
                throw new Exception("Transaction failed");
            }
            
            return array(
                'success' => true,
                'message' => "Saved {$saved_count} bidirectional exchange rates successfully",
                'saved_count' => $saved_count,
                'skipped_count' => $skipped_count,
                'error_count' => $error_count,
                'errors' => $errors
            );
            
        } catch (Exception $e) {
            $this->db->trans_rollback();
            return array(
                'success' => false,
                'message' => 'Failed to save bidirectional exchange rates: ' . $e->getMessage(),
                'saved_count' => $saved_count,
                'skipped_count' => $skipped_count,
                'error_count' => $error_count,
                'errors' => $errors
            );
        }
    }
    
    /**
     * Validate bidirectional rates completeness
     */
    public function validate_bidirectional_rates($date) {
        // Get all currencies
        $currencies = $this->db->get('db_currency')->result();
        
        if (empty($currencies)) {
            return array(
                'is_complete' => false,
                'total_currencies' => 0,
                'expected_pairs' => 0,
                'missing_count' => 0,
                'missing_pairs' => array()
            );
        }
        
        $total_currencies = count($currencies);
        $expected_pairs = $total_currencies * ($total_currencies - 1); // Each currency pair needs both directions
        $missing_pairs = array();
        
        foreach ($currencies as $from_currency) {
            foreach ($currencies as $to_currency) {
                if ($from_currency->id == $to_currency->id) {
                    continue; // Skip same currency
                }
                
                // Check if A->B rate exists
                $ab_exists = $this->db->where('from_currency_id', $from_currency->id)
                                    ->where('to_currency_id', $to_currency->id)
                                    ->where('effective_date <=', $date)
                                    ->where('(expiry_date IS NULL OR expiry_date >=)', $date)
                                    ->where('status', 1)
                                    ->get($this->table)
                                    ->num_rows() > 0;
                
                // Check if B->A rate exists
                $ba_exists = $this->db->where('from_currency_id', $to_currency->id)
                                    ->where('to_currency_id', $from_currency->id)
                                    ->where('effective_date <=', $date)
                                    ->where('(expiry_date IS NULL OR expiry_date >=)', $date)
                                    ->where('status', 1)
                                    ->get($this->table)
                                    ->num_rows() > 0;
                
                if (!$ab_exists) {
                    $missing_pairs[] = array(
                        'from_currency' => $from_currency->currency_code,
                        'to_currency' => $to_currency->currency_code,
                        'direction' => 'A->B'
                    );
                }
                
                if (!$ba_exists) {
                    $missing_pairs[] = array(
                        'from_currency' => $to_currency->currency_code,
                        'to_currency' => $from_currency->currency_code,
                        'direction' => 'B->A'
                    );
                }
            }
        }
        
        $missing_count = count($missing_pairs);
        $is_complete = $missing_count == 0;
        
        return array(
            'is_complete' => $is_complete,
            'total_currencies' => $total_currencies,
            'expected_pairs' => $expected_pairs,
            'missing_count' => $missing_count,
            'missing_pairs' => $missing_pairs
        );
    }
    
    /**
     * Get all available currencies
     */
    public function get_all_currencies() {
        $this->db->select('id, currency_name, currency_code, currency_symbol');
        $this->db->order_by('currency_name', 'ASC');
        $query = $this->db->get('db_currency');
        return $query->result();
    }
    
    /**
     * Get currencies that are actually used by customers, suppliers, and base currency
     */
    public function get_used_currencies() {
        $used_currencies = array();
        
        try {
            // Get base currency
            $base_currency = $this->db->where('is_base_currency', 1)->get('db_currency')->row();
            if ($base_currency) {
                $used_currencies[$base_currency->id] = $base_currency;
            }
            
            // Get currencies used by customers
            if ($this->db->table_exists('db_customers')) {
                $this->db->select('DISTINCT trading_currency_id');
                $this->db->where('trading_currency_id IS NOT NULL');
                $customer_currencies = $this->db->get('db_customers')->result();
                
                foreach ($customer_currencies as $cc) {
                    if ($cc->trading_currency_id && !isset($used_currencies[$cc->trading_currency_id])) {
                        $currency = $this->db->where('id', $cc->trading_currency_id)->get('db_currency')->row();
                        if ($currency) {
                            $used_currencies[$currency->id] = $currency;
                        }
                    }
                }
            }
            
            // Get currencies used by suppliers
            if ($this->db->table_exists('db_suppliers')) {
                $this->db->select('DISTINCT trading_currency_id');
                $this->db->where('trading_currency_id IS NOT NULL');
                $supplier_currencies = $this->db->get('db_suppliers')->result();
                
                foreach ($supplier_currencies as $sc) {
                    if ($sc->trading_currency_id && !isset($used_currencies[$sc->trading_currency_id])) {
                        $currency = $this->db->where('id', $sc->trading_currency_id)->get('db_currency')->row();
                        if ($currency) {
                            $used_currencies[$currency->id] = $currency;
                        }
                    }
                }
            }
            
            // Convert to indexed array
            return array_values($used_currencies);
            
        } catch (Exception $e) {
            log_message('error', 'Error in get_used_currencies model: ' . $e->getMessage());
            return array();
        }
    }
    
}
