Report Tables

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)

);

Popular Items Report Table

CREATE TABLE restaurant_popular_items (

report_id SERIAL PRIMARY KEY, restaurant_id INT NOT NULL, report_week DATE NOT NULL, item_id INT NOT NULL, item_name VARCHAR(100),

times_ordered INTEGER DEFAULT 0, total_quantity INTEGER DEFAULT 0, total_revenue DECIMAL(10,2) DEFAULT 0.00, avg_price DECIMAL(8,2) DEFAULT 0.00, popularity_rank INTEGER,

generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_popular_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id), CONSTRAINT fk_popular_items_item FOREIGN KEY (item_id) REFERENCES item(item_id)

);

Daily Breakdown Table

CREATE TABLE restaurant_daily_breakdown (

breakdown_id SERIAL PRIMARY KEY, restaurant_id INT NOT NULL, report_date DATE NOT NULL, day_of_week INTEGER, -- 0=Sunday, 6=Saturday

daily_orders INTEGER DEFAULT 0, daily_revenue DECIMAL(10,2) DEFAULT 0.00, daily_items_sold INTEGER DEFAULT 0, avg_order_value DECIMAL(8,2) DEFAULT 0.00,

-- Hourly breakdown (JSON format for flexibility) hourly_orders JSONB,

generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_daily_breakdown_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id), CONSTRAINT uq_restaurant_daily UNIQUE (restaurant_id, report_date)

);

Comparative Analysis Table (Week Over Week)

CREATE TABLE restaurant_trend_analysis (

trend_id SERIAL PRIMARY KEY, restaurant_id INT NOT NULL, analysis_date DATE DEFAULT CURRENT_DATE,

-- 3-week trends week1_revenue DECIMAL(10,2), -- Most recent week week2_revenue DECIMAL(10,2), week3_revenue DECIMAL(10,2),

week1_orders INTEGER, week2_orders INTEGER, week3_orders INTEGER,

-- Trend indicators revenue_trend VARCHAR(20), -- 'INCREASING', 'DECREASING', 'STABLE' order_trend VARCHAR(20),

-- Growth rates revenue_growth_rate DECIMAL(8,2), -- Week over week % order_growth_rate DECIMAL(8,2),

-- Performance alerts performance_alerts JSONB, recommendations JSONB,

generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_trend_analysis_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id)

);

Performance Metrics Table

CREATE TABLE performance_metrics (

metric_id SERIAL PRIMARY KEY, restaurant_id INT NOT NULL, metric_type VARCHAR(50), metric_value DECIMAL(8,2), recorded_at TIMESTAMP, CONSTRAINT fk_performance_metrics_restaurant FOREIGN KEY (restaurant_id) REFERENCES RESTAURANT(restaurant_id)

);

Audit Table for Orders

