Database creation, seeding and views

DDL script for database initialization

schema.sql​

DML Script for seeding the database

For database seeding (populating the database), we used a Python-based scripting approach. The script generates the data dynamically and inserts it into the database using the psycopg2 library. Before running the seeding process, an active SSH tunnel to the server must be established. Below is the link to the repository containing the database seeding script.

​https://github.com/AleksandarAngelevski/database-seeder

Views

views.sql​

View description

• vw_order_overview — Shows every order with its waiter, table, order type, and status. Your main "what's going on" summary.
• vw_order_items_detail — Breaks down each order into its individual products with quantities, unit price, total price, and who prepared it.
• vw_invoice_summary — Financial snapshot per invoice — total, tax, and net amount, linked to the order and waiter.
• vw_active_menu — Everything currently on the menu with prices and member types. Only shows active menus.
• vw_table_availability — Lists all tables with their type, capacity, and whether they're available.
• vw_inventory — Current stock levels for every stored product, showing quantity and its base unit.
• vw_todays_reservations — All reservations for today sorted by start time, with guest info and which employee handled it.
• vw_revenue_per_waiter — Sales performance per waiter — total orders, total revenue, and average order value.
• vw_product_usage_history — Full log of stock movements per product, showing how much changed, when, and whether it was a positive or negative change (via ct.Sign).
• vw_active_staff — All currently employed staff (no resignation date) with their role and permissions.