<?php
/**
 * Manager POS/Billing API
 * Handles billing, payment processing, and transaction management
 */

session_start();
require 'config.php';

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

// 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 'create_bill':
        createBill();
        break;
    case 'add_item':
        addItemToBill();
        break;
    case 'remove_item':
        removeItemFromBill();
        break;
    case 'update_item':
        updateBillItem();
        break;
    case 'apply_discount':
        applyDiscount();
        break;
    case 'get_bill':
        getBill();
        break;
    case 'get_table_bills':
        getTableBills();
        break;
    case 'complete_bill':
        completeBill();
        break;
    case 'process_payment':
        processPayment();
        break;
    case 'get_open_bills':
        getOpenBills();
        break;
    case 'get_bill_history':
        getBillHistory();
        break;
    case 'reprint_bill':
        reprintBill();
        break;
    case 'split_bill':
        splitBill();
        break;
    case 'get_pending_server_orders':
        getPendingServerOrders();
        break;
    case 'get_server_order_details':
        getServerOrderDetails();
        break;
    case 'convert_server_order_to_bill':
        convertServerOrderToBill();
        break;
    default:
        sendResponse(false, 'Invalid action', null);
}

// Ensure billings can track dine-in assignment scope for partial table release.
function hasTableColumn($table, $column) {
    global $conn;
    $table = $conn->real_escape_string($table);
    $column = $conn->real_escape_string($column);
    $res = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
    return ($res && $res->num_rows > 0);
}

function ensureBillingsDineInColumns() {
    global $conn;

    $check_dine_order_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if (!$check_dine_order_col || $check_dine_order_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN dine_in_order_id INT NULL AFTER order_id");
        @$conn->query("CREATE INDEX idx_billings_dine_in_order_id ON billings(dine_in_order_id)");
    }

    $check_assignment_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'assignment_id'");
    if (!$check_assignment_col || $check_assignment_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN assignment_id INT NULL AFTER dine_in_order_id");
        @$conn->query("CREATE INDEX idx_billings_assignment_id ON billings(assignment_id)");
    }

    $check_source_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'table_source'");
    if (!$check_source_col || $check_source_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN table_source VARCHAR(20) NOT NULL DEFAULT 'restaurant' AFTER table_id");
    }

    $check_source_table_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'source_table_id'");
    if (!$check_source_table_col || $check_source_table_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN source_table_id INT NULL AFTER table_source");
        @$conn->query("CREATE INDEX idx_billings_source_table_id ON billings(source_table_id)");
    }

    $check_display_table_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'display_table_number'");
    if (!$check_display_table_col || $check_display_table_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN display_table_number VARCHAR(50) NULL AFTER source_table_id");
    }

    $check_source_assignment_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'source_assignment_id'");
    if (!$check_source_assignment_col || $check_source_assignment_col->num_rows === 0) {
        @$conn->query("ALTER TABLE billings ADD COLUMN source_assignment_id INT NULL AFTER source_table_id");
        @$conn->query("CREATE INDEX idx_billings_source_assignment_id ON billings(source_assignment_id)");
    }

    // Online-source bills may not have a restaurant table_id.
    $table_id_col = $conn->query("SHOW COLUMNS FROM billings LIKE 'table_id'");
    if ($table_id_col && $table_id_col->num_rows > 0) {
        $table_id_meta = $table_id_col->fetch_assoc();
        if (isset($table_id_meta['Null']) && strtoupper($table_id_meta['Null']) === 'NO') {
            $fk_name = null;
            $fk_sql = "SELECT CONSTRAINT_NAME
                       FROM information_schema.KEY_COLUMN_USAGE
                       WHERE TABLE_SCHEMA = DATABASE()
                         AND TABLE_NAME = 'billings'
                         AND COLUMN_NAME = 'table_id'
                         AND REFERENCED_TABLE_NAME = 'restaurant_tables'
                       LIMIT 1";
            $fk_res = $conn->query($fk_sql);
            if ($fk_res && $fk_res->num_rows > 0) {
                $fk_row = $fk_res->fetch_assoc();
                $fk_name = $fk_row['CONSTRAINT_NAME'] ?? null;
            }

            if (!empty($fk_name)) {
                @$conn->query("ALTER TABLE billings DROP FOREIGN KEY `" . $conn->real_escape_string($fk_name) . "`");
            }

            @$conn->query("ALTER TABLE billings MODIFY COLUMN table_id INT NULL");

            // Keep referential integrity for direct tables while allowing NULL for online source bills.
            if (!empty($fk_name)) {
                @$conn->query("ALTER TABLE billings ADD CONSTRAINT `" . $conn->real_escape_string($fk_name) . "` FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL");
            }
        }
    }
}

// Create new bill for table
function createBill() {
    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']);
    $order_id = isset($data['order_id']) ? intval($data['order_id']) : null;
    
    // Generate billing number
    $billing_number = 'BILL-' . date('YmdHis') . '-' . $table_id;
    
    $sql = "INSERT INTO billings 
            (billing_number, table_id, manager_id, order_id, status) 
            VALUES ('" . $conn->real_escape_string($billing_number) . "', 
                    " . $table_id . ", 
                    " . $manager_id . ", 
                    " . ($order_id ? $order_id : 'NULL') . ", 
                    'open')";
    
    if ($conn->query($sql)) {
        $billing_id = $conn->insert_id;
        
        sendResponse(true, 'Bill created successfully', [
            'billing_id' => $billing_id,
            'billing_number' => $billing_number
        ]);
    } else {
        sendResponse(false, 'Error creating bill: ' . $conn->error, null);
    }
}

