-- Table structure for `waiters`
CREATE TABLE IF NOT EXISTS waiters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  waiter_id VARCHAR(50) NOT NULL UNIQUE COMMENT 'Human-readable Waiter ID (e.g., WID-105)',
  name VARCHAR(255) NOT NULL,
  shift VARCHAR(50) DEFAULT 'Morning' COMMENT 'Shift (e.g., Morning, Night, Evening)',
  pin VARCHAR(255) NOT NULL COMMENT 'Hashed 4-digit PIN for device login',
  status ENUM('Active', 'Break', 'Off-Duty') DEFAULT 'Off-Duty' COMMENT 'Current real-time status',
  device_id VARCHAR(255) DEFAULT NULL COMMENT 'Last used device ID',
  device_type VARCHAR(50) DEFAULT NULL COMMENT 'Last used device type (e.g., Tablet, Mobile)',
  os VARCHAR(50) DEFAULT NULL COMMENT 'Last used OS (e.g., Android, iOS)',
  last_seen DATETIME DEFAULT NULL COMMENT 'Timestamp of last device interaction/login',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Make sure waiter_id and status are indexed for quick lookup
CREATE INDEX IF NOT EXISTS idx_waiter_id ON waiters(waiter_id);
CREATE INDEX IF NOT EXISTS idx_waiter_status ON waiters(status);