Changes between Version 1 and Version 2 of Advanced Application Development

Timestamp:

09/06/25 18:32:09 (7 weeks ago)

Author:

221531

Comment:

--

Advanced Application Development

@@ +1 @@
+= Report Tables
 == Main Restaurant Report Summary Table
 CREATE TABLE restaurant_weekly_report (
@@ -34 +35 @@
     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);