-- 易制毒购买证 / 运输证 / 计划产品行关联（P2）
-- 执行前请确认库为 MySQL 5.7 utf8mb4

CREATE TABLE IF NOT EXISTS `pl_purchase_cert` (
  `id` varchar(64) NOT NULL COMMENT '主键UUID',
  `cert_no` varchar(64) NOT NULL COMMENT '购买证编号',
  `issue_date` date DEFAULT NULL COMMENT '核发日期',
  `buyer_name` varchar(200) DEFAULT NULL COMMENT '购买单位或个人',
  `buyer_legal` varchar(100) DEFAULT NULL COMMENT '购买方法人',
  `buyer_tel` varchar(50) DEFAULT NULL COMMENT '购买方电话',
  `buyer_addr` varchar(500) DEFAULT NULL COMMENT '购买方住所',
  `seller_name` varchar(200) DEFAULT NULL COMMENT '销售单位',
  `seller_legal` varchar(100) DEFAULT NULL COMMENT '销售方法人',
  `seller_tel` varchar(50) DEFAULT NULL COMMENT '销售方电话',
  `seller_addr` varchar(500) DEFAULT NULL COMMENT '销售方住所',
  `product_name` varchar(200) NOT NULL COMMENT '购买物品/品名',
  `usage_desc` varchar(200) DEFAULT NULL COMMENT '用途',
  `total_qty` decimal(18,3) NOT NULL DEFAULT 0.000 COMMENT '购买证数量',
  `unit` varchar(20) NOT NULL DEFAULT '吨' COMMENT '单位',
  `valid_times` varchar(50) DEFAULT NULL COMMENT '有效次数（如多次有效）',
  `valid_from` date DEFAULT NULL COMMENT '有效开始',
  `valid_to` date DEFAULT NULL COMMENT '有效截止',
  `police_org` varchar(200) DEFAULT NULL COMMENT '公安机关',
  `handler_name` varchar(100) DEFAULT NULL COMMENT '经办人',
  `police_tel` varchar(50) DEFAULT NULL COMMENT '机关电话',
  `attachment_urls` varchar(2000) DEFAULT NULL COMMENT '附件URL，逗号分隔',
  `ocr_result_json` mediumtext NULL COMMENT 'DeepSeek识别结果JSON',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` int(11) DEFAULT NULL,
  `remark` varchar(512) DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `create_dept_id` varchar(64) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  `update_time` datetime NOT NULL,
  `del_flag` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_ppc_tenant_no` (`tenant_no`,`cert_no`),
  KEY `idx_ppc_product` (`tenant_no`,`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='易制毒购买备案证明';

CREATE TABLE IF NOT EXISTS `pl_transport_cert` (
  `id` varchar(64) NOT NULL,
  `purchase_cert_id` varchar(64) NOT NULL COMMENT '主挂购买证ID',
  `cert_no` varchar(64) NOT NULL COMMENT '运输证编号',
  `issue_date` date DEFAULT NULL,
  `owner_name` varchar(200) DEFAULT NULL COMMENT '货主',
  `owner_legal` varchar(100) DEFAULT NULL,
  `owner_tel` varchar(50) DEFAULT NULL,
  `owner_addr` varchar(500) DEFAULT NULL,
  `product_name` varchar(200) DEFAULT NULL COMMENT '品名',
  `pack_spec` varchar(100) DEFAULT NULL COMMENT '包装',
  `permit_cert_nos` varchar(500) DEFAULT NULL COMMENT '购买许可/备案/出口许可证号，逗号分隔',
  `carrier_name` varchar(200) DEFAULT NULL COMMENT '运输单位',
  `carrier_contact` varchar(200) DEFAULT NULL COMMENT '住所/电话',
  `transport_mode` varchar(50) DEFAULT NULL COMMENT '运输方式',
  `plate_nos` varchar(500) DEFAULT NULL COMMENT '车号(次)，逗号或顿号分隔',
  `route_desc` varchar(1000) DEFAULT NULL COMMENT '运输路线',
  `consignee_name` varchar(200) DEFAULT NULL COMMENT '收货单位',
  `total_qty` decimal(18,3) NOT NULL DEFAULT 0.000 COMMENT '运输证数量',
  `unit` varchar(20) NOT NULL DEFAULT '吨',
  `valid_times` varchar(50) DEFAULT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `applicant_unit` varchar(200) DEFAULT NULL,
  `police_org` varchar(200) DEFAULT NULL,
  `handler_name` varchar(100) DEFAULT NULL,
  `police_tel` varchar(50) DEFAULT NULL COMMENT '公安机关联系电话',
  `attachment_urls` varchar(2000) DEFAULT NULL,
  `ocr_result_json` mediumtext NULL COMMENT 'DeepSeek识别结果JSON',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` int(11) DEFAULT NULL,
  `remark` varchar(512) DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `create_dept_id` varchar(64) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  `update_time` datetime NOT NULL,
  `del_flag` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_ptc_purchase` (`tenant_no`,`purchase_cert_id`),
  KEY `idx_ptc_cert_no` (`tenant_no`,`cert_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='易制毒运输备案证明';

CREATE TABLE IF NOT EXISTS `pl_plan_product_cert_link` (
  `id` varchar(64) NOT NULL,
  `plan_id` varchar(64) NOT NULL,
  `plan_product_id` varchar(64) NOT NULL,
  `purchase_cert_id` varchar(64) NOT NULL,
  `transport_cert_id` varchar(64) NOT NULL,
  `link_status` varchar(20) NOT NULL DEFAULT 'linked' COMMENT 'linked-已关联 occupied-已占用(齐备) voided-已失效',
  `occupy_plan_qty` decimal(18,3) DEFAULT NULL COMMENT '齐备时按计划量占用',
  `occupy_actual_qty` decimal(18,3) DEFAULT NULL COMMENT '归档实际量(P4填写)',
  `tenant_no` varchar(64) NOT NULL DEFAULT 'shanxia2.0',
  `order_num` int(11) DEFAULT NULL,
  `remark` varchar(512) DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `create_dept_id` varchar(64) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  `update_time` datetime NOT NULL,
  `del_flag` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_ppcl_plan` (`tenant_no`,`plan_id`),
  KEY `idx_ppcl_product` (`tenant_no`,`plan_product_id`),
  KEY `idx_ppcl_purchase` (`tenant_no`,`purchase_cert_id`,`link_status`),
  KEY `idx_ppcl_transport` (`tenant_no`,`transport_cert_id`,`link_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划易制毒产品行证书关联';

-- ---------------------------------------------------------------------------
-- 菜单：与「危化品证上传」同级挂在 PL_MENU_ROOT 下；按钮权限 edit / confirm
-- 角色授权：复制已有 pl:plan-cert:upload 的角色
-- ---------------------------------------------------------------------------

-- 仅首次插入时后移后续菜单（幂等：已存在 PL_PURCHASE_CERT 则跳过）
UPDATE `sys_menu` m
SET m.`order_num` = m.`order_num` + 1,
    m.`update_by` = 'system',
    m.`update_time` = NOW()
WHERE m.`parent_id` = 'PL_MENU_ROOT'
  AND m.`del_flag` = 0
  AND m.`order_num` >= 4
  AND m.`id` <> 'PL_PURCHASE_CERT'
  AND NOT EXISTS (
    SELECT 1 FROM (SELECT `id` FROM `sys_menu` WHERE `id` = 'PL_PURCHASE_CERT' AND `del_flag` = 0) t
  );

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`
) VALUES
(
  'PL_PURCHASE_CERT', '购买证管理', 'PL_MENU_ROOT', 'C', 'purchase-cert',
  'pl/purchase-cert/index', 'pl:purchase-cert:list',
  1, '1', '1', 'shanxia2.0', 4, '易制毒购买证/运输证管理与待确认计划关联',
  'system', '', NOW(), 'system', NOW(), 0
),
(
  'PL_PURCHASE_CERT_E', '录入编辑', 'PL_PURCHASE_CERT', 'F', '', '',
  'pl:purchase-cert:edit',
  1, '1', '1', 'shanxia2.0', 1, NULL,
  'system', '', NOW(), 'system', NOW(), 0
),
(
  'PL_PURCHASE_CERT_C', '确认生效', 'PL_PURCHASE_CERT', 'F', '', '',
  'pl:purchase-cert:confirm',
  1, '1', '1', 'shanxia2.0', 2, NULL,
  'system', '', NOW(), 'system', NOW(), 0
)
ON DUPLICATE KEY UPDATE
  `menu_name` = VALUES(`menu_name`),
  `parent_id` = VALUES(`parent_id`),
  `path` = VALUES(`path`),
  `component` = VALUES(`component`),
  `perms` = VALUES(`perms`),
  `order_num` = VALUES(`order_num`),
  `remark` = VALUES(`remark`),
  `update_by` = 'system',
  `update_time` = NOW(),
  `del_flag` = 0;

-- 给已有「危化品证上传」按钮权限的角色同步授权（含祖先 PL_PURCHASE_CERT）
-- id 用角色+菜单确定性拼接，避免 UUID() 主键碰撞
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
  CONCAT('RM_PC_', LEFT(REPLACE(rm.role_id, '-', ''), 20), '_', m.suffix),
  rm.role_id,
  m.id,
  'shanxia2.0',
  0,
  NULL,
  'system',
  '',
  NOW(),
  'system',
  NOW(),
  0
FROM (
  SELECT DISTINCT `role_id`
  FROM `sys_role_menu`
  WHERE `del_flag` = 0
    AND `menu_id` IN ('PL_PLAN_CERT', 'PL_PLAN_CERT_U', 'PL_PLAN_CERT_R')
) rm
CROSS JOIN (
  SELECT 'PL_PURCHASE_CERT' AS id, 'L' AS suffix
  UNION ALL SELECT 'PL_PURCHASE_CERT_E', 'E'
  UNION ALL SELECT 'PL_PURCHASE_CERT_C', 'C'
) m
WHERE 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
);