TABLE_MANAGEMENT_IMPLEMENTATION.md

Table Management Implementation

Overview

This document describes the implementation of database-specific table occupancy management based on user navigation between home page and customer pages.

Requirements Implemented

1. Database Selection: Users first select database and verify password from hotels.csv
2. Table Occupancy Logic:
   • is_occupied = 0 when user is on home page (table free)
   • is_occupied = 1 when user is on customer page (table occupied)
3. Database Independence: All table operations work within the selected database
4. Hotel Manager Visibility: Table status is for hotel manager visibility only

Implementation Details

Backend Changes

1. New API Endpoint

• Endpoint: PUT /tables/number/{table_number}/free
• Purpose: Set table as free by table number
• Location: app/routers/table.py

@router.put("/number/{table_number}/free", response_model=Table)
def set_table_free_by_number(table_number: int, db: Session = Depends(get_db)):
    # Implementation details in the file

2. Database Schema Update

• Added Field: last_occupied_at to tables table
• Type: DATETIME, nullable
• Purpose: Track when table was last occupied
• Files Modified:
  • app/database.py (SQLAlchemy model)
  • app/models/table.py (Pydantic models)

3. Migration Support

• Script: migrate_table_schema.py
• Purpose: Add last_occupied_at column to existing databases
• Usage: Run before starting the application with existing databases

Frontend Changes

1. API Service Updates

• File: frontend/src/services/api.js
• New Methods:
  • customerService.setTableFreeByNumber(tableNumber)
  • adminService.setTableFreeByNumber(tableNumber)

2. Home Page Updates

• File: frontend/src/pages/Home.js
• Changes:
  • Added freeTableOnHomeReturn() function
  • Automatically frees table when user returns to home page
  • Uses selected database for table operations

3. Customer Menu Updates

• File: frontend/src/pages/customer/Menu.js
• Changes:
  • Enhanced back-to-home button to free table before navigation
  • Added beforeunload event listener for browser close/refresh
  • Uses navigator.sendBeacon for reliable cleanup

Database Independence

How It Works

1. Database Selection: Users select database on home page
2. Session Management: Database credentials stored in localStorage
3. API Calls: All table operations use the selected database
4. Isolation: Each database maintains its own table occupancy state

Storage Keys

• customerSelectedDatabase: Selected database name
• customerDatabasePassword: Database password
• tableNumber: Current table number

Table Occupancy Flow

User Journey

1. Home Page: User selects database and table number
   • Table status: is_occupied = 0 (free)
2. Navigate to Customer Page: User enters customer interface
   • Table status: is_occupied = 1 (occupied)
   • API call: PUT /tables/number/{table_number}/occupy
3. Return to Home: User clicks back button or navigates away
   • Table status: is_occupied = 0 (free)
   • API call: PUT /tables/number/{table_number}/free

Cleanup Scenarios

1. Back Button: Explicit table freeing before navigation
2. Browser Close: beforeunload event with navigator.sendBeacon
3. Page Refresh: beforeunload event with navigator.sendBeacon
4. Direct Navigation: Home page automatically frees table on load

Testing

Test Script

• File: test_table_management.py
• Purpose: Verify table management functionality
• Tests:
  • Database selection
  • Table creation
  • Table occupation
  • Table freeing
  • Status retrieval

Running Tests

python test_table_management.py

Migration

For Existing Databases

python migrate_table_schema.py

This will:

• Find all .db files in current directory
• Add last_occupied_at column if missing
• Preserve existing data

API Endpoints Summary

Method	Endpoint	Purpose
PUT	/tables/number/{table_number}/occupy	Set table as occupied
PUT	/tables/number/{table_number}/free	Set table as free
GET	/tables/status/summary	Get table status summary
GET	/tables/number/{table_number}	Get table by number

Error Handling

• Table Not Found: Returns 404 error
• Database Connection: Graceful fallback and error logging
• Network Issues: Silent failure for cleanup operations
• Invalid Table Number: Validation and error messages

Security Considerations

• Database Access: Password-protected database selection
• Session Management: Credentials stored in localStorage
• API Security: Database switching requires authentication
• Data Isolation: Complete separation between databases

Performance Optimizations

• Minimal API Calls: Only when necessary
• Async Operations: Non-blocking table updates
• Error Recovery: Graceful handling of failed operations
• Cleanup Efficiency: navigator.sendBeacon for reliable cleanup

Future Enhancements

1. Real-time Updates: WebSocket for live table status
2. Table Reservations: Advanced booking system
3. Analytics: Table utilization tracking
4. Mobile Support: Touch-optimized interface
5. Multi-language: Internationalization support