shanxia2.0/backend/scripts/migration_20260715_crm_price_letter.sql

-- ============================================================
-- CRM 调价函管理：建表 + 字典 + 菜单（合同管理下方）
-- 执行前可先备份 sys_menu；可重复执行（菜单/字典做了 NOT EXISTS 防护）
-- ============================================================

-- 1) 业务表
CREATE TABLE IF NOT EXISTS `crm_price_letter` (
  `id` varchar(64) NOT NULL COMMENT '主键ID（UUID）',
  `letter_type` varchar(20) NOT NULL COMMENT '类型 receive接受/send发出',
  `adjust_date` date NOT NULL COMMENT '调价日期',
  `customer_id` varchar(64) NOT NULL COMMENT '客户ID（crm_customer.id）',
  `customer_name` varchar(200) DEFAULT NULL COMMENT '客户名称(冗余)',
  `product_id` varchar(64) NOT NULL COMMENT '产品ID（crm_product.id）',
  `product_name` varchar(200) DEFAULT NULL COMMENT '产品名称(冗余)',
  `price` decimal(15,2) NOT NULL COMMENT '调整后现金价格(元)',
  `bill_price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '调整后汇票价格(元)',
  `content` mediumtext COMMENT '发出类富文本HTML；接受类为空',
  `attachment_urls` varchar(4000) DEFAULT NULL COMMENT '附件URL(逗号分隔，兼容上传组件)',
  `status` varchar(20) NOT NULL DEFAULT 'normal' COMMENT '状态 normal正常/invalid已作废',
  `invalid_reason` varchar(200) DEFAULT NULL COMMENT '作废原因',
  `invalid_time` datetime DEFAULT NULL COMMENT '作废时间',
  `invalid_by` varchar(64) DEFAULT NULL COMMENT '作废人ID',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建人ID',
  `create_dept_id` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人ID',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记(0存在 1删除)',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_price_letter_tenant` (`tenant_no`) USING BTREE,
  KEY `idx_price_letter_type` (`letter_type`) USING BTREE,
  KEY `idx_price_letter_status` (`status`) USING BTREE,
  KEY `idx_price_letter_customer` (`customer_id`) USING BTREE,
  KEY `idx_price_letter_product` (`product_id`) USING BTREE,
  KEY `idx_price_letter_adjust_date` (`adjust_date`) USING BTREE,
  KEY `idx_price_letter_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='CRM-调价函档案（独立，不关联合同/报价单）';

-- 2) 字典类型
INSERT INTO `sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'CRM_DTYPE_PRICE_LETTER_TYPE', '调价函类型', 'crm_price_letter_type', 'shanxia2.0', 90, '接受/发出',
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_type` WHERE `id` = 'CRM_DTYPE_PRICE_LETTER_TYPE' OR `dict_type` = 'crm_price_letter_type');

INSERT INTO `sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'CRM_DTYPE_PRICE_LETTER_STATUS', '调价函状态', 'crm_price_letter_status', 'shanxia2.0', 91, '正常/已作废',
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_type` WHERE `id` = 'CRM_DTYPE_PRICE_LETTER_STATUS' OR `dict_type` = 'crm_price_letter_status');

-- 3) 字典数据
INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`, `order_num`,
  `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM_DDATA_PRICE_LETTER_TYPE_01', '接受调价函', 'receive', 'warning', 'crm_price_letter_type', 'shanxia2.0', 1,
  NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `id` = 'CRM_DDATA_PRICE_LETTER_TYPE_01');

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`, `order_num`,
  `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM_DDATA_PRICE_LETTER_TYPE_02', '发出调价函', 'send', 'success', 'crm_price_letter_type', 'shanxia2.0', 2,
  NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `id` = 'CRM_DDATA_PRICE_LETTER_TYPE_02');

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`, `order_num`,
  `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM_DDATA_PRICE_LETTER_STATUS_01', '正常', 'normal', 'success', 'crm_price_letter_status', 'shanxia2.0', 1,
  NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `id` = 'CRM_DDATA_PRICE_LETTER_STATUS_01');

INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`, `order_num`,
  `remark`, `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM_DDATA_PRICE_LETTER_STATUS_02', '已作废', 'invalid', 'danger', 'crm_price_letter_status', 'shanxia2.0', 2,
  NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `id` = 'CRM_DDATA_PRICE_LETTER_STATUS_02');

-- 4) 菜单排序：合同管理(order_num=6) 之后顺延
UPDATE `sys_menu`
SET `order_num` = `order_num` + 1,
    `update_time` = NOW()
WHERE `parent_id` = 'CRM00000000000000000000000000001'
  AND `order_num` >= 7
  AND `del_flag` = 0
  AND NOT EXISTS (
    SELECT 1 FROM (
      SELECT id FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000030' AND `del_flag` = 0
    ) t
  );

-- 5) 菜单 + 按钮（无 remove，含 print/void）
INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'CRM3000000000000000000000000030', '调价函管理', 'CRM00000000000000000000000000001', 'C',
  'price-letter', '/crm/price-letter/index', 'crm:priceLetter:list',
  1, '1', '1', 'shanxia2.0', 7, '独立调价函档案：接受(附件)/发出(富文本+可选附件)；不关联合同与报价单',
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000030');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM3000000000000000000000000031', '调价函新增', 'CRM3000000000000000000000000030', 'F', '', NULL, 'crm:priceLetter:add',
  1, '1', '1', 'shanxia2.0', 1, NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000031');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM3000000000000000000000000032', '调价函编辑', 'CRM3000000000000000000000000030', 'F', '', NULL, 'crm:priceLetter:edit',
  1, '1', '1', 'shanxia2.0', 2, NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000032');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM3000000000000000000000000034', '调价函查看', 'CRM3000000000000000000000000030', 'F', '', NULL, 'crm:priceLetter:query',
  1, '1', '1', 'shanxia2.0', 3, NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000034');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM3000000000000000000000000035', '调价函打印', 'CRM3000000000000000000000000030', 'F', '', NULL, 'crm:priceLetter:print',
  1, '1', '1', 'shanxia2.0', 4, NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000035');

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT 'CRM3000000000000000000000000036', '调价函作废', 'CRM3000000000000000000000000030', 'F', '', NULL, 'crm:priceLetter:void',
  1, '1', '1', 'shanxia2.0', 5, NULL, 'system', '', NOW(), NULL, NOW(), 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 'CRM3000000000000000000000000036');

-- 6) 若曾误写「删除」按钮，统一软删（仅作废，无物理删除能力）
UPDATE `sys_menu`
SET `del_flag` = 1,
    `update_time` = NOW(),
    `remark` = CONCAT(IFNULL(`remark`, ''), ' [disabled: use void]')
WHERE `id` = 'CRM3000000000000000000000000033'
  AND `del_flag` = 0;

-- 7) 将调价函菜单/按钮授权给已拥有「合同管理」的角色（可重复执行）
INSERT INTO `sys_role_menu` (
  `id`, `role_id`, `menu_id`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT DISTINCT
  CONCAT('CRM_RM_PL_', SUBSTRING(MD5(CONCAT(rm.role_id, '-', m.id)), 1, 20)),
  rm.role_id,
  m.id,
  COALESCE(NULLIF(rm.tenant_no, ''), 'shanxia2.0'),
  0,
  'auto grant price-letter with contract',
  'system',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM `sys_role_menu` rm
JOIN `sys_menu` m ON m.id IN (
  'CRM3000000000000000000000000030',
  'CRM3000000000000000000000000031',
  'CRM3000000000000000000000000032',
  'CRM3000000000000000000000000034',
  'CRM3000000000000000000000000035',
  'CRM3000000000000000000000000036'
) AND m.del_flag = 0
WHERE rm.menu_id = 'CRM3000000000000000000000000020'
  AND rm.del_flag = 0
  AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu` x
    WHERE x.role_id = rm.role_id AND x.menu_id = m.id AND x.del_flag = 0
  );