<?php
/**
 * Requisition Controller
 */
class RequisitionController extends Controller {
    
    /**
     * Constructor
     */
    public function __construct() {
        // Only call parent constructor if it exists
        if (method_exists(get_parent_class($this), '__construct')) {
            parent::__construct();
        }
    }
    
    /**
     * Display all requisitions
     */
    public function index() {
        // Check permission using can() instead of requirePermission()
        if (!Auth::can('requisition.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view requisitions']);
            return;
        }
        
        $requisitions = Database::fetchAll(
            "SELECT r.*, 
                    u.full_name as requester_name,
                    c.command_name,
                    COUNT(ri.id) as item_count
             FROM requisitions r
             LEFT JOIN users u ON r.requesting_officer_id = u.id
             LEFT JOIN commands c ON r.requesting_command_id = c.id
             LEFT JOIN requisition_items ri ON r.id = ri.requisition_id
             GROUP BY r.id
             ORDER BY r.created_at DESC"
        );
        
        if ($requisitions === false) $requisitions = [];
        
        // Calculate statistics
        $statistics = [
            'total' => count($requisitions),
            'pending' => 0,
            'approved' => 0,
            'rejected' => 0,
            'issued' => 0,
            'partially_issued' => 0,
            'completed' => 0
        ];
        
        foreach ($requisitions as $req) {
            $status = $req['status'] ?? '';
            switch ($status) {
                case 'Pending':
                    $statistics['pending']++;
                    break;
                case 'Approved':
                    $statistics['approved']++;
                    break;
                case 'Rejected':
                    $statistics['rejected']++;
                    break;
                case 'Issued':
                    $statistics['issued']++;
                    break;
                case 'Partially Issued':
                    $statistics['partially_issued']++;
                    break;
                case 'Completed':
                    $statistics['completed']++;
                    break;
            }
        }
        
