<?php
/**
 * RUHONDO BEACH RESORT HMS + POS System
 * PDF Export Handler - FIXED VERSION
 * Developer: JOACHIM'S INNOTECH
 */

// Enable error reporting for debugging (remove in production)
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Calculate the correct base path
$base_path = null;

// Method 1: Try going up 2 levels from current file
// Current file: /HMSPOS/reports/export-pdf.php
$base_path = dirname(__DIR__, 2);

// Method 2: Manual calculation if method 1 fails
if (!file_exists($base_path . '/config/config.php')) {
    $base_path = dirname(dirname(__DIR__));
}

// Method 3: Absolute path as last resort (adjust this to your actual path)
if (!file_exists($base_path . '/config/config.php')) {
    $base_path = '/Applications/XAMPP/xamppfiles/htdocs/HMSPOS';
}

// Verify the base path works
if (!file_exists($base_path . '/config/config.php')) {
    die("Error: Cannot find system files at: " . $base_path . "/config/config.php");
}

// Load required files
require_once $base_path . '/config/config.php';
require_once $base_path . '/config/constants.php';
require_once $base_path . '/config/database.php';
require_once $base_path . '/config/helpers.php';
require_once $base_path . '/config/auth.php';

// Require authentication
require_auth();

// Load Dompdf
$vendor_autoload = $base_path . '/vendor/autoload.php';
if (!file_exists($vendor_autoload)) {
    die("Error: Dompdf not found. Please run: composer require dompdf/dompdf");
}
require_once $vendor_autoload;

use Dompdf\Dompdf;
use Dompdf\Options;

// Get export parameters with fallbacks
$report_type = $_GET['report'] ?? '';
$export_type = $_GET['type'] ?? '';
$date_from = $_GET['from'] ?? $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$date_to = $_GET['to'] ?? $_GET['date_to'] ?? date('Y-m-d');
$room_type_id = isset($_GET['room_type_id']) ? (int)$_GET['room_type_id'] : null;
$category_id = isset($_GET['category_id']) ? (int)$_GET['category_id'] : null;

// Validate report type
if (empty($report_type)) {
    die("Error: No report type specified");
}

// Generate HTML based on report type
$html = generate_report_html($report_type, $export_type, $date_from, $date_to, $room_type_id, $category_id);

// Configure Dompdf
$options = new Options();
$options->set('isHtml5ParserEnabled', true);
$options->set('isPhpEnabled', false);
$options->set('defaultFont', 'Arial');
$options->set('isRemoteEnabled', true);
$options->set('chroot', $base_path); // Set base path for security

$dompdf = new Dompdf($options);
$dompdf->loadHtml($html);
$dompdf->setPaper('A4', 'landscape');
$dompdf->render();

// Output PDF
$filename = $report_type . '_report_' . $date_from . '_to_' . $date_to . '.pdf';
$dompdf->stream($filename, array("Attachment" => false));

/**
 * Generate report HTML
 */
