Advanced Application Development

In the latest version of the application, the following scenarios have been implemented:

ID	Use Case
1	View and Manage Employees
2	View and Manage Shifts
3	Assign Employees to Shifts
4	View and Manage Menu Categories
5	Manage Menu Products
6	View and Manage Restaurant Tables
7	Create and Manage Customer Reservations
8	Create and Manage In-House Orders (Tabs)
9	Add/Update/Remove Items from an Order
10	Process a Payment for an Order
11	View Open Orders
12	View Personal Shift Information (Employee)
13	View Sales Analytics and Reports
14	View Server Performance Statistics
15	View Monthly Revenue vs. Labor Costs
16	View Top-Selling Products
17	Manage Customer Profile and View Order History (Online)
18	Create an Online Order

Assign Employees to Shifts

A manager accesses the /api/assignments endpoint to schedule employees for specific shifts. The frontend displays existing assignments and a form for creating new ones. For this, the following controller is responsible:

@PostMapping
public ResponseEntity<AssignmentDto> createAssignment(@RequestBody CreateAssignmentDto dto, Authentication authentication) {
try {
String managerEmail = authentication.getName();
Assignment assignment = assignmentService.createAssignment(dto, managerEmail);
return ResponseEntity.status(HttpStatus.CREATED).body(AssignmentDto.fromAssignment(assignment));
} catch (SecurityException e) {
return ResponseEntity.status(HttpStatus.FORBIDDEN).build();
}
}

The controller extracts the manager's email from the security context and passes the request data to the assignmentService. To ensure data integrity, the service method is transactional. It validates that the user is a manager and that the specified employee and shift exist before creating and saving a new Assignment entity.

    @Override
    public Assignment createAssignment(CreateAssignmentDto dto, String managerEmail) {
        Manager manager = getManagerByEmail(managerEmail);
        Employee employee = employeeRepository.findById(dto.employeeId())
                .orElseThrow(() -> new EmployeeNotFoundException(dto.employeeId()));
        Shift shift = shiftRepository.findById(dto.shiftId())
                .orElseThrow(() -> new ShiftNotFoundException(dto.shiftId()));

        Assignment assignment = new Assignment(
                dto.clockInTime(),
                dto.clockOutTime(),
                manager,
                employee,
                shift
        );

        return assignmentRepository.save(assignment);
    }

The same implementation, translated into the SQL query that executes in the background, would look like this:

DO $$
DECLARE
v_manager_id BIGINT;
v_employee_id BIGINT;
v_shift_id BIGINT;
BEGIN
-- Assume manager_id=3, employee_id=1, shift_id=1 from the form
v_manager_id := 3;
v_employee_id := 1;
v_shift_id := 1;

    INSERT INTO assignments (manager_id, employee_id, shift_id, clock_in_time, clock_out_time)
    VALUES (v_manager_id, v_employee_id, v_shift_id, NULL, NULL);

    COMMIT;
END $$;

Manage Menu Products

A manager can add new items to the menu via the /api/products/add endpoint. The controller takes a CreateProductDto and passes it to the service layer.

 @Operation(summary = "Create new product")
    @PostMapping("/add")
    public ResponseEntity<ProductDto> save(@RequestBody CreateProductDto dto) {
        return ResponseEntity.status(HttpStatus.CREATED).body(ProductDto.from(productService.createProduct(dto)));
    }

The service logic in ProductServiceImpl is responsible for creating the Product entity. A key feature is the conditional creation of an associated Inventory record. If the manageInventory flag is set to true, a new inventory entry is created in the same transaction. This ensures that a product meant to have its stock tracked will always have an inventory record from the moment of its creation.

    @Override
    public Product createProduct(CreateProductDto dto) {
        Product productTmp=new Product();
        if (dto.name() != null) {
            productTmp.setName(dto.name());
        }
        if (dto.price() != null) {
            productTmp.setPrice(dto.price());
        }
        if(dto.taxClass()!=null){
            productTmp.setTaxClass(dto.taxClass());
        }

        productTmp.setCategory(categoryService.findById(dto.categoryId()));
        productTmp.setDescription(dto.description());

        if(dto.manageInventory()!=null){
            productTmp.setManageInventory(dto.manageInventory());
        }
        Product product=productRepository.save(productTmp);
        if(product.getManageInventory()==Boolean.TRUE){
            Inventory inventory = new Inventory(product, dto.quantity(), dto.restockLevel());
            inventoryRepository.save(inventory);
        }
        return product;
    }

The equivalent transactional SQL block for creating a product with inventory tracking would be:

DO $$
DECLARE
new_product_id BIGINT;
BEGIN
INSERT INTO products (name, description, price, category_id, manage_inventory, tax_class)
VALUES ('Cheeseburger', 'Classic beef burger with cheese', 450.00, 3, TRUE, 'A')
RETURNING id INTO new_product_id;

    INSERT INTO inventories (product_id, quantity, restock_level)
    VALUES (new_product_id, 50, 10);

    COMMIT;
