<?php
/**
 * Weapons Management Controller
 */
class WeaponsController extends Controller {
    
    public function index() {
        // Check permission
        if (!Auth::can('weapons.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view weapons']);
            return;
        }
        
        $weapons = Database::fetchAll(
            "SELECT wi.*, wt.type_name as weapon_type_name, wc.calibre_name,
                    z.zone_name as custodian_zone_name, cmd.command_name as custodian_command_name,
                    lz.zone_name as lost_zone_name, lcmd.command_name as lost_command_name
             FROM weapons_inventory wi
             LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
             LEFT JOIN weapon_calibres wc ON wi.calibre_id = wc.id
             LEFT JOIN zones z ON wi.custodian_zone_id = z.id
             LEFT JOIN commands cmd ON wi.custodian_command_id = cmd.id
             LEFT JOIN zones lz ON wi.lost_zone_id = lz.id
             LEFT JOIN commands lcmd ON wi.lost_command_id = lcmd.id
             ORDER BY wi.created_at DESC"
        );
        
        if ($weapons === false) $weapons = [];
        
        // Calculate statistics
        $statistics = [
            'total' => count($weapons),
            'issued' => 0,
            'lost' => 0,
            'serviceable' => 0,
            'unserviceable' => 0
        ];
        
        foreach ($weapons as $weapon) {
            if (($weapon['current_location'] ?? '') == 'Issued') {
                $statistics['issued']++;
            } elseif (($weapon['current_location'] ?? '') == 'Lost') {
                $statistics['lost']++;
            }
            if (($weapon['condition'] ?? '') == 'Serviceable') {
                $statistics['serviceable']++;
            } elseif (($weapon['condition'] ?? '') == 'Unserviceable') {
                $statistics['unserviceable']++;
            }
        }
        
        // Get filter options
        $weaponTypes = Database::fetchAll("SELECT id, type_name FROM weapon_types ORDER BY type_name");
        if ($weaponTypes === false) $weaponTypes = [];
        
        $calibres = Database::fetchAll("SELECT id, calibre_name FROM weapon_calibres ORDER BY calibre_name");
        if ($calibres === false) $calibres = [];
        
