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

// Initialize system
require_once dirname(__DIR__, 2) . '/config/config.php';
require_once dirname(__DIR__, 2) . '/config/constants.php';
require_once dirname(__DIR__, 2) . '/config/database.php';
require_once dirname(__DIR__, 2) . '/config/helpers.php';
require_once dirname(__DIR__, 2) . '/config/auth.php';

// Require authentication
require_auth();

// Get export parameters
$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;

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $report_type . '_report_' . $date_from . '_to_' . $date_to . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');

// Generate report based on type
switch ($report_type) {
    case 'occupancy':
        export_occupancy($date_from, $date_to, $room_type_id);
        break;
    case 'revenue':
        export_revenue($date_from, $date_to, $export_type);
        break;
    case 'adr_revpar':
        export_adr_revpar($date_from, $date_to, $room_type_id);
        break;
    case 'room_status':
        export_room_status($date_from);
        break;
    case 'reservations':
        export_reservations($date_from, $date_to, $export_type);
        break;
    case 'guest_analytics':
        export_guest_analytics($date_from, $date_to, $export_type);
        break;
    case 'inventory_valuation':
        export_inventory_valuation($date_from, $category_id);
        break;
    case 'audit':
        export_audit($date_from, $date_to);
        break;
    default:
        echo "Invalid report type";
}

/**
 * Export occupancy report
 */
function export_occupancy($date_from, $date_to, $room_type_id) {
    echo "Occupancy Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n\n";
    
    // 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 = $room_type_id";
    }
    $total_rooms = db_fetch_value($total_rooms_sql);
    
    echo "Total Rooms: $total_rooms\n\n";
    echo "Date\tAvailable\tOccupied\tOccupancy %\tRoom Nights\n";
    
    $current = strtotime($date_from);
    $end = strtotime($date_to);
    
    $total_available = 0;
    $total_occupied = 0;
    
    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 = db_fetch_value($occupied_sql, $params);
        $available = $total_rooms - $occupied;
        $occupancy_rate = $total_rooms > 0 ? round(($occupied / $total_rooms) * 100, 1) : 0;
        
        echo format_date($date) . "\t";
        echo $available . "\t";
        echo $occupied . "\t";
        echo $occupancy_rate . "%\t";
        echo $occupied . "\n";
        
        $total_available += $available;
        $total_occupied += $occupied;
        
        $current = strtotime('+1 day', $current);
    }
    
    $days = (strtotime($date_to) - strtotime($date_from)) / 86400 + 1;
    $avg_occupancy = $days > 0 ? round(($total_occupied / ($days * $total_rooms)) * 100, 1) : 0;
    
    echo "\nSummary\n";
    echo "Total Room Nights: $total_occupied\n";
    echo "Average Occupancy: $avg_occupancy%\n";
}

/**
 * Export revenue report
 */
function export_revenue($date_from, $date_to, $revenue_type) {
    echo "Revenue Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n";
    echo "Type: " . ucfirst(str_replace('_', ' ', $revenue_type)) . "\n\n";
    
    echo "Date\tRooms\tF&B\tServices\tOther\tTotal\n";
    
    $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 = 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 = 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 = 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 = 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;
        
        echo format_date($date) . "\t";
        echo format_currency($rooms) . "\t";
        echo format_currency($fnb) . "\t";
        echo format_currency($services) . "\t";
        echo format_currency($other) . "\t";
        echo format_currency($total) . "\n";
        
        $total_rooms += $rooms;
        $total_fnb += $fnb;
        $total_services += $services;
        $total_other += $other;
        $total_all += $total;
        
        $current = strtotime('+1 day', $current);
    }
    
    echo "\nTotals\n";
    echo format_currency($total_rooms) . "\t";
    echo format_currency($total_fnb) . "\t";
    echo format_currency($total_services) . "\t";
    echo format_currency($total_other) . "\t";
    echo format_currency($total_all) . "\n";
}

/**
 * Export ADR/RevPAR report
 */
function export_adr_revpar($date_from, $date_to, $room_type_id) {
    echo "ADR & RevPAR Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n\n";
    
    // 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 = $room_type_id";
    }
    $total_rooms = db_fetch_value($total_rooms_sql);
    
    echo "Total Rooms: $total_rooms\n\n";
    echo "Date\tOccupied\tRevenue\tADR\tRevPAR\n";
    
    $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 = 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 = 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;
        
        echo format_date($date) . "\t";
        echo $occupied . "\t";
        echo format_currency($revenue) . "\t";
        echo format_currency($adr) . "\t";
        echo format_currency($revpar) . "\n";
        
        $current = strtotime('+1 day', $current);
    }
}

