<?php
// Enhanced table status API for professional dine-in modal
header('Content-Type: application/json');

include '../db.php';

try {
    // Get comprehensive table status with detailed information
    $query = "
        SELECT 
            dt.id,
            dt.table_name,
            dt.seats,
            dt.status,
            df.id as floor_id,
            df.name as floor_name,
            CASE 
                WHEN EXISTS (SELECT 1 FROM hold_orders ho WHERE ho.table_id = dt.id) THEN 'occupied'
                ELSE dt.status
            END as calculated_status,
            COUNT(ho.id) as hold_orders_count,
            SUM(ho.total_amount) as total_hold_amount,
            MAX(ho.created_at) as last_order_time,
            GROUP_CONCAT(DISTINCT ho.customer_name SEPARATOR ', ') as customers,
            (
                SELECT COUNT(*) 
                FROM hold_items hi 
                JOIN hold_orders ho2 ON hi.hold_id = ho2.id 
                WHERE ho2.table_id = dt.id
            ) as total_items
        FROM dinein_tables dt
        LEFT JOIN dinein_floors df ON dt.floor_id = df.id
        LEFT JOIN hold_orders ho ON dt.id = ho.table_id
        WHERE dt.is_active = 1
        GROUP BY dt.id, dt.table_name, dt.seats, dt.status, df.id, df.name
        ORDER BY df.name, dt.table_name
    ";
    
    $result = $conn->query($query);
    
    $tables = [];
    $floors = [];
    $stats = [
        'total_tables' => 0,
        'available_tables' => 0,
        'occupied_tables' => 0,
        'reserved_tables' => 0,
        'total_orders' => 0,
        'total_amount' => 0
    ];
    
    while ($row = $result->fetch_assoc()) {
        $table = [
            'id' => $row['id'],
            'table_name' => $row['table_name'],
            'seats' => $row['seats'],
            'status' => $row['calculated_status'],
            'floor_id' => $row['floor_id'],
            'floor_name' => $row['floor_name'],
            'hold_orders_count' => intval($row['hold_orders_count']),
            'total_hold_amount' => floatval($row['total_hold_amount'] ?? 0),
            'last_order_time' => $row['last_order_time'],
            'customers' => $row['customers'] ?: 'No customers',
            'total_items' => intval($row['total_items']),
            'is_available' => $row['calculated_status'] === 'available'
        ];
        
        $tables[] = $table;
        
        // Group by floors
        if (!isset($floors[$row['floor_id']])) {
            $floors[$row['floor_id']] = [
                'id' => $row['floor_id'],
                'name' => $row['floor_name'],
                'tables' => []
            ];
        }
        $floors[$row['floor_id']]['tables'][] = $table;
        
        // Update statistics
        $stats['total_tables']++;
        switch ($row['calculated_status']) {
            case 'available':
                $stats['available_tables']++;
                break;
            case 'occupied':
                $stats['occupied_tables']++;
                break;
            case 'reserved':
                $stats['reserved_tables']++;
                break;
        }
        $stats['total_orders'] += intval($row['hold_orders_count']);
        $stats['total_amount'] += floatval($row['total_hold_amount'] ?? 0);
    }
    
    // Convert floors array to indexed array
    $floors_array = array_values($floors);
    
    echo json_encode([
        'success' => true,
        'tables' => $tables,
        'floors' => $floors_array,
        'stats' => $stats,
        'timestamp' => date('Y-m-d H:i:s')
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}

$conn->close();
?>
