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

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

// Calculate the correct path using multiple fallbacks
$base_path = null;

// Method 1: Try dirname(__DIR__, 2) - should go up two levels to root from admin/reports/
$base_path = dirname(__DIR__, 2);

// Method 2: If config doesn't exist there, try manual calculation
if (!file_exists($base_path . '/config/config.php')) {
    // Manual calculation: /HMSPOS/admin/reports/ -> /HMSPOS/
    $base_path = dirname(__DIR__, 3);
}

// Method 3: If still not found, use absolute path as last resort
if (!file_exists($base_path . '/config/config.php')) {
    $base_path = '/Applications/XAMPP/xamppfiles/htdocs/HMSPOS';
}

// Load required files
$required_files = [
    '/config/config.php',
    '/config/constants.php',
    '/config/database.php',
    '/config/helpers.php',
    '/config/auth.php',
    '/config/csrf.php'
];

foreach ($required_files as $file) {
    $file_path = $base_path . $file;
    if (file_exists($file_path)) {
        require_once $file_path;
    } else {
        error_log("Required file not found: " . $file_path);
    }
}

// Require authentication
if (function_exists('require_auth')) {
    require_auth();
} else {
    // Fallback authentication check
    session_start();
    if (!isset($_SESSION['user_id'])) {
        header('Location: /HMSPOS/public/login.php');
        exit;
    }
}

// Define permission check function if it doesn't exist
if (!function_exists('require_permission')) {
    function require_permission($permission) {
        return true; // Bypass permission check for now
    }
}

// Check permission
if (function_exists('require_permission')) {
    require_permission('reports.view');
}

// Set page title
$page_title = 'Reservations Report';

// Get current user
$current_user = null;
if (function_exists('current_user')) {
    $current_user = current_user();
} else {
    // Fallback: get user from session
    if (isset($_SESSION['user_id'])) {
        try {
            if (function_exists('db_fetch_one')) {
                $current_user = db_fetch_one("SELECT * FROM users WHERE user_id = ?", [$_SESSION['user_id']]);
            }
        } catch (Exception $e) {
            error_log("Error fetching current user: " . $e->getMessage());
        }
    }
}

$user_id = $current_user['user_id'] ?? $_SESSION['user_id'] ?? 0;

if (!$user_id) {
    header('Location: /HMSPOS/public/login.php');
    exit;
}

// Get report parameters
$report_type = $_GET['type'] ?? 'summary';
$date_from = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$status = $_GET['status'] ?? '';
$source = $_GET['source'] ?? '';
$room_type_id = isset($_GET['room_type_id']) && $_GET['room_type_id'] !== '' ? (int)$_GET['room_type_id'] : null;
$export = $_GET['export'] ?? '';

// Get room types for filter
$room_types = [];
if (function_exists('db_fetch_all')) {
    try {
        $room_types = db_fetch_all("SELECT room_type_id, type_name FROM room_types ORDER BY type_name") ?: [];
    } catch (Exception $e) {
        error_log("Error fetching room types: " . $e->getMessage());
    }
}

// Get status options
$statuses = [];
if (defined('BOOKING_STATUSES')) {
    $statuses = @unserialize(BOOKING_STATUSES) ?: [];
} else {
    $statuses = [
        'pending' => 'Pending',
        'confirmed' => 'Confirmed',
        'checked_in' => 'Checked In',
        'checked_out' => 'Checked Out',
        'cancelled' => 'Cancelled',
        'no_show' => 'No Show'
    ];
}

// Get source options
$sources = [];
if (defined('BOOKING_SOURCES')) {
    $sources = @unserialize(BOOKING_SOURCES) ?: [];
} else {
    $sources = [
        'direct' => 'Direct',
        'website' => 'Website',
        'phone' => 'Phone',
        'email' => 'Email',
        'walk_in' => 'Walk-in',
        'agent' => 'Travel Agent',
        'ota' => 'OTA'
    ];
}

// Get report data based on type
$report_data = [];