        $this->view('weapons/index', [
            'weapons' => $weapons,
            'statistics' => $statistics,
            'weaponTypes' => $weaponTypes,
            'calibres' => $calibres
        ]);
    }
    
    public function create() {
        // Check permission
        if (!Auth::can('weapons.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to create weapons']);
            return;
        }
        
        $weaponTypes = Database::fetchAll("SELECT * FROM weapon_types ORDER BY type_name");
        $calibres = Database::fetchAll("SELECT * FROM weapon_calibres ORDER BY calibre_name");
        
        if ($weaponTypes === false) $weaponTypes = [];
        if ($calibres === false) $calibres = [];
        
        $this->view('weapons/create', [
            'weaponTypes' => $weaponTypes,
            'calibres' => $calibres
        ]);
    }
    
  
  public function store() {
    // Check permission
    if (!Auth::can('weapons.create')) {
        $this->redirect('dashboard', ['error' => 'You do not have permission to create weapons']);
        return;
    }
    
    if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
        $this->redirect('weapons/create', ['error' => 'Invalid security token']);
        return;
    }
    
    // Manual validation
    $errors = [];
    
    if (empty($_POST['weapon_id'])) {
        $errors['weapon_id'] = 'Weapon ID is required';
    }
    
    if (empty($_POST['make_model'])) {
        $errors['make_model'] = 'Make/Model is required';
    } elseif (strlen($_POST['make_model']) > 255) {
        $errors['make_model'] = 'Make/Model must not exceed 255 characters';
    }
    
    if (empty($_POST['serial_no'])) {
        $errors['serial_no'] = 'Serial number is required';
    }
    
    if (empty($_POST['source'])) {
        $errors['source'] = 'Source is required';
    }
    
    if (empty($_POST['condition'])) {
        $errors['condition'] = 'Condition is required';
    }
    
    if (empty($_POST['current_location'])) {
        $errors['current_location'] = 'Current location is required';
    }
    
    // Check for duplicate weapon_id
    if (!empty($_POST['weapon_id'])) {
        $existing = Database::fetchOne(
            "SELECT id FROM weapons_inventory WHERE weapon_id = ?",
            [$_POST['weapon_id']]
        );
        if ($existing) {
            $errors['weapon_id'] = 'Weapon ID already exists';
        }
    }
    
    // Check for duplicate serial_no
    if (!empty($_POST['serial_no'])) {
        $existing = Database::fetchOne(
            "SELECT id FROM weapons_inventory WHERE serial_no = ?",
            [$_POST['serial_no']]
        );
        if ($existing) {
            $errors['serial_no'] = 'Serial number already exists';
        }
    }
    
    if (!empty($errors)) {
        Session::set('errors', $errors);
        Session::set('old', $_POST);
        $this->redirect('weapons/create');
        return;
    }
    
    Database::beginTransaction();
    
    try {
        // First, get the table columns to know what fields exist
        $columns = Database::fetchAll("SHOW COLUMNS FROM weapons_inventory");
        $existingColumns = array_column($columns, 'Field');
        
        // Start with base required fields
        $data = [
            'weapon_id' => $_POST['weapon_id'],
            'make_model' => $_POST['make_model'],
            'serial_no' => $_POST['serial_no'],
            'source' => $_POST['source'] === 'Other' ? ($_POST['source_other'] ?? 'Other') : $_POST['source'],
            'condition' => $_POST['condition'],
            'current_location' => $_POST['current_location'] === 'Other' ? ($_POST['current_location_other'] ?? 'Other') : $_POST['current_location'],
            'date_acquired' => !empty($_POST['date_acquired']) ? $_POST['date_acquired'] : null,
            'last_inspection_date' => !empty($_POST['last_inspection_date']) ? $_POST['last_inspection_date'] : null,
            'next_inspection_date' => !empty($_POST['next_inspection_date']) ? $_POST['next_inspection_date'] : null,
            'remarks' => $_POST['remarks'] ?? null,
            'created_by' => Auth::id()
        ];
        
        // Add weapon_type fields if they exist in table
        if (in_array('weapon_type_id', $existingColumns)) {
            $data['weapon_type_id'] = !empty($_POST['weapon_type_id']) && $_POST['weapon_type_id'] !== 'other' ? $_POST['weapon_type_id'] : null;
        }
        if (in_array('weapon_type_other', $existingColumns)) {
            $data['weapon_type_other'] = ($_POST['weapon_type_id'] ?? '') === 'other' ? ($_POST['weapon_type_other'] ?? null) : null;
        }
        
        // Add calibre fields if they exist in table
        if (in_array('calibre_id', $existingColumns)) {
            $data['calibre_id'] = !empty($_POST['calibre_id']) && $_POST['calibre_id'] !== 'other' ? $_POST['calibre_id'] : null;
        }
        if (in_array('calibre_other', $existingColumns)) {
            $data['calibre_other'] = ($_POST['calibre_id'] ?? '') === 'other' ? ($_POST['calibre_other'] ?? null) : null;
        }
        
        // Add current_location_other if it exists
        if (in_array('current_location_other', $existingColumns)) {
            $data['current_location_other'] = $_POST['current_location'] === 'Other' ? ($_POST['current_location_other'] ?? null) : null;
        }
        
        // Add issued fields if current_location is 'Issued' and columns exist
        if (($_POST['current_location'] ?? '') === 'Issued') {
            if (in_array('custodian', $existingColumns)) $data['custodian'] = !empty($_POST['custodian']) ? $_POST['custodian'] : null;
            if (in_array('custodian_rank', $existingColumns)) $data['custodian_rank'] = !empty($_POST['custodian_rank']) ? $_POST['custodian_rank'] : null;
            if (in_array('custodian_nis', $existingColumns)) $data['custodian_nis'] = !empty($_POST['custodian_nis']) ? $_POST['custodian_nis'] : null;
            if (in_array('custodian_phone', $existingColumns)) $data['custodian_phone'] = !empty($_POST['custodian_phone']) ? $_POST['custodian_phone'] : null;
            
            // Handle zone and command IDs and names
            if (in_array('custodian_zone_id', $existingColumns) && !empty($_POST['custodian_zone_id'])) {
                $data['custodian_zone_id'] = $_POST['custodian_zone_id'];
                // Get zone name
                $zone = Database::fetchOne("SELECT zone_name FROM zones WHERE id = ?", [$_POST['custodian_zone_id']]);
                if ($zone && in_array('custodian_zone', $existingColumns)) {
                    $data['custodian_zone'] = $zone['zone_name'];
                }
            }
            
            if (in_array('custodian_command_id', $existingColumns) && !empty($_POST['custodian_command_id'])) {
                $data['custodian_command_id'] = $_POST['custodian_command_id'];
                // Get command name
                $command = Database::fetchOne("SELECT command_name FROM commands WHERE id = ?", [$_POST['custodian_command_id']]);
                if ($command && in_array('custodian_command', $existingColumns)) {
                    $data['custodian_command'] = $command['command_name'];
                }
            }
        }
        
        // Add lost fields if current_location is 'Lost' and columns exist
        if (($_POST['current_location'] ?? '') === 'Lost') {
            if (in_array('lost_by_name', $existingColumns)) $data['lost_by_name'] = !empty($_POST['lost_by_name']) ? $_POST['lost_by_name'] : null;
            if (in_array('lost_by_rank', $existingColumns)) $data['lost_by_rank'] = !empty($_POST['lost_by_rank']) ? $_POST['lost_by_rank'] : null;
            if (in_array('lost_by_nis', $existingColumns)) $data['lost_by_nis'] = !empty($_POST['lost_by_nis']) ? $_POST['lost_by_nis'] : null;
            if (in_array('lost_by_phone', $existingColumns)) $data['lost_by_phone'] = !empty($_POST['lost_by_phone']) ? $_POST['lost_by_phone'] : null;
            if (in_array('loss_date', $existingColumns)) $data['loss_date'] = !empty($_POST['loss_date']) ? $_POST['loss_date'] : null;
            if (in_array('loss_circumstances', $existingColumns)) $data['loss_circumstances'] = !empty($_POST['loss_circumstances']) ? $_POST['loss_circumstances'] : null;
            if (in_array('loss_report_no', $existingColumns)) $data['loss_report_no'] = !empty($_POST['loss_report_no']) ? $_POST['loss_report_no'] : null;
            if (in_array('loss_action_taken', $existingColumns)) $data['loss_action_taken'] = !empty($_POST['loss_action_taken']) ? $_POST['loss_action_taken'] : null;
            
            // Handle zone and command IDs and names for lost
            if (in_array('lost_zone_id', $existingColumns) && !empty($_POST['lost_zone_id'])) {
                $data['lost_zone_id'] = $_POST['lost_zone_id'];
                // Get zone name
                $zone = Database::fetchOne("SELECT zone_name FROM zones WHERE id = ?", [$_POST['lost_zone_id']]);
                if ($zone && in_array('lost_zone', $existingColumns)) {
                    $data['lost_zone'] = $zone['zone_name'];
                }
            }
            
            if (in_array('lost_command_id', $existingColumns) && !empty($_POST['lost_command_id'])) {
                $data['lost_command_id'] = $_POST['lost_command_id'];
                // Get command name
                $command = Database::fetchOne("SELECT command_name FROM commands WHERE id = ?", [$_POST['lost_command_id']]);
                if ($command && in_array('lost_command', $existingColumns)) {
                    $data['lost_command'] = $command['command_name'];
                }
            }
        }
        
        // Debug: Log the data being inserted
        error_log("Weapon Insert Data: " . print_r($data, true));
        
        // Attempt insert
        $weaponId = Database::insert('weapons_inventory', $data);
        
        if (!$weaponId) {
            // Get the last error
            $db = Database::getConnection();
            $error = $db->errorInfo();
            error_log("Database insert error: " . print_r($error, true));
            throw new Exception("Database insert failed: " . ($error[2] ?? 'Unknown error'));
        }
        
        Database::commit();
        
        AuditLogger::logCreate('weapons_inventory', $weaponId, $_POST);
        
        $this->redirect('weapons', ['success' => 'Weapon added successfully']);
        
    } catch (Exception $e) {
        Database::rollBack();
        error_log("Weapon creation error: " . $e->getMessage());
        error_log("Stack trace: " . $e->getTraceAsString());
        
        // Get the actual database error if available
        $db = Database::getConnection();
        $error = $db->errorInfo();
        $detailedError = $error[2] ?? $e->getMessage();
        
        $this->redirect('weapons/create', ['error' => 'Failed to add weapon: ' . $detailedError]);
    }
}
  
    
    public function show($id) {
        // Check permission
        if (!Auth::can('weapons.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view weapons']);
            return;
        }
        
        $weapon = Database::fetchOne(
            "SELECT wi.*, wt.type_name as weapon_type_name, wc.calibre_name,
                    u.full_name as created_by_name,
                    z.zone_name as custodian_zone_name, cmd.command_name as custodian_command_name,
                    lz.zone_name as lost_zone_name, lcmd.command_name as lost_command_name
             FROM weapons_inventory wi
             LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
             LEFT JOIN weapon_calibres wc ON wi.calibre_id = wc.id
             LEFT JOIN users u ON wi.created_by = u.id
             LEFT JOIN zones z ON wi.custodian_zone_id = z.id
             LEFT JOIN commands cmd ON wi.custodian_command_id = cmd.id
             LEFT JOIN zones lz ON wi.lost_zone_id = lz.id
             LEFT JOIN commands lcmd ON wi.lost_command_id = lcmd.id
             WHERE wi.id = ?",
            [$id]
        );
        
        if (!$weapon) {
            $this->redirect('weapons', ['error' => 'Weapon not found']);
            return;
        }
        
        // Get issue history
        $issueHistory = Database::fetchAll(
            "SELECT wil.*, 
                    CONCAT(wil.officer_name, ' (', wil.officer_rank, ')') as officer,
                    req.requisition_number
             FROM weapon_issue_log wil
             LEFT JOIN requisitions req ON wil.requisition_id = req.id
             WHERE wil.weapon_id = ? 
             ORDER BY wil.issue_date DESC",
            [$id]
        );
        if ($issueHistory === false) $issueHistory = [];
        
        AuditLogger::logView('weapons_inventory', $id);
        
        $this->view('weapons/show', [
            'weapon' => $weapon,
            'issueHistory' => $issueHistory
        ]);
    }
    
    public function edit($id) {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit weapons']);
            return;
        }
        
        $weapon = Database::fetchOne("SELECT * FROM weapons_inventory WHERE id = ?", [$id]);
        
        if (!$weapon) {
            $this->redirect('weapons', ['error' => 'Weapon not found']);
            return;
        }
        
        $weaponTypes = Database::fetchAll("SELECT * FROM weapon_types ORDER BY type_name");
        $calibres = Database::fetchAll("SELECT * FROM weapon_calibres ORDER BY calibre_name");
        
        if ($weaponTypes === false) $weaponTypes = [];
        if ($calibres === false) $calibres = [];
        
        // Get zones and commands for dropdowns
        $zones = Database::fetchAll("SELECT id, zone_name FROM zones ORDER BY zone_name");
        if ($zones === false) $zones = [];
        
        $commands = Database::fetchAll("SELECT id, command_name, zone_id FROM commands ORDER BY command_name");
        if ($commands === false) $commands = [];
        
        $this->view('weapons/edit', [
            'weapon' => $weapon,
            'weaponTypes' => $weaponTypes,
            'calibres' => $calibres,
            'zones' => $zones,
            'commands' => $commands
        ]);
    }
    
    public function update($id) {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit weapons']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("weapons/edit/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $oldData = Database::fetchOne("SELECT * FROM weapons_inventory WHERE id = ?", [$id]);
        
        if (!$oldData) {
            $this->redirect('weapons', ['error' => 'Weapon not found']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['weapon_id'])) {
            $errors['weapon_id'] = 'Weapon ID is required';
        }
        
        if (empty($_POST['make_model'])) {
            $errors['make_model'] = 'Make/Model is required';
        } elseif (strlen($_POST['make_model']) > 255) {
            $errors['make_model'] = 'Make/Model must not exceed 255 characters';
        }
        
        if (empty($_POST['serial_no'])) {
            $errors['serial_no'] = 'Serial number is required';
        }
        
        if (empty($_POST['source'])) {
            $errors['source'] = 'Source is required';
        }
        
        if (empty($_POST['condition'])) {
            $errors['condition'] = 'Condition is required';
        }
        
        if (empty($_POST['current_location'])) {
            $errors['current_location'] = 'Current location is required';
        }
        
        // Check for duplicate weapon_id (excluding current)
        if (!empty($_POST['weapon_id']) && $_POST['weapon_id'] !== $oldData['weapon_id']) {
            $existing = Database::fetchOne(
                "SELECT id FROM weapons_inventory WHERE weapon_id = ? AND id != ?",
                [$_POST['weapon_id'], $id]
            );
            if ($existing) {
                $errors['weapon_id'] = 'Weapon ID already exists';
            }
        }
        
        // Check for duplicate serial_no (excluding current)
        if (!empty($_POST['serial_no']) && $_POST['serial_no'] !== $oldData['serial_no']) {
            $existing = Database::fetchOne(
                "SELECT id FROM weapons_inventory WHERE serial_no = ? AND id != ?",
                [$_POST['serial_no'], $id]
            );
            if ($existing) {
                $errors['serial_no'] = 'Serial number already exists';
            }
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            $this->redirect("weapons/edit/$id");
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Get zone names from IDs
            $custodianZoneName = null;
            if (!empty($_POST['custodian_zone_id'])) {
                $zone = Database::fetchOne("SELECT zone_name FROM zones WHERE id = ?", [$_POST['custodian_zone_id']]);
                $custodianZoneName = $zone ? $zone['zone_name'] : null;
            }
            
            $custodianCommandName = null;
            if (!empty($_POST['custodian_command_id'])) {
                $command = Database::fetchOne("SELECT command_name FROM commands WHERE id = ?", [$_POST['custodian_command_id']]);
                $custodianCommandName = $command ? $command['command_name'] : null;
            }
            
            $lostZoneName = null;
            if (!empty($_POST['lost_zone_id'])) {
                $zone = Database::fetchOne("SELECT zone_name FROM zones WHERE id = ?", [$_POST['lost_zone_id']]);
                $lostZoneName = $zone ? $zone['zone_name'] : null;
            }
            
            $lostCommandName = null;
            if (!empty($_POST['lost_command_id'])) {
                $command = Database::fetchOne("SELECT command_name FROM commands WHERE id = ?", [$_POST['lost_command_id']]);
                $lostCommandName = $command ? $command['command_name'] : null;
            }
            
            $data = [
                'weapon_id' => $_POST['weapon_id'],
                'weapon_type_id' => !empty($_POST['weapon_type_id']) && $_POST['weapon_type_id'] !== 'other' ? $_POST['weapon_type_id'] : null,
                'weapon_type_other' => ($_POST['weapon_type_id'] ?? '') === 'other' ? ($_POST['weapon_type_other'] ?? null) : null,
                'make_model' => $_POST['make_model'],
                'serial_no' => $_POST['serial_no'],
                'calibre_id' => !empty($_POST['calibre_id']) && $_POST['calibre_id'] !== 'other' ? $_POST['calibre_id'] : null,
                'calibre_other' => ($_POST['calibre_id'] ?? '') === 'other' ? ($_POST['calibre_other'] ?? null) : null,
                'source' => $_POST['source'] === 'Other' ? ($_POST['source_other'] ?? 'Other') : $_POST['source'],
                'condition' => $_POST['condition'],
                'current_location' => $_POST['current_location'] === 'Other' ? ($_POST['current_location_other'] ?? 'Other') : $_POST['current_location'],
                'current_location_other' => $_POST['current_location'] === 'Other' ? ($_POST['current_location_other'] ?? null) : null,
                'date_acquired' => !empty($_POST['date_acquired']) ? $_POST['date_acquired'] : null,
                'last_inspection_date' => !empty($_POST['last_inspection_date']) ? $_POST['last_inspection_date'] : null,
                'next_inspection_date' => !empty($_POST['next_inspection_date']) ? $_POST['next_inspection_date'] : null,
                'remarks' => $_POST['remarks'] ?? null
            ];
            
            // Add issued fields if current_location is 'Issued'
            if (($_POST['current_location'] ?? '') === 'Issued') {
                $data['custodian'] = !empty($_POST['custodian']) ? $_POST['custodian'] : null;
                $data['custodian_rank'] = !empty($_POST['custodian_rank']) ? $_POST['custodian_rank'] : null;
                $data['custodian_nis'] = !empty($_POST['custodian_nis']) ? $_POST['custodian_nis'] : null;
                $data['custodian_phone'] = !empty($_POST['custodian_phone']) ? $_POST['custodian_phone'] : null;
                $data['custodian_zone'] = $custodianZoneName;
                $data['custodian_zone_id'] = !empty($_POST['custodian_zone_id']) ? $_POST['custodian_zone_id'] : null;
                $data['custodian_command'] = $custodianCommandName;
                $data['custodian_command_id'] = !empty($_POST['custodian_command_id']) ? $_POST['custodian_command_id'] : null;
            } else {
                $data['custodian'] = null;
                $data['custodian_rank'] = null;
                $data['custodian_nis'] = null;
                $data['custodian_phone'] = null;
                $data['custodian_zone'] = null;
                $data['custodian_zone_id'] = null;
                $data['custodian_command'] = null;
                $data['custodian_command_id'] = null;
            }
            
            // Add lost fields if current_location is 'Lost'
            if (($_POST['current_location'] ?? '') === 'Lost') {
                $data['lost_by_name'] = !empty($_POST['lost_by_name']) ? $_POST['lost_by_name'] : null;
                $data['lost_by_rank'] = !empty($_POST['lost_by_rank']) ? $_POST['lost_by_rank'] : null;
                $data['lost_by_nis'] = !empty($_POST['lost_by_nis']) ? $_POST['lost_by_nis'] : null;
                $data['lost_by_phone'] = !empty($_POST['lost_by_phone']) ? $_POST['lost_by_phone'] : null;
                $data['lost_zone'] = $lostZoneName;
                $data['lost_zone_id'] = !empty($_POST['lost_zone_id']) ? $_POST['lost_zone_id'] : null;
                $data['lost_command'] = $lostCommandName;
                $data['lost_command_id'] = !empty($_POST['lost_command_id']) ? $_POST['lost_command_id'] : null;
                $data['loss_date'] = !empty($_POST['loss_date']) ? $_POST['loss_date'] : null;
                $data['loss_circumstances'] = !empty($_POST['loss_circumstances']) ? $_POST['loss_circumstances'] : null;
                $data['loss_report_no'] = !empty($_POST['loss_report_no']) ? $_POST['loss_report_no'] : null;
                $data['loss_action_taken'] = !empty($_POST['loss_action_taken']) ? $_POST['loss_action_taken'] : null;
            } else {
                $data['lost_by_name'] = null;
                $data['lost_by_rank'] = null;
                $data['lost_by_nis'] = null;
                $data['lost_by_phone'] = null;
                $data['lost_zone'] = null;
                $data['lost_zone_id'] = null;
                $data['lost_command'] = null;
                $data['lost_command_id'] = null;
                $data['loss_date'] = null;
                $data['loss_circumstances'] = null;
                $data['loss_report_no'] = null;
                $data['loss_action_taken'] = null;
            }
            
            $result = Database::update('weapons_inventory', $data, 'id = ?', [$id]);
            
            if ($result === false) {
                throw new Exception("Failed to update weapon record");
            }
            
            Database::commit();
            
            AuditLogger::logUpdate('weapons_inventory', $id, $oldData, $_POST);
            
            $this->redirect("weapons/show/$id", ['success' => 'Weapon updated successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon update error: " . $e->getMessage());
            error_log("Stack trace: " . $e->getTraceAsString());
            $this->redirect("weapons/edit/$id", ['error' => 'Failed to update weapon: ' . $e->getMessage()]);
        }
    }
    
    public function delete($id) {
        // Check permission
        if (!Auth::can('weapons.delete')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete weapons']);
            return;
        }
        
        $weapon = Database::fetchOne("SELECT * FROM weapons_inventory WHERE id = ?", [$id]);
        
        if (!$weapon) {
            $this->redirect('weapons', ['error' => 'Weapon not found']);
            return;
        }
        
        // Check if weapon has issue history
        $issueCount = Database::fetchOne(
            "SELECT COUNT(*) as count FROM weapon_issue_log WHERE weapon_id = ?",
            [$id]
        )['count'] ?? 0;
        
        if ($issueCount > 0) {
            $this->redirect('weapons', ['error' => 'Cannot delete weapon with issue history. Consider marking as decommissioned instead.']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::delete('weapons_inventory', 'id = ?', [$id]);
            
            Database::commit();
            
            AuditLogger::logDelete('weapons_inventory', $id, $weapon);
            
            $this->redirect('weapons', ['success' => 'Weapon deleted successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon deletion error: " . $e->getMessage());
            $this->redirect('weapons', ['error' => 'Failed to delete weapon: ' . $e->getMessage()]);
        }
    }
    
    public function dashboard() {
        // Check permission
        if (!Auth::can('weapons.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view weapons dashboard']);
            return;
        }
        
        $stats = [
            'total' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory")['count'] ?? 0,
            'issued' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE current_location = 'Issued'")['count'] ?? 0,
            'lost' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE current_location = 'Lost'")['count'] ?? 0,
            'serviceable' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE `condition` = 'Serviceable'")['count'] ?? 0,
            'unserviceable' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE `condition` = 'Unserviceable'")['count'] ?? 0,
            'in_repair' => Database::fetchOne("SELECT COUNT(*) as count FROM weapons_inventory WHERE current_location = 'In Repair'")['count'] ?? 0
        ];
        
        $byType = Database::fetchAll(
            "SELECT COALESCE(wt.type_name, 'Other') as type, COUNT(*) as count 
             FROM weapons_inventory wi
             LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
             GROUP BY COALESCE(wt.type_name, 'Other')
             ORDER BY count DESC"
        );
        if ($byType === false) $byType = [];
        
        $recentIssues = Database::fetchAll(
            "SELECT wil.*, wi.make_model, wi.weapon_id
             FROM weapon_issue_log wil
             JOIN weapons_inventory wi ON wil.weapon_id = wi.id
             ORDER BY wil.issue_date DESC
             LIMIT 10"
        );
        if ($recentIssues === false) $recentIssues = [];
        
        $this->view('weapons/dashboard', [
            'stats' => $stats,
            'byType' => $byType,
            'recentIssues' => $recentIssues
        ]);
    }
    
    public function types() {
        // Check permission
        if (!Auth::can('weapons.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to manage weapon types']);
            return;
        }
        
        $types = Database::fetchAll("SELECT * FROM weapon_types ORDER BY type_name");
        if ($types === false) $types = [];
        
        $this->view('weapons/types', ['types' => $types]);
    }
    
    public function storeType() {
        // Check permission
        if (!Auth::can('weapons.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to manage weapon types']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect('weapons/types', ['error' => 'Invalid security token']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['type_name'])) {
            $errors['type_name'] = 'Type name is required';
        }
        
        // Check for duplicate
        if (!empty($_POST['type_name'])) {
            $existing = Database::fetchOne(
                "SELECT id FROM weapon_types WHERE type_name = ?",
                [$_POST['type_name']]
            );
            if ($existing) {
                $errors['type_name'] = 'Weapon type already exists';
            }
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            $this->redirect('weapons/types');
            return;
        }
        
        try {
            Database::insert('weapon_types', [
                'type_name' => $_POST['type_name'],
                'description' => $_POST['description'] ?? null,
                'default_calibre' => $_POST['default_calibre'] ?? null
            ]);
            
            $this->redirect('weapons/types', ['success' => 'Weapon type added successfully']);
            
        } catch (Exception $e) {
            error_log("Weapon type creation error: " . $e->getMessage());
            $this->redirect('weapons/types', ['error' => 'Failed to add weapon type']);
        }
    }
    
    public function deleteType($id) {
        // Check permission
        if (!Auth::can('weapons.delete')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete weapon types']);
            return;
        }
        
        $type = Database::fetchOne("SELECT * FROM weapon_types WHERE id = ?", [$id]);
        
        if (!$type) {
            $this->redirect('weapons/types', ['error' => 'Weapon type not found']);
            return;
        }
        
        // Check if type is in use
        $inUse = Database::fetchOne(
            "SELECT COUNT(*) as count FROM weapons_inventory WHERE weapon_type_id = ?",
            [$id]
        )['count'] ?? 0;
        
        if ($inUse > 0) {
            $this->redirect('weapons/types', ['error' => 'Cannot delete weapon type that is in use']);
            return;
        }
        
        try {
            Database::delete('weapon_types', 'id = ?', [$id]);
            $this->redirect('weapons/types', ['success' => 'Weapon type deleted successfully']);
        } catch (Exception $e) {
            error_log("Weapon type deletion error: " . $e->getMessage());
            $this->redirect('weapons/types', ['error' => 'Failed to delete weapon type']);
        }
    }
    
    public function calibres() {
        // Check permission
        if (!Auth::can('weapons.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to manage calibres']);
            return;
        }
        
        $calibres = Database::fetchAll("SELECT * FROM weapon_calibres ORDER BY calibre_name");
        if ($calibres === false) $calibres = [];
        
        $this->view('weapons/calibres', ['calibres' => $calibres]);
    }
    
    public function storeCalibre() {
        // Check permission
        if (!Auth::can('weapons.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to manage calibres']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect('weapons/calibres', ['error' => 'Invalid security token']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['calibre_name'])) {
            $errors['calibre_name'] = 'Calibre name is required';
        }
        
        // Check for duplicate
        if (!empty($_POST['calibre_name'])) {
            $existing = Database::fetchOne(
                "SELECT id FROM weapon_calibres WHERE calibre_name = ?",
                [$_POST['calibre_name']]
            );
            if ($existing) {
                $errors['calibre_name'] = 'Calibre already exists';
            }
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            $this->redirect('weapons/calibres');
            return;
        }
        
        try {
            Database::insert('weapon_calibres', [
                'calibre_name' => $_POST['calibre_name'],
                'description' => $_POST['description'] ?? null
            ]);
            
            $this->redirect('weapons/calibres', ['success' => 'Calibre added successfully']);
            
        } catch (Exception $e) {
            error_log("Calibre creation error: " . $e->getMessage());
            $this->redirect('weapons/calibres', ['error' => 'Failed to add calibre']);
        }
    }
    
    public function deleteCalibre($id) {
        // Check permission
        if (!Auth::can('weapons.delete')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete calibres']);
            return;
        }
        
        $calibre = Database::fetchOne("SELECT * FROM weapon_calibres WHERE id = ?", [$id]);
        
        if (!$calibre) {
            $this->redirect('weapons/calibres', ['error' => 'Calibre not found']);
            return;
        }
        
        // Check if calibre is in use
        $inUse = Database::fetchOne(
            "SELECT COUNT(*) as count FROM weapons_inventory WHERE calibre_id = ?",
            [$id]
        )['count'] ?? 0;
        
        if ($inUse > 0) {
            $this->redirect('weapons/calibres', ['error' => 'Cannot delete calibre that is in use']);
            return;
        }
        
        try {
            Database::delete('weapon_calibres', 'id = ?', [$id]);
            $this->redirect('weapons/calibres', ['success' => 'Calibre deleted successfully']);
        } catch (Exception $e) {
            error_log("Calibre deletion error: " . $e->getMessage());
            $this->redirect('weapons/calibres', ['error' => 'Failed to delete calibre']);
        }
    }
    
    public function issueLog() {
        // Check permission
        if (!Auth::can('weapons.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view issue logs']);
            return;
        }
        
        $issues = Database::fetchAll(
            "SELECT wil.*, wi.weapon_id, wi.make_model, req.requisition_number
             FROM weapon_issue_log wil
             JOIN weapons_inventory wi ON wil.weapon_id = wi.id
             LEFT JOIN requisitions req ON wil.requisition_id = req.id
             ORDER BY wil.issue_date DESC
             LIMIT 100"
        );
        
        if ($issues === false) $issues = [];
        
        $this->view('weapons/issue_log', ['issues' => $issues]);
    }
    
    public function createIssue() {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to issue weapons']);
            return;
        }
        
        // Get available weapons
        $weapons = 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 != 'Issued' AND wi.current_location != 'Lost'
             ORDER BY wi.weapon_id"
        );
        
        if ($weapons === false) $weapons = [];
        
        $this->view('weapons/issue_create', ['weapons' => $weapons]);
    }
    
    public function storeIssue() {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to issue weapons']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect('weapons/issue', ['error' => 'Invalid security token']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['weapon_id'])) {
            $errors['weapon_id'] = 'Weapon is required';
        }
        
        if (empty($_POST['officer_name'])) {
            $errors['officer_name'] = 'Officer name is required';
        }
        
        if (empty($_POST['officer_rank'])) {
            $errors['officer_rank'] = 'Officer rank is required';
        }
        
        if (empty($_POST['unit'])) {
            $errors['unit'] = 'Unit is required';
        }
        
        if (empty($_POST['purpose'])) {
            $errors['purpose'] = 'Purpose is required';
        }
        
        if (empty($_POST['approved_by'])) {
            $errors['approved_by'] = 'Approving officer is required';
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            Session::set('old', $_POST);
            $this->redirect('weapons/issue');
            return;
        }
        
        Database::beginTransaction();
        
        try {
            $issueId = Database::insert('weapon_issue_log', [
                'weapon_id' => $_POST['weapon_id'],
                'requisition_id' => $_POST['requisition_id'] ?? null,
                'issue_date' => $_POST['issue_date'] ?? date('Y-m-d'),
                'officer_name' => $_POST['officer_name'],
                'officer_rank' => $_POST['officer_rank'],
                'officer_nis' => $_POST['officer_nis'] ?? null,
                'officer_phone' => $_POST['officer_phone'] ?? null,
                'unit' => $_POST['unit'],
                'purpose' => $_POST['purpose'],
                'approved_by' => $_POST['approved_by'],
                'issued_by' => Auth::id(),
                'expected_return_date' => $_POST['expected_return_date'] ?? null,
                'status' => 'Issued',
                'remarks' => $_POST['remarks'] ?? null
            ]);
            
            if (!$issueId) {
                throw new Exception("Failed to create issue record");
            }
            
            // Update weapon location and custodian info
            Database::update('weapons_inventory', [
                'current_location' => 'Issued',
                'custodian' => $_POST['officer_name'],
                'custodian_rank' => $_POST['officer_rank'],
                'custodian_nis' => $_POST['officer_nis'] ?? null,
                'custodian_phone' => $_POST['officer_phone'] ?? null
            ], 'id = ?', [$_POST['weapon_id']]);
            
            Database::commit();
            
            AuditLogger::logCreate('weapon_issue_log', $issueId, $_POST);
            
            $this->redirect('weapons/issue-log', ['success' => 'Weapon issued successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon issue error: " . $e->getMessage());
            $this->redirect('weapons/issue', ['error' => 'Failed to issue weapon: ' . $e->getMessage()]);
        }
    }
    
    public function returnWeapon($id) {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to return weapons']);
            return;
        }
        
        $issue = Database::fetchOne(
            "SELECT wil.*, wi.id as weapon_id, wi.weapon_id as weapon_code
             FROM weapon_issue_log wil
             JOIN weapons_inventory wi ON wil.weapon_id = wi.id
             WHERE wil.id = ?",
            [$id]
        );
        
        if (!$issue) {
            $this->redirect('weapons/issue-log', ['error' => 'Issue record not found']);
            return;
        }
        
        $this->view('weapons/return', ['issue' => $issue]);
    }
    
    public function processReturn($id) {
        // Check permission
        if (!Auth::can('weapons.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to return weapons']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("weapons/return/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $issue = Database::fetchOne("SELECT * FROM weapon_issue_log WHERE id = ?", [$id]);
        
        if (!$issue) {
            $this->redirect('weapons/issue-log', ['error' => 'Issue record not found']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            Database::update('weapon_issue_log', [
                'actual_return_date' => $_POST['return_date'] ?? date('Y-m-d'),
                'return_condition' => $_POST['return_condition'],
                'status' => 'Returned',
                'remarks' => $_POST['remarks'] ?? null
            ], 'id = ?', [$id]);
            
            // Update weapon location back to Armoury and clear custodian fields
            Database::update('weapons_inventory', [
                'current_location' => 'Armoury',
                'custodian' => null,
                'custodian_rank' => null,
                'custodian_nis' => null,
                'custodian_phone' => null,
                'custodian_zone' => null,
                'custodian_zone_id' => null,
                'custodian_command' => null,
                'custodian_command_id' => null
            ], 'id = ?', [$issue['weapon_id']]);
            
            Database::commit();
            
            AuditLogger::logUpdate('weapon_issue_log', $id, $issue, $_POST);
            
            $this->redirect('weapons/issue-log', ['success' => 'Weapon returned successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Weapon return error: " . $e->getMessage());
            $this->redirect("weapons/return/$id", ['error' => 'Failed to process return: ' . $e->getMessage()]);
        }
    }
    
    public function export() {
        // Check permission
        if (!Auth::can('reports.export')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to export data']);
            return;
        }
        
        $weapons = Database::fetchAll(
            "SELECT wi.*, wt.type_name as weapon_type_name, wc.calibre_name,
                    z.zone_name as custodian_zone_name, cmd.command_name as custodian_command_name,
                    lz.zone_name as lost_zone_name, lcmd.command_name as lost_command_name
             FROM weapons_inventory wi
             LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
             LEFT JOIN weapon_calibres wc ON wi.calibre_id = wc.id
             LEFT JOIN zones z ON wi.custodian_zone_id = z.id
             LEFT JOIN commands cmd ON wi.custodian_command_id = cmd.id
             LEFT JOIN zones lz ON wi.lost_zone_id = lz.id
             LEFT JOIN commands lcmd ON wi.lost_command_id = lcmd.id
             ORDER BY wi.created_at DESC"
        );
        
        if ($weapons === false) $weapons = [];
        
        $filename = 'weapons_inventory_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        // Headers
        fputcsv($output, [
            'Weapon ID', 'Type', 'Make/Model', 'Serial Number', 'Calibre',
            'Source', 'Condition', 'Location', 'Custodian', 'Custodian Rank',
            'Custodian NIS', 'Custodian Phone', 'Custodian Zone', 'Custodian Command',
            'Lost By Name', 'Lost By Rank', 'Lost By NIS', 'Lost By Phone',
            'Lost Zone', 'Lost Command', 'Loss Date', 'Loss Circumstances', 
            'Loss Report No', 'Loss Action Taken', 'Date Acquired', 
            'Last Inspection', 'Next Inspection', 'Remarks', 'Created At'
        ]);
        
        // Data
        foreach ($weapons as $w) {
            fputcsv($output, [
                $w['weapon_id'] ?? '',
                $w['weapon_type_name'] ?? $w['weapon_type_other'] ?? '',
                $w['make_model'] ?? '',
                $w['serial_no'] ?? '',
                $w['calibre_name'] ?? $w['calibre_other'] ?? '',
                $w['source'] ?? '',
                $w['condition'] ?? '',
                $w['current_location'] ?? '',
                $w['custodian'] ?? '',
                $w['custodian_rank'] ?? '',
                $w['custodian_nis'] ?? '',
                $w['custodian_phone'] ?? '',
                $w['custodian_zone_name'] ?? $w['custodian_zone'] ?? '',
                $w['custodian_command_name'] ?? $w['custodian_command'] ?? '',
                $w['lost_by_name'] ?? '',
                $w['lost_by_rank'] ?? '',
                $w['lost_by_nis'] ?? '',
                $w['lost_by_phone'] ?? '',
                $w['lost_zone_name'] ?? $w['lost_zone'] ?? '',
                $w['lost_command_name'] ?? $w['lost_command'] ?? '',
                $w['loss_date'] ?? '',
                $w['loss_circumstances'] ?? '',
                $w['loss_report_no'] ?? '',
                $w['loss_action_taken'] ?? '',
                $w['date_acquired'] ?? '',
                $w['last_inspection_date'] ?? '',
                $w['next_inspection_date'] ?? '',
                $w['remarks'] ?? '',
                $w['created_at'] ?? ''
            ]);
        }
        
        fclose($output);
        
        AuditLogger::logExport('weapons', 'csv');
        exit;
    }
}