Views for Data Abstraction

-- Simple view for customer order summaries
CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS full_name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(AVG(o.total_amount), 0) AS avg_order_value,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Materialized view for expensive aggregation
CREATE MATERIALIZED VIEW monthly_sales_report AS
SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    p.category_id,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    COUNT(o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.order_date), p.category_id
WITH DATA;

-- Create index on materialized view
CREATE INDEX idx_msr_month ON monthly_sales_report (month);

-- Refresh materialized view concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_report;