v0.1 Operational Analytics (Trigger + pg_cron Staging)

-- 1. Staging table
CREATE TABLE orders_staging (
    order_id INT PRIMARY KEY,
    customer_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    order_date TIMESTAMP
);

-- 2. Columnstore table
CREATE TABLE orders_analytics (
    order_id INT,
    customer_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    order_date TIMESTAMP,
    last_updated TIMESTAMP
) USING columnstore;

-- 3. Periodic batch sync function scheduled via pg_cron
CREATE OR REPLACE FUNCTION sync_to_columnstore() RETURNS void AS $$
BEGIN
    INSERT INTO orders_analytics
    SELECT s.*, NOW() FROM orders_staging s;
    TRUNCATE orders_staging;
END;
$$ LANGUAGE plpgsql;