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

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

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

Function to get the Monday of a given week

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;

Function to get week number (1, 2, or 3 for last 3 weeks)

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 (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
    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;

    -- 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;

    -- 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 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 supporting reports
    PERFORM generate_popular_items_report(p_restaurant_id, week_start);
    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
    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;

    -- Growth calculations
    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;

    -- 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;

    -- 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;

    -- 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 or update
    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

Trigger function to update reports when orders change

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;

Trigger function for order items changes

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

-- 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;

    -- Clear previous data
    DELETE FROM restaurant_daily_breakdown
    WHERE restaurant_id = p_restaurant_id
      AND report_date BETWEEN p_week_start AND p_week_end;

    -- Loop through days
    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;
    END LOOP;
END;
$$;

ALTER TABLE restaurant_trend_analysis
ADD CONSTRAINT uq_restaurant_id UNIQUE (restaurant_id);