// Add item to bill
function addItemToBill() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    $required = ['billing_id', 'item_name', 'quantity', 'unit_price'];
    foreach ($required as $field) {
        if (!isset($data[$field])) {
            sendResponse(false, 'Missing field: ' . $field, null);
        }
    }
    
    $billing_id = intval($data['billing_id']);
    $item_name = $conn->real_escape_string($data['item_name']);
    $quantity = intval($data['quantity']);
    $unit_price = floatval($data['unit_price']);
    $total_price = $quantity * $unit_price;
    
    // Check if billing exists
    $check_sql = "SELECT id FROM billings WHERE id = " . $billing_id;
    $check_result = $conn->query($check_sql);
    if ($check_result->num_rows === 0) {
        sendResponse(false, 'Bill not found', null);
    }
    
    $sql = "INSERT INTO billing_items 
            (billing_id, item_name, quantity, unit_price, total_price) 
            VALUES (" . $billing_id . ", '" . $item_name . "', 
                    " . $quantity . ", " . $unit_price . ", " . $total_price . ")";
    
    if ($conn->query($sql)) {
        $item_id = $conn->insert_id;
        
        // Update bill total
        updateBillTotal($billing_id);
        
        sendResponse(true, 'Item added successfully', [
            'item_id' => $item_id,
            'total_price' => $total_price
        ]);
    } else {
        sendResponse(false, 'Error adding item: ' . $conn->error, null);
    }
}

// Remove item from bill
function removeItemFromBill() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['item_id'])) {
        sendResponse(false, 'Item ID is required', null);
    }
    
    $item_id = intval($data['item_id']);
    
    // Get billing_id before deletion
    $get_billing_sql = "SELECT billing_id FROM billing_items WHERE id = " . $item_id;
    $get_result = $conn->query($get_billing_sql);
    
    if ($get_result->num_rows === 0) {
        sendResponse(false, 'Item not found', null);
    }
    
    $row = $get_result->fetch_assoc();
    $billing_id = $row['billing_id'];
    
    // Delete item
    $sql = "DELETE FROM billing_items WHERE id = " . $item_id;
    
    if ($conn->query($sql)) {
        // Update bill total
        updateBillTotal($billing_id);
        
        sendResponse(true, 'Item removed successfully', null);
    } else {
        sendResponse(false, 'Error removing item', null);
    }
}

// Update bill item quantity/price
function updateBillItem() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['item_id'])) {
        sendResponse(false, 'Item ID is required', null);
    }
    
    $item_id = intval($data['item_id']);
    $quantity = isset($data['quantity']) ? intval($data['quantity']) : null;
    $unit_price = isset($data['unit_price']) ? floatval($data['unit_price']) : null;
    
    $get_sql = "SELECT billing_id, unit_price, quantity FROM billing_items WHERE id = " . $item_id;
    $get_result = $conn->query($get_sql);
    
    if ($get_result->num_rows === 0) {
        sendResponse(false, 'Item not found', null);
    }
    
    $item = $get_result->fetch_assoc();
    $billing_id = $item['billing_id'];
    
    $new_quantity = $quantity ?? $item['quantity'];
    $new_unit_price = $unit_price ?? $item['unit_price'];
    $new_total = $new_quantity * $new_unit_price;
    
    $sql = "UPDATE billing_items SET quantity = " . $new_quantity . 
           ", unit_price = " . $new_unit_price . 
           ", total_price = " . $new_total . 
           " WHERE id = " . $item_id;
    
    if ($conn->query($sql)) {
        updateBillTotal($billing_id);
        
        sendResponse(true, 'Item updated successfully', null);
    } else {
        sendResponse(false, 'Error updating item', null);
    }
}

// Apply discount to bill
function applyDiscount() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['billing_id']) || !isset($data['discount'])) {
        sendResponse(false, 'Billing ID and discount are required', null);
    }
    
    $billing_id = intval($data['billing_id']);
    $discount = floatval($data['discount']);
    
    $sql = "UPDATE billings SET discount = " . $discount . " WHERE id = " . $billing_id;
    
    if ($conn->query($sql)) {
        updateBillTotal($billing_id);
        
        sendResponse(true, 'Discount applied', null);
    } else {
        sendResponse(false, 'Error applying discount', null);
    }
}

// Get bill details
function getBill() {
    global $conn;
    
    if (!isset($_GET['billing_id'])) {
        sendResponse(false, 'Billing ID is required', null);
    }
    
    $billing_id = intval($_GET['billing_id']);

    $hasDineOrderCol = false;
    $hasDisplayTable = false;
    $hasSourceCols = false;
    $c1 = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if ($c1 && $c1->num_rows > 0) $hasDineOrderCol = true;
    $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
    if ($c2 && $c2->num_rows > 0) $hasDisplayTable = true;
    $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
    $c4 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
    if ($c3 && $c3->num_rows > 0 && $c4 && $c4->num_rows > 0) $hasSourceCols = true;

    $tableExpr = 'rt.table_number';
    $joins = "LEFT JOIN restaurant_tables rt ON b.table_id = rt.id";
    if ($hasDineOrderCol) {
        $joins .= " LEFT JOIN dine_in_orders o ON b.dine_in_order_id = o.id";
        if ($hasSourceCols) {
            $joins .= " LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id";
        } else {
            $joins .= " LEFT JOIN online_tables ot ON 1 = 0";
        }
        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, rt.table_number)"
            : "COALESCE(ot.table_number, rt.table_number)";
    }
    
    $sql = "SELECT b.*, " . $tableExpr . " AS table_number
            FROM billings b
            " . $joins . "
            WHERE b.id = " . $billing_id;
    
    $result = $conn->query($sql);
    
    if ($result->num_rows === 0) {
        sendResponse(false, 'Bill not found', null);
    }
    
    $bill = $result->fetch_assoc();
    
    // Get items
    $items_sql = "SELECT * FROM billing_items WHERE billing_id = " . $billing_id . 
                 " ORDER BY added_at ASC";
    $items_result = $conn->query($items_sql);
    $bill['items'] = [];
    while ($row = $items_result->fetch_assoc()) {
        $bill['items'][] = $row;
    }
    
    sendResponse(true, 'Bill retrieved', $bill);
}

// Get all bills for a table
function getTableBills() {
    global $conn;
    
    if (!isset($_GET['table_id'])) {
        sendResponse(false, 'Table ID is required', null);
    }
    
    $table_id = intval($_GET['table_id']);
    
    $sql = "SELECT id, billing_number, table_id, total, status, payment_status, 
            created_at FROM billings 
            WHERE table_id = " . $table_id . " 
            ORDER BY created_at DESC";
    
    $result = $conn->query($sql);
    
    $bills = [];
    while ($row = $result->fetch_assoc()) {
        $bills[] = $row;
    }
    
    sendResponse(true, 'Bills retrieved', $bills);
}

