-- 创建金三角月度业绩总和视图（简化版）
-- 功能：获取每个金三角的每个月开单业绩总和
-- 主要关联：lq_kd_jksyj（健康师业绩表）和 lq_ycsd_jsj（金三角设定表）

CREATE VIEW v_jsj_monthly_summary 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,                   -- 门店名称
    SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS total_performance  -- 月度业绩总和
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 IF NOT EXISTS idx_lq_kd_jksyj_jsj_id ON lq_kd_jksyj(jsj_id);
CREATE INDEX IF NOT EXISTS idx_lq_kd_jksyj_yjsj ON lq_kd_jksyj(yjsj);
CREATE INDEX IF NOT EXISTS idx_lq_ycsd_jsj_yf ON lq_ycsd_jsj(yf);

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

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

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

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