/**
 * Export room status report
 */
function export_room_status($date) {
    echo "Room Status Report\n";
    echo "Date: " . format_date($date) . "\n\n";
    
    $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
    ");
    
    echo "Room\tFloor\tType\tStatus\tHK Status\tGuest\tCheck-out\n";
    
    foreach ($rooms as $room) {
        echo $room['room_number'] . "\t";
        echo ($room['floor'] ?: '-') . "\t";
        echo $room['type_name'] . "\t";
        echo $room['status'] . "\t";
        echo $room['housekeeping_status'] . "\t";
        echo ($room['guest_name'] ?: '-') . "\t";
        echo ($room['check_out_date'] ?: '-') . "\n";
    }
}

/**
 * Export reservations report
 */
function export_reservations($date_from, $date_to, $type) {
    echo "Reservations Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n";
    echo "Type: " . ucfirst(str_replace('_', ' ', $type)) . "\n\n";
    
    echo "Date\tBooking #\tGuest\tRoom Type\tCheck-in\tCheck-out\tNights\tTotal\tStatus\n";
    
    $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]);
    
    foreach ($reservations as $res) {
        echo $res['created_date'] . "\t";
        echo $res['booking_code'] . "\t";
        echo $res['first_name'] . ' ' . $res['last_name'] . "\t";
        echo $res['type_name'] . "\t";
        echo $res['check_in_date'] . "\t";
        echo $res['check_out_date'] . "\t";
        echo $res['nights'] . "\t";
        echo format_currency($res['total_amount']) . "\t";
        echo $res['booking_status'] . "\n";
    }
}

/**
 * Export guest analytics
 */
function export_guest_analytics($date_from, $date_to, $type) {
    echo "Guest Analytics Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n";
    echo "Type: " . ucfirst(str_replace('_', ' ', $type)) . "\n\n";
    
    $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]);
    
    echo "Guest Code\tName\tEmail\tPhone\tNationality\tStays\tTotal Spent\tLast Visit\n";
    
    foreach ($guests as $guest) {
        echo $guest['guest_code'] . "\t";
        echo $guest['first_name'] . ' ' . $guest['last_name'] . "\t";
        echo ($guest['email'] ?: '-') . "\t";
        echo ($guest['phone'] ?: '-') . "\t";
        echo ($guest['nationality'] ?: '-') . "\t";
        echo $guest['stays'] . "\t";
        echo format_currency($guest['total_spent']) . "\t";
        echo ($guest['last_visit'] ?: '-') . "\n";
    }
}

/**
 * Export inventory valuation
 */
function export_inventory_valuation($date_as_of, $category_id) {
    echo "Inventory Valuation Report\n";
    echo "As of: " . format_date($date_as_of) . "\n\n";
    
    $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);
    
    echo "Category\tCode\tProduct\tStock\tUnit\tCost Price\tTotal Value\n";
    
    $total_value = 0;
    $total_units = 0;
    
    foreach ($products as $product) {
        echo $product['category_name'] . "\t";
        echo $product['product_code'] . "\t";
        echo $product['product_name'] . "\t";
        echo $product['current_stock'] . "\t";
        echo ($product['unit_of_measure'] ?: 'unit') . "\t";
        echo format_currency($product['cost_price'] ?: 0) . "\t";
        echo format_currency($product['total_value'] ?: 0) . "\n";
        
        $total_value += $product['total_value'] ?: 0;
        $total_units += $product['current_stock'];
    }
    
    echo "\nSummary\n";
    echo "Total Products:\t" . count($products) . "\n";
    echo "Total Units:\t$total_units\n";
    echo "Total Value:\t" . format_currency($total_value) . "\n";
}

/**
 * Export audit trail
 */
function export_audit($date_from, $date_to) {
    echo "Audit Trail Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n\n";
    
    $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
    ", [$date_from, $date_to]);
    
    echo "Timestamp\tUser\tAction\tTable\tRecord ID\tIP Address\n";
    
    foreach ($logs as $log) {
        echo date('Y-m-d H:i:s', strtotime($log['created_at'])) . "\t";
        echo ($log['username'] ?: 'System') . "\t";
        echo $log['action'] . "\t";
        echo ($log['table_name'] ?: '-') . "\t";
        echo ($log['record_id'] ?: '-') . "\t";
        echo ($log['ip_address'] ?: '-') . "\n";
    }
}