<?php
/**
 * Reports Controller
 */
class ReportController extends Controller {
    
    public function index() {
        // Check permission using Auth::can()
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $this->view('reports/index');
    }
    
    public function assets() {
        // Check permission using Auth::can()
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $type = $_GET['type'] ?? 'summary';
        $format = $_GET['format'] ?? 'html';
        $startDate = $_GET['start_date'] ?? date('Y-m-01');
        $endDate = $_GET['end_date'] ?? date('Y-m-d');
        
        // Initialize data array with defaults
        $data = $this->getAssetReportData($type, $startDate, $endDate);
        
        // Ensure data is always an array
        if ($data === false || $data === null) {
            $data = [];
        }
        
        if ($format === 'csv') {
            $this->exportAssetReportCSV($data, $type);
        } elseif ($format === 'pdf') {
            $this->exportAssetReportPDF($data, $type);
        } else {
            $this->view('reports/assets', [
                'data' => $data,
                'type' => $type,
                'startDate' => $startDate,
                'endDate' => $endDate
            ]);
        }
    }
    
    public function weapons() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $type = $_GET['type'] ?? 'inventory';
        $format = $_GET['format'] ?? 'html';
        
        $data = $this->getWeaponsReportData($type);
        
        // Ensure data is always an array
        if ($data === false || $data === null) {
            $data = [];
        }
        