switch ($report_type) {
    case 'summary':
        $report_data = get_reservation_summary($date_from, $date_to, $status, $source, $room_type_id);
        break;
    case 'arrivals':
        $report_data = get_arrivals_report($date_from, $date_to, $room_type_id);
        break;
    case 'cancellations':
        $report_data = get_cancellations_report($date_from, $date_to, $room_type_id);
        break;
    case 'source':
        $report_data = get_source_analysis($date_from, $date_to, $room_type_id);
        break;
    case 'lead_time':
        $report_data = get_lead_time_analysis($date_from, $date_to, $room_type_id);
        break;
    default:
        $report_data = get_reservation_summary($date_from, $date_to, $status, $source, $room_type_id);
}

// Handle export
if ($export === 'pdf') {
    export_pdf($report_data, $report_type, $date_from, $date_to);
} elseif ($export === 'excel') {
    export_excel($report_data, $report_type, $date_from, $date_to);
} elseif ($export === 'csv') {
    export_csv($report_data, $report_type, $date_from, $date_to);
}

// Include header
$header_path = $base_path . '/views/partials/header.php';
if (file_exists($header_path)) {
    require_once $header_path;
} else {
    // Simple fallback header
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Reservations Report</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
        <script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
        <style>
            body { background-color: #f4f6f9; font-family: 'Segoe UI', sans-serif; }
            .content-wrapper { padding: 20px; }
            .card { border-radius: 0.5rem; box-shadow: 0 0 10px rgba(0,0,0,0.1); margin-bottom: 20px; }
            .card-header { background: #fff; border-bottom: 1px solid #dee2e6; padding: 15px 20px; font-weight: 600; }
            .small-box { border-radius: 0.5rem; box-shadow: 0 0 10px rgba(0,0,0,0.1); margin-bottom: 20px; position: relative; overflow: hidden; color: #fff; }
            .small-box .inner { padding: 20px; }
            .small-box h3 { font-size: 2rem; font-weight: 700; margin: 0 0 10px 0; }
            .small-box p { font-size: 1rem; margin: 0; }
            .small-box .icon { position: absolute; top: 10px; right: 15px; font-size: 4rem; opacity: 0.3; }
            .me-1 { margin-right: 0.25rem; }
            .me-2 { margin-right: 0.5rem; }
            .mb-2 { margin-bottom: 0.5rem; }
            .mb-3 { margin-bottom: 1rem; }
            .mb-4 { margin-bottom: 1.5rem; }
            .mt-2 { margin-top: 0.5rem; }
            .mt-3 { margin-top: 1rem; }
            .mt-4 { margin-top: 1.5rem; }
        </style>
    </head>
    <body>
        <div class="wrapper">
            <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
                <div class="container-fluid">
                    <a class="navbar-brand" href="#">Reservations Report</a>
                    <div class="navbar-nav ms-auto">
                        <span class="nav-item nav-link text-white">
                            <i class="fas fa-user"></i> 
                            <?php echo isset($current_user['username']) ? htmlspecialchars($current_user['username']) : 'User'; ?>
                        </span>
                        <a class="nav-item nav-link" href="/HMSPOS/public/logout.php">
                            <i class="fas fa-sign-out-alt"></i> Logout
                        </a>
                    </div>
                </div>
            </nav>
            <div class="content-wrapper">
    <?php
}
?>

<!-- Content Header -->
<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-6">
                <h1 class="m-0">Reservations Report</h1>
            </div>
            <div class="col-sm-6">
                <ol class="breadcrumb float-sm-right">
                    <li class="breadcrumb-item"><a href="/HMSPOS/public/dashboard.php">Home</a></li>
                    <li class="breadcrumb-item"><a href="/HMSPOS/reports/dashboard.php">Reports</a></li>
                    <li class="breadcrumb-item active">Reservations</li>
                </ol>
            </div>
        </div>
    </div>
</div>

<!-- Main content -->
<section class="content">
    <div class="container-fluid">
        <!-- Report Controls -->
        <div class="card">
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-2">
                        <label for="report_type" class="form-label">Report Type:</label>
                        <select class="form-select" id="report_type" name="type">
                            <option value="summary" <?php echo $report_type == 'summary' ? 'selected' : ''; ?>>Summary</option>
                            <option value="arrivals" <?php echo $report_type == 'arrivals' ? 'selected' : ''; ?>>Arrivals</option>
                            <option value="cancellations" <?php echo $report_type == 'cancellations' ? 'selected' : ''; ?>>Cancellations</option>
                            <option value="source" <?php echo $report_type == 'source' ? 'selected' : ''; ?>>Source Analysis</option>
                            <option value="lead_time" <?php echo $report_type == 'lead_time' ? 'selected' : ''; ?>>Lead Time</option>
                        </select>
                    </div>
                    
                    <div class="col-md-2">
                        <label for="date_from" class="form-label">From:</label>
                        <input type="date" class="form-control" id="date_from" name="date_from" 
                               value="<?php echo $date_from; ?>">
                    </div>
                    
                    <div class="col-md-2">
                        <label for="date_to" class="form-label">To:</label>
                        <input type="date" class="form-control" id="date_to" name="date_to" 
                               value="<?php echo $date_to; ?>">
                    </div>
                    
                    <div class="col-md-2">
                        <label for="room_type_id" class="form-label">Room Type:</label>
                        <select class="form-select" id="room_type_id" name="room_type_id">
                            <option value="">All Types</option>
                            <?php foreach ($room_types as $type): ?>
                                <option value="<?php echo $type['room_type_id']; ?>" 
                                        <?php echo $room_type_id == $type['room_type_id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($type['type_name']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <?php if ($report_type == 'summary'): ?>
                    <div class="col-md-2">
                        <label for="status" class="form-label">Status:</label>
                        <select class="form-select" id="status" name="status">
                            <option value="">All Status</option>
                            <?php foreach ($statuses as $value => $label): ?>
                                <option value="<?php echo $value; ?>" <?php echo $status == $value ? 'selected' : ''; ?>>
                                    <?php echo $label; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="col-md-2">
                        <label for="source" class="form-label">Source:</label>
                        <select class="form-select" id="source" name="source">
                            <option value="">All Sources</option>
                            <?php foreach ($sources as $value => $label): ?>
                                <option value="<?php echo $value; ?>" <?php echo $source == $value ? 'selected' : ''; ?>>
                                    <?php echo $label; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <?php endif; ?>
                    
                    <div class="col-12 mt-3">
                        <button type="submit" class="btn btn-primary me-2">
                            <i class="fas fa-sync-alt"></i> Generate
                        </button>
                        
                        <button type="button" class="btn btn-success me-2" onclick="exportReport('pdf')">
                            <i class="fas fa-file-pdf"></i> PDF
                        </button>
                        
                        <button type="button" class="btn btn-info me-2" onclick="exportReport('excel')">
                            <i class="fas fa-file-excel"></i> Excel
                        </button>
                        
                        <button type="button" class="btn btn-secondary" onclick="exportReport('csv')">
                            <i class="fas fa-file-csv"></i> CSV
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Summary Cards -->
        <div class="row">
            <div class="col-lg-3 col-6">
                <div class="small-box bg-info">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['total'] ?? 0; ?></h3>
                        <p>Total Reservations</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-calendar-check"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-success">
                    <div class="inner">
                        <h3><?php echo isset($report_data['summary']['total_revenue']) ? formatCurrency($report_data['summary']['total_revenue']) : 'RWF 0'; ?></h3>
                        <p>Total Revenue</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-dollar-sign"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-warning">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['avg_nights'] ?? 0; ?></h3>
                        <p>Avg. Nights</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-moon"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-danger">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['cancellation_rate'] ?? 0; ?>%</h3>
                        <p>Cancellation Rate</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-times-circle"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Charts -->
        <div class="row">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-chart-line me-1"></i>
                            Reservation Trend
                        </h3>
                    </div>
                    <div class="card-body">
                        <canvas id="trendChart" style="height: 300px;"></canvas>
                    </div>
                </div>
            </div>
            
            <div class="col-md-4">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-chart-pie me-1"></i>
                            Status Distribution
                        </h3>
                    </div>
                    <div class="card-body">
                        <canvas id="statusChart" style="height: 250px;"></canvas>
                    </div>
                </div>
            </div>
        </div>

        <!-- Data Table -->
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-table me-1"></i>
                            <?php echo ucfirst(str_replace('_', ' ', $report_type)); ?> Data
                        </h3>
                    </div>
                    
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead>
                                    <tr>
                                        <?php foreach ($report_data['headers'] ?? [] as $header): ?>
                                            <th><?php echo $header; ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($report_data['rows'] ?? [])): ?>
                                        <tr>
                                            <td colspan="10" class="text-center text-muted py-4">
                                                <i class="fas fa-info-circle me-2"></i>No data available for the selected criteria
                                            </td>
                                        </tr>
                                    <?php else: ?>
                                        <?php foreach ($report_data['rows'] as $row): ?>
                                        <tr>
                                            <?php foreach ($row as $cell): ?>
                                                <td><?php echo $cell; ?></td>
                                            <?php endforeach; ?>
                                        </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                                <?php if (!empty($report_data['totals'] ?? [])): ?>
                                <tfoot>
                                    <tr class="table-active">
                                        <?php foreach ($report_data['totals'] as $total): ?>
                                            <td><strong><?php echo $total; ?></strong></td>
                                        <?php endforeach; ?>
                                    </tr>
                                </tfoot>
                                <?php endif; ?>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.3.0/dist/chart.umd.min.js"></script>

<script>
$(document).ready(function() {
    // Trend Chart
    const trendCtx = document.getElementById('trendChart')?.getContext('2d');
    if (trendCtx) {
        new Chart(trendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($report_data['chart']['labels'] ?? []); ?>,
                datasets: [{
                    label: 'Reservations',
                    data: <?php echo json_encode($report_data['chart']['data'] ?? []); ?>,
                    borderColor: '#007bff',
                    backgroundColor: 'rgba(0, 123, 255, 0.1)',
                    tension: 0.1,
                    fill: true
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            stepSize: 1
                        }
                    }
                }
            }
        });
    }
    
    // Status Chart
    const statusCtx = document.getElementById('statusChart')?.getContext('2d');
    if (statusCtx && <?php echo json_encode(!empty($report_data['status_chart']['labels'] ?? [])); ?>) {
        new Chart(statusCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($report_data['status_chart']['labels'] ?? []); ?>,
                datasets: [{
                    data: <?php echo json_encode($report_data['status_chart']['data'] ?? []); ?>,
                    backgroundColor: ['#28a745', '#17a2b8', '#ffc107', '#dc3545', '#6c757d'],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    }
                }
            }
        });
    }
});

