-- 基本视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;

-- 使用视图
SELECT * FROM active_users;

-- 带计算字段的视图
CREATE VIEW user_order_summary AS
SELECT
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 查询视图
SELECT * FROM user_order_summary
WHERE lifetime_value > 1000
ORDER BY lifetime_value DESC;

-- 1. 简化复杂查询
CREATE VIEW product_details AS
SELECT
    p.id,
    p.name,
    p.price,
    c.name AS category_name,
    b.name AS brand_name,
    p.stock,
    CASE
        WHEN p.stock = 0 THEN 'Out of Stock'
        WHEN p.stock < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id;

-- 简单查询
SELECT * FROM product_details WHERE stock_status = 'Low Stock';

-- 2. 数据安全(隐藏敏感信息)
CREATE VIEW public_user_info AS
SELECT id, username, email, created_at
FROM users;
-- 不包含password_hash等敏感字段

-- 3. 逻辑数据独立性
-- 即使底层表结构变化,视图可以保持不变

-- 可更新视图(满足以下条件):
-- 1. FROM只有一个表
-- 2. 没有DISTINCT, GROUP BY, HAVING
-- 3. 没有聚合函数, 窗口函数
-- 4. 没有UNION, INTERSECT, EXCEPT

CREATE VIEW active_products AS
SELECT id, name, price, stock
FROM products
WHERE is_active = TRUE;

-- 通过视图更新数据
UPDATE active_products SET price = 99.99 WHERE id = 1;
INSERT INTO active_products (name, price, stock) VALUES ('New Product', 49.99, 100);
DELETE FROM active_products WHERE id = 10;

-- 复杂视图不可直接更新
CREATE VIEW order_summary AS
SELECT
    o.id,
    u.username,
    o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id;

-- UPDATE order_summary SET total_amount = 100 WHERE id = 1;  -- 错误!

-- 防止更新导致行从视图中消失
CREATE VIEW expensive_products AS
SELECT * FROM products WHERE price > 100
WITH CHECK OPTION;

-- 这个更新会失败,因为违反了CHECK OPTION
-- UPDATE expensive_products SET price = 50 WHERE id = 1;  -- 错误!

-- LOCAL vs CASCADED
CREATE VIEW view1 AS
SELECT * FROM products WHERE price > 50;

CREATE VIEW view2 AS
SELECT * FROM view1 WHERE stock > 0
WITH LOCAL CHECK OPTION;  -- 只检查view2的条件

CREATE VIEW view3 AS
SELECT * FROM view1 WHERE stock > 0
WITH CASCADED CHECK OPTION;  -- 检查view1和view3的所有条件

-- CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, created_at, last_login
FROM users
WHERE is_active = TRUE;

-- 注意: 列数和类型必须匹配,只能添加新列到末尾

-- 删除视图
DROP VIEW active_users;

-- 如果不存在不报错
DROP VIEW IF EXISTS active_users;

-- 级联删除(删除依赖此视图的对象)
DROP VIEW active_users CASCADE;

-- 物化视图会实际存储查询结果
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(created_at) AS sale_date,
    COUNT(*) AS order_count,
    COUNT(DISTINCT user_id) AS unique_customers,
    SUM(total_amount) AS daily_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY DATE(created_at);

-- 创建索引(物化视图支持索引!)
CREATE INDEX idx_daily_sales_date ON daily_sales_summary(sale_date);

-- 查询物化视图(非常快)
SELECT * FROM daily_sales_summary
WHERE sale_date >= '2024-01-01'
ORDER BY sale_date DESC;

-- 完全刷新(重新计算所有数据)
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- 并发刷新(不阻塞查询,需要唯一索引)
CREATE UNIQUE INDEX uk_daily_sales_date ON daily_sales_summary(sale_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- 定时刷新(使用cron或pg_cron扩展)
-- 每天凌晨1点刷新
-- 0 1 * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW daily_sales_summary"

-- 1. 复杂报表查询
CREATE MATERIALIZED VIEW monthly_revenue_report AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    c.name AS category,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.price) AS revenue,
    COUNT(DISTINCT o.user_id) AS unique_customers
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY DATE_TRUNC('month', o.created_at), c.name;

