-- 创建门店每日耗卡统计视图
-- 统计各个门店每日项目数（耗卡里面的项目数量）和消耗业绩（耗卡里面的金额）

DROP VIEW IF EXISTS `v_store_daily_consume_stats`;

CREATE VIEW `v_store_daily_consume_stats` AS
SELECT 
    hk.md AS store_id,                    -- 门店ID
    hk.mdbh AS store_code,                -- 门店编号
    hk.mdmc AS store_name,                -- 门店名称
    DATE(hk.hksj) AS consume_date,        -- 耗卡日期
    COUNT(DISTINCT hk.F_Id) AS consume_count,  -- 耗卡记录数
    COALESCE(SUM(px.F_ProjectNumber), 0) AS total_project_count,  -- 总项目数
    COALESCE(SUM(px.F_TotalPrice), 0) AS total_consume_amount,    -- 总消耗金额
    COALESCE(SUM(CASE WHEN px.F_IsEnabled = 1 THEN px.F_ProjectNumber ELSE 0 END), 0) AS enabled_project_count,  -- 有效项目数
    COALESCE(SUM(CASE WHEN px.F_IsEnabled = 1 THEN px.F_TotalPrice ELSE 0 END), 0) AS enabled_consume_amount,    -- 有效消耗金额
    COALESCE(SUM(CASE WHEN px.F_SourceType = '开卡' THEN px.F_ProjectNumber ELSE 0 END), 0) AS card_project_count,  -- 开卡项目数
    COALESCE(SUM(CASE WHEN px.F_SourceType = '开卡' THEN px.F_TotalPrice ELSE 0 END), 0) AS card_consume_amount,    -- 开卡消耗金额
    COALESCE(SUM(CASE WHEN px.F_SourceType = '赠送' THEN px.F_ProjectNumber ELSE 0 END), 0) AS gift_project_count,  -- 赠送项目数
    COALESCE(SUM(CASE WHEN px.F_SourceType = '赠送' THEN px.F_TotalPrice ELSE 0 END), 0) AS gift_consume_amount,    -- 赠送消耗金额
    COALESCE(SUM(CASE WHEN px.F_SourceType = '其他' THEN px.F_ProjectNumber ELSE 0 END), 0) AS other_project_count,  -- 其他项目数
    COALESCE(SUM(CASE WHEN px.F_SourceType = '其他' THEN px.F_TotalPrice ELSE 0 END), 0) AS other_consume_amount,    -- 其他消耗金额
    COUNT(DISTINCT hk.hy) AS member_count,  -- 消费会员数
    hk.hksj AS create_time                 -- 创建时间
FROM lq_xh_hyhk hk
LEFT JOIN lq_xh_pxmx px ON hk.F_Id = px.F_ConsumeInfoId
WHERE hk.hksj IS NOT NULL
GROUP BY hk.md, hk.mdbh, hk.mdmc, DATE(hk.hksj), hk.hksj
ORDER BY hk.md, DATE(hk.hksj) DESC;

-- 添加视图注释
ALTER VIEW `v_store_daily_consume_stats` COMMENT = '门店每日耗卡统计视图 - 统计各门店每日项目数和消耗业绩';

-- 视图说明：
-- 1. 按门店和日期分组统计耗卡数据
-- 2. 统计项目数和消耗金额，区分不同来源类型
-- 3. 包含有效/无效项目统计
-- 4. 按来源类型（开卡、赠送、其他）分别统计
-- 5. 统计消费会员数量
-- 6. 按门店和日期降序排列