<?php
/**
 * NIS Asset Management System
 * 
 * Weapon Issue Controller
 * Handles weapons and ammunition issuance, tracking, and returns
 * 
 * @author NIS Web Team
 * @version 1.0
 */

class WeaponIssueController extends Controller {
    
    /**
     * Constructor - Check permissions
     */
    public function __construct() {
        // Only call parent constructor if it exists
        if (method_exists(get_parent_class($this), '__construct')) {
            parent::__construct();
        }
        
        // Check if user has permission to issue weapons
        if (!Auth::can('weapons.edit') && !Auth::can('weapons.issue')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to issue weapons']);
        }
    }
    
    /**
     * Display weapon issue dashboard with forms and recent issues
     */
    public function index() {
        // Get available weapons (in Armoury)
        $availableWeapons = $this->getAvailableWeapons();
        
        // Get available ammunition (balance > 0)
        $availableAmmunition = $this->getAvailableAmmunition();
        
        // Get approved requisitions for dropdown
        $requisitions = $this->getApprovedRequisitions();
        
        // Get recent weapon issue logs
        $recentWeaponIssues = $this->getRecentWeaponIssues(20);
        
        // Get recent ammunition issue logs
        $recentAmmoIssues = $this->getRecentAmmoIssues(20);
        
        // Get currently issued weapons (for returns section)
        $issuedWeapons = $this->getIssuedWeapons();
        
        // Get issued ammunition (for returns section)
        $issuedAmmunition = $this->getIssuedAmmunition();
        
        // Calculate statistics
        $statistics = [
            'total_issued_weapons' => count($issuedWeapons),
            'available_weapons' => count($availableWeapons),
            'pending_returns' => $this->getPendingReturnsCount(),
            'available_ammo' => count($availableAmmunition),
            'total_weapons' => $this->getTotalWeaponsCount(),
            'total_ammunition' => $this->getTotalAmmunitionCount(),
            'serviceable_weapons' => $this->getServiceableWeaponsCount(),
            'unserviceable_weapons' => $this->getUnserviceableWeaponsCount()
        ];
        
        // Load the view
        $this->view('weapon_issue/index', [
            'availableWeapons' => $availableWeapons,
            'availableAmmunition' => $availableAmmunition,
            'requisitions' => $requisitions,
            'recentWeaponIssues' => $recentWeaponIssues,
            'recentAmmoIssues' => $recentAmmoIssues,
            'issuedWeapons' => $issuedWeapons,
            'issuedAmmunition' => $issuedAmmunition,
            'statistics' => $statistics
        ]);
    }
    
    /**
     * Show create issue form (alternative to index)
     */
    public function create() {
        $this->index();
    }
    
    /**
     * Process weapon issue form submission
     */
    public function store() {
        // Check permission
        if (!Auth::can('weapons.edit') && !Auth::can('weapons.issue')) {
            $this->jsonResponse(false, 'You do not have permission to issue weapons');
            return;
        }
        
        // Validate CSRF token
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->jsonResponse(false, 'Invalid security token');
            return;
        }
        
        $type = $_POST['issue_type'] ?? '';
        