CREATE INDEX idx_monthly_revenue ON monthly_revenue_report(month, category);

-- 2. 仪表板数据
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
    (SELECT COUNT(*) FROM users WHERE created_at >= CURRENT_DATE) AS new_users_today,
    (SELECT COUNT(*) FROM orders WHERE created_at >= CURRENT_DATE) AS orders_today,
    (SELECT SUM(total_amount) FROM orders WHERE created_at >= CURRENT_DATE) AS revenue_today,
    (SELECT COUNT(*) FROM users WHERE is_active = TRUE) AS active_users,
    (SELECT AVG(total_amount) FROM orders WHERE created_at >= CURRENT_DATE - 7) AS avg_order_value_7d;

-- 每小时刷新一次
-- 0 * * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_metrics"

-- 3. 预聚合大表
CREATE MATERIALIZED VIEW user_activity_summary AS
SELECT
    user_id,
    DATE(activity_time) AS activity_date,
    COUNT(*) AS activity_count,
    ARRAY_AGG(DISTINCT activity_type) AS activity_types
FROM user_activities
WHERE activity_time >= CURRENT_DATE - 90  -- 只保留90天数据
GROUP BY user_id, DATE(activity_time);

CREATE INDEX idx_user_activity ON user_activity_summary(user_id, activity_date);

-- 创建刷新函数
CREATE OR REPLACE FUNCTION refresh_daily_sales()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器(简化示例,生产环境需要防抖)
CREATE TRIGGER trigger_refresh_daily_sales
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_daily_sales();

-- 注意: 频繁刷新会影响性能,建议使用定时任务

-- 安装pg_cron扩展
CREATE EXTENSION pg_cron;

-- 每天凌晨2点刷新
SELECT cron.schedule('refresh-daily-sales', '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary');

