<?php
/**
 * Manager Tables API
 * Handles table allocation, merging, server assignment
 * Manager cannot create tables, only manage/allocate them
 */

session_start();
require 'config.php';

header('Content-Type: application/json');

// Release any expired bookings before processing requests
releaseExpiredBookings($conn);

// Check manager authentication
// Accept manager_id from session or from GET parameter (for client-side authentication)
$manager_id = null;

if (isset($_SESSION['manager_id'])) {
    $manager_id = $_SESSION['manager_id'];
} elseif (isset($_GET['manager_id'])) {
    $manager_id = intval($_GET['manager_id']);
    // Validate the manager exists
    $validateRes = $conn->query("SELECT id FROM managers WHERE id = " . $manager_id . " LIMIT 1");
    if (!$validateRes || $validateRes->num_rows === 0) {
        http_response_code(401);
        sendResponse(false, 'Unauthorized access', null);
        exit;
    }
} else {
    http_response_code(401);
    sendResponse(false, 'Unauthorized access', null);
    exit;
}
$method = $_SERVER['REQUEST_METHOD'];
$action = isset($_GET['action']) ? $_GET['action'] : '';

switch ($action) {
    case 'list':
        listAvailableTables();
        break;
    case 'get_by_capacity':
        getTablesByCapacity();
        break;
    case 'allocate':
        allocateTableToCustomer();
        break;
    case 'allocate_guests':
        allocateTableWithGuests();
        break;
    case 'release':
        releaseTable();
        break;
    case 'merge':
        mergeTables();
        break;
    case 'unmerge':
        unmergeTables();
        break;
    case 'set_maintenance':
        setTableMaintenance();
        break;
    case 'exit_maintenance':
        exitTableMaintenance();
        break;
    case 'assign_server':
        assignServerToTable();
        break;
    case 'assign_remaining_seats':
        assignRemainingSeatsToAssignedServer();
        break;
    case 'get_servers':
        getAvailableServers();
        break;
    case 'get_table_servers':
        getTableServers();
        break;
    case 'update_table_status':
        updateTableStatus();
        break;
    case 'get_merged_tables':
        getMergedTables();
        break;
    case 'get_table_details':
        getTableDetails();
        break;
    default:
        sendResponse(false, 'Invalid action', null);
}

function hasTableColumn($table, $column) {
    global $conn;
    $tableEsc = $conn->real_escape_string($table);
    $columnEsc = $conn->real_escape_string($column);
    $res = $conn->query("SHOW COLUMNS FROM `" . $tableEsc . "` LIKE '" . $columnEsc . "'");
    return ($res && $res->num_rows > 0);
}

function ensureServerOrderLimitColumn() {
    global $conn;
    if (!hasTableColumn('servers', 'max_active_orders')) {
        @$conn->query("ALTER TABLE servers ADD COLUMN max_active_orders INT NOT NULL DEFAULT 1 AFTER shift_end");
    }
}

function getServerLoad($server_id) {
    global $conn;

    ensureServerOrderLimitColumn();

    $server_id = intval($server_id);
    $res = $conn->query("SELECT COALESCE(max_active_orders, 1) AS max_active_orders, status FROM servers WHERE id = " . $server_id . " LIMIT 1");
    if (!$res || $res->num_rows === 0) {
        return null;
    }
    $server = $res->fetch_assoc();

    $direct_sql = "SELECT COUNT(*) AS c FROM table_assignments WHERE server_id = " . $server_id . " AND status = 'assigned'";
    $direct_res = $conn->query($direct_sql);
    $direct_count = $direct_res ? intval($direct_res->fetch_assoc()['c'] ?? 0) : 0;

    $online_count = 0;
    if (hasTableColumn('online_table_bookings', 'assigned_server_id')) {
        $online_sql = "SELECT COUNT(*) AS c
                       FROM online_table_bookings otb
                       LEFT JOIN bookings b ON b.id = otb.booking_id
                       WHERE otb.assigned_server_id = " . $server_id . "
                       AND otb.allocation_status = 'allocated'
                       AND (b.id IS NULL OR b.status IN ('pending', 'confirmed', 'checked_in'))";
        $online_res = $conn->query($online_sql);
        $online_count = $online_res ? intval($online_res->fetch_assoc()['c'] ?? 0) : 0;
    }

    return [
        'status' => strtolower(trim((string)($server['status'] ?? 'available'))),
        'max_active_orders' => max(1, intval($server['max_active_orders'] ?? 1)),
        'direct_count' => $direct_count,
        'online_count' => $online_count,
        'active_count' => $direct_count + $online_count
    ];
}