// Complete bill (mark as billed but unpaid)
function completeBill() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['billing_id'])) {
        sendResponse(false, 'Billing ID is required', null);
    }
    
    $billing_id = intval($data['billing_id']);
    
    // Recalculate totals
    updateBillTotal($billing_id);
    
    $sql = "UPDATE billings SET status = 'billed' WHERE id = " . $billing_id;
    
    if ($conn->query($sql)) {
        sendResponse(true, 'Bill completed', null);
    } else {
        sendResponse(false, 'Error completing bill', null);
    }
}

// Process payment
function processPayment() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    $required = ['billing_id', 'payment_method', 'amount_paid'];
    foreach ($required as $field) {
        if (!isset($data[$field])) {
            sendResponse(false, 'Missing field: ' . $field, null);
        }
    }
    
    $billing_id = intval($data['billing_id']);
    $payment_method = $conn->real_escape_string($data['payment_method']);
    $amount_paid = floatval($data['amount_paid']);
    
    $valid_methods = ['cash', 'card', 'upi', 'online', 'cheque'];
    if (!in_array($payment_method, $valid_methods)) {
        sendResponse(false, 'Invalid payment method', null);
    }
    
    // Keep schema backward-compatible while enabling assignment-level settlement.
    ensureBillingsDineInColumns();

    // Get bill details
    $bill_sql = "SELECT id, table_id, total, status, payment_status, dine_in_order_id, assignment_id, table_source, source_table_id, source_assignment_id
                 FROM billings
                 WHERE id = " . $billing_id;
    $bill_result = $conn->query($bill_sql);
    if (!$bill_result || $bill_result->num_rows === 0) {
        sendResponse(false, 'Bill not found', null);
    }
    $bill = $bill_result->fetch_assoc();
    
    if ($amount_paid < $bill['total']) {
        sendResponse(false, 'Insufficient payment amount', null);
    }
    
    $table_id = isset($bill['table_id']) ? intval($bill['table_id']) : 0;
    $table_source = isset($bill['table_source']) ? strtolower(trim($bill['table_source'])) : 'restaurant';
    $source_table_id = isset($bill['source_table_id']) ? intval($bill['source_table_id']) : 0;
    $bill_source_assignment_id = isset($bill['source_assignment_id']) ? intval($bill['source_assignment_id']) : 0;
    $bill_dine_in_order_id = isset($bill['dine_in_order_id']) ? intval($bill['dine_in_order_id']) : 0;
    $bill_assignment_id = isset($bill['assignment_id']) ? intval($bill['assignment_id']) : 0;
    $conn->begin_transaction();
    
    try {
        // Mark bill paid/completed
        $sql = "UPDATE billings SET 
                payment_method = '" . $payment_method . "',
                payment_status = 'completed',
                status = 'completed',
                completed_at = NOW()
                WHERE id = " . $billing_id;
        
        if (!$conn->query($sql)) {
            throw new Exception('Error updating bill: ' . $conn->error);
        }
        
        // Resolve the exact dine-in order / assignment this bill belongs to.
        $resolved_order_id = $bill_dine_in_order_id;
        $resolved_assignment_id = $bill_assignment_id;
        $resolved_online_assignment_id = $bill_source_assignment_id;
        $resolved_source_table_id = $source_table_id;
        $resolved_table_source = $table_source;

        if ($resolved_order_id <= 0 || ($resolved_table_source !== 'online' && $resolved_assignment_id <= 0)) {
            $fallback_order_sql = "SELECT id, assignment_id, table_source, source_table_id, source_assignment_id
                                   FROM dine_in_orders
                                   WHERE " . ($resolved_order_id > 0 ? "id = " . $resolved_order_id : "id = id") . "
                                   " . ($table_id > 0 ? " AND table_id = " . $table_id : "") . "
                                   AND status IN ('ready', 'completed')
                                   ORDER BY created_at DESC
                                   LIMIT 1";
            $fallback_order_result = $conn->query($fallback_order_sql);
            if ($fallback_order_result && $fallback_order_result->num_rows > 0) {
                $fallback_order = $fallback_order_result->fetch_assoc();
                if ($resolved_order_id <= 0) {
                    $resolved_order_id = intval($fallback_order['id']);
                }
                $resolved_table_source = isset($fallback_order['table_source']) ? strtolower(trim($fallback_order['table_source'])) : $resolved_table_source;
                if ($resolved_source_table_id <= 0) {
                    $resolved_source_table_id = intval($fallback_order['source_table_id'] ?? 0);
                }
                if ($resolved_table_source === 'online') {
                    $resolved_online_assignment_id = intval($fallback_order['source_assignment_id'] ?? 0);
                } else if ($resolved_assignment_id <= 0) {
                    $resolved_assignment_id = intval($fallback_order['assignment_id'] ?? 0);
                }
            }
        } else if ($resolved_table_source === 'online' && $resolved_order_id > 0) {
            $online_order_meta = $conn->query("SELECT source_assignment_id, source_table_id FROM dine_in_orders WHERE id = " . $resolved_order_id . " LIMIT 1");
            if ($online_order_meta && $online_order_meta->num_rows > 0) {
                $meta = $online_order_meta->fetch_assoc();
                $resolved_online_assignment_id = intval($meta['source_assignment_id'] ?? 0);
                if ($resolved_source_table_id <= 0) {
                    $resolved_source_table_id = intval($meta['source_table_id'] ?? 0);
                }
            }
        }

        // Mark only this paid order as paid/completed.
        if ($resolved_order_id > 0) {
            $update_order_sql = "UPDATE dine_in_orders 
                                 SET status = 'completed', payment_status = 'paid', updated_at = NOW() 
                                 WHERE id = " . $resolved_order_id;
            if (!$conn->query($update_order_sql)) {
                throw new Exception('Error updating dine-in order payment: ' . $conn->error);
            }
        }

        // Complete only the paid customer group's assignment.
        $server_id = 0;
        if ($resolved_table_source !== 'online' && $resolved_assignment_id > 0) {
            // Get the server_id from this assignment before releasing it
            $get_server_sql = "SELECT server_id FROM table_assignments WHERE id = " . $resolved_assignment_id;
            $get_server_result = $conn->query($get_server_sql);
            if ($get_server_result && $get_server_result->num_rows > 0) {
                $server_row = $get_server_result->fetch_assoc();
                $server_id = intval($server_row['server_id'] ?? 0);
            }
            
            $ended_col_check = $conn->query("SHOW COLUMNS FROM table_assignments LIKE 'ended_at'");
            if ($ended_col_check && $ended_col_check->num_rows > 0) {
                $release_assignment_sql = "UPDATE table_assignments 
                                           SET status = 'completed', ended_at = NOW()
                                           WHERE id = " . $resolved_assignment_id . "
                                           AND status = 'assigned'";
            } else {
                $release_assignment_sql = "UPDATE table_assignments 
                                           SET status = 'completed'
                                           WHERE id = " . $resolved_assignment_id . "
                                           AND status = 'assigned'";
            }
            if (!$conn->query($release_assignment_sql)) {
                throw new Exception('Error releasing assignment seats: ' . $conn->error);
            }
        }

        $table_released = false;
        if ($resolved_table_source === 'online') {
            if ($resolved_online_assignment_id > 0) {
                $online_release_sql = "UPDATE online_table_bookings
                                       SET allocation_status = 'released', released_at = NOW()
                                       WHERE id = " . $resolved_online_assignment_id . "
                                       AND allocation_status = 'allocated'";
                if (!$conn->query($online_release_sql)) {
                    throw new Exception('Error releasing online booking allocation: ' . $conn->error);
                }
                $table_released = true;
            }

            // Set server to available if no direct/online active assignments remain.
            if ($resolved_order_id > 0) {
                $srv = $conn->query("SELECT server_id FROM dine_in_orders WHERE id = " . $resolved_order_id . " LIMIT 1");
                if ($srv && $srv->num_rows > 0) {
                    $server_id = intval($srv->fetch_assoc()['server_id'] ?? 0);
                }
            }
            if ($server_id > 0) {
                $directActive = $conn->query("SELECT COUNT(*) AS c FROM table_assignments WHERE server_id = " . $server_id . " AND status = 'assigned'");
                $onlineActive = $conn->query("SELECT COUNT(*) AS c FROM online_table_bookings WHERE assigned_server_id = " . $server_id . " AND allocation_status = 'allocated'");
                $directCount = $directActive ? intval($directActive->fetch_assoc()['c'] ?? 0) : 0;
                $onlineCount = $onlineActive ? intval($onlineActive->fetch_assoc()['c'] ?? 0) : 0;
                if (($directCount + $onlineCount) === 0) {
                    $update_server_sql = "UPDATE servers SET status = 'available' WHERE id = " . $server_id;
                    if (!$conn->query($update_server_sql)) {
                        throw new Exception('Error updating server status: ' . $conn->error);
                    }
                }
            }
        } else {
            // Release the whole direct table only when no active assignment remains.
            $active_assignments_sql = "SELECT COUNT(*) AS active_count
                                       FROM table_assignments
                                       WHERE table_id = " . $table_id . "
                                         AND status = 'assigned'";
            $active_assignments_result = $conn->query($active_assignments_sql);
            $active_count = 0;
            if ($active_assignments_result && $active_assignments_result->num_rows > 0) {
                $active_row = $active_assignments_result->fetch_assoc();
                $active_count = intval($active_row['active_count'] ?? 0);
            }

            if ($active_count === 0) {
                $release_table_sql = "UPDATE restaurant_tables 
                                      SET status = 'available'
                                      WHERE id = " . $table_id;
                if (!$conn->query($release_table_sql)) {
                    throw new Exception('Error releasing table: ' . $conn->error);
                }
                $table_released = true;
                
                if ($server_id > 0) {
                    $server_active_sql = "SELECT COUNT(*) as active_count
                                           FROM table_assignments
                                           WHERE server_id = " . $server_id . "
                                             AND status = 'assigned'";
                    $server_active_result = $conn->query($server_active_sql);
                    if ($server_active_result && $server_active_result->num_rows > 0) {
                        $server_active_row = $server_active_result->fetch_assoc();
                        $server_active_count = intval($server_active_row['active_count'] ?? 0);
                        if ($server_active_count === 0) {
                            $update_server_sql = "UPDATE servers 
                                                  SET status = 'available'
                                                  WHERE id = " . $server_id;
                            if (!$conn->query($update_server_sql)) {
                                throw new Exception('Error updating server status: ' . $conn->error);
                            }
                        }
                    }
                }
            } else {
                $keep_table_sql = "UPDATE restaurant_tables
                                   SET status = 'occupied'
                                   WHERE id = " . $table_id . "
                                     AND status != 'maintenance'";
                if (!$conn->query($keep_table_sql)) {
                    throw new Exception('Error updating table occupancy status: ' . $conn->error);
                }
            }
        }
        
        // Mark associated booking as checked_out when table is released
        // This allows the table to show as available for future bookings
        if ($resolved_table_source === 'online' || $table_released) {
            if ($resolved_table_source === 'online' && $resolved_online_assignment_id <= 0 && $resolved_source_table_id > 0) {
                // Recover online allocation id when older bill/order rows missed source_assignment_id.
                $today = date('Y-m-d');
                $resolve_online_assignment_sql = "SELECT otb.id
                                                  FROM online_table_bookings otb
                                                  INNER JOIN bookings b ON b.id = otb.booking_id
                                                  WHERE otb.online_table_id = " . $resolved_source_table_id . "
                                                    AND b.booking_date = '" . $today . "'
                                                    AND LOWER(TRIM(b.status)) IN ('checked_in', 'checked_out')
                                                  ORDER BY
                                                      CASE WHEN otb.released_at IS NULL THEN 1 ELSE 0 END ASC,
                                                      otb.released_at DESC,
                                                      b.slot_time ASC
                                                  LIMIT 1";
                $resolve_online_assignment_result = $conn->query($resolve_online_assignment_sql);
                if ($resolve_online_assignment_result && $resolve_online_assignment_result->num_rows > 0) {
                    $resolved_online_assignment_id = intval($resolve_online_assignment_result->fetch_assoc()['id'] ?? 0);
                }
            }

            if ($resolved_table_source === 'online' && $resolved_online_assignment_id > 0) {
                $checkout_online_sql = "UPDATE bookings
                                        SET status = 'checked_out'
                                        WHERE id IN (
                                            SELECT otb.booking_id
                                            FROM online_table_bookings otb
                                            WHERE otb.id = " . $resolved_online_assignment_id . "
                                        )
                                        AND LOWER(TRIM(status)) = 'checked_in'";
                if (!$conn->query($checkout_online_sql)) {
                    error_log('Warning: Could not update online booking to checked_out: ' . $conn->error);
                }
            } else if ($resolved_table_source === 'online') {
                // Fallback for legacy rows where source_assignment_id was not stored on bill/order.
                $today = date('Y-m-d');
                if ($resolved_source_table_id > 0) {
                    $checkout_online_fallback_sql = "UPDATE bookings
                                                     SET status = 'checked_out'
                                                     WHERE id IN (
                                                         SELECT booking_id FROM (
                                                             SELECT otb.booking_id
                                                             FROM online_table_bookings otb
                                                             INNER JOIN bookings b ON b.id = otb.booking_id
                                                             WHERE otb.online_table_id = " . $resolved_source_table_id . "
                                                               AND b.booking_date = '" . $today . "'
                                                               AND LOWER(TRIM(b.status)) = 'checked_in'
                                                             ORDER BY
                                                                 CASE WHEN otb.released_at IS NULL THEN 1 ELSE 0 END ASC,
                                                                 otb.released_at DESC,
                                                                 b.slot_time ASC
                                                             LIMIT 1
                                                         ) picked_booking
                                                     )";
                    if (!$conn->query($checkout_online_fallback_sql)) {
                        error_log('Warning: Could not update online booking to checked_out via fallback: ' . $conn->error);
                    }
                }
            } else if ($resolved_table_source !== 'online') {
                $today = date('Y-m-d');
                $checkout_booking_sql = "UPDATE bookings 
                                         SET status = 'checked_out'
                                         WHERE id IN (
                                             SELECT b.id FROM bookings b
                                             INNER JOIN table_bookings tb ON b.id = tb.booking_id
                                             WHERE tb.table_id = " . $table_id . "
                                             AND b.booking_date = '" . $today . "'
                                             AND LOWER(TRIM(b.status)) = 'checked_in'
                                         )";
                if (!$conn->query($checkout_booking_sql)) {
                    error_log('Warning: Could not update booking to checked_out: ' . $conn->error);
                    // Don't throw exception as this is not critical to payment processing
                }
            }
        }
        
        $conn->commit();
        
        $change = $amount_paid - $bill['total'];
        
        sendResponse(true, 'Payment processed successfully', [
            'amount_paid' => $amount_paid,
            'change' => $change,
            'table_released' => $table_released
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, $e->getMessage(), null);
    }
}

