AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.340.sql

-- 1.0.340 S5 库存口径：legacy 三表归档 + 对账差异表
-- 禁止同脚本 DROP；30 天后另发迁移脚本删除 zz_archive_*。

SET NAMES utf8mb4;

-- ① 归档本库冻结镜像（若仍存在原名）
SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='LocationDetail') > 0
  AND (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='zz_archive_20260813_LocationDetail') = 0,
  'RENAME TABLE `LocationDetail` TO `zz_archive_20260813_LocationDetail`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='InvTransHist') > 0
  AND (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='zz_archive_20260813_InvTransHist') = 0,
  'RENAME TABLE `InvTransHist` TO `zz_archive_20260813_InvTransHist`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='InvMaster') > 0
  AND (SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='zz_archive_20260813_InvMaster') = 0,
  'RENAME TABLE `InvMaster` TO `zz_archive_20260813_InvMaster`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ② 日终对账差异
CREATE TABLE IF NOT EXISTS `ado_inventory_recon_diff` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL,
  `domain` varchar(32) NOT NULL,
  `item_num` varchar(80) NOT NULL,
  `lot_serial` varchar(120) NOT NULL DEFAULT '',
  `location` varchar(32) NOT NULL,
  `qty_live` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_std` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_diff` decimal(18,5) NOT NULL DEFAULT 0,
  `recon_batch_id` varchar(100) NOT NULL,
  `as_of` datetime(3) NOT NULL,
  `remark` varchar(500) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inv_recon_batch` (`recon_batch_id`),
  KEY `idx_inv_recon_item` (`tenant_id`,`domain`,`item_num`,`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='165 LocationDetail 与 mdp_std_inventory 日终对账差异';