// List all available tables for allocation
function listAvailableTables() {
    global $conn;
    
    $floor = isset($_GET['floor']) ? intval($_GET['floor']) : '';
    $section = isset($_GET['section']) ? $_GET['section'] : '';
    $status = isset($_GET['status']) ? $_GET['status'] : '';
    $booking_date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    
    $sql = "SELECT rt.*, 
            (SELECT COUNT(*) FROM table_assignments 
             WHERE table_id = rt.id AND status = 'assigned') as is_assigned,
            (SELECT ta.server_id FROM table_assignments ta
             WHERE ta.table_id = rt.id AND ta.status = 'assigned'
             ORDER BY ta.assigned_at DESC LIMIT 1) as assigned_server_id,
            (SELECT COALESCE(SUM(ta.number_of_guests), 0) FROM table_assignments ta
             WHERE ta.table_id = rt.id AND ta.status = 'assigned') as number_of_guests,
            (SELECT s.name FROM table_assignments ta
             LEFT JOIN servers s ON ta.server_id = s.id
             WHERE ta.table_id = rt.id AND ta.status = 'assigned'
             ORDER BY ta.assigned_at DESC LIMIT 1) as assigned_server_name,
            CASE 
                WHEN rt.status = 'maintenance' THEN 'maintenance'
                WHEN EXISTS (
                    SELECT 1 FROM table_assignments ta
                    WHERE ta.table_id = rt.id
                    AND ta.status = 'assigned'
                    AND COALESCE(ta.number_of_guests, 0) > 0
                ) THEN 'occupied'
                WHEN EXISTS (
                    SELECT 1 FROM table_assignments ta
                    WHERE ta.table_id = rt.id
                    AND ta.status = 'assigned'
                ) THEN 'booked'
                WHEN EXISTS (
                    SELECT 1 FROM table_bookings tb
                    INNER JOIN bookings b ON tb.booking_id = b.id
                    WHERE tb.table_id = rt.id 
                    AND b.booking_date = '" . $conn->real_escape_string($booking_date) . "'
                    AND b.status IN ('confirmed', 'pending', 'checked_in')
                ) THEN 'booked'
                ELSE 'available'
            END as actual_status
            FROM restaurant_tables rt 
            WHERE 1=1";
    
    if ($floor) {
        $sql .= " AND rt.floor_level = " . $floor;
    }
    if ($section) {
        $sql .= " AND rt.section = '" . $conn->real_escape_string($section) . "'";
    }
    $sql .= " ORDER BY rt.floor_level, rt.section, rt.table_number";
    
    $result = $conn->query($sql);
    
    if ($result) {
        $tables = [];
        while ($row = $result->fetch_assoc()) {
            // Replace the status with actual_status for date-specific bookings
            $row['status'] = $row['actual_status'];
            unset($row['actual_status']);
            $row['table_source'] = 'restaurant';
            if ($status && $row['status'] !== $status && $row['status'] !== 'merged') {
                continue;
            }
            $tables[] = $row;
        }

        // Include online booking tables as view-only cards for manager visibility
        $online_sql = "SELECT ot.id, ot.table_number, ot.base_capacity, ot.current_capacity, ot.floor_level, ot.section, ot.status,
                       0 as is_assigned,
                       NULL as assigned_server_id,
                       0 as number_of_guests,
                       NULL as assigned_server_name,
                       CASE
                           WHEN EXISTS (
                               SELECT 1 FROM online_table_bookings otb
                               INNER JOIN bookings b ON otb.booking_id = b.id
                               WHERE otb.online_table_id = ot.id
                               AND b.booking_date = '" . $conn->real_escape_string($booking_date) . "'
                               AND b.status IN ('confirmed', 'pending', 'checked_in')
                               AND otb.allocation_status = 'allocated'
                           ) THEN 'booked'
                           WHEN ot.status = 'maintenance' THEN 'maintenance'
                           ELSE 'available'
                       END as actual_status
                       FROM online_tables ot
                       WHERE 1=1";

        if ($floor) {
            $online_sql .= " AND ot.floor_level = " . $floor;
        }
        if ($section) {
            $online_sql .= " AND ot.section = '" . $conn->real_escape_string($section) . "'";
        }

        $online_sql .= " ORDER BY ot.floor_level, ot.section, ot.table_number";
        $online_result = $conn->query($online_sql);

        if ($online_result) {
            while ($row = $online_result->fetch_assoc()) {
                $row['status'] = $row['actual_status'];
                unset($row['actual_status']);
                $row['table_source'] = 'online';
                if ($status && $row['status'] !== $status) {
                    continue;
                }
                $tables[] = $row;
            }
        }

        sendResponse(true, 'Tables retrieved', $tables);
    } else {
        sendResponse(false, 'Error retrieving tables: ' . $conn->error, null);
    }
}

// Get tables by capacity
function getTablesByCapacity() {
    global $conn;
    
    $capacity = isset($_GET['capacity']) ? intval($_GET['capacity']) : 0;
    
    if ($capacity <= 0) {
        sendResponse(false, 'Invalid capacity', null);
    }
    
    $sql = "SELECT rt.* FROM restaurant_tables rt 
            WHERE rt.current_capacity >= " . $capacity . " 
            AND rt.status = 'available'
            AND rt.id NOT IN (
                SELECT table_id FROM table_assignments 
                WHERE status = 'assigned'
            )
            ORDER BY rt.current_capacity ASC, rt.floor_level, rt.section";
    
    $result = $conn->query($sql);
    
    if ($result) {
        $tables = [];
        while ($row = $result->fetch_assoc()) {
            $tables[] = $row;
        }
        sendResponse(true, 'Tables retrieved', $tables);
    } else {
        sendResponse(false, 'Error retrieving tables', null);
    }
}

