Index: backend/01.init.sql =================================================================== --- backend/01.init.sql (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/01.init.sql (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -1,4 +1,3 @@ BEGIN; - CREATE TABLE IF NOT EXISTS users ( @@ -114,5 +113,5 @@ product_id BIGSERIAL PRIMARY KEY, quantity INT NOT NULL, - restock_level INT NULL, + restock_level INT NULL, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE ); @@ -121,5 +120,7 @@ id BIGSERIAL PRIMARY KEY, status VARCHAR(255) NOT NULL DEFAULT 'PENDING', - datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + employee_id BIGINT DEFAULT NULL, + FOREIGN KEY (employee_id) REFERENCES employees(user_id) ON DELETE SET NULL ON UPDATE CASCADE ); @@ -138,9 +139,7 @@ CREATE TABLE IF NOT EXISTS tab_orders ( order_id BIGSERIAL PRIMARY KEY, - front_staff_id BIGINT NOT NULL, table_number BIGINT NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE, - FOREIGN KEY (table_number) REFERENCES tables (table_number) ON DELETE CASCADE ON UPDATE CASCADE, - FOREIGN KEY (front_staff_id) REFERENCES front_staff (employee_id) ON DELETE CASCADE ON UPDATE CASCADE + FOREIGN KEY (table_number) REFERENCES tables (table_number) ON DELETE CASCADE ON UPDATE CASCADE ); @@ -163,13 +162,14 @@ ); --- ASSERTIONS +--- +-- Assertions ALTER TABLE employees - ADD CONSTRAINT employees_net_salary_nonneg CHECK (net_salary IS NULL OR net_salary >= 0), - ADD CONSTRAINT employees_gross_salary_nonneg CHECK (gross_salary IS NULL OR gross_salary >= 0); + ADD CONSTRAINT employees_net_salary_nonneg CHECK (net_salary IS NULL OR net_salary >= 0), + ADD CONSTRAINT employees_gross_salary_nonneg CHECK (gross_salary IS NULL OR gross_salary >= 0); ALTER TABLE inventories ADD CONSTRAINT inventories_qty_nonneg CHECK (quantity >= 0), - ADD CONSTRAINT inventories_restock_nonneg CHECK (restock_level IS NULL OR restock_level >= 0); + ADD CONSTRAINT inventories_restock_nonneg CHECK (restock_level IS NULL OR restock_level >= 0); ALTER TABLE products @@ -178,9 +178,9 @@ ALTER TABLE order_items ADD CONSTRAINT order_items_qty_pos CHECK (quantity > 0), - ADD CONSTRAINT order_items_price_nonneg CHECK (price >= 0); + ADD CONSTRAINT order_items_price_nonneg CHECK (price >= 0); ALTER TABLE payments ADD CONSTRAINT payments_amount_nonneg CHECK (amount >= 0), - ADD CONSTRAINT payments_tip_nonneg CHECK (tip_amount >= 0); + ADD CONSTRAINT payments_tip_nonneg CHECK (tip_amount >= 0); ALTER TABLE tables @@ -193,4 +193,6 @@ ADD CONSTRAINT front_staff_tip_pct CHECK (tip_percent IS NULL OR (tip_percent >= 0 AND tip_percent <= 100)); +--- +-- Functions & Triggers CREATE OR REPLACE FUNCTION enforce_no_double_booking() @@ -198,5 +200,5 @@ DECLARE new_span tsrange; - conflict_exists boolean; + conflict_exists boolean; BEGIN SELECT tsrange( @@ -204,11 +206,10 @@ r.datetime + (COALESCE(r.stay_length, 2) * INTERVAL '1 hour'), '[)' - ) -INTO new_span + ) INTO new_span FROM reservations r WHERE r.id = NEW.reservation_id; IF new_span IS NULL THEN - RAISE EXCEPTION 'Reservation % not found or invalid', NEW.reservation_id; + RAISE EXCEPTION 'Reservation % not found or invalid', NEW.reservation_id; END IF; @@ -227,5 +228,5 @@ IF conflict_exists THEN - RAISE EXCEPTION 'Double booking prevented: table % has overlapping reservations', NEW.table_number; + RAISE EXCEPTION 'Double booking prevented: table % has overlapping reservations', NEW.table_number; END IF; @@ -255,12 +256,11 @@ ) ); -CREATE INDEX IF NOT EXISTS idx_payments_order ON payments(order_id); +CREATE INDEX IF NOT EXISTS idx_payments_order ON payments(order_id); CREATE INDEX IF NOT EXISTS idx_payments_created_at ON payments(created_at); - CREATE OR REPLACE FUNCTION fmr_capacity_guard() RETURNS trigger AS $$ DECLARE cap int; - party bigint; + party bigint; BEGIN SELECT seat_capacity INTO cap FROM tables WHERE table_number = NEW.table_number; @@ -268,10 +268,10 @@ IF cap IS NULL OR party IS NULL THEN - RAISE EXCEPTION 'Invalid reservation % or table %', NEW.reservation_id, NEW.table_number; + RAISE EXCEPTION 'Invalid reservation % or table %', NEW.reservation_id, NEW.table_number; END IF; - IF party > cap THEN - RAISE EXCEPTION 'Party size % exceeds capacity % for table %', - party, cap, NEW.table_number; + IF party > cap THEN + RAISE EXCEPTION 'Party size % exceeds capacity % for table %', + party, cap, NEW.table_number; END IF; @@ -289,5 +289,4 @@ BEFORE UPDATE ON frontstaff_managed_reservations FOR EACH ROW EXECUTE FUNCTION fmr_capacity_guard(); - CREATE OR REPLACE FUNCTION payments_mark_order_paid() RETURNS trigger AS $$ @@ -308,19 +307,19 @@ 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 + 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 + 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 @@ -330,8 +329,8 @@ ON mv_payments_daily_channel (day, channel); - - +--- -- DML -INSERT INTO users(id, email, password, phone_number, street, city) -- password1 + +INSERT INTO users(id, email, password, phone_number, street, city) VALUES (1, 'test@hotmail.com', '$2a$10$rliIgXfgZgT1ljzxf.3NjeF1hx63s30xKfUsKjeUYA8jL/GXA1Jsy', '070003005', 'Mladinska 3', 'Strumica'), @@ -385,5 +384,5 @@ (2, 8); -INSERT INTO reservations(id, user_id, creation_timestamp, datetime, stay_length, number_of_people) +INSERT INTO reservations(id, user_id, creation_timestamp, datetime, stay_length, number_of_people) VALUES (1, 2, now(), now(), NULL, 4); @@ -408,9 +407,9 @@ (1, 100); -INSERT INTO orders(id, status, datetime) -VALUES - (1, 'PENDING', '2025-01-05 10:00:00'), - (2, 'ACCEPTED', '2025-01-05 10:00:00'), - (3, 'CONFIRMED', '2025-01-05 11:00:00'); +INSERT INTO orders(id, status, datetime, employee_id) +VALUES + (1, 'PENDING', '2025-01-05 10:00:00', 1), + (2, 'ACCEPTED', '2025-01-05 10:00:00', 1), + (3, 'CONFIRMED', '2025-01-05 11:00:00', NULL); INSERT INTO order_items (id, order_id, product_id, is_processed, quantity, price) @@ -423,7 +422,7 @@ SELECT 4, 3, 1, FALSE, 2, price FROM products WHERE id = 1; -INSERT INTO tab_orders(order_id, front_staff_id, table_number) -VALUES - (1, 1, 1); +INSERT INTO tab_orders(order_id, table_number) +VALUES + (1, 1); INSERT INTO online_orders(order_id, delivery_address, customer_id) @@ -436,4 +435,7 @@ REFRESH MATERIALIZED VIEW mv_payments_daily_channel; + +--- +-- Analytics -- ANALYTIC: Server performance & revenue ranking (last 3 months) @@ -451,6 +453,5 @@ LEFT JOIN assignments a ON fs.employee_id = a.employee_id LEFT JOIN shifts s ON a.shift_id = s.id - LEFT JOIN tab_orders to2 ON to2.front_staff_id = fs.employee_id - LEFT JOIN orders o ON o.id = to2.order_id + LEFT JOIN orders o ON o.employee_id = fs.employee_id AND o.datetime >= CURRENT_DATE - INTERVAL '3 months' LEFT JOIN order_items oi ON o.id = oi.order_id @@ -531,5 +532,5 @@ COUNT(DISTINCT o.id) as total_orders, COUNT(DISTINCT r.user_id) as unique_customers, - COUNT(DISTINCT fs.employee_id) as active_employees, + COUNT(DISTINCT a.employee_id) as active_employees, COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue FROM generate_series( @@ -538,14 +539,9 @@ '1 day'::interval ) dates(operation_date) - LEFT JOIN reservations r - ON DATE(r.datetime) = dates.operation_date - LEFT JOIN orders o -ON DATE(o.datetime) = dates.operation_date - LEFT JOIN order_items oi - ON o.id = oi.order_id - LEFT JOIN tab_orders to2 - ON o.id = to2.order_id - LEFT JOIN front_staff fs - ON to2.front_staff_id = fs.employee_id + LEFT JOIN reservations r ON DATE(r.datetime) = dates.operation_date + LEFT JOIN orders o ON DATE(o.datetime) = dates.operation_date + LEFT JOIN order_items oi ON o.id = oi.order_id + LEFT JOIN shifts s ON DATE(s.date) = dates.operation_date + LEFT JOIN assignments a ON s.id = a.shift_id GROUP BY dates.operation_date ORDER BY dates.operation_date DESC; @@ -569,5 +565,4 @@ -- ANALYTIC: Revenue by shift period (dynamic monthly view) - CREATE OR REPLACE VIEW v_revenue_by_shift_period AS WITH distinct_shift_periods AS ( @@ -575,11 +570,10 @@ start_time::time AS start_t, end_time::time AS end_t - FROM - shifts + FROM shifts ) SELECT TO_CHAR(o.datetime, 'YYYY-MM') AS period, dsp.start_t::text || '-' || dsp.end_t::text AS shift_period, - SUM(oi.price * oi.quantity) AS total_revenue + SUM(oi.price * oi.quantity) AS total_revenue FROM orders o @@ -595,7 +589,4 @@ shift_period ASC; -SELECT * FROM v_revenue_by_shift_period; - - -- ANALYTIC: Revenue split (Online vs Tab) for a date range (by order date) DROP FUNCTION IF EXISTS get_revenue_split(date, date); @@ -611,7 +602,5 @@ JOIN online_orders oo ON o.id = oo.order_id WHERE o.datetime::date BETWEEN p_start_date AND p_end_date - UNION ALL - SELECT 'Tab Orders'::text AS order_type, COALESCE(SUM(p.amount), 0)::numeric(14,2) AS total_revenue @@ -632,8 +621,8 @@ a.employee_id AS manager_id FROM assignments a - JOIN shifts s ON s.id = a.shift_id + JOIN shifts s ON s.id = a.shift_id JOIN managers m ON m.employee_id = a.employee_id WHERE s.date >= date_trunc('year', CURRENT_DATE)::date - AND s.date < (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date + AND s.date < (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date ), shift_revenue AS ( @@ -650,5 +639,5 @@ ON o.datetime::date = mws.date AND o.datetime::time >= mws.start_time - AND o.datetime::time < mws.end_time + AND o.datetime::time < mws.end_time LEFT JOIN order_items oi ON oi.order_id = o.id GROUP BY @@ -665,6 +654,6 @@ sr.shift_date, sr.start_time AS shift_start_time, - sr.end_time AS shift_end_time, - u.email AS manager_email, + sr.end_time AS shift_end_time, + u.email AS manager_email, sr.shift_revenue, ma.avg_revenue_per_shift, @@ -672,22 +661,21 @@ FROM shift_revenue sr JOIN monthly_avg ma ON ma.month_start = sr.month_start - JOIN managers m ON m.employee_id = sr.manager_id - JOIN employees e ON e.user_id = m.employee_id - JOIN users u ON u.id = e.user_id + JOIN managers m ON m.employee_id = sr.manager_id + JOIN employees e ON e.user_id = m.employee_id + JOIN users u ON u.id = e.user_id WHERE sr.shift_revenue > ma.avg_revenue_per_shift ORDER BY period DESC, sr.shift_revenue DESC, sr.shift_date DESC; - -SELECT setval('public.users_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.users; -SELECT setval('public.staff_roles_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.staff_roles; -SELECT setval('public.shifts_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.shifts; -SELECT setval('public.assignments_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.assignments; -SELECT setval('public.reservations_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.reservations; -SELECT setval('public.frontstaff_managed_reservations_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.frontstaff_managed_reservations; -SELECT setval('public.categories_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.categories; -SELECT setval('public.products_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.products; -SELECT setval('public.orders_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.orders; -SELECT setval('public.order_items_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.order_items; -SELECT setval('public.payments_id_seq', COALESCE(MAX(id + 1), 1), false) FROM public.payments; +SELECT setval('public.users_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.users) + 1, false); +SELECT setval('public.staff_roles_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.staff_roles) + 1, false); +SELECT setval('public.shifts_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.shifts) + 1, false); +SELECT setval('public.assignments_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.assignments) + 1, false); +SELECT setval('public.reservations_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.reservations) + 1, false); +SELECT setval('public.frontstaff_managed_reservations_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.frontstaff_managed_reservations) + 1, false); +SELECT setval('public.categories_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.categories) + 1, false); +SELECT setval('public.products_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.products) + 1, false); +SELECT setval('public.orders_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.orders) + 1, false); +SELECT setval('public.order_items_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.order_items) + 1, false); +SELECT setval('public.payments_id_seq', (SELECT COALESCE(MAX(id), 0) FROM public.payments) + 1, false); COMMIT; Index: backend/src/main/java/finki/db/tasty_tabs/entity/Employee.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/Employee.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/entity/Employee.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -23,7 +23,18 @@ private Double grossSalary; + @OneToMany(mappedBy = "employee") + private List tabOrders; + // Relationship for assignments given to this employee @OneToMany(mappedBy = "employee") private List assignments; + + public List getTabOrders() { + return tabOrders; + } + + public void setTabOrders(List tabOrders) { + this.tabOrders = tabOrders; + } public Employee() { Index: backend/src/main/java/finki/db/tasty_tabs/entity/FrontStaff.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/FrontStaff.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/entity/FrontStaff.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -25,7 +25,4 @@ private StaffRole staffRole; - // Relationship for tab orders handled by this front staff member - @OneToMany(mappedBy = "frontStaff") - private List tabOrders; @OneToMany(mappedBy = "frontStaff") @@ -48,11 +45,5 @@ } - public List getTabOrders() { - return tabOrders; - } - public void setTabOrders(List tabOrders) { - this.tabOrders = tabOrders; - } public List getManagedReservations() { Index: backend/src/main/java/finki/db/tasty_tabs/entity/Order.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/Order.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/entity/Order.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -36,4 +36,8 @@ private Payment payment; + @ManyToOne(fetch = FetchType.LAZY) + @JoinColumn(name = "employee_id", referencedColumnName = "user_id", nullable = true) + private Employee employee; + public Long getId() { return id; @@ -72,4 +76,12 @@ } + public Employee getEmployee() { + return employee; + } + + public void setEmployee(Employee employee) { + this.employee = employee; + } + public void setPayment(Payment payment) { this.payment = payment; Index: backend/src/main/java/finki/db/tasty_tabs/entity/TabOrder.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/TabOrder.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/entity/TabOrder.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -19,7 +19,16 @@ private RestaurantTable restaurantTable; - @ManyToOne - @JoinColumn(name = "front_staff_id", referencedColumnName = "employee_id", nullable = false) - private FrontStaff frontStaff; + @OneToOne + @MapsId + @JoinColumn(name = "order_id") + private Order order; + + public Order getOrder() { + return order; + } + + public void setOrder(Order order) { + this.order = order; + } public RestaurantTable getRestaurantTable() { @@ -30,11 +39,3 @@ this.restaurantTable = restaurantTable; } - - public FrontStaff getFrontStaff() { - return frontStaff; - } - - public void setFrontStaff(FrontStaff frontStaff) { - this.frontStaff = frontStaff; - } } Index: backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -18,49 +18,48 @@ public List serverPerformanceLast3Months() { String sql = """ - WITH server_metrics AS ( - SELECT - fs.employee_id, - u.email as server_email, - COUNT(DISTINCT a.id) as total_assignments, - COUNT(DISTINCT o.id) as orders_processed, - COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated - FROM front_staff fs - JOIN employees e ON fs.employee_id = e.user_id - JOIN users u ON e.user_id = u.id - JOIN staff_roles sr ON fs.staff_role_id = sr.id - LEFT JOIN assignments a ON fs.employee_id = a.employee_id - LEFT JOIN shifts s ON a.shift_id = s.id - LEFT JOIN tab_orders to2 ON to2.front_staff_id = fs.employee_id - LEFT JOIN orders o ON o.id = to2.order_id - AND o.datetime >= CURRENT_DATE - INTERVAL '3 months' - LEFT JOIN order_items oi ON o.id = oi.order_id - WHERE LOWER(sr.name) = 'server' - GROUP BY fs.employee_id, u.email, u.phone_number, - e.net_salary, e.gross_salary, fs.tip_percent, sr.name - ), - performance_ranking AS ( - SELECT *, - RANK() OVER (ORDER BY total_revenue_generated DESC) as revenue_rank, - RANK() OVER (ORDER BY orders_processed DESC) as orders_rank, - CASE WHEN total_assignments > 0 - THEN (orders_processed::float / total_assignments) - ELSE 0 END as orders_per_assignment, - CASE WHEN orders_processed > 0 - THEN total_revenue_generated / orders_processed - ELSE 0 END as avg_revenue_per_order - FROM server_metrics - ) + WITH server_metrics AS ( SELECT - server_email, - total_assignments, - orders_processed, - total_revenue_generated, - revenue_rank, - orders_rank, - ROUND(orders_per_assignment::numeric, 2) as avg_orders_per_shift, - ROUND(avg_revenue_per_order::numeric, 2) as avg_order_value - FROM performance_ranking - ORDER BY total_revenue_generated DESC, orders_processed DESC - """; + fs.employee_id, + u.email as server_email, + COUNT(DISTINCT a.id) as total_assignments, + COUNT(DISTINCT o.id) as orders_processed, + COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated + FROM front_staff fs + JOIN employees e ON fs.employee_id = e.user_id + JOIN users u ON e.user_id = u.id + JOIN staff_roles sr ON fs.staff_role_id = sr.id + LEFT JOIN assignments a ON fs.employee_id = a.employee_id + LEFT JOIN shifts s ON a.shift_id = s.id + LEFT JOIN orders o ON o.employee_id = fs.employee_id + AND o.datetime >= CURRENT_DATE - INTERVAL '3 months' + LEFT JOIN order_items oi ON o.id = oi.order_id + WHERE LOWER(sr.name) = 'server' + GROUP BY fs.employee_id, u.email, u.phone_number, + e.net_salary, e.gross_salary, fs.tip_percent, sr.name + ), + performance_ranking AS ( + SELECT *, + RANK() OVER (ORDER BY total_revenue_generated DESC) as revenue_rank, + RANK() OVER (ORDER BY orders_processed DESC) as orders_rank, + CASE WHEN total_assignments > 0 + THEN (orders_processed::float / total_assignments) + ELSE 0 END as orders_per_assignment, + CASE WHEN orders_processed > 0 + THEN total_revenue_generated / orders_processed + ELSE 0 END as avg_revenue_per_order + FROM server_metrics + ) + SELECT + server_email, + total_assignments, + orders_processed, + total_revenue_generated, + revenue_rank, + orders_rank, + ROUND(orders_per_assignment::numeric, 2) as avg_orders_per_shift, + ROUND(avg_revenue_per_order::numeric, 2) as avg_order_value + FROM performance_ranking + ORDER BY total_revenue_generated DESC, orders_processed DESC + """; return jdbc.query(sql, (rs, i) -> new ServerPerformanceDto( rs.getString("server_email"), @@ -74,5 +73,4 @@ )); } - public List monthlyRevenueVsLabor() { String sql = """ @@ -117,24 +115,24 @@ public List dailyOpsSummary(int daysBack) { String sql = """ - SELECT - dates.operation_date, - COUNT(DISTINCT r.id) as total_reservations, - COUNT(DISTINCT o.id) as total_orders, - COUNT(DISTINCT r.user_id) as unique_customers, - COUNT(DISTINCT fs.employee_id) as active_employees, - COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue - FROM generate_series( - CURRENT_DATE - (? || ' days')::interval, - CURRENT_DATE, - '1 day'::interval - ) dates(operation_date) - LEFT JOIN reservations r ON DATE(r.datetime) = dates.operation_date - LEFT JOIN orders o ON DATE(o.datetime) = dates.operation_date - LEFT JOIN order_items oi ON o.id = oi.order_id - LEFT JOIN tab_orders to2 ON o.id = to2.order_id - LEFT JOIN front_staff fs ON to2.front_staff_id = fs.employee_id - GROUP BY dates.operation_date - ORDER BY dates.operation_date DESC - """; + SELECT + dates.operation_date, + COUNT(DISTINCT r.id) as total_reservations, + COUNT(DISTINCT o.id) as total_orders, + COUNT(DISTINCT r.user_id) as unique_customers, + COUNT(DISTINCT a.employee_id) as active_employees, + COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue + FROM generate_series( + CURRENT_DATE - (? || ' days')::interval, + CURRENT_DATE, + '1 day'::interval + ) dates(operation_date) + LEFT JOIN reservations r ON DATE(r.datetime) = dates.operation_date + LEFT JOIN orders o ON DATE(o.datetime) = dates.operation_date + LEFT JOIN order_items oi ON o.id = oi.order_id + LEFT JOIN shifts s ON DATE(s.date) = dates.operation_date + LEFT JOIN assignments a ON s.id = a.shift_id + GROUP BY dates.operation_date + ORDER BY dates.operation_date DESC + """; return jdbc.query(sql, ps -> ps.setInt(1, daysBack), (rs, i) -> new DailyOpsDto( rs.getDate("operation_date").toLocalDate(), @@ -146,5 +144,4 @@ )); } - public List topProducts(int daysBack, int limit) { String sql = """ Index: backend/src/main/java/finki/db/tasty_tabs/repository/TabOrderRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/TabOrderRepository.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/repository/TabOrderRepository.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -11,5 +11,5 @@ public interface TabOrderRepository extends JpaRepository { List findByRestaurantTable_TableNumberAndTimestampBetween(Integer tableNumber, LocalDateTime startOfDay, LocalDateTime endOfDay); - List findAllByFrontStaffId(Long frontStaffId); + List findAllByOrder_Employee_Id(Long employeeId); List findAllByStatus(String status); Index: backend/src/main/java/finki/db/tasty_tabs/service/OrderService.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/OrderService.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/service/OrderService.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -37,5 +37,4 @@ List findTabOrdersByTableAndDate(Integer tableNumber, LocalDate date); List findTabOrdersByStaff(Long frontStaffId); - TabOrder assignOrderToStaff(Long orderId, Long frontStaffId); List findOpenOrders(); Index: backend/src/main/java/finki/db/tasty_tabs/service/impl/OrderServiceImpl.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/impl/OrderServiceImpl.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/service/impl/OrderServiceImpl.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -202,4 +202,5 @@ // Specific TabOrder methods + // Specific TabOrder methods @Override @Transactional @@ -209,5 +210,5 @@ User user = userRepository.findByEmail(userEmail) .orElseThrow(() -> new UsernameNotFoundException("User with email " + userEmail + " not found.")); - if (!(user instanceof FrontStaff)) { + if (!(user instanceof Employee)) { // Changed to Employee since any employee can create an order now throw new SecurityException("User is not authorized to create tab orders."); } @@ -216,7 +217,8 @@ .orElseThrow(() -> new TableNotFoundException(dto.tableNumber())); tabOrder.setRestaurantTable(table); - tabOrder.setFrontStaff((FrontStaff) user); tabOrder.setTimestamp(LocalDateTime.now()); tabOrder.setStatus(dto.status()); + tabOrder.setEmployee((Employee) user); // Assign the employee who created the order + if (dto.orderItems() != null && !dto.orderItems().isEmpty()) { log.debug("OrderItems is not empty, processing items..."); @@ -257,18 +259,8 @@ @Override public List findTabOrdersByStaff(Long frontStaffId) { - return tabOrderRepository.findAllByFrontStaffId(frontStaffId); - } - - @Override - @Transactional - @CheckOnDuty - public TabOrder assignOrderToStaff(Long orderId, Long frontStaffId) { - TabOrder tabOrder = tabOrderRepository.findById(orderId) - .orElseThrow(() -> new OrderNotFoundException(orderId)); - FrontStaff frontStaff = frontStaffRepository.findById(frontStaffId) - .orElseThrow(() -> new FrontStaffNotFoundException(frontStaffId)); - tabOrder.setFrontStaff(frontStaff); - return tabOrderRepository.save(tabOrder); - } + return tabOrderRepository.findAllByOrder_Employee_Id(frontStaffId); + } + + // General methods for all order types Index: backend/src/main/java/finki/db/tasty_tabs/web/controllers/OrderController.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/controllers/OrderController.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/web/controllers/OrderController.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -168,9 +168,4 @@ } - @Operation(summary = "Assign a TabOrder to a specific front staff member") - @PatchMapping("/tab/{orderId}/assign-staff/{frontStaffId}") - public ResponseEntity assignOrderToStaff(@PathVariable Long orderId, @PathVariable Long frontStaffId) { - return ResponseEntity.ok(OrderDto.from(orderService.assignOrderToStaff(orderId, frontStaffId))); - } @Operation(summary = "Get all open orders (pending or in-progress)") @GetMapping("/open") Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/OrderDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/OrderDto.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/OrderDto.java (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -44,5 +44,5 @@ null, tabOrder.getRestaurantTable().getTableNumber(), - tabOrder.getFrontStaff().getEmail(), + tabOrder.getEmployee().getEmail(), tabOrder.getOrderItems().stream() .map(OrderItemDto::from) Index: backend/src/main/resources/application.properties =================================================================== --- backend/src/main/resources/application.properties (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) +++ backend/src/main/resources/application.properties (revision 30a0191474e9bd084bb988564f47583144341c53) @@ -1,7 +1,7 @@ spring.application.name=Tasty Tabs -spring.datasource.url=jdbc:postgresql://localhost:5434/db_202425z_va_prj_tasty_tabs -spring.datasource.username=db_202425z_va_prj_tasty_tabs_owner -spring.datasource.password=99e003badb51 -#spring.profiles.active=dev +#spring.datasource.url=jdbc:postgresql://localhost:5434/db_202425z_va_prj_tasty_tabs +#spring.datasource.username=db_202425z_va_prj_tasty_tabs_owner +#spring.datasource.password=99e003badb51 +spring.profiles.active=test springdoc.api-docs.enabled=true springdoc.swagger-ui.enabled=true