CREATE TABLE audit_orders (

audit_id SERIAL PRIMARY KEY, order_id INT, operation VARCHAR(50), new_status VARCHAR(50), changed_by VARCHAR(50), change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Utility Functions

Get Week Start Function

CREATE OR REPLACE FUNCTION get_week_start(input_date DATE)

RETURNS DATE AS $$

BEGIN

RETURN input_date - INTERVAL '1 day' * EXTRACT(DOW FROM input_date);

END; $$ LANGUAGE plpgsql;

Get Week Number Function

CREATE OR REPLACE FUNCTION get_week_number(input_date DATE)

RETURNS INTEGER AS $$

DECLARE

week_start DATE; current_week_start DATE;

BEGIN

week_start := get_week_start(input_date); current_week_start := get_week_start(CURRENT_DATE);

RETURN CASE

WHEN week_start = current_week_start THEN 1 WHEN week_start = current_week_start - INTERVAL '7 days' THEN 2 WHEN week_start = current_week_start - INTERVAL '14 days' THEN 3 ELSE 0 -- Outside 3-week window

END;

END; $$ LANGUAGE plpgsql;

Main Report Generation Function

CREATE OR REPLACE FUNCTION generate_restaurant_weekly_report(p_restaurant_id INT, p_target_date DATE DEFAULT CURRENT_DATE)

RETURNS void AS $$

DECLARE

week_start DATE; week_end DATE; week_num INTEGER;

-- Variables for calculations v_total_orders INTEGER; v_completed_orders INTEGER; v_cancelled_orders INTEGER; v_gross_revenue DECIMAL(12,2); v_net_revenue DECIMAL(12,2); v_avg_order_value DECIMAL(10,2); v_total_items_sold INTEGER; v_avg_prep_time DECIMAL(8,2); v_peak_hour INTEGER; v_busiest_day INTEGER; v_completion_rate DECIMAL(5,2); v_orders_per_hour DECIMAL(8,2);

commission_rate DECIMAL(5,4) := 0.15; -- 15% platform fee

BEGIN

-- Calculate week boundaries week_start := get_week_start(p_target_date); week_end := week_start + INTERVAL '6 days'; week_num := get_week_number(p_target_date);

-- Skip if outside 3-week window IF week_num = 0 THEN

RETURN;

END IF;

-- Calculate order statistics SELECT

COUNT(*), COUNT(*) FILTER (WHERE order_status = 'delivered'), COUNT(*) FILTER (WHERE order_status = 'cancelled')

INTO v_total_orders, v_completed_orders, v_cancelled_orders FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date BETWEEN week_start AND week_end;

-- Calculate financial metrics SELECT

COALESCE(SUM(total_amount), 0), COALESCE(SUM(total_amount * (1 - commission_rate)), 0), COALESCE(AVG(total_amount), 0)

INTO v_gross_revenue, v_net_revenue, v_avg_order_value FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date BETWEEN week_start AND week_end AND order_status = 'delivered';

-- Calculate total items sold SELECT COALESCE(SUM(oi.quantity), 0) INTO v_total_items_sold FROM order_items oi

JOIN orders o ON oi.order_id = o.order_id

WHERE o.restaurant_id = p_restaurant_id

AND o.order_date::date BETWEEN week_start AND week_end AND o.order_status = 'delivered';

-- Calculate average preparation time (using performance metrics if available) SELECT COALESCE(AVG(metric_value), 0) INTO v_avg_prep_time FROM performance_metrics pm WHERE pm.restaurant_id = p_restaurant_id

AND pm.metric_type = 'prep_time_minutes' AND pm.recorded_at::date BETWEEN week_start AND week_end;

-- Find peak hour SELECT EXTRACT(hour FROM order_date) INTO v_peak_hour FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date BETWEEN week_start AND week_end

GROUP BY EXTRACT(hour FROM order_date) ORDER BY COUNT(*) DESC LIMIT 1;

-- Find busiest day of week SELECT EXTRACT(dow FROM order_date) INTO v_busiest_day FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date BETWEEN week_start AND week_end

GROUP BY EXTRACT(dow FROM order_date) ORDER BY COUNT(*) DESC LIMIT 1;

-- Calculate completion rate v_completion_rate := CASE

WHEN v_total_orders > 0 THEN

ROUND((v_completed_orders::DECIMAL / v_total_orders::DECIMAL) * 100, 2)

ELSE 0

END;

-- Calculate orders per hour (during operating hours) WITH operating_hours AS (

SELECT

EXTRACT(EPOCH FROM (closing_time - opening_time))/3600 * 7 as total_hours_week

FROM restaurant WHERE restaurant_id = p_restaurant_id

) SELECT CASE

WHEN oh.total_hours_week > 0 THEN v_total_orders::DECIMAL / oh.total_hours_week ELSE 0

END INTO v_orders_per_hour FROM operating_hours oh;

-- Insert or update the weekly report INSERT INTO restaurant_weekly_report (

restaurant_id, report_week, week_number, total_orders, completed_orders, cancelled_orders, gross_revenue, net_revenue, average_order_value, total_items_sold, avg_preparation_time_minutes, peak_hour, busiest_day, completion_rate, orders_per_hour, last_updated

) VALUES (

p_restaurant_id, week_start, week_num, v_total_orders, v_completed_orders, v_cancelled_orders, v_gross_revenue, v_net_revenue, v_avg_order_value, v_total_items_sold, v_avg_prep_time, v_peak_hour, v_busiest_day, v_completion_rate, v_orders_per_hour, CURRENT_TIMESTAMP

) ON CONFLICT (restaurant_id, report_week) DO UPDATE SET

total_orders = EXCLUDED.total_orders, completed_orders = EXCLUDED.completed_orders, cancelled_orders = EXCLUDED.cancelled_orders, gross_revenue = EXCLUDED.gross_revenue, net_revenue = EXCLUDED.net_revenue, average_order_value = EXCLUDED.average_order_value, total_items_sold = EXCLUDED.total_items_sold, avg_preparation_time_minutes = EXCLUDED.avg_preparation_time_minutes, peak_hour = EXCLUDED.peak_hour, busiest_day = EXCLUDED.busiest_day, completion_rate = EXCLUDED.completion_rate, orders_per_hour = EXCLUDED.orders_per_hour, last_updated = CURRENT_TIMESTAMP;

-- Generate popular items report PERFORM generate_popular_items_report(p_restaurant_id, week_start);

-- Generate daily breakdown PERFORM generate_daily_breakdown_report(p_restaurant_id, week_start, week_end);

END; $$ LANGUAGE plpgsql;

Popular Items Report Function

CREATE OR REPLACE FUNCTION generate_popular_items_report(p_restaurant_id INT, p_week_start DATE)

RETURNS void AS $$

BEGIN

-- Clear existing data for this week DELETE FROM restaurant_popular_items WHERE restaurant_id = p_restaurant_id AND report_week = p_week_start;

-- Insert top 10 popular items for the week INSERT INTO restaurant_popular_items (

restaurant_id, report_week, item_id, item_name, times_ordered, total_quantity, total_revenue, avg_price, popularity_rank

) WITH popular_items AS (

SELECT

i.item_id, i.name as item_name, COUNT(DISTINCT o.order_id) as times_ordered, SUM(oi.quantity) as total_quantity, SUM(oi.total_price) as total_revenue, AVG(oi.total_price / oi.quantity) as avg_price, ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT o.order_id) DESC) as popularity_rank