// Allocate table to customer (from booking or walk-in)
function allocateTableToCustomer() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    $required_fields = ['table_id'];
    foreach ($required_fields as $field) {
        if (!isset($data[$field])) {
            sendResponse(false, 'Missing required field: ' . $field, null);
        }
    }
    
    $table_id = intval($data['table_id']);
    $booking_id = isset($data['booking_id']) ? intval($data['booking_id']) : null;
    $customer_name = isset($data['customer_name']) ? $data['customer_name'] : '';
    $notes = isset($data['notes']) ? $data['notes'] : '';
    $booking_date = isset($data['booking_date']) ? trim($data['booking_date']) : date('Y-m-d');
    
    // Check if table exists and is not blocked by base status.
    $check_sql = "SELECT id FROM restaurant_tables 
                  WHERE id = " . $table_id . " AND status NOT IN ('maintenance', 'merged')";
    $check_result = $conn->query($check_sql);
    
    if ($check_result->num_rows === 0) {
        sendResponse(false, 'Table is not available', null);
    }

    // Check if table is booked for the selected date.
    $booking_check_sql = "SELECT COUNT(*) AS booking_count
                          FROM table_bookings tb
                          INNER JOIN bookings b ON tb.booking_id = b.id
                          WHERE tb.table_id = " . $table_id . "
                          AND b.booking_date = '" . $conn->real_escape_string($booking_date) . "'
                          AND b.status IN ('confirmed', 'pending', 'checked_in')";
    $booking_check_result = $conn->query($booking_check_sql);
    if ($booking_check_result) {
        $booking_row = $booking_check_result->fetch_assoc();
        if (intval($booking_row['booking_count']) > 0) {
            sendResponse(false, 'Table is booked for ' . $booking_date, null);
        }
    }
    
    // Check if table is already assigned
    $assigned_sql = "SELECT id FROM table_assignments 
                     WHERE table_id = " . $table_id . " AND status = 'assigned'";
    $assigned_result = $conn->query($assigned_sql);
    
    if ($assigned_result->num_rows > 0) {
        sendResponse(false, 'Table is already assigned', null);
    }
    
    // Create assignment
    $insert_sql = "INSERT INTO table_assignments 
                   (table_id, manager_id, notes, status) 
                   VALUES (" . $table_id . ", " . $manager_id . ", '" . 
                   $conn->real_escape_string($notes) . "', 'assigned')";
    
    if ($conn->query($insert_sql)) {
        $assignment_id = $conn->insert_id;
        
        // Update table status
        $update_sql = "UPDATE restaurant_tables SET status = 'booked' WHERE id = " . $table_id;
        $conn->query($update_sql);
        
        sendResponse(true, 'Table allocated successfully', [
            'assignment_id' => $assignment_id,
            'table_id' => $table_id
        ]);
    } else {
        sendResponse(false, 'Error allocating table: ' . $conn->error, null);
    }
}