        // Load the view
        $this->view('requisition/index', [
            'requisitions' => $requisitions,
            'statistics' => $statistics
        ]);
    }
    
    /**
     * Show create requisition form
     */
    public function create() {
        // Check permission
        if (!Auth::can('requisition.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to create requisitions']);
            return;
        }
        
        $commands = Database::fetchAll("SELECT * FROM commands ORDER BY command_name");
        $weaponTypes = Database::fetchAll("SELECT * FROM weapon_types WHERE is_active = 1 ORDER BY type_name");
        $ammoTypes = Database::fetchAll("SELECT * FROM ammunition_types WHERE is_active = 1 ORDER BY ammo_type");
        $calibres = Database::fetchAll("SELECT * FROM ammunition_calibres WHERE is_active = 1 ORDER BY calibre");
        
        if ($commands === false) $commands = [];
        if ($weaponTypes === false) $weaponTypes = [];
        if ($ammoTypes === false) $ammoTypes = [];
        if ($calibres === false) $calibres = [];
        
        $this->view('requisition/create', [
            'commands' => $commands,
            'weaponTypes' => $weaponTypes,
            'ammoTypes' => $ammoTypes,
            'calibres' => $calibres
        ]);
    }
    
    /**
     * Store new requisition
     */
    public function store() {
        // Check permission
        if (!Auth::can('requisition.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to create requisitions']);
            return;
        }
        
        // Validate CSRF token
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect('requisition/create', ['error' => 'Invalid security token']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['requisition_date'])) {
            $errors['requisition_date'] = 'Requisition date is required';
        }
        
        if (empty($_POST['priority_level'])) {
            $errors['priority_level'] = 'Priority level is required';
        }
        
        if (empty($_POST['requisition_type'])) {
            $errors['requisition_type'] = 'Requisition type is required';
        }
        
        if (empty($_POST['requesting_officer_name'])) {
            $errors['requesting_officer_name'] = 'Officer name is required';
        }
        
        if (empty($_POST['requesting_rank'])) {
            $errors['requesting_rank'] = 'Rank is required';
        }
        
        if (empty($_POST['requesting_nis'])) {
            $errors['requesting_nis'] = 'NIS number is required';
        }
        
        if (empty($_POST['requesting_phone'])) {
            $errors['requesting_phone'] = 'Phone number is required';
        }
        
        if (empty($_POST['requesting_command_id'])) {
            $errors['requesting_command_id'] = 'Command is required';
        }
        
        if (empty($_POST['justification'])) {
            $errors['justification'] = 'Justification is required';
        }
        
        // Check if at least one item is provided
        if (!isset($_POST['item_type']) || empty(array_filter($_POST['item_type']))) {
            $errors['items'] = 'At least one requisition item is required';
        }
        
        // If there are validation errors, redirect back with errors and old input
        if (!empty($errors)) {
            Session::set('errors', $errors);
            Session::set('old', $_POST);
            $this->redirect('requisition/create');
            return;
        }
        
        $requisitionNumber = $this->generateRequisitionNumber();
        
        Database::beginTransaction();
        
        try {
            // Determine status (Draft or Pending)
            $status = isset($_POST['status']) && $_POST['status'] === 'Draft' ? 'Draft' : 'Pending';
            
            $requisitionId = Database::insert('requisitions', [
                'requisition_number' => $requisitionNumber,
                'requisition_date' => $_POST['requisition_date'],
                'requisition_type' => $_POST['requisition_type'],
                'priority_level' => $_POST['priority_level'],
                'requesting_officer_id' => Auth::id(),
                'requesting_officer_name' => $_POST['requesting_officer_name'],
                'requesting_rank' => $_POST['requesting_rank'],
                'requesting_nis' => $_POST['requesting_nis'],
                'requesting_phone' => $_POST['requesting_phone'],
                'requesting_command_id' => $_POST['requesting_command_id'],
                'justification' => $_POST['justification'],
                'expected_return_date' => !empty($_POST['expected_return_date']) ? $_POST['expected_return_date'] : null,
                'status' => $status,
                'remarks' => $_POST['remarks'] ?? null,
                'created_by' => Auth::id(),
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s')
            ]);
            
            if (!$requisitionId) {
                throw new Exception("Failed to create requisition");
            }
            
            // Insert items
            foreach ($_POST['item_type'] as $index => $itemType) {
                if (empty($itemType)) continue;
                
                $weaponTypeId = null;
                $weaponTypeOther = null;
                $ammoTypeId = null;
                $ammoTypeOther = null;
                $calibreId = null;
                $calibreOther = null;
                
                if ($itemType === 'Weapon') {
                    $weaponTypeId = !empty($_POST['weapon_type_id'][$index]) && $_POST['weapon_type_id'][$index] !== 'other' ? $_POST['weapon_type_id'][$index] : null;
                    if (($_POST['weapon_type_id'][$index] ?? '') === 'other') {
                        $weaponTypeOther = $_POST['weapon_type_other'][$index] ?? null;
                    }
                } elseif ($itemType === 'Ammunition') {
                    $ammoTypeId = !empty($_POST['ammo_type_id'][$index]) && $_POST['ammo_type_id'][$index] !== 'other' ? $_POST['ammo_type_id'][$index] : null;
                    if (($_POST['ammo_type_id'][$index] ?? '') === 'other') {
                        $ammoTypeOther = $_POST['ammo_type_other'][$index] ?? null;
                    }
                }
                
                // Handle calibre
                $calibreId = !empty($_POST['calibre_id'][$index]) && $_POST['calibre_id'][$index] !== 'other' ? $_POST['calibre_id'][$index] : null;
                if (($_POST['calibre_id'][$index] ?? '') === 'other') {
                    $calibreOther = $_POST['calibre_other'][$index] ?? null;
                }
                
                // Handle purpose
                $purpose = $_POST['purpose'][$index] ?? '';
                if ($purpose === 'Other') {
                    $purpose = $_POST['purpose_other'][$index] ?? 'Other';
                }
                
                $itemId = Database::insert('requisition_items', [
                    'requisition_id' => $requisitionId,
                    'item_type' => $itemType,
                    'weapon_type_id' => $weaponTypeId,
                    'weapon_type_other' => $weaponTypeOther,
                    'ammo_type_id' => $ammoTypeId,
                    'ammo_type_other' => $ammoTypeOther,
                    'calibre_id' => $calibreId,
                    'calibre_other' => $calibreOther,
                    'quantity' => $_POST['quantity'][$index] ?? 1,
                    'purpose' => $purpose,
                    'remarks' => $_POST['item_remarks'][$index] ?? null,
                    'created_at' => date('Y-m-d H:i:s')
                ]);
                
                if (!$itemId) {
                    throw new Exception("Failed to create requisition item");
                }
            }
            
            Database::commit();
            
            if (class_exists('AuditLogger')) {
                AuditLogger::logCreate('requisitions', $requisitionId, $_POST);
            }
            
            $successMessage = $status === 'Draft' ? 'Requisition saved as draft' : 'Requisition submitted successfully';
            $this->redirect('requisition', ['success' => $successMessage]);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Requisition creation error: " . $e->getMessage());
            Session::set('errors', ['general' => 'Failed to submit requisition: ' . $e->getMessage()]);
            Session::set('old', $_POST);
            $this->redirect('requisition/create');
        }
    }
    
    /**
     * Show requisition details
     */
    public function show($id) {
        // Check permission
        if (!Auth::can('requisition.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view requisitions']);
            return;
        }
        
        $requisition = Database::fetchOne(
            "SELECT r.*, 
                    creator.full_name as created_by_name,
                    approver.full_name as approved_by_name,
                    issuer.full_name as issued_by_name,
                    c.command_name
             FROM requisitions r
             LEFT JOIN users creator ON r.created_by = creator.id
             LEFT JOIN users approver ON r.approved_by = approver.id
             LEFT JOIN users issuer ON r.issued_by = issuer.id
             LEFT JOIN commands c ON r.requesting_command_id = c.id
             WHERE r.id = ?",
            [$id]
        );
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        $items = Database::fetchAll(
            "SELECT ri.*,
                    wt.type_name as weapon_type_name,
                    at.ammo_type as ammo_type_name,
                    ac.calibre as calibre_name
             FROM requisition_items ri
             LEFT JOIN weapon_types wt ON ri.weapon_type_id = wt.id
             LEFT JOIN ammunition_types at ON ri.ammo_type_id = at.id
             LEFT JOIN ammunition_calibres ac ON ri.calibre_id = ac.id
             WHERE ri.requisition_id = ?
             ORDER BY ri.id",
            [$id]
        );
        
        if ($items === false) $items = [];
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logView('requisitions', $id);
        }
        
        $this->view('requisition/show', [
            'requisition' => $requisition,
            'items' => $items
        ]);
    }
    
    /**
     * Show edit requisition form
     */
    public function edit($id) {
        // Check permission
        if (!Auth::can('requisition.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit requisitions']);
            return;
        }
        
        $requisition = Database::fetchOne("SELECT * FROM requisitions WHERE id = ?", [$id]);
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        if (!in_array($requisition['status'], ['Pending', 'Draft'])) {
            $this->redirect('requisition', ['error' => 'Cannot edit requisition that is not pending']);
            return;
        }
        
        $items = Database::fetchAll("SELECT * FROM requisition_items WHERE requisition_id = ?", [$id]);
        if ($items === false) $items = [];
        
        $commands = Database::fetchAll("SELECT * FROM commands ORDER BY command_name");
        $weaponTypes = Database::fetchAll("SELECT * FROM weapon_types WHERE is_active = 1 ORDER BY type_name");
        $ammoTypes = Database::fetchAll("SELECT * FROM ammunition_types WHERE is_active = 1 ORDER BY ammo_type");
        $calibres = Database::fetchAll("SELECT * FROM ammunition_calibres WHERE is_active = 1 ORDER BY calibre");
        
        if ($commands === false) $commands = [];
        if ($weaponTypes === false) $weaponTypes = [];
        if ($ammoTypes === false) $ammoTypes = [];
        if ($calibres === false) $calibres = [];
        
        $this->view('requisition/edit', [
            'requisition' => $requisition,
            'items' => $items,
            'commands' => $commands,
            'weaponTypes' => $weaponTypes,
            'ammoTypes' => $ammoTypes,
            'calibres' => $calibres
        ]);
    }
    
    /**
     * Update requisition
     */
    public function update($id) {
        // Check permission
        if (!Auth::can('requisition.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit requisitions']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("requisition/edit/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $requisition = Database::fetchOne("SELECT * FROM requisitions WHERE id = ?", [$id]);
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        if (!in_array($requisition['status'], ['Pending', 'Draft'])) {
            $this->redirect('requisition', ['error' => 'Cannot edit requisition that is not pending']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['requisition_date'])) {
            $errors['requisition_date'] = 'Requisition date is required';
        }
        
        if (empty($_POST['priority_level'])) {
            $errors['priority_level'] = 'Priority level is required';
        }
        
        if (empty($_POST['requesting_officer_name'])) {
            $errors['requesting_officer_name'] = 'Officer name is required';
        }
        
        if (empty($_POST['requesting_rank'])) {
            $errors['requesting_rank'] = 'Rank is required';
        }
        
        if (empty($_POST['requesting_nis'])) {
            $errors['requesting_nis'] = 'NIS number is required';
        }
        
        if (empty($_POST['requesting_phone'])) {
            $errors['requesting_phone'] = 'Phone number is required';
        }
        
        if (empty($_POST['requesting_command_id'])) {
            $errors['requesting_command_id'] = 'Command is required';
        }
        
        if (empty($_POST['justification'])) {
            $errors['justification'] = 'Justification is required';
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            $this->redirect("requisition/edit/$id");
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::update('requisitions', [
                'requisition_date' => $_POST['requisition_date'],
                'priority_level' => $_POST['priority_level'],
                'requesting_officer_name' => $_POST['requesting_officer_name'],
                'requesting_rank' => $_POST['requesting_rank'],
                'requesting_nis' => $_POST['requesting_nis'],
                'requesting_phone' => $_POST['requesting_phone'],
                'requesting_command_id' => $_POST['requesting_command_id'],
                'justification' => $_POST['justification'],
                'expected_return_date' => !empty($_POST['expected_return_date']) ? $_POST['expected_return_date'] : null,
                'remarks' => $_POST['remarks'] ?? null,
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$id]);
            
            // Delete existing items
            Database::delete('requisition_items', 'requisition_id = ?', [$id]);
            
            // Insert updated items
            foreach ($_POST['item_type'] as $index => $itemType) {
                if (empty($itemType)) continue;
                
                $weaponTypeId = null;
                $weaponTypeOther = null;
                $ammoTypeId = null;
                $ammoTypeOther = null;
                $calibreId = null;
                $calibreOther = null;
                
                if ($itemType === 'Weapon') {
                    $weaponTypeId = !empty($_POST['weapon_type_id'][$index]) && $_POST['weapon_type_id'][$index] !== 'other' ? $_POST['weapon_type_id'][$index] : null;
                    if (($_POST['weapon_type_id'][$index] ?? '') === 'other') {
                        $weaponTypeOther = $_POST['weapon_type_other'][$index] ?? null;
                    }
                } elseif ($itemType === 'Ammunition') {
                    $ammoTypeId = !empty($_POST['ammo_type_id'][$index]) && $_POST['ammo_type_id'][$index] !== 'other' ? $_POST['ammo_type_id'][$index] : null;
                    if (($_POST['ammo_type_id'][$index] ?? '') === 'other') {
                        $ammoTypeOther = $_POST['ammo_type_other'][$index] ?? null;
                    }
                }
                
                // Handle calibre
                $calibreId = !empty($_POST['calibre_id'][$index]) && $_POST['calibre_id'][$index] !== 'other' ? $_POST['calibre_id'][$index] : null;
                if (($_POST['calibre_id'][$index] ?? '') === 'other') {
                    $calibreOther = $_POST['calibre_other'][$index] ?? null;
                }
                
                // Handle purpose
                $purpose = $_POST['purpose'][$index] ?? '';
                if ($purpose === 'Other') {
                    $purpose = $_POST['purpose_other'][$index] ?? 'Other';
                }
                
                Database::insert('requisition_items', [
                    'requisition_id' => $id,
                    'item_type' => $itemType,
                    'weapon_type_id' => $weaponTypeId,
                    'weapon_type_other' => $weaponTypeOther,
                    'ammo_type_id' => $ammoTypeId,
                    'ammo_type_other' => $ammoTypeOther,
                    'calibre_id' => $calibreId,
                    'calibre_other' => $calibreOther,
                    'quantity' => $_POST['quantity'][$index] ?? 1,
                    'purpose' => $purpose,
                    'remarks' => $_POST['item_remarks'][$index] ?? null,
                    'created_at' => date('Y-m-d H:i:s')
                ]);
            }
            
            Database::commit();
            
            if (class_exists('AuditLogger')) {
                AuditLogger::logUpdate('requisitions', $id, $requisition, $_POST);
            }
            
            $this->redirect("requisition/show/$id", ['success' => 'Requisition updated successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Requisition update error: " . $e->getMessage());
            $this->redirect("requisition/edit/$id", ['error' => 'Failed to update requisition: ' . $e->getMessage()]);
        }
    }
    
    /**
     * Delete requisition
     */
    public function delete($id) {
        // Check permission
        if (!Auth::can('requisition.delete')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete requisitions']);
            return;
        }
        
        $requisition = Database::fetchOne("SELECT * FROM requisitions WHERE id = ?", [$id]);
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        if (!in_array($requisition['status'], ['Pending', 'Draft'])) {
            $this->redirect('requisition', ['error' => 'Cannot delete requisition that is not pending']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::delete('requisition_items', 'requisition_id = ?', [$id]);
            Database::delete('requisitions', 'id = ?', [$id]);
            
            Database::commit();
            
            if (class_exists('AuditLogger')) {
                AuditLogger::logDelete('requisitions', $id, $requisition);
            }
            
            $this->redirect('requisition', ['success' => 'Requisition deleted successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Requisition deletion error: " . $e->getMessage());
            $this->redirect('requisition', ['error' => 'Failed to delete requisition: ' . $e->getMessage()]);
        }
    }
    
    /**
     * Approve requisition
     */
    public function approve($id) {
        // Check permission
        if (!Auth::can('requisition.approve')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to approve requisitions']);
            return;
        }
        
        // Check if it's a POST request
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            $this->redirect("requisition/show/$id", ['error' => 'Invalid request method']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("requisition/show/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $requisition = Database::fetchOne("SELECT * FROM requisitions WHERE id = ?", [$id]);
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        if ($requisition['status'] !== 'Pending') {
            $this->redirect('requisition', ['error' => 'Only pending requisitions can be approved']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::update('requisitions', [
                'status' => 'Approved',
                'approved_by' => Auth::id(),
                'approval_date' => date('Y-m-d H:i:s'),
                'approval_remarks' => $_POST['approval_remarks'] ?? null,
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$id]);
            
            Database::commit();
            
            if (class_exists('AuditLogger')) {
                AuditLogger::log('APPROVE', 'requisitions', $id, null, 'Requisition approved');
            }
            
            $this->redirect('requisition/pending', ['success' => 'Requisition approved successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Requisition approval error: " . $e->getMessage());
            $this->redirect("requisition/show/$id", ['error' => 'Failed to approve requisition: ' . $e->getMessage()]);
        }
    }
    
    /**
     * Reject requisition
     */
    public function reject($id) {
        // Check permission
        if (!Auth::can('requisition.approve')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to reject requisitions']);
            return;
        }
        
        // Check if it's a POST request
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            $this->redirect("requisition/show/$id", ['error' => 'Invalid request method']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("requisition/show/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $requisition = Database::fetchOne("SELECT * FROM requisitions WHERE id = ?", [$id]);
        
        if (!$requisition) {
            $this->redirect('requisition', ['error' => 'Requisition not found']);
            return;
        }
        
        if ($requisition['status'] !== 'Pending') {
            $this->redirect('requisition', ['error' => 'Only pending requisitions can be rejected']);
            return;
        }
        
        if (empty($_POST['rejection_reason'])) {
            $this->redirect("requisition/show/$id", ['error' => 'Rejection reason is required']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::update('requisitions', [
                'status' => 'Rejected',
                'approved_by' => Auth::id(),
                'approval_date' => date('Y-m-d H:i:s'),
                'rejection_reason' => $_POST['rejection_reason'],
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$id]);
            
            Database::commit();
            
            if (class_exists('AuditLogger')) {
                AuditLogger::log('REJECT', 'requisitions', $id, null, 'Requisition rejected: ' . $_POST['rejection_reason']);
            }
            
            $this->redirect('requisition/pending', ['success' => 'Requisition rejected successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Requisition rejection error: " . $e->getMessage());
            $this->redirect("requisition/show/$id", ['error' => 'Failed to reject requisition: ' . $e->getMessage()]);
        }
    }
    
    /**
     * Show user's requisitions
     */
    public function my() {
        // Check if user is logged in
        if (!Auth::check()) {
            $this->redirect('auth/login', ['error' => 'Please login to continue']);
            return;
        }
        
        $requisitions = Database::fetchAll(
            "SELECT r.*, c.command_name, COUNT(ri.id) as item_count
             FROM requisitions r
             LEFT JOIN commands c ON r.requesting_command_id = c.id
             LEFT JOIN requisition_items ri ON r.id = ri.requisition_id
             WHERE r.created_by = ?
             GROUP BY r.id
             ORDER BY r.created_at DESC",
            [Auth::id()]
        );
        
        if ($requisitions === false) $requisitions = [];
        
        $this->view('requisition/my', ['requisitions' => $requisitions]);
    }
    
    /**
     * Show pending requisitions (for approval)
     */
    public function pending() {
        // Check permission
        if (!Auth::can('requisition.approve')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to approve requisitions']);
            return;
        }
        
        $requisitions = Database::fetchAll(
            "SELECT r.*, u.full_name as requester_name, c.command_name,
                    COUNT(ri.id) as item_count
             FROM requisitions r
             LEFT JOIN users u ON r.created_by = u.id
             LEFT JOIN commands c ON r.requesting_command_id = c.id
             LEFT JOIN requisition_items ri ON r.id = ri.requisition_id
             WHERE r.status = 'Pending'
             GROUP BY r.id
             ORDER BY 
                CASE r.priority_level 
                    WHEN 'Urgent' THEN 1 
                    WHEN 'High' THEN 2 
                    WHEN 'Medium' THEN 3 
                    WHEN 'Low' THEN 4 
                    ELSE 5 
                END, 
                r.created_at ASC"
        );
        
        if ($requisitions === false) $requisitions = [];
        
        $this->view('requisition/pending', ['requisitions' => $requisitions]);
    }
    
    /**
     * Export requisitions to CSV
     */
    public function export() {
        // Check permission
        if (!Auth::can('reports.export')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to export data']);
            return;
        }
        
        $requisitions = Database::fetchAll(
            "SELECT r.*, c.command_name,
                    COUNT(ri.id) as item_count
             FROM requisitions r
             LEFT JOIN commands c ON r.requesting_command_id = c.id
             LEFT JOIN requisition_items ri ON r.id = ri.requisition_id
             GROUP BY r.id
             ORDER BY r.created_at DESC"
        );
        
        if ($requisitions === false) $requisitions = [];
        
        $filename = 'requisitions_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        // Headers
        fputcsv($output, [
            'Requisition #', 'Date', 'Type', 'Priority', 'Officer Name',
            'Rank', 'NIS Number', 'Phone', 'Command', 'Justification',
            'Expected Return', 'Status', 'Item Count', 'Remarks', 'Created At'
        ]);
        
        // Data
        foreach ($requisitions as $req) {
            fputcsv($output, [
                $req['requisition_number'] ?? '',
                $req['requisition_date'] ?? '',
                $req['requisition_type'] ?? '',
                $req['priority_level'] ?? '',
                $req['requesting_officer_name'] ?? '',
                $req['requesting_rank'] ?? '',
                $req['requesting_nis'] ?? '',
                $req['requesting_phone'] ?? '',
                $req['command_name'] ?? '',
                $req['justification'] ?? '',
                $req['expected_return_date'] ?? '',
                $req['status'] ?? '',
                $req['item_count'] ?? 0,
                $req['remarks'] ?? '',
                $req['created_at'] ?? ''
            ]);
        }
        
        fclose($output);
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('requisitions', 'csv');
        }
        exit;
    }
    
    /**
     * Generate unique requisition number
     */
    private function generateRequisitionNumber() {
        $year = date('Y');
        $month = date('m');
        
        $last = Database::fetchOne(
            "SELECT requisition_number FROM requisitions
             WHERE requisition_number LIKE 'REQ-{$year}{$month}%' 
             ORDER BY id DESC LIMIT 1"
        );
        
        if ($last && isset($last['requisition_number'])) {
            $seq = intval(substr($last['requisition_number'], -4)) + 1;
        } else {
            $seq = 1;
        }
        
        return sprintf("REQ-%s%s-%04d", $year, $month, $seq);
    }
}