Changes between Initial Version and Version 1 of Advanced Application Development

Timestamp:

09/06/25 18:25:53 (3 days ago)

Author:

221531

Comment:

--

Advanced Application Development

@@ +1 @@
+== Main Restaurant Report Summary Table
+CREATE TABLE restaurant_weekly_report (
+    report_id SERIAL PRIMARY KEY,
+    restaurant_id INT NOT NULL,
+    report_week DATE NOT NULL, -- Monday of the week
+    week_number INT NOT NULL, -- Week 1, 2, or 3 (most recent)
+
+    -- Order Statistics
+    total_orders INTEGER DEFAULT 0,
+    completed_orders INTEGER DEFAULT 0,
+    cancelled_orders INTEGER DEFAULT 0,
+
+    -- Financial Metrics
+    gross_revenue DECIMAL(12,2) DEFAULT 0.00,
+    net_revenue DECIMAL(12,2) DEFAULT 0.00, -- After platform commission
+    average_order_value DECIMAL(10,2) DEFAULT 0.00,
+    total_items_sold INTEGER DEFAULT 0,
+
+    -- Operational Metrics
+    avg_preparation_time_minutes DECIMAL(8,2) DEFAULT 0.00,
+    peak_hour INTEGER, -- Hour with most orders
+    busiest_day INTEGER, -- Day of week (0=Sunday, 6=Saturday)
+
+    -- Performance Indicators
+    completion_rate DECIMAL(5,2) DEFAULT 0.00, -- % of orders completed
+    customer_satisfaction DECIMAL(3,2) DEFAULT 0.00, -- Placeholder for ratings
+    orders_per_hour DECIMAL(8,2) DEFAULT 0.00,
+
+    -- Timestamps
+    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+
+    CONSTRAINT fk_weekly_report_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
+    CONSTRAINT uq_restaurant_week UNIQUE (restaurant_id, report_week)
+);