<?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
if (!isset($_SESSION['manager_id'])) {
    http_response_code(401);
    sendResponse(false, 'Unauthorized access', null);
}

$manager_id = $_SESSION['manager_id'];
$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 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)");
    }
}

// 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']);
    
    $sql = "SELECT b.*, rt.table_number 
            FROM billings b
            INNER JOIN restaurant_tables rt ON b.table_id = rt.id
            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
                 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 = intval($bill['table_id']);
    $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;

        if ($resolved_order_id <= 0 || $resolved_assignment_id <= 0) {
            $fallback_order_sql = "SELECT id, assignment_id
                                   FROM dine_in_orders
                                   WHERE 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']);
                }
                if ($resolved_assignment_id <= 0) {
                    $resolved_assignment_id = intval($fallback_order['assignment_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_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);
            }
        }

        // Release the whole 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);
        }

        $table_released = false;
        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 this released assignment was associated with a server,
            // check if that server has any remaining active assignments
            // and mark them available if they don't
            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 has no more active assignments, mark them available
                    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 ($table_released) {
            $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 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;
    
    $sql = "SELECT b.id, b.billing_number, b.table_id, rt.table_number, 
            b.subtotal, b.tax, b.discount, b.total, b.created_at
            FROM billings b
            INNER JOIN restaurant_tables rt ON b.table_id = rt.id
            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;
    
    $sql = "SELECT b.id, b.billing_number, b.table_id, rt.table_number, 
            b.total, b.payment_method, b.payment_status, b.created_at, b.completed_at
            FROM billings b
            INNER JOIN restaurant_tables rt ON b.table_id = rt.id
            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']);
    
    $sql = "SELECT b.*, rt.table_number 
            FROM billings b
            INNER JOIN restaurant_tables rt ON b.table_id = rt.id
            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 {
        $sql = "SELECT 
                    o.id as order_id,
                    o.order_number,
                    o.table_id,
                    t.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
                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 {
        // 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,
                        t.table_number,
                        t.capacity,
                        t.floor,
                        t.section,
                        s.name as server_name
                    FROM dine_in_orders o
                    LEFT JOIN restaurant_tables t ON o.table_id = t.id
                    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
                      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 = $order['table_id'];
        
        // 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') . '-' . $table_id;
        $order_assignment_id = intval($order['assignment_id'] ?? 0);
        
        $bill_sql = "INSERT INTO billings 
                    (billing_number, table_id, manager_id, status, subtotal, total, dine_in_order_id, assignment_id) 
                    VALUES ('" . $conn->real_escape_string($billing_number) . "', 
                            " . $table_id . ", 
                            " . $manager_id . ", 
                            'open',
                            " . $order['total_amount'] . ",
                            " . $order['total_amount'] . ",
                            " . $order_id . ",
                            " . ($order_assignment_id > 0 ? $order_assignment_id : 'NULL') . ")";
        
        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);
    }
}
?>