// Get all open bills
function getOpenBills() {
    global $conn;

    $hasDineOrderCol = false;
    $hasDisplayTable = false;
    $hasSourceCols = false;
    $c1 = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if ($c1 && $c1->num_rows > 0) $hasDineOrderCol = true;
    $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
    if ($c2 && $c2->num_rows > 0) $hasDisplayTable = true;
    $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
    $c4 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
    if ($c3 && $c3->num_rows > 0 && $c4 && $c4->num_rows > 0) $hasSourceCols = true;

    $tableExpr = 'rt.table_number';
    $joins = "LEFT JOIN restaurant_tables rt ON b.table_id = rt.id";
    if ($hasDineOrderCol) {
        $joins .= " LEFT JOIN dine_in_orders o ON b.dine_in_order_id = o.id";
        if ($hasSourceCols) {
            $joins .= " LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id";
        } else {
            $joins .= " LEFT JOIN online_tables ot ON 1 = 0";
        }
        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, rt.table_number)"
            : "COALESCE(ot.table_number, rt.table_number)";
    }
    
    $sql = "SELECT b.id, b.billing_number, b.table_id, " . $tableExpr . " AS table_number,
            b.subtotal, b.tax, b.discount, b.total, b.created_at
            FROM billings b
            " . $joins . "
            WHERE b.status IN ('open', 'billed')
            ORDER BY b.created_at DESC";
    
    $result = $conn->query($sql);
    
    $bills = [];
    while ($row = $result->fetch_assoc()) {
        $bills[] = $row;
    }
    
    sendResponse(true, 'Open bills retrieved', $bills);
}

