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)

);