-- =====================================================================
-- 种子质检「袋码 / 托盘码 / 批次码」三种扫码类型 —— 数据库升级总脚本
--
-- 用途：把「种子质检三类型 + 生产线扫码入库三类型」所需的库结构一次性对齐。
--       全部 DDL 带存在性判断，可对同一环境重复执行，已存在的列/索引/表/
--       菜单/仓库数据会跳过，不再报 1060 Duplicate column name。
--
-- 用法：必须先选择数据库（use <库名>）再整份执行：
--         mysql -u<user> -p <库名> < mes_seed_quality_multitype_upgrade.sql
--       或在客户端里先 use <库名>。
--       若未选库，第 0 步会明确报出，且所有 DDL 一律跳过（不会误改结构）。
--
-- 前置表（缺失说明环境过旧，需先跑对应基础脚本）：
--   mes_pro_bag_code / mes_pro_pallet / mes_pro_batch / mes_pro_scan_event
--   mes_wm_warehouse / mes_wm_warehouse_location / mes_wm_warehouse_area
--   mes_qc_seed_quality / system_menu
--   其中 mes_pro_scan_event 由 sql/mes_scan_event_inbound.sql 创建
--
-- 本脚本不含任何 DELETE，也不做数据搬迁，可安全在生产库执行。
-- 提醒：mes_wm_qualified_warehouse.sql 里的「虚拟仓库合格库存搬迁」是破坏性
--       数据迁移（先合并再删源记录），不在本脚本内，需要时单独评估后执行。
-- 本脚本不使用多租户，无 tenant_id 字段。
-- =====================================================================

-- ---------------------------------------------------------------------
-- 0. 目标库确认（未选库时所有 DDL 会被跳过，只报提示）
-- ---------------------------------------------------------------------
SET @db = DATABASE();
SELECT IF(@db IS NULL OR @db = '',
  '未选择数据库：请先 use <库名> 再执行本脚本，本次未做任何结构变更',
  CONCAT('目标库：', @db)) AS db_check;

-- ---------------------------------------------------------------------
-- 1. 每袋质检状态：质检按袋粒度流转、销售出库校验的依据
-- ---------------------------------------------------------------------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'qc_status');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `qc_status` TINYINT NOT NULL DEFAULT 10 COMMENT ''质检状态（10未质检 20质检合格 30质检不合格）'' AFTER `status`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 存量回填：按已有种子质检记录同步袋码质检状态（幂等，仅改不一致的行）
UPDATE `mes_pro_bag_code` b
  JOIN `mes_qc_seed_quality` q ON b.code = q.code AND b.deleted = 0
SET b.qc_status = IF(q.result = 1, 20, 30), b.update_time = NOW()
WHERE b.qc_status <> IF(q.result = 1, 20, 30);

