-- 创建金三角月度业绩统计视图
-- 功能：获取每个金三角的每个月开单业绩总和
-- 关联表：lq_kd_jksyj（健康师业绩表）、lq_ycsd_jsj（金三角设定表）、lq_jinsanjiao_user（金三角用户绑定表）

CREATE VIEW v_jsj_monthly_performance AS
SELECT 
    jsj.F_Id AS jsj_id,                    -- 金三角ID
    jsj.jsj AS jsj_name,                   -- 金三角名称
    jsj.yf AS month,                       -- 月份
    jsj.md AS store_id,                    -- 门店ID
    md.dm AS store_name,                   -- 门店名称
    COUNT(DISTINCT jksyj.F_Id) AS order_count,  -- 开单数量
    SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS total_performance,  -- 总业绩
    AVG(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS avg_performance,    -- 平均业绩
    MAX(jksyj.yjsj) AS last_order_date,    -- 最后开单日期
    MIN(jksyj.yjsj) AS first_order_date,   -- 首次开单日期
    COUNT(DISTINCT jksyj.jks) AS health_teacher_count,  -- 健康师数量
    GROUP_CONCAT(DISTINCT jksyj.jksxm ORDER BY jksyj.jksxm SEPARATOR ',') AS health_teachers  -- 健康师姓名列表
FROM 
    lq_ycsd_jsj jsj
    LEFT JOIN lq_kd_jksyj jksyj ON jsj.F_Id = jksyj.jsj_id
    LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
WHERE 
    jsj.yf IS NOT NULL 
    AND jksyj.yjsj IS NOT NULL
    AND jksyj.jksyj IS NOT NULL 
    AND jksyj.jksyj != ''
    AND jksyj.jksyj != '0'
GROUP BY 
    jsj.F_Id, 
    jsj.jsj, 
    jsj.yf, 
    jsj.md,
    md.dm
ORDER BY 
    jsj.yf DESC, 
    total_performance DESC;

-- 创建索引以优化查询性能
CREATE INDEX idx_lq_kd_jksyj_jsj_yjsj ON lq_kd_jksyj(jsj_id, yjsj);
CREATE INDEX idx_lq_kd_jksyj_jksyj ON lq_kd_jksyj(jksyj);

-- 使用示例查询：
-- 1. 查询所有金三角的月度业绩
-- SELECT * FROM v_jsj_monthly_performance ORDER BY month DESC, total_performance DESC;

-- 2. 查询指定月份的金三角业绩
-- SELECT * FROM v_jsj_monthly_performance WHERE month = '202412' ORDER BY total_performance DESC;

-- 3. 查询指定金三角的业绩历史
-- SELECT * FROM v_jsj_monthly_performance WHERE jsj_id = '具体金三角ID' ORDER BY month DESC;

-- 4. 查询指定门店的金三角业绩
-- SELECT * FROM v_jsj_monthly_performance WHERE store_id = '具体门店ID' ORDER BY month DESC, total_performance DESC;

-- 5. 查询业绩排名前10的金三角
-- SELECT * FROM v_jsj_monthly_performance WHERE month = '202412' ORDER BY total_performance DESC LIMIT 10;