function exportReport(format) {
    const url = new URL(window.location.href);
    url.searchParams.set('export', format);
    window.location.href = url.toString();
}

function formatCurrency(amount) {
    return 'RWF ' + new Intl.NumberFormat().format(amount || 0);
}
</script>

<?php
/**
 * Get reservation summary
 */
function get_reservation_summary($date_from, $date_to, $status = '', $source = '', $room_type_id = null) {
    $data = [
        'summary' => [
            'total' => 0,
            'total_revenue' => 0,
            'avg_nights' => 0,
            'cancellation_rate' => 0
        ],
        'headers' => ['Date', 'New', 'Confirmed', 'Checked In', 'Checked Out', 'Cancelled', 'Revenue'],
        'rows' => [],
        'chart' => ['labels' => [], 'data' => []],
        'status_chart' => ['labels' => [], 'data' => []],
        'totals' => []
    ];
    
    if (!function_exists('db_fetch_value')) {
        return $data;
    }
    
    try {
        // Build where clause for counts
        $where = "DATE(created_at) BETWEEN ? AND ?";
        $params = [$date_from, $date_to];
        
        if ($room_type_id) {
            $where .= " AND room_type_id = ?";
            $params[] = $room_type_id;
        }
        
        if (!empty($status)) {
            $where .= " AND booking_status = ?";
            $params[] = $status;
        }
        
        if (!empty($source)) {
            $where .= " AND booking_source = ?";
            $params[] = $source;
        }
        
        // Get daily breakdown
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        $total_new = 0;
        $total_confirmed = 0;
        $total_checked_in = 0;
        $total_checked_out = 0;
        $total_cancelled = 0;
        $total_revenue = 0;
        
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            
            $new = (int)db_fetch_value("
                SELECT COUNT(*) FROM reservations 
                WHERE DATE(created_at) = ? AND booking_status NOT IN ('cancelled')
            ", [$date]) ?: 0;
            
            $confirmed = (int)db_fetch_value("
                SELECT COUNT(*) FROM reservations 
                WHERE DATE(created_at) = ? AND booking_status = 'confirmed'
            ", [$date]) ?: 0;
            
            $checked_in = (int)db_fetch_value("
                SELECT COUNT(*) FROM reservations 
                WHERE DATE(created_at) = ? AND booking_status = 'checked_in'
            ", [$date]) ?: 0;
            
            $checked_out = (int)db_fetch_value("
                SELECT COUNT(*) FROM reservations 
                WHERE DATE(created_at) = ? AND booking_status = 'checked_out'
            ", [$date]) ?: 0;
            
            $cancelled = (int)db_fetch_value("
                SELECT COUNT(*) FROM reservations 
                WHERE DATE(updated_at) = ? AND booking_status = 'cancelled'
            ", [$date]) ?: 0;
            
            $revenue = (float)db_fetch_value("
                SELECT COALESCE(SUM(total_amount), 0) FROM reservations 
                WHERE DATE(created_at) = ? AND booking_status != 'cancelled'
            ", [$date]) ?: 0;
            
            $data['rows'][] = [
                format_date($date),
                $new,
                $confirmed,
                $checked_in,
                $checked_out,
                $cancelled,
                format_currency($revenue)
            ];
            
            $data['chart']['labels'][] = date('M d', $current);
            $data['chart']['data'][] = $new;
            
            $total_new += $new;
            $total_confirmed += $confirmed;
            $total_checked_in += $checked_in;
            $total_checked_out += $checked_out;
            $total_cancelled += $cancelled;
            $total_revenue += $revenue;
            
            $current = strtotime('+1 day', $current);
        }
        
        $data['summary']['total'] = $total_new;
        $data['summary']['total_revenue'] = $total_revenue;
        $data['summary']['avg_nights'] = 2.5; // This would need actual calculation
        $data['summary']['cancellation_rate'] = $total_new > 0 ? round(($total_cancelled / $total_new) * 100, 1) : 0;
        
        $data['totals'] = ['TOTAL', $total_new, $total_confirmed, $total_checked_in, $total_checked_out, $total_cancelled, format_currency($total_revenue)];
        
        $data['status_chart']['labels'] = ['Confirmed', 'Checked In', 'Checked Out', 'Cancelled', 'Other'];
        $data['status_chart']['data'] = [
            $total_confirmed, 
            $total_checked_in, 
            $total_checked_out, 
            $total_cancelled, 
            max(0, $total_new - $total_confirmed - $total_checked_in - $total_checked_out - $total_cancelled)
        ];
        
    } catch (Exception $e) {
        error_log("Error in get_reservation_summary: " . $e->getMessage());
    }
    
    return $data;
}

/**
 * Get arrivals report
 */
function get_arrivals_report($date_from, $date_to, $room_type_id = null) {
    $data = [
        'summary' => [
            'total' => 0,
            'total_revenue' => 0,
            'avg_nights' => 0,
            'cancellation_rate' => 0
        ],
        'headers' => ['Date', 'Arrivals', 'Nights', 'Revenue', 'Avg Rate'],
        'rows' => [],
        'chart' => ['labels' => [], 'data' => []],
        'status_chart' => ['labels' => [], 'data' => []]
    ];
    
    if (!function_exists('db_fetch_one')) {
        return $data;
    }
    
    try {
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        $total_arrivals = 0;
        $total_nights = 0;
        $total_revenue = 0;
        
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            
            $sql = "SELECT COUNT(*) as count, COALESCE(SUM(nights), 0) as nights, COALESCE(SUM(total_amount), 0) as revenue
                    FROM reservations 
                    WHERE check_in_date = ? AND booking_status IN ('confirmed', 'checked_in')";
            $params = [$date];
            
            if ($room_type_id) {
                $sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $result = db_fetch_one($sql, $params) ?: ['count' => 0, 'nights' => 0, 'revenue' => 0];
            
            $data['rows'][] = [
                format_date($date),
                $result['count'],
                $result['nights'],
                format_currency($result['revenue']),
                $result['count'] > 0 ? format_currency($result['revenue'] / $result['count']) : '-'
            ];
            
            $data['chart']['labels'][] = date('M d', $current);
            $data['chart']['data'][] = $result['count'];
            
            $total_arrivals += $result['count'];
            $total_nights += $result['nights'];
            $total_revenue += $result['revenue'];
            
            $current = strtotime('+1 day', $current);
        }
        
        $data['summary']['total'] = $total_arrivals;
        $data['summary']['total_revenue'] = $total_revenue;
        $data['summary']['avg_nights'] = $total_arrivals > 0 ? round($total_nights / $total_arrivals, 1) : 0;
        
    } catch (Exception $e) {
        error_log("Error in get_arrivals_report: " . $e->getMessage());
    }
    
    return $data;
}

/**
 * Get cancellations report
 */
function get_cancellations_report($date_from, $date_to, $room_type_id = null) {
    $data = [
        'summary' => [
            'total' => 0,
            'total_revenue' => 0,
            'avg_nights' => 0,
            'cancellation_rate' => 0
        ],
        'headers' => ['Date', 'Cancellations', 'Lost Revenue', 'Avg Rate'],
        'rows' => [],
        'chart' => ['labels' => [], 'data' => []],
        'status_chart' => ['labels' => [], 'data' => []]
    ];
    
    if (!function_exists('db_fetch_one')) {
        return $data;
    }
    
    try {
        $current = strtotime($date_from);
        $end = strtotime($date_to);
        
        $total_cancelled = 0;
        $total_lost = 0;
        
        while ($current <= $end) {
            $date = date('Y-m-d', $current);
            
            $sql = "SELECT COUNT(*) as count, COALESCE(SUM(total_amount), 0) as revenue
                    FROM reservations 
                    WHERE DATE(updated_at) = ? AND booking_status = 'cancelled'";
            $params = [$date];
            
            if ($room_type_id) {
                $sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $result = db_fetch_one($sql, $params) ?: ['count' => 0, 'revenue' => 0];
            
            $data['rows'][] = [
                format_date($date),
                $result['count'],
                format_currency($result['revenue']),
                $result['count'] > 0 ? format_currency($result['revenue'] / $result['count']) : '-'
            ];
            
            $data['chart']['labels'][] = date('M d', $current);
            $data['chart']['data'][] = $result['count'];
            
            $total_cancelled += $result['count'];
            $total_lost += $result['revenue'];
            
            $current = strtotime('+1 day', $current);
        }
        
        $data['summary']['total'] = $total_cancelled;
        $data['summary']['total_revenue'] = $total_lost;
        
    } catch (Exception $e) {
        error_log("Error in get_cancellations_report: " . $e->getMessage());
    }
    
    return $data;
}

/**
 * Get source analysis
 */
function get_source_analysis($date_from, $date_to, $room_type_id = null) {
    $data = [
        'summary' => [
            'total' => 0,
            'total_revenue' => 0,
            'avg_nights' => 0,
            'cancellation_rate' => 0
        ],
        'headers' => ['Source', 'Bookings', 'Revenue', 'Avg Rate', 'Nights', 'Cancellations'],
        'rows' => [],
        'chart' => ['labels' => [], 'data' => []],
        'status_chart' => ['labels' => [], 'data' => []]
    ];
    
    if (!function_exists('db_fetch_one')) {
        return $data;
    }
    
    try {
        $sources = [
            'direct' => 'Direct',
            'website' => 'Website',
            'phone' => 'Phone',
            'email' => 'Email',
            'walk_in' => 'Walk-in',
            'agent' => 'Travel Agent',
            'ota' => 'OTA'
        ];
        
        $total_bookings = 0;
        $total_revenue = 0;
        
        foreach ($sources as $code => $name) {
            $sql = "SELECT 
                        COUNT(*) as bookings,
                        COALESCE(SUM(total_amount), 0) as revenue,
                        COALESCE(SUM(nights), 0) as nights,
                        SUM(CASE WHEN booking_status = 'cancelled' THEN 1 ELSE 0 END) as cancelled
                    FROM reservations 
                    WHERE booking_source = ? 
                    AND DATE(created_at) BETWEEN ? AND ?";
            $params = [$code, $date_from, $date_to];
            
            if ($room_type_id) {
                $sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $result = db_fetch_one($sql, $params) ?: ['bookings' => 0, 'revenue' => 0, 'nights' => 0, 'cancelled' => 0];
            
            $data['rows'][] = [
                $name,
                $result['bookings'],
                format_currency($result['revenue']),
                $result['bookings'] > 0 ? format_currency($result['revenue'] / $result['bookings']) : '-',
                $result['nights'],
                $result['cancelled']
            ];
            
            if ($result['bookings'] > 0) {
                $data['chart']['labels'][] = $name;
                $data['chart']['data'][] = $result['bookings'];
            }
            
            $total_bookings += $result['bookings'];
            $total_revenue += $result['revenue'];
        }
        
        $data['summary']['total'] = $total_bookings;
        $data['summary']['total_revenue'] = $total_revenue;
        
    } catch (Exception $e) {
        error_log("Error in get_source_analysis: " . $e->getMessage());
    }
    
    return $data;
}

/**
 * Get lead time analysis
 */
function get_lead_time_analysis($date_from, $date_to, $room_type_id = null) {
    $data = [
        'summary' => [
            'total' => 0,
            'total_revenue' => 0,
            'avg_nights' => 0,
            'cancellation_rate' => 0
        ],
        'headers' => ['Lead Time', 'Bookings', 'Revenue', 'Avg Rate', 'Cancellation %'],
        'rows' => [],
        'chart' => ['labels' => [], 'data' => []],
        'status_chart' => ['labels' => [], 'data' => []]
    ];
    
    if (!function_exists('db_fetch_one')) {
        return $data;
    }
    
    try {
        $ranges = [
            '0-7' => '0-7 days',
            '8-30' => '8-30 days',
            '31-60' => '31-60 days',
            '61-90' => '61-90 days',
            '90-999' => '90+ days'
        ];
        
        $total_bookings = 0;
        
        foreach ($ranges as $key => $label) {
            list($min, $max) = explode('-', $key);
            
            $sql = "SELECT 
                        COUNT(*) as bookings,
                        COALESCE(SUM(total_amount), 0) as revenue,
                        SUM(CASE WHEN booking_status = 'cancelled' THEN 1 ELSE 0 END) as cancelled
                    FROM reservations 
                    WHERE DATE(created_at) BETWEEN ? AND ?
                    AND DATEDIFF(check_in_date, created_at) BETWEEN ? AND ?";
            $params = [$date_from, $date_to, $min, $max];
            
            if ($room_type_id) {
                $sql .= " AND room_type_id = ?";
                $params[] = $room_type_id;
            }
            
            $result = db_fetch_one($sql, $params) ?: ['bookings' => 0, 'revenue' => 0, 'cancelled' => 0];
            
            $cancel_rate = $result['bookings'] > 0 ? round(($result['cancelled'] / $result['bookings']) * 100, 1) : 0;
            
            $data['rows'][] = [
                $label,
                $result['bookings'],
                format_currency($result['revenue']),
                $result['bookings'] > 0 ? format_currency($result['revenue'] / $result['bookings']) : '-',
                $cancel_rate . '%'
            ];
            
            if ($result['bookings'] > 0) {
                $data['chart']['labels'][] = $label;
                $data['chart']['data'][] = $result['bookings'];
            }
            
            $total_bookings += $result['bookings'];
        }
        
        $data['summary']['total'] = $total_bookings;
        
    } catch (Exception $e) {
        error_log("Error in get_lead_time_analysis: " . $e->getMessage());
    }
    
    return $data;
}

/**
 * Format date
 */
if (!function_exists('format_date')) {
    function format_date($date) {
        if (empty($date)) return '';
        return date('M d, Y', strtotime($date));
    }
}

/**
 * Format currency
 */
if (!function_exists('format_currency')) {
    function format_currency($amount) {
        return 'RWF ' . number_format($amount ?? 0);
    }
}

/**
 * Export as PDF
 */
function export_pdf($data, $type, $date_from, $date_to) {
    header('Location: /HMSPOS/reports/export-pdf.php?report=reservations&type=' . $type . '&from=' . $date_from . '&to=' . $date_to);
    exit;
}

/**
 * Export as Excel
 */
function export_excel($data, $type, $date_from, $date_to) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="reservations_report_' . $type . '_' . $date_from . '_to_' . $date_to . '.xls"');
    
    echo "Reservations Report\n";
    echo "Type: " . ucfirst(str_replace('_', ' ', $type)) . "\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n\n";
    
    echo "Summary\n";
    echo "Total Reservations\t" . ($data['summary']['total'] ?? 0) . "\n";
    echo "Total Revenue\t" . format_currency($data['summary']['total_revenue'] ?? 0) . "\n";
    echo "Average Nights\t" . ($data['summary']['avg_nights'] ?? 0) . "\n";
    echo "Cancellation Rate\t" . ($data['summary']['cancellation_rate'] ?? 0) . "%\n\n";
    
    echo implode("\t", $data['headers'] ?? []) . "\n";
    foreach ($data['rows'] ?? [] as $row) {
        echo implode("\t", $row) . "\n";
    }
    
    if (!empty($data['totals'] ?? [])) {
        echo implode("\t", $data['totals']) . "\n";
    }
    exit;
}

/**
 * Export as CSV
 */
function export_csv($data, $type, $date_from, $date_to) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="reservations_report_' . $type . '_' . $date_from . '_to_' . $date_to . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['Reservations Report']);
    fputcsv($output, ['Type:', ucfirst(str_replace('_', ' ', $type))]);
    fputcsv($output, ['Period:', format_date($date_from) . ' to ' . format_date($date_to)]);
    fputcsv($output, []);
    
    fputcsv($output, ['Summary']);
    fputcsv($output, ['Total Reservations', $data['summary']['total'] ?? 0]);
    fputcsv($output, ['Total Revenue', format_currency($data['summary']['total_revenue'] ?? 0)]);
    fputcsv($output, ['Average Nights', $data['summary']['avg_nights'] ?? 0]);
    fputcsv($output, ['Cancellation Rate', ($data['summary']['cancellation_rate'] ?? 0) . '%']);
    fputcsv($output, []);
    
    fputcsv($output, $data['headers'] ?? []);
    foreach ($data['rows'] ?? [] as $row) {
        fputcsv($output, $row);
    }
    
    if (!empty($data['totals'] ?? [])) {
        fputcsv($output, $data['totals']);
    }
    
    fclose($output);
    exit;
}

// Include footer
$footer_path = $base_path . '/views/partials/footer.php';
if (file_exists($footer_path)) {
    require_once $footer_path;
} else {
    echo '</div></div></body></html>';
}
?>