-- ---------------------------------------------------------------------
-- 2. 单袋入库标记：resolve 的 inbound 判断、submit-group 的
--    「有效袋码必须全部入库」校验都依赖该列
-- ---------------------------------------------------------------------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'inbound_event_id');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `inbound_event_id` BIGINT NULL COMMENT ''入库事件ID''',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------
-- 3. 三个内部业务码：托盘码 / 批次码识别时与追溯码锁定同一对象，防止重复质检
-- ---------------------------------------------------------------------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `code`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_pallet` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `code`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_batch' AND column_name = 'business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_batch` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `code`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND index_name = 'uk_business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_bag_code` ADD UNIQUE KEY `uk_business_code` (`business_code`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND index_name = 'uk_business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_pallet` ADD UNIQUE KEY `uk_business_code` (`business_code`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.statistics
  WHERE table_schema = @db AND table_name = 'mes_pro_batch' AND index_name = 'uk_business_code');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_batch` ADD UNIQUE KEY `uk_business_code` (`business_code`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 历史数据不由脚本复制追溯码，避免业务码与追溯码重复；在「袋码列表」页面勾选补生成。

-- ---------------------------------------------------------------------
-- 4. 生产线扫码入库：三种扫码类型 + 设备编码非必填 + 整托入库去重标记
-- ---------------------------------------------------------------------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_scan_event' AND column_name = 'scan_type');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_scan_event` ADD COLUMN `scan_type` VARCHAR(16) NULL COMMENT ''扫码类型（BAG袋码/PALLET托盘码/BATCH批次码）'' AFTER `bag_code`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 设备编码改为非必填（前端已去掉该录入项，历史列保持兼容）。
-- MODIFY 本身可重复执行；表不存在时会报错，属预期（说明未跑基础脚本）。
ALTER TABLE `mes_pro_scan_event`
  MODIFY COLUMN `device_code` VARCHAR(64) NULL DEFAULT NULL COMMENT '设备编码（不再必填）';

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_event_id');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_pallet` ADD COLUMN `inbound_event_id` BIGINT NULL COMMENT ''最近入库扫码事件编号'' AFTER `bag_count`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_time');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_pro_pallet` ADD COLUMN `inbound_time` DATETIME NULL COMMENT ''最近入库时间'' AFTER `inbound_event_id`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 历史扫码事件按袋码处理
UPDATE `mes_pro_scan_event` SET `scan_type` = 'BAG' WHERE `scan_type` IS NULL;

-- ---------------------------------------------------------------------
-- 5. 仓库类型 + 合格库数据
--    质检页「入库合格库」下拉只列 type=20 且未冻结的仓库，缺了无法提交质检
-- ---------------------------------------------------------------------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_wm_warehouse' AND column_name = 'type');
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
  'ALTER TABLE `mes_wm_warehouse` ADD COLUMN `type` TINYINT NOT NULL DEFAULT 10 COMMENT ''仓库类型：10暂存库，20合格库'' AFTER `frozen`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 现有仓库归类：虚拟线边仓库=暂存库；产品库/物料库=合格库
UPDATE `mes_wm_warehouse` SET `type` = 10 WHERE `code` = 'WIP_VIRTUAL_WAREHOUSE' AND `deleted` = 0;
UPDATE `mes_wm_warehouse` SET `type` = 20 WHERE `code` IN ('1212', 'WHS000001') AND `deleted` = 0;

-- 新建「成品合格库」+ 库区 + 库位（幂等）
INSERT INTO `mes_wm_warehouse` (`code`, `name`, `frozen`, `type`, `remark`)
SELECT 'FG_QUALIFIED_WH', '成品合格库', 0, 20, '质检合格直接入库的目标仓库（type=20 合格库）'
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse` WHERE `code` = 'FG_QUALIFIED_WH' AND `deleted` = 0);
SET @wh_id = (SELECT `id` FROM `mes_wm_warehouse` WHERE `code` = 'FG_QUALIFIED_WH' AND `deleted` = 0 LIMIT 1);

INSERT INTO `mes_wm_warehouse_location` (`code`, `name`, `warehouse_id`, `frozen`, `remark`)
SELECT 'FG_QUALIFIED_WH_LOC', '成品合格库区', @wh_id, 0, '质检合格入库默认库区'
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_location` WHERE `code` = 'FG_QUALIFIED_WH_LOC' AND `deleted` = 0);
SET @loc_id = (SELECT `id` FROM `mes_wm_warehouse_location` WHERE `code` = 'FG_QUALIFIED_WH_LOC' AND `deleted` = 0 LIMIT 1);

INSERT INTO `mes_wm_warehouse_area` (`code`, `name`, `location_id`, `frozen`, `remark`)
SELECT 'FG_QUALIFIED_WH_AREA', '成品合格库位', @loc_id, 0, '质检合格入库默认库位'
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_area` WHERE `code` = 'FG_QUALIFIED_WH_AREA' AND `deleted` = 0);

-- ---------------------------------------------------------------------
-- 6. 按袋库存明细表：质检合格 / 不合格按袋扣暂存、入合格库
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mes_wm_material_stock_bag` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
    `material_stock_id` BIGINT NOT NULL COMMENT '库存汇总记录ID',
    `bag_code_id` BIGINT NOT NULL COMMENT '生产袋码ID',
    `bag_code` VARCHAR(64) NOT NULL COMMENT '袋码',
    `item_id` BIGINT NOT NULL COMMENT '物料ID',
    `batch_id` BIGINT NULL COMMENT '仓库批次ID',
    `batch_code` VARCHAR(64) NULL COMMENT '批次号',
    `vendor_id` BIGINT NULL COMMENT '供应商ID',
    `warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
    `location_id` BIGINT NULL COMMENT '库区ID',
    `area_id` BIGINT NULL COMMENT '库位ID',
    `stock_state` TINYINT NOT NULL COMMENT '库存状态：10暂存，20合格，30已出库，40报废',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '明细状态：1在库，2已出库，3已报废',
    `quantity` DECIMAL(18, 6) NOT NULL DEFAULT 1 COMMENT '袋码库存数量',
    `frozen` BIT NOT NULL DEFAULT 0 COMMENT '是否冻结',
    `last_transaction_id` BIGINT NULL COMMENT '最近库存事务ID',
    `inbound_time` DATETIME NULL COMMENT '入库时间',
    `outbound_time` DATETIME 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 NOT NULL DEFAULT 0 COMMENT '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_bag_code` (`bag_code`),
    KEY `idx_material_stock` (`material_stock_id`),
    KEY `idx_batch_state` (`batch_id`, `stock_state`, `status`),
    KEY `idx_location_state` (`warehouse_id`, `location_id`, `area_id`, `stock_state`, `status`),
    KEY `idx_bag_code_id` (`bag_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MES库存袋码明细';

-- ---------------------------------------------------------------------
-- 7. 质检记录独立菜单（挂在「质量管理」5500 下）
-- ---------------------------------------------------------------------
INSERT INTO `system_menu`
(`id`, `name`, `parent_id`, `type`, `sort`, `status`, `path`, `icon`, `component`, `component_name`, `permission`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 1065224, '质检记录', 5500, 2, 95, 0, 'seed-quality-record', 'ep:list', 'mes/qc/seedQuality/record', 'MesQcSeedQualityRecord', '', b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1065224 AND `deleted` = b'0');

-- ---------------------------------------------------------------------
-- 8. 执行后自检：每项 present=1 为就绪；最后一项应 >= 1
--    若全部为 0，多半是第 0 步报的「未选择数据库」
-- ---------------------------------------------------------------------
SELECT 'mes_pro_bag_code.qc_status' AS item, COUNT(*) AS present FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'qc_status'
UNION ALL SELECT 'mes_pro_bag_code.inbound_event_id', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'inbound_event_id'
UNION ALL SELECT 'mes_pro_bag_code.business_code', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'business_code'
UNION ALL SELECT 'mes_pro_pallet.business_code', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'business_code'
UNION ALL SELECT 'mes_pro_batch.business_code', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_batch' AND column_name = 'business_code'
UNION ALL SELECT 'mes_pro_scan_event.scan_type', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_scan_event' AND column_name = 'scan_type'
UNION ALL SELECT 'mes_pro_pallet.inbound_event_id', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_event_id'
UNION ALL SELECT 'mes_pro_pallet.inbound_time', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_time'
UNION ALL SELECT 'mes_wm_warehouse.type', COUNT(*) FROM information_schema.columns
  WHERE table_schema = @db AND table_name = 'mes_wm_warehouse' AND column_name = 'type'
UNION ALL SELECT 'TABLE mes_wm_material_stock_bag', COUNT(*) FROM information_schema.tables
  WHERE table_schema = @db AND table_name = 'mes_wm_material_stock_bag'
UNION ALL SELECT 'MENU 质检记录 1065224', COUNT(*) FROM `system_menu` WHERE `id` = 1065224 AND `deleted` = b'0'
UNION ALL SELECT 'DATA type=20 未冻结仓库', COUNT(*) FROM `mes_wm_warehouse` WHERE `type` = 20 AND `frozen` = 0 AND `deleted` = 0;