// Get billing history
function getBillHistory() {
    global $conn;
    
    $days = isset($_GET['days']) ? intval($_GET['days']) : 7;
    $table_id = isset($_GET['table_id']) ? intval($_GET['table_id']) : null;
    
    $hasDineOrderCol = false;
    $hasDisplayTable = false;
    $hasSourceCols = false;
    $c1 = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if ($c1 && $c1->num_rows > 0) $hasDineOrderCol = true;
    $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
    if ($c2 && $c2->num_rows > 0) $hasDisplayTable = true;
    $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
    $c4 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
    if ($c3 && $c3->num_rows > 0 && $c4 && $c4->num_rows > 0) $hasSourceCols = true;

    $tableExpr = 'rt.table_number';
    $joins = "LEFT JOIN restaurant_tables rt ON b.table_id = rt.id";
    if ($hasDineOrderCol) {
        $joins .= " LEFT JOIN dine_in_orders o ON b.dine_in_order_id = o.id";
        if ($hasSourceCols) {
            $joins .= " LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id";
        } else {
            $joins .= " LEFT JOIN online_tables ot ON 1 = 0";
        }
        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, rt.table_number)"
            : "COALESCE(ot.table_number, rt.table_number)";
    }

    $sql = "SELECT b.id, b.billing_number, b.table_id, " . $tableExpr . " AS table_number,
            b.total, b.payment_method, b.payment_status, b.created_at, b.completed_at
            FROM billings b
            " . $joins . "
            WHERE b.status = 'completed' 
            AND b.created_at >= DATE_SUB(NOW(), INTERVAL " . $days . " DAY)";
    
    if ($table_id) {
        $sql .= " AND b.table_id = " . $table_id;
    }
    
    $sql .= " ORDER BY b.created_at DESC";
    
    $result = $conn->query($sql);
    
    $bills = [];
    while ($row = $result->fetch_assoc()) {
        $bills[] = $row;
    }
    
    sendResponse(true, 'Bill history retrieved', $bills);
}