        if ($type === 'weapon') {
            $this->processWeaponIssue();
        } elseif ($type === 'ammunition') {
            $this->processAmmunitionIssue();
        } else {
            $this->jsonResponse(false, 'Invalid issue type');
        }
    }
    
    /**
     * Process weapon issue
     */
    private function processWeaponIssue() {
        // Validate required fields
        $required = ['weapon_id', 'officer_name', 'officer_rank', 'unit', 'purpose', 'approved_by'];
        foreach ($required as $field) {
            if (empty($_POST[$field])) {
                $this->jsonResponse(false, ucfirst(str_replace('_', ' ', $field)) . ' is required');
                return;
            }
        }
        
        $weaponId = (int)$_POST['weapon_id'];
        $requisitionId = !empty($_POST['requisition_id']) ? $_POST['requisition_id'] : null;
        $issueDate = $_POST['issue_date'] ?? date('Y-m-d');
        $expectedReturnDate = !empty($_POST['expected_return_date']) ? $_POST['expected_return_date'] : null;
        
        // Check if weapon exists and is available
        $weapon = Database::fetchOne(
            "SELECT * FROM weapons_inventory WHERE id = ?",
            [$weaponId]
        );
        
        if (!$weapon) {
            $this->jsonResponse(false, 'Weapon not found');
            return;
        }
        
        if ($weapon['current_location'] != 'Armoury' && $weapon['current_location'] != 'Available') {
            $this->jsonResponse(false, 'Weapon is not available for issue');
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Insert into weapon issue log
            $issueId = Database::insert('weapon_issue_log', [
                'requisition_id' => $requisitionId,
                'weapon_id' => $weaponId,
                'issue_date' => $issueDate,
                'officer_name' => $_POST['officer_name'],
                'officer_rank' => $_POST['officer_rank'],
                'officer_nis' => $_POST['officer_nis'] ?? null,
                'unit' => $_POST['unit'],
                'purpose' => $_POST['purpose'] === 'Other' ? ($_POST['purpose_other'] ?? 'Other') : $_POST['purpose'],
                'approved_by' => $_POST['approved_by'],
                'issued_by' => Auth::id(),
                'expected_return_date' => $expectedReturnDate,
                'status' => 'Issued',
                'remarks' => $_POST['remarks'] ?? null,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s')
            ]);
            
            if (!$issueId) {
                throw new Exception("Failed to create issue record");
            }
            
            // Update weapon status
            Database::update('weapons_inventory', [
                'current_location' => 'Issued',
                'custodian' => $_POST['officer_name'],
                'custodian_rank' => $_POST['officer_rank'],
                'custodian_nis' => $_POST['officer_nis'] ?? null,
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$weaponId]);
            
            // Update requisition status if linked
            if ($requisitionId) {
                $this->updateRequisitionStatus($requisitionId, 'weapon');
            }
            
            // Log audit
            if (class_exists('AuditLogger')) {
                AuditLogger::log('WEAPON_ISSUE', 'weapons_inventory', $weaponId, null, 
                    "Weapon issued to {$_POST['officer_name']}" . ($requisitionId ? " (Requisition: $requisitionId)" : ""));
            }
            
            Database::commit();
            
            // Return success response
            if ($this->isAjax()) {
                $this->jsonResponse(true, 'Weapon issued successfully', ['issue_id' => $issueId]);
            } else {
                $this->redirect('weapon_issue', ['success' => 'Weapon issued successfully']);
            }
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon issue error: " . $e->getMessage());
            
            if ($this->isAjax()) {
                $this->jsonResponse(false, 'Failed to issue weapon: ' . $e->getMessage());
            } else {
                $this->redirect('weapon_issue/create', ['error' => 'Failed to issue weapon']);
            }
        }
    }
    
    /**
     * Process ammunition issue - FIXED VERSION
     */
    private function processAmmunitionIssue() {
        // Log all POST data for debugging
        error_log("=== AMMUNITION ISSUE DEBUG ===");
        error_log("POST data: " . json_encode($_POST));
        
        // Validate required fields based on your form
        $required = ['ammo_id', 'units_issued', 'issued_to', 'purpose', 'approved_by'];
        foreach ($required as $field) {
            if (empty($_POST[$field])) {
                error_log("Missing required field: $field");
                $this->jsonResponse(false, ucfirst(str_replace('_', ' ', $field)) . ' is required');
                return;
            }
        }
        
        $ammoId = (int)$_POST['ammo_id'];
        $unitsIssued = (int)$_POST['units_issued'];
        
        // Validate units issued
        if ($unitsIssued <= 0) {
            error_log("Invalid units issued: $unitsIssued");
            $this->jsonResponse(false, 'Units issued must be greater than zero');
            return;
        }
        
        // Calculate rounds (30 rounds per unit as standard)
        $roundsIssued = !empty($_POST['total_rounds']) ? (int)$_POST['total_rounds'] : ($unitsIssued * 30);
        $requisitionId = !empty($_POST['requisition_id']) ? $_POST['requisition_id'] : null;
        $issueDate = $_POST['issue_date'] ?? date('Y-m-d');
        
        error_log("Ammo ID: $ammoId, Units: $unitsIssued, Rounds: $roundsIssued");
        error_log("Requisition ID: " . ($requisitionId ?? 'none'));
        
        // Check if ammunition exists and has sufficient balance
        $ammo = Database::fetchOne(
            "SELECT * FROM ammunition_inventory WHERE id = ?",
            [$ammoId]
        );
        
        if (!$ammo) {
            error_log("Ammunition not found with ID: $ammoId");
            $this->jsonResponse(false, 'Ammunition not found');
            return;
        }
        
        error_log("Ammunition found: ID={$ammo['ammo_id']}, Balance={$ammo['balance']}, Received={$ammo['quantity_received']}, Issued={$ammo['quantity_issued']}");
        
        // Check balance
        if ($ammo['balance'] < $unitsIssued) {
            error_log("Insufficient balance. Available: {$ammo['balance']}, Requested: $unitsIssued");
            $this->jsonResponse(false, "Insufficient balance. Available: {$ammo['balance']} units");
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Insert into ammunition issue log - using CORRECT column names from your table structure
            $insertData = [
                'requisition_id' => $requisitionId,
                'ammo_id' => $ammoId,
                'issue_date' => $issueDate,
                'units_issued' => $unitsIssued,
                'rounds_issued' => $roundsIssued, // Using rounds_issued (not total_rounds)
                'officer_name' => $_POST['issued_to'], // Map issued_to to officer_name
                'purpose' => $_POST['purpose'] === 'Other' ? ($_POST['purpose_other'] ?? 'Other') : $_POST['purpose'],
                'approved_by' => $_POST['approved_by'],
                'issued_by' => Auth::id(),
                'remarks' => $_POST['remarks'] ?? null,
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            // Add optional fields if they exist in POST
            if (!empty($_POST['officer_rank'])) {
                $insertData['officer_rank'] = $_POST['officer_rank'];
            }
            
            if (!empty($_POST['officer_nis'])) {
                $insertData['officer_nis'] = $_POST['officer_nis'];
            }
            
            if (!empty($_POST['unit'])) {
                $insertData['unit'] = $_POST['unit'];
            }
            
            error_log("Insert data: " . json_encode($insertData));
            
            // Insert into ammunition issue log
            $issueId = Database::insert('ammunition_issue_log', $insertData);
            
            if (!$issueId) {
                throw new Exception("Failed to create ammunition issue record - insert returned false/0");
            }
            
            error_log("Issue record created with ID: $issueId");
            
            // Update ammunition balance
            $newIssued = ($ammo['quantity_issued'] ?? 0) + $unitsIssued;
            $newBalance = ($ammo['quantity_received'] ?? 0) - $newIssued;
            
            error_log("Updating ammunition - New issued: $newIssued, New balance: $newBalance");
            
            $updateResult = Database::update('ammunition_inventory', [
                'quantity_issued' => $newIssued,
                'balance' => $newBalance,
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$ammoId]);
            
            error_log("Update result: " . ($updateResult ? 'success' : 'failed'));
            
            // Update requisition status if linked
            if ($requisitionId) {
                error_log("Updating requisition status for ID: $requisitionId");
                $this->updateRequisitionStatus($requisitionId, 'ammunition');
            }
            
            // Log audit
            if (class_exists('AuditLogger')) {
                AuditLogger::log('AMMUNITION_ISSUE', 'ammunition_inventory', $ammoId, null, 
                    "Ammunition issued: $unitsIssued units to {$_POST['issued_to']}" . ($requisitionId ? " (Requisition: $requisitionId)" : ""));
            }
            
            Database::commit();
            error_log("Transaction committed successfully");
            
            // Return success response
            if ($this->isAjax()) {
                $this->jsonResponse(true, 'Ammunition issued successfully', ['issue_id' => $issueId]);
            } else {
                $this->redirect('weapon_issue', ['success' => 'Ammunition issued successfully']);
            }
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Ammunition issue error: " . $e->getMessage());
            error_log("Stack trace: " . $e->getTraceAsString());
            
            if ($this->isAjax()) {
                $this->jsonResponse(false, 'Failed to issue ammunition: ' . $e->getMessage());
            } else {
                $this->redirect('weapon_issue/create', ['error' => 'Failed to issue ammunition: ' . $e->getMessage()]);
            }
        }
    }
    
    /**
     * Show return form for a specific issue
     */
    public function return($id) {
        // Check if it's weapon or ammunition issue
        $type = $_GET['type'] ?? 'weapon';
        
        if ($type === 'weapon') {
            $issue = Database::fetchOne(
                "SELECT wil.*, wi.weapon_id, wi.make_model, wi.serial_no, wi.current_location
                 FROM weapon_issue_log wil
                 JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                 WHERE wil.id = ?",
                [$id]
            );
            
            if (!$issue) {
                $this->redirect('weapon_issue', ['error' => 'Weapon issue record not found']);
                return;
            }
            
            if ($issue['status'] != 'Issued') {
                $this->redirect('weapon_issue', ['error' => 'This weapon has already been returned']);
                return;
            }
            
            $this->view('weapon_issue/return', [
                'issue' => $issue,
                'type' => 'weapon'
            ]);
            
        } else {
            $tableName = $this->getAmmoIssueTableName();
            $issue = Database::fetchOne(
                "SELECT ail.*, ai.ammo_id, ai.ammo_type, ai.calibre
                 FROM $tableName ail
                 JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                 WHERE ail.id = ?",
                [$id]
            );
            
            if (!$issue) {
                $this->redirect('weapon_issue', ['error' => 'Ammunition issue record not found']);
                return;
            }
            
            $this->view('weapon_issue/return', [
                'issue' => $issue,
                'type' => 'ammunition'
            ]);
        }
    }
    
    /**
     * Process weapon return
     */
    public function processReturn($id) {
        // Check permission
        if (!Auth::can('weapons.edit') && !Auth::can('weapons.return')) {
            $this->jsonResponse(false, 'You do not have permission to process returns');
            return;
        }
        
        // Validate CSRF token
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->jsonResponse(false, 'Invalid security token');
            return;
        }
        
        $type = $_POST['return_type'] ?? 'weapon';
        
        if ($type === 'weapon') {
            $this->processWeaponReturn($id);
        } else {
            $this->processAmmunitionReturn($id);
        }
    }
    
    /**
     * Process weapon return
     */
    private function processWeaponReturn($id) {
        // Validate required fields
        if (empty($_POST['return_condition'])) {
            $this->jsonResponse(false, 'Return condition is required');
            return;
        }
        
        $issue = Database::fetchOne("SELECT * FROM weapon_issue_log WHERE id = ?", [$id]);
        
        if (!$issue) {
            $this->jsonResponse(false, 'Issue record not found');
            return;
        }
        
        if ($issue['status'] != 'Issued') {
            $this->jsonResponse(false, 'This weapon has already been returned');
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Update issue log with return information
            Database::update('weapon_issue_log', [
                'actual_return_date' => $_POST['return_date'] ?? date('Y-m-d'),
                'return_condition' => $_POST['return_condition'],
                'status' => 'Returned',
                'remarks' => isset($_POST['remarks']) ? $issue['remarks'] . "\nReturn remarks: " . $_POST['remarks'] : $issue['remarks'],
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$id]);
            
            // Update weapon status back to Armoury
            Database::update('weapons_inventory', [
                'current_location' => 'Armoury',
                'custodian' => null,
                'custodian_rank' => null,
                'custodian_nis' => null,
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$issue['weapon_id']]);
            
            // Log audit
            if (class_exists('AuditLogger')) {
                AuditLogger::log('WEAPON_RETURN', 'weapons_inventory', $issue['weapon_id'], null, 
                    "Weapon returned by {$issue['officer_name']} - Condition: {$_POST['return_condition']}");
            }
            
            Database::commit();
            
            if ($this->isAjax()) {
                $this->jsonResponse(true, 'Weapon returned successfully');
            } else {
                $this->redirect('weapon_issue', ['success' => 'Weapon returned successfully']);
            }
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon return error: " . $e->getMessage());
            
            if ($this->isAjax()) {
                $this->jsonResponse(false, 'Failed to process return: ' . $e->getMessage());
            } else {
                $this->redirect("weapon_issue/return/$id", ['error' => 'Failed to process return']);
            }
        }
    }
    
    /**
     * Process ammunition return
     */
    private function processAmmunitionReturn($id) {
        // Validate required fields
        if (empty($_POST['rounds_returned'])) {
            $this->jsonResponse(false, 'Rounds returned is required');
            return;
        }
        
        $tableName = $this->getAmmoIssueTableName();
        $issue = Database::fetchOne("SELECT * FROM $tableName WHERE id = ?", [$id]);
        
        if (!$issue) {
            $this->jsonResponse(false, 'Issue record not found');
            return;
        }
        
        $roundsReturned = (int)$_POST['rounds_returned'];
        $roundsUsed = (int)$_POST['rounds_used'];
        
        Database::beginTransaction();
        
        try {
            // Update issue log with return information
            Database::update($tableName, [
                'return_date' => $_POST['return_date'] ?? date('Y-m-d'),
                'rounds_returned' => $roundsReturned,
                'rounds_used' => $roundsUsed,
                'return_condition' => $_POST['return_condition'] ?? null,
                'status' => 'Returned',
                'updated_at' => date('Y-m-d H:i:s')
            ], 'id = ?', [$id]);
            
            // Update ammunition balance (add back returned rounds)
            $ammo = Database::fetchOne("SELECT * FROM ammunition_inventory WHERE id = ?", [$issue['ammo_id']]);
            
            if ($ammo) {
                $newIssued = ($ammo['quantity_issued'] ?? 0) - $roundsReturned;
                $newBalance = ($ammo['quantity_received'] ?? 0) - $newIssued;
                
                Database::update('ammunition_inventory', [
                    'quantity_issued' => $newIssued,
                    'balance' => $newBalance,
                    'updated_at' => date('Y-m-d H:i:s')
                ], 'id = ?', [$issue['ammo_id']]);
            }
            
            // Log audit
            if (class_exists('AuditLogger')) {
                AuditLogger::log('AMMUNITION_RETURN', 'ammunition_inventory', $issue['ammo_id'], null, 
                    "Ammunition returned: $roundsReturned rounds");
            }
            
            Database::commit();
            
            if ($this->isAjax()) {
                $this->jsonResponse(true, 'Ammunition returned successfully');
            } else {
                $this->redirect('weapon_issue', ['success' => 'Ammunition returned successfully']);
            }
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Ammunition return error: " . $e->getMessage());
            
            if ($this->isAjax()) {
                $this->jsonResponse(false, 'Failed to process return: ' . $e->getMessage());
            } else {
                $this->redirect("weapon_issue/return/$id?type=ammunition", ['error' => 'Failed to process return']);
            }
        }
    }
    
    /**
     * Show full issue history
     */
    public function history() {
        $type = $_GET['type'] ?? 'all';
        $page = (int)($_GET['page'] ?? 1);
        $limit = 50;
        $offset = ($page - 1) * $limit;
        
        $weaponIssues = [];
        $ammoIssues = [];
        $totalWeaponIssues = 0;
        $totalAmmoIssues = 0;
        
        if ($type === 'all' || $type === 'weapons') {
            $weaponIssues = Database::fetchAll(
                "SELECT wil.*, wi.weapon_id, wi.make_model, wi.serial_no,
                        u.full_name as issued_by_name
                 FROM weapon_issue_log wil
                 JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                 LEFT JOIN users u ON wil.issued_by = u.id
                 ORDER BY wil.issue_date DESC, wil.id DESC
                 LIMIT ? OFFSET ?",
                [$limit, $offset]
            );
            
            $totalWeaponIssues = Database::fetchOne(
                "SELECT COUNT(*) as count FROM weapon_issue_log"
            )['count'] ?? 0;
        }
        
        if ($type === 'all' || $type === 'ammunition') {
            $tableName = $this->getAmmoIssueTableName();
            $ammoIssues = Database::fetchAll(
                "SELECT ail.*, ai.ammo_id, ai.ammo_type, ai.calibre,
                        u.full_name as issued_by_name
                 FROM $tableName ail
                 JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                 LEFT JOIN users u ON ail.issued_by = u.id
                 ORDER BY ail.issue_date DESC, ail.id DESC
                 LIMIT ? OFFSET ?",
                [$limit, $offset]
            );
            
            $totalAmmoIssues = Database::fetchOne(
                "SELECT COUNT(*) as count FROM $tableName"
            )['count'] ?? 0;
        }
        
        $this->view('weapon_issue/history', [
            'type' => $type,
            'weaponIssues' => $weaponIssues,
            'ammoIssues' => $ammoIssues,
            'page' => $page,
            'totalWeaponIssues' => $totalWeaponIssues,
            'totalAmmoIssues' => $totalAmmoIssues,
            'limit' => $limit
        ]);
    }
    
    /**
     * Show a specific issue details
     */
    public function show($id) {
        $type = $_GET['type'] ?? 'weapon';
        
        if ($type === 'weapon') {
            $issue = Database::fetchOne(
                "SELECT wil.*, wi.weapon_id, wi.make_model, wi.serial_no, wi.calibre_id,
                        wc.calibre_name, wt.type_name,
                        issuer.full_name as issued_by_name,
                        req.requisition_number
                 FROM weapon_issue_log wil
                 JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                 LEFT JOIN weapon_calibres wc ON wi.calibre_id = wc.id
                 LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
                 LEFT JOIN users issuer ON wil.issued_by = issuer.id
                 LEFT JOIN requisitions req ON wil.requisition_id = req.id
                 WHERE wil.id = ?",
                [$id]
            );
            
            if (!$issue) {
                $this->redirect('weapon_issue/history', ['error' => 'Issue record not found']);
                return;
            }
            
            $this->view('weapon_issue/show', [
                'issue' => $issue,
                'type' => 'weapon'
            ]);
            
        } else {
            $tableName = $this->getAmmoIssueTableName();
            $issue = Database::fetchOne(
                "SELECT ail.*, ai.ammo_id, ai.ammo_type, ai.calibre,
                        issuer.full_name as issued_by_name,
                        req.requisition_number
                 FROM $tableName ail
                 JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                 LEFT JOIN users issuer ON ail.issued_by = issuer.id
                 LEFT JOIN requisitions req ON ail.requisition_id = req.id
                 WHERE ail.id = ?",
                [$id]
            );
            
            if (!$issue) {
                $this->redirect('weapon_issue/history', ['error' => 'Issue record not found']);
                return;
            }
            
            $this->view('weapon_issue/show', [
                'issue' => $issue,
                'type' => 'ammunition'
            ]);
        }
    }
    
    /**
     * API endpoint to get issued weapons (for return dropdown)
     */
    public function apiGetIssuedWeapons() {
        if (!$this->isAjax()) {
            header('HTTP/1.0 403 Forbidden');
            exit;
        }
        
        $search = $_GET['search'] ?? '';
        
        if ($search) {
            $weapons = Database::fetchAll(
                "SELECT wil.id as issue_log_id, wil.officer_name, wil.issue_date,
                        wi.id as weapon_id, wi.weapon_id as weapon_code, wi.make_model, wi.serial_no
                 FROM weapon_issue_log wil
                 JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                 WHERE wil.status = 'Issued' 
                   AND (wi.weapon_id LIKE ? OR wi.serial_no LIKE ? OR wi.make_model LIKE ? OR wil.officer_name LIKE ?)
                 ORDER BY wil.issue_date DESC
                 LIMIT 50",
                ["%$search%", "%$search%", "%$search%", "%$search%"]
            );
        } else {
            $weapons = Database::fetchAll(
                "SELECT wil.id as issue_log_id, wil.officer_name, wil.issue_date,
                        wi.id as weapon_id, wi.weapon_id as weapon_code, wi.make_model, wi.serial_no
                 FROM weapon_issue_log wil
                 JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                 WHERE wil.status = 'Issued'
                 ORDER BY wil.issue_date DESC
                 LIMIT 50"
            );
        }
        
        $this->jsonResponse(true, 'Success', ['weapons' => $weapons]);
    }
    
    /**
     * API endpoint to get issued ammunition (for return dropdown)
     */
    public function apiGetIssuedAmmunition() {
        if (!$this->isAjax()) {
            header('HTTP/1.0 403 Forbidden');
            exit;
        }
        
        $search = $_GET['search'] ?? '';
        $tableName = $this->getAmmoIssueTableName();
        
        if ($search) {
            $ammo = Database::fetchAll(
                "SELECT ail.id as issue_log_id, ail.officer_name, ail.issue_date, ail.units_issued, ail.rounds_issued,
                        ai.id as ammo_id, ai.ammo_id as ammo_code, ai.ammo_type, ai.calibre, ai.balance
                 FROM $tableName ail
                 JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                 WHERE (ail.status = 'Issued' OR ail.status IS NULL)
                   AND (ai.ammo_id LIKE ? OR ai.ammo_type LIKE ? OR ai.calibre LIKE ? OR ail.officer_name LIKE ?)
                 ORDER BY ail.issue_date DESC
                 LIMIT 50",
                ["%$search%", "%$search%", "%$search%", "%$search%"]
            );
        } else {
            $ammo = Database::fetchAll(
                "SELECT ail.id as issue_log_id, ail.officer_name, ail.issue_date, ail.units_issued, ail.rounds_issued,
                        ai.id as ammo_id, ai.ammo_id as ammo_code, ai.ammo_type, ai.calibre, ai.balance
                 FROM $tableName ail
                 JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                 WHERE ail.status = 'Issued' OR ail.status IS NULL
                 ORDER BY ail.issue_date DESC
                 LIMIT 50"
            );
        }
        
        $this->jsonResponse(true, 'Success', ['ammunition' => $ammo]);
    }
    
    /**
     * Helper: Get available weapons
     */
    private function getAvailableWeapons() {
        return Database::fetchAll(
            "SELECT wi.*, wt.type_name 
             FROM weapons_inventory wi
             LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
             WHERE wi.current_location = 'Armoury' OR wi.current_location = 'Available'
             ORDER BY wi.weapon_id"
        ) ?: [];
    }
    
    /**
     * Helper: Get available ammunition
     */
    private function getAvailableAmmunition() {
        return Database::fetchAll(
            "SELECT ai.*, at.ammo_type, ac.calibre 
             FROM ammunition_inventory ai
             LEFT JOIN ammunition_types at ON ai.ammo_type_id = at.id
             LEFT JOIN ammunition_calibres ac ON ai.calibre_id = ac.id
             WHERE ai.balance > 0
             ORDER BY ai.ammo_id"
        ) ?: [];
    }
    
    /**
     * Helper: Get approved requisitions
     */
    private function getApprovedRequisitions() {
        return Database::fetchAll(
            "SELECT r.*, 
                    COUNT(DISTINCT ri.id) as total_items,
                    COALESCE(SUM(CASE WHEN ri.item_type = 'Weapon' THEN ri.quantity ELSE 0 END), 0) as total_weapons,
                    COALESCE(SUM(CASE WHEN ri.item_type = 'Ammunition' THEN ri.quantity ELSE 0 END), 0) as total_ammunition
             FROM requisitions r
             LEFT JOIN requisition_items ri ON r.id = ri.requisition_id
             WHERE r.status IN ('Approved', 'Partially Issued')
             GROUP BY r.id
             ORDER BY r.created_at DESC"
        ) ?: [];
    }
    
    /**
     * Helper: Get recent weapon issues
     */
    private function getRecentWeaponIssues($limit = 20) {
        return Database::fetchAll(
            "SELECT wil.*, wi.weapon_id, wi.make_model, u.full_name as issued_by_name
             FROM weapon_issue_log wil
             JOIN weapons_inventory wi ON wil.weapon_id = wi.id
             LEFT JOIN users u ON wil.issued_by = u.id
             ORDER BY wil.issue_date DESC, wil.id DESC
             LIMIT ?",
            [$limit]
        ) ?: [];
    }
    
    /**
     * Helper: Get recent ammunition issues
     */
    private function getRecentAmmoIssues($limit = 20) {
        $tableName = $this->getAmmoIssueTableName();
        
        return Database::fetchAll(
            "SELECT ail.*, ai.ammo_id, ai.ammo_type, ai.calibre, u.full_name as issued_by_name
             FROM $tableName ail
             JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
             LEFT JOIN users u ON ail.issued_by = u.id
             ORDER BY ail.issue_date DESC, ail.id DESC
             LIMIT ?",
            [$limit]
        ) ?: [];
    }
    
    /**
     * Helper: Get currently issued weapons
     */
    private function getIssuedWeapons() {
        return Database::fetchAll(
            "SELECT wil.*, wi.weapon_id, wi.make_model, wi.serial_no
             FROM weapon_issue_log wil
             JOIN weapons_inventory wi ON wil.weapon_id = wi.id
             WHERE wil.status = 'Issued'
             ORDER BY wil.issue_date DESC"
        ) ?: [];
    }
    
    /**
     * Helper: Get issued ammunition
     */
    private function getIssuedAmmunition() {
        $tableName = $this->getAmmoIssueTableName();
        
        return Database::fetchAll(
            "SELECT ail.*, ai.ammo_id, ai.ammo_type, ai.calibre, ai.balance
             FROM $tableName ail
             JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
             WHERE ail.status = 'Issued' OR ail.status IS NULL
             ORDER BY ail.issue_date DESC"
        ) ?: [];
    }
    
    /**
     * Helper: Get pending returns count
     */
    private function getPendingReturnsCount() {
        $weaponPending = Database::fetchOne(
            "SELECT COUNT(*) as count FROM weapon_issue_log WHERE status = 'Issued' AND expected_return_date < CURDATE()"
        )['count'] ?? 0;
        
        return $weaponPending;
    }
    
    /**
     * Helper: Get total weapons count
     */
    private function getTotalWeaponsCount() {
        $result = Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory");
        return $result['count'] ?? 0;
    }
    
    /**
     * Helper: Get total ammunition count
     */
    private function getTotalAmmunitionCount() {
        $result = Database::fetchOne("SELECT COUNT(*) as count FROM ammunition_inventory");
        return $result['count'] ?? 0;
    }
    
    /**
     * Helper: Get serviceable weapons count
     */
    private function getServiceableWeaponsCount() {
        $result = Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE `condition` = 'Serviceable'");
        return $result['count'] ?? 0;
    }
    
    /**
     * Helper: Get unserviceable weapons count
     */
    private function getUnserviceableWeaponsCount() {
        $result = Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE `condition` = 'Unserviceable'");
        return $result['count'] ?? 0;
    }
    
    /**
     * Helper: Update requisition status after issue
     */
    private function updateRequisitionStatus($requisitionId, $itemType) {
        // Check if all items in requisition are issued
        $remainingWeapons = Database::fetchOne(
            "SELECT COUNT(*) as count 
             FROM requisition_items ri
             LEFT JOIN weapon_issue_log wil ON ri.id = wil.requisition_item_id
             WHERE ri.requisition_id = ? AND ri.item_type = 'Weapon' AND wil.id IS NULL",
            [$requisitionId]
        )['count'] ?? 0;
        
        $remainingAmmo = Database::fetchOne(
            "SELECT COUNT(*) as count 
             FROM requisition_items ri
             LEFT JOIN ammunition_issue_log ail ON ri.id = ail.requisition_item_id
             WHERE ri.requisition_id = ? AND ri.item_type = 'Ammunition' AND ail.id IS NULL",
            [$requisitionId]
        )['count'] ?? 0;
        
        if ($remainingWeapons == 0 && $remainingAmmo == 0) {
            Database::update('requisitions', ['status' => 'Issued'], 'id = ?', [$requisitionId]);
        } else {
            Database::update('requisitions', ['status' => 'Partially Issued'], 'id = ?', [$requisitionId]);
        }
    }
    
    /**
     * Helper: Get ammunition issue table name (handles different naming conventions)
     */
    private function getAmmoIssueTableName() {
        // Based on your table structure, it's 'ammunition_issue_log'
        $tables = ['ammunition_issue_log', 'ammo_issue_log'];
        
        foreach ($tables as $table) {
            $result = Database::fetchOne("SHOW TABLES LIKE '$table'");
            if ($result) {
                return $table;
            }
        }
        
        // Default
        return 'ammunition_issue_log';
    }
    
    /**
     * Helper: Check if request is AJAX
     */
    protected function isAjax() {
        return !empty($_SERVER['HTTP_X_REQUESTED_WITH']) && 
               strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest';
    }
    
    /**
     * Helper: Send JSON response
     */
    protected function jsonResponse($success, $message, $data = []) {
        header('Content-Type: application/json');
        echo json_encode([
            'success' => $success,
            'message' => $message,
            'data' => $data
        ]);
        exit;
    }
}