// Allocate table with number of guests (new workflow)
function allocateTableWithGuests() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    $required_fields = ['table_id', 'number_of_guests'];
    foreach ($required_fields as $field) {
        if (!isset($data[$field])) {
            sendResponse(false, 'Missing required field: ' . $field, null);
        }
    }
    
    $table_id = intval($data['table_id']);
    $number_of_guests = intval($data['number_of_guests']);
    $booking_date = isset($data['booking_date']) ? trim($data['booking_date']) : date('Y-m-d');
    
    // Validate number of guests
    if ($number_of_guests <= 0) {
        sendResponse(false, 'Invalid number of guests', null);
    }
    
    // Check if table exists and is not blocked by base status, then evaluate date-wise availability.
    $check_sql = "SELECT id, current_capacity FROM restaurant_tables 
                  WHERE id = " . $table_id . " AND status NOT IN ('maintenance', 'merged')";
    $check_result = $conn->query($check_sql);
    
    if ($check_result->num_rows === 0) {
        sendResponse(false, 'Table is not available', null);
    }
    
    $table = $check_result->fetch_assoc();

    // Block allocation if table has an active booking on selected date.
    $booking_check_sql = "SELECT COUNT(*) AS booking_count
                          FROM table_bookings tb
                          INNER JOIN bookings b ON tb.booking_id = b.id
                          WHERE tb.table_id = " . $table_id . "
                          AND b.booking_date = '" . $conn->real_escape_string($booking_date) . "'
                          AND b.status IN ('confirmed', 'pending', 'checked_in')";
    $booking_check_result = $conn->query($booking_check_sql);
    if ($booking_check_result) {
        $booking_row = $booking_check_result->fetch_assoc();
        if (intval($booking_row['booking_count']) > 0) {
            sendResponse(false, 'Table is booked for ' . $booking_date, null);
        }
    }
    
    // Validate capacity
    if ($number_of_guests > $table['current_capacity']) {
        sendResponse(false, 'Number of guests exceeds table capacity of ' . $table['current_capacity'], null);
    }
    
    try {
        $conn->begin_transaction();
        
        // Create assignment with occupied status
        $insert_sql = "INSERT INTO table_assignments 
                       (table_id, manager_id, number_of_guests, status) 
                       VALUES (" . $table_id . ", " . $manager_id . ", " . $number_of_guests . ", 'assigned')";
        
        if (!$conn->query($insert_sql)) {
            throw new Exception('Failed to create table assignment: ' . $conn->error);
        }
        
        $assignment_id = $conn->insert_id;
        
        // Create customer records for each guest
        $created_customers = [];
        for ($i = 1; $i <= $number_of_guests; $i++) {
            $customer_name = 'Guest ' . $i;
            
            $customer_insert = "INSERT INTO table_customers 
                               (assignment_id, table_id, customer_name, seat_number, status) 
                               VALUES (" . $assignment_id . ", " . $table_id . ", '" . 
                               $conn->real_escape_string($customer_name) . "', " . $i . ", 'seated')";
            
            if (!$conn->query($customer_insert)) {
                throw new Exception('Failed to create customer record: ' . $conn->error);
            }
            
            $created_customers[] = [
                'customer_id' => $conn->insert_id,
                'customer_name' => $customer_name,
                'seat_number' => $i
            ];
        }
        
        // Update table status to occupied
        $update_sql = "UPDATE restaurant_tables SET status = 'occupied' WHERE id = " . $table_id;
        if (!$conn->query($update_sql)) {
            throw new Exception('Failed to update table status: ' . $conn->error);
        }
        
        $conn->commit();
        
        sendResponse(true, 'Table allocated successfully', [
            'assignment_id' => $assignment_id,
            'table_id' => $table_id,
            'number_of_guests' => $number_of_guests,
            'customers' => $created_customers
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error allocating table: ' . $e->getMessage(), null);
    }
}

// Release table from customer
function releaseTable() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($data['table_id']);

    try {
        // Backward-compatible migration support
        $ended_col = $conn->query("SHOW COLUMNS FROM table_assignments LIKE 'ended_at'");
        if (!$ended_col || $ended_col->num_rows === 0) {
            @$conn->query("ALTER TABLE table_assignments ADD COLUMN ended_at TIMESTAMP NULL AFTER assigned_at");
        }
        
        // Get the assigned server IDs for this table - fetch ALL into an array first
        $get_servers = "SELECT DISTINCT server_id FROM table_assignments 
                        WHERE table_id = " . $table_id . " AND status = 'assigned'";
        $result = $conn->query($get_servers);
        
        if (!$result) {
            sendResponse(false, 'Database error: ' . $conn->error, null);
            return;
        }
        
        // Store all server IDs in an array
        $server_ids = [];
        while ($row = $result->fetch_assoc()) {
            $server_ids[] = intval($row['server_id']);
        }
        
        // Mark assignment as completed
        $update_sql = "UPDATE table_assignments 
                       SET status = 'completed', ended_at = NOW()
                       WHERE table_id = " . $table_id . " AND status = 'assigned'";
        
        if (!$conn->query($update_sql)) {
            sendResponse(false, 'Error updating assignments: ' . $conn->error, null);
            return;
        }
        
        // Update table status back to available
        $table_update = "UPDATE restaurant_tables 
                         SET status = 'available'
                         WHERE id = " . $table_id;
        
        if (!$conn->query($table_update)) {
            sendResponse(false, 'Error releasing table: ' . $conn->error, null);
            return;
        }
        
        // Reset server status back to available for each server
        foreach ($server_ids as $server_id) {
            $server_update = "UPDATE servers 
                             SET status = 'available'
                             WHERE id = " . $server_id;
            if (!$conn->query($server_update)) {
                error_log("Failed to update server $server_id: " . $conn->error);
            }
        }
        
        sendResponse(true, 'Table released successfully', null);
        
    } catch (Exception $e) {
        sendResponse(false, 'Error: ' . $e->getMessage(), null);
    }
}

// Merge tables together
function mergeTables() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['primary_table_id']) || !isset($data['merge_table_ids']) || 
        !is_array($data['merge_table_ids'])) {
        sendResponse(false, 'Primary table ID and merge table IDs array are required', null);
    }
    
    $primary_table_id = intval($data['primary_table_id']);
    
    // Convert to integers
    $merge_table_ids = array_map('intval', $data['merge_table_ids']);
    
    if (count($merge_table_ids) < 1) {
        sendResponse(false, 'At least 1 table required to merge with primary table', null);
    }
    
    // Start transaction
    $conn->begin_transaction();
    
    try {
        // Get all table info including status
        $all_ids = array_merge([$primary_table_id], $merge_table_ids);
        $ids_str = implode(',', $all_ids);
        
        $result = $conn->query("SELECT id, table_number, base_capacity, current_capacity, status FROM restaurant_tables WHERE id IN (" . $ids_str . ") ORDER BY id ASC");
        
        $tables = [];
        $total_capacity = 0;
        $table_names = [];
        $table_info = [];
        $main_table_status = '';
        
        while ($row = $result->fetch_assoc()) {
            $tables[] = $row;
            $total_capacity += $row['current_capacity'];
            $table_names[] = $row['table_number'];
            $table_info[] = [
                'id' => $row['id'],
                'name' => $row['table_number'],
                'base_capacity' => $row['base_capacity'],
                'current_capacity' => $row['current_capacity'], // Store current (edited) capacity
                'status' => $row['status']
            ];
            
            // Store the main table's original status
            if ($row['id'] == $primary_table_id) {
                $main_table_status = $row['status'];
            }
        }
        
        // Create merged table name with "+" notation
        $merged_table_name = implode(' + ', $table_names) . ' (Merged)';
        
        // Store table info for unmerging as JSON
        $merge_info = json_encode($table_info);
        
        // Update main table with merged name and total capacity, set status to available for booking
        $merged_name_escaped = $conn->real_escape_string($merged_table_name);
        $notes_value = $conn->real_escape_string("Merge Info: " . $merge_info);
        
        $conn->query("UPDATE restaurant_tables 
                      SET current_capacity = " . $total_capacity . ", 
                          table_number = '" . $merged_name_escaped . "', 
                          status = 'available',
                          notes = '" . $notes_value . "' 
                      WHERE id = " . $primary_table_id);
        
        // Mark other tables as merged (hidden from regular display)
        foreach ($merge_table_ids as $merge_table_id) {
            $conn->query("UPDATE restaurant_tables SET status = 'merged' WHERE id = " . $merge_table_id);
        }
        
        $conn->commit();
        
        // Fetch and return the updated main table
        $main_result = $conn->query("SELECT * FROM restaurant_tables WHERE id = " . $primary_table_id);
        $updated_table = $main_result->fetch_assoc();
        
        sendResponse(true, 'Tables merged successfully', [
            'primary_table_id' => $primary_table_id,
            'merged_table' => $updated_table,
            'total_capacity' => $total_capacity,
            'merged_ids' => $merge_table_ids
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error merging tables: ' . $e->getMessage(), null);
    }
}

// Unmerge tables
function unmergeTables() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['main_table_id'])) {
        sendResponse(false, 'Main table ID is required', null);
    }
    
    $main_table_id = intval($data['main_table_id']);
    
    $conn->begin_transaction();
    
    try {
        // Get the main table with merge info in notes
        $main_result = $conn->query("SELECT id, base_capacity, table_number, notes FROM restaurant_tables WHERE id = " . $main_table_id);
        
        if ($main_result->num_rows === 0) {
            sendResponse(false, 'Main table not found', null);
            return;
        }
        
        $main_row = $main_result->fetch_assoc();
        
        // Extract merge info from notes
        $notes = $main_row['notes'];
        $table_info = [];
        
        // Try new format: Merge Info with full table details including status
        if (preg_match('/Merge Info:\s*(\[[\s\S]*?\])/m', $notes, $matches)) {
            $json_str = $matches[1];
            // Remove escaped quotes if present
            $json_str = str_replace('\"', '"', $json_str);
            $table_info = json_decode($json_str, true);
        }
        
        // If we don't have stored info, we can't unmerge properly
        if (empty($table_info)) {
            sendResponse(false, 'Merge information not found. Cannot unmerge.', null);
            return;
        }
        
        // Restore each table to its original state
        foreach ($table_info as $info) {
            $table_id = intval($info['id']);
            $original_name = $info['name'];
            // Restore to current_capacity (which includes edits), not base_capacity
            $restore_capacity = isset($info['current_capacity']) ? intval($info['current_capacity']) : intval($info['base_capacity']);
            $original_status = isset($info['status']) ? $info['status'] : 'available';
            
            $original_name_escaped = $conn->real_escape_string($original_name);
            
            $conn->query("UPDATE restaurant_tables 
                          SET table_number = '" . $original_name_escaped . "', 
                              current_capacity = " . $restore_capacity . ", 
                              status = '" . $conn->real_escape_string($original_status) . "', 
                              notes = '' 
                          WHERE id = " . $table_id);
            
            if ($conn->error) {
                throw new Exception("Error updating table " . $table_id . ": " . $conn->error);
            }
        }
        
        $conn->commit();
        sendResponse(true, 'Tables unmerged successfully', ['main_table_id' => $main_table_id]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error unmerging tables: ' . $e->getMessage(), null);
    }
}

// Set table to maintenance
function setTableMaintenance() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($data['table_id']);
    $reason = isset($data['reason']) ? $conn->real_escape_string($data['reason']) : '';
    
    $conn->begin_transaction();
    
    try {
        // Update table to maintenance status
        $sql = "UPDATE restaurant_tables 
                SET status = 'maintenance' 
                WHERE id = " . $table_id;
        
        if (!$conn->query($sql)) {
            throw new Exception('Failed to update table status');
        }
        
        // Log maintenance record if table_maintenance table exists
        $log_sql = "INSERT INTO table_maintenance_log 
                    (table_id, manager_id, reason, started_at) 
                    VALUES (" . $table_id . ", " . $manager_id . ", '" . $reason . "', NOW())
                    ON DUPLICATE KEY UPDATE reason = '" . $reason . "', started_at = NOW()";
        
        // Attempt to log, but don't fail if table doesn't exist
        @$conn->query($log_sql);
        
        $conn->commit();
        
        sendResponse(true, 'Table set to maintenance', [
            'table_id' => $table_id,
            'status' => 'maintenance'
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error setting table to maintenance: ' . $e->getMessage(), null);
    }
}

// Exit maintenance mode
function exitTableMaintenance() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($data['table_id']);
    
    $conn->begin_transaction();
    
    try {
        // Update table back to available status
        $sql = "UPDATE restaurant_tables 
                SET status = 'available' 
                WHERE id = " . $table_id;
        
        if (!$conn->query($sql)) {
            throw new Exception('Failed to update table status');
        }
        
        // Update maintenance log end time if exists
        $log_update = "UPDATE table_maintenance_log 
                       SET completed_at = NOW() 
                       WHERE table_id = " . $table_id . " AND completed_at IS NULL";
        
        @$conn->query($log_update);
        
        $conn->commit();
        
        sendResponse(true, 'Table restored from maintenance', [
            'table_id' => $table_id,
            'status' => 'available'
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error exiting maintenance: ' . $e->getMessage(), null);
    }
}

// Assign server to table
function assignServerToTable() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['table_id']) || !isset($data['server_id'])) {
        sendResponse(false, 'Table ID and Server ID are required', null);
    }
    
    $table_id = intval($data['table_id']);
    $server_id = intval($data['server_id']);

    // Backward-compatible migration support
    $ended_col = $conn->query("SHOW COLUMNS FROM table_assignments LIKE 'ended_at'");
    if (!$ended_col || $ended_col->num_rows === 0) {
        @$conn->query("ALTER TABLE table_assignments ADD COLUMN ended_at TIMESTAMP NULL AFTER assigned_at");
    }
    
    // Check if table assignment exists
    $assignment_sql = "SELECT id, server_id, notes, number_of_guests FROM table_assignments 
                       WHERE table_id = " . $table_id . " AND status = 'assigned'
                       ORDER BY assigned_at DESC LIMIT 1";
    $assignment_result = $conn->query($assignment_sql);

    $new_assignment_id = null;
    $existing_assignment_server_id = 0;
    if ($assignment_result && $assignment_result->num_rows > 0) {
        $peek = $assignment_result->fetch_assoc();
        $existing_assignment_server_id = intval($peek['server_id'] ?? 0);
        // Rewind by rerunning the same query for existing flow below.
        $assignment_result = $conn->query($assignment_sql);
    }

    $server_load = getServerLoad($server_id);
    if (!$server_load) {
        sendResponse(false, 'Server not found', null);
    }
    if ($server_load['status'] === 'offline' || $server_load['status'] === 'break') {
        sendResponse(false, 'Server is not available for assignment', null);
    }
    $effective_active = $server_load['active_count'];
    if ($existing_assignment_server_id > 0 && $existing_assignment_server_id === $server_id) {
        $effective_active = max(0, $effective_active - 1);
    }
    if ($effective_active >= $server_load['max_active_orders']) {
        sendResponse(false, 'Server has reached active order limit (' . $server_load['max_active_orders'] . ')', null);
    }

    // If no active assignment exists, create one (for booked/occupied tables)
    if (!$assignment_result || $assignment_result->num_rows === 0) {
        // Check if table exists and get its status
        $table_sql = "SELECT status FROM restaurant_tables WHERE id = " . $table_id;
        $table_result = $conn->query($table_sql);
        
        if ($table_result->num_rows === 0) {
            sendResponse(false, 'Table does not exist', null);
        }
        
        $table = $table_result->fetch_assoc();
        
        // Only create assignment for occupied or booked tables
        if ($table['status'] !== 'occupied' && $table['status'] !== 'booked') {
            sendResponse(false, 'Cannot assign server to a ' . $table['status'] . ' table', null);
        }
        
        // Create fresh assignment cycle
        $create_sql = "INSERT INTO table_assignments 
                       (table_id, server_id, manager_id, status) 
                       VALUES (" . $table_id . ", " . $server_id . ", " . $manager_id . ", 'assigned')";
        
        if (!$conn->query($create_sql)) {
            sendResponse(false, 'Error creating assignment: ' . $conn->error, null);
        }
        $new_assignment_id = $conn->insert_id;
    } else {
        // Reassignment flow: close current cycle, then create a new one.
        $existing_assignment = $assignment_result->fetch_assoc();
        $existing_id = (int)$existing_assignment['id'];
        $existing_notes = isset($existing_assignment['notes']) ? $existing_assignment['notes'] : '';
        $existing_guests = isset($existing_assignment['number_of_guests']) ? intval($existing_assignment['number_of_guests']) : 0;

        $close_sql = "UPDATE table_assignments
                      SET status = 'completed', ended_at = NOW()
                      WHERE id = " . $existing_id;
        if (!$conn->query($close_sql)) {
            sendResponse(false, 'Error closing previous assignment: ' . $conn->error, null);
        }

        if ($existing_guests > 0) {
            $create_sql = "INSERT INTO table_assignments
                           (table_id, server_id, manager_id, status, notes, number_of_guests)
                           VALUES (" . $table_id . ", " . $server_id . ", " . $manager_id . ", 'assigned', '" . $conn->real_escape_string($existing_notes) . "', " . $existing_guests . ")";
        } else {
            $create_sql = "INSERT INTO table_assignments
                           (table_id, server_id, manager_id, status, notes)
                           VALUES (" . $table_id . ", " . $server_id . ", " . $manager_id . ", 'assigned', '" . $conn->real_escape_string($existing_notes) . "')";
        }
        if (!$conn->query($create_sql)) {
            sendResponse(false, 'Error creating new assignment cycle: ' . $conn->error, null);
        }
        $new_assignment_id = $conn->insert_id;
    }

    if ($new_assignment_id) {
        // Update server status
        $server_update = "UPDATE servers 
                          SET current_table_id = " . $table_id . ", status = 'busy' 
                          WHERE id = " . $server_id;
        $conn->query($server_update);
        
        sendResponse(true, 'Server assigned successfully', [
            'assignment_id' => $new_assignment_id
        ]);
    } else {
        sendResponse(false, 'Error assigning server', null);
    }
}

