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

class Purchase_requisitions_model extends CI_Model {
    
    public function __construct() {
        parent::__construct();
    }
    
    // Get all purchase requisitions with filters
    public function get_all_requisitions($filters = array()) {
        $this->db->select('pr.*, u1.username as requested_by_name, u2.username as approved_by_name, u3.username as rejected_by_name, s.store_name, w.warehouse_name, c.currency_name');
        $this->db->from('db_purchase_requisitions pr');
        $this->db->join('db_users u1', 'pr.requested_by = u1.id', 'left');
        $this->db->join('db_users u2', 'pr.approved_by = u2.id', 'left');
        $this->db->join('db_users u3', 'pr.rejected_by = u3.id', 'left');
        $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
        $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
        $this->db->join('db_currency c', 'pr.currency_id = c.id', 'left');
        
        // Apply filters
        if (isset($filters['status']) && !empty($filters['status'])) {
            $this->db->where('pr.status', $filters['status']);
        }
        
        if (isset($filters['requested_by']) && !empty($filters['requested_by'])) {
            $this->db->where('pr.requested_by', $filters['requested_by']);
        }
        
        if (isset($filters['from_date']) && !empty($filters['from_date'])) {
            $this->db->where('pr.requisition_date >=', $filters['from_date']);
        }
        
        if (isset($filters['to_date']) && !empty($filters['to_date'])) {
            $this->db->where('pr.requisition_date <=', $filters['to_date']);
        }
        
        if (isset($filters['priority']) && !empty($filters['priority'])) {
            $this->db->where('pr.priority', $filters['priority']);
        }
        
        if (isset($filters['store_id']) && !empty($filters['store_id'])) {
            $this->db->where('pr.store_id', $filters['store_id']);
        }
        
        // If not admin, filter by current user's store
        if (!is_admin()) {
            $this->db->where('pr.store_id', get_current_store_id());
        }
        
        $this->db->order_by('pr.created_date', 'DESC');
        
        return $this->db->get()->result();
    }
    
    // Get single requisition by ID
    public function get_requisition($id) {
        $this->db->select('pr.*, u1.username as requested_by_name, u2.username as approved_by_name, u3.username as rejected_by_name, s.store_name, w.warehouse_name, c.currency_name');
        $this->db->from('db_purchase_requisitions pr');
        $this->db->join('db_users u1', 'pr.requested_by = u1.id', 'left');
        $this->db->join('db_users u2', 'pr.approved_by = u2.id', 'left');
        $this->db->join('db_users u3', 'pr.rejected_by = u3.id', 'left');
        $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
        $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
        $this->db->join('db_currency c', 'pr.currency_id = c.id', 'left');
        $this->db->where('pr.id', $id);
        
        return $this->db->get()->row();
    }
    
    // Get requisition items
    public function get_requisition_items($requisition_id) {
        $this->db->select('pri.*, i.item_name, i.item_code, s.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_suppliers s', 'pri.supplier_id = s.id', 'left');
        $this->db->where('pri.requisition_id', $requisition_id);
        $this->db->order_by('pri.id', 'ASC');
        
        return $this->db->get()->result();
    }
    
    // Create new requisition
    public function create_requisition($data) {
        $this->db->trans_start();
        
        // Generate requisition code
        $data['requisition_code'] = $this->generate_requisition_code();
        
        // Set default values
        $data['created_date'] = date('Y-m-d H:i:s');
        $data['created_by'] = get_current_user_id();
        
        // Check if auto-approval should be applied
        if (isset($data['total_amount']) && is_pro_auto_approval_enabled()) {
            $auto_approve_amount = get_pro_auto_approve_amount();
            if ($data['total_amount'] <= $auto_approve_amount) {
                $data['status'] = 'Approved';
                $data['approved_by'] = get_current_user_id();
                $data['approved_date'] = date('Y-m-d H:i:s');
            }
        }
        
        $this->db->insert('db_purchase_requisitions', $data);
        $requisition_id = $this->db->insert_id();
        
        $this->db->trans_complete();
        
        if ($this->db->trans_status() === FALSE) {
            return false;
        }
        
        return $requisition_id;
    }
    
    // Update requisition
    public function update_requisition($id, $data) {
        $data['updated_date'] = date('Y-m-d H:i:s');
        $data['updated_by'] = get_current_user_id();
        
        $this->db->where('id', $id);
        return $this->db->update('db_purchase_requisitions', $data);
    }
    
