Restaurant System

erDiagram %% === USERS === USERS { int id PK string username UK string hashed_password string email UK int age bool gender "nullable" enum role "admin, kitchen, client, waiter" int table_id FK "nullable" } %% === TABLES === TABLES { int id PK int number UK "physical table number" int capacity enum status "available, occupied, reserved, cleaning" enum location_zone "indoor, outdoor, terrace, bar, vip" datetime reservation_start "nullable" } %% === BASIC ITEMS === BASIC_ITEMS { int id PK string name float stock string unit "kg, liters, pieces" float base_cost float tax_rate datetime expiration_date datetime last_updated int last_updated_by FK text description "nullable" } %% === MENU ITEMS === MENU_ITEMS { int id PK string name UK float price int stock enum category "entry, main_course, dessert, beverage" bool available datetime created_at text description "nullable" } %% === MENU ITEM COMPONENTS === MENU_ITEM_COMPONENTS { int menu_item_id PK,FK int basic_item_id PK,FK float quantity_required } %% === ORDERS === ORDERS { int id PK int user_id FK "nullable for guest orders" int table_id FK enum status "pending, confirmed, preparing, ready, served, completed, cancelled" datetime created_at datetime finished_at "nullable" text specifications "nullable" float total_amount float discount_applied enum payment_method "cash, card, mobile, voucher, pending" string promo_code FK "nullable" int num_customers "number of people for this order" } %% === ORDER ITEMS === ORDER_ITEMS { int order_id PK,FK int item_id PK,FK int quantity float item_price float item_cost } %% === PROMOTIONS === PROMOTIONS { int id PK string code UK text description float discount_percentage enum target_category "nullable" int target_menu_item "nullable" datetime start_date datetime end_date } %% === INVENTORY LOGS === INVENTORY_LOGS { int id PK int user_id FK int item_id FK datetime timestamp float stock_change enum reason "initial_stock, restock, sale, waste, theft, correction, return, sample" string notes "nullable" } %% === STAFF SHIFTS === STAFF_SHIFTS { int id PK int user_id FK datetime shift_start datetime shift_end enum role "waiter, kitchen" } %% === DAILY LOGS === DAILY_LOGS { int id PK date log_date UK int total_customers float total_revenue float total_expenses float worked_time } %% === MONTHLY OVERVIEW === MONTHLY_OVERVIEW { int id PK date month_start "e.g., 2025-11-01" string category "revenue, food_cost, staff_cost, electricity, rent, taxes, etc." float amount "positive for income, negative for expense" text notes "optional" } %% === MONTHLY ITEM STATS === MONTHLY_ITEM_STATS { int id PK int menu_item_id FK date month_start int quantity_sold float revenue_generated float total_item_cost float avg_margin } %% === RELATIONSHIPS === USERS ||--o{ ORDERS : "places/manages" TABLES ||--o{ USERS : "seats" TABLES ||--o{ ORDERS : "serves at" ORDERS ||--|{ ORDER_ITEMS : "contains" MENU_ITEMS ||--o{ ORDER_ITEMS : "ordered as" MENU_ITEMS ||--|{ MENU_ITEM_COMPONENTS : "composed of" BASIC_ITEMS ||--o{ MENU_ITEM_COMPONENTS : "ingredient in" USERS ||--o{ BASIC_ITEMS : "last updated by" USERS ||--o{ INVENTORY_LOGS : "performs change" BASIC_ITEMS ||--o{ INVENTORY_LOGS : "tracked in" PROMOTIONS ||--o{ ORDERS : "applied to" DAILY_LOGS ||--o{ STAFF_SHIFTS : "includes staff shifts" DAILY_LOGS ||--o{ ORDERS : "summarizes orders of the day" MONTHLY_ITEM_STATS ||--|| MENU_ITEMS : "analyzes" MONTHLY_ITEM_STATS ||--|| MONTHLY_OVERVIEW : "belongs to month" DAILY_LOGS ||--o{ MONTHLY_OVERVIEW : "aggregated into"

flowchart TD %% === Public client flow === subgraph GuestClient["Guest Client (no login)"] GC["Client (Guest)"] -->|"GET /menu"| PublicAPI GC -->|"POST /orders"| PublicAPI end %% === Unified Authenticated flow === subgraph AuthFlow["Authenticated Users (Client or Staff)"] Login["User logs in (Client / Waiter / Kitchen / Admin) -> POST /token"] --> AuthEndpoint["Auth Endpoint"] AuthEndpoint --> UsersDB["Users Table"] AuthEndpoint --> JWT["JWT Token Created"] JWT --> API["Authenticated API (profile, orders, management, etc.)"] API --> RoleCheck["Role-based Access"] RoleCheck --> Client["Client: access personal data & order history"] RoleCheck --> Waiter["Waiter: manage orders & receive WS updates"] RoleCheck --> Kitchen["Kitchen: update order statuses"] RoleCheck --> Admin["Admin: manage menu & users"] end %% === Database === DB["(Orders, Items, Tables, Users)"] SPDB["(Items, Tables)"] %% === Roles branching === RoleCheck --> DB %% === Shared backend access === PublicAPI --> SPDB