ylrz_saas_pharmacy/java/sql/V20260722_01__lobster_segment_threshold_config.sql

-- 内置自动分群阈值配置支持
-- 1. 确保 lobster_user_segment 表存在 strategy_config 字段
-- 2. 为每个已有 company 初始化内置分群记录（NEW/ACTIVE/SLEEP/DORMANT/CHURN），使用默认阈值

SET @dbname = DATABASE();
SET @tablename = 'lobster_user_segment';
SET @columnname = 'strategy_config';
SET @preparedStatement = (SELECT IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@dbname AND TABLE_NAME=@tablename AND COLUMN_NAME=@columnname)>0,'SELECT 1','ALTER TABLE lobster_user_segment ADD COLUMN strategy_config TEXT DEFAULT NULL COMMENT ''策略配置JSON（含阈值等）'''));
PREPARE s FROM @preparedStatement; EXECUTE s; DEALLOCATE PREPARE s;

-- 2. 为每个 company 初始化内置分群记录（仅当不存在时插入）
INSERT INTO lobster_user_segment (company_id, segment_code, segment_name, description, strategy_config, deleted, create_time, update_time)
SELECT
  c.company_id,
  t.segment_code,
  t.segment_name,
  t.description,
  t.strategy_config,
  0 AS deleted,
  NOW() AS create_time,
  NOW() AS update_time
FROM (
  SELECT DISTINCT company_id FROM lobster_user_segment WHERE company_id IS NOT NULL
  UNION
  SELECT DISTINCT company_id FROM lobster_user_profile WHERE company_id IS NOT NULL
  UNION
  SELECT company_id FROM company WHERE IFNULL(is_del, 0) = 0
) c
CROSS JOIN (
  SELECT 'NEW' AS segment_code, '新用户' AS segment_name, '无消费且活跃<=7天的新接入客户' AS description,
         '{"thresholds":{"newMaxInteraction":0,"activeMinInteraction":20,"sleepMinDays":31,"sleepMaxDays":60,"dormantMinDays":61,"dormantMaxDays":90,"churnMinDays":91}}' AS strategy_config
  UNION ALL SELECT 'ACTIVE', '活跃用户', '有消费且最近30天内活跃，或交互>=20次',
         '{"thresholds":{"newMaxInteraction":0,"activeMinInteraction":20,"sleepMinDays":31,"sleepMaxDays":60,"dormantMinDays":61,"dormantMaxDays":90,"churnMinDays":91}}'
  UNION ALL SELECT 'SLEEP', '沉睡用户', '有消费，31-60天未活跃',
         '{"thresholds":{"newMaxInteraction":0,"activeMinInteraction":20,"sleepMinDays":31,"sleepMaxDays":60,"dormantMinDays":61,"dormantMaxDays":90,"churnMinDays":91}}'
  UNION ALL SELECT 'DORMANT', '流失预警', '有消费，61-90天未活跃',
         '{"thresholds":{"newMaxInteraction":0,"activeMinInteraction":20,"sleepMinDays":31,"sleepMaxDays":60,"dormantMinDays":61,"dormantMaxDays":90,"churnMinDays":91}}'
  UNION ALL SELECT 'CHURN', '流失用户', '无消费且>90天未活跃，或有消费但>90天未活跃',
         '{"thresholds":{"newMaxInteraction":0,"activeMinInteraction":20,"sleepMinDays":31,"sleepMaxDays":60,"dormantMinDays":61,"dormantMaxDays":90,"churnMinDays":91}}'
) t
WHERE NOT EXISTS (
  SELECT 1 FROM lobster_user_segment s
  WHERE s.company_id = c.company_id AND s.segment_code = t.segment_code AND s.deleted = 0
);