-- ============================================
-- 为转化率统计添加关联字段
-- ============================================
-- 说明：此脚本为转化率统计功能添加关联字段，用于追踪邀约→预约→开单/耗卡的完整业务链路
-- 执行顺序：按照业务链路顺序依次执行
-- 
-- 业务链路：
-- 邀约记录 (lq_yaoyjl) → 预约记录 (lq_yyjl) → 开单记录 (lq_kd_kdjlb) / 耗卡记录 (lq_xh_hyhk)
-- 
-- 字段说明：
-- 1. lq_yyjl.F_InviteId：关联邀约记录ID，用于统计邀约→预约转化率
-- 2. lq_kd_kdjlb.F_AppointmentId：关联预约记录ID，用于统计预约→开单转化率
-- 3. lq_xh_hyhk.F_AppointmentId：关联预约记录ID，用于统计预约→耗卡转化率
-- 
-- 注意事项：
-- - 所有字段允许为NULL，因为历史数据可能没有这些关联
-- - 字段类型为VARCHAR(50)，与其他ID字段保持一致
-- - 已为所有字段创建索引，以优化查询性能

-- ============================================
-- 1. lq_yyjl（预约记录表） - 添加邀约ID字段和未成交说明字段
-- ============================================
-- 说明：关联邀约记录，用于统计邀约→预约转化率
ALTER TABLE lq_yyjl 
ADD COLUMN F_InviteId VARCHAR(50) NULL COMMENT '邀约记录ID（关联lq_yaoyjl.F_Id，用于统计邀约→预约转化率）' AFTER F_Status,
ADD COLUMN F_NoDealRemark VARCHAR(2000) NULL COMMENT '未成交说明' AFTER F_InviteId;

-- 创建索引以优化查询性能
CREATE INDEX idx_yyjl_invite_id ON lq_yyjl(F_InviteId);

-- ============================================
-- 2. lq_kd_kdjlb（开单记录表） - 添加预约ID字段
-- ============================================
-- 说明：关联预约记录，用于统计预约→开单转化率
ALTER TABLE lq_kd_kdjlb 
ADD COLUMN F_AppointmentId VARCHAR(50) NULL COMMENT '预约记录ID（关联lq_yyjl.F_Id，用于统计预约→开单转化率）' AFTER F_ActivityId;

-- 创建索引以优化查询性能
CREATE INDEX idx_kdjlb_appointment_id ON lq_kd_kdjlb(F_AppointmentId);

-- ============================================
-- 3. lq_xh_hyhk（耗卡记录表） - 添加预约ID字段
-- ============================================
-- 说明：关联预约记录，用于统计预约→耗卡转化率
ALTER TABLE lq_xh_hyhk 
ADD COLUMN F_AppointmentId VARCHAR(50) NULL COMMENT '预约记录ID（关联lq_yyjl.F_Id，用于统计预约→耗卡转化率）' AFTER F_OvertimeSgfy;

-- 创建索引以优化查询性能
CREATE INDEX idx_hyhk_appointment_id ON lq_xh_hyhk(F_AppointmentId);

-- ============================================
-- 4. 验证字段创建
-- ============================================
-- 验证 lq_yyjl 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT 
-- FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_NAME = 'lq_yyjl' 
--   AND (COLUMN_NAME = 'F_InviteId' OR COLUMN_NAME = 'F_NoDealRemark');

-- 验证 lq_kd_kdjlb 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT 
-- FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_NAME = 'lq_kd_kdjlb' 
--   AND COLUMN_NAME = 'F_AppointmentId';

-- 验证 lq_xh_hyhk 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT 
-- FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_NAME = 'lq_xh_hyhk' 
--   AND COLUMN_NAME = 'F_AppointmentId';

-- ============================================
-- 5. 验证索引创建
-- ============================================
-- 验证索引
-- SHOW INDEX FROM lq_yyjl WHERE Key_name = 'idx_yyjl_invite_id';
-- SHOW INDEX FROM lq_kd_kdjlb WHERE Key_name = 'idx_kdjlb_appointment_id';
-- SHOW INDEX FROM lq_xh_hyhk WHERE Key_name = 'idx_hyhk_appointment_id';

-- ============================================
-- 6. 转化率统计示例SQL（供参考）
-- ============================================
-- 示例1：邀约→预约转化率
-- SELECT 
--     COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
--     COUNT(DISTINCT yyjl.F_InviteId) AS 来自邀约的预约数量,
--     COUNT(DISTINCT yyjl.F_InviteId) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- WHERE yyjl.F_CreateTime >= '2024-01-01';

-- 示例2：预约→开单转化率
-- SELECT 
--     COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
--     COUNT(DISTINCT kdjlb.F_Id) AS 开单数量,
--     COUNT(DISTINCT kdjlb.F_Id) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- LEFT JOIN lq_kd_kdjlb kdjlb ON kdjlb.F_AppointmentId = yyjl.F_Id AND kdjlb.F_IsEffective = 1
-- WHERE yyjl.F_CreateTime >= '2024-01-01';

-- 示例3：预约→耗卡转化率
-- SELECT 
--     COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
--     COUNT(DISTINCT hyhk.F_Id) AS 耗卡数量,
--     COUNT(DISTINCT hyhk.F_Id) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- LEFT JOIN lq_xh_hyhk hyhk ON hyhk.F_AppointmentId = yyjl.F_Id AND hyhk.F_IsEffective = 1
-- WHERE yyjl.F_CreateTime >= '2024-01-01';

-- 示例4：邀约→开单转化率（跨步转化）
-- SELECT 
--     COUNT(DISTINCT yaoyjl.F_Id) AS 邀约数量,
--     COUNT(DISTINCT kdjlb.F_Id) AS 开单数量,
--     COUNT(DISTINCT kdjlb.F_Id) * 100.0 / COUNT(DISTINCT yaoyjl.F_Id) AS 转化率百分比
-- FROM lq_yaoyjl yaoyjl
-- LEFT JOIN lq_yyjl yyjl ON yyjl.F_InviteId = yaoyjl.F_Id
-- LEFT JOIN lq_kd_kdjlb kdjlb ON kdjlb.F_AppointmentId = yyjl.F_Id AND kdjlb.F_IsEffective = 1
-- WHERE yaoyjl.F_CreateTime >= '2024-01-01';