-- ============================================
-- 创建会员开单耗卡项目数视图
-- ============================================
-- 说明：创建视图可以提升查询效率，避免每次都执行复杂的JOIN和聚合操作
-- 视图会预先聚合好数据，查询时直接使用

-- ============================================
-- 方案1：基础聚合视图（推荐）
-- ============================================
-- 优点：查询简单、性能好、可以灵活按时间范围查询
-- 缺点：仍然需要每次查询时过滤时间范围

-- 删除已存在的视图（如果存在）
DROP VIEW IF EXISTS v_member_billing_consume_project;

-- 创建视图：会员开单项目数汇总（不限制时间，包含所有数据）
CREATE VIEW v_member_billing_project AS
SELECT 
    px.F_MemberId as member_id,
    kd.djmd as store_id,
    kd.kdrq as billing_date,
    SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
WHERE px.F_IsEffective = 1
    AND kd.F_IsEffective = 1
    AND px.F_MemberId IS NOT NULL
    AND px.F_MemberId != ''
GROUP BY px.F_MemberId, kd.djmd, DATE(kd.kdrq);

-- 创建视图：会员耗卡项目数汇总（不限制时间，包含所有数据）
CREATE VIEW v_member_consume_project AS
SELECT 
    xhpx.F_MemberId as member_id,
    xh.md as store_id,
    xh.hksj as consume_date,
    SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
FROM lq_xh_pxmx xhpx
INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
WHERE xhpx.F_IsEffective = 1
    AND xh.F_IsEffective = 1
    AND xhpx.F_MemberId IS NOT NULL
    AND xhpx.F_MemberId != ''
GROUP BY xhpx.F_MemberId, xh.md, DATE(xh.hksj);

-- 创建组合视图：会员开单耗卡项目数（按日汇总）
CREATE VIEW v_member_billing_consume_daily AS
SELECT 
    COALESCE(billing.member_id, consume.member_id) as member_id,
    COALESCE(billing.store_id, consume.store_id) as store_id,
    COALESCE(billing.billing_date, consume.consume_date) as statistics_date,
    COALESCE(billing.billing_project_count, 0) as billing_project_count,
    COALESCE(consume.consume_project_count, 0) as consume_project_count,
    (COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM v_member_billing_project billing
FULL OUTER JOIN v_member_consume_project consume 
    ON billing.member_id = consume.member_id 
    AND billing.store_id = consume.store_id
    AND DATE(billing.billing_date) = DATE(consume.consume_date);

-- ============================================
-- 方案2：使用UNION ALL的视图（兼容MySQL，推荐）
-- ============================================
-- 优点：兼容MySQL所有版本、查询简单、性能好

DROP VIEW IF EXISTS v_member_billing_consume_project;

CREATE VIEW v_member_billing_consume_project AS
SELECT 
    member_id,
    store_id,
    statistics_date,
    SUM(billing_project_count) as billing_project_count,
    SUM(consume_project_count) as consume_project_count,
    SUM(billing_project_count) - SUM(consume_project_count) as remaining_project_count
FROM (
    -- 开单项目数（按会员、门店、日期分组）
    SELECT 
        px.F_MemberId as member_id,
        kd.djmd as store_id,
        DATE(kd.kdrq) as statistics_date,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count,
        0 as consume_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
    GROUP BY px.F_MemberId, kd.djmd, DATE(kd.kdrq)
    
    UNION ALL
    
    -- 耗卡项目数（按会员、门店、日期分组）
    SELECT 
        xhpx.F_MemberId as member_id,
        xh.md as store_id,
        DATE(xh.hksj) as statistics_date,
        0 as billing_project_count,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
    GROUP BY xhpx.F_MemberId, xh.md, DATE(xh.hksj)
) all_data
GROUP BY member_id, store_id, statistics_date;

-- ============================================
-- 方案3：按会员汇总的总视图（不按日期，累计汇总）
-- ============================================
-- 优点：查询最简单、性能最好（适合查询总量）
-- 缺点：无法按时间范围查询

DROP VIEW IF EXISTS v_member_billing_consume_total;

CREATE VIEW v_member_billing_consume_total AS
SELECT 
    member_id,
    SUM(billing_project_count) as total_billing_project_count,
    SUM(consume_project_count) as total_consume_project_count,
    SUM(billing_project_count) - SUM(consume_project_count) as total_remaining_project_count,
    COUNT(DISTINCT store_id) as store_count
FROM (
    -- 开单项目数（按会员、门店汇总）
    SELECT 
        px.F_MemberId as member_id,
        kd.djmd as store_id,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count,
        0 as consume_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
    GROUP BY px.F_MemberId, kd.djmd
    
    UNION ALL
    
    -- 耗卡项目数（按会员、门店汇总）
    SELECT 
        xhpx.F_MemberId as member_id,
        xh.md as store_id,
        0 as billing_project_count,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
    GROUP BY xhpx.F_MemberId, xh.md
) all_data
GROUP BY member_id;


-- ============================================
-- 使用视图的查询示例
-- ============================================

-- 示例1：查询所有会员的开单耗卡项目数（按会员汇总，不限制时间）
SELECT 
    member_id,
    SUM(billing_project_count) as billing_project_count,
    SUM(consume_project_count) as consume_project_count,
    SUM(remaining_project_count) as remaining_project_count
FROM v_member_billing_consume_project
GROUP BY member_id
ORDER BY billing_project_count DESC;

-- 示例2：查询指定时间范围内的数据
SELECT 
    member_id,
    SUM(billing_project_count) as billing_project_count,
    SUM(consume_project_count) as consume_project_count,
    SUM(remaining_project_count) as remaining_project_count
FROM v_member_billing_consume_project
WHERE statistics_date >= '2025-10-01'
    AND statistics_date <= '2025-10-30'
GROUP BY member_id
ORDER BY billing_project_count DESC;

-- 示例3：查询指定门店的数据
SELECT 
    member_id,
    SUM(billing_project_count) as billing_project_count,
    SUM(consume_project_count) as consume_project_count,
    SUM(remaining_project_count) as remaining_project_count
FROM v_member_billing_consume_project
WHERE store_id = '1649328471923847169'
    AND statistics_date >= '2025-10-01'
    AND statistics_date <= '2025-10-30'
GROUP BY member_id
ORDER BY billing_project_count DESC;

-- 示例4：使用总视图（最简单的查询，累计所有数据）
SELECT 
    member_id,
    total_billing_project_count,
    total_consume_project_count,
    total_remaining_project_count,
    store_count
FROM v_member_billing_consume_total
ORDER BY total_billing_project_count DESC
LIMIT 100;


-- ============================================
-- 视图性能优化建议
-- ============================================
-- 1. 确保基础表已创建索引（参考优化GetStoreRemainingRights性能索引.sql）
-- 2. 如果数据量很大，可以考虑：
--    a. 创建物化视图（但MySQL不支持，需要定期刷新汇总表）
--    b. 创建汇总表，定期更新（推荐）
-- 3. 定期分析视图性能：
--    EXPLAIN SELECT * FROM v_member_billing_consume_project WHERE statistics_date >= '2025-10-01';

-- ============================================
-- 视图维护
-- ============================================
-- 查看视图定义
-- SHOW CREATE VIEW v_member_billing_consume_project;

-- 查看所有视图
-- SELECT TABLE_NAME 
-- FROM INFORMATION_SCHEMA.VIEWS 
-- WHERE TABLE_SCHEMA = DATABASE();

-- 删除视图
-- DROP VIEW IF EXISTS v_member_billing_consume_project;
-- DROP VIEW IF EXISTS v_member_billing_consume_total;