FROM orders o

JOIN order_items oi ON o.order_id = oi.order_id JOIN item i ON oi.item_id = i.item_id

WHERE o.restaurant_id = p_restaurant_id

AND o.order_date::date BETWEEN p_week_start AND p_week_start + INTERVAL '6 days' AND o.order_status = 'delivered'

GROUP BY i.item_id, i.name ORDER BY times_ordered DESC LIMIT 10

) SELECT

p_restaurant_id, p_week_start, item_id, item_name, times_ordered, total_quantity, total_revenue, avg_price, popularity_rank

FROM popular_items;

END; $$ LANGUAGE plpgsql;

Trend Analysis Function

CREATE OR REPLACE FUNCTION generate_trend_analysis(p_restaurant_id INT)

RETURNS void AS $$

DECLARE

week1_start DATE := get_week_start(CURRENT_DATE); week2_start DATE := week1_start - INTERVAL '7 days'; week3_start DATE := week1_start - INTERVAL '14 days';

w1_revenue DECIMAL(10,2); w2_revenue DECIMAL(10,2); w3_revenue DECIMAL(10,2); w1_orders INTEGER; w2_orders INTEGER; w3_orders INTEGER;

revenue_trend_val VARCHAR(20); order_trend_val VARCHAR(20); revenue_growth DECIMAL(8,2); order_growth DECIMAL(8,2);

alerts JSONB := '[]'::jsonb; recommendations JSONB := '[]'::jsonb;

BEGIN

-- Get weekly data SELECT gross_revenue, total_orders INTO w1_revenue, w1_orders FROM restaurant_weekly_report WHERE restaurant_id = p_restaurant_id AND report_week = week1_start;

SELECT gross_revenue, total_orders INTO w2_revenue, w2_orders FROM restaurant_weekly_report WHERE restaurant_id = p_restaurant_id AND report_week = week2_start;

SELECT gross_revenue, total_orders INTO w3_revenue, w3_orders FROM restaurant_weekly_report WHERE restaurant_id = p_restaurant_id AND report_week = week3_start;

-- Calculate trends IF w2_revenue > 0 THEN

revenue_growth := ROUND(((w1_revenue - w2_revenue) / w2_revenue) * 100, 2);

ELSE

revenue_growth := 0;

END IF;

IF w2_orders > 0 THEN

order_growth := ROUND(((w1_orders - w2_orders)::DECIMAL / w2_orders::DECIMAL) * 100, 2);

ELSE

order_growth := 0;

END IF;

-- Determine trend direction revenue_trend_val := CASE

WHEN revenue_growth > 5 THEN 'INCREASING' WHEN revenue_growth < -5 THEN 'DECREASING' ELSE 'STABLE'

END;

order_trend_val := CASE

WHEN order_growth > 5 THEN 'INCREASING' WHEN order_growth < -5 THEN 'DECREASING' ELSE 'STABLE'

END;

-- Generate alerts IF revenue_growth < -20 THEN

alerts := alerts

'{"type": "revenue_decline", "severity": "high", "message": "Revenue declined by more than 20% this week"}'::jsonb;

END IF;

IF order_growth < -15 THEN

alerts := alerts

'{"type": "order_decline", "severity": "medium", "message": "Order volume declined by more than 15% this week"}'::jsonb;

END IF;

-- Generate recommendations IF revenue_trend_val = 'DECREASING' THEN

recommendations := recommendations

'{"type": "marketing", "action": "Consider promotional campaigns or menu updates"}'::jsonb;

END IF;

IF order_trend_val = 'DECREASING' THEN

recommendations := recommendations