// Reprint bill
function reprintBill() {
    global $conn;
    
    if (!isset($_GET['billing_id'])) {
        sendResponse(false, 'Billing ID is required', null);
    }
    
    // Just retrieve bill - actual printing done on frontend
    $billing_id = intval($_GET['billing_id']);
    
    $hasDineOrderCol = false;
    $hasDisplayTable = false;
    $hasSourceCols = false;
    $c1 = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if ($c1 && $c1->num_rows > 0) $hasDineOrderCol = true;
    $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
    if ($c2 && $c2->num_rows > 0) $hasDisplayTable = true;
    $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
    $c4 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
    if ($c3 && $c3->num_rows > 0 && $c4 && $c4->num_rows > 0) $hasSourceCols = true;

    $tableExpr = 'rt.table_number';
    $joins = "LEFT JOIN restaurant_tables rt ON b.table_id = rt.id";
    if ($hasDineOrderCol) {
        $joins .= " LEFT JOIN dine_in_orders o ON b.dine_in_order_id = o.id";
        if ($hasSourceCols) {
            $joins .= " LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id";
        } else {
            $joins .= " LEFT JOIN online_tables ot ON 1 = 0";
        }
        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, rt.table_number)"
            : "COALESCE(ot.table_number, rt.table_number)";
    }
    
    $sql = "SELECT b.*, " . $tableExpr . " AS table_number
            FROM billings b
            " . $joins . "
            WHERE b.id = " . $billing_id;
    
    $result = $conn->query($sql);
    
    if ($result->num_rows === 0) {
        sendResponse(false, 'Bill not found', null);
    }
    
    $bill = $result->fetch_assoc();
    
    // Get items
    $items_sql = "SELECT * FROM billing_items WHERE billing_id = " . $billing_id;
    $items_result = $conn->query($items_sql);
    $bill['items'] = [];
    while ($row = $items_result->fetch_assoc()) {
        $bill['items'][] = $row;
    }
    
    sendResponse(true, 'Bill retrieved for printing', $bill);
}

// Split bill among multiple customers
function splitBill() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['billing_id']) || !isset($data['items']) || !isset($data['num_splits'])) {
        sendResponse(false, 'Billing ID, items, and number of splits are required', null);
    }
    
    $original_billing_id = intval($data['billing_id']);
    $num_splits = intval($data['num_splits']);
    $items = $data['items']; // Array of item_ids to distribute
    
    // Get original bill
    $bill_sql = "SELECT * FROM billings WHERE id = " . $original_billing_id;
    $bill_result = $conn->query($bill_sql);
    $bill = $bill_result->fetch_assoc();
    
    $conn->begin_transaction();
    
    try {
        $new_bill_ids = [];
        $per_split_total = $bill['total'] / $num_splits;
        
        for ($i = 0; $i < $num_splits; $i++) {
            $billing_number = $bill['billing_number'] . '-SPLIT' . ($i + 1);
            
            $insert_sql = "INSERT INTO billings 
                          (billing_number, table_id, manager_id, subtotal, tax, discount, total, status)
                          VALUES ('" . $conn->real_escape_string($billing_number) . "',
                                  " . $bill['table_id'] . ",
                                  " . $manager_id . ",
                                  " . ($bill['subtotal'] / $num_splits) . ",
                                  " . ($bill['tax'] / $num_splits) . ",
                                  " . ($bill['discount'] / $num_splits) . ",
                                  " . $per_split_total . ",
                                  'open')";
            
            $conn->query($insert_sql);
            $new_bill_id = $conn->insert_id;
            $new_bill_ids[] = $new_bill_id;
            
            // Copy items prorated
            if (isset($items[$i])) {
                foreach ($items[$i] as $item_id) {
                    $item_sql = "SELECT * FROM billing_items WHERE id = " . intval($item_id);
                    $item_result = $conn->query($item_sql);
                    $item = $item_result->fetch_assoc();
                    
                    $insert_item = "INSERT INTO billing_items 
                                   (billing_id, item_name, quantity, unit_price, total_price)
                                   VALUES (" . $new_bill_id . ", 
                                          '" . $conn->real_escape_string($item['item_name']) . "',
                                          " . $item['quantity'] . ",
                                          " . $item['unit_price'] . ",
                                          " . $item['total_price'] . ")";
                    $conn->query($insert_item);
                }
            }
        }
        
        // Mark original bill as split
        $update_sql = "UPDATE billings SET status = 'split' WHERE id = " . $original_billing_id;
        $conn->query($update_sql);
        
        $conn->commit();
        
        sendResponse(true, 'Bill split successfully', [
            'new_bills' => $new_bill_ids
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error splitting bill: ' . $e->getMessage(), null);
    }
}

// Helper function to update bill totals
function updateBillTotal($billing_id) {
    global $conn;
    
    $items_sql = "SELECT SUM(total_price) as item_total FROM billing_items 
                  WHERE billing_id = " . intval($billing_id);
    $items_result = $conn->query($items_sql);
    $items = $items_result->fetch_assoc();
    $subtotal = $items['item_total'] ?? 0;
    
    $bill_sql = "SELECT discount FROM billings WHERE id = " . intval($billing_id);
    $bill_result = $conn->query($bill_sql);
    $bill = $bill_result->fetch_assoc();
    $discount = $bill['discount'] ?? 0;
    
    $taxable_amount = $subtotal - $discount;
    $tax = $taxable_amount * 0.05; // 5% tax
    $total = $subtotal - $discount + $tax;
    
    $update_sql = "UPDATE billings SET 
                   subtotal = " . $subtotal . ",
                   tax = " . $tax . ",
                   total = " . $total . "
                   WHERE id = " . intval($billing_id);
    $conn->query($update_sql);
}

// Get pending server orders (orders sent from servers ready for billing)
function getPendingServerOrders() {
    global $conn, $manager_id;
    
    try {
        $hasDisplayTable = false;
        $hasSourceCols = false;
        $c1 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
        if ($c1 && $c1->num_rows > 0) $hasDisplayTable = true;
        $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
        $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
        if ($c2 && $c2->num_rows > 0 && $c3 && $c3->num_rows > 0) $hasSourceCols = true;

        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, t.table_number)"
            : "COALESCE(ot.table_number, t.table_number)";
        $onlineJoin = $hasSourceCols
            ? "LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id"
            : "LEFT JOIN online_tables ot ON 1 = 0";

        $sql = "SELECT 
                    o.id as order_id,
                    o.order_number,
                    o.table_id,
                    " . $tableExpr . " AS table_number,
                    o.total_amount,
                    o.status,
                    o.created_at,
                    s.name as server_name,
                    COUNT(oi.id) as items_count
                FROM dine_in_orders o
                LEFT JOIN restaurant_tables t ON o.table_id = t.id
                " . $onlineJoin . "
                LEFT JOIN servers s ON o.server_id = s.id
                LEFT JOIN dine_in_order_items oi ON o.id = oi.dine_in_order_id
                WHERE o.status = 'ready'
                GROUP BY o.id
                ORDER BY o.created_at DESC";
        
        $result = $conn->query($sql);
        
        if (!$result) {
            sendResponse(false, 'Error fetching orders: ' . $conn->error, null);
            return;
        }
        
        $orders = [];
        while ($row = $result->fetch_assoc()) {
            $orders[] = $row;
        }
        
        sendResponse(true, 'Pending orders retrieved', $orders);
    } catch (Exception $e) {
        sendResponse(false, 'Error: ' . $e->getMessage(), null);
    }
}

