-- 阿里云短信：模板管理 + 发送日志（会员生日等场景可扩展）
-- 执行库：lqerp_dev / 生产对应库

CREATE TABLE IF NOT EXISTS `lq_sms_template` (
  `F_Id` varchar(50) NOT NULL COMMENT '主键',
  `F_Name` varchar(128) NOT NULL COMMENT '模板名称',
  `F_TemplateCode` varchar(64) NOT NULL COMMENT '阿里云模板CODE',
  `F_SignName` varchar(64) NOT NULL DEFAULT '绿纤聚财' COMMENT '短信签名',
  `F_TemplateType` int NOT NULL DEFAULT 1 COMMENT '模板类型：1验证码 2推广短信 3通知短信',
  `F_SceneCode` varchar(64) NOT NULL COMMENT '业务场景编码：Birthday/Login/Register/VerifyCode 等',
  `F_ParamTemplate` varchar(512) DEFAULT NULL COMMENT '模板参数JSON模板，如 {"name":"{name}"}',
  `F_Enabled` int NOT NULL DEFAULT 1 COMMENT '是否启用：1启用 0停用',
  `F_Sort` int NOT NULL DEFAULT 0 COMMENT '排序',
  `F_Remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `F_CreatorTime` datetime DEFAULT NULL COMMENT '创建时间',
  `F_CreatorUserId` varchar(64) DEFAULT NULL COMMENT '创建人ID',
  `F_LastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
  `F_LastModifyUserId` varchar(64) DEFAULT NULL COMMENT '最后修改人ID',
  `F_DeleteMark` int DEFAULT NULL COMMENT '删除标记（null 未删除）',
  PRIMARY KEY (`F_Id`),
  KEY `IX_lq_sms_template_SceneCode` (`F_SceneCode`),
  KEY `IX_lq_sms_template_Code` (`F_TemplateCode`),
  KEY `IX_lq_sms_template_Enabled_Sort` (`F_Enabled`, `F_Sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信模板（按业务场景可扩展）';

CREATE TABLE IF NOT EXISTS `lq_sms_send_log` (
  `F_Id` varchar(50) NOT NULL COMMENT '主键',
  `F_SceneCode` varchar(64) NOT NULL COMMENT '业务场景编码',
  `F_TemplateId` varchar(50) DEFAULT NULL COMMENT '模板主键',
  `F_TemplateCode` varchar(64) NOT NULL COMMENT '模板CODE',
  `F_SignName` varchar(64) DEFAULT NULL COMMENT '签名',
  `F_MobileMasked` varchar(32) NOT NULL COMMENT '手机号脱敏',
  `F_MobileHash` varchar(64) DEFAULT NULL COMMENT '手机号哈希（防重比对，非明文）',
  `F_MemberId` varchar(50) DEFAULT NULL COMMENT '会员ID（lq_khxx.F_Id）',
  `F_MemberName` varchar(128) DEFAULT NULL COMMENT '会员姓名',
  `F_TemplateParam` varchar(1024) DEFAULT NULL COMMENT '实际发送模板参数JSON',
  `F_Status` int NOT NULL DEFAULT 0 COMMENT '发送状态：0失败 1成功 2跳过(防重)',
  `F_ProviderResult` varchar(256) DEFAULT NULL COMMENT '厂商返回码/说明',
  `F_BizDate` date DEFAULT NULL COMMENT '业务日期（生日防重按日）',
  `F_IsTest` int NOT NULL DEFAULT 0 COMMENT '是否测试发送：1是 0否',
  `F_CreatorTime` datetime DEFAULT NULL COMMENT '发送时间',
  `F_CreatorUserId` varchar(64) DEFAULT NULL COMMENT '操作人ID',
  PRIMARY KEY (`F_Id`),
  KEY `IX_lq_sms_send_log_BizDate_Scene_Member` (`F_BizDate`, `F_SceneCode`, `F_MemberId`),
  KEY `IX_lq_sms_send_log_CreatorTime` (`F_CreatorTime`),
  KEY `IX_lq_sms_send_log_Status` (`F_Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信发送日志';

-- 种子模板（幂等：同场景编码已存在则跳过）
INSERT INTO `lq_sms_template`
  (`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000001', '生日提醒', 'SMS_503595047', '绿纤聚财', 2, 'Birthday', '{"name":"{name}"}', 1, 1, '会员生日推广短信', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Birthday' AND `F_DeleteMark` IS NULL);

INSERT INTO `lq_sms_template`
  (`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000002', '用于登录', 'SMS_489770174', '绿纤聚财', 1, 'Login', '{"code":"{code}"}', 1, 2, '登录验证码', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Login' AND `F_DeleteMark` IS NULL);

INSERT INTO `lq_sms_template`
  (`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000003', '用于注册', 'SMS_489645186', '绿纤聚财', 1, 'Register', '{"code":"{code}"}', 1, 3, '注册验证码', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Register' AND `F_DeleteMark` IS NULL);

INSERT INTO `lq_sms_template`
  (`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000004', '验证码短信', 'SMS_321375912', '绿纤聚财', 1, 'VerifyCode', '{"code":"{code}"}', 1, 4, '通用验证码（签名也可为绿纤聚财成都美容）', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'VerifyCode' AND `F_DeleteMark` IS NULL);

-- 默认短信配置占位（Category=SmsConfig；密钥请在系统配置页填写真实值）
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000101', '短信签名', 'smssignname', '绿纤聚财', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smssignname');

INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000102', '短信厂商', 'smscompany', '1', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smscompany');

INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000103', '短信Endpoint', 'smsendpoint', 'dysmsapi.aliyuncs.com', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smsendpoint');

INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000104', '生日防重发', 'smspreventduplicate', '1', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smspreventduplicate');

-- 隐私：手机号脱敏开关（默认关闭=明文；Category=SysConfig）
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000201', '手机号脱敏', 'privacymobilemask', '0', 'SysConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SysConfig' AND LOWER(`F_KEY`) = 'privacymobilemask');