-- 每小时刷新
SELECT cron.schedule('refresh-metrics', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_metrics');

-- 查看定时任务
SELECT * FROM cron.job;

-- 删除定时任务
SELECT cron.unschedule('refresh-daily-sales');

-- 产品销售排行
CREATE MATERIALIZED VIEW product_sales_ranking AS
SELECT
    p.id,
    p.name,
    p.category_id,
    c.name AS category_name,
    COUNT(DISTINCT oi.order_id) AS times_sold,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS total_revenue,
    AVG(oi.price) AS avg_selling_price,
    MAX(o.created_at) AS last_sold_at
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
LEFT JOIN categories c ON p.category_id = c.id
GROUP BY p.id, p.name, p.category_id, c.name;

CREATE INDEX idx_product_ranking_revenue ON product_sales_ranking(total_revenue DESC);
CREATE INDEX idx_product_ranking_category ON product_sales_ranking(category_id);

-- 用户行为分析
CREATE MATERIALIZED VIEW user_behavior_metrics AS
SELECT
    u.id,
    u.username,
    u.created_at AS signup_date,
    COUNT(DISTINCT o.id) AS total_orders,
    SUM(o.total_amount) AS lifetime_value,
    MIN(o.created_at) AS first_order_date,
    MAX(o.created_at) AS last_order_date,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(DISTINCT DATE(o.created_at)) AS active_days,
    CASE
        WHEN COUNT(o.id) = 0 THEN 'Never Ordered'
        WHEN COUNT(o.id) = 1 THEN 'One-time'
        WHEN COUNT(o.id) <= 5 THEN 'Occasional'
        WHEN COUNT(o.id) <= 20 THEN 'Regular'
        ELSE 'VIP'
    END AS customer_segment
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.created_at;

CREATE INDEX idx_user_metrics_segment ON user_behavior_metrics(customer_segment);
CREATE INDEX idx_user_metrics_ltv ON user_behavior_metrics(lifetime_value DESC);

-- 实时库存状态(普通视图)
CREATE VIEW inventory_status AS
SELECT
    p.id,
    p.sku,
    p.name,
    p.stock,
    p.reorder_point,
    CASE
        WHEN p.stock = 0 THEN 'OUT_OF_STOCK'
        WHEN p.stock <= p.reorder_point THEN 'REORDER_NEEDED'
        WHEN p.stock < p.reorder_point * 2 THEN 'LOW_STOCK'
        ELSE 'SUFFICIENT'
    END AS status,
    c.name AS category,
    -- 过去30天销售速度
    (SELECT COALESCE(SUM(oi.quantity), 0) / 30.0
     FROM order_items oi
     JOIN orders o ON oi.order_id = o.id
     WHERE oi.product_id = p.id
       AND o.created_at >= CURRENT_DATE - 30
    ) AS daily_sales_rate,
    -- 预计可用天数
    CASE
        WHEN (SELECT COALESCE(SUM(oi.quantity), 0) / 30.0
              FROM order_items oi
              JOIN orders o ON oi.order_id = o.id
              WHERE oi.product_id = p.id
                AND o.created_at >= CURRENT_DATE - 30) > 0
        THEN p.stock / (SELECT COALESCE(SUM(oi.quantity), 0) / 30.0
                        FROM order_items oi
                        JOIN orders o ON oi.order_id = o.id
                        WHERE oi.product_id = p.id
                          AND o.created_at >= CURRENT_DATE - 30)
        ELSE NULL
    END AS days_until_stockout
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.is_active = TRUE;

-- 查询需要补货的产品
SELECT * FROM inventory_status
WHERE status IN ('OUT_OF_STOCK', 'REORDER_NEEDED')
ORDER BY days_until_stockout NULLS LAST;

-- ✅ 好的使用场景:
-- - 简化复杂查询
-- - 提供数据安全层
-- - 逻辑数据独立性
-- - 不需要实时性的数据

-- ❌ 不适合场景:
-- - 需要频繁更新的聚合数据(用物化视图)
-- - 性能要求极高的查询(用物化视图或表)

-- ✅ 好的使用场景:
-- - 复杂聚合查询
-- - 报表和仪表板
-- - 数据实时性要求不高(可以容忍延迟)
-- - 查询频率高,更新频率低

-- ❌ 不适合场景:
-- - 需要实时数据
-- - 频繁更新
-- - 简单查询

-- 视图: v_ 前缀
CREATE VIEW v_active_users AS ...;

-- 物化视图: mv_ 前缀
CREATE MATERIALIZED VIEW mv_daily_sales AS ...;

-- 物化视图本质是表,应该创建索引
CREATE MATERIALIZED VIEW mv_user_summary AS
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total
FROM orders
GROUP BY user_id;

-- 创建主键或唯一索引(支持并发刷新)
CREATE UNIQUE INDEX uk_mv_user_summary ON mv_user_summary(user_id);

-- 创建其他索引
CREATE INDEX idx_mv_user_total ON mv_user_summary(total DESC);

-- 记录刷新时间
CREATE TABLE mv_refresh_log (
    view_name TEXT,
    refresh_start TIMESTAMPTZ,
    refresh_end TIMESTAMPTZ,
    duration INTERVAL,
    row_count BIGINT
);

-- 刷新函数
CREATE OR REPLACE FUNCTION refresh_mv_with_log(view_name TEXT)
RETURNS VOID AS $$
DECLARE
    start_time TIMESTAMPTZ;
    end_time TIMESTAMPTZ;
    row_cnt BIGINT;
BEGIN
    start_time := clock_timestamp();

    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', view_name);

    end_time := clock_timestamp();
    EXECUTE format('SELECT COUNT(*) FROM %I', view_name) INTO row_cnt;

    INSERT INTO mv_refresh_log VALUES (
        view_name, start_time, end_time, end_time - start_time, row_cnt
    );
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT refresh_mv_with_log('daily_sales_summary');

-- 查看刷新历史
SELECT * FROM mv_refresh_log ORDER BY refresh_start DESC;