// Assign additional guests on a table to its currently assigned server (with customer tracking)
function assignRemainingSeatsToAssignedServer() {
    global $conn;

    // Get manager_id from session
    if (!isset($_SESSION['manager_id'])) {
        sendResponse(false, 'Manager not authenticated', null);
    }
    $manager_id = $_SESSION['manager_id'];

    $data = json_decode(file_get_contents("php://input"), true);

    if (!isset($data['table_id']) || !isset($data['additional_guests'])) {
        sendResponse(false, 'Table ID and additional guests are required', null);
    }

    $table_id = intval($data['table_id']);
    $additional_guests = intval($data['additional_guests']);

    if ($additional_guests <= 0) {
        sendResponse(false, 'Additional guests must be greater than 0', null);
    }

    $table_sql = "SELECT id, current_capacity, status
                  FROM restaurant_tables
                  WHERE id = " . $table_id . " LIMIT 1";
    $table_result = $conn->query($table_sql);

    if (!$table_result || $table_result->num_rows === 0) {
        sendResponse(false, 'Table not found', null);
    }

    $table = $table_result->fetch_assoc();

    if ($table['status'] !== 'occupied' && $table['status'] !== 'booked') {
        sendResponse(false, 'Remaining seats can only be assigned for occupied/booked tables', null);
    }

    $assignment_sql = "SELECT id, server_id, number_of_guests
                       FROM table_assignments
                       WHERE table_id = " . $table_id . " AND status = 'assigned'
                       ORDER BY assigned_at DESC LIMIT 1";
    $assignment_result = $conn->query($assignment_sql);

    if (!$assignment_result || $assignment_result->num_rows === 0) {
        sendResponse(false, 'No active table assignment found. Assign a server first.', null);
    }

    $assignment = $assignment_result->fetch_assoc();
    $assignment_id = intval($assignment['id']);
    $server_id = isset($assignment['server_id']) ? intval($assignment['server_id']) : 0;
    $current_guests = isset($assignment['number_of_guests']) ? intval($assignment['number_of_guests']) : 0;

    if ($server_id <= 0) {
        sendResponse(false, 'No server assigned to this table. Assign a server first.', null);
    }

    $capacity = intval($table['current_capacity']);
    $new_guest_total = $current_guests + $additional_guests;

    if ($new_guest_total > $capacity) {
        $remaining = max(0, $capacity - $current_guests);
        sendResponse(false, 'Cannot assign more than remaining seats. Remaining seats: ' . $remaining, null);
    }

    try {
        $conn->begin_transaction();
        
        // Instead of updating the existing assignment, create a NEW assignment for remaining seats
        // This allows the server to see 2 separate cards (one for original group, one for remaining)
        
        $create_new_assignment_sql = "INSERT INTO table_assignments
                                      (table_id, server_id, manager_id, number_of_guests, status)
                                      VALUES (" . $table_id . ", " . $server_id . ", " . $manager_id . ", " . $additional_guests . ", 'assigned')";
        
        if (!$conn->query($create_new_assignment_sql)) {
            throw new Exception('Failed to create new assignment: ' . $conn->error);
        }
        
        $new_assignment_id = $conn->insert_id;
        
        // Create customer records for the remaining guests
        $created_customers = [];
        for ($i = 1; $i <= $additional_guests; $i++) {
            $customer_name = 'Guest ' . $i;
            $seat_number = $i;
            
            $insert_sql = "INSERT INTO table_customers 
                           (assignment_id, table_id, customer_name, seat_number, status) 
                           VALUES (" . $new_assignment_id . ", " . $table_id . ", '" . 
                           $conn->real_escape_string($customer_name) . "', " . $seat_number . ", 'seated')";
            
            if (!$conn->query($insert_sql)) {
                throw new Exception('Failed to create customer record: ' . $conn->error);
            }
            
            $created_customers[] = [
                'customer_id' => $conn->insert_id,
                'customer_name' => $customer_name,
                'seat_number' => $seat_number
            ];
        }

        // Ensure active dine-in table is marked occupied once guests are assigned
        if ($table['status'] === 'booked') {
            $table_update = "UPDATE restaurant_tables SET status = 'occupied' WHERE id = " . $table_id;
            if (!$conn->query($table_update)) {
                throw new Exception('Failed to update table status: ' . $conn->error);
            }
        }
        
        $conn->commit();

        sendResponse(true, 'Remaining seats assigned successfully as separate group', [
            'table_id' => $table_id,
            'original_assignment_id' => $assignment_id,
            'new_assignment_id' => $new_assignment_id,
            'server_id' => $server_id,
            'remaining_seats_assigned' => $additional_guests,
            'newly_created_customers' => $created_customers,
            'note' => 'New assignment created for remaining seats. Server will see 2 separate cards for this table.'
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error assigning remaining seats: ' . $e->getMessage(), null);
    }
}

// Get available servers
function getAvailableServers() {
    global $conn;

    ensureServerOrderLimitColumn();
    $supports_online = hasTableColumn('online_table_bookings', 'assigned_server_id');

    $onlineCountExpr = "0";
    if ($supports_online) {
        $onlineCountExpr = "(SELECT COUNT(*) 
                             FROM online_table_bookings otb
                             LEFT JOIN bookings b ON b.id = otb.booking_id
                             WHERE otb.assigned_server_id = s.id
                             AND otb.allocation_status = 'allocated'
                             AND (b.id IS NULL OR b.status IN ('pending', 'confirmed', 'checked_in')))";
    }

    $sql = "SELECT s.id, s.name, s.employee_id, s.status, s.rating,
                   COALESCE(s.max_active_orders, 1) AS max_active_orders,
                   (SELECT COUNT(*) FROM table_assignments ta WHERE ta.server_id = s.id AND ta.status = 'assigned') AS direct_active_orders,
                   " . $onlineCountExpr . " AS online_active_orders
            FROM servers s
            WHERE s.status IN ('available', 'busy')
            ORDER BY s.name";

    $result = $conn->query($sql);

    if (!$result) {
        sendResponse(false, 'Error retrieving servers', null);
    }

    $servers = [];
    while ($row = $result->fetch_assoc()) {
        $direct = intval($row['direct_active_orders'] ?? 0);
        $online = intval($row['online_active_orders'] ?? 0);
        $active = $direct + $online;
        $max = max(1, intval($row['max_active_orders'] ?? 1));
        if ($active >= $max) {
            continue;
        }
        $row['active_orders_count'] = $active;
        $row['capacity_left'] = $max - $active;
        $servers[] = $row;
    }

    sendResponse(true, 'Servers retrieved', $servers);
}

// Get servers assigned to table
function getTableServers() {
    global $conn;
    
    if (!isset($_GET['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($_GET['table_id']);
    
    $sql = "SELECT s.id, s.name, s.employee_id, s.status, ta.assigned_at
            FROM servers s
            INNER JOIN table_assignments ta ON s.id = ta.server_id
            WHERE ta.table_id = " . $table_id . " AND ta.status = 'assigned'";
    
    $result = $conn->query($sql);
    
    if ($result) {
        $servers = [];
        while ($row = $result->fetch_assoc()) {
            $servers[] = $row;
        }
        sendResponse(true, 'Servers retrieved', $servers);
    } else {
        sendResponse(false, 'Error retrieving servers', null);
    }
}

// Update table status
function updateTableStatus() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['table_id']) || !isset($data['status'])) {
        sendResponse(false, 'Table ID and status are required', null);
    }
    
    $table_id = intval($data['table_id']);
    $status = $conn->real_escape_string($data['status']);
    
    $valid_statuses = ['available', 'booked', 'maintenance'];
    if (!in_array($status, $valid_statuses)) {
        sendResponse(false, 'Invalid status', null);
    }
    
    $sql = "UPDATE restaurant_tables SET status = '" . $status . "' WHERE id = " . $table_id;
    
    if ($conn->query($sql)) {
        sendResponse(true, 'Table status updated', null);
    } else {
        sendResponse(false, 'Error updating table status', null);
    }
}

// Get merged tables
function getMergedTables() {
    global $conn;
    
    if (!isset($_GET['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($_GET['table_id']);
    
    $sql = "SELECT tm.*, 
            mt.table_number as merged_table_number,
            m.name as manager_name
            FROM table_merges tm
            INNER JOIN restaurant_tables mt ON tm.merged_table_id = mt.id
            LEFT JOIN managers m ON tm.manager_id = m.id
            WHERE tm.primary_table_id = " . $table_id . " AND tm.status = 'active'
            ORDER BY tm.merged_at DESC";
    
    $result = $conn->query($sql);
    
    if ($result) {
        $merges = [];
        while ($row = $result->fetch_assoc()) {
            $merges[] = $row;
        }
        sendResponse(true, 'Merged tables retrieved', $merges);
    } else {
        sendResponse(false, 'Error retrieving merged tables', null);
    }
}

// Get complete table details with assignments and merges
function getTableDetails() {
    global $conn;
    
    if (!isset($_GET['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($_GET['table_id']);
    
    $sql = "SELECT rt.* FROM restaurant_tables rt WHERE rt.id = " . $table_id;
    $result = $conn->query($sql);
    
    if ($result->num_rows === 0) {
        sendResponse(false, 'Table not found', null);
    }
    
    $table = $result->fetch_assoc();
    
    // Get assignment details
    $assignment_sql = "SELECT ta.*, s.name as server_name, s.employee_id
                       FROM table_assignments ta
                       LEFT JOIN servers s ON ta.server_id = s.id
                       WHERE ta.table_id = " . $table_id . " AND ta.status = 'assigned'";
    $assignment_result = $conn->query($assignment_sql);
    $table['assignment'] = $assignment_result->num_rows > 0 ? 
                           $assignment_result->fetch_assoc() : null;
    
    // Get merged tables
    $merges_sql = "SELECT mt.id, mt.table_number FROM table_merges tm
                   INNER JOIN restaurant_tables mt ON tm.merged_table_id = mt.id
                   WHERE tm.primary_table_id = " . $table_id . " AND tm.status = 'active'";
    $merges_result = $conn->query($merges_sql);
    $table['merged_tables'] = [];
    while ($row = $merges_result->fetch_assoc()) {
        $table['merged_tables'][] = $row;
    }
    
    sendResponse(true, 'Table details retrieved', $table);
}
?>