    // Delete requisition
    public function delete_requisition($id) {
        $this->db->trans_start();
        
        // Delete requisition items first (due to foreign key constraints)
        $this->db->where('requisition_id', $id);
        $this->db->delete('db_purchase_requisition_items');
        
        // Delete requisition approvals
        $this->db->where('requisition_id', $id);
        $this->db->delete('db_purchase_requisition_approvals');
        
        // Delete requisition
        $this->db->where('id', $id);
        $this->db->delete('db_purchase_requisitions');
        
        $this->db->trans_complete();
        
        return $this->db->trans_status() !== FALSE;
    }
    
    // Add requisition item
    public function add_requisition_item($data) {
        $data['created_date'] = date('Y-m-d H:i:s');
        
        $this->db->insert('db_purchase_requisition_items', $data);
        $item_id = $this->db->insert_id();
        
        // Update requisition total amount
        $this->update_requisition_total($data['requisition_id']);
        
        return $item_id;
    }
    
    // Update requisition item
    public function update_requisition_item($id, $data) {
        $data['updated_date'] = date('Y-m-d H:i:s');
        
        $this->db->where('id', $id);
        $result = $this->db->update('db_purchase_requisition_items', $data);
        
        // Update requisition total amount
        if ($result && isset($data['requisition_id'])) {
            $this->update_requisition_total($data['requisition_id']);
        }
        
        return $result;
    }
    
    // Delete requisition item
    public function delete_requisition_item($id) {
        // Get requisition_id before deleting
        $this->db->select('requisition_id');
        $this->db->where('id', $id);
        $item = $this->db->get('db_purchase_requisition_items')->row();
        
        $this->db->where('id', $id);
        $result = $this->db->delete('db_purchase_requisition_items');
        
        // Update requisition total amount
        if ($result && $item) {
            $this->update_requisition_total($item->requisition_id);
        }
        
        return $result;
    }
    
    // Update requisition total amount
    public function update_requisition_total($requisition_id) {
        $this->db->select('SUM(total_price) as total_amount');
        $this->db->where('requisition_id', $requisition_id);
        $result = $this->db->get('db_purchase_requisition_items')->row();
        
        $total_amount = $result ? $result->total_amount : 0;
        
        $this->db->where('id', $requisition_id);
        return $this->db->update('db_purchase_requisitions', array('total_amount' => $total_amount));
    }
    
    // Approve requisition
    public function approve_requisition($id, $approver_id, $comments = null) {
        $data = array(
            'status' => 'Approved',
            'approved_by' => $approver_id,
            'approved_date' => date('Y-m-d H:i:s'),
            'updated_date' => date('Y-m-d H:i:s'),
            'updated_by' => $approver_id
        );
        
        if ($comments) {
            $data['notes'] = $comments;
        }
        
        $this->db->where('id', $id);
        $result = $this->db->update('db_purchase_requisitions', $data);
        
        // Update all items to approved status
        if ($result) {
            $this->db->where('requisition_id', $id);
            $this->db->update('db_purchase_requisition_items', array('status' => 'Approved'));
        }
        
        return $result;
    }
    
    // Reject requisition
    public function reject_requisition($id, $rejected_by, $rejection_reason) {
        $data = array(
            'status' => 'Rejected',
            'rejected_by' => $rejected_by,
            'rejected_date' => date('Y-m-d H:i:s'),
            'rejection_reason' => $rejection_reason,
            'updated_date' => date('Y-m-d H:i:s'),
            'updated_by' => $rejected_by
        );
        
        $this->db->where('id', $id);
        $result = $this->db->update('db_purchase_requisitions', $data);
        
        // Update all items to rejected status
        if ($result) {
            $this->db->where('requisition_id', $id);
            $this->db->update('db_purchase_requisition_items', array('status' => 'Rejected'));
        }
        
        return $result;
    }
    
    // Convert requisition to purchase
    public function convert_to_purchase($requisition_id, $purchase_id) {
        $data = array(
            'status' => 'Converted',
            'converted_to_purchase_id' => $purchase_id,
            'updated_date' => date('Y-m-d H:i:s'),
            'updated_by' => get_current_user_id()
        );
        
        $this->db->where('id', $requisition_id);
        return $this->db->update('db_purchase_requisitions', $data);
    }
    
