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

class Purchase_requisitions extends MY_Controller {
    
    public function __construct() {
        parent::__construct();
        $this->load_global();
        $this->load->helper(['custom', 'security']);
        $this->load->model('Purchase_requisitions_model'); // optional if you later move queries
        $this->load->library('form_validation');
    }
    
    /* ----------------------------
     * LIST VIEW (DataTables + actions)
     * ---------------------------- */
    public function index() {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->show_access_denied_page();
        }
        
        $data            = $this->data;
        $data['page_title'] = 'Purchase Requisitions';
        
        // quick stats (safe fallbacks)
        $stats = (object)[ 'total'=>0,'total_value'=>0,'pending'=>0,'approved'=>0,'converted'=>0,'rejected'=>0 ];
        try {
            $q = $this->db->query("SELECT 
                COUNT(*) total,
                COALESCE(SUM(total_amount),0) total_value,
                SUM(CASE WHEN status='Pending' THEN 1 ELSE 0 END) pending,
                SUM(CASE WHEN status='Approved' THEN 1 ELSE 0 END) approved,
                SUM(CASE WHEN status='Converted' THEN 1 ELSE 0 END) converted,
                SUM(CASE WHEN status='Rejected' THEN 1 ELSE 0 END) rejected
            FROM db_purchase_requisitions");
            if($q && $q->row()) $stats = $q->row();
        } catch(Exception $e){ log_message('error','PR stats: '.$e->getMessage()); }
        $data['stats'] = $stats;
        
        // Check if we need to refresh the table (after conversion)
        $data['refresh_table'] = $this->session->userdata('refresh_requisitions') ? true : false;
        if($data['refresh_table']) {
            $this->session->unset_userdata('refresh_requisitions'); // Clear the flag
        }

        // load view (includes footer)
        $this->load->view('purchase-requisitions-list', $data);
    }

    /* ----------------------------
     * DataTables JSON source
     * ---------------------------- */
    public function get_requisitions_data() {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->_json(['error'=>'Access denied'], 403);
        }

        // DataTables inputs (optional)
        $search = $this->input->get('search[value]', true);
        $orderColumnIndex = (int)$this->input->get('order[0][column]');
        $orderDir = $this->input->get('order[0][dir]') === 'asc' ? 'ASC' : 'DESC';
        $start = (int)$this->input->get('start');
        $length = (int)$this->input->get('length');
        
        // Filter inputs
        $status_filter = $this->input->get('status', true);
        $priority_filter = $this->input->get('priority', true);
        $from_date = $this->input->get('from_date', true);
        $to_date = $this->input->get('to_date', true);

        // columns map (match SELECT aliases below)
        $columns = [
            'pr.id','pr.requisition_code','pr.requisition_date','requested_by_name',
            'w.warehouse_name','s.store_name','pr.status','pr.total_amount','pr.created_date','approved_by_name'
        ];
        $orderBy = $columns[$orderColumnIndex] ?? 'pr.created_date';