        if ($format === 'csv') {
            $this->exportWeaponsReportCSV($data, $type);
        } elseif ($format === 'pdf') {
            $this->exportWeaponsReportPDF($data, $type);
        } else {
            $this->view('reports/weapons', [
                'data' => $data,
                'type' => $type
            ]);
        }
    }
    
    public function ammunition() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $type = $_GET['type'] ?? 'inventory';
        $format = $_GET['format'] ?? 'html';
        
        $data = $this->getAmmunitionReportData($type);
        
        // Ensure data is always an array
        if ($data === false || $data === null) {
            $data = [];
        }
        
        if ($format === 'csv') {
            $this->exportAmmunitionReportCSV($data, $type);
        } elseif ($format === 'pdf') {
            $this->exportAmmunitionReportPDF($data, $type);
        } else {
            $this->view('reports/ammunition', [
                'data' => $data,
                'type' => $type
            ]);
        }
    }
    
    public function fleet() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $type = $_GET['type'] ?? 'summary';
        $format = $_GET['format'] ?? 'html';
        
        $data = $this->getFleetReportData($type);
        
        // Ensure data is always an array
        if ($data === false || $data === null) {
            $data = [];
        }
        
        if ($format === 'csv') {
            $this->exportFleetReportCSV($data, $type);
        } elseif ($format === 'pdf') {
            $this->exportFleetReportPDF($data, $type);
        } else {
            $this->view('reports/fleet', [
                'data' => $data,
                'type' => $type
            ]);
        }
    }
    
    public function audit() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $quarter = $_GET['quarter'] ?? 'Q' . ceil(date('n') / 3);
        $year = $_GET['year'] ?? date('Y');
        $format = $_GET['format'] ?? 'html';
        
        $data = $this->getAuditReportData($quarter, $year);
        
        // Ensure data is always an array
        if ($data === false || $data === null) {
            $data = [];
        }
        
        if ($format === 'csv') {
            $this->exportAuditReportCSV($data);
        } elseif ($format === 'pdf') {
            $this->exportAuditReportPDF($data);
        } else {
            $this->view('reports/audit', [
                'data' => $data,
                'quarter' => $quarter,
                'year' => $year
            ]);
        }
    }
    
    public function summary() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $data = [
            'total_assets' => $this->getTotalAssets(),
            'total_value' => $this->getTotalValue(),
            'assets_by_type' => $this->getAssetsByType(),
            'assets_by_zone' => $this->getAssetsByZone(),
            'recent_additions' => $this->getRecentAdditions(),
            'expiring_items' => $this->getExpiringItems()
        ];
        
        $this->view('reports/summary', ['data' => $data]);
    }
    
    public function save() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to save reports']);
            return;
        }
        
        // Check CSRF token - handle case where Security class might not exist
        $csrfToken = $_POST['csrf_token'] ?? '';
        $validToken = false;
        
        if (class_exists('Security') && method_exists('Security', 'validateCsrfToken')) {
            $validToken = Security::validateCsrfToken($csrfToken);
        } else {
            // Simple session-based CSRF check
            $validToken = isset($_SESSION['csrf_token']) && $_SESSION['csrf_token'] === $csrfToken;
        }
        
        if (!$validToken) {
            $this->redirect('reports', ['error' => 'Invalid security token']);
            return;
        }
        
        $reportName = $_POST['report_name'] ?? '';
        $reportType = $_POST['report_type'] ?? '';
        $parameters = $_POST['parameters'] ?? '{}';
        
        if (empty($reportName) || empty($reportType)) {
            $this->redirect('reports', ['error' => 'Report name and type are required']);
            return;
        }
        
        try {
            // Check if saved_reports table exists, if not create it
            $this->ensureSavedReportsTable();
            
            $result = Database::insert('saved_reports', [
                'report_name' => $reportName,
                'report_type' => $reportType,
                'parameters' => is_array($parameters) ? json_encode($parameters) : $parameters,
                'created_by' => Auth::id(),
                'created_at' => date('Y-m-d H:i:s')
            ]);
            
            if ($result) {
                $_SESSION['success'] = 'Report saved successfully';
            } else {
                $_SESSION['error'] = 'Failed to save report';
            }
            
        } catch (Exception $e) {
            error_log("Save report error: " . $e->getMessage());
            $_SESSION['error'] = 'Failed to save report: ' . $e->getMessage();
        }
        
        $this->redirect('reports');
    }
    
    public function saved() {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        // Check if saved_reports table exists
        $this->ensureSavedReportsTable();
        
        $reports = Database::fetchAll(
            "SELECT * FROM saved_reports WHERE created_by = ? ORDER BY created_at DESC",
            [Auth::id()]
        );
        
        if ($reports === false) {
            $reports = [];
        }
        
        $this->view('reports/saved', ['reports' => $reports]);
    }
    
    public function load($id) {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view reports']);
            return;
        }
        
        $report = Database::fetchOne("SELECT * FROM saved_reports WHERE id = ?", [$id]);
        
        if (!$report || $report['created_by'] != Auth::id()) {
            $_SESSION['error'] = 'Report not found';
            $this->redirect('reports/saved');
            return;
        }
        
        $parameters = json_decode($report['parameters'], true);
        if (!is_array($parameters)) {
            $parameters = [];
        }
        
        switch ($report['report_type']) {
            case 'assets':
                $this->redirect('reports/assets?' . http_build_query($parameters));
                break;
            case 'weapons':
                $this->redirect('reports/weapons?' . http_build_query($parameters));
                break;
            case 'ammunition':
                $this->redirect('reports/ammunition?' . http_build_query($parameters));
                break;
            case 'fleet':
                $this->redirect('reports/fleet?' . http_build_query($parameters));
                break;
            default:
                $this->redirect('reports');
        }
    }
    
    public function delete($id) {
        if (!Auth::can('reports.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete reports']);
            return;
        }
        
        $report = Database::fetchOne("SELECT * FROM saved_reports WHERE id = ?", [$id]);
        
        if (!$report || $report['created_by'] != Auth::id()) {
            $_SESSION['error'] = 'Report not found';
            $this->redirect('reports/saved');
            return;
        }
        
        try {
            $result = Database::delete('saved_reports', 'id = ?', [$id]);
            
            if ($result) {
                $_SESSION['success'] = 'Report deleted successfully';
            } else {
                $_SESSION['error'] = 'Failed to delete report';
            }
        } catch (Exception $e) {
            error_log("Delete report error: " . $e->getMessage());
            $_SESSION['error'] = 'Failed to delete report';
        }
        
        $this->redirect('reports/saved');
    }
    
    /**
     * Ensure saved_reports table exists
     */
    private function ensureSavedReportsTable() {
        try {
            // Check if table exists
            $result = Database::fetchOne("SHOW TABLES LIKE 'saved_reports'");
            if (!$result) {
                // Create saved_reports table
                $sql = "CREATE TABLE IF NOT EXISTS saved_reports (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    report_name VARCHAR(255) NOT NULL,
                    report_type VARCHAR(50) NOT NULL,
                    parameters TEXT,
                    created_by INT NOT NULL,
                    created_at DATETIME NOT NULL,
                    INDEX idx_created_by (created_by)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
                
                Database::query($sql);
            }
        } catch (Exception $e) {
            error_log("Error ensuring saved_reports table: " . $e->getMessage());
        }
    }
    
    // =============================================
    // DATA COLLECTION METHODS
    // =============================================
    
    private function getAssetReportData($type, $startDate, $endDate) {
        $data = [];
        
        try {
            switch ($type) {
                case 'summary':
                    $data['land'] = Database::fetchAll(
                        "SELECT * FROM land_assets WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    
                    $data['buildings'] = Database::fetchAll(
                        "SELECT * FROM building_assets WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    
                    $data['rented'] = Database::fetchAll(
                        "SELECT * FROM rented_properties WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    
                    $data['movable'] = Database::fetchAll(
                        "SELECT * FROM movable_assets WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    
                    $data['ict'] = Database::fetchAll(
                        "SELECT * FROM ict_assets WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                case 'land':
                    $data = Database::fetchAll(
                        "SELECT la.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM land_assets la
                         LEFT JOIN states s ON la.state_id = s.id
                         LEFT JOIN lgas l ON la.lga_id = l.id
                         LEFT JOIN zones z ON la.zone_id = z.id
                         LEFT JOIN commands c ON la.command_id = c.id
                         WHERE la.created_at BETWEEN ? AND ?
                         ORDER BY la.created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                case 'buildings':
                    $data = Database::fetchAll(
                        "SELECT ba.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM building_assets ba
                         LEFT JOIN states s ON ba.state_id = s.id
                         LEFT JOIN lgas l ON ba.lga_id = l.id
                         LEFT JOIN zones z ON ba.zone_id = z.id
                         LEFT JOIN commands c ON ba.command_id = c.id
                         WHERE ba.created_at BETWEEN ? AND ?
                         ORDER BY ba.created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                case 'rented':
                    $data = Database::fetchAll(
                        "SELECT rp.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM rented_properties rp
                         LEFT JOIN states s ON rp.state_id = s.id
                         LEFT JOIN lgas l ON rp.lga_id = l.id
                         LEFT JOIN zones z ON rp.zone_id = z.id
                         LEFT JOIN commands c ON rp.command_id = c.id
                         WHERE rp.created_at BETWEEN ? AND ?
                         ORDER BY rp.created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                case 'movable':
                    $data = Database::fetchAll(
                        "SELECT ma.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM movable_assets ma
                         LEFT JOIN states s ON ma.state_id = s.id
                         LEFT JOIN lgas l ON ma.lga_id = l.id
                         LEFT JOIN zones z ON ma.zone_id = z.id
                         LEFT JOIN commands c ON ma.command_id = c.id
                         WHERE ma.created_at BETWEEN ? AND ?
                         ORDER BY ma.created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                case 'ict':
                    $data = Database::fetchAll(
                        "SELECT ia.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM ict_assets ia
                         LEFT JOIN states s ON ia.state_id = s.id
                         LEFT JOIN lgas l ON ia.lga_id = l.id
                         LEFT JOIN zones z ON ia.zone_id = z.id
                         LEFT JOIN commands c ON ia.command_id = c.id
                         WHERE ia.created_at BETWEEN ? AND ?
                         ORDER BY ia.created_at DESC",
                        [$startDate, $endDate . ' 23:59:59']
                    ) ?: [];
                    break;
                    
                default:
                    $data = [];
            }
        } catch (Exception $e) {
            error_log("Error in getAssetReportData: " . $e->getMessage());
            $data = [];
        }
        
        return $data;
    }
    
    private function getWeaponsReportData($type) {
        try {
            switch ($type) {
                case 'inventory':
                    return Database::fetchAll(
                        "SELECT wi.*, wt.type_name, wc.calibre_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
                         ORDER BY wi.created_at DESC"
                    ) ?: [];
                    
                case 'issued':
                    return Database::fetchAll(
                        "SELECT wi.*, wt.type_name, wil.*
                         FROM weapon_issue_log wil
                         JOIN weapons_inventory wi ON wil.weapon_id = wi.id
                         LEFT JOIN weapon_types wt ON wi.weapon_type_id = wt.id
                         WHERE wil.status = 'Issued'
                         ORDER BY wil.issue_date DESC"
                    ) ?: [];
                    
                case 'unserviceable':
                    return Database::fetchAll(
                        "SELECT wi.*, wt.type_name, wc.calibre_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
                         WHERE wi.condition = 'Unserviceable'
                         ORDER BY wi.created_at DESC"
                    ) ?: [];
                    
                case 'by_type':
                    return 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"
                    ) ?: [];
                    
                default:
                    return [];
            }
        } catch (Exception $e) {
            error_log("Error in getWeaponsReportData: " . $e->getMessage());
            return [];
        }
    }
    
    private function getAmmunitionReportData($type) {
        try {
            switch ($type) {
                case 'inventory':
                    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
                         ORDER BY ai.created_at DESC"
                    ) ?: [];
                    
                case 'expiring':
                    return Database::fetchAll(
                        "SELECT ai.*, at.ammo_type, ac.calibre,
                                DATEDIFF(expiry_date, CURDATE()) as days_remaining
                         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.expiry_date IS NOT NULL
                         AND ai.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)
                         ORDER BY ai.expiry_date ASC"
                    ) ?: [];
                    
                case 'low_stock':
                    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 < 100
                         ORDER BY ai.balance ASC"
                    ) ?: [];
                    
                case 'issued':
                    return Database::fetchAll(
                        "SELECT ail.*, at.ammo_type, ac.calibre
                         FROM ammunition_issue_log ail
                         LEFT JOIN ammunition_inventory ai ON ail.ammo_id = ai.id
                         LEFT JOIN ammunition_types at ON ai.ammo_type_id = at.id
                         LEFT JOIN ammunition_calibres ac ON ai.calibre_id = ac.id
                         ORDER BY ail.issue_date DESC
                         LIMIT 100"
                    ) ?: [];
                    
                default:
                    return [];
            }
        } catch (Exception $e) {
            error_log("Error in getAmmunitionReportData: " . $e->getMessage());
            return [];
        }
    }
    
    private function getFleetReportData($type) {
        try {
            switch ($type) {
                case 'summary':
                    return [
                        'vehicles' => Database::fetchAll("SELECT * FROM vehicle_assets ORDER BY created_at DESC") ?: [],
                        'aircraft' => Database::fetchAll("SELECT * FROM aircraft_assets ORDER BY created_at DESC") ?: [],
                        'marine' => Database::fetchAll("SELECT * FROM marine_assets ORDER BY created_at DESC") ?: [],
                        'motorcycles' => Database::fetchAll("SELECT * FROM motorcycle_assets ORDER BY created_at DESC") ?: []
                    ];
                    
                case 'vehicles':
                    return Database::fetchAll(
                        "SELECT v.*, s.state_name, l.lga_name, z.zone_name, c.command_name
                         FROM vehicle_assets v
                         LEFT JOIN states s ON v.state_id = s.id
                         LEFT JOIN lgas l ON v.lga_id = l.id
                         LEFT JOIN zones z ON v.zone_id = z.id
                         LEFT JOIN commands c ON v.command_id = c.id
                         ORDER BY v.created_at DESC"
                    ) ?: [];
                    
                case 'maintenance':
                    return Database::fetchAll(
                        "SELECT v.*, 
                                DATEDIFF(next_service_date, CURDATE()) as days_to_service
                         FROM vehicle_assets v
                         WHERE next_service_date IS NOT NULL
                         AND next_service_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                         ORDER BY next_service_date ASC"
                    ) ?: [];
                    
                case 'insurance':
                    return Database::fetchAll(
                        "SELECT v.*,
                                DATEDIFF(insurance_expiry, CURDATE()) as days_to_expiry
                         FROM vehicle_assets v
                         WHERE insurance_expiry IS NOT NULL
                         AND insurance_expiry <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)
                         ORDER BY insurance_expiry ASC"
                    ) ?: [];
                    
                default:
                    return [];
            }
        } catch (Exception $e) {
            error_log("Error in getFleetReportData: " . $e->getMessage());
            return [];
        }
    }
    
    private function getAuditReportData($quarter, $year) {
        try {
            $audits = Database::fetchAll(
                "SELECT qa.*, 
                        u.full_name as auditor_name,
                        c.command_name
                 FROM quarterly_audits qa
                 LEFT JOIN users u ON qa.created_by = u.id
                 LEFT JOIN commands c ON qa.command_id = c.id
                 WHERE qa.quarter = ? AND qa.year = ?
                 ORDER BY qa.audit_date DESC",
                [$quarter, $year]
            ) ?: [];
            
            foreach ($audits as &$audit) {
                $audit['weapons'] = Database::fetchAll(
                    "SELECT * FROM audit_weapons WHERE audit_id = ?",
                    [$audit['id']]
                ) ?: [];
                
                $audit['ammunition'] = Database::fetchAll(
                    "SELECT * FROM audit_ammunition WHERE audit_id = ?",
                    [$audit['id']]
                ) ?: [];
                
                $audit['missing'] = Database::fetchAll(
                    "SELECT * FROM audit_missing_weapons WHERE audit_id = ?",
                    [$audit['id']]
                ) ?: [];
            }
            
            return $audits;
        } catch (Exception $e) {
            error_log("Error in getAuditReportData: " . $e->getMessage());
            return [];
        }
    }
    
    private function getTotalAssets() {
        $tables = ['land_assets', 'building_assets', 'rented_properties', 
                   'movable_assets', 'ict_assets', 'vehicle_assets', 
                   'aircraft_assets', 'marine_assets', 'motorcycle_assets',
                   'weapons_inventory', 'ammunition_inventory'];
        
        $total = 0;
        foreach ($tables as $table) {
            try {
                $result = Database::fetchOne("SELECT COUNT(*) as count FROM $table");
                $total += $result['count'] ?? 0;
            } catch (Exception $e) {
                error_log("Error counting $table: " . $e->getMessage());
            }
        }
        
        return $total;
    }
    
    private function getTotalValue() {
        $total = 0;
        
        try {
            // Buildings (contract sum)
            $result = Database::fetchOne("SELECT SUM(contract_sum) as total FROM building_assets");
            $total += $result['total'] ?? 0;
            
            // Movable assets
            $result = Database::fetchOne("SELECT SUM(purchase_value) as total FROM movable_assets");
            $total += $result['total'] ?? 0;
            
            // ICT assets
            $result = Database::fetchOne("SELECT SUM(purchase_value) as total FROM ict_assets");
            $total += $result['total'] ?? 0;
            
            // Vehicles
            $result = Database::fetchOne("SELECT SUM(purchase_value) as total FROM vehicle_assets");
            $total += $result['total'] ?? 0;
            
            // Aircraft
            $result = Database::fetchOne("SELECT SUM(capital_value) as total FROM aircraft_assets");
            $total += $result['total'] ?? 0;
            
            // Marine
            $result = Database::fetchOne("SELECT SUM(capital_value) as total FROM marine_assets");
            $total += $result['total'] ?? 0;
            
            // Motorcycles
            $result = Database::fetchOne("SELECT SUM(purchase_value) as total FROM motorcycle_assets");
            $total += $result['total'] ?? 0;
        } catch (Exception $e) {
            error_log("Error calculating total value: " . $e->getMessage());
        }
        
        return $total;
    }
    
    private function getAssetsByType() {
        $result = [];
        
        $types = [
            'Land' => 'land_assets',
            'Buildings' => 'building_assets',
            'Rented' => 'rented_properties',
            'Movable' => 'movable_assets',
            'ICT' => 'ict_assets',
            'Vehicles' => 'vehicle_assets',
            'Aircraft' => 'aircraft_assets',
            'Marine' => 'marine_assets',
            'Motorcycles' => 'motorcycle_assets',
            'Weapons' => 'weapons_inventory',
            'Ammunition' => 'ammunition_inventory'
        ];
        
        foreach ($types as $key => $table) {
            try {
                $count = Database::fetchOne("SELECT COUNT(*) as count FROM $table")['count'] ?? 0;
                $result[$key] = $count;
            } catch (Exception $e) {
                error_log("Error counting $table: " . $e->getMessage());
                $result[$key] = 0;
            }
        }
        
        return $result;
    }
    
    private function getAssetsByZone() {
        try {
            return Database::fetchAll(
                "SELECT z.zone_name,
                        COUNT(DISTINCT la.id) as land_count,
                        COUNT(DISTINCT ba.id) as building_count,
                        COUNT(DISTINCT rp.id) as rented_count,
                        COUNT(DISTINCT ma.id) as movable_count,
                        COUNT(DISTINCT ia.id) as ict_count,
                        COUNT(DISTINCT va.id) as vehicle_count
                 FROM zones z
                 LEFT JOIN land_assets la ON la.zone_id = z.id
                 LEFT JOIN building_assets ba ON ba.zone_id = z.id
                 LEFT JOIN rented_properties rp ON rp.zone_id = z.id
                 LEFT JOIN movable_assets ma ON ma.zone_id = z.id
                 LEFT JOIN ict_assets ia ON ia.zone_id = z.id
                 LEFT JOIN vehicle_assets va ON va.zone_id = z.id
                 GROUP BY z.id
                 ORDER BY z.zone_name"
            ) ?: [];
        } catch (Exception $e) {
            error_log("Error getting assets by zone: " . $e->getMessage());
            return [];
        }
    }
    
    private function getRecentAdditions() {
        $recent = [];
        
        try {
            $land = Database::fetchAll("SELECT 'Land' as type, id, asset_code as code, created_at FROM land_assets ORDER BY created_at DESC LIMIT 5") ?: [];
            $buildings = Database::fetchAll("SELECT 'Building' as type, id, asset_code as code, created_at FROM building_assets ORDER BY created_at DESC LIMIT 5") ?: [];
            $vehicles = Database::fetchAll("SELECT 'Vehicle' as type, id, asset_code as code, created_at FROM vehicle_assets ORDER BY created_at DESC LIMIT 5") ?: [];
            $weapons = Database::fetchAll("SELECT 'Weapon' as type, id, weapon_id as code, created_at FROM weapons_inventory ORDER BY created_at DESC LIMIT 5") ?: [];
            
            $recent = array_merge($land, $buildings, $vehicles, $weapons);
            usort($recent, function($a, $b) {
                return strtotime($b['created_at']) - strtotime($a['created_at']);
            });
        } catch (Exception $e) {
            error_log("Error getting recent additions: " . $e->getMessage());
        }
        
        return array_slice($recent, 0, 10);
    }
    
    private function getExpiringItems() {
        $result = [
            'ammunition' => [],
            'insurance' => []
        ];
        
        try {
            $result['ammunition'] = Database::fetchAll(
                "SELECT id, ammo_id, ammo_type, calibre, expiry_date
                 FROM ammunition_inventory
                 WHERE expiry_date IS NOT NULL
                 AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)
                 AND expiry_date >= CURDATE()
                 ORDER BY expiry_date ASC
                 LIMIT 20"
            ) ?: [];
            
            $result['insurance'] = Database::fetchAll(
                "SELECT id, asset_code, make_manufacturer, registration_number, insurance_expiry
                 FROM vehicle_assets
                 WHERE insurance_expiry IS NOT NULL
                 AND insurance_expiry <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                 AND insurance_expiry >= CURDATE()
                 ORDER BY insurance_expiry ASC
                 LIMIT 20"
            ) ?: [];
        } catch (Exception $e) {
            error_log("Error getting expiring items: " . $e->getMessage());
        }
        
        return $result;
    }
    
    // =============================================
    // EXPORT METHODS
    // =============================================
    
    private function exportAssetReportCSV($data, $type) {
        $filename = $type . '_assets_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        if ($type === 'summary') {
            foreach ($data as $assetType => $items) {
                if (!empty($items) && is_array($items)) {
                    fputcsv($output, [strtoupper($assetType)]);
                    if (!empty($items[0]) && is_array($items[0])) {
                        fputcsv($output, array_keys($items[0]));
                        foreach ($items as $item) {
                            fputcsv($output, $item);
                        }
                    }
                    fputcsv($output, []);
                }
            }
        } else {
            if (!empty($data) && is_array($data) && !empty($data[0]) && is_array($data[0])) {
                fputcsv($output, array_keys($data[0]));
                foreach ($data as $item) {
                    fputcsv($output, $item);
                }
            }
        }
        
        fclose($output);
        
        // Log export if AuditLogger exists
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('assets', 'csv');
        }
        
        exit;
    }
    
    private function exportAssetReportPDF($data, $type) {
        // In production, use a PDF library like Dompdf or TCPDF
        // For now, redirect to a PDF generation script
        $this->redirect('reports/assets?type=' . $type . '&format=html');
    }
    
    private function exportWeaponsReportCSV($data, $type) {
        $filename = 'weapons_' . $type . '_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        if (!empty($data) && is_array($data) && !empty($data[0]) && is_array($data[0])) {
            fputcsv($output, array_keys($data[0]));
            foreach ($data as $item) {
                fputcsv($output, $item);
            }
        }
        
        fclose($output);
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('weapons', 'csv');
        }
        
        exit;
    }
    
    private function exportWeaponsReportPDF($data, $type) {
        $this->redirect('reports/weapons?type=' . $type . '&format=html');
    }
    
    private function exportAmmunitionReportCSV($data, $type) {
        $filename = 'ammunition_' . $type . '_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        if (!empty($data) && is_array($data) && !empty($data[0]) && is_array($data[0])) {
            fputcsv($output, array_keys($data[0]));
            foreach ($data as $item) {
                fputcsv($output, $item);
            }
        }
        
        fclose($output);
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('ammunition', 'csv');
        }
        
        exit;
    }
    
    private function exportAmmunitionReportPDF($data, $type) {
        $this->redirect('reports/ammunition?type=' . $type . '&format=html');
    }
    
    private function exportFleetReportCSV($data, $type) {
        $filename = 'fleet_' . $type . '_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        if ($type === 'summary') {
            foreach ($data as $fleetType => $items) {
                if (!empty($items) && is_array($items)) {
                    fputcsv($output, [strtoupper($fleetType)]);
                    if (!empty($items[0]) && is_array($items[0])) {
                        fputcsv($output, array_keys($items[0]));
                        foreach ($items as $item) {
                            fputcsv($output, $item);
                        }
                    }
                    fputcsv($output, []);
                }
            }
        } else {
            if (!empty($data) && is_array($data) && !empty($data[0]) && is_array($data[0])) {
                fputcsv($output, array_keys($data[0]));
                foreach ($data as $item) {
                    fputcsv($output, $item);
                }
            }
        }
        
        fclose($output);
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('fleet', 'csv');
        }
        
        exit;
    }
    
    private function exportFleetReportPDF($data, $type) {
        $this->redirect('reports/fleet?type=' . $type . '&format=html');
    }
    
    private function exportAuditReportCSV($data) {
        $filename = 'audit_report_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        foreach ($data as $audit) {
            fputcsv($output, ['AUDIT SUMMARY']);
            fputcsv($output, ['Audit ID', 'Date', 'Quarter', 'Officer', 'Location', 'Status']);
            fputcsv($output, [
                $audit['audit_number'] ?? '',
                $audit['audit_date'] ?? '',
                ($audit['quarter'] ?? '') . ' ' . ($audit['year'] ?? ''),
                $audit['audit_officer'] ?? '',
                $audit['audit_location'] ?? '',
                $audit['status'] ?? ''
            ]);
            
            if (!empty($audit['weapons'])) {
                fputcsv($output, []);
                fputcsv($output, ['WEAPONS AUDITED']);
                if (!empty($audit['weapons'][0])) {
                    fputcsv($output, array_keys($audit['weapons'][0]));
                    foreach ($audit['weapons'] as $weapon) {
                        fputcsv($output, $weapon);
                    }
                }
            }
            
            if (!empty($audit['ammunition'])) {
                fputcsv($output, []);
                fputcsv($output, ['AMMUNITION AUDITED']);
                if (!empty($audit['ammunition'][0])) {
                    fputcsv($output, array_keys($audit['ammunition'][0]));
                    foreach ($audit['ammunition'] as $ammo) {
                        fputcsv($output, $ammo);
                    }
                }
            }
            
            if (!empty($audit['missing'])) {
                fputcsv($output, []);
                fputcsv($output, ['MISSING WEAPONS']);
                if (!empty($audit['missing'][0])) {
                    fputcsv($output, array_keys($audit['missing'][0]));
                    foreach ($audit['missing'] as $missing) {
                        fputcsv($output, $missing);
                    }
                }
            }
            
            fputcsv($output, []);
            fputcsv($output, []);
        }
        
        fclose($output);
        
        if (class_exists('AuditLogger')) {
            AuditLogger::logExport('audit', 'csv');
        }
        
        exit;
    }
    
    private function exportAuditReportPDF($data) {
        $this->redirect('reports/audit?format=html');
    }
}