    // Generate unique requisition code
    private function generate_requisition_code() {
        $prefix = 'PRO';
        $year = date('Y');
        $month = date('m');
        
        // Get the last requisition code for this month
        $this->db->select('requisition_code');
        $this->db->like('requisition_code', $prefix . '-' . $year . $month);
        $this->db->order_by('requisition_code', 'DESC');
        $this->db->limit(1);
        
        $result = $this->db->get('db_purchase_requisitions')->row();
        
        if ($result) {
            $last_code = $result->requisition_code;
            $last_number = (int) substr($last_code, -4);
            $new_number = $last_number + 1;
        } else {
            $new_number = 1;
        }
        
        return $prefix . '-' . $year . $month . '-' . str_pad($new_number, 4, '0', STR_PAD_LEFT);
    }
    
    // Get requisition statistics
    public function get_requisition_stats($filters = array()) {
        $this->db->select('
            COUNT(*) as total_requisitions,
            SUM(CASE WHEN status = "Draft" THEN 1 ELSE 0 END) as draft_count,
            SUM(CASE WHEN status = "Pending" THEN 1 ELSE 0 END) as pending_count,
            SUM(CASE WHEN status = "Approved" THEN 1 ELSE 0 END) as approved_count,
            SUM(CASE WHEN status = "Rejected" THEN 1 ELSE 0 END) as rejected_count,
            SUM(CASE WHEN status = "Converted" THEN 1 ELSE 0 END) as converted_count,
            SUM(total_amount) as total_amount
        ');
        $this->db->from('db_purchase_requisitions');
        
        // Apply filters
        if (isset($filters['store_id']) && !empty($filters['store_id'])) {
            $this->db->where('store_id', $filters['store_id']);
        }
        
        if (isset($filters['from_date']) && !empty($filters['from_date'])) {
            $this->db->where('requisition_date >=', $filters['from_date']);
        }
        
        if (isset($filters['to_date']) && !empty($filters['to_date'])) {
            $this->db->where('requisition_date <=', $filters['to_date']);
        }
        
        // If not admin, filter by current user's store
        if (!is_admin()) {
            $this->db->where('store_id', get_current_store_id());
        }
        
        return $this->db->get()->row();
    }
    
    // Get pending requisitions for approval
    public function get_pending_requisitions($approver_id = null) {
        $this->db->select('pr.*, u1.username as requested_by_name, s.store_name, w.warehouse_name');
        $this->db->from('db_purchase_requisitions pr');
        $this->db->join('db_users u1', 'pr.requested_by = u1.id', 'left');
        $this->db->join('db_store s', 'pr.store_id = s.id', 'left');
        $this->db->join('db_warehouse w', 'pr.warehouse_id = w.id', 'left');
        $this->db->where('pr.status', 'Pending');
        
        // If specific approver is provided, check if they have permission
        if ($approver_id) {
            // Add logic to check if user has approval permissions
            // This could be based on amount limits, department, etc.
        }
        
        $this->db->order_by('pr.created_date', 'ASC');
        
        return $this->db->get()->result();
    }
    
    // Check if user can approve requisition
    public function can_approve_requisition($requisition_id, $user_id) {
        // Get requisition details
        $requisition = $this->get_requisition($requisition_id);
        
        if (!$requisition || $requisition->status !== 'Pending') {
            return false;
        }
        
        // Check if user has approval permission
        if (!$this->permissions('purchase_requisition_approve')) {
            return false;
        }
        
        // Additional business logic can be added here:
        // - Amount limits
        // - Department-based approval
        // - Multi-level approval workflow
        
        return true;
    }
    
    // Get requisition history/audit trail
    public function get_requisition_history($requisition_id) {
        // This would typically involve a separate audit table
        // For now, we'll return basic info from the requisition itself
        $requisition = $this->get_requisition($requisition_id);
        
        $history = array();
        
        if ($requisition) {
            $history[] = array(
                'action' => 'Created',
                'user' => $requisition->requested_by_name,
                'date' => $requisition->created_date,
                'details' => 'Requisition created'
            );
            
            if ($requisition->approved_date) {
                $history[] = array(
                    'action' => 'Approved',
                    'user' => $requisition->approved_by_name,
                    'date' => $requisition->approved_date,
                    'details' => 'Requisition approved'
                );
            }
            
            if ($requisition->rejected_date) {
                $history[] = array(
                    'action' => 'Rejected',
                    'user' => $requisition->rejected_by_name,
                    'date' => $requisition->rejected_date,
                    'details' => 'Requisition rejected: ' . $requisition->rejection_reason
                );
            }
        }
        
        return $history;
    }
}
