-- 工艺路线工序产出按产品归属及默认报工库位（MySQL 8.x）
-- 先执行本脚本，再部署对应后端版本；历史工艺路线产品启用前需补齐仓库、库区、库位。

CREATE TABLE IF NOT EXISTS `mes_pro_route_process_output` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `route_process_id` bigint NOT NULL COMMENT '工艺路线工序编号',
  `route_id` bigint NOT NULL COMMENT '工艺路线编号',
  `process_id` bigint NOT NULL COMMENT '工序编号',
  `product_id` bigint DEFAULT NULL COMMENT '归属产品编号，空为通用',
  `item_id` bigint NOT NULL COMMENT '产出物料编号',
  `sort` int DEFAULT NULL COMMENT '排序',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  KEY `idx_route_process_id` (`route_process_id`),
  KEY `idx_route_id` (`route_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MES 工艺路线工序产出归属';

-- 当前分支保留的工艺参数模板和工序收率；目标库可能尚未执行对应历史迁移。
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'param_template_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_route_process ADD COLUMN param_template_id BIGINT NULL COMMENT ''工艺参数模板编号'' AFTER process_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'yield_rate'),
  'SELECT 1', 'ALTER TABLE mes_pro_route_process ADD COLUMN yield_rate DECIMAL(10,4) NULL COMMENT ''工序收率（产出/投入，0~1）'' AFTER param_template_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'warehouse_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN warehouse_id BIGINT NULL COMMENT ''报工产出仓库ID'' AFTER item_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'location_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN location_id BIGINT NULL COMMENT ''报工产出库区ID'' AFTER warehouse_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'area_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN area_id BIGINT NULL COMMENT ''报工产出库位ID'' AFTER location_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'param_template_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN param_template_id BIGINT NULL COMMENT ''工艺参数模板编号'' AFTER process_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'yield_rate'),
  'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN yield_rate DECIMAL(10,4) NULL COMMENT ''工序收率（产出/投入，0~1）'' AFTER param_template_id');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'warehouse_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN warehouse_id BIGINT NULL COMMENT ''报工产出仓库ID''');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'location_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN location_id BIGINT NULL COMMENT ''报工产出库区ID''');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'area_id'),
  'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN area_id BIGINT NULL COMMENT ''报工产出库位ID''');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;