Index: backend/01.init.sql =================================================================== --- backend/01.init.sql (revision 2a99fe90dafc1b06403172fdd3ce230af8c5d345) +++ backend/01.init.sql (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -213,5 +213,4 @@ END IF; - -- Check for overlap on same table_number (exclude self on UPDATE) SELECT EXISTS ( SELECT 1 @@ -331,5 +330,4 @@ ON mv_payments_daily_channel (day, channel); -REFRESH MATERIALIZED VIEW mv_payments_daily_channel; @@ -437,3 +435,279 @@ (1, 3, 700, 'cash', 10); +REFRESH MATERIALIZED VIEW mv_payments_daily_channel; + +-- ANALYTIC: Server performance & revenue ranking (last 3 months) +WITH server_metrics AS ( + SELECT + 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 + 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 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 +FROM server_metrics + ) +SELECT + server_email, + phone_number, + total_assignments, + days_worked, + 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; + +-- ANALYTIC: Monthly revenue vs labor cost (% of revenue) +WITH monthly_revenue AS ( + SELECT + DATE_TRUNC('month', o.datetime) as operation_month, + SUM(oi.quantity * oi.price) as revenue + FROM orders o + JOIN order_items oi ON o.id = oi.order_id + GROUP BY DATE_TRUNC('month', o.datetime) +), + monthly_labor_cost AS ( + SELECT + monthly_assignments.operation_month, + SUM(e.gross_salary) as labor_cost + FROM ( + SELECT DISTINCT + DATE_TRUNC('month', s.date) as operation_month, + a.employee_id + FROM shifts s + JOIN assignments a ON s.id = a.shift_id + ) as monthly_assignments + JOIN employees e ON monthly_assignments.employee_id = e.user_id + GROUP BY monthly_assignments.operation_month + ) +SELECT + TO_CHAR(COALESCE(mr.operation_month, mlc.operation_month), 'YYYY-MM') as period, + ROUND(COALESCE(mr.revenue, 0)::numeric, 2) as total_revenue, + ROUND(COALESCE(mlc.labor_cost, 0)::numeric, 2) as total_labor_cost, + ROUND( + CASE + WHEN COALESCE(mr.revenue, 0) > 0 + THEN (COALESCE(mlc.labor_cost, 0) / mr.revenue * 100) + ELSE 0 + END::numeric, 2 + ) as labor_as_percent_of_revenue +FROM monthly_revenue mr + FULL OUTER JOIN monthly_labor_cost mlc ON mr.operation_month = mlc.operation_month +ORDER BY period DESC; + +-- ANALYTIC: Daily operations summary (30 days) with conversion & revenue thresholds +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, + 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 +FROM generate_series( + CURRENT_DATE - INTERVAL '30 days', + 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; + +-- ANALYTIC: Top 10 products by revenue (last 90 days) + revenue share +SELECT + p.id as product_id, + p.name as product_name, + c.name as category_name, + SUM(oi.quantity) as total_quantity_sold, + SUM(oi.quantity * oi.price) as total_revenue, + ROUND(100.0 * SUM(oi.quantity * oi.price) / SUM(SUM(oi.quantity * oi.price)) OVER (), 2) as revenue_share_percent +FROM products p + JOIN categories c ON p.category_id = c.id + JOIN order_items oi ON p.id = oi.product_id + JOIN orders o ON o.id = oi.order_id +WHERE o.datetime >= CURRENT_DATE - INTERVAL '90 days' +GROUP BY p.id, p.name, c.name +ORDER BY total_revenue DESC + LIMIT 10; + +-- ANALYTIC: Revenue by shift period (dynamic monthly view) +DO +$$ +DECLARE +dynamic_sql text; + shift_columns text; +BEGIN + shift_columns := ( + SELECT string_agg( + format('SUM(CASE WHEN o.datetime::time BETWEEN ''%s'' AND ''%s'' THEN COALESCE(oi.quantity * oi.price, 0) ELSE 0 END) AS "%sto%s"', + t.start_time, t.end_time, + REPLACE(t.start_time::text, ':', ''), + REPLACE(t.end_time::text, ':', '')), + ', ' + ) + FROM ( + SELECT DISTINCT start_time, end_time + FROM shifts + ORDER BY start_time, end_time + ) AS t + ); + + dynamic_sql := format( + 'DROP VIEW IF EXISTS revenue_by_shift_period; + CREATE VIEW revenue_by_shift_period AS + SELECT + TO_CHAR(period_date, ''YYYY-MM'') as period, + %s + FROM generate_series( + date_trunc(''month'', CURRENT_DATE - INTERVAL ''12 months''), + date_trunc(''month'', CURRENT_DATE), + ''1 month''::interval + ) period_date + LEFT JOIN orders o ON DATE_TRUNC(''month'', o.datetime) = period_date + LEFT JOIN order_items oi ON o.id = oi.order_id + GROUP BY period_date + ORDER BY period_date;', + shift_columns + ); + + RAISE NOTICE 'Dynamic SQL: %', dynamic_sql; +EXECUTE dynamic_sql; +END +$$; + +-- ANALYTIC: Revenue split (Online vs Tab) for a date range (by order date) +DROP FUNCTION IF EXISTS get_revenue_split(date, date); + +CREATE OR REPLACE FUNCTION get_revenue_split(p_start_date DATE, p_end_date DATE) +RETURNS TABLE(order_type TEXT, total_revenue NUMERIC(14,2)) AS $$ +BEGIN +RETURN QUERY +SELECT 'Online Orders'::text AS order_type, + COALESCE(SUM(p.amount), 0)::numeric(14,2) AS total_revenue +FROM orders o + JOIN payments p ON o.id = p.order_id + 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 +FROM orders o + JOIN payments p ON o.id = p.order_id + JOIN tab_orders tord ON o.id = tord.order_id +WHERE o.datetime::date BETWEEN p_start_date AND p_end_date; +END; +$$ LANGUAGE plpgsql; + +-- ANALYTIC: Managers' shifts above monthly average revenue (current calendar year) +WITH shifts_in_year AS ( + SELECT s.id AS shift_id, s.date, s.start_time, s.end_time, s.manager_id + FROM shifts s + WHERE s.date >= date_trunc('year', CURRENT_DATE)::date + AND s.date < (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date + ), + shift_revenue AS ( +SELECT + siy.shift_id, + date_trunc('month', siy.date)::date AS month_start, + siy.date AS shift_date, + siy.manager_id, + COALESCE(SUM(oi.quantity * oi.price), 0)::numeric(14,2) AS shift_revenue +FROM shifts_in_year siy + LEFT JOIN orders o +ON o.datetime::date = siy.date + AND o.datetime::time >= siy.start_time + AND o.datetime::time < siy.end_time + LEFT JOIN order_items oi ON oi.order_id = o.id +GROUP BY siy.shift_id, month_start, siy.date, siy.manager_id + ), + monthly_avg AS ( +SELECT month_start, AVG(shift_revenue)::numeric(14,2) AS avg_revenue_per_shift +FROM shift_revenue +GROUP BY month_start + ) +SELECT + to_char(sr.month_start, 'YYYY-MM') AS period, + sr.shift_id, + sr.shift_date, + u.email AS manager_email, + sr.shift_revenue, + ma.avg_revenue_per_shift, + (sr.shift_revenue - ma.avg_revenue_per_shift)::numeric(14,2) AS above_by +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 +WHERE sr.shift_revenue > ma.avg_revenue_per_shift +ORDER BY period DESC, sr.shift_revenue DESC; + + COMMIT; Index: backend/src/main/java/finki/db/tasty_tabs/entity/PaymentsDailyChannel.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/PaymentsDailyChannel.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/entity/PaymentsDailyChannel.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,37 @@ +package finki.db.tasty_tabs.entity; + +import finki.db.tasty_tabs.entity.composite_keys.PaymentsDailyChannelId; +import jakarta.persistence.*; +import org.hibernate.annotations.Immutable; + +import java.math.BigDecimal; +import java.time.LocalDate; + +@Entity +@Table(name = "mv_payments_daily_channel") +@IdClass(PaymentsDailyChannelId.class) +@Immutable +public class PaymentsDailyChannel { + + @Id + private LocalDate day; + + @Id + private String channel; // 'TAB' | 'ONLINE' | 'UNKNOWN' + + @Column(name = "paid_orders_cnt") + private Long paidOrdersCnt; + + private BigDecimal revenue; + + @Column(name = "tip_total") + private BigDecimal tipTotal; + + // getters only (or no setters) to keep it immutable in code + public LocalDate getDay() { return day; } + public String getChannel() { return channel; } + public Long getPaidOrdersCnt() { return paidOrdersCnt; } + public BigDecimal getRevenue() { return revenue; } + public BigDecimal getTipTotal() { return tipTotal; } +} + Index: backend/src/main/java/finki/db/tasty_tabs/entity/composite_keys/PaymentsDailyChannelId.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/entity/composite_keys/PaymentsDailyChannelId.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/entity/composite_keys/PaymentsDailyChannelId.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,22 @@ +package finki.db.tasty_tabs.entity.composite_keys; + +import java.io.Serializable; +import java.time.LocalDate; +import java.util.Objects; + +public class PaymentsDailyChannelId implements Serializable { + private LocalDate day; + private String channel; + + public PaymentsDailyChannelId() {} + public PaymentsDailyChannelId(LocalDate day, String channel) { + this.day = day; this.channel = channel; + } + + @Override public boolean equals(Object o) { + if (this == o) return true; + if (!(o instanceof PaymentsDailyChannelId that)) return false; + return Objects.equals(day, that.day) && Objects.equals(channel, that.channel); + } + @Override public int hashCode() { return Objects.hash(day, channel); } +} Index: backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsAdminRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsAdminRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsAdminRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,32 @@ +package finki.db.tasty_tabs.repository; + +import org.springframework.data.jpa.repository.Modifying; +import org.springframework.data.jpa.repository.Query; +import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Propagation; +import org.springframework.transaction.annotation.Transactional; + +import lombok.RequiredArgsConstructor; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Propagation; +import org.springframework.transaction.annotation.Transactional; + +@Repository +@RequiredArgsConstructor +public class AnalyticsAdminRepository { + + private final JdbcTemplate jdbcTemplate; + + /** Non-blocking for readers; must run outside a transaction */ + @Transactional(propagation = Propagation.NOT_SUPPORTED) + public void refreshMvPaymentsDailyChannelConcurrently() { + jdbcTemplate.update("REFRESH MATERIALIZED VIEW CONCURRENTLY mv_payments_daily_channel"); + } + + /** Blocking for readers; can run inside a transaction */ + @Transactional + public void refreshMvPaymentsDailyChannel() { + jdbcTemplate.update("REFRESH MATERIALIZED VIEW mv_payments_daily_channel"); + } +} Index: backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/repository/AnalyticsReadRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,278 @@ +package finki.db.tasty_tabs.repository; +import finki.db.tasty_tabs.web.dto.*; +import lombok.RequiredArgsConstructor; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.stereotype.Repository; + +import java.sql.ResultSet; +import java.time.LocalDate; +import java.util.List; +import java.util.Map; + +@Repository +@RequiredArgsConstructor +public class AnalyticsReadRepository { + + private final JdbcTemplate jdbc; + + public List serverPerformanceLast3Months() { + String sql = """ + WITH server_metrics AS ( + SELECT + 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 + 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 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 + FROM server_metrics + ) + SELECT + server_email, + phone_number, + total_assignments, + days_worked, + 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"), + rs.getString("phone_number"), + getLong(rs, "total_assignments"), + getLong(rs, "days_worked"), + getLong(rs, "orders_processed"), + rs.getBigDecimal("total_revenue_generated"), + rs.getInt("revenue_rank"), + rs.getInt("orders_rank"), + rs.getBigDecimal("avg_orders_per_shift"), + rs.getBigDecimal("avg_order_value") + )); + } + + public List monthlyRevenueVsLabor() { + String sql = """ + WITH monthly_revenue AS ( + SELECT DATE_TRUNC('month', o.datetime) as operation_month, + SUM(oi.quantity * oi.price) as revenue + FROM orders o + JOIN order_items oi ON o.id = oi.order_id + GROUP BY DATE_TRUNC('month', o.datetime) + ), + monthly_labor_cost AS ( + SELECT monthly_assignments.operation_month, + SUM(e.gross_salary) as labor_cost + FROM ( + SELECT DISTINCT DATE_TRUNC('month', s.date) as operation_month, a.employee_id + FROM shifts s + JOIN assignments a ON s.id = a.shift_id + ) monthly_assignments + JOIN employees e ON monthly_assignments.employee_id = e.user_id + GROUP BY monthly_assignments.operation_month + ) + SELECT TO_CHAR(COALESCE(mr.operation_month, mlc.operation_month), 'YYYY-MM') as period, + ROUND(COALESCE(mr.revenue, 0)::numeric, 2) as total_revenue, + ROUND(COALESCE(mlc.labor_cost, 0)::numeric, 2) as total_labor_cost, + ROUND( + CASE WHEN COALESCE(mr.revenue, 0) > 0 + THEN (COALESCE(mlc.labor_cost, 0) / mr.revenue * 100) + ELSE 0 END::numeric, 2 + ) as labor_as_percent_of_revenue + FROM monthly_revenue mr + FULL OUTER JOIN monthly_labor_cost mlc ON mr.operation_month = mlc.operation_month + ORDER BY period DESC + """; + return jdbc.query(sql, (rs, i) -> new MonthlyRevenueVsLaborDto( + rs.getString("period"), + rs.getBigDecimal("total_revenue"), + rs.getBigDecimal("total_labor_cost"), + rs.getBigDecimal("labor_as_percent_of_revenue") + )); + } + + 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, + 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 + 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 + """; + return jdbc.query(sql, ps -> ps.setInt(1, daysBack), (rs, i) -> new DailyOpsDto( + rs.getDate("operation_date").toLocalDate(), + getLong(rs, "total_reservations"), + getLong(rs, "total_orders"), + getLong(rs, "unique_customers"), + getLong(rs, "active_employees"), + rs.getBigDecimal("daily_revenue"), + rs.getBoolean("meets_conversion_target"), + rs.getString("revenue_category") + )); + } + + public List topProducts(int daysBack, int limit) { + String sql = """ + SELECT + p.id as product_id, + p.name as product_name, + c.name as category_name, + SUM(oi.quantity) as total_quantity_sold, + SUM(oi.quantity * oi.price) as total_revenue, + ROUND(100.0 * SUM(oi.quantity * oi.price) / SUM(SUM(oi.quantity * oi.price)) OVER (), 2) as revenue_share_percent + FROM products p + JOIN categories c ON p.category_id = c.id + JOIN order_items oi ON p.id = oi.product_id + JOIN orders o ON o.id = oi.order_id + WHERE o.datetime >= CURRENT_DATE - (? || ' days')::interval + GROUP BY p.id, p.name, c.name + ORDER BY total_revenue DESC + LIMIT ? + """; + return jdbc.query(sql, ps -> { ps.setInt(1, daysBack); ps.setInt(2, limit); }, (rs, i) -> new TopProductDto( + rs.getLong("product_id"), + rs.getString("product_name"), + rs.getString("category_name"), + getLong(rs, "total_quantity_sold"), + rs.getBigDecimal("total_revenue"), + rs.getBigDecimal("revenue_share_percent") + )); + } + + public List> revenueByShiftPeriodView() { + // dynamic columns -> return as list of maps + return jdbc.queryForList("SELECT * FROM revenue_by_shift_period"); + } + + public List revenueSplit(LocalDate from, LocalDate to) { + return jdbc.query("SELECT * FROM get_revenue_split(?, ?)", + ps -> { ps.setObject(1, from); ps.setObject(2, to); }, + (rs, i) -> new RevenueSplitDto( + rs.getString("order_type"), + rs.getBigDecimal("total_revenue") + )); + } + + public List managersShiftsAboveMonthlyAvgCurrentYear() { + String sql = """ + WITH shifts_in_year AS ( + SELECT s.id AS shift_id, s.date, s.start_time, s.end_time, s.manager_id + FROM shifts s + WHERE s.date >= date_trunc('year', CURRENT_DATE)::date + AND s.date < (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date + ), + shift_revenue AS ( + SELECT + siy.shift_id, + date_trunc('month', siy.date)::date AS month_start, + siy.date AS shift_date, + siy.manager_id, + COALESCE(SUM(oi.quantity * oi.price), 0)::numeric(14,2) AS shift_revenue + FROM shifts_in_year siy + LEFT JOIN orders o + ON o.datetime::date = siy.date + AND o.datetime::time >= siy.start_time + AND o.datetime::time < siy.end_time + LEFT JOIN order_items oi ON oi.order_id = o.id + GROUP BY siy.shift_id, month_start, siy.date, siy.manager_id + ), + monthly_avg AS ( + SELECT month_start, AVG(shift_revenue)::numeric(14,2) AS avg_revenue_per_shift + FROM shift_revenue + GROUP BY month_start + ) + SELECT + to_char(sr.month_start, 'YYYY-MM') AS period, + sr.shift_id, + sr.shift_date, + u.email AS manager_email, + sr.shift_revenue, + ma.avg_revenue_per_shift, + (sr.shift_revenue - ma.avg_revenue_per_shift)::numeric(14,2) AS above_by + 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 + WHERE sr.shift_revenue > ma.avg_revenue_per_shift + ORDER BY period DESC, sr.shift_revenue DESC + """; + return jdbc.query(sql, (rs, i) -> new ManagerShiftAboveAvgDto( + rs.getString("period"), + rs.getLong("shift_id"), + rs.getDate("shift_date").toLocalDate(), + rs.getString("manager_email"), + rs.getBigDecimal("shift_revenue"), + rs.getBigDecimal("avg_revenue_per_shift"), + rs.getBigDecimal("above_by") + )); + } + + private static Long getLong(ResultSet rs, String col) throws java.sql.SQLException { + long v = rs.getLong(col); + return rs.wasNull() ? null : v; + } +} Index: backend/src/main/java/finki/db/tasty_tabs/repository/PaymentsDailyChannelRepository.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/repository/PaymentsDailyChannelRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/repository/PaymentsDailyChannelRepository.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,20 @@ +package finki.db.tasty_tabs.repository; + +import finki.db.tasty_tabs.entity.PaymentsDailyChannel; +import finki.db.tasty_tabs.entity.composite_keys.PaymentsDailyChannelId; +import org.springframework.data.jpa.repository.JpaRepository; +import org.springframework.data.jpa.repository.Query; +import org.springframework.stereotype.Repository; + +import java.time.LocalDate; +import java.util.List; +@Repository +public interface PaymentsDailyChannelRepository + extends JpaRepository { + + @Query("select p from PaymentsDailyChannel p " + + "where p.day between :from and :to " + + "and (:channel is null or p.channel = :channel) " + + "order by p.day asc, p.channel asc") + List findRange(LocalDate from, LocalDate to, String channel); +} Index: backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsExtraService.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsExtraService.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsExtraService.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,17 @@ +package finki.db.tasty_tabs.service; + +import finki.db.tasty_tabs.web.dto.*; + +import java.time.LocalDate; +import java.util.List; +import java.util.Map; + +public interface AnalyticsExtraService { + List serverPerformanceLast3Months(); + List monthlyRevenueVsLabor(); + List dailyOpsSummary(int daysBack); + List topProducts(int daysBack, int limit); + List> revenueByShiftPeriodView(); + List revenueSplit(LocalDate from, LocalDate to); + List managersShiftsAboveMonthlyAvgCurrentYear(); +} Index: backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsService.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsService.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/service/AnalyticsService.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,9 @@ +package finki.db.tasty_tabs.service; + +import finki.db.tasty_tabs.web.dto.AnalyticsByChannelResponse; + +import java.time.LocalDate; + +public interface AnalyticsService { + AnalyticsByChannelResponse getPaymentsDailyChannel(LocalDate from, LocalDate to); +} Index: backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsExtraServiceImpl.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsExtraServiceImpl.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsExtraServiceImpl.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,42 @@ +package finki.db.tasty_tabs.service.impl; + +import finki.db.tasty_tabs.repository.AnalyticsReadRepository; +import finki.db.tasty_tabs.service.AnalyticsExtraService; +import finki.db.tasty_tabs.web.dto.*; +import lombok.RequiredArgsConstructor; +import org.springframework.stereotype.Service; + +import java.time.LocalDate; +import java.util.List; +import java.util.Map; + +@Service +@RequiredArgsConstructor +public class AnalyticsExtraServiceImpl implements AnalyticsExtraService { + + private final AnalyticsReadRepository repo; + + @Override public List serverPerformanceLast3Months() { + return repo.serverPerformanceLast3Months(); + } + @Override public List monthlyRevenueVsLabor() { + return repo.monthlyRevenueVsLabor(); + } + @Override public List dailyOpsSummary(int daysBack) { + return repo.dailyOpsSummary(daysBack <= 0 ? 30 : daysBack); + } + @Override public List topProducts(int daysBack, int limit) { + return repo.topProducts(daysBack <= 0 ? 90 : daysBack, limit <= 0 ? 10 : limit); + } + @Override public List> revenueByShiftPeriodView() { + return repo.revenueByShiftPeriodView(); + } + @Override public List revenueSplit(LocalDate from, LocalDate to) { + if (from == null) from = LocalDate.now().minusDays(30); + if (to == null) to = LocalDate.now(); + return repo.revenueSplit(from, to); + } + @Override public List managersShiftsAboveMonthlyAvgCurrentYear() { + return repo.managersShiftsAboveMonthlyAvgCurrentYear(); + } +} Index: backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsServiceImpl.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsServiceImpl.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/service/impl/AnalyticsServiceImpl.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,93 @@ +package finki.db.tasty_tabs.service.impl; + + +import finki.db.tasty_tabs.entity.PaymentsDailyChannel; +import finki.db.tasty_tabs.repository.AnalyticsAdminRepository; +import finki.db.tasty_tabs.repository.PaymentsDailyChannelRepository; +import finki.db.tasty_tabs.service.AnalyticsService; +import finki.db.tasty_tabs.web.dto.AnalyticsByChannelResponse; +import finki.db.tasty_tabs.web.dto.ChannelTableDto; +import finki.db.tasty_tabs.web.dto.PaymentsDailyChannelDto; +import lombok.RequiredArgsConstructor; +import org.springframework.stereotype.Service; + +import java.math.BigDecimal; +import java.time.LocalDate; +import java.util.Comparator; +import java.util.List; +import java.util.Map; +import java.util.stream.Collectors; + +@Service +public class AnalyticsServiceImpl implements AnalyticsService { + + private final PaymentsDailyChannelRepository mvRepo; + private final AnalyticsAdminRepository adminRepo; + + public AnalyticsServiceImpl(PaymentsDailyChannelRepository mvRepo, AnalyticsAdminRepository adminRepo) { + this.mvRepo = mvRepo; + this.adminRepo = adminRepo; + } + + @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 rows = mvRepo.findRange(from, to, null); + + // group by channel + Map> byChannel = rows.stream() + .collect(Collectors.groupingBy(PaymentsDailyChannel::getChannel)); + + // build per-channel tables + List 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); + }} Index: backend/src/main/java/finki/db/tasty_tabs/service/impl/PaymentServiceImpl.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/impl/PaymentServiceImpl.java (revision 2a99fe90dafc1b06403172fdd3ce230af8c5d345) +++ backend/src/main/java/finki/db/tasty_tabs/service/impl/PaymentServiceImpl.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -9,4 +9,5 @@ import finki.db.tasty_tabs.repository.PaymentRepository; import finki.db.tasty_tabs.service.PaymentService; +import finki.db.tasty_tabs.service.viewRefreshers.MvRefresher; import finki.db.tasty_tabs.web.dto.CreatePaymentDto; import lombok.RequiredArgsConstructor; @@ -23,4 +24,6 @@ private final PaymentRepository paymentRepository; private final OrderRepository orderRepository; + private final MvRefresher mvRefresher; + @Override @@ -34,8 +37,13 @@ payment.setTipAmount(dto.tipAmount()); payment.setPaymentType(dto.paymentType()); - payment.setTimestamp(LocalDateTime.now()); + payment.setTimestamp(LocalDateTime.now()); // ensure mapped to created_at column payment.setOrder(order); - return paymentRepository.save(payment); + Payment saved = paymentRepository.save(payment); + + // Schedule MV refresh after the transaction commits + mvRefresher.refreshPaymentsMvAfterCommit(); + + return saved; } Index: backend/src/main/java/finki/db/tasty_tabs/service/viewRefreshers/MvRefresher.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/service/viewRefreshers/MvRefresher.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/service/viewRefreshers/MvRefresher.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,27 @@ +package finki.db.tasty_tabs.service.viewRefreshers; + +import finki.db.tasty_tabs.repository.AnalyticsAdminRepository; +import lombok.RequiredArgsConstructor; +import org.springframework.stereotype.Component; +import org.springframework.transaction.support.TransactionSynchronization; +import org.springframework.transaction.support.TransactionSynchronizationManager; + +@Component +@RequiredArgsConstructor +public class MvRefresher { + private final AnalyticsAdminRepository analyticsAdminRepository; + + public void refreshPaymentsMvAfterCommit() { + // If there is an active transaction, run after commit; otherwise run immediately. + if (TransactionSynchronizationManager.isSynchronizationActive()) { + TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronization() { + @Override + public void afterCommit() { + analyticsAdminRepository.refreshMvPaymentsDailyChannelConcurrently(); + } + }); + } else { + analyticsAdminRepository.refreshMvPaymentsDailyChannelConcurrently(); + } + } +} Index: backend/src/main/java/finki/db/tasty_tabs/web/controllers/AnalyticsController.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/controllers/AnalyticsController.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/controllers/AnalyticsController.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,79 @@ +package finki.db.tasty_tabs.web.controllers; +import finki.db.tasty_tabs.service.AnalyticsExtraService; +import finki.db.tasty_tabs.service.AnalyticsService; +import finki.db.tasty_tabs.web.dto.AnalyticsByChannelResponse; +import finki.db.tasty_tabs.web.dto.*; +import lombok.RequiredArgsConstructor; +import org.springframework.format.annotation.DateTimeFormat; +import org.springframework.web.bind.annotation.*; + +import java.time.LocalDate; +import java.util.List; +import java.util.Map; + +@RestController +@RequestMapping("/api/analytics") +@RequiredArgsConstructor +public class AnalyticsController { + + private final AnalyticsService analyticsService; + private final AnalyticsExtraService analyticsExtraService; + + // Existing MV endpoint + @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); + } + + // 1) Server performance & revenue ranking (last 3 months) + @GetMapping("/servers/performance") + public List serverPerformance() { + return analyticsExtraService.serverPerformanceLast3Months(); + } + + // 2) Monthly revenue vs labor cost + @GetMapping("/monthly-revenue-vs-labor") + public List monthlyRevenueVsLabor() { + return analyticsExtraService.monthlyRevenueVsLabor(); + } + + // 3) Daily operations summary (default 30 days) + @GetMapping("/daily-ops") + public List dailyOps(@RequestParam(defaultValue = "30") int days) { + return analyticsExtraService.dailyOpsSummary(days); + } + + // 4) Top products (defaults: last 90 days, top 10) + @GetMapping("/top-products") + public List topProducts( + @RequestParam(defaultValue = "90") int days, + @RequestParam(defaultValue = "10") int limit) { + return analyticsExtraService.topProducts(days, limit); + } + + // 5) Revenue by shift period (dynamic view -> generic rows) + @GetMapping("/revenue-by-shift-period") + public List> revenueByShiftPeriod() { + return analyticsExtraService.revenueByShiftPeriodView(); + } + + // 6) Revenue split (calls your SQL function) by order date + @GetMapping("/revenue-split") + public List revenueSplit( + @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate from, + @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate to + ) { + return analyticsExtraService.revenueSplit(from, to); + } + + // 7) Managers' shifts above monthly average (current year) + @GetMapping("/managers/shifts-above-avg") + public List shiftsAboveAvg() { + return analyticsExtraService.managersShiftsAboveMonthlyAvgCurrentYear(); + } +} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/AnalyticsByChannelResponse.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/AnalyticsByChannelResponse.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/AnalyticsByChannelResponse.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,14 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; +import java.time.LocalDate; +import java.util.List; + +public record AnalyticsByChannelResponse( + LocalDate from, + LocalDate to, + List channels, + Long grandTotalPaidOrders, + BigDecimal grandTotalRevenue, + BigDecimal grandTotalTips +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/ChannelTableDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/ChannelTableDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/ChannelTableDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,12 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; +import java.util.List; + +public record ChannelTableDto( + String channel, + List data, + Long totalPaidOrders, + BigDecimal totalRevenue, + BigDecimal totalTips +) {} 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 de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/DailyOpsDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,15 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; +import java.time.LocalDate; + +public record DailyOpsDto( + LocalDate operationDate, + Long totalReservations, + Long totalOrders, + Long uniqueCustomers, + Long activeEmployees, + BigDecimal dailyRevenue, + Boolean meetsConversionTarget, + String revenueCategory +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/ManagerShiftAboveAvgDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/ManagerShiftAboveAvgDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/ManagerShiftAboveAvgDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,14 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; +import java.time.LocalDate; + +public record ManagerShiftAboveAvgDto( + String period, // YYYY-MM + Long shiftId, + LocalDate shiftDate, + String managerEmail, + BigDecimal shiftRevenue, + BigDecimal avgRevenuePerShift, + BigDecimal aboveBy +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/MonthlyRevenueVsLaborDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/MonthlyRevenueVsLaborDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/MonthlyRevenueVsLaborDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,10 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; + +public record MonthlyRevenueVsLaborDto( + String period, // YYYY-MM + BigDecimal totalRevenue, + BigDecimal totalLaborCost, + BigDecimal laborAsPercentOfRevenue +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/PaymentsDailyChannelDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/PaymentsDailyChannelDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/PaymentsDailyChannelDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,12 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; +import java.time.LocalDate; + +public record PaymentsDailyChannelDto( + LocalDate day, + String channel, + Long paidOrdersCnt, + BigDecimal revenue, + BigDecimal tipTotal +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/RevenueSplitDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/RevenueSplitDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/RevenueSplitDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,9 @@ +package finki.db.tasty_tabs.web.dto; + + +import java.math.BigDecimal; + +public record RevenueSplitDto( + String orderType, // Online Orders | Tab Orders + BigDecimal totalRevenue +) {} 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 de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/ServerPerformanceDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,16 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; + +public record ServerPerformanceDto( + String serverEmail, + String phoneNumber, + Long totalAssignments, + Long daysWorked, + Long ordersProcessed, + BigDecimal totalRevenueGenerated, + Integer revenueRank, + Integer ordersRank, + BigDecimal avgOrdersPerShift, + BigDecimal avgOrderValue +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/dto/TopProductDto.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/dto/TopProductDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) +++ backend/src/main/java/finki/db/tasty_tabs/web/dto/TopProductDto.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -0,0 +1,12 @@ +package finki.db.tasty_tabs.web.dto; + +import java.math.BigDecimal; + +public record TopProductDto( + Long productId, + String productName, + String categoryName, + Long totalQuantitySold, + BigDecimal totalRevenue, + BigDecimal revenueSharePercent +) {} Index: backend/src/main/java/finki/db/tasty_tabs/web/security/PublicUrlProvider.java =================================================================== --- backend/src/main/java/finki/db/tasty_tabs/web/security/PublicUrlProvider.java (revision 2a99fe90dafc1b06403172fdd3ce230af8c5d345) +++ backend/src/main/java/finki/db/tasty_tabs/web/security/PublicUrlProvider.java (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -10,6 +10,5 @@ private static final List PUBLIC_PATHS = List.of( "/swagger-ui/**", - "/v3/api-docs/**", - "/api/categories/**" + "/v3/api-docs/**" ); Index: backend/src/main/resources/application.properties =================================================================== --- backend/src/main/resources/application.properties (revision 2a99fe90dafc1b06403172fdd3ce230af8c5d345) +++ backend/src/main/resources/application.properties (revision de7a44e8bbc4f0a5bf45334d2c960128c0ba76e0) @@ -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=test +#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