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