shanxia2.0/backend/scripts/migration_20260719_pl_inbound_outbound_order.sql

-- PL 入库单 / 出库单（主从表）+ 菜单「运输管理 → 库存管理 → 入库单/出库单」
-- Spec: docs/superpowers/specs/2026-07-19-pl-inbound-outbound-order-design.md

-- ── 1. 入库主表 ──
CREATE TABLE IF NOT EXISTS `pl_inbound_order` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `order_no` varchar(32) NOT NULL COMMENT '入库单号',
  `plan_id` varchar(64) NOT NULL COMMENT '运输计划ID',
  `transport_id` varchar(64) NOT NULL COMMENT '运输单ID',
  `status` varchar(20) NOT NULL DEFAULT 'in_progress' COMMENT 'in_progress/completed/voided',
  `partner_company` varchar(200) DEFAULT NULL COMMENT '往来单位快照',
  `vehicle_no` varchar(32) DEFAULT NULL COMMENT '车牌快照',
  `plan_no` varchar(64) DEFAULT NULL COMMENT '计划号快照',
  `plan_date` date DEFAULT NULL COMMENT '计划日期快照',
  `site_access_token` varchar(64) NOT NULL COMMENT '现场免登录令牌',
  `site_feedback_remark` varchar(500) DEFAULT NULL COMMENT '现场备注',
  `site_feedback_images` text COMMENT '现场附件URLs（JSON数组）',
  `site_feedback_time` datetime DEFAULT NULL COMMENT '最近现场提交时间',
  `site_first_submitted_at` datetime DEFAULT NULL COMMENT '首次现场提交时间',
  `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` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pl_inbound_order_no` (`tenant_no`,`order_no`),
  UNIQUE KEY `uk_pl_inbound_site_token` (`site_access_token`),
  KEY `idx_pl_inbound_plan` (`tenant_no`,`plan_id`),
  KEY `idx_pl_inbound_transport` (`tenant_no`,`transport_id`),
  KEY `idx_pl_inbound_status` (`tenant_no`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划-入库单';

CREATE TABLE IF NOT EXISTS `pl_inbound_order_item` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `order_id` varchar(64) NOT NULL COMMENT '入库单ID',
  `plan_product_id` varchar(64) NOT NULL COMMENT '计划产品行ID',
  `product_id` varchar(64) DEFAULT NULL COMMENT 'CRM产品ID',
  `product_name` varchar(200) DEFAULT NULL COMMENT '产品名称快照',
  `specification` varchar(200) DEFAULT NULL COMMENT '规格快照',
  `unit` varchar(32) DEFAULT NULL COMMENT '单位',
  `plan_qty` decimal(15,3) DEFAULT NULL COMMENT '计划数量',
  `actual_qty` decimal(15,3) DEFAULT NULL COMMENT '实际数量',
  `qty_source` varchar(20) DEFAULT 'plan' COMMENT 'plan/dibang/manual',
  `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` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_pl_inbound_item_order` (`order_id`),
  KEY `idx_pl_inbound_item_plan_prod` (`plan_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划-入库单明细';

-- ── 2. 出库主从表（对称）──
CREATE TABLE IF NOT EXISTS `pl_outbound_order` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `order_no` varchar(32) NOT NULL COMMENT '出库单号',
  `plan_id` varchar(64) NOT NULL COMMENT '运输计划ID',
  `transport_id` varchar(64) NOT NULL COMMENT '运输单ID',
  `status` varchar(20) NOT NULL DEFAULT 'in_progress' COMMENT 'in_progress/completed/voided',
  `partner_company` varchar(200) DEFAULT NULL COMMENT '往来单位快照',
  `vehicle_no` varchar(32) DEFAULT NULL COMMENT '车牌快照',
  `plan_no` varchar(64) DEFAULT NULL COMMENT '计划号快照',
  `plan_date` date DEFAULT NULL COMMENT '计划日期快照',
  `site_access_token` varchar(64) NOT NULL COMMENT '现场免登录令牌',
  `site_feedback_remark` varchar(500) DEFAULT NULL COMMENT '现场备注',
  `site_feedback_images` text COMMENT '现场附件URLs（JSON数组）',
  `site_feedback_time` datetime DEFAULT NULL COMMENT '最近现场提交时间',
  `site_first_submitted_at` datetime DEFAULT NULL COMMENT '首次现场提交时间',
  `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` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pl_outbound_order_no` (`tenant_no`,`order_no`),
  UNIQUE KEY `uk_pl_outbound_site_token` (`site_access_token`),
  KEY `idx_pl_outbound_plan` (`tenant_no`,`plan_id`),
  KEY `idx_pl_outbound_transport` (`tenant_no`,`transport_id`),
  KEY `idx_pl_outbound_status` (`tenant_no`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划-出库单';

CREATE TABLE IF NOT EXISTS `pl_outbound_order_item` (
  `id` varchar(64) NOT NULL COMMENT '主键ID',
  `order_id` varchar(64) NOT NULL COMMENT '出库单ID',
  `plan_product_id` varchar(64) NOT NULL COMMENT '计划产品行ID',
  `product_id` varchar(64) DEFAULT NULL COMMENT 'CRM产品ID',
  `product_name` varchar(200) DEFAULT NULL COMMENT '产品名称快照',
  `specification` varchar(200) DEFAULT NULL COMMENT '规格快照',
  `unit` varchar(32) DEFAULT NULL COMMENT '单位',
  `plan_qty` decimal(15,3) DEFAULT NULL COMMENT '计划数量',
  `actual_qty` decimal(15,3) DEFAULT NULL COMMENT '实际数量',
  `qty_source` varchar(20) DEFAULT 'plan' COMMENT 'plan/dibang/manual',
  `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` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_pl_outbound_item_order` (`order_id`),
  KEY `idx_pl_outbound_item_plan_prod` (`plan_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输计划-出库单明细';

-- ── 3. 菜单：库存管理分组 + 入库单/出库单 ──
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_GROUP_STOCK', '库存管理', 'PL_MENU_ROOT', 'M', 'stock-module', NULL, NULL, 1, '1', '1', 'shanxia2.0', 2, '入库/出库单', 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_INBOUND', '入库单', 'PL_GROUP_STOCK', 'C', 'inbound', 'pl/inbound/index', 'pl:inbound:list', 1, '1', '1', 'shanxia2.0', 1, NULL, 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_OUTBOUND', '出库单', 'PL_GROUP_STOCK', 'C', 'outbound', 'pl/outbound/index', 'pl:outbound:list', 1, '1', '1', 'shanxia2.0', 2, NULL, 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_INBOUND_Q', '查询', 'PL_INBOUND', 'F', '', '', 'pl:inbound:query', 1, '1', '1', 'shanxia2.0', 1, NULL, 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_INBOUND_E', '编辑', 'PL_INBOUND', 'F', '', '', 'pl:inbound:edit', 1, '1', '1', 'shanxia2.0', 2, NULL, 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_OUTBOUND_Q', '查询', 'PL_OUTBOUND', 'F', '', '', 'pl:outbound:query', 1, '1', '1', 'shanxia2.0', 1, NULL, 'system', '', NOW(), NULL, NOW(), 0),
  ('PL_OUTBOUND_E', '编辑', 'PL_OUTBOUND', 'F', '', '', 'pl:outbound:edit', 1, '1', '1', 'shanxia2.0', 2, NULL, 'system', '', NOW(), NULL, 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),
  update_time = NOW();

-- 购买证分组排序后移，给库存管理让出 order_num=2
UPDATE sys_menu SET order_num = 3, update_time = NOW() WHERE id = 'PL_GROUP_CERT' AND del_flag = 0;
UPDATE sys_menu SET order_num = 4, update_time = NOW() WHERE id = 'PL_GROUP_FREIGHT' AND del_flag = 0;
UPDATE sys_menu SET order_num = 5, update_time = NOW() WHERE id = 'PL_GROUP_VEHICLE' AND del_flag = 0;
UPDATE sys_menu SET order_num = 6, update_time = NOW() WHERE id = 'PL_GROUP_DOC' AND del_flag = 0;
UPDATE sys_menu SET order_num = 7, update_time = NOW() WHERE id = 'PL_GROUP_BASE' AND del_flag = 0;

-- 拥有运输计划权限的角色，补全库存管理菜单
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('PLSTM_', SUBSTRING(MD5(CONCAT(rm.role_id, '-', m.id)), 1, 24)),
  rm.role_id,
  m.id,
  COALESCE(rm.tenant_no, 'shanxia2.0'),
  0,
  'pl stock menu auto grant',
  'system',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_role_menu rm
CROSS JOIN (
  SELECT 'PL_GROUP_STOCK' AS id
  UNION ALL SELECT 'PL_INBOUND'
  UNION ALL SELECT 'PL_OUTBOUND'
  UNION ALL SELECT 'PL_INBOUND_Q'
  UNION ALL SELECT 'PL_INBOUND_E'
  UNION ALL SELECT 'PL_OUTBOUND_Q'
  UNION ALL SELECT 'PL_OUTBOUND_E'
) m
WHERE rm.del_flag = 0
  AND rm.menu_id IN ('PL_PLAN', 'PL_PLAN_ARCHIVE', 'PL_MENU_ROOT', 'PL_GROUP_PLAN')
  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
  );