function generate_report_html($report_type, $export_type, $date_from, $date_to, $room_type_id, $category_id) {
    // Define currency symbol if not defined
    if (!defined('CURRENCY_SYMBOL')) {
        define('CURRENCY_SYMBOL', 'RWF');
    }
    
    $html = '
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>' . ucfirst(str_replace('_', ' ', $report_type)) . ' Report</title>
        <style>
            body { font-family: Arial, sans-serif; font-size: 10pt; }
            h1 { font-size: 18pt; color: #333; text-align: center; }
            h2 { font-size: 14pt; color: #666; margin-top: 20px; }
            .header { text-align: center; margin-bottom: 20px; }
            .header .date { color: #666; font-size: 10pt; }
            table { width: 100%; border-collapse: collapse; margin-top: 15px; }
            th { background: #007bff; color: white; padding: 8px; text-align: left; font-size: 9pt; }
            td { padding: 6px; border-bottom: 1px solid #ddd; font-size: 9pt; }
            tr:nth-child(even) { background: #f9f9f9; }
            .summary { margin-top: 20px; padding: 10px; background: #f0f0f0; border-radius: 5px; }
            .summary table { width: auto; margin: 0; }
            .summary td { padding: 5px 10px; border: none; }
            .footer { margin-top: 30px; text-align: center; font-size: 8pt; color: #999; }
            .page-break { page-break-after: always; }
        </style>
    </head>
    <body>
        <div class="header">
            <h1>Ruhondo Beach Resort</h1>
            <h2>' . ucfirst(str_replace('_', ' ', $report_type)) . ' Report</h2>
            <div class="date">Period: ' . format_date($date_from) . ' to ' . format_date($date_to) . '</div>
        </div>
    ';
    
    switch ($report_type) {
        case 'occupancy':
            $html .= get_occupancy_html($date_from, $date_to, $room_type_id);
            break;
        case 'revenue':
            $html .= get_revenue_html($date_from, $date_to, $export_type);
            break;
        case 'adr_revpar':
            $html .= get_adr_revpar_html($date_from, $date_to, $room_type_id);
            break;
        case 'room_status':
            $html .= get_room_status_html($date_from);
            break;
        case 'reservations':
            $html .= get_reservations_html($date_from, $date_to, $export_type);
            break;
        case 'guest_analytics':
            $html .= get_guest_analytics_html($date_from, $date_to, $export_type);
            break;
        case 'inventory_valuation':
            $html .= get_inventory_valuation_html($date_from, $category_id);
            break;
        case 'audit':
            $html .= get_audit_html($date_from, $date_to);
            break;
        default:
            $html .= '<p>Invalid report type: ' . htmlspecialchars($report_type) . '</p>';
    }
    
    $html .= '
        <div class="footer">
            <p>Generated on ' . date('Y-m-d H:i:s') . ' | Ruhondo Beach Resort Management System</p>
        </div>
    </body>
    </html>
    ';
    
    return $html;
}

/**
 * Get occupancy report HTML
 */
function get_occupancy_html($date_from, $date_to, $room_type_id) {
    try {
        // Get total rooms
        $total_rooms_sql = "SELECT COUNT(*) FROM rooms WHERE is_active = 1";
        if ($room_type_id) {
            $total_rooms_sql .= " AND room_type_id = " . (int)$room_type_id;
        }
        $total_rooms = (int)db_fetch_value($total_rooms_sql);
        if ($total_rooms === null) $total_rooms = 0;
        
        $html = '<table>';
        $html .= '<tr><th>Date</th><th>Available</th><th>Occupied</th><th>Occupancy %</th><th>Room Nights</th></tr>';
        
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        $total_available = 0;
        $total_occupied = 0;
        $days = 0;
        
        while ($current <= $end) {
            $days++;
            $date = date('Y-m-d', $current);
            
            $occupied_sql = "
                SELECT COUNT(DISTINCT room_id) 
                FROM reservations 
                WHERE check_in_date <= ? AND check_out_date > ? 
                AND booking_status IN ('confirmed', 'checked_in')
            ";
            $params = [$date, $date];
            
            if ($room_type_id) {
                $occupied_sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $occupied = (int)db_fetch_value($occupied_sql, $params);
            $available = max(0, $total_rooms - $occupied);
            $occupancy_rate = $total_rooms > 0 ? round(($occupied / $total_rooms) * 100, 1) : 0;
            
            $html .= '<tr>';
            $html .= '<td>' . format_date($date) . '</td>';
            $html .= '<td>' . $available . '</td>';
            $html .= '<td>' . $occupied . '</td>';
            $html .= '<td>' . $occupancy_rate . '%</td>';
            $html .= '<td>' . $occupied . '</td>';
            $html .= '</tr>';
            
            $total_available += $available;
            $total_occupied += $occupied;
            
            $current = strtotime('+1 day', $current);
        }
        
        $html .= '</table>';
        
        $avg_occupancy = $days > 0 && $total_rooms > 0 ? round(($total_occupied / ($days * $total_rooms)) * 100, 1) : 0;
        
        $html .= '<div class="summary">';
        $html .= '<h3>Summary</h3>';
        $html .= '<table>';
        $html .= '<tr><td>Total Room Nights:</td><td>' . $total_occupied . '</td></tr>';
        $html .= '<tr><td>Average Occupancy:</td><td>' . $avg_occupancy . '%</td></tr>';
        $html .= '</table>';
        $html .= '</div>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in occupancy report: " . $e->getMessage());
        return '<p>Error generating occupancy report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get revenue report HTML
 */
function get_revenue_html($date_from, $date_to, $revenue_type) {
    try {
        $html = '<table>';
        $html .= '<tr><th>Date</th><th>Rooms</th><th>F&B</th><th>Services</th><th>Other</th><th>Total</th></tr>';
        
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        $total_rooms = 0;
        $total_fnb = 0;
        $total_services = 0;
        $total_other = 0;
        $total_all = 0;
        
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            
            $rooms = (float)db_fetch_value("
                SELECT COALESCE(SUM(amount), 0) FROM folio_charges 
                WHERE charge_type = 'room' AND DATE(posted_at) = ? AND is_voided = 0
            ", [$date]);
            
            $fnb = (float)db_fetch_value("
                SELECT COALESCE(SUM(amount), 0) FROM folio_charges 
                WHERE charge_type = 'fnb' AND DATE(posted_at) = ? AND is_voided = 0
            ", [$date]);
            
            $services = (float)db_fetch_value("
                SELECT COALESCE(SUM(amount), 0) FROM folio_charges 
                WHERE charge_type = 'service' AND DATE(posted_at) = ? AND is_voided = 0
            ", [$date]);
            
            $other = (float)db_fetch_value("
                SELECT COALESCE(SUM(amount), 0) FROM folio_charges 
                WHERE charge_type NOT IN ('room', 'fnb', 'service', 'payment') 
                AND DATE(posted_at) = ? AND is_voided = 0
            ", [$date]);
            
            $total = $rooms + $fnb + $services + $other;
            
            $html .= '<tr>';
            $html .= '<td>' . format_date($date) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($rooms, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($fnb, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($services, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($other, 2) . '</td>';
            $html .= '<td><strong>' . CURRENCY_SYMBOL . ' ' . number_format($total, 2) . '</strong></td>';
            $html .= '</tr>';
            
            $total_rooms += $rooms;
            $total_fnb += $fnb;
            $total_services += $services;
            $total_other += $other;
            $total_all += $total;
            
            $current = strtotime('+1 day', $current);
        }
        
        $html .= '</table>';
        
        $html .= '<div class="summary">';
        $html .= '<h3>Summary</h3>';
        $html .= '<table>';
        $html .= '<tr><td>Total Rooms Revenue:</td><td>' . CURRENCY_SYMBOL . ' ' . number_format($total_rooms, 2) . '</td></tr>';
        $html .= '<tr><td>Total F&B Revenue:</td><td>' . CURRENCY_SYMBOL . ' ' . number_format($total_fnb, 2) . '</td></tr>';
        $html .= '<tr><td>Total Services Revenue:</td><td>' . CURRENCY_SYMBOL . ' ' . number_format($total_services, 2) . '</td></tr>';
        $html .= '<tr><td>Total Other Revenue:</td><td>' . CURRENCY_SYMBOL . ' ' . number_format($total_other, 2) . '</td></tr>';
        $html .= '<tr><td><strong>Total Revenue:</strong></td><td><strong>' . CURRENCY_SYMBOL . ' ' . number_format($total_all, 2) . '</strong></td></tr>';
        $html .= '</table>';
        $html .= '</div>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in revenue report: " . $e->getMessage());
        return '<p>Error generating revenue report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get ADR/RevPAR report HTML
 */
function get_adr_revpar_html($date_from, $date_to, $room_type_id) {
    try {
        // Get total rooms
        $total_rooms_sql = "SELECT COUNT(*) FROM rooms WHERE is_active = 1";
        if ($room_type_id) {
            $total_rooms_sql .= " AND room_type_id = " . (int)$room_type_id;
        }
        $total_rooms = (int)db_fetch_value($total_rooms_sql);
        if ($total_rooms === null) $total_rooms = 0;
        
        $html = '<table>';
        $html .= '<tr><th>Date</th><th>Occupied</th><th>Revenue</th><th>ADR</th><th>RevPAR</th></tr>';
        
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            
            $occupied_sql = "
                SELECT COUNT(DISTINCT room_id) 
                FROM reservations 
                WHERE check_in_date <= ? AND check_out_date > ? 
                AND booking_status IN ('confirmed', 'checked_in')
            ";
            $params = [$date, $date];
            
            if ($room_type_id) {
                $occupied_sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $occupied = (int)db_fetch_value($occupied_sql, $params);
            
            $revenue_sql = "
                SELECT COALESCE(SUM(fc.amount), 0) 
                FROM folio_charges fc
                JOIN reservations r ON fc.reservation_id = r.reservation_id
                WHERE fc.charge_type = 'room' 
                AND DATE(fc.posted_at) = ? 
                AND fc.is_voided = 0
            ";
            $rev_params = [$date];
            
            if ($room_type_id) {
                $revenue_sql .= " AND r.room_type_id = ?";
                $rev_params[] = $room_type_id;
            }
            
            $revenue = (float)db_fetch_value($revenue_sql, $rev_params);
            
            $adr = $occupied > 0 ? round($revenue / $occupied, 2) : 0;
            $revpar = $total_rooms > 0 ? round($revenue / $total_rooms, 2) : 0;
            
            $html .= '<tr>';
            $html .= '<td>' . format_date($date) . '</td>';
            $html .= '<td>' . $occupied . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($revenue, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($adr, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($revpar, 2) . '</td>';
            $html .= '</tr>';
            
            $current = strtotime('+1 day', $current);
        }
        
        $html .= '</table>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in ADR/RevPAR report: " . $e->getMessage());
        return '<p>Error generating ADR/RevPAR report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get room status report HTML
 */
function get_room_status_html($date) {
    try {
        $rooms = db_fetch_all("
            SELECT 
                r.room_number, r.floor, rt.type_name,
                r.status, r.housekeeping_status,
                CONCAT(g.first_name, ' ', g.last_name) as guest_name,
                res.check_out_date
            FROM rooms r
            JOIN room_types rt ON r.room_type_id = rt.room_type_id
            LEFT JOIN reservations res ON r.room_id = res.room_id 
                AND res.booking_status = 'checked_in'
            LEFT JOIN guests g ON res.guest_id = g.guest_id
            WHERE r.is_active = 1
            ORDER BY r.floor, r.room_number
        ");
        
        if (empty($rooms)) {
            return '<p>No room data available</p>';
        }
        
        $html = '<table>';
        $html .= '<tr><th>Room</th><th>Floor</th><th>Type</th><th>Status</th><th>HK Status</th><th>Guest</th><th>Check-out</th></tr>';
        
        foreach ($rooms as $room) {
            $html .= '<tr>';
            $html .= '<td>' . htmlspecialchars($room['room_number'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($room['floor'] ?? '-') . '</td>';
            $html .= '<td>' . htmlspecialchars($room['type_name'] ?? '') . '</td>';
            $html .= '<td>' . ucfirst($room['status'] ?? 'unknown') . '</td>';
            $html .= '<td>' . ucfirst(str_replace('_', ' ', $room['housekeeping_status'] ?? 'unknown')) . '</td>';
            $html .= '<td>' . ($room['guest_name'] ? htmlspecialchars($room['guest_name']) : '-') . '</td>';
            $html .= '<td>' . ($room['check_out_date'] ? htmlspecialchars($room['check_out_date']) : '-') . '</td>';
            $html .= '</tr>';
        }
        
        $html .= '</table>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in room status report: " . $e->getMessage());
        return '<p>Error generating room status report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get reservations report HTML
 */
function get_reservations_html($date_from, $date_to, $type) {
    try {
        $reservations = db_fetch_all("
            SELECT 
                r.booking_code,
                g.first_name, g.last_name,
                rt.type_name,
                r.check_in_date, r.check_out_date,
                DATEDIFF(r.check_out_date, r.check_in_date) as nights,
                r.total_amount,
                r.booking_status,
                DATE(r.created_at) as created_date
            FROM reservations r
            JOIN guests g ON r.guest_id = g.guest_id
            JOIN room_types rt ON r.room_type_id = rt.room_type_id
            WHERE DATE(r.created_at) BETWEEN ? AND ?
            ORDER BY r.created_at
        ", [$date_from, $date_to]);
        
        if (empty($reservations)) {
            return '<p>No reservation data available for the selected period</p>';
        }
        
        $html = '<table>';
        $html .= '<tr><th>Date</th><th>Booking #</th><th>Guest</th><th>Room Type</th><th>Check-in</th><th>Check-out</th><th>Nights</th><th>Total</th><th>Status</th></tr>';
        
        foreach ($reservations as $res) {
            $html .= '<tr>';
            $html .= '<td>' . htmlspecialchars($res['created_date'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($res['booking_code'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($res['first_name'] ?? '') . ' ' . htmlspecialchars($res['last_name'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($res['type_name'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($res['check_in_date'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($res['check_out_date'] ?? '') . '</td>';
            $html .= '<td>' . ($res['nights'] ?? '') . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($res['total_amount'] ?? 0, 2) . '</td>';
            $html .= '<td>' . ucfirst($res['booking_status'] ?? 'unknown') . '</td>';
            $html .= '</tr>';
        }
        
        $html .= '</table>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in reservations report: " . $e->getMessage());
        return '<p>Error generating reservations report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get guest analytics HTML
 */
function get_guest_analytics_html($date_from, $date_to, $type) {
    try {
        $guests = db_fetch_all("
            SELECT 
                g.guest_code, g.first_name, g.last_name,
                g.email, g.phone, g.nationality,
                COUNT(r.reservation_id) as stays,
                COALESCE(SUM(r.total_amount), 0) as total_spent,
                MAX(r.check_in_date) as last_visit
            FROM guests g
            LEFT JOIN reservations r ON g.guest_id = r.guest_id
                AND r.created_at BETWEEN ? AND ?
            GROUP BY g.guest_id
            ORDER BY stays DESC
            LIMIT 100
        ", [$date_from, $date_to]);
        
        if (empty($guests)) {
            return '<p>No guest data available for the selected period</p>';
        }
        
        $html = '<table>';
        $html .= '<tr><th>Guest Code</th><th>Name</th><th>Email</th><th>Phone</th><th>Nationality</th><th>Stays</th><th>Total Spent</th><th>Last Visit</th></tr>';
        
        foreach ($guests as $guest) {
            $html .= '<tr>';
            $html .= '<td>' . htmlspecialchars($guest['guest_code'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($guest['first_name'] ?? '') . ' ' . htmlspecialchars($guest['last_name'] ?? '') . '</td>';
            $html .= '<td>' . ($guest['email'] ? htmlspecialchars($guest['email']) : '-') . '</td>';
            $html .= '<td>' . ($guest['phone'] ? htmlspecialchars($guest['phone']) : '-') . '</td>';
            $html .= '<td>' . ($guest['nationality'] ? htmlspecialchars($guest['nationality']) : '-') . '</td>';
            $html .= '<td>' . ($guest['stays'] ?? 0) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($guest['total_spent'] ?? 0, 2) . '</td>';
            $html .= '<td>' . ($guest['last_visit'] ? htmlspecialchars($guest['last_visit']) : '-') . '</td>';
            $html .= '</tr>';
        }
        
        $html .= '</table>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in guest analytics report: " . $e->getMessage());
        return '<p>Error generating guest analytics report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get inventory valuation HTML
 */
function get_inventory_valuation_html($date_as_of, $category_id) {
    try {
        $sql = "SELECT 
                    p.product_code, p.product_name,
                    p.current_stock, p.unit_of_measure,
                    p.cost_price,
                    c.category_name,
                    (p.current_stock * p.cost_price) as total_value
                FROM pos_products p
                JOIN pos_categories c ON p.category_id = c.category_id
                WHERE p.stock_item = 1 AND p.is_active = 1";
        
        $params = [];
        
        if ($category_id) {
            $sql .= " AND p.category_id = ?";
            $params[] = $category_id;
        }
        
        $sql .= " ORDER BY c.category_name, p.product_name";
        
        $products = db_fetch_all($sql, $params);
        
        if (empty($products)) {
            return '<p>No inventory data available</p>';
        }
        
        $html = '<table>';
        $html .= '<tr><th>Category</th><th>Code</th><th>Product</th><th>Stock</th><th>Unit</th><th>Cost Price</th><th>Total Value</th></tr>';
        
        $total_value = 0;
        $total_units = 0;
        
        foreach ($products as $product) {
            $html .= '<tr>';
            $html .= '<td>' . htmlspecialchars($product['category_name'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($product['product_code'] ?? '') . '</td>';
            $html .= '<td>' . htmlspecialchars($product['product_name'] ?? '') . '</td>';
            $html .= '<td>' . ($product['current_stock'] ?? 0) . '</td>';
            $html .= '<td>' . ($product['unit_of_measure'] ?: 'unit') . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($product['cost_price'] ?: 0, 2) . '</td>';
            $html .= '<td>' . CURRENCY_SYMBOL . ' ' . number_format($product['total_value'] ?: 0, 2) . '</td>';
            $html .= '</tr>';
            
            $total_value += $product['total_value'] ?: 0;
            $total_units += $product['current_stock'] ?: 0;
        }
        
        $html .= '</table>';
        
        $html .= '<div class="summary">';
        $html .= '<h3>Summary</h3>';
        $html .= '<table>';
        $html .= '<tr><td>Total Products:</td><td>' . count($products) . '</td></tr>';
        $html .= '<tr><td>Total Units:</td><td>' . $total_units . '</td></tr>';
        $html .= '<tr><td>Total Value:</td><td>' . CURRENCY_SYMBOL . ' ' . number_format($total_value, 2) . '</td></tr>';
        $html .= '</table>';
        $html .= '</div>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in inventory valuation report: " . $e->getMessage());
        return '<p>Error generating inventory valuation report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Get audit trail HTML
 */
function get_audit_html($date_from, $date_to) {
    try {
        $logs = db_fetch_all("
            SELECT 
                a.created_at,
                u.username,
                a.action,
                a.table_name,
                a.record_id,
                a.ip_address
            FROM audit_logs a
            LEFT JOIN users u ON a.user_id = u.user_id
            WHERE DATE(a.created_at) BETWEEN ? AND ?
            ORDER BY a.created_at DESC
            LIMIT 500
        ", [$date_from, $date_to]);
        
        if (empty($logs)) {
            return '<p>No audit data available for the selected period</p>';
        }
        
        $html = '<table>';
        $html .= '<tr><th>Timestamp</th><th>User</th><th>Action</th><th>Table</th><th>Record ID</th><th>IP Address</th></tr>';
        
        foreach ($logs as $log) {
            $html .= '<tr>';
            $html .= '<td>' . date('Y-m-d H:i:s', strtotime($log['created_at'])) . '</td>';
            $html .= '<td>' . ($log['username'] ? htmlspecialchars($log['username']) : 'System') . '</td>';
            $html .= '<td>' . ucfirst($log['action'] ?? '') . '</td>';
            $html .= '<td>' . ($log['table_name'] ? str_replace('_', ' ', $log['table_name']) : '-') . '</td>';
            $html .= '<td>' . ($log['record_id'] ?: '-') . '</td>';
            $html .= '<td>' . ($log['ip_address'] ?: '-') . '</td>';
            $html .= '</tr>';
        }
        
        $html .= '</table>';
        
        return $html;
    } catch (Exception $e) {
        error_log("Error in audit report: " . $e->getMessage());
        return '<p>Error generating audit report: ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
}

/**
 * Format date helper
 */
if (!function_exists('format_date')) {
    function format_date($date, $format = 'M d, Y') {
        if (empty($date)) return '';
        $timestamp = strtotime($date);
        return $timestamp ? date($format, $timestamp) : $date;
    }
}
?>