// Get detailed information about a server order
function getServerOrderDetails() {
    global $conn;
    
    if (!isset($_GET['order_id'])) {
        sendResponse(false, 'Order ID is required', null);
        return;
    }
    
    $order_id = intval($_GET['order_id']);
    
    try {
        $hasDisplayTable = false;
        $hasSourceCols = false;
        $c1 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'display_table_number'");
        if ($c1 && $c1->num_rows > 0) $hasDisplayTable = true;
        $c2 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'table_source'");
        $c3 = $conn->query("SHOW COLUMNS FROM dine_in_orders LIKE 'source_table_id'");
        if ($c2 && $c2->num_rows > 0 && $c3 && $c3->num_rows > 0) $hasSourceCols = true;

        $tableExpr = $hasDisplayTable
            ? "COALESCE(o.display_table_number, ot.table_number, t.table_number)"
            : "COALESCE(ot.table_number, t.table_number)";
        $capacityExpr = "COALESCE(ot.current_capacity, t.current_capacity, t.base_capacity)";
        $floorExpr = "COALESCE(ot.floor_level, t.floor_level)";
        $sectionExpr = "COALESCE(ot.section, t.section)";
        $onlineJoin = $hasSourceCols
            ? "LEFT JOIN online_tables ot ON o.table_source = 'online' AND o.source_table_id = ot.id"
            : "LEFT JOIN online_tables ot ON 1 = 0";

        // Get order details
        $order_sql = "SELECT 
                        o.id,
                        o.order_number,
                        o.table_id,
                        o.total_amount,
                        o.status,
                        o.created_at,
                        o.special_requests,
                        " . $tableExpr . " AS table_number,
                        " . $capacityExpr . " AS capacity,
                        " . $floorExpr . " AS floor,
                        " . $sectionExpr . " AS section,
                        s.name as server_name
                    FROM dine_in_orders o
                    LEFT JOIN restaurant_tables t ON o.table_id = t.id
                    " . $onlineJoin . "
                    LEFT JOIN servers s ON o.server_id = s.id
                    WHERE o.id = $order_id";
        
        $order_result = $conn->query($order_sql);
        if (!$order_result || $order_result->num_rows === 0) {
            sendResponse(false, 'Order not found', null);
            return;
        }
        
        $order = $order_result->fetch_assoc();
        
        // Get order items
        $items_sql = "SELECT 
                        id,
                        item_id,
                        item_name,
                        quantity,
                        unit_price,
                        total_price,
                        status,
                        special_instructions
                    FROM dine_in_order_items
                    WHERE dine_in_order_id = $order_id
                    ORDER BY created_at ASC";
        
        $items_result = $conn->query($items_sql);
        $items = [];
        while ($item = $items_result->fetch_assoc()) {
            $items[] = $item;
        }
        
        sendResponse(true, 'Order details retrieved', [
            'order' => $order,
            'items' => $items
        ]);
    } catch (Exception $e) {
        sendResponse(false, 'Error: ' . $e->getMessage(), null);
    }
}

