CREATE OR REPLACE FUNCTION update_store_rating()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_num INT;
    v_store_id INT;
BEGIN
    -- Determine which order was affected
    IF TG_OP = 'DELETE' THEN
        v_order_num := OLD.order_num;
    ELSE
        v_order_num := NEW.order_num;
    END IF;

    -- Find the store associated with the order
    SELECT fs.store_id
    INTO v_store_id
    FROM for_store fs
    JOIN "order" o
        ON o.order_num = v_order_num
    WHERE fs.request_num IS NOT NULL
    LIMIT 1;

    -- Update the store rating
    IF v_store_id IS NOT NULL THEN
        UPDATE store s
        SET rating = (
            SELECT COALESCE(AVG(r.rating), 0)
            FROM review r
            JOIN "order" o
                ON o.order_num = r.order_num
            -- The exact relationship between orders and stores
            -- should be used here according to the final schema
            WHERE o.order_num IN (
                SELECT i.order_num
                FROM includes i
                JOIN sells sl
                    ON sl.code = i.code
                WHERE sl.store_id = v_store_id
            )
        )
        WHERE s.store_id = v_store_id;
    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_store_rating
AFTER INSERT OR UPDATE OR DELETE
ON review
FOR EACH ROW
EXECUTE FUNCTION update_store_rating();