        // base query
        // Get store currency symbol once
        $store_currency = get_store_currency();
        $store_currency_symbol = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '$');
        
        $this->db->select("
            pr.id,
            pr.requisition_code,
            pr.requisition_date,
            pr.status,
            pr.priority,
            pr.total_amount,
            pr.created_date,
            pr.converted_to_purchase_id,
            u.username as requested_by_name,
            approver.username as approved_by_name,
            w.warehouse_name,
            s.store_name,
            '" . $store_currency_symbol . "' as symbol,
            (SELECT COUNT(*) FROM db_purchase_requisition_items i WHERE i.requisition_id = pr.id) as items_count
        ");
        $this->db->from('db_purchase_requisitions pr');
        $this->db->join('db_users u','pr.requested_by=u.id','left');
        $this->db->join('db_users approver','pr.approved_by=approver.id','left');
        $this->db->join('db_warehouse w','pr.warehouse_id=w.id','left');
        $this->db->join('db_store s','pr.store_id=s.id','left');
        // Removed currency join - using store currency symbol instead

        // Apply filters
        if(!empty($status_filter)){
            $this->db->where('pr.status', $status_filter);
        }
        
        if(!empty($priority_filter)){
            $this->db->where('pr.priority', $priority_filter);
        }
        
        if(!empty($from_date)){
            $this->db->where('pr.requisition_date >=', $from_date);
        }
        
        if(!empty($to_date)){
            $this->db->where('pr.requisition_date <=', $to_date);
        }

        if(!empty($search)){
            $this->db->group_start();
            $this->db->like('pr.requisition_code', $search);
            $this->db->or_like('u.username', $search);
            $this->db->or_like('w.warehouse_name', $search);
            $this->db->or_like('s.store_name', $search);
            $this->db->group_end();
        }

        // total before limit
        $recordsTotal = $this->db->count_all_results('', false);

        $this->db->order_by($orderBy, $orderDir);
        if($length > 0){ $this->db->limit($length, $start); }

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

        $data = [];
        foreach($rows as $r){
            $badge = $this->_status_badge($r->status);
            
            // Add "Converted" indicator if requisition has been converted to purchase
            if(!empty($r->converted_to_purchase_id)) {
                $badge .= " <span title='Converted to Purchase Order #" . $r->converted_to_purchase_id . "' class='label label-success' style='cursor:pointer'> Converted </span>";
            }
            
            // Debug logging for troubleshooting
            log_message('debug', 'Requisition ' . $r->id . ' - Status: ' . $r->status . ', Converted ID: ' . ($r->converted_to_purchase_id ?: 'NULL'));
            
            $actions = $this->_row_actions($r);
            
            // Format currency with proper placement
            // Use store currency symbol for all requisitions
            $store_currency = get_store_currency();
            $currency_symbol = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '$');
            
            $currency_placement = $this->session->userdata('currency_placement') ?: 'Left';
            
            $formatted_amount = store_number_format($r->total_amount);
            if ($currency_placement == 'Right') {
                $formatted_currency = $formatted_amount . ' ' . $currency_symbol;
            } else {
                $formatted_currency = $currency_symbol . $formatted_amount;
            }

            $data[] = [
                $r->requisition_code,
                date('d-M-Y', strtotime($r->requisition_date)),
                $r->requested_by_name ?: '-',
                $r->priority ?: 'Medium', // Priority column
                $badge,
                $formatted_currency,
                $r->items_count ?: 0,
                $actions
            ];
        }

        return $this->_json([
            'draw' => (int)$this->input->get('draw'),
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => $recordsTotal,
            'data' => $data
        ]);
    }

    /* ----------------------------
     * View requisition details
     * ---------------------------- */
    public function view($id) {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->show_access_denied_page();
        }
        
        try {
            // Get requisition details
            $this->db->select('pr.*, u.username as requested_by_name, approver.username as approved_by_name, rejector.username as rejected_by_name, w.warehouse_name, s.store_name');
            $this->db->from('db_purchase_requisitions pr');
            $this->db->join('db_users u', 'pr.requested_by = u.id', 'left');
            $this->db->join('db_users approver', 'pr.approved_by = approver.id', 'left');
            $this->db->join('db_users rejector', 'pr.rejected_by = rejector.id', 'left');
            $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
            $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
            // Removed currency join - using store currency symbol instead
            $this->db->where('pr.id', (int)$id);
            $requisition = $this->db->get()->row();
            
            if (!$requisition) {
                $this->session->set_flashdata('error', 'Requisition not found');
                return redirect('purchase_requisitions');
            }
            
            // Set currency symbol from store settings
            $store_currency = get_store_currency();
            $requisition->symbol = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '$');
            $requisition->currency_symbol = $requisition->symbol;
            $requisition->currency_name = !empty($store_currency->currency_name) ? $store_currency->currency_name : 'US Dollar';
            
            // Get requisition items
            $this->db->select('pri.*, i.item_name, i.item_code, i.unit_id, u.unit_name, sup.supplier_name');
            $this->db->from('db_purchase_requisition_items pri');
            $this->db->join('db_items i', 'pri.item_id = i.id', 'left');
            $this->db->join('db_units u', 'i.unit_id = u.id', 'left');
            $this->db->join('db_suppliers sup', 'pri.supplier_id = sup.id', 'left');
            $this->db->where('pri.requisition_id', (int)$id);
            $items = $this->db->get()->result();
            
            $data = $this->data;
            $data['page_title'] = 'View Purchase Requisition - ' . $requisition->requisition_code;
            $data['requisition'] = $requisition;
            $data['items'] = $items;
            
            $this->load->view('purchase-requisition-view', $data);
            
        } catch(Exception $e){
            log_message('error', 'View requisition error: ' . $e->getMessage());
            $this->session->set_flashdata('error', 'Error loading requisition details');
            return redirect('purchase_requisitions');
        }
    }

    /* ----------------------------
     * Edit screen
     * ---------------------------- */
    public function edit($id) {
        // Check if user has any relevant permission
        $has_edit_permission = $this->permissions('purchase_requisition_edit');
        $has_add_permission = $this->permissions('purchase_requisition_add');
        $has_view_permission = $this->permissions('purchase_requisition_view');
        
        if(!($has_edit_permission || $has_add_permission || $has_view_permission)){
            return $this->show_access_denied_page();
        }
        
        try {
            // Get requisition details
            $this->db->select('pr.*, u.username as requested_by_name, w.warehouse_name, s.store_name');
            $this->db->from('db_purchase_requisitions pr');
            $this->db->join('db_users u', 'pr.requested_by = u.id', 'left');
            $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
            $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
            // Removed currency join - using store currency symbol instead
            $this->db->where('pr.id', (int)$id);
            $requisition = $this->db->get()->row();
            
            if (!$requisition) {
                $this->session->set_flashdata('error', 'Requisition not found');
                return redirect('purchase_requisitions');
            }
            
            // Set currency symbol from store settings
            $store_currency = get_store_currency();
            $requisition->symbol = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '$');
            $requisition->currency_symbol = $requisition->symbol;
            $requisition->currency_name = !empty($store_currency->currency_name) ? $store_currency->currency_name : 'US Dollar';
            
            // Check if requisition can be edited (only Draft and Pending status)
            if (!in_array($requisition->status, ['Draft', 'Pending'])) {
                $this->session->set_flashdata('error', 'Cannot edit requisition with status: ' . $requisition->status);
                return redirect('purchase_requisitions');
            }
            
            // Check if user can edit this requisition (creator or admin)
            $user_id = (int)$this->session->userdata('inv_userid');
            $user_role = (int)$this->session->userdata('role_id');
            
            if ((int)$requisition->requested_by !== $user_id && !in_array($user_role, [1, 2])) {
                $this->session->set_flashdata('error', 'You can only edit your own requisitions');
                return redirect('purchase_requisitions');
            }
            
            // Get requisition items
            $this->db->select('pri.*, i.item_name, i.item_code, i.sales_price, i.stock, u.unit_name, sup.supplier_name');
            $this->db->from('db_purchase_requisition_items pri');
            $this->db->join('db_items i', 'pri.item_id = i.id', 'left');
            $this->db->join('db_units u', 'i.unit_id = u.id', 'left');
            $this->db->join('db_suppliers sup', 'pri.supplier_id = sup.id', 'left');
            $this->db->where('pri.requisition_id', (int)$id);
            $items = $this->db->get()->result();
            
            // Get dropdown data
            $data['items'] = $this->db->select('id,item_name,sales_price,stock')
                                      ->from('db_items')->where('status',1)
                                      ->order_by('item_name','ASC')->get()->result();
            
            $data['suppliers'] = $this->db->select('id,supplier_name')
                                          ->from('db_suppliers')->where('status',1)
                                          ->order_by('supplier_name','ASC')->get()->result();
            
            $data['warehouses'] = $this->db->select('id,warehouse_name')
                                           ->from('db_warehouse')->where('status',1)
                                           ->order_by('warehouse_name','ASC')->get()->result();
            
            $data = array_merge($this->data, $data);
            $data['page_title'] = 'Edit Purchase Requisition - ' . $requisition->requisition_code;
            $data['requisition'] = $requisition;
            $data['requisition_items'] = $items;
            
            // Currency already set from store settings above
            $data['currency_name'] = $requisition->currency_name;
            $data['currency_symbol'] = $requisition->symbol;
            $data['default_warehouse_id'] = $requisition->warehouse_id;
            
            $this->load->view('purchase-requisition-edit', $data);
            
        } catch(Exception $e){
            log_message('error', 'Edit requisition error: ' . $e->getMessage());
            $this->session->set_flashdata('error', 'Error loading requisition for editing');
            return redirect('purchase_requisitions');
        }
    }

    /* ----------------------------
     * Update requisition (from edit screen)
     * ---------------------------- */
    public function update($id) {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }

        // Check if user can edit this requisition
        $requisition = $this->db->where('id', (int)$id)->get('db_purchase_requisitions')->row();
        if (!$requisition) {
            return $this->_json(['success'=>false,'message'=>'Requisition not found'], 404);
        }
        
        $user_id = (int)$this->session->userdata('inv_userid');
        $user_role = (int)$this->session->userdata('role_id');
        
        if ((int)$requisition->requested_by !== $user_id && !in_array($user_role, [1, 2])) {
            return $this->_json(['success'=>false,'message'=>'You can only edit your own requisitions'], 403);
        }
        
        if (!in_array($requisition->status, ['Draft', 'Pending'])) {
            return $this->_json(['success'=>false,'message'=>'Cannot edit requisition with status: ' . $requisition->status], 422);
        }

        // Basic validation
        $this->form_validation->set_rules('requisition_date','Requisition Date','required');
        $this->form_validation->set_rules('warehouse_id','Warehouse','required');
        if(!$this->form_validation->run()){
            return $this->_json(['success'=>false,'message'=>strip_tags(validation_errors())], 422);
        }

        $this->db->trans_begin();
        try {
            // Calculate total amount
            $items = $this->input->post('items');
            $total_amount = 0;
            if(is_array($items) && !empty($items)){
                foreach($items as $item){
                    if(empty($item['item_id'])) continue;
                    $qty   = (float)($item['quantity']   ?? 0);
                    $price = (float)($item['unit_price'] ?? 0);
                    $total_amount += round($qty * $price, 2);
                }
            }

            $now = date('Y-m-d H:i:s');
            $update_data = [
                'requisition_date' => $this->input->post('requisition_date', true) ?: date('Y-m-d'),
                'priority'         => $this->input->post('priority', true) ?: 'Medium',
                'warehouse_id'     => (int)$this->input->post('warehouse_id'),
                'notes'            => $this->input->post('notes', true),
                'total_amount'     => $total_amount,
                'updated_date'     => $now,
                'updated_by'       => (int)$this->session->userdata('inv_userid'),
            ];

            $this->db->where('id', (int)$id)->update('db_purchase_requisitions', $update_data);

            // Delete existing items
            $this->db->where('requisition_id', (int)$id)->delete('db_purchase_requisition_items');

            // Insert updated items
            if(is_array($items) && !empty($items)){
                foreach($items as $item){
                    if(empty($item['item_id'])) continue;

                    $qty   = (float)($item['quantity']   ?? 0);
                    $price = (float)($item['unit_price'] ?? 0);
                    $line_total = round($qty * $price, 2);

                    $row = [
                        'requisition_id' => (int)$id,
                        'item_id'        => (int)$item['item_id'],
                        'description'    => $item['description'] ?? null,
                        'requested_qty'  => $qty,
                        'unit_price'     => $price,
                        'total_price'    => $line_total,
                        'supplier_id'    => !empty($item['supplier_id']) ? (int)$item['supplier_id'] : null,
                        'notes'          => $item['notes'] ?? null,
                        'status'         => $requisition->status === 'Approved' ? 'Approved' : 'Pending',
                        'created_date'   => $now
                    ];
                    $this->db->insert('db_purchase_requisition_items', $row);
                }
            }

            if($this->db->trans_status() === FALSE){ throw new Exception('DB error'); }
            $this->db->trans_commit();

            return $this->_json(['success'=>true,'message'=>'Requisition updated successfully','requisition_id'=>$id]);

        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Add screen
     * ---------------------------- */
    public function add() {
        if(!$this->permissions('purchase_requisition_add')){
            return $this->show_access_denied_page();
        }
        
        $data = $this->data;
        $data['page_title'] = 'New Purchase Requisition';
        
        // currency (from store settings)
        $store_currency = get_store_currency();
        $data['currency_name']   = !empty($store_currency->currency_name) ? $store_currency->currency_name : 'US Dollar';
        $data['currency_symbol'] = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '');
        $store_currency_id       = $store_currency->id ?? 1;

        $this->session->set_userdata('currency_id', $store_currency_id);
        $this->session->set_userdata('currency',   $data['currency_symbol']);

        // dropdowns
        try{
            $data['items'] = $this->db->select('id,item_name,sales_price,stock')
                                      ->from('db_items')->where('status',1)
                                      ->order_by('item_name','ASC')->get()->result();
        }catch(Exception $e){ $data['items']=[]; }

        try{
            $data['suppliers'] = $this->db->select('id,supplier_name')
                                          ->from('db_suppliers')->where('status',1)
                                          ->order_by('supplier_name','ASC')->get()->result();
        }catch(Exception $e){ $data['suppliers']=[]; }

        try{
            $data['warehouses'] = $this->db->select('id,warehouse_name')
                                           ->from('db_warehouse')->where('status',1)
                                           ->order_by('warehouse_name','ASC')->get()->result();
        }catch(Exception $e){ $data['warehouses']=[]; }

        $data['default_warehouse_id'] = !empty($data['warehouses']) ? $data['warehouses'][0]->id : 1;

        // load your existing add view (you shared it) - footer included there
        $this->load->view('purchase-requisition-add', $data);
    }

    /* ----------------------------
     * Save from Add screen (AJAX)
     * ---------------------------- */
    public function save() {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }

        // basic validation
        $this->form_validation->set_rules('requisition_date','Requisition Date','required');
        $this->form_validation->set_rules('warehouse_id','Warehouse','required');
        if(!$this->form_validation->run()){
            return $this->_json(['success'=>false,'message'=>strip_tags(validation_errors())], 422);
        }

        $status = $this->input->post('status', true); // 'draft' | 'pending'
        $statusMap = [
            'draft'   => 'Draft',
            'pending' => 'Pending'
        ];
        $statusDB = $statusMap[strtolower($status)] ?? 'Pending';

        $this->db->trans_begin();
        try {
            $requisition_code = 'PR-'.date('Ymd').'-'.str_pad(mt_rand(1,9999),4,'0',STR_PAD_LEFT);
            $now = date('Y-m-d H:i:s');

            // Calculate total amount first
            $items = $this->input->post('items');
            $total_amount = 0;
            if(is_array($items) && !empty($items)){
                foreach($items as $item){
                    if(empty($item['item_id'])) continue;
                    $qty   = (float)($item['quantity']   ?? 0);
                    $price = (float)($item['unit_price'] ?? 0);
                    $total_amount += round($qty * $price, 2);
                }
            }

            $req = [
                'requisition_code' => $requisition_code,
                'requisition_date' => $this->input->post('requisition_date', true) ?: date('Y-m-d'),
                'priority'         => $this->input->post('priority', true) ?: 'Medium',
                'warehouse_id'     => (int)$this->input->post('warehouse_id'),
                'notes'            => $this->input->post('notes', true),
                'requested_by'     => (int)$this->session->userdata('inv_userid'),
                'store_id'         => (int)($this->session->userdata('store_id') ?: 1),
                'currency_id'      => (int)get_pro_default_currency_id(),
                'status'           => $statusDB,
                'total_amount'     => $total_amount,
                'created_date'     => $now,
                'created_by'       => (int)$this->session->userdata('inv_userid'),
                'updated_date'     => $now,
            ];

            // Check for conditional approval using pro settings
            if($statusDB === 'Pending' && is_pro_auto_approval_enabled()) {
                $auto_approve_amount = get_pro_auto_approve_amount();
                if($total_amount <= $auto_approve_amount) {
                    $req['status'] = 'Approved';
                    $req['approved_by'] = (int)$this->session->userdata('inv_userid');
                    $req['approved_date'] = $now;
                }
            }

            $this->db->insert('db_purchase_requisitions', $req);
            $requisition_id = (int)$this->db->insert_id();
            if(!$requisition_id){ throw new Exception('Failed to create requisition'); }

            // Insert items
            if(is_array($items) && !empty($items)){
                foreach($items as $item){
                    if(empty($item['item_id'])) continue;

                    $qty   = (float)($item['quantity']   ?? 0);
                    $price = (float)($item['unit_price'] ?? 0);
                    $line_total = round($qty * $price, 2);

                    $row = [
                        'requisition_id' => $requisition_id,
                        'item_id'        => (int)$item['item_id'],
                        'description'    => $item['description'] ?? null,
                        'requested_qty'  => $qty,
                        'unit_price'     => $price,
                        'total_price'    => $line_total,
                        'supplier_id'    => !empty($item['supplier_id']) ? (int)$item['supplier_id'] : null,
                        'notes'          => $item['notes'] ?? null,
                        'status'         => $req['status'] === 'Approved' ? 'Approved' : 'Pending',
                        'created_date'   => $now
                    ];
                    $this->db->insert('db_purchase_requisition_items', $row);
                }
            }

            if($this->db->trans_status() === FALSE){ throw new Exception('DB error'); }
            $this->db->trans_commit();

            return $this->_json(['success'=>true,'message'=>'Requisition saved','requisition_id'=>$requisition_id]);

        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * View details (modal)
     * ---------------------------- */
    public function get_requisition_details($id) {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->_json(['success'=>false,'message'=>'Access denied'], 403);
        }
        
        try {
            // Debug: Check the actual status in database
            $debug_query = $this->db->select('id, status, approved_by, approved_date')->where('id', (int)$id)->get('db_purchase_requisitions')->row();
            log_message('debug', 'Requisition ' . $id . ' status: ' . ($debug_query ? $debug_query->status : 'NOT FOUND'));
            
            $this->db->select('pr.*, u.username as requested_by_name, approver.username as approved_by_name, rejector.username as rejected_by_name, w.warehouse_name, s.store_name');
            $this->db->from('db_purchase_requisitions pr');
            $this->db->join('db_users u', 'pr.requested_by = u.id', 'left');
            $this->db->join('db_users approver', 'pr.approved_by = approver.id', 'left');
            $this->db->join('db_users rejector', 'pr.rejected_by = rejector.id', 'left');
            $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
            $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
            // Removed currency join - using store currency symbol instead
            $this->db->where('pr.id', (int)$id);
            $requisition = $this->db->get()->row();
            
            if (!$requisition) return $this->_json(['success'=>false,'message'=>'Requisition not found'], 404);
            
            // Set currency symbol from store settings
            $store_currency = get_store_currency();
            $requisition->symbol = !empty($store_currency->symbol) ? $store_currency->symbol : ($store_currency->currency_symbol ?? '$');
            $requisition->currency_symbol = $requisition->symbol;
            $requisition->currency_name = !empty($store_currency->currency_name) ? $store_currency->currency_name : 'US Dollar';
            
            $this->db->select('pri.*, i.item_name, i.item_code, i.unit_id, u.unit_name, sup.supplier_name');
            $this->db->from('db_purchase_requisition_items pri');
            $this->db->join('db_items i', 'pri.item_id = i.id', 'left');
            $this->db->join('db_units u', 'i.unit_id = u.id', 'left');
            $this->db->join('db_suppliers sup', 'pri.supplier_id = sup.id', 'left');
            $this->db->where('pri.requisition_id', (int)$id);
            $items = $this->db->get()->result();
            
            $requisition->items = $items;
            
            // Currency already set above from store settings
            return $this->_json(['success'=>true,'data'=>$requisition]);

        } catch(Exception $e){
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Approve
     * ---------------------------- */
    public function approve($id) {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }
        
        // Check basic permission first
        if(!$this->permissions('purchase_requisition_approve')){
            return $this->_json(['success'=>false,'message'=>'No permission'], 403);
        }

        $this->db->trans_begin();
        try {
            $r = $this->db->where(['id'=>(int)$id,'status'=>'Pending'])
                          ->get('db_purchase_requisitions')->row();
            if(!$r) return $this->_json(['success'=>false,'message'=>'Not found or not pending'], 404);

            // Check amount-based approval limits (optional - can be bypassed)
            $user_id = (int)$this->session->userdata('inv_userid');
            $total_amount = (float)$r->total_amount;
            
            // Prevent self-approval (users cannot approve their own requisitions)
            if((int)$r->requested_by === $user_id){
                $user_role = (int)$this->session->userdata('role_id');
                // Allow admin/superadmin to approve their own requisitions
                if(!in_array($user_role, [1, 2])) { // 1=superadmin, 2=admin
                    return $this->_json(['success'=>false,'message'=>'You cannot approve your own requisition'], 403);
                }
            }
            
            // Check if amount-based approval is enabled in pro settings
            $pro_approval_workflow_enabled = get_pro_setting('pro_approval_workflow_enabled', 1);
            
            if($pro_approval_workflow_enabled) {
                // Check if user can approve this specific amount
                if(!can_user_approve_amount($user_id, $total_amount)){
                    // Check if user is admin/superadmin (they can approve any amount)
                    $user_role = (int)$this->session->userdata('role_id');
                    if(!in_array($user_role, [1, 2])) { // 1=superadmin, 2=admin
                        return $this->_json(['success'=>false,'message'=>'You do not have permission to approve requisitions of this amount ('.$total_amount.')'], 403);
                    }
                }
            }
            // If pro_approval_workflow_enabled is 0, anyone with basic permission can approve any amount

            $now = date('Y-m-d H:i:s');
            $update_result = $this->db->where('id',(int)$id)->update('db_purchase_requisitions',[
                'status'=>'Approved',
                'approved_by'=>$user_id,
                'approved_date'=>$now,
                'updated_date'=>$now
            ]);

            // Update all items status to Approved as well
            $this->db->where('requisition_id', (int)$id)->update('db_purchase_requisition_items', [
                'status' => 'Approved'
            ]);

            if(!$update_result) throw new Exception('Failed to update requisition status');
            if($this->db->trans_status()===FALSE) throw new Exception('DB error');
            $this->db->trans_commit();
            return $this->_json(['success'=>true,'message'=>'Approved']);
        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Reject (with reason)
     * ---------------------------- */
    public function reject($id) {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }
        if(!$this->permissions('purchase_requisition_approve')){
            return $this->_json(['success'=>false,'message'=>'No permission'], 403);
        }

        $reason = trim((string)$this->input->post('reason', true));
        if($reason===''){ return $this->_json(['success'=>false,'message'=>'Rejection reason required'], 422); }

        $this->db->trans_begin();
        try {
            $r = $this->db->where(['id'=>(int)$id,'status'=>'Pending'])
                          ->get('db_purchase_requisitions')->row();
            if(!$r) return $this->_json(['success'=>false,'message'=>'Not found or not pending'], 404);

            // Check amount-based approval limits (same as approve function)
            $user_id = (int)$this->session->userdata('inv_userid');
            $total_amount = (float)$r->total_amount;
            
            // Prevent self-rejection (users cannot reject their own requisitions)
            if((int)$r->requested_by === $user_id){
                $user_role = (int)$this->session->userdata('role_id');
                // Allow admin/superadmin to reject their own requisitions
                if(!in_array($user_role, [1, 2])) { // 1=superadmin, 2=admin
                    return $this->_json(['success'=>false,'message'=>'You cannot reject your own requisition'], 403);
                }
            }
            
            // Check if amount-based approval is enabled in pro settings
            $pro_approval_workflow_enabled = get_pro_setting('pro_approval_workflow_enabled', 1);
            
            if($pro_approval_workflow_enabled) {
                // Check if user can approve/reject this specific amount
                if(!can_user_approve_amount($user_id, $total_amount)){
                    // Check if user is admin/superadmin (they can reject any amount)
                    $user_role = (int)$this->session->userdata('role_id');
                    if(!in_array($user_role, [1, 2])) { // 1=superadmin, 2=admin
                        return $this->_json(['success'=>false,'message'=>'You do not have permission to reject requisitions of this amount ('.$total_amount.')'], 403);
                    }
                }
            }
            // If pro_approval_workflow_enabled is 0, anyone with basic permission can reject any amount

            $now = date('Y-m-d H:i:s');
            $this->db->where('id',(int)$id)->update('db_purchase_requisitions',[
                'status'=>'Rejected',
                'rejected_by'=>$this->session->userdata('inv_userid'),
                'rejected_date'=>$now,
                'rejection_reason'=>$reason,
                'updated_date'=>$now
            ]);

            // Update all items status to Rejected as well
            $this->db->where('requisition_id', (int)$id)->update('db_purchase_requisition_items', [
                'status' => 'Rejected'
            ]);

            if($this->db->trans_status()===FALSE) throw new Exception('DB error');
            $this->db->trans_commit();
            return $this->_json(['success'=>true,'message'=>'Rejected']);
        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Unapprove (revert approved status to pending)
     * ---------------------------- */
    public function unapprove($id) {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }
        if(!$this->permissions('purchase_requisition_approve')){
            return $this->_json(['success'=>false,'message'=>'No permission'], 403);
        }

        $this->db->trans_begin();
        try {
            $r = $this->db->where(['id'=>(int)$id,'status'=>'Approved'])
                          ->get('db_purchase_requisitions')->row();
            if(!$r) return $this->_json(['success'=>false,'message'=>'Not found or not approved'], 404);

            // Check if already converted to purchase
            if(!empty($r->converted_to_purchase_id)){
                return $this->_json(['success'=>false,'message'=>'Cannot unapprove: Already converted to purchase order'], 422);
            }

            // Check amount-based approval limits for unapprove (optional - can be bypassed)
            $user_id = (int)$this->session->userdata('inv_userid');
            $user_role = (int)$this->session->userdata('role_id');
            $total_amount = (float)$r->total_amount;
            
            // Allow the person who approved to unapprove their own approval
            // Prevent the person who created the requisition from unapproving (unless admin)
            if((int)$r->requested_by === $user_id){
                // Allow admin/superadmin to unapprove their own requisitions
                if(!in_array($user_role, [1, 2])) { // 1=superadmin, 2=admin
                    return $this->_json(['success'=>false,'message'=>'You cannot unapprove your own requisition'], 403);
                }
            }
            
            // Additional check: If the current user is the one who approved, allow them to unapprove
            // This overrides the above restriction for the approver
            if((int)$r->approved_by === $user_id){
                // The person who approved can always unapprove their own approval
                // Skip amount-based approval limits for the original approver
                $now = date('Y-m-d H:i:s');
                $this->db->where('id',(int)$id)->update('db_purchase_requisitions',[
                    'status'=>'Pending',
                    'approved_by'=>null,
                    'approved_date'=>null,
                    'updated_date'=>$now
                ]);

                // Update all items status to Pending as well
                $this->db->where('requisition_id', (int)$id)->update('db_purchase_requisition_items', [
                    'status' => 'Pending'
                ]);

                if($this->db->trans_status()===FALSE) throw new Exception('DB error');
                $this->db->trans_commit();
                return $this->_json(['success'=>true,'message'=>'Unapproved successfully']);
            }
            
            // For other users (not the original approver), check amount-based approval limits
            $pro_approval_workflow_enabled = get_pro_setting('pro_approval_workflow_enabled', 1);
            
            if($pro_approval_workflow_enabled) {
                // Check if user can approve this amount or is admin/superadmin
                if(!can_user_approve_amount($user_id, $total_amount) && !in_array($user_role, [1, 2])){
                    return $this->_json(['success'=>false,'message'=>'You do not have permission to unapprove requisitions of this amount ('.$total_amount.')'], 403);
                }
            }
            // If pro_approval_workflow_enabled is 0, anyone with basic permission can unapprove any amount

            $now = date('Y-m-d H:i:s');
            $this->db->where('id',(int)$id)->update('db_purchase_requisitions',[
                'status'=>'Pending',
                'approved_by'=>null,
                'approved_date'=>null,
                'updated_date'=>$now
            ]);

            // Update all items status to Pending as well
            $this->db->where('requisition_id', (int)$id)->update('db_purchase_requisition_items', [
                'status' => 'Pending'
            ]);

            if($this->db->trans_status()===FALSE) throw new Exception('DB error');
            $this->db->trans_commit();
            return $this->_json(['success'=>true,'message'=>'Unapproved successfully']);
        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Delete
     * ---------------------------- */
    public function delete($id) {
        if(!$this->session->userdata('logged_in')){
            return $this->_json(['success'=>false,'message'=>'Please login first'], 401);
        }
        if(!$this->permissions('purchase_requisition_delete')){
            return $this->_json(['success'=>false,'message'=>'No permission'], 403);
        }

        $this->db->trans_begin();
        try {
            $req = $this->db->where('id',(int)$id)->get('db_purchase_requisitions')->row();
            if(!$req) return $this->_json(['success'=>false,'message'=>'Requisition not found'], 404);

            $can_delete = false;
            if(in_array($req->status, ['Draft','Pending','Rejected'])) $can_delete = true;
            elseif($req->status==='Converted' && empty($req->converted_to_purchase_id)) $can_delete = true;
            elseif($req->status==='Approved'){
                $role_id = (int)$this->session->userdata('role_id');
                if(in_array($role_id,[1,2])) $can_delete = true;
            }

            if(!$can_delete){
                $msg = ($req->status==='Converted' && !empty($req->converted_to_purchase_id))
                    ? 'Cannot delete; already converted to purchase #'.$req->converted_to_purchase_id
                    : 'Cannot delete '.$req->status.' requisitions';
                return $this->_json(['success'=>false,'message'=>$msg], 422);
            }

            $this->db->where('requisition_id',(int)$id)->delete('db_purchase_requisition_items');
            $this->db->where('requisition_id',(int)$id)->delete('db_purchase_requisition_approvals');
            $this->db->where('id',(int)$id)->delete('db_purchase_requisitions');

            if($this->db->trans_status()===FALSE) throw new Exception('DB error');
            $this->db->trans_commit();
            return $this->_json(['success'=>true,'message'=>'Deleted']);
        } catch(Exception $e){
            $this->db->trans_rollback();
            return $this->_json(['success'=>false,'message'=>$e->getMessage()], 500);
        }
    }

    /* ----------------------------
     * Convert to purchase (redirect to purchase/add prefilled)
     * ---------------------------- */
    public function convert_to_purchase($id) {
        if(!$this->permissions('purchase_requisition_convert')){
            return $this->show_access_denied_page();
        }
        if(!$this->session->userdata('logged_in')){
            return redirect('login');
        }
        
        try {
            $this->db->select('pr.*, u.username as requested_by_name, w.warehouse_name, s.store_name, c.currency_name, c.symbol, c.currency_symbol');
            $this->db->from('db_purchase_requisitions pr');
            $this->db->join('db_users u','pr.requested_by=u.id','left');
            $this->db->join('db_warehouse w','pr.warehouse_id=w.id','left');
            $this->db->join('db_store s','pr.store_id=s.id','left');
            $this->db->join('db_currency c','pr.currency_id=c.id','left');
            $this->db->where('pr.id',(int)$id);
            $requisition = $this->db->get()->row();
            
            if(!$requisition){
                $this->session->set_flashdata('error','Requisition not found');
                return redirect('purchase_requisitions');
            }
            if(strtolower($requisition->status)!=='approved'){
                $this->session->set_flashdata('error','Only approved requisitions can be converted');
                return redirect('purchase_requisitions');
            }

            $items = $this->db->select('pri.*, i.item_name, s.supplier_name')
                      ->from('db_purchase_requisition_items pri')
                      ->join('db_items i','pri.item_id=i.id','left')
                      ->join('db_suppliers s','pri.supplier_id=s.id','left')
                      ->where('pri.requisition_id',(int)$id)->get()->result();

            if(empty($items)){
                $this->session->set_flashdata('error','No items found in this requisition');
                return redirect('purchase_requisitions');
            }

            $this->session->set_userdata('converted_requisition', [
                'requisition_id'   => (int)$id,
                'requisition_code' => $requisition->requisition_code,
                'warehouse_id'     => $requisition->warehouse_id,
                'warehouse_name'   => $requisition->warehouse_name,
                'store_id'         => $requisition->store_id,
                'store_name'       => $requisition->store_name,
                'currency_id'      => $requisition->currency_id,
                'currency_name'    => $requisition->currency_name,
                'currency_symbol'  => $requisition->currency_symbol,
                'notes'            => 'Converted from Requisition: '.$requisition->requisition_code,
                'items'            => $items
            ]);

            // Set flag to refresh requisition list when user returns
            $this->session->set_userdata('refresh_requisitions', true);
            
            // Log the conversion attempt for debugging
            log_message('info', 'Converting requisition ' . $id . ' to purchase. Session data set.');

            $this->session->set_flashdata('success','Requisition converted. Review the purchase details.');
            return redirect('purchase/add');

        } catch(Exception $e){
            log_message('error','convert_to_purchase: '.$e->getMessage());
            $this->session->set_flashdata('error','Error converting requisition');
            return redirect('purchase_requisitions');
        }
    }

    /* ----------------------------
     * Helpers
     * ---------------------------- */
    private function _json($arr, $code = 200){
        $this->output->set_status_header($code)
            ->set_content_type('application/json','utf-8')
            ->set_output(json_encode($arr, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
            return;
        }
        
    private function _status_badge($status){
        $s = strtolower($status);
        $cls = 'default';
        if($s==='pending')  $cls='warning';
        if($s==='approved') $cls='success';
        if($s==='rejected') $cls='danger';
        if($s==='converted') $cls='primary';
        if($s==='draft')    $cls='info';
        return '<span class="label label-'.$cls.'">'.htmlspecialchars($status).'</span>';
    }

    private function _row_actions($r){
        $id = (int)$r->id;
        $btns = [];

        // View button (always available)
        $btns[] = '<button class="btn btn-xs btn-info btn-view" data-id="'.$id.'" title="View Details"><i class="fa fa-eye"></i></button>';

        // Edit button for Draft and Pending status
        $user_id = (int)$this->session->userdata('inv_userid');
        $user_role = (int)$this->session->userdata('role_id');
        $requester_id = (int)$r->requested_by;
        
        // Check if requisition is editable (Draft or Pending)
        $is_editable_status = in_array(strtolower($r->status), ['draft', 'pending']);
        
        // Check if user is the creator or admin
        $is_creator = ($requester_id === $user_id);
        $is_admin = in_array($user_role, [1, 2]);
        
        // Check permissions
        $has_permission = $this->permissions('purchase_requisition_add') || 
                         $this->permissions('purchase_requisition_edit') || 
                         $this->permissions('purchase_requisition_view');
        
        // Show edit button if user can edit and requisition is editable
        if($is_editable_status && $has_permission){
            $btns[] = '<button class="btn btn-xs btn-warning btn-edit" data-id="'.$id.'" title="Edit Requisition"><i class="fa fa-edit"></i></button>';
        }


           // Approve / Reject for Pending
           if(strtolower($r->status)==='pending' && $this->permissions('purchase_requisition_approve')){
               $user_id = (int)$this->session->userdata('inv_userid');
               $user_role = (int)$this->session->userdata('role_id');
               $total_amount = (float)$r->total_amount;
               
               // Check if this is self-created requisition
               $is_self_created = (int)$r->requested_by === $user_id;
               $can_self_approve = in_array($user_role, [1, 2]); // Only admin/superadmin can approve their own
               
               if($is_self_created && !$can_self_approve) {
                   // Hide approve/reject buttons for self-created requisitions (except admin/superadmin)
                   $btns[] = '<button class="btn btn-xs btn-default" disabled title="Cannot approve your own requisition"><i class="fa fa-user"></i></button>';
               } else {
                   // Check if amount-based approval workflow is enabled
                   $pro_approval_workflow_enabled = get_pro_setting('pro_approval_workflow_enabled', 1);
                   
                   if($pro_approval_workflow_enabled) {
                       // Check if user can approve/reject this amount or is admin/superadmin
                       $can_approve_reject = can_user_approve_amount($user_id, $total_amount) || in_array($user_role, [1, 2]);
                       
                       if($can_approve_reject) {
                           $btns[] = '<button class="btn btn-xs btn-success btn-approve" data-id="'.$id.'" title="Approve"><i class="fa fa-check"></i></button>';
                           $btns[] = '<button class="btn btn-xs btn-danger btn-reject" data-id="'.$id.'" title="Reject"><i class="fa fa-times"></i></button>';
                       } else {
                           $btns[] = '<button class="btn btn-xs btn-default" disabled title="Amount ('.$total_amount.') exceeds your approval limit"><i class="fa fa-lock"></i></button>';
                       }
                   } else {
                       // Amount-based approval disabled - anyone with basic permission can approve/reject
                       $btns[] = '<button class="btn btn-xs btn-success btn-approve" data-id="'.$id.'" title="Approve"><i class="fa fa-check"></i></button>';
                       $btns[] = '<button class="btn btn-xs btn-danger btn-reject" data-id="'.$id.'" title="Reject"><i class="fa fa-times"></i></button>';
                   }
               }
           }

        // Unapprove for Approved (only if not converted)
        if(strtolower($r->status)==='approved' && $this->permissions('purchase_requisition_approve') && empty($r->converted_to_purchase_id)){
            $user_id = (int)$this->session->userdata('inv_userid');
            $user_role = (int)$this->session->userdata('role_id');
            $total_amount = (float)$r->total_amount;
            
            // Check if this is self-created requisition
            $is_self_created = (int)$r->requested_by === $user_id;
            $can_self_unapprove = in_array($user_role, [1, 2]); // Only admin/superadmin can unapprove their own
            
            if($is_self_created && !$can_self_unapprove) {
                // Hide unapprove button for self-created requisitions (except admin/superadmin)
                // No button shown for self-created requisitions
            } else {
                // Check if amount-based approval workflow is enabled
                $pro_approval_workflow_enabled = get_pro_setting('pro_approval_workflow_enabled', 1);
                
                if($pro_approval_workflow_enabled) {
                    // Check if user can approve this amount or is admin/superadmin
                    $can_unapprove = can_user_approve_amount($user_id, $total_amount) || in_array($user_role, [1, 2]);
                    
                    if($can_unapprove) {
                        $btns[] = '<button class="btn btn-xs btn-warning btn-unapprove" data-id="'.$id.'" title="Unapprove"><i class="fa fa-undo"></i></button>';
                    }
                } else {
                    // Amount-based approval disabled - anyone with basic permission can unapprove
                    $btns[] = '<button class="btn btn-xs btn-warning btn-unapprove" data-id="'.$id.'" title="Unapprove"><i class="fa fa-undo"></i></button>';
                }
            }
        }

        // Convert for Approved (ONLY if not already converted)
        // Check: status is approved AND no purchase ID exists yet
        if(strtolower($r->status)==='approved' && $this->permissions('purchase_requisition_convert') && empty($r->converted_to_purchase_id)){
            $btns[] = '<button class="btn btn-xs btn-primary btn-convert" data-id="'.$id.'" title="Convert to Purchase"><i class="fa fa-shopping-cart"></i></button>';
        }

        // View Purchase for Converted requisitions (when purchase ID exists)
        if(!empty($r->converted_to_purchase_id)){
            $btns[] = '<button class="btn btn-xs btn-success btn-view-purchase" data-id="'.$r->converted_to_purchase_id.'" title="View Purchase Order"><i class="fa fa-file-text"></i></button>';
        }

        // Delete permission (with proper status checks)
        if($this->permissions('purchase_requisition_delete')){
            $btns[] = '<button class="btn btn-xs btn-warning btn-delete" data-id="'.$id.'" title="Delete"><i class="fa fa-trash"></i></button>';
        }

        return implode(' ', $btns);
    }

    // Update requisition with actual purchase ID after purchase is saved
    public function update_converted_purchase_id($requisition_id, $purchase_id) {
        try {
            $this->db->where('id', (int)$requisition_id)
                     ->update('db_purchase_requisitions', [
                         'converted_to_purchase_id' => (int)$purchase_id,
                         'updated_date' => date('Y-m-d H:i:s')
                     ]);
            return true;
        } catch(Exception $e) {
            log_message('error', 'Failed to update requisition purchase ID: ' . $e->getMessage());
            return false;
        }
    }
    
    // Helper method to create approval user entry
    public function create_approval_user($user_id, $min_amount = 0, $max_amount = 0) {
        if(!$this->permissions('pro_settings_view')){
            return $this->_json(['error'=>'Access denied'], 403);
        }
        
        try {
            // Check if table exists
            if(!$this->db->table_exists('db_pro_approval_users')) {
                return $this->_json(['success' => false, 'message' => 'db_pro_approval_users table does not exist']);
            }
            
            // Check if user already exists
            $existing = $this->db->get_where('db_pro_approval_users', array('user_id' => $user_id))->row();
            if ($existing) {
                return $this->_json(['success' => false, 'message' => 'User already has approval limits']);
            }
            
            // Insert new approval user
            $data = array(
                'user_id' => $user_id,
                'min_amount' => $min_amount,
                'max_amount' => $max_amount,
                'is_active' => 1,
                'created_by' => get_current_user_id()
            );
            
            $result = $this->db->insert('db_pro_approval_users', $data);
            
            if ($result) {
                return $this->_json(['success' => true, 'message' => 'Approval user created successfully']);
            } else {
                return $this->_json(['success' => false, 'message' => 'Error creating approval user']);
            }
            
        } catch(Exception $e) {
            return $this->_json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
    
    // Helper function to format numbers in readable format (12K, 12M, 12B, 12T, etc.)
    private function format_amount_readable($amount) {
        $amount = (float)$amount;
        
        if ($amount >= 1000000000000) {
            return round($amount / 1000000000000, 1) . 'T';
        } elseif ($amount >= 1000000000) {
            return round($amount / 1000000000, 1) . 'B';
        } elseif ($amount >= 1000000) {
            return round($amount / 1000000, 1) . 'M';
        } elseif ($amount >= 1000) {
            return round($amount / 1000, 1) . 'K';
        } else {
            return number_format($amount, 0);
        }
    }
    
    // Fixed version of can_user_approve_amount function
    public function can_user_approve_amount_fixed($user_id, $amount) {
        try {
            // Use a more explicit query construction
            $sql = "SELECT au.* FROM db_pro_approval_users au 
                   WHERE au.user_id = ? 
                   AND au.is_active = 1 
                   AND au.min_amount <= ? 
                   AND (au.max_amount >= ? OR au.max_amount = 0)";
            
            $query = $this->db->query($sql, array($user_id, $amount, $amount));
            return $query->num_rows() > 0;
            
        } catch(Exception $e) {
            log_message('error', 'Error in can_user_approve_amount_fixed: ' . $e->getMessage());
            return false;
        }
    }
    
    // Test method to debug the exact SQL query issue
    public function test_approval_query($user_id = null, $amount = 1218) {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->_json(['error'=>'Access denied'], 403);
        }
        
        try {
            $user_id = $user_id ?: (int)$this->session->userdata('inv_userid');
            
            // Test the original query
            $this->db->select('au.*');
            $this->db->from('db_pro_approval_users au');
            $this->db->where('au.user_id', $user_id);
            $this->db->where('au.is_active', 1);
            $this->db->where('au.min_amount <=', $amount);
            $this->db->where('(au.max_amount >= ' . $amount . ' OR au.max_amount = 0)');
            $original_query = $this->db->get();
            $original_sql = $this->db->last_query();
            
            // Test alternative query construction
            $this->db->select('au.*');
            $this->db->from('db_pro_approval_users au');
            $this->db->where('au.user_id', $user_id);
            $this->db->where('au.is_active', 1);
            $this->db->where('au.min_amount <=', $amount);
            $this->db->where("(au.max_amount >= $amount OR au.max_amount = 0)");
            $alt_query = $this->db->get();
            $alt_sql = $this->db->last_query();
            
            // Test manual query
            $manual_sql = "SELECT au.* FROM db_pro_approval_users au 
                          WHERE au.user_id = $user_id 
                          AND au.is_active = 1 
                          AND au.min_amount <= $amount 
                          AND (au.max_amount >= $amount OR au.max_amount = 0)";
            $manual_query = $this->db->query($manual_sql);
            
            // Get user's approval limits
            $this->db->select('au.*');
            $this->db->from('db_pro_approval_users au');
            $this->db->where('au.user_id', $user_id);
            $user_limits = $this->db->get()->result();
            
            $test_results = [
                'user_id' => $user_id,
                'test_amount' => $amount,
                'user_limits' => $user_limits,
                'original_query' => [
                    'sql' => $original_sql,
                    'num_rows' => $original_query->num_rows(),
                    'result' => $original_query->result()
                ],
                'alternative_query' => [
                    'sql' => $alt_sql,
                    'num_rows' => $alt_query->num_rows(),
                    'result' => $alt_query->result()
                ],
                'manual_query' => [
                    'sql' => $manual_sql,
                    'num_rows' => $manual_query->num_rows(),
                    'result' => $manual_query->result()
                ],
                'can_user_approve_amount_result' => can_user_approve_amount($user_id, $amount),
                'can_user_approve_amount_fixed_result' => $this->can_user_approve_amount_fixed($user_id, $amount)
            ];
            
            return $this->_json(['success' => true, 'test_results' => $test_results]);
            
        } catch(Exception $e) {
            return $this->_json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
    
    // Debug method to check approval permissions and limits
    public function debug_approval_permissions($user_id = null) {
        if(!$this->permissions('purchase_requisition_view')){
            return $this->_json(['error'=>'Access denied'], 403);
        }
        
        try {
            $user_id = $user_id ?: (int)$this->session->userdata('inv_userid');
            $user_role = (int)$this->session->userdata('role_id');
            
            // Check basic permission
            $has_basic_permission = $this->permissions('purchase_requisition_approve');
            
            // Check approval users table
            $this->db->select('au.*, u.username');
            $this->db->from('db_pro_approval_users au');
            $this->db->join('db_users u', 'au.user_id = u.id', 'left');
            $this->db->where('au.user_id', $user_id);
            $approval_limits = $this->db->get()->result();
            
            // Check all approval users
            $this->db->select('au.*, u.username');
            $this->db->from('db_pro_approval_users au');
            $this->db->join('db_users u', 'au.user_id = u.id', 'left');
            $all_approval_users = $this->db->get()->result();
            
            // Test amount approval with detailed debugging
            $test_amounts = [100, 1000, 1218, 1500, 5000, 10000];
            $amount_tests = [];
            $detailed_tests = [];
            
            foreach($test_amounts as $amount) {
                $amount_tests[$amount] = can_user_approve_amount($user_id, $amount);
                
                // Detailed debugging for the specific amount
                $this->db->select('au.*');
                $this->db->from('db_pro_approval_users au');
                $this->db->where('au.user_id', $user_id);
                $this->db->where('au.is_active', 1);
                $this->db->where('au.min_amount <=', $amount);
                $this->db->where('(au.max_amount >= ' . $amount . ' OR au.max_amount = 0)');
                $query_result = $this->db->get();
                
                $detailed_tests[$amount] = [
                    'can_approve' => $amount_tests[$amount],
                    'sql_query' => $this->db->last_query(),
                    'num_rows' => $query_result->num_rows(),
                    'result_data' => $query_result->result()
                ];
            }
            
            $debug_info = [
                'user_id' => $user_id,
                'user_role' => $user_role,
                'is_admin_superadmin' => in_array($user_role, [1, 2]),
                'has_basic_permission' => $has_basic_permission,
                'approval_limits' => $approval_limits,
                'all_approval_users' => $all_approval_users,
                'amount_tests' => $amount_tests,
                'detailed_tests' => $detailed_tests,
                'table_exists' => $this->db->table_exists('db_pro_approval_users'),
                'pro_approval_workflow_enabled' => get_pro_setting('pro_approval_workflow_enabled', 1),
                'pro_auto_approve_amount' => get_pro_setting('pro_auto_approve_amount', 0),
                'pro_require_approval_amount' => get_pro_setting('pro_require_approval_amount', 0)
            ];
            
            return $this->_json(['success' => true, 'debug_info' => $debug_info]);
            
        } catch(Exception $e) {
            return $this->_json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
}
