Index: backend/01.init.sql =================================================================== --- backend/01.init.sql (revision 893cb2e718a83ca5e4fac44b4ab00737f16540ac) +++ backend/01.init.sql (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) @@ -442,12 +442,5 @@ fs.employee_id, u.email as server_email, - u.phone_number, - e.net_salary, - e.gross_salary, - fs.tip_percent, - sr.name as staff_role_name, COUNT(DISTINCT a.id) as total_assignments, - COUNT(DISTINCT s.date) as days_worked, - AVG(EXTRACT(EPOCH FROM (a.clock_out_time - a.clock_in_time))/3600) as avg_hours_per_shift, COUNT(DISTINCT o.id) as orders_processed, COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated @@ -476,25 +469,13 @@ END as orders_per_assignment, CASE - WHEN gross_salary > 0 - THEN total_revenue_generated / gross_salary - ELSE 0 - END as revenue_to_salary_ratio, - CASE WHEN orders_processed > 0 THEN total_revenue_generated / orders_processed ELSE 0 - END as avg_revenue_per_order, - CASE - WHEN tip_percent > 0 AND total_revenue_generated > 0 - THEN (total_revenue_generated * tip_percent / 100) - ELSE 0 - END as estimated_tips_earned + END as avg_revenue_per_order FROM server_metrics ) SELECT server_email, - phone_number, total_assignments, - days_worked, orders_processed, total_revenue_generated, @@ -551,15 +532,5 @@ 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, - CASE - WHEN COUNT(DISTINCT r.id) = 0 THEN false - ELSE (COUNT(DISTINCT o.id) * 100.0 / COUNT(DISTINCT r.id)) >= 70 - END as meets_conversion_target, - CASE - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 2000 THEN 'High Revenue Day' - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 1000 THEN 'Good Revenue Day' - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 500 THEN 'Average Revenue Day' - ELSE 'Low Revenue Day' - END as revenue_category + COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue FROM generate_series( CURRENT_DATE - INTERVAL '30 days', @@ -599,8 +570,5 @@ -- ANALYTIC: Revenue by shift period (dynamic monthly view) - CREATE OR REPLACE VIEW v_revenue_by_shift_period AS - --- CTE to get all unique shift start and end times from your shifts table WITH distinct_shift_periods AS ( SELECT DISTINCT @@ -610,14 +578,7 @@ shifts ) - --- Main query to calculate revenue -SELECT - -- 1. The month of the order, e.g., '2025-08' +SELECT TO_CHAR(o.datetime, 'YYYY-MM') AS period, - - -- 2. The shift period string, e.g., '08:00:00-16:00:00' dsp.start_t::text || '-' || dsp.end_t::text AS shift_period, - - -- 3. The total revenue for that shift period within that month SUM(oi.price * oi.quantity) AS total_revenue FROM @@ -625,5 +586,4 @@ JOIN order_items oi ON o.id = oi.order_id --- Join each order to a shift period if its timestamp's time falls within the start and end times JOIN distinct_shift_periods dsp ON o.datetime::time >= dsp.start_t AND o.datetime::time < dsp.end_t Index: backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision 893cb2e718a83ca5e4fac44b4ab00737f16540ac) +++ backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) @@ -22,12 +22,5 @@ fs.employee_id, u.email as server_email, - u.phone_number, - e.net_salary, - e.gross_salary, - fs.tip_percent, - sr.name as staff_role_name, COUNT(DISTINCT a.id) as total_assignments, - COUNT(DISTINCT s.date) as days_worked, - AVG(EXTRACT(EPOCH FROM (a.clock_out_time - a.clock_in_time))/3600) as avg_hours_per_shift, COUNT(DISTINCT o.id) as orders_processed, COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated @@ -53,20 +46,12 @@ THEN (orders_processed::float / total_assignments) ELSE 0 END as orders_per_assignment, - CASE WHEN gross_salary > 0 - THEN total_revenue_generated / gross_salary - ELSE 0 END as revenue_to_salary_ratio, CASE WHEN orders_processed > 0 THEN total_revenue_generated / orders_processed - ELSE 0 END as avg_revenue_per_order, - CASE WHEN tip_percent > 0 AND total_revenue_generated > 0 - THEN (total_revenue_generated * tip_percent / 100) - ELSE 0 END as estimated_tips_earned + ELSE 0 END as avg_revenue_per_order FROM server_metrics ) SELECT server_email, - phone_number, total_assignments, - days_worked, orders_processed, total_revenue_generated, @@ -80,7 +65,5 @@ return jdbc.query(sql, (rs, i) -> new ServerPerformanceDto( rs.getString("server_email"), - rs.getString("phone_number"), getLong(rs, "total_assignments"), - getLong(rs, "days_worked"), getLong(rs, "orders_processed"), rs.getBigDecimal("total_revenue_generated"), @@ -140,14 +123,5 @@ 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, - CASE WHEN COUNT(DISTINCT r.id) = 0 THEN false - ELSE (COUNT(DISTINCT o.id) * 100.0 / COUNT(DISTINCT r.id)) >= 70 - END as meets_conversion_target, - CASE - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 2000 THEN 'High Revenue Day' - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 1000 THEN 'Good Revenue Day' - WHEN COALESCE(SUM(oi.quantity * oi.price), 0) >= 500 THEN 'Average Revenue Day' - ELSE 'Low Revenue Day' - END as revenue_category + COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue FROM generate_series( CURRENT_DATE - (? || ' days')::interval, @@ -169,7 +143,5 @@ getLong(rs, "unique_customers"), getLong(rs, "active_employees"), - rs.getBigDecimal("daily_revenue"), - rs.getBoolean("meets_conversion_target"), - rs.getString("revenue_category") + rs.getBigDecimal("daily_revenue") )); } Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/DailyOpsDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/DailyOpsDto.java (revision 893cb2e718a83ca5e4fac44b4ab00737f16540ac) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/DailyOpsDto.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) @@ -10,6 +10,4 @@ Long uniqueCustomers, Long activeEmployees, - BigDecimal dailyRevenue, - Boolean meetsConversionTarget, - String revenueCategory + BigDecimal dailyRevenue ) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/ServerPerformanceDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/ServerPerformanceDto.java (revision 893cb2e718a83ca5e4fac44b4ab00737f16540ac) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/ServerPerformanceDto.java (revision 326b462412a41ebfedf5aefad8ed79f7c64c8bd2) @@ -5,7 +5,5 @@ public record ServerPerformanceDto( String serverEmail, - String phoneNumber, Long totalAssignments, - Long daysWorked, Long ordersProcessed, BigDecimal totalRevenueGenerated,