// Convert server order to bill in the billing system
function convertServerOrderToBill() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['order_id'])) {
        sendResponse(false, 'Order ID is required', null);
        return;
    }
    
    $order_id = intval($data['order_id']);
    
    try {
        // Keep schema backward-compatible while enabling assignment-level settlement.
        ensureBillingsDineInColumns();

        // Get order details
        $order_sql = "SELECT id, table_id, total_amount, assignment_id, table_source, source_table_id, source_assignment_id, display_table_number
                      FROM dine_in_orders
                      WHERE id = $order_id AND status = 'ready'";
        $order_result = $conn->query($order_sql);
        
        if (!$order_result || $order_result->num_rows === 0) {
            sendResponse(false, 'Order not found or not ready for billing', null);
            return;
        }
        
        $order = $order_result->fetch_assoc();
        $table_id = isset($order['table_id']) ? intval($order['table_id']) : 0;
        $table_source = isset($order['table_source']) ? strtolower(trim($order['table_source'])) : 'restaurant';
        $source_table_id = isset($order['source_table_id']) ? intval($order['source_table_id']) : 0;
        $source_assignment_id = isset($order['source_assignment_id']) ? intval($order['source_assignment_id']) : 0;
        $display_table_number = isset($order['display_table_number']) ? $order['display_table_number'] : null;

        // Prevent duplicate billing records for the same dine-in order.
        if (hasTableColumn('billings', 'dine_in_order_id')) {
            $existing_sql = "SELECT id, billing_number FROM billings WHERE dine_in_order_id = " . $order_id . " ORDER BY id DESC LIMIT 1";
            $existing_result = $conn->query($existing_sql);
            if ($existing_result && $existing_result->num_rows > 0) {
                $existing = $existing_result->fetch_assoc();
                sendResponse(true, 'Order already converted to bill', [
                    'billing_id' => intval($existing['id']),
                    'billing_number' => $existing['billing_number']
                ]);
                return;
            }
        }
        
        // Start transaction
        $conn->begin_transaction();
        
        // Create billing entry.
        // Note: billings.order_id in this project references the `orders` table
        // (not `dine_in_orders`), so we should not store dine-in order id there.
        $billing_number = 'BILL-' . date('YmdHis') . '-' . $order_id;
        $order_assignment_id = intval($order['assignment_id'] ?? 0);

        // Compatibility insert: only include optional source columns when present.
        $columns = [
            'billing_number',
            'table_id',
            'manager_id',
            'status',
            'subtotal',
            'total'
        ];
        $values = [
            "'" . $conn->real_escape_string($billing_number) . "'",
            ($table_id > 0 ? $table_id : "NULL"),
            $manager_id,
            "'open'",
            floatval($order['total_amount']),
            floatval($order['total_amount'])
        ];

        if (hasTableColumn('billings', 'order_id')) {
            $columns[] = 'order_id';
            $values[] = "NULL";
        }
        if (hasTableColumn('billings', 'dine_in_order_id')) {
            $columns[] = 'dine_in_order_id';
            $values[] = $order_id;
        }
        if (hasTableColumn('billings', 'assignment_id')) {
            $columns[] = 'assignment_id';
            $values[] = ($order_assignment_id > 0 ? $order_assignment_id : "NULL");
        }
        if (hasTableColumn('billings', 'table_source')) {
            $columns[] = 'table_source';
            $values[] = "'" . $conn->real_escape_string($table_source) . "'";
        }
        if (hasTableColumn('billings', 'source_table_id')) {
            $columns[] = 'source_table_id';
            $values[] = ($source_table_id > 0 ? $source_table_id : "NULL");
        }
        if (hasTableColumn('billings', 'source_assignment_id')) {
            $columns[] = 'source_assignment_id';
            $values[] = ($source_assignment_id > 0 ? $source_assignment_id : "NULL");
        }
        if (hasTableColumn('billings', 'display_table_number')) {
            $columns[] = 'display_table_number';
            $values[] = ($display_table_number !== null && trim($display_table_number) !== '' ? ("'" . $conn->real_escape_string($display_table_number) . "'") : "NULL");
        }

        $bill_sql = "INSERT INTO billings (" . implode(', ', $columns) . ")
                     VALUES (" . implode(', ', $values) . ")";
        
        if (!$conn->query($bill_sql)) {
            $conn->rollback();
            sendResponse(false, 'Error creating bill: ' . $conn->error, null);
            return;
        }
        
        $billing_id = $conn->insert_id;
        
        // Get all items from the dine-in order and add them to the bill
        $items_sql = "SELECT 
                        oi.item_id,
                        oi.item_name,
                        oi.quantity,
                        CASE
                            WHEN oi.unit_price IS NULL OR oi.unit_price <= 0 THEN COALESCE(mi.price, 0)
                            ELSE oi.unit_price
                        END AS resolved_unit_price,
                        CASE
                            WHEN oi.total_price IS NULL OR oi.total_price <= 0 THEN
                                (CASE
                                    WHEN oi.unit_price IS NULL OR oi.unit_price <= 0 THEN COALESCE(mi.price, 0)
                                    ELSE oi.unit_price
                                END) * oi.quantity
                            ELSE oi.total_price
                        END AS resolved_total_price
                     FROM dine_in_order_items oi
                     LEFT JOIN menu_items mi ON oi.item_id = mi.id
                     WHERE oi.dine_in_order_id = $order_id";
        
        $items_result = $conn->query($items_sql);
        
        while ($item = $items_result->fetch_assoc()) {
            $quantity = max(1, intval($item['quantity']));
            $unit_price = floatval($item['resolved_unit_price']);
            $total_price = floatval($item['resolved_total_price']);
            
            if ($unit_price < 0) {
                $unit_price = 0;
            }
            if ($total_price <= 0 && $unit_price > 0) {
                $total_price = $unit_price * $quantity;
            }
            
            // billing_items schema does not include `item_id` in this project.
            $item_bill_sql = "INSERT INTO billing_items 
                             (billing_id, item_name, quantity, unit_price, total_price)
                             VALUES (" . $billing_id . ",
                                     '" . $conn->real_escape_string($item['item_name']) . "',
                                     " . $quantity . ",
                                     " . $unit_price . ",
                                     " . $total_price . ")";
            
            if (!$conn->query($item_bill_sql)) {
                $conn->rollback();
                sendResponse(false, 'Error adding items to bill: ' . $conn->error, null);
                return;
            }
        }
        
        // Recalculate subtotal, tax, and total from actual inserted billing items.
        updateBillTotal($billing_id);
        
        // Update the dine-in order status to 'completed'
        $update_sql = "UPDATE dine_in_orders SET status = 'completed' WHERE id = $order_id";
        $conn->query($update_sql);
        
        $conn->commit();
        
        sendResponse(true, 'Order converted to bill successfully', [
            'billing_id' => $billing_id,
            'billing_number' => $billing_number
        ]);
    } catch (Exception $e) {
        $conn->rollback();
        sendResponse(false, 'Error: ' . $e->getMessage(), null);
    }
}
?>