END $$;

Create and Manage In-House Orders (Tabs)

A server (Front Staff) creates a new order for a table by sending a POST request to /api/orders/tab. The controller authenticates the user and delegates the creation logic to the OrderService.

    @Operation(summary = "Create a new tab order for a logged-in front staff member")
    @PostMapping("/tab")
    public ResponseEntity<OrderDto> createTabOrder(@RequestBody CreateOrderDto dto, Authentication authentication) {
        String userEmail = authentication.getName();
        return ResponseEntity.ok(OrderDto.from(orderService.createTabOrder(dto, userEmail)));
    }

The service implementation, createTabOrder, is annotated with @Transactional and @CheckOnDuty (a custom annotation). This ensures the operation is atomic and that the staff member is clocked in. The service verifies the user is a FrontStaff member, finds the specified table, and constructs the TabOrder along with its associated OrderItems.

    @Override
    @Transactional
    @CheckOnDuty
    public TabOrder createTabOrder(CreateOrderDto dto, String userEmail) {
        log.debug("User {} creating a tab order for table {}", userEmail, dto.tableNumber());
        User user = userRepository.findByEmail(userEmail)
                .orElseThrow(() -> new UsernameNotFoundException("User with email " + userEmail + " not found."));
        if (!(user instanceof FrontStaff)) {
            throw new SecurityException("User is not authorized to create tab orders.");
        }
        TabOrder tabOrder = new TabOrder();
        RestaurantTable table = tableRepository.findById(dto.tableNumber())
                .orElseThrow(() -> new TableNotFoundException(dto.tableNumber()));
        tabOrder.setRestaurantTable(table);
        tabOrder.setFrontStaff((FrontStaff) user);
        tabOrder.setTimestamp(LocalDateTime.now());
        tabOrder.setStatus(dto.status());
        if (dto.orderItems() != null && !dto.orderItems().isEmpty()) {
            log.debug("OrderItems is not empty, processing items...");
            List<OrderItem> orderItems = dto.orderItems().stream().map(itemDto -> {
                OrderItem item = new OrderItem();
                item.setOrder(tabOrder);
                item.setQuantity(itemDto.quantity());
                item.setPrice(itemDto.price());
                item.setIsProcessed(itemDto.isProcessed());
                item.setTimestamp(LocalDateTime.now());
                Product product = productRepository.findById(itemDto.productId())
                        .orElseThrow(() -> new ProductNotFoundException(itemDto.productId()));
                item.setProduct(product);
                return item;
            }).collect(Collectors.toList());
            tabOrder.setOrderItems(orderItems);
        }
        return tabOrderRepository.save(tabOrder);
    }

The corresponding SQL operations for creating a new tab order and adding an item would be:

DO $$
DECLARE
new_order_id BIGINT;
v_product_price DECIMAL(10,2);
BEGIN
-- Create the main order record
INSERT INTO orders (status, datetime) VALUES ('PENDING', NOW())
RETURNING id INTO new_order_id;

    -- Link it as a Tab Order for a specific staff and table
    INSERT INTO tab_orders (order_id, front_staff_id, table_number)
    VALUES (new_order_id, 1, 2);

    -- Get current price
    SELECT price INTO v_product_price FROM products WHERE id = 1;

    -- Add item to the order
    INSERT INTO order_items (order_id, product_id, is_processed, quantity, price)
    VALUES (new_order_id, 1, FALSE, 2, v_product_price);

    COMMIT;
END $$;

Process a Payment for an Order

When customers are ready to pay, the server initiates the payment process from the order details screen by calling the /api/payments endpoint.

This action is primarily handled by the PaymentServiceImpl. The createPayment method is annotated with @Transactional and @CheckOnDuty. A critical design choice is the use of a database trigger (payments_mark_order_paid) to update the order's status to PAID. This offloads the responsibility from the application layer to the database, ensuring atomicity. Furthermore, after the payment is successfully saved, the service calls mvRefresher.refreshPaymentsMvAfterCommit() to schedule a refresh of the analytics materialized view, ensuring reports are kept up-to-date.

    @Override
    @Transactional
    @CheckOnDuty
    public Payment createPayment(CreatePaymentDto dto) {
        log.info("Creating payment for orderId: {}", dto.orderId());
        Order order = orderRepository.findById(dto.orderId())
                .orElseThrow(() -> new OrderNotFoundException(dto.orderId()));

        Payment payment = new Payment();
        payment.setAmount(dto.amount());
        payment.setTipAmount(dto.tipAmount());
        payment.setPaymentType(dto.paymentType());
        payment.setTimestamp(LocalDateTime.now()); // ensure mapped to created_at column
        payment.setOrder(order);

        Payment saved = paymentRepository.save(payment);

        // Schedule MV refresh after the transaction commits
        mvRefresher.refreshPaymentsMvAfterCommit();

        return saved;
    }