'{"type": "operations", "action": "Review menu pricing and delivery times"}'::jsonb;

END IF;

-- Insert trend analysis INSERT INTO restaurant_trend_analysis (

restaurant_id, week1_revenue, week2_revenue, week3_revenue, week1_orders, week2_orders, week3_orders, revenue_trend, order_trend, revenue_growth_rate, order_growth_rate, performance_alerts, recommendations

) VALUES (

p_restaurant_id, w1_revenue, w2_revenue, w3_revenue, w1_orders, w2_orders, w3_orders, revenue_trend_val, order_trend_val, revenue_growth, order_growth, alerts, recommendations

) ON CONFLICT (restaurant_id) DO UPDATE SET

week1_revenue = EXCLUDED.week1_revenue, week2_revenue = EXCLUDED.week2_revenue, week3_revenue = EXCLUDED.week3_revenue, week1_orders = EXCLUDED.week1_orders, week2_orders = EXCLUDED.week2_orders, week3_orders = EXCLUDED.week3_orders, revenue_trend = EXCLUDED.revenue_trend, order_trend = EXCLUDED.order_trend, revenue_growth_rate = EXCLUDED.revenue_growth_rate, order_growth_rate = EXCLUDED.order_growth_rate, performance_alerts = EXCLUDED.performance_alerts, recommendations = EXCLUDED.recommendations, generated_at = CURRENT_TIMESTAMP;

END; $$ LANGUAGE plpgsql;

Trigger Functions

Update Restaurant Reports Trigger Function

CREATE OR REPLACE FUNCTION update_restaurant_reports()

RETURNS TRIGGER AS $$

DECLARE

affected_restaurant_id INT; order_date_to_check DATE;

BEGIN

-- Determine which restaurant and date to update affected_restaurant_id := COALESCE(NEW.restaurant_id, OLD.restaurant_id); order_date_to_check := COALESCE(NEW.order_date::date, OLD.order_date::date);

-- Only update if the order is within the last 3 weeks IF order_date_to_check >= CURRENT_DATE - INTERVAL '21 days' THEN

-- Update reports for the affected week PERFORM generate_restaurant_weekly_report(affected_restaurant_id, order_date_to_check);

-- Update trend analysis PERFORM generate_trend_analysis(affected_restaurant_id);

-- Log the report update INSERT INTO audit_orders (order_id, operation, new_status, changed_by) VALUES (

COALESCE(NEW.order_id, OLD.order_id), 'REPORT_UPDATE', 'Restaurant report updated', 'system'

);

END IF;

RETURN COALESCE(NEW, OLD);

END; $$ LANGUAGE plpgsql;

Update Reports on Items Change Trigger Function

CREATE OR REPLACE FUNCTION update_reports_on_items_change()

RETURNS TRIGGER AS $$

DECLARE

affected_restaurant_id INT; order_date_to_check DATE;

BEGIN

-- Get restaurant and date from the order SELECT restaurant_id, order_date::date INTO affected_restaurant_id, order_date_to_check FROM orders WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);

-- Only update if within the last 3 weeks IF order_date_to_check >= CURRENT_DATE - INTERVAL '21 days' THEN

PERFORM generate_restaurant_weekly_report(affected_restaurant_id, order_date_to_check); PERFORM generate_trend_analysis(affected_restaurant_id);

END IF;

RETURN COALESCE(NEW, OLD);

END; $$ LANGUAGE plpgsql;

Create Triggers

-- Trigger on orders table CREATE TRIGGER trg_update_restaurant_reports

AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW

EXECUTE FUNCTION update_restaurant_reports();

-- Trigger on order_items table CREATE TRIGGER trg_update_reports_items

AFTER INSERT OR UPDATE OR DELETE ON order_items FOR EACH ROW

EXECUTE FUNCTION update_reports_on_items_change();

Report Views for Easy Access

Comprehensive 3-Week Restaurant Report View CREATE VIEW restaurant_3week_summary AS SELECT

r.name as restaurant_name, rwr.restaurant_id,

-- Current week (Week 1) MAX(CASE WHEN rwr.week_number = 1 THEN rwr.total_orders END) as current_week_orders, MAX(CASE WHEN rwr.week_number = 1 THEN rwr.gross_revenue END) as current_week_revenue, MAX(CASE WHEN rwr.week_number = 1 THEN rwr.completion_rate END) as current_week_completion_rate,

-- Previous week (Week 2) MAX(CASE WHEN rwr.week_number = 2 THEN rwr.total_orders END) as prev_week_orders, MAX(CASE WHEN rwr.week_number = 2 THEN rwr.gross_revenue END) as prev_week_revenue,

