-- =============================================================================
-- 员工到院转化登记 lq_member_arrival_record
-- 说明：
--   1. 主键 F_Id 须由应用层 YitIdHelper.NextId().ToString() 生成。
--   2. 独立模块，只读 lq_khxx/BASE_USER/组织/门店，不联动会员主档及其他主业务。
--   3. F_RecordKey 有效时为 storeId_memberId_yyyyMMdd，作废置空；唯一索引保证并发不重复。
--   4. 幂等：CREATE TABLE IF NOT EXISTS + 索引通过 information_schema 判断后创建。
-- =============================================================================

CREATE TABLE IF NOT EXISTS `lq_member_arrival_record` (
  `F_Id` varchar(50) NOT NULL COMMENT '主键',
  `F_RecordNo` varchar(32) NOT NULL COMMENT '登记编号（对外展示，唯一）',
  `F_RecordKey` varchar(128) DEFAULT NULL COMMENT '有效记录唯一键：门店_会员_yyyyMMdd，作废时置空',
  `F_MemberId` varchar(50) NOT NULL COMMENT '会员ID（lq_khxx.F_Id）',
  `F_MemberName` varchar(128) DEFAULT NULL COMMENT '会员姓名快照',
  `F_MemberMobile` varchar(32) DEFAULT NULL COMMENT '会员手机号快照',
  `F_SourceOrganizeId` varchar(50) DEFAULT NULL COMMENT '来源组织ID（登记人当前组织快照）',
  `F_SourceOrganizeName` varchar(200) DEFAULT NULL COMMENT '来源组织名称快照',
  `F_SourceMdid` varchar(50) DEFAULT NULL COMMENT '来源门店ID（登记人当前门店快照）',
  `F_SourceMdName` varchar(200) DEFAULT NULL COMMENT '来源门店名称快照',
  `F_ArrivalType` int NOT NULL COMMENT '到院类型：1售前 2售后',
  `F_ArrivalTime` datetime NOT NULL COMMENT '到院时间',
  `F_ConsultationStatus` int NOT NULL DEFAULT 0 COMMENT '见诊状态：0待确认 1是 2否',
  `F_ConsultationTime` datetime DEFAULT NULL COMMENT '见诊状态确认时间',
  `F_DealStatus` int NOT NULL DEFAULT 0 COMMENT '成交状态：0待确认 1是 2否',
  `F_DealTime` datetime DEFAULT NULL COMMENT '成交状态确认时间',
  `F_RecorderUserId` varchar(50) DEFAULT NULL COMMENT '登记人用户ID',
  `F_RecorderUserName` varchar(128) DEFAULT NULL COMMENT '登记人姓名',
  `F_LastModifyUserId` varchar(50) DEFAULT NULL COMMENT '最后修改人用户ID',
  `F_LastModifyUserName` varchar(128) DEFAULT NULL COMMENT '最后修改人姓名',
  `F_Remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `F_IsEffective` int NOT NULL DEFAULT 1 COMMENT '是否有效：1有效 0作废',
  `F_VoidReason` varchar(500) DEFAULT NULL COMMENT '作废原因',
  `F_VoidUserId` varchar(50) DEFAULT NULL COMMENT '作废操作人ID',
  `F_VoidUserName` varchar(128) DEFAULT NULL COMMENT '作废操作人姓名',
  `F_VoidTime` datetime DEFAULT NULL COMMENT '作废时间',
  `F_Version` int NOT NULL DEFAULT 1 COMMENT '乐观锁版本号',
  `F_CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
  `F_UpdateTime` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`F_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工到院转化登记';

-- 唯一索引：登记编号
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'UK_lq_member_arrival_record_No'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD UNIQUE KEY `UK_lq_member_arrival_record_No` (`F_RecordNo`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 唯一索引：有效记录键（可空，作废后可重新登记）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'UK_lq_member_arrival_record_RecordKey'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD UNIQUE KEY `UK_lq_member_arrival_record_RecordKey` (`F_RecordKey`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：到院时间 + 有效标记（统计/列表）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_ArrivalTime_Effective'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_ArrivalTime_Effective` (`F_ArrivalTime`, `F_IsEffective`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：来源组织 + 到院时间（权限过滤列表）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_SourceOrg_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_SourceOrg_ArrivalTime` (`F_SourceOrganizeId`, `F_ArrivalTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：来源门店 + 到院时间
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_SourceMd_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_SourceMd_ArrivalTime` (`F_SourceMdid`, `F_ArrivalTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：会员 + 到院时间
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Member_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Member_ArrivalTime` (`F_MemberId`, `F_ArrivalTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：登记人 + 到院时间（手机端我的列表）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Recorder_ArrivalTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Recorder_ArrivalTime` (`F_RecorderUserId`, `F_ArrivalTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：到院类型 + 见诊/成交状态（筛选）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_member_arrival_record' AND index_name = 'IDX_lq_member_arrival_Type_Status'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_member_arrival_record` ADD KEY `IDX_lq_member_arrival_Type_Status` (`F_ArrivalType`, `F_ConsultationStatus`, `F_DealStatus`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;