The trigger and the INSERT statement are defined in SQL as follows:

CREATE OR REPLACE FUNCTION payments_mark_order_paid() RETURNS trigger AS $$
BEGIN
UPDATE orders
SET status = 'PAID'
WHERE id = NEW.order_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- The SQL executed by the application:
INSERT INTO payments(order_id, amount, payment_type, tip_amount)
VALUES (1, 850.00, 'cash', 50.00);

View Sales Analytics and Reports

The page at /api/analytics/reveunueByChannel provides data on sales performance. Given that transaction tables will grow very large, running complex aggregations on every request is inefficient.

To solve this, we implemented a Materialized View (mv_payments_daily_channel). This view pre-aggregates sales data daily, broken down by order channel (TAB vs. ONLINE). When a user requests the report, the application queries this small, fast view instead of the large transaction tables.

    @GetMapping("/reveunueByChannel")
    public AnalyticsByChannelResponse paymentsDailyChannel(
            @RequestParam(required = false)
            @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate from,
            @RequestParam(required = false)
            @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate to
    ) {
        return analyticsService.getPaymentsDailyChannel(from, to);
    }

The service implementation fetches the pre-aggregated rows from the materialized view's repository (mvRepo) and then performs final calculations like totals and groupings in Java memory, which is extremely fast.

    @Override
    public AnalyticsByChannelResponse getPaymentsDailyChannel(LocalDate from, LocalDate to) {
        if (from == null) from = LocalDate.now().minusDays(30);
        if (to == null)   to   = LocalDate.now();

        // pass null channel to fetch ALL channels (JPQL has :channel is null guard)
        List<PaymentsDailyChannel> rows = mvRepo.findRange(from, to, null);

        // group by channel
        Map<String, List<PaymentsDailyChannel>> byChannel = rows.stream()
                .collect(Collectors.groupingBy(PaymentsDailyChannel::getChannel));

        // build per-channel tables
        List<ChannelTableDto> channels = byChannel.entrySet().stream()
                .sorted(Map.Entry.comparingByKey()) // ONLINE, TAB, UNKNOWN (alphabetical)
                .map(e -> {
                    var data = e.getValue().stream()
                            .sorted(Comparator.comparing(PaymentsDailyChannel::getDay).thenComparing(PaymentsDailyChannel::getChannel))
                            .map(r -> new PaymentsDailyChannelDto(
                                    r.getDay(), r.getChannel(), r.getPaidOrdersCnt(), r.getRevenue(), r.getTipTotal()
                            ))
                            .toList();

                    long totalOrders = e.getValue().stream()
                            .map(PaymentsDailyChannel::getPaidOrdersCnt)
                            .filter(v -> v != null)
                            .mapToLong(Long::longValue)
                            .sum();

                    BigDecimal totalRevenue = e.getValue().stream()
                            .map(PaymentsDailyChannel::getRevenue)
                            .filter(v -> v != null)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    BigDecimal totalTips = e.getValue().stream()
                            .map(PaymentsDailyChannel::getTipTotal)
                            .filter(v -> v != null)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    return new ChannelTableDto(e.getKey(), data, totalOrders, totalRevenue, totalTips);
                })
                .toList();

        // grand totals
        long grandOrders = rows.stream()
                .map(PaymentsDailyChannel::getPaidOrdersCnt)
                .filter(v -> v != null)
                .mapToLong(Long::longValue)
                .sum();

        BigDecimal grandRevenue = rows.stream()
                .map(PaymentsDailyChannel::getRevenue)
                .filter(v -> v != null)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        BigDecimal grandTips = rows.stream()
                .map(PaymentsDailyChannel::getTipTotal)
                .filter(v -> v != null)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        return new AnalyticsByChannelResponse(from, to, channels, grandOrders, grandRevenue, grandTips);
    }}

The Materialized View definition in SQL is:

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_payments_daily_channel AS
WITH orders_channel AS (
SELECT
o.id AS order_id,
CASE
WHEN EXISTS (SELECT 1 FROM tab_orders t WHERE t.order_id = o.id) THEN 'TAB'
WHEN EXISTS (SELECT 1 FROM online_orders oo WHERE oo.order_id = o.id) THEN 'ONLINE'
ELSE 'UNKNOWN'
END AS channel
FROM orders o
)
SELECT
(date_trunc('day', p.created_at))::date AS day,
oc.channel,
COUNT(DISTINCT p.order_id) AS paid_orders_cnt,
SUM(p.amount)::numeric(14,2) AS revenue,
SUM(p.tip_amount)::numeric(14,2) AS tip_total
FROM payments p
JOIN orders_channel oc ON oc.order_id = p.order_id
GROUP BY (date_trunc('day', p.created_at))::date, oc.channel;