-- 3 weeks ago (Week 3) MAX(CASE WHEN rwr.week_number = 3 THEN rwr.total_orders END) as week3_orders, MAX(CASE WHEN rwr.week_number = 3 THEN rwr.gross_revenue END) as week3_revenue,

-- Totals SUM(rwr.total_orders) as total_orders_3weeks, SUM(rwr.gross_revenue) as total_revenue_3weeks, AVG(rwr.average_order_value) as avg_order_value_3weeks, AVG(rwr.completion_rate) as avg_completion_rate,

-- Operational insights MAX(CASE WHEN rwr.week_number = 1 THEN rwr.peak_hour END) as current_peak_hour, MAX(CASE WHEN rwr.week_number = 1 THEN rwr.busiest_day END) as current_busiest_day, AVG(rwr.avg_preparation_time_minutes) as avg_prep_time,

MAX(rwr.last_updated) as last_updated

FROM restaurant_weekly_report rwr

JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id

WHERE rwr.report_week >= CURRENT_DATE - INTERVAL '21 days' GROUP BY r.name, rwr.restaurant_id;

Top Performing Restaurants View

CREATE VIEW top_restaurants_3weeks AS SELECT

r.name as restaurant_name, SUM(rwr.gross_revenue) as total_revenue, SUM(rwr.total_orders) as total_orders, AVG(rwr.completion_rate) as avg_completion_rate, AVG(rwr.average_order_value) as avg_order_value, rta.revenue_trend, rta.revenue_growth_rate

FROM restaurant_weekly_report rwr

JOIN restaurant r ON rwr.restaurant_id = r.restaurant_id LEFT JOIN restaurant_trend_analysis rta ON rwr.restaurant_id = rta.restaurant_id

WHERE rwr.report_week >= CURRENT_DATE - INTERVAL '21 days' GROUP BY r.name, rta.revenue_trend, rta.revenue_growth_rate ORDER BY total_revenue DESC;

Daily Reports Function

CREATE OR REPLACE FUNCTION generate_daily_breakdown_report(

p_restaurant_id INT, p_week_start DATE, p_week_end DATE

) RETURNS void

LANGUAGE plpgsql

AS $$ DECLARE

v_day DATE := p_week_start; v_daily_orders INTEGER := 0; v_daily_revenue NUMERIC(12,2) := 0; v_daily_items_sold INTEGER := 0; v_avg_order_value NUMERIC(12,2) := 0; v_hourly_orders JSONB := '{}'::jsonb;

BEGIN

IF p_week_end < p_week_start THEN

RAISE EXCEPTION 'p_week_end (%) must be >= p_week_start (%)', p_week_end, p_week_start;

END IF;

DELETE FROM restaurant_daily_breakdown WHERE restaurant_id = p_restaurant_id

AND report_date BETWEEN p_week_start AND p_week_end;

WHILE v_day <= p_week_end LOOP

-- Daily metrics SELECT

COUNT(*), COALESCE(SUM(total_amount), 0)::NUMERIC(12,2), COALESCE(AVG(total_amount), 0)::NUMERIC(12,2)

INTO v_daily_orders, v_daily_revenue, v_avg_order_value FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date = v_day AND order_status = 'delivered';

-- Daily items sold SELECT COALESCE(SUM(oi.quantity), 0) INTO v_daily_items_sold FROM order_items oi

JOIN orders o ON oi.order_id = o.order_id

WHERE o.restaurant_id = p_restaurant_id

AND o.order_date::date = v_day AND o.order_status = 'delivered';

-- Hourly order breakdown (as JSONB) SELECT COALESCE(jsonb_object_agg(order_hour::text, order_count), '{}'::jsonb) INTO v_hourly_orders FROM (

SELECT

EXTRACT(hour FROM order_date) AS order_hour, COUNT(*) AS order_count

FROM orders WHERE restaurant_id = p_restaurant_id

AND order_date::date = v_day AND order_status = 'delivered'

GROUP BY 1

) AS hourly_counts;

-- Insert daily breakdown INSERT INTO restaurant_daily_breakdown (

restaurant_id, report_date, day_of_week, daily_orders, daily_revenue, daily_items_sold, avg_order_value, hourly_orders

) VALUES (

p_restaurant_id, v_day, EXTRACT(DOW FROM v_day), v_daily_orders, v_daily_revenue, v_daily_items_sold, v_avg_order_value, v_hourly_orders

);

-- Next day v_day := v_day + 1; -- date + integer yields date

END LOOP;

END; $$;

Constraint Addition

ALTER TABLE restaurant_trend_analysis

ADD CONSTRAINT uq_restaurant_id UNIQUE (restaurant_id);