🎯 Entities and Attributes:

1. User
   • user_id (PK)
   • name
   • email
   • password
   • role (Super Admin, Resto Admin, User)
   • created_at
   • updated_at
2. Restaurant
   • restaurant_id (PK)
   • name
   • location
   • max_capacity
   • current_capacity
   • status (Open/Closed)
   • gps_coordinates
   • created_at
   • updated_at
3. Resto Admin Assignment
   • assignment_id (PK)
   • restaurant_id (FK)
   • admin_id (FK) - references User
   • assigned_at
4. Route Configuration
   • config_id (PK)
   • api_provider (Google Maps, OpenRouteService)
   • api_key
   • route_modes (Driving, Walking, Biking)
   • created_at
   • updated_at
5. Notification
   • notification_id (PK)
   • user_id (FK)
   • restaurant_id (FK, nullable)
   • message
   • notification_type (Crowd Change, Route Suggestion)
   • status (Sent, Pending, Failed)
   • created_at
6. Audit Log
   • log_id (PK)
   • admin_id (FK) - references User
   • action
   • description
   • created_at
7. Crowd Status
   • status_id (PK)
   • restaurant_id (FK)
   • crowd_percentage
   • wait_time
   • created_at
8. Subscription
   • subscription_id (PK)
   • user_id (FK)
   • restaurant_id (FK, nullable)
   • subscription_type (Capacity Updates, Alternative Routes)
   • status (Active, Inactive)
   • created_at
9. Route Suggestion
   • suggestion_id (PK)
   • restaurant_id (FK)
   • route_mode
   • distance
   • estimated_time
   • crowd_level (Low, Moderate, High)
   • created_at

database schema

-- Create Database
CREATE DATABASE IF NOT EXISTS resto_system;
USE cabify;

-- Create User Table
CREATE TABLE User (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password CHAR(60),
    role ENUM('Super Admin', 'Resto Admin', 'User') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create Restaurant Table
CREATE TABLE Restaurant (
    restaurant_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    location VARCHAR(255),
    max_capacity INT,
    current_capacity INT,
    status ENUM('Open', 'Closed') DEFAULT 'Open',
    gps_coordinates VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create Resto Admin Assignment Table
CREATE TABLE Resto_Admin_Assignment (
    assignment_id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT,
    admin_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES User(user_id) ON DELETE CASCADE
);

-- Create Route Configuration Table
CREATE TABLE Route_Configuration (
    config_id INT PRIMARY KEY AUTO_INCREMENT,
    api_provider ENUM('Google Maps', 'OpenRouteService'),
    api_key VARCHAR(255),
    route_modes ENUM('Driving', 'Walking', 'Biking'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create Notification Table
CREATE TABLE Notification (
    notification_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    restaurant_id INT NULL,
    message TEXT,
    notification_type ENUM('Crowd Change', 'Route Suggestion'),
    status ENUM('Sent', 'Pending', 'Failed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id) ON DELETE SET NULL
);

-- Create Audit Log Table
CREATE TABLE Audit_Log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT,
    action VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES User(user_id) ON DELETE CASCADE
);

-- Create Crowd Status Table
CREATE TABLE Crowd_Status (
    status_id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT,
    crowd_percentage DECIMAL(5, 2),
    wait_time INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id) ON DELETE CASCADE
);

-- Create Subscription Table
CREATE TABLE Subscription (
    subscription_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    restaurant_id INT NULL,
    subscription_type ENUM('Capacity Updates', 'Alternative Routes'),
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id) ON DELETE SET NULL
);

-- Create Route Suggestion Table
CREATE TABLE Route_Suggestion (
    suggestion_id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT,
    route_mode ENUM('Driving', 'Walking', 'Biking'),
    distance DECIMAL(8, 2),
    estimated_time INT,
    crowd_level ENUM('Low', 'Moderate', 'High'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id) ON DELETE CASCADE
);