-- SQL for Waiter Authentication and Login Tracking
-- This file contains the necessary table for recording waiter login sessions.

CREATE TABLE IF NOT EXISTS waiter_login_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    waiter_id INT NOT NULL COMMENT 'FK to waiters table',
    device_name VARCHAR(255) NOT NULL COMMENT 'Name/Model of the device',
    ip_address VARCHAR(50) NOT NULL COMMENT 'IP address of the device',
    login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    logout_time DATETIME NULL,
    session_status ENUM('Active', 'Logged-Out', 'Expired') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (waiter_id) REFERENCES waiters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Indexes for performance and reporting
CREATE INDEX idx_waiter_id_logs ON waiter_login_logs(waiter_id);
CREATE INDEX idx_session_status ON waiter_login_logs(session_status);
CREATE INDEX idx_login_time ON waiter_login_logs(login_time);