-- 1. Create the materialized view table
CREATE TABLE customer_summary (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    total_orders INT,
    total_amount DECIMAL(10,2),
    last_refresh TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Populate the table initially
INSERT INTO customer_summary (customer_id, customer_name, total_orders, total_amount)
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name;

-- 3. Create an event to refresh the materialized view every hour
CREATE EVENT refresh_customer_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    TRUNCATE TABLE customer_order_summary;

    INSERT INTO customer_summary (customer_id, customer_name, total_orders, total_amount)
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS total_orders,
        SUM(o.amount) AS total_amount
    FROM 
        customers c
    JOIN 
        orders o ON c.customer_id = o.customer_id
    GROUP BY 
        c.customer_id, c.customer_name;
END;